Introduction



Mini SQL, or mSQL, is a lightweight database engine designed to provide fast access to stored 
data with low memory requirements. As its name implies, mSQL offers a subset of SQL as its 
query interface. Although it only supports a subset of SQL (no views, sub-queries etc.), every-
thing it supports is in accordance with the ANSI SQL specification. The mSQL package includes 
the database engine, a terminal "monitor" program, a database administration program, a schema 
viewer, and a C language API. The API and the database engine have been designed to work in a 
client/server environment over a TCP/IP network.









































Mini SQL Specification





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 rela-
tional 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.



Although the definitions and examples below depict mSQL key words in upper case, no such 
restriction is placed on the actual queries.







The Create Clause

The create clause as supported by mSQL can only 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 (

	col_name		col_type		[ not null | primary key ]

	[ , col_name		col_type		[ not null | primary key ] ]**

)





for example



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

)







The available types are:-



char (len)			String of chracters (or other 8 bit data)

int			Signed integer values

real			Decimal or Scientific Notation real values



The Drop Clause

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





DROP TABLE table_name

for example

DROP TABLE emp_details







The Insert 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 do not specify the field names they will be used in the 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]** )



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')



The number of values supplied must match the number of columns.







The Delete Clause

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] ]



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 emp_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 DISTINCT operator could be used:



SELECT DISTINCT first_name, last_name FROM emp_details

WHERE dept = `finance'

ORDER BY last_name, first_name DESC

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

·	`\' escapes special characters (e.g. `\%' matches % and `\\' matches \ )

·	all other characters match themselves



So, to search for anyone in finance who's last name consists 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:





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 manner, 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 table aliases t1 and t2 both point to the same table (parent_data in this case) and are treated as 
two different tables taht just happen to contain exactly the same data.



The Update Clause

The mSQL update clause cannot use a column name as a value. Only literal values may by 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



for example

UPDATE emp_details SET salary=30000 WHERE emp_id = 1234



































































The Database Engine





The mSQL daemon, msqld, 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 mechanism that ensures that INSERT 
operations are performed at the same speed regardless of the size of the table being accessed. Pre-
liminary testing performed by a regular user of mSQL has shown that for simple queries, the per-
formance 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 per-
formed roughly 4 times faster than University Ingres and over 20 times faster than Postgres on an 
Intel 486 class machine running Linux.



The server may be accessed either via a well known TCP socket or via a UNIX domain socket 
with the file system ( /dev/msqld ). The availability of the TCP socket allows client software to 
access data stored on machine over the network. Use of the TCP socket should be limited to client 
software on remote machines as communicating with the server via a TCP socket rather than the 
UNIX socket will result in a substantial drop in performance. See the details on the programming 
API and also the command line options to standard programs for details on selecting the server 
machine.



The engine includes debugging code so that its progress can be monitored. There are currently 8 
debugging modules available in the engine. Debugging for any of the available modules can be 
enabled at runtime by setting the contents of the MINERVA_DEBUG environment variable to a 
colon separated list of debug module names. A list of available debug modules is given below:

·	cache	Display the workings of the table cache

·	query	Display each query before it is executed

·	error	Display error message as well as sending them to the client

·	key	Display d