1: <?php
2: /**
3: * DataTables PHP libraries.
4: *
5: * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
6: *
7: * @author SpryMedia
8: * @version __VERSION__
9: * @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
10: * @license http://editor.datatables.net/license DataTables Editor
11: * @link http://editor.datatables.net
12: */
13:
14: namespace DataTables;
15: if (!defined('DATATABLES')) exit();
16:
17: use
18: DataTables,
19: DataTables\Editor\Join,
20: DataTables\Editor\Field;
21:
22:
23: /**
24: * DataTables Editor base class for creating editable tables.
25: *
26: * Editor class instances are capable of servicing all of the requests that
27: * DataTables and Editor will make from the client-side - specifically:
28: *
29: * * Get data
30: * * Create new record
31: * * Edit existing record
32: * * Delete existing records
33: *
34: * The Editor instance is configured with information regarding the
35: * database table fields that you wish to make editable, and other information
36: * needed to read and write to the database (table name for example!).
37: *
38: * This documentation is very much focused on describing the API presented
39: * by these DataTables Editor classes. For a more general overview of how
40: * the Editor class is used, and how to install Editor on your server, please
41: * refer to the {@link https://editor.datatables.net/manual Editor manual}.
42: *
43: * @example
44: * A very basic example of using Editor to create a table with four fields.
45: * This is all that is needed on the server-side to create a editable
46: * table - the {@link process} method determines what action DataTables /
47: * Editor is requesting from the server-side and will correctly action it.
48: * <code>
49: * Editor::inst( $db, 'browsers' )
50: * ->fields(
51: * Field::inst( 'first_name' )->validator( Validate::required() ),
52: * Field::inst( 'last_name' )->validator( Validate::required() ),
53: * Field::inst( 'country' ),
54: * Field::inst( 'details' )
55: * )
56: * ->process( $_POST )
57: * ->json();
58: * </code>
59: */
60: class Editor extends Ext {
61: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
62: * Statics
63: */
64:
65: /** Request type - read */
66: const ACTION_READ = 'read';
67:
68: /** Request type - create */
69: const ACTION_CREATE = 'create';
70:
71: /** Request type - edit */
72: const ACTION_EDIT = 'edit';
73:
74: /** Request type - delete */
75: const ACTION_DELETE = 'remove';
76:
77: /** Request type - upload */
78: const ACTION_UPLOAD = 'upload';
79:
80:
81: /**
82: * Determine the request type from an HTTP request.
83: *
84: * @param array $http Typically $_POST, but can be any array used to carry
85: * an Editor payload
86: * @param string $name The parameter name that the action should be read from.
87: * @return string `Editor::ACTION_READ`, `Editor::ACTION_CREATE`,
88: * `Editor::ACTION_EDIT` or `Editor::ACTION_DELETE` indicating the request
89: * type.
90: */
91: static public function action ( $http, $name='action' )
92: {
93: if ( ! isset( $http[$name] ) ) {
94: return self::ACTION_READ;
95: }
96:
97: switch ( $http[$name] ) {
98: case 'create':
99: return self::ACTION_CREATE;
100:
101: case 'edit':
102: return self::ACTION_EDIT;
103:
104: case 'remove':
105: return self::ACTION_DELETE;
106:
107: case 'upload':
108: return self::ACTION_UPLOAD;
109:
110: default:
111: throw new \Exception("Unknown Editor action: ".$http['action']);
112: }
113: }
114:
115:
116: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
117: * Constructor
118: */
119:
120: /**
121: * Constructor.
122: * @param Database $db An instance of the DataTables Database class that we can
123: * use for the DB connection. Can be given here or with the 'db' method.
124: * <code>
125: * 456
126: * </code>
127: * @param string|array $table The table name in the database to read and write
128: * information from and to. Can be given here or with the 'table' method.
129: * @param string|array $pkey Primary key column name in the table given in
130: * the $table parameter. Can be given here or with the 'pkey' method.
131: */
132: function __construct( $db=null, $table=null, $pkey=null )
133: {
134: // Set constructor parameters using the API - note that the get/set will
135: // ignore null values if they are used (i.e. not passed in)
136: $this->db( $db );
137: $this->table( $table );
138: $this->pkey( $pkey );
139: }
140:
141:
142: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
143: * Public properties
144: */
145:
146: /** @var string */
147: public $version = '1.9.2';
148:
149:
150:
151: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
152: * Private properties
153: */
154:
155: /** @var DataTables\Database */
156: private $_db = null;
157:
158: /** @var DataTables\Editor\Field[] */
159: private $_fields = array();
160:
161: /** @var array */
162: private $_formData;
163:
164: /** @var array */
165: private $_processData;
166:
167: /** @var string */
168: private $_idPrefix = 'row_';
169:
170: /** @var DataTables\Editor\Join[] */
171: private $_join = array();
172:
173: /** @var array */
174: private $_pkey = array('id');
175:
176: /** @var string[] */
177: private $_table = array();
178:
179: /** @var string[] */
180: private $_readTableNames = array();
181:
182: /** @var boolean */
183: private $_transaction = true;
184:
185: /** @var array */
186: private $_where = array();
187:
188: /** @var array */
189: private $_leftJoin = array();
190:
191: /** @var boolean - deprecated */
192: private $_whereSet = false;
193:
194: /** @var array */
195: private $_out = array();
196:
197: /** @var array */
198: private $_events = array();
199:
200: /** @var boolean */
201: private $_debug = false;
202:
203: /** @var array */
204: private $_debugInfo = array();
205:
206: /** @var string Log output path */
207: private $_debugLog = '';
208:
209: /** @var callback */
210: private $_validator = array();
211:
212: /** @var boolean Enable true / catch when processing */
213: private $_tryCatch = true;
214:
215: /** @var boolean Enable / disable delete on left joined tables */
216: private $_leftJoinRemove = false;
217:
218: /** @var string Action name allowing for configuration */
219: private $_actionName = 'action';
220:
221:
222:
223: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
224: * Public methods
225: */
226:
227: /**
228: * Get / set the action name to read in HTTP parameters. This can be useful
229: * to set if you are using a framework that uses the default name of `action`
230: * for something else (e.g. WordPress).
231: * @param string Value to set. If not given, then used as a getter.
232: * @return string|self Value, or self if used as a setter.
233: */
234: public function actionName ( $_=null )
235: {
236: return $this->_getSet( $this->_actionName, $_ );
237: }
238:
239:
240: /**
241: * Get the data constructed in this instance.
242: *
243: * This will get the PHP array of data that has been constructed for the
244: * command that has been processed by this instance. Therefore only useful after
245: * process has been called.
246: * @return array Processed data array.
247: */
248: public function data ()
249: {
250: return $this->_out;
251: }
252:
253:
254: /**
255: * Get / set the DB connection instance
256: * @param Database $_ DataTable's Database class instance to use for database
257: * connectivity. If not given, then used as a getter.
258: * @return Database|self The Database connection instance if no parameter
259: * is given, or self if used as a setter.
260: */
261: public function db ( $_=null )
262: {
263: return $this->_getSet( $this->_db, $_ );
264: }
265:
266:
267: /**
268: * Get / set debug mode and set a debug message.
269: *
270: * It can be useful to see the SQL statements that Editor is using. This
271: * method enables that ability. Information about the queries used is
272: * automatically added to the output data array / JSON under the property
273: * name `debugSql`.
274: *
275: * This method can also be called with a string parameter, which will be
276: * added to the debug information sent back to the client-side. This can
277: * be useful when debugging event listeners, etc.
278: *
279: * @param boolean|mixed $_ Debug mode state. If not given, then used as a
280: * getter. If given as anything other than a boolean, it will be added
281: * to the debug information sent back to the client.
282: * @param string [$path=null] Set an output path to log debug information
283: * @return boolean|self Debug mode state if no parameter is given, or
284: * self if used as a setter or when adding a debug message.
285: */
286: public function debug ( $_=null, $path=null )
287: {
288: if ( ! is_bool( $_ ) ) {
289: $this->_debugInfo[] = $_;
290:
291: return $this;
292: }
293:
294: if ( $path ) {
295: $this->_debugLog = $path;
296: }
297:
298: return $this->_getSet( $this->_debug, $_ );
299: }
300:
301:
302: /**
303: * Get / set field instance.
304: *
305: * The list of fields designates which columns in the table that Editor will work
306: * with (both get and set).
307: * @param Field|string $_... This parameter effects the return value of the
308: * function:
309: *
310: * * `null` - Get an array of all fields assigned to the instance
311: * * `string` - Get a specific field instance whose 'name' matches the
312: * field passed in
313: * * {@link Field} - Add a field to the instance's list of fields. This
314: * can be as many fields as required (i.e. multiple arguments)
315: * * `array` - An array of {@link Field} instances to add to the list
316: * of fields.
317: * @return Field|Field[]|Editor The selected field, an array of fields, or
318: * the Editor instance for chaining, depending on the input parameter.
319: * @throws \Exception Unkown field error
320: * @see {@link Field} for field documentation.
321: */
322: public function field ( $_=null )
323: {
324: if ( is_string( $_ ) ) {
325: for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
326: if ( $this->_fields[$i]->name() === $_ ) {
327: return $this->_fields[$i];
328: }
329: }
330:
331: throw new \Exception('Unknown field: '.$_);
332: }
333:
334: if ( $_ !== null && !is_array($_) ) {
335: $_ = func_get_args();
336: }
337: return $this->_getSet( $this->_fields, $_, true );
338: }
339:
340:
341: /**
342: * Get / set field instances.
343: *
344: * An alias of {@link field}, for convenience.
345: * @param Field $_... Instances of the {@link Field} class, given as a single
346: * instance of {@link Field}, an array of {@link Field} instances, or multiple
347: * {@link Field} instance parameters for the function.
348: * @return Field[]|self Array of fields, or self if used as a setter.
349: * @see {@link Field} for field documentation.
350: */
351: public function fields ( $_=null )
352: {
353: if ( $_ !== null && !is_array($_) ) {
354: $_ = func_get_args();
355: }
356: return $this->_getSet( $this->_fields, $_, true );
357: }
358:
359:
360: /**
361: * Get / set the DOM prefix.
362: *
363: * Typically primary keys are numeric and this is not a valid ID value in an
364: * HTML document - is also increases the likelihood of an ID clash if multiple
365: * tables are used on a single page. As such, a prefix is assigned to the
366: * primary key value for each row, and this is used as the DOM ID, so Editor
367: * can track individual rows.
368: * @param string $_ Primary key's name. If not given, then used as a getter.
369: * @return string|self Primary key value if no parameter is given, or
370: * self if used as a setter.
371: */
372: public function idPrefix ( $_=null )
373: {
374: return $this->_getSet( $this->_idPrefix, $_ );
375: }
376:
377:
378: /**
379: * Get the data that is being processed by the Editor instance. This is only
380: * useful once the `process()` method has been called, and is available for
381: * use in validation and formatter methods.
382: *
383: * @return array Data given to `process()`.
384: */
385: public function inData ()
386: {
387: return $this->_processData;
388: }
389:
390:
391: /**
392: * Get / set join instances. Note that for the majority of use cases you
393: * will want to use the `leftJoin()` method. It is significantly easier
394: * to use if you are just doing a simple left join!
395: *
396: * The list of Join instances that Editor will join the parent table to
397: * (i.e. the one that the {@link table} and {@link fields} methods refer to
398: * in this class instance).
399: *
400: * @param Join $_,... Instances of the {@link Join} class, given as a
401: * single instance of {@link Join}, an array of {@link Join} instances,
402: * or multiple {@link Join} instance parameters for the function.
403: * @return Join[]|self Array of joins, or self if used as a setter.
404: * @see {@link Join} for joining documentation.
405: */
406: public function join ( $_=null )
407: {
408: if ( $_ !== null && !is_array($_) ) {
409: $_ = func_get_args();
410: }
411: return $this->_getSet( $this->_join, $_, true );
412: }
413:
414:
415: /**
416: * Get the JSON for the data constructed in this instance.
417: *
418: * Basically the same as the {@link data} method, but in this case we echo, or
419: * return the JSON string of the data.
420: * @param boolean $print Echo the JSON string out (true, default) or return it
421: * (false).
422: * @return string|self self if printing the JSON, or JSON representation of
423: * the processed data if false is given as the first parameter.
424: */
425: public function json ( $print=true )
426: {
427: if ( $print ) {
428: $json = json_encode( $this->_out );
429:
430: if ( $json !== false ) {
431: echo $json;
432: }
433: else {
434: echo json_encode( array(
435: "error" => "JSON encoding error: ".json_last_error_msg()
436: ) );
437: }
438:
439: return $this;
440: }
441: return json_encode( $this->_out );
442: }
443:
444:
445: /**
446: * Echo out JSONP for the data constructed and processed in this instance.
447: * This is basically the same as {@link json} but wraps the return in a
448: * JSONP callback.
449: *
450: * @param string $callback The callback function name to use. If not given
451: * or `null`, then `$_GET['callback']` is used (the jQuery default).
452: * @return self Self for chaining.
453: * @throws \Exception JSONP function name validation
454: */
455: public function jsonp ( $callback=null )
456: {
457: if ( ! $callback ) {
458: $callback = $_GET['callback'];
459: }
460:
461: if ( preg_match('/[^a-zA-Z0-9_]/', $callback) ) {
462: throw new \Exception("Invalid JSONP callback function name");
463: }
464:
465: echo $callback.'('.json_encode( $this->_out ).');';
466: return $this;
467: }
468:
469:
470: /**
471: * Add a left join condition to the Editor instance, allowing it to operate
472: * over multiple tables. Multiple `leftJoin()` calls can be made for a
473: * single Editor instance to join multiple tables.
474: *
475: * A left join is the most common type of join that is used with Editor
476: * so this method is provided to make its use very easy to configure. Its
477: * parameters are basically the same as writing an SQL left join statement,
478: * but in this case Editor will handle the create, update and remove
479: * requirements of the join for you:
480: *
481: * * Create - On create Editor will insert the data into the primary table
482: * and then into the joined tables - selecting the required data for each
483: * table.
484: * * Edit - On edit Editor will update the main table, and then either
485: * update the existing rows in the joined table that match the join and
486: * edit conditions, or insert a new row into the joined table if required.
487: * * Remove - On delete Editor will remove the main row and then loop over
488: * each of the joined tables and remove the joined data matching the join
489: * link from the main table.
490: *
491: * Please note that when using join tables, Editor requires that you fully
492: * qualify each field with the field's table name. SQL can result table
493: * names for ambiguous field names, but for Editor to provide its full CRUD
494: * options, the table name must also be given. For example the field
495: * `first_name` in the table `users` would be given as `users.first_name`.
496: *
497: * @param string $table Table name to do a join onto
498: * @param string $field1 Field from the parent table to use as the join link
499: * @param string $operator Join condition (`=`, '<`, etc)
500: * @param string $field2 Field from the child table to use as the join link
501: * @return self Self for chaining.
502: *
503: * @example
504: * Simple join:
505: * <code>
506: * ->field(
507: * Field::inst( 'users.first_name as myField' ),
508: * Field::inst( 'users.last_name' ),
509: * Field::inst( 'users.dept_id' ),
510: * Field::inst( 'dept.name' )
511: * )
512: * ->leftJoin( 'dept', 'users.dept_id', '=', 'dept.id' )
513: * ->process($_POST)
514: * ->json();
515: * </code>
516: *
517: * This is basically the same as the following SQL statement:
518: *
519: * <code>
520: * SELECT users.first_name, users.last_name, user.dept_id, dept.name
521: * FROM users
522: * LEFT JOIN dept ON users.dept_id = dept.id
523: * </code>
524: */
525: public function leftJoin ( $table, $field1, $operator, $field2 )
526: {
527: $this->_leftJoin[] = array(
528: "table" => $table,
529: "field1" => $field1,
530: "field2" => $field2,
531: "operator" => $operator
532: );
533:
534: return $this;
535: }
536:
537:
538: /**
539: * Indicate if a remove should be performed on left joined tables when deleting
540: * from the parent row. Note that this is disabled by default and will be
541: * removed completely in v2. Use `ON DELETE CASCADE` in your database instead.
542: *
543: * @deprecated
544: * @param boolean $_ Value to set. If not given, then used as a getter.
545: * @return boolean|self Value if no parameter is given, or
546: * self if used as a setter.
547: */
548: public function leftJoinRemove ( $_=null )
549: {
550: return $this->_getSet( $this->_leftJoinRemove, $_ );
551: }
552:
553:
554: /**
555: * Add an event listener. The `Editor` class will trigger an number of
556: * events that some action can be taken on.
557: *
558: * @param string $name Event name
559: * @param callable $callback Callback function to execute when the event
560: * occurs
561: * @return self Self for chaining.
562: */
563: public function on ( $name, $callback )
564: {
565: if ( ! isset( $this->_events[ $name ] ) ) {
566: $this->_events[ $name ] = array();
567: }
568:
569: $this->_events[ $name ][] = $callback;
570:
571: return $this;
572: }
573:
574:
575: /**
576: * Get / set the primary key.
577: *
578: * The primary key must be known to Editor so it will know which rows are being
579: * edited / deleted upon those actions. The default value is ['id'].
580: *
581: * @param string|array $_ Primary key's name. If not given, then used as a
582: * getter. An array of column names can be given to allow composite keys to
583: * be used.
584: * @return string|self Primary key value if no parameter is given, or
585: * self if used as a setter.
586: */
587: public function pkey ( $_=null )
588: {
589: if ( is_string( $_ ) ) {
590: $this->_pkey = array( $_ );
591: return $this;
592: }
593: return $this->_getSet( $this->_pkey, $_ );
594: }
595:
596:
597: /**
598: * Convert a primary key array of field values to a combined value.
599: *
600: * @param string $row The row of data that the primary key value should
601: * be extracted from.
602: * @param boolean $flat Flag to indicate if the given array is flat
603: * (useful for `where` conditions) or nested for join tables.
604: * @return string The created primary key value.
605: * @throws \Exception If one of the values that the primary key is made up
606: * of cannot be found in the data set given, an Exception will be thrown.
607: */
608: public function pkeyToValue ( $row, $flat=false )
609: {
610: $pkey = $this->_pkey;
611: $id = array();
612:
613: for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
614: $column = $pkey[ $i ];
615:
616: if ( $flat ) {
617: if ( isset( $row[ $column ] ) ) {
618: if ( $row[ $column ] === null ) {
619: throw new \Exception("Primary key value is null.", 1);
620: }
621: $val = $row[ $column ];
622: }
623: else {
624: $val = null;
625: }
626: }
627: else {
628: $val = $this->_readProp( $column, $row );
629: }
630:
631: if ( $val === null ) {
632: throw new \Exception("Primary key element is not available in data set.", 1);
633: }
634:
635: $id[] = $val;
636: }
637:
638: return implode( $this->_pkey_separator(), $id );
639: }
640:
641:
642: /**
643: * Convert a primary key combined value to an array of field values.
644: *
645: * @param string $value The id that should be split apart
646: * @param boolean $flat Flag to indicate if the returned array should be
647: * flat (useful for `where` conditions) or nested for join tables.
648: * @param string[] $pkey The primary key name - will use the instance value
649: * if not given
650: * @return array Array of field values that the id was made up of.
651: * @throws \Exception If the primary key value does not match the expected
652: * length based on the primary key configuration, an exception will be
653: * thrown.
654: */
655: public function pkeyToArray ( $value, $flat=false, $pkey=null )
656: {
657: $arr = array();
658: $value = str_replace( $this->idPrefix(), '', $value );
659: $idParts = explode( $this->_pkey_separator(), $value );
660:
661: if ( $pkey === null ) {
662: $pkey = $this->_pkey;
663: }
664:
665: if ( count($pkey) !== count($idParts) ) {
666: throw new \Exception("Primary key data doesn't match submitted data", 1);
667: }
668:
669: for ( $i=0, $ien=count($idParts) ; $i<$ien ; $i++ ) {
670: if ( $flat ) {
671: $arr[ $pkey[$i] ] = $idParts[$i];
672: }
673: else {
674: $this->_writeProp( $arr, $pkey[$i], $idParts[$i] );
675: }
676: }
677:
678: return $arr;
679: }
680:
681:
682: /**
683: * Process a request from the Editor client-side to get / set data.
684: *
685: * @param array $data Typically $_POST or $_GET as required by what is sent
686: * by Editor
687: * @return self
688: */
689: public function process ( $data )
690: {
691: if ( $this->_debug ) {
692: $debugInfo = &$this->_debugInfo;
693: $debugVal = $this->_db->debug( function ( $mess ) use ( &$debugInfo ) {
694: $debugInfo[] = $mess;
695: } );
696: }
697:
698: if ( $this->_tryCatch ) {
699: try {
700: $this->_process( $data );
701: }
702: catch (\Exception $e) {
703: // Error feedback
704: $this->_out['error'] = $e->getMessage();
705:
706: if ( $this->_transaction ) {
707: $this->_db->rollback();
708: }
709: }
710: }
711: else {
712: $this->_process( $data );
713: }
714:
715: if ( $this->_debug ) {
716: $this->_out['debug'] = $this->_debugInfo;
717:
718: // Save to a log file
719: if ( $this->_debugLog ) {
720: file_put_contents( $this->_debugLog, json_encode( $this->_debugInfo )."\n", FILE_APPEND );
721: }
722:
723: $this->_db->debug( false );
724: }
725:
726: return $this;
727: }
728:
729:
730: /**
731: * The CRUD read table name. If this method is used, Editor will create from the
732: * table name(s) given rather than those given by `Editor->table()`. This can be
733: * a useful distinction to allow a read from a VIEW (which could make use of a
734: * complex SELECT) while writing to a different table.
735: *
736: * @param string|array $_,... Read table names given as a single string, an array
737: * of strings or multiple string parameters for the function.
738: * @return string[]|self Array of read tables names, or self if used as a setter.
739: */
740: public function readTable ( $_=null )
741: {
742: if ( $_ !== null && !is_array($_) ) {
743: $_ = func_get_args();
744: }
745: return $this->_getSet( $this->_readTableNames, $_, true );
746: }
747:
748:
749: /**
750: * Get / set the table name.
751: *
752: * The table name designated which DB table Editor will use as its data
753: * source for working with the database. Table names can be given with an
754: * alias, which can be used to simplify larger table names. The field
755: * names would also need to reflect the alias, just like an SQL query. For
756: * example: `users as a`.
757: *
758: * @param string|array $_,... Table names given as a single string, an array of
759: * strings or multiple string parameters for the function.
760: * @return string[]|self Array of tables names, or self if used as a setter.
761: */
762: public function table ( $_=null )
763: {
764: if ( $_ !== null && !is_array($_) ) {
765: $_ = func_get_args();
766: }
767: return $this->_getSet( $this->_table, $_, true );
768: }
769:
770:
771: /**
772: * Get / set transaction support.
773: *
774: * When enabled (which it is by default) Editor will use an SQL transaction
775: * to ensure data integrity while it is performing operations on the table.
776: * This can be optionally disabled using this method, if required by your
777: * database configuration.
778: *
779: * @param boolean $_ Enable (`true`) or disabled (`false`) transactions.
780: * If not given, then used as a getter.
781: * @return boolean|self Transactions enabled flag, or self if used as a
782: * setter.
783: */
784: public function transaction ( $_=null )
785: {
786: return $this->_getSet( $this->_transaction, $_ );
787: }
788:
789:
790: /**
791: * Enable / try catch when `process()` is called. Disabling this can be
792: * useful for debugging, but is not recommended for production.
793: *
794: * @param boolean $_ `true` to enable (default), otherwise false to disable
795: * @return boolean|Editor Value if used as a getter, otherwise `$this` when
796: * used as a setter.
797: */
798: public function tryCatch ( $_=null )
799: {
800: return $this->_getSet( $this->_tryCatch, $_ );
801: }
802:
803:
804: /**
805: * Perform validation on a data set.
806: *
807: * Note that validation is performed on data only when the action is
808: * `create` or `edit`. Additionally, validation is performed on the _wire
809: * data_ - i.e. that which is submitted from the client, without formatting.
810: * Any formatting required by `setFormatter` is performed after the data
811: * from the client has been validated.
812: *
813: * @param array $errors Output array to which field error information will
814: * be written. Each element in the array represents a field in an error
815: * condition. These elements are themselves arrays with two properties
816: * set; `name` and `status`.
817: * @param array $data The format data to check
818: * @return boolean `true` if the data is valid, `false` if not.
819: */
820: public function validate ( &$errors, $data )
821: {
822: // Validation is only performed on create and edit
823: if ( $data[$this->_actionName] != "create" && $data[$this->_actionName] != "edit" ) {
824: return true;
825: }
826:
827: foreach( $data['data'] as $id => $values ) {
828: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
829: $field = $this->_fields[$i];
830: $validation = $field->validate( $values, $this,
831: str_replace( $this->idPrefix(), '', $id )
832: );
833:
834: if ( $validation !== true ) {
835: $errors[] = array(
836: "name" => $field->name(),
837: "status" => $validation
838: );
839: }
840: }
841:
842: // MJoin validation
843: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
844: $this->_join[$i]->validate( $errors, $this, $values, $data[$this->_actionName] );
845: }
846: }
847:
848: return count( $errors ) > 0 ? false : true;
849: }
850:
851:
852: /**
853: * Get / set a global validator that will be triggered for the create, edit
854: * and remove actions performed from the client-side. Multiple validators
855: * can be added.
856: *
857: * @param callable $_ Function to execute when validating the input data.
858: * It is passed three parameters: 1. The editor instance, 2. The action
859: * and 3. The values.
860: * @return Editor|callback Editor instance if called as a setter, or the
861: * validator function if not.
862: */
863: public function validator ( $_=null )
864: {
865: return $this->_getSet( $this->_validator, $_, true );
866: }
867:
868:
869: /**
870: * Where condition to add to the query used to get data from the database.
871: *
872: * Can be used in two different ways:
873: *
874: * * Simple case: `where( field, value, operator )`
875: * * Complex: `where( fn )`
876: *
877: * The simple case is fairly self explanatory, a condition is applied to the
878: * data that looks like `field operator value` (e.g. `name = 'Allan'`). The
879: * complex case allows full control over the query conditions by providing a
880: * closure function that has access to the database Query that Editor is
881: * using, so you can use the `where()`, `or_where()`, `and_where()` and
882: * `where_group()` methods as you require.
883: *
884: * Please be very careful when using this method! If an edit made by a user
885: * using Editor removes the row from the where condition, the result is
886: * undefined (since Editor expects the row to still be available, but the
887: * condition removes it from the result set).
888: *
889: * @param string|callable $key Single field name or a closure function
890: * @param string $value Single field value.
891: * @param string $op Condition operator: <, >, = etc
892: * @return string[]|self Where condition array, or self if used as a setter.
893: */
894: public function where ( $key=null, $value=null, $op='=' )
895: {
896: if ( $key === null ) {
897: return $this->_where;
898: }
899:
900: if ( is_callable($key) && is_object($key) ) {
901: $this->_where[] = $key;
902: }
903: else {
904: $this->_where[] = array(
905: "key" => $key,
906: "value" => $value,
907: "op" => $op
908: );
909: }
910:
911: return $this;
912: }
913:
914:
915: /**
916: * Get / set if the WHERE conditions should be included in the create and
917: * edit actions.
918: *
919: * @param boolean $_ Include (`true`), or not (`false`)
920: * @return boolean Current value
921: * @deprecated Note that `whereSet` is now deprecated and replaced with the
922: * ability to set values for columns on create and edit. The C# libraries
923: * do not support this option at all.
924: */
925: public function whereSet ( $_=null )
926: {
927: return $this->_getSet( $this->_whereSet, $_ );
928: }
929:
930:
931:
932: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
933: * Private methods
934: */
935:
936: /**
937: * Process a request from the Editor client-side to get / set data.
938: *
939: * @param array $data Data to process
940: * @private
941: */
942: private function _process( $data )
943: {
944: $this->_out = array(
945: "fieldErrors" => array(),
946: "error" => "",
947: "data" => array(),
948: "ipOpts" => array(),
949: "cancelled" => array()
950: );
951:
952: $action = Editor::action($data);
953: $this->_processData = $data;
954: $this->_formData = isset($data['data']) ? $data['data'] : null;
955: $validators = $this->_validator;
956:
957: // Sanity check that data isn't getting truncated as that can lead to data corruption
958: if ( count($data, COUNT_RECURSIVE) >= ini_get('max_input_vars') ) {
959: $this->_out['error'] = 'Too many rows edited at the same time (tech info: max_input_vars exceeded).';
960: }
961:
962: if ( ! $this->_out['error'] ) {
963: if ( $this->_transaction ) {
964: $this->_db->transaction();
965: }
966:
967: $this->_prepJoin();
968:
969: if ( $validators ) {
970: for ( $i=0 ; $i<count($validators) ; $i++ ) {
971: $validator = $validators[$i];
972: $ret = $validator( $this, $action, $data );
973:
974: if ( is_string($ret) ) {
975: $this->_out['error'] = $ret;
976: break;
977: }
978: }
979: }
980: }
981:
982: if ( ! $this->_out['error'] ) {
983: if ( $action === Editor::ACTION_READ ) {
984: /* Get data */
985: $this->_out = array_merge( $this->_out, $this->_get( null, $data ) );
986: }
987: else if ( $action === Editor::ACTION_UPLOAD ) {
988: /* File upload */
989: $this->_upload( $data );
990: }
991: else if ( $action === Editor::ACTION_DELETE ) {
992: /* Remove rows */
993: $this->_remove( $data );
994: $this->_fileClean();
995: }
996: else if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
997: /* Create or edit row */
998: // Pre events so they can occur before the validation
999: foreach ($data['data'] as $idSrc => &$values) {
1000: $cancel = null;
1001:
1002: if ( $action === Editor::ACTION_CREATE ) {
1003: $cancel = $this->_trigger( 'preCreate', $values );
1004: }
1005: else {
1006: $id = str_replace( $this->_idPrefix, '', $idSrc );
1007: $cancel = $this->_trigger( 'preEdit', $id, $values );
1008: }
1009:
1010: // One of the event handlers returned false - don't continue
1011: if ( $cancel === false ) {
1012: // Remove the data from the data set so it won't be processed
1013: unset( $data['data'][$idSrc] );
1014:
1015: // Tell the client-side we aren't updating this row
1016: $this->_out['cancelled'][] = $idSrc;
1017: }
1018: }
1019:
1020: // Validation
1021: $valid = $this->validate( $this->_out['fieldErrors'], $data );
1022:
1023: if ( $valid ) {
1024: foreach ($data['data'] as $id => &$values) {
1025: $d = $action === Editor::ACTION_CREATE ?
1026: $this->_insert( $values ) :
1027: $this->_update( $id, $values );
1028:
1029: if ( $d !== null ) {
1030: $this->_out['data'][] = $d;
1031: }
1032: }
1033:
1034: $this->_fileClean();
1035: }
1036: }
1037: }
1038:
1039: if ( $this->_transaction ) {
1040: $this->_db->commit();
1041: }
1042:
1043: // Tidy up the reply
1044: if ( count( $this->_out['fieldErrors'] ) === 0 ) {
1045: unset( $this->_out['fieldErrors'] );
1046: }
1047:
1048: if ( $this->_out['error'] === '' ) {
1049: unset( $this->_out['error'] );
1050: }
1051:
1052: if ( count( $this->_out['ipOpts'] ) === 0 ) {
1053: unset( $this->_out['ipOpts'] );
1054: }
1055:
1056: if ( count( $this->_out['cancelled'] ) === 0 ) {
1057: unset( $this->_out['cancelled'] );
1058: }
1059: }
1060:
1061:
1062: /**
1063: * Get an array of objects from the database to be given to DataTables as a
1064: * result of an sAjaxSource request, such that DataTables can display the information
1065: * from the DB in the table.
1066: *
1067: * @param integer|string $id Primary key value to get an individual row
1068: * (after create or update operations). Gets the full set if not given.
1069: * If a compound key is being used, this should be the string
1070: * representation of it (i.e. joined together) rather than an array form.
1071: * @param array $http HTTP parameters from GET or POST request (so we can service
1072: * server-side processing requests from DataTables).
1073: * @return array DataTables get information
1074: * @throws \Exception Error on SQL execution
1075: * @private
1076: */
1077: private function _get( $id=null, $http=null )
1078: {
1079:
1080: $cancel = $this->_trigger( 'preGet', $id );
1081: if ( $cancel === false ) {
1082: return array();
1083: }
1084:
1085: $query = $this->_db
1086: ->query('select')
1087: ->table( $this->_read_table() )
1088: ->get( $this->_pkey );
1089:
1090: // Add all fields that we need to get from the database
1091: foreach ($this->_fields as $field) {
1092: // Don't reselect a pkey column if it was already added
1093: if ( in_array( $field->dbField(), $this->_pkey ) ) {
1094: continue;
1095: }
1096:
1097: if ( $field->apply('get') && $field->getValue() === null ) {
1098: $query->get( $field->dbField() );
1099: }
1100: }
1101:
1102: $this->_get_where( $query );
1103: $this->_perform_left_join( $query );
1104: $ssp = $this->_ssp_query( $query, $http );
1105:
1106: if ( $id !== null ) {
1107: $query->where( $this->pkeyToArray( $id, true ) );
1108: }
1109:
1110: $res = $query->exec();
1111: if ( ! $res ) {
1112: throw new \Exception('Error executing SQL for data get. Enable SQL debug using `->debug(true)`');
1113: }
1114:
1115: $out = array();
1116: while ( $row=$res->fetch() ) {
1117: $inner = array();
1118: $inner['DT_RowId'] = $this->_idPrefix . $this->pkeyToValue( $row, true );
1119:
1120: foreach ($this->_fields as $field) {
1121: if ( $field->apply('get') ) {
1122: $field->write( $inner, $row );
1123: }
1124: }
1125:
1126: $out[] = $inner;
1127: }
1128:
1129: // Field options
1130: $options = array();
1131:
1132: if ( $id === null ) {
1133: foreach ($this->_fields as $field) {
1134: $opts = $field->optionsExec( $this->_db );
1135:
1136: if ( $opts !== false ) {
1137: $options[ $field->name() ] = $opts;
1138: }
1139: }
1140: }
1141:
1142: // Row based "joins"
1143: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1144: $this->_join[$i]->data( $this, $out, $options );
1145: }
1146:
1147: $this->_trigger( 'postGet', $out, $id );
1148:
1149: return array_merge(
1150: array(
1151: 'data' => $out,
1152: 'options' => $options,
1153: 'files' => $this->_fileData( null, null, $out )
1154: ),
1155: $ssp
1156: );
1157: }
1158:
1159:
1160: /**
1161: * Insert a new row in the database
1162: * @private
1163: */
1164: private function _insert( $values )
1165: {
1166: // Only allow a composite insert if the values for the key are
1167: // submitted. This is required because there is no reliable way in MySQL
1168: // to return the newly inserted row, so we can't know any newly
1169: // generated values.
1170: $this->_pkey_validate_insert( $values );
1171:
1172: // Insert the new row
1173: $id = $this->_insert_or_update( null, $values );
1174:
1175: if ( $id === null ) {
1176: return null;
1177: }
1178:
1179: // Was the primary key altered as part of the edit, if so use the
1180: // submitted values
1181: $id = count( $this->_pkey ) > 1 ?
1182: $this->pkeyToValue( $values ) :
1183: $this->_pkey_submit_merge( $id, $values );
1184:
1185: // Join tables
1186: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1187: $this->_join[$i]->create( $this, $id, $values );
1188: }
1189:
1190: $this->_trigger( 'writeCreate', $id, $values );
1191:
1192: // Full data set for the created row
1193: $row = $this->_get( $id );
1194: $row = count( $row['data'] ) > 0 ?
1195: $row['data'][0] :
1196: null;
1197:
1198: $this->_trigger( 'postCreate', $id, $values, $row );
1199:
1200: return $row;
1201: }
1202:
1203:
1204: /**
1205: * Update a row in the database
1206: * @param string $id The DOM ID for the row that is being edited.
1207: * @return array Row's data
1208: * @private
1209: */
1210: private function _update( $id, $values )
1211: {
1212: $id = str_replace( $this->_idPrefix, '', $id );
1213:
1214: // Update or insert the rows for the parent table and the left joined
1215: // tables
1216: $this->_insert_or_update( $id, $values );
1217:
1218: // And the join tables
1219: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1220: $this->_join[$i]->update( $this, $id, $values );
1221: }
1222:
1223: // Was the primary key altered as part of the edit, if so use the
1224: // submitted values
1225: $getId = $this->_pkey_submit_merge( $id, $values );
1226:
1227: $this->_trigger( 'writeEdit', $id, $values );
1228:
1229: // Full data set for the modified row
1230: $row = $this->_get( $getId );
1231: $row = count( $row['data'] ) > 0 ?
1232: $row['data'][0] :
1233: null;
1234:
1235: $this->_trigger( 'postEdit', $id, $values, $row );
1236:
1237: return $row;
1238: }
1239:
1240:
1241: /**
1242: * Delete one or more rows from the database
1243: * @private
1244: */
1245: private function _remove( $data )
1246: {
1247: $ids = array();
1248:
1249: // Get the ids to delete from the data source
1250: foreach ($data['data'] as $idSrc => $rowData) {
1251: // Strip the ID prefix that the client-side sends back
1252: $id = str_replace( $this->_idPrefix, "", $idSrc );
1253:
1254: $res = $this->_trigger( 'preRemove', $id, $rowData );
1255:
1256: // Allow the event to be cancelled and inform the client-side
1257: if ( $res === false ) {
1258: $this->_out['cancelled'][] = $idSrc;
1259: }
1260: else {
1261: $ids[] = $id;
1262: }
1263: }
1264:
1265: if ( count( $ids ) === 0 ) {
1266: return;
1267: }
1268:
1269: // Row based joins - remove first as the host row will be removed which
1270: // is a dependency
1271: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1272: $this->_join[$i]->remove( $this, $ids );
1273: }
1274:
1275: // Remove from the left join tables
1276: if ( $this->_leftJoinRemove ) {
1277: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
1278: $join = $this->_leftJoin[$i];
1279: $table = $this->_alias( $join['table'], 'orig' );
1280:
1281: // which side of the join refers to the parent table?
1282: if ( strpos( $join['field1'], $join['table'] ) === 0 ) {
1283: $parentLink = $join['field2'];
1284: $childLink = $join['field1'];
1285: }
1286: else {
1287: $parentLink = $join['field1'];
1288: $childLink = $join['field2'];
1289: }
1290:
1291: // Only delete on the primary key, since that is what the ids refer
1292: // to - otherwise we'd be deleting random data! Note that this
1293: // won't work with compound keys since the parent link would be
1294: // over multiple fields.
1295: if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
1296: $this->_remove_table( $join['table'], $ids, array($childLink) );
1297: }
1298: }
1299: }
1300:
1301: // Remove from the primary tables
1302: for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
1303: $this->_remove_table( $this->_table[$i], $ids );
1304: }
1305:
1306: foreach ($data['data'] as $idSrc => $rowData) {
1307: $id = str_replace( $this->_idPrefix, "", $idSrc );
1308:
1309: $this->_trigger( 'postRemove', $id, $rowData );
1310: }
1311: }
1312:
1313:
1314: /**
1315: * File upload
1316: * @param array $data Upload data
1317: * @throws \Exception File upload name error
1318: * @private
1319: */
1320: private function _upload( $data )
1321: {
1322: // Search for upload field in local fields
1323: $field = $this->_find_field( $data['uploadField'], 'name' );
1324: $fieldName = '';
1325:
1326: if ( ! $field ) {
1327: // Perhaps it is in a join instance
1328: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1329: $join = $this->_join[$i];
1330: $fields = $join->fields();
1331:
1332: for ( $j=0, $jen=count($fields) ; $j<$jen ; $j++ ) {
1333: $joinField = $fields[ $j ];
1334: $name = $join->name().'[].'.$joinField->name();
1335:
1336: if ( $name === $data['uploadField'] ) {
1337: $field = $joinField;
1338: $fieldName = $name;
1339: }
1340: }
1341: }
1342: }
1343: else {
1344: $fieldName = $field->name();
1345: }
1346:
1347: if ( ! $field ) {
1348: throw new \Exception("Unknown upload field name submitted");
1349: }
1350:
1351: $res = $this->_trigger( 'preUpload', $data );
1352:
1353: // Allow the event to be cancelled and inform the client-side
1354: if ( $res === false ) {
1355: return;
1356: }
1357:
1358: $upload = $field->upload();
1359: if ( ! $upload ) {
1360: throw new \Exception("File uploaded to a field that does not have upload options configured");
1361: }
1362:
1363: $res = $upload->exec( $this );
1364:
1365: if ( $res === false ) {
1366: $this->_out['fieldErrors'][] = array(
1367: "name" => $fieldName, // field name can be just the field's
1368: "status" => $upload->error() // name or a join combination
1369: );
1370: }
1371: else {
1372: $files = $this->_fileData( $upload->table(), array($res) );
1373:
1374: $this->_out['files'] = $files;
1375: $this->_out['upload']['id'] = $res;
1376:
1377: $this->_trigger( 'postUpload', $res, $files, $data );
1378: }
1379: }
1380:
1381:
1382: /**
1383: * Get information about the files that are detailed in the database for
1384: * the fields which have an upload method defined on them.
1385: *
1386: * @param string [$limitTable=null] Limit the data gathering to a single
1387: * table only
1388: * @param number[] [$id=null] Limit to a specific set of ids
1389: * @return array File information
1390: * @private
1391: */
1392: private function _fileData ( $limitTable=null, $ids=null, $data=null )
1393: {
1394: $files = array();
1395:
1396: // The fields in this instance
1397: $this->_fileDataFields( $files, $this->_fields, $limitTable, $ids, $data );
1398:
1399: // From joined tables
1400: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1401: $joinData = null;
1402:
1403: // If we have data from the get, it is nested from the join, so we need to
1404: // un-nest it (i.e. get the array of joined data for each row)
1405: if ( $data ) {
1406: $joinData = array();
1407:
1408: for ( $j=0, $jen=count($data) ; $j<$jen ; $j++ ) {
1409: $joinData = array_merge( $joinData, $data[$j][$this->_join[$i]->name()] );
1410: }
1411: }
1412:
1413: $this->_fileDataFields( $files, $this->_join[$i]->fields(), $limitTable, $ids, $joinData );
1414: }
1415:
1416: return $files;
1417: }
1418:
1419:
1420: /**
1421: * Common file get method for any array of fields
1422: * @param array &$files File output array
1423: * @param Field[] $fields Fields to get file information about
1424: * @param string[] $limitTable Limit the data gathering to a single table
1425: * only
1426: * @private
1427: */
1428: private function _fileDataFields ( &$files, $fields, $limitTable, $ids=null, $data=null )
1429: {
1430: foreach ($fields as $field) {
1431: $upload = $field->upload();
1432:
1433: if ( $upload ) {
1434: $table = $upload->table();
1435:
1436: if ( ! $table ) {
1437: continue;
1438: }
1439:
1440: if ( $limitTable !== null && $table !== $limitTable ) {
1441: continue;
1442: }
1443:
1444: // Make a collection of the ids used in this data set to get a limited data set
1445: // in return (security and performance)
1446: if ( $ids === null ) {
1447: $ids = array();
1448: }
1449:
1450: if ( $data !== null ) {
1451: for ( $i=0, $ien=count($data); $i<$ien ; $i++ ) {
1452: $val = $field->val( 'set', $data[$i] );
1453:
1454: if ( $val ) {
1455: $ids[] = $val;
1456: }
1457: }
1458:
1459: if ( count($ids) === 0 ) {
1460: // If no data to fetch, then don't bother
1461: return;
1462: }
1463: else if ( count($ids) > 1000 ) {
1464: // Don't use `where_in` for really large data sets
1465: $ids = array();
1466: }
1467: }
1468:
1469: $fileData = $upload->data( $this->_db, $ids );
1470:
1471: if ( $fileData !== null ) {
1472: if ( isset($files[$table]) ) {
1473: $files[$table] = $files[$table] + $fileData;
1474: }
1475: else {
1476: $files[$table] = $fileData;
1477: }
1478: }
1479: }
1480: }
1481: }
1482:
1483: /**
1484: * Run the file clean up
1485: *
1486: * @private
1487: */
1488: private function _fileClean ()
1489: {
1490: foreach ( $this->_fields as $field ) {
1491: $upload = $field->upload();
1492:
1493: if ( $upload ) {
1494: $upload->dbCleanExec( $this, $field );
1495: }
1496: }
1497:
1498: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1499: foreach ( $this->_join[$i]->fields() as $field ) {
1500: $upload = $field->upload();
1501:
1502: if ( $upload ) {
1503: $upload->dbCleanExec( $this, $field );
1504: }
1505: }
1506: }
1507: }
1508:
1509:
1510: /* * * * * * * * * * * * * * * * * * * * * * * * *
1511: * Server-side processing methods
1512: */
1513:
1514: /**
1515: * When server-side processing is being used, modify the query with // the
1516: * required extra conditions
1517: *
1518: * @param \DataTables\Database\Query $query Query instance to apply the SSP commands to
1519: * @param array $http Parameters from HTTP request
1520: * @return array Server-side processing information array
1521: * @private
1522: */
1523: private function _ssp_query ( $query, $http )
1524: {
1525: if ( ! isset( $http['draw'] ) ) {
1526: return array();
1527: }
1528:
1529: // Add the server-side processing conditions
1530: $this->_ssp_limit( $query, $http );
1531: $this->_ssp_sort( $query, $http );
1532: $this->_ssp_filter( $query, $http );
1533:
1534: // Get the number of rows in the result set
1535: $ssp_set_count = $this->_db
1536: ->query('count')
1537: ->table( $this->_read_table() )
1538: ->get( $this->_pkey[0] );
1539: $this->_get_where( $ssp_set_count );
1540: $this->_ssp_filter( $ssp_set_count, $http );
1541: $this->_perform_left_join( $ssp_set_count );
1542: $ssp_set_count = $ssp_set_count->exec()->fetch();
1543:
1544: // Get the number of rows in the full set
1545: $ssp_full_count = $this->_db
1546: ->query('count')
1547: ->table( $this->_read_table() )
1548: ->get( $this->_pkey[0] );
1549: $this->_get_where( $ssp_full_count );
1550: if ( count( $this->_where ) ) { // only needed if there is a where condition
1551: $this->_perform_left_join( $ssp_full_count );
1552: }
1553: $ssp_full_count = $ssp_full_count->exec()->fetch();
1554:
1555: return array(
1556: "draw" => intval( $http['draw'] ),
1557: "recordsTotal" => $ssp_full_count['cnt'],
1558: "recordsFiltered" => $ssp_set_count['cnt']
1559: );
1560: }
1561:
1562:
1563: /**
1564: * Convert a column index to a database field name - used for server-side
1565: * processing requests.
1566: * @param array $http HTTP variables (i.e. GET or POST)
1567: * @param int $index Index in the DataTables' submitted data
1568: * @returns string DB field name
1569: * @throws \Exception Unknown fields
1570: * @private Note that it is actually public for PHP 5.3 - thread 39810
1571: */
1572: public function _ssp_field( $http, $index )
1573: {
1574: $name = $http['columns'][$index]['data'];
1575: $field = $this->_find_field( $name, 'name' );
1576:
1577: if ( ! $field ) {
1578: // Is it the primary key?
1579: if ( $name === 'DT_RowId' ) {
1580: return $this->_pkey[0];
1581: }
1582:
1583: throw new \Exception('Unknown field: '.$name .' (index '.$index.')');
1584: }
1585:
1586: return $field->dbField();
1587: }
1588:
1589:
1590: /**
1591: * Sorting requirements to a server-side processing query.
1592: * @param \DataTables\Database\Query $query Query instance to apply sorting to
1593: * @param array $http HTTP variables (i.e. GET or POST)
1594: * @private
1595: */
1596: private function _ssp_sort ( $query, $http )
1597: {
1598: if ( isset( $http['order'] ) ) {
1599: for ( $i=0 ; $i<count($http['order']) ; $i++ ) {
1600: $order = $http['order'][$i];
1601:
1602: $query->order(
1603: $this->_ssp_field( $http, $order['column'] ) .' '.
1604: ($order['dir']==='asc' ? 'asc' : 'desc')
1605: );
1606: }
1607: }
1608: }
1609:
1610:
1611: /**
1612: * Add DataTables' 'where' condition to a server-side processing query. This
1613: * works for both global and individual column filtering.
1614: * @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
1615: * @param array $http HTTP variables (i.e. GET or POST)
1616: * @private
1617: */
1618: private function _ssp_filter ( $query, $http )
1619: {
1620: $that = $this;
1621:
1622: // Global filter
1623: $fields = $this->_fields;
1624:
1625: // Global search, add a ( ... or ... ) set of filters for each column
1626: // in the table (not the fields, just the columns submitted)
1627: if ( $http['search']['value'] ) {
1628: $query->where( function ($q) use (&$that, &$fields, $http) {
1629: for ( $i=0 ; $i<count($http['columns']) ; $i++ ) {
1630: if ( $http['columns'][$i]['searchable'] == 'true' ) {
1631: $field = $that->_ssp_field( $http, $i );
1632:
1633: if ( $field ) {
1634: $q->or_where( $field, '%'.$http['search']['value'].'%', 'like' );
1635: }
1636: }
1637: }
1638: } );
1639: }
1640:
1641: // if ( $http['search']['value'] ) {
1642: // $words = explode(" ", $http['search']['value']);
1643:
1644: // $query->where( function ($q) use (&$that, &$fields, $http, $words) {
1645: // for ( $j=0, $jen=count($words) ; $j<$jen ; $j++ ) {
1646: // if ( $words[$j] ) {
1647: // $q->where_group( true );
1648:
1649: // for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
1650: // if ( $http['columns'][$i]['searchable'] == 'true' ) {
1651: // $field = $that->_ssp_field( $http, $i );
1652:
1653: // $q->or_where( $field, $words[$j].'%', 'like' );
1654: // $q->or_where( $field, '% '.$words[$j].'%', 'like' );
1655: // }
1656: // }
1657:
1658: // $q->where_group( false );
1659: // }
1660: // }
1661: // } );
1662: // }
1663:
1664: // Column filters
1665: for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
1666: $column = $http['columns'][$i];
1667: $search = $column['search']['value'];
1668:
1669: if ( $search !== '' && $column['searchable'] == 'true' ) {
1670: $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
1671: }
1672: }
1673: }
1674:
1675:
1676: /**
1677: * Add a limit / offset to a server-side processing query
1678: * @param \DataTables\Database\Query $query Query instance to apply the offset / limit to
1679: * @param array $http HTTP variables (i.e. GET or POST)
1680: * @private
1681: */
1682: private function _ssp_limit ( $query, $http )
1683: {
1684: if ( $http['length'] != -1 ) { // -1 is 'show all' in DataTables
1685: $query
1686: ->offset( $http['start'] )
1687: ->limit( $http['length'] );
1688: }
1689: }
1690:
1691:
1692: /* * * * * * * * * * * * * * * * * * * * * * * * *
1693: * Internal helper methods
1694: */
1695:
1696: /**
1697: * Add left join commands for the instance to a query.
1698: *
1699: * @param \DataTables\Database\Query $query Query instance to apply the joins to
1700: * @private
1701: */
1702: private function _perform_left_join ( $query )
1703: {
1704: if ( count($this->_leftJoin) ) {
1705: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
1706: $join = $this->_leftJoin[$i];
1707:
1708: $query->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' );
1709: }
1710: }
1711: }
1712:
1713:
1714: /**
1715: * Add local WHERE condition to query
1716: * @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
1717: * @private
1718: */
1719: private function _get_where ( $query )
1720: {
1721: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
1722: if ( is_callable( $this->_where[$i] ) ) {
1723: $this->_where[$i]( $query );
1724: }
1725: else {
1726: $query->where(
1727: $this->_where[$i]['key'],
1728: $this->_where[$i]['value'],
1729: $this->_where[$i]['op']
1730: );
1731: }
1732: }
1733: }
1734:
1735:
1736: /**
1737: * Get a field instance from a known field name
1738: *
1739: * @param string $name Field name
1740: * @param string $type Matching name type
1741: * @return Field Field instance
1742: * @private
1743: */
1744: private function _find_field ( $name, $type )
1745: {
1746: for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
1747: $field = $this->_fields[ $i ];
1748:
1749: if ( $type === 'name' && $field->name() === $name ) {
1750: return $field;
1751: }
1752: else if ( $type === 'db' && $field->dbField() === $name ) {
1753: return $field;
1754: }
1755: }
1756:
1757: return null;
1758: }
1759:
1760:
1761: /**
1762: * Insert or update a row for all main tables and left joined tables.
1763: *
1764: * @param int|string $id ID to use to condition the update. If null is
1765: * given, the first query performed is an insert and the inserted id
1766: * used as the value should there be any subsequent tables to operate
1767: * on. Mote that for compound keys, this should be the "joined" value
1768: * (i.e. a single string rather than an array).
1769: * @return \DataTables\Database\Result Result from the query or null if no
1770: * query performed.
1771: * @private
1772: */
1773: private function _insert_or_update ( $id, $values )
1774: {
1775: // Loop over all tables in _table, doing the insert or update as needed
1776: for ( $i=0, $ien=count( $this->_table ) ; $i<$ien ; $i++ ) {
1777: $res = $this->_insert_or_update_table(
1778: $this->_table[$i],
1779: $values,
1780: $id !== null ?
1781: $this->pkeyToArray( $id, true ) :
1782: null
1783: );
1784:
1785: // If we don't have an id yet, then the first insert will return
1786: // the id we want
1787: if ( $res !== null && $id === null ) {
1788: $id = $res->insertId();
1789: }
1790: }
1791:
1792: // And for the left join tables as well
1793: for ( $i=0, $ien=count( $this->_leftJoin ) ; $i<$ien ; $i++ ) {
1794: $join = $this->_leftJoin[$i];
1795:
1796: // which side of the join refers to the parent table?
1797: $joinTable = $this->_alias( $join['table'], 'alias' );
1798: $tablePart = $this->_part( $join['field1'] );
1799:
1800: if ( $this->_part( $join['field1'], 'db' ) ) {
1801: $tablePart = $this->_part( $join['field1'], 'db' ).'.'.$tablePart;
1802: }
1803:
1804: if ( $tablePart === $joinTable ) {
1805: $parentLink = $join['field2'];
1806: $childLink = $join['field1'];
1807: }
1808: else {
1809: $parentLink = $join['field1'];
1810: $childLink = $join['field2'];
1811: }
1812:
1813: if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
1814: $whereVal = $id;
1815: }
1816: else {
1817: // We need submitted information about the joined data to be
1818: // submitted as well as the new value. We first check if the
1819: // host field was submitted
1820: $field = $this->_find_field( $parentLink, 'db' );
1821:
1822: if ( ! $field || ! $field->apply( 'set', $values ) ) {
1823: // If not, then check if the child id was submitted
1824: $field = $this->_find_field( $childLink, 'db' );
1825:
1826: // No data available, so we can't do anything
1827: if ( ! $field || ! $field->apply( 'set', $values ) ) {
1828: continue;
1829: }
1830: }
1831:
1832: $whereVal = $field->val('set', $values);
1833: }
1834:
1835: $whereName = $this->_part( $childLink, 'field' );
1836:
1837: $this->_insert_or_update_table(
1838: $join['table'],
1839: $values,
1840: array( $whereName => $whereVal )
1841: );
1842: }
1843:
1844: return $id;
1845: }
1846:
1847:
1848: /**
1849: * Insert or update a row in a single database table, based on the data
1850: * given and the fields configured for the instance.
1851: *
1852: * The function will find the fields which are required for this specific
1853: * table, based on the names of fields and use only the appropriate data for
1854: * this table. Therefore the full submitted data set can be passed in.
1855: *
1856: * @param string $table Database table name to use (can include an alias)
1857: * @param array $where Update condition
1858: * @return \DataTables\Database\Result Result from the query or null if no query
1859: * performed.
1860: * @throws \Exception Where set error
1861: * @private
1862: */
1863: private function _insert_or_update_table ( $table, $values, $where=null )
1864: {
1865: $set = array();
1866: $action = ($where === null) ? 'create' : 'edit';
1867: $tableAlias = $this->_alias( $table, 'alias' );
1868:
1869: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
1870: $field = $this->_fields[$i];
1871: $tablePart = $this->_part( $field->dbField() );
1872:
1873: if ( $this->_part( $field->dbField(), 'db' ) ) {
1874: $tablePart = $this->_part( $field->dbField(), 'db' ).'.'.$tablePart;
1875: }
1876:
1877: // Does this field apply to this table (only check when a join is
1878: // being used)
1879: if ( count($this->_leftJoin) && $tablePart !== $tableAlias ) {
1880: continue;
1881: }
1882:
1883: // Check if this field should be set, based on options and
1884: // submitted data
1885: if ( ! $field->apply( $action, $values ) ) {
1886: continue;
1887: }
1888:
1889: // Some db's (specifically postgres) don't like having the table
1890: // name prefixing the column name. Todo: it might be nicer to have
1891: // the db layer abstract this out?
1892: $fieldPart = $this->_part( $field->dbField(), 'field' );
1893: $set[ $fieldPart ] = $field->val( 'set', $values );
1894: }
1895:
1896: // Add where fields if setting where values and required for this
1897: // table
1898: // Note that `whereSet` is now deprecated
1899: if ( $this->_whereSet ) {
1900: for ( $j=0, $jen=count($this->_where) ; $j<$jen ; $j++ ) {
1901: $cond = $this->_where[$j];
1902:
1903: if ( ! is_callable( $cond ) ) {
1904: // Make sure the value wasn't in the submitted data set,
1905: // otherwise we would be overwriting it
1906: if ( ! isset( $set[ $cond['key'] ] ) )
1907: {
1908: $whereTablePart = $this->_part( $cond['key'], 'table' );
1909:
1910: // No table part on the where condition to match against
1911: // or table operating on matches table part from cond.
1912: if ( ! $whereTablePart || $tableAlias == $whereTablePart ) {
1913: $set[ $cond['key'] ] = $cond['value'];
1914: }
1915: }
1916: else {
1917: throw new \Exception( 'Where condition used as a setter, '.
1918: 'but value submitted for field: '.$cond['key']
1919: );
1920: }
1921: }
1922: }
1923: }
1924:
1925: // If nothing to do, then do nothing!
1926: if ( ! count( $set ) ) {
1927: return null;
1928: }
1929:
1930: // Use pkey only for the host table
1931: $pkey = in_array( $table, $this->_table ) !== false ?
1932: $this->_pkey :
1933: '';
1934:
1935: // Insert or update
1936: if ( $action === 'create' ) {
1937: return $this->_db->insert( $table, $set, $pkey );
1938: }
1939: else {
1940: return $this->_db->push( $table, $set, $where, $pkey );
1941: }
1942: }
1943:
1944:
1945: /**
1946: * Delete one or more rows from the database for an individual table
1947: *
1948: * @param string $table Database table name to use
1949: * @param array $ids Array of ids to remove
1950: * @param string $pkey Database column name to match the ids on for the
1951: * delete condition. If not given the instance's base primary key is
1952: * used.
1953: * @private
1954: */
1955: private function _remove_table ( $table, $ids, $pkey=null )
1956: {
1957: if ( $pkey === null ) {
1958: $pkey = $this->_pkey;
1959: }
1960:
1961: $tableMatch = $this->_alias($table, 'alias');
1962:
1963: // Check there is a field which has a set option for this table
1964: $count = 0;
1965:
1966: foreach ($this->_fields as $field) {
1967: $fieldName = $field->dbField();
1968: $fieldDots = substr_count( $fieldName, '.' );
1969:
1970: if ( $fieldDots === 0 ) {
1971: $count++;
1972: }
1973: else if ( $fieldDots === 1 ) {
1974: if (
1975: $field->set() !== Field::SET_NONE &&
1976: $this->_part( $fieldName, 'table' ) === $tableMatch
1977: ) {
1978: $count++;
1979: }
1980: }
1981: else {
1982: // db link
1983: // note that if the table name for the constructor uses a db part, we need to also have
1984: // the fields using the db name as Editor doesn't do any conflict resolution.
1985: $dbTable = $this->_part( $fieldName, 'db' ) .'.'. $this->_part( $fieldName, 'table' );
1986:
1987: if ( $field->set() !== Field::SET_NONE && $dbTable === $table ) {
1988: $count++;
1989: }
1990: }
1991: }
1992:
1993: if ( $count > 0 ) {
1994: $q = $this->_db
1995: ->query( 'delete' )
1996: ->table( $table );
1997:
1998: for ( $i=0, $ien=count($ids) ; $i<$ien ; $i++ ) {
1999: $cond = $this->pkeyToArray( $ids[$i], true, $pkey );
2000:
2001: $q->or_where( function ($q2) use ($cond) {
2002: $q2->where( $cond );
2003: } );
2004: }
2005:
2006: $q->exec();
2007: }
2008: }
2009:
2010:
2011: /**
2012: * Check the validity of the set options if we are doing a join, since
2013: * there are some conditions for this state. Will throw an error if not
2014: * valid.
2015: *
2016: * @private
2017: */
2018: private function _prepJoin ()
2019: {
2020: if ( count( $this->_leftJoin ) === 0 ) {
2021: return;
2022: }
2023:
2024: // Check if the primary key has a table identifier - if not - add one
2025: for ( $i=0, $ien=count($this->_pkey) ; $i<$ien ; $i++ ) {
2026: $val = $this->_pkey[$i];
2027:
2028: if ( strpos( $val, '.' ) === false ) {
2029: $this->_pkey[$i] = $this->_alias( $this->_table[0], 'alias' ).'.'.$val;
2030: }
2031: }
2032:
2033: // Check that all fields have a table selector, otherwise, we'd need to
2034: // know the structure of the tables, to know which fields belong in
2035: // which. This extra requirement on the fields removes that
2036: for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
2037: $field = $this->_fields[$i];
2038: $name = $field->dbField();
2039:
2040: if ( strpos( $name, '.' ) === false ) {
2041: throw new \Exception( 'Table part of the field "'.$name.'" was not found. '.
2042: 'In Editor instances that use a join, all fields must have the '.
2043: 'database table set explicitly.'
2044: );
2045: }
2046: }
2047: }
2048:
2049:
2050: /**
2051: * Get one side or the other of an aliased SQL field name.
2052: *
2053: * @param string $name SQL field
2054: * @param string $type Which part to get: `alias` (default) or `orig`.
2055: * @returns string Alias
2056: * @private
2057: */
2058: private function _alias ( $name, $type='alias' )
2059: {
2060: if ( stripos( $name, ' as ' ) !== false ) {
2061: $a = preg_split( '/ as /i', $name );
2062: return $type === 'alias' ?
2063: $a[1] :
2064: $a[0];
2065: }
2066:
2067: if ( stripos( $name, ' ' ) !== false ) {
2068: $a = preg_split( '/ /i', $name );
2069: return $type === 'alias' ?
2070: $a[1] :
2071: $a[0];
2072: }
2073:
2074: return $name;
2075: }
2076:
2077:
2078: /**
2079: * Get part of an SQL field definition regardless of how deeply defined it
2080: * is
2081: *
2082: * @param string $name SQL field
2083: * @param string $type Which part to get: `table` (default) or `db` or
2084: * `column`
2085: * @return string Part name
2086: * @private
2087: */
2088: private function _part ( $name, $type='table' )
2089: {
2090: $db = null;
2091: $table = null;
2092: $column = null;
2093:
2094: if ( strpos( $name, '.' ) !== false ) {
2095: $a = explode( '.', $name );
2096:
2097: if ( count($a) === 3 ) {
2098: $db = $a[0];
2099: $table = $a[1];
2100: $column = $a[2];
2101: }
2102: else if ( count($a) === 2 ) {
2103: $table = $a[0];
2104: $column = $a[1];
2105: }
2106: }
2107: else {
2108: $column = $name;
2109: }
2110:
2111: if ( $type === 'db' ) {
2112: return $db;
2113: }
2114: else if ( $type === 'table' ) {
2115: return $table;
2116: }
2117: return $column;
2118: }
2119:
2120:
2121: /**
2122: * Trigger an event
2123: *
2124: * @private
2125: */
2126: private function _trigger ( $eventName, &$arg1=null, &$arg2=null, &$arg3=null, &$arg4=null, &$arg5=null )
2127: {
2128: $out = null;
2129: $argc = func_num_args();
2130: $args = array( $this );
2131:
2132: // Hack to enable pass by reference with a "variable" number of parameters
2133: for ( $i=1 ; $i<$argc ; $i++ ) {
2134: $name = 'arg'.$i;
2135: $args[] = &$$name;
2136: }
2137:
2138: if ( ! isset( $this->_events[ $eventName ] ) ) {
2139: return;
2140: }
2141:
2142: $events = $this->_events[ $eventName ];
2143:
2144: for ( $i=0, $ien=count($events) ; $i<$ien ; $i++ ) {
2145: $res = call_user_func_array( $events[$i], $args );
2146:
2147: if ( $res !== null ) {
2148: $out = $res;
2149: }
2150: }
2151:
2152: return $out;
2153: }
2154:
2155:
2156: /**
2157: * Merge a primary key value with an updated data source.
2158: *
2159: * @param string $pkeyVal Old primary key value to merge into
2160: * @param array $row Data source for update
2161: * @return string Merged value
2162: */
2163: private function _pkey_submit_merge ( $pkeyVal, $row )
2164: {
2165: $pkey = $this->_pkey;
2166: $arr = $this->pkeyToArray( $pkeyVal, true );
2167:
2168: for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
2169: $column = $pkey[ $i ];
2170: $field = $this->_find_field( $column, 'db' );
2171:
2172: if ( $field && $field->apply( 'edit', $row ) ) {
2173: $arr[ $column ] = $field->val( 'set', $row );
2174: }
2175: }
2176:
2177: return $this->pkeyToValue( $arr, true );
2178: }
2179:
2180:
2181: /**
2182: * Validate that all primary key fields have values for create.
2183: *
2184: * @param array $row Row's data
2185: * @return boolean `true` if valid, `false` otherwise
2186: */
2187: private function _pkey_validate_insert ( $row )
2188: {
2189: $pkey = $this->_pkey;
2190:
2191: if ( count( $pkey ) === 1 ) {
2192: return true;
2193: }
2194:
2195: for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
2196: $column = $pkey[ $i ];
2197: $field = $this->_find_field( $column, 'db' );
2198:
2199: if ( ! $field || ! $field->apply("create", $row) ) {
2200: throw new \Exception( "When inserting into a compound key table, ".
2201: "all fields that are part of the compound key must be ".
2202: "submitted with a specific value.", 1
2203: );
2204: }
2205: }
2206:
2207: return true;
2208: }
2209:
2210:
2211: /**
2212: * Create the separator value for a compound primary key.
2213: *
2214: * @return string Calculated separator
2215: */
2216: private function _pkey_separator ()
2217: {
2218: $str = implode(',', $this->_pkey);
2219:
2220: return hash( 'crc32', $str );
2221: }
2222:
2223: private function _read_table ()
2224: {
2225: return count($this->_readTableNames) ?
2226: $this->_readTableNames :
2227: $this->_table;
2228: }
2229: }
2230:
2231: