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
> <[EMAIL PROTECTED]> 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)
> > <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
> .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
> > >
> >
>
