Download the PHP package technicalguru/database without Composer
On this page you can find all versions of the php package technicalguru/database. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download technicalguru/database
More information about technicalguru/database
Files in technicalguru/database
Package database
Short Description A PHP library to access databases
License LGPL-3.0-or-later
Informations about the package database
php-database
A PHP library for accessing databases easily. This library provides a MySQL/MariaDB flavoured database object that abstracts many daily task in SQL writing, such as quoting, escaping, building SQL statements, WHERE clauses, error handling and so on.
A Data Access Object (DAO) base class is also provided to cater for object-relational mapping tasks. The interface will make it easier to find objects by ID, create and update them, using special data objects of your own.
Finally, a Query API is provided to support a flexible writing of restrictions - independant of any SQL dialect.
Version 1.3 is the last major 1.x release and marks the migration release. Traditional SQL writing and Query API are supported simultanously. However, many traditional SQL methods are marked as deprecated, and they will be removed in release 2.0. So you shall migrate to the new Query API early.
License
This project is licensed under GNU LGPL 3.0.
Installation
By Composer
By Package Download
You can download the source code packages from GitHub Release Page
How to use the simple Database Layer
Creating a Database object
Create a configuration array and pass it to the constructor:
Please notice that we provide a table prefix. It is a common practice to prefix all your
tablenames like test_
. This way, you can keep several "namespaces" in your database.
The prefix will later be added to your query statements whenever you use #__
in the
table name (see examples below).
Instead of holding the credentials with the configuration, you could use a TgUtils\Auth\CredentialsProvider
that holds these data. Database
will ignore credentials in the config array then.
Querying objects
The interface delivers stdClass
objects by default. However, you can name
your own data class so the data will be populated in such a class:
Inserting, Updating and Deleting objects
You can insert your own data classes or simply use stdClass
objects or arrays:
The Database
will automatically escape and quote strings that appear as values in your new objects.
Updating your rows is accordingly easy. You will need the table name, the new values (as object or array) and a WHERE condition:
If you want to change a single object only, you also can use updateSingle()
which can give you back the
changed object (as stdClass
)
And finally you can delete objects. You will need the table name and the WHERE condition:
Remark: update()
, updateSingle()
and delete()
now support the new Query API for the
WHERE condition.
How to use a Database Access Object (DAO)
The low-level Database
abstraction makes object-relational mappings already simple. However,
it is still a lot of boilerplate to write, such as table names, WHERE clauses etc. A better way
is provided by the DAO
object. It simplifies the usage with databases a lot more.
Creating the DAO
Create a DAO by giving it the Database
instance and the table name:
The default constructor as above makes assumptions about your table:
- It always returns
stdClass
objects. - It assumes that your table has an
int auto-increment
primary key that is nameduid
.
However, you can tell DAO
your specifics:
DAO
can actually handle non-numeric primary keys. The usage is not recommended though as you need
to create the primary keys yourself.
Finding objects
Finding objects will be much easier now:
Attention: This way of describing restrictions is deprecated as of v1.3. find()
and findSingle()
now support
the new Query API. Please read the Query API chapter.
Creating, Saving and Deleting objects
WHERE clauses in DAO interface
The most simple form of a WHERE clause is the condition itself:
But you would need to do the quoting and escaping your self. That's why you can have an array
of all conditions that are concatenated with an AND
:
Or, when an equals (=
) operation is not what you need:
The default operator is equals (=
), but you also can use !=
, <=
, >=
, <
, >
, IN
and NOT IN
. Latter two
require arrays of values at the second position of the array:
Attention: This way of describing restrictions is deprecated as of v1.3. find()
and findSingle()
now support
the new Query API. Please read the Query API chapter.
ORDER clauses in DAO interface
Wherever an ORDER clause can be given, there are two types:
Default order sequence is ascending (ASC
) if not specified.
Attention: This way of describing ordering is deprecated as of v1.3. find()
and findSingle()
now support
the new Query API. Please read the Query API chapter.
Extending DAO
It is a good practice not to use DAO
class directly but derive from it in your project.
That way you can further abstract data access, e.g.
Attention: This way of describing restrictions is deprecated as of v1.3. find()
and findSingle()
now support
the new Query API. Please read the Query API chapter.
Using Data Objects with DAOs
As mentioned above, you can use your own data classes. There are actually no restrictions other than the class needs a no-argument constructor. The main advantage is that this class can have additional methods that have some logic. You can even define additional attributes that will not be saved in the database by a DAO. These attributes start with an underscore.
Here is an example:
Using a DataModel
Finally, we bring everything together. The last thing we need is a central location
for all our DAO
s. Here comes the DataModel
:
Of course, a better idea is to encapsulate this in your own DataModel
subclass:
You only need to implement the init()
method. Now your final application code looks
much cleaner and can be read easily:
Imagine, how much error-proned code you would have to write yourself!
Using a DaoFactory
The DataModel
can make use of a DaoFactory
. Such factory will create DAOs lazily when
requested by your application. Simply create your own instance of such a factory:
...and use it...
Now you don't need to overwrite the init()
method of the DataModel
.
The use of a DaoFactory
is recommended when you have many DAOs to manage and your application
usually uses only a fraction of it. It also will decouple your Data Model from the DAOs.
Query API
Version 1.3 introduces the Query
which gives you more freedom to express SQL conditions
when searching, updating or deleting objects. It is designed using the Hibernate ORM Criteria template. So much
of the code may appear familiar to you.
The Query API was created in addition to the Data Model and DAO API and enhances it. So you can still use the v1.0 way of searching objects while already starting the Query API. However, it is planned to remove the old API way of describing restrictions and orderings. Watch out for deprecation warning messages in your log.
Notice: Don't worry when you were already using the v1.2 Criteria
class. It is kept for compatibility
in the 1.x versions (Criteria
now inherits from Query
). Starting with v2.0, this interface will be removed.
Creating a Query
Two ways exist: Creating a Query
object from the Database
object, or alternatively from the DAO
object:
You can define model classes (the objects returned from SELECT
queries) and aliases when creating a Query
:
As the DAO already knows about the model class, there is no need to mention it when creating from a DAO
.
The alias is assigned to the underlying table name and will be added automatically when required in restrictions.
Using Restrictions
Restrictions are expressions that can be used in WHERE
clauses (and in JOIN
- see below). The helper
class Restrictions
is there to create them:
The most common restrictions are provided: eq, ne, lt, gt, ge, le, like, isNull, isNotNull, between. You can also use restrictions between two properties:
And it is possible to combine restrictions with and()
and or()
:
Sorting the result
The Order
class contains three static methods that produce according clauses:
asc()
and desc()
will automatically respect aliases and quote the
column names, whereas sql()
simply uses the string given.
However, you can use another alias if required:
Finally add these objects to your query:
Modifying the column list: columns and projections
Query will return all columns of the queried table by default. However, you can modify the column list:
Please notice that the first call to select
will remove the *
retrieval
on the query. Any subsequent call will enhance the list. The same result can be
achieved with:
And there are some shortcuts:
Attention: A call to setSelect()
or setProjection()
(deprecated alternative) will NOT remove
the result class definition in the query object as done before. This breaks compatibility with previous versions.
So you need to call setResultClass(NULL)
to have stdClass
returned.
Getting the result
That's the most easiest part:
You can set restrictions on the result:
Or you expect a single row only:
Using Projections
Basic projections - the aggregation of columns of different rows - are available:
You will find projections for: count, distinct, sum, avg, min, max. Please notice that
the returned model class is not reset. You need to call setResultClass(NULL)
to have
stdClass
returned when using projections.
Subqueries and JOINs
This is most likely the biggest advance in using the Query API. The traditional API methods were not able to use subqueries when searching for objects depending on other tables.
Let's assume you want to find all books in a database whose author name start with an A. The main query comes from books as it is our desired model class to be returned:
Next we join the authors table and add it to the main query using the respective restriction to join them properly:
And finally we apply the search condition for the author:
Another way of adding subqueries is directly via the main Query
object:
Updating and deleting multiple objects
The Query
object can also update and delete objects:
GROUP BY and HAVING clauses
The Query API allows to define grouping result sets and restricting the returned result with the HAVING clause:
Please notice that using ->count()
on such a query might produce unexpected results. This is still an unresolved issue.
Useful methods
You might want to make use of some methods that will ease your code writing:
Advantages and Limitations
The Query API further eases searching objects in a database and return model classes, using more
complex expressions and restrictions. You are able to dynamically apply restrictions depending on
the requirements of your front-end users and your application. And you don't need the DAO once you
created the Query
object. It is self-contained.
However, some limitations exist:
- Query API supports basic use cases so far (searching objects with basic restrictions, updates, deleting).
- Only MySQL / MariaDB SQL dialect is produced (but can be extended to other dialects easily when you stick to the API).
- A few of the limitations may be ovecome by using the
SqlExpression
andSqlProjection
classes:
But feel free to raise an issue (see below) when you need some extension that is not yet supported.
Contribution
Report a bug, request an enhancement or pull request at the GitHub Issue Tracker.