1 | drop table if exists tags; |
---|
2 | drop table if exists resources; |
---|
3 | drop table if exists bookmarks; |
---|
4 | |
---|
5 | -- bookmarks of resources |
---|
6 | -- each resource can only have one bookmark |
---|
7 | -- bookmarks have creation and modification times |
---|
8 | create 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. |
---|
19 | create table resources ( |
---|
20 | uri varchar primary key, |
---|
21 | title varchar -- URI title (e.g., page title) |
---|
22 | ); |
---|
23 | |
---|
24 | /* old resources table |
---|
25 | create 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:" |
---|
35 | create table tags ( |
---|
36 | uri varchar, |
---|
37 | tag varchar, |
---|
38 | constraint unique_tag primary key (uri, tag) |
---|
39 | ); |
---|
40 | |
---|
41 | /* |
---|
42 | create a new resource: |
---|
43 | insert into resources (uri, title) values ('http://echodin.net/', 'More Space'); |
---|
44 | |
---|
45 | create a bookmark of that resource: |
---|
46 | insert into bookmarks (uri, ctime, mtime) values ('http://echodin.net/', 1323407821, 1323407821); |
---|
47 | |
---|
48 | tag that resource: |
---|
49 | insert into tags (uri, tag) values ('http://echodin.net/', 'homepage'); |
---|
50 | |
---|
51 | The resource's primary key is the URI. The bookmark id identifies the bookmark only, NOT the bookmarked resource |
---|
52 | |
---|
53 | get the bookmark and its resource values |
---|
54 | select id,resources.uri,title,ctime,mtime from bookmarks join resources on bookmarks.uri=resources.uri where id=1; |
---|
55 | */ |
---|