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 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 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 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 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')",
)
