README

用于处理自定义查询表达式，这些表达式可以转换为针对任何数据库类型的SQL CRUD查询的库

入门指南

使用Composer包管理器安装QEL。

composer require firewox/qel

先决条件与配置

此库至少需要PHP 7.0以及PDO扩展才能运行。在你的PHP文件中包含composer自动加载

require_once 'vendor/autoload.php';

代码结构

QEL是一种用于表达数据查询的语言，与底层平台或实现无关。

使用与数据库表字段直接映射的公共属性定义模型类

use Firewox\QEL\Model;

class User extends Model
{

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

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

实现Model父类中定义的Manager抽象方法

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

  }

您还可以添加列映射、连接和影响Model子类的函数。有关如何实现这些的更多信息，请参阅以下高级主题。

实现Model父类中定义的Databases抽象方法。此方法必须返回DatabaseInterface实例，可以是PostgreSQL、MySQL等数据库。这些数据库客户端还支持内存数据缓存以加快数据查询。目前支持的是Redis (https://redis.ac.cn)

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

  }

高级功能

您可以为任何实现的数据库客户端定义添加列映射、连接和影响Model子类的函数，从而影响输出查询。

模型函数

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

连接

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

反向列映射

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

正向列映射

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

用法示例

QEL目前有3种基本用途，分别是（1）数据投影、（2）数据更新和（3）数据删除。为此，每个数据库客户端都定义了3个辅助函数

数据投影

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

数据更新

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

数据删除

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

示例1：生成数据投影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';

我们甚至可以调用在模型函数变量中定义的函数

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

生成的SQL将如下所示

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

示例2：生成数据更新SQL

我们可以使用示例1中定义的相同QEL来生成我们的更新查询

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

示例3：生成数据删除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

维护者

详细信息