/** * Update system table schema * * @param string $sql * @param string $type Schema type: `core` or <module>, default as SqlSchema::getType() * * @return bool */ protected function querySchema($sql, $type = '') { $sqlHandler = new SqlSchema(); try { $sqlHandler->queryContent($sql, $type); } catch (\Exception $exception) { $this->handler->setResult('db', array('status' => false, 'message' => 'SQL schema query failed: ' . $exception->getMessage())); return false; } return true; }
/** * Install a field * * - Create schema * * @return bool */ public function install() { if ($this->sql) { $sqlHandler = new SqlSchema(); $sqlHandler->queryContent($this->sql, 'user_custom'); } else { $file = $this->sqlFile; if (!$file) { $file = sprintf('%s/module/user/sql/%s.sql', Pi::path('custom'), $this->getName()); if (!file_exists($file)) { $file = sprintf('%s/user/sql/%s.sql', Pi::path('module'), $this->getName()); if (!file_exists($file)) { $file = ''; } } } if ($file) { $sqlHandler = new SqlSchema(); $sqlHandler->queryFile($file, 'user_custom'); } } return true; }
/** * {@inheritDoc} */ public function updateSchema(Event $e) { $moduleVersion = $e->getParam('version'); $model = Pi::model('page', $this->module); $table = $model->getTable(); $adapter = $model->getAdapter(); // Drop homepage for blocks if (version_compare($moduleVersion, '1.2.0', '<=')) { Pi::model('page')->delete(array('section' => 'front', 'module' => $this->module, 'controller' => 'index', 'action' => 'index')); } // Check for version 1.0.0-beta.2 if (version_compare($moduleVersion, '1.0.0-beta.2', '<')) { // Add table of stats, not used yet; // Solely for demonstration, will be dropped off by end of the udpate $sql = <<<'EOD' CREATE TABLE `{stats}` ( `id` int(10) unsigned NOT NULL auto_increment, `page` int(10) unsigned NOT NULL default '0', `clicks` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `page` (`page`) ); EOD; SqlSchema::setType($this->module); $sqlHandler = new SqlSchema(); try { $sqlHandler->queryContent($sql); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'SQL schema query failed: ' . $exception->getMessage())); return false; } // Alter table field `time` to `time_created` $sql = sprintf('ALTER TABLE %s CHANGE `time` `time_created` int(10)' . ' unsigned NOT NULL default \'0\'', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } // Add table field `time_updated` $sql = sprintf('ALTER TABLE %s ADD `time_updated` int(10) unsigned' . ' NOT NULL default \'0\' AFTER `time_created`', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } // Add table field `clicks` try { $sql = sprintf('ALTER TABLE %s ADD `clicks` int(10)' . ' unsigned NOT NULL default \'0\'', $table); $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } // Drop not used table try { $sql = sprintf('DROP TABLE IF EXISTS %s', Pi::model('stats', $this->module)->getTable()); $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table drop failed: ' . $exception->getMessage())); return false; } } // Check for version 1.0.1 if (version_compare($moduleVersion, '1.0.1', '<')) { // Alter table add field `seo_title` $sql = sprintf('ALTER TABLE %s ADD `seo_title` varchar(255) NOT NULL', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } // Alter table add field `seo_keywords` $sql = sprintf('ALTER TABLE %s ADD `seo_keywords` varchar(255) NOT NULL', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } // Alter table add field `seo_description` $sql = sprintf('ALTER TABLE %s ADD `seo_description` varchar(255) NOT NULL', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } } // Check for version 1.2.0 if (version_compare($moduleVersion, '1.2.0', '<')) { // Alter table add field `nav_order` $sql = sprintf('ALTER TABLE %s ADD `nav_order` smallint(5) unsigned NOT NULL default \'0\'', $table); try { $adapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } } return true; }
/** * {@inheritDoc} * * Module database table list is supposed to be updated * during module upgrade, however we don't have a feasible solution yet. * Thus module developers are encouraged to use $config['schema'] */ public function updateAction() { if ($this->skipUpgrade()) { return; } $module = $this->event->getParam('module'); $config = $this->canonizeConfig($this->config); if (isset($config['schema'])) { $schemaList = $config['schema']; } elseif (empty($config['sqlfile'])) { $schemaList = array(); } else { $sqlFile = sprintf('%s/%s/%s', Pi::path('module'), $this->event->getParam('directory'), $config['sqlfile']); if (!file_exists($sqlFile)) { $schemaList = array(); } else { $schemaList = SqlSchema::fetchSchema($sqlFile); } } $modelSchema = Pi::model('module_schema'); $rowset = $modelSchema->select(array('module' => $module)); foreach ($rowset as $row) { $name = $row->name; if (!isset($schemaList[$name])) { $row->delete(); $status = true; if (!$status) { $msg = 'Deprecated schema "%s" is not removed.'; return array('status' => false, 'message' => sprintf($msg, $name)); } } else { unset($schemaList[$row->name]); } } foreach ($schemaList as $name => $type) { $status = $modelSchema->insert(array('name' => $name, 'type' => $type, 'module' => $module)); if (!$status) { return array('status' => false, 'message' => 'Module schema is not saved.'); } } return true; }
/** * Create system module data * * @param Event $e * @return bool */ public function createSystemSchema(Event $e) { $sqlFile = Pi::path('module') . '/system/sql/mysql.system.sql'; $status = SqlSchema::query($sqlFile); return $status; }
/** * {@inheritDoc} */ public function updateSchema(Event $e) { $moduleVersion = $e->getParam('version'); // Set ticket model $ticketModel = Pi::model('ticket', $this->module); $ticketTable = $ticketModel->getTable(); $ticketAdapter = $ticketModel->getAdapter(); // Set user model $userModel = Pi::model('user', $this->module); $userTable = $userModel->getTable(); $userAdapter = $userModel->getAdapter(); // Update to version 0.0.3 if (version_compare($moduleVersion, '0.0.3', '<')) { // Alter table field `type` $sql = sprintf("ALTER TABLE %s ADD `time_update` INT(10) UNSIGNED NOT NULL DEFAULT '0'", $ticketTable); try { $ticketAdapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } } // Update to version 0.0.4 if (version_compare($moduleVersion, '0.0.4', '<')) { // Add table of user $sql = <<<'EOD' CREATE TABLE `{user}` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ticket` INT(10) UNSIGNED NOT NULL DEFAULT '0', `reply` INT(10) UNSIGNED NOT NULL DEFAULT '0', `time_update` INT(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `ticket` (`ticket`), KEY `reply` (`reply`), KEY `time_update` (`time_update`) ); EOD; SqlSchema::setType($this->module); $sqlHandler = new SqlSchema(); try { $sqlHandler->queryContent($sql); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'SQL schema query for author table failed: ' . $exception->getMessage())); return false; } // Update user table $list = array(); $order = array('time_update DESC'); $where = array('mid' => 0); $select = $ticketModel->select()->where($where)->order($order); $rowset = $ticketModel->selectWith($select); foreach ($rowset as $row) { if (isset($list[$row->uid])) { $list[$row->uid]['ticket'] = $list[$row->uid]['ticket'] + 1; $list[$row->uid]['time_update'] = $row->time_create; } else { $list[$row->uid] = array('id' => $row->uid, 'ticket' => 1, 'reply' => 0, 'time_update' => $row->time_create); } } $where = array('mid != ?' => 0); $select = $ticketModel->select()->where($where)->order($order); $rowset = $ticketModel->selectWith($select); foreach ($rowset as $row) { if (isset($list[$row->uid])) { $list[$row->uid]['reply'] = $list[$row->uid]['reply'] + 1; $list[$row->uid]['time_update'] = $row->time_create; } } foreach ($list as $single) { if (isset($single['id']) && $single['id'] > 0) { $user = $userModel->createRow(); $user->id = $single['id']; $user->ticket = $single['ticket']; $user->reply = $single['reply']; $user->time_update = $single['time_update']; $user->save(); } } } // Update to version 0.1.0 if (version_compare($moduleVersion, '0.1.0', '<')) { // Add table of label $sql = <<<'EOD' CREATE TABLE `{label}` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL DEFAULT '', `ticket` INT(10) UNSIGNED NOT NULL DEFAULT '0', `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0', `color` VARCHAR(8) NOT NULL DEFAULT '', `time_update` INT(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `ticket` (`ticket`), KEY `status` (`status`), KEY `time_update` (`time_update`) ); EOD; SqlSchema::setType($this->module); $sqlHandler = new SqlSchema(); try { $sqlHandler->queryContent($sql); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'SQL schema query for author table failed: ' . $exception->getMessage())); return false; } // Alter table field `label` $sql = sprintf("ALTER TABLE %s ADD `label` INT(10) UNSIGNED NOT NULL DEFAULT '0'", $ticketTable); try { $ticketAdapter->query($sql, 'execute'); } catch (\Exception $exception) { $this->setResult('db', array('status' => false, 'message' => 'Table alter query failed: ' . $exception->getMessage())); return false; } } return true; }