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
) 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
) inherits (entries) ;

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