SQL::Engine

SQL Generation

SQL Generation for Perl 5

method: column_change method: column_create method: column_drop method: column_rename method: constraint_create method: constraint_drop method: database_create method: database_drop method: delete method: index_create method: index_drop method: insert method: schema_create method: schema_drop method: schema_rename method: select method: table_create method: table_drop method: table_rename method: transaction method: update method: view_create method: view_drop method: union

use SQL::Engine;

my $sql = SQL::Engine->new;

$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'id'
    },
    {
      column => 'name'
    }
  ],
  values => [
    {
      value => undef
    },
    {
      value => {
        binding => 'name'
      }
    },
  ]
);

# then, e.g.
#
# my $dbh = DBI->connect;
#
# for my $operation ($sql->operations->list) {
#   my $statement = $operation->statement;
#   my @bindings  = $operation->parameters({ name => 'Rob Zombie' });
#
#   my $sth = $dbh->prepate($statement);
#
#   $sth->execute(@bindings);
# }
#
# $dbh->disconnect;

Types::Standard

grammar: ro, opt, Str operations: ro, opt, InstanceOf["SQL::Engine::Collection"] validator: ro, opt, Maybe[InstanceOf["SQL::Validator"]]

This package provides an interface and builders which generate SQL statements, by default using a standard SQL syntax or vendor-specific syntax if supported and provided to the constructor using the "grammar" property. This package does not require a database connection, by design, which gives users complete control over how connections and statement handles are managed.

This package supports automatic validation of operations using SQL::Validator which can be passed to the constructor as the value of the "validator" property. This object will be generated if not provided. Alternatively, automated validation can be disabled by passing the "undefined" value to the "validator" property on object construction. Doing so enhances the performance of SQL generation at the cost of not verifying that the instructions provided are correct.

use SQL::Engine;

my $sql = SQL::Engine->new(
  validator => undef
);

# faster, no-validation

$sql->select(
  from => {
    table => 'users'
  },
  columns => [
    {
      column => '*'
    }
  ]
);

The column_change method produces SQL operations which changes a table column definition. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ColumnChange.

column_change(Any %args) : Object

=example-1 column_change

# given: synopsis

$sql->operations->clear;

$sql->column_change(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    nullable => 1
  }
);

The column_create method produces SQL operations which create a new table column. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ColumnCreate.

column_create(Any %args) : Object

=example-1 column_create

# given: synopsis

$sql->operations->clear;

$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime'
  }
);

The column_drop method produces SQL operations which removes an existing table column. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ColumnDrop.

column_drop(Any %args) : Object

=example-1 column_drop

# given: synopsis

$sql->operations->clear;

$sql->column_drop(
  table => 'users',
  column => 'accessed'
);

The column_rename method produces SQL operations which renames an existing table column. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ColumnRename.

column_rename(Any %args) : Object

=example-1 column_rename

# given: synopsis

$sql->operations->clear;

$sql->column_rename(
  for => {
    table => 'users'
  },
  name => {
    old => 'accessed',
    new => 'accessed_at'
  }
);

The constraint_create method produces SQL operations which creates a new table constraint. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ConstraintCreate.

constraint_create(Any %args) : Object

=example-1 constraint_create

# given: synopsis

$sql->operations->clear;

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

The constraint_drop method produces SQL operations which removes an existing table constraint. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ConstraintDrop.

constraint_drop(Any %args) : Object

=example-1 constraint_drop

# given: synopsis

$sql->operations->clear;

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

The database_create method produces SQL operations which creates a new database. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::DatabaseCreate.

database_create(Any %args) : Object

=example-1 database_create

# given: synopsis

$sql->operations->clear;

$sql->database_create(
  name => 'todoapp'
);

The database_drop method produces SQL operations which removes an existing database. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::DatabaseDrop.

database_drop(Any %args) : Object

=example-1 database_drop

# given: synopsis

$sql->operations->clear;

$sql->database_drop(
  name => 'todoapp'
);

The delete method produces SQL operations which deletes rows from a table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Delete.

delete(Any %args) : Object

=example-1 delete

# given: synopsis

$sql->operations->clear;

$sql->delete(
  from => {
    table => 'tasklists'
  }
);

The index_create method produces SQL operations which creates a new table index. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::IndexCreate.

index_create(Any %args) : Object

=example-1 index_create

# given: synopsis

$sql->operations->clear;

$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ]
);

The index_drop method produces SQL operations which removes an existing table index. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::IndexDrop.

index_drop(Any %args) : Object

=example-1 index_drop

# given: synopsis

$sql->operations->clear;

$sql->index_drop(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ]
);

The insert method produces SQL operations which inserts rows into a table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Insert.

insert(Any %args) : Object

=example-1 insert

# given: synopsis

$sql->operations->clear;

$sql->insert(
  into => {
    table => 'users'
  },
  values => [
    {
      value => undef
    },
    {
      value => 'Rob Zombie'
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    }
  ]
);

The schema_create method produces SQL operations which creates a new schema. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::SchemaCreate.

schema_create(Any %args) : Object

=example-1 schema_create

# given: synopsis

$sql->operations->clear;

$sql->schema_create(
  name => 'private',
);

The schema_drop method produces SQL operations which removes an existing schema. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::SchemaDrop.

schema_drop(Any %args) : Object

=example-1 schema_drop

# given: synopsis

$sql->operations->clear;

$sql->schema_drop(
  name => 'private',
);

The schema_rename method produces SQL operations which renames an existing schema. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::SchemaRename.

schema_rename(Any %args) : Object

=example-1 schema_rename

# given: synopsis

$sql->operations->clear;

$sql->schema_rename(
  name => {
    old => 'private',
    new => 'restricted'
  }
);

The select method produces SQL operations which select rows from a table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Select.

select(Any %args) : Object

=example-1 select

# given: synopsis

$sql->operations->clear;

$sql->select(
  from => {
    table => 'people'
  },
  columns => [
    { column => 'name' }
  ]
);

The table_create method produces SQL operations which creates a new table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::TableCreate.

table_create(Any %args) : Object

=example-1 table_create

# given: synopsis

$sql->operations->clear;

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

The table_drop method produces SQL operations which removes an existing table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::TableDrop.

table_drop(Any %args) : Object

=example-1 table_drop

# given: synopsis

$sql->operations->clear;

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

The table_rename method produces SQL operations which renames an existing table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::TableRename.

table_rename(Any %args) : Object

=example-1 table_rename

# given: synopsis

$sql->operations->clear;

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

The transaction method produces SQL operations which represents an atomic database operation. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Transaction.

transaction(Any %args) : Object

=example-1 transaction

# given: synopsis

$sql->operations->clear;

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

The update method produces SQL operations which update rows in a table. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Update.

update(Any %args) : Object

=example-1 update

# given: synopsis

$sql->operations->clear;

$sql->update(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'updated',
      value => { function => ['now'] }
    }
  ]
);

The view_create method produces SQL operations which creates a new table view. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ViewCreate.

view_create(Any %args) : Object

=example-1 view_create

# given: synopsis

$sql->operations->clear;

$sql->view_create(
  name => 'active_users',
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => '*'
        }
      ],
      where => [
        {
          'not-null' => {
            column => 'deleted'
          }
        }
      ]
    }
  }
);

The view_drop method produces SQL operations which removes an existing table view. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::ViewDrop.

view_drop(Any %args) : Object

=example-1 view_drop

# given: synopsis

$sql->operations->clear;

$sql->view_drop(
  name => 'active_users'
);

The union method produces SQL operations which returns a results from two or more select queries. The arguments expected are the constructor arguments accepted by SQL::Engine::Builder::Union.

union(Any %args) : Object

=example-1 union

# given: synopsis

$sql->operations->clear;

$sql->union(
  queries => [
    {
      select => {
        from => {
          table => 'customers',
        },
        columns => [
          {
            column => 'name',
          }
        ]
      }
    },
    {
      select => {
        from => {
          table => 'employees',
        },
        columns => [
          {
            column => 'name',
          }
        ]
      }
    }
  ]
);

cpanm SQL::Engine

perl -MCPAN -e shell
install SQL::Engine