[PD-dev] Connecting up an SQL Database to PD

Tue Nov 6 04:08:25 CET 2007

I think it would be really nice to have a Pd-ish interface to MySQL,  
I am glad you are taking this project on.  Here are some thoughts:

- As for the objects, I think one idea is to have one object that is  
the connection to the database, then everything gets sent thru that.   
Then you can have supporting objects that help build queries, etc.  
which are then fed to the connection object.  The results will come  
out of the outlet of the connection object, and there again can be  
objects to parse the results, if need be.

- I think in order to make it work well, you are going to need to use  
a thread so that Pd doesn't hang waiting for a response from the  
database.  That means the interface needs to represent that the  
object is not deterministic.  That could be a bit tricky.  For  
example, you send a command to [mysql], then send another request  
before the first one is finished, then you get a response.  How do  
you know which request that response belongs to?  Normally this isn't  
a problem in Pd since basically everything responds within its time  
slice, so it acts as if everything is instantaneous.

- If you need strings, you should look at the two different string  
methods that exist: moocow's pdstring and the mrpeach's string  
datatype that's included in the most recent pd-extended builds.  You  
might be able to get away with using lists and just insert spaces  
between the atoms before sending to the database.

- I think it would be more useful to make this object an interface to  
existing databases than just an embedded sqlite object.  There is  
already [pool] and PDContainer which basically do that.  Talking to  
external databases means that you could power a website with Pd :).

.hc

On Nov 5, 2007, at 5:33 PM, Tim Boykett wrote:

>
> Hi,
>
>    this is interesting because I am fiddling about with exactly these
> sorts of things at precisely this moment.
>
> There was the claim recently that an external was not necessary  
> because
> pyext exists and SQL stuff can be done in python. However my  
> experience
> in getting pyext to work has been negative. However I have happily  
> made
> an external python program talk to a SQL database as well as speaking
> OSC to talk with PD and Max and many other things.
>
> So I would claim that OSC makes this idea of an external less  
> pressing.
>
> However I would have preferred not to have had to jump to another
> program.
>
> So I would find a PD sql external great!
>
> I would also not like to have the database integrated into the PD
> external.
> One of the important things about a database is that it can be  
> accessed
> by several processes. One process can be dumping data into it from
> somewhere, another can retrieve that data and use it for something in
> real
> time or later or whatever. So I would prefer to have an interface
> to an external database. However having a PD external that is a
> database server
> might be relevant for those who do not wish to install mySQL or some
> other
> database server on their machine.
>
> I would think that functionality would include:
>
>   connecting to a database server
>   selecting which database to use
>   creating datasets in a table
>   retrieving data sets from a table
>
> This would probably be best done as two objects, one sending data to
> store in the DB,
> one receiving it in response to the queries, with both objects having
> commands to open a connection and select which database to use.
>
> I agree that creating tables is not necessary, at least not initially.
>
> Data types: everything that is not a number (which in PD is a float by
> definition) could be a string (strings, dates, times) which would be
> a symbol,
> with the probable exception of binary data. No idea what to do with  
> this
> is PD, perhaps just ignore it?
> Auxiliary functions to turn a sql date and time strings into lists
> and vice
> versa would be useful.
>
> Hmm. that is my 0.02 Euro for tonight.
>
> Cheers,
>
> tim
>
>
> On 05/11/2007, at 10:26 PM, Mike McGonagle wrote:
>
>> Hello all,
>>
>> It has been several months since I first inquired about this idea,  
>> and
>> it seems there are some out there who think this would be a good  
>> idea.
>> So, in the interest of figuring out just how this could work with PD,
>> I would like to start a discussion about what sort of interface
>> objects should be created.
>>
>> 1.0 What the objects would look like.
>> My first thought is to design them so they operate like other already
>> existing PD objects. While there may not be a perfect match with  
>> other
>> objects, I am thinking more along the lines of what commands they
>> recognize and their basic functionality.
>>
>> While I don't have a specific idea for how to implement these
>> connecting objects, I think that there would be three (or more)
>> different PD objects instantiated to work with a database. One object
>> to handle the connectivity, one object to handle the querying, and a
>> third to handle any results coming from a query.
>>
>> A. While there could be multiple connectivity objects, each one would
>> point to a different database. This could be used to construct  
>> several
>> data paths, and each connectivity object would be passed a symbol to
>> use as a name for its data path. This name would then also have to be
>> passed onto each query and results object to link them to a  
>> particular
>> data path. (Does this need more explaination?)
>>
>> B. There could be more than one query object, each one handling a
>> different query type (as PD doesn't really support strings, it might
>> be difficult to build a whole SQL statement using commands via an
>> inlet).
>>
>> C. There could also be multiple result object, each "listening" for a
>> different parameter set.
>>
>> 2.0 Data Types, SQL, and PD
>> So far, the biggest hurdle that I see with this is Data Types. As PD
>> doesn't have much support for strings, there would either have to be
>> special handling involved, or we just agree that a string in this
>> context would be a PD symbol. Also, all the other datatypes (with the
>> possible exception of a BLOB) would not really have a PD equivelent.
>> But then again, how might binary data be stored to a BLOB from within
>> PD?
>>
>> 3.0 Linking data into PD
>> Another idea for connecting the data from the database into a PD
>> patch, would be to map each of the fields in the database table to a
>> particular PD variable. While this might allow for increased
>> interaction, it would eliminate the use of any symbols returned from
>> the database.
>>
>> 4.0 Version 0.0
>> As this is still in this design stage, there are some things that I
>> would like to put off dealing with until there is a working set of PD
>> objects.
>>
>> A. Currently works with one database. As there are so many different
>> types of databases out there, and most of them require additional
>> components to setup and configure, I would like to keep this as  
>> simple
>> as possible. Currently my idea is to use ONLY SQLite, as a backend.
>> This would be compiled directly into the PD external, as the SQLite
>> code implements a full SQL database in a single source file, it is
>> pretty easy to link this up directly without any other programs to
>> install.
>>
>> B. All databases need to be designed externally. This means that no
>> CREATE TABLE functions or other commands that would manipulate the
>> database schema. This might be a problem is you need to create tables
>> on the fly, but I would think that we could get a good start on  
>> the PD
>> interface, and then take this idea up later.
>>
>> ***************
>>
>> So, if any of you have any ideas on this topic, please feel free to
>> jump in and add your $0.02. It would be very much appreciated.
>>
>> Thanks,
>>
>> Mike
>>
>> _______________________________________________
>> PD-dev mailing list
>> PD-dev at iem.at
>> http://lists.puredata.info/listinfo/pd-dev
>>
>
>
> _______________________________________________
> PD-dev mailing list
> PD-dev at iem.at
> http://lists.puredata.info/listinfo/pd-dev

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

Using ReBirth is like trying to play an 808 with a long stick.    - 
David Zicarelli