Sql-ledger

From OptionC

sql-ledger (http://www.sql-ledger.org/) is a web-based double-entry accounting system (licensed under the GPL). It's probably best for people who are already have some familiarity with accounting systems, although the install process does allow you to pick a default set of accounts to be created based on your "industry."

We had a demo of the 2.4.14 version available here (http://www.option-c.biz/sql-ledger/login.pl), but as of 2006-01-24 we are migrating servers, and the demo servers are the first to be brought down and the last to be brought back. The password/id is xendemo/xendemo

Table of contents

4.1 Back up your data and your directory
4.2 The first garden path
4.3 The actual steps I took

5 Recommended Reading

[edit]

Versions

2005-10-17: 2.4.16 was released on 2005-09-02, and 2.6.1 was released on 2005-10-09. The developer(s) come out with new releases/bug fix releases fairly regularly (it looks to me about once every two months on average). However, I wouldn't expect much more work on the 2.4.x series. The 2.6.x is not substantially heavier than the 2.4.x series, nor does it have increased dependencies. In addition, the 2.5.x beta was in testing for quite some time, so I don't personally see a reason to hold back, (see below, I just upgraded today).

However, when I started this wiki page (2005-07-15), the version of the package available in sarge/stable is 2.4.7 (http://packages.debian.org/stable/web/sql-ledger) (it is also the one available in etch/testing and sid/unstable). To be honest, if I'd noticed that I might have used the older version. Since I didn't, I installed the latest version (2.4.14). Although the process is fairly straight-forward, as I have my postgresql server separate from my web server, I made notes of the following as I was going through the install.

[edit]

Prerequisites

The listed requirements are...

SQL Server ( PostgreSQL 7.1+, Oracle 8+, DB2 7.2+ )
Web server (Apache, thttpd 2.22+, boa, AOL server, ....)
Perl 5+
Database Independant Interface DBI
Database Driver for SQL server DBD ( DBD-Pg, DBD-Oracle, DBD-DB2 )
LaTeX (optional)

On the postgresql server:

apt-get install postgresql

If you are installing onto a clean machine, you'll need to get the following on the web server (it doesn't haven't to be apache2, just about any web server will do, but as I am using apache2, that's what I refer to):

apt-get install libdbd-pg-perl tetex-bin libclass-dbi-pg-perl apache2

There are quite a few other dependencies, but that should be all that you need to explicitly state (actually, that may be too much...). Also tetex-bin is optional, but it is needed if you want to print anything.

In order to be able to connect to your postgresql server from a remote web server, add a line to /etc/postgresql/pg_hba.conf (you may want to use "md5" instead of "password" as password uses clear text - here's the explanation of all the authentication methods (http://www.postgresql.org/docs/7.4/interactive/auth-methods.html)):

host    all         all         my.web.server.ip      255.255.255.255   password sameuser

Then restart the postgresql server:

/etc/init.d/postgresql restart

[edit]

Installation

Most of the following is directly from the README (http://www.sql-ledger.org/cgi-bin/nav.pl?page=source/readme.txt&title=README). The day I did the install, the file that was downloaded as part of the automated install did not show up as a valid gzip file (sometimes the error messages are correct!), so I downloaded the file separately.

 mkdir /usr/local/sql-ledger
 cd /usr/local/sql-legder
 wget http://www.sql-ledger.org/source/setup.perl
 mv setup.perl setup.pl
 wget http://www.sql-ledger.com/source/sql-ledger-2.4.14.tar.gz
 perl setup.pl sql-ledger-2.4.14.tar.gz
 /etc/init.d/apache2 restart

Next you need to create the postgresql user (if you don't want to use a password, omit "-P"):

  su postgres
  createuser -d -P sql-ledger
  Enter password for new user: ******** 
  Enter it again: ********
  Shall the new user be allowed to create databases? (y/n) y
  Shall the new user be allowed to create more new users? (y/n) y

And install PL/PGSQL in template1:

  su postgres
  createlang plpgsql template1

Go to the following url (substitute appropriate address for WEBSERVER): http://WEBSERVER/sql-ledger/admin.pl. You should get a screen that says things like "sql-ledger" and "administration." If you get a plain text version of the admin.pl script, then perhaps you need to restart your webserver. If you do that and you still get the error, then there's probably some dependency that's been missed (at this point, you might want to install the older version of the package from Debian, remove just that package, then continue since the dependencies are the same). At this point there isn't an admin password, so just select click "login."

You should be greeted with a screen titled "SQL-Ledger Accounting Administration" and a row of buttons. The option you want is "Pg Database Administration."

The next screen is "SQL-Ledger Accounting / Database Administration." The fields are:

Host: localhost OR ip address of postgresql server (if remote)
Port: 5432 is the default port
User: sql-ledger
Password: enter password (if you created one)
Connect to: template1

Then select "create dataset." As long as you are connecting to a server on the localhost OR you have made the correct entry in /etc/postgresql/pg_hba.conf, you should get to another screen that asks you to name the dataset (pick whatever you want as long as you only use lowercase letters) pick your character set, and pick a default set of accounts. Then click "continue" and you should get a screen saying that the dataset was successfully created.

At this point there should either be a link to return to the main admin screen, or you will want to click back until you get there. You should probably enter a new user and change the admin password. After you've done that, logout and go to http://WEBSERVER/sql-ledger/login.pl. Log in as admin or as the user you created, and have fun.

[edit]

Upgrading

I took the plunge on October 17, 2005, and upgraded to 2.6.1. This is not currently available as a Debian package. However, since in addition to upgrading I also needed to move the data to another machine, I tried to install the Debian version so that I could get all the dependencies met and then add the 2.6.1 version in the recommended fashion.

That was NOT a good idea. For one, my idea of what was needed and Debian's were a bit different. For two, I made a grave error and did not have enough space to install all the packages in the first go. And for three (although this isn't really a Debian issue per se), the Debian install of PostgreSQL is more secure than the default install, so some of the documentation isn't terribly helpful.

Now, on to the "I should be too embarassed to write about this, but I'm also afraid I'll make the same mistakes again, so I'm going to suffer through it" part of this wiki.

Keep in mind, the previous instructions were for a clean install of a demo. What I actually had on my production system was SQL-Ledger 2.4.4 on Mac OS X Panther (10.3). Today I wanted to move the data to a Debian/Apache2/Postgresql system _and_ upgrade to 2.6.1. The normal choices in such a circumstance are either:

install the current version on the new system, move the data, then upgrade, or...
upgrade the old system, install the new system, then move the data

The main page says:

Upgrading from any version later than 1.2.7

Either use setup.pl or untar over top and fix the permissions according to the UPGRADE 
file in the doc directory. Most of the time there is nothing else to be done. Datasets 
are upgraded when you log in. If other steps are required, i.e template modifications, 
they are listed in the UPGRADE file.

It wasn't abundantly clear based on the upgrade (http://www.sql-ledger.org/source/upgrade/) files if this was a system that wanted you to step through every single upgrade, or if it would handle all the steps for you. What ended up working for me was upgrading from current (2.4.4) to most recent 2.4.x (2.4.16) then going to 2.6.1.

[edit]

Back up your data and your directory

Every step of the way; really. Sql-ledger has a built in backup option, and if you've been using sql-ledger long enough that you need to upgrade, you are most likely familiar with it (it is under "system"). It appears to be a nice front-end to pg_dump. Also remember to back up the full /sql-ledger directory that goes with the data.

[edit]

The first garden path

Regardless, my first attempt was to run setup.pl (extracted from the 2.6.1 source) from within the extant /sql-ledger directory. My first big mistake was not to notice that my only options were "install over the internet" or "download." There should also be an "upgrade" option, and if setup.pl doesn't offer it to you at the beginning, you can be relatively certain the rest of the script won't work. However, I let it go through its machinations before coming to the "this is a new install line" before thinking "no, it's not" and then killing the process. My second step was to run it from above the extant /sql-ledger directory, with the same result (okay, actually with a considerably messier result - it clearly should be run from within the directory).

(Imagine me repeating this many times, from different locations, each time my internal dialog becoming considerably less polite, before moving on to...)

[edit]

The actual steps I took

At least, I wish I'd taken these steps. Mine were considerably more circuitous. Assuming that the install is /usr/local/sql-ledger...

Grab the 2.4.16 source (http://prdownloads.sourceforge.net/sql-ledger/)
Unpack the source over the existing install

cd /usr/local
tar -zxvf sql-ledger-2.4.16.tar.gz
cd sql-ledger
chown -R www-data.www-data *
chmod 711 templates users css spool

(Full disclosure: the web server group and user is not www-data on OS X, that's for Debian)

Point the web browser to http://WEBSERVER/login.pl, log in as user with administrative rights and let the upgrade proceed.

Repeat the exact same steps with the 2.6.1 source.
Do a clean install of 2.6.1 using the above instructions for 2.4.x (of all the steps, that one I didn't have any problems with)
Create a new dataset with the same name as the old one
Backup the data from the old server, copy it from the new server, and (using the uncompressed data on the new server)

su postgres
psql DATABASE NAME
\i DATABASE BACKUP FILE

At this point you should just be able to long in as normal.

The thing that really seemed to keep getting in my way was that after upgrading I would to to ~/sql-ledger/admin.pl instead of ~/sql-ledger/login.pl and use that interface to update the dataset. Then when I logged in, it would error out because the system was trying to run the automatic upgrade, but would encounter fields that already existed (since I'd already updated the data). To get around that I actually had to replace the relevant ~/sql-ledger/sql/Pg-ugrade*.sql files with blank files of the same name.

Have fun.

[edit]