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