async def init_for(self, module: str, safe=False) -> None:
with patch("aiomysql.connect", new=CoroutineMock()):
await Tortoise.init({
"connections": {
"default": {
"engine": "tortoise.backends.mysql",
"credentials": {
"database": "test",
"host": "127.0.0.1",
"password": "******",
"port": 3306,
"user": "******",
"connect_timeout": 1.5,
"charset": "utf-8",
},
}
},
"apps": {
"models": {
"models": [module],
"default_connection": "default"
}
},
})
self.sqls = get_schema_sql(Tortoise._connections["default"],
safe).split("; ")
async def init_db(args):
await Tortoise.init(config=settings.TORTOISE_ORM)
if args.test:
sql = get_schema_sql(Tortoise.get_connection('default'), safe=False)
print(sql)
else:
await Tortoise.generate_schemas()
async def init_for(self, module: str, safe=False) -> None:
if self.engine != "tortoise.backends.sqlite":
raise test.SkipTest("sqlite only")
with patch(
"tortoise.backends.sqlite.client.SqliteClient.create_connection",
new=CoroutineMock()):
await Tortoise.init({
"connections": {
"default": {
"engine": "tortoise.backends.sqlite",
"credentials": {
"file_path": ":memory:"
},
}
},
"apps": {
"models": {
"models": [module],
"default_connection": "default"
}
},
})
self.sqls = get_schema_sql(Tortoise._connections["default"],
safe).split("; ")
self.post_sqls = generate_post_table_sql(
Tortoise._connections["default"], safe).split("; ")
async def init_db(ctx: Context, safe):
config = ctx.obj["config"]
location = ctx.obj["location"]
app = ctx.obj["app"]
dirname = os.path.join(location, app)
if not os.path.isdir(dirname):
os.mkdir(dirname)
click.secho(f"Success create app migrate location {dirname}",
fg=Color.green)
else:
return click.secho(f"Inited {app} already", fg=Color.yellow)
await Tortoise.init(config=config)
connection = get_app_connection(config, app)
await generate_schema_for_client(connection, safe)
schema = get_schema_sql(connection, safe)
version = await Migrate.generate_version()
await Aerich.create(version=version,
app=app,
content=Migrate.get_models_content(
config, app, location))
with open(os.path.join(dirname, version), "w", encoding="utf-8") as f:
content = {
"upgrade": [schema],
}
json.dump(content, f, ensure_ascii=False, indent=2)
return click.secho(f'Success generate schema for app "{app}"',
fg=Color.green)
async def init_db(
ctx: Context, safe: bool = typer.Option(
True, help="When set to true, creates the table only when it does not already exist.", )
):
"""Generate schema and generate app migrate location."""
config = ctx.obj["config"]
location = ctx.obj["location"]
app = ctx.obj["app"]
dirname = os.path.join(location, app)
if not os.path.isdir(dirname):
os.mkdir(dirname)
typer.secho(f"Success create app migrate location {dirname}", fg=typer.colors.GREEN)
else:
return typer.secho(f"Inited {app} already", fg=typer.colors.YELLOW)
Migrate.write_old_models(config, app, location)
await Tortoise.init(config=config)
connection = get_app_connection(config, app)
await generate_schema_for_client(connection, safe)
schema = get_schema_sql(connection, safe)
version = await Migrate.generate_version()
await Aerich.create(version=version, app=app)
with open(os.path.join(dirname, version), "w", encoding="utf-8") as f:
content = {
"upgrade": [schema],
}
json.dump(content, f, ensure_ascii=False, indent=2)
return typer.secho(f'Success generate schema for app "{app}"', fg=typer.colors.GREEN)
async def init_for(self, module: str, safe=False) -> None:
try:
with patch("asyncpg.create_pool", new=CoroutineMock()):
await Tortoise.init({
"connections": {
"default": {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"database": "test",
"host": "127.0.0.1",
"password": "******",
"port": 3306,
"user": "******",
},
}
},
"apps": {
"models": {
"models": [module],
"default_connection": "default"
}
},
})
self.sqls = get_schema_sql(Tortoise._connections["default"],
safe).split("; ")
except ImportError:
raise test.SkipTest("asyncpg not installed")
async def init_db(ctx: Context, safe):
config = ctx.obj["config"]
location = ctx.obj["location"]
app = ctx.obj["app"]
dirname = Path(location, app)
try:
dirname.mkdir(parents=True)
click.secho(f"Success create app migrate location {dirname}",
fg=Color.green)
except FileExistsError:
return click.secho(
f"Inited {app} already, or delete {dirname} and try again.",
fg=Color.yellow)
await Tortoise.init(config=config)
connection = get_app_connection(config, app)
await generate_schema_for_client(connection, safe)
schema = get_schema_sql(connection, safe)
version = await Migrate.generate_version()
await Aerich.create(
version=version,
app=app,
content=get_models_describe(app),
)
content = {
"upgrade": [schema],
}
write_version_file(Path(dirname, version), content)
click.secho(f'Success generate schema for app "{app}"', fg=Color.green)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS `defaultpk` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`val` INT NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `sometable` (
`sometable_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`some_chars_table` VARCHAR(255) NOT NULL,
`fk_sometable` INT,
CONSTRAINT `fk_sometabl_sometabl_6efae9bd` FOREIGN KEY (`fk_sometable`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
KEY `sometable_some_ch_115115_idx` (`some_chars_table`)
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team` (
`name` VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'The TEAM name (and PK)',
`manager_id` VARCHAR(50),
CONSTRAINT `fk_team_team_9c77cd8f` FOREIGN KEY (`manager_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE IF NOT EXISTS `tournament` (
`tid` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\\'`/* datetime',
KEY `tournament_name_116110_idx` (`name`)
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\\'`/* we have';
CREATE TABLE IF NOT EXISTS `event` (
`id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` TEXT NOT NULL,
`modified` DATETIME(6) NOT NULL,
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`tournament_id` SMALLINT NOT NULL COMMENT 'FK to tournament',
CONSTRAINT `fk_event_tourname_51c2b82d` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`tid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE IF NOT EXISTS `sometable_self` (
`backward_sts` INT NOT NULL,
`sts_forward` INT NOT NULL,
FOREIGN KEY (`backward_sts`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
FOREIGN KEY (`sts_forward`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
FOREIGN KEY (`team_rel_id`) REFERENCES `team` (`name`) ON DELETE CASCADE,
FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `teamevents` (
`event_id` BIGINT NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='How participants relate';
""".strip(), # noqa
)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS "defaultpk" (
"id" SERIAL NOT NULL PRIMARY KEY,
"val" INT NOT NULL
);
CREATE TABLE IF NOT EXISTS "sometable" (
"sometable_id" SERIAL NOT NULL PRIMARY KEY,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" (sometable_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "sometable_some_ch_115115_idx" ON "sometable" (some_chars_table);
CREATE TABLE IF NOT EXISTS "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY,
"manager_id" VARCHAR(50) REFERENCES "team" (name) ON DELETE CASCADE
);
COMMENT ON COLUMN team.name IS 'The TEAM name (and PK)';
COMMENT ON TABLE team IS 'The TEAMS!';
CREATE TABLE IF NOT EXISTS "tournament" (
"tid" SMALLSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"created" TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS "tournament_name_116110_idx" ON "tournament" (name);
COMMENT ON COLUMN tournament.name IS 'Tournament name';
COMMENT ON COLUMN tournament.created IS 'Created */''`/* datetime';
COMMENT ON TABLE tournament IS 'What Tournaments */''`/* we have';
CREATE TABLE IF NOT EXISTS "event" (
"id" BIGSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL UNIQUE,
"modified" TIMESTAMP NOT NULL,
"prize" DECIMAL(10,2),
"token" VARCHAR(100) NOT NULL UNIQUE,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" (tid) ON DELETE CASCADE
);
COMMENT ON COLUMN event.id IS 'Event ID';
COMMENT ON COLUMN event.token IS 'Unique token';
COMMENT ON COLUMN event.tournament_id IS 'FK to tournament';
COMMENT ON TABLE event IS 'This table contains a list of all the events';
CREATE TABLE IF NOT EXISTS "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" (sometable_id) ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" (sometable_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "teamevents" (
"event_id" BIGINT NOT NULL REFERENCES "event" (id) ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE
);
COMMENT ON TABLE teamevents IS 'How participants relate';
""".strip(),
)
async def run():
print("SQLite:\n")
await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
print(sql)
print("\n\nMySQL:\n")
await Tortoise.init(db_url="mysql://root:@127.0.0.1:3306/", modules={"models": ["__main__"]})
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
print(sql)
print("\n\nPostgreSQL:\n")
await Tortoise.init(
db_url="postgres://postgres:@127.0.0.1:5432/", modules={"models": ["__main__"]}
)
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
print(sql)
async def run():
# print("SQLite:\n")
# await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
# sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
# print(sql)
print("\n\nMySQL:\n")
await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]})
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
print(sql)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS "defaultpk" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"val" INT NOT NULL
);
CREATE TABLE IF NOT EXISTS "sometable" (
"sometable_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "idx_sometable_some_ch_3d69eb" ON "sometable" ("some_chars_table");
CREATE TABLE IF NOT EXISTS "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY /* The TEAM name (and PK) */,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
) /* The TEAMS! */;
CREATE INDEX IF NOT EXISTS "idx_team_manager_4afe4a" ON "team" ("manager", "key");
CREATE TABLE IF NOT EXISTS "tournament" (
"tid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(100) NOT NULL /* Tournament name */,
"created" TIMESTAMP NOT NULL /* Created *\\/'`\\/* datetime */
) /* What Tournaments *\\/'`\\/* we have */;
CREATE INDEX IF NOT EXISTS "idx_tournament_name_6fe200" ON "tournament" ("name");
CREATE TABLE IF NOT EXISTS "event" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL /* Event ID */,
"name" TEXT NOT NULL,
"modified" TIMESTAMP NOT NULL,
"prize" VARCHAR(40),
"token" VARCHAR(100) NOT NULL UNIQUE /* Unique token */,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE /* FK to tournament */,
UNIQUE ("name", "prize"),
UNIQUE ("tournament_id", "key")
) /* This table contains a list of all the events */;
CREATE TABLE IF NOT EXISTS "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "teamevents" (
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
) /* How participants relate */;
""".strip(),
)
async def test_m2m_no_auto_create(self):
self.maxDiff = None
await self.init_for("tests.schema.models_no_auto_create_m2m")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
r"""CREATE TABLE "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE INDEX "idx_team_manager_676134" ON "team" ("manager_id", "key");
CREATE INDEX "idx_team_manager_ef8f69" ON "team" ("manager_id", "name");
COMMENT ON COLUMN "team"."name" IS 'The TEAM name (and PK)';
COMMENT ON TABLE "team" IS 'The TEAMS!';
CREATE TABLE "tournament" (
"tid" SMALLSERIAL NOT NULL PRIMARY KEY,
"name" VARCHAR(100) NOT NULL,
"created" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX "idx_tournament_name_6fe200" ON "tournament" ("name");
COMMENT ON COLUMN "tournament"."name" IS 'Tournament name';
COMMENT ON COLUMN "tournament"."created" IS 'Created */''`/* datetime';
COMMENT ON TABLE "tournament" IS 'What Tournaments */''`/* we have';
CREATE TABLE "event" (
"id" BIGSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"modified" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"prize" DECIMAL(10,2),
"token" VARCHAR(100) NOT NULL UNIQUE,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE,
CONSTRAINT "uid_event_name_c6f89f" UNIQUE ("name", "prize"),
CONSTRAINT "uid_event_tournam_a5b730" UNIQUE ("tournament_id", "key")
);
COMMENT ON COLUMN "event"."id" IS 'Event ID';
COMMENT ON COLUMN "event"."token" IS 'Unique token';
COMMENT ON COLUMN "event"."tournament_id" IS 'FK to tournament';
COMMENT ON TABLE "event" IS 'This table contains a list of all the events';
CREATE TABLE "teamevents" (
"id" SERIAL NOT NULL PRIMARY KEY,
"score" INT NOT NULL,
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
CONSTRAINT "uid_teamevents_team_id_9e89fc" UNIQUE ("team_id", "event_id")
);
COMMENT ON TABLE "teamevents" IS 'How participants relate';
CREATE TABLE "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);""".strip(),
)
async def test_index(self):
await self.init_for("tests.schema.models_mysql_index")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql,
"""CREATE TABLE IF NOT EXISTS `index` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`full_text` LONGTEXT NOT NULL,
`geometry` GEOMETRY NOT NULL
) CHARACTER SET utf8mb4;
CREATE FULLTEXT INDEX `idx_index_full_te_3caba4` ON `index` (`full_text`) WITH PARSER ngram;
CREATE SPATIAL INDEX `idx_index_geometr_0b4dfb` ON `index` (`geometry`);""",
)
async def test_m2m_no_auto_create(self):
self.maxDiff = None
await self.init_for("tests.schema.models_no_auto_create_m2m")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
r"""CREATE TABLE `team` (
`name` VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'The TEAM name (and PK)',
`key` INT NOT NULL,
`manager_id` VARCHAR(50),
CONSTRAINT `fk_team_team_9c77cd8f` FOREIGN KEY (`manager_id`) REFERENCES `team` (`name`) ON DELETE CASCADE,
KEY `idx_team_manager_676134` (`manager_id`, `key`),
KEY `idx_team_manager_ef8f69` (`manager_id`, `name`)
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE `tournament` (
`tid` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\'`/* datetime' DEFAULT CURRENT_TIMESTAMP(6),
KEY `idx_tournament_name_6fe200` (`name`)
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\'`/* we have';
CREATE TABLE `event` (
`id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` LONGTEXT NOT NULL,
`modified` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`key` VARCHAR(100) NOT NULL,
`tournament_id` SMALLINT NOT NULL COMMENT 'FK to tournament',
UNIQUE KEY `uid_event_name_c6f89f` (`name`, `prize`),
UNIQUE KEY `uid_event_tournam_a5b730` (`tournament_id`, `key`),
CONSTRAINT `fk_event_tourname_51c2b82d` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`tid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE `teamevents` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`score` INT NOT NULL,
`event_id` BIGINT NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
UNIQUE KEY `uid_teamevents_team_id_9e89fc` (`team_id`, `event_id`),
CONSTRAINT `fk_teameven_event_9d3bac2d` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_teameven_team_dc3bc201` FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='How participants relate';
CREATE TABLE `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
FOREIGN KEY (`team_rel_id`) REFERENCES `team` (`name`) ON DELETE CASCADE,
FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;""".strip(),
)
async def makemigrations(args):
"""
1.get old_schema.sql
2.dump current sql
3.schemalex new_schema.sql old_schema.sql
:param args:
:return:
"""
name = args.name
await Tortoise.init(config=settings.TORTOISE_ORM)
# 导出当前model sql
new_sql = get_schema_sql(Tortoise.get_connection('default'), safe=False)
new_sql = new_sql.replace("DEFAULT CURRENT_TIMESTAMP(6)", "").replace("ON UPDATE CURRENT_TIMESTAMP(6)", "")
new_sql = new_sql.replace(") CHARACTER SET utf8mb4",
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ")
with open(NEW_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
if not os.path.exists(OLD_SCHEMA_FILE):
with open(OLD_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
# 生成升级sql和降级sql
up_sql = os.popen(f'/usr/local/bin/schemalex {OLD_SCHEMA_FILE} {NEW_SCHEMA_FILE}').read()
down_sql = os.popen(f'/usr/local/bin/schemalex {NEW_SCHEMA_FILE} {OLD_SCHEMA_FILE}').read()
# print("up_sql:", up_sql)
# print("down_sql:", down_sql)
# print("-------------------------------------------------")
if up_sql == down_sql:
os.unlink(NEW_SCHEMA_FILE)
print(Fore.BLUE + 'No changes detected')
return
# 升级sql和降级sql写入dbmate格式
if not os.path.exists(MIGRATIONS_DIR):
os.mkdir(MIGRATIONS_DIR)
up_sql_file = os.path.join(MIGRATIONS_DIR, f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}_{name}.sql')
with open(up_sql_file, 'w') as f:
migrate_sql = f"""-- migrate:up\n{up_sql}\n\n-- migrate:down\n{down_sql}"""
f.write(migrate_sql)
with open(OLD_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
os.unlink(NEW_SCHEMA_FILE)
print(Fore.GREEN + f'Generate sql file {up_sql_file}.')
async def _initialize_database(self, reindex: bool = False) -> None:
self._logger.info('Initializing database')
if isinstance(
self._config.database,
PostgresDatabaseConfig) and self._config.database.schema_name:
await Tortoise._connections['default'].execute_script(
f"CREATE SCHEMA IF NOT EXISTS {self._config.database.schema_name}"
)
await Tortoise._connections['default'].execute_script(
f"SET search_path TO {self._config.database.schema_name}")
connection_name, connection = next(iter(Tortoise._connections.items()))
schema_sql = get_schema_sql(connection, False)
# NOTE: Column order could differ in two generated schemas for the same models, drop commas and sort strings to eliminate this
processed_schema_sql = '\n'.join(
sorted(schema_sql.replace(',', '').split('\n'))).encode()
schema_hash = hashlib.sha256(processed_schema_sql).hexdigest()
# TODO: Move higher
if reindex:
self._logger.warning(
'Started with `--reindex` argument, reindexing')
await self._ctx.reindex()
try:
schema_state = await State.get_or_none(index_type=IndexType.schema,
index_name=connection_name)
except OperationalError:
schema_state = None
if schema_state is None:
await Tortoise.generate_schemas()
await self._execute_sql_scripts(reindex=True)
schema_state = State(index_type=IndexType.schema,
index_name=connection_name,
hash=schema_hash)
await schema_state.save()
elif schema_state.hash != schema_hash:
self._logger.warning('Schema hash mismatch, reindexing')
await self._ctx.reindex()
await self._execute_sql_scripts(reindex=False)
async def test_schema(self):
self.maxDiff = None
await self.init_for("tortoise.tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE `sometable` (
`sometable_id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`some_chars_table` VARCHAR(255) NOT NULL,
`fk_sometable` INT REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE INDEX `sometable_some_ch_115115_idx` ON `sometable` (some_chars_table);
CREATE TABLE `team` (
`name` VARCHAR(50) NOT NULL COMMENT 'The TEAM name (and PK)',
`manager_id` VARCHAR(50) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE `tournament` (
`tid` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` TEXT NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\\'`/* datetime'
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\\'`/* we have';
CREATE INDEX `tournament_name_116110_idx` ON `tournament` (name);
CREATE TABLE `event` (
`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` TEXT NOT NULL UNIQUE,
`modified` DATETIME(6) NOT NULL,
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`tournament_id` INT NOT NULL COMMENT 'FK to tournament' REFERENCES `tournament` (`tid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE `sometable_self` (
`backward_sts` INT NOT NULL REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
`sts_forward` INT NOT NULL REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE,
`team_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE `teamevents` (
`event_id` INT NOT NULL REFERENCES `event` (`id`) ON DELETE CASCADE,
`team_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='How participants relate';
""".strip(), # noqa
)
async def test_schema(self):
self.maxDiff = None
await self.init_for("tortoise.tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE "sometable" (
"sometable_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" (sometable_id) ON DELETE CASCADE
);
CREATE INDEX "sometable_some_ch_115115_idx" ON "sometable" (some_chars_table);
CREATE TABLE "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY /* The TEAM name (and PK) */,
"manager_id" VARCHAR(50) REFERENCES "team" (name) ON DELETE CASCADE
) /* The TEAMS! */;
CREATE TABLE "tournament" (
"tid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" TEXT NOT NULL /* Tournament name */,
"created" TIMESTAMP NOT NULL /* Created *\\/'`\\/* datetime */
) /* What Tournaments *\\/'`\\/* we have */;
CREATE INDEX "tournament_name_116110_idx" ON "tournament" (name);
CREATE TABLE "event" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL /* Event ID */,
"name" TEXT NOT NULL UNIQUE,
"modified" TIMESTAMP NOT NULL,
"prize" VARCHAR(40),
"token" VARCHAR(100) NOT NULL UNIQUE /* Unique token */,
"tournament_id" INT NOT NULL REFERENCES "tournament" (tid) ON DELETE CASCADE /* FK to tournament */
) /* This table contains a list of all the events */;
CREATE TABLE "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" (sometable_id) ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" (sometable_id) ON DELETE CASCADE
);
CREATE TABLE "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE
);
CREATE TABLE "teamevents" (
"event_id" INT NOT NULL REFERENCES "event" (id) ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" (name) ON DELETE CASCADE
) /* How participants relate */;
""".strip(), # noqa
)
async def makemigrations(args):
"""
1.get old_schema.sql
2.dump current sql
3.schemalex old_schema.sql new_schema.sql
:param args:
:return:
"""
name = args.name
await Tortoise.init(config=settings.TORTOISE_ORM)
new_sql = get_schema_sql(Tortoise.get_connection('default'), safe=False)
with open(NEW_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
with open(NEW_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
if not os.path.exists(OLD_SCHEMA_FILE):
with open(OLD_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
up_sql = os.popen(f'schemalex {OLD_SCHEMA_FILE} {NEW_SCHEMA_FILE}').read()
down_sql = os.popen(
f'schemalex {NEW_SCHEMA_FILE} {OLD_SCHEMA_FILE}').read()
if up_sql == down_sql:
os.unlink(NEW_SCHEMA_FILE)
print(Fore.BLUE + 'No changes detected')
return
if not os.path.exists(MIGRATIONS_DIR):
os.mkdir(MIGRATIONS_DIR)
up_sql_file = os.path.join(
MIGRATIONS_DIR,
f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}_{name}.sql')
with open(up_sql_file, 'w') as f:
migrate_sql = f"""-- migrate:up\n{up_sql}\n\n-- migrate:down\n{down_sql}"""
f.write(migrate_sql)
with open(OLD_SCHEMA_FILE, 'w') as f:
f.write(new_sql)
os.unlink(NEW_SCHEMA_FILE)
print(Fore.GREEN + f'Generate sql file {up_sql_file}.')
async def test_m2m_no_auto_create(self):
self.maxDiff = None
await self.init_for("tests.schema.models_no_auto_create_m2m")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
r"""CREATE TABLE "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY /* The TEAM name (and PK) */,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
) /* The TEAMS! */;
CREATE INDEX "idx_team_manager_676134" ON "team" ("manager_id", "key");
CREATE INDEX "idx_team_manager_ef8f69" ON "team" ("manager_id", "name");
CREATE TABLE "tournament" (
"tid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(100) NOT NULL /* Tournament name */,
"created" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP /* Created *\/'`\/* datetime */
) /* What Tournaments *\/'`\/* we have */;
CREATE INDEX "idx_tournament_name_6fe200" ON "tournament" ("name");
CREATE TABLE "event" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL /* Event ID */,
"name" TEXT NOT NULL,
"modified" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"prize" VARCHAR(40),
"token" VARCHAR(100) NOT NULL UNIQUE /* Unique token */,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE /* FK to tournament */,
CONSTRAINT "uid_event_name_c6f89f" UNIQUE ("name", "prize"),
CONSTRAINT "uid_event_tournam_a5b730" UNIQUE ("tournament_id", "key")
) /* This table contains a list of all the events */;
CREATE TABLE "teamevents" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"score" INT NOT NULL,
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
CONSTRAINT "uid_teamevents_team_id_9e89fc" UNIQUE ("team_id", "event_id")
) /* How participants relate */;
CREATE TABLE "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);""".strip(),
)
async def init_for(self, module: str) -> None:
await Tortoise.init({
"connections": {
"default": {
"engine": "tortoise.backends.sqlite",
"credentials": {
"file_path": ":memory:",
}
}
},
"apps": {
"models": {
"models": [
module,
],
"default_connection": "default"
}
}
})
self.sqls = get_schema_sql(Tortoise._connections['default']).split('; ')
async def test_index(self):
await self.init_for("tests.schema.models_postgres_index")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql,
"""CREATE TABLE IF NOT EXISTS "index" (
"id" SERIAL NOT NULL PRIMARY KEY,
"bloom" VARCHAR(200) NOT NULL,
"brin" VARCHAR(200) NOT NULL,
"gin" TSVECTOR NOT NULL,
"gist" TSVECTOR NOT NULL,
"sp_gist" VARCHAR(200) NOT NULL,
"hash" VARCHAR(200) NOT NULL
);
CREATE INDEX "idx_index_bloom_280137" ON "index" USING BLOOM ("bloom");
CREATE INDEX "idx_index_brin_a54a00" ON "index" USING BRIN ("brin");
CREATE INDEX "idx_index_gin_a403ee" ON "index" USING GIN ("gin");
CREATE INDEX "idx_index_gist_c807bf" ON "index" USING GIST ("gist");
CREATE INDEX "idx_index_sp_gist_2c0bad" ON "index" USING SPGIST ("sp_gist");
CREATE INDEX "idx_index_hash_cfe6b5" ON "index" USING HASH ("hash");""",
)
async def init_db(self, safe: bool):
location = self.location
app = self.app
dirname = Path(location, app)
dirname.mkdir(parents=True)
await Tortoise.init(config=self.tortoise_config)
connection = get_app_connection(self.tortoise_config, app)
await generate_schema_for_client(connection, safe)
schema = get_schema_sql(connection, safe)
version = await Migrate.generate_version()
await Aerich.create(
version=version,
app=app,
content=get_models_describe(app),
)
content = {
"upgrade": [schema],
}
write_version_file(Path(dirname, version), content)
async def test_schema_no_db_constraint(self):
self.maxDiff = None
await self.init_for("tests.schema.models_no_db_constraint")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
r"""CREATE TABLE `team` (
`name` VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'The TEAM name (and PK)',
`key` INT NOT NULL,
`manager_id` VARCHAR(50),
KEY `idx_team_manager_676134` (`manager_id`, `key`),
KEY `idx_team_manager_ef8f69` (`manager_id`, `name`)
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE `tournament` (
`tid` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\'`/* datetime' DEFAULT CURRENT_TIMESTAMP(6),
KEY `idx_tournament_name_6fe200` (`name`)
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\'`/* we have';
CREATE TABLE `event` (
`id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` LONGTEXT NOT NULL,
`modified` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`key` VARCHAR(100) NOT NULL,
`tournament_id` SMALLINT NOT NULL,
UNIQUE KEY `uid_event_name_c6f89f` (`name`, `prize`),
UNIQUE KEY `uid_event_tournam_a5b730` (`tournament_id`, `key`)
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL,
`team_id` VARCHAR(50) NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE `teamevents` (
`event_id` BIGINT NOT NULL,
`team_id` VARCHAR(50) NOT NULL
) CHARACTER SET utf8mb4 COMMENT='How participants relate';""",
)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.schema.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS "company" (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"uuid" UUID NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "defaultpk" (
"id" SERIAL NOT NULL PRIMARY KEY,
"val" INT NOT NULL
);
CREATE TABLE IF NOT EXISTS "employee" (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"company_id" UUID NOT NULL REFERENCES "company" ("uuid") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "inheritedmodel" (
"id" SERIAL NOT NULL PRIMARY KEY,
"zero" INT NOT NULL,
"one" VARCHAR(40),
"new_field" VARCHAR(100) NOT NULL,
"two" VARCHAR(40) NOT NULL,
"name" TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "sometable" (
"sometable_id" SERIAL NOT NULL PRIMARY KEY,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "idx_sometable_some_ch_3d69eb" ON "sometable" ("some_chars_table");
CREATE TABLE IF NOT EXISTS "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "idx_team_manager_676134" ON "team" ("manager_id", "key");
CREATE INDEX IF NOT EXISTS "idx_team_manager_ef8f69" ON "team" ("manager_id", "name");
COMMENT ON COLUMN "team"."name" IS 'The TEAM name (and PK)';
COMMENT ON TABLE "team" IS 'The TEAMS!';
CREATE TABLE IF NOT EXISTS "teamaddress" (
"city" VARCHAR(50) NOT NULL,
"country" VARCHAR(50) NOT NULL,
"street" VARCHAR(128) NOT NULL,
"team_id" VARCHAR(50) NOT NULL PRIMARY KEY REFERENCES "team" ("name") ON DELETE CASCADE
);
COMMENT ON COLUMN "teamaddress"."city" IS 'City';
COMMENT ON COLUMN "teamaddress"."country" IS 'Country';
COMMENT ON COLUMN "teamaddress"."street" IS 'Street Address';
COMMENT ON TABLE "teamaddress" IS 'The Team''s address';
CREATE TABLE IF NOT EXISTS "tournament" (
"tid" SMALLSERIAL NOT NULL PRIMARY KEY,
"name" VARCHAR(100) NOT NULL,
"created" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS "idx_tournament_name_6fe200" ON "tournament" ("name");
COMMENT ON COLUMN "tournament"."name" IS 'Tournament name';
COMMENT ON COLUMN "tournament"."created" IS 'Created */''`/* datetime';
COMMENT ON TABLE "tournament" IS 'What Tournaments */''`/* we have';
CREATE TABLE IF NOT EXISTS "event" (
"id" BIGSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"modified" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"prize" DECIMAL(10,2),
"token" VARCHAR(100) NOT NULL UNIQUE,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE,
CONSTRAINT "uid_event_name_c6f89f" UNIQUE ("name", "prize"),
CONSTRAINT "uid_event_tournam_a5b730" UNIQUE ("tournament_id", "key")
);
COMMENT ON COLUMN "event"."id" IS 'Event ID';
COMMENT ON COLUMN "event"."token" IS 'Unique token';
COMMENT ON COLUMN "event"."tournament_id" IS 'FK to tournament';
COMMENT ON TABLE "event" IS 'This table contains a list of all the events';
CREATE TABLE IF NOT EXISTS "venueinformation" (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" VARCHAR(128) NOT NULL,
"capacity" INT NOT NULL,
"rent" DOUBLE PRECISION NOT NULL,
"team_id" VARCHAR(50) UNIQUE REFERENCES "team" ("name") ON DELETE SET NULL
);
COMMENT ON COLUMN "venueinformation"."capacity" IS 'No. of seats';
CREATE TABLE IF NOT EXISTS "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "teamevents" (
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE SET NULL,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE SET NULL
);
COMMENT ON TABLE "teamevents" IS 'How participants relate';
""".strip(),
)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.schema.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS `company` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` LONGTEXT NOT NULL,
`uuid` CHAR(36) NOT NULL UNIQUE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `defaultpk` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`val` INT NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `employee` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` LONGTEXT NOT NULL,
`company_id` CHAR(36) NOT NULL,
CONSTRAINT `fk_employee_company_08999a42` FOREIGN KEY (`company_id`) REFERENCES `company` (`uuid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `inheritedmodel` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`zero` INT NOT NULL,
`one` VARCHAR(40),
`new_field` VARCHAR(100) NOT NULL,
`two` VARCHAR(40) NOT NULL,
`name` LONGTEXT NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `sometable` (
`sometable_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`some_chars_table` VARCHAR(255) NOT NULL,
`fk_sometable` INT,
CONSTRAINT `fk_sometabl_sometabl_6efae9bd` FOREIGN KEY (`fk_sometable`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
KEY `idx_sometable_some_ch_3d69eb` (`some_chars_table`)
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team` (
`name` VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'The TEAM name (and PK)',
`key` INT NOT NULL,
`manager_id` VARCHAR(50),
CONSTRAINT `fk_team_team_9c77cd8f` FOREIGN KEY (`manager_id`) REFERENCES `team` (`name`) ON DELETE CASCADE,
KEY `idx_team_manager_676134` (`manager_id`, `key`),
KEY `idx_team_manager_ef8f69` (`manager_id`, `name`)
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE IF NOT EXISTS `teamaddress` (
`city` VARCHAR(50) NOT NULL COMMENT 'City',
`country` VARCHAR(50) NOT NULL COMMENT 'Country',
`street` VARCHAR(128) NOT NULL COMMENT 'Street Address',
`team_id` VARCHAR(50) NOT NULL PRIMARY KEY,
CONSTRAINT `fk_teamaddr_team_1c78d737` FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='The Team\\'s address';
CREATE TABLE IF NOT EXISTS `tournament` (
`tid` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\\'`/* datetime' DEFAULT CURRENT_TIMESTAMP(6),
KEY `idx_tournament_name_6fe200` (`name`)
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\\'`/* we have';
CREATE TABLE IF NOT EXISTS `event` (
`id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` LONGTEXT NOT NULL,
`modified` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`key` VARCHAR(100) NOT NULL,
`tournament_id` SMALLINT NOT NULL COMMENT 'FK to tournament',
UNIQUE KEY `uid_event_name_c6f89f` (`name`, `prize`),
UNIQUE KEY `uid_event_tournam_a5b730` (`tournament_id`, `key`),
CONSTRAINT `fk_event_tourname_51c2b82d` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`tid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE IF NOT EXISTS `venueinformation` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(128) NOT NULL,
`capacity` INT NOT NULL COMMENT 'No. of seats',
`rent` DOUBLE NOT NULL,
`team_id` VARCHAR(50) UNIQUE,
CONSTRAINT `fk_venueinf_team_198af929` FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE SET NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `sometable_self` (
`backward_sts` INT NOT NULL,
`sts_forward` INT NOT NULL,
FOREIGN KEY (`backward_sts`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
FOREIGN KEY (`sts_forward`) REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
FOREIGN KEY (`team_rel_id`) REFERENCES `team` (`name`) ON DELETE CASCADE,
FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `teamevents` (
`event_id` BIGINT NOT NULL,
`team_id` VARCHAR(50) NOT NULL,
FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE SET NULL,
FOREIGN KEY (`team_id`) REFERENCES `team` (`name`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='How participants relate';
""".strip(),
)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.schema.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=True)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS "company" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" TEXT NOT NULL,
"uuid" CHAR(36) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "defaultpk" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"val" INT NOT NULL
);
CREATE TABLE IF NOT EXISTS "employee" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" TEXT NOT NULL,
"company_id" CHAR(36) NOT NULL REFERENCES "company" ("uuid") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "inheritedmodel" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"zero" INT NOT NULL,
"one" VARCHAR(40),
"new_field" VARCHAR(100) NOT NULL,
"two" VARCHAR(40) NOT NULL,
"name" TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "sometable" (
"sometable_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "idx_sometable_some_ch_3d69eb" ON "sometable" ("some_chars_table");
CREATE TABLE IF NOT EXISTS "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY /* The TEAM name (and PK) */,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
) /* The TEAMS! */;
CREATE INDEX IF NOT EXISTS "idx_team_manager_676134" ON "team" ("manager_id", "key");
CREATE INDEX IF NOT EXISTS "idx_team_manager_ef8f69" ON "team" ("manager_id", "name");
CREATE TABLE IF NOT EXISTS "teamaddress" (
"city" VARCHAR(50) NOT NULL /* City */,
"country" VARCHAR(50) NOT NULL /* Country */,
"street" VARCHAR(128) NOT NULL /* Street Address */,
"team_id" VARCHAR(50) NOT NULL PRIMARY KEY REFERENCES "team" ("name") ON DELETE CASCADE
) /* The Team's address */;
CREATE TABLE IF NOT EXISTS "tournament" (
"tid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(100) NOT NULL /* Tournament name */,
"created" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP /* Created *\\/'`\\/* datetime */
) /* What Tournaments *\\/'`\\/* we have */;
CREATE INDEX IF NOT EXISTS "idx_tournament_name_6fe200" ON "tournament" ("name");
CREATE TABLE IF NOT EXISTS "event" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL /* Event ID */,
"name" TEXT NOT NULL,
"modified" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"prize" VARCHAR(40),
"token" VARCHAR(100) NOT NULL UNIQUE /* Unique token */,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE /* FK to tournament */,
CONSTRAINT "uid_event_name_c6f89f" UNIQUE ("name", "prize"),
CONSTRAINT "uid_event_tournam_a5b730" UNIQUE ("tournament_id", "key")
) /* This table contains a list of all the events */;
CREATE TABLE IF NOT EXISTS "venueinformation" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" VARCHAR(128) NOT NULL,
"capacity" INT NOT NULL /* No. of seats */,
"rent" REAL NOT NULL,
"team_id" VARCHAR(50) UNIQUE REFERENCES "team" ("name") ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "teamevents" (
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE SET NULL,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE SET NULL
) /* How participants relate */;
""".strip(),
)
async def test_schema(self):
self.maxDiff = None
await self.init_for("tests.models_schema_create")
sql = get_schema_sql(Tortoise.get_connection("default"), safe=False)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE "defaultpk" (
"id" SERIAL NOT NULL PRIMARY KEY,
"val" INT NOT NULL
);
CREATE TABLE "sometable" (
"sometable_id" SERIAL NOT NULL PRIMARY KEY,
"some_chars_table" VARCHAR(255) NOT NULL,
"fk_sometable" INT REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE INDEX "idx_sometable_some_ch_3d69eb" ON "sometable" ("some_chars_table");
CREATE TABLE "team" (
"name" VARCHAR(50) NOT NULL PRIMARY KEY,
"key" INT NOT NULL,
"manager_id" VARCHAR(50) REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE INDEX "idx_team_manager_4afe4a" ON "team" ("manager", "key");
COMMENT ON COLUMN "team"."name" IS 'The TEAM name (and PK)';
COMMENT ON TABLE "team" IS 'The TEAMS!';
CREATE TABLE "tournament" (
"tid" SMALLSERIAL NOT NULL PRIMARY KEY,
"name" VARCHAR(100) NOT NULL,
"created" TIMESTAMP NOT NULL
);
CREATE INDEX "idx_tournament_name_6fe200" ON "tournament" ("name");
COMMENT ON COLUMN "tournament"."name" IS 'Tournament name';
COMMENT ON COLUMN "tournament"."created" IS 'Created */''`/* datetime';
COMMENT ON TABLE "tournament" IS 'What Tournaments */''`/* we have';
CREATE TABLE "event" (
"id" BIGSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"modified" TIMESTAMP NOT NULL,
"prize" DECIMAL(10,2),
"token" VARCHAR(100) NOT NULL UNIQUE,
"key" VARCHAR(100) NOT NULL,
"tournament_id" SMALLINT NOT NULL REFERENCES "tournament" ("tid") ON DELETE CASCADE,
UNIQUE ("name", "prize"),
UNIQUE ("tournament_id", "key")
);
COMMENT ON COLUMN "event"."id" IS 'Event ID';
COMMENT ON COLUMN "event"."token" IS 'Unique token';
COMMENT ON COLUMN "event"."tournament_id" IS 'FK to tournament';
COMMENT ON TABLE "event" IS 'This table contains a list of all the events';
CREATE TABLE "sometable_self" (
"backward_sts" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE,
"sts_forward" INT NOT NULL REFERENCES "sometable" ("sometable_id") ON DELETE CASCADE
);
CREATE TABLE "team_team" (
"team_rel_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);
CREATE TABLE "teamevents" (
"event_id" BIGINT NOT NULL REFERENCES "event" ("id") ON DELETE CASCADE,
"team_id" VARCHAR(50) NOT NULL REFERENCES "team" ("name") ON DELETE CASCADE
);
COMMENT ON TABLE "teamevents" IS 'How participants relate';
""".strip(),
)
async def test_schema_safe(self):
self.maxDiff = None
await self.init_for("tests.models_schema_create")
with warnings.catch_warnings(record=True) as w:
with self.assertLogs("tortoise", level="WARNING") as cm:
sql = get_schema_sql(Tortoise.get_connection("default"),
safe=True)
self.assertEqual(
cm.output,
[
"WARNING:tortoise:CREATE INDEX `sometable_some_ch_115115_idx` ON `sometable`"
" (some_chars_table);",
"WARNING:tortoise:CREATE INDEX `tournament_name_116110_idx` ON `tournament`"
" (name);",
],
)
self.assertEqual(len(w), 1)
self.assertEqual(w[0].category, UserWarning)
self.assertEqual(
str(w[0].message),
"Skipping creation of field indexes: safe index creation is not supported yet for"
" mysql. Please find the SQL queries to create the indexes in the logs.",
)
self.assertEqual(
sql.strip(),
"""
CREATE TABLE IF NOT EXISTS `defaultpk` (
`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`val` INT NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `sometable` (
`sometable_id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`some_chars_table` VARCHAR(255) NOT NULL,
`fk_sometable` INT REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team` (
`name` VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'The TEAM name (and PK)',
`manager_id` VARCHAR(50) REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='The TEAMS!';
CREATE TABLE IF NOT EXISTS `tournament` (
`tid` SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` TEXT NOT NULL COMMENT 'Tournament name',
`created` DATETIME(6) NOT NULL COMMENT 'Created */\\'`/* datetime'
) CHARACTER SET utf8mb4 COMMENT='What Tournaments */\\'`/* we have';
CREATE TABLE IF NOT EXISTS `event` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Event ID',
`name` TEXT NOT NULL UNIQUE,
`modified` DATETIME(6) NOT NULL,
`prize` DECIMAL(10,2),
`token` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique token',
`tournament_id` SMALLINT NOT NULL COMMENT 'FK to tournament' REFERENCES `tournament` (`tid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='This table contains a list of all the events';
CREATE TABLE IF NOT EXISTS `sometable_self` (
`backward_sts` INT NOT NULL REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE,
`sts_forward` INT NOT NULL REFERENCES `sometable` (`sometable_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `team_team` (
`team_rel_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE,
`team_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `teamevents` (
`event_id` BIGINT NOT NULL REFERENCES `event` (`id`) ON DELETE CASCADE,
`team_id` VARCHAR(50) NOT NULL REFERENCES `team` (`name`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='How participants relate';
""".strip(), # noqa
)
