--- trunk/sql/schema.sql	2005/08/02 00:42:29	36
+++ trunk/sql/schema.sql	2005/08/02 15:20:44	37
@@ -1,75 +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) ;
+);
 
--- Entries in Catalog
-create table entries (
-	id	serial,
-	title	text,
-	path	text,
-	date	timestamp not null default now(),
+create table topic_types(
+	name		text not null,
+	primary key (name)
+);
+
+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),
-	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(),
-	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,
@@ -78,15 +69,3 @@
 	contributors text,
 	primary key(id)
 ) inherits (items) ;
-
--- some views
-
-create view pgbits_articles as select
-	issue,
-	date(entries_pgbits.date) as issue_date,
-	ititle,
-	mytitle,
-	date(items_pgbits.date) as date
-from items_pgbits
-join entries_pgbits on entry_id = entries_pgbits.id ;
-