Using Mumps with SQL, PostgreSQL and Ubuntu Linux

Note to students who feel that if they type the same command enough times it will eventually work: you must install PostgreSQL and pcre development libraries or this will not work. This is not optional. There is no workaround. Install the libraries. If you get error messages, it is because you did not install the libraries.

Configuring Mumps to work with PostgreSQL

Normally, Mumps runs standalone with its own B-tree based global array file system. However, it may be configured to enable access to PostgreSQL.

When PostgreSQL access is enabled, you may access RDBMS tables on PostgreSQL by means of SQL statements (usually, SELECT). Additionally, any global arrays you create or access will be created or accessed from the PostgreSQL server. At this point in time, if PostgreSQL access is enabled, globals may only be stored on the PostgreSQL server and not in the local native B-tree.

When you store global arrays, they will be stored in a PostgreSQL table known as 'mumps' on the server. This table may also be accessed by SELECT statements but it is probably not a good idea due to the nature of its format.

It is possible to change the name of this table from 'mumps' to something else but this is not discussed here.

When using the PostgreSQL server, it is possible to construct views of RDBMS tables that can be directly accessed as though they were global arrays. An example of this is given below.

When storing global arrays on the PostgreSQL server, it may be desirable to initiate a SQL transaction with the SQL BEGIN command and ultimately followed by a SQL COMMIT command. This will permit the Mumps global array inserts/updates to run faster but, should there be a failure before the COMMIT, the data will be lost.

Configuring Mumps to work with PostgreSQL

Normally, Mumps runs standalone with its own B-tree based global array file system. However, it may be configured to enable access to PostgreSQL.

It is possible to change the name of this table from 'mumps' to something else but this is not discussed here.

When using the PostgreSQL server, it is possible to construct views of RDBMS tables that can be directly accessed as though they were global arrays. An example of this is given below.

When storing global arrays on the PostgreSQL server, it may be desirable to initiate a SQL transaction with the SQL BEGIN command and ultimately followed by a SQL COMMIT command.

configure prefix=/usr --with-pgdb=/usr/include/postgresql
make
make install

If you are using 64 bit Ubuntu, add the --with-cpu64 option to the configure command line.

When running through the CGI-BIN interface, be sure the files and directories are not owned by root. Make them owned by Apache (www-data) and in the Apache group (www-data). Apache's cgi-bin directory is /usr/lib/cgi-bin. You will need to be root to add/modify files in this directory. Be sure to make Apache (www-data) an authprized PostgreSQL user with the 'createuser' command.

If you want to run scripts on a different machine than the one running PostgreSQL, you need to enable TCP/IP connections to PostgreSQL. To accept TCP/IP connections, you must start postmaster with the -i option and the connecting clients' IP numbers should be in the file pg_hba.conf found in the PostgreSQL data directory.

Note: to enable a group of IP numbers, your IPV4 address should look like:

host    all         all         134.161.0.0/16          trust

Where the high order 16 bits must match (134.161) but the remaining 16 bits can be any value. The above enables any machine on the UNI campus (134.161.....). See the PostgreSQL documentation.

To test a connection, try something like:

psql -h tuatha.cs.uni.edu -d medical

where the '-h' option specifies the remote host to connect to and the "-d medical" specifies the name of the database.

Basic Mumps SQL commands:

sql/d connect-string
where 'connect-string' is either text or &~exp~ such that 'exp' evaluates to a string of text.
The string is the information needed to connect to the PostgreSQL server. At a minimum, it should include the name of the database being connected to:
dbname=medical
Other options include the host, host address, user, password, etc. See http://www.postgresql.org/docs/8.3/static/libpq-connect.html for a complete list (this is the documentation of the C function PQconnectdb() used to connect to the database but has all the possible options listed).
Options you don't include default. For a local server, you probably only need the dbname= parameter and be sure that (1) you are running as a known PostgreSQL user and (2) that you have read/write privs in the directory you are running in.
Examples:
```
sql/d dbname=medical

sql/d host=abc.def.xyz.edu dbname=medical

sql/d hostaddr=123.321.432.321 dbname=medical

sql/d user=joe password=abc123 dbname=medical host=abc.def.xyz.edu

set x="dbname=medical"
sql/d &~x~
```
sql/c
Disconnect from the database. No parameters, no other commands on the line.
sql/f
Clear and prepare a mumps database table in PstgreSQL. This removes any previous mumps database table from PostgreSQL and defines a new one. Do this the first time you try to store Mumps global arrays in PostgreSQL. Subsequently, do this only if you want to delete the mumps global array database and start over.
Theis command (no other line options, no other commands on the same line) creates a table named mumps in the current database. Thus, you must be connected with a server and a database before you execute it. Mumps will place its global arrays in this table. The mumps database has eleven columns the first of which is named 'gbl' and the subsequent ones are named a1, a2, ... a10. It may be queried by SELECT statements but this is probably not desireable.
sql string
Where 'string' can be text or &~exp~ where 'exp' evaluates to a string. Passes a command to the PostgreSQL server. $test will be true (1) if no error is reported.
The builtin Mumps variable $zsql will contain any messages or 'ok' if there were none.
Do not use this command for SELECT queries.
sql/o=fileExp string
where 'fileExp' must evaluate to a valid filename in which will be stored the results of the command and 'string' may be a text string or &~exp~ where 'exp' evaluates to valid SQL command text. The tuple output of the command will be stored in the file with TAB characters delimiting the values of the columns.
The builtin Mumps variable $zsql will contain any messages or 'ok' if there were none.
sql/t=table,size
The sql/t=table,size command is used to switch to a different PostgreSQL table for Mumps global array access. This command only applies if PostgreSQL is being used as the backend storage facility for the global arrays. Both table and size must be valid Mumps/II expressions. 'table' is the name of the PostgreSQL table which will be used for global array references and size gives the number of columns, exclusive of the global array name column (always column one).
Normally, global array access go to the table named 'mumps' which has eleven columns named 'gbl', 'a1', 'a2', ... 'a10'. Theses are all text columns. Each successive column is a level lower in the Mumps global array tree. Many later columns are NULL.
If you create a table (see example below) and you name the columns appropriately (some prefix subset of the ones above), you can access the table with Mumps global array references.

Added builtin SQL functions in latest version of Mumps:

$zsql Returns the SQL server error message for the most recent command or 'ok.'
$zsqlCols Returns a string consisting of the columns names for the most recent operation that returned tuples. Each name is separated from the next by a character ($char(9)).
$zsqlOpen Returns true if a connection to the SQL server is open, false otherwise.

The following may work if you are on the UNI campus:

#!/usr/bin/mumps sql/d host=tuatha.cs.uni.edu dbname=medical write "msg=",$zsql,! // any message from the SQL server? write $zsqlOpen,! // is the connection open? sql/o="www" select * from ptname; write $zsql,! // messages? write $zsqlCols,! // TAB separated list of column names

Examples

Given the following small dummy database definition:

create table demographic (ptid text, DOB text, Gender text, Race text, Marital text, PriPhys text, PriPhysTel text, PriPhysAddr text, EContact text, ERelat text, EAddr text, ETel text, PriPharm text, PriPharmAddr text, PriPharmTel text); create table temperature (ptid text, date text, time text, temperature text); create table bp (ptid text, date text, time text, sys text, dia text, pulse text); create table problems (ptid text, icd text, problem text, onset text, resolved text, dxphys text); create table financial (ptid text, insur text, insnbr text, insname text, emp text,empaddr text, emptel text); create table ptname (ptid text, namefirst text, namelast text, namemiddle text, nameprefix text, namesuffix text); create table address (ptid text, street text, city text, state text, zip text, telephone text, date text); create table labs (ptid text, date text, time text, test text, result text); create table meds (ptid text, date text, time text, med text, init text, finish text, dose text, freq text, phys text);

Accessing the database from Mumps:

#!/usr/bin/mumps # establish connection to server sql/d host=tuatha.cs.uni.edu dbname=medical # check connection if '$test write "Connection error: ",$zsql,! halt # build a command set cmd="select namefirst,namelast,problem from ptname,problems" set cmd=cmd_" where ptname.ptid=problems.ptid;" # send command to server # results will be in xxx.tmp where xxx is the unique process id # of this progrogarm sql/o=$job_".tmp" &~cmd~ # check for error if '$test write "Command error: ",$zsql,! halt # open the results file for input open 1:$job_".tmp,old" if '$test write "file not found",! halt # read input and print. values are separated by TAB's - $char(9) for do . use 1 . read line . if '$test break . set first=$piece(line,$char(9),1) . set last=$piece(line,$char(9),2) . set prob=$piece(line,$char(9),3) . use 5 . write last,", ",first,":",?25,prob,! close 1 use 5 # disconnect from server sql/c halt with the results (all data faked): Jones, John: ACUTE TONSILLITIS Jones, John: CARDIOMYOPATHY Jones, John: INFLAMMATION OF EYELIDS Jones, John: EPILEPSY Jones, John: DEPRESSIVE DISORDER NEC Jones, John: COAGULATION DEFECTS Jones, John: THIAMINE/NIACIN DEFIC Jones, John: ACUTE TONSILLITIS Smith, Charles: ACUTE TONSILLITIS Smith, Charles: CARDIOMYOPATHY Smith, Charles: INFLAMMATION OF EYELIDS Smith, Charles: EPILEPSY Smith, Charles: DEPRESSIVE DISORDER NEC Smith, Charles: COAGULATION DEFECTS Smith, Charles: THIAMINE/NIACIN DEFIC Smith, Charles: ACUTE TONSILLITIS ...

With a web server

Gives the following web browser display:

Example using PostgreSQL to store the global arrays

More examples with three Mumps programs: One that gets and formats the ptname table data, one that gets and formats the labs table data and one that actually does the formating and printing

Note: in this example one Mumps program invokes another. When this happens, the symbol table, all open files, and other environment variables are shared with the program invoked. This includes the database connection.

In these examples, the HTML page, when the SUBMIT button is clicked, invokes the labrpt.mps program. This program, in turn, invokes name.mps which gets data from the ptname table. name.mps invokes rsltprint.mps which formats the results table from the results file xxx.file (where xxx is the unique process id). rsltprint.mps returns to name.mps which returns to labrpt.mps which then queries the labs table then calls the rsltprint.mps program a second time. Note, the connection to the server is established at the beginning and remains open until labrpt.mps finishes.

name.mps

rsltprint.mps

labrpt.mps

labs.html

The initial web browser display:

The results returned from the server: