public void CreateAndDropColumnAndIndexSql()
{
// Drop all tables first
MySqlTestHelper.DropAllTables();
// Open a connection
using var connection = new MySqlConnection(MySqlTestHelper.ConnectionString);
connection.Open();
// Create a table to test with
var schema = connection.Database;
const string tableName = "tableName";
CreateTables(connection, tableName);
// This column should already exist, so it should do nothing
var sql = MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "col1", "varchar(255) null");
var command = connection.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteScalar() as string;
Assert.AreEqual("column exists", result);
// Now create a new column
sql = MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "date1", "datetime(6) null");
command = connection.CreateCommand();
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual(null, result);
var columns = connection.GetColumns("", tableName);
Assert.AreEqual(3, columns.Count);
Assert.AreEqual("bigint", columns["id1"]);
Assert.AreEqual("mediumblob", columns["col1"]);
Assert.AreEqual("datetime", columns["date1"]);
// Now lets add an index
sql = MySqlMagic.CreateIndexIfNotExistsSql(schema, tableName, "idx1", "col1(10), date1");
command = connection.CreateCommand();
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual(null, result);
// Check the index got added
var indexes = connection.GetIndexes("", tableName);
Assert.AreEqual(2, indexes.Count);
Assert.AreEqual("id1", indexes["PRIMARY"]);
Assert.AreEqual("col1,date1", indexes["idx1"]);
// Try again and it should exist
sql = MySqlMagic.CreateIndexIfNotExistsSql(schema, tableName, "idx1", "col1(10), date1");
command = connection.CreateCommand();
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual("index exists", result);
// Now drop the index
sql = MySqlMagic.DropIndexIfExistsSql(schema, tableName, "idx1");
command = connection.CreateCommand();
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual(null, result);
// Check the index got removed
indexes = connection.GetIndexes("", tableName);
Assert.AreEqual(1, indexes.Count);
Assert.AreEqual("id1", indexes["PRIMARY"]);
// Try again and it should not exist
sql = MySqlMagic.DropIndexIfExistsSql(schema, tableName, "idx1");
command = connection.CreateCommand();
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual("index does not exist", result);
// Now drop the column
sql = MySqlMagic.DropColumnIfExistsSql(schema, tableName, "date1");
command = connection.CreateCommand();
command.CommandText = sql;
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual(null, result);
columns = connection.GetColumns("", tableName);
Assert.AreEqual(2, columns.Count);
Assert.AreEqual("bigint", columns["id1"]);
Assert.AreEqual("mediumblob", columns["col1"]);
// Now drop the column again and nothing happens
sql = MySqlMagic.DropColumnIfExistsSql(schema, tableName, "date1");
command = connection.CreateCommand();
command.CommandText = sql;
command.CommandText = sql;
result = command.ExecuteScalar() as string;
Assert.AreEqual("column does not exist", result);
}
void InnerEnsureTableIsCreated(TableName table)
{
using (var connection = _connectionProvider.GetConnection())
{
var tableNames = connection.GetTableNames();
if (tableNames.Contains(table))
{
// Use the current database prefix if one is not provided
var schema = table.Schema;
if (string.IsNullOrWhiteSpace(schema))
{
schema = connection.Database;
}
var tableName = table.Name;
// Check if the schema needs to be upgraded
var columns = connection.GetColumns(schema, tableName);
if (!columns.ContainsKey("processing") &&
columns.ContainsKey("ordering_key") &&
columns.ContainsKey("leased_until") &&
columns.ContainsKey("leased_by") &&
columns.ContainsKey("leased_for") &&
columns.ContainsKey("leased_at"))
{
_log.Info("Database already contains a table named {tableName} - will not create anything", table.QualifiedName);
}
else
{
connection.ExecuteCommands($@"
{MySqlMagic.DropColumnIfExistsSql(schema, tableName, "processing")}
----
{MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "ordering_key", $"varchar({OrderingKeyColumnSize}) NULL after `visible`")}
----
{MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "leased_until", "datetime(6) NULL after `body`")}
----
{MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "leased_by", $"varchar({LeasedByColumnSize}) NULL after `leased_until`")}
----
{MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "leased_for", $"varchar({OrderingKeyColumnSize}) NULL after `leased_by`")}
----
{MySqlMagic.CreateColumnIfNotExistsSql(schema, tableName, "leased_at", "datetime(6) NULL after `leased_for`")}
----
{MySqlMagic.DropIndexIfExistsSql(schema, tableName, "idx_receive")}
----
{MySqlMagic.DropIndexIfExistsSql(schema, tableName, "idx_receive_lease")}
----
{MySqlMagic.CreateIndexIfNotExistsSql(schema, tableName, "idx_receive", "`priority` DESC, `visible` ASC, `id` ASC, `expiration` ASC, `leased_until` DESC, `leased_for` ASC")}
----
{MySqlMagic.CreateIndexIfNotExistsSql(schema, tableName, "idx_leased_for", "`leased_for`")}");
}
}
else
{
_log.Info("Table {tableName} does not exist - it will be created now", table.QualifiedName);
connection.ExecuteCommands($@"
CREATE TABLE {table.QualifiedName} (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`priority` INT NOT NULL,
`expiration` DATETIME(6) NOT NULL,
`visible` DATETIME(6) NOT NULL,
`ordering_key` varchar({OrderingKeyColumnSize}) NULL,
`headers` LONGBLOB NOT NULL,
`body` LONGBLOB NOT NULL,
`leased_until` datetime(6) NULL,
`leased_by` varchar({LeasedByColumnSize}) NULL,
`leased_for` varchar({OrderingKeyColumnSize}) NULL,
`leased_at` datetime(6) NULL,
PRIMARY KEY (`id`)
);
----
CREATE INDEX `idx_receive` ON {table.QualifiedName} (
`priority` DESC,
`visible` ASC,
`id` ASC,
`expiration` ASC,
`leased_until` DESC,
`leased_for` ASC
);
----
CREATE INDEX `idx_leased_for` ON {table.QualifiedName} (
`leased_for`
);
----
CREATE INDEX `idx_expiration` ON {table.QualifiedName} (
`expiration`
);");
}
connection.Complete();
}
}