phpMyAdmin is an open source database management tool based on Web-side operation, supporting the management of MySQL and MariDB databases. phpMyAdmin's program is mainly developed with php and javascript. Its installation and use are relatively simple and many related introductions are not repeated. Today we will introduce a core component of the source code, sql-parser.
Introduction to sql-parser
The main purpose of sql-parser component is to perform lexical analysis and grammatical analysis of SQL statements, and then realize the deconstruction, processing, replacement and reassembly of SQL statements. In addition, it can also process SQL with high light. Sql-parser is implemented in pure PHP language. It is also one of the few modules in the whole phpMyAdmin source code that has clear code structure and conforms to the current PHP PSR standard specification.
sql-parser component installation
php, git client, and composer php package management tools need to be installed beforehand
margin@margin-MB1:~/tmp$ sudo git clone https://github.com/phpmyadmin/sql-parser.git
margin@margin-MB1:~/tmp$ cd sql-parser && sudo composer install
After the component is installed, the specific calls are described below.
Parsing common sentences
<?php
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Parser;
$query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
. 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)';
$parser = new Parser($query);
$stmt = $parser->statements[0];
echo json_encode($stmt);
The $parser variable in the run result is a large object that stores the lexical analysis result of the $query statement, $query - > list, the grammatical analysis result, $query-statements, and error messages. The structure of $query-states is as follows:
{"expr":
[{"database":null,"table":null,"column":null,"expr":"*","alias":null,"function":n
ull,"subquery":null}],"from":
[{"database":null,"table":"t1","column":null,"expr":"t1","alias":null,"function":
null,"subquery":null}],"partition":null,"where":null,"group":null,"having":null,"
order":null,"limit":null,"procedure":null,"into":null,"join":
[{"type":"LEFT","expr":{"database":null,"table":null,"column":null,"expr":"(t2,
t3, t4)","alias":null,"function":null,"subquery":null},"on":[{"identifiers":
["t2","a","t1"],"isOperator":false,"expr":"(t2.a=t1.a"},{"identifiers":
[],"isOperator":true,"expr":"AND"},{"identifiers":
["t3","b","t1"],"isOperator":false,"expr":"t3.b=t1.b"},{"identifiers":
[],"isOperator":true,"expr":"AND"},{"identifiers":
["t4","c","t1"],"isOperator":false,"expr":"t4.c=t1.c)"}],"using":null}],"union":
[],"options":{"options":[]},"first":0,"last":50}
Parsing affairs
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Parser;
$query = 'START TRANSACTION;' .
'SELECT @A:=SUM(salary) FROM table1 WHERE type=1;' .
'UPDATE table2 SET summary=@A WHERE type=1;' .
'COMMIT;';
$parser = new Parser($query);
$stmt = $parser->statements[0];
echo json_encode($stmt);
Output results:
{"type":1,"statements":[{"expr":
[{"database":null,"table":null,"column":null,"expr":"@A:=SUM(salary)","alias":nul
l,"function":"SUM","subquery":null}],"from":
[{"database":null,"table":"table1","column":null,"expr":"table1","alias":null,"fu
nction":null,"subquery":null}],"partition":null,"where":[{"identifiers":
["type"],"isOperator":false,"expr":"type=1"}],"group":null,"having":null,"order":
null,"limit":null,"procedure":null,"into":null,"join":null,"union":[],"options":
{"options":[]},"first":1,"last":19},{"tables":
[{"database":null,"table":"table2","column":null,"expr":"table2","alias":null,"fu
nction":null,"subquery":null}],"set":[{"column":"summary","value":"@A"}],"where":
[{"identifiers":
["type"],"isOperator":false,"expr":"type=1"}],"order":null,"limit":null,"options"
:{"options":[]},"first":20,"last":35}],"end":
{"type":2,"statements":null,"end":null,"options":{"options":
{"1":"COMMIT"}},"first":36,"last":37},"options":{"options":{"1":"START
TRANSACTION"}},"first":0,"last":0}
In addition to the above two statements, sql-parser also supports parsing stored procedures and almost all MySQL grammars, no longer one by one. The following is an example of the usage of its SQL constructor.
Assemble SQL statements
Assemble the select statement:
<?php
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Components\OptionsArray;
use SqlParser\Components\Expression;
use SqlParser\Components\Condition;
use SqlParser\Components\Limit;
use SqlParser\Statements\SelectStatement;
$stmt = new SelectStatement();
$stmt->options = new OptionsArray(array('DISTINCT'));
$stmt->expr[] = new Expression('sakila', 'film', 'film_id', 'fid');
$stmt->expr[] = new Expression('COUNT(film_id)');
$stmt->from[] = new Expression('', 'film', '');
$stmt->from[] = new Expression('', 'actor', '');
$stmt->where[] = new Condition('film_id > 10');
$stmt->where[] = new Condition('OR');
$stmt->where[] = new Condition('actor.age > 25');
$stmt->limit = new Limit(1, 10);
var_dump($stmt->build());
Output results:
margin@margin-MB1:~/code/parserTest$ php build.php
string(137) "SELECT DISTINCT `sakila`.`film`.`film_id` AS `fid`, COUNT(film_id)
FROM `film`, `actor` WHERE film_id > 10 OR actor.age > 25 LIMIT 10, 1 "
Assemble trigger statements:
<?php
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Components\Expression;
use SqlParser\Components\OptionsArray;
use SqlParser\Statements\CreateStatement;
$stmt = new CreateStatement();
$stmt->options = new OptionsArray(array('TRIGGER'));
$stmt->name = new Expression('ins_sum');
$stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT'));
$stmt->table = new Expression('account');
$stmt->body = 'SET @sum = @sum + NEW.amount';
var_dump($stmt->build());
Output results:
margin@margin-MB1:~/code/parserTest$ php build.php
string(89) "CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum
= @sum + NEW.amount"
SQL reprocessing
Multiple statements are processed together:
<?php
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Parser;
use SqlParser\Components\Expression;
$query = <<<STR
ALTER TABLE `tbl` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `tbl` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED NOT NULL;
select * from tbl3 where id = 3
STR;
$parser = new Parser($query);
//Processing the first statement
$statement_0 = $parser->statements[0];
$statement_0->table = new Expression(
'db2', 'tb2', ''
);
var_dump($statement_0->build());
//Processing the second statement
$statement_1 = $parser->statements[1];
$statement_1->table = new Expression(
'db3', 'tb3', ''
);
var_dump($statement_1->build());
Output results:
margin@margin-MB1:~/code/parserTest$ php build.php
string(85) "ALTER TABLE `db2`.`tb2` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT
NULL AUTO_INCREMENT"
string(78) "ALTER TABLE `db3`.`tb3` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED
NOT NULL"
These are some basic usage examples of sql-parser component. The sql-parser component of phpMyAdmin has rich and complete functions. This article is limited in space and can not be exhaustive. Interested readers can read the source code to understand more advanced usage.
Related recommendations
What is cloud database MySQL?
How to complete the migration of MySQL instances with total storage capacity of 20T?
CDB for MySQL related documents
This article has been authorized by the author to be published by Tencent Cloud Technology Community. Please indicate if it is reproduced. Article provenance For more dry goods of cloud computing technology, please go to Tencent Cloud Technology Community
Welcome your attention Tencent Cloud Technology Community - Official Home Page of Blog Garden We will continue to recommend high-quality technical articles in the blog park.~