This project has moved. For the latest updates, please go here.

SQL Grammar

Topics: Parsers
Aug 19, 2012 at 8:48 AM

Hi

Just come across this as a potential parser solution. Exciting stuff ... I am currently using Irony to create a Sql dialect and am thinking of trying to move across to rxx parsers.

Sql so often seems to be a often created grammar. I was wondering if anyone has created one using rxx  and is willing to share it, to give me a "leg up".

Also, could someone offer some use cases as to when it would be better to use the IObservableParser rather than the IParser.

Many thx

S

Coordinator
Aug 22, 2012 at 12:41 PM

Hi,

I'm not familiar with Irony.  Is the reason that you're interested in Rxx parsers because you need to parse string data?  If not, then have you considered creating an IQueryProvider instead?

I haven't tried defining a SQL grammar in Rxx parsers, but given that SQL itself is a declarative language and Rxx parsers uses LINQ, I'd expect a translation to have high fidelity.

If you have any particular questions, please ask.

> could someone offer some use cases as to when it would be better to use the IObservableParser

The primary difference is that IObservableParser is reactive while IParser is interactive.  So it depends on the source; e.g., IObservable<T> vs. IEnumerble<T>, respectively.

Note that previous testing has shown IParser to be much more performant than IObservableParser.  I don't know yet if that's still true in Rxx 2.0, due to the performance enhancements made in Rx 2.0, but I suspect that it is.

In some real-world cases the performance difference isn't noticeable.  But if performance is a real concern, then consider using IParser instead.  If concurrency is necessary, then you can easily execute an IParser query on a background thread; e.g., if the source is an IObservable<T>, translate it like this:

source.ToEnumerable().ParseString(...).ToObservable(NewThreadScheduler.Default);

- Dave

Aug 28, 2012 at 2:09 PM

Dave, thx very much for your reply

(Sorry, it has taken so long to get back to you,  I have been on holiday!)

Wrt your first q, yes, I am parsing text. Irony provides some classes which effectively permit you to write something like ebnf in c#, and permit the parsing and generation of an AST to represent the statement. I think I'll need to study your miniml sample to try and get a flavour of how to translate to a parser combinator type solution.

Thx again

Coordinator
Aug 28, 2012 at 3:58 PM
Edited Aug 28, 2012 at 3:59 PM

Hi, 

Since your input is a string you should use IParser<T> rather than IObservableParser<T>.

Furthermore, since string implements IEnumerable<T>, the Parse and ParseString extension methods are available directly from string.  The latter is perhaps better because it offers some built-in rules that you may find useful, such as Word and InsignificantWhiteSpace.

I recommend downloading the Rx Labs application from the Downloads area of this project.  It includes several labs that illustrate how to use various aspects of parsers, with source code included.

To get you started, here's an example console app that I just put together:

using System;
using System.Collections.Generic;
using System.Linq;
using Rxx.Parsers.Linq;

namespace TestRx
{
  class SqlParserExperiment
  {
    public void Run()
    {
      var sqls = new[]
      {
        "SELECT * FROM table1", 
        "SELECT ", 
        "INVALID * FROM BLAH", 
        "SELECT Col1 FROM table2", 
        "SELECT Col2, Col3 FROM table3", 
        "SELECT * BLAH invalid", 
        "SELECT * FROM "
      };

      foreach (var statement in sqls
        .Select(ParseSelectStatement)
        .Where(s => s != null))
      {
        Console.ForegroundColor = ConsoleColor.Green;

        Console.WriteLine("{0}: {1}",
          statement.Table,
          statement.Columns.Aggregate("", (acc, cur) => acc + cur + ", "));

        Console.ResetColor();
      }

      Console.ReadKey();
    }

    private Select ParseSelectStatement(string sql)
    {
      Console.WriteLine();
      Console.WriteLine("Parsing: " + sql);

      try
      {
        return sql.ToUpperInvariant().ParseString(parser =>
            from next in parser
            let @select = parser.Word("SELECT")
            let @from = parser.Word("FROM")
            let all = parser.Word("*")
            let term = parser.Character(char.IsLetterOrDigit).OneOrMore().Join()
            let separator = from _ in parser.InsignificantWhiteSpace
                            from __ in parser.Character(',')
                            from ___ in parser.InsignificantWhiteSpace
                            select string.Empty
            let termList = term.OneOrMore(separator)
            select from _ in @select.Required("Unknown SQL statement.")
                   from __ in parser.WhiteSpace
                   from columns in termList.Or(all.Amplify()).Required("Expected common-separated list of columns or \"*\".")
                   from ___ in parser.WhiteSpace
                   from ____ in @from.Required("Expected FROM clause.")
                   from _____ in parser.WhiteSpace
                   from table in term.Required("Expected table name after FROM clause.")
                   select new Select
                   {
                     Columns = columns.ToList(),
                     Table = table
                   })
            .First();
      }
      catch (Exception ex)
      {
        Console.ForegroundColor = ConsoleColor.Red;

        Console.WriteLine(ex.Message);

        Console.ResetColor();

        return null;
      }
    }

    class Select
    {
      public IList<string> Columns { get; set; }
      public string Table { get; set; }
    }
  }
}

 

The example above generates the following output:

Parsing: SELECT * FROM table1
TABLE1: *,

Parsing: SELECT
Expected common-separated list of columns or "*".
Source index: 7.

Parsing: INVALID * FROM BLAH
Unknown SQL statement.
Source index: 0.

Parsing: SELECT Col1 FROM table2
TABLE2: COL1,

Parsing: SELECT Col2, Col3 FROM table3
TABLE3: COL2, COL3,

Parsing: SELECT * BLAH invalid
Expected FROM clause.
Source index: 9.

Parsing: SELECT * FROM
Expected table name after FROM clause.
Source index: 14.

- Dave

Aug 28, 2012 at 5:59 PM

Wow ... gosh ... thank you, Dave ... that's really helpful.

S