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

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

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

create table items_pgbits (
        mytitle text not null,
        ititle  text not null,
        ikey    text,
        html    text,
        contributors text,
        primary key(id)
) inherits (items) ;

-- some views

create view pgbits_articles as select
        issue,
        date(entries_pgbits.date) as issue_date,
        ititle,
        mytitle,
        date(items_pgbits.date) as date
from items_pgbits
join entries_pgbits on entry_id = entries_pgbits.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	dpavlin	26	-- Items for each Entry
33	dpavlin	24	create table items (
34			id serial,
35			title text,
36			entry_id int references entries(id),
37
38			i_type text not null,
39			date timestamp not null default now(),
40			primary key(id)
41			);
42
43			-- HyperEstraier support table
44			create table items_est (
45			path text, -- unique index
46			uri text not null, -- unique index
47	dpavlin	26	size int,
48			primary key(id)
49	dpavlin	24	) inherits (items) ;
50
51			-- Tags for Entries
52			create table tags (
53			id serial,
54			title text, -- index
55			date timestamp not null default now(),
56			primary key(id)
57			);
58
59			create table entry_tag (
60			entry_id int references entries(id),
61			tag_id int references tags(id),
62			value text not null,
63			t_type text not null, -- index
64			date timestamp not null default now(),
65			primary key (entry_id, tag_id)
66			);
67
68	dpavlin	27	-- Pg General Bits
69			create table entries_pgbits (
70			issue int not null, -- unique index
71			primary key(id)
72			) inherits (entries) ;
73
74			create table items_pgbits (
75			mytitle text not null,
76			ititle text not null,
77			ikey text,
78			html text,
79			contributors text,
80			primary key(id)
81			) inherits (items) ;
82
83			-- some views
84
85			create view pgbits_articles as select
86			issue,
87	dpavlin	28	date(entries_pgbits.date) as issue_date,
88	dpavlin	27	ititle,
89			mytitle,
90	dpavlin	28	date(items_pgbits.date) as date
91	dpavlin	27	from items_pgbits
92			join entries_pgbits on entry_id = entries_pgbits.id ;
93