projects
/
webpac2
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
report missing marc_remove
[webpac2]
/
sql
/
schema.sql
diff --git
a/sql/schema.sql
b/sql/schema.sql
index
2678879
..
a026530
100644
(file)
--- a/
sql/schema.sql
+++ b/
sql/schema.sql
@@
-1,70
+1,71
@@
--- Catalogs
-create table catalogs (
- id serial,
- title text not null,
- path text,
- date timestamp not null default now(),
+begin;
+
+create table item_types(
+ name text not null,
+ primary key (name)
+);
+
+create table items (
+ id SERIAL,
+ name text not null,
+ path text, -- index
+ date timestamp not null default now(),
+ type text not null references item_types(name), -- index
primary key(id)
);
primary key(id)
);
-create table
catalog_webarchive
(
- uri text not null, -- unique index
- last_crawled timestamp
-)
inherits (catalogs)
;
+create table
topic_types
(
+ name text not null,
+ primary key (name)
+);
--- Entries in Catalog
-create table entries (
- id serial,
- title text,
- path text,
- date timestamp not null default now(),
+create table topics (
+ id SERIAL,
+ name text,
+ path text,
+ date timestamp not null default now(),
+ type text not null references topic_types(name), -- index
+ parent_id integer references topics(id), -- index
primary key(id)
);
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)
+create table item_topics (
+ item_id integer references items(id) ON UPDATE CASCADE ON DELETE CASCADE,
+ topic_id integer references topics(id) ON UPDATE CASCADE ON DELETE CASCADE,
+ PRIMARY KEY (item_id, topic_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),
+-- create inhertited topics and items
- i_type text not null,
- date timestamp not null default now(),
+insert into topic_types values ('webarchive');
+create table topics_webarchive (
+ uri text not null, -- unique index
+ last_crawled timestamp,
primary key(id)
primary key(id)
-);
+)
inherits (topics)
;
-- HyperEstraier support table
-- HyperEstraier support table
-create table items_est (
+insert into item_types values ('est');
+create table item_est (
path text, -- unique index
uri text not null, -- unique index
path text, -- unique index
uri text not null, -- unique index
- size int
+ size int,
+ primary key(id)
) inherits (items) ;
) inherits (items) ;
--- Tags for Entries
-create table tags (
- id serial,
- title text, -- index
- date timestamp not null default now(),
+-- Pg General Bits
+insert into topic_types values ('pgbits');
+create table topics_pgbits (
+ issue int not null, -- unique index
primary key(id)
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)
-);
+) inherits (topics) ;
+insert into item_types values ('pgbits');
+create table items_pgbits (
+ mytitle text not null,
+ ititle text not null,
+ ikey text,
+ html text,
+ contributors text,
+ primary key(id)
+) inherits (items) ;