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
|
/* basic info for a file or service object */
CREATE TABLE Services
(
ID Integer primary key not null,
ServiceTypeID Integer default 0, /* see ServiceTypes table above for ID values. A value of 0 indicates a group resource rather than a service */
Path Text not null COLLATE UTF8, /* non-file objects should use service name here */
Name Text default ' ' COLLATE UTF8, /* name of file or object - the combination path and name must be unique for all objects */
Enabled Integer default 1,
Mime Text default ' ',
Size Integer default 0,
Rank Integer default 5,
ParentID Integer,
KeyMetadata1 Text,
KeyMetadata2 Text,
KeyMetadata3 Text,
KeyMetadata4 Text,
KeyMetadata5 Text,
KeyMetadata6 Text,
KeyMetadata7 Text,
KeyMetadata8 Text,
KeyMetadata9 Text,
KeyMetadata10 Text,
KeyMetadata11 Text,
Icon Text,
CanWrite Integer default 1,
CanExecute Integer default 1,
LanguageId Integer default 0,
IsDirectory Integer default 0,
IsLink Integer default 0,
AuxilaryID Integer default 0, /* link to Volumes table for files, link to MailSummary table for emails*/
IndexTime Integer default 0, /* should equal st_mtime for file if up-to-date */
Accessed Integer default 0, /* last accessed */
Offset Integer default 0, /* last used disk offset for indexable files that always grow (like chat logs) or email offset */
MD5 Text,
unique (Path, Name)
);
CREATE INDEX ServiceIndex1 ON Services (ServiceTypeID);
/* child service relationships for a specific group/struct metadata */
CREATE TABLE ChildServices
(
ParentID Integer not null,
ChildID Integer not null,
MetaDataID Integer not null,
primary key (ParentID, ChildID, MetaDataID)
);
CREATE INDEX ChildServicesIndex1 ON ChildServices (ChildID);
/* utf-8 based literal metadata. */
CREATE TABLE ServiceMetaData
(
ID Integer primary key AUTOINCREMENT not null,
ServiceID Integer not null,
MetaDataID Integer not null,
MetaDataValue Text,
MetaDataDisplay Text
);
CREATE INDEX ServiceMetaDataIndex1 ON ServiceMetaData (ServiceID);
CREATE INDEX ServiceMetaDataIndex2 ON ServiceMetaData (MetaDataID);
/* metadata for all keyword types - keywords are db indexed for fast searching - they are also not processed like other metadata. */
CREATE TABLE ServiceKeywordMetaData
(
ID Integer primary key AUTOINCREMENT not null,
ServiceID Integer not null,
MetaDataID Integer not null,
MetaDataValue Text COLLATE NOCASE
);
CREATE INDEX ServiceKeywordMetaDataIndex1 ON ServiceKeywordMetaData (MetaDataID, MetaDataValue);
CREATE INDEX ServiceKeywordMetaDataIndex2 ON ServiceKeywordMetaData (ServiceID);
/* metadata for all integer/date types */
CREATE TABLE ServiceNumericMetaData
(
ID Integer primary key AUTOINCREMENT not null,
ServiceID Integer not null,
MetaDataID Integer not null,
MetaDataValue Integer not null
);
CREATE INDEX ServiceNumericMetaDataIndex1 ON ServiceNumericMetaData (MetaDataID, MetaDataValue);
CREATE INDEX ServiceNumericMetaDataIndex2 ON ServiceNumericMetaData (ServiceID);
|
