Fossil SCM

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

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button