BirdNET-BarChart/birds.db.ddl.sql at dev · dacracot/BirdNET-BarChart · 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
-- --------------------------------------
-- bird observations
DROP TABLE IF EXISTS heard;
-- -----
DROP INDEX IF EXISTS heardCommonName;
DROP INDEX IF EXISTS heardMinuteOfDay;
-- -----
CREATE TABLE heard (
	startSecond FLOAT NOT NULL,
	endSecond FLOAT NOT NULL,
	scientificName TEXT NOT NULL,
	commonName TEXT NOT NULL,
	confidence FLOAT NOT NULL,
	minuteOfDay DATETIME NOT NULL
	);
-- -----
CREATE INDEX heardCommonName ON heard (
	commonName
	);
-- -----
CREATE INDEX heardMinuteOfDay ON heard (
	minuteOfDay
	);
-- --------------------------------------
-- solar snapshots
DROP TABLE IF EXISTS sun;
-- -----
CREATE TABLE sun (
	dawn DATETIME NOT NULL,
	up DATETIME NOT NULL,
	peak DATETIME NOT NULL,
	down DATETIME NOT NULL,
	dusk DATETIME NOT NULL,
	minuteOfDay DATETIME NOT NULL
	);
-- --------------------------------------
-- lunar snapshots
DROP TABLE IF EXISTS moon;
-- -----
CREATE TABLE moon (
	phase TEXT NOT NULL,
	up DATETIME,
	peak DATETIME NOT NULL,
	down DATETIME,
	minuteOfDay DATETIME NOT NULL
	);
-- --------------------------------------
-- weather snapshots
DROP TABLE IF EXISTS weather;
-- -----
CREATE TABLE weather (
	condition TEXT NOT NULL,
	iconNumber INTEGER NOT NULL,
	temperature TEXT NOT NULL,
	humidity TEXT NOT NULL,
	wind TEXT NOT NULL,
	precipitation TEXT NOT NULL,
	pressure TEXT NOT NULL,
	minuteOfDay DATETIME NOT NULL
	);
-- --------------------------------------
-- seasonal timeframes
DROP TABLE IF EXISTS season;
-- -----
CREATE TABLE season (
	phase TEXT NOT NULL,
	changeover DATETIME NOT NULL
	);
-- --------------------------------------
-- Color Assignments
DROP TABLE IF EXISTS color;
-- -----
CREATE TABLE color (
	name TEXT NOT NULL,
	commonName TEXT NOT NULL
	);
-- --------------------------------------
-- Color Candidates
DROP TABLE IF EXISTS candidate;
-- -----
CREATE TABLE candidate (
	name TEXT NOT NULL
	);
-- --------------------------------------
INSERT INTO candidate VALUES ('Salmon');
INSERT INTO candidate VALUES ('Red');
INSERT INTO candidate VALUES ('FireBrick');
INSERT INTO candidate VALUES ('Pink');
INSERT INTO candidate VALUES ('DeepPink');
INSERT INTO candidate VALUES ('Tomato');
INSERT INTO candidate VALUES ('DarkOrange');
INSERT INTO candidate VALUES ('Orange');
INSERT INTO candidate VALUES ('Gold');
INSERT INTO candidate VALUES ('PeachPuff');
INSERT INTO candidate VALUES ('DarkKhaki');
INSERT INTO candidate VALUES ('Violet');
INSERT INTO candidate VALUES ('MediumPurple');
INSERT INTO candidate VALUES ('DarkViolet');
INSERT INTO candidate VALUES ('Lime');
INSERT INTO candidate VALUES ('SeaGreen');
INSERT INTO candidate VALUES ('Olive');
INSERT INTO candidate VALUES ('MediumAquamarine');
INSERT INTO candidate VALUES ('Teal');
INSERT INTO candidate VALUES ('Cyan');
INSERT INTO candidate VALUES ('Tan');
INSERT INTO candidate VALUES ('Sienna');
INSERT INTO candidate VALUES ('Maroon');
INSERT INTO candidate VALUES ('LawnGreen');
INSERT INTO candidate VALUES ('DarkMagenta');
INSERT INTO candidate VALUES ('YellowGreen');
INSERT INTO candidate VALUES ('PaleVioletRed');
INSERT INTO candidate VALUES ('DarkCyan');
INSERT INTO candidate VALUES ('Fuchsia');
INSERT INTO candidate VALUES ('Moccasin');
INSERT INTO candidate VALUES ('Yellow');
INSERT INTO candidate VALUES ('DarkGoldenrod');
INSERT INTO candidate VALUES ('BlueViolet');
INSERT INTO candidate VALUES ('DarkOliveGreen');
-- --------------------------------------