source: bookmarks/trunk/bookmarks.sql

Last change on this file was 73, checked in by peter, 11 years ago

create the resources table first, so that the foreign keys in the bookmarks and tags tables can correctly reference it

File size: 1.8 KB
Line 
1drop table if exists tags;
2drop table if exists resources;
3drop table if exists bookmarks;
4
5-- resources by URI
6-- this is where we would store information about the resource,
7-- such as its title, last known status, etc.
8create table resources (
9    uri varchar primary key,
10    title varchar -- URI title (e.g., page title)
11);
12
13-- bookmarks of resources
14-- each resource can only have one bookmark
15-- bookmarks have creation and modification times
16create table bookmarks (
17    id integer primary key,
18    uri varchar references resources(uri) on update cascade,
19    ctime integer, -- creation time of the bookmark
20    mtime integer,  -- modification time of the bookmark
21    constraint unique_uri unique (uri)
22);
23
24/* old resources table
25create table resources (
26    uri varchar primary key,
27    title varchar, -- URI title (e.g., page title)
28    ctime integer, -- creation time of the bookmark
29    mtime integer  -- modification time of the bookmark
30);
31*/
32
33-- tags that describe the resource
34-- TODO: machine-tag style tags? e.g. format:video or creator:NASA; implicit tag prefix is "subject:"
35create table tags (
36    uri varchar references resources(uri) on update cascade,
37    tag varchar,
38    constraint unique_tag primary key (uri, tag)
39);
40
41/*
42create a new resource:
43insert into resources (uri, title) values ('http://echodin.net/', 'More Space');
44
45create a bookmark of that resource:
46insert into bookmarks (uri, ctime, mtime) values ('http://echodin.net/', 1323407821, 1323407821);
47
48tag that resource:
49insert into tags (uri, tag) values ('http://echodin.net/', 'homepage');
50
51The resource's primary key is the URI. The bookmark id identifies the bookmark only, NOT the bookmarked resource
52
53get the bookmark and its resource values
54select id,resources.uri,title,ctime,mtime from bookmarks join resources on bookmarks.uri=resources.uri where id=1;
55*/
Note: See TracBrowser for help on using the repository browser.