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	-- 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	primary key(id)
14	) 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	issue int not null, -- unique index
35	primary key(id)
36	) inherits (entries) ;
37
38	-- Items for each Entry
39	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	size int,
54	primary key(id)
55	) 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