Sql Ledger

Version

The 'defaults' table stores the version number in the 'version' column.

Directory Structure

This is the directory structure on a Debian 5.0 (Lenny) package install:

/etc/sql-ledger/
/usr/share/lintian/overrides/sql-ledger/
/usr/share/sql-ledger/
/var/lib/sql-ledger/
/var/lib/sql-ledger/templates/
/var/lib/sql-ledger/users/

/usr/lib/sql-ledger/ is a symbolic link to /usr/share/sql-ledger/

/usr/share/sql-ledger/sql-ledger.conf is a symbolic link to /etc/sql-ledger/sql-ledger.conf.

For each user a directory is created under /var/lib/sql-ledger/templates/ using the user's name. A configuration file is created under /var/lib/sql-ledger/users/ using the user's named suffixed with '.conf'. This includes the user's database, and encrypted versions of the user and database password. Additionally, a file named 'members' contains similar, if not the same, information for all users.

At some stage, html and tex templates are copied by language choice from /var/lib/sql-ledger/templates/ to the directory name given for template when creating the user, perhaps when the user first runs a report.

Restoring Dataset

To restore a single dataset from a user's backup (one created using SQL-Ledger), assuming the database user that the sql-ledger application uses for the account being restored is 'sql-ledger', then:

   $ gunzip -c dataset-2.6.12-20080814.sql.gz | psql -U sql-ledger -a DATASET 2>&1 | tee restore.log

$ gunzip -c accounts-2.6.12-20091009.sql.gz | psql -U sql-ledger -a sp_accounts 2>&1 | tee restore.log

You may get some errors, hence the '-a' option to psql to show the commands being executed and redirecting all output to the restore.log. You should check the log. You may get errors that a releation already exists when it trys to create sequences. These shouldn't matter as the next statement in the backup should set the value correctly.

Examining the backup file, it doesn't appear to be a backup created using PostgreSQL's pg_dump utility. It appears to be a concatenation of:

  • Drop all tables and functions (not sequences)
  • ./sql/Pg-tables.sql
  • Data as full insert statements
  • ./sql/Pg-functions.sql
  • Triggers
  • Indexes

The script does expect tables to exist, so to restore it to an empty database created as follows:

$ createdb accounts --owner=sql-ledger --encoding=UTF8

requires editing the backup file to strip the create table and create function commands.

Of course, it's a really good idea to additionally perform normal full database backups as well.

Trouble Shooting

panic: attempt to copy freed scalar XXX to XXX at SL/Form.pm line NNN.

There are reports of this occuring when creating PDFs for an invoice where three or more line items exist.

This appears to be caused when running SqlLedger with Perl version 5.10 where it appears an error handling change was made.

See also:

This seems to be still an issue in version 2.8.25, and can be reproduced on Debian 5.0 (Lenny) by:

  • Create Dataset - UK-General - UTF-8
  • Create User - date format dd-mm-yy
  • Create Service - ID:1 Desc: Goods Delivery
  • Create AR Sales Invoice
  • Add 4 items with ID:1
  • Print to PDF or Postscript

For now, perhaps the most practical solution is to continue running in Debian 4.0 (Etch) as either dual boot or a virtual machine. See InstallingSqlLedgerOnDebianEtch.

Resources

_Related Topics: _ LedgerSMB

-- Frank Dean - 14 Aug 2008