A Simple Bookmark Manager

In Linux Magazine 232, I introduced the Shaarli online bookmark manager [3]. I still use Shaarli for managing bookmarks, but after writing that piece, I realized that I also needed an alternative interface to the bookmarks I keep collecting. I needed a browser-based interface that was much quicker and more script-friendly than Shaarli. Most of all, I needed an interface that was compatible with complex searches and could process search data in SQL format.

I decided copying my bookmarks to a Jam.py application would be an effective solution for my problem – and also a nice example for this tutorial. To build the first version of that bookmark manager, I followed the standard Jam.py development flow.

The first thing to do in the Builder page of a Jam.py application is to set the application name, the interface language, and the main parameters, most of which are visible in Figure 1. For your first project, you may safely leave all default values as they are. The Interface tab of Figure 1 lets you choose among several graphic themes for your application.

Figure 1: The main parameters for every Jam.py application.

The second crucial step is to define the database engine that your application will use, as well as the parameters to connect to it. For SQLite, the only parameters I had to enter in the Database box of Figure 2 were the type and name (bookmarks) of the database. This step told the Builder to create and format a local file in SQLite format, called bookmarks, and store all my data in the file.

Figure 2: The database configuration panel, showing all the databases supported by Jam.py.

So far all I had was the shell of the database. I had to create at least one table inside it for my bookmarks and define all its columns. In the Builder, you perform these operations by clicking on Catalogs in the left pane then selecting the New button to create a new item. You'll then see a form similar to the form shown in Figure 3, which serves two purposes. In the upper part, you define the name, primary fields, and some other properties. The lower part of the form lists all the columns in the table, along with their properties. To add a column, click on the New button at the bottom of that form (not visible in Figure 3), and enter all its properties. When I finished adding columns, my BOOKMARKS_MAIN table looked like Figure 3.

Figure 3: The SQLite table for a bookmark manager, with all the fields configured and ready to use.

By default, a Jam.py application automatically applies every change you make in the Builder interface to the underlying database. If you want to see how some features work without risking data corruption, click on Project in the left pane, then click on the Database button on the right side and check the DB Manual Update box. This step will prevent the application from actually changing the underlying database until you uncheck the box.

Before configuring how to display and filter the contents of the BOOKMARKS_MAIN table, I needed to fill it – that is, to import all the bookmarks I had stored in Shaarli. It is of course possible to add records to a table with just one click, but that is no way to insert hundreds or thousands of records.

You can (re)read my tutorial [3] to see how I backed up Shaarli data to a plain text file with one record per line and fields separated by pipe (|) characters. Importing files like that in an SQLite table is very simple, as long as the columns in the text file are in the same order as they are in the table. To see what columns you have in an SQLite table, you can use the table_info directive at the SQLite prompt (Listing 1).

#> sqlite3 bookmarks
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> pragma table_info('BOOKMARKS_MAIN');
0|ID|INTEGER|0||1
1|DELETED|INTEGER|0||0
2|TITLE|TEXT|0||0
3|URL|TEXT|0||0
4|PUBLISHED|TEXT|0||0
5|INSERTED|TEXT|0||0
6|EDITED|TEXT|0||0
7|BROKEN|INTEGER|0||0
8|PRIVATE|INTEGER|0||0
9|SHORT_URL|TEXT|0||0
10|COMMENT|TEXT|0||0
11|DESCRIPTION|TEXT|0||0
12|TAGS|TEXT|0||0

In my case, I had a small problem: the column order in Listing 1 is the order I wanted for the SQLite table, but it was not the same as in the Shaarli backup file, and there are two extra columns (ID and DELETED). Therefore, if I told SQLite to read the Shaarli backup file, almost all values would end up in the wrong column or be just discarded. I wrote the ugly, but effective little Perl script to generate another plain text file with all the columns in the right sequence (Listing 2).

In Listing 2, line 3 initializes a record counter called $ID. The loop in lines 4 to 13 reads the file passed as the first argument one line at the time and removes the final newline character (line 5). Then, the script splits the whole line using the pipe character as a separator, saving each field in the variables listed in line 6. Lines 8 and 9 simply replace the text value of the broken and private fields with numbers, because in BOOKMARKS_MAIN, those fields are integers, not strings: $broken is converted to 1 if it contains an x; otherwise it is 0 (line 8) and $private becomes 1 if it is equal to true (line 1).

1 #! /usr/bin/perl
2
3 $ID = 1;
4 while (<>) {
5 chomp;
6 ($published, $broken, $edited, $private, $dummy, $short_url, $title, $url, $tags, $description) = split /\|/;
7
8 $broken  = ($broken  =~ m/^\s*x\s*$/) ? 1 : 0;
9 $private = ($private =~ m/^\s*true\s*$/) ? 1 : 0;
10
11 print join("|", $ID, 0, $title, $url, $published, $edited, $edited, $broken, $private, $short_url, '', $description, $tags), "\n";
12 $ID++;
13 }

01 .mode list
02 .separator |
03 .import bookmarks-for-sqlite.csv BOOKMARKS_MAIN
04 .quit

Line 11 just prints all the same fields, joining them with pipe characters and with two extra fields at the beginning. The fields that were in the original file are printed in the order they are inside the SQLite database, as you can see when comparing line 11 with the output of the table_info command in Listing 1. Line 12 increments the line/record counter and then the loop moves to the next line.

To load the output of rearrange_columns.pl into the actual database, I had to prepare the instruction file shown in Listing 3, which I called import.sql:

The instructions in import.sql are (almost) self-explanatory: the first two lines specify that the input file stores one record per line, with columns separated by pipe characters; the third line imports the whole content of that file into the BOOKMARKS_MAIN table, and the .quit statement obviously quits SQLite. At that point, I can finally use the two scripts in sequence:

#> ./rearrange_columns.pl shaarli-bookmarks.csv bookmarks-for-sqlite.csv
#> sqlite3 --init import.sql bookmarks

The result is shown in Figure 4: all the bookmarks I saved using Shaarli are now in a quicker, much more flexible graphical interface that I can use even when I have no Internet connection. Figure 4 also shows some other interesting features of the Jam.py Builder. First of all, you can decide which columns to show – and in which order. Then, there is the Filters button, which opens a form to create search filters. For instance, you could display only bookmarks with titles that include a certain string and were published in a certain range of dates, as in Figure 5.

Figure 4: Jam.py displays the contents of a database table, using its default graphic theme.

Figure 5: The configurable filters, together with the search box, make it easy to find just the records you want.

Two other things to notice in Figures 4 and 5 are the tiny arrows near the names of each column, and the small search box in the top right corner. The arrows mean that records can be sorted by that column. The search box lets you find and display only the records that have the given string in the textual field that you select.

Users and Roles

By default, a Jam.py program only has one user account with full powers, called admin, with a default password equal to 111. Even if you are the only user of that program, it is good practice to have a separate account with fewer privileges for daily use. Figures 6 and 7 show how to create an account. First, click on the Users and Roles entries of the Builder left menu to create new users and new roles for them [4]. Next, go back to the catalog, or wherever you defined your table, select it, and click the Privilege button on the left to specify what every role can do with that table (Figure 7). To make all these changes work as intended, click on Project | Parameters and tick the Safe mode box to force all users to log in with their password.

Figure 7: Defining roles and access rights.

Figure 6: Creating a user account.

Custom HTML

If you compare Figures 4 and 5, you will notice that the titles are formatted differently. The reason for the difference is that, in Figure 4, titles are plain text, but in Figure 5 they are hyperlinks pointing to the bookmarks.

Clickable titles are just one example of how you can customize Jam.py tables. To make the titles clickable, select the BOOKMARKS_MAIN table in the catalogs, push the Client Module button, and then select the Events tab shown in Figure 8. The function names on the left correspond to all the events that the Jam.py JavaScript can detect, and the editor on the right lets you insert whatever JavaScript you want in those functions. What I did, following the documentation [5], was to add the following code:

if (field.field_name === 'title') {
        return '<a href="' + field.owner.url.value + '">' + field.value + '</a>';
    }

Figure 8: In the Client Module panel of the Jam.py builder, you can add your JavaScript code to customize how records are displayed.

In plain English, this code means "whenever a title should be printed, replace that string with the HTML markup for a clickable link, pointing at the URL that corresponds to that title." Figure 9 shows that this is what happens when you save that JavaScript code. The meaning and features of all the functions visible in Figure 8 are described in the Jam.py user manual.

Figure 9: The result of the JavaScript code shown in Figure 8: Bookmark titles become clickable links to the corresponding pages.