#!tclsh # # Run this script to create two database files "docinfo.db" and "historical.db" # with appropriate schemas. # sqlite3 db docinfo.db db eval { BEGIN; /* ** Every page in the documentation set is a row in the following table: */ CREATE TABLE IF NOT EXISTS page( pageid INTEGER PRIMARY KEY, -- ID for internal use only pagetitle TEXT, -- Brief title of the page pageabstract TEXT, -- Verbose title of the page filename TEXT UNIQUE NOT NULL, -- Name of the HTML file parent INTEGER REFERENCES page -- Aggregate page. Usually NULL ); CREATE INDEX IF NOT EXISTS page_parent ON page(parent); /* ** Each page has one or more fragments. The first fragment has ** a NULL name. All other fragments are named. */ CREATE TABLE IF NOT EXISTS fragment( fragid INTEGER PRIMARY KEY, -- ID for internal use only pageid INTEGER NOT NULL REFERENCES page, -- Part of this page fragname TEXT NOT NULL, -- Name or NULL for main fragment fragtitle TEXT -- Optional title for this fragment ); CREATE INDEX IF NOT EXISTS fragment_pageid ON fragment(pageid); /* ** Keywords used to identify link targets. */ CREATE TABLE IF NOT EXISTS keyword( kwid INTEGER PRIMARY KEY, -- ID for internal use only kw TEXT UNIQUE NOT NULL COLLATE nocase, -- The keyword or keyphrase indexKw BOOLEAN NOT NULL, -- Show in the keyword doc index fragment TEXT -- Fragment keyword refers to ); /* ** Each row in this table records a hyperlink to a keyword. The ** source and destination of the hyperlink are recorded. */ CREATE TABLE IF NOT EXISTS link( fromfrag INTEGER REFERENCES fragment, -- From this fragment tokw INTEGER REFERENCES keyword -- To this keyword ); CREATE INDEX IF NOT EXISTS link_from ON link(fromfrag); CREATE INDEX IF NOT EXISTS link_to ON link(tokw); /* Requirements or Testable Statements Of Truth (Tsots). ** These are extracts from the documentation that define what ** the product does and how it performs. */ CREATE TABLE IF NOT EXISTS requirement( rid INTEGER PRIMARY KEY, -- Requirement ID for internal use only reqno TEXT UNIQUE, -- Ex: R-12345-67890-... reqimage BOOLEAN, -- True for an image requirement reqtext TEXT, -- Normalized text of requirement or image filename origtext TEXT, -- Original, unnormalized text srcfile TEXT, -- Document from which first extracted srcseq INTEGER, -- Sequence within the same document UNIQUE(srcfile,srcseq) ); /* The source text for a requirement. Some requirements (especially ** syntax diagram images) can occur in multiple places. */ CREATE TABLE IF NOT EXISTS reqsrc( srcfile TEXT, -- Document from which extracted srcseq INTEGER, -- Sequence within the same document reqno TEXT, -- The requirement that is repeated PRIMARY KEY(srcfile,srcseq) ) WITHOUT ROWID; /* Image requirements can appears at multiple places in the /* Evidence of fulfillment of a requirement is recorded in this ** table. */ CREATE TABLE IF NOT EXISTS evidence( reqno TEXT, -- Prefix of a requirement number reqtext TEXT, -- Normalized requirement text taken from source evtype TEXT, -- evidence, implementation, assert, testcase srcclass TEXT, -- source class: tcl, th3, src srccat TEXT, -- source category. Ex: tcl, th3/cov1 srcfile TEXT, -- document from which evidence extracted srcline INTEGER, -- line number in source document url TEXT, -- URL & fragment of htmlized evidence UNIQUE(srcfile, srcline, srccat) ); CREATE INDEX ev_reqno ON evidence(reqno); COMMIT; } db eval { ATTACH 'history.db' AS history; BEGIN; CREATE TABLE IF NOT EXISTS history.allreq( reqno TEXT PRIMARY KEY, -- Ex: R-12345-67890-... reqimage BOOLEAN, -- True for an image requirement reqtext TEXT, -- Normalized text of requirement or image filename srcfile TEXT -- Document from which extracted ); REPLACE INTO history.allreq SELECT reqno, reqimage, reqtext, srcfile FROM requirement; COMMIT; } db close