VoIP Billing Platform for GnuGk
Copyright (c) 2004, Michal Zygmuntowicz

This work is published under the GNU Public License (GPL)
see file COPYING for details


Installing PostgreSQL Database Schema for GnuGk prepaid platform
=====================================================

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 '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 approtiate 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 fetch 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, maintaing 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" checkbox 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.

Upgrading PostgreSQL Database Schema for GnuGk prepaid platform
=====================================================

During the upgrade, the database should not be in use by other applications,
RADIUS server or GnuGk.

1. Change the current directory to 'src'. Connect to the existing database 
   'voipdb' as a user 'gkradius':

   psql -U gkradius -h IP_ADDRESS voipdb
   
2. Start a new SQL transaction:

   BEGIN;
   
   This is a very important step - as it makes possible to rollback all changes
   in case of any failure.

3. Delete old functions and triggers, best if you use d_all_functions.sql script
   from your existing SqlBill installation, not the one from the version being
   installed:

   \i d_all_functions.sql

4. Upgrade all tables using upgrade_tables.sql script:

   \i upgrade_tables.sql

   Some warnings (marked with NOTICE) may appear during database schema
   creation, but can be ignored safely. The upgrade result is displayed.
   If it indicates failure or there were some errors during upgrade process,
   undo all changes by:
   
   ROLLBACK;
   
   and skip further steps.
   
5. Upgrade all functions:

   \i upgrade_functions.sql

   In case of any errors - ROlLBACK.

6. You are done, the database schema is upgraded. Type:

   COMMIT;
   
   to store changes permanently.