Now that is interesting. I didn't realize we could change
sqlite_master directly, but have often thought it could be very
handy.
Warning: If you mess up, your database becomes unreadable and
unrepairable. This is a very dangerous feature. If you use it and
you lose data: no tears.
Let me clarify. What I'm asking is whether editing the view and
trigger entries in sqlite_master is safer than editing table and
index entries (see below).
I'd greatly appreciate any educated insight, not necessarily a
guarantee. :-)
Thanks,
Tom
----
From: T&B <[EMAIL PROTECTED]>
Date: 15 August 2007 11:50:53 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] PRAGMA writable_schema=ON;
[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]
-----------------------------------------------------------------------------