Last edit 2010-07-08 08:26:51 by espaan [ minor text update ]

Additions
See also this forum topic∞ about large fields and what size they are.

Deletions
See also this forum topice∞ about large fields and what size they are.

---

_EDITED 2010-07-08 08:26:22 by espaan [ adding link to forum topic about large fields ]

Additions

ADODB Data Dictionary

DBUtil uses the ADODB data dictionary. This is a way to specify a database table's structure in a generic way. For clarity we will refer to fields as columns or fields interchangably. DBUtil will then be able create the database according to whichever rule are required internally. This leaves you free from worying about making your web application work in different database environments. You can get more detailed information from the ADODB documentation at http://phplens.com/lens/adodb/docs-datadict.htm∞

Column types

We can specify column type with this code:

• C: Varchar, capped to 255 characters.
• X: Larger varchar, capped to 4000 characters (to be compatible with Oracle).
• XL: For Oracle, returns CLOB, otherwise the largest varchar size.
• C2: Multibyte varchar
• X2: Multibyte varchar (largest size)
• B: BLOB (binary large object)
• D: Date (some databases do not support this, and we return a datetime type)
• T: Datetime or Timestamp
• L: Integer field suitable for storing booleans (0 or 1)
• I: Integer (mapped to I4)
• I1: 1-byte integer
• I2: 2-byte integer
• I4: 4-byte integer
• I8: 8-byte integer
• F: Floating point number
• N: Numeric or decimal number

See also this forum topice∞ about large fields and what size they are.

Column options

Each column can have additional options

• AUTO For autoincrement number. Emulated with triggers if not available. Sets NOTNULL also.
• AUTOINCREMENT Same as auto.
• KEY Primary key field. Sets NOTNULL also. Compound keys are supported.
• PRIMARY Same as KEY.
• DEF Synonym for DEFAULT for lazy typists.
• DEFAULT The default value. Character strings are auto-quoted unless the string begins and ends with spaces, eg ' SYSDATE '.
• NOTNULL If field is not null.
• DEFDATE Set default value to call function to get today's date.
• DEFTIMESTAMP Set default to call function to get today's datetime.
• NOQUOTE Prevents autoquoting of default string values.
• CONSTRAINTS Additional constraints defined at the end of the field definition.

Dictionary Example

Data dictionaries are specified as strings. So now we can provide an example:

    $sql = "id      I      NOTNULL AUTO PRIMARY,  
            title   C(255) NOTNULL DEFAULT '',    
            content C(255) NOTNULL DEFAULT '',
            online  L      NOTNULL DEFAULT 0";

This will create a table with 4 columns called id, title, content and online
id will be an integer field, NULL values not allowed, will be auto numbered (if supported), will be used as the primary index.
title will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
content will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
online will be a boolean suitable integer field limited to on digit, NULL values not allowed, and default value will be 0

You will see how the field type can take extra numbers in brackets, examples:

• C(10) means VARCHAR with 10 characters
• F(6.2) means a FLOAT type with nnnnnn.xx

You should become familiar with this format.

---

CategoryDeveloperDocs

Deletions

ADODB Data Dictionary

DBUtil uses the ADODB data dictionary. This is a way to specify a database table's structure in a generic way. For clarity we will refer to fields as columns or fields interchangably. DBUtil will then be able create the database according to whichever rule are required internally. This leaves you free from worying about making your web application work in different database environments. You can get more detailed information from the ADODB documentation at http://phplens.com/lens/adodb/docs-datadict.htm∞

Column types

We can specify column type with this code:

• C: Varchar, capped to 255 characters.
• X: Larger varchar, capped to 4000 characters (to be compatible with Oracle).
• XL: For Oracle, returns CLOB, otherwise the largest varchar size.
• C2: Multibyte varchar
• X2: Multibyte varchar (largest size)
• B: BLOB (binary large object)
• D: Date (some databases do not support this, and we return a datetime type)
• T: Datetime or Timestamp
• L: Integer field suitable for storing booleans (0 or 1)
• I: Integer (mapped to I4)
• I1: 1-byte integer
• I2: 2-byte integer
• I4: 4-byte integer
• I8: 8-byte integer
• F: Floating point number
• N: Numeric or decimal number

Column options

Each column can have additional options

• AUTO For autoincrement number. Emulated with triggers if not available. Sets NOTNULL also.
• AUTOINCREMENT Same as auto.
• KEY Primary key field. Sets NOTNULL also. Compound keys are supported.
• PRIMARY Same as KEY.
• DEF Synonym for DEFAULT for lazy typists.
• DEFAULT The default value. Character strings are auto-quoted unless the string begins and ends with spaces, eg ' SYSDATE '.
• NOTNULL If field is not null.
• DEFDATE Set default value to call function to get today's date.
• DEFTIMESTAMP Set default to call function to get today's datetime.
• NOQUOTE Prevents autoquoting of default string values.
• CONSTRAINTS Additional constraints defined at the end of the field definition.

Dictionary Example

Data dictionaries are specified as strings. So now we can provide an example:

    $sql = "id      I      NOTNULL AUTO PRIMARY,  
            title   C(255) NOTNULL DEFAULT '',    
            content C(255) NOTNULL DEFAULT '',
            online  L      NOTNULL DEFAULT 0";

This will create a table with 4 columns called id, title, content and online
id will be an integer field, NULL values not allowed, will be auto numbered (if supported), will be used as the primary index.
title will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
content will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
online will be a boolean suitable integer field limited to on digit, NULL values not allowed, and default value will be 0

You will see how the field type can take extra numbers in brackets, examples:

• C(10) means VARCHAR with 10 characters
• F(6.2) means a FLOAT type with nnnnnn.xx

You should become familiar with this format.

---

CategoryDeveloperDocs

---

Oldest known version of this page was edited on 2008-09-05 03:20:20 by AllKnightAccess [ ]

ADODB Data Dictionary

DBUtil uses the ADODB data dictionary. This is a way to specify a database table's structure in a generic way. For clarity we will refer to fields as columns or fields interchangably. DBUtil will then be able create the database according to whichever rule are required internally. This leaves you free from worying about making your web application work in different database environments. You can get more detailed information from the ADODB documentation at http://phplens.com/lens/adodb/docs-datadict.htm∞

Column types

We can specify column type with this code:

• C: Varchar, capped to 255 characters.
• X: Larger varchar, capped to 4000 characters (to be compatible with Oracle).
• XL: For Oracle, returns CLOB, otherwise the largest varchar size.
• C2: Multibyte varchar
• X2: Multibyte varchar (largest size)
• B: BLOB (binary large object)
• D: Date (some databases do not support this, and we return a datetime type)
• T: Datetime or Timestamp
• L: Integer field suitable for storing booleans (0 or 1)
• I: Integer (mapped to I4)
• I1: 1-byte integer
• I2: 2-byte integer
• I4: 4-byte integer
• I8: 8-byte integer
• F: Floating point number
• N: Numeric or decimal number

Column options

Each column can have additional options

• AUTO For autoincrement number. Emulated with triggers if not available. Sets NOTNULL also.
• AUTOINCREMENT Same as auto.
• KEY Primary key field. Sets NOTNULL also. Compound keys are supported.
• PRIMARY Same as KEY.
• DEF Synonym for DEFAULT for lazy typists.
• DEFAULT The default value. Character strings are auto-quoted unless the string begins and ends with spaces, eg ' SYSDATE '.
• NOTNULL If field is not null.
• DEFDATE Set default value to call function to get today's date.
• DEFTIMESTAMP Set default to call function to get today's datetime.
• NOQUOTE Prevents autoquoting of default string values.
• CONSTRAINTS Additional constraints defined at the end of the field definition.

Dictionary Example

Data dictionaries are specified as strings. So now we can provide an example:

This will create a table with 4 columns called id, title, content and online
id will be an integer field, NULL values not allowed, will be auto numbered (if supported), will be used as the primary index.
title will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
content will be a VARCHAR field with a limit of 255 characters, NULL values not allowed, and default value will be
online will be a boolean suitable integer field limited to on digit, NULL values not allowed, and default value will be 0

You will see how the field type can take extra numbers in brackets, examples:

• C(10) means VARCHAR with 10 characters
• F(6.2) means a FLOAT type with nnnnnn.xx

You should become familiar with this format.

---

CategoryDeveloperDocs
CategoryReference