This repository has been archived by the owner on Mar 16, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.sql
78 lines (65 loc) · 1.71 KB
/
main.sql
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
CREATE TABLE visits (
id INTEGER PRIMARY KEY AUTOINCREMENT,
latitude NUMERIC,
longitude NUMERIC,
wof_id INTEGER,
neighbourhood_id INTEGER,
macrohood_id INTEGER,
locality_id INTEGER,
metroarea_id INTEGER,
region_id INTEGER,
country_id INTEGER,
feelings_id INTEGER,
date TEXT,
name TEXT
);
CREATE INDEX `by_date` ON visits (`date`);
CREATE INDEX `by_neighbourhood` ON visits (`neighbourhood_id`);
CREATE INDEX `by_macrohood` ON visits (`macrohood_id`);
CREATE INDEX `by_locality` ON visits (`locality_id`, `neighbourhood_id`);
CREATE INDEX `by_metroarea` ON visits (`metroarea_id`);
CREATE INDEX `by_locality_macro` ON visits (`locality_id`, `macrohood_id`);
CREATE INDEX `by_region` ON visits (`region_id`);
CREATE INDEX `by_country` ON visits (`country_id`);
CREATE TABLE places (
wof_id INTEGER PRIMARY KEY,
body TEXT,
created TEXT
);
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT
);
CREATE UNIQUE INDEX `by_tag` ON tags (`tag`);
CREATE TABLE places_tags (
wof_id INTEGER,
tag_id INTEGER
);
CREATE UNIQUE INDEX `by_tagid` ON places_tags (`wof_id`, `tag_id`);
CREATE TABLE trips (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
arrival TEXT,
departure TEXT,
wof_id INTEGER,
status_id INTEGER,
notes TEXT
);
CREATE INDEX `trips_by_date` ON trips (`arrival`, `departure`);
CREATE INDEX `trips_by_wofid` ON trips (`wof_id`);
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
created TEXT
);
CREATE UNIQUE INDEX `by_list` ON `lists` (`name`);
CREATE TABLE list_items (
list_id INTEGER,
wof_id INTEGER,
position INTEGER
);
CREATE UNIQUE INDEX `by_list_item` ON list_items (`list_id`, `wof_id`);
CREATE TABLE db (
schema TEXT PRIMARY KEY,
lastmodified INTEGER
);