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( dbda8d6ce9 2007-07-21 drh: @ type TEXT, dbda8d6ce9 2007-07-21 drh: @ mtime DATETIME, dbda8d6ce9 2007-07-21 drh: @ objid INTEGER, dbda8d6ce9 2007-07-21 drh: @ uid INTEGER REFERENCES user, dbda8d6ce9 2007-07-21 drh: @ user TEXT, dbda8d6ce9 2007-07-21 drh: @ comment TEXT dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX event_i1 ON event(mtime); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX event_i2 ON event(objid); dbda8d6ce9 2007-07-21 drh: @ 73bddaebb9 2007-08-09 drh: @ -- A record of phantoms dbda8d6ce9 2007-07-21 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: @ dbda8d6ce9 2007-07-21 drh: @ -- Aggregated ticket information dbda8d6ce9 2007-07-21 drh: @ -- dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE tkt( dbda8d6ce9 2007-07-21 drh: @ tktid INTEGER PRIMARY KEY, -- Internal ticket ID dbda8d6ce9 2007-07-21 drh: @ fnid INTEGER REFERENCES filename, -- Name of the ticket file dbda8d6ce9 2007-07-21 drh: @ rid INTEGER REFERENCES blob, -- version of ticket file scanned dbda8d6ce9 2007-07-21 drh: @ title TEXT, -- title of the ticket dbda8d6ce9 2007-07-21 drh: @ remarks TEXT -- text of the ticket dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE tkttag( dbda8d6ce9 2007-07-21 drh: @ tagid INTEGER PRIMARY KEY, -- Numeric tag ID dbda8d6ce9 2007-07-21 drh: @ name TEXT UNIQUE -- Human-readable name of tag dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE TABLE tktmap( dbda8d6ce9 2007-07-21 drh: @ tktid INTEGER REFERENCES tkt, -- This ticket dbda8d6ce9 2007-07-21 drh: @ tagid INTEGER REFERENCES tkttag, -- ....holds this tag dbda8d6ce9 2007-07-21 drh: @ UNIQUE(tktid, tagid) dbda8d6ce9 2007-07-21 drh: @ ); dbda8d6ce9 2007-07-21 drh: @ CREATE INDEX tktmap_i2 ON tktmap(tagid); 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 vfile.parent field is 0 for the root folder. 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 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: @ 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 dbda8d6ce9 2007-07-21 drh: @ merge INTEGER -- Merged with this record dbda8d6ce9 2007-07-21 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