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	dpavlin	37	begin;
2
3			create table item_types(
4			name text not null,
5			primary key (name)
6	dpavlin	24	);
7
8	dpavlin	37	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	dpavlin	26	primary key(id)
15	dpavlin	37	);
16	dpavlin	24
17	dpavlin	37	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	dpavlin	24	primary key(id)
30			);
31
32	dpavlin	37	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	dpavlin	24	);
37
38	dpavlin	37	-- 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	dpavlin	24	primary key(id)
45	dpavlin	37	) inherits (topics) ;
46	dpavlin	24
47			-- HyperEstraier support table
48	dpavlin	37	insert into item_types values ('est');
49			create table item_est (
50	dpavlin	24	path text, -- unique index
51			uri text not null, -- unique index
52	dpavlin	26	size int,
53			primary key(id)
54	dpavlin	24	) inherits (items) ;
55
56	dpavlin	27	-- Pg General Bits
57	dpavlin	37	insert into topic_types values ('pgbits');
58			create table topics_pgbits (
59	dpavlin	27	issue int not null, -- unique index
60			primary key(id)
61	dpavlin	37	) inherits (topics) ;
62	dpavlin	27
63	dpavlin	37	insert into item_types values ('pgbits');
64	dpavlin	27	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) ;