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