uberJr/databaseSetup.sql at master · luisschubert/uberJr · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135


CREATE TABLE drivers (
    driver_id integer NOT NULL,
    license_plate text NOT NULL,
    car_color text NOT NULL,
    car_year text NOT NULL,
    car_make text NOT NULL,
    is_active boolean DEFAULT false,
    timed_out boolean DEFAULT false,
    timeout_time integer NOT NULL
);


CREATE TABLE users (
    id integer NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    password text NOT NULL,
    is_driver boolean DEFAULT false
);


CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE users_id_seq OWNED BY users.id;


ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);


ALTER TABLE ONLY drivers
    ADD CONSTRAINT driver_pkey PRIMARY KEY (driver_id);


ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


ALTER TABLE ONLY drivers
    ADD CONSTRAINT driver_id FOREIGN KEY (driver_id) REFERENCES users(id) ON DELETE CASCADE;


CREATE TABLE activedrivers (
    id integer NOT NULL,
    current_lat double precision,
    current_long double precision,
    paired boolean DEFAULT false
);

ALTER TABLE ONLY activedrivers
    ADD CONSTRAINT "activedrivers_pkey" PRIMARY KEY (id);

ALTER TABLE ONLY activedrivers
    ADD CONSTRAINT id FOREIGN KEY (id) REFERENCES drivers(driver_id) ON DELETE CASCADE;


CREATE TABLE rides (
    rider_id integer NOT NULL,
    driver_id integer NOT NULL,
    pickup_lat double precision NOT NULL,
    pickup_long double precision NOT NULL,
    dest_lat double precision NOT NULL,
    dest_long double precision NOT NULL,
    accepted boolean DEFAULT false,
    pickedup boolean DEFAULT false,
    complete boolean DEFAULT false


);


ALTER TABLE ONLY rides
    ADD CONSTRAINT driver_id FOREIGN KEY (driver_id) REFERENCES activedrivers(id) ON DELETE CASCADE;

ALTER TABLE ONLY rides
    ADD CONSTRAINT rides_pkey PRIMARY KEY (driver_id);


CREATE TABLE riders (
    id integer NOT NULL,
    rider_id integer NOT NULL,
    origin_lat double precision,
    origin_long double precision,
    destination_lat double precision,
    destination_long double precision,
    paired boolean DEFAULT false
);


CREATE SEQUENCE "Riders_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE "Riders_id_seq" OWNED BY riders.id;


ALTER TABLE ONLY riders ALTER COLUMN id SET DEFAULT nextval('"Riders_id_seq"'::regclass);


ALTER TABLE ONLY riders
    ADD CONSTRAINT "Riders_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY riders
    ADD CONSTRAINT rider_id FOREIGN KEY (rider_id) REFERENCES users(id) ON DELETE CASCADE;