/**
*
* @return mixed[string]
*/
public function getOptions()
{
if ($this->options === false) {
$options = \Simplify::db()->query()->from($this->form->getTable())->select($this->getFieldName())->orderBy($this->getFieldName())->execute()->fetchCol();
$options = array_combine($options, $options);
} else {
$options = (array) $this->options;
}
return $options;
}
/**
* (non-PHPdoc)
*
* @see \Simplify\Form\Provider::getData()
*/
public function getData()
{
if ($this->data === false) {
$query = $this->query;
if (is_array($query)) {
$query = \Simplify::db()->query()->select($this->value)->select($this->label)->setParams($query);
} elseif (is_string($query)) {
$query = \Simplify::db()->query()->select($this->value)->select($this->label)->from($query);
}
$data = $query->execute()->fetchAll();
$this->data = sy_array_to_options($data, $this->value, $this->label);
}
return $this->data;
}
protected function buildPermalink(Action $action, $data, $post)
{
$id = $action->form->getId();
$id = $id[0];
$perma = call_user_func($this->callback, $data, $post);
$perma = sy_slugify($perma);
$pk = $action->form->getPrimaryKey();
$table = $action->form->getTable();
$field = $this->getFieldName();
$i = 0;
do {
$c = $i ? '-' . $i : '';
$sql = "SELECT {$pk} FROM {$table} WHERE {$field} LIKE '{$perma}{$c}'";
if ($id) {
$sql .= " AND {$pk} != {$id}";
}
$_row = \Simplify::db()->query($sql)->execute()->fetchOne();
$i++;
} while (!empty($_row) && $i < 10);
$perma .= $c;
return $perma;
}
/**
*
* @return string
*/
protected function filter()
{
if ($this->sortGroupColumn) {
if (!\Simplify::request()->get()->has($this->sortGroupColumn)) {
throw new \Exception("Missing sort group column value");
}
$value = \Simplify::request()->get($this->sortGroupColumn);
$value = \Simplify::db()->quote($value);
$filter = " {$this->sortGroupColumn} = {$value} ";
return $filter;
}
return ' TRUE ';
}
public function createRepository()
{
$create = '
CREATE TABLE `%1$s` (
`%2$s` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`%3$s` MEDIUMINT(8) UNSIGNED NOT NULL,
`%4$s` MEDIUMINT(8) UNSIGNED NOT NULL,
`%5$s` MEDIUMINT(8) UNSIGNED NOT NULL,
PRIMARY KEY (`%2$s`),
INDEX `tree_parent_id` (`%3$s`),
INDEX `tree_left_id` (`%4$s`),
INDEX `tree_right_id` (`%5$s`)
)
COLLATE=\'utf8_general_ci\'
ENGINE=InnoDB
';
$sql = sprintf($create, $this->table, $this->pk, $this->parent, $this->left, $this->right);
if (\Simplify::db()->query($sql)->executeRaw() === false) {
throw new \Simplify\Db\DatabaseException('Could not create table');
}
}
/**
*
* @param unknown_type $data
* @return multitype:multitype:Ambigous <unknown, ArrayAccess> multitype:unknown
*/
public function getOptions($data)
{
$t = $this->getTable();
$pk = $this->getPrimaryKey();
$fk = $this->getForeignKey();
$at = $this->getAssociationTable();
$apk = $this->getAssociationPrimaryKey();
$afk = $this->getAssociationForeignKey();
$q = \Simplify::db()->query()->select("{$t}.{$fk}")->select("{$t}.{$this->labelField}")->select("{$at}.{$afk} AS checked")->from($t)->leftJoin("{$at} ON ({$t}.{$fk} = {$at}.{$afk} AND {$at}.{$apk} = :{$pk})");
$data = $q->execute(array($pk => $data[\Simplify\Form::ID]))->fetchAll();
$options = sy_array_to_options($data, $fk, $this->labelField);
$checked = array();
foreach ($data as $row) {
if ($row['checked']) {
$checked[] = $row[$fk];
}
}
return array('options' => $options, 'checked' => $checked);
}
public static function addGroupPermission($group_id, $permission_id)
{
if (is_string($permission_id)) {
$permission_id = \Simplify::db()->query()->from(\Simplify::config()->get('amp:tables:permissions'))->select('permission_id')->where('permission_name = ?')->execute($permission_id)->fetchOne();
if (empty($permission_id)) {
throw new \Exception("Could not add group permission: invalid permission id");
}
}
$data = array('permission_id' => $permission_id, 'group_id' => $group_id);
\Simplify::db()->query('INSERT IGNORE INTO ' . \Simplify::config()->get('amp:tables:groups_permissions') . ' VALUES (:permission_id, :group_id)')->execute($data);
}
/**
* Get minimum and maximum datetime values from database
*
* @return string[]
*/
protected function loadDatabaseLimits()
{
$limits = \Simplify::db()->query()->from($this->form->getTable())->select("MIN({$this->getFieldName()}) AS min, MAX({$this->getFieldName()}) AS max")->execute()->fetchRow();
$minDate = \Simplify\Form\DateTime::timestamp($limits['min']);
$maxDate = \Simplify\Form\DateTime::timestamp($limits['max']);
$this->minDate = max(\Simplify\Form\DateTime::timestamp($this->minDate), $minDate);
$this->maxDate = min(\Simplify\Form\DateTime::timestamp($this->maxDate), $maxDate);
if (empty($this->maxDate)) {
$this->maxDate = $maxDate;
}
}
public function create()
{
if (!$this->exists()) {
if (empty($this->columns)) {
throw new \Simplify\Db\DatabaseException('Cannot create empty table');
}
$create = "CREATE TABLE `%1\$s` (\n%2\$s\n) \nCOLLATE='utf8_general_ci' ENGINE=InnoDB";
$s = array();
foreach ($this->columns as $field => $column) {
$s[] = ' ' . $column->getColumnSql();
}
foreach ($this->indexes as $type => $index) {
if ($type == 'PRIMARY') {
$s[] = sprintf(' PRIMARY KEY (`%s`)', implode('` , `', (array) $index->fields));
} elseif (!empty($index->unique)) {
$s[] = sprintf(' UNIQUE INDEX `%s` (`%s`)', $type, implode('` , `', (array) $index->fields));
} else {
$s[] = sprintf(' INDEX `%s` (`%s`)', $type, implode('` , `', (array) $index->fields));
}
}
$sql = sprintf($create, $this->name, implode(",\n", $s));
\Simplify::db()->query($sql)->execute();
$this->loadSchema();
}
}
/**
* (non-PHPdoc)
* @see \Simplify\Form\Action::onLoadData()
*/
protected function onLoadData()
{
$elements = $this->getElements();
$name = $this->getNameField();
$value = $this->getValueField();
$params = array();
$params[\Simplify\Db\QueryParameters::SELECT][] = $value;
$this->formData = array();
$elements->rewind();
while ($elements->valid()) {
$element = $elements->current();
$elements->next();
$params[\Simplify\Db\QueryParameters::WHERE][] = "{$name} = :{$name}";
$params[\Simplify\Db\QueryParameters::DATA] = array($name => $element->getFieldName());
$row = array();
$row['found'] = \Simplify::db()->query()->from($this->getTable())->setParams($params)->execute()->fetchRow();
$row['data'][$element->getFieldName()] = sy_get_param($row['found'], $value);
$this->formData[$element->getName()] = $row;
$element->onLoadData($this, $this->formData[$element->getName()]['data'], $row['data']);
}
}
function sy_format_sql($query, array $data = null)
{
if (!empty($data)) {
foreach ($data as $name => $value) {
$query = str_replace(':' . $name, \Simplify::db()->quote($value), $query);
}
}
return $query;
}
/**
* Read an option
*
* @param string $name
* @return array
*/
public static function read($name)
{
self::autoLoad();
if (isset(self::$options[$name])) {
$option = self::$options[$name];
} else {
$option = \Simplify::db()->query()->from(\Simplify::config()->get('amp:tables:options'))->where('option_name = ?')->execute($name)->fetchRow();
}
return $option;
}
/**
* Upgrade database tables to last version
*/
protected static function upgrade()
{
$query = 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:options') . '` (
`option_id` mediumint(8) NOT NULL AUTO_INCREMENT,
`option_name` varchar(255) NOT NULL,
`option_value` text NOT NULL,
`option_modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`option_autoload` tinyint(1) unsigned NOT NULL DEFAULT \'0\',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`),
KEY `option_autoload` (`option_autoload`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
\Simplify::db()->query($query)->executeRaw();
$version = intval(\Amplify\Options::value('db_version', 0));
if ($version <= 0) {
$queries = array('CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:groups') . '` (
`group_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) NOT NULL,
PRIMARY KEY (`group_id`),
UNIQUE KEY `group_name` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;', 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:groups_permissions') . '` (
`group_id` mediumint(8) unsigned NOT NULL,
`permission_id` mediumint(8) unsigned NOT NULL,
UNIQUE KEY `group_id_permission_id` (`group_id`,`permission_id`),
KEY `FK_groups_permissions_permissions` (`permission_id`),
CONSTRAINT `FK_groups_permissions_groups` FOREIGN KEY (`group_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:groups') . '` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_groups_permissions_permissions` FOREIGN KEY (`permission_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:permissions') . '` (`permission_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;', 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:groups_users') . '` (
`user_id` mediumint(8) unsigned NOT NULL,
`group_id` mediumint(8) unsigned NOT NULL,
UNIQUE KEY `user_id_group_id` (`user_id`,`group_id`),
KEY `FK_groups_users_groups` (`group_id`),
CONSTRAINT `FK_groups_users_groups` FOREIGN KEY (`group_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:groups') . '` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_groups_users_users` FOREIGN KEY (`user_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:users') . '` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;', 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:permissions') . '` (
`permission_id` mediumint(255) unsigned NOT NULL AUTO_INCREMENT,
`permission_name` varchar(255) NOT NULL,
`permission_description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`permission_id`),
UNIQUE KEY `permission_name` (`permission_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;', 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:permissions_users') . '` (
`user_id` mediumint(8) unsigned NOT NULL,
`permission_id` mediumint(8) unsigned NOT NULL,
UNIQUE KEY `user_id_permission_id` (`user_id`,`permission_id`),
KEY `FK_permissions_users_permissions` (`permission_id`),
CONSTRAINT `FK_permissions_users_permissions` FOREIGN KEY (`permission_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:permissions') . '` (`permission_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_permissions_users_users` FOREIGN KEY (`user_id`) REFERENCES `' . \Simplify::config()->get('amp:tables:users') . '` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;', 'CREATE TABLE IF NOT EXISTS `' . \Simplify::config()->get('amp:tables:users') . '` (
`user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`user_username` varchar(255) NOT NULL,
`user_email` varchar(255) NOT NULL,
`user_password` char(60) NOT NULL,
`access_token` char(60) DEFAULT NULL,
PRIMARY KEY (`user_id`),
INDEX `user_password` (`user_password`),
INDEX `access_token` (`access_token`),
UNIQUE KEY `user_username` (`user_username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
\Simplify::db()->beginTransaction();
\Simplify::db()->query('SET FOREIGN_KEY_CHECKS=0')->executeRaw();
foreach ($queries as $query) {
\Simplify::db()->query($query)->executeRaw();
}
\Simplify::db()->commit();
}
}
