sqlparser's Introduction

SQLParser

SQL-Parser

Why?

Sometimes we need to parse and validate SQL.

What does it do?

It parses SQL (mostly MySQL's SQL) and returns the SQL query as an object. This object can be modified programmatically to generate another SQL query.

How to install?

composer install crodas/sql-parser

How to use it?

require __DIR__ . "/vendor/autoload.php";

$parser = new SQLParser;
$queries = $parser->parse("SELECT * FROM table1 WHERE id = :id");
var_dump(get_class($queries[0])); // string(16) "SQLParser\Select"
var_dump($queries[0]->getTable()[0]->getValue()); // string(6) "table1"
/*
 array(1) {
   [0] =>
   string(2) "id"
   }
*/
var_dump($queries[0]->getVariables()); 

// SELECT * FROM 'table1' WHERE 'id' = :id
echo $queries[0] . "\n";

SQLParser\Writer\SQL::setInstance(new SQLParser\Writer\MySQL);

// SELECT * FROM `table1` WHERE `id` = :id
echo $queries[0] . "\n";

TODO:

Better documentation
Fluent-Interface to generate SQL statements and alter the parsed content
parse CREATE TABLE/ALTER TABLE (for SQLite, MySQL and PostgreSQL flavors)

sqlparser's People

Contributors

Stargazers

Watchers

sqlparser's Issues

Issue parsing SELECT statement with database

Thanks for your work. I ran across an error when i tried to parse a SELECT statement which contains the database name.

SELECT myAttribute FROM myDatabase.myTable;
Illegal offset type vendor\crodas\sql-parser\src\SQL\Select.php on line 50

Would be nice if you could take a look at it.

Some important bugs

IS and IS NOT are treated and translated as = and !=, but x IS NULL is not the same as x = NULL
OR and AND statements are parsed with equal precedence, though AND must have more precedence (maybe it is the problem with many more operators
Parenthesis dissapear when writting the query

SQL\Select->getAllColumnsAsArray - new method to retrieve all columns!

Please add similar function as below.

public function getAllColumnsAsArray()
{
$result = [];
foreach ($this->columns as $k => $v) {
	if (!empty($v[1])) {
	    $result[] = $v[1];
	} else {
		$result[] = $v[0]->getMembers()[0];
	}
}
return $result;
}

This should return:

Array
(
[0] => ID
[1] => Code
[2] => Name
[3] => Parent
)