SQLite in Ubuntu 9.04

There are a lot of good choices for a free/open-source DBMS in Ubuntu (or anywhere for that matter): MySQL and PostgreSQL would be the two that stand out most prominently. But when you’re developing a small-scale application which requires a small-scale database (whether or not you intend to scale up later), a client-server DBMS like that is the proverbial sledgehammer, with your data structure as the walnut. SQLite offers a file-based transactional SQL database engine, without the installation/configuration/administration time associated with a server-based DB. Think MS Access, cross-platform, minus all the forms.

I’m currently building a proof-of-concept application (for migrating a spreadmart system), using the Django framework to put a web form presentation layer across a db backend. SQLite is ideal for this sort of task, in that the development time is shortened – I can pretty much get down to writing code as soon as the design is complete. An interface to SQLite (sqlite3) is built in to Python since 2.5, so if you wish to create an SQLite database using Python, all you have to do is write the appropriate code to do so. Without using Python you’ll need to enable command-line access by installing the sqlite3 library:

sudo apt-get install sqlite3

or just open Synaptic and search for sqlite3.

So for instance to create a database file ‘expenses.db’, create a category table and populate one row in it, I can do the following from the command line:

$ sqlite3 expenses.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table category(
   ...> id integer primary key,
   ...> cat_code varchar(3),
   ...> cat_name varchar(20)
   ...> );
sqlite> insert into category values(
   ...> null, 'GRO', 'groceries'
   ...> );
sqlite> select * from category;

To do the same thing in Python:

>>> import sqlite3
>>> conn = sqlite3.connect('expenses.db')
>>> conn.execute('''create table category(
... id integer primary key,
... cat_code varchar(3),
... cat_name varchar(20)
... )''')
<sqlite3.Cursor object at 0xb77ce2c0>
>>> conn.commit()
>>> conn.execute('''insert into category values(
... null, 'GRO', 'groceries')''')
<sqlite3.Cursor object at 0xb77ce290>
>>> conn.commit()
>>> for row in conn.execute('select * from category'):
...   print row
(1, u'GRO', u'groceries')

If you prefer to have a GUI, you can install the SQLite Database Browser:

sudo apt-get install sqlitebrowser

This is a very handy (albeit rather basic) visual tool which gives you access to pretty much all the same functionality as you have from the command line:

So that’s SQLite – simple, easy to use and very handy. Incidentally there’s a great blog post here extolling the virtues of SQLite in itself and also in combination with Excel. Intriguing reading and makes a great case for the benefits of using a “fractional horsepower” database. I’ll post more shortly on what this approach has allowed me to do with both Django and Excel.


Ubuntu 9.04 delayed (for me)

I received the Ubuntu 9.04 CD through shipit a couple of weeks ago, which was great. So, I sat down to install it in place of 8.10 last weekend, popped the disc into the drive, and then…nada. No acknowledgement of the CD at all. Turns out the drive was faulty – my laptop must have taken a knock at some point as BIOS didn’t even recognise that there was a CD-ROM drive at all, even after unseating and reseating it.

So, I called up Dell tech support, got a replacement drive sent out, which is now safely installed and humming away. Nice. Only problem is, I had taken the CD round to install Ubuntu on my brother’s laptop (which is now running well, except for a lack of wireless connection). I’d left the CD with him, so now I need to get it back before I can install 9.04 on mine…I’m starting to wonder whether I will get 9.04 installed before 9.10 is released. <Sigh>

Ubuntu 9.04 released

Canonical has announced the availability of Ubuntu 9.04 (Jaunty Jackalope) for download (free as always). This time around, in addition to the desktop and server editions, the Netbook Remix is available for your cute wee mini-puters – awww. There are a number of options for download – as well as the live CD iso through http and torrent, there is the alternate (text-based) installation CD (handy if you have any less than the recommended 256 MB RAM) and the DVD image is a lot bigger but contains all the language packs. If you want to try Ubuntu but want a less complicated process with no disk partitioning required then Wubi may be worth a crack.

If you’re already running Ubuntu you also have the option of upgrading your system through Update Manager, but I wouldn’t recommend it. I’ve been running Ubuntu for 3 years and have tried this twice on different machines, and both times I’ve had serious problems as a result. While there may be some people who have no trouble upgrading this way, it’s much safer in general to re-install entirely. Doing this means you will need to have some way to recover your files and system configuration. Backing up /home onto an external drive and copying them back after re-installing will work, but if you have /home on a separate partition, there’s no need to do this – as long as you don’t format this partition during installation all your files and any system/application/desktop configuration files will be preserved for the new version.

I’ll post a how-to when I’ve installed Jaunty, which won’t be for a few weeks yet. I’m waiting for the Ubuntu CD through shipit as downloading a CD image as well as all the updates required afterwards takes up a fairly large proportion of my monthly data allowance. I’ll just have to cope with my recently outdated system until then…

FBReader in Ubuntu 8.10

A lot of mornings on my way in to work on the train I like to read a book on my laptop. FBReader is a simple, attractive open source e-book reader that handles a large range of formats and provides a structured view of the e-book collection.

FBReader is available from the Ubuntu repositories – just a click away through Synaptic or Add/Remove Applications:

Add/Remove Applications
Alternatively, of course, you could just type at the command line:

sudo apt-get install fbreader

And you’re good to go – start adding your e-books. The library can be viewed as a tree, and books tagged however you like:
FBReader Library

Supported formats include epub, fb2, oeb, rtf, html and plain text, among others. Notable exceptions are pdf (apparently there are plans to implement support for text-only pdf files) and doc. There are also a few really nice features, including direct reading from tar and zip archives, automatic library building, automatic table of contents generation, text search and preserving the last book and last read position between sessions. Happy reading!

Ubuntu meets Windows 7

Benchmarked: Ubuntu vs Vista vs Windows 7

I saw this on Slashdot this evening and, feeling mildly interested, decided to take a look. While it’s certainly interesting to compare performance between operating systems, I always find it more entertaining to read the inevitable arguments, trollbaiting and poor spelling/grammar that follow. I think my favourite was

“Doesn’t anybody use a spell-checker? Despite the various Windows versus Linux/Ubuntu arguments, I’m going to go out on a limb, and guess most of you use Firefox, which by default has a spell check. Please, for the love of the Internet, use it!”

I would be quite keen to get my hands on the machine they used for testing:

“Our test machine packed an Intel Core i7 920, which in layman’s terms has four cores running at 2.67GHz with hyperthreading and 8MB of L3 cache. It also had 6GB of RAM, plus two 500GB of hard drives with 16MB of cache.”

I think I could get quite decent performance using any OS on a machine like that…