To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=73132
------- Additional comments from [EMAIL PROTECTED] Wed Apr 25 20:21:45 +0000 2007 ------- There seems to me, two separate issues here. First the way HSQLdb supports views; second the anomaly that corrupted the Base file. Looking at the corrupt file I can't say for sure as I was not there - but I can make an educated guess as to the steps martinahlin took to produce it. HSQLdb supports a non-standard feature with views, namely if a view uses a select * from t_table as part of the SELECTION statement then adding a column to t_table will automatically update the column list in the view definition. That does not work in the version of HSQLdb shipped with OOo. The bug is HSQL's and not Ooo's. ( this feature is supported by other RDBMSs currently ) Base however takes HSQL at their word and allows a user to add a field. In fact as long as that session is open the view acts as if it where updated correctly. You can run it and the new column from t_table will be displayed. When the Base file is closed however the SCRIPT file that is created does not reflect this change and therefore the next time the file is opened the user gets the error message as described. To show that it is really HSQL's bug and not Base's you can add the column to t_table using an ALTER TABLE command in the SQL window and the results will be exactly as described in the previous paragraph. ( I suppose if I really want to prove it is HSQL and not OOo I should go and do this to a HSQldb server database using just their tools....not today however ) There also seems to be no way to force HSQL to update that script statement. Forcing a checkpoint or doing a shutdown compact from the SQL window makes no difference. The create view statement in the script file will be one field name short. Sure enough if one unpacks the attached Base file, then opens the embedded script file you find that the create view statement at line 13 uses a SELECT * FROM TABLE command and then if you look at the create table command for that table you find two columns at the end ( "test" and "asdf" ) that are not included in the create view command. Simply adding these column names to the statement on line 13, re-archiving the file and opening in Base gets everything back to normal. ( I will attach the corrected file to the issue - I do realize that from martinahlin's perspective this is all "A day late and a dollar short" to say the least. ) So at this point, IMO, the next step is to have someone check with the HSQL folks to insure hey know of the problem. The next issue here is the request that OOo not lock tables for altering the structure when used in views. This however is not exactly the case. HSQLdb supports the following actions when working with views. A column may be added to any table that participates in a view. ( as long as the select statement in the view is anything other then SELECT * this causes no problems, as far as I have been able to test ) A column may be dropped from a table that participates in a view so long as that column is not used in the view. ( Tested using Base with no problems found ) A columns name can be changed - same caveat, the column is not used in a view. ( Tested with Base, no problems found ) A columns datatype can be changed - same caveat, the column is not used in a view. ( Tested with Base, no problems found ) In other more 'robust' RDBMS one may drop columns or rename them in views – each RDBMS handles these cases in their own way. For the most part the view will be invalidated and most likely need to be re-created before it can be used. PostgreSQL seems to be the most flexible when it comes to this – not really a surprise. In HSQLdb however there is no support for altering a view. It can be created and it can be dropped. So there is no real option for invalidating it and waiting for the user to edit the definition. I would go further even – from my perspective this is a benefit to the end user. Thinking about the scenario that martinahlin describes in the description – that the view is used to generate a report – what happens in a larger database application. The user of the report finds out that it won't run only when they try to run it – they call the DBA and he remembers that a few minor changes where made to the databases physical structure – and had forgotten to validate the effected view. In the case of a Base (HSQL) user, an attempt to alter one of the tables such that the report would not run would not be allowed without giving plenty of warning that this is going to be a side effect of the change, before he finds out perhaps days later when the report is needed. Also there is no direct linkage between the view in the database and the report definition in the Base file, accept for when it is being used. So the user can drop the view and recreate it as needed without having to recreate the report. However, this is where a real aid for the user could be added in Base. If Base could add support for easily altering the view. Given the fact that the view definition is stored in a system table in the embedded database this seems like a ripe candidate for an extension for the Base module. With the changes being made to the API in 2.3 an extension developer should be able to put together a wizard that would pull the definition out of the table INFORMATION_SCHEMA.SYSTEM_VIEWS open a query designer window and populate it with the select statement. The user can then drop the view, alter the table(s) as needed, update the query designer window and re-create the view with the same name. At least that would handle the case for an HSQL embedded Base file. Base files connecting to other RDBMS should prove to be as easy or easier to support for updating the views. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
