Dynamic SQL Object Documentation

$name = $this->api->db->dsql() ->table('user') ->field('name') ->where('id',$_GET['id']) ->do_getOne();

Object-oriented interface to SQL language. Many objects in Agile Toolkit rely on DSQL.

In Agile Toolkit, DSQL is a built-in "Object Relationship Manager" which implements object-oriented API to interact with SQL database similar to. A typical developer for Agile Toolkit would probably experience no need to interact with DSQL at all. For expert developers DSQL provides an awesome way to extend basic functionality of Models and Views.

DSQL is extensively used by "Model_Table". When compared to alternative Object Relationship Manager implementations, DSQL exhibits several unique attributes and goals.

Table of Contents:

Goals and Features of DSQL

Collects all information about the query into one object;

Once a query object is created it will accumulate information about SQL conditions, joins, fields, tables. It even stores information about parametric variables and sub-queries.

This is really important when you need to pass query information from one object to another, between models or even allow controller or add-on to interact with standard query. Multiple queries may exist and can even be executed simultaneously without affecting each-other

Allow to modify existing query dynamically;

Most ORMs produce a query in one go. You can't create query in one object then pass it on to another object to add additional queries or joins.

DSQL allows you to add or remove fields, conditions, arguments or anything else at any time. Even after you perform one select, you can re-use the object.

Permit use of customizations for different SQL vendors;

When you design queries yourself, you must be aware of differences between different SQL implementations and extensions.

DSQL can be used with wide range of database vendors - MySQL, SQLite, PostrgreSQL, Oracle. Queries are optimized to match syntax of your database of choice.

Implement effective protection against SQL injections;

Many ORMs give you a way to use parametric queries, but would not enforce their use. This may still cause SQL injection due to developer's mistake.

DSQL obsoletes SQL language completely. You call functions instead and passing arguments, which become parametric values inside DSQL. It's virtually impossible to produce a unsafe query by accident.

Support select, updates, insert and function calls;

Some ORM will allow you to perform selects, but will leave updates, deletes and other statements to ActiveRecord. Others will not let you use custom queries or function calls.

Agile Toolkit's DSQL does not attempt to reduce SQL to a lowest denominator. Instead you can define and use any query type your RDBMS supports.

Allow using other DSQL object as sub-query;

While most ORM will merely help you build a query, combining multiple queries together is virtually impossible. For example, how can you use one query as a sub-query inside another and maintain all parameters?

Agile Toolkit makes it possible to specify other queries as fields, in conditions or anywhere else while it properly handles parametric parameters.

Well suitable for automated use for models.

Typical ORM is built to be used by developers directly. As a result they offer very little benefit to just writing queries directly.

DSQL is designed to be used by a layer of Models and Views in Agile Toolkit which completely hide presence of DSQL from developer. Developers can still manipulate the query by, for example, adding additional condition into a generic view or model.

What DSQL is not?

In the spirit of Agile Toolkit — all of it's classes are strictly focused on a one simple task, without trying to do everything. Here are things which DSQL will not do and why: No generation of PHP code

Generated code is bad for many reasons and is against one of the core principles of Agile Toolkit. See also next point.

No knowledge of your database

DSQL help you to cerate queries. It does not attempt to look into your database, will not create field caches, hold information about field types or joins. That's job of a "Model" in Agile Toolkit.

No attempts to change database structure

Several ORMs will try to create, alter, modify or upgrade your database for you. There are several reasons why DSQL does not do that.

Security — your application must not be capable of changing your database structure. That's job of your installer.

Migration flexibility — Agile Toolkit has a method for upgrading database schema by executing SQL scripts directly. This makes it more flexible to not only change schema, but also to alter / fix data.

Features — Several RDBMS have very powerful capabilities. DSQL will not attempt to restrict you from using them.

DSQL is compact and fast

All of DSQL is implemented in under 1000 lines of PHP code making it very lightweight and fast.

Other Resources