SQL Parsing

[Chris Anders]

How can i Parse SQL?

[Matthew Lock]

You need to implement the BNF grammar of SQL. Be warned though, it's huge! http://savage.net.au/SQL/sql-92.bnf

You might be able to implement a subset of SQL which is a lot smaller http://docs.killianfaughnan.com/csvsqldocs/x235.html

You may also be able to use this project to get the parse tree of T-SQL and save yourself a lot of bother http://architectshack.com/PoorMansTSqlFormatter.ashx#Overview_4

[Chris Anders]

i´ve stried to use ebnf grammar of sql, but it gives an error: Error parsing ebnf:

Index=0, Context=">>>???

comma"

Expected:

meta identifier: Repeat: Sequence

syntax rule: Sequence

the ebnf file: http://pastebin.com/e6wMk1ML

[Curtis Wensley]

Hi Chris,

It looks like there's a few problems with the grammar:

1. The file (probably) has a BOM at the top of the file which is what it is running into (the ???)'

2. Eto.Parse currently only supports Iso format EBNF, though it looks at though the grammar is in W3c style.

I might have an update for #2 to support w3c style in the near future, but for now you'd have to change it to Iso format.

Hope this helps!

Curtis.

[Curtis Wensley]

Ok, great news!

I've managed to get a few spare minutes to update the EbnfGrammar to support w3c style, and even a combination of styles (such as the SQL grammar you linked to).

The SQL grammar is very similar to w3c style, but it uses square brackets to denote optional sequences, whereas w3c uses them as character sets.

To see how you can parse this grammar, check it out here in the tests.

Happy parsing!

Curtis.

[Chris Anders]

const string sqlStatement = @"select * from MyTable where MyColumn = hello";

            string gr = Encoding.UTF8.GetString(Resources.grammar);

           var ebnfGrammar = new Eto.Parse.Grammars.EbnfGrammar(EbnfStyle.W3c | EbnfStyle.SquareBracketAsOptional | EbnfStyle.WhitespaceSeparator);

            var Grammar = ebnfGrammar.Build(gr, "command");
            var match = Grammar.Match(sqlStatement);

give the error: An unhandled exception of type 'System.FormatException' occurred in etosql.exe

Additional information: Error parsing ebnf:

Index=0, Context=">>>command :"

Expected:

meta identifier: Sequence

syntax rule

[Chris Anders]

and when i test your original code, it works but not correct. every character has his own match for the tablename

[Curtis Wensley]

This is because the grammar itself is written this way (it is not the best looking SQL grammar)... for example:

update_command ::=
    (""UPDATE""|""update"")
    (character_set)+
    (""SET""|""set"")
    (character_set)+
    ""=""
    (character_set)+
    [(""WHERE""|""where"") condition]

Notice the (character_set)+.  This will add multiple character_set matches to the update_command which is not ideal for parsing afterwards.

If you change this to something like:

table_name ::= (character_set)+
column_name ::= (character_set)+
update_value ::= (character_set)+
update_command ::=
    (""UPDATE""|""update"")
    table_name
    (""SET""|""set"")
    column_name
    ""=""
    update_value
    [(""WHERE""|""where"") condition]

Then you will get named matches for each child that you are interested in.

Hope this helps!

Curtis.

[Chris Anders]

ive solved with a new literal string.

ive tried to build an AST-Builder.

It creates the correct class and sets correct a string property, but when i have another AST-Object it doesnt work.

using System;
using System.Reflection;
using Eto.Parse;

namespace etosql
{
    public class SyntaxTree
    {
        public static object Build(Match m)
        {
            foreach (var mm in m.Matches)
            {
                foreach (var mmm in mm.Matches)
                {
                    foreach (var type in Assembly.GetEntryAssembly().GetTypes())
                    {
                        if (type.BaseType != null) {
                            if (type.BaseType.Name == typeof(IAst).Name)
                            {
                                var obj = (IAst)Activator.CreateInstance(type);

                                if (obj.Name == mm.Name)
                                {
                                    var props = type.GetProperties();
                                    for (int i = 0; i < props.Length; i++)
                                    {
                                        if (props[i].PropertyType.BaseType.Name == typeof(IAst).Name)
                                        {
                                            props[i].SetValue(obj, SyntaxTree.Build(mm), null);

                                            continue;
                                        }
                                        else if (props[i].Name == mmm.Name)
                                        {
                                            props[i].SetValue(obj, mmm.StringValue, null);
                                        }
                                    }
                                    return obj;
                                }
                            }
                        }
                    }
                }
            }

            return null;
        }
    }
}Code hier eingeben...