Latest version of https://wiki.phpbb.com/Database_Type_Map

gistfile1.txt

The Database Type Map is used to create installation scripts using [[Umil]] and is used when altering tables in the db_tools class.

==Introduction==
The Database Type Map was designed to make creating installations for multiple database systems a simple task.  Instead of having to write specific install instructions for each separate database system you can write one set of instructions and, using the Database Type Map and db_tools, alter any supported database system with a single command.

With the database table creation support in [https://github.com/phpbb/umil Umil] you can make a single set of instructions for creating a new database table and it will handle the rest, which includes creating the correct query for the specific database system and running the query.

==Type Map==
Note that with some commands you may enter the Zerofill, example 'INT:11', if the field is a numeric one.<br />

[https://www.tutorialspoint.com/what-is-the-benefit-of-zerofill-in-mysql More Information on Zerofill]
<br /><br />
With some you may enter the length of the field, example 'VARCHAR:255'.  This will make a varchar(255) column in MySQL.

In all of the fields supporting this, they will have a colon followed by %d meaning any number may fill the space of the %d.

===Numeric===
{|border="1" cellspacing="0" cellpadding="3"
! Command
! MySQL Equivalent 
! Storage Range (on MySQL)
|-
| TINT:%d
| tinyint(%d)
| -128 to 127
|-
| INT:%d
| int(%d)
| -2,147,483,648 to 2,147,483,648
|-
| BINT
| bigint(20)
| -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808
|-
| USINT
| smallint(4) UNSIGNED
| 0 to 65,535
|-
| UINT
| mediumint(8) UNSIGNED
| 0 to 16,777,215
|-
| UINT:%d
| int(%d) UNSIGNED
| 0 to 4,294,967,295
|-
| ULINT
| int(10) UNSIGNED
| 0 to 4,294,967,295
|}

===Decimal===
{|border="1" cellspacing="0" cellpadding="3"
! Command
! MySQL Equivalent 
! Storage Range (on MySQL)
|-
| DECIMAL
| decimal(5,2)
| -999.99 to 999.99
|-
| DECIMAL:%d
| decimal(%d, 2)
| -(%d - 2 digits to the left of the decimal).99 to (%d - 2 digits to the left of the decimal).99
|-
| PDECIMAL
| decimal(6,3)
| -999.999 to 999.999
|-
| PDECIMAL:%d
| decimal(%d,3)
| -(%d - 3 digits to the left of the decimal).999 to (%d - 3 digits to the left of the decimal).999
|}

===Text===
These should only be used for ASCII characters.  If you plan to use it for something like message text read the Unicode Text section
{|border="1" cellspacing="0" cellpadding="3"
! Command
! MySQL Equivalent
! Explain
|-
| VCHAR
| varchar(255)
|
|-
| VCHAR:%d
| varchar(%d)
|
|-
| CHAR:%d
| char(%d)
|
|-
| XSTEXT
| text
| text for storing 100 characters
|-
| STEXT
| text
| text for storing 255 characters
|-
| TEXT
| text
| text for storing 3000 characters
|-
| MTEXT
| mediumtext
|
|}

===Unicode Text===
{|border="1" cellspacing="0" cellpadding="3"
! Command
! MySQL Equivalent
! Explain
|-
| VCHAR_UNI
| varchar(255)
| text for storing 255 characters (normal input field with a max of 255 single-byte chars)
|-
| VCHAR_UNI:%d
| varchar(%d)
| text for storing %d characters (normal input field with a max of %d single-byte chars)
|-
| XSTEXT_UNI
| varchar(100)
| text for storing 100 characters (topic_title for example)
|-
| STEXT_UNI
| varchar(255)
| text for storing 255 characters (normal input field with a max of 255 single-byte chars)
|-
| TEXT_UNI
| text
| text for storing 3000 characters (short text, descriptions, comments, etc.)
|-
| MTEXT_UNI
| mediumtext
| (post text, large text)
|}

===Miscellaneous===
{|border="1" cellspacing="0" cellpadding="3"
! Command
! MySQL Equivalent
! Explain
|-
| BOOL
| tinyint(1) UNSIGNED
| Storing boolean values (true/false)
|-
| TIMESTAMP
| int(11) UNSIGNED
| For storing UNIX timestamps
|-
| VCHAR_CI
| varchar(255)
| varchar_ci for postgresql, others VCHAR
|-
| VARBINARY
| varbinary(255)
| Binary storage
|}

<br />
[[Category:Database]]