-- This file contains parts of the schema that can change from one
-- version to the next. The data stored in these tables is
-- reconstructed from the information in the main repo schema by the
-- "rebuild" operation.
-- Filenames
--
CREATE TABLE filename(
fnid INTEGER PRIMARY KEY, -- Filename ID
name TEXT UNIQUE -- Name of file page
);
-- Linkages between checkins, files created by each checkin, and
-- the names of those files.
--
-- pid==0 if the file is added by checkin mid.
-- fid==0 if the file is removed by checkin mid.
--
CREATE TABLE mlink(
mid INTEGER REFERENCES blob, -- Manifest ID where change occurs
pid INTEGER REFERENCES blob, -- File ID in parent manifest
fid INTEGER REFERENCES blob, -- Changed file ID in this manifest
fnid INTEGER REFERENCES filename, -- Name of the file
pfnid INTEGER REFERENCES filename, -- Previous name. 0 if unchanged
mperm INTEGER -- File permissions. 1==exec
);
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 checkins
--
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.
UNIQUE(pid, cid)
);
CREATE INDEX plink_i2 ON plink(cid,pid);
-- A "leaf" checkin is a checkin 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 checkins)
-- 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 manifests 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'); -- FSL_TAGID_BGCOLOR
INSERT INTO tag VALUES(2, 'comment'); -- FSL_TAGID_COMMENT
INSERT INTO tag VALUES(3, 'user'); -- FSL_TAGID_USER
INSERT INTO tag VALUES(4, 'date'); -- FSL_TAGID_DATE
INSERT INTO tag VALUES(5, 'hidden'); -- FSL_TAGID_HIDDEN
INSERT INTO tag VALUES(6, 'private'); -- FSL_TAGID_PRIVATE
INSERT INTO tag VALUES(7, 'cluster'); -- FSL_TAGID_CLUSTER
INSERT INTO tag VALUES(8, 'branch'); -- FSL_TAGID_BRANCH
INSERT INTO tag VALUES(9, 'closed'); -- FSL_TAGID_CLOSED
INSERT INTO tag VALUES(10,'parent'); -- FSL_TAGID_PARENT
-- arguable, to force auto-increment to start at 17:
-- INSERT INTO tag VALUES(16,'FSL_TAGID_MAX_INTERNAL');
-- 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 was 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 checkin 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);