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

[Mike McGonagle]

God, why do I do this?

---------- Forwarded message ----------
From: Mike McGonagle <mjmogo at gmail.com>
Date: Nov 12, 2007 1:35 PM
Subject: Re: [PD-dev] Fwd: Fwd: Connecting up an SQL Database to PD
To: Andy Farnell <padawan12 at obiwannabe.co.uk>


On 11/13/07, Andy Farnell <padawan12 at obiwannabe.co.uk> wrote:
> On Mon, 12 Nov 2007 12:05:47 -0600
> "Mike McGonagle" <mjmogo at gmail.com> wrote:
>
>
> Apologies for the shallow input, just scanning and speaking my thoughts...
>
> > > > 1. 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.

> > > > 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
>
> 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 at 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