[dbsrgits/SQL-Abstract.git] / lib / SQL / Abstract / Reference.pm

package SQL::Abstract::Reference;

1;

__END__
=head1 NAME

SQL::Abstract::Reference - Reference documentation for L<SQL::Abstract>

=head1 TERMS

=head2 Expression (expr)

The DWIM structure that's passed to most methods by default is referred to
as expression syntax. If you see a variable with C<expr> in the name, or a
comment before a code block saying C<# expr>, this is what's being described.

=head2 Abstract Query Tree (aqt)

The explicit structure that an expression is converted into before it's
rendered into SQL is referred to as an abstract query tree. If you see a
variable with C<aqt> in the name, or a comment before a code block saying
C<# aqt>, this is what's being described.

=head2 SQL and Bind Values (query)

The final result of L<SQL::Abstract> rendering is generally an SQL statement
plus bind values for passing to DBI, ala:

  my ($sql, @bind) = $sqla->some_method(@args);
  my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};

If you see a comment before a code block saying C<# query>, the SQL + bind
array is what's being described.

=head2 Expander

An expander subroutine is written as:

  sub {
    my ($sqla, $name, $value, $k) = @_;
    ...
    return $aqt;
  }

$name is the expr node type for node expanders, the op name for op
expanders, and the clause name for clause expanders.

$value is the body of the thing being expanded

If an op expander is being called as the binary operator in a L</hashtriple>
expression, $k will be the hash key to be used as the left hand side
identifier.

This can trivially be converted to an C<ident> type AQT node with:

  my $ident = $sqla->expand_expr({ -ident => $k });

=head2 Renderer

A renderer subroutine looks like:

  sub {
    my ($sqla, $type, $value) = @_;
    ...
    $sqla->join_query_parts($join, @parts);
  }

and can be registered on a per-type, per-op or per-clause basis.

=head1 AQT node types

An AQT node consists of a hashref with a single key, whose name is C<-type>
where 'type' is the node type, and whose value is the data for the node.

The following is an explanation of the built-in AQT type renderers;
additional renderers can be registered as part of the extension system.

=head2 literal

  # expr
  { -literal => [ 'SPANG(?, ?)', 1, 27 ] }

  # query
  SPANG(?, ?)
  [ 1, 27 ]

=head2 ident

  # expr
  { -ident => 'foo' }

  # query
  foo
  []

  # expr
  { -ident => [ 'foo', 'bar' ] }

  # query
  foo.bar
  []

=head2 bind

  # expr
  { -bind => [ 'colname', 'value' ] }

  # query
  ?
  [ 'value' ]

=head2 row

  # expr
  {
    -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
  }

  # query
  (?, clown.car)
  [ 1 ]

=head2 func

  # expr
  {
    -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
  }

  # query
  FOO(bar, ?)
  [ 7 ]

=head2 op

Standard binop:

  # expr
  { -op => [
      '=', { -ident => [ 'bomb', 'status' ] },
      { -value => 'unexploded' },
  ] }

  # query
  bomb.status = ?
  [ 'unexploded' ]

Not:

  # expr
  { -op => [ 'not', { -ident => 'explosive' } ] }

  # query
  (NOT explosive)
  []

Postfix unop: (is_null, is_not_null, asc, desc)

  # expr
  { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }

  # query
  bobby IS NULL
  []

AND and OR:

  # expr
  { -op =>
      [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
  }

  # query
  ( x AND y AND z )
  []

IN (and NOT IN):

  # expr
  { -op => [
      'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
      { -bind => [ 'card', 'J' ] },
  ] }

  # query
  card IN ( ?, ? )
  [ 3, 'J' ]

BETWEEN (and NOT BETWEEN):

  # expr
  { -op => [
      'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
      { -bind => [ 'pints', 4 ] },
  ] }

  # query
  ( pints BETWEEN ? AND ? )
  [ 2, 4 ]

Comma (use -row for parens):

  # expr
  { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }

  # query
  1, 2
  []

=head2 values

  # expr
  { -values =>
      { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
  }

  # query
  VALUES (?, ?)
  [ 1, 2 ]

  # expr
  { -values => [
      { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
      { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
  ] }

  # query
  VALUES (1, 2), (3, 4)
  []

=head2 statement types

AQT node types are also provided for C<select>, C<insert>, C<update> and
C<delete>. These types are handled by the clauses system as discussed later.

=head1 Expressions

=head2 node expr

The simplest expression is just an AQT node:

  # expr
  { -ident => [ 'foo', 'bar' ] }

  # aqt
  { -ident => [ 'foo', 'bar' ] }

  # query
  foo.bar
  []

However, even in the case of an AQT node, the node value will be expanded if
an expander has been registered for that node type:

  # expr
  { -ident => 'foo.bar' }

  # aqt
  { -ident => [ 'foo', 'bar' ] }

  # query
  foo.bar
  []

=head2 identifier hashpair types

=head3 hashtriple

  # expr
  { id => { op => 'value' } }

  # aqt
  { -op =>
      [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
  }

  # query
  id OP ?
  [ 'value' ]

=head3 identifier hashpair w/simple value

Equivalent to a hashtriple with an op of '='.

  # expr
  { id => 'value' }

  # aqt
  {
    -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
  }

  # query
  id = ?
  [ 'value' ]

(an object value will also follow this code path)

=head3 identifier hashpair w/undef RHS

Converted to IS NULL :

  # expr
  { id => undef }

  # aqt
  { -op => [ 'is_null', { -ident => [ 'id' ] } ] }

  # query
  id IS NULL
  []

=head3 identifier hashpair w/literal RHS

Directly appended to the key, remember you need to provide an operator:

  # expr
  { id => \"= dont_try_this_at_home" }

  # aqt
  { -literal => [ 'id = dont_try_this_at_home' ] }

  # query
  id = dont_try_this_at_home
  []

  # expr
  { id => \[
        "= seriously(?, ?, ?, ?, ?)",
        "use",
        "-ident",
        "and",
        "-func",
      ]
  }

  # aqt
  { -literal =>
      [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
  }

  # query
  id = seriously(?, ?, ?, ?, ?)
  [ 'use', -ident => 'and', '-func' ]

(you may absolutely use this when there's no built-in expression type for
what you need and registering a custom one would be more hassle than it's
worth, but, y'know, do try and avoid it)

=head3 identifier hashpair w/arrayref value

Becomes equivalent to a -or over an arrayref of hashrefs with the identifier
as key and the member of the original arrayref as the value:

  # expr
  { id => [ 3, 4, { '>' => 12 } ] }

  # aqt
  { -op => [
      'or',
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      {
        -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
      },
  ] }

  # query
  ( id = ? OR id = ? OR id > ? )
  [ 3, 4, 12 ]

  # expr
  { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }

  # aqt
  { -op => [
      'or',
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      {
        -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
      },
  ] }

  # query
  ( id = ? OR id = ? OR id > ? )
  [ 3, 4, 12 ]

Special Case: If the first element of the arrayref is -or or -and, that's
used as the top level logic op:

  # expr
  { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }

  # aqt
  { -op => [
      'and',
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
  ] }

  # query
  ( id > ? AND id < ? )
  [ 3, 6 ]

=head3 identifier hashpair w/hashref value

Becomes equivalent to a -and over an arrayref of hashtriples constructed
with the identifier as the key and each key/value pair of the original
hashref as the value:

  # expr
  { id => { '<' => 4, '>' => 3 } }

  # aqt
  { -op => [
      'and',
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
  ] }

  # query
  ( id < ? AND id > ? )
  [ 4, 3 ]

  # expr
  { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }

  # aqt
  { -op => [
      'and',
      { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
      { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
  ] }

  # query
  ( id < ? AND id > ? )
  [ 4, 3 ]

=cut
Commit	Line	Data
5f372bd0	1	package SQL::Abstract::Reference;
	2
	3	1;
	4
	5	__END__
	6	=head1 NAME
	7
	8	SQL::Abstract::Reference - Reference documentation for L<SQL::Abstract>
	9
	10	=head1 TERMS
	11
	12	=head2 Expression (expr)
	13
	14	The DWIM structure that's passed to most methods by default is referred to
	15	as expression syntax. If you see a variable with C<expr> in the name, or a
	16	comment before a code block saying C<# expr>, this is what's being described.
	17
	18	=head2 Abstract Query Tree (aqt)
	19
	20	The explicit structure that an expression is converted into before it's
	21	rendered into SQL is referred to as an abstract query tree. If you see a
	22	variable with C<aqt> in the name, or a comment before a code block saying
b6da657f	23	C<# aqt>, this is what's being described.
5f372bd0	24
	25	=head2 SQL and Bind Values (query)
	26
	27	The final result of L<SQL::Abstract> rendering is generally an SQL statement
	28	plus bind values for passing to DBI, ala:
	29
	30	my ($sql, @bind) = $sqla->some_method(@args);
	31	my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};
	32
	33	If you see a comment before a code block saying C<# query>, the SQL + bind
	34	array is what's being described.
	35
b3cccbdf	36	=head2 Expander
	37
	38	An expander subroutine is written as:
	39
	40	sub {
	41	my ($sqla, $name, $value, $k) = @_;
	42	...
	43	return $aqt;
	44	}
	45
	46	$name is the expr node type for node expanders, the op name for op
	47	expanders, and the clause name for clause expanders.
	48
	49	$value is the body of the thing being expanded
	50
	51	If an op expander is being called as the binary operator in a L</hashtriple>
	52	expression, $k will be the hash key to be used as the left hand side
	53	identifier.
	54
	55	This can trivially be converted to an C<ident> type AQT node with:
	56
	57	my $ident = $sqla->expand_expr({ -ident => $k });
	58
	59	=head2 Renderer
	60
	61	A renderer subroutine looks like:
	62
	63	sub {
	64	my ($sqla, $type, $value) = @_;
	65	...
	66	$sqla->join_query_parts($join, @parts);
	67	}
	68
	69	and can be registered on a per-type, per-op or per-clause basis.
	70
5f372bd0	71	=head1 AQT node types
	72
	73	An AQT node consists of a hashref with a single key, whose name is C<-type>
	74	where 'type' is the node type, and whose value is the data for the node.
	75
b3cccbdf	76	The following is an explanation of the built-in AQT type renderers;
	77	additional renderers can be registered as part of the extension system.
	78
5f372bd0	79	=head2 literal
	80
	81	# expr
	82	{ -literal => [ 'SPANG(?, ?)', 1, 27 ] }
	83
	84	# query
	85	SPANG(?, ?)
	86	[ 1, 27 ]
	87
	88	=head2 ident
	89
	90	# expr
	91	{ -ident => 'foo' }
	92
	93	# query
	94	foo
	95	[]
	96
	97	# expr
	98	{ -ident => [ 'foo', 'bar' ] }
	99
	100	# query
	101	foo.bar
	102	[]
	103
	104	=head2 bind
	105
	106	# expr
	107	{ -bind => [ 'colname', 'value' ] }
	108
	109	# query
	110	?
	111	[ 'value' ]
	112
	113	=head2 row
	114
	115	# expr
	116	{
	117	-row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
	118	}
	119
	120	# query
	121	(?, clown.car)
	122	[ 1 ]
	123
	124	=head2 func
	125
	126	# expr
	127	{
	128	-func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
	129	}
	130
	131	# query
	132	FOO(bar, ?)
	133	[ 7 ]
	134
	135	=head2 op
	136
	137	Standard binop:
	138
	139	# expr
	140	{ -op => [
	141	'=', { -ident => [ 'bomb', 'status' ] },
	142	{ -value => 'unexploded' },
143	] }
144
145	# query
146	bomb.status = ?
147	[ 'unexploded' ]
148
149	Not:
150
151	# expr
152	{ -op => [ 'not', { -ident => 'explosive' } ] }
153
154	# query
155	(NOT explosive)
156	[]
157
158	Postfix unop: (is_null, is_not_null, asc, desc)
159
160	# expr
161	{ -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
162
163	# query
164	bobby IS NULL
165	[]
166
167	AND and OR:
168
169	# expr
170	{ -op =>
171	[ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
172	}
173
174	# query
175	( x AND y AND z )
176	[]
177
178	IN (and NOT IN):
179
180	# expr
181	{ -op => [
182	'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
183	{ -bind => [ 'card', 'J' ] },
184	] }
185
186	# query
187	card IN ( ?, ? )
188	[ 3, 'J' ]
189
190	BETWEEN (and NOT BETWEEN):
191
192	# expr
193	{ -op => [
194	'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
195	{ -bind => [ 'pints', 4 ] },
196	] }
197
198	# query
199	( pints BETWEEN ? AND ? )
200	[ 2, 4 ]
201
202	Comma (use -row for parens):
203
204	# expr
205	{ -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
206
207	# query
208	1, 2
209	[]
210
211	=head2 values
212
213	# expr
214	{ -values =>
215	{ -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
216	}
217
218	# query
219	VALUES (?, ?)
220	[ 1, 2 ]
221
222	# expr
223	{ -values => [
224	{ -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
225	{ -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
226	] }
227
228	# query
229	VALUES (1, 2), (3, 4)
230	[]
231
232	=head2 statement types
233
234	AQT node types are also provided for C<select>, C<insert>, C<update> and
235	C<delete>. These types are handled by the clauses system as discussed later.
236
237	=head1 Expressions
238
b3cccbdf	239	=head2 node expr
b3cccbdf	240
5f372bd0	241	The simplest expression is just an AQT node:
	242
	243	# expr
	244	{ -ident => [ 'foo', 'bar' ] }
	245
	246	# aqt
	247	{ -ident => [ 'foo', 'bar' ] }
	248
	249	# query
	250	foo.bar
	251	[]
	252
	253	However, even in the case of an AQT node, the node value will be expanded if
	254	an expander has been registered for that node type:
	255
	256	# expr
	257	{ -ident => 'foo.bar' }
	258
	259	# aqt
	260	{ -ident => [ 'foo', 'bar' ] }
	261
	262	# query
	263	foo.bar
	264	[]
	265
b3cccbdf	266	=head2 identifier hashpair types
	267
	268	=head3 hashtriple
	269
	270	# expr
	271	{ id => { op => 'value' } }
	272
	273	# aqt
	274	{ -op =>
	275	[ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
	276	}
	277
	278	# query
	279	id OP ?
	280	[ 'value' ]
	281
	282	=head3 identifier hashpair w/simple value
	283
	284	Equivalent to a hashtriple with an op of '='.
	285
	286	# expr
	287	{ id => 'value' }
	288
	289	# aqt
	290	{
	291	-op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
	292	}
	293
	294	# query
	295	id = ?
	296	[ 'value' ]
	297
	298	(an object value will also follow this code path)
	299
	300	=head3 identifier hashpair w/undef RHS
	301
	302	Converted to IS NULL :
	303
	304	# expr
	305	{ id => undef }
	306
	307	# aqt
	308	{ -op => [ 'is_null', { -ident => [ 'id' ] } ] }
	309
	310	# query
	311	id IS NULL
	312	[]
	313
	314	=head3 identifier hashpair w/literal RHS
	315
	316	Directly appended to the key, remember you need to provide an operator:
	317
	318	# expr
	319	{ id => \"= dont_try_this_at_home" }
	320
	321	# aqt
	322	{ -literal => [ 'id = dont_try_this_at_home' ] }
	323
	324	# query
	325	id = dont_try_this_at_home
	326	[]
	327
	328	# expr
	329	{ id => \[
330	"= seriously(?, ?, ?, ?, ?)",
331	"use",
332	"-ident",
333	"and",
334	"-func",
335	]
336	}
337
338	# aqt
339	{ -literal =>
340	[ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
341	}
342
343	# query
344	id = seriously(?, ?, ?, ?, ?)
345	[ 'use', -ident => 'and', '-func' ]
346
347	(you may absolutely use this when there's no built-in expression type for
348	what you need and registering a custom one would be more hassle than it's
349	worth, but, y'know, do try and avoid it)
350
351	=head3 identifier hashpair w/arrayref value
352
353	Becomes equivalent to a -or over an arrayref of hashrefs with the identifier
354	as key and the member of the original arrayref as the value:
355
356	# expr
357	{ id => [ 3, 4, { '>' => 12 } ] }
358
359	# aqt
360	{ -op => [
361	'or',
362	{ -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
363	{ -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
364	{
365	-op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
366	},
367	] }
368
369	# query
370	( id = ? OR id = ? OR id > ? )
371	[ 3, 4, 12 ]
372
373	# expr
374	{ -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
375
376	# aqt
377	{ -op => [
378	'or',
379	{ -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
380	{ -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
381	{
382	-op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
383	},
384	] }
385
386	# query
387	( id = ? OR id = ? OR id > ? )
388	[ 3, 4, 12 ]
389
390	Special Case: If the first element of the arrayref is -or or -and, that's
391	used as the top level logic op:
392
393	# expr
394	{ id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
395
396	# aqt
397	{ -op => [
398	'and',
399	{ -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
400	{ -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
401	] }
402
403	# query
404	( id > ? AND id < ? )
405	[ 3, 6 ]
406
407	=head3 identifier hashpair w/hashref value
408
409	Becomes equivalent to a -and over an arrayref of hashtriples constructed
410	with the identifier as the key and each key/value pair of the original
b6da657f	411	hashref as the value:
b3cccbdf	412
	413	# expr
	414	{ id => { '<' => 4, '>' => 3 } }
	415
	416	# aqt
	417	{ -op => [
	418	'and',
	419	{ -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
	420	{ -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
	421	] }
	422
	423	# query
	424	( id < ? AND id > ? )
	425	[ 4, 3 ]
	426
	427	# expr
	428	{ -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
	429
	430	# aqt
	431	{ -op => [
	432	'and',
	433	{ -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
	434	{ -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
	435	] }
	436
	437	# query
	438	( id < ? AND id > ? )
	439	[ 4, 3 ]
	440
	441	=cut