Esempio n. 1
0
 /**
  * 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;
 }
Esempio n. 2
0
 /**
  * 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;
 }
Esempio n. 3
0
    /**
     * {@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;
    }
Esempio n. 4
0
 /**
  * {@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;
 }
Esempio n. 5
0
 /**
  * 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;
 }
Esempio n. 6
0
    /**
     * {@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;
    }