public void GetSql_MultipleIdParameters_ShouldIncludeInDeleteWhereClauses()
{
//Assemble
var idProperties =
_multipleIdsInfo.Properties.Where(x => x.IsIdProperty).ToList();
var procName =
$"{_schema}.{_multipleIdsInfo.SqlTableName}_get_by_id";
var builder = new StringBuilder();
builder.Append($"\tWHERE{_nl}");
builder.Append(
$"\t\t[{idProperties[0].SqlName}] = @{idProperties[0].SqlName},{_nl}"
);
builder.Append(
$"\t\t[{idProperties[1].SqlName}] = @{idProperties[1].SqlName}{_nl}"
);
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{procName}.sql"
};
var generator = new SqlGenerator(_multipleIdsInfo, _schema);
//Act
var actual = generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Contain(expected.Content);
}
/// <summary>
/// If there is any table updates that need to be applied, calling this method will apply
/// each update until the specifed update version
/// </summary>
public void MigrateTables(Version ToVersion)
{
// If we dont need updated, what are we doing here?
if (ToVersion.CompareTo(LatestVersion) < 0)
{
return;
}
// Make sure version is valid
if (!VersionList.Contains(ToVersion))
{
throw new ArgumentException("Supplied version does not exist as one of the migratable versions", "ToVersion");
}
// Get our start and stop indexies
int start = Array.IndexOf(VersionList, Version);
int end = Array.IndexOf(VersionList, ToVersion);
// Loop until we are at the specifed version
for (int i = start; i <= end; i++)
{
// Apply updates till we reach the version we want
using (DbTransaction Transaction = base.BeginTransaction())
{
try
{
// Get our version string
Version nextVersion = VersionList[i];
// Gets Table Queries
string resource = $"BF2Statistics.SQL.Stats.{DatabaseEngine}_{nextVersion}_update.sql";
List <string> Queries = SqlFile.ExtractQueries(Program.GetResourceFileLines(resource));
// Delete old version data
base.Execute("DELETE FROM _version");
// Insert rows
foreach (string Query in Queries)
{
base.Execute(Query);
}
// Insert New Data
base.Execute("INSERT INTO _version(dbver, dbdate) VALUES (@P0, @P1)", nextVersion, DateTime.UtcNow.ToUnixTimestamp());
Transaction.Commit();
}
catch
{
Transaction.Rollback();
throw;
}
}
// Set new version
GetTablesVersion();
}
}
public void run(SqlFile sql)
{
using (var command = connection.CreateCommand())
{
command.CommandText = sql.raw_sql();
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
public void run(SqlFile file)
{
using (var command = command_factory.create())
{
if (command.run("select * from migration_scripts").Any(x => !file.is_greater_than(x["version"].convert_to<int>()))) return;
command.run(file);
}
}
public static Task RequestRefleshRepos()
{
return(Task.Run(() =>
{
var pathes = SqlFile.ListupPathes();
var loader = Settings.Default.SqlFile_Format == "json" ? SqlIndexLoader.FromJsonFiles(pathes) : SqlIndexLoader.FromCsvFiles(pathes);
SqlRepository.Clear();
SqlRepository.Load(loader);
}));
}
public void GetSql_ShouldGenerateUpdateProc()
{
//Assemble
var idProperty = _simpleInfo.Properties[0];
var procName = $"{_schema}.{_simpleInfo.SqlTableName}_update";
var builder = new StringBuilder();
builder.Append(
$"CREATE OR ALTER PROCEDURE {procName}{_nl}"
);
builder.Append($"\t@{idProperty.SqlName} {idProperty.SqlType},{_nl}");
builder.Append(
$"\t@{_simpleInfo.Properties[1].SqlName} {_simpleInfo.Properties[1].SqlType},{_nl}"
);
builder.Append(
$"\t@{_simpleInfo.Properties[2].SqlName} {_simpleInfo.Properties[2].SqlType}{_nl}"
);
builder.Append($"AS{_nl}");
builder.Append($"BEGIN{_nl}");
builder.Append(
$"\tUPDATE {_schema}.{_simpleInfo.SqlTableName}{_nl}"
);
builder.Append($"\tSET{_nl}");
builder.Append(
$"\t\t[{_simpleInfo.Properties[1].SqlName}] = @{_simpleInfo.Properties[1].SqlName},{_nl}"
);
builder.Append(
$"\t\t[{_simpleInfo.Properties[2].SqlName}] = @{_simpleInfo.Properties[2].SqlName}{_nl}"
);
builder.Append($"\tWHERE{_nl}");
builder.Append(
$"\t\t[{idProperty.SqlName}] = @{idProperty.SqlName}{_nl}"
);
builder.Append("END");
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{procName}.sql"
};
//Act
var actual = _generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Be(expected.Content);
}
public void GetSql_ShouldGenerateCreateProc()
{
//Assemble
var procName = $"{_schema}.{_simpleInfo.SqlTableName}_insert";
var builder = new StringBuilder();
builder.Append(
$"CREATE OR ALTER PROCEDURE {procName}{_nl}"
);
builder.Append(
$"\t@{_simpleInfo.Properties[1].SqlName} {_simpleInfo.Properties[1].SqlType},{_nl}"
);
builder.Append(
$"\t@{_simpleInfo.Properties[2].SqlName} {_simpleInfo.Properties[2].SqlType}{_nl}"
);
builder.Append($"AS{_nl}");
builder.Append($"BEGIN{_nl}");
builder.Append(
$"\tINSERT {_schema}.{_simpleInfo.SqlTableName} ({_nl}"
);
builder.Append($"\t\t[{_simpleInfo.Properties[1].SqlName}],{_nl}");
builder.Append($"\t\t[{_simpleInfo.Properties[2].SqlName}]{_nl}");
builder.Append($"\t){_nl}");
builder.Append($"\tVALUES ({_nl}");
builder.Append($"\t\t@{_simpleInfo.Properties[1].SqlName},{_nl}");
builder.Append($"\t\t@{_simpleInfo.Properties[2].SqlName}{_nl}");
builder.Append($"\t);{_nl}");
builder.Append($"{_nl}");
builder.Append($"\tSELECT SCOPE_IDENTITY(){_nl}");
builder.Append("END");
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{procName}.sql"
};
//Act
var actual = _generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Be(expected.Content);
}
public void GetSql_ShouldGenerateGetAllProc()
{
var idProperty = _simpleInfo.Properties[0];
var procName = $"{_schema}.{_simpleInfo.SqlTableName}_get_all";
var builder = new StringBuilder();
builder.Append(
$"CREATE OR ALTER PROCEDURE {procName}{_nl}"
);
builder.Append($"AS{_nl}");
builder.Append($"BEGIN{_nl}");
builder.Append($"\tSELECT{_nl}");
builder.Append(
$"\t\t[{idProperty.CSharpName}] = [{idProperty.SqlName}],{_nl}"
);
builder.Append(
$"\t\t[{_simpleInfo.Properties[1].CSharpName}] = [{_simpleInfo.Properties[1].SqlName}],{_nl}"
);
builder.Append(
$"\t\t[{_simpleInfo.Properties[2].CSharpName}] = [{_simpleInfo.Properties[2].SqlName}]{_nl}"
);
builder.Append($"\tFROM {_schema}.{_simpleInfo.SqlTableName}{_nl}");
builder.Append("END");
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{procName}.sql"
};
//Act
var actual = _generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Be(expected.Content);
}
public void GetSql_PropertyIsNonNullable_TableDefinitionShouldHaveNullableColumn()
{
//Assemble
var type = new ClassInfo
{
CSharpClassName = "TestClass",
Properties = new List <PropertyInfo>
{
new PropertyInfo
{
CSharpName = "Id",
ValidType = ValidType.Int,
IsNullable = false,
SqlName = "test_property",
SqlType = "INT NOT NULL"
},
},
SqlTableName = "test_class"
};
var expected = new SqlFile
{
Content = "\ttest_property INT NOT NULL",
Name = $"{_schema}.{_multipleIdsInfo.SqlTableName}.sql"
};
var generator = new SqlGenerator(type, _schema);
//Act
var actual = generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Contain(expected.Content);
}
public void GetSql_ShouldGenerateTableDefiniton()
{
var idProperty = _simpleInfo.Properties[0];
var procName = $"{_schema}.{_simpleInfo.SqlTableName}";
var builder = new StringBuilder();
builder.Append($"CREATE TABLE {_simpleInfo.SqlTableName} ({_nl}");
builder.Append(
$"\t{idProperty.SqlName} {idProperty.SqlType},{_nl}"
);
builder.Append(
$"\t{_simpleInfo.Properties[1].SqlName} {_simpleInfo.Properties[1].SqlType},{_nl}"
);
builder.Append(
$"\t{_simpleInfo.Properties[2].SqlName} {_simpleInfo.Properties[2].SqlType},{_nl}"
);
builder.Append(
$"\tCONSTRAINT pk_{_simpleInfo.SqlTableName} PRIMARY KEY ({idProperty.SqlName}){_nl}"
);
builder.Append(")");
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{procName}.sql"
};
//Act
var actual = _generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Be(expected.Content);
}
public void GetSql_MultipleIdParameters_ShouldGenerateTableDefiniton()
{
var idProperties =
_multipleIdsInfo.Properties.Where(x => x.IsIdProperty).ToList();
var builder = new StringBuilder();
builder.Append(
$"\t{idProperties[0].SqlName} {idProperties[0].SqlType},{_nl}"
);
builder.Append(
$"\t{idProperties[1].SqlName} {idProperties[1].SqlType},{_nl}"
);
builder.Append(
$"\t{_multipleIdsInfo.Properties[2].SqlName} {_multipleIdsInfo.Properties[2].SqlType},{_nl}"
);
builder.Append(
$"\tCONSTRAINT pk_{_multipleIdsInfo.SqlTableName} PRIMARY KEY ({idProperties[0].SqlName}, {idProperties[1].SqlName})"
);
var expected = new SqlFile
{
Content = builder.ToString(),
Name = $"{_schema}.{_multipleIdsInfo.SqlTableName}.sql"
};
var generator = new SqlGenerator(_multipleIdsInfo, _schema);
//Act
var actual = generator.GetSql();
//Assert
actual.Should().Contain(x => x.Name.Equals(expected.Name));
actual
.Where(x => x.Name.Equals(expected.Name))
.First().Content
.Should()
.Contain(expected.Content);
}
/// <summary>
/// On a new Mysql database, this method will create the default tables
/// </summary>
private void CreateMysqlTables(IProgress <TaskProgressUpdate> TaskProgress)
{
// Show Progress Form
if (TaskProgress != null)
{
TaskProgress.Report(new TaskProgressUpdate("Creating Stats Tables", "Creating Bf2Stats Mysql Tables..."));
}
// Gets Table Queries
string[] SQL = Program.GetResourceFileLines("BF2Statistics.SQL.MySQL.Stats.sql");
List <string> Queries = SqlFile.ExtractQueries(SQL);
// Start Transaction
using (DbTransaction Transaction = BeginTransaction())
{
// Attempt to do the transaction
try
{
// Create Tables
foreach (string Query in Queries)
{
base.Execute(Query);
}
// Commit
Transaction.Commit();
}
catch
{
Transaction.Rollback();
throw;
}
}
// Update status
if (TaskProgress != null)
{
TaskProgress.Report(new TaskProgressUpdate("Inserting Ip2Nation Data"));
}
// WE STILL INSTALL ip2Nation DATA to stay compatible with the web ASP
SQL = Program.GetResourceFileLines("BF2Statistics.SQL.Ip2nation.sql");
Queries = SqlFile.ExtractQueries(SQL);
// Insert Ip2Nation data
using (DbTransaction Transaction = BeginTransaction())
{
// Attempt to do the transaction
try
{
// Insert rows
foreach (string Query in Queries)
{
base.Execute(Query);
}
// Commit
Transaction.Commit();
}
catch
{
Transaction.Rollback();
throw;
}
}
}
