Daniel,
Am 14.01.14 08:37, schrieb Daniel Lyons:
Joachim,
On Jan 14, 2014, at 12:24 AM, jtuc...@objektfabrik.de wrote:
I cannot help with opendbx, but I can comment on the way Glorp generates SQL.
You typically write blocks of code that look like a normal Smalltalk block, but
they have limitations to what you can write in there. These blocks are
evaluated and get converted to SQL by some kind of Visitor. The SQL string is
then concatenated out of fragments.
BUT: Glorp does not have an API to provide SQL fragments to include in a query.
So there is not an easy way to inject SQL into a Glorp genaretd query. What you
are asking for is not related to Glorp, but to the underlying DB interface,
DBXTalk in your case.
Interesting. So, I guess I'm wondering, how does Glorp escape the strings?
I don't feel like looking this up for you right now, but my guess would
be that it is done in Smalltalk code.
If you want to provide the user with the ability to provide SQL clauses and
construct queries out of that, I guess you are on your own.
So the question is: do your users write SQL fragments or are you thinking of a
tool to construct queries by example?
Oh, I'm afraid I haven't been very clear. The "user" supplying the query is me,
the programmer. You see, I love SQL and I love relational databases. I probably lost most
of the list with that sentence. :)
Don't we all preach everybody should use the right tool for the job (as
long as it's Smalltalk, of course ;-) )?
I'm porting this program from PHP. It's a US Census browser, if you're curious:
http://census.7gf.org . Anyway, it's basically one table and a huge pile of indexes and
some set queries. Each page there is just a different query or two. I got through the
front page and the about page with Seaside and PostgresV3 and it works great, but now I
need to handle a query with the state as a parameter. In PHP (and JDBC, and Python's
DBAPI), I can say "SELECT * FROM places WHERE state = ? ORDER BY population
DESC" and then supply an array with the state in it, which fills in the '?' in the
query.
Since I guess you are not talking about stored procedures (which I think
are supported in Glorp), I now get what you are asking about. You need
something to make sure a parameter to
'SELECT * FROM places WHERE state = %1 ORDER BY population DESC' bindWith: self
stateName
is correct SQL. This is not so much a problem for Strings, but types like Date
and Timestamp...
So this is where somebody else would have to jump in for DBXtalk. VA
Smalltalk's DB interface Layer has helper methods for this. Glorp uses
these.
On a project with a true object model and real behavior on the objects, this
approach wouldn't scale up. But in this case, the program is just a nice
frontend on a simple one-table database. Glorp is bound to be more effort than
it's worth. I only brought it up thinking Glorp must be doing this kind of
escaping. It sounds like you're saying it has some other mechanism though, in
which there is some kind of visitor that does the escaping, and I won't be able
to leverage that.
Well, Glorp might help for some more complex structures. I guess the
table maps to much more than one class. But if you want to keep things
simple, I agree: Glorp might not be the best tool for the job.
Do you provide objects to fill in some example data or do your users know the
tables? The right solution for you depends on what exactly you have in mind.
For our project we use Glorp and provide forms to enter string fragments or
number ranges or dates etc. Then we use these in Glorp's blocks. This way the
structure of the query is provided as Smalltalk-code and only user-provided
data for the right-side operand of comparisons is provided by the user. It is
extremely hard to misuse this because the query String will always be fixed and
so fragments that include SQL will always lead to SQL errors.
I agree :)
I am still learning about all the powerful things Glorp can do to cunstruct
subqueries, outer joins, having clauses and stuff, but every time I find
something I am excited ;-).
I have another project I am looking forward to learning Glorp for. Don't worry,
I'm sure I'll need a lot of help to get that going!
Just ask.
Did you know there are specialized mailing lists for glorp and dbxtalk?
You can find them on forum.world.st.
Joachim
--
-----------------------------------------------------------------------
Objektfabrik Joachim Tuchel mailto:jtuc...@objektfabrik.de
Fliederweg 1 http://www.objektfabrik.de
D-71640 Ludwigsburg http://joachimtuchel.wordpress.com
Telefon: +49 7141 56 10 86 0 Fax: +49 7141 56 10 86 1
