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' for REPLACE INTO. If a _query value is
+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
{
type => 'Identifier',
- element1 => Scalar,
- element2 => Scalar,
- element3 => Scalar,
+ elements => [ 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.
+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. Depending on the type, a Visitor may be able to make
-certain decisions.
+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
{
type => 'Operator',
op => String,
- args => ExpressionList,
+ args => [
+ Expression,
+ ],
}
Operators have a cardinality, or expected number of arguments. Some operators,
=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
Subqueries, when expressed in SQL, must be bounded by parentheses.
+=head3 Alias
+
+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.
+
+The hash will be structured as follows:
+
+ {
+ type => 'Alias',
+ value => Expression,
+ as => Identifier,
+ }
+
=head3 Expression
An Expression can be any one of the following:
=item * Subquery
+=item * Alias
+
=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). An null separator may also be used.
-
-The hash for an ExpressionList is as follows:
-
- {
- type => 'ExpressionList',
- separator => ',',
- elements => Array of Expressions,
- }
+=head3 Nesting
-An ExpressionList is always rendered in SQL with parentheses around it.
+There is no specific operator or nodetype for nesting. Instead, nesting is
+explicitly specified by node descent in the AST.
=head2 SQL clauses
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:
+The following clauses are expected to be handled by Visitors for each statement:
=over 4
-=item * :=
-
-This means "defined" and is used to create a new term to be used below.
+=item * select
-=item * []
+=over 4
-This means optional and indicates that the items within it are optional.
+=item * select
-=item * []*
+=item * tables
-This means optional and repeating as many times as desired.
+=item * where
-=item * |
+=item * orderby
-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 * groupby
-=item * C<< <> >>
+=back
-This is a grouping construct. It means that all elements within this construct
-are treated together for the purposes of optional, repeating, alternation, etc.
+=item * insert
-=back
+=over 4
-The expected clauses are (name and structure):
+=item * tables
-=head3 select
+=item * set
-This corresponds to the SELECT clause of a SELECT statement.
+=back
-A select clause unit is an array of one or more SelectComponent units.
+There are RDBMS-specific variations of the INSERT statement, such the one in
+MySQL's
-The hash for a SelectComponent unit is composed as follows:
+=item * update
- {
- type => 'SelectComponent',
- value => Expression,
- as => String,
- }
+=over 4
-The 'as' component is optional. Visitors may choose to make it required in
-certain situations.
+=item * tables
-=head3 tables
+=item * set
-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.
+=item * where
-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.
+=back
-A TableJoin is a junction of the following elements:
+=item * delete
=over 4
-=item * TableIdentifier
+=item * tables
-=item * Operator
+=item * where
=back
-The hash for a TableIdentifier will be composed as follows:
+=back
- # TableIdentifier
- {
- type => 'TableIdentifier',
- value => Expression,
- as => String,
- }
+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 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 type metadata entry, the
+appropriate clause name will be used.
-The value should be either an Identifier or a SubQuery.
+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 tables clause is an Expression.
The hash for an Operator within a tables clause will be composed as follows:
type => 'Operator',
op => '< LEFT|RIGHT|FULL [ OUTER ] > | INNER | CROSS',
on => Expression,
+ args => [ 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.
+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.
-
-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.
+The hash for an set clause will be composed as follows:
-A values clause is an ExpressionList and the unit is composed as follows.
-
- values => [
- 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:
+
+ {
+ type => 'OrderbyComponent',
+ value => Expression,
+ dir => '< ASC | DESC >',
+ }
- OrderByComponent [ OrderByDirection ]
- [ , OrderByComponent [ OrderByDirection ] ]*
+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.
- GroupByComponent := XXX-TODO-XXX
+The hash for a GroupbyComponent unit is composed as follows:
- GroupByComponent [ , GroupByComponent ]*
+ {
+ type => 'GroupbyComponent',
+ value => Expression,
+ }
+
+The value should either be an Identifier or a Number. The number corresponds to
+a column in the select clause.
+
+=head2 Possible RDBMS-specific clauses
+
+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.
-A rows clause is composed as follows:
+The hash for a rows clause is composed as follows:
- Number [, Number ]
+ {
+ start => Number,
+ count => 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:
-
- Identifier, WhereExpression
-
-=head1 EXAMPLES
-
-The following are example SQL statements and a possible AST for each one.
-
-=over 4
-
-=item * SELECT 1
+The hash for a for clause is composed as follows:
{
- _query => 'select',
- _ast_version => 0.0001,
- select => [
- {
- type => 'SelectComponent',
- value => {
- type => 'Value',
- subtype => 'number',
- value => 1,
- },
- },
+ start_with => [
+ Expression,
],
- }
-
-=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',
- },
- },
+ connect_by => {
+ option => '< PRIOR | NOCYCLE >'
+ cond => [
+ Expression,
],
- on => {
- type => 'Operator',
- op => '=',
- args => [
- {
- type => 'Identifier',
- element1 => 'foo',
- element2 => 'col1',
- },
- {
- type => 'Identifier',
- element1 => 'bar',
- element2 => 'col2',
- },
- ],
- },
},
+ order_siblings => orderby-clause,
}
-=back
+Both the start_with and order_siblings clauses are optional.
=head1 TODO
=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
+Convert INSERT and UPDATE into ->populate form.
+
=head1 AUTHORS
robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
projects / dbsrgits/SQL-Abstract-2.0-ish.git / blobdiff