source: bookmarks/trunk/bookmarks.sql @ 15

Last change on this file since 15 was 15, checked in by peter, 12 years ago
  • Bookmarks uses Moose instead of Class::Accessor
  • if not dbh is specified in the Bookmarks constructor, it can use a dbname parameter to create a SQLite DBH
  • the SQLite DBH created form dbname has the foreign_keys pragma set
  • added foreign key constraints to the bookmarks.sql table definitions for bookmarks and tags
  • added a required --file option to bkmk to specify the database file to use
  • added a load command to bkmk that loads bookmarks dumped as YAML using bkmk list
  • Bookmarks::add() can take mtime and id parameters (useful for reconstructing a database from the YAML dump of bkmk list)
  • BookmarkApp and bkmk no longer use DBI directly; just pass a dbname to the Bookmarks constructor
  • changed the default database for BookmarkApp to fk.db (schema from this revision's updated bookmarks.sql, with foreign keys)
File size: 1.8 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 references resources(uri) on update cascade,
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 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.