report missing marc_remove

[webpac2] / sql / schema.sql

diff --git a/sql/schema.sql b/sql/schema.sql
index e1d728d..a026530 100644 (file)
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1,76 +1,66 @@
--- Catalogs
-create table catalogs (
-       id      serial,
-       title   text not null,
-       path    text,
-       date    timestamp not null default now(),
-       primary key(id)
+begin;
+
+create table item_types(
+       name            text not null,
+       primary         key (name)
 );
 
-create table catalog_webarchive (
-       uri     text not null,                  -- unique index
-       last_crawled timestamp,
+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)
-) 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)
 );
 
--- Items for each Entry
-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,
        primary key(id)
 ) inherits (items) ;
 
--- Tags for Entries
-create table tags (
-       id      serial,
-       title   text,                   -- index
-       date timestamp not null default now(),
-       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)
-);
-
 -- Pg General Bits
-create table entries_pgbits (
+insert into topic_types values ('pgbits');
+create table topics_pgbits (
        issue   int not null,                   -- unique index
        primary key(id)
-) inherits (entries) ;
+) inherits (topics) ;
 
+insert into item_types values ('pgbits');
 create table items_pgbits (
        mytitle text not null,
        ititle  text not null,
@@ -79,15 +69,3 @@ create table items_pgbits (
        contributors text,
        primary key(id)
 ) inherits (items) ;
-
--- some views
-
-create view pgbits_articles as select
-       issue,
-       entries_pgbits.date as issue_date,
-       ititle,
-       mytitle,
-       items_pgbits.date as date
-from items_pgbits
-join entries_pgbits on entry_id = entries_pgbits.id ;
-