CREATE TABLE desire (
   id integer      primary key,
   date_created    timestamp,
   date_modified   timestamp,
   satisfied       boolean,
   claimed         boolean,
   title           varchar2(256),
   details         varchar2(65000)
);
CREATE TABLE record_stimulus (
   id_record integer,
   id_desire integer,
   date_created timestamp
);
CREATE TABLE desire_context (
   id_desire integer,
   id_record integer,
   date_created timestamp
);
CREATE TABLE record_subsumption (
   id_parent integer,
   id_child  integer,
   date_created timestamp
);

------------------------------------------------------------------
-- subsumed, conclusion, and closed
-- were all nonsense and never really amounted to anything.
------------------------------------------------------------------
CREATE TABLE record (
   id integer      primary key,
   subsumed        boolean,
   conclusion      varchar2(256),
   date_created    timestamp,
   date_modified   timestamp,
   closed          boolean,
   title           varchar2(256),
   details         varchar2(256000)
);


CREATE TABLE worked_on (
   id_record integer,
   date_modified timestamp
);
CREATE TABLE goal_stack (
   id integer primary key,
   order_stamp integer unsigned,
   thing_t varchar( 16 )
);
CREATE TABLE wiki_irec_connection (
  id_record integer,
  wikiName varchar2(64),
  date_modified timestamp,
  primary key (id_record, wikiName)
);
CREATE TABLE irec_rcs_tracking (
   id integer      primary key,
   current_rev integer
);
CREATE TABLE comment (
   record_id integer,
   purple_section integer,
   purple_rev     integer,
   details         varchar2(4096),
   date_created    timestamp
, submitter varchar(32));
CREATE TABLE public_comment (
   record_id integer,
   purple_section integer,
   purple_rev     integer,
   details         varchar2(4096),
   date_created    timestamp,
   submitter       varchar(32)
);
CREATE TABLE citation( 
       id integer,
       
       id_cited integer, 
       cited_version integer, 
       cited_nid integer,
       
       id_from integer,
       from_version integer,
       from_nid integer,

       date_modified timestamp
       );



-- record updates would be eliminated by the use of a "patch" database,
-- since each the date of each update would correspond to a patch entry.
CREATE TABLE record_updates(
       id_record   integer,
       new_version integer,
       date_created timestamp
);


CREATE TABLE reconcile(
       id_cited integer,
       id_from integer,
       id      integer,
       explanation varchar(256),
       reconciled boolean
);
CREATE TABLE comment_nid_diff(
  id_comment integer,
  record_rev integer,
  nid_offset integer,
  primary key( id_comment, record_rev )
);
CREATE INDEX ndx_desire_date_modified on desire ( date_modified );
CREATE INDEX ndx_record_date_modified on record ( date_modified );
CREATE INDEX ndx_comment_recid on comment( record_id );
CREATE INDEX ndx_pubcomment_recid on public_comment( record_id );