SQL-Ledger Accounting FAQ

• How to close the books

  Add a rule to prevent data from being changed or deleted.

  CREATE RULE acc_trans_delete AS ON DELETE TO acc_trans WHERE old.transdate <= 'date' DO INSTEAD NOTHING;
  CREATE RULE acc_trans_input AS ON INSERT TO acc_trans WHERE new.transdate <= 'date' DO INSTEAD NOTHING;

• How to setup audit trails

  Setup a database user for each user or group of users and REVOKE DELETE rights for the users or a group. This emulates a WORM device and forces users to reverse entries.

  REVOKE DELETE ON acc_trans FROM username

• How to enter beginning balances

  Add a GL entry to setup a beginning balance for your accounts.

• How to establish a beginning inventory

  1. add the parts to inventory, this step only describes the part, service or assembly
  2. add a vendor invoice to increase the quantity for the parts

• What does stock assembly mean

  Assemblies are manufactured goods assembled from parts, services and assemblies. Because you do not buy assemblies you 'stock assemblies' by adding assembled units to your inventory. The quantity for individual parts is reduced and the quantity for the assembly increased. To disassemble an assembly you simply return the parts to inventory by entering a negative quantity for the number of assemblies to stock.

• Mandrake and PostgreSQL problems

  Mandrake does not package the DBD-Pg driver. For installation instruction see section below.

• Redhat 7.0 Installation instructions

  1. Install apache1.3.12-25.i386.rpm
  2. Install perl5.6.0-9.i386.rpm
  3. Install postgresql-7.0.2-17.i386.rpm
  4. Install postgresql-server-7.0.2-17.i386.rpm
  5. Install postgresql-devel-7.0.2-17.i386.rpm (POSTGRES_INCLUDE)
  6. tar xvzf DBI-1.14.tar.gz
  7. cd DBI-1.14
  8. perl Makefile.PL
  9. make
  10. make install
  11. cd ..
  12. tar xvzf DBD-Pg-0.95
  13. cd DBD-Pg-0.95
  14. export POSTGRES_LIB=/usr/lib/pgsql
  15. export POSTGRES_INCLUDE=/usr/include/pgsql
  16. perl Makefile.PL
  17. make
  18. make install
  19. install SQL-Ledger

  Some of the applications have newer versions however the installation instructions remain the same. Just substitute the old version with a newer version.

• Debian Installation

  1. unpack into /var/www/sql-ledger
  2. install postgresql (Dselect)
  3. install libpg-dev (Dselect)
  4. modify /etc/postgresql/postmaster.init
     PGALLOWTCPIP=yes
     PGPORT=5432
  5. create a user for managing SQL-Ledger databases
     from root
     su postgres
     $ createuser
     name of user- (create a postgres user who is also a Linux user this user will be an account manager. Enable this user to create db and users)
  6. install DBI
     perl Makefile.PL
     make
     make test
     login as root
     make install
  7. $ export POSTGRES_INCLUDE=/usr/include/postgresql
  8. $ export POSTGRES_LIB=/var/lib/postgres
  9. install DBD-Pg
     login as account manager (above)
     perl Makefile.PL
     make
     make test
     login as root
     make install
  10. change /etc/apache/httpd.conf and add

      AddHandler cgi-script .pl
      ScriptAlias /sql-ledger/ /var/www/sql-ledger/
      <Directory /var/www/sql-ledger>
        Options ExecCGI Includes FollowSymlinks
      </Directory>
      

  11. restart apache
  12. From browser enter http://my_domain/sql-ledger/admin.pl
  13. Login as the postgres user (account manager) you have created (above)
      Create a DB
      Create users
  14. then enter http://my_domain/sql-ledger/login.pl
      Login as one of the users you have just created

• How do get SQL-Ledger working on Win2k

  Thanks to Martin Lillepuu for a writeup about how to install SQL-Ledger on a Win2k platform.

• login.pl has compilation error

  This could be because of a missing configuration file in the users directory

  check the permission for the users directory.

• script not executing, shows in browser instead

  Add

    AddHandler cgi-script .pl
  

  in your httpd.conf file.

• unknown terminal!

  The script couldn't figure out which browser you are using

  include the terminal variable on the URL
  http://localhost/sql-ledger/login.pl?terminal=lynx

  Valid terminal variables are (lynx and mozilla)

• permission denied

  Make sure the web server has write permission to write to the following
  • users/
  • templates/
  • users/members

     # chown nobody:nogroup users templates users/members
  

• Database permission denied

  The user you entered in the "Database section" must be a valid database user. Use the same name you used when you created the database and tables or another special user who has the rights to access the tables.

• html and graphics files don't show up on screen

  Enable Includes and FollowSymlinks Options in your httpd.conf file

    <Directory /usr/local/www/sql-ledger>
      Options ExecCGI Includes FollowSymlinks
    </Directory>
  

• Can I use mySQL

  Sorry, no. Although transactions are now supported with the InnoDB table handler the backend would still require a major rewrite to work with mySQL.

• How can I switch the display to a foreign language

  Load your preferences and select the language.
  Language selection is in accordance to ISO 3166 standards.

• Text shows in English when I use a foreign language

  This is because the corresponding hash entry is missing. Add the missing text in the locale/cc/all or locale/cc/missing file and run locales.pl to rebuild the individual files.
  cc refers to the country code.

1. Security depends on how you configure your web server and SQL server and who at the shell level has access to which files and directories.

2. If you do not want anyone to change the templates with the built-in editor set the files in templates/directory/ to read only or disable the menu item *Setup*.

3. secure your SQL server
   PostgreSQL uses the pg_hba.conf file to control who has access to the server. Assuming you are on a private network (192.168.100.0), your database is called 'mycompany' and you connect with user 'dbuser', then add

      host    mycompany     192.168.100.0  255.255.255.0    dbuser ident

4. in addition you can secure the tables from unauthorized access by setting up a different database user and GRANT rights. For instance, users without DELETE rights will still be able to use the program, change customers or vendors but will not be able to delete transactions. To lock all the tables GRANT SELECT rights only.

5. use a secure shell

6. use the authentication system from your web server

   etc, etc.