trunk/sql/schema.sql

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 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 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)
);

-- 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
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) ;

-- Pg General Bits
insert into topic_types values ('pgbits');
create table topics_pgbits (
        issue   int not null,                   -- unique index
        primary key(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) ;
1	begin;
2
3	create table item_types(
4	name text not null,
5	primary key (name)
6	);
7
8	create table items (
9	id SERIAL,
10	name text not null,
11	path text, -- index
12	date timestamp not null default now(),
13	type text not null references item_types(name), -- index
14	primary key(id)
15	);
16
17	create table topic_types(
18	name text not null,
19	primary key (name)
20	);
21
22	create table topics (
23	id SERIAL,
24	name text,
25	path text,
26	date timestamp not null default now(),
27	type text not null references topic_types(name), -- index
28	parent_id integer references topics(id), -- index
29	primary key(id)
30	);
31
32	create table item_topics (
33	item_id integer references items(id) ON UPDATE CASCADE ON DELETE CASCADE,
34	topic_id integer references topics(id) ON UPDATE CASCADE ON DELETE CASCADE,
35	PRIMARY KEY (item_id, topic_id)
36	);
37
38	-- create inhertited topics and items
39
40	insert into topic_types values ('webarchive');
41	create table topics_webarchive (
42	uri text not null, -- unique index
43	last_crawled timestamp,
44	primary key(id)
45	) inherits (topics) ;
46
47	-- HyperEstraier support table
48	insert into item_types values ('est');
49	create table item_est (
50	path text, -- unique index
51	uri text not null, -- unique index
52	size int,
53	primary key(id)
54	) inherits (items) ;
55
56	-- Pg General Bits
57	insert into topic_types values ('pgbits');
58	create table topics_pgbits (
59	issue int not null, -- unique index
60	primary key(id)
61	) inherits (topics) ;
62
63	insert into item_types values ('pgbits');
64	create table items_pgbits (
65	mytitle text not null,
66	ititle text not null,
67	ikey text,
68	html text,
69	contributors text,
70	primary key(id)
71	) inherits (items) ;