#697 [SQLite3] unrelated problem might prevents table rename

[Peter Liscovius]

To probably workaround to place a new column at any position in a table while sqlite does not support that out of the box, Adminer copies a table to a new table with prefix 'adminer_' and then drops the original table and rename the new table to the original one.

In some cases that does not work and the temporary table name is kept. (btw. what happens with autoincrement values, foreign key constraints, triggers, ..?)

Here is a case where this could happen:

sqlite> .schema
CREATE TABLE IF NOT EXISTS "adminer_test" (
  "atext" text NULL,
  "anumber" integer NULL
);
CREATE TABLE IF NOT EXISTS "adminer_items" (
  "name" text NULL
);
CREATE VIEW "itemview" AS
select name from items;
sqlite> alter table adminer_test rename to test;
Error: error in view itemview: no such table: main.items
sqlite> 

The view 'itemview' is invalid because table items exists not anymore. That issue is unrelated to an attempt to rename another table.

Adminer does not catch that error and I wonder why there is a BEGIN; COMMIT; if the transaction fails in the middle of work, see screenshot. So transactions are not working? Or ROLLBACK missing?

[Peter Liscovius]

and later

[Jakub Vrána]

Thanks for the detailed repro steps, I can reproduce this. SQLite should error right at CREATE VIEW "itemview" AS select name from items and not later on an unrelated command. Anyway… The root cause is that the rename command fails but SQLite3::query() doesn't report this error (however, if the result is not used then it reports it!). This is probably caused by SQLite3::query() using sqlite3_prepare_v2 and sqlite3_step instead of sqlite3_exec.

I'd probably need to change SQLite3::query() to SQLite3::exec() here.

[Jakub Vrána]

SQLite3::query() doesn't report the error only if it's executed in one command BEGIN; command; COMMIT which is not the case with Adminer. Good.

[Jakub Vrána]

Foreign keys and triggers were already preserved, I've added preserving also auto increment values.