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.8.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 = null;
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 );
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.
838: *
839: * @param callable $_ Function to execute when validating the input data.
840: * It is passed three parameters: 1. The editor instance, 2. The action
841: * and 3. The values.
842: * @return Editor|callback Editor instance if called as a setter, or the
843: * validator function if not.
844: */
845: public function validator ( $_=null )
846: {
847: return $this->_getSet( $this->_validator, $_ );
848: }
849:
850:
851: /**
852: * Where condition to add to the query used to get data from the database.
853: *
854: * Can be used in two different ways:
855: *
856: * * Simple case: `where( field, value, operator )`
857: * * Complex: `where( fn )`
858: *
859: * The simple case is fairly self explanatory, a condition is applied to the
860: * data that looks like `field operator value` (e.g. `name = 'Allan'`). The
861: * complex case allows full control over the query conditions by providing a
862: * closure function that has access to the database Query that Editor is
863: * using, so you can use the `where()`, `or_where()`, `and_where()` and
864: * `where_group()` methods as you require.
865: *
866: * Please be very careful when using this method! If an edit made by a user
867: * using Editor removes the row from the where condition, the result is
868: * undefined (since Editor expects the row to still be available, but the
869: * condition removes it from the result set).
870: *
871: * @param string|callable $key Single field name or a closure function
872: * @param string $value Single field value.
873: * @param string $op Condition operator: <, >, = etc
874: * @return string[]|self Where condition array, or self if used as a setter.
875: */
876: public function where ( $key=null, $value=null, $op='=' )
877: {
878: if ( $key === null ) {
879: return $this->_where;
880: }
881:
882: if ( is_callable($key) && is_object($key) ) {
883: $this->_where[] = $key;
884: }
885: else {
886: $this->_where[] = array(
887: "key" => $key,
888: "value" => $value,
889: "op" => $op
890: );
891: }
892:
893: return $this;
894: }
895:
896:
897: /**
898: * Get / set if the WHERE conditions should be included in the create and
899: * edit actions.
900: *
901: * @param boolean $_ Include (`true`), or not (`false`)
902: * @return boolean Current value
903: * @deprecated Note that `whereSet` is now deprecated and replaced with the
904: * ability to set values for columns on create and edit. The C# libraries
905: * do not support this option at all.
906: */
907: public function whereSet ( $_=null )
908: {
909: return $this->_getSet( $this->_whereSet, $_ );
910: }
911:
912:
913:
914: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
915: * Private methods
916: */
917:
918: /**
919: * Process a request from the Editor client-side to get / set data.
920: *
921: * @param array $data Data to process
922: * @private
923: */
924: private function _process( $data )
925: {
926: $this->_out = array(
927: "fieldErrors" => array(),
928: "error" => "",
929: "data" => array(),
930: "ipOpts" => array(),
931: "cancelled" => array()
932: );
933:
934: $this->_processData = $data;
935: $this->_formData = isset($data['data']) ? $data['data'] : null;
936: $validator = $this->_validator;
937:
938: if ( $this->_transaction ) {
939: $this->_db->transaction();
940: }
941:
942: $this->_prepJoin();
943:
944: if ( $validator ) {
945: $ret = $validator( $this, !isset($data['action']) ? self::ACTION_READ : $data['action'], $data );
946:
947: if ( $ret ) {
948: $this->_out['error'] = $ret;
949: }
950: }
951:
952: if ( ! $this->_out['error'] ) {
953: if ( ! isset($data['action']) ) {
954: /* Get data */
955: $this->_out = array_merge( $this->_out, $this->_get( null, $data ) );
956: }
957: else if ( $data['action'] == "upload" ) {
958: /* File upload */
959: $this->_upload( $data );
960: }
961: else if ( $data['action'] == "remove" ) {
962: /* Remove rows */
963: $this->_remove( $data );
964: $this->_fileClean();
965: }
966: else {
967: /* Create or edit row */
968: // Pre events so they can occur before the validation
969: foreach ($data['data'] as $idSrc => &$values) {
970: $cancel = null;
971:
972: if ( $data['action'] == 'create' ) {
973: $cancel = $this->_trigger( 'preCreate', $values );
974: }
975: else {
976: $id = str_replace( $this->_idPrefix, '', $idSrc );
977: $cancel = $this->_trigger( 'preEdit', $id, $values );
978: }
979:
980: // One of the event handlers returned false - don't continue
981: if ( $cancel === false ) {
982: // Remove the data from the data set so it won't be processed
983: unset( $data['data'][$idSrc] );
984:
985: // Tell the client-side we aren't updating this row
986: $this->_out['cancelled'][] = $idSrc;
987: }
988: }
989:
990: // Validation
991: $valid = $this->validate( $this->_out['fieldErrors'], $data );
992:
993: if ( $valid ) {
994: foreach ($data['data'] as $id => &$values) {
995: $d = $data['action'] == "create" ?
996: $this->_insert( $values ) :
997: $this->_update( $id, $values );
998:
999: if ( $d !== null ) {
1000: $this->_out['data'][] = $d;
1001: }
1002: }
1003: }
1004:
1005: $this->_fileClean();
1006: }
1007: }
1008:
1009: if ( $this->_transaction ) {
1010: $this->_db->commit();
1011: }
1012:
1013: // Tidy up the reply
1014: if ( count( $this->_out['fieldErrors'] ) === 0 ) {
1015: unset( $this->_out['fieldErrors'] );
1016: }
1017:
1018: if ( $this->_out['error'] === '' ) {
1019: unset( $this->_out['error'] );
1020: }
1021:
1022: if ( count( $this->_out['ipOpts'] ) === 0 ) {
1023: unset( $this->_out['ipOpts'] );
1024: }
1025:
1026: if ( count( $this->_out['cancelled'] ) === 0 ) {
1027: unset( $this->_out['cancelled'] );
1028: }
1029: }
1030:
1031:
1032: /**
1033: * Get an array of objects from the database to be given to DataTables as a
1034: * result of an sAjaxSource request, such that DataTables can display the information
1035: * from the DB in the table.
1036: *
1037: * @param integer|string $id Primary key value to get an individual row
1038: * (after create or update operations). Gets the full set if not given.
1039: * If a compound key is being used, this should be the string
1040: * representation of it (i.e. joined together) rather than an array form.
1041: * @param array $http HTTP parameters from GET or POST request (so we can service
1042: * server-side processing requests from DataTables).
1043: * @return array DataTables get information
1044: * @throws \Exception Error on SQL execution
1045: * @private
1046: */
1047: private function _get( $id=null, $http=null )
1048: {
1049:
1050: $cancel = $this->_trigger( 'preGet', $id );
1051: if ( $cancel === false ) {
1052: return array();
1053: }
1054:
1055: $query = $this->_db
1056: ->query('select')
1057: ->table( $this->_read_table() )
1058: ->get( $this->_pkey );
1059:
1060: // Add all fields that we need to get from the database
1061: foreach ($this->_fields as $field) {
1062: // Don't reselect a pkey column if it was already added
1063: if ( in_array( $field->dbField(), $this->_pkey ) ) {
1064: continue;
1065: }
1066:
1067: if ( $field->apply('get') && $field->getValue() === null ) {
1068: $query->get( $field->dbField() );
1069: }
1070: }
1071:
1072: $this->_get_where( $query );
1073: $this->_perform_left_join( $query );
1074: $ssp = $this->_ssp_query( $query, $http );
1075:
1076: if ( $id !== null ) {
1077: $query->where( $this->pkeyToArray( $id, true ) );
1078: }
1079:
1080: $res = $query->exec();
1081: if ( ! $res ) {
1082: throw new \Exception('Error executing SQL for data get. Enable SQL debug using `->debug(true)`');
1083: }
1084:
1085: $out = array();
1086: while ( $row=$res->fetch() ) {
1087: $inner = array();
1088: $inner['DT_RowId'] = $this->_idPrefix . $this->pkeyToValue( $row, true );
1089:
1090: foreach ($this->_fields as $field) {
1091: if ( $field->apply('get') ) {
1092: $field->write( $inner, $row );
1093: }
1094: }
1095:
1096: $out[] = $inner;
1097: }
1098:
1099: // Field options
1100: $options = array();
1101:
1102: if ( $id === null ) {
1103: foreach ($this->_fields as $field) {
1104: $opts = $field->optionsExec( $this->_db );
1105:
1106: if ( $opts !== false ) {
1107: $options[ $field->name() ] = $opts;
1108: }
1109: }
1110: }
1111:
1112: // Row based "joins"
1113: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1114: $this->_join[$i]->data( $this, $out, $options );
1115: }
1116:
1117: $this->_trigger( 'postGet', $out, $id );
1118:
1119: return array_merge(
1120: array(
1121: 'data' => $out,
1122: 'options' => $options,
1123: 'files' => $this->_fileData( null, null, $out )
1124: ),
1125: $ssp
1126: );
1127: }
1128:
1129:
1130: /**
1131: * Insert a new row in the database
1132: * @private
1133: */
1134: private function _insert( $values )
1135: {
1136: // Only allow a composite insert if the values for the key are
1137: // submitted. This is required because there is no reliable way in MySQL
1138: // to return the newly inserted row, so we can't know any newly
1139: // generated values.
1140: $this->_pkey_validate_insert( $values );
1141:
1142: // Insert the new row
1143: $id = $this->_insert_or_update( null, $values );
1144:
1145: if ( $id === null ) {
1146: return null;
1147: }
1148:
1149: // Was the primary key altered as part of the edit, if so use the
1150: // submitted values
1151: $id = count( $this->_pkey ) > 1 ?
1152: $this->pkeyToValue( $values ) :
1153: $this->_pkey_submit_merge( $id, $values );
1154:
1155: // Join tables
1156: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1157: $this->_join[$i]->create( $this, $id, $values );
1158: }
1159:
1160: $this->_trigger( 'writeCreate', $id, $values );
1161:
1162: // Full data set for the created row
1163: $row = $this->_get( $id );
1164: $row = count( $row['data'] ) > 0 ?
1165: $row['data'][0] :
1166: null;
1167:
1168: $this->_trigger( 'postCreate', $id, $values, $row );
1169:
1170: return $row;
1171: }
1172:
1173:
1174: /**
1175: * Update a row in the database
1176: * @param string $id The DOM ID for the row that is being edited.
1177: * @return array Row's data
1178: * @private
1179: */
1180: private function _update( $id, $values )
1181: {
1182: $id = str_replace( $this->_idPrefix, '', $id );
1183:
1184: // Update or insert the rows for the parent table and the left joined
1185: // tables
1186: $this->_insert_or_update( $id, $values );
1187:
1188: // And the join tables
1189: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1190: $this->_join[$i]->update( $this, $id, $values );
1191: }
1192:
1193: // Was the primary key altered as part of the edit, if so use the
1194: // submitted values
1195: $getId = $this->_pkey_submit_merge( $id, $values );
1196:
1197: $this->_trigger( 'writeEdit', $id, $values );
1198:
1199: // Full data set for the modified row
1200: $row = $this->_get( $getId );
1201: $row = count( $row['data'] ) > 0 ?
1202: $row['data'][0] :
1203: null;
1204:
1205: $this->_trigger( 'postEdit', $id, $values, $row );
1206:
1207: return $row;
1208: }
1209:
1210:
1211: /**
1212: * Delete one or more rows from the database
1213: * @private
1214: */
1215: private function _remove( $data )
1216: {
1217: $ids = array();
1218:
1219: // Get the ids to delete from the data source
1220: foreach ($data['data'] as $idSrc => $rowData) {
1221: // Strip the ID prefix that the client-side sends back
1222: $id = str_replace( $this->_idPrefix, "", $idSrc );
1223:
1224: $res = $this->_trigger( 'preRemove', $id, $rowData );
1225:
1226: // Allow the event to be cancelled and inform the client-side
1227: if ( $res === false ) {
1228: $this->_out['cancelled'][] = $idSrc;
1229: }
1230: else {
1231: $ids[] = $id;
1232: }
1233: }
1234:
1235: if ( count( $ids ) === 0 ) {
1236: return;
1237: }
1238:
1239: // Row based joins - remove first as the host row will be removed which
1240: // is a dependency
1241: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1242: $this->_join[$i]->remove( $this, $ids );
1243: }
1244:
1245: // Remove from the left join tables
1246: if ( $this->_leftJoinRemove ) {
1247: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
1248: $join = $this->_leftJoin[$i];
1249: $table = $this->_alias( $join['table'], 'orig' );
1250:
1251: // which side of the join refers to the parent table?
1252: if ( strpos( $join['field1'], $join['table'] ) === 0 ) {
1253: $parentLink = $join['field2'];
1254: $childLink = $join['field1'];
1255: }
1256: else {
1257: $parentLink = $join['field1'];
1258: $childLink = $join['field2'];
1259: }
1260:
1261: // Only delete on the primary key, since that is what the ids refer
1262: // to - otherwise we'd be deleting random data! Note that this
1263: // won't work with compound keys since the parent link would be
1264: // over multiple fields.
1265: if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
1266: $this->_remove_table( $join['table'], $ids, array($childLink) );
1267: }
1268: }
1269: }
1270:
1271: // Remove from the primary tables
1272: for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
1273: $this->_remove_table( $this->_table[$i], $ids );
1274: }
1275:
1276: foreach ($data['data'] as $idSrc => $rowData) {
1277: $id = str_replace( $this->_idPrefix, "", $idSrc );
1278:
1279: $this->_trigger( 'postRemove', $id, $rowData );
1280: }
1281: }
1282:
1283:
1284: /**
1285: * File upload
1286: * @param array $data Upload data
1287: * @throws \Exception File upload name error
1288: * @private
1289: */
1290: private function _upload( $data )
1291: {
1292: // Search for upload field in local fields
1293: $field = $this->_find_field( $data['uploadField'], 'name' );
1294: $fieldName = '';
1295:
1296: if ( ! $field ) {
1297: // Perhaps it is in a join instance
1298: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1299: $join = $this->_join[$i];
1300: $fields = $join->fields();
1301:
1302: for ( $j=0, $jen=count($fields) ; $j<$jen ; $j++ ) {
1303: $joinField = $fields[ $j ];
1304: $name = $join->name().'[].'.$joinField->name();
1305:
1306: if ( $name === $data['uploadField'] ) {
1307: $field = $joinField;
1308: $fieldName = $name;
1309: }
1310: }
1311: }
1312: }
1313: else {
1314: $fieldName = $field->name();
1315: }
1316:
1317: if ( ! $field ) {
1318: throw new \Exception("Unknown upload field name submitted");
1319: }
1320:
1321: $res = $this->_trigger( 'preUpload', $data );
1322:
1323: // Allow the event to be cancelled and inform the client-side
1324: if ( $res === false ) {
1325: return;
1326: }
1327:
1328: $upload = $field->upload();
1329: if ( ! $upload ) {
1330: throw new \Exception("File uploaded to a field that does not have upload options configured");
1331: }
1332:
1333: $res = $upload->exec( $this );
1334:
1335: if ( $res === false ) {
1336: $this->_out['fieldErrors'][] = array(
1337: "name" => $fieldName, // field name can be just the field's
1338: "status" => $upload->error() // name or a join combination
1339: );
1340: }
1341: else {
1342: $files = $this->_fileData( $upload->table(), array($res) );
1343:
1344: $this->_out['files'] = $files;
1345: $this->_out['upload']['id'] = $res;
1346:
1347: $this->_trigger( 'postUpload', $res, $files, $data );
1348: }
1349: }
1350:
1351:
1352: /**
1353: * Get information about the files that are detailed in the database for
1354: * the fields which have an upload method defined on them.
1355: *
1356: * @param string [$limitTable=null] Limit the data gathering to a single
1357: * table only
1358: * @param number[] [$id=null] Limit to a specific set of ids
1359: * @return array File information
1360: * @private
1361: */
1362: private function _fileData ( $limitTable=null, $ids=null, $data=null )
1363: {
1364: $files = array();
1365:
1366: // The fields in this instance
1367: $this->_fileDataFields( $files, $this->_fields, $limitTable, $ids, $data );
1368:
1369: // From joined tables
1370: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1371: $joinData = null;
1372:
1373: // If we have data from the get, it is nested from the join, so we need to
1374: // un-nest it (i.e. get the array of joined data for each row)
1375: if ( $data ) {
1376: $joinData = array();
1377:
1378: for ( $j=0, $jen=count($data) ; $j<$jen ; $j++ ) {
1379: $joinData = array_merge( $joinData, $data[$j][$this->_join[$i]->name()] );
1380: }
1381: }
1382:
1383: $this->_fileDataFields( $files, $this->_join[$i]->fields(), $limitTable, $ids, $joinData );
1384: }
1385:
1386: return $files;
1387: }
1388:
1389:
1390: /**
1391: * Common file get method for any array of fields
1392: * @param array &$files File output array
1393: * @param Field[] $fields Fields to get file information about
1394: * @param string[] $limitTable Limit the data gathering to a single table
1395: * only
1396: * @private
1397: */
1398: private function _fileDataFields ( &$files, $fields, $limitTable, $ids=null, $data=null )
1399: {
1400: foreach ($fields as $field) {
1401: $upload = $field->upload();
1402:
1403: if ( $upload ) {
1404: $table = $upload->table();
1405:
1406: if ( ! $table ) {
1407: continue;
1408: }
1409:
1410: if ( $limitTable !== null && $table !== $limitTable ) {
1411: continue;
1412: }
1413:
1414: if ( isset( $files[ $table ] ) ) {
1415: continue;
1416: }
1417:
1418: // Make a collection of the ids used in this data set to get a limited data set
1419: // in return (security and performance)
1420: if ( $ids === null ) {
1421: $ids = array();
1422: }
1423:
1424: if ( $data !== null ) {
1425: for ( $i=0, $ien=count($data); $i<$ien ; $i++ ) {
1426: $val = $field->val( 'set', $data[$i] );
1427:
1428: if ( $val ) {
1429: $ids[] = $val;
1430: }
1431: }
1432:
1433: if ( count($ids) === 0 ) {
1434: // If no data to fetch, then don't bother
1435: return;
1436: }
1437: else if ( count($ids) > 1000 ) {
1438: // Don't use `where_in` for really large data sets
1439: $ids = array();
1440: }
1441: }
1442:
1443: $fileData = $upload->data( $this->_db, $ids );
1444:
1445: if ( $fileData !== null ) {
1446: $files[ $table ] = $fileData;
1447: }
1448: }
1449: }
1450: }
1451:
1452: /**
1453: * Run the file clean up
1454: *
1455: * @private
1456: */
1457: private function _fileClean ()
1458: {
1459: foreach ( $this->_fields as $field ) {
1460: $upload = $field->upload();
1461:
1462: if ( $upload ) {
1463: $upload->dbCleanExec( $this, $field );
1464: }
1465: }
1466:
1467: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
1468: foreach ( $this->_join[$i]->fields() as $field ) {
1469: $upload = $field->upload();
1470:
1471: if ( $upload ) {
1472: $upload->dbCleanExec( $this, $field );
1473: }
1474: }
1475: }
1476: }
1477:
1478:
1479: /* * * * * * * * * * * * * * * * * * * * * * * * *
1480: * Server-side processing methods
1481: */
1482:
1483: /**
1484: * When server-side processing is being used, modify the query with // the
1485: * required extra conditions
1486: *
1487: * @param \DataTables\Database\Query $query Query instance to apply the SSP commands to
1488: * @param array $http Parameters from HTTP request
1489: * @return array Server-side processing information array
1490: * @private
1491: */
1492: private function _ssp_query ( $query, $http )
1493: {
1494: if ( ! isset( $http['draw'] ) ) {
1495: return array();
1496: }
1497:
1498: // Add the server-side processing conditions
1499: $this->_ssp_limit( $query, $http );
1500: $this->_ssp_sort( $query, $http );
1501: $this->_ssp_filter( $query, $http );
1502:
1503: // Get the number of rows in the result set
1504: $ssp_set_count = $this->_db
1505: ->query('select')
1506: ->table( $this->_read_table() )
1507: ->get( 'COUNT('.$this->_pkey[0].') as cnt' );
1508: $this->_get_where( $ssp_set_count );
1509: $this->_ssp_filter( $ssp_set_count, $http );
1510: $this->_perform_left_join( $ssp_set_count );
1511: $ssp_set_count = $ssp_set_count->exec()->fetch();
1512:
1513: // Get the number of rows in the full set
1514: $ssp_full_count = $this->_db
1515: ->query('select')
1516: ->table( $this->_read_table() )
1517: ->get( 'COUNT('.$this->_pkey[0].') as cnt' );
1518: $this->_get_where( $ssp_full_count );
1519: if ( count( $this->_where ) ) { // only needed if there is a where condition
1520: $this->_perform_left_join( $ssp_full_count );
1521: }
1522: $ssp_full_count = $ssp_full_count->exec()->fetch();
1523:
1524: return array(
1525: "draw" => intval( $http['draw'] ),
1526: "recordsTotal" => $ssp_full_count['cnt'],
1527: "recordsFiltered" => $ssp_set_count['cnt']
1528: );
1529: }
1530:
1531:
1532: /**
1533: * Convert a column index to a database field name - used for server-side
1534: * processing requests.
1535: * @param array $http HTTP variables (i.e. GET or POST)
1536: * @param int $index Index in the DataTables' submitted data
1537: * @returns string DB field name
1538: * @throws \Exception Unknown fields
1539: * @private Note that it is actually public for PHP 5.3 - thread 39810
1540: */
1541: public function _ssp_field( $http, $index )
1542: {
1543: $name = $http['columns'][$index]['data'];
1544: $field = $this->_find_field( $name, 'name' );
1545:
1546: if ( ! $field ) {
1547: // Is it the primary key?
1548: if ( $name === 'DT_RowId' ) {
1549: return $this->_pkey[0];
1550: }
1551:
1552: throw new \Exception('Unknown field: '.$name .' (index '.$index.')');
1553: }
1554:
1555: return $field->dbField();
1556: }
1557:
1558:
1559: /**
1560: * Sorting requirements to a server-side processing query.
1561: * @param \DataTables\Database\Query $query Query instance to apply sorting to
1562: * @param array $http HTTP variables (i.e. GET or POST)
1563: * @private
1564: */
1565: private function _ssp_sort ( $query, $http )
1566: {
1567: for ( $i=0 ; $i<count($http['order']) ; $i++ ) {
1568: $order = $http['order'][$i];
1569:
1570: $query->order(
1571: $this->_ssp_field( $http, $order['column'] ) .' '.
1572: ($order['dir']==='asc' ? 'asc' : 'desc')
1573: );
1574: }
1575: }
1576:
1577:
1578: /**
1579: * Add DataTables' 'where' condition to a server-side processing query. This
1580: * works for both global and individual column filtering.
1581: * @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
1582: * @param array $http HTTP variables (i.e. GET or POST)
1583: * @private
1584: */
1585: private function _ssp_filter ( $query, $http )
1586: {
1587: $that = $this;
1588:
1589: // Global filter
1590: $fields = $this->_fields;
1591:
1592: // Global search, add a ( ... or ... ) set of filters for each column
1593: // in the table (not the fields, just the columns submitted)
1594: if ( $http['search']['value'] ) {
1595: $query->where( function ($q) use (&$that, &$fields, $http) {
1596: for ( $i=0 ; $i<count($http['columns']) ; $i++ ) {
1597: if ( $http['columns'][$i]['searchable'] == 'true' ) {
1598: $field = $that->_ssp_field( $http, $i );
1599:
1600: if ( $field ) {
1601: $q->or_where( $field, '%'.$http['search']['value'].'%', 'like' );
1602: }
1603: }
1604: }
1605: } );
1606: }
1607:
1608: // if ( $http['search']['value'] ) {
1609: // $words = explode(" ", $http['search']['value']);
1610:
1611: // $query->where( function ($q) use (&$that, &$fields, $http, $words) {
1612: // for ( $j=0, $jen=count($words) ; $j<$jen ; $j++ ) {
1613: // if ( $words[$j] ) {
1614: // $q->where_group( true );
1615:
1616: // for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
1617: // if ( $http['columns'][$i]['searchable'] == 'true' ) {
1618: // $field = $that->_ssp_field( $http, $i );
1619:
1620: // $q->or_where( $field, $words[$j].'%', 'like' );
1621: // $q->or_where( $field, '% '.$words[$j].'%', 'like' );
1622: // }
1623: // }
1624:
1625: // $q->where_group( false );
1626: // }
1627: // }
1628: // } );
1629: // }
1630:
1631: // Column filters
1632: for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
1633: $column = $http['columns'][$i];
1634: $search = $column['search']['value'];
1635:
1636: if ( $search !== '' && $column['searchable'] == 'true' ) {
1637: $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
1638: }
1639: }
1640: }
1641:
1642:
1643: /**
1644: * Add a limit / offset to a server-side processing query
1645: * @param \DataTables\Database\Query $query Query instance to apply the offset / limit to
1646: * @param array $http HTTP variables (i.e. GET or POST)
1647: * @private
1648: */
1649: private function _ssp_limit ( $query, $http )
1650: {
1651: if ( $http['length'] != -1 ) { // -1 is 'show all' in DataTables
1652: $query
1653: ->offset( $http['start'] )
1654: ->limit( $http['length'] );
1655: }
1656: }
1657:
1658:
1659: /* * * * * * * * * * * * * * * * * * * * * * * * *
1660: * Internal helper methods
1661: */
1662:
1663: /**
1664: * Add left join commands for the instance to a query.
1665: *
1666: * @param \DataTables\Database\Query $query Query instance to apply the joins to
1667: * @private
1668: */
1669: private function _perform_left_join ( $query )
1670: {
1671: if ( count($this->_leftJoin) ) {
1672: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
1673: $join = $this->_leftJoin[$i];
1674:
1675: $query->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' );
1676: }
1677: }
1678: }
1679:
1680:
1681: /**
1682: * Add local WHERE condition to query
1683: * @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
1684: * @private
1685: */
1686: private function _get_where ( $query )
1687: {
1688: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
1689: if ( is_callable( $this->_where[$i] ) ) {
1690: $this->_where[$i]( $query );
1691: }
1692: else {
1693: $query->where(
1694: $this->_where[$i]['key'],
1695: $this->_where[$i]['value'],
1696: $this->_where[$i]['op']
1697: );
1698: }
1699: }
1700: }
1701:
1702:
1703: /**
1704: * Get a field instance from a known field name
1705: *
1706: * @param string $name Field name
1707: * @param string $type Matching name type
1708: * @return Field Field instance
1709: * @private
1710: */
1711: private function _find_field ( $name, $type )
1712: {
1713: for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
1714: $field = $this->_fields[ $i ];
1715:
1716: if ( $type === 'name' && $field->name() === $name ) {
1717: return $field;
1718: }
1719: else if ( $type === 'db' && $field->dbField() === $name ) {
1720: return $field;
1721: }
1722: }
1723:
1724: return null;
1725: }
1726:
1727:
1728: /**
1729: * Insert or update a row for all main tables and left joined tables.
1730: *
1731: * @param int|string $id ID to use to condition the update. If null is
1732: * given, the first query performed is an insert and the inserted id
1733: * used as the value should there be any subsequent tables to operate
1734: * on. Mote that for compound keys, this should be the "joined" value
1735: * (i.e. a single string rather than an array).
1736: * @return \DataTables\Database\Result Result from the query or null if no
1737: * query performed.
1738: * @private
1739: */
1740: private function _insert_or_update ( $id, $values )
1741: {
1742: // Loop over all tables in _table, doing the insert or update as needed
1743: for ( $i=0, $ien=count( $this->_table ) ; $i<$ien ; $i++ ) {
1744: $res = $this->_insert_or_update_table(
1745: $this->_table[$i],
1746: $values,
1747: $id !== null ?
1748: $this->pkeyToArray( $id, true ) :
1749: null
1750: );
1751:
1752: // If we don't have an id yet, then the first insert will return
1753: // the id we want
1754: if ( $res !== null && $id === null ) {
1755: $id = $res->insertId();
1756: }
1757: }
1758:
1759: // And for the left join tables as well
1760: for ( $i=0, $ien=count( $this->_leftJoin ) ; $i<$ien ; $i++ ) {
1761: $join = $this->_leftJoin[$i];
1762:
1763: // which side of the join refers to the parent table?
1764: $joinTable = $this->_alias( $join['table'], 'alias' );
1765: $tablePart = $this->_part( $join['field1'] );
1766:
1767: if ( $this->_part( $join['field1'], 'db' ) ) {
1768: $tablePart = $this->_part( $join['field1'], 'db' ).'.'.$tablePart;
1769: }
1770:
1771: if ( $tablePart === $joinTable ) {
1772: $parentLink = $join['field2'];
1773: $childLink = $join['field1'];
1774: }
1775: else {
1776: $parentLink = $join['field1'];
1777: $childLink = $join['field2'];
1778: }
1779:
1780: if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
1781: $whereVal = $id;
1782: }
1783: else {
1784: // We need submitted information about the joined data to be
1785: // submitted as well as the new value. We first check if the
1786: // host field was submitted
1787: $field = $this->_find_field( $parentLink, 'db' );
1788:
1789: if ( ! $field || ! $field->apply( 'set', $values ) ) {
1790: // If not, then check if the child id was submitted
1791: $field = $this->_find_field( $childLink, 'db' );
1792:
1793: // No data available, so we can't do anything
1794: if ( ! $field || ! $field->apply( 'set', $values ) ) {
1795: continue;
1796: }
1797: }
1798:
1799: $whereVal = $field->val('set', $values);
1800: }
1801:
1802: $whereName = $this->_part( $childLink, 'field' );
1803:
1804: $this->_insert_or_update_table(
1805: $join['table'],
1806: $values,
1807: array( $whereName => $whereVal )
1808: );
1809: }
1810:
1811: return $id;
1812: }
1813:
1814:
1815: /**
1816: * Insert or update a row in a single database table, based on the data
1817: * given and the fields configured for the instance.
1818: *
1819: * The function will find the fields which are required for this specific
1820: * table, based on the names of fields and use only the appropriate data for
1821: * this table. Therefore the full submitted data set can be passed in.
1822: *
1823: * @param string $table Database table name to use (can include an alias)
1824: * @param array $where Update condition
1825: * @return \DataTables\Database\Result Result from the query or null if no query
1826: * performed.
1827: * @throws \Exception Where set error
1828: * @private
1829: */
1830: private function _insert_or_update_table ( $table, $values, $where=null )
1831: {
1832: $set = array();
1833: $action = ($where === null) ? 'create' : 'edit';
1834: $tableAlias = $this->_alias( $table, 'alias' );
1835:
1836: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
1837: $field = $this->_fields[$i];
1838: $tablePart = $this->_part( $field->dbField() );
1839:
1840: if ( $this->_part( $field->dbField(), 'db' ) ) {
1841: $tablePart = $this->_part( $field->dbField(), 'db' ).'.'.$tablePart;
1842: }
1843:
1844: // Does this field apply to this table (only check when a join is
1845: // being used)
1846: if ( count($this->_leftJoin) && $tablePart !== $tableAlias ) {
1847: continue;
1848: }
1849:
1850: // Check if this field should be set, based on options and
1851: // submitted data
1852: if ( ! $field->apply( $action, $values ) ) {
1853: continue;
1854: }
1855:
1856: // Some db's (specifically postgres) don't like having the table
1857: // name prefixing the column name. Todo: it might be nicer to have
1858: // the db layer abstract this out?
1859: $fieldPart = $this->_part( $field->dbField(), 'field' );
1860: $set[ $fieldPart ] = $field->val( 'set', $values );
1861: }
1862:
1863: // Add where fields if setting where values and required for this
1864: // table
1865: // Note that `whereSet` is now deprecated
1866: if ( $this->_whereSet ) {
1867: for ( $j=0, $jen=count($this->_where) ; $j<$jen ; $j++ ) {
1868: $cond = $this->_where[$j];
1869:
1870: if ( ! is_callable( $cond ) ) {
1871: // Make sure the value wasn't in the submitted data set,
1872: // otherwise we would be overwriting it
1873: if ( ! isset( $set[ $cond['key'] ] ) )
1874: {
1875: $whereTablePart = $this->_part( $cond['key'], 'table' );
1876:
1877: // No table part on the where condition to match against
1878: // or table operating on matches table part from cond.
1879: if ( ! $whereTablePart || $tableAlias == $whereTablePart ) {
1880: $set[ $cond['key'] ] = $cond['value'];
1881: }
1882: }
1883: else {
1884: throw new \Exception( 'Where condition used as a setter, '.
1885: 'but value submitted for field: '.$cond['key']
1886: );
1887: }
1888: }
1889: }
1890: }
1891:
1892: // If nothing to do, then do nothing!
1893: if ( ! count( $set ) ) {
1894: return null;
1895: }
1896:
1897: // Insert or update
1898: if ( $action === 'create' ) {
1899: return $this->_db->insert( $table, $set, $this->_pkey );
1900: }
1901: else {
1902: return $this->_db->push( $table, $set, $where, $this->_pkey );
1903: }
1904: }
1905:
1906:
1907: /**
1908: * Delete one or more rows from the database for an individual table
1909: *
1910: * @param string $table Database table name to use
1911: * @param array $ids Array of ids to remove
1912: * @param string $pkey Database column name to match the ids on for the
1913: * delete condition. If not given the instance's base primary key is
1914: * used.
1915: * @private
1916: */
1917: private function _remove_table ( $table, $ids, $pkey=null )
1918: {
1919: if ( $pkey === null ) {
1920: $pkey = $this->_pkey;
1921: }
1922:
1923: // Check there is a field which has a set option for this table
1924: $count = 0;
1925:
1926: foreach ($this->_fields as $field) {
1927: if ( strpos( $field->dbField(), '.') === false || (
1928: $this->_part( $field->dbField(), 'table' ) === $table &&
1929: $field->set() !== Field::SET_NONE
1930: )
1931: ) {
1932: $count++;
1933: }
1934: }
1935:
1936: if ( $count > 0 ) {
1937: $q = $this->_db
1938: ->query( 'delete' )
1939: ->table( $table );
1940:
1941: for ( $i=0, $ien=count($ids) ; $i<$ien ; $i++ ) {
1942: $cond = $this->pkeyToArray( $ids[$i], true, $pkey );
1943:
1944: $q->or_where( function ($q2) use ($cond) {
1945: $q2->where( $cond );
1946: } );
1947: }
1948:
1949: $q->exec();
1950: }
1951: }
1952:
1953:
1954: /**
1955: * Check the validity of the set options if we are doing a join, since
1956: * there are some conditions for this state. Will throw an error if not
1957: * valid.
1958: *
1959: * @private
1960: */
1961: private function _prepJoin ()
1962: {
1963: if ( count( $this->_leftJoin ) === 0 ) {
1964: return;
1965: }
1966:
1967: // Check if the primary key has a table identifier - if not - add one
1968: for ( $i=0, $ien=count($this->_pkey) ; $i<$ien ; $i++ ) {
1969: $val = $this->_pkey[$i];
1970:
1971: if ( strpos( $val, '.' ) === false ) {
1972: $this->_pkey[$i] = $this->_alias( $this->_table[0], 'alias' ).'.'.$val;
1973: }
1974: }
1975:
1976: // Check that all fields have a table selector, otherwise, we'd need to
1977: // know the structure of the tables, to know which fields belong in
1978: // which. This extra requirement on the fields removes that
1979: for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
1980: $field = $this->_fields[$i];
1981: $name = $field->dbField();
1982:
1983: if ( strpos( $name, '.' ) === false ) {
1984: throw new \Exception( 'Table part of the field "'.$name.'" was not found. '.
1985: 'In Editor instances that use a join, all fields must have the '.
1986: 'database table set explicitly.'
1987: );
1988: }
1989: }
1990: }
1991:
1992:
1993: /**
1994: * Get one side or the other of an aliased SQL field name.
1995: *
1996: * @param string $name SQL field
1997: * @param string $type Which part to get: `alias` (default) or `orig`.
1998: * @returns string Alias
1999: * @private
2000: */
2001: private function _alias ( $name, $type='alias' )
2002: {
2003: if ( stripos( $name, ' as ' ) !== false ) {
2004: $a = preg_split( '/ as /i', $name );
2005: return $type === 'alias' ?
2006: $a[1] :
2007: $a[0];
2008: }
2009:
2010: if ( stripos( $name, ' ' ) !== false ) {
2011: $a = preg_split( '/ /i', $name );
2012: return $type === 'alias' ?
2013: $a[1] :
2014: $a[0];
2015: }
2016:
2017: return $name;
2018: }
2019:
2020:
2021: /**
2022: * Get part of an SQL field definition regardless of how deeply defined it
2023: * is
2024: *
2025: * @param string $name SQL field
2026: * @param string $type Which part to get: `table` (default) or `db` or
2027: * `column`
2028: * @return string Part name
2029: * @private
2030: */
2031: private function _part ( $name, $type='table' )
2032: {
2033: $db = null;
2034: $table = null;
2035: $column = null;
2036:
2037: if ( strpos( $name, '.' ) !== false ) {
2038: $a = explode( '.', $name );
2039:
2040: if ( count($a) === 3 ) {
2041: $db = $a[0];
2042: $table = $a[1];
2043: $column = $a[2];
2044: }
2045: else if ( count($a) === 2 ) {
2046: $table = $a[0];
2047: $column = $a[1];
2048: }
2049: }
2050: else {
2051: $column = $name;
2052: }
2053:
2054: if ( $type === 'db' ) {
2055: return $db;
2056: }
2057: else if ( $type === 'table' ) {
2058: return $table;
2059: }
2060: return $column;
2061: }
2062:
2063:
2064: /**
2065: * Trigger an event
2066: *
2067: * @private
2068: */
2069: private function _trigger ( $eventName, &$arg1=null, &$arg2=null, &$arg3=null, &$arg4=null, &$arg5=null )
2070: {
2071: $out = null;
2072: $argc = func_num_args();
2073: $args = array( $this );
2074:
2075: // Hack to enable pass by reference with a "variable" number of parameters
2076: for ( $i=1 ; $i<$argc ; $i++ ) {
2077: $name = 'arg'.$i;
2078: $args[] = &$$name;
2079: }
2080:
2081: if ( ! isset( $this->_events[ $eventName ] ) ) {
2082: return;
2083: }
2084:
2085: $events = $this->_events[ $eventName ];
2086:
2087: for ( $i=0, $ien=count($events) ; $i<$ien ; $i++ ) {
2088: $res = call_user_func_array( $events[$i], $args );
2089:
2090: if ( $res !== null ) {
2091: $out = $res;
2092: }
2093: }
2094:
2095: return $out;
2096: }
2097:
2098:
2099: /**
2100: * Merge a primary key value with an updated data source.
2101: *
2102: * @param string $pkeyVal Old primary key value to merge into
2103: * @param array $row Data source for update
2104: * @return string Merged value
2105: */
2106: private function _pkey_submit_merge ( $pkeyVal, $row )
2107: {
2108: $pkey = $this->_pkey;
2109: $arr = $this->pkeyToArray( $pkeyVal, true );
2110:
2111: for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
2112: $column = $pkey[ $i ];
2113: $field = $this->_find_field( $column, 'db' );
2114:
2115: if ( $field && $field->apply( 'edit', $row ) ) {
2116: $arr[ $column ] = $field->val( 'set', $row );
2117: }
2118: }
2119:
2120: return $this->pkeyToValue( $arr, true );
2121: }
2122:
2123:
2124: /**
2125: * Validate that all primary key fields have values for create.
2126: *
2127: * @param array $row Row's data
2128: * @return boolean `true` if valid, `false` otherwise
2129: */
2130: private function _pkey_validate_insert ( $row )
2131: {
2132: $pkey = $this->_pkey;
2133:
2134: if ( count( $pkey ) === 1 ) {
2135: return true;
2136: }
2137:
2138: for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
2139: $column = $pkey[ $i ];
2140: $field = $this->_find_field( $column, 'db' );
2141:
2142: if ( ! $field || ! $field->apply("create", $row) ) {
2143: throw new \Exception( "When inserting into a compound key table, ".
2144: "all fields that are part of the compound key must be ".
2145: "submitted with a specific value.", 1
2146: );
2147: }
2148: }
2149:
2150: return true;
2151: }
2152:
2153:
2154: /**
2155: * Create the separator value for a compound primary key.
2156: *
2157: * @return string Calculated separator
2158: */
2159: private function _pkey_separator ()
2160: {
2161: $str = implode(',', $this->_pkey);
2162:
2163: return hash( 'crc32', $str );
2164: }
2165:
2166: private function _read_table ()
2167: {
2168: return count($this->_readTableNames) ?
2169: $this->_readTableNames :
2170: $this->_table;
2171: }
2172: }
2173:
2174: