This only applies to Framework 13.0.121 or higher
If you haven't already done so please brush up on Database in FreePBX 12 which also applies to this article
Starting with FreePBX 13 the development team has completely removed the need for PearDB. PearDB was a Database abstraction layer that FreePBX used since it's inception. Over the years the project was depreciated in favor of MDB2, however at the same time the PHP developers added a new abstraction layer called PDO. FreePBX 13 now uses the abstraction layer called PDO with a wrapper for old PearDB calls.
Our complete PearDB to PDO wrapper can be viewed here: http://git.freepbx.org/projects/FREEPBX/repos/framework/browse/amp_conf/htdocs/admin/libraries/DB.class.php
It's 100% open source. Feel free to use it in whatever project you need
Creating/Altering/Updating a Database
Starting in Framework 13.0.121 you can now let FreePBX automatically/automagically maintain your Database table structure. Simply call the "migrate" method of FreePBX's database abstraction engine and pass the parameter the name of your table:
You will then be passed back a "Database\Migrate" object to work with.
Columns are updated through a multi dimensional array mechanism, where the "key" is the column name and the values are an array of options
The list of options is as follows:
- type (string): The column type, can be:
string: Type that maps a SQL VARCHAR to a PHP string.
integer: Type that maps a SQL INT to a PHP integer.
smallint: Type that maps a database SMALLINT to a PHP integer.
bigint: Type that maps a database BIGINT to a PHP string.
boolean: Type that maps a SQL boolean or equivalent (TINYINT) to a PHP boolean.
decimal: Type that maps a SQL DECIMAL to a PHP string.
date: Type that maps a SQL DATETIME to a PHP DateTime object.
time: Type that maps a SQL TIME to a PHP DateTime object.
datetime: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object.
datetimetz: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object with timezone.
text: Type that maps a SQL CLOB to a PHP string.
float: Type that maps a SQL Float (Double Precision) to a PHP double. IMPORTANT: Works only with locale settings that use decimal points as separator.
guid: Type that maps a database GUID/UUID to a PHP string. Defaults to varchar but uses a specific type if the platform supports it.
blob: Type that maps a SQL BLOB to a PHP resource stream
- primaryKey (boolean): When set to true this column becomes the primary key
notnull (boolean): Whether the column is nullable or not. Defaults to
default (integer|string): The default value of the column if no value was specified. Defaults to
autoincrement (boolean): Whether this column should use an autoincremented value if no value was specified. Only applies to Doctrine’s
biginttypes. Defaults to
length (integer): The maximum length of the column. Only applies to Doctrine’s
binarytypes. Defaults to
nulland is evaluated to
255in the platform.
fixed (boolean): Whether a
binaryDoctrine type column has a fixed length. Defaults to
precision (integer): The precision of a Doctrine
floattype column that determines the overall maximum number of digits to be stored (including scale). Defaults to
scale (integer): The exact number of decimal digits to be stored in a Doctrine
floattype column. Defaults to
customSchemaOptions (array): Additional options for the column that are supported by all vendors:
unique (boolean): Whether to automatically add a unique constraint for the column. Defaults to
Indexes (Besides the Primary Key) are updated through a multi dimensional array mechanism, where the "key" is the index name and the values are an array of options
The list of options is as follows:
- type (string): The index type, can be: unique or index
- cols (array): The columns this key will affect
To apply your two arrays you simply run the modify method against the table object
Or if you have no indexes:
Make sure to unset when you are done to free up memory
When finished you will have something that looks like this (Note this is NOT how the meetme table is constructed. It's for demo purposes only):
Which looks like this in MySQL