using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using JetBrains.Annotations;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using tbfDBBackup.compare_dumps;
using tbfDBBackup.compare_dumps.Parserer;
namespace tbfDBBackup.Tests.compare_dumps;
[TestClass]
[TestSubject(typeof(SqlDumpParser))]
public class SqlDumpParserTest
{
[TestMethod]
public void ParseInsertBlock()
{
string sqlDump = "INSERT INTO `componentprocedure` (`Id`, `CmpntName`, `Parameters`, `Procedure_id`) " +
"VALUES\n(1, 'WM1', '\\r\\n\\r\\n " +
"\\r\\n \\r\\n 0\\r\\n 0\\r\\n NotSpecified\\r\\n 4\\r\\n 2.5\\r\\n 0.005\\r\\n 0.003125\\r\\n A\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n \\r\\n \\r\\n 1\\r\\n \\r\\n \\r\\n \\r\\n \\r\\n \\r\\n ColdWater\\r\\n', 1),\n(2, 'WM2', '\\r\\n\\r\\n \\r\\n \\r\\n 0\\r\\n 0\\r\\n NotSpecified\\r\\n 4\\r\\n 2.5\\r\\n 0.005\\r\\n 0.003125\\r\\n A\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n NotSpecified\\r\\n " +
"\\r\\n \\r\\n 1\\r\\n \\r\\n \\r\\n \\r\\n \\r\\n \\r\\n " +
"ColdWater\\r\\n', 1);";
Dictionary> dictionary = SqlDumpParser.ParseInsertBlock(sqlDump);
Assert.AreEqual(2, dictionary.Count);
}
string GetProjectRoot() {
string dir = AppContext.BaseDirectory;
while (dir != null && !Directory.GetFiles(dir, "*.csproj").Any()) {
dir = Directory.GetParent(dir)?.FullName;
}
return dir ?? throw new Exception("Project root not found.");
}
[TestMethod]
public void ParseInsertBlock_File()
{
string projectRoot = GetProjectRoot();
string sql = File.ReadAllText( Path.Combine(projectRoot, "Resources","test_Block.sql"));
var parsed = SqlDumpParser.ParseInsertBlock(sql);
foreach (var kvp in parsed)
{
Console.WriteLine($"ID {kvp.Key}:");
//Get first 50 chars for all colums
foreach (var col in kvp.Value)
Console.WriteLine($" {col.Key} = {col.Value.Substring(0, Math.Min(80, col.Value.Length))}...");
}
}
[TestMethod]
public void ParseInsertBlock_File_LargeInsert()
{
string projectRoot = GetProjectRoot();
string sql = File.ReadAllText( Path.Combine(projectRoot, "Resources","test_Block_LageInsert.sql"));
string sqlCreate = File.ReadAllText( Path.Combine(projectRoot, "Resources","test_Block_LargeCreate.sql"));
var parsed = SqlDumpParser.ParseInsertBlock(sql,sqlCreate);
foreach (var kvp in parsed)
{
Console.WriteLine($"ID {kvp.Key}:");
//Get first 50 chars for all colums
foreach (var col in kvp.Value)
Console.WriteLine($" {col.Key} = {col.Value.Substring(0, Math.Min(80, col.Value.Length))}...");
}
Assert.AreEqual(4539, parsed.Last().Key);
}
[TestMethod]
public void ParseForeignKeys()
{
string alter = "ALTER TABLE `componentprocedure`\n ADD CONSTRAINT `FKF52C29EE1EC52E2B` FOREIGN KEY (`Procedure_id`) REFERENCES `procedure` (`Id`);";
Dictionary> foreignKeys = SqlDumpParser.ParseForeignKeys(alter);
Assert.AreEqual(1, foreignKeys.Count);
Assert.AreEqual(1, foreignKeys.First().Value.Count);
Assert.AreEqual("componentprocedure", foreignKeys.First().Key);
Assert.AreEqual("FKF52C29EE1EC52E2B", foreignKeys.First().Value.First().Name);
Assert.AreEqual("Procedure_id", foreignKeys.First().Value.First().Column);
Assert.AreEqual("procedure", foreignKeys.First().Value.First().RefTable);
Assert.AreEqual("Id", foreignKeys.First().Value.First().RefColumn);
}
[TestMethod]
public void ParseCreateSqlToRows()
{
string slqCreate = "CREATE TABLE `componentprocedure` (\n `Id` int NOT NULL AUTO_INCREMENT,\n `CmpntName` varchar(255) NOT NULL,\n `Parameters` longtext NOT NULL,\n `Procedure_id` int NOT NULL,\n PRIMARY KEY (`Id`),\n KEY `IDX_F52C29EE1EC52E2B` (`Procedure_id`),\n CONSTRAINT `FKF52C29EE1EC52E2B` FOREIGN KEY (`Procedure_id`) REFERENCES `procedure` (`Id`)\n) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;";
string[] columns = SqlDumpParser.ParseCreateSqlToRows(slqCreate);
Assert.AreEqual(4, columns.Length);
}
[TestMethod]
public void ParseCreateSqlToRows2()
{
string slqCreate = "CREATE TABLE procedure ( Id int(11) NOT NULL AUTO_INCREMENT, ItemNr int(11) DEFAULT NULL, Name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, Description varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, CreationUser varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, CreationTime datetime DEFAULT NULL, LastChgUser varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, LastChgTime datetime DEFAULT NULL, LongDescription varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, ProcedureState varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, Revision int(11) DEFAULT NULL, PredecessorId int(11) DEFAULT NULL, ObtainedByCopy tinyint(1) DEFAULT NULL, MetersKind varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, Watermeters varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, ProtocolTitle varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, Protected tinyint(1) NOT NULL DEFAULT '0', MustBeComplete tinyint(1) NOT NULL DEFAULT '0', ManualCtrlDisabled tinyint(1) NOT NULL DEFAULT '0', DataEntry varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, ResultsPrinter varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, ResultsWriter varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, EventTriggers varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, TransitionStart varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, TransitionEnd varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, AltProcName varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, AltProcPeriod int(11) NOT NULL DEFAULT '0', PRIMARY KEY (Id) ) ENGINE=InnoDB AUTO_INCREMENT=4549 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
string[] columns = SqlDumpParser.ParseCreateSqlToRows(slqCreate);
Assert.AreEqual(27, columns.Length);
}
}