phpBB

Development Wiki

Database Type Map

From phpBB Development Wiki

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 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.

More Information on Zerofill

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

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

Decimal

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

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

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

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