ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vic Cekvenich <...@friendvu.com>
Subject Re: Alternate dynamic SQL syntax?
Date Sat, 01 Jan 2005 11:52:39 GMT
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