[EMAIL PROTECTED] wrote:
It appears that you can set
PRAGMA writable_schema=ON;
Then do a manual UPDATE of the sqlite_master table to insert
I tried it and it seems to work. But it is dangerous. If you mess
up, you corrupt the database file.
Now that is interesting. I didn't realize we could change
sqlite_master directly, but have often thought it could be very handy.
I've often read from it (ie select from sqlite_master), but not
written (ie update or insert).
I imagine that writing to a table or index entry would be disastrous,
eg:
update sqlite_master
set sql = 'create table MyTable( Col1, Col2 )
where name = 'MyTable' and type = 'table'
;
since I think SQLite wouldn't set up the required table data. Correct?
But what about triggers and views? Since (AFAIK, since rootpage = 0)
there's no data structure created by them in the file, can we safely
manipulate them directly in sqlite_master?
For an example, I'll use the predicament from my earlier message
"Altering views, keeping triggers". As a possible solution to keeping
triggers when a view is changed, would it be safe to either:
1. Update the view in sqlite_master directly, thereby avoiding
SQLite's deletion of associated triggers:
update sqlite_master
set sql = 'create view MyView as select Col1, Col2 from MyTable'
where name = 'MyView' and type = 'view'
;
or:
2. Cache the triggers before changing the view, then insert them
directly into sqlite_master:
begin immediate
;
create temporary table Cache as
select * from sqlite_master where type = 'trigger'
;
drop view 'MyView'
;
insert into sqlite_master
select * from Cache
where name not in ( select name from sqlite_master where type =
'trigger' )
;
commit;
I guess "insert or ignore" could be used instead of testing for
existence, if sqlite_master enforces a unique( type, name), but I
don't know if this is safe to assume.
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
