Server - Contents

SQL-ledger

(by Dieter Simader) Version:3.2.10

revised: 26-Jan-2022

Perquisites:

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'.

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 apache2 postgresql postgresql-contrib libdbi-perl libdbd-pg-perl texlive texlive-latex-extra texlive-pstricks
sammy@imega:$ sudo apt -y install postfix mailutils // optional dependencies to enable emails but not essential.

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

Login to Webmin in your browser (user:root), click on "Refresh Modules" and go to the PostgreSQL server.

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

Postgres Permissions

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 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

b. Manual Install

The SQL-ledger v3.2.10 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.10.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.10.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$ cd /usr/local
sammy@imega:/usr/local$ sudo chmod -R 775 sql-ledger/

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