public override string GetCreateTable(CreateTableSchema table)
{
var result = new List <string>();
this.AddTimestamps(table);
var columns = table.GetColumns();
foreach (var column in columns)
{
result.Add("\t" + this.GetColumn(column.Value));
}
// unique
foreach (var column in table.columns)
{
if (column.Value.Unique)
{
result.Add("\tUNIQUE (" + this.WrapWithQuotes(column.Key) + ")");
}
}
return("CREATE TABLE " + this.FormatWithSchemaName(table.TableName) + " (\r"
+ string.Join(",\r", result)
+ "\r) " + this.GetTableEngine() + ";\r");
}
public virtual void AddTimestamps(CreateTableSchema table)
{
if (table.Timestamps &&
!table.HasColumn("created_at") &&
!table.HasColumn("updated_at"))
{
table.AddColumn("created_at", ColumnType.DateTime(), true, "CURRENT_TIMESTAMP");
table.AddColumn("updated_at", ColumnType.DateTime(), true);
}
}
public virtual string GetAddForeignKeyConstraints(CreateTableSchema table)
{
var result = "";
foreach (var foreignKey in table.foreignKeys)
{
result += this.GetAddForeignKeyConstraint(table.TableName, foreignKey.Value);
}
return(result);
}
public virtual string GetSeeds(CreateTableSchema table)
{
var result = "";
foreach (var seedRowCommand in table.seedTable.rows)
{
result += this.GetSeedRow(seedRowCommand.TableName, seedRowCommand.columnValues);
}
return(result);
}
public override string GetAddForeignKeyConstraints(CreateTableSchema table)
{
var result = "";
foreach (var foreignKey in table.foreignKeys)
{
result += "CREATE INDEX " + this.WrapWithQuotes(foreignKey.Value.ColumnName + "_index") + " ON " + this.FormatWithSchemaName(table.TableName) + " (" + this.WrapWithQuotes(foreignKey.Value.ColumnName) + ");\r";
result += "ALTER TABLE " + this.FormatWithSchemaName(table.TableName) + " ADD FOREIGN KEY (" + this.WrapWithQuotes(foreignKey.Value.ColumnName) + ") REFERENCES " + this.FormatWithSchemaName(foreignKey.Value.TableReferenced) + "(" + this.WrapWithQuotes(foreignKey.Value.PrimaryKeyReferenced) + ");\r";
}
return(result);
}
public bool HasIdentityPrimaryKey(CreateTableSchema table)
{
foreach (var primaryKey in table.primaryKeys)
{
if (primaryKey.Value.AutoIncrement)
{
return(true);
}
}
return(false);
}
public void TestGetColumn_WithDate()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Date());
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("`column1` DATE NOT NULL", result);
}
public void TestGetColumn_WithLongText()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.LongText());
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("`column1` LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL", result);
}
public void TestGetColumn_WithString()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1");
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("`column1` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL", result);
}
public void TestGetColumn_WithDefaultInt()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Int(), false, 10);
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] INT NOT NULL DEFAULT 10", result);
}
public void TestGetColumn_WithTimestamp()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Timestamp());
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] TIMESTAMP NOT NULL", result);
}
public void TestGetColumn_WithUnsignedBigInt()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.BigInt(true));
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("`column1` BIGINT UNSIGNED NOT NULL", result);
}
public void TestGetColumn_WithNotNullable()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", false);
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] NVARCHAR(255) NOT NULL", result);
}
public void TestGetColumn_WithLongText()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.LongText());
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] NTEXT NOT NULL", result);
}
public void TestGetColumn_WithFloatAndDigits()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Float(2));
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] DECIMAL(18,2) NOT NULL", result);
}
public void TestGetColumn_WithStringAndValue()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.VarChar(100));
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] NVARCHAR(100) NOT NULL", result);
}
public void TestGetColumn_WithBigIntUnisgned_IgnoreUnsigned()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.BigInt(true));
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] BIGINT NOT NULL", result);
}
public void TestGetColumn_WithCharAndValue()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Char(100));
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("`column1` CHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL", result);
}
public void TestGetColumn_WithDefault()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.VarChar(), false, "my value");
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] NVARCHAR(255) NOT NULL DEFAULT 'my value'", result);
}
public void TestAltertableAddForeignKey_WithNoForeignKey_ReturnsEmptyString()
{
var service = this.GetService();
var table = new CreateTableSchema("posts");
var result = service.GetAddForeignKeyConstraints(table);
var expected = "";
Assert.AreEqual(expected, result);
}
public void TestGetColumn_WithBlob()
{
var service = this.GetService();
var table = new CreateTableSchema("table1");
table.AddColumn("column1", ColumnType.Blob());
var result = service.GetColumn(table.GetColumn("column1"));
Assert.AreEqual("[column1] VARBINARY(MAX) NOT NULL", result);
}
public void TestChangeEngine()
{
var service = new MySqlQueryService("MyISAM");
var table = new CreateTableSchema("table1")
.AddColumn("column1");
var result = service.GetCreateTable(table);
var expected = "CREATE TABLE `table1` (\r\t`column1` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL\r) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\r";
Assert.AreEqual(expected, result);
}
public string GetAutoIncrement(CreateTableSchema table)
{
var result = "";
foreach (var column in table.primaryKeys)
{
if (column.Value.AutoIncrement)
{
result += "ALTER TABLE " + this.FormatWithSchemaName(table.TableName) + " MODIFY " + this.WrapWithQuotes(column.Key) + " INT UNSIGNED NOT NULL AUTO_INCREMENT;\r";
}
}
return(result);
}
public void TestAltertableAddPForeignKey()
{
var service = this.GetService();
var table = new CreateTableSchema("posts")
.AddForeignKey("user_id", "users", "id");
var result = service.GetAddForeignKeyConstraints(table);
var expected = "ALTER TABLE [dbo].[posts] ADD FOREIGN KEY ([user_id]) REFERENCES [dbo].[users]([id]);\r";
Assert.AreEqual(expected, result);
}
public void TestAltertableAddForeignKey()
{
var service = this.GetService();
var table = new CreateTableSchema("posts")
.AddForeignKey("user_id", "users", "id");
var result = service.GetAddForeignKeyConstraints(table);
var expected = "CREATE INDEX `user_id_index` ON `posts` (`user_id`);\rALTER TABLE `posts` ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);\r";
Assert.AreEqual(expected, result);
}
public void TestAltertableAddPrimaryKey_WithNoPrimaryKey_ReturnsEmptyString()
{
var service = this.GetService();
var table = new CreateTableSchema("table1")
.AddColumn("column1");
var result = service.GetAddPrimaryKeyConstraint(table);
var expected = "";
Assert.AreEqual(expected, result);
}
public void TestCreateTable_WithNoKeys()
{
var service = this.GetService();
var table = new CreateTableSchema("table1")
.AddColumn("column1")
.AddColumn("column2");
var result = service.GetCreateTable(table);
var expected = "CREATE TABLE `table1` (\r\t`column1` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,\r\t`column2` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL\r) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\r";
Assert.AreEqual(expected, result);
}
public void TestAltertableAddPrimaryKey()
{
var service = this.GetService();
var table = new CreateTableSchema("table1")
.AddPrimaryKey("pk1")
.AddColumn("column1");
var result = service.GetAddPrimaryKeyConstraint(table);
var expected = "ALTER TABLE `table1` ADD PRIMARY KEY (`pk1`);\rALTER TABLE `table1` MODIFY `pk1` INT UNSIGNED NOT NULL AUTO_INCREMENT;\r";
Assert.AreEqual(expected, result);
}
public void TestCreateTable_WithNoKeys()
{
var service = this.GetService();
var table = new CreateTableSchema("table1")
.AddColumn("column1")
.AddColumn("column2");
var result = service.GetCreateTable(table);
var expected = "CREATE TABLE [dbo].[table1] (\r\t[column1] NVARCHAR(255) NOT NULL,\r\t[column2] NVARCHAR(255) NOT NULL\r);\r";
Assert.AreEqual(expected, result);
}
public void TestAltertableAddPrimaryKey()
{
var service = this.GetService();
var table = new CreateTableSchema("table1")
.AddPrimaryKey("pk1")
.AddColumn("column1");
var result = service.GetAddPrimaryKeyConstraint(table);
var expected = "ALTER TABLE [dbo].[table1] ADD PRIMARY KEY ([pk1]);\r";
Assert.AreEqual(expected, result);
}
