Attachments and Views - suggested tweak

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Attachments and Views - suggested tweak

Jonathan Moules-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attachments and Views - suggested tweak

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Attachments and Views - suggested tweak

Jonathan Moules-3
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:[hidden email]] 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users