Info

Mini SQL - A Lightweight Database Engine
mSQL 1.0.16 for OS/2 2.x or Warp with TCP/IP

All programs included in this package are provided "as is", without
any warranty! Try them on your own risk.

This documentation is based on the postscript file mSQL-111.ps describing version 1.0.11 (Feb. 1996) and has been adapted to the OS/2 port.

Copyright (c) 1993 - 1996 David J. Hughes (Hughes Technologies Pty Ltd)

The mSQL language offers a significant subset of the features provided by ANSI SQL. It allows a program or user to store, manipulate and retrieve data in table structures. It does not support relational capabilities such as table joins, views or nested queries. Although it does not support all the relational operations defined in the ANSI specification, it does provide the capability of "joins" between multiple tables.

The create clause as supported by mSQL can onyl be used to create a table. It cannot be used to create other definitions such as views. It should also be noted that there can only be one primary key field defined for a table. Defining a field as a key generates and implicit not null attribute for the field.

  CREATE TABLE table_name (
      co_name      col_type [ not null | primary key ]
      [, col_name  col_type [ not null | primary key ] ]**
  )

  CREATE TABLE emp_details (
      first_name   char(15) not null,
      last_name    char(15) not null,
      dept         char(20),
      emp_id       int primary key,
      salary       int
  )

DROP is used to remove a table definition from the database:

  DROP TABLE table_name

for example:

  DROP TABLE emp_details

The INSERT INTO Clause

Unlike ANSI SQL, you cannot nest a SELECT within an INSERT (i.e. you cannot insert the data returned by a SELECT). If you don not specify the field names they will be used in the same order they were defined - you must specify a value for every field if you do this.

  INSERT INTO table_name [ ( column [ , column ]** ) ]
    VALUES ( value [ , value ]** )

  value can be a either a integer or scientific number, a string
  or 'sysdate' or 'systime'.

Note:
SYSDATE and SYSTIME are only available in the OS/2 version of mSQL. SYSDATE returns the current date in a string of 10 characters 'yyyy/mm/dd', there 'yyyy' is the year, 'mm' is the month (01-12) and 'dd' is the day of month (01-31). SYSTIME returns the current local time in a string of 8 characters 'hH:MM.SS', with 'hH' is the hour (0-23), 'MM' is the minute (00-59) and 'SS' is the second (00-59). On "Feb 14, 1996 08:01.30" SYSDATE returns "1996/02/14" and SYSTIME returns " 8:01.30". The key-words SYSDATE and SYSTIME are not case-sensitive.

for example:

  INSERT INTO emp_details (first_name, last_name, dept, salary)
    VALUES ('David', 'Hughes', 'I.T.S.', 12345)

  INSERT INTO emp_details
    VALUES ('David', 'Hughes', 'I.T.S.', 12345)

  INSERT INTO log_table
    VALUES ('starting time', sysdate, systime)

The syntax for mSQL's DELETE clause is

  DELETE FROM table_name
    WHERE column OPERATOR value
    [ AND | OR column OPERATOR value ]**

  OPERATOR can be <, >, =, <=, >=, <>, or 'like'

for example:

  DELETE FROM emp_details WHERE emp_id = 12345

The SELECT Clause

The SELECT offered by mSQL lacks some of the features provided by the SQL spec:

• No nested selects
• No implicit functions (e.g. count(), avg() )

It does however support:

• Joins - including table aliases
• DISTINCT row selection
• ORDER BY clauses
• Regular expression matching
• Column to column comparisons in WHERE clauses

So, the formal syntax for mSQL's SELECT is:

  SELECT [table.]column [, [table.]column ]**
    FROM table [ = alias][, table [ = alias]]**
    [ WHERE [table.]column OPERATOR VALUE
      [ AND | OR [table.]column OPERATOR VALUE]** ]
    [ ORDER BY [table.]column [DESC][,[table.]column [DESC]]
    [ LIMIT number_of_rows ]

  OPERATOR can be <, >, =, <=, >=, <>, or 'like'
  VALUE can be a literal value or a column name

A simple SELECT may be

  SELECT first_name, last_name FROM exmp_details
    WHERE dept = 'finance'

To sort the returned data in ascending order by last_name and descending order by first_name the query would look like this

  SELECT first_name, last_name FROM emp_details
    WHERE dept = 'finance'
    ORDER BY last_name, first_name DESC

And to remove any duplicate rows, the DESTINCT operator could be used:

  SELECT DISTINCT first_name, last_name FROM emp_details
    WHERE dept = 'finance'
    ORDER BY last_name, first_name DESC

To limit the number of rows returned to a maximum of 3 rows, use the LIMIT operator:

  SELECT DISTINCT first_name, last_name FROM emp_details
    WHERE dept = 'finance'
    ORDER BY last_name, first_name DESC
    LIMIT 3

The regular expression syntax supported by LIKE clauses is that of standard SQL:

• '_' matches any single character
• '%' matches 0 or more characters of any value
• '\' excapes special characters (e.g. '\%' matches '%' and '\\' matches '\')
• all other characters match themselves

So, to search for anyone in finance who's last name consits of a letter followed by 'ughes', such as Hughes, the query could look like this:

  SELECT first_name, last_name FROM emp_details
    WHERE dept = 'finance'
    AND last_name like '_ughes'

The power of a relational query language starts to become apparent when you start joining tables together during a select. Lets say you had two tables defined, one containing staff details and another listing the projects being worked on by each staff member, and each staff member has been assigned an employee number that is unique to that person. You could generate a sorted list of who was working on what project with a query like this:

  SELECT emp_details.first_name, emp_details.last_name,
         project_details.project
    FROM emp_details, project_details
    WHERE emp_details.emp_id = project_details.emp_id
    ORDER BY emp_details.last_name, emp_details.first_name

mSQL places no restriction on the number of tables "joined" during a query so if there were 15 tables all containing information related to an employee ID in some maner, data from each of those tables could be extracted, albeit slowly, by a single query. One key point to note regarding joins is that you must qualify all column names with a table name. mSQL does not support the concept of uniquely named columns spanning multiple tables so you are forced to qualify every column name as soon as you access more than one table in a single select.

mSQL-1.0.6 adds table aliases so that you can perform a join of a table onto itself. With this you could find out from a list of child/parent tuples any grandparents using something like

  SELECT t1.parent, t2.child FROM parent_data=t1, parent_data=t2
    WHERE t1.child = t2.parent

The mSQL UPDATE clause cannot use a column name as a value. Only literal values may be used as an update value

  UPDATE table_name SET column = value [, column = value ]**
    WHERE column OPERATOR value
    [ AND | OR column OPERATOR value ]**

  OPERATOR can be <, >, =, <=, >=, <>, or 'like'
  value can be a either a integer or scientific number, a string
  or 'sysdate' or 'systime'.

Note:
SYSDATE and SYSTIME are only available in the OS/2 version of mSQL. SYSDATE returns the current date in a string of 10 characters 'yyyy/mm/dd', there 'yyyy' is the year, 'mm' is the month (01-12) and 'dd' is the day of month (01-31). SYSTIME returns the current local time in a string of 8 characters 'hH:MM.SS', with 'hH' is the hour (0-23), 'MM' is the minute (00-59) and 'SS' is the second (00-59). On "Feb 14, 1996 08:01.30" SYSDATE returns "1996/02/14" and SYSTIME returns " 8:01.30". The key-words SYSDATE and SYSTIME are not case-sensitive.

for example:

  UPDATE emp_details SET salary=30000 WHERE emp_id=1234

  UPDATE log_table SET date=sysdate WHERE date like '1996/%'

The Database Engine (Server)

The mSQL daemon, msqld.exe, is a standalone application that listens for connections on a well known TCP socket. It is a single process engine that will accept multiple connections and serialise the queries received. It utilises memory mapped I/O and cache techniques to offer rapid access to the data stored in a database. It also utilises a stack based machanism that ensures that INSERT operations are performed at the same speed regardless of the size of the table being accessed. Preliminary testing performed by a regular user of mSQL has shown that for simple queries, the performance of mSQL is comparable to or better than other freely available database packages. For example, on a set of sample queries including simple INSERTs, UPDATEs and SELECTs, mSQL performs roughly 4 times faster than University Ingres and over 20 times faster than Postgres on an Intel 486 class machine running Linux.

By default, the software is installed into \public\mSQL\ and the server will use space within that directory for the storage of the databases and also temporary result tables during operations such as joins and ordering. The following list shows the assignment of directories below there:

\BIN\	contains all executables, such as server, monitor, utilities, etc.
\DOC\	contains all printable and online documentation
\INCLUDE\	contains header files for the C programming API
\LIB\	contains the programming and runtime libraries
\MSQLDB\	contains the sub-directories with the database tables
\TMP\	temporary stuff
\W3-mSQL\	W3-mSQL gateway programming samples

Note:
The access to directories has been changed. Up to 1.0.13c all paths were set absolutely. Since 1.0.14a the server msqld.exe the path detection goes like this:

• first the pre-defined path set in site.mm (INSTPATH) will taken
• if a environment variable MSQL_HOME does exist its values is taken instead of the pre-defined path

• the server gets the path to its binary file (msqld.exe)
• first, at that path is checked whether MSQLDB exists or not
• if not, the same check is done a directory hierarchie higher than the server executable is located (i.e. \aaa\bbb\msqld.exe, first \aaa\bbb\msqldb\, second \aaa\msqldb\)
• if this again fails the server terminates with an error message

Access Control List (ACL)

Access control is managed by the msql.acl file in the installation directory (default: \public\mSQL\). This file is split into entries for each database to be controlled. If the file doesn't exist or details for a particular database aren't configured, access reverts to global read/write. An example ACL entry is included below:

  # Sample access control for mSQL
  database=test
  read=bambi,paulp
  write=root
  host=*.Bond.edu.au,-student.it.Bond.edu.au
  access=local,remote

Using this definition, database test can be accessed by both local and remote connections from any host in the Bond.edu.au domain except for the machine student.it.Bond.edu.au. Read access is only granted to bambi and paulp. Nobody else is allowed to perform SELECTs on the database. Write access is only available to root.

Control is matched on the first match found for a given item. So, a line such as "read=-*,bambi" would not do the desired thing (i.e. deny access to everyone other than bambi) because -* will also match bambi. In this case the line would have to be "read=bambi,-*" although the -* is superfluous as that is the default action.

Note that if an entry isn't found for a particular configuration line (such as read) it defaults to a global denial. For example, if there is no "read" line (i.e. there are no read tokens after the data is loaded) nobody will be granted read access. This is in contrast to the action taken if the entire database definition is missing in which case access to everything is granted.

Another thing to note is that a database's entry must be followed by a blank line to signify the end of the entry. There may also be multiple config lines in the one entry (such as "read=bambi,paulp" "read=root"). The data will be loaded as though it was concatenated onto the same "read" line (i.e. "read=bambi,paulp,root").

Wildcards can be used in any configuration entry. A wildcard by itself will match anything whereas a wildcard followed by some text will cause only a partial wildcard (e.g. *.Bond.edu.au matches anything that ends in .Bond.edu.au). A wildcard can also be set for the database name.

Note:
A good practice is to install an entry with database=* as the last entry in the ACL file so that if the database being accessed wasn't covered by any of the other rules a default site policy can be enforced.

By default, mSQL uses a pre-defined TCP/IP port (OS/2: 4333) for network communications. You can reconfigure mSQL to use another TCP port in 2 ways, either with the MSQL_TCP_PORT variable or by editing \MPTN\ETC\services (on OS/2 Warp Connect).

Included in the distribution is the mSQL API library, mSQL.lib for static and mSQL_dll.lib for dynamic linking against mSQL.DLL. The API allows any C program to communicate with the database engine. The API functions are accessed by including the msql.h header file into your program and by linking against the mSQL library (by adding mSQL.lib or mSQL_dll to the list of modules to be linked by the linker). The library and header files will be installed by default into \lib\ and \include\ respictively.

The following function and procedure prototypes are defined in msql.h:

API Debugging

  char *msqlGetErrMsg( char *pszErrMsg );

This function grants access to an internal variable in the database library, containing a text string with a detailed message after errors. The parameter pszErrMsg points to a buffer long enough to store this message. In this case the returned pointer is equal to pszErrMsg. If the parameter is set NULL the returned pointer can be used to address the internal library variable (read-only).

  int msqlConnect( char *host );

only available within the OS/2 port

  int msqlUserConnect( char *host, char *user );

undocumented within original documentation

  int msqlCreateDB( int sock, char *dbName );

This function ables one to create of a new database. Its parameters are sock, the communication socket, and dbName, the name of the database to be created. The function returns a value of 0 on success, any other value represents an error.

undocumented within original documentation

  int msqlDropDB( int sock, char *dbName );

  int msqlSelectDB( int   sock,
                    char *dbName
                  );

  int msqlQuery( int   sock,
                 char *query
               );

  m_result *msqlStoreResult();

  void msqlFreeResult( m_result *result );

  m_row msqlFetchRow( m_result *result );

  void msqlDataSeek( m_result *result,
                     int       pos
                   );

  int msqlNumRows( m_result *result );

  m_field *msqlFetchField( m_result *result );

msqlFieldSeek

  void msqlFieldSeek( m_result *result,
                      int       pos
                    );

  int msqlNumFields( m_result *result );

  m_result *msqlListDBs( int sock );

  m_result *msqlListTables( int sock );

  m_result *msqlListFields( int   sock,
                            char *tableName
                          );

  int msqlClose( int sock );

undocumented within original documentation

  char *msqlGetHostInfo();

msqlGetProtoInfo

undocumented within original documentation

  int msqlGetProtoInfo();

undocumented within original documentation

  char *msqlGetServerInfo();

  extern char msqlErrMsg[];

contains a zero-terminated error message, if an error occurs.

The OS/2 version of mSQL comes with the same client programs as the original distribution for Unix does. In fact, in order to be able to run mSQL an FAT based file systems, too, the names of the programs has been adapted to 8.3 notation of FAT.

  msql[.exe] [ -q ] [ -h host ] database

Like all database applications, mSQL provides a program that allows a user to interactively submit queries to the database engine. In the case of mSQL, it is a program simply called 'msql.exe'. If requires one command line argument, being the name of the database to access. Once started, there is no way to swap databases without restarting the program.

The monitor also accepts two command line flags as outlined below:

• '-h host' connect to the mSQL server on the given host
• '-q' process one query and quit returning an exit code

The monitor has been modelled after the original Ingres (and the subsequent Postgres) monitor program. Commands are distinguished from queries due to their being prefixed with a backslash. To obtain help from the monitor prompt, the \h command is used. To exit from the program, the \q command must be entered.

To send a query to the engine, the query is entered followed by the \g command. \g tells the monitor to "Go" and send the query to the engine. If you wish to edit your last query, \e will place you inside an editor so that you can modify your query.

  msqladm[.exe] [ -h host ] [ create database |
                              drop   database |
                              shutdown        |
                              reload          |
                              version ]

mSQL databases are administrated using the msqladm.exe (Unix: msqladmin) tool. Several administrative tasks, such as creating new databases and forcing a server shutdown are performed using msqladm.exe. Like all mSQL programs, msqladm.exe accepts the '-h host' command line flag to specify the desired machine. The commands available via msqladm.exe are:

create database	Create a new database called 'database'
drop database	Delete the entire database called 'database' (with all tables)
shutdown	Tell the server to shut itself down
reload	Tell the server to relaod its access control information (ACL)
version	Display various version information from the server

If should be noted that the server will only accept create, drop, shutdown, and reload commands if they are sent by the root user and are sent from the machine running the server. An attempt to perform any of these commands from a remote client or as a non-root user will result in a "permission denied" error. The only command you can execute over the network or as a non-root user is version.

  relshow[.exe] [ -h host ] [ database ] [ table ]

mSQL provides the relshow command for display the structure of a database. If executed with no arguments, relshow.exe will list the available databases. If it is executed with the name of a database, relshow.exe will list the tables that have been defined for that database. If given both a database and table name, the schema viewer will display the structure of the table including the field names, types, and sizes. Like all mSQL programs, relshow.exe honors the '-h host' command line flag to specify a remote machine as the database server.

  msqldump[.exe] [ -h host ] [ -v ] database [ table ]

Kevin Gill (kgill@kindle.ie) has written an open database connection driver (ODBC level 1) and a Windows mSQL dynamic link library, so one could access mSQL via Visual Basic or other ODBC client software within the Windows environment. For more details mail the author and check ftp://ftp.Bond.edu.au/pub/Minerva/msql/contrib/ODBC/ for the latest archives:

• gorta.faq (28kB) - FAQ for the ODBC driver
• gortarts.zip (17kB) - ODBC driver for use with ODBC manager
• gortasrc.zip (33kB) - ODBC driver source for use with ODBC-SDK
• installd.zip (225kB) - ODBC driver set, if you don't have an ODBC manager
• wmsqlrts.zip (12kB) - mSQL client for MS-Windows
• wmsqlsrc.zip (15kB) - mSQL client for MS-Windows, Source

Rexx (OS/2)

Mark Hessling (M.Hessling@qut.edu.au) is the programmer of REXX/SQL, a REXX interface for SQL databases including mSQL and Oracle. It is distributed under the GNU General Public License with source included. It can be found on ftp://ftp.qut.edu.au/src/REXXSQL/rxsql*.zip (ZIPped version) or ftp://ftp.qut.edu.au/src/REXXSQL/rxsql*.tar.Z (TARed and compressed version). The current version is 1.3 for the mSQL and 1.2 for the Oracle implementation (V 1.3 dated March 1996). The mSQL version can be found on ftp://ftp.Bond.edu.au/pub/Minerva/msql/Contrib/, too.

The following sections contain questions and answers to various problems with bot Unix and OS/2 version of mSQL and related programs. There are the following sections:

common FAQ

Common problems and their solution, that is the intention of this section.

Q: When I try to store a large number into a 'real' field, then the number is stored wrong.

This section deals with special problems of the OS/2 version of mSQL.

Q: On starting relshow, msqldump or w3-mSQL I get an error 'SYS1804: Can't find file MSQL'

A: OS/2 can't find the dynamic link library mSQL.DLL. Check, if there is mSQL.DLL in a path accessable via the LIB_PATH entry in your CONFIG.SYS. You may include the directory \lib\ or \bin\ in the LIB_PATH line.

Q: I can't create databases, msqladm returns 'access denied'

Mini SQL was written by:

  David J. Hughes
  Managing Director
  Hughes Technologies Pty Ltd.
  Australia

  E-mail: bambi@Hughes.edu.au
          http://Hughes.com.au/
  Fax:    +61 7 3302 2199

Mini SQL has been ported to OS/2 by:

  Dirk Ohme
  Programmer at transtec AG
  Tübingen, Germany

  E-mail:  Dirk.Ohme@transtec.de
  Fidonet: 2:246/2001.9@fidonet

Archive Location

The primary source of information relating to Mini SQL is Hughes Technologies Web Site. It contains all current information and pointers to the software distribution, mailing list archives, and other important information. The Hughes Technologies Web Site is located at:

  http://Hughes.com.au/

The latest version of the OS/2 port can be found at :

  Host:   www.fh-albsig.de (141.87.110.2)
  URL:    http://www.fh-albsig.de/~ohme
  Files:  FILES/msql*.lsm (description)
          FILES/msql*.zip (Zip 2.0.1 archive, Source & Binaries)
  Note:   OS/2 port

Mailing List

A mailing list is operated to provide a place for common users of mSQL to discuss the product. It is currently operated at Bunyip Information Systems in Canada (a long-time user of Mini SQL) and we thank them for their help and support. To subscribe to the mailing list, send an e-mail message containing the word "subscribe" to msql-list-request@Bunyip.com.

Once you are subscribed you can send a message to the entire list by addressing it to msql-list@Bunyip.com. Please note that there are usually between 600 and 1000 mSQL users subscribed to the mailing list at the time of writing so it is an excellent forum for asking general mSQL user questions.

If you have special questions, suggestions, etc. for the OS/2 port, please send your mail directly to Dirk.Ohme@transtec.de - Thank you!