Re: [sqlite] Altering views, keeping triggers
Hi Joe, You've got the right idea. Just make use of sqlite_master.tbl_name. select sql || ';' from sqlite_master where type = 'trigger' and tbl_name = 'MyView'; sqlite_master.name is the name of the table/view/index/trigger itself, and sqlite_master.tbl_name is what it acts on. I think that's mostly right. tbl_name "is what it acts on" for an index and trigger, but not for a view. Unfortunately, for a view, tbl_name is just the name of the view, not the table (or view) that it acts on. That's partly understandable since a view could act on more than one table. But I had overlooked the fact that tbl_name will tell me what a trigger acts on. So thanks a lot for your thoughtful posting. That will help :-) Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Altering views, keeping triggers
--- T <[EMAIL PROTECTED]> wrote: > As mentioned, all the associated triggers are deleted. IS there a way > to systematically locate and keep them? Such as: > > set viewDefinition to SQLExec( "select sql || ';' from sqlite_master > where type = 'view' and name = 'MyView';" ) > set triggerDefinitions to SQLExec( "select sql || ';' from > sqlite_master where type = 'trigger' and sql like '%on MyView%';" ) > SQLExec( "drop view 'MyView';" ) > set viewDefinition to ChangeColumnDefinitions( viewDefinition ) > SQLExec( viewDefinition ) > SQLExec( triggerDefinitions ) > > However, that would fail because the test "like '%on MyView%'" is not > robust or accurate enough. You've got the right idea. Just make use of sqlite_master.tbl_name. select sql || ';' from sqlite_master where type = 'trigger' and tbl_name = 'MyView'; sqlite_master.name is the name of the table/view/index/trigger itself, and sqlite_master.tbl_name is what it acts on. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Altering views, keeping triggers
Oops, sorry, that first paragraph should have read as below (I said "trigger" once where I meant "view"): Short version of question: I frequently want to alter view definitions, such as renaming them or changing the column definitions. The only way to do this is to drop the old view and create a new one (eg with the same name). The problem is that when I do this, any associated triggers are deleted by SQLite. What's the best way to keep the associated triggers? Long version of question: I can rename or add a column to a table (using SQLite's "alter table" command"). But to rename or add a column to a view I have to drop the view and recreate it. And if I want to to alter existing columns in a table, I have to drop the table and recreate it. That's fine, I can cope with that. My problem is that, to my mind, SQLite seems inconsistent in how it deals with triggers compared to views, when a precedent table or view is deleted. Here are the actions I take and the effects in SQLite: Action Effect on associated views Effect on associated triggers -- -- - Rename table None Rename table references Drop table None Delete trigger Drop view None Delete trigger I would rather than SQLite consistently did not change any dependent triggers when I changed a precedent table or view (ie "none" in all the grid cells above). That would be consistent with how it treats dependent views. ie SQLite seems happy to keep views that no longer link to anything, but not triggers. And it's easier for me to explicitly delete triggers, if I want to, than try to find out what triggers will be affected, cache them somehow, perform my action, and reconstitute the triggers. As a specific case, when I want to change existing columns in a table or view, the only way to do that in SQLite is to cache the old definition, drop the table or view, edit the cached definition (changing the columns) and recreate it. That's fine. But now all the associated triggers have disappeared. Or, in psuedo code: set viewDefinition to SQLExec( "select sql from sqlite_master where name = 'MyView';" ) SQLExec( "drop view 'MyView';" ) set viewDefinition to ChangeColumnDefinitions( viewDefinition ) SQLExec( viewDefinition ) As mentioned, all the associated triggers are deleted. IS there a way to systematically locate and keep them? Such as: set viewDefinition to SQLExec( "select sql || ';' from sqlite_master where type = 'view' and name = 'MyView';" ) set triggerDefinitions to SQLExec( "select sql || ';' from sqlite_master where type = 'trigger' and sql like '%on MyView%';" ) SQLExec( "drop view 'MyView';" ) set viewDefinition to ChangeColumnDefinitions( viewDefinition ) SQLExec( viewDefinition ) SQLExec( triggerDefinitions ) However, that would fail because the test "like '%on MyView%'" is not robust or accurate enough. Any other ideas or comments? How do you approach the issue, or don't you have the need to alter views with associated triggers? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Altering views, keeping triggers
Hi all, Short version of question: I frequently want to alter view definitions, such as renaming them or changing the column definitions. The only way to do this is to drop the old trigger and create a new one (eg with the same name). The problem is that when I do this, any associated triggers are deleted by SQLite. What's the best way to keep the associated triggers? Long version of question: I can rename or add a column to a table (using SQLite's "alter table" command"). But to rename or add a column to a view I have to drop the view and recreate it. And if I want to to alter existing columns in a table, I have to drop the table and recreate it. That's fine, I can cope with that. My problem is that, to my mind, SQLite seems inconsistent in how it deals with triggers compared to views, when a precedent table or view is deleted. Here are the actions I take and the effects in SQLite: Action Effect on associated views Effect on associated triggers -- -- - Rename table None Rename table references Drop table None Delete trigger Drop view None Delete trigger I would rather than SQLite consistently did not change any dependent triggers when I changed a precedent table or view (ie "none" in all the grid cells above). That would be consistent with how it treats dependent views. ie SQLite seems happy to keep views that no longer link to anything, but not triggers. And it's easier for me to explicitly delete triggers, if I want to, than try to find out what triggers will be affected, cache them somehow, perform my action, and reconstitute the triggers. As a specific case, when I want to change existing columns in a table or view, the only way to do that in SQLite is to cache the old definition, drop the table or view, edit the cached definition (changing the columns) and recreate it. That's fine. But now all the associated triggers have disappeared. Or, in psuedo code: set viewDefinition to SQLExec( "select sql from sqlite_master where name = 'MyView';" ) SQLExec( "drop view 'MyView';" ) set viewDefinition to ChangeColumnDefinitions( viewDefinition ) SQLExec( viewDefinition ) As mentioned, all the associated triggers are deleted. IS there a way to systematically locate and keep them? Such as: set viewDefinition to SQLExec( "select sql || ';' from sqlite_master where type = 'view' and name = 'MyView';" ) set triggerDefinitions to SQLExec( "select sql || ';' from sqlite_master where type = 'trigger' and sql like '%on MyView%';" ) SQLExec( "drop view 'MyView';" ) set viewDefinition to ChangeColumnDefinitions( viewDefinition ) SQLExec( viewDefinition ) SQLExec( triggerDefinitions ) However, that would fail because the test "like '%on MyView%'" is not robust or accurate enough. Any other ideas or comments? How do you approach the issue, or don't you have the need to alter views with associated triggers? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
