I agree with Max. Sorry for the long email, but I wanted to express a particular way of looking at this problem, as much as an opinion on how best to solve it.
To summarize for those not interested in the long-winded version, I suggest that named query validation be done by the Dialect implementation, rather than by checking against a live DB. Here's why: IMHO checking HQL queries to see that they will run against a specific DB breaks an abstraction. My viewpoint is that the hibernate mapping docs define two things: 1) The class model that your application persists. 2) A mapping of that class model to a RDBMS schema. HQL queries are not intended to be run against the RDBMS schema. They are intended to run against the class model. Whether or not a given RDBMS is capable of supporting the defined mappings and queries is not really relevant to the correctness of the queries. It should be possible to test the correctness of the mappings/queries independently of the database. Compiling the mapping files (including the named HQL queries) will (should) test this. Those cases where Hibernate generates SQL that is not compatible with a specific database should be treated as bugs in Hibernate. (i.e. The Dialect implementation is incorrect.) What I would like to see is one of two types of exception thrown during HQL compilation: 1) An exception to indicate that your HQL syntax is incorrect, (or does not match your class model,) regardless of dialect. 2) An exception that indicates that your HQL, while sytactically correct and matching your class model, cannot be compiled with the given Dialect, because the Dialect does not support some feature that you have used. >From there, it becomes obvious that the only thing that we /need/ to check against a >live database is whether or not the RDBMS schema specified in our mapping docs >matches that installed in the database instance to which we are connected. Checking >for anything further is actually a check for a bugs in hibernate, rather than in your >application code. This is better done through unit tests. Note that this approach does not address the problem of SQL query validation, but syntax checking of arbitrary SQL for every possible dialect is not really Hibernate's job is it? Putting SQL queries in your hibernate app is a concious decision to step away from design purism in order to optimise or to achieve something that cannot be achieved with hibernate's current feature set. The point being made here is that hibernate should allow you to take matters into your own hands, it should not be expected to check that you have done so with competence. That was my $0.02 worth. Cheers ADK > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Max > Rydahl Andersen > Sent: Wednesday, June 30, 2004 6:08 AM > To: Joris Verschoor > Cc: [EMAIL PROTECTED] > Subject: Re[2]: [Hibernate] Check named queries > > > Hello guys, > > What we need is two things: > > 1. check the <class>, <subclass>, <joined-subclass> mapping > etc. against the database > > 2. check if the namedQueries are valid > > This stuff should be: > > - as correct as possible/necessary > - as fast as possible/necessary > ...but most important as usable and maintainable as possible! > > Using some clear mechanism to fetch out the correct sql for > named sql queries or hibernate queries is to fragile IMHO > > Secondly, doing a find for each and every mapped class would be > timeconsuming and not very usable since we would only be able to say: > "bummer - your query xxx failed with error yyy" where yyy could be > something as (un)usable as "ORA-00942: table or view not found" which > does not help the user to identify which table is missing simply that > something is wrong.... > > Thus I propose instead for each of the items above: > > Use JDBC metadata to verify if the table and columns are in the > database (and they have the proper type etc.) > > Utilize the features/code in the mapping package or maybe even > easier refactor SchemaExport to be able to check the existence of > items (it already has the code for traversing the compiled model in > SessionFactory to generate sql - so it should be quite easy to make it > look up stuff in DatabaseMetaData). > > Doing that would allow us to precisly report any mapping "mismatches". > Like: > "Property 'name' of class 'Person' cannot be found on table > 'A_PERSON'" instead of the unusable "ORA-009242" > > It will also be quite maintainable since it would be using > schemaexport code (hopefully) > > Checking namedqueries should *not* be done by executing them - they > could possible execute triggers and if they are wrong they could be > doing other malicous stuff - even though you would do 1=0 to "trick" > them to behave. > > IMHO we should simply just compile named hql queries and see > if the HQL parser > complains. If not - then we assume they are valid. > > Regarding sql queries it's harder - I would rather go for trying a way > to "compile" them too and make it be better to report possible errors. > > That's my 2 cents - and I surely look forward for a hbmlint ;) > > Best regards, > Max Rydahl Andersen > mailto:[EMAIL PROTECTED] > www.hibernate.org > > > > Tuesday, June 29, 2004, 1:02:02 PM, you wrote: > > > Emmanuel Bernard wrote: > > >> I don't see how HQL query can insert update delete. Even a trigger > >> cannot do that. > > > I meant namedsqlqueries, but in the future, it could be > possible to be > > used for inserting / updating data. I don't know if > hibernate is going > > to go that way.. > > > Remember that I won't load any entity for real (1=0). > > I know.. > > >> We could add the keyword in Dialect, but let's start with the HQL > >> queries first. I'm not sure the syntaxt is always > >> SPEIFIC_KEYWORD<select> on every DB. > > > I'm not sure either, but it doesn't have to be explain. It > just needs to > > be some way to check a query. And if the db does not > support something > > similar, you can just ignore it. frequently used databases > support it, > > so i think it's nice to add it. > > >> > >> Joris Verschoor wrote: > >> > >>> compiling named queries only checks hql / mapping > (sometimes hql is > >>> correct, but the query it generates is not supported on the db) > >>> NamedQueries can not only be heavy, but can also destroy > / insert / > >>> update data. > >>> Maybe there we could add some sort of getCheckSyntaxString(String > >>> queryString) to Dialect > >>> This would allow checking on databases that support things like > >>> explain (or maybe some other query that can be used to > check a syntax) > >>> If that's done, you can also check the sql syntax of the hql > >>> namedqueries > >>> > >>> > >>> Emmanuel Bernard wrote: > >>> > >>>> "from A as a where 1=0" should check the DB fields easily wo any > >>>> data size drawbacks. > >>>> For SQL Query this is a bit more complex because queries may be > >>>> heavy, so we can't execute them for real. I'm not sure EXPLAIN is > >>>> really portable. > >>>> > >>>> So to sumarize: > >>>> - Compile all mapping documents to metamodel, check for > persistent > >>>> classes => building the sessionFactory should do the job. > >>>> - Compile all named queries => OK except for SQL ones > >>>> - Validate the database schema by querying each table or > something > >>>> => Shouldn't be too hard with the "from A as a where 1=0" > >>>> > >>>> > >>>> > >>>> Joris Verschoor wrote: > >>>> > >>>>> Hi, > >>>>> > >>>>> I've created that patch. The code in the ant task was > all I could > >>>>> think > >>>>> of... > >>>>> > >>>>> A check for database fields would be quite nice > actually, when working > >>>>> on several machines, I sometimes forget to modify a table. > >>>>> That could be done by doing a find on every perstisted > class (load > >>>>> will > >>>>> throw an error when it's not foud), and checking the exceptions > >>>>> > >>>>> Another thing that's missing is checking the named sql queries > >>>>> (getNamedSQLQueries) > >>>>> Because of all dialects, the queries have to be executed. > >>>>> Maybe an EXPLAIN <SQL> would do, or a TX <SQL> rollback > >>>>> But it doesn't feel right.. > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> Emmanuel Bernard wrote: > >>>>> > >>>>>> Actually the ant task wrapping is done and just call > for a session > >>>>>> factory build. Do you think it's enough? > >>>>>> > >>>>>> Christian Bauer wrote: > >>>>>> > >>>>>>> > >>>>>>> On Jun 28, 2004, at 6:27 PM, Emmanuel Bernard wrote: > >>>>>>> > >>>>>>>> I would be ready to apply the YAGNI principle and not provide > >>>>>>>> such param (yet). > >>>>>>>> What do you guys think? > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> I think it should be configurable. I've heard one or > two requests > >>>>>>> for a "mapping validation tool" that you could run > independently > >>>>>>> from Hibernate. It might be possible that we isolate such > >>>>>>> functionality, or at least, wrap it in an Ant task or > something. > >>>>>>> > >>>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> ------------------------------------------------------- > >>>>> This SF.Net email sponsored by Black Hat Briefings & Training. > >>>>> Attend Black Hat Briefings & Training, Las Vegas July 24-29 - > >>>>> digital self defense, top technical experts, no vendor pitches, > >>>>> unmatched networking opportunities. Visit www.blackhat.com > >>>>> _______________________________________________ > >>>>> hibernate-devel mailing list > >>>>> [EMAIL PROTECTED] > >>>>> https://lists.sourceforge.net/lists/listinfo/hibernate-devel > >>>>> > >>>> > >>>> > >>>> ------------------------------------------------------- > >>>> This SF.Net email sponsored by Black Hat Briefings & Training. > >>>> Attend Black Hat Briefings & Training, Las Vegas July 24-29 - > >>>> digital self defense, top technical experts, no vendor pitches, > >>>> unmatched networking opportunities. Visit www.blackhat.com > >>>> _______________________________________________ > >>>> hibernate-devel mailing list > >>>> [EMAIL PROTECTED] > >>>> https://lists.sourceforge.net/lists/listinfo/hibernate-devel > >>>> > >>>> > >>> > >>> > >>> > >>> ------------------------------------------------------- > >>> This SF.Net email sponsored by Black Hat Briefings & Training. > >>> Attend Black Hat Briefings & Training, Las Vegas July > 24-29 - digital > >>> self defense, top technical experts, no vendor pitches, unmatched > >>> networking opportunities. Visit www.blackhat.com > >>> _______________________________________________ > >>> hibernate-devel mailing list > >>> [EMAIL PROTECTED] > >>> https://lists.sourceforge.net/lists/listinfo/hibernate-devel > >>> > >> > >> > >> ------------------------------------------------------- > >> This SF.Net email sponsored by Black Hat Briefings & Training. > >> Attend Black Hat Briefings & Training, Las Vegas July > 24-29 - digital > >> self defense, top technical experts, no vendor pitches, unmatched > >> networking opportunities. Visit www.blackhat.com > >> _______________________________________________ > >> hibernate-devel mailing list > >> [EMAIL PROTECTED] > >> https://lists.sourceforge.net/lists/listinfo/hibernate-devel > >> > >> > > > > > > ------------------------------------------------------- > > This SF.Net email sponsored by Black Hat Briefings & Training. > > Attend Black Hat Briefings & Training, Las Vegas July 24-29 - > > digital self defense, top technical experts, no vendor pitches, > > unmatched networking opportunities. Visit www.blackhat.com > > _______________________________________________ > > hibernate-devel mailing list > > [EMAIL PROTECTED] > > https://lists.sourceforge.net/lists/listinfo/hibernate-devel > > > > -- > Best regards, > Max Rydahl Andersen > mailto:[EMAIL PROTECTED] > www.hibernate.org > > > > > > ------------------------------------------------------- > This SF.Net email sponsored by Black Hat Briefings & Training. > Attend Black Hat Briefings & Training, Las Vegas July 24-29 - > digital self defense, top technical experts, no vendor pitches, > unmatched networking opportunities. Visit www.blackhat.com > _______________________________________________ > hibernate-devel mailing list > [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/hibernate-devel > ----------------------------------------------------------------------------------------------- Have you seen our website?.... http://www.vodafone.co.nz CAUTION: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Thank you. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Vodafone New Zealand Limited. Vodafone New Zealand Limited 21 Pitt Street, Private Bag 92161, Auckland, 1020, New Zealand Telephone + 64 9 357 5100 Facsimile + 64 9 377 0962 ------------------------------------------------------- This SF.Net email sponsored by Black Hat Briefings & Training. Attend Black Hat Briefings & Training, Las Vegas July 24-29 - digital self defense, top technical experts, no vendor pitches, unmatched networking opportunities. Visit www.blackhat.com _______________________________________________ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
