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