SQLite Forum

Timeline
Login

6 forum posts by user PeterJanRoes

2020-04-08
15:05 Post: Bug in ALTER TABLE ADD COLUMN (artifact: 21c7b443aa user: PeterJanRoes)

When you try to add a new column to an empty table using ALTER TABLE, and this column is defined as NOT NULL, SQLite gives an error: Error: Cannot add a NOT NULL column with default value NULL. I think this is not correct because an empty table does not need a default value for the ADD COLUMN to succeed. Creating a table with a NOT NULL column is possible.

Example creating a table with NOT NULL columns, which succeeds:

CREATE TABLE test (Column1 INT NOT NULL, Column2 INT NOT NULL);

Example creating the same table in two steps, which fails:

CREATE TABLE test (Column1 INT NOT NULL);
ALTER TABLE test ADD COLUMN Column2 INT NOT NULL;

Both operations should be equivalent for empty tables. For non-empty tables the error message is as expected.

2020-04-06
15:58 Edit reply: Rename an INDEX using sqlite_master (artifact: 80c960f59e user: PeterJanRoes)

First, playing with PRAGMA writable_schema=ON is not recommended.

I know you have to be careful with this, but the documentation explicitly states that it can be used as a faster and simpler procedure to perform some ALTER ... statements. Just updating a row in sqlite_master seems much faster and simpler for renaming an index than dropping it and creating it again. I was hoping that would work, and in principle I think it can be made to work.

Second, why are you using a . in your index name? Dot is already used as a separator between DB name and schema name, or schema name and column name, etc... You do properly use double-quotes, but why?

I am sorry, this is left-over from the code that actually generates the statements and is careful to prefix index names with table names to prevent name collisions. I do not think it matters for the example though. I tested now with names without a dot and the behavior is the same.

13:45 Reply: Rename an INDEX using sqlite_master (artifact: 22b1acc9e0 user: PeterJanRoes)

First, playing with PRAGMA writable_schema=ON is not recommended.

I know you have to be careful with this, but the documentation explicitly states that it can be used as a faster and simpler procedure to perform some ALTER ... statements. Just updating a row in sqlite_master seems much faster and simpler for renaming an index than dropping it and creating it again. I was hoping that would work, and in principle I think it can be made to work.

Second, why are you using a . in your index name? Dot is already used as a separator between DB name and schema name, or schema name and column name, etc... You do properly use > double-quotes, but why?

I am sorry, this is left-over from the code that actually generates the statements and is careful to prefix index names with table names to prevent name collisions. I do not think it matters for the example though. I tested now with names without a dot and the behavior is the same.

12:18 Reply: Rename an INDEX using sqlite_master (artifact: fde1f51b09 user: PeterJanRoes)

I think you mean PRAGMA schema_version. I use this in the example and it does trigger sqlite_master to be reread, and this seems to work in cases where only the sql column is updated. It does not seem to work when the name column is updated.

12:16 Reply: Rename an INDEX using sqlite_master (artifact: 5731ed6b88 user: PeterJanRoes)

I tested this, but it does not seem to work.

2020-04-04
10:50 Post: Rename an INDEX using sqlite_master (artifact: ab78b497a0 user: PeterJanRoes)

I am trying to rename an INDEX by updating sqlite_master with the new name and sql. This does not seem to work as I would expect it to. The documentation at https://www.sqlite.org/lang_altertable.html does not explicitly state that this should be possible, but I do think that this type of update meets the criterion as stated in the documentation:

However, a simpler and faster procedure can optionally be used for some changes that do no affect the on-disk content in any way.

I use the following script from sqlite3.exe (test_rename_index.db should not exist):

.open test_rename_index.db

CREATE TABLE test (Column1 INTEGER);
CREATE INDEX "test.Column1" ON test (Column1);

BEGIN TRANSACTION;
PRAGMA schema_version;      -- This should output 2
PRAGMA writable_schema=ON;

UPDATE sqlite_master SET name='test_renamed.Column1', sql='CREATE INDEX "test_renamed.Column1" ON "test" (Column1)' WHERE name='test.Column1';
PRAGMA schema_version=3;    -- Because this is a new database, 3 should be correct

PRAGMA writable_schema=OFF;
COMMIT;

PRAGMA index_list(test);

The last PRAGMA outputs:

0|test.Column1|0|c|0

So the internal data structures have not been updated with the new name. After closing sqlite3.exe and restarting it, execute the following commands:

.open test_rename_index.db
PRAGMA index_list(test);

The last PRAGMA now outputs:

0|test_renamed.Column1|0|c|0

So now the internal data structures have been updated with the new name. So my questions are the following:

  • Are the described steps correctly following the procedure described in the documentation? I think they do.
  • Can I deduce from the documentation that updating the name column of sqlite_master is not supported as the example only updates the sql column?
  • Am I correct in believing that renaming an INDEX in the way I am trying to meets the criterion of not affecting any on-disk content in any way? So reopening a connection to the database does indeed maintain a schema that is not corrupt and perfectly usable?
  • If so, I think that not all internal data structures are refreshed entirely when an update in schema_version is detected. Probably those that are indexed on the name of schema objects. Would it be possible, or even easy, to refresh those structures as well?
  • If so, I would like to request that feature, because it would mean that renaming an index can be done instantaneously as well, instead of dropping and creating it again.