popufare/server/ui/fleet-management/create_tables.sql

CREATE TABLE drivers (
	id INTEGER UNIQUE NOT NULL,
	pin VARCHAR(8) NOT NULL,
	name VARCHAR(32)
);

CREATE TABLE stops (
	id INTEGER UNIQUE PRIMARY KEY,
	latitude REAL NOT NULL,
	longitude REAL NOT NULL,
	name VARCHAR(32)
);

CREATE TABLE paddles (
	id INTEGER NOT NULL,
	INDEX ididx(id),
	slot INTEGER NOT NULL,
	arrival TIME,
	route INTEGER,
	trip INTEGER,
	stage INTEGER,
	stop INTEGER,
	stopid INTEGER NOT NULL
);

CREATE TABLE live_stops (
	id INTEGER UNIQUE PRIMARY KEY,
	latitude REAL NOT NULL,
	longitude REAL NOT NULL,
	name VARCHAR(32)
);

CREATE TABLE live_paddles (
	id INTEGER NOT NULL,
	INDEX ididx(id),
	slot INTEGER NOT NULL,
	arrival TIME,
	route INTEGER,
	trip INTEGER,
	stage INTEGER,
	stop INTEGER,
	stopid INTEGER NOT NULL
);

CREATE TABLE old_stops (
	verstring VARCHAR(256),
	id INTEGER NOT NULL,
	latitude REAL NOT NULL,
	longitude REAL NOT NULL,
	name VARCHAR(32)
);

CREATE TABLE old_paddles (
	verstring VARCHAR(256),
	id INTEGER NOT NULL,
	slot INTEGER NOT NULL,
	arrival TIME,
	route INTEGER,
	trip INTEGER,
	stage INTEGER,
	stop INTEGER,
	stopid INTEGER NOT NULL
);