SQL-Translator

 view release on metacpan or  search on metacpan

lib/SQL/Translator/Parser/Access.pm  view on Meta::CPAN

    |
    /match partial/i { 'partial' }

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

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

reference_option: /restrict/i |
    /cascade/i   |
    /set null/i  |
    /no action/i |
    /set default/i
    { $item[1] }

index : normal_index
    | fulltext_index
    | <error>

table_name   : NAME

lib/SQL/Translator/Parser/DBI/PostgreSQL.pm  view on Meta::CPAN

use warnings;
use DBI;
use Data::Dumper;
use SQL::Translator::Schema::Constants;

our ($DEBUG, @EXPORT_OK);
our $VERSION = '1.66';
$DEBUG = 0 unless defined $DEBUG;

my $actions = {
  c => 'cascade',
  r => 'restrict',
  a => 'no action',
  n => 'set null',
  d => 'set default',
};

sub parse {
  my ($tr, $dbh) = @_;

  my $schema                 = $tr->schema;

lib/SQL/Translator/Parser/MySQL.pm  view on Meta::CPAN

    { $item[2] }

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

reference_option: /restrict/i |
    /cascade/i   |
    /set null/i  |
    /no action/i |
    /set default/i
    { $item[1] }

index : normal_index
    | fulltext_index
    | spatial_index
    | <error>

lib/SQL/Translator/Parser/PostgreSQL.pm  view on Meta::CPAN

        $return = {
            type   => 'update',
            action => $item[2],
        };
    }

key_mutation : /no action/i { $return = 'no_action' }
    |
    /restrict/i { $return = 'restrict' }
    |
    /cascade/i { $return = 'cascade' }
    |
    /set null/i { $return = 'set null' }
    |
    /set default/i { $return = 'set default' }

alter : alter_table table_id add_column field ';'
    {
        my $field_def = $item[4];
        $tables{ $item[2]->{'table_name'} }{'fields'}{ $field_def->{'name'} } = {
            %$field_def, order => $field_order++

lib/SQL/Translator/Parser/PostgreSQL.pm  view on Meta::CPAN

    }

alter : alter_table table_id ADD table_constraint ';'
    {
        my $table_name = $item[2]->{'table_name'};
        my $constraint = $item[4];
        push @{ $tables{ $table_name }{'constraints'} }, $constraint;
        1;
    }

alter : alter_table table_id drop_column NAME restrict_or_cascade(?) ';'
    {
        $tables{ $item[2]->{'table_name'} }{'fields'}{ $item[4] }{'drop'} = 1;
        1;
    }

alter : alter_table table_id alter_column NAME alter_default_val ';'
    {
        $tables{ $item[2]->{'table_name'} }{'fields'}{ $item[4] }{'default'} =
            $item[5]->{'value'};
        1;

lib/SQL/Translator/Parser/PostgreSQL.pm  view on Meta::CPAN

alter : alter_table table_id alter_column NAME SET /statistics/i INTEGER ';'
    { 1 }

alter : alter_table table_id alter_column NAME SET /storage/i storage_type ';'
    { 1 }

alter : alter_table table_id rename_column NAME /to/i NAME ';'
    { 1 }

alter : alter_table table_id DROP /constraint/i NAME restrict_or_cascade ';'
    { 1 }

alter : alter_table table_id /owner/i /to/i NAME ';'
    { 1 }

alter : alter_sequence NAME /owned/i /by/i column_name ';'
    { 1 }

storage_type : /(plain|external|extended|main)/i

lib/SQL/Translator/Parser/PostgreSQL.pm  view on Meta::CPAN

alter_table : ALTER TABLE ONLY(?)

alter_sequence : ALTER SEQUENCE

drop_column : DROP COLUMN(?)

alter_column : ALTER COLUMN(?)

rename_column : /rename/i COLUMN(?)

restrict_or_cascade : /restrict/i |
    /cascade/i

# Handle functions that can be called
select : SELECT select_function ';'
    { 1 }

# Read the setval function but don't do anything with it because this parser
# isn't handling sequences
select_function : schema_qualification(?) /setval/i '(' VALUE /,/ VALUE /,/ /(true|false)/i ')'
    { 1 }

lib/SQL/Translator/Parser/SQLServer.pm  view on Meta::CPAN

            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

lib/SQL/Translator/Parser/SQLite.pm  view on Meta::CPAN

    }
    |
    DEFAULT VALUE
    {
        $return   = {
            type  => 'default',
            value => $item[2],
        }
    }
    |
    REFERENCES ref_def cascade_def(?)
    {
        $return   = {
            type             => 'foreign_key',
            reference_table  => $item[2]{'reference_table'},
            reference_fields => $item[2]{'reference_fields'},
            on_delete        => $item[3][0]{'on_delete'},
            on_update        => $item[3][0]{'on_update'},
        }
    }
    |

lib/SQL/Translator/Parser/SQLite.pm  view on Meta::CPAN

    CHECK_C '(' expr ')' conflict_clause(?)
    {
        $return         = {
            supertype   => 'constraint',
            type        => 'check',
            expression  => $item[3],
            on_conflict => $item[5][0],
        }
    }
    |
    FOREIGN_KEY parens_field_list REFERENCES ref_def cascade_def(?)
    {
      $return = {
        supertype        => 'constraint',
        type             => 'foreign_key',
        fields           => $item[2],
        reference_table  => $item[4]{'reference_table'},
        reference_fields => $item[4]{'reference_fields'},
        on_delete        => $item[5][0]{'on_delete'},
        on_update        => $item[5][0]{'on_update'},
      }
    }

ref_def : table_name parens_field_list
    { $return = { reference_table => $item[1]{name}, reference_fields => $item[2] } }

cascade_def : cascade_update_def cascade_delete_def(?)
    { $return = {  on_update => $item[1], on_delete => $item[2][0] } }
    |
    cascade_delete_def cascade_update_def(?)
    { $return = {  on_delete => $item[1], on_update => $item[2][0] } }

cascade_delete_def : /on\s+delete\s+(set null|set default|cascade|restrict|no action)/i
    { $return = $1}

cascade_update_def : /on\s+update\s+(set null|set default|cascade|restrict|no action)/i
    { $return = $1}

table_name : qualified_name

qualified_name : NAME
    { $return = { name => $item[1] } }

qualified_name : /(\w+)\.(\w+)/
    { $return = { db_name => $1, name => $2 } }

lib/SQL/Translator/Schema.pm  view on Meta::CPAN

}

sub drop_table {

=pod

=head2 drop_table

Remove a table from the schema. Returns the table object if the table was found
and removed, an error otherwise. The single parameter can be either a table
name or an L<SQL::Translator::Schema::Table> object. The "cascade" parameter
can be set to 1 to also drop all triggers on the table, default is 0.

  $schema->drop_table('mytable');
  $schema->drop_table('mytable', cascade => 1);

=cut

  my $self        = shift;
  my $table_class = 'SQL::Translator::Schema::Table';
  my $table_name;

  if (UNIVERSAL::isa($_[0], $table_class)) {
    $table_name = shift->name;
  } else {
    $table_name = shift;
  }
  my %args    = @_;
  my $cascade = $args{'cascade'};

  if (!exists $self->_tables->{$table_name}) {
    return $self->error(qq[Can't drop table: "$table_name" doesn't exist]);
  }

  my $table = delete $self->_tables->{$table_name};

  if ($cascade) {

    # Drop all triggers on this table
    $self->drop_trigger() for (grep { $_->on_table eq $table_name } values %{ $self->_triggers });
  }
  return $table;
}

has _procedures => (is => 'ro', init_arg => undef, default => quote_sub(q{ +{} }));

sub add_procedure {

lib/SQL/Translator/Schema/Constraint.pm  view on Meta::CPAN

Object constructor.

  my $schema           =  SQL::Translator::Schema::Constraint->new(
      table            => $table,        # table to which it belongs
      type             => 'foreign_key', # type of table constraint
      name             => 'fk_phone_id', # name of the constraint
      fields           => 'phone_id',    # field in the referring table
      reference_fields => 'phone_id',    # referenced field
      reference_table  => 'phone',       # referenced table
      match_type       => 'full',        # how to match
      on_delete        => 'cascade',     # what to do on deletes
      on_update        => '',            # what to do on updates
  );

=cut

# Override to remove empty arrays from args.
# t/14postgres-parser breaks without this.
around BUILDARGS => sub {
  my $orig = shift;
  my $self = shift;

lib/SQL/Translator/Schema/Constraint.pm  view on Meta::CPAN

  my @options = $constraint->options;

=cut

with ListAttr options => ();

=head2 on_delete

Get or set the constraint's "on delete" action.

  my $action = $constraint->on_delete('cascade');

=cut

has on_delete => (is => 'rw', default => quote_sub(q{ '' }));

around on_delete => sub {
  my ($orig, $self, $arg) = @_;
  $self->$orig($arg || ());
};

lib/SQL/Translator/Schema/Table.pm  view on Meta::CPAN

  my $self        = shift;
  my $field_class = 'SQL::Translator::Schema::Field';
  my $field_name;

  if (UNIVERSAL::isa($_[0], $field_class)) {
    $field_name = shift->name;
  } else {
    $field_name = shift;
  }
  my %args    = @_;
  my $cascade = $args{'cascade'};

  if (!($self->_has_fields && exists $self->_fields->{$field_name})) {
    return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
  }

  my $field = delete $self->_fields->{$field_name};

  if ($cascade) {

    # Remove this field from all indices using it
    foreach my $i ($self->get_indices()) {
      my @fs = $i->fields();
      @fs = grep { $_ ne $field->name } @fs;
      $i->fields(@fs);
    }

    # Remove this field from all constraints using it
    foreach my $c ($self->get_constraints()) {

t/13schema.t  view on Meta::CPAN

  is($redundant_table, undef, qq[Can't add an anonymous table...]);
  like($schema->error, qr/No table name/i, '... because it has no name ');

  $redundant_table = SQL::Translator::Schema::Table->new(name => '');
  is($redundant_table, undef, qq[Can't create an anonymous table]);
  like(SQL::Translator::Schema::Table->error, qr/No table name/i, '... because it has no name ');

  #
  # $schema-> drop_table
  #
  my $dropped_table = $schema->drop_table($foo_table->name, cascade => 1);
  isa_ok($dropped_table, 'SQL::Translator::Schema::Table', 'Dropped table "foo"');
  $schema->add_table($foo_table);
  my $dropped_table2 = $schema->drop_table($foo_table, cascade => 1);
  isa_ok($dropped_table2, 'SQL::Translator::Schema::Table', 'Dropped table "foo" by object');
  my $dropped_table3 = $schema->drop_table($foo_table->name, cascade => 1);
  like($schema->error, qr/doesn't exist/, qq[Can't drop non-existant table "foo"]);

  $schema->add_table($foo_table);
  #
  # Table default new
  #
  is($foo_table->name,     'foo', 'Table name is "foo"');
  is("$foo_table",         'foo', 'Table stringifies to "foo"');
  is($foo_table->is_valid, undef, 'Table "foo" is not yet valid');

t/13schema.t  view on Meta::CPAN

  is($fields[0]->name,                      'foo',    'First field is "foo"');
  is($fields[1]->name,                      'f2',     'Second field is "f2"');
  is(join(",", $person_table->field_names), 'foo,f2', 'field_names is "foo,f2"');

  my $ci_field = $person_table->get_field('FOO', 'case_insensitive');
  is($ci_field->name, 'foo', 'Got field case-insensitively');
  #
  # $table-> drop_field
  #
  my $dropped_field = $person_table->drop_field($f2->name, cascade => 1);
  isa_ok($dropped_field, 'SQL::Translator::Schema::Field', 'Dropped field "f2"');
  $person_table->add_field($f2);
  my $dropped_field2 = $person_table->drop_field($f2, cascade => 1);
  isa_ok($dropped_field2, 'SQL::Translator::Schema::Field', 'Dropped field "f2" by object');
  my $dropped_field3 = $person_table->drop_field($f2->name, cascade => 1);
  like($person_table->error, qr/doesn't exist/, qq[Can't drop non-existant field "f2"]);

  $person_table->add_field($f2);

  #
  # Field methods
  #
  is($f1->name('person_name'),  'person_name', 'Field name is "person_name"');
  is($f1->data_type('varchar'), 'varchar',     'Field data type is "varchar"');
  is($f1->size('30'),           '30',          'Field size is "30"');

t/14postgres-parser.t  view on Meta::CPAN

    CREATE INDEX test_index2 ON t_test1 USING hash (f_char, f_bool);
    CREATE INDEX test_index3 ON t_test1 USING hash (f_bigint, f_tz) WHERE f_bigint = '1' AND f_tz IS NULL;
    CREATE INDEX test_index4 ON t_test1 USING hash (f_bigint, f_tz) include (f_bool) WHERE f_bigint = '1' AND f_tz IS NULL;

    alter table t_test1 add f_fk2 integer;

    alter table only t_test1 add constraint c_u1 unique (f_varchar);

    alter table t_test1 add constraint "c_fk2" foreign key (f_fk2)
    references t_test2 (f_id) match simple
    on update no action on delete cascade deferrable;


    alter table t_test1 drop column f_dropped restrict;

    alter table t_test1 alter column f_fk2 set default 'FOO';

    alter table t_test1 alter column f_char drop default;

    -- The following are allowed by the grammar
    -- but won\'t do anything... - ky

t/14postgres-parser.t  view on Meta::CPAN

    alter table t_text1 alter column f_char drop not null;

    alter table t_test1 alter f_char set statistics 10;

    alter table t_test1 alter f_text set storage extended;

    alter table t_test1 rename column f_text to foo;

    alter table t_test1 rename to foo;

    alter table only t_test1 drop constraint foo cascade;

    alter table t_test1 owner to foo;

    -- we should tests views if they're supported, right?

    create or replace temporary view fez (foo, bar) as select foo, count(bar) as bar from baz group by foo;

    create materialized view if not exists baa (black, sheep) as select foo black, bar sheep from baz;

    commit;

t/14postgres-parser.t  view on Meta::CPAN

my $c3 = $t1_constraints[5];
is($c3->type,              UNIQUE,      'Third constraint is unique');
is(join(',', $c3->fields), 'f_varchar', 'Constraint is on field "f_varchar"');

my $c4 = $t1_constraints[6];
is($c4->type,                        FOREIGN_KEY, 'Fourth constraint is foreign key');
is(join(',', $c4->fields),           'f_fk2',     'Constraint is on field "f_fk2"');
is($c4->reference_table,             't_test2',   'Constraint is to table "t_test2"');
is(join(',', $c4->reference_fields), 'f_id',      'Constraint is to field "f_id"');
is($c4->on_delete,                   'cascade',   'On delete: cascade');
is($c4->on_update,                   'no_action', 'On delete: no action');
is($c4->match_type,                  'simple',    'Match type: simple');
is($c4->deferrable,                  1,           'Deferrable detected');

my $t2 = shift @tables;
is($t2->name, 't_test2', 'Table t_test2 exists');

my @t2_fields = $t2->get_fields;
is(scalar @t2_fields, 4, '4 fields in t_test2');

t/27sqlite-parser.t  view on Meta::CPAN

  is($t1->name, 'pet', "'Pet' table");

  my @constraints = $t1->get_constraints;
  is(scalar @constraints, 5, '5 constraints on pet');

  my $c1 = $constraints[2];
  is($c1->type,                        'FOREIGN KEY',  'FK constraint');
  is($c1->reference_table,             'person',       'References person table');
  is($c1->name,                        'fk_person_id', 'Constraint name fk_person_id');
  is($c1->on_delete,                   'RESTRICT',     'On delete restrict');
  is($c1->on_update,                   'CASCADE',      'On update cascade');
  is(join(',', $c1->reference_fields), 'person_id',    'References person_id field');

  my $c2 = $constraints[3];
  is($c2->on_delete, 'SET DEFAULT', 'On delete set default');
  is($c2->on_update, 'SET NULL',    'On update set null');

  my $c3 = $constraints[4];
  is($c3->on_update, 'NO ACTION', 'On update no action');
  is($c3->on_delete, '',          'On delete not defined');

t/47postgres-producer.t  view on Meta::CPAN

my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS
    SELECT id, name FROM thing
";
is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');

my $view2 = SQL::Translator::Schema::View->new(
  name  => 'view_foo2',
  sql   => 'SELECT id, name FROM thing',
  extra => {
    'temporary'    => '1',
    'check_option' => 'cascaded',
  },
);
my $create2_opts = { add_replace_view => 1, no_comments => 1 };
my $view2_sql1   = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);

my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS
    SELECT id, name FROM thing
 WITH CASCADED CHECK OPTION";
is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');