Re: [sqlite] Drop Existing Table Results in 'no such table'

[David Raymond]

Using the writable_schema pragma "only" makes sqlite_master treated like any 
other table for  queries, it doesn't introduce extra logic to check for 
needed re-parsing or other  controlled stuff. So running an UPDATE 
query on sqlite_master becomes just like any  UPDATE on any normal table. 
It's seen as changing some text or numbers etc in some random table, and that's 
it. And since normal everyday  updates are not seen as changing the 
format of the schema, the  doesn't waste resources in re-parsing the 
schema, or in updating the file header with a new "schema cookie" value. Only 
queries that would have the  read or alter sqlite_master  
are the things that would trigger the  to re-parse and update the file 
header. So CREATE, DROP, ALTER etc, or analyze sqlite_master.

(Appologies if I used too many <>'s for  in plain text)


I think that's all correct anyway. Though I have been known to be wrong.

(You know, I used to be indecisive, but now I'm not so sure...)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sanhua.zh
Sent: Friday, October 21, 2016 3:24 AM
To: SQLite mailing list
Subject: Re: [sqlite] Drop Existing Table Results in 'no such table'

Hello Simon,
I try it again and I find that I can update the memory of connection itself by 
changing any other connections' schema_version.
This kind of behavior make me confused. Since it can tell other connections’ 
that the schema is changed, why can’t it tell to itself ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:48
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


You are changing the details saved on disk but not the copy in memory. Since 
reopen a new connection is a ugly way, is there any other way to update the 
memory ? I know that sqlite3InitCallback may update it, but I can’t find a way 
to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing 
listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:39 主题:Re: 
[sqlite] Drop Existing Table Results in 'no such table' On 21 Oct 2016, at 
4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by 
editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME 
TO …’ syntax to do that. I just want to try it in this way. But I find that the 
‘no such table’ error occurs while I try to drop the renamed table. SQLite does 
not look at the sqlite_master table before executing each command. It has a 
copy of the database schema in the memory allocated to the connection. You are 
changing the details saved on disk but not the copy in memory. Only I close the 
connection or start a new connection, the renamed table can be dropped. That 
makes sense. Simon. ___ 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

[David Raymond]

I believe "analyze sqlite_master;" will force a re-parse without requiring a 
new connection. Try it and let us know.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sanhua.zh
Sent: Friday, October 21, 2016 2:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] Drop Existing Table Results in 'no such table'

You are changing the details saved on disk but not the copy in memory.
Since reopen a new connection is a ugly way, is there any other way to update 
the memory ?
I know that sqlite3InitCallback may update it, but I can’t find a way to invoke 
it.




原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:39
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote:  I am trying 
to rename a table by editing the sqlite_master directly. I do know there are a 
‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this 
way.  But I find that the ‘no such table’ error occurs while I try to drop the 
renamed table. SQLite does not look at the sqlite_master table before executing 
each command. It has a copy of the database schema in the memory allocated to 
the connection. You are changing the details saved on disk but not the copy in 
memory.  Only I close the connection or start a new connection, the renamed 
table can be dropped. That makes sense. Simon. 
___ 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

[Jens Alfke]

> On Oct 20, 2016, at 11:40 PM, Clemens Ladisch  wrote:
> 
> sanhua.zh wrote:
>> I am trying to rename a table by editing the sqlite_master directly.
> 
> Don't do this.

Patient: “Doctor, my arm hurts when I do this!”
Doctor: “Then don’t do that.”

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

[sanhua.zh]

Hello Simon,
I try it again and I find that I can update the memory of connection itself by 
changing any other connections' schema_version.
This kind of behavior make me confused. Since it can tell other connections’ 
that the schema is changed, why can’t it tell to itself ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:48
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


You are changing the details saved on disk but not the copy in memory. Since 
reopen a new connection is a ugly way, is there any other way to update the 
memory ? I know that sqlite3InitCallback may update it, but I can’t find a way 
to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing 
listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:39 主题:Re: 
[sqlite] Drop Existing Table Results in 'no such table' On 21 Oct 2016, at 
4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by 
editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME 
TO …’ syntax to do that. I just want to try it in this way. But I find that the 
‘no such table’ error occurs while I try to drop the renamed table. SQLite does 
not look at the sqlite_master table before executing each command. It has a 
copy of the database schema in the memory allocated to the connection. You are 
changing the details saved on disk but not the copy in memory. Only I close the 
connection or start a new connection, the renamed table can be dropped. That 
makes sense. Simon. ___ 
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

Re: [sqlite] Drop Existing Table Results in 'no such table'

[Rowan Worth]

On 21 October 2016 at 14:49, sanhua.zh  wrote:

> Don't do this.
> I don’t think so. It is written on the sqlite.com. So it should be a
> tricky way but not a wrong way.
>

The docs say, regarding the procedure you followed:

> The following simpler procedure is appropriate for removing CHECK
> or FOREIGN KEY or NOT NULL constraints, renaming columns, or
> adding or removing or changing default values on a column.

Note that "renaming tables" is not included here. You should probably use
ALTER TABLE for that :)

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

[sanhua.zh]

Don't do this.
I don’t think so. It is written on the sqlite.com. So it should be a tricky way 
but not a wrong way.
All we need to do is testing enough.




原始邮件
发件人:Clemens ladischclem...@ladisch.de
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:40
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


sanhua.zh wrote:  I am trying to rename a table by editing the sqlite_master 
directly. Don't do this.  But I find that the ‘no such table’ error occurs 
while I try to drop  the renamed table. Only I close the connection or start a 
new  connection, the renamed table can be dropped. Re-opening the correction is 
the only way to make the changes visible. (I did not include the word 
"supported" in the previous sentence because there is nothing supported about 
the writable_schema pragma.) Regards, Clemens 
___ 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

[sanhua.zh]

You are changing the details saved on disk but not the copy in memory.
Since reopen a new connection is a ugly way, is there any other way to update 
the memory ?
I know that sqlite3InitCallback may update it, but I can’t find a way to invoke 
it.




原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:39
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote:  I am trying 
to rename a table by editing the sqlite_master directly. I do know there are a 
‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this 
way.  But I find that the ‘no such table’ error occurs while I try to drop the 
renamed table. SQLite does not look at the sqlite_master table before executing 
each command. It has a copy of the database schema in the memory allocated to 
the connection. You are changing the details saved on disk but not the copy in 
memory.  Only I close the connection or start a new connection, the renamed 
table can be dropped. That makes sense. Simon. 
___ 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

[Clemens Ladisch]

sanhua.zh wrote:
> I am trying to rename a table by editing the sqlite_master directly.

Don't do this.

> But I find that the ‘no such table’ error occurs while I try to drop
> the renamed table. Only I close the connection or start a new
> connection, the renamed table can be dropped.

Re-opening the correction is the only way to make the changes visible.
(I did not include the word "supported" in the previous sentence because
there is nothing supported about the writable_schema pragma.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

[Simon Slavin]

On 21 Oct 2016, at 4:42am, sanhua.zh  wrote:

> I am trying to rename a table by editing the sqlite_master directly. I do 
> know there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want 
> to try it in this way.
> But I find that the ‘no such table’ error occurs while I try to drop the 
> renamed table.

SQLite does not look at the sqlite_master table before executing each command.  
It has a copy of the database schema in the memory allocated to the connection. 
 You are changing the details saved on disk but not the copy in memory.

> Only I close the connection or start a new connection, the renamed table can 
> be dropped.

That makes sense.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Drop Existing Table Results in 'no such table'

[sanhua.zh]

I am trying to rename a table by editing the sqlite_master directly. I do know 
there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try 
it in this way.
But I find that the ‘no such table’ error occurs while I try to drop the 
renamed table.
Only I close the connection or start a new connection, the renamed table can be 
dropped. It seems that the memory inside this connection out of date and 
'PRAGMA schema_version=X+1’ do not update it.
I use theprocedure wrote at the bottom 
ofhttps://www.sqlite.org/lang_altertable.html. It may be a tricky way, but it 
may not be wrong. Does any one know about this?


Here is my test code:

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open test
sqlite CREATE TABLE A (i int);
sqlite select * from sqlite_master;
table|A|A|2|CREATE TABLE A (i int)
sqlite BEGIN
sqlite PRAGMA schema_version;
1
sqlite PRAGMA writable_schema=ON;
sqlite UPDATE sqlite_master set name='B', tbl_name='B', sql='CREATE TABLE B (i 
int)' where name='A';
sqlite PRAGMA schema_version=2;
sqlite PRAGMA writable_schema=OFF;
sqlite COMMIT;
sqlite DROP TABLE B;
Error: no such table: B
sqlite .exit
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users