RedSQL

RedSQL is a database agnostic helper for RedBean delivered as a plugin. It's a tiny, fun and predictable DSL to create SQL queries in a programmatic way.

Features

Express syntax
Dynamic API: available methods are just a mirror of your table (bean) structure.
Programmatic: avoid nasty string manipulations to achieve dynamic SQL construction.
Database agnostic: SQL inconsistencies across databases (aka Oracle) like LIMIT and OFFSET are gracefully normalized.
Lazy field loading: restrict wich fields you want to select.
Still looks like SQL

Installation

With Composer

{
  "require": {
    "redsql/redsql": "~1.0"
  }
}

Or just use your terminal: composer require redsql/redsql:~1.0 🎱

Phar (single file)

For the folks that are not using composer yet (why u no?) you can download redsql.phar single distribution file and include it on your project:

include 'path/to/redsql.phar';

Usage

RedSQL public API is ~~awsome~~ fluid and completely achieved with PHP ~~wizardry~~ magic. So, given the following table structure:

Express syntax:

$projects =
    R::redsql('project')
        ->name('like', '%project x')
        ->priority('>', 5)
        ->created_at('between', [$time1, $time2])
        ->find($limit, $offset);

// parses to:

SELECT * FROM `project`
WHERE `name` LIKE ?
    AND `priority` > ?
    AND `created_at` BETWEEN ? AND ?
LIMIT ? OFFSET ?

+ Syntatic sugar:

$projects =
    R::redsql('project')
        ->name('like', '%secret%')->AND->priority('>', 9)
        ->OR
        ->OPEN
            ->code('in', [007, 51])
            ->AND->NOT->created_at('between', [$time1, $time2])
        ->CLOSE
        ->find($limit, $offset)

// parses to:

SELECT * FROM `project`
WHERE `name` LIKE ? 
    AND `priority` > ? 
    OR (
        `code` IN (?,?)
        AND  NOT  `created_at` BETWEEN ? AND ?
    )
LIMIT ? OFFSET ?

Select specific fields:

$projects = R::redsql('project', ['name', 'description'])->find($limit, $offset);

// parses to:

SELECT `name`, `description` FROM `project` LIMIT ? OFFSET ?

- Houston, we got Oracle! - No problem.

$projects = R::redsql('project', ['name', 'description'])->find($limit, $offset);

// parses to:

SELECT * FROM  (
    SELECT VIRTUAL.*, ROWNUM ROWOFFSET FROM  (
       SELECT `name`, `description` FROM `project`
    ) VIRTUAL
) WHERE ROWNUM <= ? AND ROWOFFSET >= ?

Public API

R::redsql($table, array $fields = [])

Returns a configured RedBeanPHP\Plugins\RedSql\Finder instance restricted to a given entity:

$project_query = R::redsql('project');

New conditions can be queued lazily:

$project_query->created_at('>', '2014-04-03 00:00:00');
$projects = $project_query->find(); // finally reach database

Finder can restrict what fields should be queried:

$project_query = R::redsql('project', ['name', 'priority']);
//
SELECT `id`, `name`, `priority` FROM `project`

find($limit = null, $offset = null)

Fetches an array of RedBean_OODBBean instances from database:

$projects = R::redsql('project')->NOT->priority(3)->find();
//
SELECT * FROM `project` WHERE  NOT  `priority` = ?

findFirst()

Applies LIMIT and ORDER BY ASC statements and fetches a single RedBean_OODBBean instance from database:

$project = R::redsql('project')->NOT->priority(3)->findFirst();
//
SELECT * FROM `project` WHERE NOT `priority` = ? ORDER BY `id` ASC LIMIT 1 OFFSET ?

findLast()

Applies LIMIT and ORDER BY DESC statements and fetches a single RedBean_OODBBean instance from database:

$project = R::redsql('project')->NOT->priority(3)->findLast();
//
SELECT * FROM `project` WHERE NOT `priority` = ? ORDER BY `id` DESC LIMIT 1 OFFSET ?

findAlike(array $conditions, $limit = null, $offset = null)

Applies a batch of conditions and fetches an array of RedBean_OODBBean instances from database:

$projects = R::redsql('project')->findAlike([
    'name' => 'X Project',
    'description' => '%secret%',
    'priority' => [1, 2, 3]
    ], $limit, $offset
);

//

SELECT *
FROM `project`
WHERE  `name` = ? 
    AND  `description` LIKE ?
    AND  `priority` IN (?,?,?)
LIMIT ?  OFFSET ?

toString()

Finders RedBeanPHP\Plugins\RedSql\Finder can be converted to plain SQL using (string) casting or toString method:

$project_finder = R::redsql('project');
echo (string) $project_finder;
$project_finder->NOT->priority(3);
echo $project_finder->toString();

// >

SELECT * FROM `project`
SELECT * FROM `project` WHERE  NOT  `priority` = ?

Database Support

If build badge is green it means RedSql latest version is working on:

Oracle
Postgre
MySQL
SQLite
CUBRID
~~MSSQL Server~~ (as soon as RedBean supports it)

Roadmap

RedBean ~4.0 support
Beans collection
Relationships
Single file release (phar)

Contributing

Fork redsql
Clone redsql git clone git@github.com:marcioAlmada/redsql.git
Install composer dependencies $ composer install --prefer-dist
Run desired unit tests $ phpunit or at least $ phpunit --group sqlite
Correct bug, implement feature
Back to step 4

When everything is ready, create a pull request to master branch :)

Building

Clone redsql git clone git@github.com:marcioAlmada/redsql.git
Install kherge/php-box
Run $ box build
A new phar will be available at dist folder

PS: This plugin follows specification discussed in #311.

marcioAlmada/redsql