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

    • char (len) String of characters (or other 8 bit data)
    text (len) Variable length string of chracters (or other 8 bit data) The defined length is used to indicate the expected average length of the data. Any data longer than the specified length will be split between the data table and external overflow buffers.
  • Note : text fields are slower to access than char fields and cannot be used in an index nor in LIKE tests.
    • int Signed integer values
    real Decimal or Scientific Notation real values



    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

    `_' 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

    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

    • '.' matches any single character
    '^' When used as the first charactr in a regex, the caret character forces the match to start at the first character of the string
    '$' When used as the last charactr in a regex, the dollar sign forces the match to end at the last character of the string
    '[ ]' By enclosing a group of single characters withing square brackets, the regex will match a single character from the group of characters. If the ']' character is one of the characters you wish to match you may specifiy it as the first character in the group without closing the group (e.g. '[]abc]' would match any single character that was either ']', 'a', 'b', or 'c'). Ranges of characters can be specified within the group using the 'first-last' syntax (e.g. '[a-z0-9]' would match any lower case letter or a digit). If the first charactr of the group is the '^' character the regex will match any single character that is not contained within the group.
    '*' If any regex element is followed by a '*' it will match zero or more instances of the regular expression.

    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:

    •
    • typedef struct {
    		char * name ; /* name of field */
      char * table ; /* name of table */
      int type ; /* data type of field */
      int length , /* length in bytes of field */
      int flags ; /* attribute flags */
    } m_field;

  • 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 compund 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

    •

    • row[0]
    avl
    first_name
    last_name

  • 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

    Usage msql [-h host] [-f confFile] database
    Options -h Specify a remote hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    Description The mSQL monitor is an interactive interface to the mSQL server. It allows you to submit SQL commands directly to the server. Any valid mSQL syntax can be entered at the prompt provided by the mSQL monitor.
  • Control of the monitor itself is provided by 4 internal commands. Each command is comprised of a backslash followed by a single character. The available command are
    		•  
    \q Quit  
    \g Go (Send the query to the server)
    \e Edit (Edit the previous query)
    \p Print (Print the query buffer)


    Schema viewer - relshow

    Usage relshow [-h host] [-f confFile] [database [rel [idx] ] ]
    Options -h Specify a remore hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    Description Relshow is used to display the structure of the contents of mSQL databases. If no arguments are given, relshow will list the names of the databases currently defined. If a database name is given it will list the tables defined in that database. If a table name is also given then it will display the structure of the table (i.e. field names, types, lengths etc).
  • If an index name is provided along with the database and table names, relshow will display the structure of the specified index including the type of index and the fields that comprise the index.


    • Admin program - msqladmin

    Usage msqladmin [-h host] [-f confFile] [-q] Command
    Options -h Specify a remore hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    -q Put msqladmin into quiet mode. If this flag is specified, msqladmin will not prompt the user to verify dangerous actions (such as dropping a database).
    Description msqladmin is used to perform administrative operations on an mSQL database server. Such tasks include the creation of databases, performing server shutdowns etc. The available commands for msqladmin are
    create db_name Creates a new database called db_name
    drop db_name Removes the database called db_name from the server. This will also delete all data contained in the database!
    shutdown Terminates the mSQL server.
    reload Forces the server to reload ACL information.
    version Displays version and configuration information about the currently running server.
    stats Displays server statistics.
  • Note : most administrative functions can only be executed by the user specified in the run-time configuration as the admin user. They can also only be executed from the host on which the server process is running (e.g. you cannot shutdown a remote server process).


    • Data dumper - msqldump

    Usage msqldump [-h host] [-f confFile] [-c] [-v] database [table]
    Options -h Specify a remore hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    -c Include column names in INSERT commands generated by the dump.
    -v Run in verbose mode. This will display details such as connection results etc.
    Description msqldump produces an ASCII text file containing valid SQL commands that will recreate the table or database dumped when piped through the mSQL monitor program. The output will include all CREATE TABLE commands required to recreate the table structures, CREATE INDEX commands to recreate the indices, and INSERT commands to populate the tables with the data currently contained in the tables.
  • Note : msqldump does not recreate sequences at this time.


    • Data exporter - msqlexport

    Usage msqlexport [-h host] [-f conf] [-v] [-s Char] [-q Char] [-e Char] database table
    Options -h Specify a remore hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    -v Verbose mode
    -s Use the character Char as the separation character. The default is a comma.
    -q Quote each value with the specified character
    -e Use the specifed Char as the escape character. The default is \
    Description msqlexport produces an ASCII export of the data from the specified table. The output produced can be used as input to other programs such as spreadsheets. It has been designed to be as flexible as possible allowing the user to specify the character to use to separate the fields, the character to use to escape the separator character if it appears in the data, and whether the data should be quoted and if so what character to use as the quote character.
  • The output is sent to stdout with one data row per line.


    • Data importer - msqlimport

    Usage msqlimport [-h host] [-f conf] [-v] [-s Char] [-e Char] [-c col,col...] database table
    Options -h Specify a remore hostname or IP address on which the mSQL server is running. The default is to connect to a server on the localhost using a UNIX domain socket rather than TCP/IP (which gives better performance)
    -f Specify a non-default configuration file to be loaded. The default action is to load the the standard configuration file located in INST_DIR/msql.conf (usually /usr/local/Hughes/msql.conf)
    -v Verbose mode
    -s Use the character Char as the separation character. The default is a comma.
    -e Use the specifed Char as the escape character. The default is \
    -c A comma separated list of column names into which the data will be inserted.
    Note : there can be no spaces in the list.
    Description msqlimport loads a flat ASCII data file into an mSQL database table. The file can be formatted using any character as the column separator. When passed through msqlimport, each line of the txt file will be loaded as a row in the database table. The separation character as specified by the -s flag, will be used to split the line of text into columns. If the data uses a specific character to escape any occurence of the separation character in the data, the escape character can be specified with the -e flag and will be removed from the data before it is inserted.

    Mini SQL 2.0 (Beta) Run Time Configuration


    Introduction

    mSQL 1.x offered several configuration options, including such details as the user the server should run as, the location of the TCP and UNIX sockets for client/server communications, the location of the database files etc. The problem with configuring mSQL 1.x was that all these details were hard-coded into the software at compile time. Once the software was compiled and installed you couldn't easily change those settings.

    To overcome this problem, mSQL 2.0 utilises an external run-time configuration file for definition of all these values. The file is called msql.conf and is located in the installation directory (usually /usr/local/Hughes). An application can choose to use a different configuration file by calling the new msqlLoadConfigFile( ) API function. All standard mSQL applications and utilities provide a command line flag, -f ConfFile , that allows you to specify a non-standard configuration file. When an application first calls the mSQL API library, a check is made to see if a configuration file has been loaded via a call to the msqlLoadConfigFile( ) function. If no such call has been made, the API library loads the default config file. Any values that are specified in that file will over-ride the normal operating paramaters used by mSQL.


    Structure of the config file

    The configuration file is a plain text file organised into sections. The file can contain blank lines and comments. A comment is a line that begins with the '#' character. Each section of the configuration file has a section header, which is written as the section name enclosed in square brackets (for example [ general ]). Currently the only section defined is the general section although further sections covering security and access control will be added later.

    Configuration values within a section are presented using the config parameter name followed by and equals sign and then the new value. There can only be one entry per line and if an entry is defined multiple times in the one config file the last value defined will be used. If a parameter is not defined in the config file then an internal default value will be used at run-time.


    Elements of the General section

    The following configuration parameters are available in the general section of the config file. Please note that %I may be used in configuration entries to signify the mSQL installation directory (e.g. /usr/local/Hughes).

    Parameter

    Default Value

    Definition
    Inst_Dir /usr/local/Hughes The full path to the installation directory. This is the directory in which all the mSQL files are located (such as the program files, the database files etc).
    mSQL_User msql The user that the mSQL server should run as. If the server is started by a user other than this user (e.g. it is started as root from a boot script) it will change UID so that it runs as the specified user.
    Admin_User root The user that is allowed to perform privileged operations such as server shutdown, cration of databases etc.
    Pid_File %I/msql2.pid The full path of a file in which the PID of the running mSQL server process will be stored.
    TCP_Port 1114 The TCP port number on which the mSQL server will accept client/server connections over a TCP/IP network. If this value is modified it must be modified on the machine running the client software also.
    UNIX_Port %I/msql2.sock The full path name of the UNIX domain socket created by the mSQL server for connections from client applications running on the same machine.


    Example configuration file

    Below is a sample configuration file. This file does not achieve anything as it just sets the parameters to their default values. 

    

#

# msql.conf  -  Configuration file for Mini SQL Version 2

#

#--------------------------------------------------------------

#

# This file is an example configuration and may require 

# modification to suit your needs or your site.  The values 

# given are the default values and will be used by the 

# software if either this file is missing or a specific value 

# is not specified.

#

#--------------------------------------------------------------



[general]



Inst_Dir = /usr/local/Hughes

mSQL_User = msql

Admin_User = root

Pid_File = %I/msql2.pid

TCP_Port = 1114

UNIX_Port = %I/msql2.sock