-
Notifications
You must be signed in to change notification settings - Fork 122
/
Copy pathschema.sql
176 lines (171 loc) · 8.36 KB
/
schema.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
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
CREATE TABLE IF NOT EXISTS "entity_changes" (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`entityName` TEXT NOT NULL,
`entityId` TEXT NOT NULL,
`hash` TEXT NOT NULL,
`isErased` INT NOT NULL,
`changeId` TEXT NOT NULL,
`componentId` TEXT NOT NULL,
`instanceId` TEXT NOT NULL,
`isSynced` INTEGER NOT NULL,
`utcDateChanged` TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "etapi_tokens"
(
etapiTokenId TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
tokenHash TEXT NOT NULL,
utcDateCreated TEXT NOT NULL,
utcDateModified TEXT NOT NULL,
isDeleted INT NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS "branches" (
`branchId` TEXT NOT NULL,
`noteId` TEXT NOT NULL,
`parentNoteId` TEXT NOT NULL,
`notePosition` INTEGER NOT NULL,
`prefix` TEXT,
`isExpanded` INTEGER NOT NULL DEFAULT 0,
`isDeleted` INTEGER NOT NULL DEFAULT 0,
`deleteId` TEXT DEFAULT NULL,
`utcDateModified` TEXT NOT NULL,
PRIMARY KEY(`branchId`));
CREATE TABLE IF NOT EXISTS "notes" (
`noteId` TEXT NOT NULL,
`title` TEXT NOT NULL DEFAULT "note",
`isProtected` INT NOT NULL DEFAULT 0,
`type` TEXT NOT NULL DEFAULT 'text',
`mime` TEXT NOT NULL DEFAULT 'text/html',
blobId TEXT DEFAULT NULL,
`isDeleted` INT NOT NULL DEFAULT 0,
`deleteId` TEXT DEFAULT NULL,
`dateCreated` TEXT NOT NULL,
`dateModified` TEXT NOT NULL,
`utcDateCreated` TEXT NOT NULL,
`utcDateModified` TEXT NOT NULL,
PRIMARY KEY(`noteId`));
CREATE TABLE IF NOT EXISTS "revisions" (`revisionId` TEXT NOT NULL PRIMARY KEY,
`noteId` TEXT NOT NULL,
type TEXT DEFAULT '' NOT NULL,
mime TEXT DEFAULT '' NOT NULL,
`title` TEXT NOT NULL,
`isProtected` INT NOT NULL DEFAULT 0,
blobId TEXT DEFAULT NULL,
`utcDateLastEdited` TEXT NOT NULL,
`utcDateCreated` TEXT NOT NULL,
`utcDateModified` TEXT NOT NULL,
`dateLastEdited` TEXT NOT NULL,
`dateCreated` TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS "options"
(
name TEXT not null PRIMARY KEY,
value TEXT not null,
isSynced INTEGER default 0 not null,
utcDateModified TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "attributes"
(
attributeId TEXT not null primary key,
noteId TEXT not null,
type TEXT not null,
name TEXT not null,
value TEXT default '' not null,
position INT default 0 not null,
utcDateModified TEXT not null,
isDeleted INT not null,
`deleteId` TEXT DEFAULT NULL,
isInheritable int DEFAULT 0 NULL);
CREATE UNIQUE INDEX `IDX_entityChanges_entityName_entityId` ON "entity_changes" (
`entityName`,
`entityId`
);
CREATE INDEX `IDX_branches_noteId_parentNoteId` ON `branches` (`noteId`,`parentNoteId`);
CREATE INDEX IDX_branches_parentNoteId ON branches (parentNoteId);
CREATE INDEX `IDX_notes_title` ON `notes` (`title`);
CREATE INDEX `IDX_notes_type` ON `notes` (`type`);
CREATE INDEX `IDX_notes_dateCreated` ON `notes` (`dateCreated`);
CREATE INDEX `IDX_notes_dateModified` ON `notes` (`dateModified`);
CREATE INDEX `IDX_notes_utcDateModified` ON `notes` (`utcDateModified`);
CREATE INDEX `IDX_notes_utcDateCreated` ON `notes` (`utcDateCreated`);
CREATE INDEX `IDX_revisions_noteId` ON `revisions` (`noteId`);
CREATE INDEX `IDX_revisions_utcDateCreated` ON `revisions` (`utcDateCreated`);
CREATE INDEX `IDX_revisions_utcDateLastEdited` ON `revisions` (`utcDateLastEdited`);
CREATE INDEX `IDX_revisions_dateCreated` ON `revisions` (`dateCreated`);
CREATE INDEX `IDX_revisions_dateLastEdited` ON `revisions` (`dateLastEdited`);
CREATE INDEX `IDX_entity_changes_changeId` ON `entity_changes` (`changeId`);
CREATE INDEX IDX_attributes_name_value
on attributes (name, value);
CREATE INDEX IDX_attributes_noteId_index
on attributes (noteId);
CREATE INDEX IDX_attributes_value_index
on attributes (value);
CREATE TABLE IF NOT EXISTS "recent_notes"
(
noteId TEXT not null primary key,
notePath TEXT not null,
utcDateCreated TEXT not null
);
CREATE TABLE IF NOT EXISTS "blobs" (
`blobId` TEXT NOT NULL,
`content` TEXT NULL DEFAULT NULL,
`dateModified` TEXT NOT NULL,
`utcDateModified` TEXT NOT NULL,
PRIMARY KEY(`blobId`)
);
CREATE TABLE IF NOT EXISTS "attachments"
(
attachmentId TEXT not null primary key,
ownerId TEXT not null,
role TEXT not null,
mime TEXT not null,
title TEXT not null,
isProtected INT not null DEFAULT 0,
position INT default 0 not null,
blobId TEXT DEFAULT null,
dateModified TEXT NOT NULL,
utcDateModified TEXT not null,
utcDateScheduledForErasureSince TEXT DEFAULT NULL,
isDeleted INT not null,
deleteId TEXT DEFAULT NULL);
CREATE INDEX IDX_attachments_ownerId_role
on attachments (ownerId, role);
CREATE INDEX IDX_notes_blobId on notes (blobId);
CREATE INDEX IDX_revisions_blobId on revisions (blobId);
CREATE INDEX IDX_attachments_blobId on attachments (blobId);
CREATE TABLE IF NOT EXISTS "note_embeddings" (
"embedId" TEXT NOT NULL PRIMARY KEY,
"noteId" TEXT NOT NULL,
"providerId" TEXT NOT NULL,
"modelId" TEXT NOT NULL,
"dimension" INTEGER NOT NULL,
"embedding" BLOB NOT NULL,
"version" INTEGER NOT NULL DEFAULT 1,
"dateCreated" TEXT NOT NULL,
"utcDateCreated" TEXT NOT NULL,
"dateModified" TEXT NOT NULL,
"utcDateModified" TEXT NOT NULL
);
CREATE INDEX "IDX_note_embeddings_noteId" ON "note_embeddings" ("noteId");
CREATE INDEX "IDX_note_embeddings_providerId_modelId" ON "note_embeddings" ("providerId", "modelId");
CREATE TABLE IF NOT EXISTS "embedding_queue" (
"noteId" TEXT NOT NULL PRIMARY KEY,
"operation" TEXT NOT NULL,
"dateQueued" TEXT NOT NULL,
"utcDateQueued" TEXT NOT NULL,
"priority" INTEGER NOT NULL DEFAULT 0,
"attempts" INTEGER NOT NULL DEFAULT 0,
"lastAttempt" TEXT NULL,
"error" TEXT NULL,
"failed" INTEGER NOT NULL DEFAULT 0,
"isProcessing" INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS "embedding_providers" (
"providerId" TEXT NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"isEnabled" INTEGER NOT NULL DEFAULT 0,
"priority" INTEGER NOT NULL DEFAULT 0,
"config" TEXT NOT NULL,
"dateCreated" TEXT NOT NULL,
"utcDateCreated" TEXT NOT NULL,
"dateModified" TEXT NOT NULL,
"utcDateModified" TEXT NOT NULL
);