A bunch of changes, primarily focused on adding the concept of a FooList and adding...

[dbsrgits/SQL-Abstract-2.0-ish.git] / lib / SQL / Abstract / Manual / Specification.pod

=head1 NAME

SQL::Abstract::Manual::Specification

=head1 SYNOPSIS

This discusses the specification for the AST provided by L<SQL::Abstract>. It is
meant to describe how the AST is structured, various components provided by
L<SQL::Abstract> for use with this AST, how to manipulate the AST, and various
uses for the AST once it is generated.

=head1 MOTIVATIONS

L<SQL::Abstract> has been in use for many years. Originally created to handle
the where-clause formation found in L<DBIx::Abstract>, it was generalized to
manage the creation of any SQL statement through the use of Perl structures.
Through the beating it received as the SQL generation syntax for L<DBIx::Class>,
various deficiencies were found and a generalized SQL AST was designed. This
document describes that AST.

=head1 GOALS

The goals for this AST are as follows:

=head2 SQL-specific semantics

Instead of attempting to be an AST to handle any form of query, this will
instead be specialized to manage SQL queries (and queries that map to SQL
queries). This means that there will be support for SQL-specific features, such
as placeholders.

=head2 Perl-specific semantics

This AST is meant to be used from within Perl5 only. So, it will take advantage
of as many Perl-specific features that make sense to use. No attempt whatosever
will be made to make this AST work within any other language, including Perl6.

=head2 Whole-lifecycle management

Whether a query is built out of whole cloth in one shot or cobbled together from
several snippets over the lifetime of a process, this AST will support any way
to construct the query. Queries can also be built from other queries, so an
UPDATE statement could be used as the basis for a SELECT statement, DELETE
statement, or even a DDL statement of some kind.

=head2 Dialect-agnostic usage

Even though SQL itself has several ANSI specifications (SQL-92 and SQL-99 among
them), this only serves as a basis for what a given RDBMS will expect. However,
every engine has its own specific extensions and specific ways of handling
common features. The AST will provide ways of expressing common functionality in
a common language. The emitters (objects that follow the Visitor pattern) will
be responsible for converting that common language into RDBMS-specific SQL.

=head1 RESTRICTIONS

The following are the restrictions upon the AST:

=head2 DML-only

The AST will only support DML (Data Modelling Language). It will not (currently)
support DDL (Data Definition Language). Practically, this means that the only
statements supported will be:

=over 4

=item * SELECT

=item * INSERT INTO

=item * UPDATE

=item * DELETE

=back

Additional DML statements may be supported by specific Visitors (such as a
MySQL visitor supporting REPLACE INTO). q.v. the relevant sections of this
specification for details.

=head1 COMPONENTS

There are two major components to SQL::Abstract v2.

=over 4

=item * AST

This is the Abstract Syntax Tree. It is a data structure that represents
everything necessary to construct the SQL statement in whatever dialect the
user requires.

=item * Visitor

This object conforms to the Visitor pattern and is used to generate the SQL
represented by the AST. Each dialect will have a different Visitor object. In
addition, there will be visitors for at least one of the ANSI specifications.

=back

The division of duties between the two components will focus on what the AST
can and cannot assume. For example, identifiers do not have 20 components in
any dialect, so the AST can validate that. However, determining what
constitutes a legal identifier can only be determined by the Visitor object
enforcing that dialect's rules.

=head1 AST STRUCTURE

The AST will be a HoHo..oH (hash of hash of ... of  hashes). The keys to the
outermost hash will be the various clauses of a SQL statement, plus some
metadata keys. All metadata keys will be identifiable as such by being prefixed
with an underscore. All keys will be in lowercase.

=head2 Metadata keys

These are the additional metadata keys that the AST provides for.

=head3 _query

This denotes what kind of query this AST should be interpreted as. Different
Visitors may accept additional values for _query. For example, a MySQL Visitor
may choose to accept 'replace' for REPLACE INTO. If a _query value is
unrecognized by the Visitor, the Visitor is expected to throw an error.

All Visitors are expected to handle the following values for _query:

=over 4

=item * select

This is a SELECT statement.

=item * insert

This is an INSERT statement.

=item * update

This is an UPDATE statement.

=item * delete

This is a DELETE statement.

=back

=head3 _version

This denotes the version of the AST. Different versions will indicate different
capabilities provided. Visitors will choose to respect the _version as needed
and desired.

=head2 Structural units

All structural units will be hashes. These hashes will have, at minimum, the
following keys:

=over 4

=item * name

This indicates the structural unit that this hash is representing. While this
specification provides for standard structural units, different Visitors may
choose to accept additional units as desired. If a Visitor encounters a unit it
doesn't know how to handle, it is expected to throw an exception. 

=back

Structural units in the AST are supported by loaded components. L<SQL::Abstract>
provides for the following structural units by default:

=head3 Identifier

This is a (potentially) fully canonicalized identifier for a elemnt in the
query. This element could be a schema, table, or column. The Visitor will
determine validity within the context of that SQL dialect. The AST is only
responsible for validating that the elements are non-empty Strings.

The hash will be structured as follows:

  {
      name     => 'Identifier',
      element1 => Scalar,
      element2 => Scalar,
      element3 => Scalar,
  }

If element3 exists, then element2 must exist. element1 must always exist. If a
given element exists, then it must be defined and of non-zero length.

Visitors are expected to, by default, quote all identifiers according to the SQL
dialect's quoting scheme.

=head3 Value

A Value is a Perl scalar. Depending on the type, a Visitor may be able to make
certain decisions.

=over 4

=item * String

A String is a quoted series of characters. The Visitor is expected to ensure
that embedded quotes are properly handled per the SQL dialect's quoting scheme.

=item * Number

A Number is an unquoted number in some numeric format.

=item * Null

Null is SQL's NULL and corresponds to Perl's C<undef>.

=item * BindParameter

This corresponds to a value that will be passed in. This value is normally
quoted in such a fashion so as to protect against SQL injection attacks. (q.v.
L<DBI/quote()> for an example.)

BindParameters are normally represented by a '?'.

=back

The hash will be structured as follows:

  {
      name    => 'Value'
      subtype => [ 'String' | 'Number' | 'Null' | 'BindParameter' ]
      value   => Scalar
  }

The provided subtypes are the ones that all Visitors are expected to support.
Visitors may choose to support additional subtypes. Visitors are expected to
throw an exception upon encountering an unknown subtype.

=head3 Function

A Function is anything of the form C<< name( arglist ) >> where C<<name>> is a
string and C<arglist> is an ExpressionList.

Yes, a Subquery is legal as an argument for many functions. Some example
functions are:

=over 4

=item * C<< MAX >>

=item * C<< MIN >>

=item * C<< SUM >>

=item * C<< IF >>

=back

The hash will be structured as follows:

  {
      name     => "Function",
      function => String,
      arglist  => ExpressionList,
  }

Functions have a cardinality, or expected number of arguments. Some functions,
such as MAX(), have a cardinality of 1. Others, such as IF(), have a cardinality
of N, meaning they can have any number of arguments greater than 0. Others, such
as NOW(), have a cardinality of 0. Several functions with the same meaning may
have a different cardinality in different SQL dialects as different engines may
allow different behaviors.

As cardinality may differ between dialects, enforcing cardinality is necessarily
left to the Visitor.

=head3 Subquery

A Subquery is another AST whose _query metadata parameter is set to "SELECT".

Most places that a Subquery can be used would require a single value to be
returned (single column, single row), but that is not something that the AST can
easily enforce. The single-column restriction may possibly be enforced, but the
single-row restriction is much more difficult and, in most cases, probably
impossible.

Subqueries, when expressed in SQL, must be bounded by parentheses.

=head3 Unary Operator

A UnaryOperator takes a single argument on the RHS. The argument for a
UnaryOperator is an Expression.

Visitors are expected to support, at minimum, the following operators:

=over 4

=item * NOT X

=item * ANY X

=item * ALL X

=item * SOME X

=back

The hash for a UnaryOperator is as follows:

  {
      name      => 'UnaryOperator'
      operator  => [ .... ],
      argument1 => Expression,
  }

Visitors may choose to support additional operators. Visitors are expected to
throw an exception upon encountering an unknown operator.

=head3 BinaryOperator

A BinaryOperator takes two arguments (one on the LHS and one on the RHS). The
arguments for a BinaryOperator are all Expressions.

Visitors are expected to support, at minimum, the following operators:

=over 4

=item * X = Y

=item * X != Y

=item * X > Y

=item * X < Y

=item * X >= Y

=item * X <= Y

=item * X IS Y

=item * X IN Y

=item * X NOT IN Y

=item * X AND Y

=item * X OR Y

=back

(Note that an operator can comprise of what would be multiple tokens in a normal
parsing effort.)

Visitors may choose to support additional operators. Visitors are expected to
throw an exception upon encountering an unknown operator.

The hash for a BinaryOperator is as follows:

  {
      name      => 'BinaryOperator'
      operator  => [ .... ],
      argument1 => Expression,
      argument2 => Expression,
  }

=head3 TrinaryOperator

A TrinaryOperator takes three arguments. It generally is composed of two
elements with one argument to the LHS, one to the RHS, and a third in the middle
of the elements. The arguments for a TrinaryOperator are all Expressions.

Visitors are expected to support, at minimum, the following operators:

=over 4

=item * X BETWEEN Y AND Z

=back

Visitors may choose to support additional operators. Visitors are expected to
throw an exception upon encountering an unknown operator.

The hash for a TrinaryOperator is as follows:

  {
      name      => 'TrinaryOperator'
      operator  => [ .... ],
      argument1 => Expression,
      argument2 => Expression,
      argument3 => Expression,
  }

=head3 Expression

An Expression can be any one of the following:

=over 4

=item * Value

=item * Function

=item * Subquery

=item * UnaryOperator

=item * BinaryOperator

=item * TrinaryOperator

=back

An Expression is a meta-syntactic unit. An "Expression" unit will never appear
within the AST. It acts as a junction.

=head3 ExpressionList

An ExpressionList is a list of Expressions, generally separated by commas
(though other separators may be appropriate at times or for different SQL
dialects).

The hash for an ExpressionList is as follows:

  {
      name      => 'ExpressionList',
      separator => ',',
      elements  =>  Array of Expressions,
  }

An ExpressionList is always rendered in SQL with parentheses around it.

=head2 SQL clauses

These are all the legal and acceptable clauses within the AST that would
correpsond to clauses in a SQL statement. Not all clauses are legal within a
given RDBMS engine's SQL dialect and some clauses may be required in one and
optional in another. Detecting and enforcing those engine-specific restrictions
is the responsibility of the Visitor object.

The clauses are defined with a yacc-like syntax. The various parts are:

=over 4

=item * :=

This means "defined" and is used to create a new term to be used below.

=item * []

This means optional and indicates that the items within it are optional.

=item * []*

This means optional and repeating as many times as desired.

=item * |

This means alternation. It is a binary operator and indicates that either the
left or right hand sides may be used, but not both.

=item * C<< <> >>

This is a grouping construct. It means that all elements within this construct
are treated together for the purposes of optional, repeating, alternation, etc.

=back

The expected clauses are (name and structure):

=head3 select

This corresponds to the SELECT clause of a SELECT statement.

A select clause unit is an array of one or more SelectComponent units.

The hash for a SelectComponent unit is composed as follows:

  {
      name  => 'SelectComponent',
      value => Expression,
      [ as    => Identifier, ]
  }

The 'as' component is optional. Visitors may choose to make it required in
certain situations.

=head3 tables

This is a list of tables that this clause is affecting. It corresponds to the
FROM clause in a SELECT statement and the INSERT INTO/UPDATE/DELETE clauses in
those respective statements. Depending on the _query metadata entry, the
appropriate clause name will be used.

A tables clause unit is an array of one or more TableComponent units.

The tables clause has several RDBMS-specific variations. The AST will support
all of them and it is up to the Visitor object constructing the actual SQL to
validate and/or use what is provided as appropriate.

The hash for a TableJoin will be composed as follows:

  # TableJoin
  {
      name => 'TableJoin',
      join => < LEFT|RIGHT [ OUTER ] > | INNER | CROSS | ',',
      [ using => IdentifierList, ]
      [ on    => ExpressionList, ]
  }

A TableJoin may not have both a 'using' element and an 'on' element. It may
have one of them if the 'join' element is not equal to ',' but doesn't have to.
If the 'join' element is equal to ',', then it may not have either a 'using' or
an 'on' element.

The hash for a TableIdentifier will be composed as follows:

  # TableIdentifier
  {
      name  => 'TableIdentifier',
      value => Identifier | SubQuery
      [ join  => TableJoin, ]
      [ as    => Identifier, ]
  }

The first TableComponent in a tables clause may not have a join element. All
other TableComponent elements that do not have a join element will have a
default join element of:

  {
      name => 'TableJoin',
      join => ',',
  }

The 'as' component is optional. Visitors may choose to make it required in
certain situations (such as MySQL requiring an alias for subqueries).

Additionally, where aliases are provided for in the TableIdentifier, those
aliases must be used as the tablename in subsequent Identifiers that identify a
column of that table. This may be enforceable by the AST or the Visitor. But, it
is more likely that it will not be.

=head3 where

This corresponds to the WHERE clause in a SELECT, UPDATE, or DELETE statement.

A where clause is composed as follows:

  WhereOperator := AND | OR
  WhereExpression := Expression | Expression WhereOperator Expression

  WhereExpression

=head3 set

This corresponds to the SET clause in an INSERT or UPDATE statement.

A set clause is composed as follows:

  SetComponent := Identifier = Expression

  SetComponent [ , SetComponent ]*

=head3 columns

This corresponds to the optional list of columns in an INSERT statement.

A columns clause is an IdentifierList and the unit is composed as follows:

  columns => [
      Identifier,
      [ Identifier, ]*
  ],

=head3 values

This corresponds to the VALUES clause in an INSERT statement.

A values clause is an ExpressionList and the unit is composed as follows.

  values => [
      Expression,
      [ Expression, ]*
  ],

If there is a columns clause, the number of entries in the values clause must be
equal to the number of entries in the columns clause.

=head3 orderby

This corresponds to the ORDER BY clause in a SELECT statement.

An orderby clause is composed as follows:

  OrderByComponent := XXX-TODO-XXX
  OrderByDirection := ASC | DESC

  OrderByComponent [ OrderByDirection ]
  [ , OrderByComponent [ OrderByDirection ] ]*

=head3 groupby

This corresponds to the GROUP BY clause in a SELECT statement.

An groupby clause is composed as follows:

  GroupByComponent := XXX-TODO-XXX

  GroupByComponent [ , GroupByComponent ]*

=head3 rows

This corresponds to the clause that is used in some RDBMS engines to limit the
number of rows returned by a query. In MySQL, this would be the LIMIT clause.

A rows clause is composed as follows:

  Number [, Number ]

=head3 for

This corresponds to the clause that is used in some RDBMS engines to indicate
what locks are to be taken by this SELECT statement.

A for clause is composed as follows:

  UPDATE | DELETE

=head3 connectby

This corresponds to the clause that is used in some RDBMS engines to provide for
an adjacency-list query.

A connectby clause is composed as follows:

  Identifier, WhereExpression

=head1 EXAMPLES

The following are example SQL statements and a possible AST for each one.

=over 4

=item * SELECT 1

  {
      _query => 'select',
      _ast_version => 0.0001,
      select => [
          {
              name  => 'SelectComponent',
              value => {
                  name    => 'Value',
                  subtype => 'number',
                  value   => 1,
              },
          },
      ],
  }

=item * SELECT NOW() AS time FROM dual AS duality

  {
      _query => 'select',
      _ast_version => 0.0001,
      select => [
          {
              name  => 'SelectComponent',
              value => {
                  name     => 'Function',
                  function => 'NOW',
              },
              as => {
                  name     => 'Identifier',
                  element1 => 'time',
              },
          },
      ],
      tables => [
          {
              name => 'TablesComponent',
              value => {
                  name => 'Identifier',
                  element1 => 'dual',
              },
              as => {
                  name     => 'Identifier',
                  element1 => 'duality',
              },
          },
      ],
  }

=back

=head1 AUTHORS

robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>

=head1 LICENSE

You may distribute this code under the same terms as Perl itself.

=cut