/**
* {@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}
*/
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;
}