PHP Connection::getTableSchema方法代码示例

 /**
  * Create table if not exists
  */
 protected function createTable()
 {
     if ($this->db->getTableSchema($this->tableName) === null) {
         $this->db->createCommand()->createTable($this->tableName, ['id' => 'pk', 'created_at' => 'integer', 'updated_at' => 'integer', 'data' => 'binary'])->execute();
         $this->db->getTableSchema($this->tableName, true);
     }
 }

 /**
  * Saves messages to database
  *
  * @param array $messages
  * @param \yii\db\Connection $db
  * @param string $sourceMessageTable
  * @param string $messageTable
  * @param boolean $removeUnused
  * @param array $languages
  */
 public function saveMessagesToDb($messages, $db, $sourceMessageTable, $messageTable, $removeUnused, $languages)
 {
     $q = new \yii\db\Query();
     $current = [];
     foreach ($q->select(['id', 'category', 'message'])->from($sourceMessageTable)->all() as $row) {
         $current[$row['category']][$row['id']] = $row['message'];
     }
     $new = [];
     $obsolete = [];
     foreach ($messages as $category => $msgs) {
         $msgs = array_unique($msgs);
         if (isset($current[$category])) {
             $new[$category] = array_diff($msgs, $current[$category]);
             $obsolete += array_diff($current[$category], $msgs);
         } else {
             $new[$category] = $msgs;
         }
     }
     foreach (array_diff(array_keys($current), array_keys($messages)) as $category) {
         $obsolete += $current[$category];
     }
     if (!$removeUnused) {
         foreach ($obsolete as $pk => $m) {
             if (mb_substr($m, 0, 2) === '@@' && mb_substr($m, -2) === '@@') {
                 unset($obsolete[$pk]);
             }
         }
     }
     $obsolete = array_keys($obsolete);
     $this->stdout("Inserting new messages...");
     $savedFlag = false;
     $columnNames = $db->getTableSchema($sourceMessageTable)->columnNames;
     $hasLocationColumn = in_array('location', $columnNames) ?: false;
     d($hasLocationColumn);
     foreach ($new as $category => $msgs) {
         d([$category, $msgs]);
         foreach ($msgs as $m) {
             $savedFlag = true;
             $msgHash = md5($m);
             $sourceMessageData = ['category' => $category, 'message' => $m];
             if (true === $hasLocationColumn) {
                 $sourceMessageData['location'] = $this->extractLocations($category, $m);
                 $sourceMessageData['hash'] = $msgHash;
             }
             $db->createCommand()->insert($sourceMessageTable, $sourceMessageData)->execute();
             $lastID = $db->driverName == 'pgsql' ? $db->getLastInsertID($sourceMessageTable . '_id_seq') : $db->getLastInsertID();
             dd($lastID);
             foreach ($languages as $language) {
                 $messageData = ['id' => $lastID, 'language' => $language];
                 if (true === $hasLocationColumn) {
                     $messageData['hash'] = $msgHash;
                 }
                 $db->createCommand()->insert($messageTable, $messageData)->execute();
             }
         }
     }
     $this->stdout($savedFlag ? "saved." . PHP_EOL : "Nothing new...skipped." . PHP_EOL);
     $this->stdout($removeUnused ? "Deleting obsoleted messages..." . PHP_EOL : "Updating obsoleted messages..." . PHP_EOL);
     if (empty($obsolete)) {
         $this->stdout("Nothing obsoleted!...skipped." . PHP_EOL);
     } else {
         if ($removeUnused) {
             $db->createCommand()->delete($sourceMessageTable, ['in', 'id', $obsolete])->execute();
             $this->stdout("deleted." . PHP_EOL);
         } else {
             $db->createCommand()->update($sourceMessageTable, ['message' => new \yii\db\Expression("CONCAT('@@',message,'@@')")], ['in', 'id', $obsolete])->execute();
             $this->stdout("updated." . PHP_EOL);
         }
     }
     // ------------------------------ COUNTER ------------------------------
     $counter = ['new' => 0, 'obsolete' => 0];
     foreach ($new as $msgs) {
         $counter['new'] += count($msgs);
     }
     foreach ($obsolete as $msgs) {
         $counter['obsolete'] += count($msgs);
     }
     return $counter;
 }

 public function run()
 {
     $command = $this->db->createCommand();
     $changed = false;
     foreach ($this->nameSpaces as $key => $nameSpace) {
         if (is_integer($key)) {
             $alias = '@' . str_replace('\\', '/', $nameSpace);
             $path = \Yii::getAlias($alias);
         } else {
             $path = $key;
         }
         if (!is_dir($path)) {
             echo 'Directory not exist' . PHP_EOL;
             echo 'Path - "' . $path . '"' . PHP_EOL;
             echo 'Namespace - "' . $nameSpace . '"' . PHP_EOL . PHP_EOL;
             break;
         }
         foreach (glob($path . '*.php') as $file) {
             $info = pathinfo($file);
             $modelCls = $nameSpace . $info['filename'];
             /**
              * @var $model ActiveRecord
              */
             $model = new $modelCls();
             if (!$model instanceof ActiveRecord) {
                 break;
             }
             if (!method_exists($model, 'attributeTypes')) {
                 echo 'Required method "' . get_class($model) . '::attributeTypes()" not found.';
                 break;
             }
             $tblName = $model->tableName();
             $fieldTypes = $model->attributeTypes();
             $schema = $this->db->getTableSchema($tblName, true);
             $fullTblName = $schema ? $schema->fullName : null;
             if (null !== $fullTblName && in_array($fullTblName, $this->tableNames)) {
                 $currColNames = $schema->getColumnNames();
                 $newColumns = array_diff(array_keys($fieldTypes), $currColNames);
                 $removeColumns = array_diff($currColNames, array_keys($fieldTypes));
                 if (!empty($newColumns)) {
                     echo 'Add new column(s) to the table "' . $fullTblName . '"' . PHP_EOL;
                     foreach ($newColumns as $colName) {
                         $command->addColumn($tblName, $colName, $fieldTypes[$colName]);
                         $command->execute();
                         echo '  Column "' . $colName . '" added with type [' . $fieldTypes[$colName] . ']' . PHP_EOL;
                     }
                     $changed = true;
                     echo 'Done.' . PHP_EOL . PHP_EOL;
                 }
                 if (!empty($removeColumns)) {
                     echo 'Remove column(s) from the table "' . $fullTblName . '"' . PHP_EOL;
                     foreach ($removeColumns as $colName) {
                         $command->dropColumn($tblName, $colName);
                         $command->execute();
                         echo '  Column "' . $colName . '" is removed' . PHP_EOL;
                     }
                     $changed = true;
                     echo 'Done.' . PHP_EOL . PHP_EOL;
                 }
             } else {
                 $command = $this->db->createCommand();
                 $command->createTable($tblName, $fieldTypes);
                 $command->execute();
                 $changed = true;
                 echo 'New table "' . trim($this->db->quoteSql($tblName), '`') . '" is created.' . PHP_EOL;
             }
         }
     }
     if (!$changed) {
         echo 'Changes not found.' . PHP_EOL;
     }
 }