=head1 NAME SQL::Abstract::Manual::Specification =head1 DESCRIPTION These are the examples for the AST =head1 EXAMPLES The following are example SQL statements and the AST that would represent each one. The SQL used is from the MySQL dialect. =over 4 =item * SELECT 1 { type => 'select', ast_version => 0.0001, select => [ { type => 'Value', subtype => 'Number', value => 1, }, ], } =item * SELECT NOW() AS time FROM dual AS duality { type => 'select', ast_version => 0.0001, select => [ { type => 'Alias', value => { type => 'Function', function => 'NOW', }, as => { type => 'Identifier', args => [ 'time' ], }, }, ], tables => { type => 'Alias', value => { type => 'Identifier', args => [ 'dual' ], }, as => { type => 'Identifier', args => [ 'duality' ], }, }, } =item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 ) { type => 'select', ast_version => 0.0001, select => [ { type => 'Value', subtype => 'Number', value => 1, }, ], tables => { type => 'Operator', op => 'LEFT OUTER', args => [ { type => 'Identifier', args => [ 'foo' ], }, { type => 'Identifier', args => [ 'bar' ], }, ], on => { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'foo', 'col1' ], }, { type => 'Identifier', args => [ 'bar', 'col2' ], }, ], }, }, } =item SELECT * FROM foo WHERE name = 'John' { type => 'select', ast_version => 0.0001, select => [ { type => 'Identifier', args => [ '*' ], }, ], tables => { type => 'Identifier', args => [ 'foo' ], }, where => { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'name' ], }, { type => 'Value', subtype => 'String', value => 'John', }, ], }, } =item SELECT COUNT(*) FROM foo WHERE name = 'John' AND ( title = 'Mr' OR abbrev = 'Dr' ) { type => 'select', ast_version => 0.0001, select => [ { type => 'Operator', op => 'COUNT', args => [ { type => 'Identifier', args => [ '*' ], }, ], }, ], tables => { type => 'Identifier', args => [ 'foo' ], }, where => { type => 'Operator', op => 'AND', args => [ { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'name' ], }, { type => 'Value', subtype => 'String', value => 'John', }, ], }, { type => 'Operator', op => 'OR', args => [ { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'title' ], }, { type => 'Value', subtype => 'String', value => 'Mr', }, ], }, { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'abbrev' ], }, { type => 'Value', subtype => 'String', vaue => 'Dr', }, ], }, ], }, ], }, } =item SELECT COUNT(DISTINCT(*)) FROM foo WHERE ( name = 'John' AND title = 'Mr' ) OR abbrev = 'Dr' { type => 'select', ast_version => 0.0001, select => [ { type => 'Operator', op => 'COUNT', args => [ { type => 'Operator', op => 'DISTINCT', args => [ { type => 'Identifier', args => [ '*' ], }, ], }, ], }, ], tables => { type => 'Identifier', args => [ 'foo' ], }, where => { type => 'Operator', op => 'OR', args => [ { type => 'Operator', op => 'AND', args => [ { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'name' ], }, { type => 'Value', subtype => 'String', value => 'John', }, ], }, { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'title' ], }, { type => 'Value', subtype => 'String', value => 'Mr', }, ], }, ], }, { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'abbrev' ], }, { type => 'Value', subtype => 'String', value => 'Dr', }, ], }, ], }, } =item * SELECT foo, bar baz FROM foo ORDER BY bar, baz DESC GROUP BY 1,3,2 { type => 'select', ast_version => 0.0001, select => [ { type => 'Identifier', args => [ 'foo' ], }, { type => 'Identifier', elements => 'bar', }, { type => 'Identifier', args => [ 'baz' ], }, ], tables => { type => 'Identifier', args => [ 'foo' ], }, orderby => [ { type => 'OrderbyComponent', value => { type => 'Identifier', args => [ 'bar' ], }, dir => 'ASC', }, { type => 'OrderbyComponent', value => { type => 'Identifier', args => [ 'baz' ], }, dir => 'DESC', }, ], groupby => [ { type => 'GroupbyComponent', value => { type => 'Value', subtype => 'Number', value => 1, }, }, { type => 'GroupbyComponent', value => { type => 'Value', subtype => 'Number', value => 3, }, }, { type => 'GroupbyComponent', value => { type => 'Value', subtype => 'Number', value => 2, }, }, ], } =item * SELECT * FROM ( SELECT 1 ) AS foo { type => 'select', ast_version => 0.0001, select => [ { type => 'Identifier', args => [ '*' ], }, ], tables => { type => 'Identifier', args => [ 'foo' ], value => { type => 'select', ast_version => 0.0001, select => [ { type => 'Value', subtype => 'Number', value => 1, }, ], }, as => { type => 'Identifier', args => [ 'foo' ], }, }, } =item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ) { type => 'insert', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, set => [ [ { type => 'Identifier, args => [ 'col1' ], }, { type => 'Identifier, args => [ 'col2' ], }, ], [ { type => 'Value', subtype => 'Number', value => '1', }, { type => 'Value', subtype => 'Number', value => '3', }, ], ], } =item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ), ( 2, 4 ) { type => 'insert', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, set => [ [ { type => 'Identifier, args => [ 'col1' ], }, { type => 'Identifier, args => [ 'col2' ], }, ], [ { type => 'Value', subtype => 'Number', value => '1', }, { type => 'Value', subtype => 'Number', value => '3', }, ], [ { type => 'Value', subtype => 'Number', value => '2', }, { type => 'Value', subtype => 'Number', value => '3', }, ], ], } =item * UPDATE foo SET col1 = 1 { type => 'update', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, set => [ [ { type => 'Identifier, args => [ 'col1' ], }, ], [ { type => 'Value', subtype => 'Number', value => 1, }, ], ], } =item * UPDATE foo SET col1 = 1, col2 = 6 { type => 'update', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, set => [ [ { type => 'Identifier, args => [ 'col1' ], }, { type => 'Identifier, args => [ 'col2' ], }, ], [ { type => 'Value', subtype => 'Number', value => 1, }, { type => 'Value', subtype => 'Number', value => 6, }, ], ], } =item * DELETE FROM foo WHERE col1 = 10 { type => 'delete', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, where => { type => 'Operator', op => '=', args => [ { type => 'Identifier', args => [ 'col1' ], }, { type => 'Value', subtype => 'Number', value => 10, }, ], }, } =item * INSERT INTO foo ( col1, col2 ) SELECT col1, col2 FROM bar; { type => 'insert', ast_version => 0.0001, tables => { type => 'Identifier', args => [ 'foo' ], }, set => [ [ { type => 'Identifier, args => [ 'col1' ], }, { type => 'Identifier, args => [ 'col2' ], }, ], [ { type => 'select', ast_version => 0.0001, select => [ { type => 'Identifier', args => [ 'col1' ], }, { type => 'Identifier', args => [ 'col2' ], }, ], tables => { type => 'Identifier', args => [ 'bar' ], }, }, ], ], } =back =head1 AUTHORS robkinyon: Rob Kinyon C<< >> =head1 LICENSE You may distribute this code under the same terms as Perl itself. =cut