CDatabase - PHP classes för working with SQL queries and databases
The CDatabase
is a collection of PHP classes that aid in working and querying databases. It has a query builder that let you build the SQL queries using code, in a database agnostic way and it has a database abstraction layer on top of PHP PDO which adds logging, debugging and ease of use.
This is basically an addon upon PHP PDO. It is small enough to understand and easy enough to extend by a intermediate skilled PHP programmer. It can be used as a base to build upon to create your own custom database library, before moving on to a more complete ORM library (Object Relational Mapping).
#License
CDatabase
is free software and open source software, licensed according MIT.
#Requirements
CDatabase
requires PHP 5.4.
#Installation
The current version is tagged as v0.1.0 and release 2014-04-17.
It is not a completely stable version and there might be changes in the API in coming releases. This software is still under development.
#Clone it from GitHub
The sourcode is available on GitHub. Clone, fork or download as zip.
git clone https://github.com/mosbth/cdatabase.git
You can use the latest and most updated version. It should work. You can also switch to a more tested version, like this. First check which tags are available and then checkout one of those. But start with the latest and most updated version that comes per default.
git tag git checkout v0.1.0
#Install using composer
The package is available at Packagist as mos/cdatabase
. You can install the package using composer. Add the following to your composer.json
.
composer.json
{
"require": {
"mos/cdatabase": "dev-master"
}
}
You can then install the package mos/cdatabase
using the following command.
composer validate composer install --no-dev
The package is now available in you vendor
map.
#Verify installation
Point your web browser to the installation directory and the directory webroot
. Then choose the test file testSQLQueryBuilderBasic.php
. It should print out a lot of SQL commands, something like this.
CREATE TABLE mos_test ( id integer primary key not null, age integer, text varchar(20), text2 varchar(20) ); INSERT INTO mos_test (id, text, text2) VALUES (2, 'Mumintrollet', 'Mumindalen');
And so on. It works – if all you see is wonderful SQL statements.
#Basic usage
#Classes and an use case
CDatabase
consists of two parts. First the database abstraction layer which is implemented in the class Mos\Database\CDatabaseBasic
. Second is the trait Mos\Database\TSQLQueryBuilderBasic
which is used by CDatabase
. So, it means that all features are collected in the class CDatabase
, or CDatabaseBasic
that is.
Basic
is appended since this is still a development project, it will be stripped once a more stable version, with more features, is reached.
#Connect to MySQL or SQLite
The classes are tested on a limited set of the features for the database SQLite and MySQL. You should create a config-file with the connection details. The directory webroot
contains two examples.
Connection details for SQLite in webroot/config_sqlite.php
.
return [
'dsn' => "sqlite:.htsqlite.db",
'verbose' => true,
];
Connection details for MySQL.
return [
'dsn' => "mysql:host=localhost;dbname=test;",
'username' => "test",
'password' => "test",
'driver_options' => null,
'driver_options' => [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"],
'verbose' => true,
//'debug_connect' => true,
];
The config item verbose
is useful for development, it prints out all the requests to the database.
The config item debug_connect
is useful for development when failing to connect to the database. It throws an exception with secret connection details such as user and password. So use it only when no other sees it.
We use these config files to create a database object and connect it with the database.
Connect to a database
$db = new \Mos\Database\CDatabaseBasic();
$options = require "config_sqlite.php";
$db->setOptions($options);
$db->connect();
As you can see, you only need to change the config file, to connect to your MySQL database instead.
#Verbose mode
As I said above, the config item verbose
is useful for development, it prints out all the requests to the database. This can also be set to on and off whenever needed.
Use verbose mode
$db->setVerbose(); // Set verbose mode to on
$db->setVerbose(true); // Set verbose mode to on
$db->setVerbose(false); // Set verbose mode to off
When using verbose
mode the queries are displayed with their resulting SQL-code and parameters. Like this.
Num query = 1 Query = INSERT INTO test (age, text) VALUES (?, ?); Params: Array ( [0] => 22 [1] => Mumintrollet )
This provides a quick way to visually inspect the query and its parameters. Its good for troubleshooting.
#Query the database
There are several ways to query the database. You can construct the SQL queries using plain old SQL. Or, you can use the query builder.
Lets go through the different steps of creating and executing queries.
#Table prefix
Sometimes you need to have many databases in one database and that can be solved using namespacing the table names. This can be done using a table prefix that is global for all table names.
$db->setTablePrefix('mos_');
#DROP TABLE
This is how to drop a table.
$sql = "DROP TABLE mos_test;";
$db->execute($sql);
$sql = "DROP TABLE IF EXISTS mos_test;";
$db->execute($sql);
$db->dropTableIfExists('test')
->execute();
$db->dropTable('test')
->execute();
The table prefix is added when creating the queries using the query builder. But, you need to add it manually when you create the SQL queries by hand.
#CREATE TABLE
This is how to create a table.
$sql = "
CREATE TABLE mos_test
(
id integer primary key not null,
age integer,
text varchar(20)
)
;";
$db->execute($sql);
$db->createTable(
'test',
[
'id' => ['integer', 'auto_increment', 'primary key', 'not null'],
'age' => ['integer'],
'text' => ['varchar(20)'],
]
);
$db->execute();
SQLite and MySQL behaves differently using the attribute auto_increment
. It is used by MySQL but removed in SQLite. It is these small changes between database engines that can be handled in a more database agnostic way by using the query builder who are aware of these small differences and takes action to manage them.
#INSERT
This is how to insert rows into a table.
$sql = "
INSERT INTO mos_test
(age, text)
VALUES
(22, 'Mumintrollet');
;";
$db->execute($sql);
$sql = "
INSERT INTO mos_test
(age, text)
VALUES
(?, ?);
;";
$db->execute($sql, [22, 'Mumintrollet']);
$db->insert(
'test',
[
'id' => 2,
'text' => "Mumintrollet",
]
);
$db->execute();
$db->insert(
'test',
['id', 'text'],
[2, "Mumintrollet"]
);
$db->execute();
$db->insert(
'test',
['id', 'text']
);
$db->execute([22, 'Mumintrollet']);
You can see the flexibility on how to create the SQL query. All queries results in the same SQL statement being processed.
#UPDATE
This is how to update rows into a table.
$sql = "
UPDATE mos_test
SET
age = 44,
text = 'Mumindalen'
WHERE id = 2
;";
$db->execute($sql);
$sql = "
UPDATE mos_test
SET
age = ?,
text = ?
WHERE id = ?
;";
$db->execute($sql, [44, 'Mumindalen', 2]);
$db->update(
'test',
[
'age' => 44,
'text' => "Mumindalen",
],
"id = 2"
);
$db->execute();
$db->update(
'test',
['age', 'text'],
[44, "Mumindalen"],
"id = 2"
);
$db->execute();
$db->update(
'test',
['age', 'text'],
"id = ?"
);
$db->execute([44, 'Mumindalen', 2]);
The update statement has the same flexibility as the insert statement in how it is created.
#DELETE
This is how to delete rows in a table.
$sql = "DELETE FROM mos_test WHERE id = 2;";
$db->execute($sql);
$sql = "DELETE FROM mos_test;";
$db->execute($sql);
$db->delete(
'test',
"id = 2"
);
$db->execute();
$db->delete(
'test',
"id = ?"
);
$db->execute([2]);
$db->delete(
'test'
);
$db->execute();
Be sure to add the where-part to avoid accidentally deleting all rows in the table.
#SELECT
This is how to select rows and columns in a table.
$sql = "
SELECT
age, text
FROM mos_test
WHERE
id = 2
;";
$db->execute($sql);
$sql = "
SELECT
age, text
FROM mos_test
WHERE
id = ?
;";
$db->execute($sql, [2]);
$db->select("age, text, text1")
->from('test')
->where("id = 2")
;
$db->execute();
$db->select("age, text, text1")
->from('test')
->where("id = ?")
;
$db->execute([2]);
This is how to select all rows and columns in a table and sort them.
$sql = "
SELECT
*
FROM mos_test
ORDER BY id ASC
;";
$db->execute($sql);
$db->select()
->from('test')
->orderBy("id ASC")
;
$db->execute();
This is how to select and join tables.
$sql = "
SELECT
t1.*, t2.id AS id2, t3.id AS id3
FROM mos_test AS t1
INNER JOIN mos_test AS t2
ON t1.id = t2.id
INNER JOIN mos_test AS t3
ON t1.id = t3.id
;";
$db->execute($sql);
$db->select("t1.*, t2.id AS id2, t3.id AS id3")
->from('test AS t1')
->join('test AS t2', 't1.id = t2.id')
->join('test AS t3', 't1.id = t3.id');
$db->execute();
As you see, it is possible to create more and more advanced queries using the query builder.
Check the source code to see what options are available and you will notice that it is not to hard to add your own constructs, whenever needed.
#Test programs for each database
In the directory webroot
there exists one test program for each database, testSQLite.php
for SQLite and testMySQL.php
for MySQL.
#Test SQLite
This test is dependent on the following files.
testDatabaseQueries.php
config_sqlite.php
testSQLite.php
Review the files and run the test by pointing your browser to testSQLite.php
.
You need to make the directory writable by the web server, so it can create the database file.
#Test MySQL
This test is dependent on the following files.
testDatabaseQueries.php
config_mysql.php
testMySQL.php
Review the files and run the test by pointing your browser to testMySQL.php
.
You need to set up the connection details to your own database.
#Get going on your own
The test programs gives you a base to start from. Continue by building your own test program and try it out. Does it work the way you though it should? Are any constructs missing?
There is not to much effort needed to add items to the CDatabase API. Put an issue on GitHub if you are uncertain on the right way to do it. Create a pull request if you did something nice.
#A sample installation
You can see the sample installation here, and try out the example programs in webroot
.
#Trouble and feature requests
Use GitHub to report issues. Always include the following.
- Describe very shortly: What are you trying to achieve, what happens, what did you expect.
- Make a test case with a minimal structure of the behavior and explain how to reproduce it.
If you request a feature, describe its usage and argument for why it fits into CDatabase
.
Feel free to fork, clone and create pull requests.
#Revision
2014-04-17 (A, mos) First release on dbwebb/opensource as part of course material for phpmvc-course.