The Perl Advent Calendar needs more articles for 2022. Submit your idea today!

NAME

SQL::Engine - SQL Generation

ABSTRACT

SQL Generation for Perl 5

SYNOPSIS

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;

DESCRIPTION

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.

LIBRARIES

This package uses type constraints from:

Types::Standard

SCENARIOS

This package supports the following scenarios:

validation

use SQL::Engine;

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

# faster, no-validation

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

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.

ATTRIBUTES

This package has the following attributes:

grammar

grammar(Str)

This attribute is read-only, accepts (Str) values, and is optional.

operations

operations(InstanceOf["SQL::Engine::Collection"])

This attribute is read-only, accepts (InstanceOf["SQL::Engine::Collection"]) values, and is optional.

validator

validator(Maybe[InstanceOf["SQL::Validator"]])

This attribute is read-only, accepts (Maybe[InstanceOf["SQL::Validator"]]) values, and is optional.

METHODS

This package implements the following methods:

column_change

column_change(Any %args) : Object

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_create

column_create(Any %args) : Object

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_drop

column_drop(Any %args) : Object

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_rename

column_rename(Any %args) : Object

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.

constraint_create

constraint_create(Any %args) : Object

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_drop

constraint_drop(Any %args) : Object

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.

database_create

database_create(Any %args) : Object

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_drop

database_drop(Any %args) : Object

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.

delete

delete(Any %args) : Object

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.

index_create

index_create(Any %args) : Object

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_drop

index_drop(Any %args) : Object

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.

insert

insert(Any %args) : Object

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.

schema_create

schema_create(Any %args) : Object

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_drop

schema_drop(Any %args) : Object

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_rename

schema_rename(Any %args) : Object

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.

select

select(Any %args) : Object

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.

table_create

table_create(Any %args) : Object

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_drop

table_drop(Any %args) : Object

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_rename

table_rename(Any %args) : Object

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.

transaction

transaction(Any %args) : Object

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.

union

union(Any %args) : Object

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.

update

update(Any %args) : Object

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.

view_create

view_create(Any %args) : Object

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_drop

view_drop(Any %args) : Object

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.

EXAMPLES

This distribution supports generating SQL statements using standard syntax or using database-specific syntax if a grammar is specified. The following is a collection of examples covering the most common operations (using PostgreSQL syntax):

setup

use SQL::Engine;

my $sql = SQL::Engine->new(
  grammar => 'postgres'
);

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

$sql->operations->first->statement;

# SELECT "name" FROM "people"

select

insert

update

delete

table-create

table-drop

table-rename

index-create

index-drop

column-change

column-create

column-drop

column-rename

constraint-create

constraint-drop

database-create

database-drop

schema-create

schema-drop

schema-rename

transaction

view-create

view-drop

union

AUTHOR

Al Newkirk, awncorp@cpan.org

LICENSE

Copyright (C) 2011-2019, Al Newkirk, et al.

This is free software; you can redistribute it and/or modify it under the terms of the The Apache License, Version 2.0, as elucidated in the "license file".

PROJECT

Wiki

Project

Initiatives

Milestones

Contributing

Issues