Section 3.1: ODBC Up Chapter 3: Set up Chapter 4: Problems 

3.2 Databases

You will, in general, need to have a database engine available for Aubit4GL.
If you already have one installed (e.g. Informix, PostgreSQL, MySQL, SQLite3, or an ODBC application) skip this chapter.

3.2.1 Informix

We do not supply documentation about installing or upgrading Informix. It is proprietary software and IBM who supply it give ample documentation.
But remember that you will need to obtain and install the Informix CSDK (Client Software Development Kit) in order to get the esql package.

3.2.2 PostgreSQL

Most people using Aubit4GL will use it in conjunction with PostgreSQL and most of the current development effort in Aubit4GL is with PostgreSQL.
Aubit4GL now does not need PostgreSQL’s esqlc package. Instead use the pg8 module.

3.2.2.1 Overview

PostgreSQL is a fully ANSI and ISO compliant Relational Database Management System (RDBMS) which arose from a project called Ingres at the University of California, Berkeley under the direction of Professor Michael Stonebraker in the 1980s. Ingres was spun off to become a commercial product. Michael Stonebraker sold his developments in Object Relational technology to Informix and the University renamed its free Ingres as Postgres and continued development. The name change to PostgreSQL reflects its conformance with the official ANSI-ISO SQL3 public standard.

3.2.2.2 Documentation

The official website for PostgreSQL documentation is:
http://www.postgresql.org/docs/8.3

3.2.2.3 Installation

PostgreSQL is installed from RPM packages supplied with the Linux Distribution. OpenSUSE provides about 30 Postgres packages of which the following are essential:
These packages are best and most easily installed using the SUSE Yast2 program.
Take the route: Software -> Software Management. Enter: postgres in the search field and check the boxes for the above packages (and any others you want to install).
Alternatively you may install from the shell command line: 
yast2 -i postgresql
yast2 -i postgresql-server
...
The simple package name is sufficient. Yast2 will find the full path and filename of the package.
You can also use the zypper equivalent of yast2: 
zypper install postresql-server
...
Once installed, the RPM system will create a user postgres intended to be the superuser for PostgreSQL databases. (In principle the database superuser should be a different user from root).
For Debian derived distributions of Linux such as Ubuntu the equivalent installer is 
apt-get install postgresql
...

3.2.2.4 Instances

PostgreSQL shares the same notion as Informix of a database instance being a collection of databases with common resources (known as a cluster in PostgreSQL parlance).
3.2.2.4.1 initdb
Instances are created with the command initdb. For example:
initdb --pgdata=/local/data/dev
In the example, dev is our name for the development instance of the database. You create a directory like /local/data/dev in our example and pass it to the option: --pgdata
By default, postgresql will build an instance in /var. This is vulnerable to being lost when you upgrade or reinstall the operating system. It is much better to create disk area outside the normal operating system directories. Hence the use of the --pgdata=/local/data in our example.
initdb populates its given directory with all the system files, common catalogue tables, and a database called template1 which is copied when any user databases are subsequently created in the instance.
To connect to the instance created, as a user you set the environment to include:
For more details, use the man or info commands: e.g. info initdb
The backends need PGDATA to identify the directories where the database files are.
The backends and frontends need PGPORT (or command line options) to identify which instance of the program: /usr/bin/postgres to communicate with.
To test that all is well, try the command:
env | grep PG
and you should see PGDATA, PGPORT, PGDATABASE, and PGDATESTYLE with appropriate values.
3.2.2.4.2 pg_ctl start
Having created a database instance, you must start the postgres engine using the commands:
pg_ctl start -l /local/data/dev.log
The -l option determines where the instance will write its log.
Unlike Informix’s threaded architecture, PostgreSQL is single-threaded and a new postgres backend process is spawned as each new connection is made to the engine instance.
Another check you can make to see if postgres instances are running is the command: 
netstat -pa | grep postgres
This will return a line of output showing UNIX domain sockets with an endpoint something like: 
/tmp/.s.PGSQL.5432
where 5432 is the TCP/IP port for the socket.
3.2.2.4.3 pg_ctl stop
The pg_ctl command is also used to stop the postgres engine: 
pg_ctl stop
There are in fact 3 stop options for pg_ctl: smart (the default), fast, and immediate
Calls to pg_ctl {start|stop} can be used at startup and shutdown via the usual /etc/init.d scripts.
PostgreSQL will not allow the pg_ctl command to be run by any user other than the Postgres super-user.
3.2.2.4.4 createdb
Having created an instance, and having started postgres with pg_ctl start, you may now create a database. There are 2 ways to do this
The shell command createdb is just a wrapper for the SQL statement. In both cases, the database name defaults to the user’s name. We override this by setting the environment variable: PGDATABASE e.g.
export PGDATABASE=mydb.

3.2.2.5 Environment

Set up the following environment variables for PostgreSQL: 
export PGDATABASE=mydb 
export PGDATA=/local/data/$INSTANCE
export PGPORT=5432 # or 5433 or 5434
export PGDATESTYLE=’SQL, dmy’
The character encoding used by the PostgreSQL engine will default to the Linux LOCALE which is UTF-8. This seems to work fine and we can leave it that way unless you run into trouble with your LANG environment and special smbols like the British pound sign. UTF-8 is likely to become a universal practice in the future.
By default, PostgreSQL tries to connect to a database with the user’s login name. The variable PGDATABASE overrides this. When you run commands like: psql it will connect to your database by default.
By default, PostgreSQL places its data in the /var partition. We regard this as undesirable as it is vulnerable to loss or corruption when the OS is reinstalled or upgraded. We install it in the partition:/local. To ensure this, set the environment variable PGDATA.
By default, PostgreSQL listens on port 5432 for frontend programs seeking to connect. When we run multiple instances of PostgreSQL, we need to have a separate port assigned to each instance. If, for example, you have 3 instances: for development, training, and production, you could make the following arbitrary assignments:
The frontends (e.g. psql, pg_ctl, createdb, etc.) all rely on the environment variable PGPORT if it is set. PGPORT can be overidden by command line options, otherwise the default is to use port 5432.
We need to set PGDATESTYLE to SQL, dmy to get your style of date format. The default is American style middle-endian dates (mdy). You can configure European style date within SQL also using the syntax: SET datestyle TO "SQL, dmy";
Set the following environment variable for Aubit4GL: 
export A4GL_LEXTYPE=C
export A4GL_SQLTYPE=pg8
The above environment variables may be set up in

3.2.2.6 Maintenance

3.2.2.6.1 vacuumdb
The vacuumdb command is PostgreSQL’s equivalent to the Informix UPDATE STATISTICS command. You can run it from the command line or within psql or adbaccess as an SQL statement. Its Synopsis: 
vacuumdb [--full] [--analyze]
The equivalent SQL syntax is: VACUUM FULL ANALYZE
with the FULL and ANALYZE optional.
For documentation on vacuumdb, use info or man: 
info vacuumdb
3.2.2.6.2 pg_dump
The PostgreSQL command: pg_dump is used in place of Informix’s dbschema and dbexport commands.
Use pg_dump -s for a schema of the whole database.
Use pg_dump -st access for a schema of the access table.
Use pg_dump for a complete export of the schema and data. The database will be placed in a single file of SQL statements which are sufficient to reload the data using the commands: psql or adbaccess

3.2.2.7 Commands

The installation of PostgreSQL results in the following commands being installed in /usr/bin: 
clusterdb
createdb
createlang
createuser
dropdb
droplang
dropuser
pg_dump
pg_dumpall
pg_restore
psql
reindexdb
vacuumdb
initdb
ipcclean
pg_controldata
pg_ctl
pg_resetxlog
postgres
postmaster
rcpostgresql
--- contributed --
oid2name
pgbench
vacuumlo
Many of the above are simply shell wrappers around the same command in SQL. Some (e.g. postmaster and postgres) are meant to be invoked only by other commands (e.g.: pg_ctl). Use the commands info or man to learn more.

3.2.2.8 psql

psql is the PostgreSQL SQL interpreter. Unlike Informix’s dbaccess it is not a form mode menu program but instead uses the Linux readline library to buffer a historied command line interface very similar to the Unix shells.
Use psql to execute SQL statements.
To use psql to execute an SQL file: 
psql -f filename [mydb]
You can echo SQL statement(s) into psql as follows: 
echo "select * from agent;" | psql
This is faster for one-line statements as psql exits immediately after execution, but be careful to protect special characters (like ;) from the shell by quoting.
Another useful argument for psql is -l which lists the available databases and exits immediately.
Aubit4GL provides adbaccess as a workalike for Informix’s dbaccess. This is fully featured when connected to an Informix database but still lacks some capabilities with PostgreSQL as a backend. You will likely prefer adbaccess for tables with rows of many columns (such as the table: access).
We recommend that you not use PostgreSQL’s esql package. Aubit4GL’s pg8 library provides all the necessary connectivity with the PostgreSQL engine.

3.2.2.9 Stored Procedures

PostgreSQL supports stored procedure languages including its own native PL/pgSQL. This is similar to Informix’s SPL but has quite different syntax.
We need to use PL/pgSQL to perform the translation of variables containing CONSTRUCTed clauses from Informix MATCHES statements into PostgreSQL regular expression (RE) statements (using the ~ operator).
The algorithm of conversion is:
3.2.2.9.1 PL/pgSQL matches function 
CREATE FUNCTION matches_to_regexp(str text, esc text)
  RETURNS text
AS $$
DECLARE
   lv_rval text;
   lv_c char;
   lv_cnt int4;
BEGIN
   lv_cnt:=0;
   lv_rval:=’^’;
   for lv_cnt in 1..length(str) loop
       lv_c:=substr(str,lv_cnt,1);
       if lv_c=’?’ then
           lv_rval:=lv_rval||’.’;
       elsif lv_c=’*’ then
           lv_rval:= lv_rval||’.*’;
       elsif lv_c=’.’ then
           lv_rval:=lv_rval||E’\.’;
       else
           lv_rval:=lv_rval||lv_c;
       end if  
   end loop;
   if substr(str,length(str),1) != 
         ’*’ then
       lv_rval := lv_rval || ’$’;
   end if;
   return lv_rval;
END;
$$
    LANGUAGE plpgsql;
3.2.2.9.2 $$ quoting
In the above code, $$ is a quoting mechanism which allows the usual single and double quotes to be used inside the stored function without interference with the syntax of the function declaration. The $$ can also be replaced with an identifier between the $ symbols: e.g. $fred$ and differently identified quotes can nest within each other.
3.2.2.9.3 E escaping
In the above stored procedure the E’\.’ expression tells pg to treat the \. literally and not interpret the \ as an escape mechanism.
The PL/pgSQL syntax also allows CREATE OR REPLACE FUNCTION f() during development to obviate the need for a DROP FUNCTION f() when modifying and testing.
3.2.2.9.4 PL/pgSQL install
You may need to run the following SQL statements before the above will be accepted: 
CREATE LANGUAGE plpgsql;

CREATE FUNCTION plpgsql_call_handler()
RETURNS opaque AS ’/usr/lib/postgresql/plpgsql.so’,
’plpgsql_call_handler’ LANGUAGE c;

ALTER FUNCTION public.plpgsql_call_handler()
OWNER TO informix;
A good technique is to add the above stored procedure statements to the template1 database so that in the event of dropping and reinstalling your database the matches_to_regex() function will be automatically built again by the statement: CREATE DATABASE mydb;

3.2.3 MySQL

Install various Mysql components e.g. on Ubuntu 
apt-get install mysql-common \
   libmysqlclient15-dev \
   mysql-server mysql-client
You should now have mysql running.
MySQL setup example:
#set up the MySQL user userpt18z97 but first of all set up a root passwort in mysql for safety
#start MySQL as root, we assume you run MySQL only locally for the time being
As root: 
mysql

mysql>update user 
   set password=PASSWORD("x5a4p4i7") 
   where User=’root’;
From now on you start MySQL as root with "mysql -p"
Create your initial database 
mysql>create database mydb;
Tip: if you want to be able to switch databases in your programs create one database emptydb which has all the tables you use in mydb but without data. This is then used in the .per and the .4gl at compile time and you only switch later to the real database mydb with data in your 4gl application. Naturally emptydb must reflect the exact db-schema of mydb at all times.
Grant whatever privileges you want the user to have, below is the maximum (not advisable in production) 
mysql>grant all privileges on mydb.* 
  to ’userpt18z97’@’localhost’ 
  identified by ’p5z1m7u9’ 
  with grant option;
All further operations on the db (e.g.: create table...) should be done only as user userpt18z97
...
#Spanish keyboard with Euro sign for example, 7-bits chars (ncurses) unicode would be a different story 
export LANG=es_ES@euro
export A4GL_SQLTYPE=mysql
export A4GL_SQLUID=userpt18z97
#can be different from logon password, of course 
export A4GL_SQLPWD=p5z1m7u9
export A4GL_UI=TUI
export A4GL_DBNAME=mydb
export DBDATE=dmy4.
# for example with putty settings example Spanish keyboard : Terminal/keyboard "linux",
# putty contd: ...Window"80"x"25",Translation "ISO-8859-15:1999 (Latin-9, "euro")", etc... 
export TERM=linux
Our thanks to Karl Rumpf (klrumpf@gmail.com) for this advice.

3.2.4 SQLite3 

export A4GL_SQLTYPE=sqlite3
export A4GL_LEXTYPE=C
DBNAME needs to contain the full path to where your SQLite3 database is. e.g.: 
export DBPATH=/home/john/data
When the 4glc compiler encounters the statement: 
DATABASE mydb
It will search the DBPATH directories for a database file: mydb
SQLite3 is unusual in that it does not have an SQL statement to create a database. You create a database by supplying the database path and name on the calling line. e.g.: 
sqlite3 /home/john/data/mydb
If the mydb file does not exist, it will be created. Note that SQLite3 needs the full path to the database file unless it is in the current directory. Aubit4GL syntax does not accept a path in the DATABASE statement or the CONNECT TO statement, hence the need to supply the path in the DBPATH variable. Once Aubit4GL has found your full pathname for the database in $DBPATH, it will subsequently use that full path in all its communication with the SQLite3 library.

3.2.5 SQLServer

Bernard Moreton (itman@tnauk.org.uk) has this advice for setting up Aubit4GL to work with SQLServer.
SQLServer is a Microsoft database engine available only on Windows.
To access SQLServer, from a Linux machine you need to install
I run Aubit4GL against a (pre-installed) MS SQLServer database. This is a matter of ’needs must’, and I would not necessarily recommend that database engine as first choice from cold.
On MS Windows, there is little to do. Users run a batch file to set the environment and start the core menu module: 
@echo off
set AUBITDIR=y:\a4gl
set A4GL_HOME=y:\a4gl
set PATH=%AUBITDIR%\bin;%AUBITDIR%\lib;%AUBITDIR%\local-bin;%PATH%
set A4GL_SQLTYPE=odbc32
set A4GL_DBDATE=DMY4/
set A4GL_UI=TUI
set LOCAL_BIN=y:\a4gl\local-bin
set A4GL_CLASSPATH=y:\a4gl\local-bin
set ALLOWUSINGEXT=Y
set A4GL_ALWAYS_EXTENDED_FETCH=Y
set A4GL_STATUSASCOL=Y
set ALLOWDYNAMIC=Y
%AUBITDIR%\local-bin\tnauk.exe
exit
Permissions for users are all set up through the normal MS database system.
On W32, Aubit is used only for reporting purposes, since the database is the core of a proprietary membership system; but running from Linux, where all the development is done, I also have a number of modules that write to the database under controlled conditions.
On Linux, freeTDS is required; and sqlconvert/INFORMIX_SQLSERVER.cnv is already present in the standard build. Access permissions are set up by the normal .aubit4gl.acl
 Section 3.1: ODBC Up Chapter 3: Set up Chapter 4: Problems