source: bookmarks/trunk/bookmarks.sql @ 2

Last change on this file since 2 was 2, checked in by peter, 13 years ago

Added exisiting bookmarks project files

File size: 1.7 KB
Line 
1drop table if exists tags;
2drop table if exists resources;
3drop table if exists bookmarks;
4
5-- bookmarks of resources
6-- each resource can only have one bookmark
7-- bookmarks have creation and modification times
8create table bookmarks (
9    id integer primary key,
10    uri varchar,
11    ctime integer, -- creation time of the bookmark
12    mtime integer,  -- modification time of the bookmark
13    constraint unique_uri unique (uri)
14);
15
16-- resources by URI
17-- this is where we would store information about the resource,
18-- such as its title, last known status, etc.
19create table resources (
20    uri varchar primary key,
21    title varchar -- URI title (e.g., page title)
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,
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.