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
[an error occurred while processing this directive]
Saturday, 05-Mar-2005 15:20:37 EST
5 March, 2005 15:20