def setUp(self):
self.schema = Schema(
connection="dev",
connection_details=DATABASES,
platform=SQLitePlatform,
dry=True,
).on("dev")
def setUp(self):
self.schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
platform=PostgresPlatform,
dry=True,
)
def setUp(self):
self.schema = Schema(
connection_class=MySQLConnection,
connection="mysql",
connection_details=DATABASES,
platform=MySQLPlatform,
dry=True,
).on("mysql")
def test_alter_drop_on_table_schema_table(self):
schema = Schema(connection="dev",
connection_details=DATABASES).on("dev")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name").nullable()
def test_alter_drop_on_table_schema_table(self):
schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
).on("postgres")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name")
def test_should_return_relation_applying_hidden_attributes(self):
schema = Schema(connection_details=DATABASES,
connection="dev",
platform=SQLitePlatform).on("dev")
tables = ["users_hidden", "group_user", "groups"]
for table in tables:
schema.drop_table_if_exists(table)
with schema.create("users_hidden") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.integer("token")
blueprint.string("password")
blueprint.timestamps()
with schema.create("groups") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.timestamps()
with schema.create("group_user") as blueprint:
blueprint.increments("id")
blueprint.unsigned_integer("group_id")
blueprint.unsigned_integer("user_id")
blueprint.foreign("group_id").references("id").on("groups")
blueprint.foreign("user_id").references("id").on("users_hidden")
blueprint.timestamps()
UserHydrateHidden.create(name="Name",
password="******",
token="token_value")
Group.create(name="Group")
user = UserHydrateHidden.first()
group = Group.first()
group.attach_related("team", user)
serialized = Group.first().serialize()
self.assertIn("team", serialized)
self.assertTrue("team", serialized)
relation_serialized = serialized.get("team")
self.assertNotIn("password", relation_serialized)
self.assertNotIn("token", relation_serialized)
for table in tables:
schema.truncate(table)
class TestMSSQLSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MSSQLConnection,
connection="mssql",
connection_details=DATABASES,
platform=MSSQLPlatform,
dry=True,
).on("mssql")
def test_can_add_columns(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
["CREATE TABLE [users] ([name] VARCHAR(255) NOT NULL, [age] INT NOT NULL)"],
)
def test_can_add_columns_with_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] ([name] VARCHAR(255) NOT NULL, [age] INT NOT NULL, CONSTRAINT users_name_unique UNIQUE (name))"
],
)
def test_can_have_float_type(self):
with self.schema.create("users") as blueprint:
blueprint.float("amount")
self.assertEqual(
blueprint.to_sql(),
["""CREATE TABLE [users] (""" """[amount] FLOAT(19, 4) NOT NULL)"""],
)
def test_can_have_unsigned_columns(self):
with self.schema.create("users") as blueprint:
blueprint.integer("profile_id").unsigned()
blueprint.big_integer("big_profile_id").unsigned()
blueprint.tiny_integer("tiny_profile_id").unsigned()
blueprint.small_integer("small_profile_id").unsigned()
blueprint.medium_integer("medium_profile_id").unsigned()
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] ("
"[profile_id] INT NOT NULL, "
"[big_profile_id] BIGINT NOT NULL, "
"[tiny_profile_id] TINYINT NOT NULL, "
"[small_profile_id] SMALLINT NOT NULL, "
"[medium_profile_id] MEDIUMINT NOT NULL)"
],
)
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] "
"([name] VARCHAR(255) NOT NULL, "
"[age] INT NOT NULL, "
"[profile_id] INT NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY ([profile_id]) REFERENCES [profiles]([id]))"
],
)
def test_can_add_columns_with_add_foreign_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.add_foreign("profile_id.id.profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] "
"([name] VARCHAR(255) NOT NULL, "
"[age] INT NOT NULL, "
"[profile_id] INT NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY ([profile_id]) REFERENCES [profiles]([id]))"
],
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("email").unique()
blueprint.string("password")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamp("registered_at").default_raw("CURRENT_TIMESTAMP")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 10)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] ([id] INT IDENTITY NOT NULL, [name] VARCHAR(255) NOT NULL, [email] VARCHAR(255) NOT NULL, "
"[password] VARCHAR(255) NOT NULL, [admin] INT NOT NULL DEFAULT 0, [remember_token] VARCHAR(255) NULL, "
"[verified_at] DATETIME NULL, [registered_at] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, [created_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"[updated_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_email_unique UNIQUE (email))"
],
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.enum("gender", ["male", "female"])
blueprint.string("name")
blueprint.string("duration")
blueprint.string("url")
blueprint.inet("last_address").nullable()
blueprint.cidr("route_origin").nullable()
blueprint.macaddr("mac_address").nullable()
blueprint.datetime("published_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on("users").on_delete(
"CASCADE"
)
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 15)
self.assertEqual(
blueprint.to_sql(),
(
[
"CREATE TABLE [users] ([id] INT IDENTITY NOT NULL, [gender] VARCHAR(255) NOT NULL CHECK([gender] IN ('male', 'female')), [name] VARCHAR(255) NOT NULL, [duration] VARCHAR(255) NOT NULL, "
"[url] VARCHAR(255) NOT NULL, [last_address] VARCHAR(255) NULL, [route_origin] VARCHAR(255) NULL, [mac_address] VARCHAR(255) NULL, [published_at] DATETIME NOT NULL, [thumbnail] VARCHAR(255) NULL, [premium] INT NOT NULL, "
"[author_id] INT NULL, [description] TEXT NOT NULL, [created_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"[updated_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_author_id_foreign FOREIGN KEY ([author_id]) REFERENCES [users]([id]) ON DELETE CASCADE)"
]
),
)
def test_can_add_columns_with_foreign_key_constraint_name(self):
with self.schema.create("users") as blueprint:
blueprint.integer("profile_id")
blueprint.foreign("profile_id", name="profile_foreign").references("id").on(
"profiles"
)
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] ("
"[profile_id] INT NOT NULL, "
"CONSTRAINT profile_foreign FOREIGN KEY ([profile_id]) REFERENCES [profiles]([id]))"
],
)
def test_can_have_composite_keys(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.primary(["name", "age"])
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] "
"([name] VARCHAR(255) NOT NULL, "
"[age] INT NOT NULL, "
"[profile_id] INT NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_name_age_primary PRIMARY KEY (name, age))"
],
)
def test_can_have_column_primary_key(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").primary()
blueprint.integer("age")
blueprint.integer("profile_id")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE [users] "
"([name] VARCHAR(255) NOT NULL, "
"[age] INT NOT NULL, "
"[profile_id] INT NOT NULL, "
"CONSTRAINT users_name_primary PRIMARY KEY (name))"
],
)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, "TRUNCATE TABLE [users]")
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, "EXEC sp_rename [users], [clients]")
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, "DROP TABLE IF EXISTS [users]")
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, "DROP TABLE [users]")
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT 1 FROM sys.columns WHERE Name = N'name' AND Object_ID = Object_ID(N'users')",
)
def test_can_enable_foreign_keys(self):
sql = self.schema.enable_foreign_key_constraints()
self.assertEqual(sql, "")
def test_can_disable_foreign_keys(self):
sql = self.schema.disable_foreign_key_constraints()
self.assertEqual(sql, "")
def test_can_truncate_without_foreign_keys(self):
sql = self.schema.truncate("users", foreign_keys=True)
self.assertEqual(
sql,
[
"ALTER TABLE [users] NOCHECK CONSTRAINT ALL",
"TRUNCATE TABLE [users]",
"ALTER TABLE [users] WITH CHECK CHECK CONSTRAINT ALL",
],
)
class TestMySQLSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MySQLConnection,
connection="mysql",
connection_details=DATABASES,
platform=MySQLPlatform,
dry=True,
).on("mysql")
def test_can_add_columns1(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL, `age` INT(11) NOT NULL)"
],
)
def test_can_add_columns_with_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name"),
blueprint.unique("name", name="table_unique"),
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL, `age` INT(11) NOT NULL, CONSTRAINT users_name_unique UNIQUE (name), CONSTRAINT table_unique UNIQUE (name))"
],
)
def test_add_column_comment(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL COMMENT 'A users username')"
],
)
def test_can_add_table_comment(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.table_comment("A users table")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL) COMMENT 'A users table'"
],
)
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL, "
"`age` INT(11) NOT NULL, "
"`profile_id` INT(11) NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY (`profile_id`) REFERENCES `profiles`(`id`))"
],
)
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.add_foreign("profile_id.id.profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`name` VARCHAR(255) NOT NULL, "
"`age` INT(11) NOT NULL, "
"`profile_id` INT(11) NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY (`profile_id`) REFERENCES `profiles`(`id`))"
],
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.tiny_integer("active")
blueprint.string("email").unique()
blueprint.enum("gender", ["male", "female"])
blueprint.string("password")
blueprint.decimal("money")
blueprint.integer("admin").default(0)
blueprint.string("option").default("ADMIN")
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 13)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`id` INT UNSIGNED AUTO_INCREMENT NOT NULL, "
"`name` VARCHAR(255) NOT NULL, `active` TINYINT(1) NOT NULL, `email` VARCHAR(255) NOT NULL, `gender` ENUM('male', 'female') NOT NULL, "
"`password` VARCHAR(255) NOT NULL, `money` DECIMAL(17, 6) NOT NULL, "
"`admin` INT(11) NOT NULL DEFAULT 0, `option` VARCHAR(255) NOT NULL DEFAULT 'ADMIN', `remember_token` VARCHAR(255) NULL, `verified_at` TIMESTAMP NULL, "
"`created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_email_unique UNIQUE (email))"
],
)
def test_can_add_primary_constraint_without_column_name(self):
with self.schema.create("users") as blueprint:
blueprint.integer("user_id").primary()
blueprint.string("name")
blueprint.string("email")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(len(blueprint.table.added_constraints), 1)
self.assertTrue(
blueprint.to_sql()[0].startswith(
"CREATE TABLE `users` (`user_id` INT(11) NOT NULL"
)
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.big_increments("id")
blueprint.string("name")
blueprint.string("duration")
blueprint.string("url")
blueprint.inet("last_address").nullable()
blueprint.cidr("route_origin").nullable()
blueprint.macaddr("mac_address").nullable()
blueprint.datetime("published_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on("users").on_delete(
"CASCADE"
)
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 14)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` (`id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, `name` VARCHAR(255) NOT NULL, "
"`duration` VARCHAR(255) NOT NULL, `url` VARCHAR(255) NOT NULL, `last_address` VARCHAR(255) NULL, `route_origin` VARCHAR(255) NULL, `mac_address` VARCHAR(255) NULL, "
"`published_at` DATETIME NOT NULL, `thumbnail` VARCHAR(255) NULL, "
"`premium` INT(11) NOT NULL, `author_id` INT UNSIGNED NULL, `description` TEXT NOT NULL, `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"`updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_author_id_foreign FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE CASCADE)"
],
)
def test_can_add_columns_with_foreign_key_constraint_name(self):
with self.schema.create("users") as blueprint:
blueprint.integer("profile_id")
blueprint.foreign("profile_id", name="profile_foreign").references("id").on(
"profiles"
)
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` ("
"`profile_id` INT(11) NOT NULL, "
"CONSTRAINT profile_foreign FOREIGN KEY (`profile_id`) REFERENCES `profiles`(`id`))"
],
)
def test_can_have_composite_keys(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.primary(["name", "age"])
self.assertEqual(len(blueprint.table.added_columns), 3)
print(blueprint.to_sql())
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` "
"(`name` VARCHAR(255) NOT NULL, "
"`age` INT(11) NOT NULL, "
"`profile_id` INT(11) NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_name_age_primary PRIMARY KEY (name, age))"
],
)
def test_can_have_column_primary_key(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").primary()
blueprint.integer("age")
blueprint.integer("profile_id")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
"CREATE TABLE `users` "
"(`name` VARCHAR(255) NOT NULL, "
"`age` INT(11) NOT NULL, "
"`profile_id` INT(11) NOT NULL, "
"CONSTRAINT users_name_primary PRIMARY KEY (name))"
],
)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = f"SELECT * from information_schema.tables where table_name='users' AND table_schema = '{os.getenv('MYSQL_DATABASE_DATABASE')}'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, "TRUNCATE `users`")
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, "ALTER TABLE `users` RENAME TO `clients`")
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, "DROP TABLE IF EXISTS `users`")
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, "DROP TABLE `users`")
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT column_name FROM information_schema.columns WHERE table_name='users' and column_name='name'",
)
def test_can_enable_foreign_keys(self):
sql = self.schema.enable_foreign_key_constraints()
self.assertEqual(sql, "SET FOREIGN_KEY_CHECKS=1")
def test_can_disable_foreign_keys(self):
sql = self.schema.disable_foreign_key_constraints()
self.assertEqual(sql, "SET FOREIGN_KEY_CHECKS=0")
def test_can_truncate_without_foreign_keys(self):
sql = self.schema.truncate("users", foreign_keys=True)
self.assertEqual(
sql,
[
"SET FOREIGN_KEY_CHECKS=0",
"TRUNCATE `users`",
"SET FOREIGN_KEY_CHECKS=1",
],
)
class TestMySQLSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MSSQLConnection,
connection="mssql",
connection_details=DATABASES,
platform=MSSQLPlatform,
dry=True,
)
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL, [age] INT NOT NULL"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_adds_column_with_default(self):
with self.schema.table("users") as blueprint:
blueprint.string("name").default(0)
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL DEFAULT 0"
]
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 = ["EXEC sp_rename 'users.post', 'comment', 'COLUMN'"]
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_alter_drop1(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ["ALTER TABLE [users] DROP COLUMN post"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on(
"playlists").on_delete("cascade")
sql = [
"ALTER TABLE [users] ADD [playlist_id] INT NULL",
"ALTER TABLE [users] ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY ([playlist_id]) REFERENCES [playlists]([id]) ON DELETE CASCADE",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_add_foreign(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.add_foreign("playlist_id.id.playlists").on_delete(
"cascade")
sql = [
"ALTER TABLE [users] ADD [playlist_id] INT NULL",
"ALTER TABLE [users] ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY ([playlist_id]) REFERENCES [playlists]([id]) ON DELETE CASCADE",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = ["ALTER TABLE [users] DROP CONSTRAINT users_playlist_id_foreign"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = ["ALTER TABLE [users] DROP CONSTRAINT users_playlist_id_foreign"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ["DROP INDEX [users].[users_playlist_id_unique]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_primary(self):
with self.schema.table("users") as blueprint:
blueprint.primary("playlist_id")
sql = [
"ALTER TABLE [users] ADD CONSTRAINT users_playlist_id_primary PRIMARY KEY (playlist_id)"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ["CREATE INDEX users_playlist_id_index ON [users](playlist_id)"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ["DROP INDEX [users].[users_playlist_id_index]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ["DROP INDEX [users].[users_playlist_id_index]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ["DROP INDEX [users].[users_playlist_id_unique]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_primary(self):
with self.schema.table("users") as blueprint:
blueprint.drop_primary(["id"])
sql = ["DROP INDEX [users].[users_id_primary]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_sql, sql)
def test_drop_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_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_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_can_create_indexes(self):
with self.schema.table("users") as blueprint:
blueprint.index("name")
blueprint.index(["name", "email"])
blueprint.unique("name")
blueprint.unique(["name", "email"])
blueprint.fulltext("description")
self.assertEqual(len(blueprint.table.added_columns), 0)
print(blueprint.to_sql())
self.assertEqual(
blueprint.to_sql(),
[
"CREATE INDEX users_name_index ON [users](name)",
"CREATE INDEX users_name_email_index ON [users](name,email)",
"ALTER TABLE [users] ADD CONSTRAINT users_name_unique UNIQUE(name)",
"ALTER TABLE [users] ADD CONSTRAINT users_name_email_unique UNIQUE(name,email)",
],
)
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)
class TestPostgresSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
platform=PostgresPlatform,
dry=True,
).on("postgres")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
'ALTER TABLE "users" ADD COLUMN "name" VARCHAR(255) NOT NULL, ADD COLUMN "age" INTEGER NOT NULL'
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_add_column_comments(self):
with self.schema.table("users") as blueprint:
blueprint.string("name").comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
'ALTER TABLE "users" ADD COLUMN "name" VARCHAR(255) NOT NULL',
"""COMMENT ON COLUMN "users"."name" is 'A users username'""",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_add_table_comment(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.table_comment("A users table")
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
'ALTER TABLE "users" ADD COLUMN "name" VARCHAR(255) NOT NULL',
"""COMMENT ON TABLE "users" is 'A users table'""",
]
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_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_drop(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ['ALTER TABLE "users" DROP COLUMN "post"']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on(
"playlists").on_delete("cascade")
sql = [
'ALTER TABLE "users" ADD COLUMN "playlist_id" INT NULL',
'ALTER TABLE "users" ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY ("playlist_id") REFERENCES "playlists"("id") ON DELETE CASCADE',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_create_indexes(self):
with self.schema.table("users") as blueprint:
blueprint.index("name")
blueprint.index(["name", "email"])
blueprint.unique("name")
blueprint.unique(["name", "email"])
blueprint.fulltext("description")
self.assertEqual(len(blueprint.table.added_columns), 0)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE INDEX users_name_index ON "users"(name)',
'CREATE INDEX users_name_email_index ON "users"(name,email)',
'ALTER TABLE "users" ADD CONSTRAINT users_name_unique UNIQUE(name)',
'ALTER TABLE "users" ADD CONSTRAINT users_name_email_unique UNIQUE(name,email)',
],
)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_foreign']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_foreign']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_unique']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ['CREATE INDEX users_playlist_id_index ON "users"(playlist_id)']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_primary(self):
with self.schema.table("users") as blueprint:
blueprint.primary("playlist_id")
sql = [
'ALTER TABLE "users" ADD CONSTRAINT users_playlist_id_primary PRIMARY KEY (playlist_id)'
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ["DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ["DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_primary(self):
with self.schema.table("users") as blueprint:
blueprint.drop_primary("users_id_primary")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_id_primary']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_unique']
self.assertEqual(blueprint.to_sql(), sql)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * from information_schema.tables where table_name='users'"
self.assertEqual(schema_sql, sql)
def test_drop_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * from information_schema.tables where table_name='users'"
self.assertEqual(schema_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_drop_add_and_change(self):
with self.schema.table("users") as blueprint:
blueprint.integer("age").default(0).nullable().change()
blueprint.string("name")
blueprint.string("external_type").default("external")
blueprint.drop_column("email")
self.assertEqual(len(blueprint.table.added_columns), 2)
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, ADD COLUMN "external_type" VARCHAR(255) NOT NULL DEFAULT 'external'""",
'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_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_alter_drop_on_table_schema_table(self):
schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
).on("postgres")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name")
class TestMySQLSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MSSQLConnection,
connection="mssql",
connection_details=DATABASES,
platform=MSSQLPlatform,
dry=True,
)
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
"ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL, [age] INT NOT NULL"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_adds_column_with_default(self):
with self.schema.table("users") as blueprint:
blueprint.string("name").default(0)
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = ["ALTER TABLE [users] ADD [name] VARCHAR(255) NOT NULL DEFAULT 0"]
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 = ["EXEC sp_rename 'users.post', 'comment', 'COLUMN'"]
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_alter_drop1(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ["ALTER TABLE [users] DROP COLUMN post"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on("playlists")
sql = [
"ALTER TABLE [users] ADD [playlist_id] INT NULL",
"ALTER TABLE [users] ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY ([playlist_id]) REFERENCES playlists([id])",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = ["ALTER TABLE [users] DROP CONSTRAINT users_playlist_id_foreign"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = ["ALTER TABLE [users] DROP CONSTRAINT users_playlist_id_foreign"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ["DROP INDEX [users].[users_playlist_id_unique]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ["CREATE INDEX users_playlist_id_index ON users(playlist_id)"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ["DROP INDEX [users].[users_playlist_id_index]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ["DROP INDEX [users].[users_playlist_id_index]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ["DROP INDEX [users].[users_playlist_id_unique]"]
self.assertEqual(blueprint.to_sql(), sql)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_sql, sql)
def test_drop_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_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] ALTER COLUMN [age] INT NOT NULL",
]
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_alter_drop_on_table_schema_table(self):
# schema = Schema(
# connection=MSSQLConnection,
# connection_details=DATABASES,
# ).on("mssql")
# with schema.table("table_schema") as blueprint:
# blueprint.drop_column("name")
# with schema.table("table_schema") as blueprint:
# blueprint.string("name")
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 DEFAULT CURRENT_TIMESTAMP"
]
self.assertEqual(blueprint.to_sql(), sql)
class TestSQLiteSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection="dev",
connection_details=DATABASES,
platform=SQLitePlatform,
dry=True,
).on("dev")
def test_can_add_columns(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
'CREATE TABLE "users" (name VARCHAR(255), age INTEGER)')
def test_can_add_columns_with_constraint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
'CREATE TABLE "users" (name VARCHAR(255), age INTEGER, UNIQUE(name))',
)
def test_can_add_columns_with_foreign_key_constraint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
'CREATE TABLE "users" '
"(name VARCHAR(255), "
"age INTEGER, "
"profile_id INTEGER, "
"UNIQUE(name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY (profile_id) REFERENCES profiles(id))",
)
def test_can_use_morphs_for_polymorphism_relationships(self):
with self.schema.create("likes") as blueprint:
blueprint.morphs("record")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
'CREATE TABLE "likes" '
"(record_id UNSIGNED INT, "
"record_type VARCHAR)",
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("email").unique()
blueprint.string("password")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 9)
self.assertEqual(
blueprint.to_sql(),
('CREATE TABLE "users" (id INTEGER PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), '
"password VARCHAR(255), admin INTEGER DEFAULT 0, remember_token VARCHAR(255), "
"verified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
"updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(email))"),
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("duration")
blueprint.string("url")
blueprint.datetime("published_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on(
"users").on_delete("CASCADE")
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 11)
self.assertEqual(
blueprint.to_sql(),
('CREATE TABLE "users" (id INTEGER PRIMARY KEY, name VARCHAR(255), duration VARCHAR(255), '
"url VARCHAR(255), published_at DATETIME, thumbnail VARCHAR(255), premium INTEGER, "
"author_id UNSIGNED INT, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
"updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
"CONSTRAINT users_author_id_foreign FOREIGN KEY (author_id) REFERENCES users(id))"
),
)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, 'TRUNCATE "users"')
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, 'ALTER TABLE "users" RENAME TO "clients"')
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, 'DROP TABLE IF EXISTS "users"')
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, 'DROP TABLE "users"')
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT column_name FROM information_schema.columns WHERE table_name='users' and column_name='name'",
)
class TestMySQLSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MySQLConnection,
connection="mysql",
connection_details=DATABASES,
platform=MySQLPlatform,
dry=True,
).on("mysql")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
"ALTER TABLE `users` ADD name VARCHAR(255) NOT NULL, ADD age INT(11) NOT NULL"
]
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_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",
"ALTER TABLE `users` RENAME COLUMN post TO comment",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop1(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ["ALTER TABLE `users` DROP COLUMN post"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on("playlists")
sql = [
"ALTER TABLE `users` ADD playlist_id INT UNSIGNED NULL",
"ALTER TABLE `users` ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY (playlist_id) REFERENCES playlists(id)",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = [
"ALTER TABLE `users` DROP FOREIGN KEY users_playlist_id_foreign"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = [
"ALTER TABLE `users` DROP FOREIGN KEY users_playlist_id_foreign"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_unique"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ["CREATE INDEX users_playlist_id_index ON users(playlist_id)"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_unique"]
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_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 TIMESTAMP NULL"]
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` MODIFY age INT(11) NOT NULL DEFAULT 0",
"ALTER TABLE `users` DROP COLUMN email",
]
self.assertEqual(blueprint.to_sql(), sql)
class TestPostgresSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
platform=PostgresPlatform,
dry=True,
)
def test_can_add_columns(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL)'
],
)
def test_can_create_table_if_not_exists(self):
with self.schema.create_table_if_not_exists("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE IF NOT EXISTS "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL)'
],
)
def test_can_add_column_comment(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL)',
"""COMMENT ON COLUMN "users"."name" is 'A users username'""",
],
)
def test_can_add_table_comment(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.table_comment("A users table")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL)',
"""COMMENT ON TABLE "users" is 'A users table'""",
],
)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, 'TRUNCATE "users"')
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, 'ALTER TABLE "users" RENAME TO "clients"')
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, 'DROP TABLE IF EXISTS "users"')
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, 'DROP TABLE "users"')
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT column_name FROM information_schema.columns WHERE table_name='users' and column_name='name'",
)
def test_can_add_columns_with_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL, CONSTRAINT users_name_unique UNIQUE (name))'
],
)
def test_can_add_columns_with_long_text(self):
with self.schema.create("users") as blueprint:
blueprint.long_text("description")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
['CREATE TABLE "users" ("description" TEXT NOT NULL)'])
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, CONSTRAINT users_name_unique UNIQUE (name), '
'CONSTRAINT users_profile_id_foreign FOREIGN KEY ("profile_id") REFERENCES "profiles"("id"))'
],
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("email").unique()
blueprint.string("password")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 9)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("id" SERIAL UNIQUE NOT NULL, "name" VARCHAR(255) NOT NULL, '
'"email" VARCHAR(255) NOT NULL, "password" VARCHAR(255) NOT NULL, "admin" INTEGER NOT NULL DEFAULT 0, '
'"remember_token" VARCHAR(255) NULL, "verified_at" TIMESTAMP NULL, '
'"created_at" TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP, '
"CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_email_unique UNIQUE (email))"
],
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.big_increments("id")
blueprint.string("name")
blueprint.enum("gender", ["male", "female"])
blueprint.string("duration")
blueprint.decimal("money")
blueprint.string("url")
blueprint.string("option").default("ADMIN")
blueprint.jsonb("payload")
blueprint.inet("last_address").nullable()
blueprint.cidr("route_origin").nullable()
blueprint.macaddr("mac_address").nullable()
blueprint.datetime("published_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.double("amount").default(0.0)
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on(
"authors").on_delete("CASCADE")
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 19)
self.assertEqual(
blueprint.to_sql(),
([
"""CREATE TABLE "users" ("id" BIGSERIAL UNIQUE NOT NULL, "name" VARCHAR(255) NOT NULL, "gender" VARCHAR(255) CHECK(gender IN ('male', 'female')) NOT NULL, """
""""duration" VARCHAR(255) NOT NULL, "money" DECIMAL(17, 6) NOT NULL, "url" VARCHAR(255) NOT NULL, "option" VARCHAR(255) NOT NULL DEFAULT 'ADMIN', "payload" JSONB NOT NULL, "last_address" INET NULL, """
'"route_origin" CIDR NULL, "mac_address" MACADDR NULL, "published_at" TIMESTAMPTZ NOT NULL, "thumbnail" VARCHAR(255) NULL, "premium" INTEGER NOT NULL, "amount" DOUBLE PRECISION NOT NULL DEFAULT 0.0, '
'"author_id" INT NULL, "description" TEXT NOT NULL, "created_at" TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP, '
'"updated_at" TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP, '
'CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_author_id_foreign FOREIGN KEY ("author_id") REFERENCES "authors"("id") ON DELETE CASCADE)'
]),
)
def test_can_add_uuid_column(self):
# might not be the right place for this test + other column types
# are not tested => just for testing the PR now
with self.schema.create("users") as table:
table.uuid("id").default_raw("uuid_generate_v4()")
table.primary("id")
table.string("name")
table.uuid("public_id").nullable()
table.uuid("other_id").default_raw("uuid_generate_v4()")
self.assertEqual(len(table.table.added_columns), 4)
self.assertEqual(
table.to_sql(),
[
'CREATE TABLE "users" ("id" UUID NOT NULL DEFAULT uuid_generate_v4(), "name" VARCHAR(255) NOT NULL, "public_id" UUID NULL, "other_id" UUID NOT NULL DEFAULT uuid_generate_v4(), CONSTRAINT users_id_primary PRIMARY KEY (id))'
],
)
def test_can_add_columns_with_foreign_key_constraint_name(self):
with self.schema.create("users") as blueprint:
blueprint.integer("profile_id")
blueprint.foreign(
"profile_id",
name="profile_foreign").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 1)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ('
'"profile_id" INTEGER NOT NULL, '
'CONSTRAINT profile_foreign FOREIGN KEY ("profile_id") REFERENCES "profiles"("id"))'
],
)
def test_can_have_composite_keys(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.primary(["name", "age"])
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_name_age_primary PRIMARY KEY (name, age))"
],
)
def test_can_have_column_primary_key(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").primary()
blueprint.integer("age")
blueprint.integer("profile_id")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"CONSTRAINT users_name_primary PRIMARY KEY (name))"
],
)
def test_can_add_other_integer_types_column(self):
with self.schema.create("integer_types") as table:
table.tiny_integer("tiny")
table.small_integer("small")
table.medium_integer("medium")
table.big_integer("big")
self.assertEqual(len(table.table.added_columns), 4)
self.assertEqual(
table.to_sql(),
[
'CREATE TABLE "integer_types" ("tiny" TINYINT NOT NULL, "small" SMALLINT NOT NULL, "medium" MEDIUMINT NOT NULL, "big" BIGINT NOT NULL)'
],
)
def test_can_add_binary_column(self):
with self.schema.create("binary_storing") as table:
table.binary("filecontent")
self.assertEqual(len(table.table.added_columns), 1)
self.assertEqual(
table.to_sql(),
['CREATE TABLE "binary_storing" ("filecontent" BYTEA NOT NULL)'],
)
def test_can_enable_foreign_keys(self):
sql = self.schema.enable_foreign_key_constraints()
self.assertEqual(sql, "")
def test_can_disable_foreign_keys(self):
sql = self.schema.disable_foreign_key_constraints()
self.assertEqual(sql, "")
def test_can_truncate_without_foreign_keys(self):
sql = self.schema.truncate("users", foreign_keys=True)
self.assertEqual(
sql,
[
'ALTER TABLE "users" DISABLE TRIGGER ALL',
'TRUNCATE "users"',
'ALTER TABLE "users" ENABLE TRIGGER ALL',
],
)
class TestMSSQLSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MSSQLConnection,
connection="mssql",
connection_details=DATABASES,
platform=MSSQLPlatform,
dry=True,
).on("mssql")
def test_can_add_columns(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE [users] ([name] VARCHAR(255) NOT NULL, [age] INT NOT NULL)",
)
def test_can_add_columns_with_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE [users] ([name] VARCHAR(255) NOT NULL, [age] INT NOT NULL, CONSTRAINT users_name_unique UNIQUE (name))",
)
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE [users] "
"([name] VARCHAR(255) NOT NULL, "
"[age] INT NOT NULL, "
"[profile_id] INT NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY (profile_id) REFERENCES profiles(id))",
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("email").unique()
blueprint.string("password")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 9)
self.assertEqual(
blueprint.to_sql(),
("CREATE TABLE [users] ([id] INT IDENTITY PRIMARY KEY NOT NULL, [name] VARCHAR(255) NOT NULL, [email] VARCHAR(255) NOT NULL, "
"[password] VARCHAR(255) NOT NULL, [admin] INT NOT NULL DEFAULT 0, [remember_token] VARCHAR(255) NULL, "
"[verified_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, [created_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"[updated_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_email_unique UNIQUE (email))"
),
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("duration")
blueprint.string("url")
blueprint.datetime("published_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on(
"users").on_delete("CASCADE")
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 11)
self.assertEqual(
blueprint.to_sql(),
("CREATE TABLE [users] ([id] INT IDENTITY PRIMARY KEY NOT NULL, [name] VARCHAR(255) NOT NULL, [duration] VARCHAR(255) NOT NULL, "
"[url] VARCHAR(255) NOT NULL, [published_at] DATETIME NOT NULL, [thumbnail] VARCHAR(255) NULL, [premium] INT NOT NULL, "
"[author_id] INT NULL, [description] TEXT NOT NULL, [created_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"[updated_at] DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "
"CONSTRAINT users_author_id_foreign FOREIGN KEY (author_id) REFERENCES users(id))"
),
)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, "TRUNCATE TABLE [users]")
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, "EXEC sp_rename [users], [clients]")
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, "DROP TABLE IF EXISTS [users]")
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, "DROP TABLE [users]")
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT 1 FROM sys.columns WHERE Name = N'name' AND Object_ID = Object_ID(N'users')",
)
class TestPostgresSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
platform=PostgresPlatform,
dry=True,
).on("postgres")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
'ALTER TABLE "users" ADD COLUMN name VARCHAR(255) NOT NULL, ADD COLUMN age INTEGER NOT NULL'
]
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_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_drop(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ['ALTER TABLE "users" DROP COLUMN post']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on("playlists")
sql = [
'ALTER TABLE "users" ADD COLUMN playlist_id INT NULL',
'ALTER TABLE "users" ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY (playlist_id) REFERENCES playlists(id)',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_foreign']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_foreign']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_unique']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ["CREATE INDEX users_playlist_id_index ON users(playlist_id)"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_index']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_index']
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ['ALTER TABLE "users" DROP CONSTRAINT users_playlist_id_unique']
self.assertEqual(blueprint.to_sql(), sql)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * from information_schema.tables where table_name='users'"
self.assertEqual(schema_sql, sql)
def test_drop_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT * from information_schema.tables where table_name='users'"
self.assertEqual(schema_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_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_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_alter_drop_on_table_schema_table(self):
schema = Schema(
connection_class=PostgresConnection,
connection="postgres",
connection_details=DATABASES,
).on("postgres")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name")
class TestSQLiteSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection="dev",
connection_details=DATABASES,
platform=SQLitePlatform,
dry=True,
).on("dev")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.string("external_type").default("external")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 3)
sql = [
'ALTER TABLE "users" ADD COLUMN "name" VARCHAR NOT NULL',
"""ALTER TABLE "users" ADD COLUMN "external_type" VARCHAR NOT NULL DEFAULT 'external'""",
'ALTER TABLE "users" ADD COLUMN "age" INTEGER NOT NULL',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_add_constraints(self):
with self.schema.table("users") as blueprint:
blueprint.unique("name", name="table_unique")
self.assertEqual(len(blueprint.table.added_columns), 0)
sql = ['CREATE UNIQUE INDEX table_unique ON "users"(name)']
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_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_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_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 NOT NULL, "name" VARCHAR(255) NOT NULL)',
'INSERT INTO "users" ("age") SELECT age FROM __temp__users',
"DROP TABLE __temp__users",
]
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_alter_drop_on_table_schema_table(self):
schema = Schema(connection="dev",
connection_details=DATABASES).on("dev")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name").nullable()
def test_alter_add_primary(self):
with self.schema.table("users") as blueprint:
blueprint.primary("playlist_id")
sql = [
'ALTER TABLE "users" ADD CONSTRAINT users_playlist_id_primary PRIMARY KEY (playlist_id)'
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
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 = [
'ALTER TABLE "users" ADD COLUMN "playlist_id" UNSIGNED INT NULL REFERENCES "playlists"("id")',
"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, "playlist_id" UNSIGNED INT 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_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)
class TestMySQLSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MySQLConnection,
connection="mysql",
connection_details=DATABASES,
platform=MySQLPlatform,
dry=True,
).on("mysql")
def test_can_add_columns1(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(11) NOT NULL)",
)
def test_can_add_columns_with_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(11) NOT NULL, CONSTRAINT users_name_unique UNIQUE (name))",
)
def test_can_add_columns_with_foreign_key_constaint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
"CREATE TABLE users (name VARCHAR(255) NOT NULL, "
"age INT(11) NOT NULL, "
"profile_id INT(11) NOT NULL, "
"CONSTRAINT users_name_unique UNIQUE (name), "
"CONSTRAINT users_profile_id_foreign FOREIGN KEY (profile_id) REFERENCES profiles(id))",
)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.string("email").unique()
blueprint.string("password")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 9)
self.assertEqual(
blueprint.to_sql(),
("CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, "
"name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, "
"admin INT(11) NOT NULL DEFAULT 0, remember_token VARCHAR(255) NULL, verified_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, "
"created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_email_unique UNIQUE (email))"
),
)
def test_can_add_primary_constraint_without_column_name(self):
with self.schema.create("users") as blueprint:
blueprint.integer("user_id").primary()
blueprint.string("name")
blueprint.string("email")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertTrue(blueprint.to_sql().startswith(
"CREATE TABLE users (user_id INT(11) NOT NULL PRIMARY KEY"))
# def test_can_advanced_table_creation2(self):
# with self.schema.create("users") as blueprint:
# blueprint.increments("id")
# blueprint.string("name")
# blueprint.string("duration")
# blueprint.string("url")
# blueprint.datetime("published_at")
# blueprint.string("thumbnail").nullable()
# blueprint.integer("premium")
# blueprint.integer("author_id").unsigned().nullable()
# blueprint.foreign("author_id").references("id").on("users").on_delete(
# "CASCADE"
# )
# blueprint.text("description")
# blueprint.timestamps()
# self.assertEqual(len(blueprint.table.added_columns), 11)
# self.assertEqual(
# blueprint.to_sql(),
# (
# "CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), "
# "duration VARCHAR(255), url VARCHAR(255), published_at DATETIME, thumbnail VARCHAR(255), "
# "premium INT(11), author_id INT UNSIGNED, description TEXT, created_at TIMESTAMP, "
# "updated_at TIMESTAMP, CONSTRAINT users_author_id_foreign FOREIGN KEY (author_id) REFERENCES users(id))"
# ),
# )
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = f"SELECT * from information_schema.tables where table_name='users' AND table_schema = '{os.getenv('MYSQL_DATABASE_DATABASE')}'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, "TRUNCATE `users`")
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, "ALTER TABLE `users` RENAME TO `clients`")
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, "DROP TABLE IF EXISTS `users`")
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, "DROP TABLE `users`")
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT column_name FROM information_schema.columns WHERE table_name='users' and column_name='name'",
)
class TestMySQLSchemaBuilderAlter(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection_class=MySQLConnection,
connection="mysql",
connection_details=DATABASES,
platform=MySQLPlatform,
dry=True,
).on("mysql")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL, ADD `age` INT(11) NOT NULL"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_add_column_comments(self):
with self.schema.table("users") as blueprint:
blueprint.string("name").comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL COMMENT 'A users username'"
]
def test_can_add_table_comment(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.table_comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL COMMENT 'A users username'"
]
def test_can_add_table_comment_with_no_columns(self):
with self.schema.table("users") as blueprint:
blueprint.table_comment("A users username")
self.assertEqual(len(blueprint.table.added_columns), 0)
sql = ["ALTER TABLE `users` COMMENT 'A users username'"]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_add_column_after(self):
with self.schema.table("users") as blueprint:
blueprint.string("name").after("age")
self.assertEqual(len(blueprint.table.added_columns), 1)
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL AFTER `age`"
]
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_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_to_string(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.rename("post", "comment", "string", length=200)
table = Table("users")
table.add_column("post", "integer")
blueprint.table.from_table = table
sql = [
"ALTER TABLE `users` ADD `name` VARCHAR(255) NOT NULL",
"ALTER TABLE `users` CHANGE `post` `comment` VARCHAR(200) NOT NULL",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop1(self):
with self.schema.table("users") as blueprint:
blueprint.drop_column("post")
sql = ["ALTER TABLE `users` DROP COLUMN `post`"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_column_and_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.unsigned_integer("playlist_id").nullable()
blueprint.foreign("playlist_id").references("id").on(
"playlists").on_delete("cascade")
sql = [
"ALTER TABLE `users` ADD `playlist_id` INT UNSIGNED NULL",
"ALTER TABLE `users` ADD CONSTRAINT users_playlist_id_foreign FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign("users_playlist_id_foreign")
sql = [
"ALTER TABLE `users` DROP FOREIGN KEY users_playlist_id_foreign"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_foreign_key_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_foreign(["playlist_id"])
sql = [
"ALTER TABLE `users` DROP FOREIGN KEY users_playlist_id_foreign"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique("users_playlist_id_unique")
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_unique"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_index(self):
with self.schema.table("users") as blueprint:
blueprint.index("playlist_id")
sql = ["CREATE INDEX users_playlist_id_index ON `users`(playlist_id)"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index("users_playlist_id_index")
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_add_primary(self):
with self.schema.table("users") as blueprint:
blueprint.primary("playlist_id")
sql = [
"ALTER TABLE `users` ADD CONSTRAINT users_playlist_id_primary PRIMARY KEY (playlist_id)"
]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_index_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_index(["playlist_id"])
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_index"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_unique_constraint_shortcut(self):
with self.schema.table("users") as blueprint:
blueprint.drop_unique(["playlist_id"])
sql = ["ALTER TABLE `users` DROP INDEX users_playlist_id_unique"]
self.assertEqual(blueprint.to_sql(), sql)
def test_alter_drop_primary(self):
with self.schema.table("users") as blueprint:
blueprint.drop_primary("users_id_primary")
sql = ["ALTER TABLE `users` DROP INDEX users_id_primary"]
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_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` TIMESTAMP NULL"]
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` MODIFY `age` INT(11) NOT NULL DEFAULT 0",
"ALTER TABLE `users` DROP COLUMN `email`",
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_create_indexes(self):
with self.schema.table("users") as blueprint:
blueprint.index("name")
blueprint.index(["name", "email"])
blueprint.unique("name")
blueprint.unique("name", name="table_unique")
blueprint.unique(["name", "email"])
blueprint.fulltext("description")
self.assertEqual(len(blueprint.table.added_columns), 0)
print(blueprint.to_sql())
self.assertEqual(
blueprint.to_sql(),
[
"CREATE INDEX users_name_index ON `users`(name)",
"CREATE INDEX users_name_email_index ON `users`(name,email)",
"ALTER TABLE `users` ADD CONSTRAINT UNIQUE INDEX users_name_unique(name)",
"ALTER TABLE `users` ADD CONSTRAINT UNIQUE INDEX table_unique(name)",
"ALTER TABLE `users` ADD CONSTRAINT UNIQUE INDEX users_name_email_unique(name,email)",
"ALTER TABLE `users` ADD FULLTEXT description_fulltext(description)",
],
)
class TestSQLiteSchemaBuilder(unittest.TestCase):
maxDiff = None
def setUp(self):
self.schema = Schema(
connection="dev",
connection_details=DATABASES,
platform=SQLitePlatform,
dry=True,
).on("dev")
def test_can_add_columns(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL)'
],
)
def test_can_create_table_if_not_exists(self):
with self.schema.create_table_if_not_exists("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE IF NOT EXISTS "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL)'
],
)
def test_can_add_columns_with_constraint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
blueprint.unique("name")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" ("name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL, UNIQUE(name))'
],
)
def test_can_add_columns_with_foreign_key_constraint(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign("profile_id").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"UNIQUE(name), "
'CONSTRAINT users_profile_id_foreign FOREIGN KEY ("profile_id") REFERENCES "profiles"("id"))'
],
)
def test_can_add_columns_with_foreign_key_constraint_name(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.foreign(
"profile_id",
name="profile_foreign").references("id").on("profiles")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"UNIQUE(name), "
'CONSTRAINT profile_foreign FOREIGN KEY ("profile_id") REFERENCES "profiles"("id"))'
],
)
def test_can_use_morphs_for_polymorphism_relationships(self):
with self.schema.create("likes") as blueprint:
blueprint.morphs("record")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
'CREATE TABLE "likes" ("record_id" UNSIGNED INT NOT NULL, "record_type" VARCHAR(255) NOT NULL)',
'CREATE INDEX likes_record_id_index ON "likes"(record_id)',
'CREATE INDEX likes_record_type_index ON "likes"(record_type)',
]
self.assertEqual(blueprint.to_sql(), sql)
def test_can_advanced_table_creation(self):
with self.schema.create("users") as blueprint:
blueprint.increments("id")
blueprint.string("name")
blueprint.enum("gender", ["male", "female"])
blueprint.string("email").unique()
blueprint.string("password")
blueprint.string("option").default("ADMIN")
blueprint.integer("admin").default(0)
blueprint.string("remember_token").nullable()
blueprint.timestamp("verified_at").nullable()
blueprint.unique(["email", "name"])
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 11)
self.assertEqual(
blueprint.to_sql(),
[
"""CREATE TABLE "users" ("id" INTEGER NOT NULL, "name" VARCHAR(255) NOT NULL, "gender" VARCHAR(255) CHECK(gender IN ('male', 'female')) NOT NULL, "email" VARCHAR(255) NOT NULL, """
""""password" VARCHAR(255) NOT NULL, "option" VARCHAR(255) NOT NULL DEFAULT 'ADMIN', "admin" INTEGER NOT NULL DEFAULT 0, "remember_token" VARCHAR(255) NULL, """
'"verified_at" TIMESTAMP NULL, "created_at" DATETIME NULL DEFAULT CURRENT_TIMESTAMP, '
'"updated_at" DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT users_id_primary PRIMARY KEY (id), '
"UNIQUE(email), UNIQUE(email, name))"
],
)
def test_can_create_indexes(self):
with self.schema.table("users") as blueprint:
blueprint.index("name")
blueprint.index("active", "active_idx")
blueprint.index(["name", "email"])
blueprint.unique("name")
blueprint.unique(["name", "email"])
blueprint.fulltext("description")
self.assertEqual(len(blueprint.table.added_columns), 0)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE INDEX users_name_index ON "users"(name)',
'CREATE INDEX active_idx ON "users"(active)',
'CREATE INDEX users_name_email_index ON "users"(name,email)',
'CREATE UNIQUE INDEX users_name_unique ON "users"(name)',
'CREATE UNIQUE INDEX users_name_email_unique ON "users"(name,email)',
],
)
def test_can_create_indexes_on_previous_column(self):
with self.schema.table("users") as blueprint:
blueprint.string("email").index()
blueprint.string("active").index(name="email_idx")
self.assertEqual(len(blueprint.table.added_columns), 2)
self.assertEqual(
blueprint.to_sql(),
[
'ALTER TABLE "users" ADD COLUMN "email" VARCHAR NOT NULL',
'ALTER TABLE "users" ADD COLUMN "active" VARCHAR NOT NULL',
'CREATE INDEX users_email_index ON "users"(email)',
'CREATE INDEX email_idx ON "users"(active)',
],
)
def test_can_have_composite_keys(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").unique()
blueprint.integer("age")
blueprint.integer("profile_id")
blueprint.primary(["name", "age"])
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"UNIQUE(name), "
"CONSTRAINT users_name_age_primary PRIMARY KEY (name, age))"
],
)
def test_can_have_column_primary_key(self):
with self.schema.create("users") as blueprint:
blueprint.string("name").primary()
blueprint.integer("age")
blueprint.integer("profile_id")
self.assertEqual(len(blueprint.table.added_columns), 3)
self.assertEqual(
blueprint.to_sql(),
[
'CREATE TABLE "users" '
'("name" VARCHAR(255) NOT NULL, '
'"age" INTEGER NOT NULL, '
'"profile_id" INTEGER NOT NULL, '
"CONSTRAINT users_name_primary PRIMARY KEY (name))"
],
)
def test_can_advanced_table_creation2(self):
with self.schema.create("users") as blueprint:
blueprint.big_increments("id")
blueprint.string("name")
blueprint.string("duration")
blueprint.string("url")
blueprint.json("payload")
blueprint.year("birth")
blueprint.inet("last_address").nullable()
blueprint.cidr("route_origin").nullable()
blueprint.macaddr("mac_address").nullable()
blueprint.datetime("published_at")
blueprint.time("wakeup_at")
blueprint.string("thumbnail").nullable()
blueprint.integer("premium")
blueprint.integer("author_id").unsigned().nullable()
blueprint.foreign("author_id").references("id").on(
"users").on_delete("set null")
blueprint.text("description")
blueprint.timestamps()
self.assertEqual(len(blueprint.table.added_columns), 17)
self.assertEqual(
blueprint.to_sql(),
([
'CREATE TABLE "users" ("id" BIGINT NOT NULL, "name" VARCHAR(255) NOT NULL, "duration" VARCHAR(255) NOT NULL, '
'"url" VARCHAR(255) NOT NULL, "payload" JSON NOT NULL, "birth" VARCHAR(4) NOT NULL, "last_address" VARCHAR(255) NULL, "route_origin" VARCHAR(255) NULL, "mac_address" VARCHAR(255) NULL, '
'"published_at" DATETIME NOT NULL, "wakeup_at" TIME NOT NULL, "thumbnail" VARCHAR(255) NULL, "premium" INTEGER NOT NULL, "author_id" UNSIGNED INT NULL, "description" TEXT NOT NULL, '
'"created_at" DATETIME NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NULL DEFAULT CURRENT_TIMESTAMP, '
'CONSTRAINT users_id_primary PRIMARY KEY (id), CONSTRAINT users_author_id_foreign FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE SET NULL)'
]),
)
def test_has_table(self):
schema_sql = self.schema.has_table("users")
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
self.assertEqual(schema_sql, sql)
def test_can_truncate(self):
sql = self.schema.truncate("users")
self.assertEqual(sql, 'DELETE FROM "users"')
def test_can_rename_table(self):
sql = self.schema.rename("users", "clients")
self.assertEqual(sql, 'ALTER TABLE "users" RENAME TO "clients"')
def test_can_drop_table_if_exists(self):
sql = self.schema.drop_table_if_exists("users", "clients")
self.assertEqual(sql, 'DROP TABLE IF EXISTS "users"')
def test_can_drop_table(self):
sql = self.schema.drop_table("users", "clients")
self.assertEqual(sql, 'DROP TABLE "users"')
def test_has_column(self):
sql = self.schema.has_column("users", "name")
self.assertEqual(
sql,
"SELECT column_name FROM information_schema.columns WHERE table_name='users' and column_name='name'",
)
def test_can_enable_foreign_keys(self):
sql = self.schema.enable_foreign_key_constraints()
self.assertEqual(sql, "PRAGMA foreign_keys = ON")
def test_can_disable_foreign_keys(self):
sql = self.schema.disable_foreign_key_constraints()
self.assertEqual(sql, "PRAGMA foreign_keys = OFF")
def test_can_truncate_without_foreign_keys(self):
sql = self.schema.truncate("users", foreign_keys=True)
self.assertEqual(
sql,
[
"PRAGMA foreign_keys = OFF",
'DELETE FROM "users"',
"PRAGMA foreign_keys = ON",
],
)
class TestSQLiteSchemaBuilderAlter(unittest.TestCase):
def setUp(self):
self.schema = Schema(
connection="dev",
connection_details=DATABASES,
platform=SQLitePlatform,
dry=True,
).on("dev")
def test_can_add_columns(self):
with self.schema.table("users") as blueprint:
blueprint.string("name")
blueprint.integer("age")
self.assertEqual(len(blueprint.table.added_columns), 2)
sql = [
"ALTER TABLE users ADD COLUMN name VARCHAR NOT NULL",
"ALTER TABLE users ADD COLUMN age INTEGER NOT NULL",
]
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)',
'INSERT INTO "users" (comment) SELECT post 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, email VARCHAR)',
'INSERT INTO "users" (name, email) SELECT name, email 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 COLUMN name VARCHAR NOT NULL",
"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",
]
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",
]
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_alter_drop_on_table_schema_table(self):
schema = Schema(connection="dev",
connection_details=DATABASES).on("dev")
with schema.table("table_schema") as blueprint:
blueprint.drop_column("name")
with schema.table("table_schema") as blueprint:
blueprint.string("name").nullable()
