README

Charm/Recordset is a library which lets you define data collections from any data source with several different and powerful query APIs.

Features:

Publish any data source as a REST API with paging, sorting and filtering capabilities.
Query and join these data sources with standard SQL, including JOINS.
Supported data sources:
- PDO database queries
- Sqlite3 database tables
- CSV files
- PHP Arrays, Generators and Iterators
- Other REST APIs
- Custom data source adapters
Full UTF support with locale collation.

Make data available with a single API regardless of An API that provides the building blocks to make data available and queryable, even if that data is stored in a CSV file, a remote API or is built from generated data.

To illustrate; this API allows you to perform the equivalent of the following SQL queries:

SELECT * FROM ./countries.csv ORDER BY latitude
SELECT value FROM positive_integers WHERE value < 10
SELECT value FROM all_integers WHERE value > -10 AND value < 10

The above examples would simply require you to implement backends for each of the tables.

TLDR

Instead of making all sorts of methods to fetch objects of a particular class, you can provide all data through a simple querying API:

Instead of creating dozens of class methods like getByUserId(int $userId) and getByFirstName(string $name), consumers can use a powerful and predictable querying API:

    // $user = $users->getById(123);

    // one of three approaches
    $user = $users->eq('id', 123)->first();         // low level API
    $user = $users->where('id=123')->first();       // query string API
    $user = $users->where('id=?', 123)->first();    // query string API with bound and typed columns

This approach yields massive benefits on a number of areas:

More powerful querying automatically supported: 'users where gender = "male" and registered_ts > 123'
Higher security; recordsets can be "pre-filtered" in the backend, so that consumers are restricted to querying on a limited subset of rows.
Ability to replace backends without updating anything other than the backend adapter.
File based backends can be updated using the version control system instead of through database migrations.
Recordsets can be exposed as REST API collections with query string based filtering

A backend must implement Charm\Recordset\RecordsetInterface. The easiest way to implement this is by extending the Charm\Recordset\AbstractRecordset.

There are some pre-made backends available for CSV files, PDO database tables, arrays and generators.

Below you'll see some usage examples and a simple implementation for a CSV-backed Country collection.

Querying

The most compact method of querying is through the Recordset::query() method.

The following examples are equivalent:

foreach (Country::all()
    ->where('name[gt]=t&name[lt]=z&latitude[gte]=50&latitude[lte]=80&$order=-name')
    ->page(0,100) as $country) {

    echo $country->getCapital()."\n";
}

and

foreach (Country::all()         // fetch a recordset
    ->gt('name', 't')           // reduce the recordset to countries where name > 't'
    ->lt('name', 'z')           // and where name < '<'
    ->gte('latitude', 50)       // and where latitude >= 20
    ->lt('latitude', 80)        // and where latitude <= 80
    ->order('name', true)       // sort them in descending order by name
    ->page(0, 100)              // fetch the 100 first rows
    as
    $country) {
    
    echo $country->getCapital()."\n";
}

Querying is done by adding operator expressions to a recordset.

$southernCountries = $countries->lt('latitude', 0);
$northernCountries = $countries->gte('latitude', 0);

Note that recordsets are immutable. This means that you can use operators to restrict access to rows.

Multiple operators can be chained:

$countriesNearEquator = $countries->lt('latitude', 23.43)->gt('latitude', -23.43);

The following operators are supported:

Operator	Method
=	`Recordset::eq($key, $value)`
>	`Recordset::gt($key, $value)`
>=	`Recordset::gte($key, $value)`
<	`Recordset::lt($key, $value)`
<=	`Recordset::lte($key, $value)`
startsWith	`Recordset::startsWith('name', 'n')

Fetching

To fetch the first 100 rows from a recordset:

$iterator = $northernCountries->page(0, 100)

Even if this request requires 100 API calls to a backend, this fact is invisible to the consumer. If the filtering is done by a database engine, or if the filtering is done via PHP code is also hidden.

Iterating results

To actually fetch rows, use the page method:

foreach ($southernCountries->page(0, 100) as $country) {
    if ($country instanceof Country) {
        // This requires that the backend objects of this kind
    }
}

Country collection

<?php
use Charm\Recordset\CSVRecordset;

class Country {
    public $code, name, $latitude, $longitude;

    protected function __construct(string $code, string $name, float $latitude, float $longitude) {
        $this->code = $code;
        $this->name = $name;
        $this->latitude = $latitude;
        $this->longitude = $longitude;
    }

    public static function all() {

        /**
         * It is quite simple to create a custom data provider, but
         * here we'll be using the CSVRecordset class.
         *
         * CSV file are not indexed, but there are ways to accelerate
         * this, for example by using a CachingRecordset or by
         * using a more powerful backend for your data.
         */
        return new CSVRecordset(
            // the filename where data is stored
            'countries.csv',

            // a function which will convert an array of arrays to an array of objects
            function(array $rows) {
                foreach ($rows as $i => $row) {
                    $rows[$i] = new Country($row[0], $row[3], $row[1], $row[2]);
                }
                return $rows;
            }
        });

    }
}

Select by a key

<?php
// get a particular country
$norway = Country::all()->eq('name', 'Norway');

// get countries sorted by latitude
$countries = Country::all()->order('latitude');

// get countries sorted by name where latitude is greater than 70
$countries = Country::all()->gt('latitude', 70)->order('name');

charm / recordset

Maintainers

Details