forked from abraunegg/onedrive
-
Notifications
You must be signed in to change notification settings - Fork 0
/
itemdb.d
477 lines (445 loc) · 13.3 KB
/
itemdb.d
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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
import std.datetime;
import std.exception;
import std.path;
import std.string;
import std.stdio;
import std.algorithm.searching;
import core.stdc.stdlib;
import sqlite;
static import log;
enum ItemType {
file,
dir,
remote
}
struct Item {
string driveId;
string id;
string name;
ItemType type;
string eTag;
string cTag;
SysTime mtime;
string parentId;
string crc32Hash;
string sha1Hash;
string quickXorHash;
string remoteDriveId;
string remoteId;
string syncStatus;
}
final class ItemDatabase
{
// increment this for every change in the db schema
immutable int itemDatabaseVersion = 10;
Database db;
string insertItemStmt;
string updateItemStmt;
string selectItemByIdStmt;
string selectItemByParentIdStmt;
string deleteItemByIdStmt;
this(const(char)[] filename)
{
db = Database(filename);
int dbVersion;
try {
dbVersion = db.getVersion();
} catch (SqliteException e) {
// An error was generated - what was the error?
log.error("\nAn internal database error occurred: " ~ e.msg ~ "\n");
exit(-1);
}
if (dbVersion == 0) {
createTable();
} else if (db.getVersion() != itemDatabaseVersion) {
log.log("The item database is incompatible, re-creating database table structures");
db.exec("DROP TABLE item");
createTable();
}
// Set the enforcement of foreign key constraints.
// https://www.sqlite.org/pragma.html#pragma_foreign_keys
// PRAGMA foreign_keys = boolean;
db.exec("PRAGMA foreign_keys = TRUE");
// Set the recursive trigger capability
// https://www.sqlite.org/pragma.html#pragma_recursive_triggers
// PRAGMA recursive_triggers = boolean;
db.exec("PRAGMA recursive_triggers = TRUE");
// Set the journal mode for databases associated with the current connection
// https://www.sqlite.org/pragma.html#pragma_journal_mode
db.exec("PRAGMA journal_mode = WAL");
// Automatic indexing is enabled by default as of version 3.7.17
// https://www.sqlite.org/pragma.html#pragma_automatic_index
// PRAGMA automatic_index = boolean;
db.exec("PRAGMA automatic_index = FALSE");
// Tell SQLite to store temporary tables in memory. This will speed up many read operations that rely on temporary tables, indices, and views.
// https://www.sqlite.org/pragma.html#pragma_temp_store
db.exec("PRAGMA temp_store = MEMORY");
// Tell SQlite to cleanup database table size
// https://www.sqlite.org/pragma.html#pragma_auto_vacuum
// PRAGMA schema.auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;
db.exec("PRAGMA auto_vacuum = FULL");
insertItemStmt = "
INSERT OR REPLACE INTO item (driveId, id, name, type, eTag, cTag, mtime, parentId, crc32Hash, sha1Hash, quickXorHash, remoteDriveId, remoteId, syncStatus)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14)
";
updateItemStmt = "
UPDATE item
SET name = ?3, type = ?4, eTag = ?5, cTag = ?6, mtime = ?7, parentId = ?8, crc32Hash = ?9, sha1Hash = ?10, quickXorHash = ?11, remoteDriveId = ?12, remoteId = ?13, syncStatus = ?14
WHERE driveId = ?1 AND id = ?2
";
selectItemByIdStmt = "
SELECT *
FROM item
WHERE driveId = ?1 AND id = ?2
";
selectItemByParentIdStmt = "SELECT * FROM item WHERE driveId = ? AND parentId = ?";
deleteItemByIdStmt = "DELETE FROM item WHERE driveId = ? AND id = ?";
}
void createTable()
{
db.exec("CREATE TABLE item (
driveId TEXT NOT NULL,
id TEXT NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL,
eTag TEXT,
cTag TEXT,
mtime TEXT NOT NULL,
parentId TEXT,
crc32Hash TEXT,
sha1Hash TEXT,
quickXorHash TEXT,
remoteDriveId TEXT,
remoteId TEXT,
deltaLink TEXT,
syncStatus TEXT,
PRIMARY KEY (driveId, id),
FOREIGN KEY (driveId, parentId)
REFERENCES item (driveId, id)
ON DELETE CASCADE
ON UPDATE RESTRICT
)");
db.exec("CREATE INDEX name_idx ON item (name)");
db.exec("CREATE INDEX remote_idx ON item (remoteDriveId, remoteId)");
db.exec("CREATE INDEX item_children_idx ON item (driveId, parentId)");
db.exec("CREATE INDEX selectByPath_idx ON item (name, driveId, parentId)");
db.setVersion(itemDatabaseVersion);
}
void insert(const ref Item item)
{
auto p = db.prepare(insertItemStmt);
bindItem(item, p);
p.exec();
}
void update(const ref Item item)
{
auto p = db.prepare(updateItemStmt);
bindItem(item, p);
p.exec();
}
void dump_open_statements()
{
db.dump_open_statements();
}
int db_checkpoint()
{
return db.db_checkpoint();
}
void upsert(const ref Item item)
{
auto s = db.prepare("SELECT COUNT(*) FROM item WHERE driveId = ? AND id = ?");
s.bind(1, item.driveId);
s.bind(2, item.id);
auto r = s.exec();
Statement stmt;
if (r.front[0] == "0") stmt = db.prepare(insertItemStmt);
else stmt = db.prepare(updateItemStmt);
bindItem(item, stmt);
stmt.exec();
}
Item[] selectChildren(const(char)[] driveId, const(char)[] id)
{
auto p = db.prepare(selectItemByParentIdStmt);
p.bind(1, driveId);
p.bind(2, id);
auto res = p.exec();
Item[] items;
while (!res.empty) {
items ~= buildItem(res);
res.step();
}
return items;
}
bool selectById(const(char)[] driveId, const(char)[] id, out Item item)
{
auto p = db.prepare(selectItemByIdStmt);
p.bind(1, driveId);
p.bind(2, id);
auto r = p.exec();
if (!r.empty) {
item = buildItem(r);
return true;
}
return false;
}
// returns true if an item id is in the database
bool idInLocalDatabase(const(string) driveId, const(string)id)
{
auto p = db.prepare(selectItemByIdStmt);
p.bind(1, driveId);
p.bind(2, id);
auto r = p.exec();
if (!r.empty) {
return true;
}
return false;
}
// returns the item with the given path
// the path is relative to the sync directory ex: "./Music/Turbo Killer.mp3"
bool selectByPath(const(char)[] path, string rootDriveId, out Item item)
{
Item currItem = { driveId: rootDriveId };
// Issue https://github.com/abraunegg/onedrive/issues/578
if (startsWith(path, "./") || path == ".") {
// Need to remove the . from the path prefix
path = "root/" ~ path.chompPrefix(".");
} else {
// Leave path as it is
path = "root/" ~ path;
}
auto s = db.prepare("SELECT * FROM item WHERE name = ?1 AND driveId IS ?2 AND parentId IS ?3");
foreach (name; pathSplitter(path)) {
s.bind(1, name);
s.bind(2, currItem.driveId);
s.bind(3, currItem.id);
auto r = s.exec();
if (r.empty) return false;
currItem = buildItem(r);
// if the item is of type remote substitute it with the child
if (currItem.type == ItemType.remote) {
Item child;
if (selectById(currItem.remoteDriveId, currItem.remoteId, child)) {
assert(child.type != ItemType.remote, "The type of the child cannot be remote");
currItem = child;
}
}
}
item = currItem;
return true;
}
// same as selectByPath() but it does not traverse remote folders
bool selectByPathWithoutRemote(const(char)[] path, string rootDriveId, out Item item)
{
Item currItem = { driveId: rootDriveId };
// Issue https://github.com/abraunegg/onedrive/issues/578
if (startsWith(path, "./") || path == ".") {
// Need to remove the . from the path prefix
path = "root/" ~ path.chompPrefix(".");
} else {
// Leave path as it is
path = "root/" ~ path;
}
auto s = db.prepare("SELECT * FROM item WHERE name IS ?1 AND driveId IS ?2 AND parentId IS ?3");
foreach (name; pathSplitter(path)) {
s.bind(1, name);
s.bind(2, currItem.driveId);
s.bind(3, currItem.id);
auto r = s.exec();
if (r.empty) return false;
currItem = buildItem(r);
}
item = currItem;
return true;
}
void deleteById(const(char)[] driveId, const(char)[] id)
{
auto p = db.prepare(deleteItemByIdStmt);
p.bind(1, driveId);
p.bind(2, id);
p.exec();
}
private void bindItem(const ref Item item, ref Statement stmt)
{
with (stmt) with (item) {
bind(1, driveId);
bind(2, id);
bind(3, name);
string typeStr = null;
final switch (type) with (ItemType) {
case file: typeStr = "file"; break;
case dir: typeStr = "dir"; break;
case remote: typeStr = "remote"; break;
}
bind(4, typeStr);
bind(5, eTag);
bind(6, cTag);
bind(7, mtime.toISOExtString());
bind(8, parentId);
bind(9, crc32Hash);
bind(10, sha1Hash);
bind(11, quickXorHash);
bind(12, remoteDriveId);
bind(13, remoteId);
bind(14, syncStatus);
}
}
private Item buildItem(Statement.Result result)
{
assert(!result.empty, "The result must not be empty");
assert(result.front.length == 15, "The result must have 15 columns");
Item item = {
driveId: result.front[0].dup,
id: result.front[1].dup,
name: result.front[2].dup,
eTag: result.front[4].dup,
cTag: result.front[5].dup,
mtime: SysTime.fromISOExtString(result.front[6]),
parentId: result.front[7].dup,
crc32Hash: result.front[8].dup,
sha1Hash: result.front[9].dup,
quickXorHash: result.front[10].dup,
remoteDriveId: result.front[11].dup,
remoteId: result.front[12].dup,
syncStatus: result.front[14].dup
};
switch (result.front[3]) {
case "file": item.type = ItemType.file; break;
case "dir": item.type = ItemType.dir; break;
case "remote": item.type = ItemType.remote; break;
default: assert(0, "Invalid item type");
}
return item;
}
// computes the path of the given item id
// the path is relative to the sync directory ex: "Music/Turbo Killer.mp3"
// the trailing slash is not added even if the item is a directory
string computePath(const(char)[] driveId, const(char)[] id)
{
assert(driveId && id);
string path;
Item item;
auto s = db.prepare("SELECT * FROM item WHERE driveId = ?1 AND id = ?2");
auto s2 = db.prepare("SELECT driveId, id FROM item WHERE remoteDriveId = ?1 AND remoteId = ?2");
while (true) {
s.bind(1, driveId);
s.bind(2, id);
auto r = s.exec();
if (!r.empty) {
item = buildItem(r);
if (item.type == ItemType.remote) {
// substitute the last name with the current
ptrdiff_t idx = indexOf(path, '/');
path = idx >= 0 ? item.name ~ path[idx .. $] : item.name;
} else {
if (path) path = item.name ~ "/" ~ path;
else path = item.name;
}
id = item.parentId;
} else {
if (id == null) {
// check for remoteItem
s2.bind(1, item.driveId);
s2.bind(2, item.id);
auto r2 = s2.exec();
if (r2.empty) {
// root reached
assert(path.length >= 4);
// remove "root/" from path string if it exists
if (path.length >= 5) {
if (canFind(path, "root/")){
path = path[5 .. $];
}
} else {
path = path[4 .. $];
}
// special case of computing the path of the root itself
if (path.length == 0) path = ".";
break;
} else {
// remote folder
driveId = r2.front[0].dup;
id = r2.front[1].dup;
}
} else {
// broken tree
assert(0);
}
}
}
return path;
}
Item[] selectRemoteItems()
{
Item[] items;
auto stmt = db.prepare("SELECT * FROM item WHERE remoteDriveId IS NOT NULL");
auto res = stmt.exec();
while (!res.empty) {
items ~= buildItem(res);
res.step();
}
return items;
}
string getDeltaLink(const(char)[] driveId, const(char)[] id)
{
assert(driveId && id);
auto stmt = db.prepare("SELECT deltaLink FROM item WHERE driveId = ?1 AND id = ?2");
stmt.bind(1, driveId);
stmt.bind(2, id);
auto res = stmt.exec();
if (res.empty) return null;
return res.front[0].dup;
}
void setDeltaLink(const(char)[] driveId, const(char)[] id, const(char)[] deltaLink)
{
assert(driveId && id);
assert(deltaLink);
auto stmt = db.prepare("UPDATE item SET deltaLink = ?3 WHERE driveId = ?1 AND id = ?2");
stmt.bind(1, driveId);
stmt.bind(2, id);
stmt.bind(3, deltaLink);
stmt.exec();
}
// National Cloud Deployments (US and DE) do not support /delta as a query
// We need to track in the database that this item is in sync
// As we query /children to get all children from OneDrive, update anything in the database
// to be flagged as not-in-sync, thus, we can use that flag to determing what was previously
// in-sync, but now deleted on OneDrive
void downgradeSyncStatusFlag(const(char)[] driveId, const(char)[] id)
{
assert(driveId);
auto stmt = db.prepare("UPDATE item SET syncStatus = 'N' WHERE driveId = ?1 AND id = ?2");
stmt.bind(1, driveId);
stmt.bind(2, id);
stmt.exec();
}
// National Cloud Deployments (US and DE) do not support /delta as a query
// Select items that have a out-of-sync flag set
Item[] selectOutOfSyncItems(const(char)[] driveId)
{
assert(driveId);
Item[] items;
auto stmt = db.prepare("SELECT * FROM item WHERE syncStatus = 'N' AND driveId = ?1");
stmt.bind(1, driveId);
auto res = stmt.exec();
while (!res.empty) {
items ~= buildItem(res);
res.step();
}
return items;
}
// OneDrive Business Folders are stored in the database potentially without a root | parentRoot link
// Select items associated with the provided driveId
Item[] selectByDriveId(const(char)[] driveId)
{
assert(driveId);
Item[] items;
auto stmt = db.prepare("SELECT * FROM item WHERE driveId = ?1 AND parentId IS NULL");
stmt.bind(1, driveId);
auto res = stmt.exec();
while (!res.empty) {
items ~= buildItem(res);
res.step();
}
return items;
}
}