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:

$table = $this->FreePBX->Database->migrate("meetme");

You will then be passed back a "Database\Migrate" object to work with.

Adding/Modifying Columns

Columns are updated through a multi dimensional array mechanism, where the "key" is the column name and the values are an array of options

$cols = array(
	"exten" => array(
		"type" => "string",
		"length" => 50
	)
);

The list of options is as follows:

Adding/Modifying Indexes

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

$indexes = array(
	"index1" => array(
		"type" => "unique",
		"cols" => array(
			"music",
			"users",
			"language"
		)
	)
);

The list of options is as follows:

Apply

To apply your two arrays you simply run the modify method against the table object

$table->modify($cols,$indexes);

Or if you have no indexes:

$table->modify($cols);

Complete

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):

$table = $this->FreePBX->Database->migrate("meetme");
$cols = array(
	"exten" => array(
		"type" => "string",
		"length" => 50,
		"primaryKey" => true
	),
	"options" => array(
		"type" => "string",
		"length" => 15,
		"notnull" => false,
	),
	"userpin" => array(
		"type" => "string",
		"length" => 50,
		"notnull" => false,
	),
	"adminpin" => array(
		"type" => "string",
		"length" => 50,
		"notnull" => false,
	),
	"description" => array(
		"type" => "string",
		"length" => 50,
		"notnull" => false,
	),
	"joinmsg_id" => array(
		"type" => "integer",
		"notnull" => false,
	),
	"music" => array(
		"type" => "string",
		"length" => 80,
		"notnull" => false,
	),
	"users" => array(
		"type" => "smallint",
		"unsigned" => false,
		"default" => 0,
		"notnull" => false
	),
	"language" => array(
		"type" => "string",
		"length" => 10,
		"default" => "",
	),
);
$table->modify($cols);

Which looks like this in MySQL

mysql> describe meetme;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| exten       | varchar(50) | NO   | PRI | NULL    |       |
| options     | varchar(15) | YES  |     | NULL    |       |
| userpin     | varchar(50) | YES  |     | NULL    |       |
| adminpin    | varchar(50) | YES  |     | NULL    |       |
| description | varchar(50) | YES  |     | NULL    |       |
| joinmsg_id  | int(11)     | YES  |     | NULL    |       |
| music       | varchar(80) | YES  |     | NULL    |       |
| users       | smallint(6) | YES  |     | 0       |       |
| language    | varchar(10) | NO   |     |         |       |
+-------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)