FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
ezsql/shared/ezQueryInterface.php at v3 · ezSQL/ezsql · GitHub
Uh oh!
There was an error while loading.
Please reload this page
.
ezSQL
/
ezsql
Public
Notifications
You must be signed in to change notification settings
Fork
280
Star
866
Code
Issues
0
Pull requests
0
Actions
Projects
Wiki
Security and quality
0
Insights
Additional navigation options
Code
Issues
Pull requests
Actions
Projects
Wiki
Security and quality
Insights
Expand file tree
Breadcrumbs
ezsql
/
shared
/
ezQueryInterface.php
Copy path
More file actions
More file actions
Latest commit
History
History
History
421 lines (398 loc) · 17.4 KB
Breadcrumbs
ezsql
/
shared
/
ezQueryInterface.php
Copy path
File metadata and controls
421 lines (398 loc) · 17.4 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
<?php
/**
* Author: Lawrence Stubbs <technoexpressnet@gmail.com>
*
* Important: Verify that every feature you use will work with your database vendor.
* ezSQL Query Builder will attempt to validate the generated SQL according to standards.
* Any errors will return an boolean false, and you will be responsible for handling.
*
* ezQuery does no validation whatsoever if certain features even work with the
* underlying database vendor.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
* A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
* DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
* THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
* OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*
* This software consists of voluntary contributions made by many individuals
* and is licensed under the MIT license.
*/
namespace
ezsql
;
interface
ezQueryInterface
{
/**
* Clean input of XSS, html, javascript, etc...
* @param string $string
* @return string cleaned string
*/
public
static
function
clean
(
$
string
);
/**
* Turn on prepare function availability in ezQuery shortcut method calls
*/
public
function
prepareActive
();
/**
* Turn off prepare function availability in ezQuery shortcut method calls
*/
public
function
prepareInActive
();
/**
* Specifies a grouping over the results of the query.
*<code>
* selecting('table',
* 'columns',
* where( eq( 'columns', values, _AND ), like( 'columns', _d ) ),
* groupBy( 'columns' ),
* having( between( 'columns', values1, values2 ) ),
* orderBy( 'columns', 'desc' );
*</code>
* @param mixed $groupBy The grouping expression.
*
* @return string - GROUP BY SQL statement, or false on error
*/
public
function
groupBy
(
$
groupBy
);
/**
* Specifies a restriction over the groups of the query.
*
* format
* `having( array(x, =, y, and, extra) );` or
* `having( "x = y and extra" );`
*
* example:
* `having( array(key, operator, value, combine, extra) );`or
* `having( "key operator value combine extra" );`
*
* @param array $having
* @param string $key, - table column
* @param string $operator, - set the operator condition,
* either '<','>', '=', '!=', '>=', '<=', '<>', 'in',
* 'like', 'between', 'not between', 'is null', 'is not null'
* @param mixed $value, - will be escaped
* @param string $combine, - combine additional where clauses with,
* either 'AND','OR', 'NOT', 'AND NOT'
* or carry over of @value in the case the @operator is 'between' or 'not between'
* @param string $extra - carry over of @combine in the case the operator is 'between' or 'not between'
* @return bool/string - HAVING SQL statement, or false on error
*/
public
function
having
(...
$
having
);
/**
* Return all rows from multiple tables where the join condition is met.
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
public
function
innerJoin
(
string
$
leftTable
=
null
,
string
$
rightTable
=
null
,
string
$
leftColumn
=
null
,
string
$
rightColumn
=
null
,
$
condition
= \
EQ
);
/**
* This type of join returns all rows from the LEFT-hand table
* specified in the ON condition and only those rows from the other table
* where the joined fields are equal (join condition is met).
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
public
function
leftJoin
(
string
$
leftTable
=
null
,
string
$
rightTable
=
null
,
string
$
leftColumn
=
null
,
string
$
rightColumn
=
null
,
$
condition
= \
EQ
);
/**
* This type of join returns all rows from the RIGHT-hand table
* specified in the ON condition and only those rows from the other table
* where the joined fields are equal (join condition is met).
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
public
function
rightJoin
(
string
$
leftTable
=
null
,
string
$
rightTable
=
null
,
string
$
leftColumn
=
null
,
string
$
rightColumn
=
null
,
$
condition
= \
EQ
);
/**
* This type of join returns all rows from the LEFT-hand table and RIGHT-hand table
* with NULL values in place where the join condition is not met.
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
public
function
fullJoin
(
string
$
leftTable
=
null
,
string
$
rightTable
=
null
,
string
$
leftColumn
=
null
,
string
$
rightColumn
=
null
,
$
condition
= \
EQ
);
/**
* Returns an `UNION` SELECT SQL string, given the
* - table, column fields, conditions or conditional array.
*
* In the following format:
* ```
* union(
* table,
* columns,
* // innerJoin(), leftJoin(), rightJoin(), fullJoin() alias of
* joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset )
*);
* ```
* @param $table, - database table to access
* @param $columnFields, - table columns, string or array
* @param mixed $conditions - same as selecting method.
*
* @return bool|string - false for error
*/
public
function
union
(
$
table
=
''
,
$
columnFields
=
'
*
'
, ...
$
conditions
);
/**
* Returns an `UNION ALL` SELECT SQL string, given the
* - table, column fields, conditions or conditional array.
*
* In the following format:
* ```
* unionAll(
* table,
* columns,
* // innerJoin(), leftJoin(), rightJoin(), fullJoin() alias of
* joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset )
*);
* ```
* @param $table, - database table to access
* @param $columnFields, - table columns, string or array
* @param mixed $conditions - same as selecting method.
*
* @return bool|string - false for error
*/
public
function
unionAll
(
$
table
=
''
,
$
columnFields
=
'
*
'
, ...
$
conditions
);
/**
* Specifies an ordering for the query results.
* @param string $orderBy - The column.
* @param string $order - The ordering direction.
*
* @return string - ORDER BY SQL statement, or false on error
*/
public
function
orderBy
(
$
orderBy
,
$
order
);
/**
* Specifies records from one or more tables in a database and
* limit the number of records returned.
*
* @param int $numberOf - set limit number of records to be returned.
* @param int $offset - Optional. The first row returned by LIMIT will be determined by offset value.
*
* @return string - LIMIT and/or OFFSET SQL statement, or false on error
*/
public
function
limit
(
$
numberOf
,
$
offset
=
null
);
/**
* Helper returns an WHERE sql clause string.
*
* format:
* `where( array(x, =, y, and, extra) )` or
* `where( "x = y and extra" );` // Strings will need to be double spaced
*
* example:
* `where( array(key, operator, value, combine, extra) );` or
* `where( "key operator value combine extra" );` // Strings will need to be double spaced
*
* @param array $whereKeyArray
* @param $key, - table column
* @param $operator, - set the operator condition,
* either '<','>', '=', '!=', '>=', '<=', '<>', 'in', 'like',
* 'not like', 'between', 'not between', 'is null', 'is not null'
* @param $value, - will be escaped
* @param $combine, - combine additional where clauses with,
* either 'AND','OR', 'NOT', 'AND NOT'
* or carry over of `value` in the case the `operator` is 'between' or 'not between'
* @param $extra - carry over of `combine` in the case the operator is 'between' or 'not between')"
*
* @return mixed bool/string - WHERE SQL statement, or false on error
*/
public
function
where
( ...
$
whereKeyArray
);
/**
* Returns an SQL string or result set, given the
* - table, column fields, conditions or conditional array.
*
* In the following format:
* ```
* selecting(
* table,
* columns,
* // innerJoin(), leftJoin(), rightJoin(), fullJoin() alias of
* joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset ),
* union(table, columnFields, conditions), // Returns an select SQL string with `UNION`
* unionAll(table, columnFields, conditions) // Returns an select SQL string with `UNION ALL`
*);
* ```
* @param $table, - database table to access
* @param $columnFields, - table columns, string or array
* @param mixed $conditions - of the following parameters:
*
* @param $joins, - join clause (type, left table, right table, left column, right column, condition = EQ)
* @param $whereKey, - where clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
* @param $groupBy, - grouping over clause the results
* @param $having, - having clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
* @param $orderby, - ordering by clause for the query
* @param $limit, - limit clause the number of records
* @param $union/$unionAll - union clause combine the result sets and removes duplicate rows/does not remove
*
* @return mixed result set - see docs for more details, or false for error
*/
public
function
selecting
(
$
table
=
''
,
$
columnFields
=
'
*
'
, ...
$
conditions
);
/**
* Does an create select statement by calling selecting method
*
* @param $newTable, - new database table to be created
* @param $fromColumns - the columns from old database table
* @param $oldTable - old database table
* @param $fromWhere, - where clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
*
* @return mixed bool/result - false for error
*/
public
function
create_select
(
$
newTable
,
$
fromColumns
,
$
oldTable
=
null
, ...
$
fromWhere
);
/**
* Does an select into statement by calling selecting method
* @param $newTable, - new database table to be created
* @param $fromColumns - the columns from old database table
* @param $oldTable - old database table
* @param $fromWhere, - where clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
*
* @return mixed bool/result - false for error
*/
public
function
select_into
(
$
newTable
,
$
fromColumns
,
$
oldTable
=
null
, ...
$
fromWhere
);
/**
* Does an update query with an array, by conditional operator array
* @param $table, - database table to access
* @param $keyAndValue, - table fields, assoc array with key = value (doesn't need escaped)
* @param $WhereKey, - where clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
*
* @return mixed bool/results - false for error
*/
public
function
update
(
$
table
=
''
,
$
keyAndValue
, ...
$
whereKeys
);
/**
* Helper does the actual delete query with an array
* @return mixed bool/results - false for error
*/
public
function
delete
(
$
table
=
''
, ...
$
whereKeys
);
/**
* Does an replace query with an array
* @param $table, - database table to access
* @param $keyAndValue - table fields, assoc array with key = value (doesn't need escaped)
* @return mixed bool/id of replaced record, or false for error
*/
public
function
replace
(
$
table
=
''
,
$
keyAndValue
);
/**
* Does an insert query with an array
* @param $table, - database table to access
* @param $keyAndValue - table fields, assoc array with key = value (doesn't need escaped)
* @return mixed bool/id of inserted record, or false for error
*/
public
function
insert
(
$
table
=
''
,
$
keyAndValue
);
/**
* Does an insert into select statement by calling insert method helper then selecting method
* @param $toTable, - database table to insert table into
* @param $toColumns - the receiving columns from other table columns, leave blank for all or array of column fields
* @param $WhereKey, - where clause ( array(x, =, y, and, extra) ) or ( "x = y and extra" )
*
* @return mixed bool/id of inserted record, or false for error
*/
public
function
insert_select
(
$
toTable
=
''
,
$
toColumns
=
'
*
'
,
$
fromTable
=
null
,
$
fromColumns
=
'
*
'
, ...
$
fromWhere
);
/**
* Creates an database table and columns, by either:
* - array( column, datatype, ...value/options arguments ) // calls create_schema()
* - column( column, datatype, ...value/options arguments ) // returns string
* - primary( primary_key_label, ...primaryKeys) // returns string
* - foreign( foreign_key_label, ...foreignKeys) // returns string
* - unique( unique_key_label, ...uniqueKeys) // returns string
*
* @param string $table, - The name of the db table that you wish to create
* @param mixed $schemas, - An array of:
*
* @param string $column|CONSTRAINT, - column name/CONSTRAINT usage for PRIMARY|FOREIGN KEY
* @param string $type|$constraintName, - data type for column/primary|foreign constraint name
* @param mixed $size|...$primaryForeignKeys,
* @param mixed $value, - column should be NULL or NOT NULL. If omitted, assumes NULL
* @param mixed $default - Optional. It is the value to assign to the column
*
* @return mixed results of query() call
*/
public
function
create
(
string
$
table
=
null
, ...
$
schemas
);
}
Back
|
FazBrowse Home
|
New Git URL