Introduction Mini SQL 3.0 is the third generation of the mSQL database system. The development of this generation of the product has been aimed at widening the potential application of the software. Depending on one's point of view, mSQL 2.x could have been viewed as either too small (for multi-million entry databases requiring simultaneous query execution) or too large (for embedded systems). This release provides configurable features that will allow it to be used in large enterprise environments, embedded systems, and everything else in between.
To cater for the widest range of applications, two version of the database server are provided: a single process server called msql3d, and a multi-process server called msql3_broker. msql3d is similar in concept to the server provided with the mSQL 2.x release. The new msql3_broker backend (or broker based server) allows a group of processes to be configured to simultaneously handle queries coming from multiple clients. By tuning the size of the process pool, the number of queries that can be processed simultaneously can be set.
New features common to both the servers include extensions to the range of SQL statements supported, more efficient CPU and memory utilisation, an enhanced query optimiser allowing much greater use of indicies, and a more reliable module based structure to the underlying server code.

Single Process
and Multi-Process
Servers This generation of mSQL includes two version of the server: a single process and a multi-process (broker based) server. The single process server is called msql3d and is similar in design to the 2.x server. The new broker based server, called msql3_broker, uses a process farm model that allows multiple queries to be executed simultaneously. The number of backend process (in addition to the actual broker process) is controlled by the new Num_Children configuration file item. Each back-end process is capable of simultaneously executing a client's query.

Enhanced Indexing The query optimiser has been enhanced for the 3.0 release. It now supports index ranging as well as index equality testing. As a result, conditions that use "<" ">" or "BETWEEN" will now result in an indexed access to the data table rather than a sequential scan.

Data Types Further data types have been added. The new types are listed below. int8, int16, int32, int64 8, 16, 32 and 64 bit integers uint8, uint16, uint32, uint64 8, 16, 32 and 64 bit unsigned integers ipv4, ipaddr An internet address (version 4 only)

Function 3.0 includes support for value functions in SELECT queries. The functions currently available for manipulating character values include upper ( ), lower( ), length( ), substr( ), chop( ), translate( ), replace( ), soundex( )
Numeric values can be manipulated using the following functions abs( ), ceil( ), floor( ), mod( ), sign( ), power( )

Regular Expressions Regular expression conditions (LIKE etc) can now be applied to TEXT fields. Previously, regular expression testing could only be performed on fixed length character fields (CHAR) not variable length character fields (TEXT).

Tunable Memory Use Runtime configuration items have been added to control two of the main areas of memory use: the table cache and the internal sorting buffer. mSQL 2.x had an internal, hard coded size of 8 entries for the table cache. Each entry consumes both memory and file descriptor resources. The size of the table cache is now dynamic and can be set using the Table_Cache item of the configuration file.
The configuration file now also offers a Sort_Max_Mem item that defines the largest amount of memory that the server is allowed to use during the processing of an ORDER or DISTINCT clause. If the operation will consume more memory than the defined limit an alternate algorithm is used.

Higher Performance An extensive audit of the server software has been undertaken with the aim of increasing both reliability and performance. Both goals have been achieved. The single process server included with mSQL 3.x executes standard benchmark tests 30faster than the server distributed in the mSQL 2.0.12 release. On systems and applications that can benefit from executing multiple queries simultaneously, the broker based server will produce even greater performance increases in terms of user response times.

Introduction		Mini SQL 3.0 is the third generation of the mSQL database system. The development of this generation of the product has been aimed at widening the potential application of the software. Depending on one's point of view, mSQL 2.x could have been viewed as either too small (for multi-million entry databases requiring simultaneous query execution) or too large (for embedded systems). This release provides configurable features that will allow it to be used in large enterprise environments, embedded systems, and everything else in between. To cater for the widest range of applications, two version of the database server are provided: a single process server called msql3d, and a multi-process server called msql3_broker. msql3d is similar in concept to the server provided with the mSQL 2.x release. The new msql3_broker backend (or broker based server) allows a group of processes to be configured to simultaneously handle queries coming from multiple clients. By tuning the size of the process pool, the number of queries that can be processed simultaneously can be set. New features common to both the servers include extensions to the range of SQL statements supported, more efficient CPU and memory utilisation, an enhanced query optimiser allowing much greater use of indicies, and a more reliable module based structure to the underlying server code.

Single Process and Multi-Process Servers		This generation of mSQL includes two version of the server: a single process and a multi-process (broker based) server. The single process server is called msql3d and is similar in design to the 2.x server. The new broker based server, called msql3_broker, uses a process farm model that allows multiple queries to be executed simultaneously. The number of backend process (in addition to the actual broker process) is controlled by the new Num_Children configuration file item. Each back-end process is capable of simultaneously executing a client's query.

Enhanced Indexing		The query optimiser has been enhanced for the 3.0 release. It now supports index ranging as well as index equality testing. As a result, conditions that use "<" ">" or "BETWEEN" will now result in an indexed access to the data table rather than a sequential scan.

Data Types		Further data types have been added. The new types are listed below. int8, int16, int32, int64 8, 16, 32 and 64 bit integers uint8, uint16, uint32, uint64 8, 16, 32 and 64 bit unsigned integers ipv4, ipaddr An internet address (version 4 only)

Function		3.0 includes support for value functions in SELECT queries. The functions currently available for manipulating character values include upper ( ), lower( ), length( ), substr( ), chop( ), translate( ), replace( ), soundex( ) Numeric values can be manipulated using the following functions abs( ), ceil( ), floor( ), mod( ), sign( ), power( )

Regular Expressions		Regular expression conditions (LIKE etc) can now be applied to TEXT fields. Previously, regular expression testing could only be performed on fixed length character fields (CHAR) not variable length character fields (TEXT).

Tunable Memory Use		Runtime configuration items have been added to control two of the main areas of memory use: the table cache and the internal sorting buffer. mSQL 2.x had an internal, hard coded size of 8 entries for the table cache. Each entry consumes both memory and file descriptor resources. The size of the table cache is now dynamic and can be set using the Table_Cache item of the configuration file. The configuration file now also offers a Sort_Max_Mem item that defines the largest amount of memory that the server is allowed to use during the processing of an ORDER or DISTINCT clause. If the operation will consume more memory than the defined limit an alternate algorithm is used.

Higher Performance		An extensive audit of the server software has been undertaken with the aim of increasing both reliability and performance. Both goals have been achieved. The single process server included with mSQL 3.x executes standard benchmark tests 30faster than the server distributed in the mSQL 2.0.12 release. On systems and applications that can benefit from executing multiple queries simultaneously, the broker based server will produce even greater performance increases in terms of user response times.