[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 * 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
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
753e226d	372	=item * UPDATE foo SET col1 = 1
	373
	374	{
	375	type => 'update',
	376	ast_version => 0.0001,
	377	tables => {
662b716d	378	type => 'Identifier',
662b716d	379	element1 => 'foo',
753e226d	380	},
753e226d	381	set => [
a08e7c02	382	[
a08e7c02	383	{
753e226d	384	type => 'Identifier,
	385	element1 => 'col1',
	386	},
a08e7c02	387	],
	388	[
	389	{
da93022e	390	type => 'Value',
da93022e	391	subtype => 'Number',
753e226d	392	value => 1,
753e226d	393	},
a08e7c02	394	],
753e226d	395	],
	396	}
	397
338df86b	398	=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 )
	399
	400	{
	401	type => 'insert',
	402	ast_version => 0.0001,
	403	tables => {
662b716d	404	type => 'Identifier',
662b716d	405	element1 => 'foo',
338df86b	406	},
a08e7c02	407	set => [
	408	[
	409	{
	410	type => 'Identifier,
	411	element1 => 'col1',
	412	},
	413	{
	414	type => 'Identifier,
	415	element1 => 'col2',
	416	},
	417	],
	418	[
	419	{
	420	type => 'Value',
	421	subtype => 'Number',
	422	value => '1',
	423	},
	424	{
	425	type => 'Value',
	426	subtype => 'Number',
	427	value => '3',
	428	},
	429	],
338df86b	430	],
	431	}
	432
da93022e	433	=item * DELETE FROM foo WHERE col1 = 10
	434
	435	{
	436	type => 'delete',
	437	ast_version => 0.0001,
	438	tables => {
662b716d	439	type => 'Identifier',
662b716d	440	element1 => 'foo',
da93022e	441	},
	442	where => {
	443	type => 'Operator',
	444	op => '=',
	445	args => [
	446	{
	447	type => 'Identifier',
	448	element1 => 'col1',
	449	},
	450	{
	451	type => 'Value',
	452	subtype => 'Number',
	453	value => 10,
	454	},
	455	],
	456	},
	457	}
	458
3004ebb7	459	=item * SELECT * FROM ( SELECT 1 ) AS foo
	460
	461	{
	462	type => 'select',
	463	ast_version => 0.0001,
	464	select => [
	465	{
	466	type => 'Identifier',
	467	element1 => '*',
	468	},
	469	],
	470	tables => {
	471	type => 'Identifier',
	472	element1 => 'foo',
	473	value => {
	474	type => 'select',
	475	ast_version => 0.0001,
	476	select => [
	477	{
	478	type => 'Value',
	479	subtype => 'Number',
	480	value => 1,
	481	},
	482	],
	483	},
	484	as => {
	485	type => 'Identifier',
	486	element1 => 'foo',
	487	},
	488	},
	489	}
	490
37f2cc3f	491	=back
	492
	493	=head1 AUTHORS
	494
	495	robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
	496
	497	=head1 LICENSE
	498
	499	You may distribute this code under the same terms as Perl itself.
	500
	501	=cut