Mini SQL 2.0 (Beta)

Language Specifications

Introduction

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 some relational capabilities such as views and 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 definitions and examples below depict mSQL key words in upper case, but no such restriction is placed on the actual queries.

The Create Clause
The create clause as supported by mSQL 2 can be used to create tables, indices, and sequences. It cannot be used to create other definitions such as views. The three valid constructs of the create clause are shown below:

CREATE TABLE table_name (

col_name col_type [ not null ]

[ , col_name col_type [ not null ] ]**

)

CREATE [ UNIQUE ] INDEX index_name ON table_name (

field_name

[ , field_name ] **

)

CREATE SEQUENCE ON table_name [ STEP step_val ] [ VALUE initial_val ]

An example of the creation of a table is show below:

CREATE TABLE emp_details (

first_name char(15) not null,

last_name char(15) not null,

comment text(50),

dept char(20),

emp_id int

)

The available types are:
 

The table structure shown in the example would benefit greatly from the creation of some indices. It is assumed that the emp_id field would be a unique value that is used to identify an employee. Such a field would normally be defined as the primary key. mSQL 2.0 has removed support for the primary key construct within the table creation syntax although the same result can be achieved with an index. Similarly, a common query may be to access an employee based on the combination of the first and last names. A compound index (i.e. constructed from more than 1 field) would improve performance. We could construct these indices using:

CREATE UNIQUE INDEX idx1 ON emp_details (emp_id)
CREATE INDEX idx2 ON emp_details (first_name, last_name)

These indices will be used automatically whenever a query is sent to the database engine that uses those fields in its WHERE clause. The user is not required to specify any special values in the query to ensure the indices are used to increase performance.

Sequences provide a mechanism via which a sequence value can be maintained by the mSQL server. This allows for atomic operations (such as getting the next sequence value) and removes the concerns associated with performing these operations in client applications. A sequence is associated with a table and a table may contain at most one sequence.

Once a sequence has been created it can be accessed by SELECTing the _seq system variable from the table in which the sequence is defined.

For example:
CREATE SEQUENCE ON test STEP 1 VALUE 5
SELECT _seq FROM test

The above CREATE operation would define a sequence on the table called test that had an initial value of 5 and would be incremented each time it is accessed (i.e. have a step of 1). The SELECT statement above would return the value 5. If the SELECT was issued again, a value of 6 would be returned. Each time the _seq field is selected from test the current value is returned to the caller and the sequence value itself is incremented.

Using the STEP and VALUE options a sequence can be created that starts at any specified number and is incremented or decremented by any specified value. The value of a sequence would decrease by 5 each time it was accessed if it was defined with a step of -5.

The Drop Clause
The Drop clause is used to remove a definition from the database. It is most commonly used to remove a table from a database but can also be used for removing several other constructs. In 2.0 it can be used to remove the definition of an index, a sequence, or a table. It should be noted that dropping a table or an index removes the data associated with that object as well as the definition.

The syntax of the drop clause as well as examples of its use are given below.

DROP TABLE table_name
DROP INDEX index_name FROM table_name
DROP SEQUENCE FROM table_name

For example:
DROP TABLE emp_details
DROP INDEX idx1 FROM emp_details
DROP SEQUENCE FROM 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', `Development','12345')

INSERT INTO emp_details VALUES (`David', `Hughes', `Development','12345')

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

The Select Clause
The SELECT offered by mSQL lacks some of the features provided by the standard SQL specification. Development of mSQL 2 is continuing and some of this missing functionality will be made available in the next beta release. At this point in time, mSQL's select does not provide

• Nested selects
• 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
• Complex conditions

The formal definition of the syntax for mSQL's select clause 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 <,> , =, <=, =, <>, LIKE, RLIKE or CLIKE
VALUE can be a literal value or a column name

Where clauses may contain '(' ')' to nest conditions e.g. "where (age <20 or age>30) and sex = 'male'".

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 from the result of the select, 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

mSQL provides three regular expression operators for use in where comparisons. The standard SQL syntax provides a very simplistic regular expression capability that does not provide the power nor the flexibility UNIX programmers or users will be accustomed to. mSQL supports the "standard" SQL regular expression syntax, via the LIKE operator, but also provide further functionality if it is required. The available regular expression operators are:

• LIKE - the standard SQL regular expression operator.
• CLIKE - a standard LIKE operator that ignores case.
• RLIKE - a complete UNIX regular expression operator.

Note : CLIKE and RLIKE are not standard SQL and may not be available in other implementations of the language if you decide to port your application. They are however very convenient and powerful features of mSQL.

The regular expression syntax supported by the LIKE and CLIKE operators is that of standard SQL and is outlined below

As an example of the LIKE operator, it is possible to search for anyone in the finance department who's last name consists of any letter followed by `ughes', such as Hughes. The query to perform this operation could look like

SELECT first_name, last_name FROM emp_details

WHERE dept = `finance' and last_name like `_ughes'

The RLIKE operator provides access to the power of the UNIX standard regular expression syntax. The UNIX regular expression syntax provides far greater functionality than SQL's LIKE syntax. The UNIX regex syntax does not use the '_' or '%' characters in the way SQL's regex does (as outlined above). The syntax available in the RLIKE operator is

The power of a relational query language starts to become apparent when you join tables together during a select operation. 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, 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 also supports table aliases so that you can perform a join of a table onto itself. This may appear to be an unusual thing to do but it is a very powerful feature if there are rows within a single table relate to each other in some way. An example of such a table could be a list of people including the names of their parents. In such a table there would be multiple rows with a parent/child relationship. Using a table alias you could find out any grandparents contained in the table 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 that just happen to contain exactly the same data.

The Delete Clause
The SQL DELETE construct is used to remove one or more entries from a database table. The selection of rows to be removed from the table is based on the same where construct as used by the SELECT 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 <,>, =, <=, =, <>, LIKE, RLIKE, or CLIKE

for example

DELETE FROM emp_details WHERE emp_id = 12345

The Update Clause
The SQL update clause is used to modify data that is already in the database. The operation is carried out on one or more rows as specified by the where construct. The value of any number of fields on the rows matching the where construct can be updated. mSQL places a limitation on the operation of the update clause in that it cannot use a column name as an update value (i.e. you cannot set the value of one field to the current value of another field). Only literal values may by used as an update value. The syntax supported by mSQL is

UPDATE table_name SET column=value [ , column=value ]**

WHERE column OPERATOR value

[ AND | OR column OPERATOR value ]**

OPERATOR can be <,> , =, <=, =, <>, LIKE, RLIKE or CLIKE

for example

UPDATE emp_details SET salary=30000 WHERE emp_id = 1234

Mini SQL 2.0 (Beta) System Variables

Introduction
Mini SQL 2.0 includes internal support for system variables (often known as pseudo fields or pseudo columns). These variables can be accessed in the same way that normal table fields are accessed although the information is provided by the database engine itself rather than being loaded from a database table. System variables are used to provide access to server maintained information or meta data relating to the databases.

System variables may be identified by a leading underscore in the variables name. Such an identifier is not valid in mSQL for table or field names. Examples of the supported system variables and uses for those variables are provided below.

Available System Variables
The mSQL 2 engine currently supports the following system variables:

_rowid

The _rowid system variable provides a unique row identifier for any row in a table. The value contained in this variable is the internal record number used by the mSQL engine to access the table row. It may be included in any query to uniquely identify a row in a table. An example of such queries could be:

select _rowid, first_name, last_name from emp_details

where last_name = 'Smith'

update emp_details set title = 'IT Manager'

where _rowid = 57

The candidate row module is capable of utilising _rowid values to increase the performance of the database. In the second example query above, only 1 row (the row with the internal record ID of 57) would be accessed. This is in contrast to a sequential search through the database looking for that value which may result in only 1 row being modified but every row being accessed. Using the _rowid value to constrain a search is the fastest access method available in mSQL 2.0. As with all internal access decisions, the decision to base the table access on the _rowid value is automatic and requires no action by the programmer or user other than including the _rowid variable in the where clause of the query.

_timestamp

The _timestamp system variable contains the time at which a row was last modified. The value, although specified in the standard UNIX time format (i.e. seconds since the epoch), is not intended for interpretation by application software. The value is intended to be used as a point of reference via which an application may determine if a particular row has was modified before or after another table row. The application should not try to determine an actual time from this value as the internal representation used may change in a future release of mSQL.

The primary use for the _timestamp system variable will be internal to the mSQL engine. Using this information, the engine may determine if a row has been modified after a specified point in time (the start of a transaction for example). It may also use this value to synchronise a remote database for database replication. Although neither of these functions is currently available, the presence of a row timestamp is the first step in the implementation.

Example queries may be:

select first_name, _timestamp from emp_details

where first_name like '%fred%'

order by _timestamp
 

select * from emp_details

where _timestamp 88880123

_seq

The _seq system variable is used to access the current sequence value of the table from which it is being selected. The current sequence value is returned and the sequence is update to the next value in the sequence (see the CREATE section of the Language Specification section from more information on sequences).

An example query using _seq could be

select _seq from staff

_sysdate

The server can provide a central standard for the current time and date. If selected from any table, the _sysdate system variable will return the current time and date on the server machine using the standard UNIX time format (e.g. seconds since the epoch).

An example query using _sysdate could be

select _sysdate from staff

_user

By selecting the _user system variable from any table, the server will return the username of the user who submitted the query.

An example query using _user could be

select _user from staff

Mini SQL 2.0 (Beta) C Programming API

Introduction

Included in the distribution is the mSQL API library, libmsql.a. 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 (using -lmsql as an argument to your C compiler). The library and header file will be installed by default into /usr/local/ Hughes/lib and /usr/local/Hughes/include respectively.

Like the mSQL engine, the API supports debugging via the MSQL_DEBUG environment variable. Three debugging modules are currently supported by the API: query, api, and malloc. Enabling "query" debugging will cause the API to print the contents of queries as they are sent to the server. The "api" debug modules causes internal information, such as connection details, to be printed. Details about the memory used by the API library can be obtained via the "malloc" debug module. Information such as the location and size of malloced blocks and the addresses passed to free() will be generated. Multiple debug modules can be enabled by setting MSQL_DEBUG to a colon separated list of module names. For example setenv MSQL_DEBUG api:query

Query Related Functions

msqlConnect()

int msqlConnect ( host )

char * host ;

msqlConnect() forms an interconnection with the mSQL engine. It takes as its only argument the name or IP address of the host running the mSQL server. If NULL is specified as the host argument, a connection is made to a server running on the localhost using the UNIX domain socket /dev/msqld. If an error occurs, a value of -1 is returned and the external variable msqlErrMsg will contain an appropriate text message. This variable is defined in "msql.h".

If the connection is made to the server, an integer identifier is returned to the calling function. This values is used as a handle for all other calls to the mSQL API. The value returned is in fact the socket descriptor for the connection. By calling msqlConnect() more than once and assigning the returned values to separate variables, connections to multiple database servers can be maintained simultaneously.

In previous versions of mSQL, the MSQL_HOST environment variable could be used to specify a target machine if the host parameter was NULL. This is no longer the case.

msqlSelectDB()

int msqlSelectDB ( sock , dbName )

int sock ;

char * dbName ;

Prior to submitting any queries, a database must be selected. msqlSelectDB() instructs the engine which database is to be accessed. msqlSelectDB() is called with the socket descriptor returned by msqlConnect() and the name of the desired database. A return value of -1 indicates an error with msqlErrMsg set to a text string representing the error. msqlSelectDB() may be called multiple times during a program's execution. Each time it is called, the server will use the specified data- base for future accesses. By calling msqlSelectDB() multiple times, a program can switch between different databases during its execution.

msqlQuery()

int msqlQuery ( sock , query )

int sock ;

char * query ;

Queries are sent to the engine over the connection associated with sock as plain text strings using msqlQuery(). As with previous releases of mSQL, a returned value of -1 indicates an error and msqlErrMsg will be updated to contain a valid error message. If the query generates output from the engine, such as a SELECT statement, the data is buffered in the API waiting for the application to retrieve it. If the application submits another query before it retrieves the data using msqlStoreResult(), the buffer will be overwritten by any data generated by the new query.

In previous versions of mSQL, the return value of msqlQuery() was either -1 (indicating an error) or 0 (indicating success). mSQL 2 adds to these semantics by providing more information back to the client application via the return code. If the return code is greater than 0, not only does it imply success, it also indicates the number of rows "touched" by the query (i.e. the number of rows returned by a SELECT, the number of rows modified by an update, or the number of rows removed by a delete).

msqlStoreResult()

m_result * msqlStoreResult ( )

Data returned by a SELECT query must be stored before another query is submitted or it will be removed from the internal API buffers. Data is stored using the msqlStoreResult() function which returns a result handle to the calling routines. The result handle is a pointer to a m_result structure and is passed to other API routines when access to the data is required. Once the result handle is allocated, other queries may be submitted. A program may have many result handles active simultaneously.

msqlFreeResult()

void msqlFreeResult ( result )

m_result * result ;

When a program no longer requires the data associated with a particular query result, the data must be freed using msqlFreeResult(). The result handle associated with the data, as returned by msqlStoreResult() is passed to msqlFreeResult() to identify the data set to be freed.

msqlFetchRow()

m_row msqlFetchRow ( result )

m_result * result ;

The individual database rows returned by a select are accessed via the msqlFetchRow() function. The data is returned in a variable of type m_row which contains a char pointer for each field in the row. For example, if a select statement selected 3 fields from each row returned, the value of the 3 fields would be assigned to elements [0], [1], and [2] of the variable returned by msqlFetchRow(). A value of NULL is returned when the end of the data has been reached. See the example at the end of this sections for further details. Note, a NULL value is represented as a NULL pointer in the row.

msqlDataSeek()

void msqlDataSeek ( result , pos )

m_result * result ;

int pos ;

The m_result structure contains a client side "cursor" that holds information about the next row of data to be returned to the calling program. msqlDataSeek() can be used to move the position of the data cursor. If it is called with a position of 0, the next call to msqlFetchRow() will return the first row of data returned by the server. The value of pos can be anywhere from 0 (the first row) and the number of rows in the table. If a seek is made past the end of the table, the next call to msqlFetchRow() will return a NULL.

msqlNumRows()

int msqlNumRows ( result )

m_result * result ;

The number of rows returned by a query can be found by calling msqlNumRows() and passing it the result handle returned by msqlStoreResult(). The number of rows of data sent as a result of the query is returned as an integer value.

If a select query didn't match any data, msqlNumRows() will indicate that the result table has 0 rows (note: earlier versions of mSQL returned a NULL result handle if no data was found. This has been simplified and made more intuitive by returning a result handle with 0 rows of result data)

msqlFetchField()

m_field * msqlFetchField ( result )

m_result * result ;

Along with the actual data rows, the server returns information about the data fields selected. This information is made available to the calling program via the msqlFetchField() function. Like msqlFetchRow(), this function returns one element of information at a time and returns NULL when no further information is available. The data is returned in a m_field structure which contains the following information:

Possible values for the type field are defined in msql.h as

INT_TYPE, CHAR_TYPE and REAL_TYPE. The individual attribute flags can be accessed using the following macros:

msqlFieldSeek()

void msqlFieldSeek ( result , pos )

m_result * result ;

int pos ;

The result structure includes a "cursor" for the field data. It's position can be moved using the msqlFieldSeek() function. See msqlDataSeek() for further details.

msqlNumFields()

int msqlNumFields ( result )

m_result * result ;

The number of fields returned by a query can be ascertained by calling msqlNumFields() and passing it the result handle. The value returned by msqlNumFields() indicates the number of elements in the data vector returned by msqlFetchRow(). It is wise to check the number of fields returned before, as with all arrays, accessing an element that is beyond the end of the data vector can result in a segmentation fault

msqlClose()

int msqlClose ( sock )

int sock ;

The connection to the mSQL engine can be closed using msqlClose().  The function must be called with the connection socket returned by msqlConnect() when the initial connection was made.

Schema Related Functions

msqlListDBs()

m_result * msqlListDBs ( sock )

int sock ;

A list of the databases known to the mSQL engine can be obtained via the msqlListDBs() function. A result handle is returned to the calling program that can be used to access the actual database names. The individual names are accessed by calling msqlFetchRow() passing it the result handle. The m_row data structure returned by each call will contain one field being the name of one of the available databases. As with all functions that return a result handle, the data associated with the result must be freed when it is no longer required using

msqlFreeResult(). msqlListTables()

m_result * msqlListTables ( sock )

int sock ;

Once a database has been selected using msqlInitDB(), a list of the tables defined in that database can be retrieved using msqlListTables(). As with msqlListDBs(), a result handle is returned to the calling program and the names of the tables are contained in data rows where element [0] of the row is the name of one table in the current database. The result handle must be freed when it is no longer needed by calling msqlFreeResult().

msqlListFields()

m_result * msqlListFields ( sock , tableName ) ;

int sock;

char * tableName;

Information about the fields in a particular table can be obtained using msqlListFields(). The function is called with the name of a table in the current database as selected using msqlSelectDB() and a result handle is returned to the caller. Unlike msqlListDBs() and msqlListTables(), the field information is contained in field structures rather than data rows. It is accessed using msqlFetchField(). The result handle must be freed when it is no longer needed by calling msqlFreeResult().

msqlListIndex()

m_result * msqlListIndex ( sock , tableName , index ) ;

int sock ;

char * tableName;

char * index;

The structure of a table index can be obtained from the server using the msqlListIndex() function. The result table returned contains one field. The first row of the result contains the symbolic name of the index mechanism used to store the index. Rows 2 and onwards contain the name of the fields that comprise the index. For example, if a compound index was defined as an AVL Tree index and was based on the values of the fields first_name and last_name, then the result table would look like

Currently the only valid index type is 'avl' signifying a memory mapped AVL tree.

Mini SQL 2.0 (Beta) Standard Programs and Utilities

The monitor - msql

Schema viewer - relshow

Admin program - msqladmin