o Tim Finin o UMBC Baltimore MD o finin@umbc.edu o http://umbc.edu/~finin/ o http://ebiquity.umbc.edu/


A Brief mSQL Tutorial

Tim Finin, finin@umbc.edu

March 1996

Computer Science and Electrical Engineering
University of Maryland Baltimore County
Baltimore MD 21228

This document gives a very brief tutorial on how to use mSQL, showing, among other things, how to create and query the company database used in "Fundamentals of Database Systems" by Elmasri and Navathe. In the examples of terminal sessions below, text typed in my the user is given in italic font.

Contents

Introduction

mSQL, or mini SQL, is a light weight database engine which supports a subset of ANSI SQL. It provides fast access to stored data with low memory requirements and runs on a wide variety of Unix systems. mSQL was developed by David J. Hughes at Bond University, Australia and can be used without cost by Universities, non-commercial research groups, and not-for-profit organizations.

The mSQL distribution includes the mSQL server, client programs, a C programming interface for client software, and several tools. User contributed software is available including interfaces to mSQL from Perl, Tcl, REXX, Java and Python, www interfaces, a Windows port of the client library and much more.

mSQL embodies a simple client-server architecture. The server maintains the databases, receives commands from client programs and sends replies. The standard client programs that you will need to use are:

  • msql -- the mSQL terminal monitor -- an interactive program for typing ad hoc queries.
  • relshow -- the mSQL schema viewer -- shows what databases are available on a server and what tables are in a database.
  • msqldump -- the mSQL database dumper -- writes out an ASCII version of a database as a series of SQL statements that can be reloaded into msql.
In addition, if you are acting as the database administrator for an mSQL system, you will use the program msqladmin to send administrative commands to the mSQL server (e.g., telling it to create a new database, to reload the access control file, etc.).

How to use mSQL

How to access a server

You need to know what machine the server is on in order to access it. If it is running on the same machine as you, then it's easy. Try executing the relshow command and see what happens. If there is no server running on your machine you will see something like this:
     %  relshow 
     Error connecting to database : Can't connect to local MSQL server
If there is a server running on your machine, then executing the relshow command will cause a list of the current databases to be displayed, as in:
     %  relshow  

       +-----------------+
       |    Databases    |
       +-----------------+
       | finin           |
       | bevans1         |
       | ...             |
       +-----------------+
You can access a server running on another machines by giving any of the client program a command line argument specifying which Internet host the server is on. For example, to access the mSQL server on cujo.cs.umbc.edu, you could do:
     %  relshow -h cujo.cs.umbc.edu  

How to see what databases are available

The relshow command with no arguments (other than the optional host specifier) will show the databases that are on the server. Just because you see a database in the list doesn't mean you can access it. mSQL has a simple access control mechanism that can control who can read from or write to a database. If you try to access a database in a way that you are not privileged to, you will get an appropriate error message, as in:
     %  relshow secret  
     
     Access to database denied

How to create a new database

Only the DBA ("database administrator") can add or drop a database from mSQL. (Which user is the DBA is defined when mSQL is built.) Both actions are done with the msqladmin command:
     %  msqladmin create company 
     %  msqladmin drop company 
If you (as the DBA) are creating a new database, you may want to add an entry in the file msql.acl which defines access type restrictions to databases. This file should be set up with a "default" access specification for databases (e.g., anyone can read, only root can write, access is permitted from any internet host, etc.).

The DBA can also use the msqladmin command to shutdown the server or cause it to reload the access control file:

     %  msqladmin shutdown 
     %  msqladmin reload 

How to explore the database schema

The relshow command takes an argument which names a database and displays the names of all of the tables in that database. Given both a database and a table, the table schema will be displayed.
    %  relshow company 

     Database = company

       +---------------------+
       |       Table         |
       +---------------------+
       | project             |
       | dept_locations      |
       | employee            |
       | department          |
       | dependent           |
       | works_on            |
       +---------------------+

    %  relshow company project
    
    Database = company
    Table    = project

     +-----------------+----------+--------+----------+-----+
     |     Field       |   Type   | Length | Not Null | Key |
     +-----------------+----------+--------+----------+-----+
     | pname           | char     | 15     | N        | N   |
     | pnumber         | char     | 5      | Y        | N   |
     | plocation       | char     | 10     | N        | N   |
     | dnum            | char     | 1      | N        | N   |
     +-----------------+----------+--------+----------+-----+

How to access and manipulate databases

The msql client program provides an interactive system into which you can type SQL commands for a particular database.

     %  msql company 
     
     Welcome to the miniSQL monitor.  Type \h for help.
     
     mSQL > \h
     
     MiniSQL Help!
     
     The following commands are available :- 
     
     	\q	Quit
     	\g	Go (Send query to database)
     	\e	Edit (Edit previous query)
     	\p	Print (Print the query buffer)
     
     mSQL >  select lname, fname from employee 
         ->  \g 
     
     Query OK.
     
     8 rows matched.
     
      +------------+------------+
      | lname      | fname      |
      +------------+------------+
      | Smith      | John       |
      | Wong       | Frank      |
      | Zelaya     | Alicia     |
      | Wallace    | Jennifer   |
      | Narayan    | Ramesh     |
      | English    | Joyce      |
      | Jabbar     | Ahmad      |
      | Borg       | James      |
      +------------+------------+
     
     mSQL > 
Notice that the msql client takes input and buffers it until you issue a command like \g or \q. Each time you type a newline you will give you the prompt " -> to let you know that more input is expected. You can enter any number of commands before having them sent to the server by the \g. However, it appears that the server stops executing the commands in a series as soon as it encounters an error. Thus, it is a good idea to put a \g after each command.

Comments

The # character introduces comments in the msql terminal program. All characters from a # to the end of the line are ignored. Thus, you should not use the # character in the names of any of your tables or fields. Using #'s in your field names will result in confusing problems, since parts of your table definitions will be missing.

How to define or change the database schema

You can use the msql client to issue SQL statements to the server to define or modify your schema. For example, if we are starting with an empty database company , we can do:

     mSQL >      CREATE TABLE project 
         ->          (pname char(15),
         ->           pnumber char(5) primary key,
         ->           plocation char(10),
         ->           dnum char(1)) \g
     Query OK.
     
     mSQL >     CREATE TABLE dept_locations 
         ->          (dnumber char(1) not null,
         ->           dlocation char(10) not null ) \g
     Query OK.
Since it is easy to make mistakes when you are typing things into the computer, it's good idea to enter all of the mSQL commands to define your database into a file, such as company-schema.msql and then use file redirection to call msql with this as input:
 
     %  msql company < company-schema.msql  
In that way, if there are mistakes, you can edit the file and redo it. Notice that in this file, we've included a DROP TABLE command before each CREATE TABLE command to that the file will work to initially define or to redefine the database.
     DROP TABLE project \g
     CREATE TABLE project 
          (pname char(15),
           pnumber char(5) primary key,
           plocation char(10),
           dnum char(1)) \g
The first time this is executed (i.e., when there is no project table defined) you will see an warning message
     ERROR : Unknown table "foobar"
but it is not treated as a fatal error. However, recall that if a mSQL command in a sequence of commands fails, then the rest of the commands in that sequence are not executed. So be sure to include the \g after the DROP TABLE command. In General, it is a good idea to put a \g after every mSQL command.

How to Populate a database with tuples

You can enter data is with a series INSERT commands. mSQL does support some flexibility in the insert command:
 
    mSQL > drop table test
    mSQL > create table test (one int, two int)
    mSQL > insert into test (one,two) values (11,21) 
    mSQL > insert into test           values (12,22)
    mSQL > insert into test (two,one) values (23,13) 
    mSQL > insert into test (one)     values (14) 
    mSQL > select * from test\g 
    Query OK.
    5 rows matched.
     +----------+----------+
     | one      | two      |
     +----------+----------+
     | 1        | 2        |
     | 11       | 21       |
     | 12       | 22       |
     | 13       | 23       |
     | 14       | NULL     |
     +----------+----------+
Again, you will probably find it convenient to enter a lot of data in a batch mode by typing the mSQL input statements into a file such as company-data.msql and using it as input to the msql client:
 
     %  msql company < company-data.msql 

How to submit a batch of queries

If you want to process a batch of queries, you can use the file redirection trick again. Put the queries in a file like company-query.msql and feed the file to msql:
 
     %  msql company < company-query.msql 

How to Save your database

mSQL immediately commits changes to a database, so there is no explicit COMMIT command. You can use the msqldump client to generate a set of SQL commands to recreate your database:
     %   msqldump company > company-dump.msql 
will write to the file company-dump.msql a series of mSQL commands that will re-create the database if read back into the msql client.

How to Put it all together

You might find it useful to have a simple script like makecompany to define, populate and dump a database. Then you can rebuild your database with just one command:
     %  makecompany 

mSQL APIs

This section is not finished. I hope that eventually it will contain a brief description of the the basic C API:
    int 	msqlConnect (char * host);
    int 	msqlSelectDB (int sock, char *dbName);
    int 	msqlQuery (int sock, char *query);
    void	msqlClose (int sock);
    void 	msqlDataSeek (m_result *result, int pos);
    void 	msqlFieldSeek (m_result* result, int pos);
    void 	msqlFreeResult (m_result * result);
    m_row   msqlFetchRow (m_result * result);
    m_field	*msqlFetchField (m_result * result);
    m_result *msqlListDBs (int sock);
    m_result *msqlListTables (int sock);
    m_result *msqlListFields (int, char*);
    m_result *msqlStoreResult();
    
as well as an example programs in C that does something with the company database. I would also like to show the same example in a few other languages, such as Perl and Tcl. My ambitiousness may result in this section never being done, however.

mSQL and the web

There are at least two packages that support accessing a mSQL database from the web:
  • websql -- a MSQL Table Web Browser Interface
  • w3-msql -- a more general utility for building web-msql interfaces.
  • Php

websql

The websql system is a simple cgi-bin program that lets you browse a mSQL database,issue queries, and insert tuples. Assuming that websql is installed in the gci-bin directory of the web server on www.your.site, to browse an arbitrary database on an arbitrary server, you can use a url like this:
     http://www.your.site/cgi-bin/websql
WEBSQL Table Browser. Assuming your web server and msql server are on the same machine, you can see what is in the Company database using this url in your page:
     http://www.your.site/cgi-bin/websql?db_name=company
WEBSQL Record Browser. A particular table, such as the employee table, can be accessed, browsed and updated with this url:
     http://www.your.site/cgi-bin/websql?db_name=company&db_table_name=employee
Since the websql cgi script runs as uid 'www' (or whatever your web server's effective uid is), your database will have to be accessible by a client running with that uid.

Using W3-MSQL to build web-mSQL interfaces

w3-msql is a program intended to be used as a cgi-script which allows you to embed SQL queries and updates within in your web pages and have the results processed on the fly. An example developed by the w3-msql authors is a shared web bookmarks database. This example lets anyone explore this database, follow links, and add new book mark folders and links.

PHP

PHP is a server side scripting language with a "C flavor" that many people have found very useful in building web-msql applications.

Limitation of mSQL

mSQL has a number of limitations. Some are major and reduce the expressive power of the query language. Others are minor and, while they do not reduce the expressive power, they do make the query language less convenient to use.

Major limitation: WHERE clauses

Within a WHERE clause, AND and OR have the same precedence and you can't use parentheses to specify arbitrary groupings. mSQL also lacks a NOT operator. This means that certain conditions simply can not be expressed.

Limitation: no aggregation functions

There are no aggregation functions (e.g., sum, count, min, max, avg). Although these are not traditionally part of the relational calculus or algebra they are important in practice.

Limitation: no embedded queries

Limitation: no views

Limitation: no set operations

There are no Union or Difference operations.

Limitation: no grouping

No group-by or having clauses

Minor limitation: no composit keys

A key in mSQL can only be a single field. If you declare a field to be a key, then mSQL will enforce two constraints -- that the field be non-null and that the field have unique values. If your table has a key with more than one attribute, the best you can do is to declare that each field be non-null.

Minor limitation: can't use "SELECT *" over multiple tables

In mSQL's SELECT you can not use * for the fields you want in the answer if there are two or more tables involved. Note that this limitation does not reduce the "expressive power" of mSQL but it is somewhat annoying. Here is an example. Suppose we define T1 and T2 as:
  create table t1 (a int, b int)
  create table t2 (b int, c int)
  insert into t1 values (1,2)
  insert into t2 values (2,3)
Then these queries generate errors:
  
  mSQL > select * from t1, t2 where t1.b=t2.b \g
  ERROR : Reference to un-selected table ""

  mSQL > select * from t1,t2 \g
  ERROR : Reference to un-selected table ""
But explicitly specifying the fields to return works:
  mSQL > select t1.a,t1.b,t2.b,t2.c from t1, t2 \g

   +----------+----------+----------+----------+
   | a        | b        | b        | c        |
   +----------+----------+----------+----------+
   | 1        | 2        | 2        | 3        |
   +----------+----------+----------+----------+

  mSQL > select t1.a,t1.b,t2.c from t1, t2 where t1.b=t2.b \g
  
   +----------+----------+----------+
   | a        | b        | c        |
   +----------+----------+----------+
   | 1        | 2        | 3        |
   +----------+----------+----------+

For more information

Saturday, 05-Mar-2005 15:20:37 EST

5 March, 2005 15:20