private static void CreateTable(MsSqlDatabase sqlDb, XElement table)
{
var tableName = table.Attribute("name").Value;
sqlDb.ExecuteQuery(new MsSqlQuery($"IF OBJECT_ID(\'{tableName}\', 'U') IS NOT NULL DROP TABLE {tableName}"));
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE ");
sb.Append(tableName);
sb.Append(" (");
bool first = true;
foreach (var tablePart in table.Elements())
{
if (!first)
{
sb.Append(", ");
}
else
{
first = false;
}
if (tablePart.Name == "column")
{
sb.Append(tablePart.Attribute("name").Value);
sb.Append(' ');
sb.Append(tablePart.Attribute("type").Value);
sb.Append(' ');
if (tablePart.Attribute("nullable") != null && tablePart.Attribute("nullable").Value == "true")
{
sb.Append("NULL");
}
else
{
sb.Append("NOT NULL");
}
}
else
{
throw new Exception(String.Format("Unknown table part {1} when creating table {0}", tableName, tablePart.Name));
}
}
sb.Append(")");
string primaryKeyName = "pk_" + tableName;
RemovePrimaryKey(sqlDb, tableName, primaryKeyName);
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
AddPrimaryKey(sqlDb, tableName, primaryKeyName, table);
}
private static void RemovePrimaryKey(MsSqlDatabase sqlDb, string tableName, string primaryKeyName)
{
StringBuilder sb = new StringBuilder();
sb.Append("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'");
sb.Append(primaryKeyName);
sb.Append("') AND type in (N'U'))");
sb.AppendLine();
sb.Append("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" DROP CONSTRAINT ");
sb.Append(primaryKeyName);
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
}
private static void AddPrimaryKey(MsSqlDatabase sqlDb, string tableName, string primaryKeyName, XElement table)
{
StringBuilder sb = new StringBuilder();
sb.Append("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" ADD CONSTRAINT ");
sb.Append(primaryKeyName);
sb.Append(" PRIMARY KEY (");
bool first = true;
foreach (var tablePart in table.Elements())
{
if (tablePart.Name == "column" &&
tablePart.Attribute("primary-key") != null &&
tablePart.Attribute("primary-key").Value == "true")
{
if (!first)
{
sb.Append(", ");
}
else
{
first = false;
}
sb.Append(tablePart.Attribute("name").Value);
}
}
if (!first)
{
sb.Append(")");
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
}
}
private static void ExecuteQuery(MsSqlDatabase sqlDb, XElement query)
{
sqlDb.ExecuteQuery(new MsSqlQuery(query.Value));
}
