---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Nov 8, 2007 12:35 PM Subject: Re: [PD-dev] Connecting up an SQL Database to PD To: Hans-Christoph Steiner hans@eds.org
On 11/7/07, Hans-Christoph Steiner hans@eds.org wrote:
On Nov 7, 2007, at 4:37 AM, Frank Barknecht wrote:
Hallo, Hans-Christoph Steiner hat gesagt: // Hans-Christoph Steiner wrote:
AFAIK, MySQL and the like do not guarantee realtime responses, and network connections certainly do not. So if want something that is designed to do that, then use [pool], [textfile], or perhaps an embedded SQLlite, if it is quick enough.
I have been gone for a couple of days (under a germ...) and during that time, A LOT has been posted here, and I would like to spend some time reading the responses...
But, in that down time, I was playing with SQLite, and actually have an object that can send arbitrary queries, taking input from PD inlets (both float and symbol). After thinking over how to do this, I realized that VERY SIMPLE SQL statments can be entered into a PD objects creation arguments. While not the most elegant, it is very functional for reading. I know the writing works, but just how fast it is will have to be determined. (SQLite writes a lot faster when using transactions, one test I did without transactions, it took almost 2 hours to load 36000 records (each with 10 data elements), with transactions turned on it was a matter of minutes. (These tests were not done using the PD interface, but using the commandline version of SQLite.)
Anyway, on skimming the thread, Ben's idea of working with PD struct definitions is a really good one. I want to be able to take a definition for a PD struct, and generate an SQL table based on that. I was playing with this doing some 'SELECT' statements from my object, and with a metronome triggering it once every 20ms, it was reading fairly consistently.
So, that being said, I don't have anyway of sending the data to an outlet, it just currently dumps all the result sets to the console. As the amount of data that can be retrieved can be STAGGERING, there needs to be some reasonable thought given to how to handle the result sets. I would think that the access is pretty fast that smaller chunks of data can be read, processed, and then re-read...
I don't know if anyone would be interested in seeing the external that I have so far (while there are only a couple of files, it is kind of messy), but the file for SQLite is almost 2.5 megs, just by itself. Compressed, this whole thing is about 1.2 megs. The compiled external comes out to be able .5 megs. I am working on PPC 10.4.10 Mac, so anyone using that could probably use my compiled external. Anyone on other platforms will have to fend with my simple makefile.
You can get the files from ftp.wpco.com, with the user 'fsr', and the password of 'puredata'. There is a zip file with the external source, a makefile, a couple of preliminary examples. Try the 'test2' one, as it may be the most understandable. Also, as I said, this doesn't really do much more than spew to the console, but it is a start.
Let me have it!!!
Mike
PS. I am still going to read this whole thread, but that will be a little later. Thanks again.
Unless there are ways to guarantee the database will reply within one clock tick (usually ~1.5ms), then the interface should represent the reality of the situation. Otherwise the database object will cause lots of clicks and interruptions when used.
.hc
Ciao
Frank Barknecht _ ______footils.org__
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
Mistrust authority - promote decentralization. - the hacker ethic
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
Hi Mike,
On Thu, 2007-11-08 at 12:52 -0600, Mike McGonagle wrote:
I don't know if anyone would be interested in seeing the external that I have so far (while there are only a couple of files, it is kind of messy), but the file for SQLite is almost 2.5 megs, just by itself. Compressed, this whole thing is about 1.2 megs. The compiled external comes out to be able .5 megs. I am working on PPC 10.4.10 Mac, so anyone using that could probably use my compiled external. Anyone on other platforms will have to fend with my simple makefile.
You can get the files from ftp.wpco.com, with the user 'fsr', and the password of 'puredata'. There is a zip file with the external source, a makefile, a couple of preliminary examples. Try the 'test2' one, as it may be the most understandable. Also, as I said, this doesn't really do much more than spew to the console, but it is a start.
I had a look at your external, and I have a few comments. Firstly I think having a sqlite external for Pd is a nice idea because it is very light weight. If you statically link to the sqlite3 library then nothing is required but the external itself -- no database server to configure, which is a big advantage. On this basis, I think an embedded sqlite will always be welcome, and can happily coexist with other solutions e.g. the sqlsingle/psql externals, and the lua and Python scripting solutions.
A few suggestions about the code/design:
1. I would strongly suggest not including the sqlite sources with the sqlite4pd sources. I had a raft of build errors trying to work this way, whereas downloading and building sqlite from the sources on sqlite.org was a breeze.
2. 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 (see the psql sources in CVS for an example of how to do this). You are also reinventing Pd's existing variable substitution with your @ substitution.
3. Your test.pd file seemed to work, but when running example.pd, I get '...got something very wrong...' posted to the Pd console, and Pd crashes when I click the subpatches. Not sure why this is, but I definitely suggest a more informative error message than 'got something very wrong' ;-)
4. It looks like your 'next' and 'dispose' methods aren't implemented yet. Do you plan to cache the query results in the object's data struct and then use 'next' to iterate over them? Why not just dump the results to an outlet(s) (again see sqlsingle/psql code)? If you want to store the results and then iterate over them, they can be dumped to a [textfile] object.
Just a few thoughts.
Jamie
-
Jamie Bullock -
Mike McGonagle