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.
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.
+metadata keys.
=head2 Metadata keys
These are the additional metadata keys that the AST provides for.
-=head3 _query
+=head3 type
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'. If a _query value is unrecognized by the
-Visitor, the Visitor is expected to throw an error.
+Visitors may accept additional values for type. For example, a MySQL Visitor
+may choose to accept 'replace' for REPLACE INTO. If a type 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:
+All Visitors are expected to handle the following values for type:
=over 4
=back
-=head3 _version
+=head3 ast_version
This denotes the version of the AST. Different versions will indicate different
-capabilities provided. Visitors will choose to respect the _version as needed
+capabilities provided. Visitors will choose to respect the ast_version as needed
and desired.
=head2 Structural units
=over 4
-=item * _name
+=item * type
This indicates the structural unit that this hash is representing. While this
specification provides for standard structural units, different Visitors may
The hash will be structured as follows:
{
- _name => 'Identifier',
- items => [String],
+ type => 'Identifier',
+ elements => [ Scalar ],
}
-The items will always be quoted per the SQL dialect's quoting scheme. It is the
-responsibility of the Visitor to do this.
+All values in elements must be defined.
+
+Visitors are expected to, by default, quote all identifiers according to the SQL
+dialect's quoting scheme.
+
+Any of the elements may be '*', as in SELECT * or SELECT COUNT(*). Visitors must
+be careful to I<not> quote asterisks.
=head3 Value
-A Value is a Perl scalar. It may either be a:
+A Value is a Perl scalar. Depending on the subtype, a Visitor may be able to
+make certain decisions. The following are the minimally-valid subtypes:
=over 4
=item * String
-A String is a quoted series of characters
+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
+A Number is an unquoted number in some numeric format.
-=item * NULL
+=item * Null
-NULL is SQL's NULL and corresponds to Perl's C<undef>.
+Null is SQL's NULL and corresponds to Perl's C<undef>.
=item * BindParameter
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]
+ 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 Function
-
-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 Operator
-Yes, a Subquery is legal as an argument for many functions. Some example
-functions are:
+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.
-=over 4
+The hash will be structured as follows:
-=item * C<< MAX >>
+ {
+ type => 'Operator',
+ op => String,
+ args => [
+ Expression,
+ ],
+ }
-=item * C<< MIN >>
+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.
-=item * C<< SUM >>
+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.
-=back
+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".
+A Subquery is another AST whose type 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 Alias
-A UnaryOperator takes a single argument on the RHS and is one of the following:
-
-=over 4
+An Alias is any place where the construct "X as Y" appears. While the "as Y" is
+often optional, the AST will make it required.
-=item * C<< NOT >>
-
-=back
+The hash will be structured as follows:
-=head3 BinaryOperator
+ {
+ type => 'Alias',
+ value => Expression,
+ as => Identifier,
+ }
-A BinaryOperator takes two arguments (one on the LHS and one on the RHS).
+=head3 Expression
-Examples of BinaryOperators would include:
+An Expression can be any one of the following:
=over 4
-=item * X = Y
+=item * Identifier
-=item * X != Y
+=item * Value
-=item * X > Y
+=item * Operator
-=item * X < Y
+=item * Subquery
-=item * X >= Y
+=item * Alias
-=item * X <= Y
+=back
-=item * X IS Y
+An Expression is a meta-syntactic unit. An "Expression" unit will never appear
+within the AST. It acts as a junction.
-=item * X IS NOT Y
+=head3 Nesting
-=item * X IN Y
+There is no specific operator or nodetype for nesting. Instead, nesting is
+explicitly specified by node descent in the AST.
-=back
+=head2 SQL clauses
-Note that an operator can comprise of what would be multiple tokens in a normal
-parsing effort.
+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.
-=head3 TrinaryOperator
+The following clauses are expected to be handled by Visitors for each statement:
-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.
+=over 4
-Examples of TrinaryOperators would include:
+=item * select
=over 4
-=item * X BETWEEN Y AND Z
+=item * select
-=back
+=item * tables
-=head3 Expression
+=item * where
-An expression can be any one of the following:
+=item * orderby
-=over 4
-
-=item * Value
+=item * groupby
-=item * Function
+=back
-=item * Subquery
+=item * insert
-=item * UnaryOperator Expression
+=over 4
-=item * Expression BinaryOperator Expression
+=item * tables
-=item * ( Expression )
+=item * set
=back
-Parentheses indicate precedence and, in some situations, are necessary for
-certain operators.
+There are RDBMS-specific variations of the INSERT statement, such the one in
+MySQL's
-=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:
+=item * update
=over 4
-=item * :=
-
-This means "defined" and is used to create a new term to be used below.
+=item * tables
-=item * []
+=item * set
-This means optional and indicates that the items within it are optional.
+=item * where
-=item * []*
+=back
-This means optional and repeating as many times as desired.
+=item * delete
-=item * |
+=over 4
-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 * tables
-=item * C<< <> >>
+=item * where
-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
=back
This corresponds to the SELECT clause of a SELECT statement.
-A select clause is composed as follows:
-
- SelectComponent := Expression [ [ AS ] String ]
-
- SelectComponent
- [ , SelectComponent ]*
+A select clause unit is an array of one or more Expressions.
=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
+those respective statements. Depending on the type metadata entry, the
appropriate clause name will be used.
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.
-A table clause is composed as follows:
+A tables clause is an Expression.
- TableIdentifier := Identifier [ [ AS ] String ]
- JoinType := < LEFT|RIGHT [ OUTER ] > | INNER | CROSS
+The hash for an Operator within a tables clause will be composed as follows:
- TableIdentifier
- [
- < , TableIdentifier >
- | <
- [ JoinType ] JOIN TableIdentifier
- [
- < USING ( Identifier [ , Identifier ] ) >
- | < ON [ ( ] Expression [ , Expression ] [ ) ] >
- ]
- >
- ]*
+ # Operator
+ {
+ type => 'Operator',
+ op => '< LEFT|RIGHT|FULL [ OUTER ] > | INNER | CROSS',
+ on => Expression,
+ args => [ Expression ],
+ }
-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.
+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 and, as such, is
+not provided for by the AST. The on clause is optional.
=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
+A where clause is composed of an Expression.
=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.
+The hash for an set clause will be composed as follows:
-A columns clause is composed as follows:
-
- ( Identifier [ , Identifier ]* )
-
-=head3 values
-
-This corresponds to the VALUES clause in an INSERT statement.
-
-A values clause is composed as follows:
-
- ( Expression [ , Expression ]* )
+ {
+ type => 'Set',
+ args => [
+ [ Identifier ],
+ [ Expresion ],
+ ],
+ }
-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.
+The args is an array that is organized as follows: The first element is an array of
+Identifiers for the columns being set. The following arrays are Expressions describing
+the values. The various arrays should be the same length. The array of Identifiers can
+be omitted.
=head3 orderby
This corresponds to the ORDER BY clause in a SELECT statement.
-An orderby clause is composed as follows:
+A orderby clause unit is an array of one or more OrderbyComponent units.
- OrderByComponent := XXX-TODO-XXX
- OrderByDirection := ASC | DESC
+The hash for a OrderbyComponent unit is composed as follows:
- OrderByComponent [ OrderByDirection ]
- [ , OrderByComponent [ OrderByDirection ] ]*
+ {
+ type => 'OrderbyComponent',
+ value => Expression,
+ dir => '< ASC | DESC >',
+ }
+
+The value should either be an Identifier or a Number. The dir element, if
+omitted, will be defaulted to ASC by the AST. The number corresponds to a column
+in the select clause.
=head3 groupby
This corresponds to the GROUP BY clause in a SELECT statement.
-An groupby clause is composed as follows:
+A groupby clause unit is an array of one or more GroupbyComponent units.
+
+The hash for a GroupbyComponent unit is composed as follows:
+
+ {
+ type => 'GroupbyComponent',
+ value => Expression,
+ }
+
+The value should either be an Identifier or a Number. The number corresponds to
+a column in the select clause.
- GroupByComponent := XXX-TODO-XXX
+=head2 Possible RDBMS-specific clauses
- GroupByComponent [ , GroupByComponent ]*
+The following clauses are provided as examples for RDBMS-specific elements. They
+are B<not> expected to be supported by all Visitors. Visitors may choose whether
+or not to throw on an unexpected clause, though it is strongly recommended.
=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.
+number of rows returned by a SELECT statement. In MySQL, this would be the LIMIT
+clause.
+
+The hash for a rows clause is composed as follows:
-A rows clause is composed as follows:
+ {
+ start => Number,
+ count => Number,
+ }
- Number [, Number ]
+The start attribute, if ommitted, will default to 0. The count attribute is
+optional.
=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:
+The hash for a for clause is composed as follows:
- UPDATE | DELETE
+ {
+ value => '< 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:
+The hash for a for clause is composed as follows:
+
+ {
+ start_with => [
+ Expression,
+ ],
+ connect_by => {
+ option => '< PRIOR | NOCYCLE >'
+ cond => [
+ Expression,
+ ],
+ },
+ order_siblings => orderby-clause,
+ }
+
+Both the start_with and order_siblings clauses are optional.
+
+=head1 TODO
+
+=over 4
+
+=item * sproc unit
+
+=item * UNION, UNION ALL, and MINUS
+
+=item * INSERT INTO <table> SELECT ...
+
+=item * INSERT INTO <table> SET ...
+
+=item * UPDATE foo me SET bar = 1 FROM fnord f WHERE me.col1 = f.frobinator
+
+=item * INSERT moves to using the SET terminology with Visitor dwimming
+
+=back
- Identifier, WhereExpression
+Convert INSERT and UPDATE into ->populate form.
=head1 AUTHORS
projects / dbsrgits/SQL-Abstract-2.0-ish.git / blobdiff