SQL::Engine::Grammar

Standard Grammar

SQL::Engine Standard Grammar

method: binding method: column_change method: column_create method: column_definition method: column_drop method: column_rename method: column_specification method: constraint_create method: constraint_drop method: constraint_name method: constraint_option method: criteria method: criterion method: database_create method: database_drop method: delete method: execute method: expression method: index_create method: index_drop method: index_name method: insert method: name method: operation method: process method: schema_create method: schema_drop method: schema_rename method: select method: table method: table_create method: table_drop method: table_rename method: term method: transaction method: type method: type_binary method: type_boolean method: type_char method: type_date method: type_datetime method: type_datetime_wtz method: type_decimal method: type_double method: type_enum method: type_float method: type_integer method: type_integer_big method: type_integer_big_unsigned method: type_integer_medium method: type_integer_medium_unsigned method: type_integer_small method: type_integer_small_unsigned method: type_integer_tiny method: type_integer_tiny_unsigned method: type_integer_unsigned method: type_json method: type_number method: type_string method: type_text method: type_text_long method: type_text_medium method: type_time method: type_time_wtz method: type_timestamp method: type_timestamp_wtz method: type_uuid method: update method: validate method: value method: view_create method: view_drop method: wrap

use SQL::Engine::Grammar;

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

# $grammar->execute;

Types::Standard

operations: ro, opt, InstanceOf["SQL::Engine::Collection"] schema: ro, req, HashRef validator: ro, opt, Maybe[InstanceOf["SQL::Validator"]]

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

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

binding(Str $name) : Str

=example-1 binding

# given: synopsis

$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');
$grammar->binding('user_id');

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

column_change(HashRef $data) : Object

=example-1 column_change

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

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

column_create(HashRef $data) : Object

=example-1 column_create

# given: synopsis

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

The column_definition method column definition SQL statement fragments.

column_definition(HashRef $data) : HashRef

=example-1 column_definition

# given: synopsis

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

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

column_drop(HashRef $data) : Object

=example-1 column_drop

# given: synopsis

$grammar->column_drop({
  table => 'users',
  column => 'accessed'
});

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

column_rename(HashRef $data) : Object

=example-1 column_rename

# given: synopsis

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

The column_specification method a column definition SQL statment partial.

column_specification(HashRef $data) : Str

=example-1 column_specification

# given: synopsis

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

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

constraint_create(HashRef $data) : Object

=example-1 constraint_create

# given: synopsis

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

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

constraint_drop(HashRef $data) : Object

=example-1 constraint_drop

# given: synopsis

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

The constraint_name method returns the generated constraint name.

constraint_name(HashRef $data) : Str

=example-1 constraint_name

# given: synopsis

my $constraint_name = $grammar->constraint_name({
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  }
});

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

constraint_option(Str $name) : Str

=example-1 constraint_option

# given: synopsis

$grammar->constraint_option('no-action');

The criteria method returns a list of SQL expressions.

criteria(ArrayRef $data) : ArrayRef[Str]

=example-1 criteria

# given: synopsis

my $criteria = $grammar->criteria([
  {
    eq => [{ column => 'id' }, 123]
  },
  {
    'not-null' => { column => 'deleted' }
  }
]);

The criterion method returns a SQL expression.

criterion(HashRef $data) : Str

=example-1 criterion

# given: synopsis

my $criterion = $grammar->criterion({
  in => [{ column => 'theme' }, 'light', 'dark']
});

The database_create method generates SQL statements to create a database.

database_create(HashRef $data) : Object

=example-1 database_create

# given: synopsis

$grammar->database_create({
  name => 'todoapp'
});

The database_drop method generates SQL statements to remove a database.

database_drop(HashRef $data) : Object

=example-1 database_drop

# given: synopsis

$grammar->database_drop({
  name => 'todoapp'
});

The delete method generates SQL statements to delete table rows.

delete(HashRef $data) : Object

=example-1 delete

# given: synopsis

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

The execute method validates and processes the object instruction.

execute() : Object

=example-1 execute

# given: synopsis

$grammar->operations->clear;

$grammar->execute;

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

expression(Any $data) : Any

=example-1 expression

# given: synopsis

$grammar->expression(undef);

# NULL

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

index_create(HashRef $data) : Object

=example-1 index_create

# given: synopsis

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

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

index_drop(HashRef $data) : Object

=example-1 index_drop

# given: synopsis

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

The index_name method returns the generated index name.

index_name(HashRef $data) : Str

=example-1 index_name

# given: synopsis

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

The insert method generates SQL statements to insert table rows.

insert(HashRef $data) : Object

=example-1 insert

# given: synopsis

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

The name method returns a qualified quoted object name.

name(Any @args) : Str

=example-1 name

# given: synopsis

my $name = $grammar->name(undef, 'public', 'users');

# "public"."users"

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

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

=example-1 operation

# given: synopsis

$grammar->operation('SELECT TRUE');

The process method processes the object instructions.

process(Mayb[HashRef] $schema) : Object

=example-1 process

# given: synopsis

$grammar->process;

The schema_create method generates SQL statements to create a schema.

schema_create(HashRef $data) : Object

=example-1 schema_create

# given: synopsis

$grammar->schema_create({
  name => 'private',
});

The schema_drop method generates SQL statements to remove a schema.

schema_drop(HashRef $data) : Object

=example-1 schema_drop

# given: synopsis

$grammar->schema_drop({
  name => 'private',
});

The schema_rename method generates SQL statements to rename a schema.

schema_rename(HashRef $data) : Object

=example-1 schema_rename

# given: synopsis

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

The select method generates SQL statements to select table rows.

select(HashRef $data) : Object

=example-1 select

# given: synopsis

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

The table method returns a qualified quoted table name.

table(HashRef $data) : Str

=example-1 table

# given: synopsis

my $table = $grammar->table({
  schema => 'public',
  table => 'users',
  alias => 'u'
});

The table_create method generates SQL statements to create a table.

table_create(HashRef $data) : Object

=example-1 table_create

# given: synopsis

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

The table_drop method generates SQL statements to remove a table.

table_drop(HashRef $data) : Object

=example-1 table_drop

# given: synopsis

$grammar->table_drop({
  name => 'people'
});

The table_rename method generates SQL statements to rename a table.

table_rename(HashRef $data) : Object

=example-1 table_rename

# given: synopsis

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

The term method returns a SQL keyword.

term(Str @args) : Str

=example-1 term

# given: synopsis

$grammar->term('end');

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

transaction(HashRef $data) : Object

=example-1 transaction

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

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

type(HashRef $data) : Str

=example-1 type

# given: synopsis

$grammar->type({
  type => 'datetime-wtz'
});

# datetime

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

type_binary(HashRef $data) : Str

=example-1 type_binary

# given: synopsis

$grammar->type_binary({});

# blob

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

type_boolean(HashRef $data) : Str

=example-1 type_boolean

# given: synopsis

$grammar->type_boolean({});

# tinyint(1)

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

type_char(HashRef $data) : Str

=example-1 type_char

# given: synopsis

$grammar->type_char({});

# varchar

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

type_date(HashRef $data) : Str

=example-1 type_date

# given: synopsis

$grammar->type_date({});

# date

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

type_datetime(HashRef $data) : Str

=example-1 type_datetime

# given: synopsis

$grammar->type_datetime({});

# datetime

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

type_datetime_wtz(HashRef $data) : Str

=example-1 type_datetime_wtz

# given: synopsis

$grammar->type_datetime_wtz({});

# datetime

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

type_decimal(HashRef $data) : Str

=example-1 type_decimal

# given: synopsis

$grammar->type_decimal({});

# numeric

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

type_double(HashRef $data) : Str

=example-1 type_double

# given: synopsis

$grammar->type_double({});

# float

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

type_enum(HashRef $data) : Str

=example-1 type_enum

# given: synopsis

$grammar->type_enum({});

# varchar

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

type_float(HashRef $data) : Str

=example-1 type_float

# given: synopsis

$grammar->type_float({});

# float

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

type_integer(HashRef $data) : Str

=example-1 type_integer

# given: synopsis

$grammar->type_integer({});

# integer

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

type_integer_big(HashRef $data) : Str

=example-1 type_integer_big

# given: synopsis

$grammar->type_integer_big({});

# integer

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

type_integer_big_unsigned(HashRef $data) : Str

=example-1 type_integer_big_unsigned

# given: synopsis

$grammar->type_integer_big_unsigned({});

# integer

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

type_integer_medium(HashRef $data) : Str

=example-1 type_integer_medium

# given: synopsis

$grammar->type_integer_medium({});

# integer

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

type_integer_medium_unsigned(HashRef $data) : Str

=example-1 type_integer_medium_unsigned

# given: synopsis

$grammar->type_integer_medium_unsigned({});

# integer

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

type_integer_small(HashRef $data) : Str

=example-1 type_integer_small

# given: synopsis

$grammar->type_integer_small({});

# integer

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

type_integer_small_unsigned(HashRef $data) : Str

=example-1 type_integer_small_unsigned

# given: synopsis

$grammar->type_integer_small_unsigned({});

# integer

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

type_integer_tiny(HashRef $data) : Str

=example-1 type_integer_tiny

# given: synopsis

$grammar->type_integer_tiny({});

# integer

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

type_integer_tiny_unsigned(HashRef $data) : Str

=example-1 type_integer_tiny_unsigned

# given: synopsis

$grammar->type_integer_tiny_unsigned({});

# integer

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

type_integer_unsigned(HashRef $data) : Str

=example-1 type_integer_unsigned

# given: synopsis

$grammar->type_integer_unsigned({});

# integer

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

type_json(HashRef $data) : Str

=example-1 type_json

# given: synopsis

$grammar->type_json({});

# text

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

type_number(HashRef $data) : Str

=example-1 type_number

# given: synopsis

$grammar->type_number({});

# integer

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

type_string(HashRef $data) : Str

=example-1 type_string

# given: synopsis

$grammar->type_string({});

# varchar

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

type_text(HashRef $data) : Str

=example-1 type_text

# given: synopsis

$grammar->type_text({});

# text

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

type_text_long(HashRef $data) : Str

=example-1 type_text_long

# given: synopsis

$grammar->type_text_long({});

# text

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

type_text_medium(HashRef $data) : Str

=example-1 type_text_medium

# given: synopsis

$grammar->type_text_medium({});

# text

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

type_time(HashRef $data) : Str

=example-1 type_time

# given: synopsis

$grammar->type_time({});

# time

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

type_time_wtz(HashRef $data) : Str

=example-1 type_time_wtz

# given: synopsis

$grammar->type_time_wtz({});

# time

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

type_timestamp(HashRef $data) : Str

=example-1 type_timestamp

# given: synopsis

$grammar->type_timestamp({});

# datetime

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

type_timestamp_wtz(HashRef $data) : Str

=example-1 type_timestamp_wtz

# given: synopsis

$grammar->type_timestamp_wtz({});

# datetime

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

type_uuid(HashRef $data) : Str

=example-1 type_uuid

# given: synopsis

$grammar->type_uuid({});

# varchar

The update method generates SQL statements to update table rows.

update(HashRef $data) : Object

=example-1 update

# given: synopsis

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

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

validate() : Bool

=example-1 validate

# given: synopsis

my $valid = $grammar->validate;

The value method returns the SQL representation of a value.

value(Any $value) : Str

=example-1 value

# given: synopsis

$grammar->value(undef);

# NULL

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

view_create(HashRef $data) : Object

=example-1 view_create

# given: synopsis

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

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

view_drop(HashRef $data) : Object

=example-1 view_drop

# given: synopsis

$grammar->view_drop({
  name => 'active_users'
});

The wrap method returns a SQL-escaped string.

wrap(Str $name) : Str

=example-1 wrap

# given: synopsis

$grammar->wrap('field');

# "field"

cpanm SQL::Engine

perl -MCPAN -e shell
install SQL::Engine