Author: Manuel Lemos (mlemos-at-acm.org)
Version control: @(#) $Id: metabase.documentation,v 1.73 2015/04/02 05:20:56 mlemos Exp $
Metabase is a PHP package intended to provide DBMS (DataBase Management System) independent means to install, access and maintain SQL based databases.
The development of Metabase has two main goals. One is to provide a unified programming interface to access distinct SQL based DBMS. Using the same interface, programmers may develop database applications that may run with DBMS of different vendors: Oracle, Sybase, MySQL, Microsoft SQL Server, etc..
The second goal is to provide a tool to install and maintain database schemas using the same DBMS independent database interface. Using this tool, developers may design and evolve database schemas that serve the needs of their applications without having to worry on how to install or upgrade them on each type of DBMS that is used.
Metabase is divided in several parts:
Consists of a class that is able to instantiate the driver classes. There is also a set of functions that the applications should call to execute several different types of operations needed to access to the databases.
Consists of a set of driver classes that implement functions that are called by the interface functions to implement database access aspects that are DBMS specific.
Consists of a set of driver extension classes that implement functions that are called by the main driver class to implement schema management functions.
Consists of a script that performs a set of tests to verify if the DBMS specific drivers work in conformance with the way they are expected to work, thus without any bugs that may affect the Metabase applications that use them.
Class that is able to parse database schema description defined in a custom XML based format. The class produces a data structure that has the information of the different database objects described in the schema description file.
Class that is able to take the schema description data structure generated by the parser class and then create a database with the specified DBMS using the interface functions.
This class is able to compare the data structures of two versions of the same schema description (the currently installed in the DBMS and a new one to upgrade to) and execute the necessary operations to alter the database schema without disturbing any data stored after the database was installed or its schema was upgrade for the last time.
Design, implementation and documentation of most of Metabase.
Informix driver.
Interbase driver.
EditPlus templates for editing database schema XML files.
Query result set metadata retrieval functions.
smith-at-dybnet.de
clinn-at-dybnet.de
Query result set bulk data retrieval functions.
Schema XML style sheet
Database schema reverse engineering and SQLite driver
Microsoft Access ODBC sub-driver
SQLite driver auto-increment and primary key support
All DBMS provide multiple choice of data types for the information that can be stored in their database table fields. However, the set of data types made available varies from DBMS to DBMS.
To simplify the interface with the DBMS supported by Metabase it was defined a base set of data types that applications may access independently of the underlying DBMS.
The Metabase applications programming interface takes care of mapping data types when managing database options. It is also able to convert that is sent to and received from the underlying DBMS using the respective driver.
The text data type is available with two options for the length: one that is explicitly length limited and another of undefined length that should be as large as the database allows.
The length limited option is the most recommended for efficiency reasons. The undefined length option allows very large fields but may prevent the use of indexes and may not allow sorting on fields of its type.
The fields of this type should be able to handle 8 bit characters. Drivers take care of DBMS specific escaping of characters of special meaning with the values of the strings to be converted to this type.
The boolean data type represents only two values that can be either 1 or 0. Do not assume that these data types are stored as integers because some DBMS drivers may implement this type with single character text fields for a matter of efficient. Ternary logic is possible by using NULL as the third possible value that may be assigned to fields of this type.
The integer data type may store integer values as large as each DBMS may handle. Fields of this type may be created optionally as unsigned integers but not all DBMS support it. Therefore, such option may be ignored. Truly portable applications should not rely on the availability of this option.
The decimal data type may store decimal numbers accurately with a fixed number of decimal places. This data type is suitable for representing accurate values like currency amounts.
Some DBMS drivers may emulate the decimal data type using integers. Such drivers need to know in advance how many decimal places that should be used to perform eventual scale conversion when storing and retrieving values from a database. Despite this, applications may use arithmetic expressions and functions with the values stored on decimal type fields as long as any constant values that are used in the expressions are also converted with the respective Metabase conversion functions.
The number of places that are used to the left and the right of the decimal point is pre-determined and fixed for all decimal values stored in the same database. By default, Metabase uses 2 places to the right of the decimal point, but this may be changed when setting the database connection. The number of places available to the right of the decimal point depend on the DBMS.
It is not recommended to change the number places used to represent decimal values in database after it is installed. Metabase does not keep track of changes in the number of decimal places.
The float data type may store floating point decimal numbers. This data type is suitable for representing numbers within a large scale range that do not require high accuracy. The scale and the precision limits of the values that may be stored in a database depends on the DBMS that it is used.
The date data type may represent dates with year, month and day. DBMS independent representation of dates is accomplished by using text strings formatted according to the IS0 8601 standard.
The format defined by the ISO 8601 standard for dates is YYYY-MM-DD where YYYY is the number of the year (Gregorian calendar), MM is the number of the month from 1 to 12 and DD is the number of the day from 1 to 31. Months or days numbered below 10 should be padded on the left with 0.
Some DBMS have native support for date formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between date values as well sort query results by fields of this type.
The time data type may represent the time of a given moment of the day. DBMS independent representation of the time of the day is also accomplished by using text strings formatted according to the IS0 8601 standard.
The format defined by the ISO 8601 standard for the time of the day is HH:MI:SS where HH is the number of hour the day from 0 to 23 and MI and SS are respectively the number of the minute and of the second from 0 to 59. Hours, minutes and seconds numbered below 10 should be padded on the left with 0.
Some DBMS have native support for time of the day formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between time values as well sort query results by fields of this type.
The time stamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS. The represented values obey the same rules and ranges described for the date and time data types.
The large object data types are meant to store data of undefined length that may be to large to store in text fields, like data that is usually stored in files.
Metabase supports two types of large object fields: Character Large OBjects (CLOBs) and Binary Large OBjects (BLOBs). CLOB fields are meant to store only data made of printable ASCII characters. BLOB fields are meant to store all types of data.
Large object fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS supports a feature usually known as "full text search".
The applications programming interface consists of one set of functions that call the appropriate DBMS specific functions to implement several database access functions.
The Metabase API function can be called in two different forms: calling the API global functions or calling the driver object functions directly. Both forms are always available and may be used at the same time in the programs that use Metabase.
The original form to call Metabase API functions is through a set of global functions that have their names starting with the Metabase prefix.
These functions make indirect calls to the chosen driver class object. They always take as first parameter an integer value that works as a handle to identify the driver class object that is meant to be called.
If for some reason you need to use Metabase under PHP 3 or you need write code that works under PHP 3, using these functions is your only option.
To use this form of calling Metabase API you need to use first the function MetabaseSetupDatabase described below.
Another form to call Metabase API functions is calling the driver class object functions directly. The driver class functions to call have the exact same names as the API global functions, except that they do not have the Metabase prefix nor need the database handler integer as first argument.
To use this form you need to use first the function MetabaseSetupDatabaseObject described below. This function only works under PHP 4 or better because it returns a reference to a driver class object that is stored in a private global variable.
Calling driver functions directly works slightly faster. However, you need to be careful to always pass references to the object to other functions or objects because when you pass objects by value you end with two distinct copies of the same driver object that may lead to inconsistencies in the use of the driver class functions.
If you do not want or you are not sure of how to safely pass or assign objects by reference, just call the driver object functions from your main scripts or use the API global functions instead.
$error=MetabaseSetupDatabase($arguments, &$database)
Setup a database interface instance. Applications need call this function first to obtain a database interface instance handle. This handle is needed to call all the other database interface functions.
Usually, this function does not establish a database connection. Database connections are established by the DBMS drivers whenever they are needed.
The $arguments argument is an associative array that takes pairs of tag names and values that define which DBMS driver is meant to be used and the parameters needed to setup that driver. Currently, the types of supported arguments are as follows:
The database connection setup arguments may be specified individually or all at once with the Connection argument. This argument consists of a connection string like an URL of the following format:
Type://User:Password@Host:Port/Database?
Options/option1=value1&Options/option2=value2...
You may use this argument to replace all or part of the other arguments. Values specified in this connection string prevail over values specified in the separate arguments.
The Type argument is required. If the User argument is specified, the @ character is necessary. If the Password is specified, the : character before it is required. If the Port argument is specified, the : before it is required.
The Options argument forms an option array where the indexes are the names of the database specific options specified after the respective / character. The values of the respective options follow the = character until the & character or the end of the Connection URL.
Keep in mind that argument values with characters that have special meaning in URLs like &, =, ? and % need to be properly escaped using a % followed by the hexadecimal representation of the ASCII code of the respective characters, so they become respectively: %26, %3D, %3F and %25. If you may have characters like these in an argument value, it may be simpler to encode them with the PHP function UrlEncode.
Default value: none
Example: mysql://root@localhost/driver_test?
Options/Port=/var/lib/mysql/mysql.sock
Designation of the type of DBMS driver that is meant to be used. The designation of the currently built-in supported drivers is as follows:
Default value: not specified
Name of the file of the DBMS driver class to be included if the Type argument is not specified.
Default value: not specified
Name of the constant that is defined within the specified DBMS driver class file. This function will check if the specified name is defined to prevent multiple inclusions of the driver class file. This argument is ignored if the Type argument is specified.
Default value: not specified
Name of the DBMS driver class. This argument is ignored if the Type argument is specified.
Default value: not specified
Path of the directory where the driver class include file may be found. This argument is required if Metabase include files are not located in the same directory as the current script directory that is running.
Default value: ""
Access name of the user that has to login to have access to the DBMS. This argument may be ignored by some DBMS drivers.
Default value: ""
Password of the user that is has to login to have access to the DBMS. This argument may be ignored by some DBMS drivers.
Default value: ""
Address of the host on which the DBMS is running. This argument may be ignored by some DBMS drivers.
Default value: ""
Optional argument that can be used to specify a initial database name that is equivalent to use the MetabaseSetDatabase function.
Default value: none
Boolean option that indicates whether the connections to the DBMS should be persistent. A persistent connection may be reused multiple times from the same application when using the same connection context (user, password, DBMS host, etc.).
Persistent connections are important to minimize DBMS connection overhead that may be avoided, especially when running a PHP as Web server module.
Default value: 1
Boolean option that indicates whether the it should be allowed to start a transaction when another transaction is already in progress.
Default value: 1
Integer option that indicates the number of places to the right of the decimal point that should be used to represent values of the decimal data type. The default value is suitable to represent currency amounts in most applications, but in some markets it is required by law to represent currency values with 3 or more decimal places.
Default value: 2
Name of a global function that is meant to be called to output debugging information. If this option is not set or is set an empty string, no debug information is output.
Default value: ""
Associative array that takes pairs of tag names and values that define the DBMS driver specific options. Consult the respective driver documentation to learn about the available options.
Default value: empty array
Integer option that indicates the length of the buffer that is used to read data to store in large object fields.
Default value: 8000
String option that indicates the characters that will be used to break the lines of the messages that are outputted to the log stream with the MetabaseDebug function. The default value is suitable for outputting the log lines when using Unix like OS based computers. For Windows like OS based computers you may want to set this option to "\r\n".
Default value: \n
The $database argument is a reference to a variable that will hold a unique integer value that works as database access handle. This handle value should be passed as argument to the other database interface functions.
The $error return value contains an error message if this function call did not succeed. Otherwise it contains an empty string. This return value may be safely ignored if the function arguments are correctly defined.
$error=MetabaseSetupDatabase(array(
"Type"=>"oci",
"User"=>"oracle_user",
"Password"=>"oracle_user_password",
"Options"=>array(,
"SID"=>"dboracle",
"HOME"=>"/home/oracle/u01",
"DBAUser"=>"SYS",
"DBAPassword"=>"change_on_install"
),
), $database);
if($error!="")
{
echo "Error while setting database access instance: $error";
exit;
}
MetabaseSetDatabase($database,"test");
$result=MetabaseQuery($database,"SELECT id,password FROM users WHERE alias='admin'");
$error=MetabaseSetupDatabaseObject($arguments, &$db)
The purpose of this function is the same as the MetabaseSetupDatabaseObject function. The only difference is that the $database integer handle argument is replaced by the $db object argument that should be used to call the driver class object functions directly instead of calling the Metabase API global functions.
This function can not be used under PHP 3 because it takes variable references internally, which is only supported starting from PHP 4.
The $arguments argument is an associative array that with the exact same usage as when calling MetabaseSetupDatabase function.
The $db argument is a reference to a variable that will hold a driver class object value. This object value should be used to call the driver object functions directly.
The driver object functions have exactly the same name of the correspondent API global functions, except that they do not have the Metabase prefix. If you still need to call the API global functions, you may retrieve the database access integer handle by accessing the database driver object variable ($db->database).
The $error return value contains an error message if this function call did not succeed. Otherwise it contains an empty string. This return value may be safely ignored if the function arguments are correctly defined.
$error=MetabaseSetupDatabaseObject(array(
"Type"=>"oci",
"User"=>"oracle_user",
"Password"=>"oracle_user_password",
"Options"=>array(,
"SID"=>"dboracle",
"HOME"=>"/home/oracle/u01",
"DBAUser"=>"SYS",
"DBAPassword"=>"change_on_install"
),
), $db);
if($error!="")
{
echo "Error while setting database access instance: $error";
exit;
}
$db->SetDatabase("test");
$result=$db->Query("SELECT id,password FROM users WHERE alias='admin'");
$error=MetabaseParseConnectionArguments($connection, $arguments)
Parse a connection string, as the one that may be passed to the Connection argument of the function MetabaseSetupDatabase, and return array of arguments populated with the arguments that would be passed to that same function if their values were passed separately.
This function can be used to validate a connection string and extract the respective argument values if the string is correctly formatted.
The $connection argument is a URL string that defines the database connection arguments as defined for the Connection argument for the function MetabaseSetupDatabase.
The $arguments argument is a reference to an array variable that will be populated with the connection arguments. This function does not initialize this array. An already initialized array variable must be passed to the function.
The $error return value contains an error message if the connection string is not valid. Otherwise it contains an empty string.
MetabaseCloseSetup($database)
Release any resources allocated during the access of the specified database instance. Applications MUST call this function before exit or else incomplete operations may be canceled.
If there is a transaction in progress when this function is called, it is canceled by calling the MetabaseRollbackTransaction. Other DBMS specific resources may be freed depending on the associated driver.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
$previous_name=MetabaseSetDatabase($database, $name)
Set the name of the database that is meant to be accessed. This function does not establish a new database connection or closes any previously opened database connection. It just defines the name of the database that is meant to be access in subsequent operations that necessarily require database access.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is meant to be accessed.
The $previous_name return value is the name of the database that was set before calling this function.
$error=MetabaseError($database)
Retrieve the error message text associated with the last operation that failed. Some functions may fail but they do not return the reason that makes them to fail. This function is meant to retrieve a textual description of the failure cause.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $error return value is the error message text associated with the last failure.
$previous_error_handler=MetabaseSetErrorHandler($database, $error_handler)
Specify a function that is called when an error occurs.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $error_handler argument is a string with the name of the function that will be called on error. If an empty string is specified, no handler function is called on error.
The error handler function receives two arguments. The first argument a reference to the driver class object that triggered the error. The second argument is a reference to an associative array that provides details about the error that occurred. These details provide more information than it is returned by the MetabaseError function.
Here follows the list of currently supported error detail entries:
String that indicates the scope of the driver object class within which the error occurred.
Error message as is returned by the MetabaseError function.
The $previous_error_handler return value is name of the function that was previously defined as error handler.
$supported=MetabaseSupport($database, $feature)
Determine if the DBMS driver associated with the given database access handle supports the specified feature.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $feature argument is the name of the feature that is intended to be queried for support. Developers should consult the DMBS driver specific documentation to determine which support the features they need to develop their applications.
The name of the features that currently may be queried is as follows:
Ability to determine the number of table rows that were affected by the last INSERT, UPDATE or DELETE query.
Ability to create and use tables auto-incremented key fields. Support for this feature implies supporting primary keys.
Ability to create indexes on table fields.
Ability to specify sorting direction on the creation of table indexes.
Ability to retrieve the current value of a sequence. Support for this feature implies supporting sequences.
Has Large OBject table fields to store character or binary data.
Ability to execute INSERT queries omiting the auto-increment key field.
Ability to order the result of SELECT queries by text fields of unspecified length.
Ability to build patterns to match text expressions that may include wildcard characters that should be matched literally.
Ability to create tables primary key fields.
Ability to execute REPLACE queries using the function MetabaseReplace even if it is done by emulation.
Ability to restrict the range of result rows that are returned by the DBMS when executing a SELECT query.
Ability to create and retrieve sequential values.
Ability to use summary function on queries such as COUNT, MAX, MIN, AVG, etc..
Ability to start atomic multiple query transactions.
$result=MetabaseQuery($database, $query)
Execute an arbitrary database query statement.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the query statement to be executed. The values used in the statement should be already in a DBMS specific representation format.
Use the data type conversion functions to convert DBMS independent data values into DBMS specific values to use within this function's query argument. Alternatively, you may want to use prepared queries to avoid having to compose statements with DBMS specific values.
The $result return value determines if this function succeeded. A value of 0 indicates that the query failed. For queries that return selected information, the $result return value is a handle that should be used access to the query result information. In this case, the MetabaseFreeResult function should be called when the result information is no longer needed.
$success=MetabaseReplace($database, $table, $fields)
Execute a SQL REPLACE query. A REPLACE query is identical to a INSERT query, except that if there is already a row in the table with the same key field values, the REPLACE query just updates its values instead of inserting a new row.
The REPLACE type of query does not make part of the SQL standards. Since practically only MySQL implements it natively, this type of query is emulated through this Metabase function for other DBMS using standard types of queries inside a transaction to assure the atomicity of the operation.
Use the MetabaseSupport function to figure if the current driver class object implements the REPLACE query even if it is emulated.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the REPLACE query will be executed.
The $fields argument is an associative array that describes the fields and the values that will be inserted or updated in the specified table. The indexes of the array are the names of all the fields of the table. The values of the array are also associative arrays that describe the values and other properties of the table fields.
Here follows a list of field properties that need to be specified:
Value to be assigned to the specified field. This value may be of specified in database independent type format as this function can perform the necessary datatype conversions.
Default: this property is required unless the Null property is set to 1.
Name of the type of the field. Currently, all types Metabase are supported except for clob and blob.
Default: text
Boolean property that indicates that the value for this field should be set to NULL.
The default value for fields missing in INSERT queries may be specified the definition of a table. Often, the default value is already NULL, but since the REPLACE may be emulated using an UPDATE query, make sure that all fields of the table are listed in this function argument array.
Default: 0
Boolean property that indicates that this field should be handled as a primary key or at least as part of the compound unique index of the table that will determine the row that will updated if it exists or inserted a new row otherwise.
This function will fail if no key field is specified or if the value of a key field is set to NULL because fields that are part of unique index they may not be NULL.
Default: 0
The $success return value determines if this function succeeded. A value of 0 indicates that the query failed.
Metabase defines a set of base data types that are supported somehow in all DBMS for which there is a driver. However, not all data types are directly supported by each DBMS.
The DBMS drivers are responsible for converting the values of the base datatypes to a representation format that is suitable to emulate their behavior without loss of the values that each type is intended to represent.
$converted=MetabaseGetTextFieldValue($database, $value)
Convert a text value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that is intended to be converted.
The $converted_value return value is a text string that already contains any DBMS specific escaped character sequences.
$converted_value=MetabaseGetBooleanFieldValue($database, $value)
Convert a boolean value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the boolean value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetDecimalFieldValue($database, $value)
Convert a decimal value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the decimal value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetFloatFieldValue($database, $value)
Convert a float value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the float value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetDateFieldValue($database, $value)
Convert a date value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the date value that is intended to be converted. The value should be represent in the ISO 8601 format "YYYY-MM-DD".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetTimeFieldValue($database, $value)
Convert a time value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the time value that is intended to be converted. The value should be represent in the ISO 8601 format "HH-MI-SS".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetTimestampFieldValue($database, $value)
Convert a time stamp value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the time stamp value that is intended to be converted. The value should be represent in the ISO 8601 format "YYYY-MM-DD HH:MM:SS".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$timestamp=MetabaseNow()
Generate a text string that represents the actual date and time on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take timestamp values.
The $timestamp return value is a text string that represents the current date and time.
$date=MetabaseToday()
Generate a text string that represents the actual date on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take date values.
The $date return value is a text string that represents the current date.
$time=MetabaseTime()
Generate a text string that represents the current time on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take date values.
The $time return value is a text string that represents the current time.
All DBMS provide means to search for patterns in text fields using the LIKE operator. It can be used to match a text field value with a pattern that may include wildcard characters.
Patterns may use the character wildcard character % to match 0 or more occurrences of any character. The wildcard character _ may be used to match a single occurence of any character.
The characters % and _ need to be escaped in a special way to be matched literally. Some DBMS also consider other wildcard characters that need to be escaped so they can be used be matched literally.
Metabase provides several functions to build patterns and escaping character literals that otherwise would be handled as wildcards.
$match=MetabaseBeginsWith($database, $value)
Build a pattern that matches the any text expression that starts with the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match the beginning of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseEndsWith($database, $value)
Build a pattern that matches the any text expression that ends with the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match the end of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseContains($database, $value)
Build a pattern that matches the any text expression that contains the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match any part of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseMatchPattern($database, $pattern)
Build a custom pattern that matches a text expression according to the given pattern definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $pattern argument is an array of string values that defines the pattern to match.
The array position 0 and any other values even positions represent strings that should be matched literally. The array position 1 and any other values in odd positions must be strings with the wildcard characters % and _.
For instance, to match text expressions that start with the literal string begin, the pattern array should be defined as: array( 'begin', '%'). To match text expressions that end with the literal string end, the pattern array should be defined as: array( '', '%', 'end'). To match text expressions that contain the literal string contains, the pattern array should be defined as: array( '', '%', 'contains', '%').
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
Metabase supports the insertion of table rows with special key fields, known as auto-increment fields. These table fields store automatically generated sequential values.
Although not all DBMS natively support table fields with auto-incremented keys, Metabase provides a DBMS independent set of functions to insert and retrieve this kind of key values.
With certain DBMS that do not support native auto-incremented key fields, the respective Metabase DBMS driver may provide a suitable emulation that makes it work the same way with all DBMS.
If a DBMS driver does not support auto-incremented key fields, not even through emulation, the alternative is to use database sequence objects, if possible. Use the MetabaseSupport function to determine whether a driver supports either auto-incremented key fields or database sequence objects.
Note that to implement auto-incremented table keys with DBMS that do not support them natively, it is necessary that the table is installed by the function MetabaseCreateDetailedTable or by Metabase database schema manager class.
$success=MetabaseGetNextKey($database, $table, $key_sql)
Retrieve an SQL expression to be used in the place of the value of an auto-incremented key field in SQL INSERT queries to insert new rows in a given table.
If the current DBMS supports omitting the auto-increment key field, it is recommended that it is omitted from the INSERT query. In that case the function MetabaseGetNextKey is not necessary.
Use the MetabaseSupport function to determine whether the current driver class supports omitting the auto-increment key field in INSERT queries.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table into which a new row will be inserted.
The $key_sql argument is a reference to a string variable into which it is returned the SQL expression to be used in the place of the value of an auto-incremented key field in the SQL INSERT query.
The $success return value determines if this function succeeded. A value of 0 indicates that it was not possible to retrieve the key SQL expression.
$success=MetabaseGetNextKey($database, "users", $key);
if($success)
{
$result=MetabaseQuery($database, "INSERT INTO users (id, alias, password) VALUES ( ".$key.", 'some user', 'some password')");
}
$success=MetabaseGetInsertedKey($database, $table, $key_value)
Retrieve the value of an auto-incremented key of a row just inserted in a given database table.
This function must be called right after calling MetabaseQuery or MetabaseExecuteQuery that executed the SQL INSERT query to insert a row in the table with an auto-incremented key field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table into which the new row was inserted.
The $key_value is a reference to an integer variable into which it is returned the value of the auto-incremented key value of the row inserted in the specified table.
The $success return value determines if this function succeeded. A value of 0 indicates that it was not possible to retrieve the key value.
Prepared queries are queries that have passed through a pre-parsing process. They may take less time to run when they are intended to run if they are intended to be run multiple times from the same application.
Prepared queries may have marks that identify the position in the statement where parameter values will be inserted. Insertion positions are marked by the character ?. Every time a prepared query is executed the insertion marks are substituted by the values defined for the respective parameter. Parameter values may be changed before each time the query is executed.
Not all DBMS support prepared queries. For those DBMS that do not support them, prepared queries are emulated by substituting the insertion marks in the query with the parameter values converted to the DBMS specific format using the respective data type conversion functions.
$prepared_query=MetabasePrepareQuery($database, $query)
Create a prepared query. The specified query is parsed and the resulting data is stored for subsequent execution.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the query statement to be prepared. ? character should be used to mark the positions of query parameters in the statement.
The $prepared_query return value is a handle that should be passed as argument to the prepared query parameterization and execution functions. If this function fails the return value is set to 0.
$success=MetabaseFreePreparedQuery($database, $prepared_query)
Release resources allocated for the specified prepared query.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $success return value indicates whether the function was able to successfully free the resources allocated for the given $prepared_query. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query handle that was not yet freed.
$result=MetabaseExecuteQuery($database, $prepared_query)
Execute a prepared query statement.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $result return value determines if this function succeeded. A value of 0 indicates that there are some undefined parameter values or that the query failed.
For queries that return selected information, the $result return value is a handle that should be used access to the query result information. In this case, the MetabaseFreeResult function should be called when the result information is no longer needed.
$success=MetabaseQuerySet($database, $prepared_query, $parameter, $type, $value, $is_null, $field)
Set the value of a parameter of a prepared query. Note that this function does not perform any value escaping or data type conversions, like would be done when calling MetabaseQuerySet<type> like functions.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $type argument is designation of the type of the parameter to be set. The designation of the currently supported types is as follows:
The $value argument is the value that is meant to be assigned to specified parameter. The type of the value depends on the $type argument.
The $is_null argument is a boolean flag that indicates whether whether the parameter is a NULL.
The $field argument is the name of the field that is meant to be assigned with this parameter value when it is of type clob or blob.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid value of the specified type.
$success=MetabaseQuerySetNull($database, $prepared_query, $parameter, $type)
Set the value of a parameter of a prepared query to NULL.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $type argument is designation of the type of the parameter to be set. The designation of the currently supported types is list in the usage of the function MetabasePrepareQuery.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetText($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a text value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid text value.
$success=MetabaseQuerySetBoolean($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a boolean value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a boolean value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid boolean value.
$success=MetabaseQuerySetInteger($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with an integer value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is an integer value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid integer value.
$success=MetabaseQuerySetDecimal($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with an decimal value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is an decimal value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid decimal value.
$success=MetabaseQuerySetFloat($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a float value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a float value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid float value.
$success=MetabaseQuerySetDate($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a date value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a date value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid date value.
$success=MetabaseQuerySetTime($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a date value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a time value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid time value.
$success=MetabaseQuerySetTimestamp($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a time stamp value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a time stamp value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid time stamp value.
$success=MetabaseQuerySetCLOB($database, $prepared_query, $parameter, $value, $field)
Set a parameter of a prepared query with a character large object value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a handle of large object created with MetabaseCreateLOB function from which it will be read the data value that is meant to be assigned to specified parameter.
The $field argument is the name of the field of a INSERT or UPDATE query to which it will be assigned the value to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetBLOB($database, $prepared_query, $parameter, $value, $field)
Set a parameter of a prepared query with a binary large object value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a handle of large object created with MetabaseCreateLOB function from which it will be read the data value that is meant to be assigned to specified parameter.
The $field argument is the name of the field of a INSERT or UPDATE query to which it will be assigned the value to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetKey($database, $prepared_query, $parameter, $table)
Set a parameter of an INSERT prepared query to be bound to the auto-incremented key field of a given table. When the prepared query is executed, Metabase will assign the specified query parameter to the given table auto-incremented key SQL expression as it is returned by the MetabaseGetNextKey function.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $table argument is the name of the table with the auto-incremented key field to be bound to the specified query parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $table argument corresponds to a valid table with an auto-incremented key field.
$prepared_query=MetabasePrepareQuery($database, "INSERT INTO articles (id, title, body) VALUES(?, ?, ?)");
if($prepared_query)
{
MetabaseQuerySetKey($database, $prepared_query, 1, "articles");
MetabaseQuerySetText($database, $prepared_query, 2, "Some title");
MetabaseQuerySetText($database, $prepared_query, 3, "Some body text");
if(MetabaseExecuteQuery($database, $prepared_query)
&& MetabaseGetInsertedKey($database, "articles", $article_id))
echo "The article was successfully inserted with identifier", $article_id, "\n";
else
echo "An error occurred: ", MetabaseError($database), "\n";
}
$success=MetabaseSetSelectedRowRange($database, $first, $limit)
Restrict the range of result rows that are returned by the DBMS when executing a SELECT query.
The most obvious use for this function is to display query results split into pages with a fixed number of result rows per page to be given by the $limit argument. The $first argument would be set to the number of first the record to displayed in each page. Its value would always be a multiple of the value set to the $limit argument starting from 0 for the first page.
This function may also be used to browse individual database records in pages displaying each record with controls to go forward or backwards. In this case the $limit argument would be set to 1 and the $first argument value would be increased or decreased depending on whether it would be browsing forward or backwards respectively.
This function should be called right before executing a query using the MetabaseQuery function or creating a prepared query with the MetabasePrepareQuery function.
After calling these functions the select range values will be reset as if they were never set before. So, to activate select range restriction, this function has to be called every time before executing a query or creating a prepared query.
The select range may be changed for prepared queries by calling this function before the queries are actually executed with MetabaseExecuteQuery. The range values are maintained between subsequent executions of each prepared query with the values that were defined initially or before the last time the prepared query was executed.
The number of rows returned by a query restricted by a range defined by this function is always smaller or equal to the value of the $limit argument. The result row numbers start from 0 but the returned rows correspond to those that would appear in the unrestricted result set displaced by the number specified by the $first argument.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $first argument is number of the first row to be returned when the next SELECT query is executed. Row numbers start from 0. If the total number of rows that result from the query is less or equal to the range first row number, the result set will be empty. The $first argument should be equal or greater than 0 or else this function fails.
The $limit argument is the maximum number of rows to be returned when the next SELECT query is executed. The result set may return a smaller number of rows if there are not as many rows in the query result set, starting from the row specified by the $first argument. The $limit argument should be equal or greater than 1 or else this function fails.
The $success return value indicates whether the function was able to successfully set the range of rows to be returned by the next SELECT query. This return value may be safely ignored if the respective DBMS driver supports selected range rows and $first and $limit arguments specify valid values.
$success=MetabaseAffectedRows($database, &$affected_rows)
Determine the number of table rows that were affected by the last INSERT, UPDATE or DELETE query. Not all DBMS can to determine the number of affected rows by a query. Use the function MetabaseSupport to determine whether the DBMS driver has this ability.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $affected_rows argument is a reference to a variable that will hold the number of affected rows.
The $success return value indicates whether the function was able to successfully determine the number of affected rows. This function will always fail if the DBMS driver does not can determine the number of affected rows or the last query was not supposed to affect database table rows.
$is_null=MetabaseResultIsNull($database, $result, $row, $field)
Determine whether the value of a query result located in given row and field is a NULL.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to check the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to check the result value. Column numbers start from 0.
The $is_null result value is a boolean flag that indicates whether the result value in the given position is a NULL. Applications should not fetch the values of NULL result positions.
$value=MetabaseFetchResult($database, $result, $row, $field)
Fetch the value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
Referencing the result columns by name makes the application code look cleaner. Specifying the column by number runs faster. If you are sure about the order of the columns in the result set, you may want to pass column numbers. One possible compromise is to variable names for readability like this:
$name=0;
$email=1;
$name=MetabaseFetchResult($database, $result,0, $name);
$email=MetabaseFetchResult($database, $result,0, $email);
However, do not use column numbers when you run queries like SELECT * FROM some_table because the order that the columns may come is not guaranteed to be always the same, specially after making table alterations.
The $value result value is a text string that with the value as it was returned by the DBMS. Text or integer field result may be used as they are returned, but fields of other types need to be retrieved using data type specific result fetching functions.
$value=MetabaseFetchBooleanResult($database, $result, $row, $field)
Fetch a boolean value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string with the fetched boolean value.
$value=MetabaseFetchDecimalResult($database, $result, $row, $field)
Fetch a decimal value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string that with the fetched decimal value.
$value=MetabaseFetchFloatResult($database, $result, $row, $field)
Fetch a float value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string with the fetched float.
$value=MetabaseFetchDateResult($database, $result, $row, $field)
Fetch a date value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result date value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result date value. Column numbers start from 0.
The $value result value is a text string with the date value converted to the normalized ISO format.
$value=MetabaseFetchTimeResult($database, $result, $row, $field)
Fetch a time value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result date value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result date value. Column numbers start from 0.
The $value result value is a text string with the time value converted to the normalized ISO format.
$value=MetabaseFetchTimestampResult($database, $result, $row, $field)
Fetch a time stamp value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result time stamp value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result time stamp value. Column numbers start from 0.
The $value result value is a text string with the time stamp value converted to the normalized ISO format.
$lob_handle=MetabaseFetchCLOBResult($database, $result, $row, $field)
Fetch the value of a handle to read character large object of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
The $lob_handle return value is a integer handle that has to be used when retrieving the contents of the specified large object field using the function MetabaseReadLOB. Each application is responsible for freeing the resources associated with the retrieved large object by calling the function MetabaseFreeLOB. If this return value is 0 it means that there was an error.
$lob_handle=MetabaseFetchBLOBResult($database, $result, $row, $field)
Fetch the value of a handle to read binary large object of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
The $lob_handle return value is a integer handle that has to be used when retrieving the contents of the specified large object field using the function MetabaseReadLOB. Each application is responsible for freeing the resources associated with the retrieved large object by calling the function MetabaseFreeLOB. If this return value is 0 it means that there was an error.
$end_of_result=MetabaseEndOfResult($database, $result)
Determine whether the highest numbered row that was fetched with MetabaseFetchResult like functions is the last row available in the given result set. If no rows have been fetched yet, this function determines whether the result set contains any rows.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $end_of_result return value indicates whether there are any rows after the highest numbered row that was fetched from the given result set. If the return value is 0 it means that there is at least one row to be fetched. If the return value is 1 it means that there no more rows to be fetched. If the return value is -1 it means that there was an error.
$rows=MetabaseNumberOfRows($database, $result)
Count the number of rows returned by the DBMS in a query result. If the query was executed with the range of result rows restricted by the function MetabaseSetSelectedRowRange, the value returned by the MetabaseNumberOfRows function never exceeds the range $limit value. The number of rows may be 0 if the $first row range value is higher than the the number of rows that the query would return when it is not restricted to a given range.
For performance reasons, avoid using this function with queries that return many rows. Some DBMS do not provide a means to obtain the total number of rows contained in a result set. The reason for this is that such DBMS start returning result data as soon as it is found on the database. So, the total number of rows can not be anticipated when the DBMS starts returning result data.
The drivers for these DBMS have to count the number of rows by fetching the whole result data into memory. Since the applications may still need to fetch the result rows, the data retrieved from the DBMS still has to be hold in memory until the result is freed. Therefore, using this function may also demand a great amount of memory.
Alternatively, if you really need to know in advance the number of rows that a query will return, you should first run a query that use the SQL COUNT() function.
If you just need to know whether all rows in the given result set have been fetched, use the MetabaseEndOfResult instead.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
$columns=MetabaseNumberOfColumns($database, $result)
Count the number of columns returned by the DBMS in a query result.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $columns return value indicates the number of columns returned in the specified result set. Some DBMS may not return any columns when the result set does not contain any rows. If it is specified an invalid result set, the function will return -1.
$success=MetabaseGetColumnNames($database, $result, $column_names)
Retrieve the names of of columns returned by the DBMS in a query result.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $column_names argument is a reference to an associative array variable that will hold the names of columns. The indexes of the array are the column names mapped to lower case and the values are the respective numbers of the columns starting from 0. Some DBMS may not return any columns when the result set does not contain any rows.
The $success return value indicates whether the function was able to successfully retrieve the column names associated to the given query result handle. This return value may be safely ignored if the $result argument corresponds to a valid result handle.
$success=MetabaseFreeResult($database, $result)
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $success return value indicates whether the function was able to successfully free the resources associated to the given query result handle. This return value may be safely ignored if the $result argument corresponds to a valid result handle that was not already freed.
The data of large objects table fields should not be stored or retrieved all at once because that may require a large amount of memory. The alternative is to retrieve or store data splitting it in blocks of limited length.
Metabase provides a set of special purpose classes that are meant to manage the access to blocks of data to be stored or retrieved in large object fields. The following functions should be used to create and access objects of such classes.
Storing data in large object fields should be done by executing INSERT or UPDATE prepared queries. The functions MetabaseSetQueryCLOB and MetabaseSetQueryBLOB should be used to specify the large object input stream objects from which the data to be stored will be read when the prepared queries are executed. The function MetabaseCreateLOB should be used to create input stream objects that define how to read the data to be stored in large object fields.
Data already stored in large object fields should be retrieved by executing SELECT queries. The functions MetabaseFetchCLOBResult and MetabaseFetchBLOBResult should be used to retrieve the data of the selected large object fields. These functions return large object handle values that should be used to subsequently read the data using the function MetabaseReadLOB.
$success=MetabaseCreateLOB(&$arguments, &$lob)
Create a handler object of a specified class with functions to retrieve data from a large object data stream.
The $arguments argument is reference to an associative array with parameters to create the handler object. The array indexes are the names of the parameters and the array values are the respective parameter values.
Some parameters are specific of the class of each type of handler object that is created. The following parameters are common to all handler object classes:
Name of the type of the built-in supported class that will be used to create the handler object. There are currently four built-in types of handler object classes: data, resultlob, inputfile and outputfile.
Name of the class of the handler object that will be created if the Type argument is not specified. This argument should be used when you need to specify a custom handler class.
Database connection handler as returned by the MetabaseSetupDatabase. This is an option argument needed by some handler classes like resultlob.
Optional error entry that will hold the error message returned by this function when it fails.
The following argument is specific of the data handler class:
String of data that will be returned by the class when it requested with the MetabaseReadLOB function.
The following argument is specific of the resultlob handler class:
Integer handle value of a large object result row field.
The following arguments are specific of the inputfile handler class:
Integer handle value of a file already opened for reading.
Name of a file to be opened for reading if the File argument is not specified.
The following arguments are specific of the outputfile handler class:
Integer handle value of a file already opened for writing.
Name of a file to be opened for writing if the File argument is not specified.
Integer value that specifies the length of a buffer that will be used to read from the specified large object.
Integer handle value that specifies a large object from which the data to be stored in the output file will be written.
Integer handle value as returned by the function MetabaseQuery or MetabaseExecuteQuery that specifies the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Integer value that specifies the number of the row of the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Integer or string value that specifies the number or the name of the column of the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Boolean value that specifies whether the large object column to be retrieved is of binary type (blob) or otherwise is of character type (clob). If the LOB argument is specified, this argument is ignored.
The $lob argument is a reference to a variable that will hold an integer handle value that should be passed as argument in subsequent calls to functions that retrieve data from the large object input stream.
The $success return value indicates whether the function was able to successfully create the large object handler object.
$read_length=MetabaseReadLOB($lob, &$data, $length)
Read data from large object input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $data argument is a reference to a variable that will hold data to be read from the large object input stream.
The $length argument is a integer value that indicates the largest amount of data to be read from the large object input stream.
The $read_length return value indicates the effective number of bytes read from the large object input stream.
If this function succeeded, the return value may range from 0 up to the value of the $length argument. Use the function MetabaseEndOfLOB to determine if there is still more data to be retrieved.
If this function fails, the return value is -1. Use the function MetabaseLOBError to retrieve the associated error message.
$end_of_lob=MetabaseEndLOB($lob)
Determine whether it was reached the end of the large object and therefore there is no more data to be read for the its input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $end_of_lob return value is a boolean flag that indicates whether it was reached the end of the large object input stream.
MetabaseDestroyLOB($lob)
Free any resources allocated during the lifetime of the large object handler object.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
$error=MetabaseLOBError($lob)
Retrieve the error message text associated with the last operation on the large object input stream that failed.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $error return value is the error message string.
$end_of_lob=MetabaseEndLOB($lob)
Determine whether it was reached the end of the large object and therefore there is no more data to be read for the its input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $end_of_lob return value is a boolean flag that indicates whether it was reached the end of the large object input stream.
MetabaseDestroyLOB($lob)
Free any resources allocated during the lifetime of the large object handler object.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The most common use of database applications is to retrieve information that is stored table rows. The process to retrieve data from a database is repetitive: execute a query, iterate over the result set and free it.
To simplify this repetitive process, Metabase provides an additional set of functions that is meant to reduce the number of calls to the API when retrieving result set data in the most common ways.
There are two main types of functions that are provided for retrieving data in bulk with a single call. The simplest type of functions just retrieves the data into a given variable from the result set of an already executed query and then frees the result set.
The second type of functions wraps around the first functions and besides that also executes a given immediate query before retrieving the result set data.
The first type of functions is more appropriate to use with prepared queries because the second type of functions performs the same actions and also execute immediate queries.
These types of functions retrieve data in four different manners:
Besides these main types of functions there is also a function that fetches data from a given row of a result set and another function that is meant to define the data types that should be associated to each result set column, so that the necessary datatype conversions are already performed before returning data.
$success=MetabaseSetResultTypes($database, $result, &$types)
Define the list of types to be associated with the columns of a given result set.
This function may be called before invoking MetabaseFetchResultArray, MetabaseFetchResultField, MetabaseFetchResultRow, MetabaseFetchResultColumn, MetabaseFetchResultAll and MetabaseFetchResultAllIndexed so that the necessary data type conversions are performed on the data to be retrieved by them. If this function is not called, the type of all result set columns is assumed to be text, thus leading to not perform any conversions.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $types argument is an array variable that lists the data types to be expected in the result set columns. If the array is associative, the names used in the keys will be used as indexes of the result arrays returned by the functions MetabaseFetchResultRow, MetabaseFetchResultAll and MetabaseFetchResultAllIndexed. If this array contains less types than the number of columns that are returned in the result set, the remaining columns are assumed to be of the type text. Currently, the types clob and blob are not fully supported.
The $success return value indicates whether the function call succeeded in associating the types list to the given result set. This return value may be safely ignored if the function arguments are correctly defined.
$success=MetabaseFetchResultArray($database, $result, &$array, $row)
Fetch a specified result set row into a given array variable.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $array argument is a reference to an array variable into which the specified result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
The $row argument is the number of the row from which it is intended to fetch the result set row. Row numbers start from 0.
The $success return value indicates whether the function call succeeded in retrieving the given row result set data.
$success=MetabaseFetchResultField($database, $result, &$field)
Fetch the value from the first column of the first row of the specified result set into a given variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the field value fails for any reason. This function assumes that no data was already retrieved from the result set or else it may fail stating the result set is empty.
The $field argument is a reference to a variable into which the specified result set field is fetched. If it is NULL this variable is unset.
The $success return value indicates whether the function call succeeded in retrieving the result set field value.
$success=MetabaseFetchResultRow($database, $result, &$row)
Fetch the first row of the specified result set row into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the row values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may fail stating the result set is empty.
The types set with the function MetabaseSetResultTypes affect the results returned by this function. If the types array is associative, the results row array is also associative using the same indexes from the types array.
The $row argument is a reference to an array variable into which the specified result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
The $success return value indicates whether the function call succeeded in retrieving the result set row data.
$success=MetabaseFetchResultColumn($database, $result, &$column)
Fetch the value from the first column of all the rows of the specified result set into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the column values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may not return any data.
The $column argument is a reference to an array variable into which the specified result set column is fetched. The rows on which the first column is NULL are ignored. Therefore, do not rely on the count of entries of the array variable to assume that it is the number of rows in the result set.
The $success return value indicates whether the function call succeeded in retrieving the result set column data.
$success=MetabaseFetchResultAll($database, $result, &$all)
Fetch all rows of the specified result set row into a given two dimension array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the its values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may not return any data.
The $all argument is a reference to a two dimension array variable into which the specified result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
The types set with the function MetabaseSetResultTypes affect the results returned by this function. If the types array is associative, the results row array is also associative using the same indexes from the types array.
The $success return value indicates whether the function call succeeded in retrieving the result set row data.
$success=MetabaseFetchResultAllIndexed($database, $result, $index, &$all)
Fetch all rows of the specified result set row into a given two dimension array variable and then frees the result set. The indexes of each row array entry are the values of the respective row column with the index given by the $index parameter.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the its values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may not return any data.
The $index argument determines the value that will be used as index of the array of result rows.
The $all argument is a reference to a two dimension array variable into which the specified result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
The types set with the function MetabaseSetResultTypes affect the results returned by this function. If the types array is associative, the results row array is also associative using the same indexes from the types array.
The $success return value indicates whether the function call succeeded in retrieving the result set row data.
$success=MetabaseQueryField($database, $query , &$field, $type)
Execute the specified query, fetch the value from the first column of the first row of the result set into a given variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $field argument is a reference to a variable into which the result set field is fetched. If it is NULL this variable is unset.
$type is an optional argument that specifies the expected datatype of the result set field, so that an eventual conversion may be performed. The default datatype is text, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set field value.
$success=MetabaseQueryRow($database, $query, &$row, $types)
Execute the specified query, fetch the first row of the result set row into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $row argument is a reference to an array variable into which the result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
$types is an optional array argument that specifies a list of expected datatypes of the result set columns, so that the eventual conversions may be performed. The default list of datatypes is empty, meaning that no conversion is performed.
If the types array is associative, the results row array is also associative using the same indexes from the types array.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set row data.
$success=MetabaseQueryColumn($database, $query, &$column, $type)
Execute the specified query, fetch the value from the first column of all the rows of the result set into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $column argument is a reference to an array variable into which the result set column is fetched. The rows on which the first column is NULL are ignored. Therefore, do not rely on the count of entries of the array variable to assume that it is the number of rows in the result set.
$type is an optional argument that specifies the expected datatype of the result set column, so that an eventual conversion may be performed. The default datatype is text, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set column data.
$success=MetabaseQueryAll($database, $query, &$all, $types)
Execute the specified query, fetch all rows of the result set row into a given two dimension array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $all argument is a reference to a two dimension array variable into which the result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
$types is an optional array argument that specifies a list of expected datatypes of the result set columns, so that the eventual conversions may be performed. The default list of datatypes is empty, meaning that no conversion is performed.
If the types array is associative, the results array for each row is also associative using the same indexes from the types array.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set data.
$success=MetabaseQueryAllIndexed($database, $query, $index, &$all, $types)
Execute the specified query, fetch all rows of the result set row into a given two dimension array variable and then frees the result set. The indexes of each row array entry are the values of the respective row column with the index given by the $index parameter.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $index argument determines the value that will be used as index of the array of result rows.
The $all argument is a reference to a two dimension array variable into which the result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
$types is an optional array argument that specifies a list of expected datatypes of the result set columns, so that the eventual conversions may be performed. The default list of datatypes is empty, meaning that no conversion is performed.
If the types array is associative, the results array for each row is also associative using the same indexes from the types array.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set data.
A transaction is a set of operations that are executed atomically by the DBMS. The operations that are executed during a transaction by a database user are not affected by other operations being executed at the same time during the access of other users.
In practice this means that the DBMS behaves as if the database is being accessed by one user at a time while a transaction takes place. The DBMS may set implicit locks that prevent simultaneous accesses to affects the same data.
A transaction is ended by a commit command or by a rollback command. The commit command tells the DBMS to make definite the changes done on the database. The rollback command tells the DBMS to cancel the changes done on the database since the transaction begun.
If for some reason a transaction is not completed before a database connection is terminated either ending the application normally or abnormally, the DBMS execute an implicit rollback operation. This includes situations when the application is terminated by the operating system or the computer where the application or the DBMS is running is shutdown by a power interruption.
An application script may be terminated without ending a transaction. The function MetabaseCloseSetup ends an uncommitted transaction that may be in progress when it is called, but if a script exits without calling this function, for instance due to a syntax error, the transaction could be left pending until the application process ends.
Since Metabase supports persistent database connections, there would be a chance that an uncommitted transaction be left pending until the Web server process is terminated, therefore holding implicit database access locks.
To prevent this situation, Metabase implicitly registers a shutdown handler function with PHP that assures that any pending transactions are canceled before the script exits.
By default, Metabase allows to start nested transactions, i.e. a transaction started while another is already in progress. In reality, when a second transaction is started, Metabase just continues the current transaction.
A nested transaction is only finished when the first transaction is ended either by committing or rolling back the transaction. If an inner transaction is rolled back, the first transaction must be also rolled back. Otherwise, Metabase will fail in error, as this situation is a result of an application inconsistency bug.
Nested transactions may be disallowed by using the database setup option AllowNestedTransactions.
Not all DBMS support transactions. Use the function MetabaseSupport to determine whether the DBMS driver support transactions.
$success=MetabaseAutoCommitTransactions($database, $auto_commit)
Define whether database changes done on the database be automatically committed. This function may also implicitly start or end a transaction.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $auto_commit argument is a boolean flag that indicates whether the database changes should be committed right after executing every query statement. If this argument is 0 a transaction implicitly started. Otherwise, if a transaction is in progress it is ended by committing any database changes that were pending.
The $success return value indicates whether the function call succeeded in either starting a transaction or committing any changes and ending a transaction in progress.
$success=MetabaseCommitTransaction($database)
Commit the database changes done during a transaction that is in progress. This function may only be called when auto-committing is disabled, otherwise it will fail. Therefore, a new transaction is implicitly started after committing the pending changes.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $success return value indicates whether the function call succeeded in committing the database changes and starting a new transaction.
$success=MetabaseRollbackTransaction($database)
Cancel any database changes done during a transaction that is in progress. This function may only be called when auto-committing is disabled, otherwise it will fail. Therefore, a new transaction is implicitly started after canceling the pending changes.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $success return value indicates whether the function call succeeded in canceling the database changes and starting a new transaction.
$success=MetabaseCreateDatabase($database, $name)
Create a database space within which may be created database objects like tables, indexes and sequences. The implementation of this function is highly DBMS specific and may require special permissions to run successfully. Consult the documentation or the DBMS drivers that you use to be aware of eventual configuration requirements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is intended to be created.
The $success return value indicates whether the function call succeeded in creating the new database.
$success=MetabaseDropDatabase($database, $name)
Drop an existing database space. The implementation of this function is highly DBMS specific and may require special permissions to run successfully. Consult the documentation or the DBMS drivers that you use to be aware of eventual configuration requirements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is intended to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified database.
$declaration=MetabaseGetTextFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a text type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the text field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Text value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetBooleanFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a boolean type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Boolean value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetIntegerFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an integer type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Boolean flag that indicates whether the field should be declared as unsigned integer if possible.
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetDecimalFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a decimal type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetFloatFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a float type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetDateFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a date type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Date value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetTimeFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an time type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Date value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetTimestampFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an time stamp type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Time stamp value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetCLOBFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a character large object type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the large object field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetBLOBFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a binary large object type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the large object field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$success=MetabaseCreateTable($database, $name, $fields)
Create a new database table. This function is deprecated in favour of the MetabaseCreateDetailedTable function, as this allows the creation of more complex tables, for instance with primary keys and auto-incremented key fields.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be created.
The $fields argument is an associative array that contains the definition of each field of the new table. The indexes of the array entries are the names of the fields of the table an the array entry values are associative arrays like those that are meant to be passed with the field definitions to MetabaseGetTypeFieldTypeDeclaration functions.
The $success return value indicates whether the function call succeeded in creating the new table.
$table_name="users";
$success=MetabaseCreateTable($database, "users", array(
"id"=>array(
"type"=>"integer",
"unsigned"=>1
"notnull"=>1
"default"=>0
),
"name"=>array(
"type"=>"text",
"length"=>12
),
"password"=>array(
"type"=>"text",
"length"=>12
)
));
if(!$success)
{
echo "Error while creating the database table "users": ",MetabaseError($database);
exit;
}
$success=MetabaseCreateDetailedTable($database, $table, $check)
Create a new database table that may include other details besides the fields list, such as the table primary key. This function may optionally simulate the creation of a table to determine whether the current DBMS driver class is capable of creating a table with the given definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is a reference to an associative array variable that specifies the definition of the table that is intended to be created.
Here follow the list of entries of the definition associative array:
The name argument entry is the name of the table that is intended to be created.
The FIELDS argument entry is an associative array that contains the definition of each field of the new table. This is the same as the $fields parameter of the MetabaseCreateTable function.
The PRIMARYKEY argument entry is an associative array that contains the parameters of the key. It must have an entry named FIELDS that is another associative array that lists fields that compose the table primary key, just like the FIELDS definition for the MetabaseCreateIndex function.
The $check argument is a boolean flag value that determines whether the function should simulate the creation of the table or create it for real. If this argument is 1, this function will not create the table but may fail in case the DBMS driver class would not be able to create a table with the requested definition.
The $success return value indicates whether the function call succeeded in creating the new table, or whether the function could create a table with the requested definition if the $check variable is 1.
$table=array(
"name"=>"users",
"FIELDS"=>array(
"id"=>array(
"type"=>"integer",
"autoincrement"=>1
),
"name"=>array(
"type"=>"text",
"length"=>12
),
"password"=>array(
"type"=>"text",
"length"=>12
)
),
"PRIMARYKEY"=>array(
"FIELDS"=>array(
"id"=>array("sorting"=>"ascending")
)
)
);
$success=MetabaseCreateDetailedTable($database, $table, 0);
if(!$success)
{
echo "Error while creating the database table 'users': ",MetabaseError($database),"\n"
exit;
}
$success=MetabaseDropTable($database, $name)
Drop an existing database table. This function is deprecated in favour of the MetabaseDropDetailedTable function, as this allows dropping more complex tables, for instance with primary keys and auto-incremented key fields.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified table.
$success=MetabaseDropDetailedTable($database, $table, $check)
Drop an existing database table that may include other details besides the fields list, such as the table primary key. This function may optionally simulate dropping the table to determine whether the current DBMS driver class is capable of dropping table with the given definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is reference to an associative array variable that specifies the definition of the table that is intended to be dropped, just like the $table argument of the function MetabaseCreateDetailedTable.
The $check argument is a boolean flag value that determines whether the function should simulate dropping the table or drop it for real. If this argument is 1, this function will not drop the table but may fail in case the DBMS driver class would not be able to drop a table with the given definition.
The $success return value indicates whether the function call succeeded in dropping the new table, or whether the function could drop a table with the given definition if the $check variable is 1.
$success=MetabaseAlterTable($database, $name, $changes, $check)
Perform a list of changes in a database table. This function is also able to check if the DBMS driver is able to perform the listed changes without actually making them.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be changed.
The $changes argument is an associative array that contains the details of each type of change that is intended to be performed. The types of changes that are currently supported are defined as follows:
New name for the table.
Associative array with the names of fields to be added as indexes of the array. The value of each entry of the array should be set to another associative array with the properties of the fields to be added. The properties of the fields should be the same as defined by the Metabase parser.
Additionally, there should be an entry named Declaration that is expected to contain the portion of the field declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
Associative array with the names of fields to be removed as indexes of the array. Currently the values assigned to each entry are ignored. An empty array should be used for future compatibility.
Associative array with the names of fields to be renamed as indexes of the array. The value of each entry of the array should be set to another associative array with the entry named name with the new field name and the entry named Declaration that is expected to contain the portion of the field declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
Associative array with the names of the fields to be changed as indexes of the array. Keep in mind that if it is intended to change either the name of a field and any other properties, the ChangedFields array entries should have the new names of the fields as array indexes.
The value of each entry of the array should be set to another associative array with the properties of the fields to that are meant to be changed as array entries. These entries should be assigned to the new values of the respective properties. The properties of the fields should be the same as defined by the Metabase parser.
If the default property is meant to be added, removed or changed, there should also be an entry with index ChangedDefault assigned to 1. Similarly, if the notnull constraint is to be added or removed, there should also be an entry with index ChangedNotNull assigned to 1.
Additionally, there should be an entry named Declaration that is expected to contain the portion of the field changed declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
The $check argument is a boolean flag value that indicates whether the function should just check if the DBMS driver can perform the requested table alterations if the value is 1 or actually perform them otherwise.
The $success return value indicates whether the DBMS driver is able to attempt to perform the requested changes if the $check argument is 1 or if the function call succeeded in altering the table otherwise.
$table_name="users";
$success=MetabaseAlterTable($database, $name, array(
"name"=>"userlist",
"AddedFields"=>array(
"quota"=>array(
"type"=>"integer",
"unsigned"=>1
"Declaration"=>"quota INT"
)
),
"RemovedFields"=>array(
"file_limit"=>array(),
"time_limit"=>array()
),
"ChangedFields"=>array(
"gender"=>array(
"default"=>"M",
"ChangeDefault"=>1,
"Declaration"=>"gender CHAR(1) DEFAULT 'M'"
)
),
"RenamedFields"=>array(
"sex"=>array(
"name"=>"gender",
"Declaration"=>"gender CHAR(1) DEFAULT 'M'"
)
)
), 0);
if(!$success)
{
echo "Error while altering the database table $table_name: ",MetabaseError($database);
exit;
}
Sequences are database objects that store integer numbers. A sequence value is automatically incremented when it is accessed. Sequences are useful for applications that need to have table fields on which each row has to have a unique value.
For this purpose, auto-incremented key fields are more recommended. However, not all Metabase drivers support auto-incremented fields.
Not all DBMS support database sequence objects either. However, for those DBMS that do not support sequences, if possible, some DBMS drivers emulate them by creating separate tables with fields with the AUTO_INCREMENT property. Use the function MetabaseSupport to determine whether the DBMS driver support either database sequence objects or auto-incremented key fields.
$success=MetabaseCreateSequence($database, $name, $start)
Create a database sequence object.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be created.
The $start argument is the initial integer value of the sequence to be created. Usually, this value is set to 1.
The $success return value indicates whether the function call succeeded in creating the database sequence object.
$support=MetabaseDropSequence($database, $name)
Drop an existing database sequence object.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be dropped.
The $success return value indicates whether the function call succeeded in dropping the database sequence object.
$success=MetabaseGetSequenceNextValue($database, $name, &$value)
Get the next value of a sequence and increment its current value. Both operation are execute atomically by the DBMS. If more than one application attempts to access simultaneously to a sequence, it is assured that each one will get a different value without corrupting the sequence.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be accessed.
The $value argument is a reference to an integer variable on which it will be stored the next sequence value.
The $success return value indicates whether the function call succeeded in accessing to the sequence next value.
$success=MetabaseGetSequenceCurrentValue($database, $name, &$value)
Get the current value of a sequence. The value returned by this function may not be useful if there may be multiple applications simultaneously accessing the specified sequence.
Not all DBMS drivers that support sequences are able to determine the current value of the sequences. Use the function MetabaseSupport to determine whether the DBMS driver has this ability.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be accessed.
The $value argument is a reference to an integer variable on which it will be stored the current sequence value.
The $success return value indicates whether the function call succeeded in accessing to the sequence current value.
Indexes are database objects intended make more efficient the searches for information in tables. Although it does not require any changes in the way the information is accessed, the use of indexes may drastically reduce the amount of time that is necessary to look for information specified in the query statements search clause.
An index is created by specifying a set of one or more fields of a table. The fields to be picked for the index creation should be the same that are used in the search clause of each query that is intended to be optimized.
The decision to create indexes for each table should not be made lightly. While using indexes may reduce the time that a query on a table may take, it also increases the time that takes to insert new rows on that table, as that causes all its indexes to be regenerated. Also, each new index requires more disk space and memory to be managed. Therefore, database designers have to balance these aspects before deciding which indexes are worthy to be created.
Not all DBMS support indexes, despite most do. Use the function MetabaseSupport to determine whether the DBMS driver in use can manage indexes.
$success=MetabaseCreateIndex($database, $table, $name, $definition)
Create a new table index.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the index is to be created.
The $name argument is the name of the index to be created.
The $definition argument is an associative array that defines properties of the index to be created. It must defined one property named FIELDS that is an associative with the names of the index fields as array indexes. Each entry of this array is set to another type of associative array that specifies properties of the index that are specific to each field.
Currently, only the sorting property is supported. It should be used to define the sorting direction of the index. It may be set to either ascending or descending.
Not all DBMS support index sorting direction configuration. The DBMS drivers of those that do not support it ignore this property. Use the function MetabaseSupport to determine whether the DBMS driver can manage indexes.
The index definition argument may contain also a boolean property named unique. When it is set to 1 it specifies that the combination of the values of the index fields on each row of the table should unique.
The $success return value indicates whether the function call succeeded in creating the specified index.
$table_name="users";
$success=MetabaseCreateIndex($database, $table_name, "users_index", array(
"FIELDS"=>array(
"user_name"=>array(
"sorting"=>"ascending"
),
"last_login"=>array()
)
));
if(!$success)
{
echo "Error creating a database index for table $table_name: ",MetabaseError($database);
exit;
}
$success=MetabaseDropIndex($database, $table, $name)
Drop an existing table index.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the index was created.
The $name argument is the name of the index to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified index.
Despite Metabase provides schema creation and maintenance support functions, the Metabase API functions can also access databases that were created by some other process. However, it is recommended that you install database schemas with Metabase manager to use with your database applications based on Metabase API.
If you have a database application that you want to migrate to start using Metabase, there is a set of functions that you can use to migrate your database schemas by performing reverse engineering.
Keep in mind that the current schema reverse engineering support is experimental. This means that what is provided is not a complete solution. You may need to correct manually the reverse engineered schemas generated by Metabase. Depending on some reverse engineering decisions, any data that may already exist in the legacy database may need to be converted. However, there is not yet support to migrate data from a database with a reverse engineered schema. Future versions of Metabase API will address these limitations.
Metabase API only provides functions that are needed to retrieve schema information for the reverse engineering process. The actual assembly of the information to produce a reverse engineered schema is made by the GetDefinitionFromDatabase of the database manager class.
$success=MetabaseListTables($database, $tables)
Retrieve the list of tables in the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $tables argument is a reference to an array variable in which it will be returned the list of database tables.
The $success return value indicates whether the function call succeeded in retrieving the database tables.
$success=MetabaseListTableFields($database, $table, $fields)
Retrieve the list of fields of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its fields.
The $fields argument is a reference to an array variable in which it will be returned the list of table fields.
The $success return value indicates whether the function call succeeded in retrieving the list of table fields.
$success=MetabaseGetTableFieldDefinition($database, $table, $field, $definition)
Retrieve the definition of a given table field of the database. This function performs a mapping between the field native definition and the database independent field definition used by Metabase.
Sometimes the field definition mapping may be ambiguous because the developer that created the original schema may have used some data types to emulate other data types. For instance, often it is used a text field of one character of length to emulate boolean fields.
In case of ambiguity, this function will return multiple definitions to describe all the possible field definitions mappings that may be valid. The first definition that is returned in $definition argument array is the one that seems to be more likely, so it can be used by applications that are only interested in the best guess definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the field to get its definition.
The $field argument is the name of the table field to get its definition.
The $definition argument is a reference to an array variable in which it will be returned one or more possible definitions of the table field. Each entry of the array is an associative array that contains the definition of the properties of the field. The names and values of the properties in the definition array are the same defined in the schema description data structure for table fields.
The $success return value indicates whether the function call succeeded in retrieving the definition of the table field.
$success=MetabaseListTableKeys($database, $table, $primary, $keys)
Retrieve the list of keys of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its keys.
The $primary argument is boolean flag that determines whether it should be returned the table primary key or others.
The $keys argument is a reference to an array variable in which it will be returned the list of table keys.
The $success return value indicates whether the function call succeeded in retrieving the list of table keys.
$success=MetabaseGetTableKeyDefinition($database, $table, $key, $primary, $definition)
Retrieve the definition of a given table key of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the index to get its definition.
The $key argument is the name of the table key to get its definition.
The $primary argument is boolean flag that indicates whether the specified key is the table primary key or of other type.
The $definition argument is a reference to an array variable in which it will be returned the definition of the table key. The associative array contains the definition of the properties of the key. The names and values of the properties in the definition array are the same defined in the schema description data structure for table keys.
The $success return value indicates whether the function call succeeded in retrieving the table key definition.
$success=MetabaseListTableIndexes($database, $table, $indexes)
Retrieve the list of indexes of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its indexes.
The $indexes argument is a reference to an array variable in which it will be returned the list of table indexes.
The $success return value indicates whether the function call succeeded in retrieving the list of table indexes.
$success=MetabaseGetTableIndexDefinition($database, $table, $index, $definition)
Retrieve the definition of a given table index of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the index to get its definition.
The $index argument is the name of the table index to get its definition.
The $definition argument is a reference to an array variable in which it will be returned the definition of the table index. The associative array contains the definition of the properties of the index. The names and values of the properties in the definition array are the same defined in the schema description data structure for table indexes.
The $success return value indicates whether the function call succeeded in retrieving the table index definition.
$success=MetabaseListSequences($database, $sequences)
Retrieve the list of sequences of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $sequences argument is a reference to an array variable in which it will be returned the list of sequences.
The $success return value indicates whether the function call succeeded in retrieving the list of sequences.
$success=MetabaseGetSequenceDefinition($database, $sequence, $definition)
Retrieve the definition of a given sequence of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $sequence argument is the name of the sequence to get its definition.
The $definition argument is a reference to an array variable in which it will be returned the definition of the sequence. The associative array contains the definition of the properties of the sequence. The names and values of the properties in the definition array are the same defined in the schema description data structure for sequences.
The $success return value indicates whether the function call succeeded in retrieving the sequence definition.
MetabaseCaptureDebugOutput($database, $capture)
Set the debugging function to an internal function that captures the output generated by calls to MetabaseDebug functions. The captured debug output information maybe retrieved with the function MetabaseDebugOutput.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $capture argument is a boolean flag that indicates whether the debug output capture is meant to be enabled or disabled. If this argument is set to 0, the debug output handling function is reset and debugging calls are ignored.
$debug_output=MetabaseDebugOutput($database)
Retrieve the debug output information captured so far from calls to Metabase functions within the context associated with the given database access handle. When this function is called, the debug output buffer is emptied.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $debug_output return value is a text string that comprises all the debug output that was captured so far.
MetabaseDebug($database, $message)
Send a debug output message to the current debugging handling function. If no debug output handling function is set, nothing happens.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $message argument is a text string the message that is meant to be outputted for debugging purposes.
The access to each of the supported DBMS is implemented by objects of driver classes. Application developers do not have to manage the driver objects directly, that is the role of Metabase applications programming interface functions.
When the MetabaseSetupDatabase function is called, it determines which driver class should be used and creates an object of that class. The database access handle that is returned by this function is an indirect reference to the driver class object that is created. The other Metabase interface functions use that database access handle to call the respective driver class object functions or manipulate its variables.
All Metabase driver classes provide the same set of functions and object variables. Metabase comes with a base class to simplify driver class development. All the driver classes are derived from the base class. This base class provides the default definition for all the driver object functions. Driver classes only have to redefine the functions that should be implemented differently from the base class to access properly to the respective DBMS.
Metabase driver classes have two types of functions: public and private.
The driver class public functions may be called by applications using the applications programming interface functions. Most of these functions only serve as stubs that do not do more than call the driver class functions. Their arguments are the same except for the database access handle argument that is dereferenced and so it is not passed when calling the driver class functions.
Public functions have the same as the interface functions without the Metabase prefix. The applications programming interface documentation has all details on their usage. Here follows the complete list of the driver class public functions:
The driver class private functions should not be called directly from the applications. There are two types of private functions: internal and external.
Internal functions are only meant to execute a driver specific action. These functions are not documented because they should only be called from driver class and so they have no interest to application developers.
External functions are only meant to be called from interface functions. Currently they only implement initialization and termination functions.
$error=$driver_object->Setup()
Initialize the driver object internal state. This function is called from within the MetabaseSetupDatabase after creating the driver object. Most driver classes do not do anything in this function, but a few other classes need to evaluate the options set from MetabaseSetupDatabase to initialize internal variables.
The $error return value contains an error message if this function call did not succeed. Otherwise it contains an empty string. This return value may be safely ignored if the database setup options are correctly defined.
$driver_object->Close()
Release any resources allocated during the driver object life time. This function is called from within the MetabaseCloseSetup before destroying the driver object. Most driver classes do not do anything in this function, but a few other classes need to check and free any resources that may be reused by the applications.
The driver objects may use either private or public variables. Private variables are only meant for internal usage. Public variables are only meant to be directly accessed from the applications programming interface functions.
Most public variables are used to store values that are initialized within the MetabaseSetupDatabase function. These variables have names that are similar to the names of the options of that function. The only exception is the database_name variable. This variable is used by the MetabaseSetDatabase to store the name of the current database being accessed. Here follows a list of all public driver variables.
Metabase API is already very large. Each complete driver is made by a reasonably large body of code. However, part of this code is not needed all the time. So, it can be loaded on demand to let the main driver class load faster and save some memory. For this reason, part of the drivers code is off-loaded to driver class extensions.
Currently the only driver class extension that is supported is the one that implements schema management functions. These functions are usually needed only at database installation time. The schema management extension is loaded only when any of those functions is called for the first time. Metabase driver base class implements these functions by loading the manager extension class and calling the respective functions of that class.
Currently the manager driver extensions should implement these functions:
CreateDatabase
DropDatabase
CreateDetailedTable
DropDetailedTable
AlterTable
ListTables
ListTableFields
GetTableFieldDefinition
ListTableKeys
GetTableKeyDefinition
ListTableIndexes
GetTableIndexDefinition
ListSequences
GetSequenceDefinition
CreateIndex
DropIndex
CreateSequence
DropSequence
GetSequenceCurrentValue
Driver classes should also define these private variables in case they have a manager extension class:
manager_include
File that contains the manager extension driver class.
manager_included_constant
Name of the constant that is defined within the specified driver extension class file to help preventing multiple inclusion.
manager_class_name
Name of the driver manager extension class.
Currently, there are several drivers being made available within the Metabase package but some are not completely developed. Work is in progress to finish those that are incomplete and add new drivers to support other DBMS. Other drivers may be written by third party developers.
ibase
Completely developed except for large objects that are supported by Interbase DBMS but its support is not yet implemented in the Metabase driver. Primary keys, auto-increment table fields and schema reverse engineering support functions are also not yet provided.
Specifies the path of the directory where are located the database files that are meant to be accessed. This path should include any ending slashes because the driver will just append the database name to it to pass the database file name to the Interbase server.
Default value: empty string
Specifies the extension of the database files that are meant to be accessed. This extension is appended to the database name to pass its file name to the Interbase server.
Default value: .gdb
Access name of a user that connects to the server to create databases.
Default value: none
Password for user specified by the DBAUser option.
Default value: none
Default length of text fields of unspecified length limit.
Default value: 4000
ifx
Completely developed except for large objects that are supported by Informix DBMS but its support is not yet implemented in the Metabase driver. Primary keys, auto-increment table fields and schema reverse engineering support functions are also not yet provided.
Specifies the mode of logging that the databases being accessed are using. The logging mode defines the way the databases support transactions. It also defines the way databases are created by Metabase MetabaseCreateDatabase function.
The modes of logging that are supported are: Unbuffered, Buffered, ANSI and none.
All actions that affect a database during a transaction are logged to disk immediately as they happen. This logging mode is slower but it is also safer in case there is a need to recover from a crash that happen during a transaction.
All actions that affect a database during a transaction are logged to a buffer that is flushed to disk when it is full. This logging mode is faster because it requires less disk access during transactions but not as safe because when crash happens during a transaction the latest actions may not be recovered if the transaction log buffer was not completely flushed to disk.
All actions that affect a database during a transaction are logged to disk as with the Unbuffered logging mode.
When this mode of logging is specified, the function MetabaseCreateDatabase creates a database that is ANSI compliant. Please refer to the section "Informix SQL Guide" on the CREATE DATABASE statement to understand the full meaning of database ANSI compliance.
When using the ANSI logging mode, all statements are executed implicitly within a transaction. This is completely transparent for Metabase applications because when transaction auto-commit mode is set to 1, this driver class assures that actions are automatically committed right after each query is executed.
In this logging mode no database action is logged. Therefore, transactions are not supported.
Default value: none
Access name of a user that connects to the server to create databases. Currently, if the user specified with this option is other than the user specified by the Metabase database setup option User, when a database is created this User is granted with RESOURCE access privileges, which means that he may create tables and indexes.
Default value: none
Password for user specified by the DBAUser option.
Default value: none
Indicates whether 8 byte integers should be used to declare table integer fields (INT8) and sequences (SERIAL8). It is not recommended to set this option to 1 if the platform under which PHP is going to run to access a Informix server does not use at least 64 bit for integers.
Default value: 0
The names of the tables is defined using sequence name prefixed with _sequence_. Sequences may not start from 0 or less.
In any case, the FIRST clause of Informix SELECT statement is used to avoid having the server returning more rows to the client side than are needed.
mssql
Completely developed except schema reverse engineering support functions that are also not yet provided.
Name of the device on which a new database should be created.
Default value: "DEFAULT"
Default size in megabytes with which a new database should be created.
Default value: not defined
If an auto-increment field is used in an INSERT query right after using the MetabaseGetNextKey function, the Metabase driver for Microsoft SQL server rewrites the query to remove the auto-increment field from the query statement.
When possible, it is recommend the use of INSERT queries that omit the auto-increment field to avoid the SQL query rewriting overhead.
To insert rows with specific values for auto-increment fields, first it is necessary to execute the query: SET IDENTITY_INSERT some_table ON.
The driver AlterTable function does not implement column renaming and other column definition changes. In the future versions of this driver those capabilities may be emulated using other SQL statements to recreate the tables with a new definition.
msql
The driver AlterTable function is not implemented because Mini-SQL does not support ALTER TABLE SQL command. AlterTable function may be implemented in the future using temporary tables. Primary keys, auto-increment table fields and schema reverse engineering support functions are also not yet provided.
Default length of text fields of unspecified length limit. Fields may grow larger than the default length though.
Default value: 255
mysql
MySQL 5.6.17 (2015-02-28)
Completely developed.
Number of TCP port to connect to the server. If specified this value is appended to the name of the server to connect to separated by a colon (:).
Default value: none
Name of the character set to be used returning data from the database, as well the default character set used to create new tables.
Default value: none
Boolean flag that tells whether Metabase should emulate decimal data types using large integer fields. This option is meant to support old MySQL versions that did not support real decimal fields.
Default value: 0
Number of decimal places to use when declaring float table fields. If this option value is other than 0, float fields are declared as DOUBLE with the specified number of decimal places, otherwise the fields not are declared with any number of decimal places. If this option is not specified, the driver class will query MySQL server and assume 30 as default number of decimal places if the server version is less than 3.23.6 or 0 otherwise.
Default value: none
Name of the table type to be used when creating tables. Currently it are accepted the following types: BDB, INNODB, GEMINI, HEAP, ISAM, MERGE, MRG_MYISAM, MYISAM.
If UseTransaction option flag is set, the DefaultTableType must be either BDB, INNODB or GEMINI.
If the DefaultTableType option is not defined, no default table type is specified on table creation, if the UseTransaction option flag is not set. Otherwise the default table type is BDB.
Default value: none or BDB if UseTransaction option is set
Flag option that indicates whether the driver should support MySQL transactions. Currently, MySQL transactions are only supported on tables of the types: BDB - Berkeley DB, INNODB and GEMINI.
Transactions support using BDB tables was added to MySQL in version 3.23.17. MySQL has to be built explicitly with Berkeley DB external libraries to support this kind of tables.
MySQL does not rollback actions performed during transactions on tables that are not of type BDB. So, when the option UseTransactions is set to 1, Metabase MySQL driver class assures that new tables are created as being of type BDB during the installation of a database.
Currently, MySQL requires that BDB tables have one field declared as PRIMARY KEY. So, Metabase MySQL driver class also assures that a integer field named dummy_primary_key is implicitly added to the definition of the table as PRIMARY KEY, NOT NULL and AUTO_INCREMENT. This may change when Metabase supports primary keys explicitly but it will completely transparent for applications that use Metabase.
INNODB tables were introduced with MySQL 3.23.34a. GEMINI were introduced with NuSphere MySQL derivation. Neither INNODB nor GEMINI tables require additional primary key fields like with BDB tables.
Default value: 0
mysql
MySQL 5.6.17 (2015-02-28)
Completely developed.
Number of TCP port to connect to the server.
Default value: none
Path of the UNIX domain socket to connect to the server.
Default value: none
Name of the character set to be used returning data from the database, as well the default character set used to create new tables.
Default value: none
Boolean flag that tells whether Metabase should emulate decimal data types using large integer fields. This option is meant to support old MySQL versions that did not support real decimal fields.
Default value: 0
Number of decimal places to use when declaring float table fields. If this option value is other than 0, float fields are declared as DOUBLE with the specified number of decimal places, otherwise the fields not are declared with any number of decimal places. If this option is not specified, the driver class will query MySQL server and assume 30 as default number of decimal places if the server version is less than 3.23.6 or 0 otherwise.
Default value: none
Name of the table type to be used when creating tables. Currently it are accepted the following types: BDB, INNODB, GEMINI, HEAP, ISAM, MERGE, MRG_MYISAM, MYISAM.
If UseTransaction option flag is set, the DefaultTableType must be either BDB, INNODB or GEMINI.
If the DefaultTableType option is not defined, no default table type is specified on table creation, if the UseTransaction option flag is not set. Otherwise the default table type is BDB.
Default value: none or BDB if UseTransaction option is set
Flag option that indicates whether the driver should support MySQL transactions. Currently, MySQL transactions are only supported on tables of the types: BDB - Berkeley DB, INNODB and GEMINI.
Transactions support using BDB tables was added to MySQL in version 3.23.17. MySQL has to be built explicitly with Berkeley DB external libraries to support this kind of tables.
MySQL does not rollback actions performed during transactions on tables that are not of type BDB. So, when the option UseTransactions is set to 1, Metabase MySQL driver class assures that new tables are created as being of type BDB during the installation of a database.
Currently, MySQL requires that BDB tables have one field declared as PRIMARY KEY. So, Metabase MySQL driver class also assures that a integer field named dummy_primary_key is implicitly added to the definition of the table as PRIMARY KEY, NOT NULL and AUTO_INCREMENT. This may change when Metabase supports primary keys explicitly but it will completely transparent for applications that use Metabase.
INNODB tables were introduced with MySQL 3.23.34a. GEMINI were introduced with NuSphere MySQL derivation. Neither INNODB nor GEMINI tables require additional primary key fields like with BDB tables.
Default value: 0
odbc
The base functionality is implemented but performing actions that depends on the underlying DBMS needs to be implemented in database specific Metabase driver subclasses of this ODBC driver class. Such actions include sequence creation and manipulation and database table alterations.
CreateDatabase and DropDatabase assume that it works using SQL commands CREATE DATABASE and DROP DATABASE. Primary keys, auto-increment table fields and schema reverse engineering support functions are also not yet provided.
Name of the data service that database administrator has to access to create a database.
Default value: empty string
Database administrator user with permissions to create databases.
Default value: empty string
Password for the user specified by the option DBAUser.
Default value: empty string
Default length of text fields of unspecified length limit when the underlying DBMS does not have a specific data type for this kind of text fields and odbc driver needs to implement this using VARCHAR fields.
Default value: 255
Boolean option that indicates whether the underlying DBMS supports transactions.
Default value: 0
Boolean option that indicates whether the underlying DBMS supports index creation.
Default value: 0
Boolean option that indicates whether the underlying DBMS supports transactions the specification of default values for table fields.
Default value: 1
Boolean option that indicates whether the underlying DBMS supports the specification of the number of places for the scale of decimal values. If this option is 0, the Metabase odbc driver assumes that ODBC DECIMAL data type works with scale 0 and will perform necessary value conversions to send and retrieve decimal values to interchange with the database.
Default value: 1
Boolean option that indicates whether text values with backslash characters (\) need to be escaped with another backslash character (\\). The ANSI standard does not require escaping of backslash characters, but some ODBC drivers do not take care of that, so this option needs to be enabled.
Default value: 0
odbc-msaccess
This driver extends the generic odbc driver redefining some operations and default values with first hand information about the way Microsoft Access databases work and what features are supported.
Most considerations made about the generic odbc driver still apply to the Microsoft Access driver. However certain features are improved.
Name of the native data type to use for character LOBs.
Default value: "MEMO"
Name of the native data type to use for binary LOBs.
Default value: "IMAGE"
oci
Oracle9i 9.0.2.4.0 (2005-09-04)
The driver AlterTable function is not able rename fields because it is not supported by Oracle at least until version 9.0.2. It implements the ability to drop columns but in earlier versions of Oracle that was not supported. The AlterTable function does not verify the server version when this function with the $check argument set to determine whether dropping columns is supported. Schema reverse engineering support functions are also not yet provided.
The driver does not dispose the data of the previously fetched rows. In the future there will be an option for random access to result set row data, that unless it is specified, the data of rows before the current being fetched is disposed to save memory on queries that return large row sets.
Service identifier of the database instance that is meant to be accessed.
Default value: value of the environment variable ORACLE_SID
Home directory path when Oracle is installed if it is running in the local machine.
Default value: value of the environment variable ORACLE_HOME
Default length of text fields of unspecified length limit. Fields may not grow larger than the length by this option.
Default value: 4000
Access name of a user with the necessary permissions to execute the commands necessary in the function CreateDatabase. It should be SYS or any other with equivalent privileges.
Default value: none
Password for user specified by the DBAUser option.
Default value: none
Name of the default tablespace where will be located the user schema that is implicitly created for the database user in the CreateDatabase function. If this option is not specified, Oracle will use the SYSTEM tablespace as default tablespace.
Default value: none
The Metabase oci driver implements the function CreateDatabase by creating a user with the necessary grants to execute the usual operations that Metabase applications need. A logical database created this way is confined to the user schema space.
The User and Password options specified when calling the MetabaseSetupDatabase are used to create a new user schema. Therefore, the specified user must not exist when calling the CreateDatabase function. The database name argument is ignored by this function.
The DropDatabase function is implemented by dropping the specified user and, consequently, any objects located in the respective schema space. This user and the respective schema space should not be used for any purpose other than accessing it using the Metabase functions.
Besides the fact that only INSERT and UPDATE queries are supported to upload large object data values, only UPDATE queries that affect only one row will set the large object fields correctly.
When that variable is not set, some versions of the PHP oci extension may either crash with a segmentation fault error, or fail to connect to an Oracle database usually returning the error ORA-03117: two-task save area overflow.
When PHP is run from a Web server, the ORACLE_HOME environment variable may be set before executing the script that starts the Web server, or within the Web server configuration, depending on the Web server that you are using.
The auto-increment emulation sequences are implicitly created by the MetabaseCreateDetailedTable or MetabaseAlterTable. The name of the sequence associated to a table with an auto-increment field is the prefix auto_increment_ followed by the table name.
A trigger is also added to a table with an auto-increment field to automatically set the field with the next sequence value when a new row is inserted and the auto-increment field is omitted or set to NULL. The name of the trigger is the table name followed by the suffix _key_insert.
Dropping tables with auto-incremented fields must be done using the MetabaseDropDetailedTable to assure that the associated sequences and triggers are also dropped.
pgsql
PostgreSQL 7.3.10 (2005-09-04)
The driver AlterTable function can change the table field attributes such as the length, but this is only supported by PostgreSQL 8 server. Adding table columns is also implemented, but it is only supported by PostgreSQL 7.3 server. Schema reverse engineering support functions are also not yet provided.
Boolean flag that tells whether Metabase should emulate decimal data types using large integer fields. This option is meant to support old PostgreSQL versions that did not support real decimal fields.
Default value: 0
Number of TCP port to connect to the server.
Default value: empty string
The auto-increment emulation sequences are implicitly created by the MetabaseCreateDetailedTable or MetabaseAlterTable. The name of the sequence associated to a table with an auto-increment field is the prefix _auto_increment_ followed by the table name.
Dropping tables with auto-incremented fields must be done using the MetabaseDropDetailedTable to assure that the associated sequences are also dropped.
sqlite
The driver AlterTable function only implements table renaming and field adding. Those types of changes require using SQLite 3 libraries. Prior versions do not support any kind of table schema changes.
Specify the database file access mode which determines the permissions that the database file will have when it is created. If the value starts with 0, the permissions value is interpreted as an octal value.
Default value: 0664
Specifies the path of the directory where are located the database files that are meant to be accessed. This path should include any ending slashes because the driver will just append the database name to open its file.
Default value: empty string
Specifies the extension of the database files that are meant to be accessed. This extension is appended to the database name to to open its file.
Default value: .db
Software development is an engineering discipline that has as goal the development of products that consist of computer programs and data. Software products are meant to be used to perform well-defined computer tasks.
Like in other engineering disciplines, the quality of a software product can only be certified if it can be demonstrated that the software works according to its product specification.
Software quality certification is an important issue because it inspires the trust of the users that rely on the products to achieve their goals with the software that they intend to use.
Users that trust on the software they use are usually more satisfied with their software product choice. Satisfied users often contribute voluntarily to the dissemination of the products among user communities with the same interests. Usually this leads to the increase of the user base. The increase of product user base often fosters further product development and consequently further user satisfaction.
For a complex software product like Metabase, it is not easy to demonstrate that it works according to its specification under all possible circumstances that it may be used. However, it is possible specify a set of tests that demonstrate that the package works as specified.
As part of the development of Metabase, it was specified a set of tests to verify the conformance of the package in general and the DBMS specific drivers in particular.
Each of the specified tests produces a series of results. The testing method that is used compares the results that are obtained with the results that are expected. If they do not match, the test failed and there is a conformance problem with either Metabase programming interface or with the implementation of the DBMS specific driver that was used in the test.
Failed tests are a very good reason to send bug reports, so the developers that are in charge take some action to trace the cause and eventually fix the bugs that lead to the test failures.
When new features are added, a correspondent number of tests should be added to also verify the conformance of such features.
The same set of tests should be repeated using different DBMS specific drivers. They also should be repeated before making each new release of the package, even if the features that they test were not changed. It may happen that the addition or change of some features may accidentally break other features that used to be working. This procedure of repeating previously successful tests is called regression testing.
Metabase test suite is currently implemented as a single script that implements several different tests. The test suite script is named driver_test.php.
The current set of tests is DBMS independent. This means that they do not verify aspects that are specific only to each of the supported DBMS, but rather features that are expected to work equally with all DBMS specific drivers that support them.
The test suite script performs tests with only one DBMS at once. A configuration script named driver_test_configuration.php should be edited to choose the driver to use in the tests as well to choose the values of some DBMS specific parameters in the $driver_arguments array as they are defined for the MetabaseSetupDatabase function.
The sample configuration script pre-defines option values for each of the supported DBMS specific drivers, but you may need to adjust those values to make it work with your setup.
The test script also installs a test database named driver_test. You should adjust the $database_variables array in the configuration script to change the test database name changing the name array entry or tell the script to not create the database but rather use a previously created database changing the create entry to "0".
The Metabase test suite could perform other tests besides the ones that are currently implemented. More tests will be included in a future release.
With some DBMS specific drivers there are tests that are deliberately skipped because the features being tested are not supported by the driver. The test script will not denote those tests as failures but it will notice that the respective features are not supported by the driver.
These are tests that are currently implemented:
This test inserts several rows in a table that has fields that use all data types supported by Metabase. Then it selects the same rows to verify if the fetched data matches the data that was inserted.
This test inserts several rows in a table that has fields that use all data types supported by Metabase. Then it selects the same rows using functions that execute the query and retrieve the whole data in a single call to verify if the fetched data matches the data that was inserted.
This test inserts rows with constant text values that have question marks and quote characters to verify if the driver class is able to distinguish these characters from real prepared query value place holders.
This test performs a SELECT query and verifies if the the number and the names of the columns of the returned result set correspond to what is expected.
This test inserts repeatedly a single row with a column with different text values and then a real NULL value. Then it selects the same row to verify whether the inserted row column contains a NULL as expected. This test also verifies if the driver EndOfResult function returns correct values.
This test inserts rows with a text column with character sequences that usually need to be correctly escaped with the driver classes' function GetTextFieldValue. Then it selects the inserted rows to verify if the column contains the correct text values. This test also verifies if the driver EndOfResult function returns correct values.
This test is similar to the previous, but instead of selecting all rows in a single query, it selects different ranges of rows by executing the same query several times.
This test creates a sequence several times with different start values. It verifies if the function to retrieve the next sequence value returns the correct values.
This test executes INSERT, UPDATE and DELETE queries. Then verifies if the function to retrieve the number of affected rows, matches the expected number of rows changed in the table.
This test starts a transaction using the function to disable auto committing. Then it inserts a table row and rolls back the transaction to verify if the insertion of the row was effectively canceled. Then it inserts the table row again and commits the transaction to verify if the row was effectively inserted. Finally, it deletes the inserted row and implicitly commits and ends the transaction disabling auto committing to verify if the inserted row was effectively deleted.
This test inserts a row in a table and updates the same row with the SQL REPLACE query and verify that the row data was properly inserted and updated.
This test stores character and binary data in large object fields and retrieves those field values to verify whether they contain what was stored.
This test stores character and binary data read from files in large object fields and retrieves those field values into the same files to verify whether they contain what was stored.
This test inserts NULL values in large object fields and then verifies whether they are stored correctly.
This test inserts rows in a table with auto-incremented key fields using direct queries and then verifies whether the information in the table rows matches the inserted values of the auto-increment key field and of other fields.
This test inserts rows in a table with auto-incremented key fields using prepared queries and then verifies whether the information in the table rows matches the inserted values of the auto-increment key field and of other fields.
This test inserts several rows in a table and executes different queries to verify whether the inserted text values match the expected patterns.
The current Metabase release does not have any problems that are known to be due to bugs in Metabase programming interface or DBMS specific drivers. However, there are some issues that make the conformance tests fail due to known problems in PHP or some releases of certain DBMS.
In some versions of Interbase, the database server may not be able to start or commit transactions causing errors or entering in deadlock. These problems are not due to problems with PHP or this Metabase driver, as they seem to be caused by problems in the communication between Interbase client libraries and the server. These problems may lead driver transactions test fail with errors, thus making the driver test script to exit.
At least until the current version of PHP there is no way to determine with certainty whether a result value is NULL.
Metabase ifx driver checks the result values against the string text NULL which could mean either that it is a real NULL value or it is a text value with a string with the letters of NULL. This fact makes the nulls driver conformance test fail.
At least until the current version of PHP there is no way to determine with certainty whether a result value is NULL.
Metabase msql driver checks the result values against an empty string which could mean either that it is a real NULL value or it is a text value with a real empty string. This fact makes the nulls driver conformance test fail.
Sequences are emulated by the MySQL driver using tables with a single column defined with the AUTO_INCREMENT attribute. The initial value of a sequence emulated this way is set by inserting one row in the table with the sequence start value minus 1 in the auto incrementing column.
Apparently, since MySQL 3.23 release auto-incremented columns with values that are negative are turned into into a very large positive numbers despite the column is declared as signed integer.
This problem was reported to MySQL development team but they do not understand this as a bug, despite there was no such problem in earlier MySQL releases. This means that they are not willing to fix MySQL behavior to make it work consistently with the declaration of the column type, i.e., auto-incremented values be handled as signed or unsigned depending on the type of integer declared for the respective column.
This problem only affects sequences that start with 0 or less. Use sequences that start from 1 or more to avoid the problem.
At the time of writing of this document the currently available version of MySQL (3.23.23) is known to not work well with BDB tables. Specifically selecting data from BDB using the ORDER BY clause is known to fail. BDB tables are created by Metabase MySQL driver class to be able to support transactions.
At least until the current version of PHP (4.3) there is no way to determine whether a result value is NULL. PHP ODBC functions return an empty string where a NULL result value is expected. This fact makes the driver conformance tests nulls and lobnulls fail. The bug in PHP was reported and it may be fixed soon.
The Metabase PostgreSQL driver sets the PG_DATESTYLE environment variable to assure that date column values are returned by PostgreSQL database queries formatted with the ISO 8601 standard (YYYY-MM-DD).
Apparently, under at least release PHP 4 RC1, when a connection to PostgreSQL server is established for a second time in the same script execution, the PG_DATESTYLE environment variable is corrupted by PHP setenv function, making subsequent PostgreSQL result fetching calls return dates formatted in the PostgreSQL default date format, which usually is not the format defined by ISO 8601 standard. This makes the first two tests fail when using PHP RC1.
The problem was reported and fixed by to the PHP development team before PHP 4.0.0 was released. It does not seem to exist in PHP 3 releases.
Current Oracle DBMS treat empty strings as NULL when inserting or updating data in table columns of text. This behavior may change in the future but currently it makes the Metabase nulls driver conformance test fail.
Metabase schema description parser is class that is able to parse a custom data format that is used to describe database schemas in a DBMS independent manner.
The schema description format is based on XML (eXtensible Markup Language). For those that are not familiar with it, XML is standard that specifies rules to define abstract data formats based on tagged text.
Like HTML (HyperText Markup Language), XML is also based on SGML (Standard Generalized Markup Language). SGML defines rules to structure data using special text tags.
SGML tags may be used to delimit data sections. Section begin tags are of the form <name attributes... > and end tags are of the form </name>. name is the name of the tag and attributes is a set of zero or more pairs of attribute names and the values associated with the respective tag.
XML is a little stricter in the way tags may be used. While with many SGML formats some end tags are optional, in XML end tags are always required. Also, when tag attributes are used, attribute values must be specified always between quotes. These XML requirements are usually known as well-formedness.
Another important detail about XML strictness is that tag names and attributes are case sensitive. This means that tags in upper case are distinct from tags in lower case.
Unlike a common (but mistaken) belief, XML is not meant just for describing data in documents that are meant to be displayed or printed. XML is a standard that defines rules for describing abstract data may be used to for any purpose.
Even though it may be used to schemas that may be displayed or printed, Metabase schema description format is meant to provide a way for developers to design their database schemas using a DBMS independent file format. Using this format, developers may describe relations and properties of tables, field, indexes, sequences, etc..
This format uses just a subset of the XML known as SML (Simplified Markup Language). SML formats complies with the same rules as any XML format but it does not use all its possibilities to make it simpler for users to write and understand the data. For instance, files written on SML do not use any tag attributes.
Metabase schema description format is also simpler to edit by hand because tags and constant data values should always be in lower case to save the user from holding frequently the keyboard shift key.
The Metabase schema description format lets the developers describe a set of database schema objects following the database objects hierarchy. This means that a database may contain tables and sequence objects, tables may contain fields and index objects and all these objects have their own attributes.
The definition of each database schema object contained within the begin and end tags of the respective container object. Therefore, the definition of each table and sequence has to be specified between the main database begin and end tags. Likewise, the definition of fields and indexes has to be specified between the respective table begin and end tags.
The properties of each schema object are also defined between the respective begin and end tags. The values of each property are also defined between the respective property being and end tags.
The values of the properties are subject of validation according to the type of each property and the context within which they are being defined.
Some properties define names of database schema objects. There are names that are accepted as valid for some DBMS that are not accepted by other DBMS. Metabase schema parser may optionally fail if such names are used to reduce the potential problems when using the same Metabase based application with different DBMS.
The schema object description tags are defined as follows:
The database tag should be always at the top of the schema object hierarchy. Currently it may contain the definition of two types of objects: table and sequence.
The database schema object may have the following properties:
Name of the database that is meant to be created when it is installed for the first time.
Default: none
Boolean flag that indicates whether the database manager class should create the specified database or use a previously installed database of the same name.
This property may have to be set to 0 if you are splitting your database definition in multiple schema description files and only the installation of the first description file is supposed to lead to the actual database creation operation.
Another circumstance on which this property may have to be set to 0 is when the DBMS driver does not support database creation or if this operation requires special database administrator permissions that may not be available to the database user.
Default: 0
Free text property meant for describing the purpose of the database. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Additional database comments. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
The table is one of the main database schema objects. It may be used in a schema description multiple times, once per each table that is contained the database being described.
The table object definition may contain the declaration and initialization sections besides the properties. The table schema object may have the following properties:
Name of the table.
Default: none
Name that the table had before the last time it was renamed. This property is only meant to be used when the database is being upgraded and the table is meant to be renamed.
Beware that if this property is not specified and the name is changed, that is intended as if it is meant to drop the table with the previous name and create a new table without keeping the data that was previously stored in the table.
Default: none
Free text property meant for describing the purpose of the table. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Additional table comments. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Example:
<table>
<name>users</name>
<declaration>
<field>
<name>id</name>
<type>integer</type>
<notnull>1</notnull>
<default>0</default>
</field>
<field>
<name>name</name>
<type>text</type>
<length><variable>name_length</variable></length>
</field>
</declaration>
<initialization>
<insert>
<field>
<name>id</name>
<value>1</value>
</field>
<field>
<name>name</name>
<value>administrator</value>
</field>
</insert>
</initialization>
</table>
declaration is one of the sections that is part of the table definition. This section is required because it must contain the definition of the table field and index objects.
field is one of the types of table definition object. It should be specified within the table declaration section for each field that the table should contain.
The field schema object may have the following properties:
Name of the field.
Default: none
Name that the field had before the last time it was renamed. This property is only meant to be used when the database is being upgraded and the field is meant to be renamed.
Default: none
Type of the field. Valid type values are: integer, text, boolean, date, timestamp, time, float, decimal, clob and blob.
Default: none
Default value for the field. The allowed values depend on the type of the field. If this property is not specified the default value is assumed to be NULL. If there is a table index on the field being declared, the default value must be specified because indexes can not be created on table fields with NULL values.
Notice that some low end DBMS do not support specifying default values in the field definition. Make sure that the you either specify all the values for each new row that is inserted in the tables or that the DBMS actually supports default values.
Only some DBMS support default values for large object fields. For this reason it is not allowed to specify default values on the definition of this type of field.
Default: none
Boolean flag property that specifies whether the table field should be created with the constraint NOT NULL. As it suggests, this implies that it is not allowed to set this field value with NULL. The parser does not allow that an index is declared on a field that is not declared with the notnull flag property.
Default: 0
Boolean flag property that specifies whether an integer field should be declared as unsigned integer. Note that some DBMS do not support unsigned integer fields. In such case the DBMS driver will ignore this property but it issues a warning.
Default: 0
Unsigned integer property that specifies the length of a text. If this property is specified the text field may not have a longer length. Text values shorter than the length are not padded. If this property is not specified the length limit is determined by the DBMS.
Default: none
Boolean flag property that specifies whether the table field should be an auto-incremented key field. Setting this property to 1 implicitly sets other properties of the field: type to integer, unsigned to 1, notnull to 1, default to 0, and also define the table primary key to this field if the table primary key is not defined.
Default: none
Free text property meant for describing the purpose of the field. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Additional field comments. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Example:
<field>
<name>id</name>
<type>integer</type>
<notnull>1</notnull>
<default>0</default>
</field>
index is another type of table definition object. It should also be specified within the table declaration section.
The index schema object may have the following properties:
Name of the index.
Only some DBMS support indexes on large object fields. For this reason it is not allowed to specify a large object field for indexes.
Default: none
Name that the index had before the last time it was renamed. This property is only meant to be used when the database is being upgraded and the field is meant to be renamed.
Default: none
Boolean flag property that specifies whether the combination of the values of the index fields on each row of the table should unique.
Default: 0
field is a section that is part of the table index declaration. It should be used once per each field on which the index should be created.
The index field declaration may have the following properties:
Name of the field on which the index should be created.
Default: none
Type of field sorting that should be assumed when the index is created. On DBMS that support index sorting, queries that search tables on the fields specified by the given index may execute faster if the specified sorting type is chosen adequately. DBMS that do not support index sorting will ignore this property.
Valid sorting types are ascending and descending.
Default: none
Example:
<index>
<name>users_index</name>
<field>
<name>id</name>
<sorting>ascending</sorting>
</field>
</index>
primarykey is another type of table definition object. It should also be specified within the table declaration section.
The primarykey schema object only contains one or more sections that define the fields the define the table primary key. Each field section starts with the field tag and its definition is the same for normal table index fields.
If the table contains an auto-incremented key field, automatically the primary key definition is associated to that field. Therefore, the primary key definition becomes redundant unless its definition includes other fields besides the table auto-incremented key field.
Example:
<primarykey>
<field>
<name>id</name>
<sorting>ascending</sorting>
</field>
</primarykey>
initialization is another section that is part of the table definition. This section is optional and may contain the definition of actions that should be executed when installing the database for the first time. Currently the only action that is supported is insert.
insert is a table initialization command that specifies the values of the fields of rows that should be inserted in the respective table after the database is created for the first time.
The insert command definition only contains one more field sections with name and value pairs that indicate the values of the fields are explicitly initialized when the new row is inserted. Missing fields are implicitly initialized by the DBMS with the respective default values.
The sequence is another main database schema object. It may be used in a schema description multiple times, once per each sequence that is contained the database being described.
A sequence is an object that only contains an integer value. A sequence value is automatically incremented after every time it is fetch the next sequence value. The fetch and increment actions constitute an atomic operation. This means that if two concurrent accesses to the database fetch the next sequence value, they will always different values.
Sequences are useful to generate integer numbers that may be used to store in fields that must contain unique numbers.
The sequence object definition may contain one on section besides the properties. The sequence schema object may have the following properties:
Name of the sequence.
Default: none
Name that the sequence had before the last time it was renamed. This property is only meant to be used when the database is being upgraded and the sequence is meant to be renamed.
Default: none
Integer property that specifies the value that the sequence will return when it is fetched the next sequence value.
Default: none
Free text property meant for describing the purpose of the sequence. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
Additional table comments. This field is not processed by the schema parser. It may contain text or other XML well formed tags that may be useful for embedding additional data structures for documentation applications or other purposes.
Default: none
The sequence on table field section specifies a table field on which the sequence value should be synchronized.
This information is meant to be used only by the database manager class when it is added a sequence later after the database has been installed for the first time. If the sequence on is specified, the database manager class will override the sequence start value with a value that is higher than the highest value in the specified field table. Therefore, the specified field table type must be integer.
Example:
<sequence>
<name>users_sequence</name>
<start>1</start>
<on>
<field>id</field>
<table>users</table>
</on>
</sequence>
The variable tag is not meant to define any database schema object but rather a means to replace property values with variables defined at parsing time.
For instance, if you have several tables that store user names in text fields with the same length limit, instead of hard coding the length value in all field definitions, you may use a variable reference. The parser will replace variable references found in property definitions by the text values of the respective values passed to the parser.
The parser will issue an error if the it is referenced a variable that is not defined.
The schema description parser is a class that is able to build a data structure that describes database schema objects by parsing a schema description.
The objects of this class are initialized like the objects of other classes, i.e., using the new operator:
$parser_object=new metabase_parser_class;
$error=$parser_object->Parse($data, $end_of_data)
Parse schema description to build a data structure that describes database schema objects.
The $data argument is a string that contains the contents of all or part of the schema description XML data. This function may be call once or more times to feed consecutive chunks of schema description until the whole XML data and been fed to the parser.
The resulting schema description data structure is stored in the object database variable.
The $end_of_data argument is a boolean flag that indicates whether the data being passed to the parse is the last (or probably the only) chunk of the schema description being passed.
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
$error=$parser_object->ParseStream($stream)
Parse schema description by calling the Parse function by reading the input from an opened stream until it reaches its end.
The $stream argument is an identifier for an opened stream that may be an opened file, network connection, etc..
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
The objects of this class have several public variables that contain additional information beside what is returned by the class functions.
Integer value that holds the size of the buffer into which the ParseStream function reads the chunks of the schema description input data. The default value is 4096.
Integer value that holds the error number that resulted from the last failed call to the class functions. The currently defined values are:
0 - No error
1 - Could not parse data
2 - Could not read from input stream
3 - Metabase format syntax error
4 - Variable not defined
Text value that holds the error number that resulted from the last failed call to the class functions.
Integer value that holds the number of the line of the schema description input data that originated the error that resulted from the last failed call to the class functions.
Integer value that holds the number of the column of the schema description input data that originated the error that resulted from the last failed call to the class functions.
Integer value that holds the number of byte of the schema description input data that originated the error that resulted from the last failed call to the class functions.
Associative array that holds the list of variable names and their respective values that should be used to replace variable reference of the data of schema object properties.
Boolean flag value that indicates whether the parser should fail when it parses a schema object with a name that is potentially invalid, i.e., it may not be accepted by all DBMS.
Associative array that contains the data structure that describes the database schema objects after calling the Parse function. The contents of this array reflect the structure of the data in database schema definitions XML input data.
The database manager is a class that provides a set of database management services like installing, altering and dumping the data structures of databases.
The objects of this class are initialized like the objects of other classes, i.e., using the new operator:
$manager_object=new metabase_manager_class;
$error=$manager_object->AlterDatabase(&$previous_definition, &$changes)
Execute the necessary actions to implement the requested changes in a database structure.
The $previous_definition argument is an associative array that contains the definition of the database structure before applying the requested changes. The definition of this array may be built separately, but usually it is built by the Parse method the Metabase parser class.
The $changes argument is an associative array that contains the definition of the changes that are meant to be applied to the database structure.
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
$error=$manager_object->DumpDatabase($arguments)
Dump a previously parsed database structure in the Metabase schema XML based format suitable for the Metabase parser. This function may optionally dump the database definition with initialization commands that specify the data that is currently present in the tables.
The $arguments argument is an associative array that takes pairs of tag names and values that define dump options. Currently, the supported option arguments are as follows:
Name of the global function that is meant to be called to output the database dump. The function will only take a string argument that contains that text that defined the dump.
Default value: not specified
String that defines how the lines of the dump are meant to be broken.
Default value: a single new line character
Option that indicates whether it is intended to dump the data of the database tables as initialization commands.
Default value: not defined
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
$error=$manager_object->ParseDatabaseDefinitionFile($input_file, &$database_definition, &$variables)
Parse a database definition file by creating a Metabase schema format parser object and passing the file contents as parser input data stream.
The $input_file argument is the path of the database schema file.
The $database_definition argument is a reference to an associative array that will hold the information about the database schema structure as return by the parser object.
The $variables argument is an associative array that the defines the text string values that are meant to be used to replace the variables that are used in the schema description.
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
$manager_object->DumpDatabaseChanges(&$changes, $function)
Dump the changes between two database definitions.
The $changes argument is an associative array that specifies the list of database definitions changes as returned by the CompareDefinitions manager class function.
The $function argument is the name of the global function that is meant to be called to output the database changes dump. The function will only take a string argument that contains that text that defined the dump.
$success=$manager_object->UpdateDatabase($current_schema_file, $previous_schema_file, $arguments, $variables, $check)
Compare the corresponding files of two versions of a database schema definition: the previously installed and the one that defines the schema that is meant to update the database.
If the specified previous definition file does not exist, this function will create the database from the definition specified in the current schema file.
If both files exist, the function assumes that the database was previously installed based on the previous schema file and will update it by just applying the changes.
If this function succeeds, the contents of the current schema file are copied to replace the previous schema file contents. Any subsequent schema changes should only be done on the file specified by the $current_schema_file to let this function make a consistent evaluation of the exact changes that need to be applied.
The $current_schema_file argument is the name of the updated database schema definition file.
The $previous_schema_file argument is the name the previously installed database schema definition file.
The $arguments argument is an associative array that takes pairs of tag names and values that define the setup arguments that are passed to the MetabaseSetupDatabase function.
The $variables argument is an associative array that is passed to the argument of the same name to the ParseDatabaseDefinitionFile function.
The $success result value is boolean flag that indicates whether this function call succeeded. If it failed, the object variable error contains the text string of the error that occurred.
$error=$manager_object->DumpDatabaseContents($schema_file, &$setup_arguments, &$dump_arguments,& $variables)
Parse a database schema definition file and dump the respective structure and contents.
The $schema_file argument is the path of the database schema file.
The $setup_arguments argument is an associative array that takes pairs of tag names and values that define the setup arguments that are passed to the MetabaseSetupDatabase function.
The $dump_arguments argument is an associative array that takes pairs of tag names and values that define dump options as defined for the DumpDatabase function.
The $variables argument is an associative array that the defines the text string values that are meant to be used to replace the variables that are used in the schema description as defined for the ParseDatabaseDefinitionFile function.
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
$error=$manager_object->GetDefinitionFromDatabase($arguments)
Retrieve the schema definition of the current database using reverse engineering functions of Metabase API.
This function reverse engineers the definition of the current database and stores it class variable database_definition. After successfully executing the GetDefinitionFromDatabase function , you may call the DumpDatabaseContents to output the database schema definition in the XML format.
A script named get_database.php is provided with Metabase distribution to use these functions to dump database schemas in the XML format.
The $arguments argument is a reference to an associative array variable that has pairs of tag names and values that define the setup arguments that are passed to the MetabaseSetupDatabase function.
The $error result value is a string that describes the error that occurred if this function call fails. If it succeeds, the function returns an empty string.
The objects of this class have several public variables that contain additional information beside what is returned by the class functions.
The schema parser class variable of the same name is set with the value of the option variable before parsing new schema descriptions. Refer to the schema parser variable description to understand its meaning.
Default value: 1
Text value that holds an error string that may result from failure of the UpdateDatabase function call.
Array that holds a list of warning messages that may result from unexpected situations that should be paid attention.
Associative array that holds the definition of the current database schema.