File: dbschema.h

package info (click to toggle)
gworkspace 0.9.4-1
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster, sid
  • size: 20,832 kB
  • sloc: objc: 69,382; sh: 488; makefile: 39
file content (185 lines) | stat: -rw-r--r-- 4,650 bytes parent folder | download | duplicates (5)
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185

static NSString *db_version = @"v4";


static NSString *db_schema = @"\
\
CREATE TABLE paths \
(id INTEGER PRIMARY KEY AUTOINCREMENT, \
path TEXT UNIQUE ON CONFLICT IGNORE, \
words_count INTEGER, \
moddate REAL, \
is_directory INTEGER); \
\
CREATE TABLE words \
(id INTEGER PRIMARY KEY AUTOINCREMENT, \
word TEXT UNIQUE ON CONFLICT IGNORE); \
\
CREATE TABLE postings \
(word_id INTEGER REFERENCES words(id), \
path_id INTEGER REFERENCES paths(id), \
word_count INTEGER); \
\
CREATE INDEX postings_wid_index ON postings(word_id); \
CREATE INDEX postings_pid_index ON postings(path_id); \
\
\
CREATE TABLE attributes \
(path_id INTEGER REFERENCES paths(id), \
key TEXT, \
attribute TEXT); \
\
CREATE INDEX attributes_path_index ON attributes(path_id); \
CREATE INDEX attributes_key_attr_index ON attributes(key, attribute); \
CREATE INDEX attributes_attr_index ON attributes(attribute); \
\
\
CREATE TABLE updated_paths \
(id INTEGER UNIQUE ON CONFLICT REPLACE, \
path TEXT UNIQUE ON CONFLICT REPLACE, \
words_count INTEGER, \
moddate REAL, \
is_directory INTEGER, \
timestamp REAL); \
\
CREATE INDEX updated_paths_index ON updated_paths(timestamp); \
\
CREATE TABLE removed_paths \
(path TEXT UNIQUE ON CONFLICT REPLACE); \
\
\
CREATE TRIGGER updated_paths_trigger AFTER UPDATE ON paths \
WHEN (checkUpdating() != 0) \
  BEGIN \
    INSERT INTO updated_paths (id, path, words_count, moddate, is_directory, timestamp) \
    VALUES (new.id, new.path, new.words_count, new.moddate, new.is_directory, timeStamp()); \
  END; \
\
CREATE TRIGGER deleted_paths_trigger AFTER DELETE ON paths \
WHEN (checkUpdating() != 0) \
  BEGIN \
    DELETE FROM updated_paths WHERE id = old.id; \
    INSERT INTO removed_paths (path) \
    VALUES (old.path); \
  END; \
";

static NSString *db_schema_tmp = @"\
CREATE TEMP TABLE removed_id \
(id INTEGER PRIMARY KEY); \
\
\
CREATE TEMP TABLE renamed_paths \
(id INTEGER PRIMARY KEY, \
path TEXT, \
base TEXT, \
oldbase TEXT); \
\
CREATE TEMP TABLE renamed_paths_base \
(base TEXT, \
oldbase TEXT); \
\
CREATE TEMP TRIGGER renamed_paths_trigger AFTER INSERT ON renamed_paths \
BEGIN \
  INSERT INTO removed_paths (path) VALUES (new.path); \
  UPDATE paths \
  SET path = pathMoved(new.oldbase, new.base, new.path) \
  WHERE id = new.id; \
END; \
";




/* for ddbd when/if it will be sqlite-based */
static NSString *user_db_schema = @"\
\
CREATE TABLE user_paths \
(id INTEGER PRIMARY KEY AUTOINCREMENT, \
path TEXT UNIQUE ON CONFLICT IGNORE, \
moddate REAL, \
md_moddate REAL, \
is_directory INTEGER); \
\
CREATE INDEX user_paths_directory_index ON user_paths(path, is_directory); \
\
CREATE TABLE user_attributes \
(path_id INTEGER, \
key TEXT, \
attribute BLOB); \
\
CREATE INDEX attributes_path_index ON user_attributes(path_id, key); \
CREATE INDEX attributes_key_index ON user_attributes(key); \
\
CREATE TRIGGER user_attributes_trigger BEFORE INSERT ON user_attributes \
BEGIN \
  DELETE FROM user_attributes \
  WHERE path_id = new.path_id \
  AND key = new.key; \
END; \
";

static NSString *user_db_schema_tmp = @"\
CREATE TEMP TABLE user_paths_removed_id \
(id INTEGER PRIMARY KEY); \
\
\
CREATE TEMP TABLE user_renamed_paths \
(id INTEGER PRIMARY KEY, \
path TEXT, \
base TEXT, \
oldbase TEXT); \
\
CREATE TEMP TABLE user_renamed_paths_base \
(base TEXT, \
oldbase TEXT); \
\
CREATE TEMP TRIGGER user_renamed_paths_trigger AFTER INSERT ON user_renamed_paths \
BEGIN \
  UPDATE user_paths \
  SET path = pathMoved(new.oldbase, new.base, new.path) \
  WHERE id = new.id; \
END; \
\
\
CREATE TEMP TABLE user_copied_paths \
(src_id PRIMARY KEY, \
srcpath TEXT, \
dstpath TEXT, \
srcbase TEXT, \
dstbase TEXT, \
moddate REAL, \
md_moddate REAL, \
is_directory INTEGER); \
\
CREATE TEMP TABLE user_copied_paths_base \
(srcbase TEXT, \
dstbase TEXT); \
\
CREATE TEMP TRIGGER user_copied_paths_trigger_1 AFTER INSERT ON user_copied_paths \
BEGIN \
  UPDATE user_copied_paths \
  SET \
    dstpath = pathMoved(new.srcbase, new.dstbase, new.srcpath), \
    moddate = timeStamp(), \
    md_moddate = timeStamp() \
  WHERE src_id = new.src_id; \
END; \
\
CREATE TEMP TRIGGER user_copied_paths_trigger_2 AFTER UPDATE ON user_copied_paths \
BEGIN \
  INSERT INTO user_paths (path, moddate, md_moddate, is_directory) \
    VALUES (new.dstpath, new.moddate, new.md_moddate, new.is_directory); \
\
  INSERT INTO user_attributes (path_id, key, attribute) \
    SELECT \
      upaths.id, \
      user_attributes.key, \
      user_attributes.attribute \
    FROM \
      (SELECT id FROM user_paths WHERE path = new.dstpath) AS upaths, \
      user_attributes \
    WHERE \
      user_attributes.path_id = new.src_id; \
END; \
";