Dacapo class (Simple PHP database wrapper)
Copyright Christos Pontikis http://www.pontikis.net
License MIT https://raw.github.com/pontikis/dacapo/master/MIT_LICENSE
- Supported RDMBS: MySQLi (or MariaDB), POSTGRESQL
- Simple and clear syntax
- Only prepared statements are used
- Supported Queries: single SELECT, UPDATE, INSERT, DELETE.
- Support of transactions
- Write SQL easily and securely. Use dacapo
sql_placeholder
(? is the default) in place of parameters values. Dacapo will create SQL prepared statements from standard ANSI SQL.
$sql = 'SELECT procuct_name FROM products WHERE manufacturer = ? and type IN (?,?,?)';
- For MYSQLi SELECT prepared statements,
mysqlnd
is required - Persistent database connection NOT supported.
- BLOB columns NOT supported
- avoid boolean columns, use integer instead (1,0)
- Use
$ds->execute()
to execute one or usually multiple SQL statements (e.g. an SQL script). You cannot use prepared statements here.
You SHOULD create custom wrappers in your application to catch exceptions.
Dacapo Error Handler will throw DacapoErrorException
.
If you choose to not use Dacapo Error Handler you will define type of Exception in your own Error Handler.
When you execute an INSERT query in Postgres you have also to query a sequence if you want to get the last inserted value in Primary Key column. In this case use setQueryInsertPgSequence()
. There are three options:
self::PG_SEQUENCE_NAME_AUTO
in this case sequence name will be automatically constructed astablename_id_seq
. This is the default setting (ideal for SERIAL columns)null
(in the rare case when no Primary key is defined)- the sequence real name
REMEMBER that query_insert_pg_sequence
will be reset to default self::PG_SEQUENCE_NAME_AUTO
after each INSERT query.
For HTML documentation, see docs/doxygen/html/
folder (open index.html
file in a browser).
using Composer (recommended)
composer require pontikis/dacapo
or the old-school way:
require_once 'path/to/Dacapo.php';
require_once 'path/to/DacapoErrorException.php';
use Pontikis\Database\Dacapo;
use Pontikis\Database\DacapoErrorException;
$db_settings = [
'rdbms' => Dacapo::RDBMS_POSTGRES, // or Dacapo::RDBMS_MYSQLI for MySQL/MariaDB
'db_server' => 'localhost',
'db_user' => 'foo',
'db_passwd' => 'bar',
'db_name' => 'baz',
];
try {
$ds = new Dacapo($db_settings);
} catch (Exception $e) {
// your code here
}
$sql = 'SELECT id, firstname, lastname FROM customers WHERE lastname LIKE ?';
$bind_params = ['%' . $str . '%'];
try {
$ds->select($sql, $bind_params);
$customers = $ds->getData();
} catch (DacapoErrorException $e) {
// your code here
}
if($ds->getNumRows() > 0) {
foreach($customers as $customer) {
$id = $customer['id'];
$lastname = $customer['lastname'];
$firstname = $customer['firstname'];
}
}
$sql = 'SELECT firstname, lastname FROM customers WHERE id = ?';
$bind_params = [$id];
try {
$ds->select($sql, $bind_params);
if(1 === $ds->getNumRows()) {
$customer = $ds->getRow();
$firstname = $customer['firstname'];
$lastname = $customer['lastname'];
}
} catch (DacapoErrorException $e) {
// your code here
}
$sql = 'INSERT INTO customers (firstname, lastname) VALUES (?,?)';
$bind_params = [$firstname, $lastname];
try {
$ds->insert($sql, $bind_params);
$new_customer_id = $ds->getInsertId();
} catch (DacapoErrorException $e) {
// your code here
}
$sql = 'UPDATE customers SET category = ? WHERE balance > ?';
$bind_params = [$category, $balance];
try {
$ds->update($sql, $bind_params);
$affected_rows = $ds->getAffectedRows();
} catch (DacapoErrorException $e) {
// your code here
}
$sql = 'DELETE FROM customers WHERE category = ?';
$bind_params = [$category];
try {
$ds->delete($sql, $bind_params);
$affected_rows = $ds->getAffectedRows();
} catch (DacapoErrorException $e) {
// your code here
}
try {
$ds->beginTrans();
// delete from customers
$sql = 'DELETE FROM customers WHERE id = ?';
$bind_params = [$customers_id];
$ds->delete($sql, $bind_params);
// delete from demographics
$sql = 'DELETE FROM demographics WHERE id = ?';
$bind_params = [$customer_demographics_id];
$ds->delete($sql, $bind_params);
$ds->commitTrans();
} catch (DacapoErrorException $e) {
$ds->rollbackTrans();
// your code here
}
// check for unique username (CASE IN-SENSITIVE)
$sql = "SELECT count('id') as total_rows FROM users WHERE {$ds->lower('username')} = ?";
$bind_params = [mb_strtolower($username)];
$ds->select($sql, $bind_params);
if($ds->getNumRows() > 0) {
echo 'Username in use...';
}
$limitSQL = $ds->limit($rows_per_page, ($page_num - 1) * $rows_per_page);
Tests performed in Debian 9 Linux server with
- php 7
- MariaDB Ver 15.1 Distrib 10.1.26-MariaDB (similar to MySQL 5.7)
- Postgres 9.6.7
Test databases are provided in tests/dbdata
folder. Customize credentials in tests/phpunit.xml
. First copy phpunit.dest.xml
to phpunit.xml
./vendor/bin/phpunit --configuration tests/phpunit.xml tests/MySQLTest.php
mysqli timout make some tests slow. Run them once and then use:
./vendor/bin/phpunit --enforce-time-limit --configuration tests/phpunit.xml tests/MySQLTest.php
In this case PHP_Invoker is needed https://github.com/sebastianbergmann/php-invoker
./vendor/bin/phpunit --configuration tests/phpunit.xml tests/MySQLCUDTest.php
./vendor/bin/phpunit --configuration tests/phpunit.xml tests/PostgresqlTest.php
./vendor/bin/phpunit --configuration tests/phpunit.xml tests/PostgresqlCUDTest.php
./vendor/bin/phpunit --configuration tests/phpunit.xml tests/PostgresqlTest.php --filter '/testConnectFails1$/'
You cannot use --filter
with CUD tests. Actually every CUD test depends on previous.
Your contribution is welcomed.
- Pull requests are accepted only in
dev
branch. - Remember to also submit the relevant PHPUnit tests.
- Review is always required.