Hi,
I agree that requested features are necessary, although I don't necessarily
agree with the suggested implementation. Currently, I am focusing on spec
compliance, and I need to update the code to the final spec. As Marc
suggested, if you want to see these features soon, submit a patch,
otherwise, I will implement them after spec compliance.
See my comments below.
> -----Original Message-----
> From: Hunter Hillegas [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 21, 2001 3:11 PM
> To: JBoss Dev
> Subject: Re: [JBoss-dev] EJB/QL - JBoss extentions
>
>
> See my comments below...
>
> > From: Dave Smith <[EMAIL PROTECTED]>
> > Reply-To: [EMAIL PROTECTED]
> > Date: Fri, 21 Sep 2001 15:56:22 -0400
> > To: [EMAIL PROTECTED]
> > Subject: [JBoss-dev] EJB/QL - JBoss extentions
> >
> >
> > 1. No order clause.
> > Well ... this is obvious and I have not tested the current
> stuff to see
> > if Dain put it in.
>
> It's not in the spec and most people think it's something
> that definitely
> SHOULD have been in... I assume we can implement it as
> extra-functionality... I don't know if it's in already.
>
I suggest that we add an order element to the query section in
jbosscmp-jdbc.xml file. Something like
<query>
<description>Find all orders with the specified status</description>
<query-method>
<method-name>findWithStatus</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
<order>ORDER_NUMBER</order>
</ejb-ql>
</query>
Note the order element would contain the exact sql that would appear in the
order clause. I think that we should require the user to use the sql column
names and not support a bean level field ordering. The reason being that it
would be difficult to determine how multi column fields would be ordered,
and it also allows the user to order by column that are not mapped.
> >
> > 2. No limit/offset or cursors.
> > In a word searches. If you are tring to search for
> something you display
> > a list of current values based on a selection criteria. What happens
> > when the list is 100,000 records. With current DB code you
> would declare
> > a cursor or use offset and limits and only retreive a bit at a time.
> >
>
> I would find this very useful as well.
>
Duh. I think Oleg is working on this.
> > 3. Dynamic where clause
> > Prompting the user for a search criteria and returning a result set
> > based on those criteria. Also would be handy to have cursors/limit.
>
> Isn't this already in there?
>
I think this would be best handled by a bmp style finder (ejbFindBlah)
implementation, which is supported just like it was in jaws. These types of
query languages tend to be very application specific.
> > 4. database functions
> > If I am doing a query on a datestamp type field but I only
> care about
> > the date part, in postgresql I need to use a postgresql
> function that
> > only returns the date.
Date handling is EJB-QL sucks. Here is how you can do it
myRef.date >= ?1 and myRef.date < ?2
Then pass in the day and the next day. The big benefit is its spec
compliant.
> >
> > For 1,3,4 I would propose that we add a special jboss
> function into the
> > QL, something like jboss_sql_inline() and what is between
> the brackets
> > is just passed through to the sql engine. Arguments could be passed
> > through to parts of the code with ??1 etc, sort of an
> escaped argument.
> > So you could have
> >
> > select object(o) from foo o where o.id=?1 and
> > jboss_sql_inline(datefunc(datefield) = ??2)
> >
> > I'm willing to put some time into this. (ie coding)
>
> As am I.
>
I don't recommend this. It would make the query engine much more difficult
then it needs to be. Just off the top of my head how does this fit into the
EJB-QL BNF? What is the type of a jboss_sql_inline function? What is the
precedence? What is the allowed text inside of the function? Are path
statements allowed? Are parameters allowed? How do we escape things that
look like parameters or path statements if allowed?
I have been thinking about statements like this for a while, and I think I
have a solution that every one will like. I think the real goal is to allow
much more complex queries then can be generated from ejb-ql, and to allow
optimized queries (hinting). I think the best way is to support raw-sql
queries. A raw-sql query would have the following properties:
* allow any sql statement
* hand mapping of results from selected columns
* internal state of parameters may be used
I am thinking of an xml spec that is something like
<query>
<description>Count orders with same status as the specified
order</description>
<query-method>
<method-name>ejbSelectStatusCount</method-name>
<method-params>
<method-param>org.foo.bar.Order</method-param>
</method-params>
</query-method>
<raw-sql>
<sql>
SELECT COUNT(order_id)
FROM order
WHERE status = ?1.status
</sql>
<results-map>
<ordinal>1</ordinal>
</results-map>
</raw-sql>
</query>
The results-map would have to be much more complicated to support
multi-column pk keys and eager loading.
The biggest problem with this solution is the raw-sql specification could be
very difficult, so to go with this spec we would supply a tool that takes
the current ejb-jar.xml (especially the ejb-ql section) and
jbosscmp-jdbc.xml files and generates a new jboss-cmp.xml file with all of
the ejb-ql statements converted to raw-sql queries. This is part of a tool
set that I would like to write to help with the optimization process. It
also do things like fill in all of the default mappings of tables.
Any way, I think this would allow people who need advanced db features to do
whatever they want, and allows us to keep the ejb-ql parser spec compliant.
Also it would not be that difficult to implement, because the xml structure
is similar to how the internal structure of queries are implemented in the
engine.
I hope all of this is intelligible.
-dain
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
