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 } ] } } ] ); 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. union example #1 # given: synopsis $sql->operations->clear; $sql->union( queries => [ { select => { from => { table => 'customers', }, columns => [ { column => 'name', } ] } }, { select => { from => { table => 'employees', }, columns => [ { column => 'name', } ] } } ] ); 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" union union example #1 $sql->union( queries => [ { select => { from => { table => 'customers', }, columns => [ { column => 'name', } ] } }, { select => { from => { table => 'employees', }, columns => [ { column => 'name', } ] } } ] ); union example #1 output # (SELECT "name" FROM "customers") UNION (SELECT "name" FROM "employees") 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