phpBB DB type map [3.3.x]

https://phpbbstudio.com/ext/phpbbstudio/studio/styles/all/theme/images/studio-round-white.svg Development 13-Sep-2022T17:37:10 phpBB Studio https://phpbbstudio.com/ext/phpbbstudio/studio/styles/all/theme/images/studio-round-orange.svg https://phpbbstudio.com/ext/phpbbstudio/studio/styles/all/theme/images/studio-round-orange.svg

I revived an important page for developers, which phpBB mistakenly deleted from the Wiki with it. Using ‘MD pipe tables’ with some CSS.


Introduction

note

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.

Type Map

tip

Note that with some commands you may enter the Zerofill, example ‘INT:11’, if the field is a numeric one. 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

CommandMySQL eqStorage Range (on MySQL)Range
TINT:%dtinyint(%d)from -128 to 127Signed
INT:%dint(%d)from -2,147,483,648 to 2,147,483,647Signed
BINTbigint(20)from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807Signed
USINTsmallint(4)from 0 to 65,535Unsigned
UINTmediumint(8)from 0 to 16,777,215Unsigned
UINT:%dint(%d)from 0 to 4,294,967,295Unsigned
ULINTint(10)from 0 to 4,294,967,295Unsigned

Decimal

CommandMySQL eqStorage Range (on MySQL)Range
DECIMALdecimal(5,2)from -999.99 to 999.99Signed
DECIMAL:%ddecimal(%d, 2)-(%d - 2 digits to the left of the decimal).99 to (%d - 2 digits to the left of the decimal).99Signed
PDECIMALdecimal(6,3)from -999.999 to 999.999Signed
PDECIMAL:%ddecimal(%d,3)-(%d - 3 digits to the left of the decimal).999 to (%d - 3 digits to the left of the decimal)9.99Signed

Text

note

These should only be used for ASCII characters.
If you plan to use it for something like message text read the Unicode Text section

CommandMySQL eqExplain
VCHARvarchar(255)
VCHAR:%dvarchar(%d)
CHAR:%dchar(%d)
XSTEXTtexttext for storing 100 characters
STEXTtexttext for storing 255 characters
TEXTtexttext for storing 3000 characters
MTEXTmediumtext

Unicode Text

CommandMySQL eqExplain
VCHAR_UNIvarchar(255)text for storing 255 characters (normal input field with a max of 255 single-byte chars)
VCHAR_UNI:%dvarchar(%d)text for storing %d characters (normal input field with a max of %d single-byte chars)
XSTEXT_UNIvarchar(100)text for storing 100 characters (topic_title for example)
STEXT_UNIvarchar(255)text for storing 255 characters (normal input field with a max of 255 single-byte chars)
TEXT_UNItexttext for storing 3000 characters (short text, descriptions, comments, etc.)
MTEXT_UNImediumtext(post text, large text)

Miscellaneous

CommandMySQL eqExplain
BOOLtinyint(1) UNSIGNEDStoring boolean values (true/false)
TIMESTAMPint(11) UNSIGNEDFor storing UNIX timestamps
VCHAR_CIvarchar(255)varchar_ci for postgresql, others VCHAR
VARBINARYvarbinary(255)Binary storage