Installing SQLiteManager 1.2.0 on FreeBSD

I've been into b/w photography for at least 15 years now, and I've got a serious collection of negatives and prints. A rule of thumb says: the larger the format, the fewer images. As I'm using my 4x5 as much as possible, there's only a handful of negatives to add each year. Still, I'd like to keep track of the negatives and the prints in an easier way than I used to do so far: I scribbled the data onto legal pads and waded through them if I needed to look up something. This calls for some sort of database.

First of all, conventional digital image databases are not up to the task. They are geared towards digital camera shots, but they cannot map a print to the corresponding negative, or the negative to the film (or batch of sheet films). However, I need just this in order to find the print exposure/development data, the film development data, and the negative exposure data associated with a given print.

I thought OpenOffice Base (2.3.1) would do the trick. Base is something like a MS Access clone, i.e. a front end with graphical tools to build tables, define relationships, and to create queries, forms, and reports. I might even be able to link the data to the scans of the negatives and prints which I keep on the computer anyway, and display them in a search form. I had to find out the hard way that Base is not even close to being usable, at least on FreeBSD. Whenever you do something interesting, it crashes reproducibly. I came across half a dozen ways to crash Base before being able to enter even a single complete dataset. As much as I like and rely upon the other OO tools, Base is ridiculous. I had to go back to a tried-and-true SQL database.

Although I usually don't shy away from command lines, a frontend would simplify data entry considerably. Think of issuing INSERT commands in tables containing a dozen fields or so. This is of course doable, but inconvenient. I came across SQLiteManager-1.2.0 which seemed to offer a simple and clean web interface to my tables.

The installation was far from smooth, although SQLiteManager is part of the FreeBSD ports collection (i.e. my box has the instructions to build and install the software from the original sources). SQLiteManager is a PHP5 web application which means a few additional hurdles on our way to eternal happiness. But lets go through the installation procedure step by step.

After installing the databases/sqlitemanager, databases/sqlite3, and /databases/php-sqlite3 ports (I intended to use version 3 databases right away), I had to edit the Apache config file manually:

Alias /sqlitemanager/ "/usr/local/www/sqlitemanager/"

<Directory "/usr/local/www/sqlitemanager">
AllowOverride None
Order allow,deny
Allow from all
AddType application/x-httpd-php .php .phtml

Running "apachectl restart" as root caused Apache to pick up the changes. Now I pointed my browser to "http://localhost/sqlitemanager/index.php" - and was greeted by a PHP error message saying something about missing session support. I recalled that FreeBSD needs an extra port (www/php5-session) to enable PHP sessions.

After restarting Apache and reloading the page, I got one step further, just to find out that there was no support for SQLite3 yet. I don't speak French, but the French installation instructions were clear enough to make me edit /usr/local/www/sqlitemanager/include/ to add the line

define("SQLITE3", true);

However, this was not sufficient. I happened to find the INSTALL file in the SQLiteManager installation directory which told me to install two additional database-related PHP ports: databases/php5-pdo and databases/php5-pdo_sqlite. With these in place (and Apache restarted), SQLiteManager offered SQLite3 support as well (watch out for the version numbers which are displayed on top of the start screen).

Now there was still a problem with file permissions. SQLiteManager keeps internal information in a SQLite database of its own. The INSTALL file already mentioned to set the permissions appropriately. On FreeBSD, Apache (and thus PHP scripts as part of a web interface) runs as user:group www:www. I changed the group of /usr/local/www/sqlitemanager/include/config.db (and of config3.db, although the installation instructions didn't mention it) to "www" and added write permissions for group members. However, I still got errors saying the config database is not writable. I recalled that in order to write to a file, you'll also have to write to the directory that contains the file in order to update the timestamp and the size information. Therefore I had to change the group and change the permissions of /usr/local/www/sqlitemanager/include as well.

It wasn't immediately apparent how to create a new database from scratch. The interface won't tell, and the (French) manual was of limited use as well. I finally figured that you have to enter a short database name (which will then be displayed on the left hand side of the web interface) in the upper field and a full path to the database file in the lower field. This file does not necessarily have to carry the same name as the display name. SQLiteManager of course needs write permissions in the target directory in order to create databases. To this end, I created /usr/local/var/db/sqlitemanager, changed the group to "www" and added write permissions for group members. I'd then use this directory to hold all database files created with SQLiteManager.

Finally, with less than 2 h of hairpulling, I was able to add my first table and start some serious work. I'll probably get back with further comments once I have gained some experience in handling my data with this interface.


Dave Pawson meint:

And you thought command line interface was inconvenient? I hope you have success with this one!
Mittwoch 16 April 09:09

mhoenicka meint:

:-) I hope the initial hassles pay off once everything is set up. And I still hope that I'll be able to access the database from OpenOffice Base once it is usable...
Mittwoch 16 April 16:23

Mein Kommentar

Dieser Artikel ist geschlossen. Keine Kommentare mehr möglich.