Xap

MySQL Rapid Development Engine for PHP 5.5+

Xap requirements:

1. PHP 5.5+
2. PHP PDO database extension
3. Database table names cannot include characters ., /, : or (whitespace) and cannot start with [

Install Xap options:

• Git clone: git clone https://github.com/shayanderson/xap.git
• Subversion checkout URL: https://github.com/shayanderson/xap/trunk
  • Subversion checkout library files only: https://github.com/shayanderson/xap/trunk/lib/Xap
• Download ZIP file

Here is a list of Xap commands:

• add - insert record (can also use insert)
• call - call stored procedure or function (and call_affected and call_rows)
• cache - set single cache expire time
• close - close the database connection
• columns - show table columns
• commit - commit transaction
• count - count table records
• debug - get debug info for connections
• del - delete record(s) (can also use delete)
• error - check if error has occurred
• error_last - get last error, when error has occurred
• exists - check if record exists
• id - get last insert ID
• limit - global max limit for queries
• log - get debug log (debugging must be turned on)
• log_handler - add log message to database log (debugging must be turned on)
• mod - update record(s) (can also use update)
• pagination - get/set pagination params
• query - execute manual query
• replace - replace record
• rollback - rollback transaction
• tables - show database tables
• transaction - start transaction
• truncate - truncate table

Xap supports:

• Custom log handler
• Custom error handler
• Query options
• Multiple database connections
• Pagination
• Pagination Helper Class
• Data Modeling (ORM)
• Data Decorators
• Caching

Quick Start

Edit the xap.bootstrap.php file and add your database connection params:

// register database connection
xap([
	// database connection params
	'host' => 'localhost',
	'database' => 'test',
	'user' => 'myuser',
	'password' => 'mypass',
	'errors' => true, // true: Exceptions, false: no Exceptions, use error methods
	'debug' => true // turn logging on/off
]);

Next, include the bootstrap file in your project file:

require_once './xap.bootstrap.php';

Now execute SELECT query:

$user = xap('users WHERE id = ?', [14]); // same as "SELECT * FROM users WHERE id = '14'"
if($user) echo $user->fullname; // print record column value

Commands

Select

Simple select queries examples:

$r = xap('users'); // SELECT * FROM users
$r = xap('users(fullname, email)'); // SELECT fullname, email FROM users
$r = xap('users LIMIT 1'); // SELECT * FROM users LIMIT 1
$r = xap('users WHERE is_active = 1'); // SELECT * FROM users WHERE is_active = 1

Select Where

Select query with named parameters:

// SELECT fullname, email FROM users WHERE is_active = '1'
//	AND fullname = 'Shay Anderson'
$r = xap('users(fullname, email) WHERE is_active = :active AND fullname = :name'
	. ' LIMIT 2', ['active' => 1, 'name' => 'Shay Anderson']);

Select query with question mark parameters:

// SELECT fullname, email FROM users WHERE is_active = 1
//	AND fullname = 'Shay Anderson' LIMIT 2
$r = xap('users(fullname, email) WHERE is_active = ? AND fullname = ? LIMIT 2',
	[1, 'Shay Anderson']);

Select Distinct

Select distinct example query:

$r = xap('users(fullname)/distinct'); // SELECT DISTINCT fullname FROM users

Insert

Simple insert example:

// INSERT INTO users (fullname, is_active, created)
//	VALUES('Name Here', '1', NOW())
$affected_rows = xap('users:add', ['fullname' => 'Name Here', 'is_active' => 1,
	'created' => ['NOW()']]);

// can also use action ':insert'
// xap('users:insert', ...);

The replace command can also be used, for example:

// REPLACE INTO users (id, fullname, is_active, created)
//	VALUES(5, 'Name Here', '1', NOW())
$affected_rows = xap('users:replace', ['id' => 5, 'fullname' => 'Name Here',
	'is_active' => 1, 'created' => ['NOW()']]);

Insert with Insert ID

Insert query and get insert ID:

// INSERT INTO users (fullname, is_active, created) VALUES('Name Here', '1', NOW())
xap('users:add', ['fullname' => 'Name Here', 'is_active' => 1,
	'created' => ['NOW()']]);

// get insert ID
$insert_id = xap(':id');

Insert Ignore

Insert ignore query example:

// INSERT IGNORE INTO users (user_id, fullname) VALUES('3', 'Name Here')
xap('users:add/ignore', ['user_id' => 3, 'fullname' => 'Name Here']);

Inserting Objects

Insert into table using object instead of array:

// note: all class public properties must be table column names
class User
{
	public $user_id = 70;
	public $fullname = 'Name';
	public $created = ['NOW()'];
}

$affected_rows = xap('users:add', new User);

Update

Simple update query example:

// UPDATE users SET fullname = 'Shay Anderson' WHERE user_id = '2'
$affected_rows = xap('users:mod WHERE user_id = :user_id',
	['fullname' => 'Shay Anderson'], ['user_id' => 2]);

// can also use action ':update'
// xap('users:update', ...);

When using the mod (or update) command all params must be named params like :my_param and not question mark parameters

Update Ignore

Update ignore query example:

// UPDATE IGNORE users SET user_id = '3' WHERE user_id = 6
$affected_rows = xap('users:mod/ignore WHERE user_id = 6', ['user_id' => 3]);

Delete

Delete query examples:

// delete all
$affected_rows = xap('users:del'); // DELETE FROM users

// can also use action ':delete'
// xap('users:delete', ...);

// DELETE FROM users WHERE is_active = 1
$affected_rows = xap('users:del WHERE is_active = 1');

// DELETE FROM users WHERE user_id = '29'
$affected_rows = xap('users:del WHERE user_id = ?', [29]);

Delete Ignore

Delete ignore query example:

// DELETE IGNORE FROM users WHERE user_id = 60
$affected_rows = xap('users:del/ignore WHERE user_id = 60');

Execute Query

Execute manual query example:

// execute any query using the 'query' command
$r = xap(':query SELECT * FROM users LIMIT 2');

// use params with manual query:
$r = xap(':query SELECT * FROM users WHERE user_id = ?', [2]);

The query command can use these query options: /query, /first, /value, /pagination, /cache. For example:

$query_string = $r = xap(':query/query SELECT * FROM users LIMIT 2');

Count Query

Get back a count (integer) query example:

// returns int of all records
$count = xap('users:count'); // SELECT COUNT(1) FROM users

// SELECT COUNT(1) FROM users WHERE is_active = 1
$count = xap('users:count WHERE is_active = 1');

// SELECT COUNT(1) FROM users WHERE user_id > '2' AND is_active = '1'
$count = xap('users:count WHERE user_id > ? AND is_active = ?', [2, 1]);

Record(s) Exist

Check if record(s) exists:

$has_records = xap('users:exists'); // check if records exists
if($has_records) // do something

// use query params example:
$is_record = xap('users:exists WHERE user_id = ? AND is_active = 1', [2])
if($is_record) // do something

Truncate Table

A table can be truncated using:

xap('table_name:truncate');

Call Stored Procedure/Function (Routines)

Call SP/SF example:

xap(':call sp_name'); // CALL sp_name()

// Call SP/SF with params:
// CALL sp_addUser('Name Here', '1', NOW())
xap(':call sp_addUser', ['Name Here', 1, ['NOW()']]);

// Call SP/SF with params and out param
xap(':query SET @out = "";'); // set out param
// CALL sp_addUser('Name Here', '1', NOW(), @out)
xap(':call sp_addUserGetId', ['Name Here', 1, ['NOW()'], ['@out']]);
// get out param value
$r = xap(