README

Introduction

YAQB is a very simple, stupid implementation framework agnostic to dynamically create SQL query strings and parameters. It does not have database specific functionality nor does it actually query the database.

Use cases

• Base for yet another roll your own ORM.
• Required to compose many queries then bulk execute them.
• Pre-generate known static CRUD SQL queries.
• Generate queries in which the same query may be required to be queried in various ways e.g. asynchronously in web request, synchronously in background script or task. Same query but different drivers running it.

Licence

All source files in YAQB are released subject to the terms of the MIT license, as written in the included LICENSE.txt file.

Installation

Composer

YAQB can be installed with composer (http://getcomposer.org/).

1. Install composer:

   $ curl -s https://getcomposer.org/installer | php
   

2. Require YAQB as a dependency using composer:

   $ php composer.phar require victorium/yaqb
   

Getting Started

This is a simple example on getting started with YAQB:

<?php

require PATH_TO_YAQB_SRC . "bootstrap.php";

$builder = \Yaqb\Builder::init();
$builder->select("pointer"); // start a simple select statement

echo $builder->getSql(); // get the SQL
print_r($builder->getParameters()); // get the SQL parameters

Features

YAQB supports the following SQL features:

SELECT

<?php

Builder::init()->select()->column("1+7", "total");
// SELECT 1+7 AS total

Builder::init()->select("pointer");
// SELECT * FROM pointer

Builder::init()->select("pointer", "id, b");
// SELECT id, b FROM pointer

Builder::init()->select("pointer")->where("id", 4);
// SELECT * FROM pointer WHERE (id=:id_0)

Builder::init()->select("pointer")->where("id", [4, 5, 7]);
// SELECT * FROM pointer WHERE (id IN (:id_0_0, :id_0_1, :id_0_2))

Builder::init()->select("pointer")->where("id", 4, ">");
// SELECT * FROM pointer WHERE (id>id_0)

Builder::init()->select("pointer")->where("id", 4)->orCondition()->where("id", 5);
// SELECT * FROM pointer WHERE (id=:id_0 OR id=:id_1)

Builder::init()->select("pointer")
				->where("id", 4)
				->orCondition()
				->subWhere()
					->where("id", 5)
					->andCondition()
					->where("b", 50)
				->subWhereEnd();
// SELECT * FROM pointer WHERE (id=:id_0) OR (id=:id_1 AND b=:b_2)

Builder::init()->select("pointer")->orderBy("b");
// SELECT * FROM pointer ORDER BY b

Builder::init()->select("pointer")->limit(10)->offset(10);
// SELECT * FROM pointer LIMIT 10 OFFSET 10

Builder::init()->select("pointer")->column("SUM(b)", "b_sum")->groupBy("id")->having("b_sum > 70");
// SELECT SUM(b) AS b_sum FROM pointer GROUP BY id HAVING b_sum > 70

Builder::init()->select("pointer")->whereRaw("id=(SELECT id FROM setter WHERE b=40)");
// SELECT * FROM pointer WHERE (id=(SELECT id FROM setter WHERE b=40))

Builder::init()->select("pointer")->whereRaw("id=(SELECT id FROM setter WHERE b=:b)")->addParameters(["b" => 40]);
// SELECT * FROM pointer WHERE (id=(SELECT id FROM setter WHERE b=:b))

Builder::init()->select("pointer")->column("b")->action("DISTINCT");
// SELECT DISTINCT b FROM pointer

$subBuilder = Builder::init("sub")->select("pointer")->column("1")->where("id", 4);
Builder::init()->select()->action("EXISTS")->subQuery($subBuilder->getSql(), "p_exists");
// SELECT EXISTS (SELECT 1 FROM pointer WHERE (id=:sub_id_0)) AS p_exists

Builder::init()->select("pointer")->innerJoin("polygon", "pointer_id=id");
// SELECT * FROM pointer INNER JOIN polygon ON pointer_id=id

Builder::init()->select("pointer")->leftOuterJoin("polygon", "pointer_id=id");
// SELECT * FROM pointer LEFT OUTER JOIN polygon ON pointer_id=id

$builder = Builder::init()->select("pointer", "p1")
            ->innerJoin("polygon AS p2", "p1.pointer_id=p2.id");
// SELECT * FROM pointer AS p1 INNER JOIN polygon AS p2 ON p1.pointer_id=p2.id

UPDATE

<?php

Builder::init()->update("pointer")->values(["id" => 40]);
// UPDATE pointer SET id=:id

Builder::init()->update("pointer")->values(["id" => 40])->where("b", 40);
// UPDATE pointer SET id=:id WHERE (b=:b_0)

Builder::init("nms")->update("pointer")->action("IGNORE")->values(["id" => ["id+1"], "b" => 40])->where("b", 100, ">");
// UPDATE IGNORE pointer SET id=id+1, b=:nms_b WHERE (b>:nms_b_0)

INSERT

<?php

Builder::init()->insert("pointer")->values(["a" => 20, "b" => 40]);
// INSERT INTO pointer (a, b) VALUES (:a_0, :b_0) DEFAULT VALUES

Builder::init()->insert("pointer")->setDefaultValues(false)->values(["a" => 20, "b" => 40]);
// INSERT INTO pointer (a, b) VALUES (:a_0, :b_0)

Builder::init()->insert("pointer")->values(["a" => 20, "b" => 40])->values(["a" => 10, "b" => 30]);
// INSERT INTO pointer (a, b) VALUES (:a_0, :b_0), (:a_1, :b_1) DEFAULT VALUES

DELETE

<?php

Builder::init()->delete("pointer");
// DELETE FROM pointer

Builder::init()->delete("pointer")->where("id", 17);
// DELETE FROM pointer WHERE (id=:id_0)

Builder::init()->delete("pointer")->action("IGNORE")->where("id", 17);
// DELETE IGNORE FROM pointer WHERE (id=:id_0)

Builder::init()->delete("pointer")->where("id", 17)->orderBy("b DESC");
// DELETE FROM pointer WHERE (id=:id_0) ORDER BY b DESC