SQL-Engine

 view release on metacpan or  search on metacpan

README  view on Meta::CPAN

        );

    table-drop example #1 output

        # DROP TABLE "people"

    table-drop example #2

        $sql->table_drop(
          name => 'people',
          condition => 'cascade'
        );

    table-drop example #2 output

        # DROP TABLE "people" CASCADE

 table-rename

    table-rename example #1

README  view on Meta::CPAN

    constraint-create example #2 output

        # ALTER TABLE "users" ADD CONSTRAINT "user_profile_id" FOREIGN KEY
        # ("profile_id") REFERENCES "profiles" ("id")

    constraint-create example #3

        $sql->constraint_create(
          on => {
            update => 'cascade',
            delete => 'cascade'
          },
          source => {
            table => 'users',
            column => 'profile_id'
          },
          target => {
            table => 'profiles',
            column => 'id'
          },
          name => 'user_profile_id'

README.md  view on Meta::CPAN

        );

- table-drop example #1 output

        # DROP TABLE "people"

- table-drop example #2

        $sql->table_drop(
          name => 'people',
          condition => 'cascade'
        );

- table-drop example #2 output

        # DROP TABLE "people" CASCADE

## table-rename

- table-rename example #1

README.md  view on Meta::CPAN

- constraint-create example #2 output

        # ALTER TABLE "users" ADD CONSTRAINT "user_profile_id" FOREIGN KEY
        # ("profile_id") REFERENCES "profiles" ("id")

- constraint-create example #3

        $sql->constraint_create(
          on => {
            update => 'cascade',
            delete => 'cascade'
          },
          source => {
            table => 'users',
            column => 'profile_id'
          },
          target => {
            table => 'profiles',
            column => 'id'
          },
          name => 'user_profile_id'

examples/mssql  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * named constraint, on update, on delete
#
# ALTER TABLE ADD CONSTRAINT user_defined_name (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/mssql  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * table, condition
#
# DROP TABLE users CASCADE

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

say $sql->operations->pull->statement;

# table-rename operations

# * table rename
#
# ALTER TABLE peoples RENAME TO people

examples/mssql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

$dbh->begin_work;
while (my $op = $sql->operations->pull) {
  say '[NO SUPPORT] ', $op->statement;
  # $dbh->prepare($op->statement)->execute;
}
$dbh->rollback;

# table-rename operations

examples/mssql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/mssql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/mysql  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * named constraint, on update, on delete
#
# ALTER TABLE ADD CONSTRAINT user_defined_name (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/mysql  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * table, condition
#
# DROP TABLE users CASCADE

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

say $sql->operations->pull->statement;

# table-rename operations

# * table rename
#
# ALTER TABLE peoples RENAME TO people

examples/mysql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

$dbh->begin_work;
while (my $op = $sql->operations->pull) {
  say '[MYSQL] ', $op->statement;
  $dbh->prepare($op->statement)->execute;
}
$dbh->rollback;

# table-rename operations

examples/mysql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/mysql-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/postgres  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * named constraint, on update, on delete
#
# ALTER TABLE ADD CONSTRAINT user_defined_name (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/postgres  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * table, condition
#
# DROP TABLE users CASCADE

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

say $sql->operations->pull->statement;

# table-rename operations

# * table rename
#
# ALTER TABLE peoples RENAME TO people

examples/postgres-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

$dbh->begin_work;
while (my $op = $sql->operations->pull) {
  say '[POSTGRES] ', $op->statement;
  $dbh->prepare($op->statement)->execute;
}
$dbh->rollback;

# table-rename operations

examples/postgres-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/postgres-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/sqlite  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * named constraint, on update, on delete
#
# ALTER TABLE ADD CONSTRAINT user_defined_name (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/sqlite  view on Meta::CPAN

);

say $sql->operations->pull->statement;

# * table, condition
#
# DROP TABLE users CASCADE

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

say $sql->operations->pull->statement;

# table-rename operations

# * table rename
#
# ALTER TABLE peoples RENAME TO people

examples/sqlite-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);

$dbh->begin_work;
while (my $op = $sql->operations->pull) {
  say '[SQLITE3] ', $op->statement;
  $dbh->prepare($op->statement)->execute;
}
$dbh->rollback;

# table-rename operations

examples/sqlite-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

examples/sqlite-tdb  view on Meta::CPAN

    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'

lib/SQL/Engine.pm  view on Meta::CPAN

  );

=item table-drop example #1 output

  # DROP TABLE "people"

=item table-drop example #2

  $sql->table_drop(
    name => 'people',
    condition => 'cascade'
  );

=item table-drop example #2 output

  # DROP TABLE "people" CASCADE

=back

=cut

lib/SQL/Engine.pm  view on Meta::CPAN

=item constraint-create example #2 output

  # ALTER TABLE "users" ADD CONSTRAINT "user_profile_id" FOREIGN KEY
  # ("profile_id") REFERENCES "profiles" ("id")

=item constraint-create example #3

  $sql->constraint_create(
    on => {
      update => 'cascade',
      delete => 'cascade'
    },
    source => {
      table => 'users',
      column => 'profile_id'
    },
    target => {
      table => 'profiles',
      column => 'id'
    },
    name => 'user_profile_id'

lib/SQL/Engine/Grammar.pm  view on Meta::CPAN

method constraint_name(HashRef $data) {

  return $data->{name} || join('_', 'foreign',
    join('_', grep {defined} @{$data->{source}}{qw(schema table column)}),
    join('_', grep {defined} @{$data->{target}}{qw(schema table column)})
  );
}

method constraint_option(Str $name) {
  if (lc($name) eq "cascade") {
    return $self->term('cascade');
  }
  elsif (lc($name) eq "no-action") {
    return $self->term(qw(no action));
  }
  elsif (lc($name) eq "restrict") {
    return $self->term('restrict');
  }
  elsif (lc($name) eq "set-default") {
    return $self->term(qw(set default));
  }