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:

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');