Topic: How to install SQL Ledger

[dave_d]

>>>>>>>>>>>>> Edited 11 April, 2005 <<<<<<<<<<<<<

Just in case anyone else is interested, here's how to install SQL Ledger onto a 6.0.1-01 server.

1.  Install Postgres.  There are plentiful descriptions about how to do this in contribs. Alternatively make an installation script like this:


#!/bin/bash
#
cd /downloads/postgres
#
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-contrib-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-devel-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-docs-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-jdbc-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-libs-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-pl-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-python-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-server-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-tcl-7.4.2-1PGDG.i386.rpm
wget -c ftp://ftp.be.postgresql.org/postgresql/binary/v7.4.2/redhat/redhat-7.3/postgresql-test-7.4.2-1PGDG.i386.rpm
wget -c http://linuxsoft.cern.ch/cern/7.3.X/i386/RedHat/RPMS/mx-2.0.3-1.i386.rpm
#
rpm -Uvh --nodeps *.rpm                   # Install all the Postgres RPMs
/etc/init.d/postgresql start
# Create template directories
mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
mkdir -p /etc/e-smith/templates-custom/var/lib/pgsql/data
# Copy original config file into template directory
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates/var/lib/pgsql/data
cp /var/lib/pgsql/data/pg_hba.conf /etc/e-smith/templates/var/lib/pgsql/data
# Make modifications for this SME install
# This changes '#tcpip_socket = false' to 'tcpip_socket = true' whilst
# making the copy
sed '/#tcpip_socket/s/#tcpip_socket/tcpip_socket/;s/false/true/' \
/etc/e-smith/templates/var/lib/pgsql/data/postgresql.conf > \
/etc/e-smith/templates-custom/var/lib/pgsql/data/postgresql.conf
# This changes 'local all all ident sameuser' to '#local all all ident sameuser'
# whilst making the copy
sed -e '/local/s/local[ ]*all[ ]*all/#local all all/' \
/etc/e-smith/templates/var/lib/pgsql/data/pg_hba.conf > \
/etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf
# ..and this adds some lines
echo ""
echo -n "Enter the IP address of this server (please get it right, there's no checking!!) "
read ipstring
echo -n "Enter the IP mask of this server (probably 255.255.255.0) "
read maskstring
echo "host all all $ipstring $maskstring trust" >> /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf
echo "host all all 0.0.0.0 255.255.255.255 reject" >> /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf
#
# ..and these should not really be added - it's just for testing
echo "local all all                              trust" >> /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf
echo "host  all all 127.0.0.1  255.255.255.255   trust" >> /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf

/sbin/e-smith/expand-template /var/lib/pgsql/data/pg_hba.conf
/sbin/e-smith/expand-template /var/lib/pgsql/data/postgresql.conf

# Just a symbolic link so that the actual postgress Java interfacec can be changed easily
ln -s /usr/share/java/pg74.1jdbc3.jar /usr/share/java/postgresql.jar

/etc/init.d/postgresql stop
/etc/init.d/postgresql start

# Enable automatic launching of postgres on system start
ln -s /etc/init.d/postgresql /etc/rc.d/rc7.d/S56postgresql
ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K03postgresql


 
2.  Create an ibay into which you want to install SQL-Ledger.  Make sure the ibay has 'Execution of dynamic content' enabled.  We'll assume you call it sql-ledger.
3.  Log onto your server using the console or putty from a Windoze box or any other way you fancy.  In any case log on as root.
4.  Now do the following.

- Create somewhere to put some downloads
# mkdir /downloads

- Install gcc (needed later)
# cd /downloads
# wget -nc http://download.fedoralegacy.org/redhat/7.3/updates/i386/gcc-2.96-113.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/updates/i386/cpp-2.96-113.i386.rpm
# wget -nc http://mirror.datapipe.net/norlug/redhat-7.3/RPMS/glibc-devel-2.2.5-48.norlug.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/updates/i386/glibc-kernheaders-2.4-7.16.i386.rpm

# rpm -ivh glibc-kernheaders-2.4-7.16.i386.rpm
# rpm -ivh glibc-devel-2.2.5-48.norlug.i386.rpm
# rpm -ivh cpp-2.96-113.i386.rpm
# rpm -ivh gcc-2.96-113.i386.rpm

- update perl so that we can get Postgres connected to the SQL-Ledger application
- (this is where gcc is needed)

# perl -MCPAN -eshell
(Lots of things happen here - just accept the defaults unless you know differntly!!)
cpan> install Bundle::CPAN
cpan> reload cpan
cpan> install MD5
cpan> install Storable
cpan> install DBI
(Lots of things happen - check right at the end to see that the operation completed OK)
cpan> install DBD::Pg
(Lots of things happen - check right at the end to see that the operation completed OK)
cpan> exit

- Now we'll install the application

# cd /home/e-smith/files/ibays/sql-ledger/html
# wget -nc http://www.sql-ledger.com/source/setup.perl
# mv setup.perl setup.pl
# perl setup.pl

- when asked (i) from the internet.
(accept default values 'www' when asked)
- the setup routine will be unable to modify httpd.conf and will tell you what needs to be added. (The file /etc/httpd/conf/sql-ledger-httpd.conf will contain the bits that need to be added)
- in order to make this installation SME standard, do the following....

# cd /etc/e-smith/templates-custom/etc/httpd/conf/httpd.conf
# pico 95sql-ledger
(insert the following ...)


Alias /sql-ledger/ /home/e-smith/files/ibays/sql-ledger/html/
<Directory /home/e-smith/files/ibays/sql-ledger/html>
  AllowOverride All
  AddHandler cgi-script .pl
  Options ExecCGI Includes FollowSymlinks
  Order Allow,Deny
  Allow from All
</Directory>

<Directory /home/e-smith/files/ibays/sql-ledger/html/users>
  Order Deny,Allow
  Deny from All
</Directory>

(end of insert, hit CTRL/X to finish the edit)

- now expand the template and thus create a new httpd.conf file

# sbin/e-smith/expand-template /etc/httpd/conf/httpd.conf

- restart the web server

# service httpd restart

- clean up what you want.  For example get rid of the gcc downloads and uninstall the gcc RPMs if you feel like it.

- Create the default SQL-Ledger user and enable the plpgsql language ...

su - postgres -c "createuser -A -D sql-ledger"
su - postgres -c "createlang plpgsql template1"

Note (12 April 2005)  You may want the first of these two commands to read:
su - postgres -c "createuser -a -d sql-ledger"

The -A says "don't allow user to create other users" whereas -a says "do allow user to create other users".
Similarly -D says "can't create databases" whereas -d says "can create databases".

I think the latter is required for defining different datasets in sql-ledger.
- That's it on the SME server.  Now go to an attached PC, fire up a web browser and visit http://your-server-name>/sql-ledger/admin.pl
- Log on with blank password - and now you know as much as me!!!

Hope this helps someone.
 

[dave_d]

Doh!! I forgot a step ....

AFTER installing SQL ledger and BEFORE trying to run it you'll need to create a user and add PL/PGSQL into template1.  All this is fully described in the README file that's to be found in the '/home/e-smith/files/ibays/sql-ledger/html/docs' directory.

The rest of the setup instructions are here too!

[cydonia]

Thanks Dave.

I just check out the sql-ledger website and it looks great.  Might put your howto, to the test soon .


Cheers
Tristan

[tbcomputing]

Hi Dave

Thanks for the howto - installed perfectly first time.

Since like me you are in the UK have you any thoughts on a UK chart of accounts or UK accounting standards compatibility

Cheer

Tony

[dave_d]

Hello Tony,

I'm glad it worked out for you!

Now, as for the USE of the product, you have me at a disadvantage!  I must confess that the mere sight of accounts fills me with fear and the certain knowledge that someone is going to make a lot of money out of me - be it accountant, bookkeeper, or government - and there's nothing I can do about it.  This stems from my complete inability to understand the basic concepts of accounting.  Quantum physics - OK, accounting - after 40 years still don't understand.

Anyway, I started this exercise because several clients of mine are complaining that accountants are increasingly only ready to accept accounts prepared on Sage or Quickbooks and both of these are becoming prohibitivley expensive and so they're looking for alternatives.  Thus, in my crusade to expunge Windoze from systems that I manage I started looking at GPL/OpenSource products, fell across SQL-Ledger, and the rest you know.

As for a UK COA, that was the first quetion I was asked by one of my clients and I had to confess that I didn't know what he was talking about!  Anyway, it was explained to me in a child-like manner and so now the hunt is on!  I did come across a reference to UK CAO early on but I stupidly lost it.  I'll post here if I find anything worth reporting.

Regards,

Dave

[cydonia]

Hi Dave,

I got this error when trying to restart httpd:


Starting httpd: Syntax error on line 1697 of /etc/httpd/conf/httpd.conf:
Invalid command '</Directory>Alias', perhaps mis-spelled or defined by a module not included in the server configuration
                                                           [ FAILED ]



My file, 95sql-ledger file looks exactly like in your howto.

Any idea why its doing this?  

EDIT:  I'm using SME 6.0.1, not 6.0.1.01, if that makes a difference.

Thanks.

[cydonia]

Ok, it seems to be working.

What i had to do was rename 95sql-ledger to 97sql-ledger.  Since i already had something in 95 under httpd.conf.

If this is wrong for any reason, let me know, perhaps its the difference between 6.0.1 and 6.0.1-01

[dave_d]

Looks OK to me!

I think that the number in front of the file name in the template tree simply defines the order in which the files are processed.  IIRC I've had to check out the order of processing in the past if ever I wanted to get anything inserted in a generated file in a particular place.  You have to figure out what order the particular template directories are processed in too!  I think the expand-template process starts with /templates and builds a list of things to do and then modifies this list by looking in /templates-custom before going ahead and doing the expansion.

If I'm wrong here I'm sure that a more ecxperienced member will put me right!

Regards,

Dave

[TrevorB]

I think that the number in front of the file name in the template tree simply defines the order in which the files are processed.  IIRC I've had to check out the order of processing in the past if ever I wanted to get anything inserted in a generated file in a particular place.  You have to figure out what order the particular template directories are processed in too!  I think the expand-template process starts with /templates and builds a list of things to do and then modifies this list by looking in /templates-custom before going ahead and doing the expansion.

That's a pretty good description of what happens.
expand-templates just works in the same order as 'ls' (with templates-custom overriding templates).

Trevor B

[cydonia]

Has anyone got the PDF function within SQL-Ledger to work?

The FAQ says that you need something called Latex installed for it to work.

RequestedDeletion mentioned it a while back here.  But there is no solution in the thread...


Also, how do i set currency?  I know its only basic but i cant find setting or anything like that.


Tristan

[dave_d]

Hello Tristan,

I didn't worry about Latex when I did the installation because it was listed as optional and all I wanted to do at the time was determine whether or not SQL-Ledger could be persuaded to run or not.

However, one of my clients seems quite interested and so I've got to learn a little more about how it operates.

In order to get Latex installed, I did the following (I don't think all of the files are needed - but you never know with this sort of work!) ....

Log in as root on a terminal as per the first part of this story.  Make yourself a directory in which to work.

# mkdir -p /downloads/tetex
# cd /downloads/tetex

Now get the files ...

# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/dialog-0.9a-5.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-afm-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-doc-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-dvilj-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-dvips-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-fonts-1.0.7-47.i386.rpm
# wget -nc http://download.fedoralegacy.org/redhat/7.3/os/i386/tetex-latex-1.0.7-47.i386.rpm

Now install the minimum set to get Latex available. Do the installations in this order ....

# rpm -ivh dialog-0.9a-5.i386.rpm
# rpm -ivh tetex-fonts-1.0.7-47.i386.rpm
# rpm -ivh tetex-1.0.7-47.i386.rpm
# rpm -ivh tetex-latex-1.0.7-47.i386.rpm

Now check that Latex is available  ....

# latex -version
TeX (Web2C 7.3.1) 3.14159
kpathsea version 3.3.1
Copyright (C) 1999 D.E. Knuth.
Kpathsea is copyright (C) 1999 Free Software Foundation, Inc.
There is NO warranty.  Redistribution of this software is
covered by the terms of both the TeX copyright and
the GNU General Public License.
For more information about these matters, see the files
named COPYING and the TeX source.
Primary author of TeX: D.E. Knuth.
Kpathsea written by Karl Berry and others.

#


... and now you know as much as I do.  I'm still fighting to get a printer working on my system (I keep getting outbid on eBay 10 seconds before end of auction!!!!).  As soon as I get things working I'll post some more - unless someone else is kind enough to   do so before I get the chance!!  

Anyway, I'm off for a    now.

Regards,

Dave

[guest22]

Now that is something I was waiting for about a year or so! Thx

[cydonia]

Perhaps its just due to my not understanding accounting, but does any one else find SQL-Ledger to be temperamental and illogical?

I can understand why you have to pay for support since you will need a lot of it.


I love the concept, but to me it seems to need heaps of work to make it more friendly and reliable.
Some points that annoy me;

- I define a service, but then when creating an invoice, it never recognises the service no.  Why not just have a drop down list for all service numbers?

- Recording to accounts does not work for me.  I have setup an account, and when i create an invoice i set it to record to that specific account, but it never shows up.

- Settings, the settings are very poorly organised.


Anyway, as i said its a great idea, and i'm still on a steep learning curve for both this, and accounting in general, which may explain my inability to get it to work.

However, i may use this idea and create my own accounting package using php/postgres which would resolve all my problems and ranting:).

[len_chan]

Backups?

Any idea how this will integrate with the backup scripts built into SME server and/or the backup2ws script?  I guess the only important thing to backup is postgresql...

[mophilly]

Perhaps its just due to my not understanding accounting, but does any one else find SQL-Ledger to be temperamental and illogical?

Many programs, like QuickBooks, play tricks with accounting practice. This is to make it easier for non-accountants to use the products. The downside is that you end up with a rather messy set of books that your accountant hates or won't use. Changing is hard.

Also, said programs are frequently light on proper database design, and deliberatly cripple or omit proper export facilities. This makes it impossible to integrate with other databases you may have.

SQL-Ledger follows general accounting practices. That makes it hard for those of who are not accountants. OTOH, you end up with a better set of books.

BTW, the support costs are minor compared to charges for similar support from Intuit and others. The author is very helpful and typically responds quickly.

Hang in there.

[wjhobbs]

Dave,

I was following your excellent how-to and then got stuck.

I succeeded in getting PostgreSQL installed. But then when when it came time to update Perl, I ran into a problem. Your instructions say:

cpan> install DBI
(Lots of things happen - check right at the end to see that the operation completed OK)

But you don't say what to do if the operation did not complete OK.

At the end of the 'install DBI' process I got a message that said:

12 tests and 135 subtests skipped.
failed 2/43 test scripts, 95.35% okay. 0/2058 subtests failed, 100.00% okay.
make: *** [test_dynamic] error 11
   /usr/bin/make test -- NOT OK
Running make install
   make test had returned bad status, won't install without force
 
Any suggestions for getting beyond this would be much appreciated. Please bear in mind that I am not a programmer or an experienced Linux person -- but I can usually follow specific instructions.

Thanks for your help.

John[/quote]

[dave_d]

Hello John,

Did you get past your error condition yet?  If not I may be able to help.  I'm just starting a rebuild of my developement server and so I can run the installation again and check my original instructions.

regards,

Dave

[wjhobbs]

Hi Dave,

The problem persists.

I have a fresh install of 6.0.1-01 on a small test machine. I installed Plone using Des Dougan's RPMs (I still have a minor problem there setting up the ProxyPass in httpd.conf) but that's a separate issue.

I installed PostgreSQL 7.4.2 using Michel's HowTo from Sleepy SME as the first step in your HowTo.

I then created an i-bay as instructed, downloaded and installed the four RPMs as specified.

The '# perl -MCPAM -eshell' seemed to work fine.

But the 'cpan> install DBI' failed as noted in my previous post. The messages flew by fairly quickly -- but I seem to recall something that seemed to indicate it expected a component that wasn't there. (I'm so new to this stuff that I don't know how to scroll back through the messages to find out what I saw.)

And that's where I am. I just bought a copy of "Learning Perl" so that I can get better at understanding what the SME Server is all about. I'm a real novice.

I would appreciate any help you can give.

John

[dave_d]

Hello John,

I'll install it again during the next day or so using a clean 6.0.1-01 server.  As soon as I've found time to do it I'll let you know how things went!

Regards,

Dave

[wjhobbs]

Dave,

What version of perl do you have installed? I think SME 6.01 has perl 5.6 installed and I think something in the DBI install process needs 5.8 -- but I don't know if this is the source of my problem.

John

[dave_d]

Hello John,

I just completed an installation of SQL-Ledger on a new 6.0.1-01 server.  I've edited the very first post in this thread to include a couple of steps that I missed out! (the result of continually using a single server to do lots of test work, I'm afraid).

Anyway, it worked for me!

Regards,

Dave

[wjhobbs]

Dave,

Your revised procedure worked like a charm!

Thanks.

John

[warren]

Dave,

Great stuff. One change though,

# pico 95sql-ledger
(insert the following ...)


Alias /html /sql-ledger/ /home/e-smith/files/ibays/sql-ledger/html/

Should Read

Alias /sql-ledger/ /home/e-smith/files/ibays/sql-ledger/html/

Thnks

Warren

[dave_d]

Thanks Warren, you're quite right. I must be getting sloppy in my dotage!

I've changed the post accordingly.  See too my comment near the bottom of the post regarding the creation of the sql-ledger user.

Regards,

Dave

[jester]

I've been trying your howto but on a SME7beta8 installation...

- I had postgres allready installed with an (test) installation of open-xchange (postgresql-7.4.8-1)
- A 'yum install gcc' seemed to work for installing the needed files for the extra perl modules.
- Installing CPAN, MD5, Storable, DBI went fine as well

But installing DBD::Pg is different from the install in SME6.0. I get the following questions wich i can't seem to answer correctly 'cause the install is ending with an NOT OK error:

* Path to pg_config ?!
* Postgres major version number ?! (7)
* Postgres minor version number ?! (4)
* Postgres patch number ?! (
* valid PostGreSQL include dir ?!
* valid PostGreSQL library dir ?!

Someone who knows the (right) answers to the above questions?!

Regards,
jester.