ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Alternate dynamic SQL syntax?
Date Thu, 30 Dec 2004 23:48:10 GMT
Well, the beauty is that you certainly should be able to write your version.

Oooh! Name dropping are we!? ;-) Did you know Dave Thomas has not
coded a stitch of Java for 2 years? He now codes in Ruby. So, if you
engaged Dave a little deeper you would find that he would ask you why
you are coding Java. ;-)

I can honestly say that i would hardly be stumped as to why i would
use xml tags. 1) The sql is not in it's final form. It helps generate
the end sql. It's not THE sql 2) Its easily distiguishable from
comments and therefore much clearer to recognize. Too many # signs
begin to cloud the actual sql statement 3) because it is xml I can use
my xml editor to edit it. I don't know of any developer who would not
easily be able to recognize what is happening. As compared to using an
unfamiliar delimeter that developers would have to get used to. 4)
Ibatis is already constructed with xml and it retains it's continuity
to remain in the same syntactical semantic than to introduce a new
one.

Your goals are a bit different than mine. You want something you can
work with in your sql editor. I want something i can work with in my
xml editor and that stands apart from the # and $ signs. For me, I
decide which portions of my sql will perform dynamically and tool my
sql statement in it's most complex state. I can honestly say that I
have never had trouble with this process. But, to each his own...
that's the beauty of a pluggable framework.

IMO the official direction of the IBatis supported dynamic sql should
remain as xml with an EL ehnahcement and some tag simplifications.

Brandon

P.S. Clinton, we need to discuss this sometime soon. I've been waiting
for a while to revamp the dynamic sql. Is everything in place to
begin?

On Thu, 30 Dec 2004 17:03:50 -0600, Severtson, Scott (Associate)
<Scott.Severtson@qg.com> wrote:
> Brandon,
> 
> > The way i see things is that dynamic tags are a form of scripting that
> > take advantage of EL. So, there is a distinction between supporting an
> > EL with our tagset or supporting scripting in a similar way as Ant
> > does. I don't think EL and Scripting are the same thing and i don't
> > think EL should stand alone as a full blown scripting solution.
> 
> Exactly. I look at the dynamic SQL tasks as:
> * Data retrieval (via EL)
> * Data replacement (i.e. #...# syntax)
> * SQL replacement (i.e. $...$ syntax)
> * Conditionals
> * Looping
> 
> Given such a small set of functionality, using a general purpose
> scripting language would be overkill, and would move dynamic SQL from a
> templating solution to a programming solution. Interesting, but
> obviously not the intended purpose.
> 
> 
> > Examine the iterate tag:
> >
> > WHERE someValue IN
> > <iterate property="myProperty" open="(" close=")" conjunction=",">
> > #myProperty[]#
> > <iterate/>
> >
> > This would get pretty messy in a scripting language because all of the
> > internal processing that the <iterate> tag represents would be exposed
> > in the mapped statement and we begin to push back to clumsier looking
> > sql/code mingling.
> >
> > We could provide for custom script functions. But, i have yet to see
> > any scripting language that would keep the mapped statement as clean
> > and readable as the tags do.
> 
> Agreed. Although I can imagine doing something with closures +
> here-documents, it certainly would be strange syntax.
> 
> 
> > I think we need to expand our tags to be more JSTLish in nature. We
> > need to provide some conditional tags. The EL then becomes the means
> > for the tags to access the scoped objects and perform basic boolean
> > testing. One of the advantages of the tags is that they provide for
> > some very tidy coding.
> 
> Also agreed. A generic "if" statement with EL support would simplify a
> number of issues I've run into.
> 
> So, we're on the same wavelength. I totally agree that iBATIS needs a
> dynamic SqlMap implementation with a generic if statement and EL
> support. However, we diverge on one major point: XML.
> 
> ----
> 
> At the No Fluff Just Stuff conference in October, I was describing
> dynamic SqlMaps to Dave Thomas (of Pragmatic Programmers fame, not
> Wendy's :). He stumped me with a single question:
> 
> "Why would I want XML in my SQL?"
> 
> I wibbled.
> 
> I knew I didn't want SQL in Java code; comparatively, XML is so much
> better. But, to be honest, it just creates diffefrent problems.
> 
> During development, you need to work with the SQL in a query tool, to
> code, test and tune it. At best, it's completely static SQL, and you
> just need to copy the SQL to your editor. Realistically, it's parameter
> mapped SQL, and you need to replace #object.value:INTEGER# with testing
> values. Worst case is dynamic SQL - what do you do with the tags?
> Personally, I comment out both parameters and dynamic SQL tags, and
> undesired conditional contents. What about prepends? Obviously, I've got
> to get them out of the tags and back into the SQL.
> 
> OK, so my SQL is coded, tested, and tuned. Now I've got to reverse the
> process to get it back into the SqlMap.
> 
> Ugg.
> 
> To wrap up my tale, I've spent the past 3 months mulling this over. My
> criteria for a better dynamic SqlMap:
> 1. Leave the SQL in an executable, testable state
> 2. Be at least as easy to code and understand as current solution
> 3. Use an existing EL to simplify development/aid adoption
> 
> Number 1 drives the rest of the design. Basically, I'm working on a very
> small (~6 keywords!), special-purpose scripting language, embedded in
> SQL comments. The below is very much a draft; I'm sure it will change
> greatly as implementation progresses:
> 
> SELECT DISTINCT
>         response.id,
>         response.response_status_name,
>         response.interview_version_id,
>         response.user_id,
>         response.date,
>         response.response_flag_name
> FROM
>         response
>         INNER JOIN interview_version
>                 ON response.interview_version_id = interview_version.id
>         INNER JOIN interview
>                 ON interview_version.interview_id = interview.id
>         INNER JOIN "user"
>                 ON response.user_id = "user".id
> --#dynamic prefix:WHERE
>         WHERE
>                 --#if test:!empty user && user.userId != 0
>                         response.user_id = 32 --#param from:user.userId
>                 --#end
>                 --#if test:!empty status prefix:AND
>                         --? AND response.response_status_name =
> 'type.response.status.completed' --#param from:status.name
>                 --#end
> --#end
> ORDER BY
>         response.date --#replace with:orderBy
> 
> Notes:
> * The "--#" syntax is to differentiate between "real" SQL comments and
> executable ones.
> * --#if test:... is the generic IF syntax.
> * prefix:... is prepend="..." stood on its head. Instead of introducing
> SQL fragments as needed, they are removed when not needed. I'm not sold
> on the name, though.
> * --#param from:... replaces #...#. The parser will mark the last SQL
> literal token for replacement. It also gets around the "I want to
> specify a null replacement value without specifying a type" problem by
> allowing --#param from:... null:...
> * --#replace with:... replaces $...$, and operates on the whole line it
> occurs on.
> * --? comments out code for SQL development, with the --? removed at
> SqlMap parse time. Allows disabling code for testing purposes, while
> keeping it executable for iBATIS.
> 
> Open issues:
> * Because of iBATIS.NET, I'm trying to find a language-neutral, decent
> EL I can reuse. If .NET were not a consideration, I would probably go
> with JSTL-EL, just because web developers may be more familiar with it.
> OGNL comes in a close second.
> * I don't think I've ever used the <iterate> tag, so I haven't put a
> whole lot of thought into a syntax for it. Suggestions would be greatly
> appreciated.
> * I'm afraid that requiring --#replace with:... to operate on the whole
> line might be too restrictive. Nor do I like the name, but I think it's
> better than "Simple Dynamic SQL", when it can wreak more havoc than any
> other functionality.
> * I'll probably also support /*# ... #*/ style-SQL comments for
> executable code, to allow placement somewhere other than the end of
> line. Note that the /* ... */ SQL syntax in non-standard, though.
> * I'll probably also support abbreviated syntax, i.e. --#rep instead of
> --#replace, for those who don't like typing.
> * I'm not sold on the name:value syntax; I like working without value
> delimiters, but it makes writing a recursive decent parser difficult, as
> my lookahead will have to support a fairly large window.
> 
> Anyhow, I've started working on an ANTLR grammar for the parser (after
> learning ANTLR first). However, we're about to have our third child, so
> I doubt I'll have much time in the short term. Think of this as a
> long-term proposal :)
> 
> Any comments/suggestions would be *greatly* appreciated; I've run this
> by some colleagues, but unfortunately, most are not familiar with the
> joys/pains of dynamic SqlMaps, so their input has been limited.
> 
> ----
> Scott Severtson
> Centare Group, LLC
> 
> > Brandon
> >
> > On Thu, 30 Dec 2004 11:22:19 -0700, Clinton Begin
> > <clinton.begin@gmail.com> wrote:
> > > Hi Scott,
> > >
> > > 1.  In a sense, iBATIS has always been pluggable in this
> > way.  The Sql
> > > interface is the generalization that allows different Sql sources to
> > > be applied to a mapped statement.  I have not made any public
> > > documentation for it.  The bigger challenge is how we'll add the
> > > "plugability" to the XML parser.  It shouldn't be too hard, but we
> > > just need to do some tinkering to see what will work best.
> > >
> > > 2.  I can't speak for Brandon, save to say that I don't think we've
> > > landed on which EL or template language we want to support.  Perhaps
> > > the best thing to do is to just implement it and see how we can plug
> > > it in?
> > >
> > > The good news is that with the 2.0.9/2.1 release, we'll have
> > > implemented a number of the most critical features, which
> > means we'll
> > > have more time to tinker with this.  I've also rewritten the XML
> > > parser to make it easier to add such things.
> > >
> > > >> I've got an itch to scratch re: dynamic SQL syntax, and
> > > >> I was wondering what I could use as a starting point.
> > >
> > > You can get cream for that.  ;-)  But really, if you look at the
> > > package com.ibatis.sqlmap.engine.mapping.sql.* you'll see three
> > > implementations: Static, Simple Dynamic, and Dynamic.  These are in
> > > order of increasing complexity.
> > >
> > > The job of an Sql implementation is to: 1) prepare and
> > return the SQL,
> > > 2) prepare and return the parameter map, and 3) prepare and
> > return the
> > > result map.
> > >
> > > Parameter Maps and Result Maps are (in their simplest form) property
> > > to column mappings.
> > >
> > > Have a look and see if you can work with that.
> > >
> > > Cheers,
> > > Clinton
> > >
> > > On Wed, 29 Dec 2004 12:10:45 -0600, Severtson, Scott (Associate)
> > > <Scott.Severtson@qg.com> wrote:
> > > > Clinton/Brandon,
> > > >
> > > > I was wondering if anything came from the Sourceforge
> > Forum discussions
> > > > in June regarding velocity/jexl/ognl templating
> > > > (http://sourceforge.net/forum/message.php?msg_id=2620936)
> > for dynamic
> > > > SQL Maps? I see that as late as November, Clinton said
> > "No decisions
> > > > have been made yet."
> > > >
> > (http://www.mail-archive.com/ibatis-user-java@incubator.apache
> > .org/msg00
> > > > 030.html)
> > > >
> > > > Two specific questions:
> > > > 1. Did Clinton ever create the plug-points for alternate
> > dynamic SQL
> > > > implementations
> > > >
> > (http://sourceforge.net/forum/message.php?msg_id=2622001)? If
> > so, did he
> > > > produce any documentation?
> > > >
> > > > 2. Did Brandon ever make any progress/prototypes for an alternate
> > > > implementation?
> > > >
> > > > I've got an itch to scratch re: dynamic SQL syntax, and I
> > was wondering
> > > > what I could use as a starting point.
> > > >
> > > > ----
> > > > Scott Severtson
> > > > Centare Group, LLC
> > > >
> > >
> >
>

Mime
View raw message