def test_alter_rename(self):
with self.schema.table("users") as blueprint:
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = ["EXEC sp_rename 'users.post', 'comment', 'COLUMN'"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_rename(self):
with self.schema.table("users") as blueprint:
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = ['ALTER TABLE "users" RENAME COLUMN post TO comment']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_rename(self):
with self.schema.table("users") as blueprint:
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = ["ALTER TABLE `users` CHANGE `post` `comment` INT NOT NULL"]
self.assertEqual(blueprint.to_sql(), sql)
def test_timestamp_alter_add_nullable_column(self):
with self.schema.table("users") as blueprint:
blueprint.timestamp("due_date").nullable()
self.assertEqual(len(blueprint.table.added_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = ['ALTER TABLE "users" ADD COLUMN due_date TIMESTAMP NULL']
self.assertEqual(blueprint.to_sql(), sql)
def test_timestamp_alter_add_nullable_column(self):
with self.schema.table("users") as blueprint:
blueprint.timestamp("due_date").nullable()
self.assertEqual(len(blueprint.table.added_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = ["ALTER TABLE [users] ADD [due_date] DATETIME NULL"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_and_rename(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.rename("post", "comment", "string")
table = Table("users")
table.add_column("post", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL",
"ALTER TABLE `users` CHANGE `post` `comment` VARCHAR NOT NULL",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_and_rename(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL",
"EXEC sp_rename 'users.post', 'comment', 'COLUMN'",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change_string(self):
with self.schema.table("users") as blueprint:
blueprint.string("name", 93).change()
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
'ALTER TABLE "users" ALTER COLUMN "name" TYPE VARCHAR(93), ALTER COLUMN "name" SET NOT NULL',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_and_rename(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = [
'ALTER TABLE "users" ADD COLUMN name VARCHAR(255) NOT NULL',
'ALTER TABLE "users" RENAME COLUMN post TO comment',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_rename(self):
with self.schema.table("users") as blueprint:
blueprint.rename("post", "comment", "integer")
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = [
"CREATE TEMPORARY TABLE __temp__users AS SELECT post FROM users",
'DROP TABLE "users"',
'CREATE TABLE "users" ("comment" INTEGER NOT NULL)',
'INSERT INTO "users" ("comment") SELECT post FROM __temp__users',
"DROP TABLE __temp__users",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("name")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE `users` ADD name VARCHAR(255) NOT NULL",
"ALTER TABLE `users` MODIFY age INT(11) NOT NULL",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("name")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
'ALTER TABLE "users" ADD COLUMN name VARCHAR(255) NOT NULL',
'ALTER TABLE "users" ALTER COLUMN age TYPE INTEGER, ALTER COLUMN age SET NOT NULL',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("name")
blueprint.string("external_type").default("external")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL, [external_type] VARCHAR(255) NOT NULL DEFAULT 'external'",
"ALTER TABLE [users] ALTER COLUMN [age] INT NOT NULL",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("external_type").default("external")
blueprint.integer("gender").nullable().change()
blueprint.string("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(len(blueprint.table.changed_columns), 2)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE `users` ADD `external_type` VARCHAR(255) NOT NULL DEFAULT 'external', ADD `name` VARCHAR(255) NOT NULL",
"ALTER TABLE `users` MODIFY `age` INT(11) NOT NULL, MODIFY `gender` INT(11) NULL",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("name")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
blueprint.table.from_table = table
sql = [
'ALTER TABLE "users" ADD COLUMN "name" VARCHAR NOT NULL',
"CREATE TEMPORARY TABLE __temp__users AS SELECT age FROM users",
'DROP TABLE "users"',
'CREATE TABLE "users" ("age" INTEGER NOT NULL, "name" VARCHAR(255) NOT NULL)',
'INSERT INTO "users" ("age") SELECT age FROM __temp__users',
"DROP TABLE __temp__users",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
table = Table("users")
table.add_column("post", "string")
table.add_column("name", "string")
table.add_column("email", "string")
blueprint.table.from_table = table
sql = [
"CREATE TEMPORARY TABLE __temp__users AS SELECT name, email FROM users",
'DROP TABLE "users"',
'CREATE TABLE "users" ("name" VARCHAR NOT NULL, "email" VARCHAR NOT NULL)',
'INSERT INTO "users" ("name", "email") SELECT name, email FROM __temp__users',
"DROP TABLE __temp__users",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_foreign_key_only(self):
with self.schema.table("users") as blueprint:
blueprint.foreign("playlist_id").references("id").on(
"playlists").on_delete("cascade").on_update("set null")
table = Table("users")
table.add_column("age", "string")
table.add_column("email", "string")
blueprint.table.from_table = table
sql = [
"CREATE TEMPORARY TABLE __temp__users AS SELECT age, email FROM users",
'DROP TABLE "users"',
'CREATE TABLE "users" ("age" VARCHAR NOT NULL, "email" VARCHAR NOT NULL, '
'CONSTRAINT users_playlist_id_foreign FOREIGN KEY ("playlist_id") REFERENCES "playlists"("id") ON DELETE CASCADE ON UPDATE SET NULL)',
'INSERT INTO "users" ("age", "email") SELECT age, email FROM __temp__users',
"DROP TABLE __temp__users",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_drop_add_and_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").default(0).nullable().change()
blueprint.string("name")
blueprint.drop_column("email")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
table.add_column("email", "string")
blueprint.table.from_table = table
sql = [
'ALTER TABLE "users" ADD COLUMN name VARCHAR(255) NOT NULL',
'ALTER TABLE "users" DROP COLUMN email',
'ALTER TABLE "users" ALTER COLUMN age TYPE INTEGER, ALTER COLUMN age DROP NOT NULL, ALTER COLUMN age SET DEFAULT 0',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_drop_add_and_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").default(0).change()
blueprint.string("name")
blueprint.drop_column("email")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
table.add_column("email", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL",
"ALTER TABLE [users] ALTER COLUMN [age] INT NOT NULL DEFAULT 0",
"ALTER TABLE [users] DROP COLUMN email",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_drop_add_and_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").change()
blueprint.string("name")
blueprint.drop_column("email")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(len(blueprint.table.changed_columns), 1)
table = Table("users")
table.add_column("age", "string")
table.add_column("email", "string")
blueprint.table.from_table = table
sql = [
"ALTER TABLE users ADD COLUMN name VARCHAR",
"CREATE TEMPORARY TABLE __temp__users AS SELECT age FROM users",
"DROP TABLE users",
'CREATE TABLE "users" (age INTEGER, name VARCHAR(255))',
'INSERT INTO "users" (age) SELECT age FROM __temp__users',
"DROP TABLE __temp__users",
]
