|
1
|
/* |
|
2
|
** Copyright (c) 2007 D. Richard Hipp |
|
3
|
** |
|
4
|
** This program is free software; you can redistribute it and/or |
|
5
|
** modify it under the terms of the Simplified BSD License (also |
|
6
|
** known as the "2-Clause License" or "FreeBSD License".) |
|
7
|
** |
|
8
|
** This program is distributed in the hope that it will be useful, |
|
9
|
** but without any warranty; without even the implied warranty of |
|
10
|
** merchantability or fitness for a particular purpose. |
|
11
|
** |
|
12
|
** Author contact information: |
|
13
|
** [email protected] |
|
14
|
** http://www.hwaci.com/drh/ |
|
15
|
** |
|
16
|
******************************************************************************* |
|
17
|
** |
|
18
|
** This file contains string constants that implement the database schema. |
|
19
|
*/ |
|
20
|
#include "config.h" |
|
21
|
#include "schema.h" |
|
22
|
|
|
23
|
/* |
|
24
|
** The database schema for the ~/.fossil configuration database. |
|
25
|
*/ |
|
26
|
const char zConfigSchema[] = |
|
27
|
@ -- This file contains the schema for the database that is kept in the |
|
28
|
@ -- ~/.fossil file and that stores information about the users setup. |
|
29
|
@ -- |
|
30
|
@ CREATE TABLE global_config( |
|
31
|
@ name TEXT PRIMARY KEY, |
|
32
|
@ value TEXT |
|
33
|
@ ) WITHOUT ROWID; |
|
34
|
@ |
|
35
|
@ -- Identifier for this file type. |
|
36
|
@ -- The integer is the same as 'FSLG'. |
|
37
|
@ PRAGMA application_id=252006675; |
|
38
|
; |
|
39
|
|
|
40
|
#if INTERFACE |
|
41
|
/* |
|
42
|
** The content tables have a content version number which rarely |
|
43
|
** changes. The aux tables have an arbitrary version number (typically |
|
44
|
** a date) which can change frequently. When the content schema changes, |
|
45
|
** we have to execute special procedures to update the schema. When |
|
46
|
** the aux schema changes, all we need to do is rebuild the database. |
|
47
|
*/ |
|
48
|
#define CONTENT_SCHEMA "2" |
|
49
|
#define AUX_SCHEMA_MIN "2011-04-25 19:50" |
|
50
|
#define AUX_SCHEMA_MAX "2015-01-24" |
|
51
|
/* NB: Some features require the latest schema. Warning or error messages |
|
52
|
** will appear if an older schema is used. However, the older schemas are |
|
53
|
** adequate for many common functions. */ |
|
54
|
|
|
55
|
#endif /* INTERFACE */ |
|
56
|
|
|
57
|
|
|
58
|
/* |
|
59
|
** The schema for a repository database. |
|
60
|
** |
|
61
|
** Schema1[] contains parts of the schema that are fixed and unchanging |
|
62
|
** across versions. Schema2[] contains parts of the schema that can |
|
63
|
** change from one version to the next. The information in Schema2[] |
|
64
|
** is reconstructed from the information in Schema1[] by the "rebuild" |
|
65
|
** operation. |
|
66
|
*/ |
|
67
|
const char zRepositorySchema1[] = |
|
68
|
@ -- The BLOB and DELTA tables contain all records held in the repository. |
|
69
|
@ -- |
|
70
|
@ -- The BLOB.CONTENT column is always compressed using zlib. This |
|
71
|
@ -- column might hold the full text of the record or it might hold |
|
72
|
@ -- a delta that is able to reconstruct the record from some other |
|
73
|
@ -- record. If BLOB.CONTENT holds a delta, then a DELTA table entry |
|
74
|
@ -- will exist for the record and that entry will point to another |
|
75
|
@ -- entry that holds the source of the delta. Deltas can be chained. |
|
76
|
@ -- |
|
77
|
@ -- The blob and delta tables collectively hold the "global state" of |
|
78
|
@ -- a Fossil repository. |
|
79
|
@ -- |
|
80
|
@ CREATE TABLE blob( |
|
81
|
@ rid INTEGER PRIMARY KEY, -- Record ID |
|
82
|
@ rcvid INTEGER, -- Origin of this record |
|
83
|
@ size INTEGER, -- Size of content. -1 for a phantom. |
|
84
|
@ uuid TEXT UNIQUE NOT NULL, -- hash of the content |
|
85
|
@ content BLOB, -- Compressed content of this record |
|
86
|
@ CHECK( length(uuid)>=40 AND rid>0 ) |
|
87
|
@ ); |
|
88
|
@ CREATE TABLE delta( |
|
89
|
@ rid INTEGER PRIMARY KEY, -- BLOB that is delta-compressed |
|
90
|
@ srcid INTEGER NOT NULL REFERENCES blob -- Baseline for delta-compression |
|
91
|
@ ); |
|
92
|
@ CREATE INDEX delta_i1 ON delta(srcid); |
|
93
|
@ |
|
94
|
@ ------------------------------------------------------------------------- |
|
95
|
@ -- The BLOB and DELTA tables above hold the "global state" of a Fossil |
|
96
|
@ -- project; the stuff that is normally exchanged during "sync". The |
|
97
|
@ -- "local state" of a repository is contained in the remaining tables of |
|
98
|
@ -- the zRepositorySchema1 string. |
|
99
|
@ ------------------------------------------------------------------------- |
|
100
|
@ |
|
101
|
@ -- Whenever new blobs are received into the repository, an entry |
|
102
|
@ -- in this table records the source of the blob. |
|
103
|
@ -- |
|
104
|
@ CREATE TABLE rcvfrom( |
|
105
|
@ rcvid INTEGER PRIMARY KEY, -- Received-From ID |
|
106
|
@ uid INTEGER REFERENCES user, -- User login |
|
107
|
@ mtime DATETIME, -- Time of receipt. Julian day. |
|
108
|
@ nonce TEXT UNIQUE, -- Nonce used for login |
|
109
|
@ ipaddr TEXT -- Remote IP address. NULL for direct. |
|
110
|
@ ); |
|
111
|
@ |
|
112
|
@ -- Information about users |
|
113
|
@ -- |
|
114
|
@ -- The user.pw field can be either cleartext of the password, or |
|
115
|
@ -- a SHA1 hash of the password. If the user.pw field is exactly 40 |
|
116
|
@ -- characters long we assume it is a SHA1 hash. Otherwise, it is |
|
117
|
@ -- cleartext. The sha1_shared_secret() routine computes the password |
|
118
|
@ -- hash based on the project-code, the user login, and the cleartext |
|
119
|
@ -- password. |
|
120
|
@ -- |
|
121
|
@ CREATE TABLE user( |
|
122
|
@ uid INTEGER PRIMARY KEY, -- User ID |
|
123
|
@ login TEXT UNIQUE, -- login name of the user |
|
124
|
@ pw TEXT, -- password |
|
125
|
@ cap TEXT, -- Capabilities of this user |
|
126
|
@ cookie TEXT, -- WWW login cookie |
|
127
|
@ ipaddr TEXT, -- IP address for which cookie is valid |
|
128
|
@ cexpire DATETIME, -- Time when cookie expires |
|
129
|
@ info TEXT, -- contact information |
|
130
|
@ mtime DATE, -- last change. seconds since 1970 |
|
131
|
@ photo BLOB, -- JPEG image of this user |
|
132
|
@ jx TEXT DEFAULT '{}' -- Extra fields in JSON |
|
133
|
@ ); |
|
134
|
@ |
|
135
|
@ -- The config table holds miscellanous information about the repository. |
|
136
|
@ -- in the form of name-value pairs. |
|
137
|
@ -- |
|
138
|
@ CREATE TABLE config( |
|
139
|
@ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry |
|
140
|
@ value CLOB, -- Content of the named parameter |
|
141
|
@ mtime DATE, -- last modified. seconds since 1970 |
|
142
|
@ CHECK( typeof(name)='text' AND length(name)>=1 ) |
|
143
|
@ ) WITHOUT ROWID; |
|
144
|
@ |
|
145
|
@ -- Artifacts that should not be processed are identified in the |
|
146
|
@ -- "shun" table. Artifacts that are control-file forgeries or |
|
147
|
@ -- spam or artifacts whose contents violate administrative policy |
|
148
|
@ -- can be shunned in order to prevent them from contaminating |
|
149
|
@ -- the repository. |
|
150
|
@ -- |
|
151
|
@ -- Shunned artifacts do not exist in the blob table. Hence they |
|
152
|
@ -- have not artifact ID (rid) and we thus must store their full |
|
153
|
@ -- UUID. |
|
154
|
@ -- |
|
155
|
@ CREATE TABLE shun( |
|
156
|
@ uuid TEXT PRIMARY KEY,-- UUID of artifact to be shunned. Canonical form |
|
157
|
@ mtime DATE, -- When added. seconds since 1970 |
|
158
|
@ scom TEXT -- Optional text explaining why the shun occurred |
|
159
|
@ ) WITHOUT ROWID; |
|
160
|
@ |
|
161
|
@ -- Artifacts that should not be pushed are stored in the "private" |
|
162
|
@ -- table. Private artifacts are omitted from the "unclustered" and |
|
163
|
@ -- "unsent" tables. |
|
164
|
@ -- |
|
165
|
@ -- A phantom artifact (that is, an artifact with BLOB.SIZE<0 - an artifact |
|
166
|
@ -- for which we do not know the content) might also be marked as private. |
|
167
|
@ -- This comes about when an artifact is named in a manifest or tag but |
|
168
|
@ -- the content of that artifact is held privately by some other peer |
|
169
|
@ -- repository. |
|
170
|
@ -- |
|
171
|
@ CREATE TABLE private(rid INTEGER PRIMARY KEY); |
|
172
|
@ |
|
173
|
@ -- An entry in this table describes a database query that generates a |
|
174
|
@ -- table of tickets. |
|
175
|
@ -- |
|
176
|
@ CREATE TABLE reportfmt( |
|
177
|
@ rn INTEGER PRIMARY KEY, -- Report number |
|
178
|
@ owner TEXT, -- Owner of this report format (not used) |
|
179
|
@ title TEXT UNIQUE, -- Title of this report |
|
180
|
@ mtime DATE, -- Last modified. seconds since 1970 |
|
181
|
@ cols TEXT, -- A color-key specification |
|
182
|
@ sqlcode TEXT, -- An SQL SELECT statement for this report |
|
183
|
@ jx TEXT DEFAULT '{}' -- Additional fields encoded as JSON |
|
184
|
@ ); |
|
185
|
@ |
|
186
|
@ -- Some ticket content (such as the originators email address or contact |
|
187
|
@ -- information) needs to be obscured to protect privacy. This is achieved |
|
188
|
@ -- by storing an SHA1 hash of the content. For display, the hash is |
|
189
|
@ -- mapped back into the original text using this table. |
|
190
|
@ -- |
|
191
|
@ -- This table contains sensitive information and should not be shared |
|
192
|
@ -- with unauthorized users. |
|
193
|
@ -- |
|
194
|
@ CREATE TABLE concealed( |
|
195
|
@ hash TEXT PRIMARY KEY, -- The SHA1 hash of content |
|
196
|
@ mtime DATE, -- Time created. Seconds since 1970 |
|
197
|
@ content TEXT -- Content intended to be concealed |
|
198
|
@ ) WITHOUT ROWID; |
|
199
|
@ |
|
200
|
@ -- The application ID helps the unix "file" command to identify the |
|
201
|
@ -- database as a fossil repository. |
|
202
|
@ PRAGMA application_id=252006673; |
|
203
|
; |
|
204
|
|
|
205
|
/* |
|
206
|
** The default reportfmt entry for the schema. This is in an extra |
|
207
|
** script so that (configure reset) can install the default report. |
|
208
|
*/ |
|
209
|
const char zRepositorySchemaDefaultReports[] = |
|
210
|
@ INSERT INTO reportfmt(title,mtime,cols,sqlcode) |
|
211
|
@ VALUES('All Tickets',julianday('1970-01-01'),'#ffffff Key: |
|
212
|
@ #f2dcdc Active |
|
213
|
@ #e8e8e8 Review |
|
214
|
@ #cfe8bd Fixed |
|
215
|
@ #bde5d6 Tested |
|
216
|
@ #cacae5 Deferred |
|
217
|
@ #c8c8c8 Closed','SELECT |
|
218
|
@ CASE WHEN status IN (''Open'',''Verified'') THEN ''#f2dcdc'' |
|
219
|
@ WHEN status=''Review'' THEN ''#e8e8e8'' |
|
220
|
@ WHEN status=''Fixed'' THEN ''#cfe8bd'' |
|
221
|
@ WHEN status=''Tested'' THEN ''#bde5d6'' |
|
222
|
@ WHEN status=''Deferred'' THEN ''#cacae5'' |
|
223
|
@ ELSE ''#c8c8c8'' END AS ''bgcolor'', |
|
224
|
@ substr(tkt_uuid,1,10) AS ''#'', |
|
225
|
@ datetime(tkt_mtime) AS ''mtime'', |
|
226
|
@ type, |
|
227
|
@ status, |
|
228
|
@ subsystem, |
|
229
|
@ title |
|
230
|
@ FROM ticket'); |
|
231
|
; |
|
232
|
|
|
233
|
const char zRepositorySchema2[] = |
|
234
|
@ -- Filenames |
|
235
|
@ -- |
|
236
|
@ CREATE TABLE filename( |
|
237
|
@ fnid INTEGER PRIMARY KEY, -- Filename ID |
|
238
|
@ name TEXT UNIQUE -- Name of file page |
|
239
|
@ ); |
|
240
|
@ |
|
241
|
@ -- Linkages between check-ins, files created by each check-in, and |
|
242
|
@ -- the names of those files. |
|
243
|
@ -- |
|
244
|
@ -- Each entry represents a file that changed content from pid to fid |
|
245
|
@ -- due to the check-in that goes from pmid to mid. fnid is the name |
|
246
|
@ -- of the file in the mid check-in. If the file was renamed as part |
|
247
|
@ -- of the mid check-in, then pfnid is the previous filename. |
|
248
|
@ |
|
249
|
@ -- There can be multiple entries for (mid,fid) if the mid check-in was |
|
250
|
@ -- a merge. Entries with isaux==0 are from the primary parent. Merge |
|
251
|
@ -- parents have isaux set to true. |
|
252
|
@ -- |
|
253
|
@ -- Field name mnemonics: |
|
254
|
@ -- mid = Manifest ID. (Each check-in is stored as a "Manifest") |
|
255
|
@ -- fid = File ID. |
|
256
|
@ -- pmid = Parent Manifest ID. |
|
257
|
@ -- pid = Parent file ID. |
|
258
|
@ -- fnid = File Name ID. |
|
259
|
@ -- pfnid = Parent File Name ID. |
|
260
|
@ -- isaux = pmid IS AUXiliary parent, not primary parent |
|
261
|
@ -- |
|
262
|
@ -- pid==0 if the file is added by check-in mid. |
|
263
|
@ -- pid==(-1) if the file exists in a merge parents but not in the primary |
|
264
|
@ -- parent. In other words, if the file was added by merge. |
|
265
|
@ -- fid==0 if the file is removed by check-in mid. |
|
266
|
@ -- |
|
267
|
@ CREATE TABLE mlink( |
|
268
|
@ mid INTEGER, -- Check-in that contains fid |
|
269
|
@ fid INTEGER, -- New file content. 0 if deleted |
|
270
|
@ pmid INTEGER, -- Check-in that contains pid |
|
271
|
@ pid INTEGER, -- Prev file content. 0 if new. -1 merge |
|
272
|
@ fnid INTEGER REFERENCES filename, -- Name of the file |
|
273
|
@ pfnid INTEGER, -- Previous name. 0 if unchanged |
|
274
|
@ mperm INTEGER, -- File permissions. 1==exec |
|
275
|
@ isaux BOOLEAN DEFAULT 0 -- TRUE if pmid is the primary |
|
276
|
@ ); |
|
277
|
@ CREATE INDEX mlink_i1 ON mlink(mid); |
|
278
|
@ CREATE INDEX mlink_i2 ON mlink(fnid); |
|
279
|
@ CREATE INDEX mlink_i3 ON mlink(fid); |
|
280
|
@ CREATE INDEX mlink_i4 ON mlink(pid); |
|
281
|
@ |
|
282
|
@ -- Parent/child linkages between check-ins |
|
283
|
@ -- |
|
284
|
@ CREATE TABLE plink( |
|
285
|
@ pid INTEGER REFERENCES blob, -- Parent manifest |
|
286
|
@ cid INTEGER REFERENCES blob, -- Child manifest |
|
287
|
@ isprim BOOLEAN, -- pid is the primary parent of cid |
|
288
|
@ mtime DATETIME, -- the date/time stamp on cid. Julian day. |
|
289
|
@ baseid INTEGER REFERENCES blob, -- Baseline if cid is a delta manifest. |
|
290
|
@ UNIQUE(pid, cid) |
|
291
|
@ ); |
|
292
|
@ CREATE INDEX plink_i2 ON plink(cid,pid); |
|
293
|
@ |
|
294
|
@ -- A "leaf" check-in is a check-in that has no children in the same |
|
295
|
@ -- branch. The set of all leaves is easily computed with a join, |
|
296
|
@ -- between the plink and tagxref tables, but it is a slower join for |
|
297
|
@ -- very large repositories (repositories with 100,000 or more check-ins) |
|
298
|
@ -- and so it makes sense to precompute the set of leaves. There is |
|
299
|
@ -- one entry in the following table for each leaf. |
|
300
|
@ -- |
|
301
|
@ CREATE TABLE leaf(rid INTEGER PRIMARY KEY); |
|
302
|
@ |
|
303
|
@ -- Events used to generate a timeline. Type meanings: |
|
304
|
@ -- ci Check-ins |
|
305
|
@ -- e Technotes |
|
306
|
@ -- f Forum posts |
|
307
|
@ -- g Tags |
|
308
|
@ -- t Ticket changes |
|
309
|
@ -- w Wiki page edit |
|
310
|
@ -- |
|
311
|
@ CREATE TABLE event( |
|
312
|
@ type TEXT, -- Type of event: ci, e, f, g, t, w |
|
313
|
@ mtime DATETIME, -- Time of occurrence. Julian day. |
|
314
|
@ objid INTEGER PRIMARY KEY, -- Associated record ID |
|
315
|
@ tagid INTEGER, -- Associated ticket or wiki name tag |
|
316
|
@ uid INTEGER REFERENCES user, -- User who caused the event |
|
317
|
@ bgcolor TEXT, -- Color set by 'bgcolor' property |
|
318
|
@ euser TEXT, -- User set by 'user' property |
|
319
|
@ user TEXT, -- Name of the user |
|
320
|
@ ecomment TEXT, -- Comment set by 'comment' property |
|
321
|
@ comment TEXT, -- Comment describing the event |
|
322
|
@ brief TEXT, -- Short comment when tagid already seen |
|
323
|
@ omtime DATETIME -- Original unchanged date+time, or NULL |
|
324
|
@ ); |
|
325
|
@ CREATE INDEX event_i1 ON event(mtime); |
|
326
|
@ |
|
327
|
@ -- A record of phantoms. A phantom is a record for which we know the |
|
328
|
@ -- file hash but we do not (yet) know the file content. |
|
329
|
@ -- |
|
330
|
@ CREATE TABLE phantom( |
|
331
|
@ rid INTEGER PRIMARY KEY -- Record ID of the phantom |
|
332
|
@ ); |
|
333
|
@ |
|
334
|
@ -- A record of orphaned delta-manifests. An orphan is a delta-manifest |
|
335
|
@ -- for which we have content, but its baseline-manifest is a phantom. |
|
336
|
@ -- We have to track all orphan manifests so that when the baseline arrives, |
|
337
|
@ -- we know to process the orphaned deltas. |
|
338
|
@ CREATE TABLE orphan( |
|
339
|
@ rid INTEGER PRIMARY KEY, -- Delta manifest with a phantom baseline |
|
340
|
@ baseline INTEGER -- Phantom baseline of this orphan |
|
341
|
@ ); |
|
342
|
@ CREATE INDEX orphan_baseline ON orphan(baseline); |
|
343
|
@ |
|
344
|
@ -- Unclustered records. An unclustered record is a record (including |
|
345
|
@ -- a cluster records themselves) that is not mentioned by some other |
|
346
|
@ -- cluster. |
|
347
|
@ -- |
|
348
|
@ -- Phantoms are usually included in the unclustered table. A new cluster |
|
349
|
@ -- will never be created that contains a phantom. But another repository |
|
350
|
@ -- might send us a cluster that contains entries that are phantoms to |
|
351
|
@ -- us. |
|
352
|
@ -- |
|
353
|
@ CREATE TABLE unclustered( |
|
354
|
@ rid INTEGER PRIMARY KEY -- Record ID of the unclustered file |
|
355
|
@ ); |
|
356
|
@ |
|
357
|
@ -- Records which have never been pushed to another server. This is |
|
358
|
@ -- used to reduce push operations to a single HTTP request in the |
|
359
|
@ -- common case when one repository only talks to a single server. |
|
360
|
@ -- |
|
361
|
@ CREATE TABLE unsent( |
|
362
|
@ rid INTEGER PRIMARY KEY -- Record ID of the phantom |
|
363
|
@ ); |
|
364
|
@ |
|
365
|
@ -- Each artifact can have one or more tags. A tag |
|
366
|
@ -- is defined by a row in the next table. |
|
367
|
@ -- |
|
368
|
@ -- Wiki pages are tagged with "wiki-NAME" where NAME is the name of |
|
369
|
@ -- the wiki page. Tickets changes are tagged with "ticket-HASH" where |
|
370
|
@ -- HASH is the identifier of the ticket. Tags used to assign symbolic |
|
371
|
@ -- names to baselines are branches are of the form "sym-NAME" where |
|
372
|
@ -- NAME is the symbolic name. |
|
373
|
@ -- |
|
374
|
@ CREATE TABLE tag( |
|
375
|
@ tagid INTEGER PRIMARY KEY, -- Numeric tag ID |
|
376
|
@ tagname TEXT UNIQUE -- Tag name. |
|
377
|
@ ); |
|
378
|
@ INSERT INTO tag VALUES(1, 'bgcolor'); -- TAG_BGCOLOR |
|
379
|
@ INSERT INTO tag VALUES(2, 'comment'); -- TAG_COMMENT |
|
380
|
@ INSERT INTO tag VALUES(3, 'user'); -- TAG_USER |
|
381
|
@ INSERT INTO tag VALUES(4, 'date'); -- TAG_DATE |
|
382
|
@ INSERT INTO tag VALUES(5, 'hidden'); -- TAG_HIDDEN |
|
383
|
@ INSERT INTO tag VALUES(6, 'private'); -- TAG_PRIVATE |
|
384
|
@ INSERT INTO tag VALUES(7, 'cluster'); -- TAG_CLUSTER |
|
385
|
@ INSERT INTO tag VALUES(8, 'branch'); -- TAG_BRANCH |
|
386
|
@ INSERT INTO tag VALUES(9, 'closed'); -- TAG_CLOSED |
|
387
|
@ INSERT INTO tag VALUES(10,'parent'); -- TAG_PARENT |
|
388
|
@ INSERT INTO tag VALUES(11,'note'); -- TAG_NOTE |
|
389
|
@ |
|
390
|
@ -- Assignments of tags to artifacts. Note that we allow tags to |
|
391
|
@ -- have values assigned to them. So we are not really dealing with |
|
392
|
@ -- tags here. These are really properties. But we are going to |
|
393
|
@ -- keep calling them tags because in many cases the value is ignored. |
|
394
|
@ -- |
|
395
|
@ CREATE TABLE tagxref( |
|
396
|
@ tagid INTEGER REFERENCES tag, -- The tag being added, removed, |
|
397
|
@ -- or propagated |
|
398
|
@ tagtype INTEGER, -- 0:-,cancel 1:+,single 2:*,propagate |
|
399
|
@ srcid INTEGER REFERENCES blob, -- Artifact tag originates from, or |
|
400
|
@ -- 0 for propagated tags |
|
401
|
@ origid INTEGER REFERENCES blob, -- Artifact holding propagated tag |
|
402
|
@ -- (any artifact type with a P-card) |
|
403
|
@ value TEXT, -- Value of the tag. Might be NULL. |
|
404
|
@ mtime TIMESTAMP, -- Time of addition or removal. Julian day |
|
405
|
@ rid INTEGER REFERENCE blob, -- Artifact tag is applied to |
|
406
|
@ UNIQUE(rid, tagid) |
|
407
|
@ ); |
|
408
|
@ CREATE INDEX tagxref_i1 ON tagxref(tagid, mtime); |
|
409
|
@ |
|
410
|
@ -- When a hyperlink occurs from one artifact to another (for example |
|
411
|
@ -- when a check-in comment refers to a ticket) an entry is made in |
|
412
|
@ -- the following table for that hyperlink. This table is used to |
|
413
|
@ -- facilitate the display of "back links". |
|
414
|
@ -- |
|
415
|
@ CREATE TABLE backlink( |
|
416
|
@ target TEXT, -- Where the hyperlink points to |
|
417
|
@ srctype INT, -- 0=comment 1=ticket 2=wiki. See BKLNK_* below. |
|
418
|
@ srcid INT, -- EVENT.OBJID for the source document |
|
419
|
@ mtime TIMESTAMP, -- time that the hyperlink was added. Julian day. |
|
420
|
@ UNIQUE(target, srctype, srcid) |
|
421
|
@ ); |
|
422
|
@ CREATE INDEX backlink_src ON backlink(srcid, srctype); |
|
423
|
@ |
|
424
|
@ -- Each attachment is an entry in the following table. Only |
|
425
|
@ -- the most recent attachment (identified by the D card) is saved. |
|
426
|
@ -- |
|
427
|
@ CREATE TABLE attachment( |
|
428
|
@ attachid INTEGER PRIMARY KEY, -- Local id for this attachment |
|
429
|
@ isLatest BOOLEAN DEFAULT 0, -- True if this is the one to use |
|
430
|
@ mtime TIMESTAMP, -- Last changed. Julian day. |
|
431
|
@ src TEXT, -- Hash of the attachment. NULL to delete |
|
432
|
@ target TEXT, -- Object attached to. Wikiname or Tkt hash |
|
433
|
@ filename TEXT, -- Filename for the attachment |
|
434
|
@ comment TEXT, -- Comment associated with this attachment |
|
435
|
@ user TEXT -- Name of user adding attachment |
|
436
|
@ ); |
|
437
|
@ CREATE INDEX attachment_idx1 ON attachment(target, filename, mtime); |
|
438
|
@ CREATE INDEX attachment_idx2 ON attachment(src); |
|
439
|
@ |
|
440
|
@ -- Template for the TICKET table |
|
441
|
@ -- |
|
442
|
@ -- NB: when changing the schema of the TICKET table here, also make the |
|
443
|
@ -- same change in tktsetup.c. |
|
444
|
@ -- |
|
445
|
@ CREATE TABLE ticket( |
|
446
|
@ -- Do not change any column that begins with tkt_ |
|
447
|
@ tkt_id INTEGER PRIMARY KEY, |
|
448
|
@ tkt_uuid TEXT UNIQUE, |
|
449
|
@ tkt_mtime DATE, |
|
450
|
@ tkt_ctime DATE, |
|
451
|
@ -- Add as many field as required below this line |
|
452
|
@ type TEXT, |
|
453
|
@ status TEXT, |
|
454
|
@ subsystem TEXT, |
|
455
|
@ priority TEXT, |
|
456
|
@ severity TEXT, |
|
457
|
@ foundin TEXT, |
|
458
|
@ private_contact TEXT, |
|
459
|
@ resolution TEXT, |
|
460
|
@ title TEXT, |
|
461
|
@ comment TEXT |
|
462
|
@ ); |
|
463
|
@ CREATE TABLE ticketchng( |
|
464
|
@ -- Do not change any column that begins with tkt_ |
|
465
|
@ tkt_id INTEGER REFERENCES ticket, |
|
466
|
@ tkt_rid INTEGER REFERENCES blob, |
|
467
|
@ tkt_mtime DATE, |
|
468
|
@ tkt_user TEXT, |
|
469
|
@ -- Add as many fields as required below this line |
|
470
|
@ login TEXT, |
|
471
|
@ username TEXT, |
|
472
|
@ mimetype TEXT, |
|
473
|
@ icomment TEXT |
|
474
|
@ ); |
|
475
|
@ CREATE INDEX ticketchng_idx1 ON ticketchng(tkt_id, tkt_mtime); |
|
476
|
@ |
|
477
|
@ -- For tracking cherrypick merges |
|
478
|
@ CREATE TABLE cherrypick( |
|
479
|
@ parentid INT, |
|
480
|
@ childid INT, |
|
481
|
@ isExclude BOOLEAN DEFAULT false, |
|
482
|
@ PRIMARY KEY(parentid, childid) |
|
483
|
@ ) WITHOUT ROWID; |
|
484
|
@ CREATE INDEX cherrypick_cid ON cherrypick(childid); |
|
485
|
; |
|
486
|
|
|
487
|
/* |
|
488
|
** Allowed values for backlink.srctype |
|
489
|
*/ |
|
490
|
#if INTERFACE |
|
491
|
# define BKLNK_COMMENT 0 /* Check-in comment */ |
|
492
|
# define BKLNK_TICKET 1 /* Ticket body or title */ |
|
493
|
# define BKLNK_WIKI 2 /* Wiki */ |
|
494
|
# define BKLNK_EVENT 3 /* Technote */ |
|
495
|
# define BKLNK_FORUM 4 /* Forum post */ |
|
496
|
# define ValidBklnk(X) (X>=0 && X<=4) /* True if backlink.srctype is valid */ |
|
497
|
#endif |
|
498
|
|
|
499
|
/* |
|
500
|
** Allowed values for MIMEtype codes |
|
501
|
*/ |
|
502
|
#if INTERFACE |
|
503
|
# define MT_NONE 0 /* unspecified */ |
|
504
|
# define MT_WIKI 1 /* Wiki */ |
|
505
|
# define MT_MARKDOWN 2 /* Markdown */ |
|
506
|
# define MT_UNKNOWN 3 /* unknown */ |
|
507
|
# define ValidMTC(X) ((X)>=0 && (X)<=3) /* True if MIMEtype code is valid */ |
|
508
|
#endif |
|
509
|
|
|
510
|
/* |
|
511
|
** Predefined tagid values |
|
512
|
*/ |
|
513
|
#if INTERFACE |
|
514
|
# define TAG_BGCOLOR 1 /* Set the background color for display */ |
|
515
|
# define TAG_COMMENT 2 /* The check-in comment */ |
|
516
|
# define TAG_USER 3 /* User who made a check-in */ |
|
517
|
# define TAG_DATE 4 /* The date of a check-in */ |
|
518
|
# define TAG_HIDDEN 5 /* Do not display in timeline */ |
|
519
|
# define TAG_PRIVATE 6 /* Do not sync */ |
|
520
|
# define TAG_CLUSTER 7 /* A cluster */ |
|
521
|
# define TAG_BRANCH 8 /* Value is name of the current branch */ |
|
522
|
# define TAG_CLOSED 9 /* Do not display this check-in as a leaf */ |
|
523
|
# define TAG_PARENT 10 /* Change to parentage on a check-in */ |
|
524
|
# define TAG_NOTE 11 /* Extra text appended to a check-in comment */ |
|
525
|
#endif |
|
526
|
|
|
527
|
/* |
|
528
|
** The schema for the local FOSSIL database file found at the root |
|
529
|
** of every check-out. This database contains the complete state of |
|
530
|
** the check-out. See also the addendum in zLocalSchemaVmerge[]. |
|
531
|
*/ |
|
532
|
const char zLocalSchema[] = |
|
533
|
@ -- The VVAR table holds miscellanous information about the local checkout |
|
534
|
@ -- in the form of name-value pairs. This is similar to the VAR table |
|
535
|
@ -- table in the repository except that this table holds information that |
|
536
|
@ -- is specific to the local check-out. |
|
537
|
@ -- |
|
538
|
@ -- Important Variables: |
|
539
|
@ -- |
|
540
|
@ -- repository Full pathname of the repository database |
|
541
|
@ -- user-id Userid to use |
|
542
|
@ -- |
|
543
|
@ CREATE TABLE vvar( |
|
544
|
@ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry |
|
545
|
@ value CLOB, -- Content of the named parameter |
|
546
|
@ CHECK( typeof(name)='text' AND length(name)>=1 ) |
|
547
|
@ ) WITHOUT ROWID; |
|
548
|
@ |
|
549
|
@ -- Each entry in the vfile table represents a single file in the |
|
550
|
@ -- current check-out. |
|
551
|
@ -- |
|
552
|
@ -- The file.rid field is 0 for files or folders that have been |
|
553
|
@ -- added but not yet committed. |
|
554
|
@ -- |
|
555
|
@ -- Vfile.chnged meaning: |
|
556
|
@ -- 0 File is unmodified |
|
557
|
@ -- 1 Manually edited and/or modified as part of a merge command |
|
558
|
@ -- 2 Replaced by a merge command |
|
559
|
@ -- 3 Added by a merge command |
|
560
|
@ -- 4,5 Same as 2,3 except merge using --integrate |
|
561
|
@ -- |
|
562
|
@ CREATE TABLE vfile( |
|
563
|
@ id INTEGER PRIMARY KEY, -- ID of the checked-out file |
|
564
|
@ vid INTEGER REFERENCES blob, -- The check-in this file is part of. |
|
565
|
@ chnged INT DEFAULT 0, |
|
566
|
@ -- 0:unchng 1:edit 2:m-chng 3:m-add 4:i-chng |
|
567
|
@ -- 5:i-add 6:+exec 7:+symlink 8:-exec 9:unlink |
|
568
|
@ deleted BOOLEAN DEFAULT 0, -- True if deleted |
|
569
|
@ isexe BOOLEAN, -- True if file should be executable |
|
570
|
@ islink BOOLEAN, -- True if file should be symlink |
|
571
|
@ rid INTEGER, -- Originally from this repository record |
|
572
|
@ mrid INTEGER, -- Based on this record due to a merge |
|
573
|
@ mtime INTEGER, -- Mtime of file on disk. sec since 1970 |
|
574
|
@ pathname TEXT, -- Full pathname relative to root |
|
575
|
@ origname TEXT, -- Original pathname. NULL if unchanged |
|
576
|
@ mhash TEXT, -- Hash of mrid iff mrid!=rid |
|
577
|
@ UNIQUE(pathname,vid) |
|
578
|
@ ); |
|
579
|
@ |
|
580
|
@ -- Identifier for this file type. |
|
581
|
@ -- The integer is the same as 'FSLC'. |
|
582
|
@ PRAGMA application_id=252006674; |
|
583
|
; |
|
584
|
|
|
585
|
/* Additional local database initialization following the schema |
|
586
|
** enhancement of 2019-01-19, in which the mhash column was added |
|
587
|
** to vmerge and vfile. |
|
588
|
*/ |
|
589
|
const char zLocalSchemaVmerge[] = |
|
590
|
@ -- This table holds a record of uncommitted merges in the local |
|
591
|
@ -- file tree. If a VFILE entry with id has merged with another |
|
592
|
@ -- record, there is an entry in this table with (id,merge) where |
|
593
|
@ -- merge is the RECORD table entry that the file merged against. |
|
594
|
@ -- An id of 0 or <-3 here means the version record itself. When |
|
595
|
@ -- id==(-1) that is a cherrypick merge, id==(-2) that is a |
|
596
|
@ -- backout merge and id==(-4) is an integrate merge. |
|
597
|
@ -- |
|
598
|
@ |
|
599
|
@ CREATE TABLE vmerge( |
|
600
|
@ id INTEGER REFERENCES vfile, -- VFILE entry that has been merged |
|
601
|
@ merge INTEGER, -- Merged with this record |
|
602
|
@ mhash TEXT -- SHA1/SHA3 hash for merge object |
|
603
|
@ ); |
|
604
|
@ CREATE UNIQUE INDEX vmergex1 ON vmerge(id,mhash); |
|
605
|
@ |
|
606
|
@ -- The following trigger will prevent older versions of Fossil that |
|
607
|
@ -- do not know about the new vmerge.mhash column from updating the |
|
608
|
@ -- vmerge table. This must be done with a trigger, since legacy Fossil |
|
609
|
@ -- uses INSERT OR IGNORE to update vmerge, and the OR IGNORE will cause |
|
610
|
@ -- a NOT NULL constraint to be silently ignored. |
|
611
|
@ |
|
612
|
@ CREATE TRIGGER vmerge_ck1 AFTER INSERT ON vmerge |
|
613
|
@ WHEN new.mhash IS NULL BEGIN |
|
614
|
@ SELECT raise(FAIL, |
|
615
|
@ 'trying to update a newer check-out with an older version of Fossil'); |
|
616
|
@ END; |
|
617
|
@ |
|
618
|
; |
|
619
|
|
|
620
|
/* |
|
621
|
** The following table holds information about forum posts. It |
|
622
|
** is created on-demand whenever the manifest parser encounters |
|
623
|
** a forum-post artifact. |
|
624
|
*/ |
|
625
|
static const char zForumSchema[] = |
|
626
|
@ CREATE TABLE repository.forumpost( |
|
627
|
@ fpid INTEGER PRIMARY KEY, -- BLOB.rid for the artifact |
|
628
|
@ froot INT, -- fpid of the thread root |
|
629
|
@ fprev INT, -- Previous version of this same post |
|
630
|
@ firt INT, -- This post is in-reply-to |
|
631
|
@ fmtime REAL -- When posted. Julian day |
|
632
|
@ ); |
|
633
|
@ CREATE INDEX repository.forumthread ON forumpost(froot,fmtime); |
|
634
|
; |
|
635
|
|
|
636
|
/* Create the forum-post schema if it does not already exist */ |
|
637
|
void schema_forum(void){ |
|
638
|
if( !db_table_exists("repository","forumpost") ){ |
|
639
|
db_multi_exec("%s",zForumSchema/*safe-for-%s*/); |
|
640
|
} |
|
641
|
} |
|
642
|
|