Motion database

The Motion Database is used to keep track of my rolling stock including cars and bicycles. It's a demo database for developing concepts in PostgreSQL and Wavemaker.

I could do photo/image processing in the database if I installed PostPIC. http://drotiro.github.com/postpic/

Create the database and add extensions

createdb -U webadmin motion
psql -U webadmin motion

CREATE EXTENSION postgis;

# Add support for Large Objects
CREATE EXTENSION lo;

# Show installed extensions
SELECT * FROM pg_available_extensions
WHERE comment LIKE '%string%' OR installed_version IS NOT NULL ORDER BY name;

# Get more info on an extension
\dx+ postgis
\dx+ lo

Table creation SQL

DROP TABLE IF EXISTS vehicle;
CREATE TABLE vehicle (
 tag CHAR DEFAULT 'V',
 vehicle_id SERIAL NOT NULL, 
 name VARCHAR(20) NOT NULL,
 description TEXT,
 serial_number VARCHAR(40),
 PRIMARY KEY(tag,vehicle_id)
);

DROP TABLE IF EXISTS photos;
CREATE TABLE photos (
 photo_id SERIAL NOT NULL,
 thumbnail BYTEA,
 raster LO,
 description TEXT,
 location POINT,
 FOREIGN KEY(photo_fkey) REFERENCES vehicle(vehicle_pkey),
 PRIMARY KEY(photo_id)
);
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON photos
   FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);

DROP TABLE IF EXISTS trips;
CREATE TABLE trips (
 tag CHAR DEFAULT 't',
 trip_id SERIAL NOT NULL,
 date DATESTAMP,
 mileage NUMERIC,
 vehicle_id FOREIGN KEY,
 gpx_id FOREIGN KEY,
 notes TEXT,
 PRIMARY KEY(tag,trip_id)
);

DROP TABLE IF EXISTS maintenance;
CREATE TABLE maintenance (
 tag CHAR DEFAULT 'M',
 maint_id SERIAL NOT NULL,
 date
 description
 notes
 vehicle_id ,
 PRIMARY KEY(tag,main_id)
);

DROP TABLE IF EXISTS gpx;
CREATE TABLE gpx (
 gpx_id SERIAL NOT NULL,
 upload_date DATESTAMP,
 log TEXT,
 note TEXT,
 PRIMARY KEY(gpx_id)
);