Page 1 of 1

DB Gateway generators?

Posted: Fri Mar 05, 2010 11:31 am
by alex.barylski
I am not a fan od ORM, mostly because my current gateway API consist of some fairly interesting SQL which would probably be difficult to easily express (or do at all) in ORM. I just find it easier to hammer out the SQL by hand and make it dynamic as required.

Of course this means a lot of similar code (especially in CRUD) and while I could use a trivial data mapper/AR to simply much of this SQL generation, there is the issue of consistency for me, having some code use direct SQL and others still use various data objects, just bugs me.

I'm looking for a tool that lets me build a database schema, specify relationships and maybe even the API of the gateway and have the software automgaically generate the gateway API for me, so I basically program the gateway code using a interface like phpMyAdmin or Drupal CCK/Views and voila. The output is optimized generated SQL hidden behind a interface which I can easily use throughout my applications.

I've toyed with some ideas myself but the project is not trivial and I would prefer something ASAP, so I figured I would ask and see what or how you replied.

Not looking for Doctrine, Propel, phpDataMapper, etc type solutions. Thanks

Cheers,
Alex

Re: DB Gateway generators?

Posted: Fri Mar 05, 2010 11:42 am
by josh
ORM = object relational mapping, whether you use mysql_query() or some framework, you are still doing ORM (if you use objects and databases in the same app). Your first sentence contradicts itself.

I believe phpmyadmin will already generate a select statement for you. Just create an empty object, create a find method, and paste the query. When it shows you the SQL is generating under "browse" on a table you can just ask it for PHP code.

Re: DB Gateway generators?

Posted: Fri Mar 05, 2010 12:09 pm
by alex.barylski
ORM = object relational mapping, whether you use mysql_query() or some framework, you are still doing ORM (if you use objects and databases in the same app). Your first sentence contradicts itself.
Ummm OK maybe it does, but that was not my intent. Simply to say, ORM does not work for me, as I frequently have to resort to hand written SQL, I might as well find a SQL generator that lets me build arbitraily complex queries using a GUI interface, like Drupal CCK/Views.
I believe phpmyadmin will already generate a select statement for you. Just create an empty object, create a find method, and paste the query. When it shows you the SQL is generating under "browse" on a table you can just ask it for PHP code.
You missed the point, I don't want to copy and paste. I want an powerful interface like PMA that allows me to express arbitrarily complex queries (JOIN, DISTINCT, etc), assign a method name and arguments and when I click the build button, it generates the entire API for DAL manipulation for me.

Now my models simply invoke a lower level DAL API and I never have to waste time writing 80% common code.

Re: DB Gateway generators?

Posted: Fri Mar 05, 2010 1:45 pm
by josh
PCSpectra wrote:Ummm OK maybe it does, but that was not my intent. Simply to say, ORM does not work for me, as I frequently have to resort to hand written SQL, I might as well find a SQL generator that lets me build arbitraily complex queries using a GUI interface, like Drupal CCK/Views.
I'm still not following. First of all I feel you should clarify what you mean by ORM? Do you mean ORM? or ORM framework?

Like I said hand-writing SQL can still be object relational mapping. So I still am not following.

Your question to me sounds like this "Hi, I don't like ORMs, don't suggest an ORM to me. But... I'm hoping someone can find me a decent ORM". Maybe I'm misunderstanding something due to terminology.

Also nothing will generate "fully optimized SQL", that's a fallacy

Re: DB Gateway generators?

Posted: Fri Mar 05, 2010 2:03 pm
by alex.barylski
I'm still not following. First of all I feel you should clarify what you mean by ORM? Do you mean ORM? or ORM framework?

Like I said hand-writing SQL can still be object relational mapping. So I still am not following.
ORM = Object Relational/Mapping. An attempt to solve the mis-match between the object world and relational world. Works for trivial things, like CRUD and some SELECT.
Your question to me sounds like this "Hi, I don't like ORMs, don't suggest an ORM to me. But... I'm hoping someone can find me a decent ORM". Maybe I'm misunderstanding something due to terminology.
Suffice it to say, ORM is not the solution I want, not that I have anything against objects, I use them lots, but in the case of a gateway, I want generated SQL, and an low-level API, something like:

create_record($f1, $f2, $f3)
update_record($p1, $f1, $f2, $f3)
return_record($p1)


The DAL API above would look something like:

Code: Select all

 
    public function return_record($id_primary)
    {
      $id_primary = mysql_real_escape_string($id_primary);
 
      $sql = "SELECT * FROM procedures WHERE id_primary = $id_primary";
      $res = mysql_query($sql);
      $row = mysql_fetch_assoc($res);
 
      return $row;
    }
 

Also nothing will generate "fully optimized SQL", that's a fallacy
Except a human, which is the whole point. Instead of writing the code above, a DBA would use an interface similar to PMA or Access or any of the DBMS out there. Look at Drupal CCK/Views for a clearer idea. The difference being that once the queries were defined, the app would generate an API like the above, which I could then call directly.

Tweaking the code manually could be possible but would defeat the purpose, the idea is to be able to express those queries in a visual manner using an GUI and rebuild the gateway code each time, much like an interface builder might work. Although admittedly interface is almost always required to be tweaked, this could probalby be avoided in DAL.

Cheers,
Alex

Re: DB Gateway generators?

Posted: Sat Mar 06, 2010 11:20 am
by josh
PCSpectra wrote:Tweaking the code manually could be possible but would defeat the purpose, the idea is to be able to express those queries in a visual manner using an GUI and
There you said it.... "and"... clear indication you're mixing two ideas ;-)
rebuild the gateway code each time, much like an interface builder might work. Although admittedly interface is almost always required to be tweaked, this could probalby be avoided in DAL.
Both tools exist. Glue them together?

We have code generators. And "PMA" (phpmyadmin).. god please chill with the abbreviations lol already does what you need for query building yes? Just write a utility to hook them together? Here's the code generator ;-)
http://framework.zend.com/manual/en/zen ... rator.html

Basically all the works done, youre just configuring it ;-) don't be lazy

cheers
11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.