Open PythonCoderAS opened 3 years ago
I fixed this by manually modifying the SQL to:
-- upgrade --
CREATE TABLE IF NOT EXISTS "argument" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(2000) NOT NULL,
"description" TEXT NOT NULL,
"command_id" INT NOT NULL REFERENCES "command" ("id") ON DELETE CASCADE
);;
CREATE TABLE IF NOT EXISTS "command" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(2000) NOT NULL UNIQUE,
"description" TEXT
);
CREATE INDEX IF NOT EXISTS "idx_command_name_a5f5d3" ON "command" ("name");;
CREATE TABLE IF NOT EXISTS "example" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"text" TEXT NOT NULL,
"argument_id" INT NOT NULL REFERENCES "argument" ("id") ON DELETE CASCADE
);;
CREATE INDEX IF NOT EXISTS "idx_option_name_abe08f" ON "option" ("name");
CREATE TABLE IF NOT EXISTS "prefix" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"guild_id" BIGINT NOT NULL,
"value" VARCHAR(20) NOT NULL
);
CREATE INDEX IF NOT EXISTS "idx_prefix_guild_i_22997b" ON "prefix" ("guild_id");
CREATE INDEX IF NOT EXISTS "idx_prefix_value_3efe4f" ON "prefix" ("value");;
-- downgrade --
DROP INDEX IF EXISTS "idx_option_name_abe08f";
DROP TABLE IF EXISTS "argument";
DROP TABLE IF EXISTS "command";
DROP TABLE IF EXISTS "example";
DROP TABLE IF EXISTS "prefix";
However, it should not require intervention.
So which db?
So which db?
This is for an SQLite database.
I can confirm that the created ALTER TABLE
command does not work with SQLite and this it's not possible to create migrations for it.
If I drop a column "old_cfg" and create a new one "config" I get
tortoise.exceptions.OperationalError: near "DROP": syntax error
If I try it again I get
tortoise.exceptions.OperationalError: duplicate column name: config
because the column was created but the migration was not complete.
I can't do init-db
because then it will not create the column on my server and the app will crash.
@long2ice Could you provide me with hints how I can make the migrations happen? Thanks for your help!
@spacemanspiff2007 one option you have is editing the generated SQL migration manually. aerich upgrade
does not do any integrity checks, so it'll read the migration code verbatim. I use this feature to fix any errors so that aerich upgrade
works as expected.
@PythonCoderAS Thank you for your hint! Unfortunately it's not clear to me how I can drop columns manually through the sql statement since everything looks correct. Could you provide me with a hint?
According to sqlite documentation, you would want something like ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>
.
This is what is generated by aerich:
-- upgrade --
ALTER TABLE "mymodel" DROP COLUMN "myfield";
-- downgrade --
ALTER TABLE "mymodel" ADD "myfield" JSON;
The generation is only successful on the second aerich migrate
call.
The first one throws the following error:
table = change[0][1].get("through")
AttributeError: 'str' object has no attribute 'get'
The migration statements actually look allright, but I get the following exception when doing the aerich upgrade
:
raise OperationalError(exc)
tortoise.exceptions.OperationalError: near "DROP": syntax error
I had the same issue and it seems to be a database version problem.
The syntax ALTER TABLE DROP COLUMN
was only introduced in sqlite 3.35.0. I have debian stable installed and it is still sqlite 3.34.1-3 (you can see this with apt show sqlite3
).
You might want to clarify supported versions of sqlite3.
PS: Thanks for the open source work!
When I tried to upgrade the migrations made by
aerich migrate
, I got the errortortoise.exceptions.OperationalError: near "INDEX": syntax error
. I suspect the lineALTER TABLE "option" ADD INDEX "idx_option_name_abe08f" ("name");
is causing it, because my IDE marks it as invalid. Here is the full migration script that was generated: