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.

1 Introduction
2 Type Map

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
VCHAR	varchar(255)
VCHAR:%d	varchar(%d)
CHAR:%d	char(%d)
XSTEXT	text
STEXT	text
TEXT	text
MTEXT	mediumtext

Unicode Text

Command	MySQL Equivalent
VCHAR_UNI	varchar(255)
VCHAR_UNI:%d	varchar(%d)
XSTEXT_UNI	varchar(100)
STEXT_UNI	varchar(255)
TEXT_UNI	text
MTEXT_UNI	mediumtext

Miscellaneous

Command	MySQL Equivalent	Purpose
BOOL	tinyint(1) UNSIGNED	Storing boolean values (true/false)
TIMESTAMP	int(11) UNSIGNED	For storing UNIX timestamps
VCHAR_CI	varchar(255)
VARBINARY	varbinary(255)	Binary storage