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

[Mike McGonagle]

forgot to send to the list.

On 11/12/07, Mike McGonagle <mjmogo at gmail.com> wrote:
> On 11/12/07, Jamie Bullock <jamie at postlude.co.uk> wrote:
> >
> > Hi Mike,
>
> Hola, Jamie.
>
> > 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.
>
> Thanks for the time you put in. I appreciate it very much. I agree
> that this would coexist with others very well. That being said, I am
> trying to come up with a way of representing SQL within PD. As in most
> other SQL/DB programming, the statements are pretty much encapsulated
> within a program, and a basic form of a statement is used. I just want
> to know how useful and workable it is to consider each of these
> objects to be an instance of an SQL statement, allowing the entry of
> the SQL in the creation args for the PD object.
>
> >
> > 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.
>
> This is kind of odd, as they claimed all you would need do is to just
> include this with your project. Could you elaborate a little on what
> problems you had?
>
> Oh, is it possible that the "raft" of errors you had were actually
> warnings? and not real errors? I got many warning, but everything
> works fine when run. Guess we should take out the '-Wall' option from
> the makefile.
>
> > 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.
>
> Well, passing the stuff in as a list is doable, the output from such
> an object would be very difficult to work with in PD, IMHO. The output
> list would basically be UNFORMATTED data, and it would require the PD
> code to interpret the result sets. Also, just how do you handle both a
> result set from a SELECT or an INSERT? They are two very different
> result sets, and forcing either the external to have KNOWLEDGE about
> the statement or the PD handling objects would produce a lot of
> spaghetti. Or would I be wrong?
>
> > 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' ;-)
>
> Yes, I kind of figure there would be troubles. This is just a very rough draft.
>
> > 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.
>
> No, just the communications with the Database. I am still working on
> getting the result sets to convert over to PD objects. But the basic
> idea is to return a list for each result set.
>
> > Just a few thoughts.
>
> Thanks, I will have an update when I get the result sets connected up
> and sent out as a list. I would also like to be able to have it work
> where there would be a single outlet for each expected piece of data.
> At the same time, these limitations would have to be known, so that
> nothing unexpected would happen.
>
> Thanks again, Jamie.
>
> Mike
>
>
> --
> 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