Aerospace Software Ltd.

SQL-Ledger How-to Guide

For Mandriva Linux 10.2, 2005LE

August 2005

Scope

There are many accounting packages for UNIX, but most of them are hideously expensive. Of the free ones, only two are worth the effort: GNU Cash and SQL Ledger. GNU Cash is described in another guide and is good for very small businesses - it is similar to Quick Books. SQL Ledger is rather more advanced and aimed at large businesses.

SQL Ledger is developed by Dieter Simader in Edmonton, Alberta, Canada, http://www.sql-ledger.org. The program has good support and you can buy a book from him - recommended.

This guide describes the installation on a Mandriva Linux system, where the automated installer just doesn't want to cut it.

Preparation

You have to install a bunch of stuff to make this work. You need Apache, Apache Modperl, Perl, PostgreSQL, Postgres Procedural Languages, DBD Postgres, DBI and Latex. Run rpmdrake and install what you can. Specifically install all RPMs containing the following words:

dbd-pg
postgresql
postgres-pl
dbi
latex
apache
perl

These things don't take much space, so simply search for and install everything that appears to be remotely applicable.

Get it

Download SQL Ledger from http://www.sql-ledger.org and save the tarball in /usr/local, which is where we are going to install it. Untar the thing and then look for the README file in the doc directory:

# cd /usr/local
# tar -zxvf sql[tab]
# cd sql[tab]
# cd doc
# less README

Basically, we are simply going to follow the instructions in the README, but since it isn't completely correct for Mandriva, we'll add a few extra pointers to keep you on track.

Copy sql-ledger.conf.default to sql-ledger.conf:

# cd /usr/local/sql-ledger
# cp sql-ledger.conf.default sql-ledger.conf

Change some permissions:

# cd /usr/local/sql-ledger
# chown -hR apache:apache users templates css spool

That is all you need to configure for SQL Ledger.

Configure Apache

Add the following to the bottom of /etc/httpd/conf/httpd2.conf:

  Alias /sql-ledger /usr/local/sql-ledger/
  <Directory /usr/local/sql-ledger>
    AllowOverride All
    AddHandler cgi-script .pl
    Options ExecCGI Includes FollowSymlinks
    Order Allow,Deny
    Allow from All
  </Directory>

  <Directory /usr/local/sql-ledger/users>
    Order Deny,Allow
    Deny from All
  </Directory>

Now restart Apache with "service httpd restart".

Configure PostgreSQL

To start off, we need an administrator, so we need to create him:

# su postgres
  $ createuser -d sql-ledger
  Shall the new user be allowed to create more new users? (y/n) y

Now we need to run a setup script for which we need procedural language support from the postgres-pl RPM:

# su postgres
$ createlang plpgsql template1

At this point we are essentially good to go.

Configure Your Accounting System

There are are two startup scripts, admin.pl and login.pl. First, we need to run the admin script and create an accounting dataset for your company. Fire up a browser and go here: http://localhost/sql-ledger/admin.pl

The next step is to lock the system - click the 'Lock System' button. You have to lock the system, else you won't be able to do much!

Once locked, you can create a database, or dataset as SQL Ledger calls it, click the 'PG Database Administration' button. Note, leave the 'host' and 'port' boxes empty, unless you need to connect to a distant machine - blank assumes localhost. The 'User' box is 'sql-ledger', the 'connect to' box says 'template1'. Click the 'Create Dataset' button and give your dataset a name. Select your country if it appears in the big list, else use 'default'. If your country is listed, then most tax related stuff will be preconfigured for you.

Go back and click 'Add user'. The first user you create sort-of owns the dataset and initiates a group. Other users are created as members of this group, by editing the first user. So, create the first user. Remember, on any UNIX system, always use lower case for usernames and things and never use a name twice for different things - this is a very important tip!

The Add/Edit User form consists of three parts. The top part defines the user, the middle part selects the dataset and the bottom part assigns the permissions for this user. In the top part, fill in the Login, Password and Name as a minimum - leave the stuff on the right alone.

In the middle, click the 'Pg' button, type the name of your dataset and type the name and password of the first user.

Leave the bottom part of the form alone, scroll way down to the bottom and click 'Save'. If all went well, you will now have one user defined.

To add more users, click on this username and edit the top part of the form only, leave the Database part as is with the first username, scroll to the bottom and click 'Save'.

You should create a few essential users. You can come back and fine tune things later.

Before you leave, 'Unlock' the system!

Try it Out

Open a new tab in your browser and type: http://localhost/login.pl

You should now get the regular login page - unless the system is still locked - if so, go and unlock it on the admin page. Log in as a user and see what it looks like.

If you are familiar with accounting systems, then you could figure out how to use the program simply by playing around with it for a while, but I recommend that you buy support and the book from Dieter and save yourself some grey hair.

Have fun!