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:
% relshow Error connecting to database : Can't connect to local MSQL serverIf 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
% relshow secret Access to database denied
% msqladmin create company % msqladmin drop companyIf 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
% 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 | +-----------------+----------+--------+----------+-----+
% 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.
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.msqlIn 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)) \gThe 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.
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
% msql company < company-query.msql
% msqldump company > company-dump.msqlwill 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.
% makecompany
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.
http://www.your.site/cgi-bin/websqlWEBSQL 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=companyWEBSQL 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=employeeSince 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.
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 | +----------+----------+----------+