Difference between revisions of "Database patterns"

From ProgClub
Jump to: navigation, search
Line 142: Line 142:
  
 
A [[#DBT_HASH_BIN|DBT_HASH_BIN]] is a case-sensitive 28-byte binary string containing a SHA512/224 hash value.
 
A [[#DBT_HASH_BIN|DBT_HASH_BIN]] is a case-sensitive 28-byte binary string containing a SHA512/224 hash value.
 +
 +
== DBT_HASH_HEX ==
 +
 +
A [[#DBT_HASH_HEX|DBT_HASH_HEX]] is a case-insensitive 56-character hexadecimal string containing a SHA512/224 hash value.
  
 
== DBT_TOKEN ==
 
== DBT_TOKEN ==

Revision as of 09:26, 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.

The 'a_{name}_enum' must be the primary key.

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 as PHPBOM will code from the min enum up.

token

The token tables contain tokens.

A token table is named 't_token_{name}'.

A token table must contain a 'a_{name}_id' column which contains the ID for the token. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

The 'a_{name}_id' column must be the primary key.

A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a DBT_TOKEN.

There should be a unique index on the 'a_{name}' column.

name

The name tables contain names. Names are case-insensitive strings.

A name table is named 't_name_{name}'.

A name table must contain a 'a_{name}_id' column which contains the ID for the name. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

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.

particle

The particle tables contain particles. A particle table maps a wide column to a narrow column.

A particle table is named 't_particle_{name}'.

A particle table must contain a 'a_{name}_id' column which contains the narrow ID for the particle. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A particle table must contain a 'a_{name}' column which is the wide value. When particle 'a_{name}' columns are strings they should be case-sensitive. If you want a case-insensitive string use a name table instead.

string

The string tables contain strings. A string table maps a wide column to a narrow column. You would usually use a string table if a particle table is too small.

A string table is named 't_string_{name}'.

A string table must contain a 'a_{name}_id' column which contains the narrow ID for the string. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A string table must contain a 'a_{name}' column which is the wide string value. The 'a_{name}' column should be case-insensitive. Case-sensitivity is ensured by the 'a_{name}_hash_bin' column, so the 'a_{name}' column can use a case-insensitive collation.

A string table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the string value.

jzon

The jzon tables contain compressed JSON.

A jzon table is named 't_jzon_{name}'.

A jzon table must contain a 'a_{name}_id' column which contains the narrow ID for the compressed data. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A jzon table must contain a 'a_{name}' column which is the compressed binary data. The 'a_{name}' column is usually a DBT_MLOB.

A jzon table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the compressed data.

zlob

The zlob tables contain compressed blobs.

A zlob table is named 't_zlob_{name}'.

A zlob table must contain a 'a_{name}_id' column which contains the narrow ID for the compressed blob. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A zlob table must contain a 'a_{name}' column which is the compressed binary data. The 'a_{name}' column is usually a DBT_MLOB.

A zlob table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the compressed blob.

Database types

DBT_ID8

A DBT_ID8 is an auto generated integer from 1 to 255.

DBT_ID16

A DBT_ID16 is an auto generated integer from 1 to 65,535.

DBT_ID24

A DBT_ID24 is an auto generated integer from 1 to 16,777,215.

DBT_ID32

A DBT_ID32 is an auto generated integer from 1 to 4,294,967,295.

DBT_ID64

A DBT_ID64 is an auto generated integer from 1 to 264-1.

DBT_UINT8

A DBT_UINT8 is an integer from 0 to 255.

DBT_UINT16

A DBT_UINT16 is an integer from 1 to 65,535.

DBT_UINT24

A DBT_UINT24 is an integer from 1 to 16,777,215.

DBT_UINT32

A DBT_UINT32 is an integer from 1 to 4,294,967,295.

DBT_UINT64

A DBT_UINT64 is an integer from 1 to 264-1.

DBT_ASCII_CHAR_BIN

A DBT_ASCII_CHAR_BIN is a single case-sensitive ASCII character.

DBT_HASH_BIN

A DBT_HASH_BIN is a case-sensitive 28-byte binary string containing a SHA512/224 hash value.

DBT_HASH_HEX

A DBT_HASH_HEX is a case-insensitive 56-character hexadecimal string containing a SHA512/224 hash value.

DBT_TOKEN

A DBT_TOKEN is a case-sensitive 32-character alphanumeric ASCII string.

DBT_CREATED_ON

A DBT_CREATED_ON is the timestamp of when the record was created.

DBT_UPDATED_ON

A DBT_UPDATED_ON is the timestamp of when the record was last updated.

Standards

As standard all database tables should finish with DBT_CREATED_ON and DBT_UPDATED_ON columns. Even for insert-only data. The logic being that if insert-only data is modified then the DBT_CREATED_ON !== DBT_UPDATED_ON discrepancy might help reveal the issue.