Download the PHP package faslatam/sparrow without Composer

Sparrow

Sparrow is a simple but powerful database toolkit. Sparrow is a fluent SQL builder, database abstraction layer, cache manager, query statistics generator, and micro-ORM all rolled into a single class file.

Requirements

Sparrow requires PHP 5.3 or greater.

Building SQL

Output:

Method Chaining

Sparrow allows you to chain methods together, so you can instead do:

Where Conditions

To add where conditions to your query, use the where function.

Output:

You can call where multiple times to add multiple conditions.

Output:

You can also pass an array to the where function. The following would produce the same output.

You can even pass in a string literal.

Output:

Custom Operators

The default operator for where queries is =. You can use different operators by placing them after the field declaration.

Output:

OR Queries

By default where conditions are joined together by AND keywords. To use OR instead, simply place a | delimiter before the field name.

Output:

LIKE Queries

To build a LIKE query you can use the special % operator.

Output:

To build a NOT LIKE query, add a ! before the % operator.

Output:

IN Queries

To use an IN statement in your where condition, use the special @ operator and pass in an array of values.

Output:

To build a NOT IN query, add a ! before the @ operator.

Output:

Selecting Fields

To select specific fields, pass an array in to the select function.

Output:

Limit and Offset

To add a limit or offset to a query, you can use the limit and offset functions.

Output:

You can also pass in additional parameters to the select function.

Output:

Distinct

To add a DISTINCT keyword to your query, call the distinct function.

Output:

Table Joins

To add a table join, use the join function and pass in an array of fields to join on.

Output:

The default join type is an INNER join. To build other types of joins you can use the alternate join functions leftJoin, rightJoin, and fullJoin.

The join array works just like where conditions, so you can use custom operators and add multiple conditions.

Output:

Sorting

To add sorting to a query, use the sortAsc and sortDesc functions.

Output:

You can also pass an array to the sort functions.

Output:

Grouping

To add a field to group by, use the groupBy function.

Output:

Insert Queries

To build an insert query, pass in an array of data to the insert function.

Output:

Update Queries

To build an update query, pass in an array of data to the update function.

Output:

Delete Queries

To build a delete query, use the delete function.

Output:

Executing Queries

Sparrow can also execute the queries it builds. You will need to call the setDb() method with either a connection string, an array of connection information, or a connection object.

The supported database types are mysql, mysqli, pgsql, sqlite and sqlite3.

Using a connection string:

The connection string uses the following format:

For sqlite, you need to use:

Using a connection array:

The possible array options are type, hostname, database, username, password, and port.

Using a connection object:

You can also use PDO for the database connection. To use the connection string or array method, prefix the database type with pdo:

The possible PDO types are pdomysql, pdopgsql, and pdosqlite.

You can also pass in any PDO object directly:

Fetching records

To fetch multiple records, use the many function.

The result returned is an array of associative arrays:

To fetch a single record, use the one function.

The result returned is a single associative array:

To fetch the value of a column, use the value function and pass in the name of the column.

All the fetch functions automatically perform a select, so you don't need to include the select function unless you want to specify the fields to return.

Non-queries

For non-queries like update, insert and delete, use the execute function after building your query.

Executes:

Custom Queries

You can also run raw SQL by passing it to the sql function.

Escaping Values

Sparrow's SQL building functions automatically quote and escape values to prevent SQL injection. To quote and escape values manually, like when you're writing own queries, you can use the quote function.

Output:

Query Properties

After executing a query, several property values will be populated which you can access directly.

These values are reset every time a new query is executed.

Helper Methods

To get a count of rows in a table.

To get the minimum value from a table.

To get the maximum value from a table.

To get the average value from a table.

To get the sum value from a table.

Direct Access

You can also access the database object directly by using the getDb function.

Caching

To enable caching, you need to use the setCache method with a connection string or connection object.

Using a connection string:

Using a cache object:

You can then pass a cache key to the query functions and Sparrow will try to fetch from the cache before executing the query. If there is a cache miss, Sparrow will execute the query and store the results using the specified cache key.

Cache Types

The supported caches are memcache, memcached, apc, xcache, file and memory.

To use memcache or memcached, you need to use the following connection string:

protocol://hostname:port

To use apc or xcache, just pass in the cache name:

To use the filesystem as a cache, pass in a directory path:

Note that local directories must be prefixed with ./.

The default cache is memory and only lasts the duration of the script.

Cache Expiration

To cache data only for a set period of time, you can pass in an additional parameter which represents the expiraton time in seconds.

In the above example, we are getting a list of the top 100 highest scoring users and caching it for 600 seconds (10 minutes). You can pass the expiration parameter to any of the query methods that take a cache key parameter.

Direct Access

You can access the cache object directly by using the getCache function.

You can manipulate the cache data directly as well. To cache a value use the store function.

To retrieve a cached value use the fetch function.

To delete a cached value use the clear function.

To completely empty the cache use the flush function.

Using Objects

Sparrow also provides some functionality for working with objects. Just define a class with public properties to represent database fields and static variables to describe the database relationship.

Class Configuration

• The table property represents the database table. This property is required.
• The id_field property represents the auto-incrementing identity field in the table. This property is required for saving and deleting records.
• The name_field property is used for finding records by name. This property is optional.

Loading Objects

To define the object use the using function and pass in the class name.

After setting your object, you can then use the find method to populate the object. If you pass in an int Sparrow will search using the id field.

This will execute:

If you pass in a string Sparrow will search using the name field.

This will execute:

If you pass in an array Sparrow will use the fields specified in the array.

This will execute:

If the find method retrieves multiple records, it will return an array of objects instead of a single object.

Saving Objects

To save an object, just populate your object properties and use the save function.

This will execute:

To update an object, use the save function with the id_field property populated.

This will execute:

To update an existing record, just fetch an object from the database, update its properties, then save it.

By default, all of the object's properties will be included in the update. To specify only specific fields, pass in an additional array of fields to the save function.

This will execute:

Deleting Objects

To delete an object, use the remove function.

Advanced Finding

You can use the sql builder functions to further define criteria for loading objects.

This will execute:

You can also pass in raw SQL to load your objects.

Statistics

Sparrow has built in query statistics tracking. To enable it, just set the stats_enabled property.

After running your queries, get the stats array:

The stats array contains the total time for all queries and an array of all queries executed with individual query times.

Debugging

When Sparrow encounters an error while executing a query, it will raise an exception with the database error message. If you want to display the generated SQL along with the error message, set the show_sql property.

License

Sparrow is released under the MIT license.