Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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:

  • type (string): The column type, can be: string, binary, decimal, float, smallint, integer and bigint
  • primaryKey (boolean): When set to true this column becomes the primary key
  • notnull (boolean): Whether the column is nullable or not. Defaults to true.

  • default (integer|string): The default value of the column if no value was specified. Defaults to null.

  • autoincrement (boolean): Whether this column should use an autoincremented value if no value was specified. Only applies to Doctrine’s smallintinteger and bigint types. Defaults to false.

  • length (integer): The maximum length of the column. Only applies to Doctrine’s string and binary types. Defaults to null and is evaluated to 255 in the platform.

  • fixed (boolean): Whether a string or binary Doctrine type column has a fixed length. Defaults to false.

  • precision (integer): The precision of a Doctrine decimal or float type column that determines the overall maximum number of digits to be stored (including scale). Defaults to 10.

  • scale (integer): The exact number of decimal digits to be stored in a Doctrine decimal or float type column. Defaults to 0.

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

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:

  • type (string): The index type, can be: unique or index
  • cols (array): The columns this key will affect

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)
  • No labels