V6 [[v6: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 [[v6:dictionary:metatype|metaType()]] method. ==== Summary Of Types ==== Each MetaType now has 2 representative class constants available: - A Short Constant, which is formed from **ADODB_METATYPE_** plus the type defined below, e.g ADODB_METATYPE_J - A Long Constant, which is formed from **ADODB_METATYPE_** plus the value shown, e.g. ADODB_METATYPE_JSON The constants are available from both the main connection class and the ADOMetaFunction class, e.g. ADOConnection::ADODB_METATYPE_INT ^ Type ^ Long Constant ^ Represents Field Types ^ When Using addColumnSql ^ Example ^ | C |CHAR | character fields that should be shown in a tag | Add the length of the field | C(20) | | C2 |MCHAR | 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 | TeXt, large text or CLOB fields that should be shown in a | | | | X2 |MTEXT | Like an X field, but where possimble creates a field that can hold multi-byte (unicode) data is created | | | | XL |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 |BIN | Blobs, or Binary Large Objects. Typically images. | | | | D |DATE | Date (sometimes DateTime) field | | | | T |TIME | Timestamp field | | | | L |LOG | Logical field (boolean or bit-field). Some databases emulate logicals using I2 fields | | | | I |INT, INT2, INT4, INT8| 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 |NUM | Numeric field. Includes autoincrement, numeric, floating point, real and integer. | Add the precision and decimal digits | N 14.4 | | R |REAL | Serial field. Includes serial, autoincrement integers. This works for selected databases. Some databases do not signify if a field is auto-increment | | | | J | JSON | A database specifc JSON database type | | | | G | GEOMETRY | Basic Geometry Column | | | | PO | POINT | | | | | PY | POLYGON | | | | | LS | LINESTRING | | | | | MP | MULTIPOINT | | | | | MY | MULTIPOLYGON | | | | | MS | MULTILINESTRING | | | | | GC | GEOMETRYCOLLECTION | | | | ==== 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 [[v6: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. | [[v6:dictionary:actualtype|actualType()]] | Returns a string description of the actual data type linked to the [[v6:dictionary:metatype|metaType]] for the currently connected database | | [[v6:dictionary:metacolumns|metaColumns()]] | Returns a list of available columns and column type information for the supplied table name | | [[v6:dictionary:metacolumnnames|metaColumnNames()]] | Returns a list of available columns for the supplied table name | | [[v6:dictionary:metadatabases|metaDatabases()]] | Returns a list of available databases | | [[v6:dictionary:metaforeignkeys|metaForeignKeys()]] | Returns a list of foreign keys for the supplied table name | | [[v6:dictionary:metaindexes|metaIndexes()]] | Returns a list of indexes for the supplied table name | | [[v6:dictionary:metaprimarykeys|metaPrimaryKeys()]] | Returns a list of primary keys for the supplied table name | | [[v6:dictionary:metaprocedures|metaProcedures()]] | Returns a list of procedures and functions for the connected database | | [[v6:dictionary:metatables|metaTables()]] | Returns list of available tables for the supplied database name | | [[v6:dictionary:metatransaction|metaTransaction()]] | Returns a database specific transaction mode for a provided meta transaction type | | [[v6:dictionary:metatype|metaType()]] | Converts database-specific data type into a portable type | | [[v6:dictionary:serverinfo|serverInfo()]] | Returns a set of server information | ===== Schema Management ===== <WRAP info> 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. </WRAP> ==== Executing prepared code to update the schema ==== Each of the methods below return an array of data, which should be executed using the [[v6:dictionary:executesqlarray|executeSqlArray()]] method ==== Creating A Database ==== | [[v6:dictionary:createdatabase|createDatabase()]] | Returns the SQL necessary to create a database | ==== Managing Tables ==== | [[v6:dictionary:createtablesql|createTableSql()]] | Returns an SQL statement necessary to create a new table in the database | | [[v6:dictionary:changetablesql|changeTableSql()]] | Returns an SQL statement necessary to change a table in the database | | [[v6:dictionary:renametablesql|renameTableSql()]] | Returns an SQL statement necessary to rename a table in the database | | [[v6:dictionary:droptablesql|dropTableSql()]] | Returns an SQL statement necessary to drop a table from the database | ==== Managing Columns ==== | [[v6:dictionary:addcolumnsql|addColumnSql()]] | Returns an SQL statement necessary to add a new column to an existing table in the database | | [[v6:dictionary:altercolumnsql|altercolumnSql()]] | Returns an SQL statement necessary to modify the attributes of an existing column in a table in the database | | [[v6:dictionary:renamecolumnsql|renameColumnSql()]] | Returns an SQL statement necessary to rename an existing column in an existing table in the database | | [[v6:dictionary:dropcolumnsql|dropColumnSql()]] | Returns an SQL statement necessary to drop an existing column from an existing table in the database | ==== Managing Indexes ==== | [[v6:dictionary:createindexsql|createIndexSql()]] | Returns an SQL statement necessary to add an index to an existing table in the database | | [[v6:dictionary:dropindexsql|dropIndexSql()]] | Returns an SQL statement necessary to drop an existing index from a table in the database | ==== Managing Database Column Comments ==== |[[v6:dictionary:getcommentsql]]| Returns the SQL necessary to read the comment on a column| |[[v6:dictionary:setcommentsql]]| Returns the SQL necessary to write the comment on a column| ===== XML Driven Functions ===== See the section on [[v6:dictionary:xmlschema|ADOdb-xmlschema]] on how to extend data dictionary functions using XML