BEGIN TRANSACTION;
CREATE TABLE system (
    name text PRIMARY KEY,
    value text
);
INSERT INTO "system" VALUES('database_version', '20');
INSERT INTO "system" VALUES('youngest_rev', '');
CREATE TABLE permission (
    username text,
    action text,
    UNIQUE (username,action)
);
INSERT INTO "permission" VALUES('anonymous', 'LOG_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'FILE_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'WIKI_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'WIKI_CREATE');
INSERT INTO "permission" VALUES('anonymous', 'WIKI_MODIFY');
INSERT INTO "permission" VALUES('anonymous', 'SEARCH_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'REPORT_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'REPORT_SQL_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'TICKET_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'TICKET_CREATE');
INSERT INTO "permission" VALUES('anonymous', 'TICKET_MODIFY');
INSERT INTO "permission" VALUES('anonymous', 'BROWSER_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'TIMELINE_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'CHANGESET_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'ROADMAP_VIEW');
INSERT INTO "permission" VALUES('anonymous', 'MILESTONE_VIEW');
CREATE TABLE auth_cookie (
    cookie text,
    name text,
    ipnr text,
    time integer,
    UNIQUE (cookie,ipnr,name)
);
INSERT INTO "auth_cookie" VALUES('3fa6b59c681d68a20307714a12a02128', 'thomas', '127.0.0.1', 1188854070);
INSERT INTO "auth_cookie" VALUES('6d26d7f4e896158b83d8aed95ad66609', 'god', '127.0.0.1', 1188854674);
CREATE TABLE session (
    sid text,
    authenticated integer,
    last_visit integer,
    UNIQUE (sid,authenticated)
);
INSERT INTO "session" VALUES('thomas', 1, 1188853944);
INSERT INTO "session" VALUES('god', 1, 1188854674);
CREATE TABLE session_attribute (
    sid text,
    authenticated integer,
    name text,
    value text,
    UNIQUE (sid,authenticated,name)
);
CREATE TABLE attachment (
    type text,
    id text,
    filename text,
    size integer,
    time integer,
    description text,
    author text,
    ipnr text,
    UNIQUE (type,id,filename)
);
CREATE TABLE wiki (
    name text,
    version integer,
    time integer,
    author text,
    ipnr text,
    text text,
    comment text,
    readonly integer,
    UNIQUE (name,version)
);
INSERT INTO "wiki" VALUES('WikiStart', 1, 1188853681, 'trac', '127.0.0.1', '= Welcome to Trac 0.10.4 =

Trac is a ''''''minimalistic'''''' approach to ''''''web-based'''''' management of
''''''software projects''''''. Its goal is to simplify effective tracking and handling of software issues, enhancements and overall progress.

All aspects of Trac have been designed with the single goal to 
''''''help developers write great software'''''' while ''''''staying out of the way''''''
and imposing as little as possible on a team''s established process and
culture.

As all Wiki pages, this page is editable, this means that you can
modify the contents of this page simply by using your
web-browser. Simply click on the "Edit this page" link at the bottom
of the page. WikiFormatting will give you a detailed description of
available Wiki formatting commands.

"[wiki:TracAdmin trac-admin] ''''yourenvdir'''' initenv" created
a new Trac environment, containing a default set of wiki pages and some sample
data. This newly created environment also contains 
[wiki:TracGuide documentation] to help you get started with your project.

You can use [wiki:TracAdmin trac-admin] to configure
[http://trac.edgewall.org/ Trac] to better fit your project, especially in
regard to ''''components'''', ''''versions'''' and ''''milestones''''. 


TracGuide is a good place to start.

Enjoy! [[BR]]
''''The Trac Team''''

== Starting Points ==

 * TracGuide --  Built-in Documentation
 * [http://trac.edgewall.org/ The Trac project] -- Trac Open Source Project
 * [http://trac.edgewall.org/wiki/TracFaq Trac FAQ] -- Frequently Asked Questions
 * TracSupport --  Trac Support

For a complete list of local wiki pages, see TitleIndex.
', NULL, NULL);
INSERT INTO "wiki" VALUES('WikiStart', 2, 1188854016.73276, 'thomas', '127.0.0.1', 'I didn''t like this page so I deleted it.', '', 0);
INSERT INTO "wiki" VALUES('WikiStart', 3, 1188854758.57867, 'god', '127.0.0.1', '== The World ==

 * Seas
   * Rivers
 * Mountains
   * Caves
 * Forest
   * Trees
   * Bunnies
', '', 0);
CREATE TABLE revision (
    rev text PRIMARY KEY,
    time integer,
    author text,
    message text
);
CREATE TABLE node_change (
    rev text,
    path text,
    node_type text,
    change_type text,
    base_path text,
    base_rev text,
    UNIQUE (rev,path,change_type)
);
CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);
INSERT INTO "ticket" VALUES(1, 'defect', 1188853968, 1188854721, 'component1', NULL, 'trivial', 'gabriel', 'thomas', 'god,thomas', '', 'milestone2', 'new', NULL, 'This ticket is owned by thomas', 'Yes it is', '');
INSERT INTO "ticket" VALUES(2, 'task', 1188854696, 1188854696, 'component1', NULL, 'major', 'god', 'god', '', '', '', 'new', NULL, 'create the world in six days', 'maybe I could rest on day 7 ?', '');
CREATE TABLE ticket_change (
    ticket integer,
    time integer,
    author text,
    field text,
    oldvalue text,
    newvalue text,
    UNIQUE (ticket,time,field)
);
INSERT INTO "ticket_change" VALUES(1, 1188854051, 'thomas', 'priority', 'major', 'trivial');
INSERT INTO "ticket_change" VALUES(1, 1188854051, 'thomas', 'milestone', '', 'milestone2');
INSERT INTO "ticket_change" VALUES(1, 1188854051, 'thomas', 'comment', '1', 'It''s not an important ticket.');
INSERT INTO "ticket_change" VALUES(1, 1188854721, 'god', 'comment', '2', 'I''m not sure I want my angel to work on this.  Can''t you do it instead ?');
CREATE TABLE ticket_custom (
    ticket integer,
    name text,
    value text,
    UNIQUE (ticket,name)
);
CREATE TABLE enum (
    type text,
    name text,
    value text,
    UNIQUE (type,name)
);
INSERT INTO "enum" VALUES('status', 'new', '1');
INSERT INTO "enum" VALUES('status', 'assigned', '2');
INSERT INTO "enum" VALUES('status', 'reopened', '3');
INSERT INTO "enum" VALUES('status', 'closed', '4');
INSERT INTO "enum" VALUES('resolution', 'fixed', '1');
INSERT INTO "enum" VALUES('resolution', 'invalid', '2');
INSERT INTO "enum" VALUES('resolution', 'wontfix', '3');
INSERT INTO "enum" VALUES('resolution', 'duplicate', '4');
INSERT INTO "enum" VALUES('resolution', 'worksforme', '5');
INSERT INTO "enum" VALUES('priority', 'blocker', '1');
INSERT INTO "enum" VALUES('priority', 'critical', '2');
INSERT INTO "enum" VALUES('priority', 'major', '3');
INSERT INTO "enum" VALUES('priority', 'minor', '4');
INSERT INTO "enum" VALUES('priority', 'trivial', '5');
INSERT INTO "enum" VALUES('ticket_type', 'defect', '1');
INSERT INTO "enum" VALUES('ticket_type', 'enhancement', '2');
INSERT INTO "enum" VALUES('ticket_type', 'task', '3');
CREATE TABLE component (
    name text PRIMARY KEY,
    owner text,
    description text
);
INSERT INTO "component" VALUES('component1', 'somebody', NULL);
INSERT INTO "component" VALUES('component2', 'somebody', NULL);
CREATE TABLE milestone (
    name text PRIMARY KEY,
    due integer,
    completed integer,
    description text
);
INSERT INTO "milestone" VALUES('milestone1', 0, 0, NULL);
INSERT INTO "milestone" VALUES('milestone2', 0, 0, NULL);
INSERT INTO "milestone" VALUES('milestone3', 0, 0, NULL);
INSERT INTO "milestone" VALUES('milestone4', 0, 0, NULL);
CREATE TABLE version (
    name text PRIMARY KEY,
    time integer,
    description text
);
INSERT INTO "version" VALUES('1.0', 0, NULL);
INSERT INTO "version" VALUES('2.0', 0, NULL);
CREATE TABLE report (
    id integer PRIMARY KEY,
    author text,
    title text,
    query text,
    description text
);
INSERT INTO "report" VALUES(1, NULL, 'Active Tickets', '
SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status IN (''new'', ''assigned'', ''reopened'') 
  ORDER BY p.value, milestone, t.type, time
', '
 * List all active tickets by priority.
 * Color each row based on priority.
 * If a ticket has been accepted, a ''*'' is appended after the owner''s name
');
INSERT INTO "report" VALUES(2, NULL, 'Active Tickets by Version', '
SELECT p.value AS __color__,
   version AS __group__,
   id AS ticket, summary, component, version, t.type AS type, 
   (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status IN (''new'', ''assigned'', ''reopened'') 
  ORDER BY (version IS NULL),version, p.value, t.type, time
', '
This report shows how to color results by priority,
while grouping results by version.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.
');
INSERT INTO "report" VALUES(3, NULL, 'Active Tickets by Milestone', '
SELECT p.value AS __color__,
   milestone||'' Release'' AS __group__,
   id AS ticket, summary, component, version, t.type AS type, 
   (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status IN (''new'', ''assigned'', ''reopened'') 
  ORDER BY (milestone IS NULL),milestone, p.value, t.type, time
', '
This report shows how to color results by priority,
while grouping results by milestone.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.
');
INSERT INTO "report" VALUES(4, NULL, 'Assigned, Active Tickets by Owner', '

SELECT p.value AS __color__,
   owner AS __group__,
   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status = ''assigned''
  ORDER BY owner, p.value, t.type, time
', '
List assigned tickets, group by ticket owner, sorted by priority.
');
INSERT INTO "report" VALUES(5, NULL, 'Assigned, Active Tickets by Owner (Full Description)', '
SELECT p.value AS __color__,
   owner AS __group__,
   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
   description AS _description_,
   changetime AS _changetime, reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status = ''assigned''
  ORDER BY owner, p.value, t.type, time
', '
List tickets assigned, group by ticket owner.
This report demonstrates the use of full-row display.
');
INSERT INTO "report" VALUES(6, NULL, 'All Tickets By Milestone  (Including closed)', '
SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN ''closed'' THEN ''color: #777; background: #ddd; border-color: #ccc;''
      ELSE 
        (CASE owner WHEN $USER THEN ''font-weight: bold'' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  ORDER BY (milestone IS NULL), milestone DESC, (status = ''closed''), 
        (CASE status WHEN ''closed'' THEN modified ELSE (-1)*p.value END) DESC
', '
A more complex example to show how to make advanced reports.
');
INSERT INTO "report" VALUES(7, NULL, 'My Tickets', '
SELECT p.value AS __color__,
   (CASE status WHEN ''assigned'' THEN ''Assigned'' ELSE ''Owned'' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE t.status IN (''new'', ''assigned'', ''reopened'') AND owner = $USER
  ORDER BY (status = ''assigned'') DESC, p.value, milestone, t.type, time
', '
This report demonstrates the use of the automatically set 
USER dynamic variable, replaced with the username of the
logged in user when executed.
');
INSERT INTO "report" VALUES(8, NULL, 'Active Tickets, Mine first', '
SELECT p.value AS __color__,
   (CASE owner 
     WHEN $USER THEN ''My Tickets'' 
     ELSE ''Active Tickets'' 
    END) AS __group__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = ''priority''
  WHERE status IN (''new'', ''assigned'', ''reopened'') 
  ORDER BY (owner = $USER) DESC, p.value, milestone, t.type, time
', '
 * List all active tickets by priority.
 * Show all tickets owned by the logged in user in a group first.
');
CREATE INDEX session_last_visit_idx ON session (last_visit);
CREATE INDEX session_authenticated_idx ON session (authenticated);
CREATE INDEX wiki_time_idx ON wiki (time);
CREATE INDEX revision_time_idx ON revision (time);
CREATE INDEX node_change_rev_idx ON node_change (rev);
CREATE INDEX ticket_time_idx ON ticket (time);
CREATE INDEX ticket_status_idx ON ticket (status);
CREATE INDEX ticket_change_ticket_idx ON ticket_change (ticket);
CREATE INDEX ticket_change_time_idx ON ticket_change (time);
COMMIT;