Class Mysql.SqlTable

Description

This class provides some abstractions on top of an SQL table.

At the core it is generic for any SQL database, but the current implementation is MySQL specific on some points, notably the semantics of AUTO_INCREMENT, the quoting method, knowledge about column types, and some conversion functions. Hence the location in the Mysql module.

Among other things, this class handles some convenient conversions between SQL and pike data types:

Similar to Sql.big_typed_query, SQL integer and floating point columns are converted to/from pike ints and floats, and SQL NULLs are converted to/from the Val.null object.

MySQL DECIMAL columns are converted to/from Gmp.mpq objects if they have one or more decimal places, otherwise they are converted to/from ints.
MySQL TIMESTAMP columns are converted to/from pike ints containing unix timestamps. This conversion is done on the MySQL side using the UNIX_TIMESTAMP and FROM_UNIXTIME functions, which means that the conversion is not susceptible to offsets due to time zone differences etc. There is however one special case here that MySQL doesn't handle cleanly - see note below.
Other SQL types are kept in string form. That includes DATE, TIME, and DATETIME, which are returned as MySQL formats them.

Note that Sql.mysql can handle conversions to/from Unicode strings for text data types. If that is enabled then this class also supports that conversion.
There are debug checks (with the DEBUG define) that verify the incoming pike types, to avoid bugs which could otherwise be hidden by implicit casts on the SQL side. The date and time types (except TIMESTAMP) can be sent either as strings or integers (e.g. either "2010-01-01" or 20100101).

This class can also optionally simulate an arbitrary set of fields in each table row: If a field name is the same as a column then the column is accessed, otherwise it accesses an entry in a mapping stored in a special BLOB column which is usually called "properties".

Note

Although SQL is case insensitive on column names, this class isn't.

Note

The generated SQL queries always quote table and column names according to MySQL syntax using backticks (`). However, literal backticks in names are not quoted and might therefore lead to SQL syntax errors. This might change if it becomes a problem.

Note

The handling of TIMESTAMP columns in MySQL (as of 5.1 at least) through UNIX_TIMESTAMP and FROM_UNIXTIME has one problem if the active time zone uses daylight savings time:

Apparently FROM_UNIXTIME internally formats the integer to a MySQL date/time string, which is then parsed again to set the unix timestamp in the TIMESTAMP column. The formatting and the parsing uses the same time zone, so the conversions generally cancel themselves out. However, there is one exception with the 1 hour overlap in the fall when going from summer time to normal time.

E.g. if the active time zone on the connection is Central European Time, which uses DST, then setting 1130630400 (Sun 30 Oct 2005 2:00:00 CEST) through "INSERT INTO foo SET ts = FROM_UNIXTIME(1130630400)" actually sets the ts column to 1130634000 (Sun 30 Oct 2005 2:00:00 CET).

The only way around that problem is apparently to ensure that the time zone used on the connection is one which doesn't use DST. E.g. UTC is a reasonable choice, which can be set on the connection through "SET time_zone = '+00:00'". That is not done automatically by this class.

Variable col_types: mapping(string:string) Mysql.SqlTable.col_types
Description: Maps the names of the table columns to the types SqlTable will handle them as. This is queried from the database in create. Do not change.

Variable get_db: function(void:Sql.Sql) Mysql.SqlTable.get_db
Description: Callback to get a database connection.

Variable id_col: string Mysql.SqlTable.id_col
Description: The column containing the AUTO_INCREMENT values (if any).

Variable pk_cols: array(string) Mysql.SqlTable.pk_cols
Description: The column(s) containing the primary key, in order. Typically it is the same as ({id_col}).

Variable prop_col: string Mysql.SqlTable.prop_col
Description: The column containing miscellaneous properties. May be zero if this feature is disabled. Do not change.

Variable prop_col_max_length: int Mysql.SqlTable.prop_col_max_length
Description: Maximum length of the value prop_col can hold. Only applicable if prop_col is set. Do not change.

Variable table: string Mysql.SqlTable.table
Description: The table to query or change. Do not change.

Method create: Mysql.SqlTable Mysql.SqlTable(function(void:Sql.Sql) get_db, string table, void|string prop_col)
Description: Creates an SqlTable object for accessing (primarily) a specific table.
Parameter get_db: A function that will be called to get a connection to the database containing the table.
Parameter table: The name of the table.
Parameter prop_col: The column in which all fields which don't have explicit columns are stored. It has to be a non-null blob or varbinary column. If this isn't specified and there is such a column called "properties" then it is used for this purpose. Set to "-" to force this feature to be disabled.