Motion database: Difference between revisions

From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs)
Brian Wilson (talk | contribs)
mNo edit summary
Line 1: Line 1:
The Motion Database is used to keep track of my rolling stock including cars and bicycles.
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]].
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 ==
== Create the database and add extensions ==
Line 127: Line 124:
Using lo (Large Object) extension to hold photo in database, I like to keep everything in one place so I don't lose them.
Using lo (Large Object) extension to hold photo in database, I like to keep everything in one place so I don't lose them.
(In the database instead of keeping them on the filesystem)
(In the database instead of keeping them on the filesystem)
I could do photo/image processing in the database if I installed the PostPIC extension, see
http://drotiro.github.com/postpic/ Possibly I could use it to generate the thumbnails?


  DROP TABLE IF EXISTS photo;
  DROP TABLE IF EXISTS photo;
Line 148: Line 148:
  SELECT * FROM photo;
  SELECT * FROM photo;


Perhaps use PostPIC extension to create thumbnails?


Photos are good for everything -- people, rides, vehicles, maintenance... so I need a junction table to accommodate this
Photos are good for everything -- people, rides, vehicles, maintenance... so I need a junction table to accommodate this

Revision as of 04:53, 19 March 2013

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.

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

First the look up tables

The vehicle table is used to describe a vehicle

DROP TABLE IF EXISTS vehicle;
CREATE TABLE vehicle (
 vehicle_id SERIAL NOT NULL, 
 name VARCHAR(40) NOT NULL,
 description TEXT,
 serial_number VARCHAR(40),
 PRIMARY KEY(vehicle_id)
);
INSERT INTO vehicle (name,description) VALUES ('Davidson', '1995 Davidson Discovery');
INSERT INTO vehicle (name,description) VALUES ('Fuji', '1998 Fuji Touring Series');
INSERT INTO vehicle (name,description) VALUES ('Blue', '2013 Longbikes Slipstream');
INSERT INTO vehicle (name,description) VALUES ('Swallow', '1990 Specialized Stumpjumper');
INSERT INTO vehicle (name,description) VALUES ('Alba', '2010 Toyota Yaris');
INSERT INTO vehicle (name,description) VALUES ('MG', '1970 MG Midget');
INSERT INTO vehicle (name,description) VALUES ('Honda', 'Honda Civic');
SELECT * FROM vehicle;

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');
INSERT INTO rider (name) VALUES ('Rob');
SELECT * FROM rider;

Trip tables

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 (
 trip_id SERIAL NOT NULL,
 date DATE,
 mileage NUMERIC,
 notes TEXT,
 gpxlog TEXT,
 PRIMARY KEY(trip_id)
);
INSERT INTO trip (date,mileage,notes) VALUES ('3/14/2013',15,'Airport loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/15/2013',22,'Airport loop + Walnut Ave');
INSERT INTO trip (date,mileage,notes) VALUES ('3/16/2013',14,'Bald Hill loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/18/2013',12,'HP loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/20/2013',93,'Home - Flightstats');
SELECT * FROM trip;
DROP TABLE IF EXISTS j_trip;
CREATE TABLE j_trip (
 j_trip_id SERIAL NOT NULL,
 trip_id INT, FOREIGN KEY (trip_id) REFERENCES trip(trip_id),
 rider_id INT, FOREIGN KEY (rider_id) REFERENCES rider(rider_id),
 vehicle_id INT, FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
 PRIMARY KEY(j_trip_id)
);
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (1,1,1); --1 brian davidson
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (2,1,1); --2 "     "
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (2,2,4); --2 julie swallow
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (3,1,2); --3 brian fuji
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (4,1,2); --4 brian fuji
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (4,2,4); --4 julie swallow
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,1,7); --5 brian honda
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,2,7); --5 julie honda
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,3,7); --5 rob honda
SELECT * FROM j_trip;
SELECT t.date,t.notes,r.name,v.name
FROM trip AS t,rider AS r,vehicle AS v, j_trip AS j
WHERE (j.trip_id=t.trip_id) AND (j.rider_id=r.rider_id) AND (j.vehicle_id=v.vehicle_id);
-- WRONG - shows constraint
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,3,8); --5 rob ???

Maintenance records

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;

Photo tables

Using lo (Large Object) extension to hold photo in database, I like to keep everything in one place so I don't lose them. (In the database instead of keeping them on the filesystem)

I could do photo/image processing in the database if I installed the PostPIC extension, see http://drotiro.github.com/postpic/ Possibly I could use it to generate the thumbnails?

DROP TABLE IF EXISTS photo;
CREATE TABLE photo (
 photo_id SERIAL NOT NULL,  PRIMARY KEY(photo_id),
 time TIMESTAMP,
 thumbnail BYTEA,
 image LO,
 lat NUMERIC(8,6), lon NUMERIC(9,6),
 description TEXT
);
CREATE TRIGGER t_image BEFORE UPDATE OR DELETE ON photo
   FOR EACH ROW EXECUTE PROCEDURE lo_manage(image);
-- Lat Lon are from iTouchMap.com
INSERT INTO photo (description) VALUES ('Davidson photo');
INSERT INTO photo (description) VALUES ('Fuji photo');
INSERT INTO photo (description,lat,lon) VALUES ('Bald Hill',44.56739,-123.332099);
INSERT INTO photo (description) VALUES ('New wheel photo');
INSERT INTO photo (description,lat,lon) VALUES ('Bellfountain Rd', 44.495692, -123.337787);
INSERT INTO photo (description,lat,lon) VALUES ('MG in Gold Beach',42.564287, -124.383210);
SELECT * FROM photo;


Photos are good for everything -- people, rides, vehicles, maintenance... so I need a junction table to accommodate this

DROP TABLE IF EXISTS j_photo;
CREATE TABLE j_photo (
 j_photo_id SERIAL NOT NULL, PRIMARY KEY(j_photo_id),
 photo_id INT NOT NULL, FOREIGN KEY (photo_id) REFERENCES photo(photo_id),
 trip_id INT, FOREIGN KEY (trip_id) REFERENCES trip(trip_id),
 rider_id INT, FOREIGN KEY (rider_id) REFERENCES rider(rider_id),
 vehicle_id INT, FOREIGN KEY(vehicle_id) REFERENCES vehicle(vehicle_id),
 maint_id INT, FOREIGN KEY (maint_id) REFERENCES maintenance(maint_id)
);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (1,1);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (2,2);
INSERT INTO j_photo (photo_id,trip_id) VALUES (3,3);
INSERT INTO j_photo (photo_id,maint_id) VALUES (4,2);
INSERT INTO j_photo (photo_id,trip_id) VALUES (5,3);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (6,6);
SELECT p.description,v.name
FROM j_photo AS j, photo AS p, vehicle AS v
WHERE ((j.photo_id=p.photo_id) AND (j.vehicle_id=v.vehicle_id));
SELECT p.description,m.notes
FROM j_photo AS j, photo AS p, maintenance AS m
WHERE ((j.photo_id=p.photo_id) AND (j.maint_id=m.maint_id));
SELECT p.description,t.notes
FROM j_photo AS j, photo AS p, trip AS t
WHERE ((j.photo_id=p.photo_id) AND (j.trip_id=t.trip_id));