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 API to 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.
+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.
+
+=head2 Dialect-agnostic construction
+
+The AST will not attempt to be immediately readable to a human as SQL. In fact,
+due to the dialect differences, particularly in terms of which use operators and
+which use functions for a given action, the AST will provide simple units. It is
+the responsibility of the Visitor to provide the appropriate SQL. Furthermore,
+the AST will be very generic and only provide hints for a subset of SQL. If a
+Visitor is sufficiently intelligent, pretty SQL may be emitted, but that is not
+the goal of this AST.
+
+=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 HoA (hash of arrays). The keys to the 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.
+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 * _query
+=item * select
+
+This is a SELECT statement.
-This denotes what kind of query this AST should be interpreted as.
+=item * insert
-=item *
+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 * type
+
+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 table or column. Is
-is of the structure C< [schema][sep][table][sep]column > or
-C< [schema][sep]table >.
+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.
-In the case of a two-element identifier which could be C< table[sep]column > or
-C< schema[sep]table >, context will determine which it is. However, the AST
-doesn't care which it is, only that it properly parses.
+The hash will be structured as follows:
-=head3 Constant
+ {
+ type => 'Identifier',
+ element1 => Scalar,
+ element2 => Scalar,
+ element3 => Scalar,
+ }
-A Constant is a Perl scalar. It may either be a String (quoted series of
-characters) or a number (unquoted) or NULL (corresponds to Perl's C<undef>).
+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.
-=head3 Function
+Visitors are expected to, by default, quote all identifiers according to the SQL
+dialect's quoting scheme.
-A Function is anything of the form C< name( arglist ) > where C<name> is a
-string and C<arglist> is a comma-separated list of Expressions.
+=head3 Value
-Yes, a Subquery is legal as an argument for many functions. Some example
-functions are:
+A Value is a Perl scalar. Depending on the type, a Visitor may be able to make
+certain decisions.
=over 4
-=item * C<< IN >>
+=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 * C<< MAX >>
+=item * BindParameter
-=item * C<< MIN >>
+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.)
-=item * C<< SUM >>
+BindParameters are normally represented by a '?'.
=back
+The hash will be structured as follows:
+
+ {
+ type => '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 Operator
+
+An Operator would be, in SQL dialect terms, a unary operator, a binary operator,
+a trinary operator, or a function. Since different dialects may have a given
+functionality as an operator or a function (such as CONCAT in MySQl vs. || in
+Oracle for string concatenation), they will be represented in the AST as generic
+operators.
+
+The hash will be structured as follows:
+
+ {
+ type => 'Operator',
+ op => String,
+ args => ExpressionList,
+ }
+
+Operators have a cardinality, or expected number of arguments. Some operators,
+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 operators 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.
+
+Operators also have restrictions on the types of arguments they will accept. The
+first argument may or may not restricted in the same fashion as the other
+arguments. As with cardinality, this restriction will need to be managed by the
+Visitor.
+
+The operator name needs to take into account the possibility that the RDBMS may
+allow UDFs (User-Defined Functions) that have the same name as an operator, such
+as 'AND'. This will have to be managed by 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 can possibly be enforced, but the
+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 bounded by parentheses.
+Subqueries, when expressed in SQL, must be bounded by parentheses.
-=head3 Unary Operator
+=head3 Expression
-A UnaryOperator takes a single argument on the RHS and is one of the following:
+An Expression can be any one of the following:
=over 4
-=item * C<< NOT >>
-
-=back
+=item * Identifier
-=head3 BinaryOperator
+=item * Value
-A BinaryOperator takes two arguments (one on the LHS and one on the RHS) and is
-one of the following:
+=item * Operator
-=over 4
+=item * Subquery
-=item * C<< = >>
+=back
-=item * C<< != >>
+An Expression is a meta-syntactic unit. An "Expression" unit will never appear
+within the AST. It acts as a junction.
-=item * C<< > >>
+=head3 ExpressionList
-=item * C<< < >>
+An ExpressionList is a list of Expressions, generally separated by commas
+(though other separators may be appropriate at times or for different SQL
+dialects). An null separator may also be used.
-=item * C<< >= >>
+The hash for an ExpressionList is as follows:
-=item * C<< <= >>
+ {
+ type => 'ExpressionList',
+ separator => ',',
+ elements => Array of Expressions,
+ }
-=item * C<< IS >>
+An ExpressionList is always rendered in SQL with parentheses around it.
-=item * C<< IS NOT >>
+=head2 SQL clauses
-=back
+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.
-Note that an operator can comprise of what would be multiple tokens in a normal
-parsing effort.
+The clauses are defined with a yacc-like syntax. The various parts are:
-=head3 Expression
+=over 4
-An expression can be any one of the following:
+=item * :=
-=over 4
+This means "defined" and is used to create a new term to be used below.
-=item * Constant
+=item * []
-=item * Function
+This means optional and indicates that the items within it are optional.
-=item * Subquery
+=item * []*
-=item * UnaryOperator Expression
+This means optional and repeating as many times as desired.
-=item * Expression BinaryOperator Expression
+=item * |
-=item * ( Expression )
+This means alternation. It is a binary operator and indicates that either the
+left or right hand sides may be used, but not both.
-=back
+=item * C<< <> >>
-Parentheses indicate precedence and, in some situations, are necessary for
-certain operators.
+This is a grouping construct. It means that all elements within this construct
+are treated together for the purposes of optional, repeating, alternation, etc.
-=head2 SQL clauses
+=back
The expected clauses are (name and structure):
This corresponds to the SELECT clause of a SELECT statement.
-A select clause is composed as follows:
+A select clause unit is an array of one or more SelectComponent units.
+
+The hash for a SelectComponent unit is composed as follows:
- SelectComponent := Expression [ [ AS ] String ]
+ {
+ type => 'SelectComponent',
+ value => Expression,
+ as => String,
+ }
- SelectComponent
- [ , SelectComponent ]*
+The 'as' component is optional. Visitors may choose to make it required in
+certain situations.
=head3 tables
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.
-A table clause is composed as follows:
+A TableJoin is a junction of the following elements:
+
+=over 4
+
+=item * TableIdentifier
+
+=item * Operator
- TableIdentifier := Identifier [ [ AS ] String ]
- JoinType := < LEFT|RIGHT [ OUTER ] > | INNER | CROSS
+=back
+
+The hash for a TableIdentifier will be composed as follows:
+
+ # TableIdentifier
+ {
+ type => 'TableIdentifier',
+ value => Expression,
+ as => String,
+ }
- TableIdentifier
- [
- < , TableIdentifier >
- | <
- [ JoinType ] JOIN TableIdentifier
- [
- < USING ( Identifier [ , Identifier ] ) >
- | < ON [ ( ] Expression [ , Expression ] [ ) ] >
- ]
- >
- ]*
+The value should be either an Identifier or a SubQuery.
-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.
+The hash for an Operator within a tables clause will be composed as follows:
+
+ # Operator
+ {
+ type => 'Operator',
+ op => '< LEFT|RIGHT|FULL [ OUTER ] > | INNER | CROSS',
+ on => Expression,
+ }
+
+A USING clause is syntactic sugar for an ON clause and, as such, is not provided
+for by the AST. A join of a comma is identical to a CROSS JOIN. The on clause is
+optional.
=head3 where
This corresponds to the optional list of columns in an INSERT statement.
-A columns clause is composed as follows:
+A columns clause is an IdentifierList and the unit is composed as follows:
- ( Identifier [ , Identifier ]* )
+ columns => [
+ Identifier,
+ [ Identifier, ]*
+ ],
=head3 values
This corresponds to the VALUES clause in an INSERT statement.
-A values clause is composed as follows:
+A values clause is an ExpressionList and the unit is composed as follows.
- ( Expression [ , Expression ]* )
+ 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.
An orderby clause is composed as follows:
- OrderByComponent := XXX
+ OrderByComponent := XXX-TODO-XXX
OrderByDirection := ASC | DESC
OrderByComponent [ OrderByDirection ]
An groupby clause is composed as follows:
- GroupByComponent := XXX
+ GroupByComponent := XXX-TODO-XXX
GroupByComponent [ , GroupByComponent ]*
Identifier, WhereExpression
-=head3
+=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 => [
+ {
+ type => 'SelectComponent',
+ value => {
+ type => 'Value',
+ subtype => 'number',
+ value => 1,
+ },
+ },
+ ],
+ }
+
+=item * SELECT NOW() AS time FROM dual AS duality
+
+ {
+ _query => 'select',
+ _ast_version => 0.0001,
+ select => [
+ {
+ type => 'SelectComponent',
+ value => {
+ type => 'Function',
+ function => 'NOW',
+ },
+ as => {
+ type => 'Identifier',
+ element1 => 'time',
+ },
+ },
+ ],
+ tables => {
+ type => 'TableIdentifier',
+ value => {
+ type => 'Identifier',
+ element1 => 'dual',
+ },
+ as => 'duality',
+ },
+ }
+
+=item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 )
+
+ {
+ _query => 'select',
+ _ast_version => 0.0001,
+ select => [
+ {
+ type => 'SelectComponent',
+ value => {
+ type => 'Value',
+ subtype => 'number',
+ value => 1,
+ },
+ },
+ ],
+ tables => {
+ type => 'Operator',
+ op => 'LEFT OUTER',
+ args => [
+ {
+ type => 'TableIdentifier',
+ value => {
+ type => 'Identifier',
+ element1 => 'foo',
+ },
+ },
+ {
+ type => 'TableIdentifier',
+ value => {
+ type => 'Identifier',
+ element1 => 'bar',
+ },
+ },
+ ],
+ on => {
+ type => 'Operator',
+ op => '=',
+ args => [
+ {
+ type => 'Identifier',
+ element1 => 'foo',
+ element2 => 'col1',
+ },
+ {
+ type => 'Identifier',
+ element1 => 'bar',
+ element2 => 'col2',
+ },
+ ],
+ },
+ },
+ }
+
+=back
+
+=head1 TODO
+
+=over 4
+
+=item * sproc unit
+
+=back
=head1 AUTHORS