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
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 |
| |
|
|
Innovation award
Nominee: 9x
Winner: 1x |
|