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