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