[[v5:dictionary:Index]]\\ ====== The Data Dictionary ====== ===== Introduction ===== The Data Dictionary provides a series of **'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 [[v5:dictionary:metatype|metaType()]] method. ==== Summary Of Standard Types ==== ^ Type ^ Represents Field Types ^ When Using addColumnSql ^ Example ^ | C | character fields that should be shown in a 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 | | | | 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 [[v5:database: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. | [[v5:dictionary:actualtype|actualType()]] | Returns a string description of the actual data type linked to the [[v5:dictionary:metatype|metaType]] for the currently connected database | | [[v5:reference:connection:setcustomactualtype|setCustomActualType()]] | Links a physical database type to a custom metatype | | [[v5:dictionary:getcustomactualtypes|getCustomActualTypes()]] | Returns a list of previously defined custom | | [[v5:dictionary:metacolumns|metaColumns()]] | Returns a list of available columns and column type information for the supplied table name | | [[v5:dictionary:metacolumnnames|metaColumnNames()]] | Returns a list of available columns for the supplied table name | | [[v5:dictionary:metadatabases|metaDatabases()]] | Returns a list of available databases | | [[v5:dictionary:metaforeignkeys|metaForeignKeys()]] | Returns a list of foreign keys for the supplied table name | | [[v5:dictionary:metaindexes|metaIndexes()]] | Returns a list of indexes for the supplied table name | | [[v5:dictionary:metaprimarykeys|metaPrimaryKeys()]] | Returns a list of primary keys for the supplied table name | | [[v5:dictionary:metaprocedures|metaProcedures()]] | Returns a list of procedures and functions for the connected database | | [[v5:dictionary:metatables|metaTables()]] | Returns list of available tables for the supplied database name | | [[v5:dictionary:metatransaction|metaTransaction()]] | Returns a database specific transaction mode for a provided meta transaction type | | [[v5:dictionary:metatype|metaType()]] | Converts database-specific data type into a portable type | | [[v5:dictionary:setcustommetatype|setCustomMetaType()]] | Creates a custom, database-specific data type and defines how it is handled by inserts and updates | | [[v5:dictionary:getcustommetatypes|getCustomMetaTypes()]] | Returns a list of custom metatypes | | [[v5:reference:connection:serverinfo|serverInfo()]] | Returns a set of server information | ===== Schema Management ===== <WRAP info> The user id 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. </WRAP> ==== Executing prepared code to update the schema ==== Each of the methods below returns an array of data, which should be executed using the [[v5:dictionary:executesqlarray|executeSqlArray()]] method. ==== Creating A Database ==== | [[v5:dictionary:createdatabase|createDatabase()]] | Returns the SQL necessary to create a database | ==== Managing Tables ==== | [[v5:dictionary:createtablesql|createTableSql()]] | Returns an SQL statement necessary to create a new table in the database | | [[v5:dictionary:changetablesql|changeTableSql()]] | Returns an SQL statement necessary to change a table in the database | | [[v5:dictionary:renametablesql|renameTableSql()]] | Returns an SQL statement necessary to rename a table in the database | | [[v5:dictionary:droptablesql|dropTableSql()]] | Returns an SQL statement necessary to drop a table from the database | ==== Managing Columns ==== | [[v5:dictionary:addcolumnsql|addColumnSql()]] | Returns an SQL statement necessary to add a new column to an existing table in the database | | [[v5:dictionary:altercolumnsql|altercolumnSql()]] | Returns an SQL statement necessary to modify the attributes of an existing column in a table in the database | | [[v5:dictionary:renamecolumnsql|renameColumnSql()]] | Returns an SQL statement necessary to rename an existing column in an existing table in the database | | [[v5:dictionary:dropcolumnsql|dropColumnSql()]] | Returns an SQL statement necessary to drop an existing column from an existing table in the database | ==== Managing Indexes ==== | [[v5:dictionary:createindexsql|createIndexSql()]] | Returns an SQL statement necessary to add an index to an existing table in the database | | [[v5:dictionary:dropindexsql|dropIndexSql()]] | Returns an SQL statement necessary to drop an existing index from a table in the database | ==== Managing Database Column Comments ==== |[[v5:dictionary:getcommentsql]]| Returns the SQL necessary to read the comment on a column| |[[v5:dictionary:setcommentsql]]| Returns the SQL necessary to write the comment on a column| ===== XML Driven Functions ===== See the section on [[v5:dictionary:xmlschema|ADOdb-xmlschema]] on how to extend data dictionary functions using XML