/** * @param Database $db * @param string $class * @throws SchemaException */ public static function loadTable(Database $db, string $class) { // Check if $class is path to an existing $class // $class must be instanceof AbstractTable if (class_exists($class) && is_subclass_of($class, "Comely\\IO\\Database\\Schema\\AbstractTable", true)) { // Lets instantiate $class $table = new $class($db); // Save instance in Schema self::$tables[$class] = $table; } else { // Not found throw SchemaException::tableNotFound($class); } }
/** * Sets default value for column * * Booleans are converted to 1 (true) or 0 (false) * * @param $value * @return Column * @throws SchemaException */ public function defaultValue($value) : self { // Check if given value is Boolean if (is_bool($value)) { // Convert Boolean to Integer $value = (int) $value; } // Cross check given value type with scalarType of column if (gettype($value) !== $this->scalarType) { throw SchemaException::badDefaultValue(gettype($value), $this->scalarType); } // Save default value $this->default = $value; return $this; }
/** * Sets PRIMARY KEY for table * * @param string $column * @throws SchemaException */ protected final function primaryKey(string $column) { // Check if column exists if (!array_key_exists($column, $this->columns)) { throw SchemaException::undefinedColumn($column, $this->tableId); } // Set this column as PRIMARY_KEY $this->columns[$column]->attributes["primary"] = 1; $this->primaryKey = $column; }
/** * Generate CREATE TABLE query * * @param bool $dropExisting * @return string * @throws SchemaException */ public function buildQuery($dropExisting = false) : string { // Configure line endings and indents $lb = "\n"; $indent = str_repeat(" ", 2); // Include command to drop existing table? if ($dropExisting === true) { // Works with MySQL and SQLite $sqlTable = sprintf("DROP TABLE IF EXISTS `%s`;%s", $this->tableName, $lb); } else { // Start with an empty String $sqlTable = ""; } // CREATE TABLE statement $sqlTable .= sprintf("CREATE TABLE `%s` (%s", $this->tableName, $lb); // Empty arrays to collect relevant data for reprocessing $mySqlUniqueKeys = []; // Iterate through columns foreach ($this->columns as $name => $column) { // Start column with indent $sqlTable .= sprintf("%s`%s` ", $indent, $name); // Check column type if ($column->type === "int") { // Integer table $integerTable = [self::INT_TINY => "tinyint", self::INT_SMALL => "smallint", self::INT_MEDIUM => "mediumint", self::INT_DEFAULT => "int", self::INT_BIG => "bigint"]; // Integer column type if ($this->dbDriver === "mysql") { $sqlTable .= $integerTable[$column->flag]; // Number of digits explicitly specified? if (array_key_exists("digits", $column->attributes)) { $sqlTable .= sprintf("(%d)", $column->attributes["digits"]); } } elseif ($this->dbDriver === "sqlite") { // SQLite is a simple as it gets $sqlTable .= "INTEGER"; } elseif ($this->dbDriver === "pgsql") { // PostgreSQL doesn't have AI if (array_key_exists("ai", $column->attributes)) { // SERIAL Type } else { // Integer Type } } } elseif ($column->type === "string") { // String column type (char|varchar) if ($this->dbDriver === "mysql") { // MySQL // Determine if its a CHAR or VARCHAR $sqlTable .= $column->flag === self::STR_FIXED ? "char" : "varchar"; // Check if explicit length has been provided if (array_key_exists("length", $column->attributes)) { $sqlTable .= sprintf("(%d)", $column->attributes["length"]); } } elseif ($this->dbDriver === "sqlite") { // String types in SQLite $sqlTable .= "TEXT"; } } elseif ($column->type === "binary") { // String column type (char|varchar) if ($this->dbDriver === "mysql") { // MySQL // Determine if its a CHAR or VARCHAR $sqlTable .= $column->flag === self::BIN_FIXED ? "binary" : "varbinary"; // Check if explicit length has been provided if (array_key_exists("length", $column->attributes)) { $sqlTable .= sprintf("(%d)", $column->attributes["length"]); } } elseif ($this->dbDriver === "sqlite") { // String types in SQLite $sqlTable .= "BLOB"; } } elseif ($column->type === "text") { // Data type TEXT if ($this->dbDriver === "mysql") { // MySQL $textTable = [self::TEXT_DEFAULT => "TEXT", self::TEXT_MEDIUM => "MEDIUMTEXT", self::TEXT_LONG => "LONGTEXT"]; $sqlTable .= $textTable[$column->flag]; } elseif ($this->dbDriver === "sqlite") { // SQLite $sqlTable .= "TEXT"; } } elseif ($column->type === "enum") { // Enums $options = array_key_exists("options", $column->attributes) ? (array) $column->attributes["options"] : []; if ($this->dbDriver === "mysql") { // Straight-forward enum implementation in MySQL $sqlTable .= "enum("; $sqlTable .= implode(",", array_map(function ($opt) { return "'" . $opt . "'"; }, $options)); $sqlTable .= ")"; } elseif ($this->dbDriver === "sqlite") { // For SQLite we will use CHECK() on data type TEXT $sqlTable .= sprintf("TEXT CHECK(%s in (", $name); $sqlTable .= implode(",", array_map(function ($opt) { return "'" . $opt . "'"; }, $options)); $sqlTable .= "))"; } } elseif (in_array($column->type, ["double", "decimal"])) { // Double and decimals if ($this->dbDriver === "mysql") { // MySQL $sqlTable .= $column->type; if (array_key_exists("m", $column->attributes)) { $sqlTable .= "(" . $column->attributes["m"]; if (array_key_exists("d", $column->attributes)) { $sqlTable .= "," . $column->attributes["d"]; } $sqlTable .= ")"; } } elseif ($this->dbDriver === "sqlite") { // SQLite Real $sqlTable .= "REAL"; } } // Unsigned Number? if (array_key_exists("signed", $column->attributes) && $column->attributes["signed"] === 0) { // Is this attribute appropriate for this column type? if (in_array($column->type, ["int", "double", "decimal"])) { if ($this->dbDriver === "sqlite" && array_key_exists("ai", $column->attributes)) { // SQLite AI columns cannot have UNSIGNED declaration } else { $sqlTable .= " UNSIGNED"; } } } // Primary Key if (array_key_exists("primary", $column->attributes)) { $sqlTable .= " PRIMARY KEY"; } // Auto-increment if (array_key_exists("ai", $column->attributes)) { // Is this attribute appropriate for this column type? if (in_array($column->scalarType, ["integer", "double"])) { if ($this->dbDriver === "mysql") { $sqlTable .= " auto_increment"; } elseif ($this->dbDriver === "sqlite") { $sqlTable .= " AUTOINCREMENT"; // AUTOINCREMENT must be used with INTEGER PRIMARY KEY if (!array_key_exists("primary", $column->attributes)) { throw SchemaException::columnParseError("AUTOINCREMENT must be used with INTEGER PRIMARY KEY"); } } } } // Unique if (array_key_exists("unique", $column->attributes)) { if ($this->dbDriver === "mysql") { // Save UNIQUE flag in an Array to be processed after all columns $mySqlUniqueKeys[] = $name; } elseif ($this->dbDriver === "sqlite") { // Add UNIQUE flag inline $sqlTable .= " UNIQUE"; } } // Special attributes // MySQL charset and collation if ($this->dbDriver === "mysql") { if (in_array($column->type, ["string", "text", "enum"])) { $sqlTable .= " CHARACTER SET " . $column->attributes["charset"]; $sqlTable .= " COLLATE " . $column->attributes["collation"]; } } // Is Nullable? if (!array_key_exists("nullable", $column->attributes)) { // Cannot be a NULL $sqlTable .= " NOT NULL"; } // Default Value if ($column->default === null) { // Default Value is NULL, But column is? if (array_key_exists("nullable", $column->attributes)) { $sqlTable .= " default NULL"; } } else { // Set default value $sqlTable .= " default "; $sqlTable .= is_string($column->default) ? sprintf("'%s'", $column->default) : $column->default; } // End column line $sqlTable .= "," . $lb; } // MySQL specific constrains and additional columns if ($this->dbDriver === "mysql") { // Unique Keys if (count($mySqlUniqueKeys) > 0) { foreach ($mySqlUniqueKeys as $uniqueKey) { $sqlTable .= sprintf("%sUNIQUE KEY (`%s`),%s", $indent, $uniqueKey, $lb); } } } // Constraints if (count($this->constraints) > 0) { foreach ($this->constraints as $name => $constraint) { // Given indent $sqlTable .= $indent; // Constraints for different database types if ($this->dbDriver === "mysql") { // Check constraint type if ($constraint["type"] === "unique") { // Unique Constraint $sqlTable .= sprintf("UNIQUE KEY `%s` (%s),%s", $name, implode(",", array_map(function ($col) { return "`" . $col . "`"; }, $constraint["cols"])), $lb); } elseif ($constraint["type"] === "foreign") { // Foreign Constraint $sqlTable .= sprintf("FOREIGN KEY (`%s`) REFERENCES `%s`(`%s`),%s", $name, $constraint["table"], $constraint["col"], $lb); } } elseif ($this->dbDriver === "sqlite") { // Check constraint type if ($constraint["type"] === "unique") { // Unique Constraint $sqlTable .= sprintf("CONSTRAINT `%s` UNIQUE (%s),%s", $name, implode(",", array_map(function ($col) { return "`" . $col . "`"; }, $constraint["cols"])), $lb); } elseif ($constraint["type"] === "foreign") { // Foreign Constraint $sqlTable .= sprintf("CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s`(`%s`),%s", "cnstrnt_" . $name . "_frgn", $name, $constraint["table"], $constraint["col"], $lb); } } } } // Closing CREATE TABLE statement $sqlTable = substr($sqlTable, 0, -1 * (1 + strlen($lb))) . $lb; if ($this->dbDriver === "mysql") { // Specify storage engine $sqlTable .= sprintf(") ENGINE=%s;", $this->tableEngine); } else { $sqlTable .= ");"; } return $sqlTable; }