[PD] Question about SQL Placeholder Implementation

Mike McGonagle mjmogo at gmail.com
Wed Dec 26 23:11:03 CET 2007


On Dec 26, 2007 3:24 PM, Hans-Christoph Steiner <hans at eds.org> wrote:

>
> My idea for using this would be to pair the [sql_query] directly to the
> [sqlite] object, then the rest of the time, the data would be in the form of
> Pd messages.  I think for most people, they'll want to work in Pd messages
> as much as possible, then at the last possible moment, the Pd messages
> should be converted into SQL queries.
>

I guess I don't see the "pairing" as being necessary. There is usually only
one object that represents a database, and while there can be two or more
(each handling its own set of result sets, I think of this as being multiple
"data channels" into a single database), the result sets still I think
should be coming out of the query object, as that is the place that is
mostly associated with that particular query.


> Also, since Pd objects visually represent instances, I think that the
> [sqlite] server object should represent one database.  If there are numerous
> [sqlite] objects representing the same database, then I think things will
> get confusing to the Pd user and in the C code.  We tried this in hidio, and
> it turned out to be a pain.  I am not sure if this is what you are
> proposing, though, but that is my understanding of it.
>

Hum, well, I don't see this as being a problem, as a matter of fact, I think
this will be a plus, as it would allow the creation of multiple connections
to a single database, one that might handle only inserts, another than would
handle selects. As it currently stands, each database server instance can
only handle one set of results sets at a time. Each instance would free us
from having to deal with two different queries trying to make requests at
the same time.

As it stands right now, each of my server objects is given a PD symbol ID,
and that is passed to each of the query objects. The query object sends any
requests directly to the server object (yes, I know, that is behind the
scenes, but it is similar to a 'netsend' object.). The connections being
made are purely symbolic, and can even be changed on each query instance.


> I'll take a look at your design once its out there.
>

You might be surprised at the differences between our implementations. I
will say, though, that I see how you are handling char buffers, and I will
have to look into just how PD actually works with 'binbuf' objects.

Mike



>
> .hc
>
> On Dec 26, 2007, at 10:28 AM, Mike McGonagle wrote:
>
> Hans,
> From looking at this (I didn't compile the external yet), but my first
> thought about your patch is that it would be very cumbersome to have the
> result sets coming out of the "server" object. They should be coming out of
> the "query" object, as that is the object that is handling that particular
> result set. I kind of envision that once someone has finished with creating
> the SQL queries, that they would then create the processing code to handle
> each query. That is something that is specific to the query, and not the
> "server" object. The way that you have this, we would still need to create a
> separate "query" and "server" object for each query. That would make
> swapping the "server" object in and out very difficult, as it would require
> replacing ALL of the "server" objects for each query.
>
> Just to let you know, I need to clean up some more stuff, but I will post
> my latest version of the "server" and "query" objects tomorrow. I have not
> done a whole lot of testing, but I am able to insert SQL and make the
> queries, and get results out. It also handles errors and most recent insert
> row id stuff...
>
> Mike
>
>
> On Dec 22, 2007 1:27 AM, Hans-Christoph Steiner <hans at eds.org> wrote:
>
> >
> > I just committed my first stabs at a sketch for the [sql_query] object.
> >  It doesn't do anything yet, but it does recognize ? placeholders as object
> > arguments and creates inlets.  Other than that, the only thing it currently
> > does is crash :).
> >
> >
> > http://pure-data.cvs.sourceforge.net/pure-data/externals/hcs/sql_query-help.pd
> > http://pure-data.cvs.sourceforge.net/pure-data/externals/hcs/sql_query.c
> >
> > .hc
> >
> > On Dec 21, 2007, at 1:36 PM, Mike McGonagle wrote:
> >
> >
> >
> > On 12/21/07, Mathieu Bouchard <matju at artengine.ca> wrote:
> > >
> > > On Tue, 18 Dec 2007, Mike McGonagle wrote:
> > >
> > > > [query <srv_id> insert into mytable (id, name, token) values
> > > (?f,'?s','?s')]
> > > > In this example, the first placeholder would expect a Float, with
> > > the next
> > > > two expecting a Symbol (just think of this as a variation on the
> > > printf
> > > > string substitutions).
> > >
> > > It looks more like the types of [t] [pack] [unpack] [expr] etc.
> >
> >
> >
> > Yes, they are PD types.
> >
> >
> > You don't really need to type your data. SQL is largely typeless: at
> > > least
> > > at the level of what you can put in one table cell, anything is a
> > > string.
> > > It becomes typed (but auto-cast) when put in the cell, but rebecomes
> > > untyped when taken out of the cell.
> >
> >
> >
> > Yes, I know that these things can be stored in a typeless manner, but
> > the functions that bind the SQL statement with their placeholders can be of
> > any type, as there are functions for the various datatypes.
> >
> > http://www.sqlite.org/c3ref/bind_blob.html
> >
> > This link shows all the different functions used to bind the data to the
> > statement placeholders.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html
> >
> >
> > And if you look at how MySQL does this, while they only provide a single
> > bind function, it does have the datatypes for the data in a structure that
> > gets passed into the bind function.
> >
> > Also, from looking at libdbi, it appears that they don't even bother
> > with placeholders at all.
> >
> > This is not an issue of how the user sees these things, it is how the C
> > code interfaces with the database.
> >
> > So, just how do we do this in a consistent manner when all the different
> > interfaces don't support these things, AND if they do, they do them
> > differently.
> >
> > > but considering that the databases need to know the specific data
> > > types
> > > > for each placeholder,
> > >
> > > heh? do they?
> >
> >
> >
> > SQLite does. MySQL appears to as well. And yes, it is possible to just
> > force everything to be a string/c-string and be done with it. SQLite
> > actually stores everything as a string anyway, despite the defined types in
> > the create statement.
> >
> > Mike
> >
> >
> >   _ _ __ ___ _____ ________ _____________ _____________________ ...
> > > | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
> >
> >
> >
> >
> > --
> > 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
> > _______________________________________________
> > PD-list at iem.at mailing list
> > UNSUBSCRIBE and account-management ->
> > http://lists.puredata.info/listinfo/pd-list
> >
> >
> >
> >
> >
> >
> > ----------------------------------------------------------------------------
> >
> >                             kill your television
> >
> >
> >
>
>
> --
> 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 have the audacity to believe that peoples everywhere can have three
> meals a day for their bodies, education and culture for their minds, and
> dignity, equality and freedom for their spirits.      - Martin Luther
> King, Jr.
>
>
>


-- 
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071226/766edadf/attachment.htm>


More information about the Pd-list mailing list