Elxis database

Mon December 10, 2012 14:15 by Elxis Team

Working with the database in Elxis and the elxisDatabase library.

Elxis uses PHP's native PDO extension for accessing and managing the database. Elxis has built in support for many database types sych as MySQL, PostgreSQL, MsSQL, Oracle, Firebird and SQLite although it is thoughrogly tested only with MySQL. In this article we will provide a general presentation of the PDO's implementation in Elxis and documentation for the main, elxisDatabase class.

General presentation

The Elxis database library (elxisDatabase) is the main class for accessing the database. Elxis has a number of help classes under the includes/libraries/elxis/database/ path which are used for various tasks. Let's see how the whole system is organized.

elxisDatabase

File: includes/libraries/elxis/database.class.php
Get elxisDatabase instance by calling the getDB method of Elxis Factory:

$db = eFactory::getDB();

Abstract class elxisDbAdapter

File: includes/libraries/elxis/database/adapter.class.php

elxisDbAdapter implementations

Dir: includes/libraries/elxis/database/adapters/
Class name format: elxisDatabase typeAdapter

//Example:

class elxisMysqlAdapter extends elxisDbAdapter

elxisPDOStatement

Class elxisPDOStatement is an extension of the PHP's PDOStatement class.
File: includes/libraries/elxis/database/statement.class.php

Abstract class elxisDbTable

A class for handling database tables.
File: includes/libraries/elxis/database/table.class.php

elxisDbTable implementations

Dir: includes/libraries/elxis/database/tables/
Implementations for built-in extensions can be found ib the directory shown above. For the third party extensions it is up to the developer where he will place them inside his extension.
Class name format: table nameDbTable (actually table name can be anything but we recommend using something representative of the actual database table name)

//Example:

class menuDbTable extends elxisDbTable

Abstract class elxisDbImporter

A class for importing data into the database.
File: includes/libraries/elxis/database/importer.class.php

elxisDbImporter implementations

Dir: includes/libraries/elxis/database/importers/
Class name format: elxisDatabase typeImporter

//Example:

class elxisMysqlImporter extends elxisDbImporter

The elxisDatabase class

The public methods of the elxisDatabase class

__construct

A magic constructor for the elxisDatabase class. You can connect to a database directly from the constructor or from the connect method (see below). $params contains an array of connection settings. If not defined the values from the configuration file are used. $options is an optional array of parameters to be passed to the PDO class on connection. $connect (true/false) connect immediately to the database or not.

public function __construct($params=array(), $options=array(), $connect=true)

connect

Connect to the database

public function connect($dsn, $user, $pass, $options=array(), $silent=false)

dsn A ready to use connection DSN string for PDO. If empty Elxis will create it automatically.
user Database username
pass Database password
options Optional array of parameters to be passed to the PDO class on connection.
silent If true on connection fail the method will return false and set an error message. If false, Elxis will though a system error and an exit page will be displayed.

disconnect

Disconnect from the database

public function disconnect()

getAvailableDrivers

Returns an array of PDO's available drivers on the system.

public function getAvailableDrivers()

getAttribute

Returns a PDO's attribute value.

public function getAttribute($attribute)

setAttribute

Sets the value of a PDO attribute.

public function setAttribute($attribute, $value)

version

Returns current adapter (database type) version.

public function version()

lastInsertId

Get the last Id generated by an auto-increment column.

public function lastInsertId($tableName=null, $primaryKey=null)

quote

Quotes a string depending on the current adapter.

public function quote($value, $parameter_type = PDO::PARAM_STR)

quoteId

Quotes an identifier (database table name or column name) depending on the current adapter.

public function quoteId($string)

prepare

Prepares a statement for execution and returns a statement object. It calls internally the prepare method of the elxisPDOStatement class.

public function prepare($sql, $options=array(), $prefix='#__')

prepareLimit

As for prepare but with limit capabilities (PDO lucks support for limiting results).

public function prepareLimit($sql, $offset=-1, $limit=-1, $options=array(), $prefix='#__')

replacer

Replaces SQL prefix with the real database tables prefix. Normally this is called internally by prepare and prepareLimit methods so you never have to use it.

public function replacer($sql, $prefix='#__')

limiter

Adds limit instructions to an SQL query. Normally this is called internally by prepareLimit method so you never have to use it.

public function limiter($sql, $offset=-1, $limit=-1)

listTables

Returns an array of table names in the database.

public function listTables()

backup

Performs an SQL backup on selected or all database tables. Returns SQL data on success or 0 (fail), -1 (not supported database), -2 (Invalid/Insuficient backup parameters), -3 (other db specific error)

public function backup($userparams = array())

import

Import an SQL file into the database. The file should be properly formatted for the current database type. Use #__ in SQL file as tables prefix replacements. Note that you can alternatively use the elxisDbImporter library.

public function import($sqlfile)

query

Execute a query and return result as PDO statement.

public function query($statement, $params = array(), $options = array())

exec

Execute SQL query and return number of affected rows.

public function exec($sql, $prefix='#__')

Other methods

//Add SQL query into Elxis performance monitor

public function monitor($sql='')

//Bigin transaction

public function beginTransaction()

//Commit transaction

public function commit()

//Rollback transaction

public function rollBack()

//Returns true if a transaction is active, else false.

public function inTransaction()

//Returns database type

public function getType()

//Returns database host

public function getHost()

//Returns database port

public function getPort()

//Returns database name

public function getName()

//Returns DSN connection string

public function getDSN()

//Returns database scheme for database types such as sqlite.

public function getScheme()

//Returns true if the connection is persistent, else false

public function getPersistent()

//Returns last error code

public function getErrorCode()

//Returns last PDO's error code

public function errorCode()

//Returns last error message

public function getErrorMsg()

//Clears all errors

public function clearErrors()

//Returns PDO's errorInfo result

public function errorInfo()

//Returns backtrace message

public function getBackTrace()

Examples

Backup selected database tables

// Backup tables elx_content and elx_categories

$userparams = array('tables' => array('elx_content', 'elx_categories'));

// Execute the backup and get the generated SQL into the $sql variable.

$sql = eFactory::getDB()->backup($userparams);

// Create a zip file, put inside the sql data and save zip file into Elxis repository.

$elxis = eFactory::getElxis();

$repo_path = $elxis->getConfig('REPO_PATH');

if ($repo_path == '') { $repo_path = ELXIS_PATH.'/repository'; }

$archive = $repo_path.'/backup/mybackup.zip';

$data = array('mybackup.sql' => $sql);

$zip = $elxis->obj('zip');

if ($zip->zip($archive, null, $data)) {

echo 'Zip created successfully!';

} else {

echo $zip->getError();

}

It has been read 6036 times

Tags: database elxisdatabase prepared statements pdo database tables

Prepared statements

Elxis database

General presentation

elxisDatabase

Abstract class elxisDbAdapter

elxisDbAdapter implementations

elxisPDOStatement

Abstract class elxisDbTable

elxisDbTable implementations

Abstract class elxisDbImporter

elxisDbImporter implementations

The elxisDatabase class

__construct

connect

disconnect

getAvailableDrivers

getAttribute

setAttribute

version

lastInsertId

quote

quoteId

prepare

prepareLimit

replacer

limiter

listTables

backup

import

query

exec

Other methods

Examples

Backup selected database tables

Developers guide

Latest Articles