tbfDBBackup/tbfDBBackup.Tests/compare_dumps/Parserer/SqlDumpFileParserTest.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using JetBrains.Annotations;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using tbfDBBackup.compare_dumps.Parserer;

namespace tbfDBBackup.Tests.compare_dumps.Parserer;

[TestClass]
[TestSubject(typeof(SqlDumpFileParser))]
public class SqlDumpFileParserTest
{

    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 SqlDumpParserFile()
    {
        string basePath = GetProjectRoot(); 
        string sql = Path.Combine(basePath,"Resources/old.sql");
        
        ValidateSqlDumpParserResults(sql);
    }
    
    [TestMethod]
    public void SqlDumpParserFile2()
    {
        string basePath = GetProjectRoot(); 
        string sql = Path.Combine(basePath,"Resources/new.sql");
        
        ValidateSqlDumpParserResults(sql);
    }
    
    [TestMethod]
    public void SqlDumpParserFileR()
    {
        string basePath = GetProjectRoot(); 
        string sql = Path.Combine(basePath,"Resources/old-r.sql");
        
        ValidateSqlDumpParserResults(sql);
    }
    
    [TestMethod]
    public void SqlDumpParserFileR2()
    {
        string basePath = GetProjectRoot(); 
        string sql = Path.Combine(basePath,"Resources/new-r.sql");
        
        ValidateSqlDumpParserResults(sql);
    }
    
    [TestMethod]
    public void SqlDumpParserFile_2()
    {
        string basePath = GetProjectRoot(); 
        string sql = Path.Combine(basePath,"Resources","slm50-250709-1549.sql");
        
        ValidateSqlDumpParserResults(sql, false);
        
        SqlDumpFileParser parser = new();
        parser.SqlDumpParserFile(sql);

        List<ForeignKeyDef> foreignKeyDefList = new();
        foreach (var parserTableDefinition in parser.TableDefinitions)
        {
            List<ForeignKeyDef> foreignKeyDefs = parser.GetAllForeignKeysByTableName(parserTableDefinition.Key);
            Console.WriteLine($" Table {parserTableDefinition.Key} has foreign keys count: {foreignKeyDefs.Count}");
            foreignKeyDefList.AddRange(foreignKeyDefs);
        }

        Assert.IsTrue(foreignKeyDefList.Count > 0);
    }

    private static void ValidateSqlDumpParserResults(string sql, bool testForineKeys = true)
    {
        SqlDumpFileParser parser = new();
        parser.SqlDumpParserFile(sql);

        Assert.IsTrue(parser.TableDefinitions.Count > 1);
        foreach (var table in parser.TableDefinitions)
        {
            Console.WriteLine(table.Key);
            Assert.IsTrue(table.Value.Substring(0, Math.Min(50, table.Value.Length)).Contains("CREATE"));
        }

        Assert.IsTrue(parser.TableInserts.Count > 1);
        
        foreach (var table in parser.TableInserts)
        {
            Console.WriteLine(table.Key);
            foreach (var row in table.Value)
            {
                Assert.IsTrue(row.Substring(0, Math.Min(20, row.Length-1)).Contains("INSERT INTO"));
                string restString = row.Substring(20);
                if (restString.Contains("INSERT INTO"))
                {
                    Assert.Fail($"Insert contains insert more than once, see: {row}");
                }
                
                string lastCharacters = row.Length >= 5 ? row.Substring(row.Length - 5) : "";
                Assert.IsTrue(lastCharacters.Contains(';'));
            }
        }

        if (testForineKeys)
        {
            Assert.IsTrue(parser.TableForeignKeys.Count > 1);

            foreach (var table in parser.TableForeignKeys)
            {
                Console.WriteLine(table.Key);
                foreach (var row in table.Value)
                {
                    //check if it contains alter table
                    Assert.IsTrue(row.Substring(0, Math.Min(20, row.Length - 1)).Contains("ALTER TABLE"));
                    //check rest of string for alter table - non valid sql
                    string restString = row.Substring(20);
                    if (restString.Contains("ALTER TABLE"))
                    {
                        Assert.Fail($"Alter contains insert more than once, see: {row}");
                    }

                    //check if it ends with ;
                    string lastCharacters = row.Length >= 5 ? row.Substring(row.Length - 5) : "";
                    Assert.IsTrue(lastCharacters.Contains(';'));
                }
            }
        }
    }

    [TestMethod]
    public void ExtractForeignKeysFromCreateTable_Test()
    {
        string sql = "CREATE TABLE `usersgroups` (\n`Group_id` int(11) NOT NULL,\n`User_id` int(11) NOT NULL,\nKEY `User_id` (`User_id`),\nKEY `Group_id` (`Group_id`),\nCONSTRAINT `FKEC3AF23373767C99` FOREIGN KEY (`User_id`) REFERENCES `user` (`Id`),\nCONSTRAINT `FKEC3AF23393C4061C` FOREIGN KEY (`Group_id`) REFERENCES `group` (`Id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        List<ForeignKeyDef> list = SqlDumpFileParser.ExtractForeignKeysFromCreateTable(sql);
        list.ForEach(FK => Console.WriteLine(FK.ToString()));
        
        Assert.IsTrue(list.Count == 2);
        
    }
}