Download the PHP package pyrsmk/olive without Composer

Olive 0.26.7

This library is now obsolete. Its successor is Olive2, a small wrapper around PDO.

Olive is a database library that aims to handle several databases with one simple API. It is designed for small to medium projects that don't need to be highly optimized because the API just supports the common tasks.

This project is a proof-of-concept and should be used as is.

Even if you can switch between different database types (like MySQL and MongoDB) with the same project and the same data structure, I do not encourage anyone to do it. Each database system has its own pros and cons and you should choose wisely what to use for your needs. Moreover, some methods on the API can be greedy, like join() with MongoDB which runs one additional request per join to retrieve data.

Please note that MongoDB is relation-less and should not be used with relational data structures.

Installation

Features

• CRUD operations
• querying with support for AND/OR operators
• select fields
• create aliases
• joining
• sorting
• limit and skip results
• support for regexes
• namespaces
• simple ORM/ODM support

Create database connection

Creating a database connection works the same over all database adapters, it takes a database name as first argument and an array of options as second. Here's the available options for each database object :

• Olive\4d
• Olive\Cubrid
• Olive\Firebird
• Olive\Freetds
• Olive\Ibm
• Olive\Informix
• Olive\Mariadb (just an alias of Olive\Mysql)
• Olive\Mongodb
• Olive\Mssql (Microsoft SQL Server)
• Olive\Mysql
• Olive\Odbc
• Olive\Oracle
• Olive\Postgresql
• Olive\Sqlite
• Olive\Sqlsrv (MS SQL Server and SQL Azure)
• Olive\Sybase

MongoDB

MySQL

SQLite

Get data containers

A data container is an abstraction class for a table or a collection, per example. Data containers are the entry point to create queries. We can retrieve them with a simple call to :

If your table has a weird name, you can get it anyway with :

We strongly advise you to use namespaces in your applications so that your database is not pollute by random tables and avoid incompatibility issues between different applications/websites :

In Olive, you can specify a global namespace for simplicity :

If you need to remove the global namespace :

CRUD operations

The insert(), update(), save() and remove() are used for basic CRUD operations. These methods can accept an additional argument : an array of options for the driver. Please refer to the related PHP documentation pages (in PDO or MongoDB chapters) to have further information about it.

Querying

Searching & fetching

As you have seen, searches use a simple syntax to handle conditional operators. These operators are :

• is : equality operator
• is not : non-equality operator
• less : the field value is less than the specified value
• greater : the field value is greater than the specified value
• in : verify if the field value is in the specified array
• not in : verify if the field value is not in the specified array
• like : use the LIKE SQL syntax to match a field against a pattern
• not like : use the LIKE SQL syntax to verify if the field does not match the provided pattern
• match : use regexes to match a string against a pattern
• not match : use regexes to verify if the field does not match the provided pattern

Take a look at how we're getting results :

The fetch() method retrieves all results. But there's other methods like fetchOne() which get the first row in the results and fetchFirst() that get the first field of the first row. Let's see a concrete example for that case :

There's also direct methods to search and retrieve in one call :

But please note that findFirst() is here for API consistency. Since we're not selecting any field, all of them are returned and the first field is often the ID of the row.

Of course, you can specify several searches in one request. Each search will be appended to the request with a AND operator.

Let's get a further look how searching works. In fact, each call to search() will be concatenated with AND operators. But we sometimes need to add an OR clause to the query. It is obtained by calling the orSearch() method :

All orSearch() clauses will be appended to the previous search.

Last note on this subject : calling search() returns a new Query object. It could happen that you need, per example, to do a loop and add a search at each cycle. In that case, you'll need to get a new query before calling search() :

Select fields & set aliases

With a SQL database, you could need to set aliases for several tables in your query to avoid conflicts :

Join

Sort

The sort() method takes the field and the sorting direction as arguments. The direction is either asc or desc.

Limit

Skip

Skipping results is useful when using limit() for pagination.

Count

For ease of use, you can directly count how many results that a search should return.

Create models

To simplify your models and have a nice object-oriented API, you can extend Olive\Model. The constructor takes an Olive object as argument and expects that $singular, $plural, $data_container and $primary_key class properties are well defined. Let's say we have a users table that we want to map, here's how we're defining it :

That's all we need for a simple model. But you often need specific queries to optimize things. You can just do it by adding a new method to your class, like getThoseFuckingWeirdResults().

Here's the exhaustive list of the methods you can natively call (replace singular and plural parameters by those you defined in your class) :

• <singular>Exists($id) : verify if an element with the provided id exists (ex : userExists(123))
• <singular>Exists($search) : verify if an element with the provided search exists (ex : userExists(array('email'=>'[email protected]')))
• <singular>ExistsBy<SearchField>($value) : verify if an element exists by verifying one of its field (ex : userExistsByEmail('[email protected]'))
• <plural>Exist($search) : verify if several elements exist (ex : usersExist(array('name'=>'Thomas')))
• <plural>ExistBy<SearchField>($value) : verify if several elements exist against a field value (ex : usersExistByName('Thomas'))
• count<Plural>() : count results (ex : countUsers())
• count<Plural>($search) : count results (ex : countUsers('name','is','Thomas'))
• count<Plural>By<SearchField>($value) : count results by searching a field (ex : countUsersByName('Thomas'))
• insert<Singular>($data) : insert data (ex : insertUser($data))
• insert<Plural>($data) : insert several rows (ex : insertUsers($data))
• add<Singular>($data) : alias of insert<Singular>
• add<Plural>($data) : alias of insert<Plural>
• get<Singular>($id, $fields) : get a row by its id (ex : getUser(72))
• get<Singular>($search, $fields) : get a row by a specific search (ex : getUser(array('email'=>'[email protected]')))
• get<Singular><Field>($id) : get a field by id (ex : getUserEmail(72))
• get<Singular><Field>($search) : get a field by a specific search (ex : getUserEmail(array('name' => 'Thomas')))
• get<Singular>By<SearchField>($value, $fields) : get at row by a field (ex : getUserByName('Thomas'))
• get<Singular><Field>By<SearchField>($value) : get a field by a search on another field (ex : getUserEmailByName('Thomas'))
• get<Plural>($search, $fields) : search for several rows (ex : getUsers(array('status' => 'admin')))
• get<Plural><Field>($search) : search for several rows but retrieve one field (ex : getUsersEmail(array('status' => 'admin')))
• get<Plural>By<SearchField>($value, $fields) : search for several rows by a specific field (ex : getUsersByStatus('admin'))
• get<Plural><Field>By<SearchField>($value) : search for several rows by a specific field, and return one field per row (ex : getUsersEmailByStatus('admin'))
• update<Singular>($id, $data) : update an ID specific element (ex : updateUser(72, $data))
• update<Singular>($search, $data) : update an element with a search (ex : updateUser(array('_id' => 72), $data))
• update<Singular><Field>($id, $value) : update a specific field of an element (ex : updateUserName(72,'Pierre'))
• update<Singular><Field>($search, $value) : update a specific field of an element with a search (ex : updateUserName(array('_id' => 72), 'Pierre'))
• update<Singular>By<SearchField>($value, $data) : update an element by searching a field (ex : updateUserById(72, $data))
• update<Singular><Field>By<SearchField>($search_value, $field_value) : update the field of an element by searching another field (ex : updateUserNameById(72, 'Pierre'))
• update<Plural>($search, $data) : update several elements (ex : updateUsers(array('name' => 'Pierre'), $data))
• update<Plural><Field>($search, $value) : update several element fields (ex : updateUsersName(array('name' => 'Pierre'), 'Jacques'))
• update<Plural>By<SearchField>($value, $data) : update several elements by searching a field (ex : updateUsersByName('Pierre', $data))
• update<Plural><Field>By<SearchField>($search_value, $field_value) : update several elements field by searching another field (ex : updateUsersNameByName('Pierre', 'Jacques'))
• save<Singular>($data) : save data (ex : saveUser($data))
• set<Singular>($data) : alias of save<Singular>
• remove<Singular>($id) : remove an element (ex : removeUser(72))
• remove<Singular>($search) : remove an element by search (ex : removeUser(array('_id' => 72)))
• remove<Singular>By<SearchField>($value) : remove an element by searching a field (ex : removeUserByEmail('[email protected]'))
• remove<Plural>($search) : remove several elements (ex : removeUsers(array('name' => 'Pierre')))
• remove<Plural>By<SearchField>($value) : remove several elements by searching a field (ex : removeUsersByName('Pierre'))
• delete<Singular>($id) : alias of remove<Singular>
• delete<Singular>($search) : alias of remove<Singular>
• delete<Singular>By<SearchField>($value) : alias of remove<Singular>By<SearchField>
• delete<Plural>($search) : alias of remove<Plural>
• delete<Plural>By<SearchField>($value) : alias of remove<Plural>By<SearchField>

We should take a look at all those variables defined in the API. Most of them talk by themselves but not $search or $fields. The $search parameter is an associative array that lists the fields with the value to match for the query :

The $fields parameter is also an associative array that lists the fields to retrieve and maps aliases :

Advanced use

Last notes

• you may want to use _id as default primary key for your tables because it's the key used in MongoDB, so you can use it across you applications regardless of the database in use
• in MongoDB all _id primary keys are an instance of ObjectId, in Olive we automatically stringify the id and create objects when needed : shortly, you don't need to bother about ObjectId at all

License

Olive is released under the MIT license.