- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:dictionary:dictionary_index

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
X | TeXt, large text or CLOB fields that should be shown in a <textarea> |  |
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
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/01/13 07:10 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top