Dave Cross: Still Munging Data With Perl: Online event - Mar 17 Learn more

NAME

SQL::Engine::Grammar - Standard Grammar

ABSTRACT

SQL::Engine Standard Grammar

SYNOPSIS

my $grammar = SQL::Engine::Grammar->new(
schema => {
select => {
from => {
table => 'users'
},
columns => [
{
column => '*'
}
]
}
}
);
# $grammar->execute;

DESCRIPTION

This package provides methods for converting json-sql data structures into SQL statements.

LIBRARIES

This package uses type constraints from:

Types::Standard

ATTRIBUTES

This package has the following attributes:

operations

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

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

schema

schema(HashRef)

This attribute is read-only, accepts (HashRef) values, and is required.

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:

binding

binding(Str $name) : Str

The binding method registers a SQL statement binding (or placeholder).

binding example #1
# given: synopsis
$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');

column_change

column_change(HashRef $data) : Object

The column_change method generates SQL statements to change a column definition.

column_change example #1
my $grammar = SQL::Engine::Grammar->new({
schema => {
'column-change' => {
for => {
table => 'users'
},
column => {
name => 'accessed',
type => 'datetime',
nullable => 1
}
}
}
});
$grammar->column_change($grammar->schema->{'column-change'});

column_create

column_create(HashRef $data) : Object

The column_create method generates SQL statements to add a new table column.

column_create example #1
# given: synopsis
$grammar->column_create({
for => {
table => 'users'
},
column => {
name => 'accessed',
type => 'datetime'
}
});

column_definition

column_definition(HashRef $data) : HashRef

The column_definition method column definition SQL statement fragments.

column_definition example #1
# given: synopsis
my $column_definition = $grammar->column_definition({
name => 'id',
type => 'number',
primary => 1
});

column_drop

column_drop(HashRef $data) : Object

The column_drop method generates SQL statements to remove a table column.

column_drop example #1
# given: synopsis
$grammar->column_drop({
table => 'users',
column => 'accessed'
});

column_rename

column_rename(HashRef $data) : Object

The column_rename method generates SQL statements to rename a table column.

column_rename example #1
# given: synopsis
$grammar->column_rename({
for => {
table => 'users'
},
name => {
old => 'accessed',
new => 'accessed_at'
}
});

column_specification

column_specification(HashRef $data) : Str

The column_specification method a column definition SQL statment partial.

column_specification example #1
# given: synopsis
my $column_specification = $grammar->column_specification({
name => 'id',
type => 'number',
primary => 1
});

constraint_create

constraint_create(HashRef $data) : Object

The constraint_create method generates SQL statements to create a table constraint.

constraint_create example #1
# given: synopsis
$grammar->constraint_create({
source => {
table => 'users',
column => 'profile_id'
},
target => {
table => 'profiles',
column => 'id'
}
});

constraint_drop

constraint_drop(HashRef $data) : Object

The constraint_drop method generates SQL statements to remove a table constraint.

constraint_drop example #1
# given: synopsis
$grammar->constraint_drop({
source => {
table => 'users',
column => 'profile_id'
},
target => {
table => 'profiles',
column => 'id'
}
});

constraint_name

constraint_name(HashRef $data) : Str

The constraint_name method returns the generated constraint name.

constraint_name example #1
# given: synopsis
my $constraint_name = $grammar->constraint_name({
source => {
table => 'users',
column => 'profile_id'
},
target => {
table => 'profiles',
column => 'id'
}
});

constraint_option

constraint_option(Str $name) : Str

The constraint_option method returns a SQL expression for the constraint option provided.

constraint_option example #1
# given: synopsis
$grammar->constraint_option('no-action');

criteria

criteria(ArrayRef $data) : ArrayRef[Str]

The criteria method returns a list of SQL expressions.

criteria example #1
# given: synopsis
my $criteria = $grammar->criteria([
{
eq => [{ column => 'id' }, 123]
},
{
'not-null' => { column => 'deleted' }
}
]);

criterion

criterion(HashRef $data) : Str

The criterion method returns a SQL expression.

criterion example #1
# given: synopsis
my $criterion = $grammar->criterion({
in => [{ column => 'theme' }, 'light', 'dark']
});

database_create

database_create(HashRef $data) : Object

The database_create method generates SQL statements to create a database.

database_create example #1
# given: synopsis
$grammar->database_create({
name => 'todoapp'
});

database_drop

database_drop(HashRef $data) : Object

The database_drop method generates SQL statements to remove a database.

database_drop example #1
# given: synopsis
$grammar->database_drop({
name => 'todoapp'
});

delete

delete(HashRef $data) : Object

The delete method generates SQL statements to delete table rows.

delete example #1
# given: synopsis
$grammar->delete({
from => {
table => 'tasklists'
}
});

execute

execute() : Object

The execute method validates and processes the object instruction.

execute example #1
# given: synopsis
$grammar->operations->clear;
$grammar->execute;

expression

expression(Any $data) : Any

The expression method returns a SQL expression representing the data provided.

expression example #1
# given: synopsis
$grammar->expression(undef);
# NULL

index_create

index_create(HashRef $data) : Object

The index_create method generates SQL statements to create a table index.

index_create example #1
# given: synopsis
$grammar->index_create({
for => {
table => 'users'
},
columns => [
{
column => 'name'
}
]
});

index_drop

index_drop(HashRef $data) : Object

The index_drop method generates SQL statements to remove a table index.

index_drop example #1
# given: synopsis
$grammar->index_drop({
for => {
table => 'users'
},
columns => [
{
column => 'name'
}
]
});

index_name

index_name(HashRef $data) : Str

The index_name method returns the generated index name.

index_name example #1
# given: synopsis
my $index_name = $grammar->index_name({
for => {
table => 'users'
},
columns => [
{
column => 'email'
}
],
unique => 1
});

insert

insert(HashRef $data) : Object

The insert method generates SQL statements to insert table rows.

insert example #1
# given: synopsis
$grammar->insert({
into => {
table => 'users'
},
values => [
{
value => undef
},
{
value => 'Rob Zombie'
},
{
value => {
function => ['now']
}
},
{
value => {
function => ['now']
}
},
{
value => {
function => ['now']
}
}
]
});

name

name(Any @args) : Str

The name method returns a qualified quoted object name.

name example #1
# given: synopsis
my $name = $grammar->name(undef, 'public', 'users');
# "public"."users"

operation

operation(Str $statement) : InstanceOf["SQL::Engine::Operation"]

The operation method creates and appends an operation to the "operations" collection.

operation example #1
# given: synopsis
$grammar->operation('SELECT TRUE');

process

process(Mayb[HashRef] $schema) : Object

The process method processes the object instructions.

process example #1
# given: synopsis
$grammar->process;

schema_create

schema_create(HashRef $data) : Object

The schema_create method generates SQL statements to create a schema.

schema_create example #1
# given: synopsis
$grammar->schema_create({
name => 'private',
});

schema_drop

schema_drop(HashRef $data) : Object

The schema_drop method generates SQL statements to remove a schema.

schema_drop example #1
# given: synopsis
$grammar->schema_drop({
name => 'private',
});

schema_rename

schema_rename(HashRef $data) : Object

The schema_rename method generates SQL statements to rename a schema.

schema_rename example #1
# given: synopsis
$grammar->schema_rename({
name => {
old => 'private',
new => 'restricted'
}
});

select

select(HashRef $data) : Object

The select method generates SQL statements to select table rows.

select example #1
# given: synopsis
$grammar->select({
from => {
table => 'people'
},
columns => [
{ column => 'name' }
]
});

table

table(HashRef $data) : Str

The table method returns a qualified quoted table name.

table example #1
# given: synopsis
my $table = $grammar->table({
schema => 'public',
table => 'users',
alias => 'u'
});

table_create

table_create(HashRef $data) : Object

The table_create method generates SQL statements to create a table.

table_create example #1
# given: synopsis
$grammar->table_create({
name => 'users',
columns => [
{
name => 'id',
type => 'integer',
primary => 1
}
]
});

table_drop

table_drop(HashRef $data) : Object

The table_drop method generates SQL statements to remove a table.

table_drop example #1
# given: synopsis
$grammar->table_drop({
name => 'people'
});

table_rename

table_rename(HashRef $data) : Object

The table_rename method generates SQL statements to rename a table.

table_rename example #1
# given: synopsis
$grammar->table_rename({
name => {
old => 'peoples',
new => 'people'
}
});

term

term(Str @args) : Str

The term method returns a SQL keyword.

term example #1
# given: synopsis
$grammar->term('end');

transaction

transaction(HashRef $data) : Object

The transaction method generates SQL statements to commit an atomic database transaction.

transaction example #1
my $grammar = SQL::Engine::Grammar->new({
schema => {
'transaction' => {
queries => [
{
'table-create' => {
name => 'users',
columns => [
{
name => 'id',
type => 'integer',
primary => 1
}
]
}
}
]
}
}
});
$grammar->transaction($grammar->schema->{'transaction'});

type

type(HashRef $data) : Str

The type method return the SQL representation for a data type.

type example #1
# given: synopsis
$grammar->type({
type => 'datetime-wtz'
});
# datetime

type_binary

type_binary(HashRef $data) : Str

The type_binary method returns the SQL expression representing a binary data type.

type_binary example #1
# given: synopsis
$grammar->type_binary({});
# blob

type_boolean

type_boolean(HashRef $data) : Str

The type_boolean method returns the SQL expression representing a boolean data type.

type_boolean example #1
# given: synopsis
$grammar->type_boolean({});
# tinyint(1)

type_char

type_char(HashRef $data) : Str

The type_char method returns the SQL expression representing a char data type.

type_char example #1
# given: synopsis
$grammar->type_char({});
# varchar

type_date

type_date(HashRef $data) : Str

The type_date method returns the SQL expression representing a date data type.

type_date example #1
# given: synopsis
$grammar->type_date({});
# date

type_datetime

type_datetime(HashRef $data) : Str

The type_datetime method returns the SQL expression representing a datetime data type.

type_datetime example #1
# given: synopsis
$grammar->type_datetime({});
# datetime

type_datetime_wtz

type_datetime_wtz(HashRef $data) : Str

The type_datetime_wtz method returns the SQL expression representing a datetime (and timezone) data type.

type_datetime_wtz example #1
# given: synopsis
$grammar->type_datetime_wtz({});
# datetime

type_decimal

type_decimal(HashRef $data) : Str

The type_decimal method returns the SQL expression representing a decimal data type.

type_decimal example #1
# given: synopsis
$grammar->type_decimal({});
# numeric

type_double

type_double(HashRef $data) : Str

The type_double method returns the SQL expression representing a double data type.

type_double example #1
# given: synopsis
$grammar->type_double({});
# float

type_enum

type_enum(HashRef $data) : Str

The type_enum method returns the SQL expression representing a enum data type.

type_enum example #1
# given: synopsis
$grammar->type_enum({});
# varchar

type_float

type_float(HashRef $data) : Str

The type_float method returns the SQL expression representing a float data type.

type_float example #1
# given: synopsis
$grammar->type_float({});
# float

type_integer

type_integer(HashRef $data) : Str

The type_integer method returns the SQL expression representing a integer data type.

type_integer example #1
# given: synopsis
$grammar->type_integer({});
# integer

type_integer_big

type_integer_big(HashRef $data) : Str

The type_integer_big method returns the SQL expression representing a big-integer data type.

type_integer_big example #1
# given: synopsis
$grammar->type_integer_big({});
# integer

type_integer_big_unsigned

type_integer_big_unsigned(HashRef $data) : Str

The type_integer_big_unsigned method returns the SQL expression representing a big unsigned integer data type.

type_integer_big_unsigned example #1
# given: synopsis
$grammar->type_integer_big_unsigned({});
# integer

type_integer_medium

type_integer_medium(HashRef $data) : Str

The type_integer_medium method returns the SQL expression representing a medium integer data type.

type_integer_medium example #1
# given: synopsis
$grammar->type_integer_medium({});
# integer

type_integer_medium_unsigned

type_integer_medium_unsigned(HashRef $data) : Str

The type_integer_medium_unsigned method returns the SQL expression representing a unsigned medium integer data type.

type_integer_medium_unsigned example #1
# given: synopsis
$grammar->type_integer_medium_unsigned({});
# integer

type_integer_small

type_integer_small(HashRef $data) : Str

The type_integer_small method returns the SQL expression representing a small integer data type.

type_integer_small example #1
# given: synopsis
$grammar->type_integer_small({});
# integer

type_integer_small_unsigned

type_integer_small_unsigned(HashRef $data) : Str

The type_integer_small_unsigned method returns the SQL expression representing a unsigned small integer data type.

type_integer_small_unsigned example #1
# given: synopsis
$grammar->type_integer_small_unsigned({});
# integer

type_integer_tiny

type_integer_tiny(HashRef $data) : Str

The type_integer_tiny method returns the SQL expression representing a tiny integer data type.

type_integer_tiny example #1
# given: synopsis
$grammar->type_integer_tiny({});
# integer

type_integer_tiny_unsigned

type_integer_tiny_unsigned(HashRef $data) : Str

The type_integer_tiny_unsigned method returns the SQL expression representing a unsigned tiny integer data type.

type_integer_tiny_unsigned example #1
# given: synopsis
$grammar->type_integer_tiny_unsigned({});
# integer

type_integer_unsigned

type_integer_unsigned(HashRef $data) : Str

The type_integer_unsigned method returns the SQL expression representing a unsigned integer data type.

type_integer_unsigned example #1
# given: synopsis
$grammar->type_integer_unsigned({});
# integer

type_json

type_json(HashRef $data) : Str

The type_json method returns the SQL expression representing a json data type.

type_json example #1
# given: synopsis
$grammar->type_json({});
# text

type_number

type_number(HashRef $data) : Str

The type_number method returns the SQL expression representing a number data type.

type_number example #1
# given: synopsis
$grammar->type_number({});
# integer

type_string

type_string(HashRef $data) : Str

The type_string method returns the SQL expression representing a string data type.

type_string example #1
# given: synopsis
$grammar->type_string({});
# varchar

type_text

type_text(HashRef $data) : Str

The type_text method returns the SQL expression representing a text data type.

type_text example #1
# given: synopsis
$grammar->type_text({});
# text

type_text_long

type_text_long(HashRef $data) : Str

The type_text_long method returns the SQL expression representing a long text data type.

type_text_long example #1
# given: synopsis
$grammar->type_text_long({});
# text

type_text_medium

type_text_medium(HashRef $data) : Str

The type_text_medium method returns the SQL expression representing a medium text data type.

type_text_medium example #1
# given: synopsis
$grammar->type_text_medium({});
# text

type_time

type_time(HashRef $data) : Str

The type_time method returns the SQL expression representing a time data type.

type_time example #1
# given: synopsis
$grammar->type_time({});
# time

type_time_wtz

type_time_wtz(HashRef $data) : Str

The type_time_wtz method returns the SQL expression representing a time (and timezone) data type.

type_time_wtz example #1
# given: synopsis
$grammar->type_time_wtz({});
# time

type_timestamp

type_timestamp(HashRef $data) : Str

The type_timestamp method returns the SQL expression representing a timestamp data type.

type_timestamp example #1
# given: synopsis
$grammar->type_timestamp({});
# datetime

type_timestamp_wtz

type_timestamp_wtz(HashRef $data) : Str

The type_timestamp_wtz method returns the SQL expression representing a timestamp (and timezone) data type.

type_timestamp_wtz example #1
# given: synopsis
$grammar->type_timestamp_wtz({});
# datetime

type_uuid

type_uuid(HashRef $data) : Str

The type_uuid method returns the SQL expression representing a uuid data type.

type_uuid example #1
# given: synopsis
$grammar->type_uuid({});
# varchar

update

update(HashRef $data) : Object

The update method generates SQL statements to update table rows.

update example #1
# given: synopsis
$grammar->update({
for => {
table => 'users'
},
columns => [
{
column => 'updated',
value => { function => ['now'] }
}
]
});

validate

validate() : Bool

The validate method validates the data structure defined in the "schema" property.

validate example #1
# given: synopsis
my $valid = $grammar->validate;

value

value(Any $value) : Str

The value method returns the SQL representation of a value.

value example #1
# given: synopsis
$grammar->value(undef);
# NULL

view_create

view_create(HashRef $data) : Object

The view_create method generates SQL statements to create a table view.

view_create example #1
# given: synopsis
$grammar->view_create({
name => 'active_users',
query => {
select => {
from => {
table => 'users'
},
columns => [
{
column => '*'
}
],
where => [
{
'not-null' => {
column => 'deleted'
}
}
]
}
}
});

view_drop

view_drop(HashRef $data) : Object

The view_drop method generates SQL statements to remove a table view.

view_drop example #1
# given: synopsis
$grammar->view_drop({
name => 'active_users'
});

wrap

wrap(Str $name) : Str

The wrap method returns a SQL-escaped string.

wrap example #1
# given: synopsis
$grammar->wrap('field');
# "field"

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