[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / SQLServer.pm

package SQL::Translator::Parser::SQLServer;

=head1 NAME

SQL::Translator::Parser::SQLServer - parser for SQL Server

=head1 SYNOPSIS

  use SQL::Translator::Parser::SQLServer;

=head1 DESCRIPTION

Adapted from Parser::Sybase and mostly parses the output of
Producer::SQLServer.  The parsing is by no means complete and
should probably be considered a work in progress.

=cut

use strict;
use warnings;

our $VERSION = '1.59';

our $DEBUG;
$DEBUG   = 0 unless defined $DEBUG;

use Data::Dumper;
use SQL::Translator::Utils qw/ddl_parser_instance/;

use base qw(Exporter);
our @EXPORT_OK = qw(parse);

our $GRAMMAR = <<'END_OF_GRAMMAR';

{
    my ( %tables, @table_comments, $table_order, %procedures, $proc_order, %views, $view_order );

    sub _err {
      my $max_lines = 5;
      my @up_to_N_lines = split (/\n/, $_[1], $max_lines + 1);
      die sprintf ("Unable to parse line %d:\n%s\n",
        $_[0],
        join "\n", (map { "'$_'" } @up_to_N_lines[0..$max_lines - 1 ]), @up_to_N_lines > $max_lines ? '...' : ()
      );
    }

}

startrule : statement(s) eofile
   {
      return {
         tables     => \%tables,
         procedures => \%procedures,
         views      => \%views,
      }
   }

eofile : /^\Z/

statement : create_table
    | create_procedure
    | create_view
    | create_index
    | create_constraint
    | comment
    | disable_constraints
    | drop
    | use
    | setuser
    | if
    | print
    | grant
    | exec
    | /^\Z/ | { _err ($thisline, $text) }

use : /use/i WORD GO
    { @table_comments = () }

setuser : /setuser/i NAME GO

if : /if/i object_not_null begin if_command end GO

if_command : grant
    | create_index
    | create_constraint

object_not_null : /object_id/i '(' ident ')' /is not null/i

field_not_null : /where/i field_name /is \s+ not \s+ null/ix

print : /\s*/ /print/i /.*/

else : /else/i /.*/

begin : /begin/i

end : /end/i

grant : /grant/i /[^\n]*/

exec : exec_statement(s) GO

exec_statement : /exec/i /[^\n]+/

comment : /^\s*(?:#|-{2}).*\n/
    {
        my $comment =  $item[1];
        $comment    =~ s/^\s*(#|--)\s*//;
        $comment    =~ s/\s*$//;
        $return     = $comment;
        push @table_comments, $comment;
    }

comment : comment_start comment_middle comment_end
    {
        my $comment = $item[2];
        $comment =~ s/^\s*|\s*$//mg;
        $comment =~ s/^\**\s*//mg;
        push @table_comments, $comment;
    }

comment_start : m#^\s*\/\*#

comment_end : m#\s*\*\/#

comment_middle : m{([^*]+|\*(?!/))*}

drop : if_exists(?) /drop/i tbl_drop END_STATEMENT

tbl_drop : /table/i ident

if_exists : /if exists/i '(' /select/i 'name' /from/i 'sysobjects' /[^\)]+/ ')'

#
# Create table.
#
create_table : /create/i /table/i ident '(' create_def(s /,/) ')' lock(?) on_system(?) END_STATEMENT
    {
        my $table_owner = $item[3]{'owner'};
        my $table_name  = $item[3]{'name'};

        if ( @table_comments ) {
            $tables{ $table_name }{'comments'} = [ @table_comments ];
            @table_comments = ();
        }

        $tables{ $table_name }{'order'}  = ++$table_order;
        $tables{ $table_name }{'name'}   = $table_name;
        $tables{ $table_name }{'owner'}  = $table_owner;
        $tables{ $table_name }{'system'} = $item[7];

        my $i = 0;
        for my $def ( @{ $item[5] } ) {
            if ( $def->{'supertype'} eq 'field' ) {
                my $field_name = $def->{'name'};
                $tables{ $table_name }{'fields'}{ $field_name } =
                    { %$def, order => $i };
                $i++;

                if ( $def->{'is_primary_key'} ) {
                    push @{ $tables{ $table_name }{'constraints'} }, {
                        type   => 'primary_key',
                        fields => [ $field_name ],
                    };
                }
            }
            elsif ( $def->{'supertype'} eq 'constraint' ) {
                push @{ $tables{ $table_name }{'constraints'} }, $def;
            }
            else {
                push @{ $tables{ $table_name }{'indices'} }, $def;
            }
        }
    }

disable_constraints : if_exists(?) /alter/i /table/i ident /nocheck/i /constraint/i /all/i END_STATEMENT

# this is for the normal case 
create_constraint : /create/i constraint END_STATEMENT
    {
        @table_comments = ();
        push @{ $tables{ $item[2]{'table'} }{'constraints'} }, $item[2];
    }

# and this is for the BEGIN/END case
create_constraint : /create/i constraint
    {
        @table_comments = ();
        push @{ $tables{ $item[2]{'table'} }{'constraints'} }, $item[2];
    }


create_constraint : /alter/i /table/i ident /add/i foreign_key_constraint END_STATEMENT
    {
        push @{ $tables{ $item[3]{name} }{constraints} }, $item[5];
    }


create_index : /create/i index
    {
        @table_comments = ();
        push @{ $tables{ $item[2]{'table'} }{'indices'} }, $item[2];
    }

create_procedure : /create/i PROCEDURE WORD not_go GO
    {
        @table_comments = ();
        my $proc_name = $item[3];
        my $owner = '';
        my $sql = "$item[1] $item[2] $proc_name $item[4]";

        $procedures{ $proc_name }{'order'}  = ++$proc_order;
        $procedures{ $proc_name }{'name'}   = $proc_name;
        $procedures{ $proc_name }{'owner'}  = $owner;
        $procedures{ $proc_name }{'sql'}    = $sql;
    }

create_procedure : /create/i PROCEDURE '[' WORD '].' WORD not_go GO
    {
        @table_comments = ();
        my $proc_name = $item[6];
        my $owner = $item[4];
        my $sql = "$item[1] $item[2] [$owner].$proc_name $item[7]";

        $procedures{ $proc_name }{'order'}  = ++$proc_order;
        $procedures{ $proc_name }{'name'}   = $proc_name;
        $procedures{ $proc_name }{'owner'}  = $owner;
        $procedures{ $proc_name }{'sql'}    = $sql;
    }

PROCEDURE : /procedure/i
   | /function/i

create_view : /create/i /view/i WORD not_go GO
    {
        @table_comments = ();
        my $view_name = $item[3];
        my $sql = "$item[1] $item[2] $item[3] $item[4]";

        $views{ $view_name }{'order'}  = ++$view_order;
        $views{ $view_name }{'name'}   = $view_name;
        $views{ $view_name }{'sql'}    = $sql;
    }

not_go : /((?!\bgo\b).)*/is

create_def : constraint
    | index
    | field

blank : /\s*/

field : field_name data_type field_qualifier(s?)
    {
        my %qualifiers  = map { %$_ } @{ $item{'field_qualifier(s?)'} || [] };
        my $nullable = defined $qualifiers{'nullable'}
                   ? $qualifiers{'nullable'} : 1;
        $return = {
            supertype      => 'field',
            name           => $item{'field_name'},
            data_type      => $item{'data_type'}{'type'},
            size           => $item{'data_type'}{'size'},
            nullable       => $nullable,
            default        => $qualifiers{'default_val'},
            is_auto_inc    => $qualifiers{'is_auto_inc'},
#            is_primary_key => $item{'primary_key'}[0],
        }
    }

field_qualifier : nullable
    {
        $return = {
             nullable => $item{'nullable'},
        }
    }

field_qualifier : default_val
    {
        $return = {
             default_val => $item{'default_val'},
        }
    }

field_qualifier : auto_inc
    {
        $return = {
             is_auto_inc => $item{'auto_inc'},
        }
    }

constraint : primary_key_constraint
    | foreign_key_constraint
    | unique_constraint

field_name : WORD
   { $return = $item[1] }
   | LQUOTE WORD RQUOTE
   { $return = $item[2] }

index_name : WORD
   { $return = $item[1] }
   | LQUOTE WORD RQUOTE
   { $return = $item[2] }

table_name : WORD
 { $return = $item[1] }
 | LQUOTE WORD RQUOTE
 { $return = $item[2] }

data_type : WORD field_size(?)
    {
        $return = {
            type => $item[1],
            size => $item[2][0]
        }
    }

lock : /lock/i /datarows/i

field_type : WORD

field_size : '(' num_range ')' { $item{'num_range'} }

num_range : DIGITS ',' DIGITS
    { $return = $item[1].','.$item[3] }
               | DIGITS
    { $return = $item[1] }


nullable : /not/i /null/i
    { $return = 0 }
    | /null/i
    { $return = 1 }

default_val : /default/i /null/i
    { $return = 'null' }
   | /default/i /'[^']*'/
    { $item[2]=~ s/'//g; $return = $item[2] }
   | /default/i WORD
    { $return = $item[2] }

auto_inc : /identity/i { 1 }

primary_key_constraint : /constraint/i index_name(?) /primary/i /key/i parens_field_list
    {
        $return = {
            supertype => 'constraint',
            name      => $item[2][0],
            type      => 'primary_key',
            fields    => $item[5],
        }
    }

foreign_key_constraint : /constraint/i index_name(?) /foreign/i /key/i parens_field_list /references/i table_name parens_field_list(?) on_delete(?) on_update(?)
    {
        $return = {
            supertype        => 'constraint',
            name             => $item[2][0],
            type             => 'foreign_key',
            fields           => $item[5],
            reference_table  => $item[7],
            reference_fields => $item[8][0],
            on_delete        => $item[9][0],
            on_update        => $item[10][0],
        }
    }

unique_constraint : /constraint/i index_name(?) /unique/i parens_field_list
    {
        $return = {
            supertype => 'constraint',
            type      => 'unique',
            name      => $item[2][0],
            fields    => $item[4],
        }
    }

unique_constraint : /unique/i clustered(?) INDEX(?) index_name(?) on_table(?) parens_field_list field_not_null(?)
    {
        $return = {
            supertype => 'constraint',
            type      => 'unique',
            clustered => $item[2][0],
            name      => $item[4][0],
            table     => $item[5][0],
            fields    => $item[6],
        }
    }

on_delete : /on delete/i reference_option
    { $item[2] }

on_update : /on update/i reference_option
    { $item[2] }

reference_option: /cascade/i
    { $item[1] }
    | /no action/i
    { $item[1] }

clustered : /clustered/i
    { $return = 1 }
    | /nonclustered/i
    { $return = 0 }

INDEX : /index/i

on_table : /on/i table_name
    { $return = $item[2] }

on_system : /on/i /system/i
    { $return = 1 }

index : clustered(?) INDEX index_name(?) on_table(?) parens_field_list END_STATEMENT
    {
        $return = {
            supertype => 'index',
            type      => 'normal',
            clustered => $item[1][0],
            name      => $item[3][0],
            table     => $item[4][0],
            fields    => $item[5],
        }
    }

parens_field_list : '(' field_name(s /,/) ')'
    { $item[2] }

ident : QUOTE WORD '.' WORD QUOTE | LQUOTE WORD '.' WORD RQUOTE
    { $return = { owner => $item[2], name => $item[4] } }
    | LQUOTE WORD RQUOTE '.' LQUOTE WORD RQUOTE
    { $return = { owner => $item[2], name => $item[6] } }
    | LQUOTE WORD RQUOTE
    { $return = { name  => $item[2] } }
    | WORD '.' WORD
    { $return = { owner => $item[1], name => $item[3] } }
    | WORD
    { $return = { name  => $item[1] } }

END_STATEMENT : ';'
   | GO

GO : /^go/i

NAME : QUOTE(?) /\w+/ QUOTE(?)
    { $item[2] }

WORD : /[\w#]+/

DIGITS : /\d+/

COMMA : ','

QUOTE : /'/

LQUOTE : '['

RQUOTE : ']'

END_OF_GRAMMAR

sub parse {
    my ( $translator, $data ) = @_;

    # Enable warnings within the Parse::RecDescent module.
    local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
    local $::RD_WARN   = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
    local $::RD_HINT   = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.

    local $::RD_TRACE  = $translator->trace ? 1 : undef;
    local $DEBUG       = $translator->debug;

    my $parser = ddl_parser_instance('SQLServer');

    my $result = $parser->startrule($data);
    return $translator->error( "Parse failed." ) unless defined $result;
    warn Dumper( $result ) if $DEBUG;

    my $schema = $translator->schema;
    my @tables = sort {
        $result->{tables}->{ $a }->{'order'} <=> $result->{tables}->{ $b }->{'order'}
    } keys %{ $result->{tables} };

    for my $table_name ( @tables ) {
        my $tdata = $result->{tables}->{ $table_name };
        my $table = $schema->add_table( name => $tdata->{'name'} )
                    or die "Can't create table '$table_name': ", $schema->error;

        $table->comments( $tdata->{'comments'} );

        my @fields = sort {
            $tdata->{'fields'}->{$a}->{'order'}
            <=>
            $tdata->{'fields'}->{$b}->{'order'}
        } keys %{ $tdata->{'fields'} };

        for my $fname ( @fields ) {
            my $fdata = $tdata->{'fields'}{ $fname };
            my $field = $table->add_field(
                name              => $fdata->{'name'},
                data_type         => $fdata->{'data_type'},
                size              => $fdata->{'size'},
                default_value     => $fdata->{'default'},
                is_auto_increment => $fdata->{'is_auto_inc'},
                is_nullable       => $fdata->{'nullable'},
                comments          => $fdata->{'comments'},
            ) or die $table->error;

            $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};

            for my $qual ( qw[ binary unsigned zerofill list ] ) {
                if ( my $val = $fdata->{ $qual } || $fdata->{ uc $qual } ) {
                    next if ref $val eq 'ARRAY' && !@$val;
                    $field->extra( $qual, $val );
                }
            }

            if ( $field->data_type =~ /(set|enum)/i && !$field->size ) {
                my %extra = $field->extra;
                my $longest = 0;
                for my $len ( map { length } @{ $extra{'list'} || [] } ) {
                    $longest = $len if $len > $longest;
                }
                $field->size( $longest ) if $longest;
            }

            for my $cdata ( @{ $fdata->{'constraints'} } ) {
                next unless $cdata->{'type'} eq 'foreign_key';
                $cdata->{'fields'} ||= [ $field->name ];
                push @{ $tdata->{'constraints'} }, $cdata;
            }
        }

        for my $idata ( @{ $tdata->{'indices'} || [] } ) {
            my $index  =  $table->add_index(
                name   => $idata->{'name'},
                type   => uc $idata->{'type'},
                fields => $idata->{'fields'},
            ) or die $table->error;
        }

        for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
            my $constraint       =  $table->add_constraint(
                name             => $cdata->{'name'},
                type             => $cdata->{'type'},
                fields           => $cdata->{'fields'},
                reference_table  => $cdata->{'reference_table'},
                reference_fields => $cdata->{'reference_fields'},
                match_type       => $cdata->{'match_type'} || '',
                on_delete        => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
                on_update        => $cdata->{'on_update'} || $cdata->{'on_update_do'},
            ) or die $table->error;
        }
    }

    my @procedures = sort {
        $result->{procedures}->{ $a }->{'order'} <=> $result->{procedures}->{ $b }->{'order'}
    } keys %{ $result->{procedures} };
    for my $proc_name (@procedures) {
      $schema->add_procedure(
         name  => $proc_name,
         owner => $result->{procedures}->{$proc_name}->{owner},
         sql   => $result->{procedures}->{$proc_name}->{sql},
      );
    }

    my @views = sort {
        $result->{views}->{ $a }->{'order'} <=> $result->{views}->{ $b }->{'order'}
    } keys %{ $result->{views} };
    for my $view_name (keys %{ $result->{views} }) {
      $schema->add_view(
         name => $view_name,
         sql  => $result->{views}->{$view_name}->{sql},
      );
    }

    return 1;
}

1;

# -------------------------------------------------------------------
# Every hero becomes a bore at last.
# Ralph Waldo Emerson
# -------------------------------------------------------------------

=pod

=head1 AUTHOR

Chris Hilton E<lt>chris@dctank.comE<gt> - Bulk of code from
Sybase parser, I just tweaked it for SQLServer. Thanks.

=head1 SEE ALSO

SQL::Translator, SQL::Translator::Parser::DBI, L<http://www.midsomer.org/>.

=cut
Commit	Line	Data
be4469ab	1	package SQL::Translator::Parser::SQLServer;
be4469ab	2
be4469ab	3	=head1 NAME
	4
	5	SQL::Translator::Parser::SQLServer - parser for SQL Server
	6
	7	=head1 SYNOPSIS
	8
	9	use SQL::Translator::Parser::SQLServer;
	10
	11	=head1 DESCRIPTION
	12
	13	Adapted from Parser::Sybase and mostly parses the output of
	14	Producer::SQLServer. The parsing is by no means complete and
	15	should probably be considered a work in progress.
	16
	17	=cut
	18
	19	use strict;
f27f9229	20	use warnings;
be4469ab	21
0c04c5a2	22	our $VERSION = '1.59';
bdf60588	23
bdf60588	24	our $DEBUG;
be4469ab	25	$DEBUG = 0 unless defined $DEBUG;
	26
	27	use Data::Dumper;
bdf60588	28	use SQL::Translator::Utils qw/ddl_parser_instance/;
be4469ab	29
bdf60588	30	use base qw(Exporter);
bdf60588	31	our @EXPORT_OK = qw(parse);
be4469ab	32
bdf60588	33	our $GRAMMAR = <<'END_OF_GRAMMAR';
be4469ab	34
e5f65c59	35	{
ff3dd529	36	my ( %tables, @table_comments, $table_order, %procedures, $proc_order, %views, $view_order );
28224573	37
	38	sub _err {
	39	my $max_lines = 5;
	40	my @up_to_N_lines = split (/\n/, $_[1], $max_lines + 1);
	41	die sprintf ("Unable to parse line %d:\n%s\n",
	42	$_[0],
	43	join "\n", (map { "'$_'" } @up_to_N_lines[0..$max_lines - 1 ]), @up_to_N_lines > $max_lines ? '...' : ()
	44	);
	45	}
	46
be4469ab	47	}
be4469ab	48
ff3dd529	49	startrule : statement(s) eofile
e5f65c59	50	{
	51	return {
	52	tables => \%tables,
	53	procedures => \%procedures,
	54	views => \%views,
	55	}
	56	}
be4469ab	57
	58	eofile : /^\Z/
	59
	60	statement : create_table
	61	\| create_procedure
ff3dd529	62	\| create_view
be4469ab	63	\| create_index
	64	\| create_constraint
	65	\| comment
028386aa	66	\| disable_constraints
e2fb9ad3	67	\| drop
be4469ab	68	\| use
	69	\| setuser
	70	\| if
	71	\| print
	72	\| grant
	73	\| exec
28224573	74	\| /^\Z/ \| { _err ($thisline, $text) }
be4469ab	75
e5f65c59	76	use : /use/i WORD GO
be4469ab	77	{ @table_comments = () }
	78
	79	setuser : /setuser/i NAME GO
	80
	81	if : /if/i object_not_null begin if_command end GO
	82
	83	if_command : grant
	84	\| create_index
	85	\| create_constraint
	86
	87	object_not_null : /object_id/i '(' ident ')' /is not null/i
	88
028386aa	89	field_not_null : /where/i field_name /is \s+ not \s+ null/ix
028386aa	90
be4469ab	91	print : /\s/ /print/i /./
	92
	93	else : /else/i /.*/
	94
	95	begin : /begin/i
	96
	97	end : /end/i
	98
	99	grant : /grant/i /[^\n]*/
	100
	101	exec : exec_statement(s) GO
	102
	103	exec_statement : /exec/i /[^\n]+/
	104
e5f65c59	105	comment : /^\s(?:#\|-{2}).\n/
e5f65c59	106	{
be4469ab	107	my $comment = $item[1];
	108	$comment =~ s/^\s(#\|--)\s//;
	109	$comment =~ s/\s*$//;
	110	$return = $comment;
	111	push @table_comments, $comment;
	112	}
	113
	114	comment : comment_start comment_middle comment_end
e5f65c59	115	{
be4469ab	116	my $comment = $item[2];
	117	$comment =~ s/^\s\|\s$//mg;
	118	$comment =~ s/^\*\s//mg;
	119	push @table_comments, $comment;
	120	}
	121
	122	comment_start : m#^\s\/\#
	123
	124	comment_end : m#\s\\/#
	125
	126	comment_middle : m{([^]+\|\(?!/))*}
	127
e2fb9ad3	128	drop : if_exists(?) /drop/i tbl_drop END_STATEMENT
e2fb9ad3	129
028386aa	130	tbl_drop : /table/i ident
e2fb9ad3	131
	132	if_exists : /if exists/i '(' /select/i 'name' /from/i 'sysobjects' /[^\)]+/ ')'
	133
be4469ab	134	#
	135	# Create table.
	136	#
ff3dd529	137	create_table : /create/i /table/i ident '(' create_def(s /,/) ')' lock(?) on_system(?) END_STATEMENT
e5f65c59	138	{
be4469ab	139	my $table_owner = $item[3]{'owner'};
	140	my $table_name = $item[3]{'name'};
	141
	142	if ( @table_comments ) {
	143	$tables{ $table_name }{'comments'} = [ @table_comments ];
	144	@table_comments = ();
	145	}
	146
	147	$tables{ $table_name }{'order'} = ++$table_order;
	148	$tables{ $table_name }{'name'} = $table_name;
	149	$tables{ $table_name }{'owner'} = $table_owner;
	150	$tables{ $table_name }{'system'} = $item[7];
	151
	152	my $i = 0;
	153	for my $def ( @{ $item[5] } ) {
	154	if ( $def->{'supertype'} eq 'field' ) {
	155	my $field_name = $def->{'name'};
e5f65c59	156	$tables{ $table_name }{'fields'}{ $field_name } =
be4469ab	157	{ %$def, order => $i };
be4469ab	158	$i++;
e5f65c59	159
be4469ab	160	if ( $def->{'is_primary_key'} ) {
	161	push @{ $tables{ $table_name }{'constraints'} }, {
	162	type => 'primary_key',
	163	fields => [ $field_name ],
	164	};
	165	}
	166	}
	167	elsif ( $def->{'supertype'} eq 'constraint' ) {
	168	push @{ $tables{ $table_name }{'constraints'} }, $def;
	169	}
	170	else {
	171	push @{ $tables{ $table_name }{'indices'} }, $def;
	172	}
	173	}
	174	}
	175
028386aa	176	disable_constraints : if_exists(?) /alter/i /table/i ident /nocheck/i /constraint/i /all/i END_STATEMENT
	177
	178	# this is for the normal case
	179	create_constraint : /create/i constraint END_STATEMENT
	180	{
	181	@table_comments = ();
	182	push @{ $tables{ $item[2]{'table'} }{'constraints'} }, $item[2];
	183	}
	184
	185	# and this is for the BEGIN/END case
f9a5ee79	186	create_constraint : /create/i constraint
be4469ab	187	{
	188	@table_comments = ();
	189	push @{ $tables{ $item[2]{'table'} }{'constraints'} }, $item[2];
	190	}
	191
028386aa	192
f9a5ee79	193	create_constraint : /alter/i /table/i ident /add/i foreign_key_constraint END_STATEMENT
	194	{
	195	push @{ $tables{ $item[3]{name} }{constraints} }, $item[5];
	196	}
	197
028386aa	198
be4469ab	199	create_index : /create/i index
	200	{
	201	@table_comments = ();
	202	push @{ $tables{ $item[2]{'table'} }{'indices'} }, $item[2];
	203	}
	204
ff3dd529	205	create_procedure : /create/i PROCEDURE WORD not_go GO
be4469ab	206	{
be4469ab	207	@table_comments = ();
ff3dd529	208	my $proc_name = $item[3];
	209	my $owner = '';
	210	my $sql = "$item[1] $item[2] $proc_name $item[4]";
e5f65c59	211
ff3dd529	212	$procedures{ $proc_name }{'order'} = ++$proc_order;
	213	$procedures{ $proc_name }{'name'} = $proc_name;
	214	$procedures{ $proc_name }{'owner'} = $owner;
	215	$procedures{ $proc_name }{'sql'} = $sql;
be4469ab	216	}
be4469ab	217
ff3dd529	218	create_procedure : /create/i PROCEDURE '[' WORD '].' WORD not_go GO
	219	{
	220	@table_comments = ();
	221	my $proc_name = $item[6];
	222	my $owner = $item[4];
	223	my $sql = "$item[1] $item[2] [$owner].$proc_name $item[7]";
e5f65c59	224
ff3dd529	225	$procedures{ $proc_name }{'order'} = ++$proc_order;
	226	$procedures{ $proc_name }{'name'} = $proc_name;
	227	$procedures{ $proc_name }{'owner'} = $owner;
	228	$procedures{ $proc_name }{'sql'} = $sql;
	229	}
	230
	231	PROCEDURE : /procedure/i
e5f65c59	232	\| /function/i
ff3dd529	233
ff3dd529	234	create_view : /create/i /view/i WORD not_go GO
	235	{
	236	@table_comments = ();
	237	my $view_name = $item[3];
	238	my $sql = "$item[1] $item[2] $item[3] $item[4]";
e5f65c59	239
ff3dd529	240	$views{ $view_name }{'order'} = ++$view_order;
	241	$views{ $view_name }{'name'} = $view_name;
	242	$views{ $view_name }{'sql'} = $sql;
	243	}
	244
ff3dd529	245	not_go : /((?!\bgo\b).)*/is
be4469ab	246
	247	create_def : constraint
	248	\| index
	249	\| field
	250
	251	blank : /\s*/
	252
	253	field : field_name data_type field_qualifier(s?)
e5f65c59	254	{
be4469ab	255	my %qualifiers = map { %$_ } @{ $item{'field_qualifier(s?)'} \|\| [] };
e5f65c59	256	my $nullable = defined $qualifiers{'nullable'}
be4469ab	257	? $qualifiers{'nullable'} : 1;
e5f65c59	258	$return = {
be4469ab	259	supertype => 'field',
e5f65c59	260	name => $item{'field_name'},
be4469ab	261	data_type => $item{'data_type'}{'type'},
be4469ab	262	size => $item{'data_type'}{'size'},
e5f65c59	263	nullable => $nullable,
	264	default => $qualifiers{'default_val'},
	265	is_auto_inc => $qualifiers{'is_auto_inc'},
	266	# is_primary_key => $item{'primary_key'}[0],
	267	}
be4469ab	268	}
	269
	270	field_qualifier : nullable
e5f65c59	271	{
e5f65c59	272	$return = {
be4469ab	273	nullable => $item{'nullable'},
e5f65c59	274	}
be4469ab	275	}
	276
	277	field_qualifier : default_val
e5f65c59	278	{
e5f65c59	279	$return = {
be4469ab	280	default_val => $item{'default_val'},
e5f65c59	281	}
be4469ab	282	}
	283
	284	field_qualifier : auto_inc
e5f65c59	285	{
e5f65c59	286	$return = {
be4469ab	287	is_auto_inc => $item{'auto_inc'},
e5f65c59	288	}
be4469ab	289	}
	290
	291	constraint : primary_key_constraint
	292	\| foreign_key_constraint
	293	\| unique_constraint
	294
	295	field_name : WORD
028386aa	296	{ $return = $item[1] }
	297	\| LQUOTE WORD RQUOTE
	298	{ $return = $item[2] }
be4469ab	299
be4469ab	300	index_name : WORD
028386aa	301	{ $return = $item[1] }
	302	\| LQUOTE WORD RQUOTE
	303	{ $return = $item[2] }
be4469ab	304
be4469ab	305	table_name : WORD
028386aa	306	{ $return = $item[1] }
	307	\| LQUOTE WORD RQUOTE
	308	{ $return = $item[2] }
be4469ab	309
e5f65c59	310	data_type : WORD field_size(?)
	311	{
	312	$return = {
	313	type => $item[1],
be4469ab	314	size => $item[2][0]
e5f65c59	315	}
be4469ab	316	}
	317
	318	lock : /lock/i /datarows/i
	319
	320	field_type : WORD
	321
	322	field_size : '(' num_range ')' { $item{'num_range'} }
	323
	324	num_range : DIGITS ',' DIGITS
	325	{ $return = $item[1].','.$item[3] }
	326	\| DIGITS
	327	{ $return = $item[1] }
	328
	329
	330	nullable : /not/i /null/i
	331	{ $return = 0 }
	332	\| /null/i
	333	{ $return = 1 }
	334
2a8fb466	335	default_val : /default/i /null/i
2a8fb466	336	{ $return = 'null' }
e5f65c59	337	\| /default/i /'[^']*'/
be4469ab	338	{ $item[2]=~ s/'//g; $return = $item[2] }
e5f65c59	339	\| /default/i WORD
06baeb21	340	{ $return = $item[2] }
be4469ab	341
	342	auto_inc : /identity/i { 1 }
	343
	344	primary_key_constraint : /constraint/i index_name(?) /primary/i /key/i parens_field_list
e5f65c59	345	{
e5f65c59	346	$return = {
be4469ab	347	supertype => 'constraint',
	348	name => $item[2][0],
	349	type => 'primary_key',
	350	fields => $item[5],
e5f65c59	351	}
be4469ab	352	}
be4469ab	353
100684f3	354	foreign_key_constraint : /constraint/i index_name(?) /foreign/i /key/i parens_field_list /references/i table_name parens_field_list(?) on_delete(?) on_update(?)
be4469ab	355	{
e5f65c59	356	$return = {
be4469ab	357	supertype => 'constraint',
	358	name => $item[2][0],
	359	type => 'foreign_key',
	360	fields => $item[5],
	361	reference_table => $item[7],
e5f65c59	362	reference_fields => $item[8][0],
100684f3	363	on_delete => $item[9][0],
100684f3	364	on_update => $item[10][0],
e5f65c59	365	}
be4469ab	366	}
be4469ab	367
e2fb9ad3	368	unique_constraint : /constraint/i index_name(?) /unique/i parens_field_list
e2fb9ad3	369	{
e5f65c59	370	$return = {
e2fb9ad3	371	supertype => 'constraint',
	372	type => 'unique',
	373	name => $item[2][0],
	374	fields => $item[4],
	375	}
	376	}
	377
028386aa	378	unique_constraint : /unique/i clustered(?) INDEX(?) index_name(?) on_table(?) parens_field_list field_not_null(?)
e5f65c59	379	{
e5f65c59	380	$return = {
be4469ab	381	supertype => 'constraint',
	382	type => 'unique',
	383	clustered => $item[2][0],
	384	name => $item[4][0],
	385	table => $item[5][0],
	386	fields => $item[6],
e5f65c59	387	}
be4469ab	388	}
be4469ab	389
100684f3	390	on_delete : /on delete/i reference_option
be4469ab	391	{ $item[2] }
be4469ab	392
100684f3	393	on_update : /on update/i reference_option
be4469ab	394	{ $item[2] }
be4469ab	395
ff3dd529	396	reference_option: /cascade/i
	397	{ $item[1] }
	398	\| /no action/i
	399	{ $item[1] }
be4469ab	400
	401	clustered : /clustered/i
	402	{ $return = 1 }
	403	\| /nonclustered/i
	404	{ $return = 0 }
	405
	406	INDEX : /index/i
	407
	408	on_table : /on/i table_name
	409	{ $return = $item[2] }
	410
	411	on_system : /on/i /system/i
	412	{ $return = 1 }
	413
e2fb9ad3	414	index : clustered(?) INDEX index_name(?) on_table(?) parens_field_list END_STATEMENT
e5f65c59	415	{
e5f65c59	416	$return = {
be4469ab	417	supertype => 'index',
	418	type => 'normal',
	419	clustered => $item[1][0],
	420	name => $item[3][0],
	421	table => $item[4][0],
	422	fields => $item[5],
e5f65c59	423	}
be4469ab	424	}
	425
	426	parens_field_list : '(' field_name(s /,/) ')'
	427	{ $item[2] }
	428
028386aa	429	ident : QUOTE WORD '.' WORD QUOTE \| LQUOTE WORD '.' WORD RQUOTE
be4469ab	430	{ $return = { owner => $item[2], name => $item[4] } }
028386aa	431	\| LQUOTE WORD RQUOTE '.' LQUOTE WORD RQUOTE
	432	{ $return = { owner => $item[2], name => $item[6] } }
	433	\| LQUOTE WORD RQUOTE
	434	{ $return = { name => $item[2] } }
	435	\| WORD '.' WORD
	436	{ $return = { owner => $item[1], name => $item[3] } }
be4469ab	437	\| WORD
	438	{ $return = { name => $item[1] } }
	439
ff3dd529	440	END_STATEMENT : ';'
e5f65c59	441	\| GO
ff3dd529	442
be4469ab	443	GO : /^go/i
	444
	445	NAME : QUOTE(?) /\w+/ QUOTE(?)
	446	{ $item[2] }
	447
	448	WORD : /[\w#]+/
	449
	450	DIGITS : /\d+/
	451
	452	COMMA : ','
	453
	454	QUOTE : /'/
	455
028386aa	456	LQUOTE : '['
	457
	458	RQUOTE : ']'
	459
bdf60588	460	END_OF_GRAMMAR
be4469ab	461
be4469ab	462	sub parse {
be4469ab	463	my ( $translator, $data ) = @_;
bdf60588	464
	465	# Enable warnings within the Parse::RecDescent module.
	466	local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
	467	local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
	468	local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
be4469ab	469
	470	local $::RD_TRACE = $translator->trace ? 1 : undef;
	471	local $DEBUG = $translator->debug;
	472
bdf60588	473	my $parser = ddl_parser_instance('SQLServer');
be4469ab	474
	475	my $result = $parser->startrule($data);
	476	return $translator->error( "Parse failed." ) unless defined $result;
	477	warn Dumper( $result ) if $DEBUG;
	478
	479	my $schema = $translator->schema;
e5f65c59	480	my @tables = sort {
ff3dd529	481	$result->{tables}->{ $a }->{'order'} <=> $result->{tables}->{ $b }->{'order'}
ff3dd529	482	} keys %{ $result->{tables} };
be4469ab	483
be4469ab	484	for my $table_name ( @tables ) {
ff3dd529	485	my $tdata = $result->{tables}->{ $table_name };
e5f65c59	486	my $table = $schema->add_table( name => $tdata->{'name'} )
be4469ab	487	or die "Can't create table '$table_name': ", $schema->error;
	488
	489	$table->comments( $tdata->{'comments'} );
	490
e5f65c59	491	my @fields = sort {
e5f65c59	492	$tdata->{'fields'}->{$a}->{'order'}
be4469ab	493	<=>
	494	$tdata->{'fields'}->{$b}->{'order'}
	495	} keys %{ $tdata->{'fields'} };
	496
	497	for my $fname ( @fields ) {
	498	my $fdata = $tdata->{'fields'}{ $fname };
	499	my $field = $table->add_field(
	500	name => $fdata->{'name'},
	501	data_type => $fdata->{'data_type'},
	502	size => $fdata->{'size'},
	503	default_value => $fdata->{'default'},
	504	is_auto_increment => $fdata->{'is_auto_inc'},
	505	is_nullable => $fdata->{'nullable'},
	506	comments => $fdata->{'comments'},
	507	) or die $table->error;
	508
	509	$table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
	510
	511	for my $qual ( qw[ binary unsigned zerofill list ] ) {
	512	if ( my $val = $fdata->{ $qual } \|\| $fdata->{ uc $qual } ) {
	513	next if ref $val eq 'ARRAY' && !@$val;
	514	$field->extra( $qual, $val );
	515	}
	516	}
	517
	518	if ( $field->data_type =~ /(set\|enum)/i && !$field->size ) {
	519	my %extra = $field->extra;
	520	my $longest = 0;
	521	for my $len ( map { length } @{ $extra{'list'} \|\| [] } ) {
	522	$longest = $len if $len > $longest;
	523	}
	524	$field->size( $longest ) if $longest;
	525	}
	526
	527	for my $cdata ( @{ $fdata->{'constraints'} } ) {
	528	next unless $cdata->{'type'} eq 'foreign_key';
	529	$cdata->{'fields'} \|\|= [ $field->name ];
	530	push @{ $tdata->{'constraints'} }, $cdata;
	531	}
	532	}
	533
	534	for my $idata ( @{ $tdata->{'indices'} \|\| [] } ) {
	535	my $index = $table->add_index(
	536	name => $idata->{'name'},
	537	type => uc $idata->{'type'},
	538	fields => $idata->{'fields'},
	539	) or die $table->error;
	540	}
	541
	542	for my $cdata ( @{ $tdata->{'constraints'} \|\| [] } ) {
	543	my $constraint = $table->add_constraint(
	544	name => $cdata->{'name'},
	545	type => $cdata->{'type'},
	546	fields => $cdata->{'fields'},
	547	reference_table => $cdata->{'reference_table'},
	548	reference_fields => $cdata->{'reference_fields'},
	549	match_type => $cdata->{'match_type'} \|\| '',
100684f3	550	on_delete => $cdata->{'on_delete'} \|\| $cdata->{'on_delete_do'},
100684f3	551	on_update => $cdata->{'on_update'} \|\| $cdata->{'on_update_do'},
be4469ab	552	) or die $table->error;
	553	}
	554	}
e5f65c59	555
e5f65c59	556	my @procedures = sort {
ff3dd529	557	$result->{procedures}->{ $a }->{'order'} <=> $result->{procedures}->{ $b }->{'order'}
	558	} keys %{ $result->{procedures} };
	559	for my $proc_name (@procedures) {
e5f65c59	560	$schema->add_procedure(
	561	name => $proc_name,
	562	owner => $result->{procedures}->{$proc_name}->{owner},
	563	sql => $result->{procedures}->{$proc_name}->{sql},
	564	);
ff3dd529	565	}
ff3dd529	566
e5f65c59	567	my @views = sort {
ff3dd529	568	$result->{views}->{ $a }->{'order'} <=> $result->{views}->{ $b }->{'order'}
	569	} keys %{ $result->{views} };
	570	for my $view_name (keys %{ $result->{views} }) {
e5f65c59	571	$schema->add_view(
	572	name => $view_name,
	573	sql => $result->{views}->{$view_name}->{sql},
	574	);
ff3dd529	575	}
be4469ab	576
	577	return 1;
	578	}
	579
	580	1;
	581
	582	# -------------------------------------------------------------------
	583	# Every hero becomes a bore at last.
	584	# Ralph Waldo Emerson
	585	# -------------------------------------------------------------------
	586
	587	=pod
	588
	589	=head1 AUTHOR
	590
	591	Chris Hilton E<lt>chris@dctank.comE<gt> - Bulk of code from
	592	Sybase parser, I just tweaked it for SQLServer. Thanks.
	593
	594	=head1 SEE ALSO
	595
	596	SQL::Translator, SQL::Translator::Parser::DBI, L<http://www.midsomer.org/>.
	597
	598	=cut