Page 1 of 1

SQL Generation

Posted: Wed Jun 18, 2008 7:28 pm
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.

Re: SQL Generation

Posted: Wed Jun 18, 2008 9:31 pm
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.

Re: SQL Generation

Posted: Thu Jun 19, 2008 1:15 am
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.

Re: SQL Generation

Posted: Tue Jun 24, 2008 4:42 pm
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.