README

Library for handling custom query expressions that can be translated into SQL CRUD queries for any database type

Getting Started

Use composer package manager to install QEL.

composer require firewox/qel

Prerequisites & configurations

This library requires at least PHP 7.0 with PDO extension to run. In your PHP file include composer autoload:

require_once 'vendor/autoload.php';

Structuring your code

QEL is simply a language to express your data queries independent of the underlying platform or implementations.

Define model classes with public properties directly mapping database table fields

use Firewox\QEL\Model;

class User extends Model
{

  const SCHEMA = 'public';
  const TABLE = 'user';

  public $id;
  public $username;
  public $password;
  ...

Implement Manager abstract method defined in Model parent class

public function manager(): FunctionCommon
  {

    // Get column conversion / transformation maps
    $forwardMap = [];
    $reverseMap = [];

    // Define functions associated with this Model
    $functions = [];

    // Define joins to this model
    $joins = [];

    // Instantiate function class
    return new FunctionCommon(
      $this,
      self::SCHEMA,
      self::TABLE,
      self::PRIMARY_KEY,
      $joins,
      $forwardMap,
      $reverseMap,
      $functions);

  }

You can also add column mapping, joins, and functions that affect the Model child class. Please see the advanced topics below for more information on how you can accomplish that.

Implement Databases abstract method defined in Model parent class. This method must return DatabaseInterface instance, which can be one of PostgreSQL, MySQL etc. database. These database clients also support in-memory data caching to speed up data queries. Currently supported is Redis (https://redis.io)

  public function database(): DatabaseInterface
  {

    // Postgres singleton instance (but you can use constructor directly)
    return Db::get('127.0.0.1', 5432, 'sample', 'postgres', 'admin', Redis::default());

  }

Advanced features

You can define add column mapping, joins, and functions that affect the Model child class and consequently the output queries for any of the implemented database clients.

Model functions

// Define functions associated with this Model
$functions = [
  'fx:count' => new SQLFx('count', [ new SQLFxParam('id')], 'Database COUNT function.')
];

Joins

// Define joins to this model
$joins = [
  'user' => new JoinWireMap(User::class, QueryJoin::TYPE_LEFT, 'userid', 'id')
];

Reverse column mapping

$reverseMap = [
    'activated' => 'CAST(COALESCE(activated, FALSE) AS integer) AS activated',
    'geom' => 'ST_ASText(geom) AS geom'
]

Forward column mapping

$forwardMap = [
    'geom' => 'ST_GeomFromText(:geom)'
]

Usage examples

QEL currently has 3 fundamental uses, which are (1) Data projection, (2) data updating, and (3) data deletion. To achieve these there are 3 helpers defined in each database client:

Data projection

public function generateJoinSelect(
  string $columns,
  string $table,
  array $joins,
  ?string $where = null,
  ?string $order = null,
  int $offset = 0,
  int $limit = 0): string;

Data updating

public function generateJoinUpdate(
  array $columns, 
  string $table, 
  array $joinedTables,
  array $conditions,
  ?string $where = null): string;

Data deletion

public function generateJoinDelete(
  string $table,
  array $joinedTables, 
  array $conditions,
  string $where): string;

Example 1: Generating data projection SQL

$session = new Session();
$man = $session->manager();

// Get all sessions where user id is 1
$qel = 'user.id = 1';

// Determine join depth
$depth = $man->determineJoinDepth($qel);      // Expected depth is 1 (number of dots in query)

// Get qel evaluator for to parse QEL
$evaluator = $man->getQueryEvaluator($depth);
$parser = new QueryParser($evaluator);

// Parse QEL
$parser->parse($qel);

// Create select query base on session database (ie. PgSQL)
$sql = $man->getDb()->generateJoinSelect(
  $evaluator->getProjection(),
  $man->getTableWithAlias(),
  $evaluator->getJoinQueries(),
  $evaluator->getQuery(),
  $evaluator->getOrder()
);

echo $sql;

// OUTPUT
// 'SELECT * FROM public.session AS a 
//    LEFT OUTER JOIN public.user AS b 
//    ON a.userid = b.id WHERE b.id = 1';

We can even call functions that we have defined in the model functions variables:

$qel = 'fx:count(id) of user.id = 1';

SQL generated will be:

SELECT count ( a.id ) FROM public.session AS a 
    LEFT OUTER JOIN public.user AS b 
        ON a.userid = b.id WHERE b.id = 1

Example 2: Generating data update SQL

We can use the same QEL defined in example 1 to generate our update query

// Create update join query base on session database (ie. PgSQL)
$sql = $man->getDb()->generateJoinUpdate(
  $man->getData(),
  $man->getTableWithAlias(),
  $evaluator->getJoinTables(),
  $evaluator->getJoinConditions(),
  $evaluator->getQuery()
);

echo $sql;

// OUTPUT
// 'UPDATE public.session AS a 
// SET id = :id, token = :token, userid = :userid, expireson = :expireson
// FROM public.user AS b WHERE a.userid = b.id AND (b.id = 1)';

Example 3: Generating data deletion SQL

// Create delete join query base on session database (ie. PgSQL)
$sql = $man->getDb()->generateJoinDelete(
  $man->getTableWithAlias(),
  $evaluator->getJoinTables(),
  $evaluator->getJoinConditions(),
  $evaluator->getQuery()
);

echo $sql;
// OUTPUT
// DELETE FROM public.session AS a 
// USING public.user AS b 
// WHERE a.userid = b.id AND (b.id = 1)

firewox / qel

Maintainers

Details