[dbsrgits/SQL-Abstract-2.0-ish.git] / lib / SQL / Abstract / Manual / Examples.pod

=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 * 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',
          },
      },
  }

=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 * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ), ( 2, 4 )

  {
      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',
              },
          ],
          [
              {
                  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',
          element1 => 'foo',
      },
      set => [
          [
              {
                  type => 'Identifier,
                  element1 => 'col1',
              },
          ],
          [
              {
                  type => 'Value',
                  subtype => 'Number',
                  value => 1,
              },
          ],
      ],
  }

=item * UPDATE foo SET col1 = 1, col2 = 6

  {
      type => 'update',
      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 => 6,
              },
          ],
      ],
  }

=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,
              },
          ],
      },
  }

=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
Commit	Line	Data
37f2cc3f	1	=head1 NAME
	2
	3	SQL::Abstract::Manual::Specification
	4
	5	=head1 DESCRIPTION
	6
	7	These are the examples for the AST
	8
	9	=head1 EXAMPLES
	10
	11	The following are example SQL statements and the AST that would represent each
	12	one. The SQL used is from the MySQL dialect.
	13
	14	=over 4
	15
	16	=item * SELECT 1
	17
	18	{
	19	type => 'select',
	20	ast_version => 0.0001,
	21	select => [
	22	{
662b716d	23	type => 'Value',
	24	subtype => 'Number',
	25	value => 1,
37f2cc3f	26	},
	27	],
	28	}
	29
	30	=item * SELECT NOW() AS time FROM dual AS duality
	31
	32	{
	33	type => 'select',
	34	ast_version => 0.0001,
	35	select => [
	36	{
662b716d	37	type => 'Alias',
37f2cc3f	38	value => {
	39	type => 'Function',
	40	function => 'NOW',
	41	},
3004ebb7	42	as => {
	43	type => 'Identifier',
	44	element1 => 'time',
	45	},
37f2cc3f	46	},
	47	],
	48	tables => {
662b716d	49	type => 'Alias',
37f2cc3f	50	value => {
	51	type => 'Identifier',
	52	element1 => 'dual',
	53	},
3004ebb7	54	as => {
	55	type => 'Identifier',
	56	element1 => 'duality',
	57	},
37f2cc3f	58	},
	59	}
	60
	61	=item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 )
	62
	63	{
	64	type => 'select',
	65	ast_version => 0.0001,
	66	select => [
	67	{
662b716d	68	type => 'Value',
	69	subtype => 'Number',
	70	value => 1,
37f2cc3f	71	},
	72	],
	73	tables => {
	74	type => 'Operator',
	75	op => 'LEFT OUTER',
	76	args => [
	77	{
662b716d	78	type => 'Identifier',
662b716d	79	element1 => 'foo',
37f2cc3f	80	},
37f2cc3f	81	{
662b716d	82	type => 'Identifier',
662b716d	83	element1 => 'bar',
37f2cc3f	84	},
	85	],
	86	on => {
	87	type => 'Operator',
	88	op => '=',
	89	args => [
	90	{
	91	type => 'Identifier',
	92	element1 => 'foo',
	93	element2 => 'col1',
	94	},
	95	{
	96	type => 'Identifier',
	97	element1 => 'bar',
	98	element2 => 'col2',
	99	},
	100	],
	101	},
	102	},
	103	}
	104
9c604aeb	105	=item SELECT * FROM foo WHERE name = 'John'
	106
	107	{
	108	type => 'select',
	109	ast_version => 0.0001,
	110	select => [
	111	{
662b716d	112	type => 'Identifier',
662b716d	113	element1 => '*',
9c604aeb	114	},
	115	],
	116	tables => {
662b716d	117	type => 'Identifier',
662b716d	118	element1 => 'foo',
9c604aeb	119	},
	120	where => {
	121	type => 'Operator',
	122	op => '=',
	123	args => [
	124	{
	125	type => 'Identifier',
	126	element1 => 'name',
	127	},
	128	{
	129	type => 'Value',
	130	subtype => 'String',
	131	element1 => 'John',
	132	},
	133	],
4f6e8987	134	},
9c604aeb	135	}
9c604aeb	136
4f6e8987	137	=item SELECT COUNT(*) FROM foo WHERE name = 'John' AND ( title = 'Mr' OR abbrev = 'Dr' )
	138
	139	{
	140	type => 'select',
	141	ast_version => 0.0001,
	142	select => [
	143	{
662b716d	144	type => 'Operator',
	145	op => 'COUNT',
	146	args => [
	147	{
	148	type => 'Identifier',
	149	element1 => '*',
	150	},
	151	],
4f6e8987	152	},
	153	],
	154	tables => {
662b716d	155	type => 'Identifier',
662b716d	156	element1 => 'foo',
4f6e8987	157	},
	158	where => {
	159	type => 'Operator',
	160	op => 'AND',
	161	args => [
	162	{
	163	type => 'Operator',
	164	op => '=',
	165	args => [
	166	{
	167	type => 'Identifier',
	168	element1 => 'name',
	169	},
	170	{
	171	type => 'Value',
	172	subtype => 'String',
	173	element1 => 'John',
	174	},
	175	],
	176	},
	177	{
3d8ddf0b	178	type => 'Operator',
3d8ddf0b	179	op => 'OR',
4f6e8987	180	args => [
	181	{
	182	type => 'Operator',
3d8ddf0b	183	op => '=',
	184	args => [
	185	{
	186	type => 'Identifier',
	187	element1 => 'title',
	188	},
	189	{
	190	type => 'Value',
	191	subtype => 'String',
	192	element1 => 'Mr',
	193	},
	194	],
	195	},
	196	{
	197	type => 'Operator',
	198	op => '=',
4f6e8987	199	args => [
4f6e8987	200	{
3d8ddf0b	201	type => 'Identifier',
3d8ddf0b	202	element1 => 'abbrev',
4f6e8987	203	},
4f6e8987	204	{
3d8ddf0b	205	type => 'Value',
	206	subtype => 'String',
	207	element1 => 'Dr',
4f6e8987	208	},
	209	],
	210	},
	211	],
	212	},
	213	],
	214	},
	215	}
	216
de1a11fa	217	=item SELECT COUNT(DISTINCT(*)) FROM foo WHERE ( name = 'John' AND title = 'Mr' ) OR abbrev = 'Dr'
3d8ddf0b	218
	219	{
	220	type => 'select',
	221	ast_version => 0.0001,
	222	select => [
de1a11fa	223	{
662b716d	224	type => 'Operator',
	225	op => 'COUNT',
	226	args => [
	227	{
	228	type => 'Operator',
	229	op => 'DISTINCT',
	230	args => [
	231	{
	232	type => 'Identifier',
	233	element1 => '*',
	234	},
	235	],
	236	},
	237	],
3d8ddf0b	238	},
	239	],
	240	tables => {
662b716d	241	type => 'Identifier',
662b716d	242	element1 => 'foo',
3d8ddf0b	243	},
	244	where => {
	245	type => 'Operator',
	246	op => 'OR',
	247	args => [
	248	{
	249	type => 'Operator',
	250	op => 'AND',
	251	args => [
	252	{
	253	type => 'Operator',
	254	op => '=',
	255	args => [
	256	{
	257	type => 'Identifier',
	258	element1 => 'name',
	259	},
	260	{
	261	type => 'Value',
	262	subtype => 'String',
	263	element1 => 'John',
	264	},
	265	],
	266	},
	267	{
	268	type => 'Operator',
	269	op => '=',
	270	args => [
	271	{
	272	type => 'Identifier',
	273	element1 => 'title',
	274	},
	275	{
	276	type => 'Value',
	277	subtype => 'String',
	278	element1 => 'Mr',
	279	},
	280	],
	281	},
	282	],
	283	},
	284	{
	285	type => 'Operator',
	286	op => '=',
	287	args => [
	288	{
	289	type => 'Identifier',
	290	element1 => 'abbrev',
	291	},
	292	{
	293	type => 'Value',
	294	subtype => 'String',
	295	element1 => 'Dr',
	296	},
	297	],
	298	},
	299	],
	300	},
	301	}
	302
da74c1c8	303	=item * SELECT foo, bar baz FROM foo ORDER BY bar, baz DESC GROUP BY 1,3,2
	304
	305	{
	306	type => 'select',
	307	ast_version => 0.0001,
	308	select => [
	309	{
662b716d	310	type => 'Identifier',
662b716d	311	element1 => 'foo',
da74c1c8	312	},
da74c1c8	313	{
662b716d	314	type => 'Identifier',
662b716d	315	elements => 'bar',
da74c1c8	316	},
da74c1c8	317	{
662b716d	318	type => 'Identifier',
662b716d	319	element1 => 'baz',
da74c1c8	320	},
	321	],
	322	tables => {
662b716d	323	type => 'Identifier',
662b716d	324	element1 => 'foo',
da74c1c8	325	},
	326	orderby => [
	327	{
	328	type => 'OrderbyComponent',
	329	value => {
	330	type => 'Identifier',
	331	element1 => 'bar',
	332	},
	333	dir => 'ASC',
	334	},
	335	{
	336	type => 'OrderbyComponent',
	337	value => {
	338	type => 'Identifier',
	339	element1 => 'baz',
	340	},
	341	dir => 'DESC',
	342	},
	343	],
	344	groupby => [
	345	{
	346	type => 'GroupbyComponent',
	347	value => {
da93022e	348	type => 'Value',
da93022e	349	subtype => 'Number',
da74c1c8	350	value => 1,
	351	},
	352	},
	353	{
	354	type => 'GroupbyComponent',
	355	value => {
da93022e	356	type => 'Value',
da93022e	357	subtype => 'Number',
da74c1c8	358	value => 3,
	359	},
	360	},
	361	{
	362	type => 'GroupbyComponent',
	363	value => {
da93022e	364	type => 'Value',
da93022e	365	subtype => 'Number',
da74c1c8	366	value => 2,
	367	},
	368	},
	369	],
	370	}
	371
7b7ed1cb	372	=item * SELECT * FROM ( SELECT 1 ) AS foo
753e226d	373
753e226d	374	{
7b7ed1cb	375	type => 'select',
	376	ast_version => 0.0001,
	377	select => [
	378	{
	379	type => 'Identifier',
	380	element1 => '*',
	381	},
	382	],
	383	tables => {
	384	type => 'Identifier',
	385	element1 => 'foo',
	386	value => {
	387	type => 'select',
	388	ast_version => 0.0001,
	389	select => [
	390	{
	391	type => 'Value',
	392	subtype => 'Number',
	393	value => 1,
	394	},
	395	],
	396	},
	397	as => {
	398	type => 'Identifier',
	399	element1 => 'foo',
	400	},
	401	},
	402	}
	403
	404	=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 )
	405
	406	{
	407	type => 'insert',
753e226d	408	ast_version => 0.0001,
753e226d	409	tables => {
662b716d	410	type => 'Identifier',
662b716d	411	element1 => 'foo',
753e226d	412	},
753e226d	413	set => [
a08e7c02	414	[
a08e7c02	415	{
753e226d	416	type => 'Identifier,
	417	element1 => 'col1',
	418	},
7b7ed1cb	419	{
	420	type => 'Identifier,
	421	element1 => 'col2',
	422	},
a08e7c02	423	],
	424	[
	425	{
da93022e	426	type => 'Value',
da93022e	427	subtype => 'Number',
7b7ed1cb	428	value => '1',
	429	},
	430	{
	431	type => 'Value',
	432	subtype => 'Number',
	433	value => '3',
753e226d	434	},
a08e7c02	435	],
753e226d	436	],
	437	}
	438
7b7ed1cb	439	=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ), ( 2, 4 )
338df86b	440
	441	{
	442	type => 'insert',
	443	ast_version => 0.0001,
	444	tables => {
662b716d	445	type => 'Identifier',
662b716d	446	element1 => 'foo',
338df86b	447	},
a08e7c02	448	set => [
	449	[
	450	{
	451	type => 'Identifier,
	452	element1 => 'col1',
	453	},
	454	{
	455	type => 'Identifier,
	456	element1 => 'col2',
	457	},
	458	],
	459	[
	460	{
	461	type => 'Value',
	462	subtype => 'Number',
	463	value => '1',
	464	},
	465	{
	466	type => 'Value',
	467	subtype => 'Number',
	468	value => '3',
	469	},
	470	],
7b7ed1cb	471	[
	472	{
	473	type => 'Value',
	474	subtype => 'Number',
	475	value => '2',
	476	},
	477	{
	478	type => 'Value',
	479	subtype => 'Number',
	480	value => '3',
	481	},
	482	],
	483	],
	484	}
	485
	486	=item * UPDATE foo SET col1 = 1
	487
	488	{
	489	type => 'update',
	490	ast_version => 0.0001,
	491	tables => {
	492	type => 'Identifier',
	493	element1 => 'foo',
	494	},
	495	set => [
	496	[
	497	{
	498	type => 'Identifier,
	499	element1 => 'col1',
	500	},
	501	],
	502	[
	503	{
	504	type => 'Value',
	505	subtype => 'Number',
	506	value => 1,
	507	},
	508	],
	509	],
	510	}
	511
	512	=item * UPDATE foo SET col1 = 1, col2 = 6
	513
	514	{
	515	type => 'update',
	516	ast_version => 0.0001,
	517	tables => {
	518	type => 'Identifier',
	519	element1 => 'foo',
	520	},
	521	set => [
	522	[
	523	{
	524	type => 'Identifier,
	525	element1 => 'col1',
	526	},
	527	{
	528	type => 'Identifier,
	529	element1 => 'col2',
	530	},
	531	],
	532	[
	533	{
	534	type => 'Value',
535	subtype => 'Number',
536	value => 1,
537	},
538	{
539	type => 'Value',
540	subtype => 'Number',
541	value => 6,
542	},
543	],
338df86b	544	],
	545	}
	546
da93022e	547	=item * DELETE FROM foo WHERE col1 = 10
	548
	549	{
	550	type => 'delete',
	551	ast_version => 0.0001,
	552	tables => {
662b716d	553	type => 'Identifier',
662b716d	554	element1 => 'foo',
da93022e	555	},
	556	where => {
	557	type => 'Operator',
	558	op => '=',
	559	args => [
	560	{
	561	type => 'Identifier',
	562	element1 => 'col1',
	563	},
	564	{
	565	type => 'Value',
	566	subtype => 'Number',
	567	value => 10,
	568	},
	569	],
	570	},
	571	}
	572
37f2cc3f	573	=back
	574
	575	=head1 AUTHORS
	576
	577	robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
	578
	579	=head1 LICENSE
	580
	581	You may distribute this code under the same terms as Perl itself.
	582
	583	=cut