hatsuseno's blog

yapb - yet another programming blog

Building a custom SQL::Parser

2014-01-10 by hatsuseno, tagged as parsing, perl, sql

SQL::Parser is a pure-perl library that, sort of, parses generic SQL strings into a pseudo-AST, in the form of a nested hash.

Since all the cool kids in town (I'm looking at you, Atlassian) are developing in-house query languages to ease searching and database-poking from an end-user perspective, at my job we thought the same thing. Thus was born xQL (replace x with the first letter of your product).

The first thing I did was strip all the ANSI-SQL keywords and commands from the parser rules, and here we run into the first 'problem'. SQL::Parser holds that no new dialects would ever be added to the parsing rules, so adding a simplified set of keywords meant I had to hijack the namespace and extend it like SQL::Dialects::xQL. The contents couldn't be much simpler, though, see the source.

Usage

use Data::Dumper;
use SQL::Parser;

my $parser = SQL::Parser->new('xQL'); # This mirrors the naming of your
                                      # dialect package

$parser->parse('SELECT * FROM thing WHERE id = 1;');

print Dumper($parser->structure);

Output

$VAR1 = {
          'column_aliases' => {},
          'column_defs' => [
                             {
                               'type' => 'column',
                               'value' => '*'
                             }
                           ],
          'command' => 'SELECT',
          'dialect' => 'xQL',
          'list_ids' => [],
          'org_table_names' => [
                                 'case'
                               ],
          'original_string' => 'SELECT * FROM case WHERE id LIKE 1',
          'table_alias' => {},
          'table_names' => [
                             'case'
                           ],
          'where_clause' => {
                              'arg1' => {
                                          'fullorg' => 'id',
                                          'type' => 'column',
                                          'value' => 'id'
                                        },
                              'arg2' => {
                                          'fullorg' => '1',
                                          'type' => 'number',
                                          'value' => '1'
                                        },
                              'neg' => 0,
                              'nots' => {},
                              'op' => '='
                            },
          'where_cols' => {
                            'id' => [
                                    '1'
                                  ]
                          }
        };

Attachments

package SQL::Dialects::xQL;

use SQL::Dialects::Role;

sub get_config {
    # slurp __DATA__ blob
    do { local $/; <DATA> };
}

1;

__DATA__
[VALID COMMANDS]
SELECT

[VALID OPTIONS]
SELECT_MULTIPLE_TABLES

[VALID COMPARISON OPERATORS]
=
<>
<
<=
>
>=
LIKE
NOT LIKE
IS
IS NOT
IN
NOT IN
BETWEEN
NOT BETWEEN

[VALID DATA TYPES]
VARCHAR
DECIMAL
INTEGER
FLOAT
DATE
INTERVAL

[RESERVED WORDS]
AND
OR
XQL
VALUE
VALUES
ORDER