An Open Source ERP for Accounting

Recent industry trends show that organizations, while cutting costs to

counter the slowdown are also keen on exploring open source ERP packages and

comparing them against their commercial counterparts. There are several open

source ERP solutions available and many of them cater to requirements of various

industry verticals. Indeed some ERP packages provide complete solution for a

specific business process. SQL-Ledger is one such ERP solution that addresses

complete accounting requirements for an organization.

It is is an open source accounts ERP available for free download. It uses

PostgreSQL database to store accounting information. As it's written in Perl, it

has an HTML interface that makes it accessible on any operating system, thus

providing cross-platform usage. It covers all aspects of an organization's

accounting needs, from point-of-sale entries to accounts receivables and

payables, and for stock tracking. It has multi-user capability and is well

suited for the needs of a small business organization.

SQL-Ledger has primarily been written for the Linux platform, but can also be

installed on Windows or Mac machines. You have a certain set of pre-requisites

to fulfill for Linux, after which you can execute 'setup.pl' script to complete

SQL-Ledger installation and also to update Apache web server's config file. To

install it on Windows or Mac, some manual changes need to be done to the

SQL-Ledger code and to Apache's configuration files. In this article we will

show how SQL-Ledger can be installed on a Windows XP-based machine.

Getting started



The downloaded SQL-Ledger is a zipped package of Perl files that constitute the
interface and business logic of the whole accounting system of the ERP solution,

but is devoid of any database or web server. For installing SQL-Ledger, we have

to configure and install the web-server and the database first. Also, for

processing Perl scripts and code, we need to have the Perl runtime installed.

And the database driver for PostgreSQL for Perl engine has to be configured

before we start using SQL-Ledger. With this month's DVD we are providing the

SQL-Ledger package with all essentials needed for its working. In the DVD you

will find the following:

1. SQL-Ledger: sql-ledger-2.8.19.tar. gz. tar



2. Web Server: apache_2.2.11-win32-x86-no_ssl.msi


3. Perl installer: ActivePerl-5.10.xxx. msi


4. PostgreSQL: postgresql-8.3.4-1.zip


5. Perl DB Driver for PostgreSQL: DBD-Pg-2.10.0-Perl5.10.ppd

Installing Apache and Perl



SQL-Ledger needs an HTTP server, so first install the Apache 2.2 web-server
provided on this month's DVD. During Apache 2.2 Server installation, when you're

prompted at the Server Information dialog box, provide “localhost” both as the

Network Domain and Server Name. Provide an email ID as administrator's address

and also check the option of using Port 80 as the service. When you complete the

installation, a new icon of Apache server on the System Tray gets installed. By

right-clicking on this icon and selecting Open Apache Monitor, we can

Start/Stop/Restart the Apache Web Server. To test that the server has been

installed and is running, type http:// localhost on your browser's address bar.

If you get the message “It Works,” it indicates that the Apache web service is

running.

The second important requirement for SQL-Ledger ERP system is a Perl runtime

for all its scripts to get executed. ActivePerl from ActiveState is a

ready-to-use complete Perl distribution package and on the DVD we have provided

the latest bundle for Windows. Install it with default settings and it will

register the Perl path in the system's environment variables. Once when the

installation has been completed, open the command prompt and type command 'ppm'.

It will open a GUI interface of Perl Package Manager, where you can see the

installed packages. When you type 'dbd' in the package filter search box, you'll

see a list of Database Drivers (DBD) for Oracle, ODBC and SQLite. However, the

DBD package for PostgreSQL is not there.

Setting up DBD for PostgreSQL



The DBD-Pg and DBI provide the database interface mechanism between the
SQL-Ledger and PostgreSQL database. The ActivePerl has the DBI package already

installed, so we just need to install the PostgreSQL database driver for Perl's

DBI module. Since, we are using Perl 5.10 for this demo, we'll have to use the

DBD-Pg version 2.10 which is compliant with Perl's latest version. Copy the

DBD-Pg-2.10.0-Perl5.10.ppd file from the DVD to a temporary directory on your

computer. The Perl Package Descriptor (PPD) file downloads the necessary Perl

drivers for PostgreSQL database. So, it's important that you are connected to

the Internet while providing this file to the PPM command. PPM is the

ActivePerls' package manager that is used to install, upgrade or for removing

packages. You have to install the DBD-Pg package manually using the PPM install

command. So, through the command prompt, go to the root of the temporary

directory where you copied this PPD file and enter the following command (for

convenience we have renamed the PPD file to just DBD-Pg.ppd):

ppm install DBD-Pg.ppd

This will download DBD-Pg-2.10 packages and install them in the site

directory of Perl's installation directory. After the DBD-Pg has been installed,

again run the “ppm” or “ppm gui”command from the command prompt which will bring

Perl's Package Manager window. Here scroll down to see the DBD-Pg package

installed and that also in the area 'site' and having version 2.10.

Perl Package Manager interface lists the DBD-Pg diver for PostgreSQL database which has been installed in the default area: site

Configuring SQL-Ledger's database



PostgreSQL database is the core of the SQL-Ledger accounting ERP solution where
all accounting information is saved. So, a database needs to be in place before

we can get to the business of installing and using SQL-Ledger. We have provided

the latest version, 8.3, of the PostgreSQL database server in the DVD. Extract

the msi installer to a temporary directory and start the installation by

executing the msi installer file. Follow the instructions in the PostgreSQL

Installation Wizard dialog box and when it prompts for Installation options, do

not select default settings, instead click on PostgreSQL in the menu tree and

select 'Entire Feature will be installed on local hard drive.' And then click on

the Next button. In the Server Configuration dialog box, we need to provide a

User Account Name and its password. To create a user, right-click on My Computer

and select Manage. The Computer Management window opens up. Here, expand the

node of Local Users and Groups, and select Users. Now through Action > New User,

create a new user having User Name as “postgres” and Password as “postgres”.

Also in the New User dialog box, uncheck the option for “User must change

password at next logon”.

Now provide the same user name and password in the Server Configuration

dialog box of the PostgreSQL installation Wizard and click on Next. In the

Initialize Database screen, set the Port number as 5432 and pass the Superuser

Name and password as “postgres” and then complete the installation. Now log on

to the database as postgres user; for this goto Start > Run and type the

following as the Run command:

runas /user:postgres cmd.exe

The screen that pops up prompts for a password. Enter the password that was

set for the User Name “postgres” while creating the user from Computer

management window. Once you enter the password and click enter, a new command

prompt window comes. Here, change the directory to the bin directory of base

installation of PostgreSQL server (in our case with all default settings as

during installation). The directory happens to be “C:\Program Files\PosrgreSQL\8.3\bin”.

At the command prompt, after changing to PostgreSQL's bin directory, type the

following command to create a new user for the SQL-Ledger database.

createuser -d -a sql-ledger

The final configuration step for PostgreSQL database is to set the client

authentication for local TCP/IP connection, which can be set through pg_hba.conf

file found in the data directory of PostgreSQL base installation. Open the file

using Notepad and at the bottom, where settings for IPv4 local connections are

given, change the Method to trust from md5 and save the file. The settings

should look as follows after the change:

host all all 127.0.0.1/32 trust

Create a new user postgres through the Computer Management Window by clicking on Action > New User.

Installing SQL-Ledger 2.8.19



Now with all pre-requisites in place we can start installing the SQL-Ledger
accounting system. In the DVD we have provided the latest version, 2.8.19, of

SQL-Ledger ERP system. Extract the contents of the zipped file

sql-ledger-2.8.19.tar.gz to the Apache Web Servers directory, which in our case

is “C:\Program Files\Apache Software Foundation\Apache2.2”. Navigate to this

directory and you'll find a directory named “sql-ledger”, which contains all

Perl files of the ERP system. As we know that this SQL-Ledger was configured for

Linux platform, we will have to do some changes to Perl files. Open the admin.pl

file using Notepad from the SQL-Ledger folder of Apache Server's directory and

provide the base installation folder of Perl in Windows format instead of Linux,

ie change the first line of the file from

#!/usr/bin/perl

to

#!c:\perl\bin\perl

Once the file admin.pl has been changed with Perl's Windows directory

location, the configuration file of Apache server needs to be updated to support

Perl extensions and also to give an alias to the directory location of the

SQL-Ledger. Therefore, append the following directive code at the bottom of the

Apache's configuration file httpd.conf:

Alias /sql-ledger "C:/Program Files/Apache Software

Foundation/Apache2.2/sql-ledger/"






AllowOverride All


AddHandler cgi-script .pl


Options +ExecCGI


Order Allow,Deny


Allow from All















Order Deny,Allow


Deny from All

When you click on Create Dataset button, a Windows Runtime error occurs because of a missing dll file in the database driver.

Once the error has been rectified, SQL-Ledger's dataset for a company profile can be created. It provides several accounting formats to choose from.

Final touches



Once the installation and configuration of SQL-Ledger has been done, type the
URL address http://localhost/sql-ledger/admin.pl in the browser and SQL-Ledger's

login screen appears. Click on Log in button in the SQL-Ledger Accounting

Administration screen to set a new password or add a new user. And to configure

the database, click on 'Pg Database Administration' button. On the next screen,

provide Host as localhost and the port as 5432, and then click “Create Dataset”

button. Surprisingly, here we encountered a Microsoft Visual C++ runtime error

message. We tried the installation on a new Windows XP machine, but to no avail.

This hampered the creation of the dataset for the database.

However, this is not the end of the road and the box we tell you how to

remove this error. Once the error has been removed, you can create the dataset

which is associated with a company. For each dataset template, you can create

users and assign them accounting or accessing rights to company's accounts and

reports.

Thus, you can start using the SQL-Ledger accounting system for your business.