Support comparing script directories as the either the source or the target

[sethreno]

I found this example that might help: http://agimatec-tools.googlecode.com/svn/trunk/dbmigrate/src/main/java/com/agimatec/sql/script/SQLScriptParser.java

[sethreno]

also, see http://stackoverflow.com/questions/7377344/how-do-i-write-a-parser-in-c

[keith-hall]

There is a TSQL parser as part of the SQL Server SDK that we can use, which should save a lot of effort :) here is an example of how to use it:

using Microsoft.SqlServer.TransactSql.ScriptDom; // from C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll
IList<ParseError> errors;
TSqlFragment script = new TSql120Parser(initialQuotedIdentifiers: false).Parse(new StringReader("SELECT * FROM [dbo].Test"), out errors);
((TSqlScript)script).Batches.First().ScriptTokenStream.Dump();
errors.Dump();

[keith-hall]

class Program
{
    static void Main(string[] args)
    {
        TextReader txtRdr = new StringReader(@"
/*
some example select statements to show that a TSqlFragmentVisitor is pretty powerful...
we just need to override ExplicitVisit for all the nodes we are interested in to build our Database model :)
*/
SELECT * FROM Test
SELECT * from Test2

-- a create table statement
create table dbo.[testing] (id int identity(1,1), text varchar(100))
");
        var parser = new TSql110Parser(true);

        IList<ParseError> errors;
        var sqlFragment = parser.Parse(txtRdr, out errors);
        // TODO report the parsing errors generated (if any)

        SQLVisitor myVisitor = new SQLVisitor();
        sqlFragment.Accept(myVisitor);
    }
}

internal class SQLVisitor : TSqlFragmentVisitor
{
    private Database _db = new Database();

    private string GetNodeTokenText(TSqlFragment fragment)
    {
        StringBuilder tokenText = new StringBuilder();
        for (int counter = fragment.FirstTokenIndex; counter <= fragment.LastTokenIndex; counter++)
        {
            tokenText.Append(fragment.ScriptTokenStream[counter].Text);
        }

        return tokenText.ToString();
    }

    const string defaultSchema = "dbo";
    public override void ExplicitVisit(CreateTableStatement node)
    {
        //node.SchemaObjectName.Identifiers.Select (i => i.Value).Dump();
        var t = new Table(node.SchemaObjectName.SchemaIdentifier?.Value ?? defaultSchema, node.SchemaObjectName.BaseIdentifier.Value);
        node.Definition.ColumnDefinitions.Dump();
        _db.Tables.Add(t);
    }
}

[sethreno]

@MisterY requested this feature in #47.

[alensiljak]

Yes. To be precise, I would like to compare a database to the script directory. Reading this title initially, I was under impression that this would compare two script directories or something like that.

[sethreno]

Thanks @MisterY. I edited the title to try and make it more clear. This feature should allow comparing two script directories, or comparing a script directory to a database.

[rubensr]

I've implemented a simple/naive change to allow connection strings of the form "file:<folder>" to support this - different to what has been discussed so far, I actually used existing functionality by creating a SQLLocalDB database on the fly using the folder, and using that to perform the comparison. If anyone is interested, code is here: https://github.com/rubensr/schemazen/tree/compare_directories