Given the application description, it's obvious I need a list of links, and for each link a description, a count of the number of times its been accessed, and the date of last access. Since I'm going to be keeping a list of URLs, some form of automated checking might be in order, so we'll also keep track of the date it was last checked, and the date it was added just because. Finally, I need some way to identify which link a user is trying to follow, so I'll add an integer id. That table is described by the following SQL:
create sequence link_counter ; create table hotlist ( id int not null , // The index counter count int default 0 , // and the click counter added datetime , // when it was added followed datetime , // last time it was followed checked datetime , // last time it was verified description text , // What the link is to URL text , // And how to get there primary key (id) ) ;
The first line is specific to PostGreSQL, and is a counter to generate id's; the rest is just the attributes of the list elements as described above.
Some preliminaries are required before you can create the database
in PostGreSQL. First, you should add yourself as a user of the
database. To do that, su (as root) to psql, and run the
create_user command:
# su pgsql $ /usr/local/pgsql/bin/createuser -d username
Where username is your username. The
-d flag allows you to create databases. Note
that the PostGreSQL commands have been installed in
/usr/local/pgsql/bin; you should add that to your path
for the future examples.
Having added yourself as a PostGreSQL user, you create the database, and then create the table in it using the SQL source like so:
$ createdb hotlist $ psql -f hotlist.sql hotlist
Ideally, you would issue a command to your browser, and it would add the displayed HTML page to the hotlist. The method - indeed, the ability - to do that varies from browser to browser, so here's a short Python script that adds items. If invoked with arguments, the first is treated as the URL, and the rest as the description, and that item is added. If invoked without any arguments, it reads lines from standard input. The first word on each line is treated as a URL, the rest of the line as a description, and that item is added - for every line in the file.
The actual code consists of a function to add a url and the text
description, and the body of the code, which invokes that routine on
either the arguments (sys.argv) or each line of standard
input.
Having set up the database, and populated it - or at least having a tool to populate it - it's time to look at the code.