comments below...
> >>>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
Sorry, wasn't clear. I was suggesting a tool that would auto generate the
jbosscmp-jdbc.xml file with all defaults filled in. This would help with
configuration and optimization. Configuration of cmp is getting
complicated.
> 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.
>
The example you have given can be done today with declared-sql.
Declared-sql is designed to satify 95% of the cases. The other 5% are very
important and should be addressed. I most large applications there are a
few queries that must be hand optimized, and it would be nice to do this
declarativly. Additionally, there are several features in standard sql that
are not available in ejb-ql, such as group-by. I think this is important
but not critical, and that is why I have not started working on it.
-dain
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
