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/

Table creation SQL

createdb -U webadmin motion -W -T template_postgis

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(fk_photo) REFERENCES vehicle,
 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)

);