Login   Register  
PHP Classes
elePHPant
Icontem

SQL Parse and Compile: Parse and compose SQL queries programatically

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us

  Author Author  
Picture of Tom Schaefer
Name: Tom Schaefer is available for providing paid consulting. Contact Tom Schaefer .
Classes: 39 packages by
Country: Germany Germany
Age: ???
All time rank: 1109 in Germany Germany
Week rank: 129 Down6 in Germany Germany Up
Innovation award
Innovation award
Nominee: 9x

Winner: 1x


  Detailed description   Download Download .zip .tar.gz  
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.

  Classes of Tom Schaefer  >  SQL Parse and Compile  >  Download Download .zip .tar.gz  >  Support forum Support forum (4)  >  Blog Blog  >  RSS 1.0 feed RSS 2.0 feed Latest changes  
Name: SQL Parse and Compile
Base name: parsecompile
Description: Parse and compose SQL queries programatically
Version: 0.3
PHP version: 5.0
License: Artistic License
All time users: 1701 users
All time rank: 2120
Week users: 2 users
Week rank: 882 Up
 
  Groups   Screenshots Screenshots   Rate classes User ratings   Applications   Files Files  

  Groups  
Group folder image PHP 5 Classes using PHP 5 specific features View top rated classes
Group folder image Databases Database management, accessing and searching View top rated classes
Group folder image Text processing Manipulating and validating text data View top rated classes

  Files folder image Screenshots  
sql01.png
File Role Description
Accessible without login Image file sql01.png Screen Classes UML
Accessible without login Image file sql02.png Screen Bottom


  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

  User ratings  
RatingsUtility Consistency Documentation Examples Tests Videos Overall Rank
All time: Sufficient (75%) Perfect (100%) - Good (91%) - - Not sure (56%) 1112
Month: Not yet rated by the users

  Applications that use this package  
No pages of applications that use this class were specified.
Add link image If you know an application of this package, send a message to the author to add a link here.
  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

Download Download all files: parsecompile.tar.gz parsecompile.zip
NOTICE: if you are using a download manager program like 'GetRight', please Login before trying to download this archive.