Table of Contents
The Data Dictionary
Introduction
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.
Portable Data Types
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.
Summary Of Standard Types
| 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 |
Using Data Types For Non-Portable Applications
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 |
The Meta Functions
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 |
Schema Management
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.
Executing prepared code to update the schema
Each of the methods below return an array of data, which should be executed using the executeSqlArray() method
Creating A Database
| createDatabase() | Returns the SQL necessary to create a database |
Managing Tables
| 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 |
Managing Columns
| 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 |
Managing Indexes
| 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 |
Managing Database Column Comments
| getCommentSql | Returns the SQL necessary to read the comment on a column |
| setCommentSql | Returns the SQL necessary to write the comment on a column |
XML Driven Functions
See the section on ADOdb-xmlschema on how to extend data dictionary functions using XML