PHP Classes

SQL Parse and Compile: Parse and compose SQL queries programatically

Recommend this page to a friend!
  Info   Screenshots Screenshots   View files View files (34)   DownloadInstall with Composer Download .zip   Reputation   Support forum (4)   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStar 54%Total: 1,868 All time: 2,113 This week: 455Up
Version License PHP version Categories
parsecompile 0.3Artistic License5.0PHP 5, Databases, Text processing
Description 

Author

This package can be used to parse and compose SQL queries programatically.

It can take an SQL query and parse it to extract the different parts of the query like the type of command, fields, tables, conditions, etc..

It can also be used to do the opposite, i.e. compose SQL queries from values that define each part of the query.


Features:
I. Parser
- insert
- replace
- update
- delete
- select
- union
- subselect
- recognizes flow control function (IF, CASE - WHEN - THEN)
- recognition of many sql functions

II. Composer (Compiler)
- insert
- replace
- update
- delete
- select
- union

III. Wrapper SQL
- object oriented writing of SQL statements from the scratch

i.e.:
#################################################
$insertObject = new Sql();
$insertObject
->setCommand("insert")
->addTableNames("employees")
->addColumnNames(array("LastName","FirstName"))
->addValues(
array(
array("Value"=>"Davolio","Type"=>"text_val"),
array("Value"=>"Nancy","Type"=>"text_val"),
)
);
$sqlout = $insertObject->compile();
#################################################

result:
echo $sqlout;
#################################################
INSERT INTO employees (LastName, FirstName) VALUES ('Davolio', 'Nancy')
#################################################



#################################################
a more advanced example:
#################################################

$sql = 'SELECT
countrylanguage.CountryCode,
country.Name,
country.Continent,
country.Region,
country.SurfaceArea,
city.District,
country.IndepYear,
country.Population,
city.CountryCode,
city.Name,
city.Population,
countrylanguage.Language AS lang,
countrylanguage.IsOfficial,
countrylanguage.Percentage,
country.GovernmentForm,
country.LocalName,
country.GNPOld,
country.GNP,
country.LifeExpectancy
FROM
country co
LEFT JOIN city ct ON co.Code = ct.CountryCode
LEFT JOIN countrylanguage cl ON cl.CountryCode = ct.CountryCode
WHERE
(co.Continent='Asia' AND cl.Language='Pashto')
GROUP BY
co.Name
HAVING
ct.CountryCode = 'AFG'
LIMIT 100
':

// using wrapper class
$sqlDef = new Sql();
$sqlDef->parse($sql);

// adding a left join
$sqlDef->setJoinLeft(
array(
'Left'=> array("Value"=>"employees.employeeID", "Type" => "ident"),
'Op'=> '=',
'Right'=> array("Value"=>1, "Type" => "int_val"),
)
);

$sqlout = $sqlDef->compile();
#################################################

result:

echo $sqlout;
#################################################
SELECT countrylanguage.CountryCode, country.Name, country.Continent, country.Region, country.SurfaceArea, city.District, country.IndepYear, country.Population, city.CountryCode, city.Name, city.Population, countrylanguage.Language AS lang, countrylanguage.IsOfficial, countrylanguage.Percentage, country.GovernmentForm, country.LocalName, country.GNPOld, country.GNP, country.LifeExpectancy
FROM country AS co
LEFT JOIN city AS ct ON co.Code = ct.CountryCode
LEFT JOIN countrylanguage AS cl ON cl.CountryCode = ct.CountryCode
LEFT JOIN employees ON employees.employeeID = 1
WHERE (co.Continent = 'Asia' and cl.Language = 'Pashto')
GROUP BY co.Name
HAVING ct.CountryCode = 'AFG'
LIMIT 0,100
#################################################

IV. Hint
Sql_Compiler is none validating, but throws errors on type mismatch or corrupt statements


Note:
If you have good ideas to improve this set of classes, let me know.

Acknowledgement:
Thanks a lot to George Antoniadis (Author: rephp framework, www.rephp.net) from noodles.gr for his qualified feedback.

Many thanks to all, which gave me qualified feedback and voted for this set of classes at the contest.

Innovation Award
PHP Programming Innovation award nominee
December 2008
Number 2


Prize: One copy of VS.PHP
Programmatically composing simple SQL queries is a relatively easy task. However, more complex queries are harder to compose.

This class provides a solution that not only simplifies the composition of complex queries using a fluent interface, but it can also parse, edit and rewrite predefined SQL queries.

Manuel Lemos
Picture of Tom Schaefer
  Performance   Level  
Name: Tom Schaefer is available for providing paid consulting. Contact Tom Schaefer .
Classes: 39 packages by
Country: Germany Germany
Age: ???
All time rank: 1088 in Germany Germany
Week rank: 246 Down12 in Germany Germany Down
Innovation award
Innovation award
Nominee: 9x

Winner: 1x

Screenshots  
  • sql01.png
  • sql02.png
  Files folder image Files  
File Role Description
Files folder imagesamples (9 files)
Files folder imageSql_Parser (8 files)
Files folder imageSql_Compiler (8 files)
Files folder imageSql_Dialect (1 file)
Files folder imageSql_Interface (2 files)
Accessible without login Plain text file autoload.php Aux. autoload function
Plain text file Sql.class.php Class Wrapper class for parsing and compiling sql, adding joins, condition etc.
Plain text file Sql_Compiler.class.php Class compiler class
Plain text file Sql_Lexer.class.php Class tokenizes a sql string
Plain text file Sql_Object.class.php Class sql object class (singleton)
Plain text file Sql_Parser.class.php Class parser class

  Files folder image Files  /  samples  
File Role Description
  Accessible without login Plain text file config.inc.php Conf. config
  Accessible without login Plain text file delete.sql Data delete sql
  Accessible without login Plain text file function.php Example example for composing function with alias from the scratch
  Accessible without login Plain text file index.php Example sample
  Accessible without login Plain text file insert.sql Data insert sql
  Accessible without login Plain text file select.sql Data sample select
  Accessible without login Plain text file test.txt Aux. Author's testing statements
  Accessible without login Plain text file union.sql Data Example Union SQL
  Accessible without login Plain text file update.sql Data update sql

  Files folder image Files  /  Sql_Parser  
File Role Description
  Plain text file Sql_ParserDelete.class.php Class parses delete statement into object
  Plain text file Sql_ParserFlow.class.php Class parses flow control functions into object
  Plain text file Sql_ParserFunction.class.php Class parses sql functions into object
  Plain text file Sql_ParserInsert.class.php Class parses insert statement into object
  Plain text file Sql_ParserReplace.class.php Class parses replace statement into object
  Plain text file Sql_ParserSelect.class.php Class parses select statement into object
  Plain text file Sql_ParserUnion.class.php Class parses union statement into object
  Plain text file Sql_ParserUpdate.class.php Class parses update statement into object

  Files folder image Files  /  Sql_Compiler  
File Role Description
  Plain text file Sql_CompilerInsert.class.php Class compile insert statement
  Plain text file Sql_CompilerUnion.class.php Class compile union statement
  Plain text file Sql_CompilerDelete.class.php Class compile delete statement
  Plain text file Sql_CompilerFlow.class.php Class compile flow control functions
  Plain text file Sql_CompilerFunction.class.php Class compile sql functions
  Plain text file Sql_CompilerReplace.class.php Class compile replace statement
  Plain text file Sql_CompilerSelect.class.php Class compile select statement
  Plain text file Sql_CompilerUpdate.class.php Class compile update statement

  Files folder image Files  /  Sql_Dialect  
File Role Description
  Accessible without login Plain text file Sql_DialectMysql.inc.php Aux. mysql dialect tokens

  Files folder image Files  /  Sql_Interface  
File Role Description
  Plain text file Sql_InterfaceCompiler.class.php Class interface for compiler classes
  Plain text file Sql_InterfaceParser.class.php Class interface for parser classes

 Version Control Unique User Downloads Download Rankings  
 0%
Total:1,868
This week:0
All time:2,113
This week:455Up
User Ratings User Comments (2)
 All time
Utility:75%StarStarStarStar
Consistency:87%StarStarStarStarStar
Documentation:-
Examples:83%StarStarStarStarStar
Tests:-
Videos:-
Overall:54%StarStarStar
Rank:2041
 
Great.
12 years ago (Paweł Napier)
67%StarStarStarStar
That package seems to be cool, but .
13 years ago (Francois Biot)
32%StarStar