=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',
                  element1 => 'time',
              },
          },
      ],
      tables => {
          type => 'Alias',
          value => {
              type => 'Identifier',
              element1 => 'dual',
          },
          as => {
              type => 'Identifier',
              element1 => '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',
                  element1 => 'foo',
              },
              {
                  type => 'Identifier',
                  element1 => 'bar',
              },
          ],
          on => {
              type => 'Operator',
              op   => '=',
              args => [
                  {
                      type     => 'Identifier',
                      element1 => 'foo',
                      element2 => 'col1',
                  },
                  {
                      type     => 'Identifier',
                      element1 => 'bar',
                      element2 => 'col2',
                  },
              ],
          },
      },
  }

=item SELECT * FROM foo WHERE name = 'John'

  {
      type => 'select',
      ast_version => 0.0001,
      select => [
          {
              type     => 'Identifier',
              element1 => '*',
          },
      ],
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      where => {
          type => 'Operator',
          op   => '=',
          args => [
              {
                  type     => 'Identifier',
                  element1 => 'name',
              },
              {
                  type     => 'Value',
                  subtype  => 'String',
                  element1 => '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',
                      element1 => '*',
                  },
              ],
          },
      ],
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      where => {
          type => 'Operator',
          op   => 'AND',
          args => [
              {
                  type => 'Operator',
                  op   => '=',
                  args => [
                      {
                          type     => 'Identifier',
                          element1 => 'name',
                      },
                      {
                          type     => 'Value',
                          subtype  => 'String',
                          element1 => 'John',
                      },
                  ],
              },
              {
                  type => 'Operator',
                  op   => 'OR',
                  args => [
                      {
                          type => 'Operator',
                          op   => '=',
                          args => [
                              {
                                  type     => 'Identifier',
                                  element1 => 'title',
                              },
                              {
                                  type     => 'Value',
                                  subtype  => 'String',
                                  element1 => 'Mr',
                              },
                          ],
                      },
                      {
                          type => 'Operator',
                          op   => '=',
                          args => [
                              {
                                  type     => 'Identifier',
                                  element1 => 'abbrev',
                              },
                              {
                                  type     => 'Value',
                                  subtype  => 'String',
                                  element1 => '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',
                              element1 => '*',
                          },
                      ],
                  },
              ],
          },
      ],
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      where => {
          type => 'Operator',
          op   => 'OR',
          args => [
              {
                  type => 'Operator',
                  op   => 'AND',
                  args => [
                      {
                          type => 'Operator',
                          op   => '=',
                          args => [
                              {
                                  type     => 'Identifier',
                                  element1 => 'name',
                              },
                              {
                                  type     => 'Value',
                                  subtype  => 'String',
                                  element1 => 'John',
                              },
                          ],
                      },
                      {
                          type => 'Operator',
                          op   => '=',
                          args => [
                              {
                                  type     => 'Identifier',
                                  element1 => 'title',
                              },
                              {
                                  type     => 'Value',
                                  subtype  => 'String',
                                  element1 => 'Mr',
                              },
                          ],
                      },
                  ],
              },
              {
                  type => 'Operator',
                  op   => '=',
                  args => [
                      {
                          type     => 'Identifier',
                          element1 => 'abbrev',
                      },
                      {
                          type     => 'Value',
                          subtype  => 'String',
                          element1 => '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',
              element1 => 'foo',
          },
          {
              type => 'Identifier',
              elements => 'bar',
          },
          {
              type => 'Identifier',
              element1 => 'baz',
          },
      ],
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      orderby => [
          {
              type  => 'OrderbyComponent',
              value => {
                  type => 'Identifier',
                  element1 => 'bar',
              },
              dir => 'ASC',
          },
          {
              type  => 'OrderbyComponent',
              value => {
                  type => 'Identifier',
                  element1 => '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 * UPDATE foo SET col1 = 1

  {
      type => 'update',
      ast_version => 0.0001,
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      set => [
          [
              {
                  type => 'Identifier,
                  element1 => 'col1',
              },
          ],
          [
              {
                  type => 'Value',
                  subtype => 'Number',
                  value => 1,
              },
          ],
      ],
  }

=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 )

  {
      type => 'insert',
      ast_version => 0.0001,
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      set => [
          [
              {
                  type => 'Identifier,
                  element1 => 'col1',
              },
              {
                  type => 'Identifier,
                  element1 => 'col2',
              },
          ],
          [
              {
                  type => 'Value',
                  subtype => 'Number',
                  value => '1',
              },
              {
                  type => 'Value',
                  subtype => 'Number',
                  value => '3',
              },
          ],
      ],
  }

=item * DELETE FROM foo WHERE col1 = 10

  {
      type => 'delete',
      ast_version => 0.0001,
      tables => {
          type => 'Identifier',
          element1 => 'foo',
      },
      where => {
          type => 'Operator',
          op   => '=',
          args => [
              {
                  type     => 'Identifier',
                  element1 => 'col1',
              },
              {
                  type     => 'Value',
                  subtype  => 'Number',
                  value => 10,
              },
          ],
      },
  }

=item * SELECT * FROM ( SELECT 1 ) AS foo

  {
      type => 'select',
      ast_version => 0.0001,
      select => [
          {
              type => 'Identifier',
              element1 => '*',
          },
      ],
      tables => {
          type => 'Identifier',
          element1 => 'foo',
          value => {
              type => 'select',
              ast_version => 0.0001,
              select => [
                  {
                      type    => 'Value',
                      subtype => 'Number',
                      value   => 1,
                  },
              ],
          },
          as => {
              type => 'Identifier',
              element1 => 'foo',
          },
      },
  }

=back

=head1 AUTHORS

robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>

=head1 LICENSE

You may distribute this code under the same terms as Perl itself.

=cut