SQL Generation

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Bruno De Barros
Forum Commoner
Posts: 82
Joined: Mon May 12, 2008 8:41 am
Location: Ireland

SQL Generation

Post by Bruno De Barros »

I have seen database abstraction classes and all of that, and I'd admit, they are pretty good, but truth be said, I love not writing SQL. It just makes my job extremely easier.

I made a small Database Handling class, which has been extremely helpful. I'm also growing it to make it more easy to abstract (i.e. add other database systems), and more powerful (i.e. more queries generated).

It goes like this:

Code: Select all

 
$insert = new Insert('table', $database_handler); # The database handler is optional. If a database handler is found, and if it is connected to a database, there is no need to provide the database handler, just like PHP does for the optional link identifier on MySQL. The database handler also tells the Insert class what the current database is, so the Insert class can find a MySQL Insert or a SQLite Insert, or whatever insert is needed.
$insert->field('FIELD','VALUE');
if ($insert->execute()) {
# Query was executed properly. Continue.
} else {
# Query failed.
$insert->error; # Contains the error message given by the database.
}
 
# You can also use method chaining.
 
if ($insert->field('FIELD','VALUE')->execute()) {
# Query was executed properly. Continue. You can also execute more queries with the same object instance, if you want to, to save on performance.
}
 
What do you think? So far, with this, I don't have to worry with SQL anymore (i never really liked it) or SQL Injections (by the way, I read advanced SQL injection techniques, and I have protected myself against them :)). I also am considering transactions, and more advanced stuff, to make it a more fully fledged database abstraction class, but I didn't need them yet, and I am trying to put this whole database system to the test with unit testing, and also make a couple more queries (for the tests and for installers, like creating / dropping tables, etc).

So, what is your view on SQL code generation? Currently, they work properly and have been a blessing (in my opinion). It's also less error-prone than writing my own SQL.

I am trying to make the move to PDO, and just generate the SQL code for the PDO Statements. It's just an idea, but I think it's better to use an already made and tested database abstraction class than try to write my own.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SQL Generation

Post by Christopher »

You should look a the Skeleton SQL classes. They have a nice fluent interface and produce SQL so they can be used with any DB library. I think the main outstanding feature is to make JOIN ON more full featured.
(#10850)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: SQL Generation

Post by Mordred »

We had a discussion about this a couple of months ago, look in the backpages here.

SQL generators should return SQL statements, not call the database. Heed arborint's advice (and mine ;))

I'm all for SQL generation, but I think it should be approached from a higher level. I declare my domain objects in a special way, so the declaration is used to define the underlying database structure. This includes one to one, one to many and many to many relationships between tables. The needed joins (for "one to one" rels) are handled automatically. Then I have a SQL generator with methods for complete statements: something like

Code: Select all

$sStatement = $pSql->GetSelect($sView, $nStart, $nCount, $order, $aSearches);
view = subset of "fields"
start, count and order are obvious
"searches" is the variable data in the WHERE clause. Each field type has a default operator for the where clause, but the view can customize it if needed.

Having a fluid interface that mimics the SQL statement flow, like $pSql->select("...")->from("...)->where("...")->orderby("...") is nice and clean, but I prefer higher level APIs that do "what I mean" in less code.
Bruno De Barros
Forum Commoner
Posts: 82
Joined: Mon May 12, 2008 8:41 am
Location: Ireland

Re: SQL Generation

Post by Bruno De Barros »

That is a good idea. If it generated the SQL, I could then use it in whatever library I wanted, be it the standard mysql_query() function or the PDO library, or ADODB, etc. Intelligent, indeed :P.
Post Reply