You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Clinton Begin <cl...@gmail.com> on 2005/01/01 01:33:32 UTC
Re: Alternate dynamic SQL syntax?
> 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)
<Sc...@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
> > <cl...@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)
> > > <Sc...@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
> > > >
> > >
> >
>
Re: Alternate dynamic SQL syntax?
Posted by Brandon Goodin <br...@gmail.com>.
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 <vi...@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)
> ><Sc...@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
> >>><cl...@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)
> >>>><Sc...@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>
>
Re: Alternate dynamic SQL syntax?
Posted by Vic Cekvenich <vi...@friendvu.com>.
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)
><Sc...@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
>>><cl...@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)
>>>><Sc...@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>