Difference between revisions of "Database patterns"
| Line 26: | Line 26: | ||
A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a [[#DBT_TOKEN|DBT_TOKEN]]. | A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a [[#DBT_TOKEN|DBT_TOKEN]]. | ||
| + | |||
| + | == name == | ||
| + | |||
| + | The [[#name|name]] tables contain names. | ||
| + | |||
| + | A name table is named 't_name_{name}'. | ||
| + | |||
| + | A name table must contain a 'a_{name}' column which contains the case-insensitive name. The 'a_{name}' column is usually a [[#DBT_ASCII_CI|DBT_ASCII_CI]] or [[#DBT_UTF8_CI|DBT_UTF8_CI]]. | ||
| + | |||
| + | A name table must contain a 'a_{name}_lower' column which contains the case-sensitive lowercase name. The 'a_{name}_lower' column is usually a [[#DBT_ASCII_BIN|DBT_ASCII_BIN]] or [[#DBT_UTF8_BIN|DBT_UTF8_BIN]]. | ||
= Database types = | = Database types = | ||
Revision as of 07:17, 18 March 2020
Patterns
about
The about tables contain database meta data.
An about table is named 't_about_{name}'.
lookup
The lookup tables contain hard-coded reference data.
A lookup table is named 't_lookup_{name}'.
A lookup table must contain a 'a_{name}_enum' column which identifies a row. The 'a_{name}_enum' column is usually be a DBT_UINT8.
If an optional 'a_{name}_char' column exists it must be of type DBT_ASCII_CHAR_BIN. The PHPBOM library will use lowercase char codes and applications can use uppercase char codes.
If you're extending a PHPBOM lookup table you should code from the max enum down.
token
The token tables contain tokens.
A token table is named 't_token_{name}'.
A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a DBT_TOKEN.
name
The name tables contain names.
A name table is named 't_name_{name}'.
A name table must contain a 'a_{name}' column which contains the case-insensitive name. The 'a_{name}' column is usually a DBT_ASCII_CI or DBT_UTF8_CI.
A name table must contain a 'a_{name}_lower' column which contains the case-sensitive lowercase name. The 'a_{name}_lower' column is usually a DBT_ASCII_BIN or DBT_UTF8_BIN.
Database types
DBT_UINT8
A DBT_UINT8 is an integer from 0 to 255.
DBT_ASCII_CHAR_BIN
A DBT_ASCII_CHAR_BIN is a single case-sensitive ASCII character.
DBT_TOKEN
A DBT_TOKEN is a case-sensitive 32-character alphanumeric ASCII string.
