TqcTdE9d

1DROP TABLE IF EXISTS Stops;
2DROP TABLE IF EXISTS Agencies;
3DROP TABLE IF EXISTS Routes;
4DROP TABLE IF EXISTS Trips;
5DROP TABLE IF EXISTS FareRules;
6DROP TABLE IF EXISTS FareSpec;
7DROP TABLE IF EXISTS StopTimes;
8DROP TABLE IF EXISTS ServiceCalendar;
9DROP TABLE IF EXISTS ServiceExceptions;
10
11
12CREATE TABLE Agencies(
13	AgencyID SERIAL PRIMARY KEY,
14	ShortName VARCHAR(50),
15	LongName VARCHAR(200)
16);
17
18CREATE TABLE Stops(
19	StopID SERIAL PRIMARY KEY,
20	Code VARCHAR(20),
21	Name VARCHAR(200),
22	Description TEXT,
23	Location POINT,
24	ZoneID INT,
25	StopURL TEXT,
26	LocationType SMALLINT,
27	ParentStation INT REFERENCES Stops(StopID),
28	StopTimezone INT, --need actual datatype
29	WheelchairAccessID SMALLINT	
30);
31
32CREATE TABLE Routes (
33	RouteID SERIAL PRIMARY KEY,
34	AgencyID INT NOT NULL,
35	ShortName VARCHAR(10),
36	LongName VARCHAR(200),
37	Description TEXT,
38	RouteType CHAR,
39	URL TEXT,
40	HexColor CHAR(6),
41	HexTextColor CHAR(6),
42	AgencySortOrder INT
43);
44
45CREATE TABLE Trips (
46	TripID SERIAL PRIMARY KEY,
47	RouteID INT NOT NULL REFERENCES Routes(RouteID),
48	ServiceID INT NOT NULL,
49	Headsign VARCHAR(200),
50	ShortName VARCHAR(100),
51	DirectionID BIT,
52	BlockID INT,
53	Shape PATH,
54	WheelchairAccessID SMALLINT,
55	BikeAccessID SMALLINT
56);
57
58CREATE TABLE FareSpec (
59	FareID SERIAL PRIMARY KEY,
60	Price MONEY NOT NULL,
61	CurrencyType CHAR(3) NOT NULL,
62	PaymentMethod BIT NOT NULL,
63	Transfers INT NOT NULL, --transform NULL to -1
64	AgencyID INT,
65	TransferDuration INT
66);
67
68CREATE TABLE FareRules (
69	FareID INT NOT NULL REFERENCES FareSpec(FareID),
70	RouteID INT REFERENCES Routes(RouteID),
71	OriginID INT,
72	DestinationID INT,
73	ContainsID INT
74);
75
76CREATE TABLE StopTimes (
77	TripID INT NOT NULL REFERENCES Trips(TripID),
78	ArrivalTime TIME NOT NULL, --should use tz?
79	DepartureTime TIME NOT NULL,
80	StopID INT NOT NULL REFERENCES Stops(StopID),
81	StopSequence INT NOT NULL,
82	StopHeadsign VARCHAR(200) NULL,
83	PickupType SMALLINT DEFAULT 0,
84	DropOffType SMALLINT DEFAULT 0,
85	Timepoint BIT DEFAULT CAST(1 AS BIT)
86);
87
88CREATE TABLE ServiceCalendar (
89	ServiceID INT NOT NULL,
90	Monday BIT NOT NULL,
91	Tuesday BIT NOT NULL,
92	Wednesday BIT NOT NULL,
93	Thursday BIT NOT NULL,
94	Friday BIT NOT NULL,
95	Saturday BIT NOT NULL,
96	Sunday BIT NOT NULL,
97	StartDate DATE NOT NULL,
98	EndDate DATE NOT NULL
99);
100
101CREATE TABLE ServiceExceptions (
102	ServiceID INT NOT NULL,
103	ServiceDate DATE NOT NULL,
104	ExceptionType SMALLINT NOT NULL	
105);