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_change example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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_drop example #1

# given: synopsis

$sql->operations->clear;

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

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.

column_rename example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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.

constraint_drop example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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.

database_drop example #1

# given: synopsis

$sql->operations->clear;

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

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.

delete example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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.

index_drop example #1

# given: synopsis

$sql->operations->clear;

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

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.

insert example #1

# 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']
      }
    }
  ]
);

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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_drop example #1

# given: synopsis

$sql->operations->clear;

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

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.

schema_rename example #1

# given: synopsis

$sql->operations->clear;

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

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.

select example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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_drop example #1

# given: synopsis

$sql->operations->clear;

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

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.

table_rename example #1

# given: synopsis

$sql->operations->clear;

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

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.

transaction example #1

# given: synopsis

$sql->operations->clear;

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

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.

update example #1

# given: synopsis

$sql->operations->clear;

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

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_create example #1

# given: synopsis

$sql->operations->clear;

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

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.

view_drop example #1

# given: synopsis

$sql->operations->clear;

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

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

select example #1

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

select example #1 output

# SELECT * FROM "users"

select example #2

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

select example #2 output

# SELECT "id", "name" FROM "users"

select example #3

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

select example #3 output

# SELECT * FROM "users" WHERE "id" = ?

select example #4

$sql->select(
  from => {
    table => 'users',
    alias => 'u'
  },
  columns => [
    {
      column => '*',
      alias => 'u'
    }
  ],
  joins => [
    {
      with => {
        table => 'tasklists',
        alias => 't'
      },
      having => [
        {
          eq => [
            {
              column => 'id',
              alias => 'u'
            },
            {
              column => 'user_id',
              alias => 't'
            }
          ]
        }
      ]
    }
  ],
  where => [
    {
      eq => [
        {
          column => 'id',
          alias => 'u'
        },
        {
          binding => 'id'
        }
      ]
    }
  ]
);

select example #4 output

# SELECT "u".* FROM "users" "u"
# JOIN "tasklists" "t" ON "u"."id" = "t"."user_id" WHERE "u"."id" = ?

select example #5

$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      function => ['count', { column => 'user_id' }]
    }
  ],
  group_by => [
    {
      column => 'user_id'
    }
  ]
);

select example #5 output

# SELECT count("user_id") FROM "tasklists" GROUP BY "user_id"

select example #6

$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      function => ['count', { column => 'user_id' }]
    }
  ],
  group_by => [
    {
      column => 'user_id'
    }
  ],
  having => [
    {
      gt => [
        {
          function => ['count', { column => 'user_id' }]
        },
        1
      ]
    }
  ]
);

select example #6 output

# SELECT count("user_id") FROM "tasklists" GROUP BY "user_id" HAVING
# count("user_id") > 1

select example #7

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

select example #7 output

# SELECT * FROM "tasklists" ORDER BY "user_id"

select example #8

$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      column => '*'
    }
  ],
  order_by => [
    {
      column => 'user_id'
    },
    {
      column => 'id',
      sort => 'desc'
    }
  ]
);

select example #8 output

# SELECT * FROM "tasklists" ORDER BY "user_id", "id" DESC

select example #9

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

select example #9 output

# SELECT * FROM "tasks" LIMIT 5

select example #10

$sql->select(
  from => {
    table => 'tasks'
  },
  columns => [
    {
      column => '*'
    }
  ],
  rows => {
    limit => 5,
    offset => 1
  }
);

select example #10 output

# SELECT * FROM "tasks" LIMIT 5, OFFSET 1

select example #11

$sql->select(
  from => [
    {
      table => 'tasklists',
      alias => 't1'
    },
    {
      table => 'tasks',
      alias => 't2'
    }
  ],
  columns => [
    {
      column => '*',
      alias => 't1'
    },
    {
      column => '*',
      alias => 't1'
    }
  ],
  where => [
    {
      eq => [
        {
          column => 'tasklist_id',
          alias => 't2'
        },
        {
          column => 'id',
          alias => 't1'
        }
      ]
    }
  ]
);

select example #11 output

# SELECT "t1".*, "t1".* FROM "tasklists" "t1", "tasks" "t2"
# WHERE "t2"."tasklist_id" = "t1"."id"

insert

insert example #1

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

insert example #1 output

# INSERT INTO "users" VALUES (NULL, 'Rob Zombie', now(), now(), now())

insert example #2

$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'id'
    },
    {
      column => 'name'
    },
    {
      column => 'created'
    },
    {
      column => 'updated'
    },
    {
      column => 'deleted'
    }
  ],
  values => [
    {
      value => undef
    },
    {
      value => 'Rob Zombie'
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    }
  ]
);

insert example #2 output

# INSERT INTO "users" ("id", "name", "created", "updated", "deleted")
# VALUES (NULL, 'Rob Zombie', now(), now(), now())

insert example #3

$sql->insert(
  into => {
    table => 'users'
  },
  default => 1
);

insert example #3 output

# INSERT INTO "users" DEFAULT VALUES

insert example #4

$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    },
    {
      column => 'user_id'
    }
  ],
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => 'name'
        },
        {
          column => 'id'
        }
      ]
    }
  }
);

insert example #4 output

# INSERT INTO "users" ("name", "user_id") SELECT "name", "id" FROM "users"

insert example #5

$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    },
    {
      column => 'user_id'
    }
  ],
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => 'name'
        },
        {
          column => 'id'
        }
      ],
      where => [
        {
          'not-null' => {
            column => 'deleted'
          }
        }
      ]
    }
  }
);

insert example #5 output

# INSERT INTO "users" ("name", "user_id") SELECT "name", "id" FROM "users"
# WHERE "deleted" IS NOT NULL

update

update example #1

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

update example #1 output

# UPDATE "users" SET "updated" = now()

update example #2

$sql->update(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name',
      value => { function => ['concat', '[deleted]', ' ', { column => 'name' }] }
    }
  ],
  where => [
    {
      'not-null' => {
        column => 'deleted'
      }
    }
  ]
);

update example #2 output

# UPDATE "users" SET "name" = concat('[deleted]', ' ', "name") WHERE
# "deleted" IS NOT NULL

update example #3

$sql->update(
  for => {
    table => 'users',
    alias => 'u1'
  },
  columns => [
    {
      column => 'updated',
      alias => 'u1',
      value => { function => ['now'] }
    }
  ],
  where => [
    {
      in => [
        {
          column => 'id',
          alias => 'u1'
        },
        {
          subquery => {
            select => {
              from => {
                table => 'users',
                alias => 'u2'
              },
              columns => [
                {
                  column => 'id',
                  alias => 'u2'
                }
              ],
              joins => [
                {
                  with => {
                    table => 'tasklists',
                    alias => 't1'
                  },
                  having => [
                    {
                      eq => [
                        {
                          column => 'id',
                          alias => 'u2'
                        },
                        {
                          column => 'user_id',
                          alias => 't1'
                        }
                      ]
                    }
                  ]
                }
              ],
              where => [
                {
                  eq => [
                    {
                      column => 'id',
                      alias => 'u2'
                    },
                    {
                      binding => 'user_id'
                    }
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  ]
);

update example #3 output

# UPDATE "users" "u1" SET "u1"."updated" = now() WHERE "u1"."id" IN (SELECT
# "u2"."id" FROM "users" "u2" JOIN "tasklists" "t1" ON "u2"."id" =
# "t1"."user_id" WHERE "u2"."id" = ?)

delete

delete example #1

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

delete example #1 output

# DELETE FROM "tasklists"

delete example #2

$sql->delete(
  from => {
    table => 'tasklists'
  },
  where => [
    {
      'not-null' => {
        column => 'deleted'
      }
    }
  ]
);

delete example #2 output

# DELETE FROM "tasklists" WHERE "deleted" IS NOT NULL

table-create

table-create example #1

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

table-create example #1 output

# CREATE TABLE "users" ("id" integer PRIMARY KEY)

table-create example #2

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

table-create example #2 output

# CREATE TABLE "users" ("id" integer PRIMARY KEY, "name" text, "created"
# timestamp(0) without time zone, "updated" timestamp(0) without time zone,
# "deleted" timestamp(0) without time zone)

table-create example #3

$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'name',
      type => 'text',
    },
    {
      name => 'created',
      type => 'datetime',
    },
    {
      name => 'updated',
      type => 'datetime',
    },
    {
      name => 'deleted',
      type => 'datetime',
    },
  ],
  temp => 1
);

table-create example #3 output

# CREATE TEMPORARY TABLE "users" ("id" integer PRIMARY KEY, "name" text,
# "created" timestamp(0) without time zone, "updated" timestamp(0) without
# time zone, "deleted" timestamp(0) without time zone)

table-create example #4

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

table-create example #4 output

# CREATE TABLE "people" AS SELECT * FROM "users"

table-drop

table-drop example #1

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

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

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

table-rename example #1 output

# ALTER TABLE "peoples" RENAME TO "people"

index-create

index-create example #1

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

index-create example #1 output

# CREATE INDEX "index_users_name" ON "users" ("name")

index-create example #2

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

index-create example #2 output

# CREATE UNIQUE INDEX "unique_users_email" ON "users" ("email")

index-create example #3

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

index-create example #3 output

# CREATE INDEX "user_name_index" ON "users" ("name")

index-create example #4

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

index-create example #4 output

# CREATE UNIQUE INDEX "user_email_unique" ON "users" ("email")

index-create example #5

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

index-create example #5 output

# CREATE INDEX "index_users_login_email" ON "users" ("login", "email")

index-drop

index-drop example #1

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

index-drop example #1 output

# DROP INDEX "index_users_name"

index-drop example #2

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

index-drop example #2 output

# DROP INDEX "unique_users_email"

index-drop example #3

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

index-drop example #3 output

# DROP INDEX "user_email_unique"

column-change

column-change example #1

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

column-change example #1 output

# BEGIN TRANSACTION
# ALTER TABLE "users" ALTER "accessed" TYPE timestamp(0) without time zone
# ALTER TABLE "users" ALTER "accessed" DROP NOT NULL
# ALTER TABLE "users" ALTER "accessed" DROP DEFAULT
# COMMIT

column-change example #2

$sql->column_change(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    default => { function => ['now'] }
  }
);

column-change example #2 output

# BEGIN TRANSACTION
# ALTER TABLE "users" ALTER "accessed" TYPE timestamp(0) without time zone
# ALTER TABLE "users" ALTER "accessed" SET DEFAULT now()
# COMMIT

column-change example #3

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

column-change example #3 output

# BEGIN TRANSACTION
# ALTER TABLE "users" ALTER "accessed" TYPE timestamp(0) without time zone
# ALTER TABLE "users" ALTER "accessed" DROP NOT NULL
# ALTER TABLE "users" ALTER "accessed" SET DEFAULT now()
# COMMIT

column-create

column-create example #1

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

column-create example #1 output

# ALTER TABLE "users" ADD COLUMN "accessed" timestamp(0) without time zone

column-create example #2

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

column-create example #2 output

# ALTER TABLE "users" ADD COLUMN "accessed" timestamp(0) without time zone
# NULL

column-create example #3

$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    nullable => 1,
    default => {
      function => ['now']
    }
  }
);

column-create example #3 output

# ALTER TABLE "users" ADD COLUMN "accessed" timestamp(0) without time zone
# NULL DEFAULT now()

column-create example #4

$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'ref',
    type => 'uuid',
    primary => 1
  }
);

column-create example #4 output

# ALTER TABLE "users" ADD COLUMN "ref" uuid PRIMARY KEY

column-drop

column-drop example #1

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

column-drop example #1 output

# ALTER TABLE "users" DROP COLUMN "accessed"

column-rename

column-rename example #1

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

column-rename example #1 output

# ALTER TABLE "users" RENAME COLUMN "accessed" TO "accessed_at"

constraint-create

constraint-create example #1

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

constraint-create example #1 output

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

constraint-create example #2

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

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'
);

constraint-create example #3 output

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

constraint-drop

constraint-drop example #1

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

constraint-drop example #1 output

# ALTER TABLE "users" DROP CONSTRAINT "foreign_users_profile_id_profiles_id"

constraint-drop example #2

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

constraint-drop example #2 output

# ALTER TABLE "users" DROP CONSTRAINT "user_profile_id"

database-create

database-create example #1

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

database-create example #1 output

# CREATE DATABASE "todoapp"

database-drop

database-drop example #1

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

database-drop example #1 output

# DROP DATABASE "todoapp"

schema-create

schema-create example #1

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

schema-create example #1 output

# CREATE SCHEMA "private"

schema-drop

schema-drop example #1

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

schema-drop example #1 output

# DROP SCHEMA "private"

schema-rename

schema-rename example #1

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

schema-rename example #1 output

# ALTER SCHEMA "private" RENAME TO "restricted"

transaction

transaction example #1

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

transaction example #1 output

# BEGIN TRANSACTION
# CREATE TABLE "users" ("id" integer PRIMARY KEY)
# COMMIT

transaction example #2

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

transaction example #2 output

# BEGIN TRANSACTION EXCLUSIVE
# CREATE TABLE "users" ("id" integer PRIMARY KEY)
# COMMIT

view-create

view-create example #1

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

view-create example #1 output

# CREATE VIEW "active_users" AS SELECT * FROM "users" WHERE "deleted" IS NOT
# NULL

view-create example #2

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

view-create example #2 output

# CREATE TEMPORARY VIEW "active_users" AS SELECT * FROM "users" WHERE
# "deleted" IS NOT NULL

view-drop

view-drop example #1

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

view-drop example #1 output

# DROP VIEW "active_users"

AUTHOR

Al Newkirk, awncorp@cpan.org

LICENSE

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

To install SQL::Engine, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::Engine

CPAN shell

perl -MCPAN -e shell
install SQL::Engine

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

ABSTRACT

SYNOPSIS

DESCRIPTION

LIBRARIES

SCENARIOS

validation

ATTRIBUTES

grammar

operations

validator

METHODS

column_change

column_create

column_drop

column_rename

constraint_create

constraint_drop

database_create

database_drop

delete

index_create

index_drop

insert

schema_create

schema_drop

schema_rename

select

table_create

table_drop

table_rename

transaction

update

view_create

view_drop

EXAMPLES

setup

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

AUTHOR

LICENSE

PROJECT

Module Install Instructions