1<?php
2
3/**
4 * Class ilDBPdoFieldDefinition
5 *
6 * @author Fabian Schmid <fs@studer-raimann.ch>
7 */
8abstract class ilDBPdoFieldDefinition
9{
10    const DEFAULT_DECIMAL_PLACES = 2;
11    const DEFAULT_TEXT_LENGTH = 4000;
12    const DEFINITION_COLUMN_NAME = "/^[a-z]+[_a-z0-9]*$/";
13    const DEFINITION_TABLE_NAME = "/^[a-z]+[_a-z0-9]*$/";
14    const INDEX_FORMAT = '%s_idx';
15    const SEQUENCE_COLUMNS_NAME = 'sequence';
16    const SEQUENCE_FORMAT = '%s_seq';
17    const T_BLOB = 'blob';
18    const T_CLOB = 'clob';
19    const T_DATE = 'date';
20    const T_DATETIME = 'datetime';
21    const T_FLOAT = 'float';
22    const T_INTEGER = 'integer';
23    const T_TEXT = 'text';
24    const T_TIME = 'time';
25    const T_TIMESTAMP = 'timestamp';
26    /**
27     * @var ilDBPdoFieldDefinition
28     */
29    protected static $instance;
30    /**
31     * @var array
32     */
33    public $allowed_attributes_old = array(
34        self::T_TEXT => array( 'length', 'notnull', 'default', 'fixed' ),
35        self::T_INTEGER => array( 'length', 'notnull', 'default', 'unsigned' ),
36        self::T_FLOAT => array( 'notnull', 'default' ),
37        self::T_DATE => array( 'notnull', 'default' ),
38        self::T_TIME => array( 'notnull', 'default' ),
39        self::T_TIMESTAMP => array( 'notnull', 'default' ),
40        self::T_CLOB => array( 'notnull', 'default' ),
41        self::T_BLOB => array( 'notnull', 'default' ),
42    );
43    /**
44     * @var array
45     */
46    public $allowed_attributes = array(
47        "text" => array( "length", "notnull", "default", "fixed" ),
48        "integer" => array( "length", "notnull", "default", "unsigned" ),
49        "float" => array( "notnull", "default" ),
50        "date" => array( "notnull", "default" ),
51        "time" => array( "notnull", "default" ),
52        "timestamp" => array( "notnull", "default" ),
53        "clob" => array( "length", "notnull", "default" ),
54        "blob" => array( "length", "notnull", "default" ),
55    );
56    /**
57     * @var ilDBInterface
58     */
59    protected $db_instance;
60    /**
61     * @var array
62     */
63    protected $max_length = array(
64        self::T_INTEGER => array( 1, 2, 3, 4, 8 ),
65        self::T_TEXT => 4000,
66    );
67    /**
68     * @var array
69     */
70    protected $available_types = array(
71        self::T_TEXT,
72        self::T_INTEGER,
73        self::T_FLOAT,
74        self::T_DATE,
75        self::T_TIME,
76        self::T_TIMESTAMP,
77        self::T_CLOB,
78        self::T_BLOB,
79    );
80    /**
81     * @var array
82     */
83    protected $reserved_mysql = array(
84        "ACCESSIBLE",
85        "ACCOUNT",
86        "ACTION",
87        "ADD",
88        "AFTER",
89        "AGAINST",
90        "AGGREGATE",
91        "ALGORITHM",
92        "ALL",
93        "ALTER",
94        "ALWAYS",
95        "ANALYSE",
96        "ANALYZE",
97        "AND",
98        "ANY",
99        "AS",
100        "ASC",
101        "ASCII",
102        "ASENSITIVE",
103        "AT",
104        "AUTHORS",
105        "AUTOEXTEND_SIZE",
106        "AUTO_INCREMENT",
107        "AVG",
108        "AVG_ROW_LENGTH",
109        "BACKUP",
110        "BEFORE",
111        "BEGIN",
112        "BETWEEN",
113        "BIGINT",
114        "BINARY",
115        "BINLOG",
116        "BIT",
117        "BLOB",
118        "BLOCK",
119        "BOOL",
120        "BOOLEAN",
121        "BOTH",
122        "BTREE",
123        "BY",
124        "BYTE",
125        "CACHE",
126        "CALL",
127        "CASCADE",
128        "CASCADED",
129        "CASE",
130        "CATALOG_NAME",
131        "CHAIN",
132        "CHANGE",
133        "CHANGED",
134        "CHANNEL",
135        "CHAR",
136        "CHARACTER",
137        "CHARSET",
138        "CHECK",
139        "CHECKSUM",
140        "CIPHER",
141        "CLASS_ORIGIN",
142        "CLIENT",
143        "CLOSE",
144        "COALESCE",
145        "CODE",
146        "COLLATE",
147        "COLLATION",
148        "COLUMN",
149        "COLUMNS",
150        "COLUMN_FORMAT",
151        "COLUMN_NAME",
152        "COMMENT",
153        "COMMIT",
154        "COMMITTED",
155        "COMPACT",
156        "COMPLETION",
157        "COMPRESSED",
158        "COMPRESSION",
159        "CONCURRENT",
160        "CONDITION",
161        "CONNECTION",
162        "CONSISTENT",
163        "CONSTRAINT",
164        "CONSTRAINT_CATALOG",
165        "CONSTRAINT_NAME",
166        "CONSTRAINT_SCHEMA",
167        "CONTAINS",
168        "CONTEXT",
169        "CONTINUE",
170        "CONTRIBUTORS",
171        "CONVERT",
172        "CPU",
173        "CREATE",
174        "CROSS",
175        "CUBE",
176        "CURRENT",
177        "CURRENT_DATE",
178        "CURRENT_TIME",
179        "CURRENT_TIMESTAMP",
180        "CURRENT_USER",
181        "CURSOR",
182        "CURSOR_NAME",
183        "DATA",
184        "DATABASE",
185        "DATABASES",
186        "DATAFILE",
187        "DATE",
188        "DATETIME",
189        "DAY",
190        "DAY_HOUR",
191        "DAY_MICROSECOND",
192        "DAY_MINUTE",
193        "DAY_SECOND",
194        "DEALLOCATE",
195        "DEC",
196        "DECIMAL",
197        "DECLARE",
198        "DEFAULT",
199        "DEFAULT_AUTH",
200        "DEFINER",
201        "DELAYED",
202        "DELAY_KEY_WRITE",
203        "DELETE",
204        "DESC",
205        "DESCRIBE",
206        "DES_KEY_FILE",
207        "DETERMINISTIC",
208        "DIAGNOSTICS",
209        "DIRECTORY",
210        "DISABLE",
211        "DISCARD",
212        "DISK",
213        "DISTINCT",
214        "DISTINCTROW",
215        "DIV",
216        "DO",
217        "DOUBLE",
218        "DROP",
219        "DUAL",
220        "DUMPFILE",
221        "DUPLICATE",
222        "DYNAMIC",
223        "EACH",
224        "ELSE",
225        "ELSEIF",
226        "ENABLE",
227        "ENCLOSED",
228        "ENCRYPTION",
229        "END",
230        "ENDS",
231        "ENGINE",
232        "ENGINES",
233        "ENUM",
234        "ERROR",
235        "ERRORS",
236        "ESCAPE",
237        "ESCAPED",
238        "EVENT",
239        "EVENTS",
240        "EVERY",
241        "EXCHANGE",
242        "EXECUTE",
243        "EXISTS",
244        "EXIT",
245        "EXPANSION",
246        "EXPIRE",
247        "EXPLAIN",
248        "EXPORT",
249        "EXTENDED",
250        "EXTENT_SIZE",
251        "FALSE",
252        "FAST",
253        "FAULTS",
254        "FETCH",
255        "FIELDS",
256        "FILE",
257        "FILE_BLOCK_SIZE",
258        "FILTER",
259        "FIRST",
260        "FIXED",
261        "FLOAT",
262        "FLOAT4",
263        "FLOAT8",
264        "FLUSH",
265        "FOLLOWS",
266        "FOR",
267        "FORCE",
268        "FOREIGN",
269        "FORMAT",
270        "FOUND",
271        "FROM",
272        "FULL",
273        "FULLTEXT",
274        "FUNCTION",
275        "GENERAL",
276        "GENERATED",
277        "GEOMETRY",
278        "GEOMETRYCOLLECTION",
279        "GET",
280        "GET_FORMAT",
281        "GLOBAL",
282        "GRANT",
283        "GRANTS",
284        "GROUP",
285        "GROUP_REPLICATION",
286        "HANDLER",
287        "HASH",
288        "HAVING",
289        "HELP",
290        "HIGH_PRIORITY",
291        "HOST",
292        "HOSTS",
293        "HOUR",
294        "HOUR_MICROSECOND",
295        "HOUR_MINUTE",
296        "HOUR_SECOND",
297        "IDENTIFIED",
298        "IF",
299        "IGNORE",
300        "IGNORE_SERVER_IDS",
301        "IMPORT",
302        "IN",
303        "INDEX",
304        "INDEXES",
305        "INFILE",
306        "INITIAL_SIZE",
307        "INNER",
308        "INOUT",
309        "INSENSITIVE",
310        "INSERT",
311        "INSERT_METHOD",
312        "INSTALL",
313        "INSTANCE",
314        "INT",
315        "INT1",
316        "INT2",
317        "INT3",
318        "INT4",
319        "INT8",
320        "INTEGER",
321        "INTERVAL",
322        "INTO",
323        "INVOKER",
324        "IO",
325        "IO_AFTER_GTIDS",
326        "IO_BEFORE_GTIDS",
327        "IO_THREAD",
328        "IPC",
329        "IS",
330        "ISOLATION",
331        "ISSUER",
332        "ITERATE",
333        "JOIN",
334        "JSON",
335        "KEY",
336        "KEYS",
337        "KEY_BLOCK_SIZE",
338        "KILL",
339        "LANGUAGE",
340        "LAST",
341        "LEADING",
342        "LEAVE",
343        "LEAVES",
344        "LEFT",
345        "LESS",
346        "LEVEL",
347        "LIKE",
348        "LIMIT",
349        "LINEAR",
350        "LINES",
351        "LINESTRING",
352        "LIST",
353        "LOAD",
354        "LOCAL",
355        "LOCALTIME",
356        "LOCALTIMESTAMP",
357        "LOCK",
358        "LOCKS",
359        "LOGFILE",
360        "LOGS",
361        "LONG",
362        "LONGBLOB",
363        "LONGTEXT",
364        "LOOP",
365        "LOW_PRIORITY",
366        "MASTER",
367        "MASTER_AUTO_POSITION",
368        "MASTER_BIND",
369        "MASTER_CONNECT_RETRY",
370        "MASTER_DELAY",
371        "MASTER_HEARTBEAT_PERIOD",
372        "MASTER_HOST",
373        "MASTER_LOG_FILE",
374        "MASTER_LOG_POS",
375        "MASTER_PASSWORD",
376        "MASTER_PORT",
377        "MASTER_RETRY_COUNT",
378        "MASTER_SERVER_ID",
379        "MASTER_SSL",
380        "MASTER_SSL_CA",
381        "MASTER_SSL_CAPATH",
382        "MASTER_SSL_CERT",
383        "MASTER_SSL_CIPHER",
384        "MASTER_SSL_CRL",
385        "MASTER_SSL_CRLPATH",
386        "MASTER_SSL_KEY",
387        "MASTER_SSL_VERIFY_SERVER_CERT",
388        "MASTER_TLS_VERSION",
389        "MASTER_USER",
390        "MATCH",
391        "MAXVALUE",
392        "MAX_CONNECTIONS_PER_HOUR",
393        "MAX_QUERIES_PER_HOUR",
394        "MAX_ROWS",
395        "MAX_SIZE",
396        "MAX_STATEMENT_TIME",
397        "MAX_UPDATES_PER_HOUR",
398        "MAX_USER_CONNECTIONS",
399        "MEDIUM",
400        "MEDIUMBLOB",
401        "MEDIUMINT",
402        "MEDIUMTEXT",
403        "MEMORY",
404        "MERGE",
405        "MESSAGE_TEXT",
406        "MICROSECOND",
407        "MIDDLEINT",
408        "MIGRATE",
409        "MINUTE",
410        "MINUTE_MICROSECOND",
411        "MINUTE_SECOND",
412        "MIN_ROWS",
413        "MOD",
414        "MODE",
415        "MODIFIES",
416        "MODIFY",
417        "MONTH",
418        "MULTILINESTRING",
419        "MULTIPOINT",
420        "MULTIPOLYGON",
421        "MUTEX",
422        "MYSQL_ERRNO",
423        "NAME",
424        "NAMES",
425        "NATIONAL",
426        "NATURAL",
427        "NCHAR",
428        "NDB",
429        "NDBCLUSTER",
430        "NEVER",
431        "NEW",
432        "NEXT",
433        "NO",
434        "NODEGROUP",
435        "NONBLOCKING",
436        "NONE",
437        "NOT",
438        "NO_WAIT",
439        "NO_WRITE_TO_BINLOG",
440        "NULL",
441        "NUMBER",
442        "NUMERIC",
443        "NVARCHAR",
444        "OFFSET",
445        "OLD_PASSWORD",
446        "ON",
447        "ONE",
448        "ONE_SHOT",
449        "ONLY",
450        "OPEN",
451        "OPTIMIZE",
452        "OPTIMIZER_COSTS",
453        "OPTION",
454        "OPTIONALLY",
455        "OPTIONS",
456        "OR",
457        "ORDER",
458        "OUT",
459        "OUTER",
460        "OUTFILE",
461        "OWNER",
462        "PACK_KEYS",
463        "PAGE",
464        "PARSER",
465        "PARSE_GCOL_EXPR",
466        "PARTIAL",
467        "PARTITION",
468        "PARTITIONING",
469        "PARTITIONS",
470        "PASSWORD",
471        "PHASE",
472        "PLUGIN",
473        "PLUGINS",
474        "PLUGIN_DIR",
475        "POINT",
476        "POLYGON",
477        "PORT",
478        "PRECEDES",
479        "PRECISION",
480        "PREPARE",
481        "PRESERVE",
482        "PREV",
483        "PRIMARY",
484        "PRIVILEGES",
485        "PROCEDURE",
486        "PROCESSLIST",
487        "PROFILE",
488        "PROFILES",
489        "PROXY",
490        "PURGE",
491        "QUARTER",
492        "QUERY",
493        "QUICK",
494        "RANGE",
495        "READ",
496        "READS",
497        "READ_ONLY",
498        "READ_WRITE",
499        "REAL",
500        "REBUILD",
501        "RECOVER",
502        "REDOFILE",
503        "REDO_BUFFER_SIZE",
504        "REDUNDANT",
505        "REFERENCES",
506        "REGEXP",
507        "RELAY",
508        "RELAYLOG",
509        "RELAY_LOG_FILE",
510        "RELAY_LOG_POS",
511        "RELAY_THREAD",
512        "RELEASE",
513        "RELOAD",
514        "REMOVE",
515        "RENAME",
516        "REORGANIZE",
517        "REPAIR",
518        "REPEAT",
519        "REPEATABLE",
520        "REPLACE",
521        "REPLICATE_DO_DB",
522        "REPLICATE_DO_TABLE",
523        "REPLICATE_IGNORE_DB",
524        "REPLICATE_IGNORE_TABLE",
525        "REPLICATE_REWRITE_DB",
526        "REPLICATE_WILD_DO_TABLE",
527        "REPLICATE_WILD_IGNORE_TABLE",
528        "REPLICATION",
529        "REQUIRE",
530        "RESET",
531        "RESIGNAL",
532        "RESTORE",
533        "RESTRICT",
534        "RESUME",
535        "RETURN",
536        "RETURNED_SQLSTATE",
537        "RETURNS",
538        "REVERSE",
539        "REVOKE",
540        "RIGHT",
541        "RLIKE",
542        "ROLLBACK",
543        "ROLLUP",
544        "ROTATE",
545        "ROUTINE",
546        "ROW",
547        "ROWS",
548        "ROW_COUNT",
549        "ROW_FORMAT",
550        "RTREE",
551        "SAVEPOINT",
552        "SCHEDULE",
553        "SCHEMA",
554        "SCHEMAS",
555        "SCHEMA_NAME",
556        "SECOND",
557        "SECOND_MICROSECOND",
558        "SECURITY",
559        "SELECT",
560        "SENSITIVE",
561        "SEPARATOR",
562        "SERIAL",
563        "SERIALIZABLE",
564        "SERVER",
565        "SESSION",
566        "SET",
567        "SHARE",
568        "SHOW",
569        "SHUTDOWN",
570        "SIGNAL",
571        "SIGNED",
572        "SIMPLE",
573        "SLAVE",
574        "SLOW",
575        "SMALLINT",
576        "SNAPSHOT",
577        "SOCKET",
578        "SOME",
579        "SONAME",
580        "SOUNDS",
581        "SOURCE",
582        "SPATIAL",
583        "SPECIFIC",
584        "SQL",
585        "SQLEXCEPTION",
586        "SQLSTATE",
587        "SQLWARNING",
588        "SQL_AFTER_GTIDS",
589        "SQL_AFTER_MTS_GAPS",
590        "SQL_BEFORE_GTIDS",
591        "SQL_BIG_RESULT",
592        "SQL_BUFFER_RESULT",
593        "SQL_CACHE",
594        "SQL_CALC_FOUND_ROWS",
595        "SQL_NO_CACHE",
596        "SQL_SMALL_RESULT",
597        "SQL_THREAD",
598        "SQL_TSI_DAY",
599        "SQL_TSI_HOUR",
600        "SQL_TSI_MINUTE",
601        "SQL_TSI_MONTH",
602        "SQL_TSI_QUARTER",
603        "SQL_TSI_SECOND",
604        "SQL_TSI_WEEK",
605        "SQL_TSI_YEAR",
606        "SSL",
607        "STACKED",
608        "START",
609        "STARTING",
610        "STARTS",
611        "STATS_AUTO_RECALC",
612        "STATS_PERSISTENT",
613        "STATS_SAMPLE_PAGES",
614        "STATUS",
615        "STOP",
616        "STORAGE",
617        "STORED",
618        "STRAIGHT_JOIN",
619        "STRING",
620        "SUBCLASS_ORIGIN",
621        "SUBJECT",
622        "SUBPARTITION",
623        "SUBPARTITIONS",
624        "SUPER",
625        "SUSPEND",
626        "SWAPS",
627        "SWITCHES",
628        "TABLE",
629        "TABLES",
630        "TABLESPACE",
631        "TABLE_CHECKSUM",
632        "TABLE_NAME",
633        "TEMPORARY",
634        "TEMPTABLE",
635        "TERMINATED",
636        "TEXT",
637        "THAN",
638        "THEN",
639        "TIME",
640        "TIMESTAMP",
641        "TIMESTAMPADD",
642        "TIMESTAMPDIFF",
643        "TINYBLOB",
644        "TINYINT",
645        "TINYTEXT",
646        "TO",
647        "TRAILING",
648        "TRANSACTION",
649        "TRIGGER",
650        "TRIGGERS",
651        "TRUE",
652        "TRUNCATE",
653        "TYPE",
654        "TYPES",
655        "UNCOMMITTED",
656        "UNDEFINED",
657        "UNDO",
658        "UNDOFILE",
659        "UNDO_BUFFER_SIZE",
660        "UNICODE",
661        "UNINSTALL",
662        "UNION",
663        "UNIQUE",
664        "UNKNOWN",
665        "UNLOCK",
666        "UNSIGNED",
667        "UNTIL",
668        "UPDATE",
669        "UPGRADE",
670        "USAGE",
671        "USE",
672        "USER",
673        "USER_RESOURCES",
674        "USE_FRM",
675        "USING",
676        "UTC_DATE",
677        "UTC_TIME",
678        "UTC_TIMESTAMP",
679        "VALIDATION",
680        "VALUE",
681        "VALUES",
682        "VARBINARY",
683        "VARCHAR",
684        "VARCHARACTER",
685        "VARIABLES",
686        "VARYING",
687        "VIEW",
688        "VIRTUAL",
689        "WAIT",
690        "WARNINGS",
691        "WEEK",
692        "WEIGHT_STRING",
693        "WHEN",
694        "WHERE",
695        "WHILE",
696        "WITH",
697        "WITHOUT",
698        "WORK",
699        "WRAPPER",
700        "WRITE",
701        "X509",
702        "XA",
703        "XID",
704        "XML",
705        "XOR",
706        "YEAR",
707        "YEAR_MONTH",
708        "ZEROFILL",
709    );
710    /**
711     * @var array
712     */
713    protected $reserved_postgres = array(
714        "ALL",
715        "ANALYSE",
716        "ANALYZE",
717        "AND",
718        "ANY",
719        "ARRAY",
720        "AS",
721        "ASC",
722        "ASYMMETRIC",
723        "AUTHORIZATION",
724        "BETWEEN",
725        "BINARY",
726        "BOTH",
727        "CASE",
728        "CAST",
729        "CHECK",
730        "COLLATE",
731        "COLUMN",
732        "CONSTRAINT",
733        "CREATE",
734        "CROSS",
735        "CURRENT_DATE",
736        "CURRENT_ROLE",
737        "CURRENT_TIME",
738        "CURRENT_TIMESTAMP",
739        "CURRENT_USER",
740        "DEFAULT",
741        "DEFERRABLE",
742        "DESC",
743        "DISTINCT",
744        "DO",
745        "ELSE",
746        "END",
747        "EXCEPT",
748        "FALSE",
749        "FOR",
750        "FOREIGN",
751        "FREEZE",
752        "FROM",
753        "FULL",
754        "GRANT",
755        "GROUP",
756        "HAVING",
757        "ILIKE",
758        "IN",
759        "INITIALLY",
760        "INNER",
761        "INTERSECT",
762        "INTO",
763        "IS",
764        "ISNULL",
765        "JOIN",
766        "LEADING",
767        "LEFT",
768        "LIKE",
769        "LIMIT",
770        "LOCALTIME",
771        "LOCALTIMESTAMP",
772        "NATURAL",
773        "NEW",
774        "NOT",
775        "NOTNULL",
776        "NULL",
777        "OFF",
778        "OFFSET",
779        "OLD",
780        "ON",
781        "ONLY",
782        "OR",
783        "ORDER",
784        "OUTER",
785        "OVERLAPS",
786        "PLACING",
787        "PRIMARY",
788        "REFERENCES",
789        "RETURNING",
790        "RIGHT",
791        "SELECT",
792        "SESSION_USER",
793        "SIMILAR",
794        "SOME",
795        "SYMMETRIC",
796        "TABLE",
797        "THEN",
798        "TO",
799        "TRAILING",
800        "TRUE",
801        "UNION",
802        "UNIQUE",
803        "USER",
804        "USING",
805        "VERBOSE",
806        "WHEN",
807        "WHERE",
808        "WITH",
809    );
810    /**
811     * @var
812     */
813    protected $query_utils;
814
815
816    /**
817     * ilDBPdoFieldDefinition constructor.
818     *
819     * @param \ilDBInterface $ilDBInterface
820     */
821    public function __construct(\ilDBInterface $ilDBInterface)
822    {
823        $this->db_instance = $ilDBInterface;
824    }
825
826
827    /**
828     * @return \ilMySQLQueryUtils
829     */
830    protected function getQueryUtils()
831    {
832        if (!$this->query_utils) {
833            $this->query_utils = new ilMySQLQueryUtils($this->db_instance);
834        }
835
836        return $this->query_utils;
837    }
838
839
840    /**
841     * @var array
842     */
843    protected $valid_default_values = array(
844        'text' => '',
845        'boolean' => true,
846        'integer' => 0,
847        'decimal' => 0.0,
848        'float' => 0.0,
849        'timestamp' => '1970-01-01 00:00:00',
850        'time' => '00:00:00',
851        'date' => '1970-01-01',
852        'clob' => '',
853        'blob' => '',
854    );
855
856
857    /**
858     * @param $table_name
859     * @return bool
860     * @throws \ilDatabaseException
861     */
862    public function checkTableName($table_name)
863    {
864        if (!preg_match(self::DEFINITION_TABLE_NAME, $table_name)) {
865            throw new ilDatabaseException('Table name must only contain _a-z0-9 and must start with a-z.');
866        }
867
868        if ($this->isReserved($table_name)) {
869            throw new ilDatabaseException("Invalid table name '" . $table_name . "' (Reserved Word).");
870        }
871
872        if (strtolower(substr($table_name, 0, 4)) == "sys_") {
873            throw new ilDatabaseException("Invalid table name '" . $table_name . "'. Name must not start with 'sys_'.");
874        }
875
876        if (strlen($table_name) > 22) {
877            throw new ilDatabaseException("Invalid table name '" . $table_name . "'. Maximum table identifer length is 22 bytes.");
878        }
879
880        return true;
881    }
882
883
884    /**
885     * @param $table_name
886     * @return bool
887     */
888    public function isReserved($table_name)
889    {
890        return false;
891    }
892
893
894    /**
895     * @return array
896     */
897    public function getAllReserved()
898    {
899        return array_merge($this->getReservedMysql(), $this->getReservedPostgres());
900    }
901
902
903    /**
904     * @return array
905     */
906    public function getReservedMysql()
907    {
908        return $this->reserved_mysql;
909    }
910
911
912    /**
913     * @param array $reserved_mysql
914     */
915    public function setReservedMysql($reserved_mysql)
916    {
917        $this->reserved_mysql = $reserved_mysql;
918    }
919
920
921    /**
922     * @return array
923     */
924    public function getReservedPostgres()
925    {
926        return $this->reserved_postgres;
927    }
928
929
930    /**
931     * @param array $reserved_postgres
932     */
933    public function setReservedPostgres($reserved_postgres)
934    {
935        $this->reserved_postgres = $reserved_postgres;
936    }
937
938
939    /**
940     * @param $column_name
941     * @return bool
942     * @throws \ilDatabaseException
943     */
944    public function checkColumnName($column_name)
945    {
946        if (!preg_match("/^[a-z]+[_a-z0-9]*$/", $column_name)) {
947            throw new ilDatabaseException("Invalid column name '" . $column_name
948                                          . "'. Column name must only contain _a-z0-9 and must start with a-z.");
949        }
950
951        if ($this->isReserved($column_name)) {
952            throw new ilDatabaseException("Invalid column name '" . $column_name . "' (Reserved Word).");
953        }
954
955        if (strtolower(substr($column_name, 0, 4)) == "sys_") {
956            throw new ilDatabaseException("Invalid column name '" . $column_name . "'. Name must not start with 'sys_'.");
957        }
958
959        if (strlen($column_name) > 30) {
960            throw new ilDatabaseException("Invalid column name '" . $column_name . "'. Maximum column identifer length is 30 bytes.");
961        }
962
963        return true;
964    }
965
966
967    /**
968     * @param $a_name
969     * @return bool
970     * @throws \ilDatabaseException
971     */
972    public function checkIndexName($a_name)
973    {
974        if (!preg_match("/^[a-z]+[_a-z0-9]*$/", $a_name)) {
975            throw new ilDatabaseException("Invalid column name '" . $a_name . "'. Column name must only contain _a-z0-9 and must start with a-z.");
976        }
977
978        if ($this->isReserved($a_name)) {
979            throw new ilDatabaseException("Invalid column name '" . $a_name . "' (Reserved Word).");
980        }
981
982        if (strlen($a_name) > 3) {
983            throw new ilDatabaseException("Invalid index name '" . $a_name . "'. Maximum index identifer length is 3 bytes.");
984        }
985
986        return true;
987    }
988
989
990    /**
991     * @param $a_def
992     * @return bool
993     * @throws \ilDatabaseException
994     */
995    public function checkColumnDefinition($a_def)
996    {
997        // check valid type
998        if (!in_array($a_def["type"], $this->getAvailableTypes())) {
999            switch ($a_def["type"]) {
1000                case "boolean":
1001                    throw new ilDatabaseException("Invalid column type '" . $a_def["type"] . "'. Use integer(1) instead.");
1002                    break;
1003
1004                case "decimal":
1005                    throw new ilDatabaseException("Invalid column type '" . $a_def["type"] . "'. Use float or integer instead.");
1006                    break;
1007
1008                default:
1009                    throw new ilDatabaseException("Invalid column type '" . $a_def["type"] . "'. Allowed types are: "
1010                                                  . implode(', ', $this->getAvailableTypes()));
1011            }
1012        }
1013
1014        // check used attributes
1015        $allowed_attributes = $this->getAllowedAttributes();
1016        foreach ($a_def as $k => $v) {
1017            if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]])) {
1018                throw new ilDatabaseException("Attribute '" . $k . "' is not allowed for column type '" . $a_def["type"] . "'.");
1019            }
1020        }
1021
1022        // type specific checks
1023        $max_length = $this->getMaxLength();
1024        switch ($a_def["type"]) {
1025            case self::T_TEXT:
1026                if (!isset($a_def["length"]) || $a_def["length"] < 1 || $a_def["length"] > $max_length[self::T_TEXT]) {
1027                    if (isset($a_def["length"])) {
1028                        throw new ilDatabaseException("Invalid length '" . $a_def["length"] . "' for type text." . " Length must be >=1 and <= "
1029                                                      . $max_length[self::T_TEXT] . ".");
1030                    }
1031                }
1032                break;
1033
1034            case self::T_INTEGER:
1035                if (isset($a_def["length"]) && !in_array($a_def["length"], $max_length[self::T_INTEGER])) {
1036                    throw new ilDatabaseException("Invalid length '" . $a_def["length"] . "' for type integer." . " Length must be "
1037                                                      . implode(', ', $max_length[self::T_INTEGER]) . " (bytes).");
1038                }
1039                if ($a_def["unsigned"] ?? null) {
1040                    throw new ilDatabaseException("Unsigned attribut must not be true for type integer.");
1041                }
1042                break;
1043        }
1044
1045        return true;
1046    }
1047
1048
1049    /**
1050     * @param $attribute
1051     * @param $type
1052     * @return bool
1053     */
1054    public function isAllowedAttribute($attribute, $type)
1055    {
1056        return in_array($attribute, $this->allowed_attributes[$type]);
1057    }
1058
1059
1060    /**
1061     * @return array
1062     */
1063    public function getAvailableTypes()
1064    {
1065        return $this->available_types;
1066    }
1067
1068
1069    /**
1070     * @param array $available_types
1071     */
1072    public function setAvailableTypes($available_types)
1073    {
1074        $this->available_types = $available_types;
1075    }
1076
1077
1078    /**
1079     * @return array
1080     */
1081    public function getAllowedAttributes()
1082    {
1083        return $this->allowed_attributes;
1084    }
1085
1086
1087    /**
1088     * @param array $allowed_attributes
1089     */
1090    public function setAllowedAttributes($allowed_attributes)
1091    {
1092        $this->allowed_attributes = $allowed_attributes;
1093    }
1094
1095
1096    /**
1097     * @return array
1098     */
1099    public function getMaxLength()
1100    {
1101        return $this->max_length;
1102    }
1103
1104
1105    /**
1106     * @param array $max_length
1107     */
1108    public function setMaxLength($max_length)
1109    {
1110        $this->max_length = $max_length;
1111    }
1112
1113
1114    /**
1115     * @return \ilDBPdo
1116     */
1117    protected function getDBInstance()
1118    {
1119        return $this->db_instance;
1120    }
1121
1122
1123    /**
1124     * @return array
1125     */
1126    public function getValidTypes()
1127    {
1128        $types = $this->valid_default_values;
1129        $db = $this->getDBInstance();
1130
1131        if (!empty($db->options['datatype_map'])) {
1132            foreach ($db->options['datatype_map'] as $type => $mapped_type) {
1133                if (array_key_exists($mapped_type, $types)) {
1134                    $types[$type] = $types[$mapped_type];
1135                } elseif (!empty($db->options['datatype_map_callback'][$type])) {
1136                    $parameter = array( 'type' => $type, 'mapped_type' => $mapped_type );
1137                    $default = call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
1138                    $types[$type] = $default;
1139                }
1140            }
1141        }
1142
1143        return $types;
1144    }
1145
1146
1147    /**
1148     * @param $types
1149     * @return array|\ilDBInterface
1150     * @throws \ilDatabaseException
1151     */
1152    protected function checkResultTypes($types)
1153    {
1154        $types = is_array($types) ? $types : array( $types );
1155        foreach ($types as $key => $type) {
1156            if (!isset($this->valid_default_values[$type])) {
1157                $db = $this->getDBInstance();
1158                if (empty($db->options['datatype_map'][$type])) {
1159                    throw new ilDatabaseException($type . ' for ' . $key . ' is not a supported column type');
1160                }
1161            }
1162        }
1163
1164        return $types;
1165    }
1166
1167
1168    /**
1169     * @param $value
1170     * @param $type
1171     * @param bool $rtrim
1172     * @return bool|float|int|resource|string
1173     * @throws \ilDatabaseException
1174     */
1175    protected function baseConvertResult($value, $type, $rtrim = true)
1176    {
1177        throw new ilDatabaseException("deprecated");
1178        switch ($type) {
1179            case 'text':
1180                if ($rtrim) {
1181                    $value = rtrim($value);
1182                }
1183
1184                return $value;
1185            case 'integer':
1186                return intval($value);
1187            case 'boolean':
1188                return !empty($value);
1189            case 'decimal':
1190                return $value;
1191            case 'float':
1192                return doubleval($value);
1193            case 'date':
1194                return $value;
1195            case 'time':
1196                return $value;
1197            case 'timestamp':
1198                return $value;
1199            case 'clob':
1200            case 'blob':
1201                $this->lobs[] = array(
1202                    'buffer' => null,
1203                    'position' => 0,
1204                    'lob_index' => null,
1205                    'endOfLOB' => false,
1206                    'resource' => $value,
1207                    'value' => null,
1208                    'loaded' => false,
1209                );
1210                end($this->lobs);
1211                $lob_index = key($this->lobs);
1212                $this->lobs[$lob_index]['lob_index'] = $lob_index;
1213
1214                return fopen('MDB2LOB://' . $lob_index . '@' . $this->db_index, 'r+');
1215        }
1216
1217        throw new ilDatabaseException('attempt to convert result value to an unknown type :' . $type);
1218    }
1219
1220
1221    /**
1222     * @param $value
1223     * @param $type
1224     * @param bool $rtrim
1225     * @return bool|float|int|mixed|null|resource|string
1226     * @throws \ilDatabaseException
1227     */
1228    public function convertResult($value, $type, $rtrim = true)
1229    {
1230        throw new ilDatabaseException("deprecated");
1231        if (is_null($value)) {
1232            return null;
1233        }
1234        $db = $this->getDBInstance();
1235
1236        if (!empty($db->options['datatype_map'][$type])) {
1237            $type = $db->options['datatype_map'][$type];
1238            if (!empty($db->options['datatype_map_callback'][$type])) {
1239                $parameter = array( 'type' => $type, 'value' => $value, 'rtrim' => $rtrim );
1240
1241                return call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
1242            }
1243        }
1244
1245        return $this->baseConvertResult($value, $type, $rtrim);
1246    }
1247
1248
1249    /**
1250     * @param $types
1251     * @param $row
1252     * @param bool $rtrim
1253     * @return bool|float|int|mixed|null|resource|string
1254     */
1255    public function convertResultRow($types, $row, $rtrim = true)
1256    {
1257        throw new ilDatabaseException("deprecated");
1258        $types = $this->sortResultFieldTypes(array_keys($row), $types);
1259        foreach ($row as $key => $value) {
1260            if (empty($types[$key])) {
1261                continue;
1262            }
1263            $value = $this->convertResult($row[$key], $types[$key], $rtrim);
1264
1265            $row[$key] = $value;
1266        }
1267
1268        return $row;
1269    }
1270
1271    // }}}
1272    // {{{ _sortResultFieldTypes()
1273
1274    /**
1275     * @param $columns
1276     * @param $types
1277     * @return array
1278     */
1279    protected function sortResultFieldTypes($columns, $types)
1280    {
1281        $n_cols = count($columns);
1282        $n_types = count($types);
1283        if ($n_cols > $n_types) {
1284            for ($i = $n_cols - $n_types; $i >= 0; $i--) {
1285                $types[] = null;
1286            }
1287        }
1288        $sorted_types = array();
1289        foreach ($columns as $col) {
1290            $sorted_types[$col] = null;
1291        }
1292        foreach ($types as $name => $type) {
1293            if (array_key_exists($name, $sorted_types)) {
1294                $sorted_types[$name] = $type;
1295                unset($types[$name]);
1296            }
1297        }
1298        // if there are left types in the array, fill the null values of the
1299        // sorted array with them, in order.
1300        if (count($types)) {
1301            reset($types);
1302            foreach (array_keys($sorted_types) as $k) {
1303                if (is_null($sorted_types[$k])) {
1304                    $sorted_types[$k] = current($types);
1305                    next($types);
1306                }
1307            }
1308        }
1309
1310        return $sorted_types;
1311    }
1312
1313
1314    /**
1315     * @param $type
1316     * @param $name
1317     * @param $field
1318     * @return \ilDBInterface|mixed
1319     * @throws \ilDatabaseException
1320     */
1321    public function getDeclaration($type, $name, $field)
1322    {
1323        $db = $this->getDBInstance();
1324
1325        if (!empty($db->options['datatype_map'][$type])) {
1326            $type = $db->options['datatype_map'][$type];
1327            if (!empty($db->options['datatype_map_callback'][$type])) {
1328                $parameter = array( 'type' => $type, 'name' => $name, 'field' => $field );
1329
1330                return call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
1331            }
1332            $field['type'] = $type;
1333        }
1334
1335        if (!method_exists($this, "get{$type}Declaration")) {
1336            throw new ilDatabaseException('type not defined: ' . $type);
1337        }
1338
1339        return $this->{"get{$type}Declaration"}($name, $field);
1340    }
1341
1342
1343    /**
1344     * @param $field
1345     * @return \ilDBInterface|string
1346     */
1347    public function getTypeDeclaration($field)
1348    {
1349        $db = $this->getDBInstance();
1350
1351        switch ($field['type']) {
1352            case 'text':
1353                $length = !empty($field['length']) ? $field['length'] : $db->options['default_text_field_length'];
1354                $fixed = !empty($field['fixed']) ? $field['fixed'] : false;
1355
1356                return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(' . $db->options['default_text_field_length']
1357                                                                     . ')') : ($length ? 'VARCHAR(' . $length . ')' : 'TEXT');
1358            case 'clob':
1359                return 'TEXT';
1360            case 'blob':
1361                return 'TEXT';
1362            case 'integer':
1363                return 'INT';
1364            case 'boolean':
1365                return 'INT';
1366            case 'date':
1367                return 'CHAR (' . strlen('YYYY-MM-DD') . ')';
1368            case 'time':
1369                return 'CHAR (' . strlen('HH:MM:SS') . ')';
1370            case 'timestamp':
1371                return 'CHAR (' . strlen('YYYY-MM-DD HH:MM:SS') . ')';
1372            case 'float':
1373                return 'TEXT';
1374            case 'decimal':
1375                return 'TEXT';
1376        }
1377
1378        return '';
1379    }
1380
1381
1382    /**
1383     * @param $name
1384     * @param $field
1385     * @return \ilDBInterface|string
1386     */
1387    protected function getInternalDeclaration($name, $field)
1388    {
1389        $db = $this->getDBInstance();
1390
1391        $name = $db->quoteIdentifier($name, true);
1392        $declaration_options = $db->getFieldDefinition()->getDeclarationOptions($field);
1393
1394        return $name . ' ' . $this->getTypeDeclaration($field) . $declaration_options;
1395    }
1396
1397
1398    /**
1399     * @param $field
1400     * @return \ilDBPdo|string
1401     * @throws \ilDatabaseException
1402     */
1403    protected function getDeclarationOptions($field)
1404    {
1405        $charset = empty($field['charset']) ? '' : ' ' . $this->getCharsetFieldDeclaration($field['charset']);
1406
1407        $default = '';
1408        if (array_key_exists('default', $field)) {
1409            if ($field['default'] === '') {
1410                $db = $this->getDBInstance();
1411
1412                if (empty($field['notnull'])) {
1413                    $field['default'] = null;
1414                } else {
1415                    $valid_default_values = $this->getValidTypes();
1416                    $field['default'] = $valid_default_values[$field['type']];
1417                }
1418                if ($field['default'] === ''
1419                    && isset($db->options["portability"])
1420                    && ($db->options['portability'] & 32)
1421                ) {
1422                    $field['default'] = ' ';
1423                }
1424            }
1425            $default = ' DEFAULT ' . $this->quote($field['default'], $field['type']);
1426        } elseif (empty($field['notnull'])) {
1427            $default = ' DEFAULT NULL';
1428        }
1429
1430        $notnull = empty($field['notnull']) ? '' : ' NOT NULL';
1431        // alex patch 28 Nov 2011 start
1432        if (isset($field["notnull"]) && $field['notnull'] === false) {
1433            $notnull = " NULL";
1434        }
1435        // alex patch 28 Nov 2011 end
1436
1437        $collation = empty($field['collation']) ? '' : ' ' . $this->getCollationFieldDeclaration($field['collation']);
1438
1439        return $charset . $default . $notnull . $collation;
1440    }
1441
1442
1443    /**
1444     * @param $charset
1445     * @return string
1446     */
1447    protected function getCharsetFieldDeclaration($charset)
1448    {
1449        return '';
1450    }
1451
1452
1453    /**
1454     * @param $collation
1455     * @return string
1456     */
1457    protected function getCollationFieldDeclaration($collation)
1458    {
1459        return '';
1460    }
1461
1462
1463    /**
1464     * @param $name
1465     * @param $field
1466     * @return \ilDBInterface|\ilDBPdo|mixed
1467     * @throws \ilDatabaseException
1468     */
1469    protected function getIntegerDeclaration($name, $field)
1470    {
1471        if (!empty($field['unsigned'])) {
1472            $db = $this->getDBInstance();
1473
1474            $db->warnings[] = "unsigned integer field \"$name\" is being declared as signed integer";
1475        }
1476
1477        return $this->getInternalDeclaration($name, $field);
1478    }
1479
1480
1481    /**
1482     * @param $name
1483     * @param $field
1484     * @return \ilDBInterface|mixed
1485     * @throws \ilDatabaseException
1486     */
1487    protected function getTextDeclaration($name, $field)
1488    {
1489        return $this->getInternalDeclaration($name, $field);
1490    }
1491
1492
1493    /**
1494     * @param $name
1495     * @param $field
1496     * @return \ilDBPdo|string
1497     */
1498    protected function getCLOBDeclaration($name, $field)
1499    {
1500        $db = $this->getDBInstance();
1501
1502        $notnull = empty($field['notnull']) ? '' : ' NOT NULL';
1503        $name = $db->quoteIdentifier($name, true);
1504
1505        return $name . ' ' . $this->getTypeDeclaration($field) . $notnull;
1506    }
1507
1508
1509    /**
1510     * @param $name
1511     * @param $field
1512     * @return \ilDBPdo|string
1513     */
1514    protected function getBLOBDeclaration($name, $field)
1515    {
1516        $db = $this->getDBInstance();
1517
1518        $notnull = empty($field['notnull']) ? '' : ' NOT NULL';
1519        $name = $db->quoteIdentifier($name, true);
1520
1521        return $name . ' ' . $this->getTypeDeclaration($field) . $notnull;
1522    }
1523
1524
1525    /**
1526     * @param $name
1527     * @param $field
1528     * @return \ilDBInterface|string
1529     */
1530    protected function getBooleanDeclaration($name, $field)
1531    {
1532        return $this->getInternalDeclaration($name, $field);
1533    }
1534
1535
1536    /**
1537     * @param $name
1538     * @param $field
1539     * @return \ilDBInterface|string
1540     */
1541    protected function getDateDeclaration($name, $field)
1542    {
1543        return $this->getInternalDeclaration($name, $field);
1544    }
1545
1546
1547    /**
1548     * @param $name
1549     * @param $field
1550     * @return \ilDBInterface|string
1551     */
1552    protected function getTimestampDeclaration($name, $field)
1553    {
1554        return $this->getInternalDeclaration($name, $field);
1555    }
1556
1557
1558    /**
1559     * @param $name
1560     * @param $field
1561     * @return \ilDBInterface|string
1562     */
1563    protected function getTimeDeclaration($name, $field)
1564    {
1565        return $this->getInternalDeclaration($name, $field);
1566    }
1567
1568
1569    /**
1570     * @param $name
1571     * @param $field
1572     * @return \ilDBInterface|string
1573     */
1574    protected function getFloatDeclaration($name, $field)
1575    {
1576        return $this->getInternalDeclaration($name, $field);
1577    }
1578
1579
1580    /**
1581     * @param $name
1582     * @param $field
1583     * @return \ilDBInterface|string
1584     */
1585    protected function getDecimalDeclaration($name, $field)
1586    {
1587        return $this->getInternalDeclaration($name, $field);
1588    }
1589
1590
1591    /**
1592     * @param $current
1593     * @param $previous
1594     * @return \ilDBPdo|mixed
1595     * @throws \ilDatabaseException
1596     */
1597    public function compareDefinition($current, $previous)
1598    {
1599        $type = !empty($current['type']) ? $current['type'] : null;
1600
1601        if (!method_exists($this, "compare{$type}Definition")) {
1602            $db = $this->getDBInstance();
1603
1604            if (!empty($db->options['datatype_map_callback'][$type])) {
1605                $parameter = array( 'current' => $current, 'previous' => $previous );
1606                $change = call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
1607
1608                return $change;
1609            }
1610
1611            throw new ilDatabaseException('type "' . $current['type'] . '" is not yet supported');
1612        }
1613
1614        if (empty($previous['type']) || $previous['type'] != $type) {
1615            return $current;
1616        }
1617
1618        $change = $this->{"compare{$type}Definition"}($current, $previous);
1619
1620        if ($previous['type'] != $type) {
1621            $change['type'] = true;
1622        }
1623
1624        $previous_notnull = !empty($previous['notnull']) ? $previous['notnull'] : false;
1625        $notnull = !empty($current['notnull']) ? $current['notnull'] : false;
1626        if ($previous_notnull != $notnull) {
1627            $change['notnull'] = true;
1628        }
1629
1630        $previous_default = array_key_exists('default', $previous) ? $previous['default'] : ($previous_notnull ? '' : null);
1631        $default = array_key_exists('default', $current) ? $current['default'] : ($notnull ? '' : null);
1632        if ($previous_default !== $default) {
1633            $change['default'] = true;
1634        }
1635
1636        return $change;
1637    }
1638
1639
1640    /**
1641     * @param $current
1642     * @param $previous
1643     * @return array
1644     */
1645    protected function compareIntegerDefinition($current, $previous)
1646    {
1647        $change = array();
1648        $previous_unsigned = !empty($previous['unsigned']) ? $previous['unsigned'] : false;
1649        $unsigned = !empty($current['unsigned']) ? $current['unsigned'] : false;
1650        if ($previous_unsigned != $unsigned) {
1651            $change['unsigned'] = true;
1652        }
1653        $previous_autoincrement = !empty($previous['autoincrement']) ? $previous['autoincrement'] : false;
1654        $autoincrement = !empty($current['autoincrement']) ? $current['autoincrement'] : false;
1655        if ($previous_autoincrement != $autoincrement) {
1656            $change['autoincrement'] = true;
1657        }
1658
1659        return $change;
1660    }
1661
1662
1663    /**
1664     * @param $current
1665     * @param $previous
1666     * @return array
1667     */
1668    protected function compareTextDefinition($current, $previous)
1669    {
1670        $change = array();
1671        $previous_length = !empty($previous['length']) ? $previous['length'] : 0;
1672        $length = !empty($current['length']) ? $current['length'] : 0;
1673        if ($previous_length != $length) {
1674            $change['length'] = true;
1675        }
1676        $previous_fixed = !empty($previous['fixed']) ? $previous['fixed'] : 0;
1677        $fixed = !empty($current['fixed']) ? $current['fixed'] : 0;
1678        if ($previous_fixed != $fixed) {
1679            $change['fixed'] = true;
1680        }
1681
1682        return $change;
1683    }
1684
1685
1686    /**
1687     * @param $current
1688     * @param $previous
1689     * @return array
1690     */
1691    protected function compareCLOBDefinition($current, $previous)
1692    {
1693        return $this->compareTextDefinition($current, $previous);
1694    }
1695
1696
1697    /**
1698     * @param $current
1699     * @param $previous
1700     * @return array
1701     */
1702    protected function compareBLOBDefinition($current, $previous)
1703    {
1704        return $this->compareTextDefinition($current, $previous);
1705    }
1706
1707
1708    /**
1709     * @param $current
1710     * @param $previous
1711     * @return array
1712     */
1713    protected function compareDateDefinition($current, $previous)
1714    {
1715        return array();
1716    }
1717
1718
1719    /**
1720     * @param $current
1721     * @param $previous
1722     * @return array
1723     */
1724    protected function compareTimeDefinition($current, $previous)
1725    {
1726        return array();
1727    }
1728
1729
1730    /**
1731     * @param $current
1732     * @param $previous
1733     * @return array
1734     */
1735    protected function compareTimestampDefinition($current, $previous)
1736    {
1737        return array();
1738    }
1739
1740
1741    /**
1742     * @param $current
1743     * @param $previous
1744     * @return array
1745     */
1746    protected function compareBooleanDefinition($current, $previous)
1747    {
1748        return array();
1749    }
1750
1751
1752    /**
1753     * @param $current
1754     * @param $previous
1755     * @return array
1756     */
1757    protected function compareFloatDefinition($current, $previous)
1758    {
1759        return array();
1760    }
1761
1762
1763    /**
1764     * @param $current
1765     * @param $previous
1766     * @return array
1767     */
1768    protected function compareDecimalDefinition($current, $previous)
1769    {
1770        return array();
1771    }
1772
1773
1774    /**
1775     * @param $value
1776     * @param null $type
1777     * @param bool $quote
1778     * @param bool $escape_wildcards
1779     * @return \ilDBPdo|mixed|string
1780     * @throws \ilDatabaseException
1781     */
1782    public function quote($value, $type = null, $quote = true, $escape_wildcards = false)
1783    {
1784        $db = $this->getDBInstance();
1785
1786        return $db->quote($value, $type);
1787
1788        if (is_null($value)
1789            || ($value === '' && $db->options['portability'])
1790        ) {
1791            if (!$quote) {
1792                return null;
1793            }
1794
1795            return 'NULL';
1796        }
1797
1798        if (is_null($type)) {
1799            switch (gettype($value)) {
1800                case 'integer':
1801                    $type = 'integer';
1802                    break;
1803                case 'double':
1804                    // todo: default to decimal as float is quite unusual
1805                    // $type = 'float';
1806                    $type = 'decimal';
1807                    break;
1808                case 'boolean':
1809                    $type = 'boolean';
1810                    break;
1811                case 'array':
1812                    $value = serialize($value);
1813                    // no break
1814                case 'object':
1815                    $type = 'text';
1816                    break;
1817                default:
1818                    if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$/', $value)) {
1819                        $type = 'timestamp';
1820                    } elseif (preg_match('/^\d{2}:\d{2}$/', $value)) {
1821                        $type = 'time';
1822                    } elseif (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
1823                        $type = 'date';
1824                    } else {
1825                        $type = 'text';
1826                    }
1827                    break;
1828            }
1829        } elseif (!empty($db->options['datatype_map'][$type])) {
1830            $type = $db->options['datatype_map'][$type];
1831            if (!empty($db->options['datatype_map_callback'][$type])) {
1832                $parameter = array( 'type' => $type, 'value' => $value, 'quote' => $quote, 'escape_wildcards' => $escape_wildcards );
1833
1834                return call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
1835            }
1836        }
1837
1838        if (!method_exists($this, "quote{$type}")) {
1839            throw new ilDatabaseException('type not defined: ' . $type);
1840        }
1841        $value = $this->{"quote{$type}"}($value, $quote, $escape_wildcards);
1842        if ($quote && $escape_wildcards && $db->string_quoting['escape_pattern']
1843            && $db->string_quoting['escape'] !== $db->string_quoting['escape_pattern']
1844        ) {
1845            $value .= $this->patternEscapeString();
1846        }
1847
1848        return $value;
1849    }
1850
1851
1852    /**
1853     * @param $value
1854     * @param $quote
1855     * @param $escape_wildcards
1856     * @return int
1857     */
1858    protected function quoteInteger($value, $quote, $escape_wildcards)
1859    {
1860        return (int) $value;
1861    }
1862
1863
1864    /**
1865     * @param $value
1866     * @param $quote
1867     * @param $escape_wildcards
1868     * @return \ilDBPdo|string
1869     */
1870    protected function quoteText($value, $quote, $escape_wildcards)
1871    {
1872        if (!$quote) {
1873            return $value;
1874        }
1875
1876        $db = $this->getDBInstance();
1877
1878        $value = $db->escape($value, $escape_wildcards);
1879
1880        return "'" . $value . "'";
1881    }
1882
1883
1884    /**
1885     * @param $value
1886     * @return \ilDBPdo|string
1887     */
1888    protected function readFile($value)
1889    {
1890        $close = false;
1891        if (preg_match('/^(\w+:\/\/)(.*)$/', $value, $match)) {
1892            $close = true;
1893            if ($match[1] == 'file://') {
1894                $value = $match[2];
1895            }
1896            // do not try to open urls
1897            #$value = @fopen($value, 'r');
1898        }
1899
1900        if (is_resource($value)) {
1901            $db = $this->getDBInstance();
1902
1903            $fp = $value;
1904            $value = '';
1905            while (!@feof($fp)) {
1906                $value .= @fread($fp, $db->options['lob_buffer_length']);
1907            }
1908            if ($close) {
1909                @fclose($fp);
1910            }
1911        }
1912
1913        return $value;
1914    }
1915
1916
1917    /**
1918     * @param $value
1919     * @param $quote
1920     * @param $escape_wildcards
1921     * @return \ilDBPdo|string
1922     */
1923    protected function quoteLOB($value, $quote, $escape_wildcards)
1924    {
1925        $value = $this->readFile($value);
1926
1927        return $this->quoteText($value, $quote, $escape_wildcards);
1928    }
1929
1930
1931    /**
1932     * @param $value
1933     * @param $quote
1934     * @param $escape_wildcards
1935     * @return \ilDBPdo|string
1936     */
1937    protected function quoteCLOB($value, $quote, $escape_wildcards)
1938    {
1939        return $this->quoteLOB($value, $quote, $escape_wildcards);
1940    }
1941
1942
1943    /**
1944     * @param $value
1945     * @param $quote
1946     * @param $escape_wildcards
1947     * @return \ilDBPdo|string
1948     */
1949    protected function quoteBLOB($value, $quote, $escape_wildcards)
1950    {
1951        return $this->quoteLOB($value, $quote, $escape_wildcards);
1952    }
1953
1954
1955    /**
1956     * @param $value
1957     * @param $quote
1958     * @param $escape_wildcards
1959     * @return int
1960     */
1961    protected function quoteBoolean($value, $quote, $escape_wildcards)
1962    {
1963        return ($value ? 1 : 0);
1964    }
1965
1966
1967    /**
1968     * @param $value
1969     * @param $quote
1970     * @param $escape_wildcards
1971     * @return \ilDBPdo|string
1972     */
1973    protected function quoteDate($value, $quote, $escape_wildcards)
1974    {
1975        if ($value === 'CURRENT_DATE') {
1976            $db = $this->getDBInstance();
1977
1978            return 'CURRENT_DATE';
1979        }
1980
1981        return $this->quoteText($value, $quote, $escape_wildcards);
1982    }
1983
1984
1985    /**
1986     * @param $value
1987     * @param $quote
1988     * @param $escape_wildcards
1989     * @return \ilDBPdo|string
1990     */
1991    protected function quoteTimestamp($value, $quote, $escape_wildcards)
1992    {
1993        throw new ilDatabaseException("deprecated");
1994        if ($value === 'CURRENT_TIMESTAMP') {
1995            $db = $this->getDBInstance();
1996
1997            if (isset($db->function) && is_a($db->function, 'MDB2_Driver_Function_Common')) {
1998                return $db->function->now('timestamp');
1999            }
2000
2001            return 'CURRENT_TIMESTAMP';
2002        }
2003
2004        return $this->quoteText($value, $quote, $escape_wildcards);
2005    }
2006
2007
2008    /**
2009     * @param $value
2010     * @param $quote
2011     * @param $escape_wildcards
2012     * @return \ilDBPdo|string
2013     */
2014    protected function quoteTime($value, $quote, $escape_wildcards)
2015    {
2016        throw new ilDatabaseException("deprecated");
2017        if ($value === 'CURRENT_TIME') {
2018            $db = $this->getDBInstance();
2019
2020            if (isset($db->function) && is_a($db->function, 'MDB2_Driver_Function_Common')) {
2021                return $db->function->now('time');
2022            }
2023
2024            return 'CURRENT_TIME';
2025        }
2026
2027        return $this->quoteText($value, $quote, $escape_wildcards);
2028    }
2029
2030
2031    /**
2032     * @param $value
2033     * @param $quote
2034     * @param $escape_wildcards
2035     * @return string
2036     */
2037    protected function quoteFloat($value, $quote, $escape_wildcards)
2038    {
2039        if (preg_match('/^(.*)e([-+])(\d+)$/i', $value, $matches)) {
2040            $decimal = $this->quoteDecimal($matches[1], $quote, $escape_wildcards);
2041            $sign = $matches[2];
2042            $exponent = str_pad($matches[3], 2, '0', STR_PAD_LEFT);
2043            $value = $decimal . 'E' . $sign . $exponent;
2044        } else {
2045            $value = $this->quoteDecimal($value, $quote, $escape_wildcards);
2046        }
2047
2048        return $value;
2049    }
2050
2051
2052    /**
2053     * @param $value
2054     * @param $quote
2055     * @param $escape_wildcards
2056     * @return mixed|string
2057     */
2058    protected function quoteDecimal($value, $quote, $escape_wildcards)
2059    {
2060        $value = (string) $value;
2061        $value = preg_replace('/[^\d\.,\-+eE]/', '', $value);
2062        if (preg_match('/[^.0-9]/', $value)) {
2063            if (strpos($value, ',')) {
2064                // 1000,00
2065                if (!strpos($value, '.')) {
2066                    // convert the last "," to a "."
2067                    $value = strrev(str_replace(',', '.', strrev($value)));
2068                // 1.000,00
2069                } elseif (strpos($value, '.') && strpos($value, '.') < strpos($value, ',')) {
2070                    $value = str_replace('.', '', $value);
2071                    // convert the last "," to a "."
2072                    $value = strrev(str_replace(',', '.', strrev($value)));
2073                // 1,000.00
2074                } else {
2075                    $value = str_replace(',', '', $value);
2076                }
2077            }
2078        }
2079
2080        return $value;
2081    }
2082
2083
2084    /**
2085     * @param $lob
2086     * @param $file
2087     * @return bool|\ilDBPdo
2088     * @throws \ilDatabaseException
2089     */
2090    public function writeLOBToFile($lob, $file)
2091    {
2092        $db = $this->getDBInstance();
2093
2094        if (preg_match('/^(\w+:\/\/)(.*)$/', $file, $match)) {
2095            if ($match[1] == 'file://') {
2096                $file = $match[2];
2097            }
2098        }
2099
2100        $fp = @fopen($file, 'wb');
2101        while (!@feof($lob)) {
2102            $result = @fread($lob, $db->options['lob_buffer_length']);
2103            $read = strlen($result);
2104            if (@fwrite($fp, $result, $read) != $read) {
2105                @fclose($fp);
2106
2107                throw new ilDatabaseException('could not write to the output file');
2108            }
2109        }
2110        @fclose($fp);
2111
2112        return true;
2113    }
2114
2115
2116    /**
2117     * @param $lob
2118     * @return bool
2119     */
2120    protected function retrieveLOB(&$lob)
2121    {
2122        if (is_null($lob['value'])) {
2123            $lob['value'] = $lob['resource'];
2124        }
2125        $lob['loaded'] = true;
2126
2127        return true;
2128    }
2129
2130
2131    /**
2132     * @param $lob
2133     * @param $length
2134     * @return string
2135     */
2136    protected function readLOB($lob, $length)
2137    {
2138        return substr($lob['value'], $lob['position'], $length);
2139    }
2140
2141
2142    /**
2143     * @param $lob
2144     * @return mixed
2145     */
2146    protected function endOfLOB($lob)
2147    {
2148        return $lob['endOfLOB'];
2149    }
2150
2151
2152    /**
2153     * @param $lob
2154     * @return bool
2155     */
2156    public function destroyLOB($lob)
2157    {
2158        $lob_data = stream_get_meta_data($lob);
2159        $lob_index = $lob_data['wrapper_data']->lob_index;
2160        fclose($lob);
2161        if (isset($this->lobs[$lob_index])) {
2162            $this->destroyLOBInternal($this->lobs[$lob_index]);
2163            unset($this->lobs[$lob_index]);
2164        }
2165
2166        return true;
2167    }
2168
2169
2170    /**
2171     * @param $lob
2172     * @return bool
2173     */
2174    protected function destroyLOBInternal(&$lob)
2175    {
2176        return true;
2177    }
2178
2179
2180    /**
2181     * @param $array
2182     * @param bool $type
2183     * @return string
2184     * @throws \ilDatabaseException
2185     */
2186    public function implodeArray($array, $type = false)
2187    {
2188        if (!is_array($array) || empty($array)) {
2189            return 'NULL';
2190        }
2191        if ($type) {
2192            foreach ($array as $value) {
2193                $return[] = $this->quote($value, $type);
2194            }
2195        } else {
2196            $return = $array;
2197        }
2198
2199        return implode(', ', $return);
2200    }
2201
2202
2203    /**
2204     * @param $pattern
2205     * @param null $operator
2206     * @param null $field
2207     * @return \ilDBPdo|string
2208     * @throws \ilDatabaseException
2209     */
2210    public function matchPattern($pattern, $operator = null, $field = null)
2211    {
2212        $db = $this->getDBInstance();
2213
2214        $match = '';
2215        if (!is_null($operator)) {
2216            $operator = strtoupper($operator);
2217            switch ($operator) {
2218                // case insensitive
2219                case 'ILIKE':
2220                    if (is_null($field)) {
2221                        throw new ilDatabaseException('case insensitive LIKE matching requires passing the field name');
2222                    }
2223                    $db->loadModule('Function', null, true);
2224                    $match = $db->function->lower($field) . ' LIKE ';
2225                    break;
2226                // case sensitive
2227                case 'LIKE':
2228                    $match = is_null($field) ? 'LIKE ' : $field . ' LIKE ';
2229                    break;
2230                default:
2231                    throw new ilDatabaseException('not a supported operator type:' . $operator);
2232            }
2233        }
2234        $match .= "'";
2235        foreach ($pattern as $key => $value) {
2236            if ($key % 2) {
2237                $match .= $value;
2238            } else {
2239                if ($operator === 'ILIKE') {
2240                    $value = strtolower($value);
2241                }
2242                $escaped = $db->escape($value);
2243                $match .= $db->escapePattern($escaped);
2244            }
2245        }
2246        $match .= "'";
2247        $match .= $this->patternEscapeString();
2248
2249        return $match;
2250    }
2251
2252
2253    /**
2254     * @return string
2255     */
2256    public function patternEscapeString()
2257    {
2258        return '';
2259    }
2260
2261
2262    /**
2263     * @param $field
2264     * @return \ilDBPdo|mixed
2265     */
2266    public function mapNativeDatatype($field)
2267    {
2268        $db = $this->getDBInstance();
2269        $db_type = strtok($field['type'], '(), ');
2270        if (!empty($db->options['nativetype_map_callback'][$db_type])) {
2271            return call_user_func_array($db->options['nativetype_map_callback'][$db_type], array( $db, $field ));
2272        }
2273
2274        return $this->mapNativeDatatypeInternal($field);
2275    }
2276
2277
2278    /**
2279     * @param $field
2280     * @return \ilDBPdo
2281     * @throws \ilDatabaseException
2282     */
2283    abstract protected function mapNativeDatatypeInternal($field);
2284
2285
2286    /**
2287     * @param $type
2288     * @return \ilDBPdo|mixed
2289     */
2290    public function mapPrepareDatatype($type)
2291    {
2292        $db = $this->getDBInstance();
2293
2294        if (!empty($db->options['datatype_map'][$type])) {
2295            $type = $db->options['datatype_map'][$type];
2296            if (!empty($db->options['datatype_map_callback'][$type])) {
2297                $parameter = array( 'type' => $type );
2298
2299                return call_user_func_array($db->options['datatype_map_callback'][$type], array( &$db, __FUNCTION__, $parameter ));
2300            }
2301        }
2302
2303        return $type;
2304    }
2305}
2306