trunk/sql/schema.sql

-- Catalogs
create table catalogs (
        id      serial,
        title   text not null,
        path    text,
        date    timestamp not null default now(),
        primary key(id)
);

create table catalog_webarchive (
        uri     text not null,                  -- unique index
        last_crawled timestamp,
        primary key(id)
) inherits (catalogs) ;

-- Entries in Catalog
create table entries (
        id      serial,
        title   text,
        path    text,
        date    timestamp not null default now(),
        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)
);

-- Pg General Bits entries
create table entries_pgbits (
        issue   int not null,                   -- unique index
        primary key(id)
) inherits (entries) ;

-- 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(),
        primary key(id)
);

-- HyperEstraier support table
create table items_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)
);

1	dpavlin	24	-- Catalogs
2			create table catalogs (
3			id serial,
4			title text not null,
5			path text,
6			date timestamp not null default now(),
7			primary key(id)
8			);
9
10			create table catalog_webarchive (
11			uri text not null, -- unique index
12	dpavlin	26	last_crawled timestamp,
13			primary key(id)
14	dpavlin	24	) inherits (catalogs) ;
15
16			-- Entries in Catalog
17			create table entries (
18			id serial,
19			title text,
20			path text,
21			date timestamp not null default now(),
22			primary key(id)
23			);
24
25			create table catalog_entry (
26			catalog_id int references catalogs(id),
27			entry_id int references entries(id),
28			e_type text not null, -- index
29			primary key (catalog_id, entry_id)
30			);
31
32			-- Pg General Bits entries
33			create table entries_pgbits (
34	dpavlin	26	issue int not null, -- unique index
35			primary key(id)
36	dpavlin	24	) inherits (entries) ;
37
38	dpavlin	26	-- Items for each Entry
39	dpavlin	24	create table items (
40			id serial,
41			title text,
42			entry_id int references entries(id),
43
44			i_type text not null,
45			date timestamp not null default now(),
46			primary key(id)
47			);
48
49			-- HyperEstraier support table
50			create table items_est (
51			path text, -- unique index
52			uri text not null, -- unique index
53	dpavlin	26	size int,
54			primary key(id)
55	dpavlin	24	) inherits (items) ;
56
57			-- Tags for Entries
58			create table tags (
59			id serial,
60			title text, -- index
61			date timestamp not null default now(),
62			primary key(id)
63			);
64
65			create table entry_tag (
66			entry_id int references entries(id),
67			tag_id int references tags(id),
68			value text not null,
69			t_type text not null, -- index
70			date timestamp not null default now(),
71			primary key (entry_id, tag_id)
72			);
73