ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:dictionary:dictionary_index

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 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 TypeDescription
-9nvarchar
-8nchar
1char
12varchar

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
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
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

v5/dictionary/dictionary_index.txt · Last modified: 2016/05/13 08:04 by mnewnham