---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Dec 10, 2007 1:07 PM Subject: Re: [PD] [psql] object hand-holding To: jamie@postlude.co.uk
On 12/10/07, Jamie Bullock jamie@postlude.co.uk wrote:
Good point! I think Hans' recent suggestion addresses the problem. It also occurs to me that for Postgres at least, we have the PREPARE statement, which addresses the optimisation and injection issues you have raised. Technically [psql] already supports PREPARE except that PREPARE uses the '$' character as its placeholder identifier, and '$1' can't be passed around as a symbol in Pd. I think it might be interesting to use the '?' notation currently under discussion as an interface to PREPARE though.
One of the things that I have been reading on this is that when you use these 'pre-complied' SQL statements, you need to 'bind' each of the variables with their types to a specific function. (Also, I kind of like the ':' syntax, as it puts a name to the data within the statement.) I also think that we would need to do something like
[insert into table (id, name) values (:id, :name) ( <-- sent to the cold inlet [bind :id float( <-- sent to hold inlet [bind :name symbol( <-- sent to hold inlet
[:id 1 ( <-- sent to hold inlet [:name john ( <-- sent to hold inlet [bang ( <-- sent to hold inlet
and then we would be able to put all the data into the database with the expected types. At least SQLite and libdbi use these 'bind' functions to associate the expected datatype with the input data.
Mike
Jamie
-- www.postlude.co.uk
PD-list@iem.at mailing list UNSUBSCRIBE and account-management -> http://lists.puredata.info/listinfo/pd-list
On Dec 10, 2007, at 2:07 PM, Mike McGonagle wrote:
---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Dec 10, 2007 1:07 PM Subject: Re: [PD] [psql] object hand-holding To: jamie@postlude.co.uk
On 12/10/07, Jamie Bullock jamie@postlude.co.uk wrote: Good point! I think Hans' recent suggestion addresses the problem. It also occurs to me that for Postgres at least, we have the PREPARE statement, which addresses the optimisation and injection issues you have raised. Technically [psql] already supports PREPARE except that PREPARE uses the '$' character as its placeholder identifier, and '$1' can't be passed around as a symbol in Pd. I think it might be interesting to use the '?' notation currently under discussion as an interface to PREPARE though.
One of the things that I have been reading on this is that when you
use these 'pre-complied' SQL statements, you need to 'bind' each of
the variables with their types to a specific function. (Also, I
kind of like the ':' syntax, as it puts a name to the data within
the statement.) I also think that we would need to do something like[insert into table (id, name) values (:id, :name) ( <-- sent to the
cold inlet [bind :id float( <-- sent to hold inlet [bind :name symbol( <-- sent to hold inlet[:id 1 ( <-- sent to hold inlet [:name john ( <-- sent to hold inlet [bang ( <-- sent to hold inlet
and then we would be able to put all the data into the database
with the expected types. At least SQLite and libdbi use these
'bind' functions to associate the expected datatype with the input
data.
(What is the "hold" inlet, by the way?)
It would be really nice to be able to have inlets for the sql
placeholders, but I couldn't think of a clean way to do it. We could
try the dynamic allocation like you say here, I don't know if I have
a specific objection to it, but it could get weird. Since the SQL
inlet has to be cold to support the comma hack, then these dynamic
inlets shouldn't be hot since they are the right of a cold inlet.
But that's not a big deal.
Also, I don't know if any objects do this kind of dynamic inlet
creation after the object itself has been instantiated. It might not
even be possible. I figure that you can easily make a subpatch or
abstraction around the SQL message generation, then manage the inlets
there. Perhaps not ideal, but it's not difficult to do and follows
existing practice.
As for the SQL binding, I was thinking it would happen inside of the
object, binding the placeholder names to the selectors. As long as
there wasn't any new input on the SQL inlet, then the SQL statement
wouldn't need to be recompiled.
Another question I just thought of it how to handle returning
multiple results from s single query. If it followed the [textfile]
style, then you would have to bang to get each individual result,
then keep banging until you get a bang on the status/second outlet,
meaning the query was done. I think this could work.
.hc
There is no way to peace, peace is the way. -A.J. Muste
On 12/10/07, Hans-Christoph Steiner hans@eds.org wrote:
(What is the "hold" inlet, by the way?)
It's a hot inlet that has been misspelled...
It would be really nice to be able to have inlets for the sql placeholders,
but I couldn't think of a clean way to do it. We could try the dynamic allocation like you say here, I don't know if I have a specific objection to it, but it could get weird. Since the SQL inlet has to be cold to support the comma hack, then these dynamic inlets shouldn't be hot since they are the right of a cold inlet. But that's not a big deal.
And if you want placeholders to have inlets, then we are back to putting the SQL into the object creation arguments, like:
[sqlite insert into table (id, name) values (:id, :name)]
While I don't think it is a bad idea, it does restrict a particular instance of a database object to only a single type of message, and it would have to be done at creation time of the object.
Should we allow both methods of entering SQL? One done at creation time, the other one done on the fly?
Also, I don't know if any objects do this kind of dynamic inlet creation
after the object itself has been instantiated. It might not even be possible. I figure that you can easily make a subpatch or abstraction around the SQL message generation, then manage the inlets there. Perhaps not ideal, but it's not difficult to do and follows existing practice.
I would think that an object having the ability to change its inlets on the fly would NOT be possible, as the inlets may change so much, as to lose a connection when it is recreated.
OR, could we possibly do something like this
[sqlite :id(f) :name(s)]
Which would create an instance with 4 inlets, the first 'hot' inlet would accept all the control messages, the next (x number) would represent the bound inlets for the placeholders, and the last would be the inlet for the incoming SQL statement. Would it be an error if the incoming SQL statement didn't have the same placeholders defined in it? Or should it be allowed to operate as any other?
As for the SQL binding, I was thinking it would happen inside of the object,
binding the placeholder names to the selectors. As long as there wasn't any new input on the SQL inlet, then the SQL statement wouldn't need to be recompiled.
My thought too...
Another question I just thought of it how to handle returning multiple
results from s single query. If it followed the [textfile] style, then you would have to bang to get each individual result, then keep banging until you get a bang on the status/second outlet, meaning the query was done. I think this could work.
This is already what I have been doing. Basically, as we are working now, the SQL would get input on the right inlet, then if any binding were needed, that would be done, and when the next bang occurs, the query is submitted, and the first result set is returned. Any subsequent bang would return the next result set. And when the result sets have been exhausted, it would send a bang out the "status" outlet to indicate the sets are done.
Mike
.hc
There is no way to peace, peace is the way. -A.J. Muste
On Dec 10, 2007, at 3:57 PM, Mike McGonagle wrote:
On 12/10/07, Hans-Christoph Steiner hans@eds.org wrote: (What is the "hold" inlet, by the way?)
It's a hot inlet that has been misspelled...
It would be really nice to be able to have inlets for the sql
placeholders, but I couldn't think of a clean way to do it. We
could try the dynamic allocation like you say here, I don't know if
I have a specific objection to it, but it could get weird. Since
the SQL inlet has to be cold to support the comma hack, then these
dynamic inlets shouldn't be hot since they are the right of a cold
inlet. But that's not a big deal.And if you want placeholders to have inlets, then we are back to
putting the SQL into the object creation arguments, like:[sqlite insert into table (id, name) values (:id, :name)]
While I don't think it is a bad idea, it does restrict a particular
instance of a database object to only a single type of message, and
it would have to be done at creation time of the object.Should we allow both methods of entering SQL? One done at creation
time, the other one done on the fly?
Also, I don't know if any objects do this kind of dynamic inlet
creation after the object itself has been instantiated. It might
not even be possible. I figure that you can easily make a subpatch
or abstraction around the SQL message generation, then manage the
inlets there. Perhaps not ideal, but it's not difficult to do and
follows existing practice.I would think that an object having the ability to change its
inlets on the fly would NOT be possible, as the inlets may change
so much, as to lose a connection when it is recreated.OR, could we possibly do something like this
[sqlite :id(f) :name(s)]
Which would create an instance with 4 inlets, the first 'hot' inlet
would accept all the control messages, the next (x number) would
represent the bound inlets for the placeholders, and the last would
be the inlet for the incoming SQL statement. Would it be an error
if the incoming SQL statement didn't have the same placeholders
defined in it? Or should it be allowed to operate as any other?
This kind of thing could be done in a separate "query" object, like
what's included in the "sqlwrappers", and [sqlite]/[psql] would still
represent the database itself. Having the objects structured this
way means that Matju could write his [expr] SQL query too, and they
would work with the different SQL database objects (sqlite, psql,
mysql).
As for the SQL binding, I was thinking it would happen inside of
the object, binding the placeholder names to the selectors. As
long as there wasn't any new input on the SQL inlet, then the SQL
statement wouldn't need to be recompiled.My thought too...
Another question I just thought of it how to handle returning
multiple results from s single query. If it followed the
[textfile] style, then you would have to bang to get each
individual result, then keep banging until you get a bang on the
status/second outlet, meaning the query was done. I think this
could work.This is already what I have been doing. Basically, as we are
working now, the SQL would get input on the right inlet, then if
any binding were needed, that would be done, and when the next bang
occurs, the query is submitted, and the first result set is
returned. Any subsequent bang would return the next result set. And
when the result sets have been exhausted, it would send a bang out
the "status" outlet to indicate the sets are done.
Cool, that sounds like a good idea. I probably got it from you. :)
Maybe you are already doing this, but a query could also send a
message out of the status outlet to say how many results were found
(i.e. [results 5(). This number could be routed to trigger an
[until] or other such behaviors. (using the completion bang to stop
[until] probably makes more sense though).
.hc
Mike
.hc
There is no way to peace, peace is the way. - A.J. Muste
-- 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
"[W]e have invented the technology to eliminate scarcity, but we are
deliberately throwing it away to benefit those who profit from
scarcity." -John Gilmore
On 12/10/07, Hans-Christoph Steiner hans@eds.org wrote:
Cool, that sounds like a good idea. I probably got it from you. :) Maybe you are already doing this, but a query could also send a message out of the status outlet to say how many results were found (i.e. [results 5(). This number could be routed to trigger an [until] or other such behaviors. (using the completion bang to stop [until] probably makes more sense though).
I could be wrong on other databases, but sqlite doesn't return the count of the number of result sets found. And as far as using an [until] object, you could still hook up the bang sent out the status outlet to the until so that it would shut it down when needed.
Mike
On 12/10/07, Hans-Christoph Steiner hans@eds.org wrote:
(What is the "hold" inlet, by the way?)
It's a hot inlet that has been misspelled...
It would be really nice to be able to have inlets for the sql placeholders,
but I couldn't think of a clean way to do it. We could try the dynamic allocation like you say here, I don't know if I have a specific objection to it, but it could get weird. Since the SQL inlet has to be cold to support the comma hack, then these dynamic inlets shouldn't be hot since they are the right of a cold inlet. But that's not a big deal.
And if you want placeholders to have inlets, then we are back to putting the SQL into the object creation arguments, like:
[sqlite insert into table (id, name) values (:id, :name)]
While I don't think it is a bad idea, it does restrict a particular instance of a database object to only a single type of message, and it would have to be done at creation time of the object.
Should we allow both methods of entering SQL? One done at creation time, the other one done on the fly?
Also, I don't know if any objects do this kind of dynamic inlet creation
after the object itself has been instantiated. It might not even be possible. I figure that you can easily make a subpatch or abstraction around the SQL message generation, then manage the inlets there. Perhaps not ideal, but it's not difficult to do and follows existing practice.
I would think that an object having the ability to change its inlets on the fly would NOT be possible, as the inlets may change so much, as to lose a connection when it is recreated.
OR, could we possibly do something like this
[sqlite :id(f) :name(s)]
Which would create an instance with 4 inlets, the first 'hot' inlet would accept all the control messages, the next (x number) would represent the bound inlets for the placeholders, and the last would be the inlet for the incoming SQL statement. Would it be an error if the incoming SQL statement didn't have the same placeholders defined in it? Or should it be allowed to operate as any other?
As for the SQL binding, I was thinking it would happen inside of the object,
binding the placeholder names to the selectors. As long as there wasn't any new input on the SQL inlet, then the SQL statement wouldn't need to be recompiled.
My thought too...
Another question I just thought of it how to handle returning multiple
results from s single query. If it followed the [textfile] style, then you would have to bang to get each individual result, then keep banging until you get a bang on the status/second outlet, meaning the query was done. I think this could work.
This is already what I have been doing. Basically, as we are working now, the SQL would get input on the right inlet, then if any binding were needed, that would be done, and when the next bang occurs, the query is submitted, and the first result set is returned. Any subsequent bang would return the next result set. And when the result sets have been exhausted, it would send a bang out the "status" outlet to indicate the sets are done.
Mike
.hc
There is no way to peace, peace is the way. -A.J. Muste
On Mon, 10 Dec 2007, Hans-Christoph Steiner wrote:
Also, I don't know if any objects do this kind of dynamic inlet creation after the object itself has been instantiated. It might not even be possible.
It's possible, and I've done it, except for the bit of disconnecting every wire connecting to deleted inlets. I think that it's just 2 more lines of code, but I haven't tried, especially as we don't have any use for it anymore, as we are replacing LtiLib support by OpenCV support, which doesn't need that trick.
_ _ __ ___ _____ ________ _____________ _____________________ ... | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
-
Hans-Christoph Steiner -
Mathieu Bouchard -
Mike McGonagle