Mumps with PostgreSQL

offbeatlossΔιαχείριση Δεδομένων

22 Νοε 2012 (πριν από 4 χρόνια και 4 μήνες)

289 εμφανίσεις

Mumps
with
PostgreSQL
by
Kevin C.
Ó
Kane

1
Copyright © 2009 by Kevin C.
Ó
Kane
. All rights reserved.
The author may be contacted at:
kc.okane@gmail.com
okane@cs.uni.edu
Department of Computer Science
The University of Northern Iowa
Cedar Falls, Iowa 50614-507
http://www.omahadave.com
Revision: 2.01
November 29, 2009
2
Table of Contents
1 PostgreSQL Access
.............................................
4
1.1 Configuring Mumps to work with PostgreSQL
........................
4
1.2 Basic Mumps SQL commands:
..............................................
9
1.2.1 Connect to server
.......................................................................
9
1.2.2 Disconnect from server
..............................................................
9
1.2.3 Format/prepare global array table
............................................
10
1.2.4 Execute SQL command
.............................................................
10
1.2.5 Execute SQL command with file results
....................................
10
1.2.6 Re-direct Mumps global array table
..........................................
11
1.2.7 SQL related builtin functions
....................................................
11
1.2.8 Examples
.................................................................................
12
1.2.8.1 Database definition
............................................................
12
1.2.8.2 Accessing a relational database from Mumps:
...................
12
1.2.8.3 With a web server
.............................................................
13
1.2.8.4 Using PostgreSQL to store the global arrays
......................
15
1.2.8.5 More examples
..................................................................
16
1.2.8.6 Timing comparisons.
.........................................................
19
1.2.8.7 SQL Commands generated by Mumps statements
............
20
Index of Figures
Figure 1 PostgreSQL configuration
.........
5
Figure 2 PostgreSQL connections
...........
9
Figure 3 - Accessing the RDBMS
...........
11
Figure 4 - Data base definition
.............
12
Figure 5 - SQL from Mumps
..................
13
Figure 6 - Web server SQL access
........
15
Figure 7 - Browser display
....................
15
Figure 8 - Accessing RDBMS
.................
16
Figure 9 - Browser display
....................
16
Figure 10 - name.mps
...........................
17
Figure 11 - rsltprint.mps
.......................
18
Figure 12 - labrpt.mps
..........................
19
Figure 13 - Initial screen HTML
............
19
Figure 14 - Native vs. PostgreSQL
........
20
Figure 15 - Mumps to SQL
....................
23

3
1
PostgreSQL Access
In order for PostgreSQL access to be enabled several things must

be done on both you host machine (the one running PostgreSQL)

and your client machine (the one running your Mumps program).

These include establishing user accounts on PostgreSQL and

configuring it to accept queries from the user id of the person

running the Mumps program.
You may also need to set encryption, access rights and establish

the IP number of the client as an authorized site. These are

detailed in the PostgreSQL manuals and only touched upon here.

The discussion here is sufficient to create a demonstration.

Refinements are left to the user. The PostgreSQL web site is:
http://www.postgresql.org/
Additionally, you must install the PostgreSQL server

development libraries on the machine which will run the client

Mumps programs even if this is not the machine running the

PostgreSQL server. Also, when you configure Mumps, you must

specify that PostgreSQL will be used for database activities.
1.1
Configuring Mumps to work with PostgreSQL

Normally, Mumps runs in standalone mode with its own, native,

B-tree
based global array file system. However, the compiler and

interpreter may be configured to enable the global arrays to be

stored on a PostgreSQL server.
Although other relational database servers (such as MySQL) may

also be used, subject to a re-coding the internal interface modules,

this version of Mumps supports PostgreSQL because PostgreSQL is

the main free, non-proprietary, high-performance, open source

system in the database area.
Once PostgreSQL access has been enabled, you may store your

global arrays on the PostgreSQL server as well as access other

relational tables on the server directly from your Mumps program.

While the native B-tree based globals are faster, using PostgreSQL

has the advantage of being multi-user and far more fault-tolerant.
PostgreSQL is organized into databases. When you connect to a

PostgreSQL server, you specify the database to which you are

connecting. Each database consists of some number of tables.

When you store global arrays on a PostgreSQL server, they will, by

default, be stored in the database you specify in a relational table

known as
mumps
. This table may also be accessed by SELECT

statements from non-Mumps environments (see the structural

definition of this table given below). This table will contain all the

Mumps global array data for this database. Other databases on the

4
server may contain other Mumps global arrays. These are

completely separate from one another.
When using a PostgreSQL server, it is possible to construct views

of non-Mumps RDBMS tables which can be directly accessed by

Mumps as though they were global arrays. An example of this is

given below.
When accessing data in global arrays on a PostgreSQL server,

Mumps does so by generating SQL commands that are sent to and

processed by the PostgreSQL server. Normally, PostgreSQL will

checkpoint each of transaction which modifies the database unless

you specify otherwise.
However, to improve performance, it may be desirable to initiate

this set of transactions with the
SQL BEGIN
command before

global array assignments ultimately followed by a
SQL COMMIT

command when the assignments are done. This will permit the

Mumps global array inserts/updates to run faster. However, should

there be a server or client failure before the COMMIT, the data will

be lost and need to be re-assigned. An example is given below.

The BEGIN/COMMIT mechanism can also be used to rollback failed

transactions.
In order to use PostgreSQL for the global array backend server,

use the following configuration and make commands for
Ubuntu

(you must be
root
when doing these)
:
configure prefix=/usr --with-pgdb=/usr/include/postgresql
make
make install
Figure
1
PostgreSQL configuration
If you are using 64 bit
Ubuntu
, add the
--with-
cpu64

option to the
configure
command line. If you are using a system

different than Ubuntu, the PostgreSQL libraries may be located in a

different place (/usr/include/postrgresql in Ubuntu). You will need

to locate the PostgreSQL include libraries and add the option:
--with-pgdb=/usr/include/postgresql
to reflect the correct location (insert the correct location for your

system in the above). For example, in Cygwin this would be:
--with-pgdb=/usr/include
If you want to run Mumps scripts on a different machine than the

one running PostgreSQL, you need to enable
TCP/IP
connections to

the PostgreSQL on the machine running the server. You can do

this by modifying the
listen_addresses
option in the file

/etc/postgresql/8.4/main/postgresql.conf.
Note:
8.4
is the current


5
POstgreSQL version number as of this writing. The number may be

defferent on your system. Modify
listen_addresses
to include a

quoted comma separated list of authorized IP number. An '*' will

enable all (probably not a good idea). Example:
listen_addresses = '127.0.0.1,localhost' # what IP address(es) to 
      # listen on;
The above enables the local machine only.
Note: the default install of PostgreSQL was configured to listen

on port 5433 whereas the client software was configured to

operate on port 5432. The port is set in this file. You may need to

change the port setting to 5432 on your system (re-start required).

You can discover which port PostgreSQL is listening on with the

command:
ls /var/run/postgresql/ ­a 
If you see files named:
.s.PGSQL.5433
and
.s.PGSQL.5433.lock
,

your PostgreSQL is listening on port 5433 and you should change

the configuration port and re-start.
T
he connecting clients IP numbers should be in the file

pg_hba.conf

found in the same directory. This file is used in client

authentication. For
localhost,
the following should be adequate:
# IPv4 local connections: 
   host    all  all  127.0.0.1/32    trust 
# IPv6 local connections: 
   host    all  all  ::1/128         trust 
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 for further details.
After you have modified the configuration settings you need to

restart the server. As root, execute the following:
/etc/init.d/postgresql­8.4 stop
/etc/init.d/postgresql­8.4 start
Note: systems other than Ubuntu may place the commands in a

different location. Also not the version number which may be

different on your machine.
Next, you need to create a user or users. By default, the Ubuntu

PostgreSQL install will add your primary login account as a user. It

will also create a user named
postgres.
You can add additional

users by adding their Ubuntu login id. To do this, become

6
superuser then become user
postgres
and then create the user.

You will be prompted for details:
sudo su postgres (system password will be requested)
createuser demo 
Shall the new role be a superuser? (y/n) n 
Shall the new role be allowed to create databases? (y/n) n 
Shall the new role be allowed to create more new roles? (y/n) n
 
Answer the questions as appropriate for you case. You can

delete a user with the
dropuser
command. Note: there are other

ways to add users and configure their privileges. The user id

should be the same as the user's Linux login user id.
To continue you need to create a database. As mentioned above,

PostgreSQL consists of a collection of one or more databases. In

each, are the relational tables, etc. One of them will be named

mumps
and will contain the Mumps global arrays. The actual name

of you database(s) will be determined by their use but for purposes

of example, we create a database named
medical.
One way to

create a database is:
createdb medical
To test a connection, try something like:
psql
 ­h host1.abc.com ­d medical
where the
-h
option specifies the remote host to connect to and

the
-d medical
specifies the name of the database to which you are

connecting. Omit the
-h host1.abc.com
if the server is running on

your local machine. If all works well, you should get a prompt from

the
psql
command line interface. You may exit it by typing \q.
Some other handy command for
psql
are:
1.
\h – a complete list of commands to the server for which

help is available (type q to end the listing);
2.
\? - a complete list of commands to psql ;
3.
\dt – to show the tables in this data base.
Finally, you need to start Mumps and initialize the global array

table. As noted above, when you are connected to PostgreSQL, you

are connected to a database. Each database has, among other

things, tables. The Mumps global arrays are stored in a table

named
mumps
. For each database in which you store global array

variables, you must have a
mumps
table. Each
mumps
table must

be initialized before it is used.
By default, Mumps will attempt to connect to the database

named
mumps
and access the table named
mumps
unless you

specify a different database in the connection command (
sql/d
).


7
Thus, you should to create the default database and initialize it at

this point. To do so, enter the following commands:
createdb mumps
  mumps
  sql/f
  sql/c
  halt
The first creates the default
mumps
database. The second

starts the Mumps interpreter. The third, processed by the Mumps

interpreter, initializes the
mumps
table in the
mumps
database.

You may receive a
Notice
message from PostgreSQL when you

type this. You may ignore the message. It is merely advising that

no previous instances of the
mumps
table exist. The
sql/c

command disconnects from the database.
Now you can run mumps programs and all global array activity

will default to the database named
mumps.
When you run the

Mumps interpreter, you will not need to connect to the server and

specify a particular database. The interpreter will automatically

connect to the
mumps
database.
However, you may want your mumps global arrays

to reside in

another database
.
In this case, you need to create the database

and initialize a
mumps
table

in it. Subsequently, you will need to

connect to the database each time you want to use it (
sql/
d).
First, you need to create the database:
createdb medical;
Then you initialize a
mumps
table in it. To do this, you need to

connect to the database and then run the initialization command

sql/f.
The initialization command will create the
mumps
table (and

delete any pre-existing table and all its contents). The commands

to do this are:
mumps
sql/d dbname=medical
sql/f
sql/c
The first command establishes the connection with the database,

in this case
medical
running on the local host (see below for

additional connection parameters). The second command formats

the
mumps
table. The third command disconnects from the

database. Now the database is ready for use by Mumps. Note: you

must re-connect to the database each time you want to store

global arrays in it and you should always disconnect when the

connection is no longer needed.
8
1.2
Basic Mumps SQL commands:

1.2.1
Connect to server
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 to which you are connecting:
dbname=medical

Other options include the host, host address, user, password,

etc. See

http://www.postgresql.org/docs/8.4/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:
1
sql/d dbname=medical
2
sql/d host=abc.def.xyz.edu dbname=medical
3
sql/d hostaddr=123.321.432.321 dbname=medical
4
sql/d user=joe password=abc123 dbname=medical host=abc.def.com
5
set x="dbname=medical"
6
sql/d &~x~
Figure 
2
 PostgreSQL connections
Line 1 shows a connection being made to a server on the local

machine by a Mumps program being run by a user who is an

authorized user of the server. Line 2 shows access to a remote

server by a user on a machine authorized to access the server.

Line 3 uses the IP number style rather than a URL. Line 4 shows a

login to a remote server with user name and password. Lines 5

and 6 show command line substitution where the value of
x
is

inserted onto the connection line.
1.2.2
Disconnect from server
sql/c

9
Disconnect from the database. No parameters, no other

commands on the line. No further commands may be sent to the

database server until a new connection is established.
1.2.3
Format/prepare global array table
sql/f
Clear and prepare a mumps database table in PostgreSQL. This

command deletes and removes any previous
mumps
database

table from the PostgreSQL server and defines a new, empty table

of the same name. You should do this the first time you store

Mumps global arrays in PostgreSQL. Subsequently, do this only if

you want to delete the global array database and start over.
This command (no other line options, no other commands

permitted 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. The mumps database has

eleven columns the first of which is named 'gbl' and the

subsequent ones are named a1, a2, ... a10 and it may be queried

by
SELECT
statements but this is probably not desirable.
Note: this only clears the
mumps
table in the current database

(
dbname=...
). The contents of
mumps
tables in other databases

are unchanged.
1.2.4
Execute SQL command
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.
1.2.5
Execute SQL command with file results
sql/o
=
fileName string
where
fileName
must evaluate to a valid filename in which will

be stored the results of the command which follows.
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.
10
The builtin Mumps variable
$zsql
will contain any messages or

ok
if there were none.
For example:
1
#!/usr/bin/mumps
2
sql/d host=192.168.2.4 dbname=medical
3
write "msg=",$zsql,! // any message from the SQL server?
4
write $zsqlOpen,! // is the connection open?
5
sql/o="www" select * from ptname;
6
write $zsql,! // messages?
7
write $zsqlCols,! // TAB separated list of column names
Figure
3
- Accessing the RDBMS
The output of the SQL command on line 5 will be written to the

file named
www
.
1.2.6
Re-direct Mumps global array table
sql/t
=
table,size
The
sql/t=table,size
command is used to enable a different

PostgreSQL table to become accessible as a Mumps global array.

Both
table
and
size
must be valid Mumps expressions.
Normally, Mumps will store global array references into a table in

the current database named
mumps
. This table consists of twelve

columns. The first column (
gbl
) contains the name of the global

array, columns
a1 … a10
contain index values or
null
and column

a11
contains the value stured at the global array reference or
null.
You may cause global array references to take place in a

different table by use of this command. In the command, you

specify the name of the table and its size.
table
must be the name of an existing, valid table or view in

the relational database.
size
gives the number of columns,

exclusive of the first column. The first column must contain the

name to be used for global array references. After this command

is executed, the table named may be accessed as a global array.

The new table must have column names
gbl, a1, a2, …
1.2.7
SQL related builtin functions
Added builtin SQL functions in latest version of Mumps:
i.
$zsql

Returns the SQL server error message for

the most recent command or 'ok.'
ii.
$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
TAB
character ($char(9)).

11
iii.
$zsqlOpen

Returns true if a connection to the SQL

server is open, false otherwise.
1.2.8
Examples

1.2.8.1
Database definition
The following examples use a simplistic relational database

containing tables related to medical data. The following gives the

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);
Figure
4
- Data base definition
1.2.8.2
Accessing a relational database from Mumps:
1
#!/usr/bin/mumps
2
3
# establish connection to server
4
5
sql/d dbname=medical
6
7
# check connection
8
9
if '$test write "Connection error: ",$zsql,! halt
10
11
# build a command
12
13
set cmd="select namefirst,namelast,problem from ptname,problems"
14
set cmd=cmd_" where ptname.ptid=problems.ptid;"
15
12
16
# send command to server
17
# results will be in xxx.tmp where xxx is the unique process id
18
# of this progrogarm
19
20
sql/o=$job_".tmp" &~cmd~
21
22
# check for error
23
24
if '$test write "Command error: ",$zsql,! halt
25
26
# open the results file for input
27
28
open 1:$job_".tmp,old"
29
30
if '$test write "file not found",! halt
31
32
# read input & print. values separated by TAB's - $char(9)
33
34
for do
35
. use 1
36
. read line
37
. if '$test break
38
. set first=$piece(line,$char(9),1)
39
. set last=$piece(line,$char(9),2)
40
. set prob=$piece(line,$char(9),3)
41
. use 5
42
. write last,", ",first,":",?25,prob,!
43
44
close 1
45
use 5
46
47
# disconnect from server
48
49
sql/c
50

halt
with the results (all data fictitious):
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
Figure
5
- SQL from Mumps
1.2.8.3
With a web server
1
#!/usr/bin/mumps
2
3
html Content-type: text/html &!&!
4
html <html><body bgcolor=silver><font size=+1>
5

13
6
set x="dbname=medical"
7
8
# Open the connection.
9
# The &~exp~ causes 'exp' to be inserted into the line
10
11
sql/d &~x~
12
13
# $test will be 1 and $zsql will be 'ok' if it worked
14
15
if $test html Connection to database open <br>
16
else do
17
. html Connection to database failed </body></html>
18
. halt
19
20
# Flush/delete/create a mumps table in the database.
21
22
sql/f
23
24
if $test html Mumps tables initialized <br>
25
else html Mumps table initialization failed <br>
26
27
# prepare a query and run it. Output will go to xxx.file
28
# where 'xxx' is the process id of this program
29
30
set x="select * from ptname;"
31
32
html Sending query: &~x~ <br>
33
34
sql/o="/tmp/"_$job_".file" &~x~
35
36
set ptname=$zsqlCols // gets column names TAB separated
37
38
if $test html Query successfully processed <p>
39
else do
40
. html Query failed. Message=&~$zsql~ <br></body></html>
41
. halt
42
43
# Open the file or results and process same.
44
45
open 1:"/tmp/"_$job_".file,old"
46
47
if '$test do
48
. html Results file error<br></body></html>
49
. halt
50
51
# Each line consists of columns separated by TAB characters
52
# $char(9) is a TAB. sepearte the lines. ptname is:
53
54
html <table border><tr>
55
56
for i=1:1:6 do
57
. html <td> &~$piece(ptname,$char(9),i)~ </td>
58
59
html </tr>
60
61
for do
62
. use 1 // unit 1 to be used for I/O
63
. read line
64
. if '$test break
65
. use 5 // unit 5 now used for I/O
66
. html <tr>
67
. for i=1:1 do
68
.. set col=$piece(line,$char(9),i)
69
.. if col="" break
14
70
.. html <td> &~col~ </td>
71
. html </tr>
72
73
use 5
74
html </table>
75
76
html </body>
77
html </html>
78
79
shell/g rm &~"/tmp/"_$job_".file"~
80






halt
Figure
6
- Web server SQL access
Yields the following web browser display:
1.2.8.4
Using PostgreSQL to store the global arrays
1
# name.mps
2
3
#!/usr/bin/mumps
4
5
html Content-type: text/html &!&!
6
html <html><body bgcolor=silver><font size=+1>
7
8
set x="dbname=medical"
9
10
# Open the connection.
11
# &~exp~ causes result of 'exp' to be inserted in line
12
13
sql/d &~x~
14
15
# $test will be 1 and $zsql will be 'ok' if it worked
16
17
if $test html Connection to database open <br>

15
Figure
7
- Browser display
18
else do
19
. html Connection to database failed </body></html>
20
. halt
21
22
# Flush/delete/create a mumps table in the database.
23
24
sql/f
25
26
if $test html Mumps tables initialized <br>
27
else html Mumps table initialization failed <br>
28
29
set cmd="create temp view names (gbl,a1,a2,a3) as select"
30
set cmd=cmd_"text 'names', ptid, namefirst, namelast from
ptname;"
31
32
sql &~cmd~
33
34
sql/t="names",3
35
36
html <hr>
37
for ptid="":$order(^names(ptid)):"" do
38
. for first="":$order(^names(ptid,first)):"" do
39
.. for last="":$order(^names(ptid,first,last)):"" do
40
... write ptid," ",first," ",last,"<br>"
41
42
html <hr></body></html>
43
halt
Figure
8
- Accessing RDBMS
1.2.8.5
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.
16
Figure
9
- Browser display
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.
1
# name.mps
2
3
#!/usr/bin/mumps
4
5
# this module assumes that the connection is open
6
7
# prepare a query and run it. Output will go to xxx.file
8
# where 'xxx' is the process id of this program
9
10
set x="select * from ptname where ptid='"_ptid_"';"
11
12
sql/o="/tmp/"_$job_".file" &~x~
13
14
set cols=$zsqlCols
15
16
if '$test do
17
. html Query failed. <br></body></html>
18
. halt
19
20
# Open the file or results and process same.
21
22
do ^rsltprint.mps
Figure
10
- name.mps
1
#!/usr/bin/mumps
2
3
# rsltprint.mps
4
5
# this module assumes that the results file exists
6
7
open 1:"/tmp/"_$job_".file,old"
8
9
if '$test do
10
. html Results file error<br></body></html>
11
. halt
12
13
# Each line consists of columns separated by TAB characters
14
# $char(9) is a TAB. separate the lines.
15
16
html <table border=1><tr>
17
18
for i=1:1 do
19
. set x=$piece(cols,$char(9),i)
20
. if x="" break
21
. html <td> &~x~ </td>
22
html </tr><hr>
23
24
for do
25
. use 1 // unit 1 to be used for I/O

17
26
. read line
27
. if '$test break
28
. use 5 // unit 5 now used for I/O
29
. html <tr>
30
. for i=1:1 do
31
.. set col=$piece(line,$char(9),i)
32
.. if col="" break
33
.. html <td> &~col~ </td>
34
. html </tr>
35
36
use 5
37
html </table>
38
39
html </body>
40
html </html>
41
42
close 1
43
44
# delete the file
45
46






shell/g rm &~"/tmp/"_$job_".file"~
47






halt
Figure
11
- rsltprint.mps
1
# labrpt.mps
2
3
#!/usr/bin/mumps
4
5
html Content-type: text/html &!&!
6
html <html><body bgcolor=silver><font size=+1><center>
7
8
set x="user=okane host=192.168.2.4 dbname=medical"
9
10
# Open the connection.
11
# The &~exp~ causes 'exp' result inserted into the line
12
13
sql/d &~x~
14
15
# $test will be 1 and $zsql will be 'ok' if it worked
16
17
if '$test do
18
. html Connection to database failed </body></html>
19
. halt
20
21
if '$data(ptid) do
22
. html Error: no value specified for Patient ID (ptid).
23
. html </body></html>
24
25
# call a mumps routine to print the name of person ptid
26
27
do ^name.mps
28
29
# prepare a query and run it. Output will go to xxx.file
30
# where 'xxx' is the process id of this program
31
32
set x="select * from labs where ptid='"_ptid_"';"
33
34
sql/o="/tmp/"_$job_".file" &~x~
35
36
set cols=$zsqlCols
37
38
if '$test do
18
39
. html Query failed. Message=&~$zsql~ <br></body></html>
40
. halt
41
42
# print results
43
44
do ^rsltprint.mps
45
46
html </center></body>
47
html </html>
48
halt
Figure
12
- labrpt.mps
1
<html>
2
<body>
3
<font size=+2>
4
Worst General Hospital Lab Reports
5
<p>
6
<hr>
7
<form method=get action="cgi-bin/labrpt.mps">
8
Enter Patient Id:
9
<input type=text name=ptid value="" size=10>
10
&nbsp; &nbsp; &nbsp;
11
<input type=submit value="Display Labs">
12
</form>
13
</body>
14
</html>
Figure
13
- Initial screen HTML
1.2.8.6
Timing comparisons.
Using Postgresql versus the native B-tree results in

approximately a 12 to 1 degradation. The following test program

was run several times. It randomly inserts 100,000 items into the

database.
1
#!/usr/bin/mumps
2
3
sql/d dbname=medical
4
sql/f
5
6
kill ^a
7
8
do $zsrand(999) // see random number generator
9
10
sql begin;
11
set t1=$zd1 // start time
12
for i=1:1:100000 set ^a($r(1000000)+1,i)=i
13
sql commit;
14
15
set i=""
16
set k=0
17
for do
18
. set i=$o(^a(i))
19
. if i="" break
20
. set j=""
21
. for do
22
.. set j=$o(^a(i,j))
23
.. if j="" break
24
.. set k=k+1
25
26
write $zd1-t1," ",k,!

19
Figure
14
- Native vs. PostgreSQL
The average time of execution using the native B-tree was

approximately 4 seconds. For the same program with the
--with-
pgdb
option enabled was 48 seconds. Note: the SQL commands

are ignored if the
--with-pgdb
option was not specified when the

Mumps system was configured.
1.2.8.7
SQL Commands generated by Mumps statements
The following are examples of the SQL commands generated by

common Mumps global array manipulations. The Mumps

interpreter has an option which displays to
stdout
the SQL

commands being executed. These appear in the output that

follows the example program:
1
#!/usr/bin/mumps
2
3
write "*** establish database connection",!!
4
5
sql/d dbname=medical
6
7
write !,"*** flush/initialize the mumps table",!!
8
9
sql/f
10
11
write !,"*** kill any values in the table",!!
12
13
kill ^a
14
15
write !,"*** execute an SQL command",!!
16
17
sql begin;
18
19
write !,"*** insert values into the database",!!
20
21
set ^a(1)=1
22
set ^a(2)=12
23
set ^a(1,2)=2
24
set ^a(1,3)=23
25
set ^a(1,2,3)=3
26
set ^a(1,2,3,4)=4
27
set ^a(1,2,3,4,5)=5
28
set ^a(1,2,3,4,5,6)=6
29
set ^a(1,2,3,4,5,6,7)=7
30
set ^a(1,2,3,4,5,6,7,8)=8
31
set ^a(1,2,3,4,5,6,7,8,9)=9
32
set ^a(1,2,3,4,5,6,7,8,9,10)=10
33
34
write !,"*** extracting from the data base",!!
35
36
set i=^a(1)
37
write "*** expect 1 ",i,!
38
set i=^a(1,2)
39
write "*** expect 2 ",i,!
40
set i=^a(1,2,3)
41
write "*** expect 3 ",i,!
42
set i=^a(1,2,3,4)
43
write "*** expect 4 ",i,!
44
set i=^a(1,2,3,4,5)
20
45
write "*** expect 5 ",i,!
46
set i=^a(1,2,3,4,5,6)
47
write "*** expect 6 ",i,!
48
set i=^a(1,2,3,4,5,6,7)
49
write "*** expect 7 ",i,!
50
set i=^a(1,2,3,4,5,6,7,8)
51
write "*** expect 8 ",i,!
52
set i=^a(1,2,3,4,5,6,7,8,9)
53
write "*** expect 9 ",i,!
54
set i=^a(1,2,3,4,5,6,7,8,9,10)
55
write "*** expect 10 ",i,!
56
57
write !,"*** execute an SQL command",!!
58
59
sql commit;
60
61
write !,"*** $order() examples",!!
62
63
set i=$order(^a(1))
64
write "*** $o(^a(1)) expect 2 ",i,!
65
set i=$order(^a(1,2))
66
write "*** $o(^a(1,2)) expect 3 ",i,!
67
68
write !,"*** $data() examples",!!
69
70
set i=$data(^a)
71
write "*** $d(^a) expect 10 ",i,!!
72
73
set i=$data(^a(1))
74
write "*** $d(^a(1)) expect 11 ",i,!!
75
76
set i=$data(^a(1,2))
77
write "*** $d(^a(1,2)) expect 11 ",i,!!
78
79
set i=$data(^a(1,2,3,4,5,6,7,8,9))
80
write "*** $d(^a(1,2...9)) expect 11 ",i,!!
81
82
set i=$data(^a(1,2,3,4,5,6,7,8,9,10))
83
write "*** $d(^a(1,2,...10)) expect 1 ",i,!!
84
85
write !,"*** $query() example",!!
86
87
set x="^a"
88
for do
89
. set x=$query(x)
90
. if x="" break
91
. write "*** returned: ",x,!
92
93
write !,"*** kill example",!!
94
kill ^a(1,2)
95
96
write !,"*** dump the entire mumps table",!!
97
98
sql/o="tmp" select * from mumps;
output:
*** establish database connection
*** flush/initialize the mumps table
cmd=drop table if exists mumps;

21
cmd=create table mumps (gbl text, a1 text, a2 text, a3 text, a4 text,
a5 text, a6 text, a7 text, a8 text, a9 text, a10 text, a11
text);
cmd=create index m1 on mumps (gbl, a1, a2);
*** kill any values in the table
cmd04=delete from mumps where gbl='a' ;
*** execute an SQL command
*** insert values into the database
cmd=delete from mumps where gbl='a' and a1='1' ;
cmd02=insert into mumps values ('a', '1', '', '', '', '', '', '', '',
'', '', '1' );
cmd=delete from mumps where gbl='a' and a1='2' ;
cmd02=insert into mumps values ('a', '2', '', '', '', '', '', '', '',
'', '', '12' );
cmd=delete from mumps where gbl='a' and a1='1' and a2='2' ;
cmd02=insert into mumps values ('a', '1', '2', '', '', '', '', '', '',

'', '', '2' );
...
cmd=delete from mumps where gbl='a' and a1='1' and a2='2' and a3='3'
and a4='4' and a5='5' and a6='6' and a7='7' and a8='8' and
a9='9' and a10='10' ;
cmd02=insert into mumps values ('a', '1', '2', '3', '4', '5', '6',
'7', '8', '9', '10', '10' );
*** extracting from the data base
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='' and
a3='' and a4='' and a5='' and a6='' and a7='' and a8='' and
a9='' and a10='' limit 1;
*** expect 1 1
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='2' and
a3='' and a4='' and a5='' and a6='' and a7='' and a8='' and
a9='' and a10='' limit 1;
*** expect 2 2
...
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='2' and
a3='3' and a4='4' and a5='5' and a6='6' and a7='7' and a8='8'
and a9='9' and a10='10' limit 1;
*** expect 10 10
*** execute an SQL command
*** $order() examples
cmd05=select a1 from mumps where gbl='a' and a1>'1' order by a1 limit

1;
*** $o(^a(1)) expect 2 2
cmd05=select a2 from mumps where gbl='a' and a1='1' and a2>'2' order
by a2 limit 1;
*** $o(^a(1,2)) expect 3 3
*** $data() examples
cmd01=select a11 from mumps where gbl='a' and a1='' and a2='' and
a3='' and a4='' and a5='' and a6='' and a7='' and a8='' and
a9='' and a10='' limit 1;
cmd05=select a1 from mumps where gbl='a' and a1>'' order by a1 limit
1;
*** $d(^a) expect 10 10
22
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='' and
a3='' and a4='' and a5='' and a6='' and a7='' and a8='' and
a9='' and a10='' limit 1;
cmd05=select a2 from mumps where gbl='a' and a1='1' and a2>'' order
by a2 limit 1;
*** $d(^a(1)) expect 11 11
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='2' and
a3='' and a4='' and a5='' and a6='' and a7='' and a8='' and
a9='' and a10='' limit 1;
cmd05=select a3 from mumps where gbl='a' and a1='1' and a2='2' and
a3>'' order by a3 limit 1;
*** $d(^a(1,2)) expect 11 11
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='2' and
a3='3' and a4='4' and a5='5' and a6='6' and a7='7' and a8='8'
and a9='9' and a10='' limit 1;
cmd05=select a10 from mumps where gbl='a' and a1='1' and a2='2' and
a3='3' and a4='4' and a5='5' and a6='6' and a7='7' and a8='8'
and a9='9' and a10>'' order by a10 limit 1;
*** $d(^a(1,2...9)) expect 11 11
cmd01=select a11 from mumps where gbl='a' and a1='1' and a2='2' and
a3='3' and a4='4' and a5='5' and a6='6' and a7='7' and a8='8'
and a9='9' and a10='10' limit 1;
*** $d(^a(1,2,...10)) expect 1 1
*** $query() example
cmd03=select gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 from mumps where
(gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10) >
('a','','','','','','','','','','') order by
gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 limit 1;
*** returned: ^a("1")
...
cmd03=select gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 from mumps where
(gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10) >
('a','1','2','3','4','5','6','7','8','9','') order by
gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 limit 1;
*** returned: ^a("1","2","3","4","5","6","7","8","9","10")
...
cmd03=select gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 from mumps where
(gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10) >
('a','1','3','','','','','','','','') order by
gbl,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 limit 1;
*** returned: ^a("2")
*** kill example
cmd04=delete from mumps where gbl='a' and a1='1' and a2='2';
*** dump the entire mumps table
Figure
15
- Mumps to SQL

23
Alphabetical Index
B-tree
......................................................
4
configure
.................................................
5
Connect to server
...................................
9
cpu64
......................................................
5
create table
..........................................
12
Disconnect from server
..........................
9
Execute SQL command
.........................
10
Execute SQL command with file results
..............................................................
10
Format/prepare global array table
.......
10
pg_hba.conf
...........................................
6
psql
.........................................................
7
Re-direct Mumps global array table
.....
11
SELECT
..................................................
10
sql
.........................................................
10
SQL BEGIN
..............................................
5
SQL COMMIT
...........................................
5
sql/c
........................................................
9
sql/d
........................................................
9
sql/f
.......................................................
10
sql/o
......................................................
10
sql/t
.......................................................
11
SUBMIT button
......................................
17
TCP/IP
......................................................
5
Ubuntu
....................................................
5
--with-pgdb
...........................................
20
$zsql
..................................................
10p.
$zsqlCols
...............................................
11
$zsqlOpen
.............................................
12
24