/** * Defines a table in the connection with the given table definition. If the table does not * exist, it will be created. If the table structure is different then the definition, it * will be altered * @param Table $table table definition * @return null */ public function defineTable(Table $table) { if ($this->tableExists($table->getName())) { $this->alterTable($table); } else { $this->createTable($table); } }
/** * Defines the foreign keys for the provided table * @param Table $table table definition * @return null */ public function defineForeignKeys(Table $table) { $tableName = $table->getName(); $databaseTable = $this->getTable($tableName); $foreignKeys = $table->getForeignKeys(); $foreignKeysToDrop = array(); foreach ($foreignKeys as $fieldName => $foreignKey) { if (!$databaseTable->hasForeignKey($fieldName)) { continue; } $databaseForeignKey = $databaseTable->getForeignKey($fieldName); if ($foreignKey->equals($databaseForeignKey)) { unset($foreignKeys[$fieldName]); } else { $foreignKeysToDrop[] = $this->connection->quoteIdentifier($databaseForeignKey->getName()); } } $tableName = $this->connection->quoteIdentifier($tableName); foreach ($foreignKeysToDrop as $foreignKey) { $this->connection->execute('ALTER TABLE ' . $tableName . ' DROP FOREIGN KEY ' . $foreignKey); } foreach ($foreignKeys as $foreignKey) { $name = $this->connection->quoteIdentifier($foreignKey->getName()); $fieldName = $this->connection->quoteIdentifier($foreignKey->getFieldName()); $referenceTableName = $this->connection->quoteIdentifier($foreignKey->getReferenceTableName()); $referenceFieldName = $this->connection->quoteIdentifier($foreignKey->getReferenceFieldName()); $this->connection->execute('ALTER TABLE ' . $tableName . ' ADD CONSTRAINT ' . $name . ' FOREIGN KEY (' . $fieldName . ') REFERENCES ' . $referenceTableName . ' (' . $referenceFieldName . ') ON DELETE SET NULL ON UPDATE NO ACTION'); } }
/** * Creates a new table * @param zibo\library\database\definition\Table $table Table definition for the new table * @return null */ protected function createTable(Table $table) { $tableName = $this->connection->quoteIdentifier($table->getName()); $fields = $table->getFields(); $primaryKeys = array(); $indexes = array(); $uniques = array(); $sql = ''; foreach ($fields as $field) { $fieldName = $this->connection->quoteIdentifier($field->getName()); $sql .= $sql == '' ? '' : ', '; $sql .= $fieldName; $sql .= ' ' . $this->getFieldType($field); if ($field->isPrimaryKey()) { $primaryKeys[] = $fieldName; } else { $sql .= ' DEFAULT ' . $this->getDefaultValue($field); if ($field->isUnique()) { $uniques[] = $fieldName; } elseif ($field->isIndexed()) { $indexes[] = $field->getName(); } } } if ($primaryKeys) { $sql .= ', PRIMARY KEY (' . implode(', ', $primaryKeys) . ')'; } if ($uniques) { $sql .= ', UNIQUE (' . implode(', ', $uniques) . ')'; } $sql = 'CREATE TABLE ' . $tableName . ' (' . $sql . ')'; $this->connection->execute($sql); foreach ($indexes as $fieldName) { $this->addIndexFromFieldName($table->getName(), $fieldName); } $indexes = $table->getIndexes(); foreach ($indexes as $index) { $this->addIndex($table->getName(), $index); } }