Summary:

Can hibernate now or later be made to support non-table object "existence testing" and creation prior to hibernate executing any other schemaupdate methods against the database? Important for the availability of Firebird basic SQL functions which are standard in other databases. Useful for the declaration of helper Firebird stored procedures.

Details:

Is there a way of including within the schema maintenance abilities of hibernate (specifically using schemaupdate) a means to create a stored procedure and first test for its existence to determine the need to create it before it executes any other schemaupdate methods against the database?

I've worked a fair bit with Firebird and I had to create support stored procedures such as spx_drop_dependencies(<object_name>) to make my life a whole lot easier when dealing with another O/R framework which created/updated database objects dynamically as the exact dependency order to be dropped can be daunting to figure out manually and time consuming especially as the relationships grows and get more complex.

What would be nice is for each non-table object being able to specify a test SQL string and if the count returns 0 then run a SQL statement or file to load up this stored procedure (or UDF declaration) before any of the other schemaupdate code is ran against the database so that I can use these support procedures (or supplied UDFs) to assist schemaupdate code.

Being able to test for the existence of an object and the generation of this object if it doesn't exist would also come in very handy with databases such as Firebird where a lot of the supplied functions (which for a lot of other databases are basic functions) have to be initially declared even though they are supplied with the database i.e string length, abs, acos, atan, pi, etc. With Firebird before you can use rtrim you need to execute the following SQL at least once in the life of the database:

DECLARE EXTERNAL FUNCTION rtrim
CSTRING(32767)
RETURNS CSTRING(32767) FREE_IT
ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';

With Firebird UDFs there are two sets of complimentary libraries supplied with the database but for which none of them are by default declared and therefore unavailable by default. Most of these are so basic as being taken for granted as existing and therefore I suggest that they be added to the constructor registerFunction calls but it would be more foolproof if they could be tested as needing to be "declared" first and if so "declared".

I don't mind sharing these generic stored procedures if useful to the hibernate project i.e. spx_create_or_alter_exception (useful so you don't have to test for the exception existence before having to modify or create it), spx_drop_all_exceptions, spx_drop_all_tables, spx_drop_dependencies, spx_drop_external (the functions), spx_drop_generator (some like this one are more like helper functions just to avoid exceptions being thrown) and spx_drop_table.