dbda8d6ce9 2007-07-21 drh: /* dbda8d6ce9 2007-07-21 drh: ** Copyright (c) 2007 D. Richard Hipp dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** This program is free software; you can redistribute it and/or dbda8d6ce9 2007-07-21 drh: ** modify it under the terms of the GNU General Public dbda8d6ce9 2007-07-21 drh: ** License version 2 as published by the Free Software Foundation. dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** This program is distributed in the hope that it will be useful, dbda8d6ce9 2007-07-21 drh: ** but WITHOUT ANY WARRANTY; without even the implied warranty of dbda8d6ce9 2007-07-21 drh: ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU dbda8d6ce9 2007-07-21 drh: ** General Public License for more details. dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** You should have received a copy of the GNU General Public dbda8d6ce9 2007-07-21 drh: ** License along with this library; if not, write to the dbda8d6ce9 2007-07-21 drh: ** Free Software Foundation, Inc., 59 Temple Place - Suite 330, dbda8d6ce9 2007-07-21 drh: ** Boston, MA 02111-1307, USA. dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** Author contact information: dbda8d6ce9 2007-07-21 drh: ** drh@hwaci.com dbda8d6ce9 2007-07-21 drh: ** http://www.hwaci.com/drh/ dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ******************************************************************************* dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** This file contains string constants that implement the database schema. dbda8d6ce9 2007-07-21 drh: */ dbda8d6ce9 2007-07-21 drh: #include "config.h" dbda8d6ce9 2007-07-21 drh: #include "schema.h" dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: /* dbda8d6ce9 2007-07-21 drh: ** The database schema for the ~/.fossil configuration database. dbda8d6ce9 2007-07-21 drh: */ dbda8d6ce9 2007-07-21 drh: const char zConfigSchema[] = dbda8d6ce9 2007-07-21 drh: @ -- This file contains the schema for the database that is kept in the dbda8d6ce9 2007-07-21 drh: @ -- ~/.fossil file and that stores information about the users setup. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE global_config( dbda8d6ce9 2007-07-21 drh: @ name TEXT PRIMARY KEY, dbda8d6ce9 2007-07-21 drh: @ value TEXT dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: ; dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: #if INTERFACE dbda8d6ce9 2007-07-21 drh: /* dbda8d6ce9 2007-07-21 drh: ** The content tables have a content version number which rarely dbda8d6ce9 2007-07-21 drh: ** changes. The aux tables have an arbitrary version number (typically dbda8d6ce9 2007-07-21 drh: ** a date) which can change frequently. When the content schema changes, dbda8d6ce9 2007-07-21 drh: ** we have to execute special procedures to update the schema. When dbda8d6ce9 2007-07-21 drh: ** the aux schema changes, all we need to do is rebuild the database. dbda8d6ce9 2007-07-21 drh: */ dbda8d6ce9 2007-07-21 drh: #define CONTENT_SCHEMA "1" dbda8d6ce9 2007-07-21 drh: #define AUX_SCHEMA "2006-12-23" dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: #endif /* INTERFACE */ dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: /* dbda8d6ce9 2007-07-21 drh: ** The schema for a repository database. dbda8d6ce9 2007-07-21 drh: ** dbda8d6ce9 2007-07-21 drh: ** Schema1[] contains parts of the schema that are fixed and unchanging dbda8d6ce9 2007-07-21 drh: ** across versions. Schema2[] contains parts of the schema that can dbda8d6ce9 2007-07-21 drh: ** change from one version to the next. The information in Schema2[] dbda8d6ce9 2007-07-21 drh: ** can be reconstructed from the information in Schema1[]. dbda8d6ce9 2007-07-21 drh: */ dbda8d6ce9 2007-07-21 drh: const char zRepositorySchema1[] = dbda8d6ce9 2007-07-21 drh: @ -- The BLOB and DELTA tables contain all records held in the repository. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- The BLOB.CONTENT column is always compressed using libz. This dbda8d6ce9 2007-07-21 drh: @ -- column might hold the full text of the record or it might hold dbda8d6ce9 2007-07-21 drh: @ -- a delta that is able to reconstruct the record from some other dbda8d6ce9 2007-07-21 drh: @ -- record. If BLOB.CONTENT holds a delta, then a DELTA table entry dbda8d6ce9 2007-07-21 drh: @ -- will exist for the record and that entry will point to another dbda8d6ce9 2007-07-21 drh: @ -- entry that holds the source of the delta. Deltas can be chained. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE blob( dbda8d6ce9 2007-07-21 drh: @ rid INTEGER PRIMARY KEY, -- Record ID dbda8d6ce9 2007-07-21 drh: @ rcvid INTEGER, -- Origin of this record dbda8d6ce9 2007-07-21 drh: @ size INTEGER, -- Size of content. -1 for a phantom. dbda8d6ce9 2007-07-21 drh: @ uuid TEXT UNIQUE, -- SHA1 hash of the content dbda8d6ce9 2007-07-21 drh: @ content BLOB -- Compressed content of this record dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE delta( dbda8d6ce9 2007-07-21 drh: @ rid INTEGER PRIMARY KEY, -- Record ID dbda8d6ce9 2007-07-21 drh: @ srcid INTEGER NOT NULL REFERENCES blob -- Record holding source document dbda8d6ce9 2007-07-21 drh: @ ); eea381f416 2007-08-09 drh: @ CREATE INDEX delta_i1 ON delta(srcid); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Whenever new blobs are received into the repository, an entry dbda8d6ce9 2007-07-21 drh: @ -- in this table records the source of the blob. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE rcvfrom( dbda8d6ce9 2007-07-21 drh: @ rcvid INTEGER PRIMARY KEY, -- Received-From ID dbda8d6ce9 2007-07-21 drh: @ uid INTEGER REFERENCES user, -- User login dbda8d6ce9 2007-07-21 drh: @ mtime DATETIME, -- Time or receipt dbda8d6ce9 2007-07-21 drh: @ nonce TEXT UNIQUE, -- Nonce used for login dbda8d6ce9 2007-07-21 drh: @ ipaddr TEXT -- Remote IP address. NULL for direct. dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Information about users dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE user( dbda8d6ce9 2007-07-21 drh: @ uid INTEGER PRIMARY KEY, -- User ID dbda8d6ce9 2007-07-21 drh: @ login TEXT, -- login name of the user dbda8d6ce9 2007-07-21 drh: @ pw TEXT, -- password dbda8d6ce9 2007-07-21 drh: @ cap TEXT, -- Capabilities of this user dbda8d6ce9 2007-07-21 drh: @ cookie TEXT, -- WWW login cookie dbda8d6ce9 2007-07-21 drh: @ ipaddr TEXT, -- IP address for which cookie is valid dbda8d6ce9 2007-07-21 drh: @ cexpire DATETIME, -- Time when cookie expires dbda8d6ce9 2007-07-21 drh: @ info TEXT, -- contact information dbda8d6ce9 2007-07-21 drh: @ photo BLOB -- JPEG image of this user dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- The VAR table holds miscellanous information about the repository. dbda8d6ce9 2007-07-21 drh: @ -- in the form of name-value pairs. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE config( dbda8d6ce9 2007-07-21 drh: @ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry dbda8d6ce9 2007-07-21 drh: @ value CLOB, -- Content of the named parameter dbda8d6ce9 2007-07-21 drh: @ CHECK( typeof(name)='text' AND length(name)>=1 ) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: ; dbda8d6ce9 2007-07-21 drh: const char zRepositorySchema2[] = dbda8d6ce9 2007-07-21 drh: @ -- Filenames dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE filename( dbda8d6ce9 2007-07-21 drh: @ fnid INTEGER PRIMARY KEY, -- Filename ID dbda8d6ce9 2007-07-21 drh: @ name TEXT UNIQUE -- Name of file page dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Linkages between manifests, files created by that manifest, and dbda8d6ce9 2007-07-21 drh: @ -- the names of those files. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE mlink( dbda8d6ce9 2007-07-21 drh: @ mid INTEGER REFERENCES blob, -- Manifest ID where change occurs dbda8d6ce9 2007-07-21 drh: @ pid INTEGER REFERENCES blob, -- File ID in parent manifest dbda8d6ce9 2007-07-21 drh: @ fid INTEGER REFERENCES blob, -- Changed file ID in this manifest dbda8d6ce9 2007-07-21 drh: @ fnid INTEGER REFERENCES filename -- Name of the file dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX mlink_i1 ON mlink(mid); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX mlink_i2 ON mlink(fnid); 73bddaebb9 2007-08-09 drh: @ CREATE INDEX mlink_i3 ON mlink(fid); 73bddaebb9 2007-08-09 drh: @ CREATE INDEX mlink_i4 ON mlink(pid); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Parent/child linkages dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE plink( dbda8d6ce9 2007-07-21 drh: @ pid INTEGER REFERENCES blob, -- Parent manifest dbda8d6ce9 2007-07-21 drh: @ cid INTEGER REFERENCES blob, -- Child manifest dbda8d6ce9 2007-07-21 drh: @ isprim BOOLEAN, -- pid is the primary parent of cid dbda8d6ce9 2007-07-21 drh: @ mtime DATETIME, -- the date/time stamp on cid dbda8d6ce9 2007-07-21 drh: @ UNIQUE(pid, cid) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX plink_i2 ON plink(cid); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Events used to generate a timeline dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE event( 2bc0e2c565 2007-09-21 drh: @ type TEXT, -- Type of event 2bc0e2c565 2007-09-21 drh: @ mtime DATETIME, -- Date and time when the event occurs 2bc0e2c565 2007-09-21 drh: @ objid INTEGER PRIMARY KEY, -- Associated record ID 2bc0e2c565 2007-09-21 drh: @ uid INTEGER REFERENCES user, -- User who caused the event 2bc0e2c565 2007-09-21 drh: @ user TEXT, -- Name of the user 2bc0e2c565 2007-09-21 drh: @ comment TEXT -- Comment describing the event dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX event_i1 ON event(mtime); 73bddaebb9 2007-08-09 drh: @ 48c4e69d2b 2007-09-09 drh: @ -- A record of phantoms. A phantom is a record for which we know the 48c4e69d2b 2007-09-09 drh: @ -- UUID but we do not (yet) know the file content. 73bddaebb9 2007-08-09 drh: @ -- 73bddaebb9 2007-08-09 drh: @ CREATE TABLE phantom( 73bddaebb9 2007-08-09 drh: @ rid INTEGER PRIMARY KEY -- Record ID of the phantom 73bddaebb9 2007-08-09 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ ba486fec5a 2007-09-03 drh: @ -- Unclustered records. An unclustered record is a record (including ba486fec5a 2007-09-03 drh: @ -- a cluster records themselves) that is not mentioned by some other ba486fec5a 2007-09-03 drh: @ -- cluster. 48c4e69d2b 2007-09-09 drh: @ -- 48c4e69d2b 2007-09-09 drh: @ -- Phantoms are usually included in the unclustered table. A new cluster 48c4e69d2b 2007-09-09 drh: @ -- will never be created that contains a phantom. But another repository 48c4e69d2b 2007-09-09 drh: @ -- might send us a cluster that contains entries that are phantoms to 48c4e69d2b 2007-09-09 drh: @ -- us. ba486fec5a 2007-09-03 drh: @ -- ba486fec5a 2007-09-03 drh: @ CREATE TABLE unclustered( ba486fec5a 2007-09-03 drh: @ rid INTEGER PRIMARY KEY -- Record ID of the unclustered file ba486fec5a 2007-09-03 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ ba486fec5a 2007-09-03 drh: @ -- Records which have never been pushed to another server. This is ba486fec5a 2007-09-03 drh: @ -- used to reduce push operations to a single HTTP request in the ba486fec5a 2007-09-03 drh: @ -- common case when one repository only talks to a single server. ba486fec5a 2007-09-03 drh: @ -- ba486fec5a 2007-09-03 drh: @ CREATE TABLE unsent( ba486fec5a 2007-09-03 drh: @ rid INTEGER PRIMARY KEY -- Record ID of the phantom dbda8d6ce9 2007-07-21 drh: @ ); ba486fec5a 2007-09-03 drh: @ 2bc0e2c565 2007-09-21 drh: @ -- Each baseline or manifest can have one or more tags. A tag 2bc0e2c565 2007-09-21 drh: @ -- is defined by a row in the next table. 2bc0e2c565 2007-09-21 drh: @ -- 2bc0e2c565 2007-09-21 drh: @ -- Tags that begin with "br" automatically propagate to direct 2bc0e2c565 2007-09-21 drh: @ -- children, but not to merge children. ba486fec5a 2007-09-03 drh: @ -- 2bc0e2c565 2007-09-21 drh: @ CREATE TABLE tag( 2bc0e2c565 2007-09-21 drh: @ tagid INTEGER PRIMARY KEY, -- Numeric tag ID 2bc0e2c565 2007-09-21 drh: @ tagname TEXT UNIQUE -- Tag name. Prefixed by 'v' or 'b' dbda8d6ce9 2007-07-21 drh: @ ); 2bc0e2c565 2007-09-21 drh: @ 2bc0e2c565 2007-09-21 drh: @ -- Assignments of tags to baselines 2bc0e2c565 2007-09-21 drh: @ -- 2bc0e2c565 2007-09-21 drh: @ CREATE TABLE tagxref( 2bc0e2c565 2007-09-21 drh: @ tagid INTEGER REFERENCES tag, -- The tag that added or removed 2bc0e2c565 2007-09-21 drh: @ addFlag BOOLEAN, -- True to add the tag, False to remove 2bc0e2c565 2007-09-21 drh: @ srcid INTEGER REFERENCES blob, -- Origin of the tag. 0 for propagated tags 2bc0e2c565 2007-09-21 drh: @ mtime TIMESTAMP, -- Time of addition or removal 2bc0e2c565 2007-09-21 drh: @ rid INTEGER REFERENCE blob, -- Baseline that tag added/removed from 2bc0e2c565 2007-09-21 drh: @ UNIQUE(rid, tagid) 2bc0e2c565 2007-09-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: ; dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: /* dbda8d6ce9 2007-07-21 drh: ** The schema for the locate FOSSIL database file found at the root dbda8d6ce9 2007-07-21 drh: ** of very check-out. This database contains the complete state of dbda8d6ce9 2007-07-21 drh: ** the checkout. dbda8d6ce9 2007-07-21 drh: */ dbda8d6ce9 2007-07-21 drh: const char zLocalSchema[] = dbda8d6ce9 2007-07-21 drh: @ -- The VVAR table holds miscellanous information about the local database dbda8d6ce9 2007-07-21 drh: @ -- in the form of name-value pairs. This is similar to the VAR table dbda8d6ce9 2007-07-21 drh: @ -- table in the repository except that this table holds information that dbda8d6ce9 2007-07-21 drh: @ -- is specific to the local checkout. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- Important Variables: dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- repository Full pathname of the repository database dbda8d6ce9 2007-07-21 drh: @ -- user-id Userid to use dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE vvar( dbda8d6ce9 2007-07-21 drh: @ name TEXT PRIMARY KEY NOT NULL, -- Primary name of the entry dbda8d6ce9 2007-07-21 drh: @ value CLOB, -- Content of the named parameter dbda8d6ce9 2007-07-21 drh: @ CHECK( typeof(name)='text' AND length(name)>=1 ) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- Each entry in the vfile table represents a single file or folder dbda8d6ce9 2007-07-21 drh: @ -- that is part of a version. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- The file.rid field is 0 for files or folders that have been dbda8d6ce9 2007-07-21 drh: @ -- added but not yet committed. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- Vfile.chnged is 0 for unmodified files, 1 for files that have dbda8d6ce9 2007-07-21 drh: @ -- been edited or which have been subjected to a 3-way merge. dbda8d6ce9 2007-07-21 drh: @ -- Vfile.chnged is 2 if the file has been replaced from a different dbda8d6ce9 2007-07-21 drh: @ -- version by the merge and 3 if the file has been added by a merge. dbda8d6ce9 2007-07-21 drh: @ -- The difference between vfile.chnged==2 and a regular add is that dbda8d6ce9 2007-07-21 drh: @ -- with vfile.chnged==2 we know that the current version of the file dbda8d6ce9 2007-07-21 drh: @ -- is already in the repository. dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE vfile( dbda8d6ce9 2007-07-21 drh: @ id INTEGER PRIMARY KEY, -- ID of the checked out file 32c639d086 2007-08-26 aku: @ vid INTEGER REFERENCES blob, -- The version this file is part of. dbda8d6ce9 2007-07-21 drh: @ chnged INT DEFAULT 0, -- 0:unchnged 1:edited 2:m-chng 3:m-add dbda8d6ce9 2007-07-21 drh: @ deleted BOOLEAN DEFAULT 0, -- True if deleted dbda8d6ce9 2007-07-21 drh: @ rid INTEGER, -- Originally from this repository record dbda8d6ce9 2007-07-21 drh: @ mrid INTEGER, -- Based on this record due to a merge dbda8d6ce9 2007-07-21 drh: @ pathname TEXT, -- Full pathname dbda8d6ce9 2007-07-21 drh: @ UNIQUE(pathname,vid) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ -- This table holds a record of uncommitted merges in the local dbda8d6ce9 2007-07-21 drh: @ -- file tree. If a VFILE entry with id has merged with another dbda8d6ce9 2007-07-21 drh: @ -- record, there is an entry in this table with (id,merge) where dbda8d6ce9 2007-07-21 drh: @ -- merge is the RECORD table entry that the file merged against. dbda8d6ce9 2007-07-21 drh: @ -- An id of 0 here means the version record itself. dbda8d6ce9 2007-07-21 drh: @ dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE vmerge( dbda8d6ce9 2007-07-21 drh: @ id INTEGER REFERENCES vfile, -- VFILE entry that has been merged 4c82c7773f 2007-08-30 drh: @ merge INTEGER, -- Merged with this record 4c82c7773f 2007-08-30 drh: @ UNIQUE(id, merge) dbda8d6ce9 2007-07-21 drh: @ ); a36177bcce 2007-09-11 drh: @ dbda8d6ce9 2007-07-21 drh: ; dbda8d6ce9 2007-07-21 drh: dbda8d6ce9 2007-07-21 drh: const char zServerTempSchema[] = dbda8d6ce9 2007-07-21 drh: @ -- A copy of the vfile table schema used by the WWW server dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TEMP TABLE vfile( dbda8d6ce9 2007-07-21 drh: @ id INTEGER PRIMARY KEY, -- ID of the checked out file dbda8d6ce9 2007-07-21 drh: @ vid INTEGER REFERENCES record, -- The version this file is part of. dbda8d6ce9 2007-07-21 drh: @ chnged INT DEFAULT 0, -- 0:unchnged 1:edited 2:m-chng 3:m-add dbda8d6ce9 2007-07-21 drh: @ deleted BOOLEAN DEFAULT 0, -- True if deleted dbda8d6ce9 2007-07-21 drh: @ rid INTEGER, -- Originally from this repository record dbda8d6ce9 2007-07-21 drh: @ mrid INTEGER, -- Based on this record due to a merge dbda8d6ce9 2007-07-21 drh: @ pathname TEXT, -- Full pathname dbda8d6ce9 2007-07-21 drh: @ UNIQUE(pathname,vid) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: ;
File Annotation