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
=item * Null
-Null is SQL's NULL and corresponds to Perl's C<undef>.
+A Null is SQL's NULL and corresponds to Perl's C<undef>.
+
+=item * Boolean
+
+A Boolean is a two-value entity - true or false. Some DBMSes provide an explicit
+boolean while others do not.
+
+=item * Date
-=item * BindParameter
+A Date represents (generally) a year, month, and day.
-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 * Time
-BindParameters are normally represented by a '?'.
+A Time represents (generally) an hour, minute, and second.
+
+=item * DateTime
+
+A DateTime represents the complete description necessary to determine a specific point in
+time. This could correspond to a L<DateTime/> object in Perl.
=back
{
type => 'Value'
- subtype => [ 'String' | 'Number' | 'Null' | 'BindParameter' ]
+ subtype => [ 'String' | 'Number' | 'Null' | 'Boolean' | 'Date' | 'Time' | 'DateTime' ]
value => Scalar
+ is_bind => Boolean
}
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.
+C<is_bind> defaults to true. It determines whether or not the Visitor should
+attempt to treat this value as a BindParameter or not.
+
=head3 Operator
An Operator would be, in SQL dialect terms, a unary operator, a binary operator,
{
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; it is the "AS Y" part.
+
+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):
-The value should be either an Identifier or a SubQuery.
+=head3 select
-The hash for an Operator within a tables clause will be composed as follows:
+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 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 that also allows for Joins.
+
+The hash for an Join within a tables clause will be composed as follows:
- # Operator
{
- type => 'Operator',
+ type => 'Join',
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, ]*
- ],
+The hash for an set clause will be composed as follows:
-=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, ]*
- ],
+ {
+ 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:
-
- 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 groupby clause unit is an array of one or more GroupbyComponent units.
-A for clause is composed as follows:
+The hash for a GroupbyComponent unit 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.
+ {
+ type => 'GroupbyComponent',
+ value => Expression,
+ }
-A connectby clause is composed as follows:
+The value should either be an Identifier or a Number. The number corresponds to
+a column in the select clause.
- Identifier, WhereExpression
+=head2 Possible RDBMS-specific clauses
-=head1 EXAMPLES
+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.
-The following are example SQL statements and a possible AST for each one.
+=head3 rows
-=over 4
+This corresponds to the clause that is used in some RDBMS engines to limit the
+number of rows returned by a SELECT statement. In MySQL, this would be the LIMIT
+clause.
-=item * SELECT 1
+The hash for a rows clause is composed as follows:
{
- _query => 'select',
- _ast_version => 0.0001,
- select => [
- {
- type => 'SelectComponent',
- value => {
- type => 'Value',
- subtype => 'number',
- value => 1,
- },
- },
- ],
+ start => Number,
+ count => Number,
}
-=item * SELECT NOW() AS time FROM dual AS duality
+The start attribute, if ommitted, will default to 0. The count attribute is
+optional.
- {
- _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',
- },
- }
+=head3 for
-=item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 )
+This corresponds to the clause that is used in some RDBMS engines to indicate
+what locks are to be taken by this SELECT statement.
+
+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,
- },
- },
- ],
- 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',
- },
- ],
- },
- },
+ value => '< UPDATE | DELETE >',
}
-=back
-
=head1 TODO
=over 4
=item * sproc unit
+=item * UNION, UNION ALL, and MINUS
+
+=item * start the API guidelines
+
=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