One more time... please.
---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Nov 12, 2007 1:38 PM Subject: Re: [PD-dev] Fwd: Fwd: Connecting up an SQL Database to PD To: Andy Farnell padawan12@obiwannabe.co.uk
I was just thinking, should the result sets be returned as TAGGED data? Meaning, should the name of the column precede the data in the returned PD list? While I can see that this would be useful, it would also require more processing to merge the keys with the data. I have so far been assuming that it is only the data that gets returned.
Mike
On 11/12/07, Mike McGonagle mjmogo@gmail.com wrote:
On 11/13/07, Andy Farnell padawan12@obiwannabe.co.uk wrote:
On Mon, 12 Nov 2007 12:05:47 -0600 "Mike McGonagle" mjmogo@gmail.com wrote:
Apologies for the shallow input, just scanning and speaking my thoughts...
- I would strongly suggest not including the sqlite sources with the
sqlite4pd sources.
Yes, modularity, maintainability.
This really isn't a big issue. I will look at using a library for sqlite.
- I think having the SQL query provided as an object creation argument
is a really bad idea. Wouldn't it be better to just pass the query in as a list
It seems mean to leave all formatting to the user, but that's most flexible in practice.
At the same time, the process of building the message to create the SQL that then gets submitted could be cumbersome. If it is just a simple thing like loading up a textfile, and then forwarding this on that is one thing (assuming that each line in the textfile is a complete SQL statement), but to construct dynamic SQL on the fly, that is a whole different thing. And this doesn't even address the different types of SQL statements, each returning a different result set, or error code.
And then there is still the issue of having to parse through the result sets as they comeback...
list would basically be UNFORMATTED data, and it would require the PD code to interpret the result sets.
KISS. Object should know as little about databases as it can, it's all down to the object user and the service behind the object, object == gateway, nothing much more.
Ok, can anyone create a PD patch that might illustrate some of the ways you envision sending SQL to the database? Nothing "workable", just a prototype of what you see...
While I would tend to agree in keeping it simple. There are a couple of hurdles that must be crossed. On the one hand, I get the impression that some are thinking that it is as simple as saying "select * from my-table;" and then you are done, each SQL statement is potentially VASTLY different from the next, and to expect to create a single INSTANCE of a connection, and pipe all your requests through that one object, I think is a little naive. If you are familiar with other Database programming enviornments, they assume that you are working with a single SQL statement (or a simple variable statement) that serves a single purpose. They don't (AFAIK) try to pipe all their requests through the same object. Working with SQL not only requires knowing the programming, but also the format of the data expected in the database.
On the other hand, I would think that expecting a single object to handle this would result in a 'route'ing nightmare in the PD code in interpreting the result sets. While dealing with a single instance of an SQL statement that has known results, would be very manageable and not require a lot of routing.
Mike
-- Use the source
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
-- Help the Environment, Plant a Bush back in Texas!
"I place economy among the first and most important republican virtues, and public debt as the greatest of the dangers to be feared. To preserve our independence, we must not let our rulers load us with perpetual debt." -- Thomas Jefferson, third US president, architect and author (1743-1826)
"Give Peace a Chance" -- John Lennon (9 October 1940 – 8 December 1980)
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
If you think you can, or you think you can't, you are probably right. —Mark Twain
"Art may imitate life, but life imitates TV." Ani DiFranco
-- Help the Environment, Plant a Bush back in Texas!
"I place economy among the first and most important republican virtues, and public debt as the greatest of the dangers to be feared. To preserve our independence, we must not let our rulers load us with perpetual debt." -- Thomas Jefferson, third US president, architect and author (1743-1826)
"Give Peace a Chance" -- John Lennon (9 October 1940 – 8 December 1980)
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
If you think you can, or you think you can't, you are probably right. —Mark Twain
"Art may imitate life, but life imitates TV." Ani DiFranco
Hallo, Mike McGonagle hat gesagt: // Mike McGonagle wrote:
I was just thinking, should the result sets be returned as TAGGED data? Meaning, should the name of the column precede the data in the returned PD list? While I can see that this would be useful, it would also require more processing to merge the keys with the data. I have so far been assuming that it is only the data that gets returned.
If it's a determinisic object, I'd say you can omit the tags. If a user wants tags, she can use "select id from table" or so to get her own id-tags in the results. it would be useful to have a second outlet [textfile]-style to signal the end of results.
Generally I prefer working with databases using ORMs like sqlobject.org instead of with SQL queries directly, but I don't know how a good and simple way to design something like an SQL-ORM for Pd could look like.
Another thing that may be important in the long run would be a common interface for all DB adapters. So the Pd object for sqlite shouldn't behave too differenty from the objects for postgresql, mysql, etc. Again that's something I like about Pythons SQLObject (or other ORMs like SQLAlchemy ...): They provide an abstract interface to lots of backends. Probably something like this exists for C/C++ as well, you may want to g**gle for it. (Oh, and maybe prototyping and testing such a general purpose SQL class for Pd is easier in Lua or Python, but I think I already mentioned that. ;)
Ciao
On 11/12/07, Frank Barknecht fbar@footils.org wrote:
Hallo, Mike McGonagle hat gesagt: // Mike McGonagle wrote:
I was just thinking, should the result sets be returned as TAGGED data? Meaning, should the name of the column precede the data in the returned PD list? While I can see that this would be useful, it would also require more processing to merge the keys with the data. I have so far been assuming that it is only the data that gets returned.
If it's a determinisic object, I'd say you can omit the tags. If a user wants tags, she can use "select id from table" or so to get her own id-tags in the results. it would be useful to have a second outlet [textfile]-style to signal the end of results.
Maybe there could be an option to have the keys returned, or not. I would figure that with most databases used with PD would already know the format of the database, and therefore would not really require the handling of the keys.
As far as the second outlet, yes, I was going in that direction. It would serve two purposes, one to indicate the end of the result set has been reached (outputting a 'bang'), and the second would be a list containing an error message from the database for any queries that error out.
Generally I prefer working with databases using ORMs like sqlobject.org instead of with SQL queries directly, but I don't know how a good and simple way to design something like an SQL-ORM for Pd could look like.
Another thing that may be important in the long run would be a common interface for all DB adapters. So the Pd object for sqlite shouldn't behave too differenty from the objects for postgresql, mysql, etc. Again that's something I like about Pythons SQLObject (or other ORMs like SQLAlchemy ...): They provide an abstract interface to lots of backends. Probably something like this exists for C/C++ as well, you may want to g**gle for it. (Oh, and maybe prototyping and testing such a general purpose SQL class for Pd is easier in Lua or Python, but I think I already mentioned that. ;)
Well, this is one of the goals. Provide for UNIVERSAL access. At the same time, would a trade off be in performance? One of the things that I would think using something like sqlite as a simple file database, is that there is no intervening layers to go through. You are "closer" to the data, as it is on your machine, stored in a file. No drivers or networking involved.
But, first things first. I do think that it is important to have a single interface for these things, and one way to use them. Then once you get those things down, you can tweak things by replacing the classes with those of your choice.
Mike
Ciao
Frank Barknecht _ ______footils.org__
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
-
Frank Barknecht -
Mike McGonagle