Hi David,
Thanks for the recovery tip. Fortunately this was simple to recover from
by re-attaching as the "approved" name and then dropping the offending
view using a standard "DROP VIEW a_view;" query.
On 2018-09-25 17:25, David Raymond wrote:
I think I'd be inclined to have it give the error first rather than rename it.
But that's just me.
If you need to recover, you can do it with pragma writable_schema to delete the
line from sqlite_master.
D:\Temp>sqlite3
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach database 'DeleteMe.sqlite' as att;
sqlite> create table att.a_table (id integer primary key, foo text);
sqlite> insert into a_table values (1, 'In attached table');
sqlite> create view att.a_view as select * from att.a_table;
sqlite> select * from a_view;
id|foo
1|In attached table
sqlite> .exit
D:\Temp>sqlite3 DeleteMe.sqlite
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.
sqlite> .tables
Error: malformed database schema (a_view) - view a_view cannot reference
objects in database att
sqlite> select * from sqlite_master;
Error: malformed database schema (a_view) - view a_view cannot reference
objects in database att
sqlite> pragma writable_schema = on;
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|a_table|a_table|2|CREATE TABLE a_table (id integer primary key, foo text)
view|a_view|a_view|0|CREATE VIEW a_view as select * from att.a_table
sqlite> drop view a_view;
Error: no such view: a_view
sqlite> delete from sqlite_master where name = 'a_view';
sqlite> pragma writable_schema = off;
sqlite> .tables
a_table
sqlite>
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jonathan Moules
Sent: Tuesday, September 25, 2018 11:55 AM
To: SQLite mailing list
Subject: [sqlite] Attachments and Views - suggested tweak
Hi List,
I just caught myself in a little circle and accidentally created a
semi-invalid database. I was wondering if it's something SQLite's query
parser could self-resolve / alert about.
Attach a database as "my_attachment" schema name, and then run:
CREATE VIEW my_attachment.a_view AS
SELECT * FROM my_attachment.a_table;
If you now disconnect from the database and then re-attach it, this time
attaching it as "hello_world" schema name, SQLite will fail to attach it
and give this error:
malformed database schema (a_view) - view a_view cannot reference
objects in database my_attachment
I understand that I've been "over-specific" in my SQL, but could not the
query-parser strip off the schema name from the select statement in the
view given it's clearly meant to be self-referential. Or alternatively
give a warning/error at creation time if specifying schema names inside
of Views that are not TEMP given they're unnecessary for such views. The
former would self-solve the problem, the later would provide
transparency and stop you accidentally creating invalid schemas that
seem valid at the time.
Cheers,
Jonathan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
