/* ** Copyright (c) 2007 D. Richard Hipp ** ** This program is free software; you can redistribute it and/or ** modify it under the terms of the Simplified BSD License (also ** known as the "2-Clause License" or "FreeBSD License".) ** This program is distributed in the hope that it will be useful, ** but without any warranty; without even the implied warranty of ** merchantability or fitness for a particular purpose. ** ** Author contact information: ** drh@hwaci.com ** http://www.hwaci.com/drh/ ** ******************************************************************************* ** ** This file contains string constants that implement the database schema. */ #include "config.h" #include "schema.h" /* ** The database schema for the ~/.fossil configuration database. */ const char zConfigSchema[] = @ -- This file contains the schema for the database that is kept in the @ -- ~/.fossil file and that stores information about the users setup. @ -- @ CREATE TABLE global_config( @ name TEXT PRIMARY KEY, @ value TEXT @ ); @ @ -- Identifier for this file type. @ -- The integer is the same as 'FSLG'. @ PRAGMA application_id=252006675; ; #if INTERFACE /* ** The content tables have a content version number which rarely ** changes. The aux tables have an arbitrary version number (typically ** a date) which can change frequently. When the content schema changes, ** we have to execute special procedures to update the schema. When ** the aux schema changes, all we need to do is rebuild the database. */ #define CONTENT_SCHEMA "2" #define AUX_SCHEMA_MIN "2011-04-25 19:50" #define AUX_SCHEMA_MAX "2015-01-24" /* NB: Some features require the latest schema. Warning or error messages ** will appear if an older schema is used. However, the older schemas are ** adequate for many common functions. */ #endif /* INTERFACE */ /* ** The schema for a repository database. ** ** Schema1[] contains parts of the schema that are fixed and unchanging ** across versions. Schema2[] contains parts of the schema that can ** change from one version to the next. The information in Schema2[] ** is reconstructed from the information in Schema1[] by the "rebuild" ** operation. */ const char zRepositorySchema1[] = @ -- The BLOB and DELTA tables contain all records held in the repository. @ -- @ -- The BLOB.CONTENT column is always compressed using zlib. This @ -- column might hold the full text of the record or it might hold @ -- a delta that is able to reconstruct the record from some other @ -- record. If BLOB.CONTENT holds a delta, then a DELTA table entry @ -- will exist for the record and that entry will point to another @ -- entry that holds the source of the delta. Deltas can be chained. @ -- @ -- The blob and delta tables collectively hold the "global state" of @ -- a Fossil repository. @ -- @ CREATE TABLE blob( @ rid INTEGER PRIMARY KEY, -- Record ID @ rcvid INTEGER, -- Origin of this record @ size INTEGER, -- Size of content. -1 for a phantom. @ uuid TEXT UNIQUE NOT NULL, -- hash of the content @ content BLOB, -- Compressed content of this record @ CHECK( length(uuid)>=40 AND rid>0 ) @ ); @ CREATE TABLE delta( @ rid INTEGER PRIMARY KEY, -- BLOB that is delta-compressed @ srcid INTEGER NOT NULL REFERENCES blob -- Baseline for delta-compression @ ); @ CREATE INDEX delta_i1 ON delta(srcid); @ @ ------------------------------------------------------------------------- @ -- The BLOB and DELTA tables above hold the "global state" of a Fossil @ -- project; the stuff that is normally exchanged during "sync". The @ -- "local state" of a repository is contained in the remaining tables of @ -- the zRepositorySchema1 string. @ ------------------------------------------------------------------------- @ @ -- Whenever new blobs are received into the repository, an entry @ -- in this table records the source of the blob. @ -- @ CREATE TABLE rcvfrom( @ rcvid INTEGER PRIMARY KEY, -- Received-From ID @ uid INTEGER REFERENCES user, -- User login @ mtime DATETIME, -- Time of receipt. Julian day. @ nonce TEXT UNIQUE, -- Nonce used for login @ ipaddr TEXT -- Remote IP address. NULL for direct. @ ); @ @ -- Information about users @ -- @ -- The user.pw field can be either cleartext of the password, or @ -- a SHA1 hash of the password. If the user.pw field is exactly 40 @ -- characters long we assume it is a SHA1 hash. Otherwise, it is @ -- cleartext. The sha1_shared_secret() routine computes the password @ -- hash based on the project-code, the user login, and the cleartext @ -- password. @ -- @ CREATE TABLE user( @ uid INTEGER PRIMARY KEY, -- User ID @ login TEXT UNIQUE, -- login name of the user @ pw TEXT, -- password @ cap TEXT, -- Capabilities of this user @ cookie TEXT, -- WWW login cookie @ ipaddr TEXT, -- IP address for which cookie is valid @ cexpire DATETIME, -- Time when cookie expires @ info TEXT, -- contact information @ mtime DATE, -- last change. seconds since 1970 @ photo BLOB -- JPEG image of this user @ ); @ @ -- The config table holds miscellanous information about the repository. @ -- in the form of name-value pairs. @ -- @ CREATE TABLE config( @ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry @ value CLOB, -- Content of the named parameter @ mtime DATE, -- last modified. seconds since 1970 @ CHECK( typeof(name)='text' AND length(name)>=1 ) @ ); @ @ -- Artifacts that should not be processed are identified in the @ -- "shun" table. Artifacts that are control-file forgeries or @ -- spam or artifacts whose contents violate administrative policy @ -- can be shunned in order to prevent them from contaminating @ -- the repository. @ -- @ -- Shunned artifacts do not exist in the blob table. Hence they @ -- have not artifact ID (rid) and we thus must store their full @ -- UUID. @ -- @ CREATE TABLE shun( @ uuid UNIQUE, -- UUID of artifact to be shunned. Canonical form @ mtime DATE, -- When added. seconds since 1970 @ scom TEXT -- Optional text explaining why the shun occurred @ ); @ @ -- Artifacts that should not be pushed are stored in the "private" @ -- table. Private artifacts are omitted from the "unclustered" and @ -- "unsent" tables. @ -- @ CREATE TABLE private(rid INTEGER PRIMARY KEY); @ @ -- An entry in this table describes a database query that generates a @ -- table of tickets. @ -- @ CREATE TABLE reportfmt( @ rn INTEGER PRIMARY KEY, -- Report number @ owner TEXT, -- Owner of this report format (not used) @ title TEXT UNIQUE, -- Title of this report @ mtime DATE, -- Last modified. seconds since 1970 @ cols TEXT, -- A color-key specification @ sqlcode TEXT -- An SQL SELECT statement for this report @ ); @ @ -- Some ticket content (such as the originators email address or contact @ -- information) needs to be obscured to protect privacy. This is achieved @ -- by storing an SHA1 hash of the content. For display, the hash is @ -- mapped back into the original text using this table. @ -- @ -- This table contains sensitive information and should not be shared @ -- with unauthorized users. @ -- @ CREATE TABLE concealed( @ hash TEXT PRIMARY KEY, -- The SHA1 hash of content @ mtime DATE, -- Time created. Seconds since 1970 @ content TEXT -- Content intended to be concealed @ ); @ @ -- The application ID helps the unix "file" command to identify the @ -- database as a fossil repository. @ PRAGMA application_id=252006673; ; /* ** The default reportfmt entry for the schema. This is in an extra ** script so that (configure reset) can install the default report. */ const char zRepositorySchemaDefaultReports[] = @ INSERT INTO reportfmt(title,mtime,cols,sqlcode) @ VALUES('All Tickets',julianday('1970-01-01'),'#ffffff Key: @ #f2dcdc Active @ #e8e8e8 Review @ #cfe8bd Fixed @ #bde5d6 Tested @ #cacae5 Deferred @ #c8c8c8 Closed','SELECT @ CASE WHEN status IN (''Open'',''Verified'') THEN ''#f2dcdc'' @ WHEN status=''Review'' THEN ''#e8e8e8'' @ WHEN status=''Fixed'' THEN ''#cfe8bd'' @ WHEN status=''Tested'' THEN ''#bde5d6'' @ WHEN status=''Deferred'' THEN ''#cacae5'' @ ELSE ''#c8c8c8'' END AS ''bgcolor'', @ substr(tkt_uuid,1,10) AS ''#'', @ datetime(tkt_mtime) AS ''mtime'', @ type, @ status, @ subsystem, @ title @ FROM ticket'); ; const char zRepositorySchema2[] = @ -- Filenames @ -- @ CREATE TABLE filename( @ fnid INTEGER PRIMARY KEY, -- Filename ID @ name TEXT UNIQUE -- Name of file page @ ); @ @ -- Linkages between check-ins, files created by each check-in, and @ -- the names of those files. @ -- @ -- Each entry represents a file that changed content from pid to fid @ -- due to the check-in that goes from pmid to mid. fnid is the name @ -- of the file in the mid check-in. If the file was renamed as part @ -- of the mid check-in, then pfnid is the previous filename. @ @ -- There can be multiple entries for (mid,fid) if the mid check-in was @ -- a merge. Entries with isaux==0 are from the primary parent. Merge @ -- parents have isaux set to true. @ -- @ -- Field name mnemonics: @ -- mid = Manifest ID. (Each check-in is stored as a "Manifest") @ -- fid = File ID. @ -- pmid = Parent Manifest ID. @ -- pid = Parent file ID. @ -- fnid = File Name ID. @ -- pfnid = Parent File Name ID. @ -- isaux = pmid IS AUXiliary parent, not primary parent @ -- @ -- pid==0 if the file is added by check-in mid. @ -- pid==(-1) if the file exists in a merge parents but not in the primary @ -- parent. In other words, if the file file was added by merge. @ -- fid==0 if the file is removed by check-in mid. @ -- @ CREATE TABLE mlink( @ mid INTEGER, -- Check-in that contains fid @ fid INTEGER, -- New file content. 0 if deleted @ pmid INTEGER, -- Check-in that contains pid @ pid INTEGER, -- Prev file content. 0 if new. -1 merge @ fnid INTEGER REFERENCES filename, -- Name of the file @ pfnid INTEGER REFERENCES filename, -- Previous name. 0 if unchanged @ mperm INTEGER, -- File permissions. 1==exec @ isaux BOOLEAN DEFAULT 0 -- TRUE if pmid is the primary @ ); @ CREATE INDEX mlink_i1 ON mlink(mid); @ CREATE INDEX mlink_i2 ON mlink(fnid); @ CREATE INDEX mlink_i3 ON mlink(fid); @ CREATE INDEX mlink_i4 ON mlink(pid); @ @ -- Parent/child linkages between check-ins @ -- @ CREATE TABLE plink( @ pid INTEGER REFERENCES blob, -- Parent manifest @ cid INTEGER REFERENCES blob, -- Child manifest @ isprim BOOLEAN, -- pid is the primary parent of cid @ mtime DATETIME, -- the date/time stamp on cid. Julian day. @ baseid INTEGER REFERENCES blob, -- Baseline if cid is a delta manifest. @ UNIQUE(pid, cid) @ ); @ CREATE INDEX plink_i2 ON plink(cid,pid); @ @ -- A "leaf" check-in is a check-in that has no children in the same @ -- branch. The set of all leaves is easily computed with a join, @ -- between the plink and tagxref tables, but it is a slower join for @ -- very large repositories (repositories with 100,000 or more check-ins) @ -- and so it makes sense to precompute the set of leaves. There is @ -- one entry in the following table for each leaf. @ -- @ CREATE TABLE leaf(rid INTEGER PRIMARY KEY); @ @ -- Events used to generate a timeline @ -- @ CREATE TABLE event( @ type TEXT, -- Type of event: 'ci', 'w', 'e', 't', 'g' @ mtime DATETIME, -- Time of occurrence. Julian day. @ objid INTEGER PRIMARY KEY, -- Associated record ID @ tagid INTEGER, -- Associated ticket or wiki name tag @ uid INTEGER REFERENCES user, -- User who caused the event @ bgcolor TEXT, -- Color set by 'bgcolor' property @ euser TEXT, -- User set by 'user' property @ user TEXT, -- Name of the user @ ecomment TEXT, -- Comment set by 'comment' property @ comment TEXT, -- Comment describing the event @ brief TEXT, -- Short comment when tagid already seen @ omtime DATETIME -- Original unchanged date+time, or NULL @ ); @ CREATE INDEX event_i1 ON event(mtime); @ @ -- A record of phantoms. A phantom is a record for which we know the @ -- UUID but we do not (yet) know the file content. @ -- @ CREATE TABLE phantom( @ rid INTEGER PRIMARY KEY -- Record ID of the phantom @ ); @ @ -- A record of orphaned delta-manifests. An orphan is a delta-manifest @ -- for which we have content, but its baseline-manifest is a phantom. @ -- We have to track all orphan maniftests so that when the baseline arrives, @ -- we know to process the orphaned deltas. @ CREATE TABLE orphan( @ rid INTEGER PRIMARY KEY, -- Delta manifest with a phantom baseline @ baseline INTEGER -- Phantom baseline of this orphan @ ); @ CREATE INDEX orphan_baseline ON orphan(baseline); @ @ -- Unclustered records. An unclustered record is a record (including @ -- a cluster records themselves) that is not mentioned by some other @ -- cluster. @ -- @ -- Phantoms are usually included in the unclustered table. A new cluster @ -- will never be created that contains a phantom. But another repository @ -- might send us a cluster that contains entries that are phantoms to @ -- us. @ -- @ CREATE TABLE unclustered( @ rid INTEGER PRIMARY KEY -- Record ID of the unclustered file @ ); @ @ -- Records which have never been pushed to another server. This is @ -- used to reduce push operations to a single HTTP request in the @ -- common case when one repository only talks to a single server. @ -- @ CREATE TABLE unsent( @ rid INTEGER PRIMARY KEY -- Record ID of the phantom @ ); @ @ -- Each baseline or manifest can have one or more tags. A tag @ -- is defined by a row in the next table. @ -- @ -- Wiki pages are tagged with "wiki-NAME" where NAME is the name of @ -- the wiki page. Tickets changes are tagged with "ticket-UUID" where @ -- UUID is the indentifier of the ticket. Tags used to assign symbolic @ -- names to baselines are branches are of the form "sym-NAME" where @ -- NAME is the symbolic name. @ -- @ CREATE TABLE tag( @ tagid INTEGER PRIMARY KEY, -- Numeric tag ID @ tagname TEXT UNIQUE -- Tag name. @ ); @ INSERT INTO tag VALUES(1, 'bgcolor'); -- TAG_BGCOLOR @ INSERT INTO tag VALUES(2, 'comment'); -- TAG_COMMENT @ INSERT INTO tag VALUES(3, 'user'); -- TAG_USER @ INSERT INTO tag VALUES(4, 'date'); -- TAG_DATE @ INSERT INTO tag VALUES(5, 'hidden'); -- TAG_HIDDEN @ INSERT INTO tag VALUES(6, 'private'); -- TAG_PRIVATE @ INSERT INTO tag VALUES(7, 'cluster'); -- TAG_CLUSTER @ INSERT INTO tag VALUES(8, 'branch'); -- TAG_BRANCH @ INSERT INTO tag VALUES(9, 'closed'); -- TAG_CLOSED @ INSERT INTO tag VALUES(10,'parent'); -- TAG_PARENT @ INSERT INTO tag VALUES(11,'note'); -- TAG_NOTE @ @ -- Assignments of tags to baselines. Note that we allow tags to @ -- have values assigned to them. So we are not really dealing with @ -- tags here. These are really properties. But we are going to @ -- keep calling them tags because in many cases the value is ignored. @ -- @ CREATE TABLE tagxref( @ tagid INTEGER REFERENCES tag, -- The tag that added or removed @ tagtype INTEGER, -- 0:-,cancel 1:+,single 2:*,propagate @ srcid INTEGER REFERENCES blob, -- Artifact of tag. 0 for propagated tags @ origid INTEGER REFERENCES blob, -- check-in holding propagated tag @ value TEXT, -- Value of the tag. Might be NULL. @ mtime TIMESTAMP, -- Time of addition or removal. Julian day @ rid INTEGER REFERENCE blob, -- Artifact tag is applied to @ UNIQUE(rid, tagid) @ ); @ CREATE INDEX tagxref_i1 ON tagxref(tagid, mtime); @ @ -- When a hyperlink occurs from one artifact to another (for example @ -- when a check-in comment refers to a ticket) an entry is made in @ -- the following table for that hyperlink. This table is used to @ -- facilitate the display of "back links". @ -- @ CREATE TABLE backlink( @ target TEXT, -- Where the hyperlink points to @ srctype INT, -- 0: check-in 1: ticket 2: wiki @ srcid INT, -- rid for check-in or wiki. tkt_id for ticket. @ mtime TIMESTAMP, -- time that the hyperlink was added. Julian day. @ UNIQUE(target, srctype, srcid) @ ); @ CREATE INDEX backlink_src ON backlink(srcid, srctype); @ @ -- Each attachment is an entry in the following table. Only @ -- the most recent attachment (identified by the D card) is saved. @ -- @ CREATE TABLE attachment( @ attachid INTEGER PRIMARY KEY, -- Local id for this attachment @ isLatest BOOLEAN DEFAULT 0, -- True if this is the one to use @ mtime TIMESTAMP, -- Last changed. Julian day. @ src TEXT, -- UUID of the attachment. NULL to delete @ target TEXT, -- Object attached to. Wikiname or Tkt UUID @ filename TEXT, -- Filename for the attachment @ comment TEXT, -- Comment associated with this attachment @ user TEXT -- Name of user adding attachment @ ); @ CREATE INDEX attachment_idx1 ON attachment(target, filename, mtime); @ CREATE INDEX attachment_idx2 ON attachment(src); @ @ -- Template for the TICKET table @ -- @ -- NB: when changing the schema of the TICKET table here, also make the @ -- same change in tktsetup.c. @ -- @ CREATE TABLE ticket( @ -- Do not change any column that begins with tkt_ @ tkt_id INTEGER PRIMARY KEY, @ tkt_uuid TEXT UNIQUE, @ tkt_mtime DATE, @ tkt_ctime DATE, @ -- Add as many field as required below this line @ type TEXT, @ status TEXT, @ subsystem TEXT, @ priority TEXT, @ severity TEXT, @ foundin TEXT, @ private_contact TEXT, @ resolution TEXT, @ title TEXT, @ comment TEXT @ ); @ CREATE TABLE ticketchng( @ -- Do not change any column that begins with tkt_ @ tkt_id INTEGER REFERENCES ticket, @ tkt_rid INTEGER REFERENCES blob, @ tkt_mtime DATE, @ -- Add as many fields as required below this line @ login TEXT, @ username TEXT, @ mimetype TEXT, @ icomment TEXT @ ); @ CREATE INDEX ticketchng_idx1 ON ticketchng(tkt_id, tkt_mtime); ; /* ** Predefined tagid values */ #if INTERFACE # define TAG_BGCOLOR 1 /* Set the background color for display */ # define TAG_COMMENT 2 /* The check-in comment */ # define TAG_USER 3 /* User who made a checking */ # define TAG_DATE 4 /* The date of a check-in */ # define TAG_HIDDEN 5 /* Do not display in timeline */ # define TAG_PRIVATE 6 /* Do not sync */ # define TAG_CLUSTER 7 /* A cluster */ # define TAG_BRANCH 8 /* Value is name of the current branch */ # define TAG_CLOSED 9 /* Do not display this check-in as a leaf */ # define TAG_PARENT 10 /* Change to parentage on a check-in */ # define TAG_NOTE 11 /* Extra text appended to a check-in comment */ #endif /* ** The schema for the local FOSSIL database file found at the root ** of every check-out. This database contains the complete state of ** the checkout. */ const char zLocalSchema[] = @ -- The VVAR table holds miscellanous information about the local database @ -- in the form of name-value pairs. This is similar to the VAR table @ -- table in the repository except that this table holds information that @ -- is specific to the local checkout. @ -- @ -- Important Variables: @ -- @ -- repository Full pathname of the repository database @ -- user-id Userid to use @ -- @ CREATE TABLE vvar( @ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry @ value CLOB, -- Content of the named parameter @ CHECK( typeof(name)='text' AND length(name)>=1 ) @ ); @ @ -- Each entry in the vfile table represents a single file in the @ -- current checkout. @ -- @ -- The file.rid field is 0 for files or folders that have been @ -- added but not yet committed. @ -- @ -- Vfile.chnged is 0 for unmodified files, 1 for files that have @ -- been edited or which have been subjected to a 3-way merge. @ -- Vfile.chnged is 2 if the file has been replaced from a different @ -- version by the merge and 3 if the file has been added by a merge. @ -- Vfile.chnged is 4|5 is the same as 2|3, but the operation has been @ -- done by an --integrate merge. The difference between vfile.chnged==3|5 @ -- and a regular add is that with vfile.chnged==3|5 we know that the @ -- current version of the file is already in the repository. @ -- @ CREATE TABLE vfile( @ id INTEGER PRIMARY KEY, -- ID of the checked out file @ vid INTEGER REFERENCES blob, -- The baseline this file is part of. @ chnged INT DEFAULT 0, -- 0:unchng 1:edit 2:m-chng 3:m-add 4:i-chng 5:i-add @ deleted BOOLEAN DEFAULT 0, -- True if deleted @ isexe BOOLEAN, -- True if file should be executable @ islink BOOLEAN, -- True if file should be symlink @ rid INTEGER, -- Originally from this repository record @ mrid INTEGER, -- Based on this record due to a merge @ mtime INTEGER, -- Mtime of file on disk. sec since 1970 @ pathname TEXT, -- Full pathname relative to root @ origname TEXT, -- Original pathname. NULL if unchanged @ UNIQUE(pathname,vid) @ ); @ @ -- This table holds a record of uncommitted merges in the local @ -- file tree. If a VFILE entry with id has merged with another @ -- record, there is an entry in this table with (id,merge) where @ -- merge is the RECORD table entry that the file merged against. @ -- An id of 0 or <-3 here means the version record itself. When @ -- id==(-1) that is a cherrypick merge, id==(-2) that is a @ -- backout merge and id==(-4) is a integrate merge. @ @ CREATE TABLE vmerge( @ id INTEGER REFERENCES vfile, -- VFILE entry that has been merged @ merge INTEGER, -- Merged with this record @ UNIQUE(id, merge) @ ); @ @ -- Identifier for this file type. @ -- The integer is the same as 'FSLC'. @ PRAGMA application_id=252006674; ;