Installing a PostgreSQL Database Schema for SQLBill

Make sure your PostgreSQL server has tcpip_socket = true option enabled in the config and you can access the default database (template1) with the user postgres.

To create the schema, follow these steps:

1. Change the current directory to 'contrib/sqlbill/sql/src'. Connect to a database 'template1' (or template0) running by PostgreSQL server as the admin user ('postgres'?) using the psql utility. Example: 

   psql -U postgres -h IP_ADDRESS template1

(where IP_ADDRESS is an address of a machine that is running PostgreSQL - 'localhost', for example)

2. Create a new database for the prepaid system using c_voipdb.sql script: 

   \i c_voipdb.sql

(if the current directory is not the one with c_voipdb.sql file, please use full path here)

3. Exit psql utility (\q command).

4. Enable PL/pgSQL procedural language for the new database (you may need to install appropriate optional PostgreSQL module with PL/pgSQL package - postgresql-pl).

Use this shell command: 

   createlang -U postgres -h IP_ADDRESS plpgsql voipdb

5. Connect to the new database 'voipdb' as a new user 'gkradius'. You may need to add an entry in pg_hba.conf PostgreSQL configuration file for the pair (gkradius,voipdb) to allow database connections (something like "host voipdb gkradius 127.0.0.1/32 md5"): 

   psql -U gkradius -h IP_ADDRESS voipdb

It is recommended that after all 'gkradius' user is made more secure (add md5 password, limit pool of IP addresses, etc). 6. Create all tables, indices, triggers and procedures using c_all.sql script: 

   \i c_all.sql

Some warnings (marked with NOTICE) may appear during database schema creations, but can be ignored safely.

7. You are done, the database schema is ready.

8. You can see an example how to import a tariff table from a file using f_voiptariff.sql script and voiptariff.asc file. Change directory to 'examples', connect to the database and enter the following commands: 

   \i f_voiptariffdst.sql
   \i f_voiptariff.sql

9. A more convenient way to manage tariff tables is to use a provided spreadsheet named 'tariffs.sxc' (OpenOffice.org Calc) and f_pricing.sql or f_merged.sql scripts. 

   In the spreadsheets you have the following sheets:
   *) Parameters - default settings for values like billing units, VAT tax rate, etc.
   *) Destinations - prefix table (used to build voiptariffdst)
   *) Pricing - prices for Destinations (used to build voiptariff)
   *) Merged - an advanced table, if Destinations and Pricing combination 
      is not flexible enough (e.g. if one wants to assign different prices
	  for each prefix, maintaining same destination name/group)

To build your own tariff table you fill either Destinations and Pricing sheets and use f_pricing.sql script or fill Merged sheet and use f_merged.sql script.

After the sheets are filled with tariffs, you need to export Destinations/Pricing or Merged sheets to a CSV file (using Save As... option for each sheet). The CSV file is expected to be Tab separated, with cell values surrounded with ' character. To achieve this, you need to toggle "Edit filter settings" check box in the "Save As..." dialog.

The f_pricing.sql script expects two files: pricing.csv and destination.csv. The f_merged.sql script expects only one file: merged.csv.

To insert/update you tariffs into a database, you run the following commands: 

     psql -U gkradius -h IP_ADDRESS voipdb

   (at psql prompt):

   BEGIN;
      
     \i f_pricing.sql
   or
     \i f_merged.sql

   COMMIT;

The 'examples' directory contains tariffs.sxc spreadsheet with a sample tariff table and pricing.csv, destinations.csv, merged.csv files generated from the sample tariff table. In case of any problems, use ROLLBACK instead of COMMIT to undo changes.


Last updated: 25. Dec 2020