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

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(vehicle_id)
);

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(trip_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');

DROP TABLE IF EXISTS photo;
CREATE TABLE photo (
 photo_id SERIAL NOT NULL,
 thumbnail BYTEA,
 image LO,
 description TEXT,
 location POINT,
 vehicle_id INT NOT NULL, 
 FOREIGN KEY (vehicle_id) REFERENCES vehicle(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,vehicle_id) VALUES ('d',1);
INSERT INTO photo (description,vehicle_id) VALUES ('f',2);
INSERT INTO photo (description,vehicle_id) VALUES ('l',3);
INSERT INTO photo (description,vehicle_id) VALUES ('t1',5);
INSERT INTO photo (description,vehicle_id) VALUES ('t2',5);
INSERT INTO photo (description,vehicle_id) VALUES ('mg',6);

SELECT name,photo.description FROM vehicle, photo WHERE vehicle.vehicle_id = photo.vehicle_id;

DROP TABLE IF EXISTS maintenance;
CREATE TABLE maintenance (
 tag CHAR DEFAULT 'M',
 maint_id SERIAL NOT NULL,
 date DATE,
 description TEXT,
 notes TEXT,
 vehicle_id INT, FOREIGN KEY(vehicle_id) REFERENCES vehicle(vehicle_id),
 PRIMARY KEY(maint_id)
);

INSERT INTO maintenance (date, description, vehicle_id) VALUES ('2/1/2013', 'Oiled chain', 1);
INSERT INTO maintenance (date, description, notes, vehicle_id) VALUES ('3/17/2013', 'New front wheel', 'Phil Wood hub', 4);

SELECT date,name,maintenance.description FROM maintenance,vehicle WHERE maintenance.vehicle_id=vehicle.vehicle_id;

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

INSERT INTO gpx () VALUES ();

SELECT FROM WHERE;