SQL-Ledger Database Explanation

by Armaghan Saqib, Wednesday, December 14, 2022, 02:09 (471 days ago)

SQL-Ledger uses postgresql as database engine. You can create as many databases in Postgres as you want; each with its own tables, indexes etc.

In SQL-Ledger, whenever you create a new dataset for a business using admin.pl, a new database is created and populated with tables and indexes required for sql-ledger.

I strongly recommend to browse any existing sql-ledger database. There are 3 tools which you can use to look into a postgresql database.

  • psql is a command line tool.
  • phpPgAdmin is browser based tool.
  • pgadmin3 is a desktop based tool.

These tools will allow you to view the structure of tables which were created using admin.pl as well as data stored in these tables.

Within the sql-ledger folder, database table and indexes information is stored in the following files which are used to create a dataset using admin.pl

  • sql/Pg-tables.sql
  • sql/Pg-indices.sql

Open these files in your text editor and go through the code to get an understanding of the database tables used in sql-ledger.

sql/ folder

by Armaghan Saqib, Thursday, December 15, 2022, 01:02 (470 days ago) @ Armaghan Saqib
edited by Armaghan Saqib, Thursday, December 15, 2022, 01:07

'sql/' sub folder within sql-ledger folder 3 type of sql files.

1. SQL-Ledger database tables creation files. These are:

Pg-tables.sql
Pg-indices.sql
Pg-functions.sql
Pg-custom_tables.sql

2. Chart of accounts

These file names end with '-chart.sql' and contain country/industry specific chart of accounts. When you create a new data set using admin.pl, chart of accounts selection shown there is generated from filenames in this folder (by removing '-chart.sql' from the file name)

You can also add your own chart of accounts in this folder with name like 'MyIndustryChart-chart.sql' and it will appear in admin.pl as 'MyIndustryChart' for you to select when creating a new dataset.

3. Database upgrade files

These files are used to upgrade existing sql-ledger databases whenever a new enhancement is made which adds new tables or table columns.

These are named as 'Pg-upgrade-oldversion-newversion.sql

These are executed automatically whenever you login to sql-ledger first time with upgraded code.

Exercises:

1. Browser sql/ folder and view some of the files there to get a clear idea of what is happening there.

RSS Feed of thread