report missing marc_remove

[webpac2] / sql / schema.sql

diff --git a/sql/schema.sql b/sql/schema.sql
index 2678879..a026530 100644 (file)
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1,70 +1,71 @@
--- Catalogs
-create table catalogs (
-       id      serial,
-       title   text not null,
-       path    text,
-       date    timestamp not null default now(),
+begin;
+
+create table item_types(
+       name            text not null,
+       primary         key (name)
+);
+
+create table items (
+       id              SERIAL,
+       name            text not null,
+       path            text,                                           -- index
+       date            timestamp not null default now(),
+       type            text not null references item_types(name),      -- index
        primary key(id)
 );
 
-create table catalog_webarchive (
-       uri     text not null,                  -- unique index
-       last_crawled timestamp
-) inherits (catalogs) ;
+create table topic_types(
+       name            text not null,
+       primary key (name)
+);
 
--- Entries in Catalog
-create table entries (
-       id      serial,
-       title   text,
-       path    text,
-       date    timestamp not null default now(),
+create table topics (
+       id              SERIAL,
+       name            text,
+       path            text,
+       date            timestamp not null default now(),
+       type            text not null references topic_types(name),     -- index
+       parent_id       integer references topics(id),                  -- index
        primary key(id)
 );
 
-create table catalog_entry (
-       catalog_id int references catalogs(id),
-       entry_id int references entries(id),
-       e_type text not null,                   -- index
-       primary key (catalog_id, entry_id)
+create table item_topics (
+       item_id         integer references items(id) ON UPDATE CASCADE ON DELETE CASCADE,
+       topic_id        integer references topics(id) ON UPDATE CASCADE ON DELETE CASCADE,
+       PRIMARY KEY (item_id, topic_id)
 );
 
--- Pg General Bits entries
-create table entries_pgbits (
-       issue   int not null                    -- unique index
-) inherits (entries) ;
-
--- Items in Entries
-create table items (
-       id      serial,
-       title   text,
-       entry_id int references entries(id),
+-- create inhertited topics and items
 
-       i_type  text not null,
-       date timestamp not null default now(),
+insert into topic_types values ('webarchive');
+create table topics_webarchive (
+       uri     text not null,                  -- unique index
+       last_crawled timestamp,
        primary key(id)
-);
+) inherits (topics) ;
 
 -- HyperEstraier support table
-create table items_est (
+insert into item_types values ('est');
+create table item_est (
        path    text,                   -- unique index
        uri     text not null,          -- unique index
-       size    int
+       size    int,
+       primary key(id)
 ) inherits (items) ;
 
--- Tags for Entries
-create table tags (
-       id      serial,
-       title   text,                   -- index
-       date timestamp not null default now(),
+-- Pg General Bits
+insert into topic_types values ('pgbits');
+create table topics_pgbits (
+       issue   int not null,                   -- unique index
        primary key(id)
-);
-
-create table entry_tag (
-       entry_id int references entries(id),
-       tag_id int references tags(id),
-       value text not null,
-       t_type text not null,           -- index
-       date timestamp not null default now(),
-       primary key (entry_id, tag_id)
-);
+) inherits (topics) ;
 
+insert into item_types values ('pgbits');
+create table items_pgbits (
+       mytitle text not null,
+       ititle  text not null,
+       ikey    text,
+       html    text,
+       contributors text,
+       primary key(id)
+) inherits (items) ;