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; } } }