File modules/database/Column.class.php

Last commit: Sun Oct 1 02:58:08 2023 +0200	Jan Dankert	Refactoring: No it is possible to use 8 byte integers in the database


     1 <?php
     2 
     3 
     4 namespace database;
     5 
     6 
     7 class Column
     8 {
     9 	const TYPE_INT     = 1;
    10 	const TYPE_VARCHAR = 2;
    11 	const TYPE_TEXT    = 3;
    12 	const TYPE_BLOB    = 4;
    13 	const TYPE_BIGINT  = 5;
    14 
    15 	/**
    16 	 * medium INT with 1 byte.
    17 	 */
    18 	const SIZE_INT_BOOL = 1;
    19 	/**
    20 	 * medium INT with 4 bytes.
    21 	 */
    22 	const SIZE_INT_MED = 2;
    23 	/**
    24 	 * medium INT with 8 bytes.
    25 	 */
    26 	const SIZE_INT_BIG = 3;
    27 
    28 	const SIZE_VARCHAR_MAX = 255;
    29 
    30 	private $db;
    31 	private $dbmsType;
    32 	private $name;
    33 	private $table;
    34 
    35 	private $type     = self::TYPE_INT;
    36 	private $charset  = null;
    37 	private $size     = null;
    38 	private $default  = null;
    39 	private $nullable = false;
    40 
    41 	public function type( $type ) {
    42 		$this->type = $type;
    43 		return $this;
    44 	}
    45 
    46 
    47 	public function charset( $charset ) {
    48 		$this->charset = $charset;
    49 		return $this;
    50 	}
    51 
    52 
    53 	public function size( $size ) {
    54 		$this->size = $size;
    55 		return $this;
    56 	}
    57 
    58 	public function defaultValue( $default ) {
    59 		$this->default = $default;
    60 		return $this;
    61 	}
    62 
    63 
    64 	/**
    65 	 * Column is nullable.
    66 	 * Marks the column as nullable, default is not nullable.
    67 	 * @return $this
    68 	 */
    69 	public function nullable() {
    70 		$this->nullable = true;;
    71 		return $this;
    72 	}
    73 
    74 	/**
    75 	 * Creates a column.
    76 	 *
    77 	 * @param $db Database database
    78 	 * @param $type int Type of column
    79 	 * @param $table string table name
    80 	 * @param $name string column name
    81 	 */
    82 	public function __construct($db, $type, $table, $name)
    83 	{
    84 		$this->db     = $db;
    85 		$this->dbmsType = $type;
    86 		$this->table  = $table;
    87 		$this->name   = $name;
    88 	}
    89 
    90 
    91 	/**
    92 	 * Creating the column definition.
    93 	 */
    94 	protected function getColumnDefinition()
    95 	{
    96 		$table = $this->table->getSqlName();
    97 		$size = null;
    98 
    99 		switch ($this->type) {
   100 			case self::TYPE_INT:
   101 				switch ($this->dbmsType) {
   102 					case DbVersion::TYPE_MYSQL:
   103 
   104 						switch ($this->size ) {
   105 							case self::SIZE_INT_BOOL: // small 1 or 2 byte integer
   106 								$dbmsInternalType = 'TINYINT';
   107 								break;
   108 							case self::SIZE_INT_MED:
   109 							case null: // default size is the 4-byte integer
   110 								$dbmsInternalType = 'INT';
   111 								break;
   112 							case self::SIZE_INT_BIG: // 8 byte integer
   113 								$dbmsInternalType = 'BIGINT';
   114 								break;
   115 						}
   116 
   117 						break;
   118 
   119 					case DbVersion::TYPE_POSTGRES:
   120 						switch ( $this->size ) {
   121 
   122 							case self::SIZE_INT_BOOL: // small 1 or 2 byte integer
   123 								$dbmsInternalType = 'SMALLINT';
   124 								break;
   125 							case self::SIZE_INT_MED:
   126 							case null: // default size is the 4-byte integer
   127 								$dbmsInternalType = 'INTEGER';
   128 								break;
   129 							case self::SIZE_INT_BIG: // 8 byte integer
   130 								$dbmsInternalType = 'BIGINT';
   131 								break;
   132 						}
   133 						break;
   134 
   135 					case DbVersion::TYPE_SQLITE:
   136 					default:
   137 						$dbmsInternalType = 'INTEGER';
   138 				}
   139 				break;
   140 
   141 			case self::TYPE_VARCHAR:
   142 				switch ($this->dbmsType) {
   143 					default:
   144 						$dbmsInternalType = 'VARCHAR';
   145 
   146 				}
   147 				$size = $this->size; // char count
   148 
   149 				if   ( ! $size )
   150 					$size = self::SIZE_VARCHAR_MAX;
   151 
   152 				break;
   153 
   154 			case self::TYPE_TEXT:
   155 				switch ($this->dbmsType) {
   156 					case DbVersion::TYPE_MYSQL:
   157 						$dbmsInternalType = 'MEDIUMTEXT';
   158 						break;
   159 
   160 					case DbVersion::TYPE_ORACLE:
   161 						$dbmsInternalType = 'CLOB';
   162 						break;
   163 
   164 					default:
   165 						$dbmsInternalType = 'TEXT';
   166 
   167 				}
   168 				break;
   169 
   170 			case self::TYPE_BLOB:
   171 				switch ($this->dbmsType) {
   172 					case DbVersion::TYPE_MYSQL:
   173 						$dbmsInternalType = 'MEDIUMBLOB';
   174 						break;
   175 
   176 					case DbVersion::TYPE_ORACLE:
   177 						$dbmsInternalType = 'CLOB';
   178 						break;
   179 
   180 					case DbVersion::TYPE_POSTGRES:
   181 					case DbVersion::TYPE_SQLITE:
   182 						$dbmsInternalType = 'TEXT';
   183 						break;
   184 
   185 					default:
   186 						$dbmsInternalType = 'BLOB';
   187 
   188 				}
   189 				break;
   190 			default:
   191 				throw new \LogicException( 'Unknown Column type: ' . $this->type);
   192 		}
   193 
   194 		if ($this->dbmsType == DbVersion::TYPE_ORACLE) {
   195 			// TEXT-columns must be nullable in Oracle, because empty strings are treated as NULL. BAD BAD BAD, Oracle!
   196 			if ($this->type == self::TYPE_VARCHAR || $this->type == self::TYPE_TEXT)
   197 				$nullable = true;
   198 
   199 		}
   200 
   201 		return $dbmsInternalType .
   202 			($size ? '(' . $size . ')' : '') .
   203 			($this->charset !== null ? ' CHARACTER SET ' . $this->charset : '') .
   204 			($this->default !== null ? ' DEFAULT ' . (is_string($this->default) ? "'" : '') . $this->default . (is_string($this->default) ? "'" : '') : '') .
   205 			' ' . ($this->nullable ? 'NULL' : 'NOT NULL');
   206 	}
   207 
   208 
   209 	public function add() {
   210 		$table = $this->table->getSqlName();
   211 		$ddl = $this->db->sql('ALTER TABLE ' . $table .
   212 			' ADD COLUMN ' . $this->name . ' ' . $this->getColumnDefinition(). ';'
   213 		);
   214 		$ddl->execute();
   215 	}
   216 
   217 	public function modify() {
   218 		$table = $this->table->getSqlName();
   219 		$ddl = $this->db->sql('ALTER TABLE ' . $table .
   220 			' MODIFY COLUMN ' . $this->name . ' ' . $this->getColumnDefinition() . ';'
   221 		);
   222 		$ddl->execute();
   223 	}
   224 
   225 	function drop()
   226 	{
   227 		$table = $this->table->getSqlName();
   228 
   229 		$ddl = $this->db->sql('ALTER TABLE ' . $table . ' DROP COLUMN ' . $this->name . ';');
   230 		$ddl->execute();
   231 	}
   232 }



Download modules/database/Column.class.php


History
Sun, 1 Oct 2023 02:58:08 +0200	Jan Dankert	Refactoring: No it is possible to use 8 byte integers in the database
Sun, 7 Mar 2021 00:10:20 +0100	Jan Dankert	Refactoring: Hopefully more performance while accessing the database resultsets.
Sat, 31 Oct 2020 02:35:54 +0100	Jan Dankert	Save bytes and use the ascii charset for username and issuer.
Sat, 31 Oct 2020 00:43:29 +0100	Jan Dankert	New: Support for OpenId Connect; Removed: Support for LDAP.
Tue, 29 Sep 2020 22:17:11 +0200	Jan Dankert	Refactoring: Do not use global constants.
Fri, 25 Sep 2020 01:00:58 +0200	Jan Dankert	Refactoring: More OO in the database updater :)