-- 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 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);
