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