As normal dain converts my ramblings into good ideas .. Comments below ..
> 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.
>
Cool .
>
>>>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.
>
>
Bad example on my part. I think thought what is stated below will cover it.
>
> 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.
>
Yup, this is the way to do it. All extra stuff should be in the .xml
config file.
So with the above syntax for my own queries why not
<query>
<description>My own hard coded where clause </description>
<query-method>
<method-name>ejbMyQuery</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<raw-sql>
<sql>
SELECT *
FROM my_table
WHERE ?1
</sql>
<results-map>
<ordinal>Many</ordinal>
<return_type>org.foo.bar.MyTable</return_type>
</results-map>
</raw-sql>
</query>
As far as generating another file with optimized queries I think this is
overkill, confusing and not necessary. With most databases the query
optimizations are done at the sql level why not just use the above for
queries that need to be optimized. For the majority of cases the basic
query syntax will be fine so we should code for 95% of the cases not 5%.
I would think that we should use the same principal as before. If it is
in jbosscmp-jdbc.xml then override what is in ejb-jar.xml. So for the 5%
of cases you whould have to have entries in both config files.
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
