PHP SQL query

Since ORM is too heavy for my needs, I usually used DbSimple . However, after getting acquainted with Twig , which compiles the templates in php code, the idea to write something similar to work with the database periodically arose. And so I did it. The picture shows a request for PHP, which, after compilation, generates code for creating and executing a SQL query.

In the first implementation, I compiled a query from syntax similar to DbSimple in PHP code. The idea was to get the output code with native functions without any wrappers. At the same time, it was possible to tailor requests of any complexity and the speed of their analysis did not affect the runtime, since after compilation it was normal native code. However, the difficulty in debugging such queries (it was difficult to look for errors in the SQL syntax) and the fact that the query processing time is not so long compared with the query execution led to the fact that I refused to use this approach.

Not so long ago I came across a library for parsing PHP code on PHP-Parser tokens. At work, I write code in the ABAP language, in which the commands for working with the database are built into the language itself, so the idea came up: “What if you do something similar for PHP?”
')
The implementation scheme is quite simple and obvious: when a class is autoloading, we check its presence in the directory of compiled classes. If there is no compiled class, then we take the source file of the class, replace all the special commands in it and write the finished class into the directory of the compiled classes. All parsing is done by the PHP-Parser library. So that the compiler can understand that this is exactly the command he needs, we wrap everything into the namespace ML ( M acro L anguage). For example, in the code we write this:

\ML::SQL(Select( 'aa,bb', ucase('xx')->as('Uxx') ), from("MyTable")->as("tab"), where( like('aa','sha%'), _or( field('bb') == NULL, field('bb') == 2006 ) ), orderBy( "-aa,+bb" ), into($rows)->rows()); 

and get the output:

 $__driver0 = \ML\SQL::getDriver('c933f3523437d521bf59e9e6077255b9', array('server' => '***', 'database' => '***', 'user' => '***', 'pass' => '***', 'prefix' => 'MyCMS-', 'codepage' => 'utf8')); $__query1 = new \ML\SQL\Query($__driver0); $__query1->sql = 'SELECT `tab`.`aa` as `tab.aa`, `tab`.`bb` as `tab.bb`, UCASE(`tab`.`xx`) as `Uxx` FROM `MyCMS-MyTable` as `tab` WHERE `tab`.`aa` LIKE \'sha%\' AND (`tab`.`bb` IS NULL OR `tab`.`bb`=2006) ORDER BY `tab`.`aa` DESC, `tab`.`bb` ASC'; $rows = $__query1->rows(); 

In this case, we can use PHP variables directly in the query (which are inserted into the query with protection from SQL injection in PHP and MySQL ) and the function of conditional query generation _if. In particular, this code:

 \ML::SQL(Select( 'aa', _if($mode==1,'bb') ), from("MyTable")->as("tab"), where( field($fname) = $value ), into($rows)->row()); 

compiles to this code:

 $__driver0 = \ML\SQL::getDriver('c933f3523437d521bf59e9e6077255b9', array('server' => '***', 'database' => '***', 'user' => '***', 'pass' => '***', 'prefix' => 'MyCMS-', 'codepage' => 'utf8')); $__query1 = new \ML\SQL\Query($__driver0); $__query1->sql = 'SELECT `tab`.`aa` as `tab.aa`' . ($mode == 1 ? ', `tab`.`bb` as `tab.bb`' : '') . ' FROM `MyCMS-MyTable` as `tab` WHERE ' . $__driver0->getField('tab', $fname, '', '') . '=' . $__driver0->getValue($value); $rows = $__query1->row(); 

Since the implementation scheme is simple, I decided not to limit myself to SQL. To do this, the code does not look for specific \ ML \ SQL commands, but for all calls from the \ ML namespace. If such a call is found, then the presence of the class \ ML \ <class name> is checked. If there is such a class, then an object of this class is created and its method <class name> -> compile (...) is called, to which the tokens of the current call (the root node received from PHP-Parser ) + object of this class are transferred. This will allow to use this approach not only for accessing the database, but also for other needs (I have not yet come up with any :)). In particular, I plan to make an ORM extension over SQL. Those. I will call the \ ML \ ORM compile into the \ ML \ SQL code, which will already be compiled into PHP code. Moreover, since the time to parse an expression is not important, then when parsing an ORM call, you can spend time reading information about entities, their connections, fields, etc.

PS Although before doing the ORM, I will add the JOIN + command to the UPDATE and DELETE commands.

For those who are interested, a small test site for compiling code . If you find any error, write in the comments. So far this is version 0.1 alpha, so errors are very likely.

UPD: 2017.02.03 Added more examples

UPD: Next Version