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