--- Catalogs
-create table catalogs (
- id serial,
- title text not null,
- path text,
- date timestamp not null default now(),
- primary key(id)
-);
+begin;
-create table catalog_webarchive (
- uri text not null, -- unique index
- last_crawled timestamp,
- primary key(id)
-) inherits (catalogs) ;
+create table item_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 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_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 topic_types(
+ name text not null,
+ primary key (name)
);
--- Pg General Bits entries
-create table entries_pgbits (
- issue int not null, -- unique index
+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)
-) inherits (entries) ;
+);
--- Items for each Entry
-create table items (
- id serial,
- title text,
- entry_id int references entries(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)
+);
- i_type text not null,
- date timestamp not null default now(),
+-- create inhertited topics and items
+
+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(),
+-- 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) ;