I cc'ed the list since I think this is valuable discussion:

On Dec 10, 2007, at 7:39 PM, Mike McGonagle wrote:



On 12/10/07, Hans-Christoph Steiner <hans@eds.org> wrote:
It is useful to represent the pieces in Pd space, so you can understand what's going on.  That's one reason why I advocate having the core object represent the connection to the database rather than a query.  Otherwise, it's starts to become more like Max/MSP's mega-objects (coll, zl, etc) that are really like mini-applications than programming.  


At the same time, while you seem to want to abstract the query from the database connection objects, SQL is not SQL to all databases. One thing I have noticed is that in SQLite, you would create an autoincremented ID/index field using:

CREATE TABLE MINE (
   id integer primary key autoincrement not null
);

While in MySQL (from what I remember) you do this:

CREATE TABLE MINE (
   id INT auto_increment not null
PRIMARY KEY(id)
);

(Please not the difference in spelling "autoincrement".) So, while I can understand you desire to abstract these concepts out, I wonder if it would be possible. Also, I don't think that it should be the task of the external to "normalize" what SQL gets entered. One of the original design goals was to NOT have to actually parse the SQL, relying on the user to know what they are doing, and just feed the SQL into the database.

I can see that from this thread alone, that this is not going to be possible. Plus, if we are going to implement the idea of "placeholders", that Yes, we will need to be parsing some SQL.
 

There still can be query objects, they would just be designed to feed to the core database objects.  These query objects would then be usable if we maintain the same interface.


And yet, the differences between databases might actually make this difficult at best. That is why I still think there will end up being a different object for 'sqlite', postgres, mysql, etc...

I agree with you.  Ideally, there would be a common SQL, but there is not.  We could try to make a common SQL, I think things like Perl:DBI do that (I could be wrong), but I think that would be a version 2 kind of thing.  We can leave that till later, or perhaps never.

I still think we can make a common query object that just handles the placeholders, and otherwise just passed the SQL statements thru.

.hc



Mike
 

.hc


 
----------------------------------------------------------------------------
 

 
News is what people want to keep hidden and everything else is publicity.          - Bill Moyers





--
Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician



----------------------------------------------------------------------------

I spent 33 years and four months in active military service and during that period I spent most of my time as a high class muscle man for Big Business, for Wall Street and the bankers.      - General Smedley Butler