SQLite Forum

Timeline
Login

8 forum posts by user ChirsL

2020-06-16
15:18 Reply: ALTER TABLE new_X RENAME TO X issue. (artifact: 4663edf0ac user: ChirsL)

This is a clear and convincing explanation for legacy_alter_table=on/off to me. 👍

03:21 Reply: ALTER TABLE new_X RENAME TO X issue. (artifact: 58d4d10f72 user: ChirsL)

I think your comments make sense. Maybe I will make my purpose more clearly next time :) I treated the behavior of legacy alter table as deprecated somehow, but base on the fact that SQLite is doing not bad in the backward compatibility area, maybe I can treat the behavior of legacy alter table as a good friend to the modern one in future.

2020-06-15
16:31 Reply: A question about the "type" field in the result of PRAGMA table_xinfo for the generated column. (artifact: e14d67992d user: ChirsL)

Thank you for the reply. I was just not feel intuitive about it at first sight. If it’s a little tricky to fix, I am okay to stay with the behavior.

16:12 Reply: ALTER TABLE new_X RENAME TO X issue. (artifact: 74516c17f2 user: ChirsL)

Thank you for the reply. I am aware the solutions exist. My intention is moving forward and probably help SQLite be a better product by pointing out potential issues or by giving some suggestions.

So that makes PRAGMA legacy_alter_table = TRUE; not an option for my intention, since with time goes on, software’s modern behavior will finally replace the legacy one. I think the intermediate rename one will do the trick, and it’s nice idea to solve the issue, thank you for pointing this out, I didn’t see that before.

So if SQLite now behaves like that intentionally, at least part of the 12 steps for altering table in the document I mentioned before is incorrect. The ‘Drop View’ operation in step 9 should probably move up to before step 7 the ALTER TABLE RENAME TO operation, since in step 7, the error I saw will probably occurs.

13:58 Post: ALTER TABLE new_X RENAME TO X issue. (artifact: 677649be1c user: ChirsL)

Hi, I am following these steps in https://sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

to make some schema changes, but looks like there's some issue with it, I'm wondering if it's a designed feature or if it's a routine that could be improved.

To make the reproduce steps simple, consider a database has this schema, that is two tables "Test", "New_Test", and a view "TestView":

CREATE TABLE Test ( id INTEGER PRIMARY KEY ) ; CREATE TABLE New_Test ( id INTEGER PRIMARY KEY ) ; CREATE VIEW TestView AS SELECT Test.id FROM Test ;

Then if I do

DROP TABLE Test; ALTER TABLE New_Test RENAME TO Test;

I will get an error:

error in view TestView: no such table: main.Test

I mean of course there's no such table "main.Test", because I dropped it, and I want another table to be the "main.Test".

I know that if I drop the view "TestView" before the "ALTER TABLE..." step, then recreate the view after the "ALTER TABLE..." step will work, but it just feel weird.

So like I mentioned before, is this a designed feature or is this a routine that could be improved?

08:26 Post: A question about the "type" field in the result of PRAGMA table_xinfo for the generated column. (artifact: 102fb8fa37 user: ChirsL)

An example for SQLite version 3.32.2:

If a table "Test"'s structure is

CREATE TABLE Test ( a INTEGER, b INTEGER GENERATED ALWAYS AS (a), c INTEGER GENERATED ALWAYS AS (a) NOT NULL, d INTEGER NOT NULL GENERATED ALWAYS AS (a), e INTEGER AS (a) );

The result of PRAGMA table_xinfo(Test); will be:

cid,name,type,notnull,dflt_value,pk,hidden 0,a,INTEGER,0,,0,0 1,b,INTEGER GENERATED ALWAYS,0,,0,2 2,d,INTEGER GENERATED ALWAYS,1,,0,2 3,e,INTEGER,1,,0,2 4,c,INTEGER,0,,0,2

So the question is, since from the document(https://sqlite.org/gencol.html), we know that the "GENERATED ALWAYS" is a column constraint, and it's not a column type, is it proper to appears in the "type" field of the result of PRAGMA table_xinfo?

And if this is a designed behavior, then why do the column "d" and "e" which are also generated column just display a type name "INTEGER" without "GENERATED ALWAYS" appended to the right?

Thank you for seeing my question.

08:21 Edit: (Deleted) (artifact: 34a2b0f758 user: ChirsL)
Deleted
08:19 Post: (Deleted) (artifact: cecce7ae6d user: ChirsL)

An example for SQLite version 3.32.2:

If a table "Test"'s structure is

CREATE TABLE Test ( a INTEGER, b INTEGER GENERATED ALWAYS AS (a), c INTEGER GENERATED ALWAYS AS (a) NOT NULL, d INTEGER NOT NULL GENERATED ALWAYS AS (a), e INTEGER AS (a) );

The result of PRAGMA table_xinfo(Test); will be:

cid name type notnull dflt_value pk hidden
0 a INTEGER 0 0 0
1 b INTEGER GENERATED ALWAYS 0 0 2
2 d INTEGER GENERATED ALWAYS 1 0 2
3 e INTEGER 1 0 2
4 c INTEGER 0 0 2

So the question is, since from the document(https://sqlite.org/gencol.html), we know that the "GENERATED ALWAYS" is a column constraint, and it's not a column type, is it proper to appears in the "type" field of the result of PRAGMA table_xinfo?

And if this is a designed behavior, then why do the column "d" and "e" which are also generated column just display a type name "INTEGER" without "GENERATED ALWAYS" appended to the right?

Thank you for seeing my question.