Hello all, To give you an update on what is happening, I think I will have something to post tomorrow or the next day. I did diverge from the Object Model discussed, but that was because I couldn't quite figure out where Hans was outputting the result sets. It also may turn out to be a good thing, as it makes it possible to create the Server Object in any window, and the query objects can be in any other window, and they will still connect up (without patching). I like this as each query object can be isolated within its own subpatch, and you don't have to build additional objects just to connect to the server.
So, I have not quite implemented the Placeholder version of inputting the SQL, and from reading some more about SQL injection, it sounds like it would be a good idea if we were to have these inlets created using the specific type of data to be inserted. This is also probably going to be required, as the database packages require the binding of a particular data type into these placeholders. So, we are going to need a means of defining these types at creation time.
[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). I am not quite sure if I remember correctly, but it sounded like Hans wants to set these inlets to be list inlets, but considering that the databases need to know the specific data types for each placeholder, I don't think this would be practical.
Also, as the servers I have looked at so far, would expect this string in the form of
"insert into mytable (id, name, token) values (?, '?', '?')"
The external would be required to strip out the data type character.
Also, from reading these things ( http://en.wikipedia.org/wiki/SQL_injection), it was stated that most database engines only allow the sending of a single SQL statement. This is done so that SQL injection can't be used to create additional statements that are embedded within a Placeholder replacement. So, this means that I won't get my multiple statement buffer. (Also, from some tests that I have done, there doesn't have to be a ';' terminating the statement, as the server appears to assume these things.
Ok, so I am a little all over the board on this, but if you could focus on the stuff on the Placeholder things, as that is pretty much still to be implemented, that would be great.
Thanks,
Mike
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.
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.
but considering that the databases need to know the specific data types for each placeholder,
heh? do they?
_ _ __ ___ _____ ________ _____________ _____________________ ... | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
On 12/21/07, Mathieu Bouchard matju@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.ht...
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
On Fri, 21 Dec 2007, Mike McGonagle wrote:
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.
Ok, cool then. :)
This is not an issue of how the user sees these things, it is how the C code interfaces with the database.
yes.
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.
I think that it's plenty good if you have separate calls for when an atom is a float and when an atom is a symbol. It's not any more extra code and I don't think that it's a good idea to use atom_string anyway.
_ _ __ ___ _____ ________ _____________ _____________________ ... | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
On Dec 21, 2007, at 1:36 PM, Mike McGonagle wrote:
On 12/21/07, Mathieu Bouchard matju@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.
I don't know whether Perl DBI is the same as libdbi, but Perl DBI
definitely uses placeholders:
http://www.stupidfool.org/perl/docs/perltut/dbi/dbiplace.html
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.
We just need a clearly defined standard, then each object will handle
the translation. We can look at code like Perl:DBI for an examples of
how thing could be done. That might even be in C, so we could use
that code.
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.
We can get the type info from the Pd type system if we need it. That
will give us symbols and floats.
.hc
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@iem.at mailing list UNSUBSCRIBE and account-management -> http://lists.puredata.info/ listinfo/pd-list
Using ReBirth is like trying to play an 808 with a long stick. - David Zicarelli
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@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@iem.at mailing list UNSUBSCRIBE and account-management -> http://lists.puredata.info/ listinfo/pd-list
kill your television
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@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.... 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@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.ht...
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@iem.at mailing list UNSUBSCRIBE and account-management -> http://lists.puredata.info/listinfo/pd-list
kill your television
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.
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.
I'll take a look at your design once its out there.
.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@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@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@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.
On Dec 26, 2007 3:24 PM, Hans-Christoph Steiner hans@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@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.... 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@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.ht...
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@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.
-
Hans-Christoph Steiner -
Mathieu Bouchard -
Mike McGonagle