Electri/schema.sql at main · catherinemeadows/Electri · 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
USE electri_db;
set foreign_key_checks = 0;

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS matches;
DROP TABLE IF EXISTS alerts;
DROP TABLE IF EXISTS image_info;

set foreign_key_checks = 1;

-- generate tables
CREATE TABLE user (
    username varchar(32) not null,
    user_password varchar(32) not null,
    fname varchar(32),
    lname varchar(32),
    email varchar(50),
    organization varchar(50),
    is_verified int(1)
);

CREATE TABLE alerts (
    id int(8) not null auto_increment,
    alert_status int(1) not null,
    city varchar(32),
    alert_state varchar(32),
    latitude decimal (6,4),
    longitude decimal (6,4),
    license_plate varchar(8),
    make varchar(32),
    model varchar(32),
    vehicle_year int (4),
    color varchar(20),
    color_rgb blob,
    PRIMARY KEY(id)
);

CREATE TABLE image_info (
    id varchar(128),
    img_path varchar(32) not null,
    colors blob,
    make varchar(32),
    model varchar(32),
    vehicle_year int(4),
    license_plate varchar(32),
    latitude decimal (6,4),
    longitude decimal (6,4),
    PRIMARY KEY(id)
);

CREATE TABLE matches (
    match_id int(8) not null auto_increment,
    img_id varchar(128) not null,
    alert_id int(8) not null,
    FOREIGN KEY (img_id) REFERENCES image_info(id),
    FOREIGN KEY (alert_id) REFERENCES alerts(id),
    PRIMARY KEY(match_id)
);

-- USERS
insert into user(username, user_password, fname, lname, email, organization)
values ("admin", "admin", "Admin", "User", "admin_fake@email.com", "DC MPD", 1);

-- ALERTS
-- alerts with matches in db:
insert into alerts(id, alert_status, city, alert_state, latitude, longitude, license_plate, make, model, vehicle_year, color)
values (00010000, 1, "Washington, DC", "DC", 38.9072, 77.0639, "FN9173", "Toyota", "Camry", 2010, "Black");

insert into alerts(id, alert_status, city, alert_state, latitude, longitude, license_plate, make, model, vehicle_year, color)
values (00010001, 1, "Cincinnati", "OH", 39.1031, 84.5120, "AA00AA", "Ford", "Bronco", 2016, "Blue");

insert into alerts(id, alert_status, city, alert_state, latitude, longitude, license_plate, make, model, vehicle_year, color)
values (00010002, 1, "Boston", "MA", 42.3601, 71.0589, "215BG2", "Honda", "Civic", 2017, "White");

-- alerts with no matches:
insert into alerts(id, alert_status, city, alert_state, latitude, longitude, license_plate, make, model, vehicle_year, color)
values (00010003, 0, "Phoenix", "AZ", 33.4484, 112.0704, "BY8567", "Ford", "F-150", 2018, "Red");

-- insert into alerts(alert_id, alert_status, city, alert_state, license_plate, make, model, vehicle_year, color, color_rgb)
-- values (00010004, 0, )

-- insert into alerts(alert_id, alert_status, city, alert_state, license_plate, make, model, vehicle_year, color, color_rgb)
-- values (00010005, 0, )

-- insert into alerts(alert_id, alert_status, city, alert_state, license_plate, make, model, vehicle_year, color, color_rgb)
-- values (00010005, 0, )

-- insert into alerts(alert_id, alert_status, city, alert_state, license_plate, make, model, vehicle_year, color, color_rgb)
-- values (00010007, 0, )