The Data Dictionary provides a series of functions 'Meta-Functions' that allow a portable method of managing the schema of databases, from simple querying of data attributes of columns to XML based routines to build and manage new tables.
ADOdb provides a cross-platform method of identifying the data type of a field. These values can be used as a replacement in code that relies on the data type of field to produce a result. Each database driver contains a cross-reference table of datatypes that can accessed using the metaType() method.
| Type | Represents Field Types | When Using addColumnSql | Example |
|---|---|---|---|
| C | character fields that should be shown in a <input type=“text”> tag | Add the length of the field | C(20) |
| C2 | Like a C field, but where possible a field that can hold multi-byte (unicode) data is created | Add the length of the field | C2(20) |
| X | TeXt, large text or CLOB fields that should be shown in a <textarea> | ||
| X2 | Like an X field, but where possible creates a field that can hold multi-byte (unicode) data is created | ||
| XL | On systems that support it, creates a large clob field (>32K). This may require additional database configuration. If the database does not support it, a standard clob field is created. | ||
| B | Blobs, or Binary Large Objects. Typically images. | ||
| D | Date (sometimes DateTime) field | ||
| T | Timestamp field | ||
| L | Logical field (boolean or bit-field). Some databases emulate logicals using I2 fields | ||
| I | Integer field, This may be broken down further into I2,I4 or I8 types to represent small,medium and large integers. The largest integer data size is always represented by an I field | ||
| N | Numeric field. Includes autoincrement, numeric, floating point, real and integer. | Add the precision and decimal digits | N 14.4 |
| R | Serial field. Includes serial, autoincrement integers. This works for selected databases. Some databases do not signify if a field is auto-increment |
There are benefits to using ADOdb data types even when writing an application that is not designed to be portable. The metaType() method can hide numerous data sub-types from a developer, when the difference in the field is not important. For example the following actual data types in the Microsoft SQL Server driver are all represented by the ADOdb type C:
| Data Type | Description |
|---|---|
| -9 | nvarchar |
| -8 | nchar |
| 1 | char |
| 12 | varchar |
Metadata functions convert database specific table, column and index queries into a standardized cross-database format.
| actualType() | Returns a string description of the actual data type linked to the metaType for the currently connected database |
| setCustomActualType() | Links a physical database type to a custom metatype |
| getCustomActualTypes() | Returns a list of previously defined custom |
| metaColumns() | Returns a list of available columns and column type information for the supplied table name |
| metaColumnNames() | Returns a list of available columns for the supplied table name |
| metaDatabases() | Returns a list of available databases |
| metaForeignKeys() | Returns a list of foreign keys for the supplied table name |
| metaIndexes() | Returns a list of indexes for the supplied table name |
| metaPrimaryKeys() | Returns a list of primary keys for the supplied table name |
| metaProcedures() | Returns a list of procedures and functions for the connected database |
| metaTables() | Returns list of available tables for the supplied database name |
| metaTransaction() | Returns a database specific transaction mode for a provided meta transaction type |
| metaType() | Converts database-specific data type into a portable type |
| setCustomMetaType() | Creates a custom, database-specific data type and defines how it is handled by inserts and updates |
| getCustomMetaTypes() | Returns a list of custom metatypes |
| serverInfo() | Returns a set of server information |
The userid used when establishing a database connection may need specific privileges beyond normal INSERT,DELETE,UPDATE,SELECT in order to manage a schema in a database. Those privileges vary from one DBMS to another.
Each of the methods below return an array of data, which should be executed using the executeSqlArray() method
| createDatabase() | Returns the SQL necessary to create a database |
| createTableSql() | Returns an SQL statement necessary to create a new table in the database |
| changeTableSql() | Returns an SQL statement necessary to change a table in the database |
| renameTableSql() | Returns an SQL statement necessary to rename a table in the database |
| dropTableSql() | Returns an SQL statement necessary to drop a table from the database |
| addColumnSql() | Returns an SQL statement necessary to add a new column to an existing table in the database |
| altercolumnSql() | Returns an SQL statement necessary to modify the attributes of an existing column in a table in the database |
| renameColumnSql() | Returns an SQL statement necessary to rename an existing column in an existing table in the database |
| dropColumnSql() | Returns an SQL statement necessary to drop an existing column from an existing table in the database |
| createIndexSql() | Returns an SQL statement necessary to add an index to an existing table in the database |
| dropIndexSql() | Returns an SQL statement necessary to drop an existing index from a table in the database |
| getCommentSql | Returns the SQL necessary to read the comment on a column |
| setCommentSql | Returns the SQL necessary to write the comment on a column |
See the section on ADOdb-xmlschema on how to extend data dictionary functions using XML