/** * Note that SQLite 3.6.19 is requrired for foreign key support. * * (non-PHPdoc) * * @see Alpha\Model\ActiveRecordProviderInterface::createForeignIndex() */ public function createForeignIndex($attributeName, $relatedClass, $relatedClassAttribute, $indexName = null) { self::$logger->info('>>createForeignIndex(attributeName=[' . $attributeName . '], relatedClass=[' . $relatedClass . '], relatedClassAttribute=[' . $relatedClassAttribute . '], indexName=[' . $indexName . ']'); /* * High-level approach * * 1. Rename the source table to [tablename]_temp * 2. Create a new [tablename] table, with the new FK in place. * 3. Copy all of the data from [tablename]_temp to [tablename]. * 4. Drop [tablename]_temp. */ try { ActiveRecord::begin($this->BO); // rename the table to [tablename]_temp $query = 'ALTER TABLE ' . $this->BO->getTableName() . ' RENAME TO ' . $this->BO->getTableName() . '_temp;'; $this->BO->setLastQuery($query); self::getConnection()->query($query); self::$logger->info('Renamed the table [' . $this->BO->getTableName() . '] to [' . $this->BO->getTableName() . '_temp]'); // now create the new table with the FK in place $record = new $relatedClass(); $tableName = $record->getTableName(); $this->foreignKeys[$attributeName] = array($tableName, $relatedClassAttribute); $this->makeTable(); self::$logger->info('Made a new copy of the table [' . $this->BO->getTableName() . ']'); // copy all of the old data to the new table $query = 'INSERT INTO ' . $this->BO->getTableName() . ' SELECT * FROM ' . $this->BO->getTableName() . '_temp;'; $this->BO->setLastQuery($query); self::getConnection()->query($query); self::$logger->info('Copied all of the data from [' . $this->BO->getTableName() . '] to [' . $this->BO->getTableName() . '_temp]'); // finally, drop the _temp table and commit the changes $this->BO->dropTable($this->BO->getTableName() . '_temp'); self::$logger->info('Dropped the table [' . $this->BO->getTableName() . '_temp]'); ActiveRecord::commit($this->BO); } catch (Exception $e) { ActiveRecord::rollback($this->BO); throw new FailedIndexCreateException('Failed to create the index [' . $attributeName . '] on [' . $this->BO->getTableName() . '], error is [' . $e->getMessage() . '], query [' . $this->BO->getLastQuery() . ']'); } self::$logger->info('<<createForeignIndex'); }