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

The vehicle table is used to describe a vehicle

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

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

The rider table describes people who can be bicyclists or car drivers/passengers.

DROP TABLE IF EXISTS rider;
CREATE TABLE rider (
 rider_id SERIAL NOT NULL,
 name VARCHAR(10) NOT NULL,
 PRIMARY KEY (rider_id)
);

INSERT INTO rider (name) VALUES ('Brian');
INSERT INTO rider (name) VALUES ('Julie');

SELECT date, name, mileage FROM trip,rider WHERE trip.rider_id=rider.rider_id;

One trip can have more than one bicyclist and that means more than one rider, or one vehicle can carry two riders on one trip, so we need a junction table to link together riders, vehicles, and trips. I don't care who drives if more than one person is in a car together. (Maybe they take turns.)

DROP TABLE IF EXISTS trip;
CREATE TABLE trip (
 tag CHAR DEFAULT 'T',
 trip_id SERIAL NOT NULL,
 date DATE,
 mileage NUMERIC,
 notes TEXT,
 gpxlog TEXT,
 PRIMARY KEY(trip_id)
);

INSERT INTO trip (date,mileage,vehicle_id,notes) VALUES ('3/14/2013',15,1,1,'Airport loop');
INSERT INTO trip (date,mileage,vehicle_id,notes) VALUES ('3/15/2013',22,1,1,'Airport loop + Walnut Ave');
INSERT INTO trip (date,mileage,vehicle_id,notes) VALUES ('3/15/2013',22,2,4,'Airport loop + Walnut Ave');

SELECT date, name, mileage FROM trip,rider WHERE trip.rider_id=rider.rider_id;

DROP TABLE IF EXISTS j_trips;
CREATE TABLE j_trip (
 vehicle_id INT, FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
 trip_id INT, FOREIGN KEY (trip_id) REFERENCES trip(trip_id),
);

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