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

The main tables

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

INSERT INTO vehicle (name) VALUES ('Davidson Discovery');
INSERT INTO vehicle (name) VALUES ('Fuji Touring Series');
INSERT INTO vehicle (name) VALUES ('Longbikes Slipstream');
INSERT INTO vehicle (name) VALUES ('Specialized Stumpjumper');
INSERT INTO vehicle (name) VALUES ('Toyota Yaris');
INSERT INTO vehicle (name) VALUES ('MG Midget');

SELECT vehicle_pkey FROM vehicle;

Child tables

DROP TABLE IF EXISTS photo;
CREATE TABLE photo (
 photo_id SERIAL NOT NULL,
 thumbnail BYTEA,
 image LO,
 description TEXT,
 location POINT,
 tag CHAR NOT NULL,
 fkey INT NOT NULL, 
 FOREIGN KEY (tag,fkey) REFERENCES vehicle(tag,vehicle_id),
 PRIMARY KEY(photo_id)
);
CREATE TRIGGER t_image BEFORE UPDATE OR DELETE ON photo
   FOR EACH ROW EXECUTE PROCEDURE lo_manage(image);

INSERT INTO photo (description,tag,fkey) VALUES ('d','V',1);
INSERT INTO photo (description,tag,fkey) VALUES ('f','V',2);
INSERT INTO photo (description,tag,fkey) VALUES ('l','V',3);
INSERT INTO photo (description,tag,fkey) VALUES ('t1','V',4);
INSERT INTO photo (description,tag,fkey) VALUES ('t2','V',4);
INSERT INTO photo (description,tag,fkey) VALUES ('mg','V',5);

SELECT name,photo.description FROM vehicle, photo WHERE (vehicle.tag,vehicle.vehicle_id) = (photo.tag,photo.fkey);

DROP TABLE IF EXISTS trips;
CREATE TABLE trips (
 tag CHAR DEFAULT 't',
 trip_id SERIAL NOT NULL,
 date DATE,
 mileage NUMERIC,
 vehicle_id INT,
 FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
 gpx_id INT,
 FOREIGN KEY (gpx_id) REFERENCES gpx(gpx_id),
 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)
);