Tencent cloud database team: the use of sql-parser component in phpMyAdmin

Keywords: MySQL SQL PHP Database phpMyAdmin

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

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:

t3, t4)","alias":null,"function":null,"subquery":null},"on":[{"identifiers":

Parsing affairs

require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Parser;

    'SELECT @A:=SUM(salary) FROM table1 WHERE type=1;' .
    'UPDATE table2 SET summary=@A WHERE type=1;' .

$parser = new Parser($query);
$stmt = $parser->statements[0];
echo json_encode($stmt);

Output results:


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:


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);


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:

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';


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:

require_once '../sql-parser/vendor/autoload.php';

use SqlParser\Parser;
use SqlParser\Components\Expression;

$query  = <<&lt;STR
ALTER TABLE `tbl` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED NOT NULL;
select * from tbl3 where id = 3

$parser = new Parser($query);

//Processing the first statement
$statement_0 = $parser-&gt;statements[0];
$statement_0->table  = new Expression(
    'db2', 'tb2', ''

//Processing the second statement
$statement_1 = $parser->statements[1];
$statement_1->table  = new Expression(
    'db3', 'tb3', ''

Output results:

margin@margin-MB1:~/code/parserTest$ php build.php 
string(85) "ALTER TABLE `db2`.`tb2` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT 
string(78) "ALTER TABLE `db3`.`tb3` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED 

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.~

Posted by jboku on Fri, 19 Apr 2019 18:09:34 -0700