SQL-Engine

 view release on metacpan or  search on metacpan

examples/postgres-tdb  view on Meta::CPAN

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

$sql->table_drop(
  name => 'users',
);

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

# table-drop operations

# * table
#
# DROP TABLE users

$sql->table_create(
  name => 'people',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

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

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

# * table, condition
#
# DROP TABLE users CASCADE

$sql->table_create(
  name => 'people',
  columns => [
    {
      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

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

$sql->table_create(
  name => 'peoples',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->table_rename(
  name => {
    old => 'peoples',
    new => 'people'
  }
);

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

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

# column-create operations

# * column type
#
# ALTER TABLE users ADD COLUMN accessed datetime

$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

examples/postgres-tdb  view on Meta::CPAN

$sql->constraint_create(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'
);

$sql->table_drop(
  name => 'users'
);

$sql->table_drop(
  name => 'profiles'
);

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

# * 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->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'profile_id',
      type => 'integer'
    }
  ]
);

$sql->table_create(
  name => 'profiles',
  columns => [
    {
      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'
);

$sql->table_drop(
  name => 'users'
);

$sql->table_drop(
  name => 'profiles'
);

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

# constraint-drop operations

# * constraint
#
# ALTER TABLE DROP CONSTRAINT generated

$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'profile_id',
      type => 'integer'
    }
  ]
);

$sql->table_create(
  name => 'profiles',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);

$sql->constraint_create(

examples/postgres-tdb  view on Meta::CPAN

);

$sql->constraint_drop(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  }
);

$sql->table_drop(
  name => 'users'
);

$sql->table_drop(
  name => 'profiles'
);

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

# * named constraint
#
# ALTER TABLE DROP CONSTRAINT user_defined_name

$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'profile_id',
      type => 'integer'
    }
  ]
);

$sql->table_create(
  name => 'profiles',
  columns => [
    {
      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'
);

$sql->constraint_drop(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'
);

$sql->table_drop(
  name => 'users'
);

$sql->table_drop(
  name => 'profiles'
);

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

# index-create operations

# * index
#
# CREATE INDEX index_users_email ON users (name)

$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'number',
      primary => 1
    },
    {
      name => 'name',
      type => 'string'
    }
  ]
);