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.

For demos I sometimes run PostgreSQL on a Mac

Create the database and add extensions

export PGDATA=localhost
createdb -U postgres motion

psql -U postgres template1
CREATE USER motion WITH PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE motion TO motion;

psql -U motion 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

This is the SQL required to create a vehicle tracking database in PostgreSQL that can be used from Wavemaker.

psql -U postgres

CREATE USER motion WITH PASSWORD 'QuintanaRoo';
CREATE DATABASE motion WITH owner=motion;
\c motion;
CREATE EXTENSION postgis;
# for photo support
CREATE EXTENSION lo;

Tables

First create 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;

Now, the 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 (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 ???

Add tables for 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;

Current location for AVL

Each vehicle can have just one current location.

DROP TABLE IF EXISTS location;
CREATE TABLE location (
 location_id SERIAL NOT NULL,
 vehicle_id INT, FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
 time TIMESTAMP,
 PRIMARY KEY(location_id)
);
SELECT AddGeometryColumn('location', 'geom', 4326, 'POINT', 2 );

\d location
                                      Table "public.location"
  Column    |         Type         |                           Modifiers                            
-------------+----------------------+----------------------------------------------------------------
location_id | integer              | not null default nextval('location_location_id_seq'::regclass)
vehicle_id  | integer              | 
geom        | geometry(Point,4326) | 
Indexes:
   "location_pkey" PRIMARY KEY, btree (location_id)
Foreign-key constraints:
   "location_vehicle_id_fkey" FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id)

INSERT INTO location (vehicle_id, geom, time) VALUES(1, ST_GeometryFromText('POINT(0 0)',4326), NOW());

SELECT vehicle.name, time, ST_AsText(location.geom) FROM location, vehicle WHERE location.vehicle_id=vehicle.vehicle_id;

Location logs

I should do this but not today.

Dispatch log

A log of places to which vehicles have been dispatched. Yes, I need this now! This is a little sparse but the hour is late.

DROP TABLE IF EXISTS dispatch_log;
CREATE TABLE dispatch_log (
 dispatch_id SERIAL NOT NULL,  PRIMARY KEY(dispatch_id),
 time TIMESTAMP,
 description VARCHAR(255)
);

Credentials

To read dispatches I need access to email on a server so I need a place to keep those settings.

DROP TABLE IF EXISTS account;
CREATE TABLE account (
  user_id SERIAL NOT NULL,  PRIMARY KEY(user_id),
  login VARCHAR(40),
  password VARCHAR(40),
  server VARCHAR(40)
);

Everyone needs photo tables

I don't have the lo extension installed on Kilchis so I left this table out on that installation. Maybe not everyone needs photos.

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),
 title VARCHAR(80),
 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));

Views

Views give me results that appear denormalized so that Wavemaker does not need to know about the joins. The problem is that you can't access tables for writing if they are in views. So these are read-only.

-- simplest case first
CREATE VIEW maintenance_view AS
SELECT date,name,maintenance.description FROM maintenance,vehicle WHERE maintenance.vehicle_id=vehicle.vehicle_id;

-- more complicated version
CREATE VIEW trip_view (date, mileage, notes, rider_name, vehicle_name) AS
SELECT t.date,t.mileage,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);

Now I can use these tables in Wavemaker * read only *

Onward

That's enough to develop the sample app so go back to Wavemaker