Home Original page

Cannot modify table if part of a view

Details for the issue

This may be linked to #1650.
Tried to rename fields in a table, but it failed as it tried to do something with the view, and failed.

image

What did you do?

Tried to modify a table. In fact, just opening the 'Edit table definition' window and clicking 'OK' causes the error (which obviously tries to rebuild the table)

What did you expect to see?

No error

What did you see instead?

An error.

Useful extra information

The info below often helps, please fill it out if you're able to. :)

So the table schema is:

CREATE TABLE "categories" (
	"ca_id"	INTEGER,
	"ca_category"	INTEGER,
	"ca_parentCategory"	INTEGER,
	PRIMARY KEY("ca_id")
);

And I have a view:

CREATE VIEW "view_transactions" AS select * from transactions as t
left join accounts as a on tr_accountId=ac_id
left join categories on tr_categoryId=ca_id

I was aiming to remove the 'ca_' prefixes from the fields, but any table modification results in the above error. Deleting the view works as a workaround, but obviously, a long-term solution would be handy. 😉

This was from the SQL log.

PRAGMA foreign_keys
PRAGMA foreign_keys = '0';
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
PRAGMA "main".TABLE_INFO("view_transactions");
SELECT type,name,sql,tbl_name FROM sqlite_temp_master;
CREATE TABLE "sqlb_temp_table_4" (
	"ca_id"	INTEGER,
	"ca_category"	INTEGER,
	"ca_parentCategory"	INTEGER,
	PRIMARY KEY("ca_id")
);
INSERT INTO "main"."sqlb_temp_table_4" ("ca_category","ca_id","ca_parentCategory") SELECT "ca_category","ca_id","ca_parentCategory" FROM "main"."categories";
PRAGMA defer_foreign_keys
PRAGMA defer_foreign_keys = '1';
DROP TABLE "main"."categories";
ALTER TABLE "main"."sqlb_temp_table_4" RENAME TO "categories"
PRAGMA database_list;

Error occurred at the second from last line - at the 'ALTER TABLE' command.

What operating system are you using?

  • Windows: ( version: 10_ )
  • Linux: ( distro: ___ )
  • Mac OS: ( version: ___ )
  • Other: ___

What is your DB4S version?

  • Nightly (24 Dec)
  • 3.11.0-alpha1 or 3.11.0-beta*
  • 3.10.1
  • Other: ___

Did you also