SQL-ledger

(by Dieter Simader) Version:3.2.12

revised: 01-Jul-2024

Perquisites:

Basic Server (Ubuntu 22.04 64bit) with Webmin linked (optionally) to a domain-name: such as example.com. Note: the IP address can simply be used to access the site.
Installed Firewall.
Installed FTP server

Tip#01: Given the sensitive nature of an online ledger, it is recommend that the ledger is setup as the sole principal service on an independant server without a domain-name and that the extra secure measures (in Extras) are applied. One canapply a password for extra security on the main admin user: 'postgres' in the PostgreSQL Server. For demonstration purposes, the username used here is 'sammy' and the server is 'imega'.
Tip#02: SQL-Ledger can be used with the Nginx Webserver (and optionally with iRedmail) see Tip#9 in Extras
Tip#03: The advantages of version:3.2.12 are in its ability to attached documents, to manage users via HR moreover with a payroll and the function to backup and restore (Admin only) but where backups must also be done using Postgres tools: pg_dump and pg_restore for a fully assured backup strategy. That said, these abilities are not essential for most small organizations/business where the enhanced Version by - Armaghan Saqib (ledger123: an enhanced SQL-ledger) is more suitable with the advantage of tagging warehouse deliveries with a deliver note. See Tip#8 in Ledger-Extras: For the install replace 'sql-ledger-3.2.12.tar.gz' with ledger123v2833-SQLledger992018.tar.gz and after setting up the Postgres server with user:sql-ledger, follow the instructions below for the manual install.

Step01: Install the supporting software dependencies

Using PuTTY, logon as your regular user (eg.sammy)and 'copy and paste' this one line command (that in italics) below:

sammy@imega:$ sudo apt -y install acpid postgresql postgresql-contrib libdbi-perl libdbd-pg-perl texlive texlive-latex-extra texlive-pstricks
sammy@imega:$ sudo apt -y install apache2 // install the Apach2 webserver.

The Postfix configuration for this purpose only is required to be "No Configuration". Press the 'tab' then the return key to continue and accept all settings.

Step02: Setup PostgresSQL Server

Now go to the PostgreSQL server-> (Icon) PostgreSQL Users:

a. First click on user: postgres to set a password by activating the "Set to" button and entering a password then save. Then create a new user "sql-ledger" that can create databases (see "Can create new databases? - check "Yes"). Give this user a password. It will be the 'sql-ledgerpassword' for use with the Postgres tools: 'pg_dump' and 'pg_restore'.
b. Go to the the tab "Create a New Allowed Hosts". Click to enter and under the section "Host Address", click on the entry called "Local Connection". At "listed users" activate the button beside, type in "sql-ledger". Then set to "No Authentication"
c. The new setting will be at the bottom of the "Allowed Hosts" list. With the use of the "up-arrow", click several times to move the new setting to the top of the list.
Note: Alternatively this file is found at /etc/postgresql/11/main/postgresql.conf and, via 'nano' paste this line following that for user 'postgres':
local all sql-ledger trust
d. Restart the PostgresSQL server.

Step03: Setup Apache2
The 'cgi' module needs to be enabled:

sammy@imega:$ sudo a2enmod cgid

Step04: Upload SQL-ledger and install it

a. Via the Setup Programme
Do the following commands and if successful, jump to Step05:

sammy@imega:$ sudo mkdir /usr/local/sql-ledger
sammy@imega:$ cd /usr/local/sql-ledger
sammy@imega:/usr/local/sql-ledger$ sudo wget https://sql-ledger.com/source/setup.pl
sammy@imega:/usr/local/sql-ledger$ sudo perl setup.pl // select 'i' to install and when it reads: /sql-ledger-httpd.conf, use 'Ctl-c' to exit.
sammy@imega:/usr/local/sql-ledger$ ls -ls // verify that the files have be installed.
sammy@imega:/usr/local/sql-ledger$ sudo chown -hR www-data:www-data users templates css spool doc
sammy@imega:/usr/local/sql-ledger$ sudo chmod -R 775 users templates css spool doc
After the setup is complete, verify that the following file exists:
sammy@imega:/usr/local$ sudo nano /etc/apache2/conf-enabled/sql-ledger-httpd.conf // paste in these directives: sql-ledger-httpd.conf

Note: Sql-ledger can always be upgraded using the above setup.pl application within always the sql-ledger directory.

b. Manual Install

The SQL-ledger v3.2.12 can be downloaded (4Mb) HERE

a. Using FTP (Filezilla), upload the enhanced SQL-ledger application (~4Mb) to the home directory of your regular user (eg.sammy)

b. Using PuTTY, logon as your regular user (eg.sammy)and move the SQL-ledger application to the /usr/local directory then go to that directory:

sammy@imega:$ sudo mv sql-ledger-3.2.12.tar.gz /usr/local
sammy@imega:$ cd /usr/local
sammy@imega:/usr/local$ ls -ls Note: to list the files therein

Decompress the tar file and, after entering the sql-ledger sub-directory, create the spool directory and to spool, users, css and tempates directories. Finally give writing permissions to the sql-ledger sub-directory:

sammy@imega:/usr/local$ sudo tar xzvf sql-ledger-3.2.12.tar.gz
sammy@imega:/usr/local$ cd sql-ledger
sammy@imega:/usr/local/sql-ledger$ sudo chown -hR www-data:www-data users templates css spool doc
sammy@imega:/usr/local/sql-ledger$ touch users/members.default
sammy@imega:/usr/local/sql-ledger$ sudo cp sql-ledger.conf.default sql-ledger.conf
sammy@imega:/usr/local/sql-ledger$ sudo chmod -R 775 users templates css spool doc

Step05: Adapting SQL-ledger to Apache Webserver

a. Establishing the link, do the following:

sammy@imega:/usr/local/sql-ledger$ sudo nano /etc/apache2/conf-enabled/sql-ledger-httpd.conf // paste in these directives: sql-ledger-httpd.conf

Note Final Step: Via Webmin->Apache2 Webserver->Global Configuration->Edit Config Files, go to the main configuration file 'apache2.conf' add this specific entry at the bottom:
IncludeOptional /etc/apache2/conf-enabled/sql-ledger-httpd.conf

Restart the Apache Webserver (Click on button at top right hand corner which applies the changes)

Step06 Open Sql-Ledger:

a. In your browser, enter: http://[IP-address]/sql-ledger/admin.pl Add a new and strong password (minimum 14 mixed characters/numbers is recommended); this entry is unique, making the owner of the password the Master Controller of all datasets and by using this password, one can create 'datasets' (the app's word for databases)-- simply via "Add Dataset". Note: No extra entries are needed to input here (eg. host, password) if the user:sql-ledger has no password.

b. In the "Create Dataset" dialogue box, type in the dataset name (eg mytest2022db) along with the other inputs.

Note: Know that 'admin' is the fixed username followed by the dataset ( eg. admin@mytest2022db) for the administrator of that particular dataset. The 'Administrator' entry when creating the dataset is used only to reflect the administrator's name upon opening the ledger.

Thereafter go and click on "continue". This will create the database (eg "mytest2022db") with the default settings (a VAT tax-related British style Chart of Accounts layout). Although, one has the option of choosing a different layout for the Chart of Accounts.

c. Open SQL-Ledger (http://[IP-address]/sql-ledger/login.pl) to login. For the Administator of any dataset, always logins with username: admin@[dataset].

Other Notes:
1. All other users to that database are set by this administator by logging in and establishing other users via the "Employee" section -- and by which setting of particular access rights is done for each new user. If an employee is not to have access, do not enter a login and password for that employee
2. Each enabled new user can then logon as: [login-name]@[dataset]
3. Moreover under 'System -> Audit control', the Administrator can set transactions to be set to enforce the strict reveral process/non-editing of posted entries.
4. Always 'Update' prior to saving an entry or a posting - an important general rule for SQL-Ledger.

Ledger-Home	Ledger-Extras
Ledger-Backup	Ledger-Restore