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