=head1 NAME SQL::Abstract::Manual::Specification =head1 SYNOPSIS This discusses the specification for the AST provided by L. It is meant to describe how the AST is structured, various components provided by L for use with this AST, how to manipulate the AST, and various uses for the AST once it is generated. =head1 MOTIVATIONS L has been in use for many years. Originally created to handle the where-clause formation found in L, 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, 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. =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 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. =head2 Metadata keys These are the additional metadata keys that the AST provides for. =head3 type This denotes what kind of query this AST should be interpreted as. Different 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 type: =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 ast_version This denotes the version of the AST. Different versions will indicate different capabilities provided. Visitors will choose to respect the ast_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 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: { type => '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. Any of the elements may be '*', as in SELECT * or SELECT COUNT(*). Visitors must be careful to I quote asterisks. =head3 Value 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. 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. =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 for an example.) 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 => [ Expression, ], } 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 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 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 Expression An Expression can be any one of the following: =over 4 =item * Identifier =item * Value =item * Operator =item * Subquery =back An Expression is a meta-syntactic unit. An "Expression" unit will never appear within the AST. It acts as a junction. =head3 Nesting There is no specific operator or nodetype for nesting. Instead, nesting is explicitly specified by node descent in the AST. =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 following clauses are expected to be handled by Visitors for each statement: =over 4 =item * SELECT =over 4 =item * select =item * tables =item * where =item * orderby =item * groupby =back =item * insert =over 4 =item * tables =item * columns =item * values =back There are RDBMS-specific variations of the INSERT statement, such the one in MySQL's =item * update =over 4 =item * tables =item * set =item * where =back =item * delete =over 4 =item * tables =item * where =back =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: { type => 'SelectComponent', value => Expression, as => String, } 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 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 TableJoin is a junction of the following elements: =over 4 =item * TableIdentifier =item * Operator =back The hash for a TableIdentifier will be composed as follows: # TableIdentifier { type => 'TableIdentifier', value => Expression, as => String, } The value should be either an Identifier or a SubQuery. 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 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 of an Expression. =head3 set This corresponds to the SET clause in an INSERT or UPDATE statement. A set clause unit is an array of one or more SetComponent units. The hash for SetComponent unit is composed as follows: { type => 'SetComponent', col => Identifier, value => Expression, } =head3 columns This corresponds to the optional list of columns in an INSERT statement. A columns clause unit is an array of one or more Identifier units. =head3 values This corresponds to the VALUES clause in an INSERT statement. A values clause unit is an array of one or more Expression units. 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. A orderby clause unit is an array of one or more OrderbyComponent units. The hash for a OrderbyComponent unit is composed as follows: { 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. 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. =head2 Possible RDBMS-specific clauses The following clauses are provided as examples for RDBMS-specific elements. They are B 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 SELECT statement. In MySQL, this would be the LIMIT clause. The hash for a rows clause is composed as follows: { 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. The hash for a for clause is composed as follows: { value => '< UPDATE | DELETE >', } =head3 connectby This corresponds to the clause that is used in some RDBMS engines to provide for an adjacency-list query. 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 =back =head1 AUTHORS robkinyon: Rob Kinyon C<< >> =head1 LICENSE You may distribute this code under the same terms as Perl itself. =cut