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 Sat, 01 Jan 2005 19:30:52 GMT
We absolutely encourage innovation. So, if you want to develop a "sql
editor friendly" dynamic sql... feel free. However, I believe the xml
approach is a good happy medium. DBAs should know xml and be able to
comprehend the constructs of ibatis. DBAs already understand their own
various internal procedure languages (Transact SQL, PL/SQL, etc..) and
they are having to deal with return xml results in many cases. So, i
have full trust in their ability to deal with and comprehend dynamic
sql whether it be in a non-xml or xml form.

Brandon

On Sat, 01 Jan 2005 05:52:39 -0600, Vic Cekvenich <vin@friendvu.com> wrote:
> I can have a DBA that does not know Java or IDE or build system edit my SQL.
> .V
> 
> 
> Clinton Begin wrote:
> 
> >>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:
> >>
> >>
> >
> >I love Dave.  But he asked a question, but did he give you any
> >answers?  Don't let luminaries make you feel dumb by asking a question
> >for which even they do not have an answer (or alternate solution).
> >
> >
> >
> >>"Why would I want XML in my SQL?"
> >>
> >>
> >
> >(or more accurately:  "Why would I want my SQL in XML"?)
> >
> >Because it's better than your SQL in Java code.  Trust me.  Try it.
> >Write the equivalent Java/JDBC code for *ANY* SQL Map, dynamic or
> >otherwise.  If you can find one that you can do in less code, let me
> >know.
> >
> >I'm not claiming that there isn't a better solution.  I never have.
> >But I would say that there has never been, nor is there now, anything
> >more "standard" than XML.  The closest 2nd is Velocity, which has a
> >manual about 50 pages long.  Other options are: EL/JSTL, Groovy,
> >JavaScript, Jython, Beanshell. Challenge:  find an editor for any one
> >of these.
> >
> >XML is the simplest, most widely known, tooled and accepted language
> >available today.  The limitations we face in iBATIS are there because
> >our tags are limited.  WE (or actually *I*) didn't implement them as
> >best they could be.
> >
> >We have work to do.
> >
> >Cheers,
> >Clinton
> >
> >
> >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
> >>>>>
> >>>>>
> >>>>>
> >
> >
> >
> >
> 
> 
> --
> RiA-SoA w/JDNC <http://www.SandraSF.com> forums
> - help develop a community
> My blog <http://www.sandrasf.com/adminBlog>
>

Mime
View raw message