Login   Register  
PHP Classes
elePHPant
Icontem

Class: SQL Parse and Compile

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Tom Schaefer  >  SQL Parse and Compile  >  Download .tar.gz .zip  >  Support forum Support forum (3)  >  Blog Blog  >  RSS 1.0 feed RSS 2.0 feed Latest changes  
Name: SQL Parse and Compile
Support forum Support forum
Base name: parsecompile
Description: Parse and compose SQL queries programatically
Related classes: , , , , , , ,
Version: 0.3
PHP version: 5.0
License: Artistic License
All time users: 1446 users
All time rank: 2244
Week users: 3 users
Week rank: 1290
Picture of Tom Schaefer
Author: Tom Schaefer <e-mail contact>
Packages: 27 Browse this author's classes Browse this author's classes
Country: Germany Germany - PHP jobs in Germany
Age: ???
All time rank: 15810 in Germany Germany
Week rank: 140 Up5 Up in Germany Germany
Innovation award
Innovation award
Nominee: 4x


  Detailed description  
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.

 

  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

  Screenshots Screenshots   Rate classes User ratings   Applications   Files Files  
  Files folder image Screenshots  
Classes UML
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
 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  
Ratings
Utility
Consistency
Documentation
Examples
Tests
Videos
Overall
Rank
All time:
Sufficient (75.0%)
Perfect (100.0%)
-
Good (91.7%)
-
-
Not sure (56.7%)
1031
Month:
Not yet rated by the users

  Applications that use this class  
No application links were specified for this class.
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
Accessible without login Plain text file Sql.class.php Class Wrapper class for parsing and compiling sql, adding joins, condition etc.
Accessible without login Plain text file Sql_Compiler.class.php Class compiler class
Accessible without login Plain text file Sql_Lexer.class.php Class tokenizes a sql string
Accessible without login Plain text file Sql_Object.class.php Class sql object class (singleton)
Accessible without login Plain text file Sql_Parser.class.php Class parser class

  Files  /  Files folder image samples  
File Role Description
  Plain text file config.inc.php Conf. config
  Plain text file delete.sql Data delete sql
  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
  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
  Plain text file union.sql Data Example Union SQL
  Plain text file update.sql Data update sql

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

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

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

  Files  /  Files folder image 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 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.