Re: [sqlite] PRAGMA writable_schema=ON;
--- Scott Hess <[EMAIL PROTECTED]> wrote: > On 8/15/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > If you find a way to get sqlite3 to re-parse the schema after your direct > > sqlite_master change, please post it to the list. I don't think it can > > be done without modifying the code or making a new connection. > > You could probably manage it by doing some other schema change. Yeah, you may be able to force a reload of a schema related to a specific tbl_name with ALTER TABLE xxx RENAME TO yyy (twice, presumably). But that's an even worse hack. :-) CREATE TABLE will do a schema reload, but that's self-defeating. I can't see anything that will force a reload of view or trigger definitions, barring dropping and recreating them, which again defeats the purpose. This is just a philosophical exercise - it's all hacking territory anyway, as Dan put it. Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
On 8/15/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > If you find a way to get sqlite3 to re-parse the schema after your direct > sqlite_master change, please post it to the list. I don't think it can > be done without modifying the code or making a new connection. You could probably manage it by doing some other schema change. Maybe create a table then drop the table as two separate transactions. You might need to do it in a different connection, though, which isn't much better than simply closing and re-opening the current connection. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
--- T <[EMAIL PROTECTED]> wrote: > > Even if you got the sqlite_master table entries right, the in- > > memory sqlite schema data structures would not be in sync. > > Yes, but my point/question was, would that not only apply to tables > and indexes? I expect that views and triggers have no data structures > (eg rootpage = 0) so there's nothing to get out of sync. Change the SQL column for a VIEW in sqlite_master and see for yourself that sqlite3 does not pick it up. You have to drop the connection and make a new connection to see it. > > You'd be better off using the normal DROP/CREATE SQL statements and > > published sqlite APIs to do this sort of thing. > > Ideally, yes. But I've come across a few situations (such as the one > I gave as an example) where being able to write to the sqlite_master > tables would permit a solution that could be done totally in SQL, or > which would save parsing schema. If you find a way to get sqlite3 to re-parse the schema after your direct sqlite_master change, please post it to the list. I don't think it can be done without modifying the code or making a new connection. You would also be at the mercy of future incompatible internal SQLite changes. Only using the public API protects you from such future breakage. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
Hi Joe, Even if you got the sqlite_master table entries right, the in- memory sqlite schema data structures would not be in sync. Yes, but my point/question was, would that not only apply to tables and indexes? I expect that views and triggers have no data structures (eg rootpage = 0) so there's nothing to get out of sync. You'd be better off using the normal DROP/CREATE SQL statements and published sqlite APIs to do this sort of thing. Ideally, yes. But I've come across a few situations (such as the one I gave as an example) where being able to write to the sqlite_master tables would permit a solution that could be done totally in SQL, or which would save parsing schema. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
T <[EMAIL PROTECTED]> wrote: > >> 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). > No. Any mistake, however slight, in either triggers or views, will probably leave the database unreadable and unrepairable. SQLite is unforgiving of errors in the sqlite_master table. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
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 <[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] -
Re: [sqlite] PRAGMA writable_schema=ON;
T <[EMAIL PROTECTED]> wrote: > [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. 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. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
--- T <[EMAIL PROTECTED]> wrote: > [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). Even if you got the sqlite_master table entries right, the in-memory sqlite schema data structures would not be in sync. You'd be better off using the normal DROP/CREATE SQL statements and published sqlite APIs to do this sort of thing. Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
