<?php
/**
* SocialEngine
*
* @category Application_Core
* @package Fields
* @copyright Copyright 2006-2020 Webligo Developments
* @license http://www.socialengine.com/license/
* @version $Id: Search.php 10179 2014-04-24 19:41:37Z lucas $
* @author John
*/
/**
* @category Application_Core
* @package Fields
* @copyright Copyright 2006-2020 Webligo Developments
* @license http://www.socialengine.com/license/
* @author John
*/
class Fields_Model_DbTable_Search extends Fields_Model_DbTable_Abstract
{
protected $_fieldSearch = array();
protected $_rowClass = 'Fields_Model_Search';
public function getSearch($item)
{
$id = $this->_getIdentity($item);
if( !array_key_exists($id, $this->_fieldSearch) ) {
$this->_fieldSearch[$id] = $this->fetchRow($this->select()->where('item_id = ?', $id));
}
return $this->_fieldSearch[$id];
}
public function clearValues()
{
$this->_fieldSearch = array();
return $this;
}
public function getSearchSelect($params)
{
$select = $this->select();
$parts = $this->getSearchQuery($params);
foreach( $parts as $w => $v ) {
$select->where($w, $v);
}
return $select;
}
public function getSearchQuery($params)
{
$colsMeta = $this->info('metadata');
$metaData = Engine_Api::_()->fields()->getFieldsMeta($this->getFieldType());
$tableName = $this->info('name');
$parts = array();
foreach( $params as $key => $value ) {
if( !isset($colsMeta[$key]) ) continue;
$colMeta = $colsMeta[$key];
// Ignore empty values
if( (is_scalar($value) && $value === '') ||
(is_array($value) && empty($value)) ||
(is_array($value) && array_key_exists('min', $value) && array_filter($value) === array() ) ) {
continue;
}
// Hack for age->birthdate
if( $key == 'birthdate' || $key == 'birthday' ) {
if( is_array($value) && $value['min'] != $value['max'] ) {
$min = null;
$max = null;
if( !empty($value['min']) ) {
$max = date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value['min']));
}
unset($value['min']);
if( !empty($value['max']) ) {
$min = date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value['max'])
- (365 * 24 * 60 * 60)); // Hack for max-age year);
}
unset($value['max']);
if( $min ) {
$value['min'] = $min;
}
if( $max ) {
$value['max'] = $max;
}
} else if( is_scalar($value) || $value['min'] == $value['max'] ) {
if (!is_scalar($value)) $value = $value['min'];
$value = array(
'min' => date('Y-m-d', (time() - (365 * 24 * 60 * 60) * ($value + 1) - 1)),
'max' => date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value )),
);
}
}
// Set
if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 3)) === 'SET' ) {
preg_match('/\((.+)\)/', $colMeta['DATA_TYPE'], $m);
if( empty($m[1]) ) continue;
$allowed = $m[1];
$allowed = explode(',', $m[1]);
foreach( $allowed as &$al ) {
$al = trim($al, '\'",');
}
$value = (array) $value;
$value = array_intersect($allowed, $value);
if( empty($value) ) continue;
$subParts = array();
foreach( $value as $val ) {
$subParts[] = $this->getAdapter()->quoteInto('(FIND_IN_SET(?,`' . $tableName . '`.`' . $key . '`) > 0)', $val);
}
$parts[join(' ' . Zend_Db_Select::SQL_OR . ' ', $subParts)] = null;
}
// Range
else if( is_array($value) && (array_key_exists('min', $value) || array_key_exists('max', $value)) ) {
if( isset($value['min']) && $value['min'] !== '' ) {
$parts[$key . ' >= ?'] = $value['min'];
}
if( isset($value['max']) && $value['max'] !== '' ) {
$parts[$key . ' <= ?'] = $value['max'];
}
}
// ENUM OR INT
else if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 4)) === 'ENUM' || strpos(strtoupper($colMeta['DATA_TYPE']), 'INT') !== FALSE ) {
$value = is_array($value) ? $value: array($value);
$parts[$key . ' IN (?)'] = $value;
}
// Substring?
// @todo don't really like this
else if( is_string($value) && ($value[0] == '%' || $value[strlen($value)-1] == '%') ) {
$parts[$key . ' LIKE ?'] = $value;
}
else if ( is_string($value) ){
$parts[$key . ' LIKE ?'] = '%' . $value . '%';
}
// Scalar
else if( is_scalar($value) ) {
$parts[$key . ' = ?'] = $value;
}
}
return $parts;
}
public function updateSearch($spec, $values)
{
if( !($spec instanceof Core_Model_Item_Abstract) ) {
throw new Fields_Model_Exception('Not an item');
}
if( !($values instanceof Zend_Db_Table_Rowset_Abstract) ) {
return null;
}
// Prepare data
$cols = $this->info('cols');
$colsMeta = $this->info('metadata');
$metaData = Engine_Api::_()->fields()->getFieldsMeta($spec);
$searchRow = $this->getSearch($spec);
if( null === $searchRow ) {
$this->_fieldSearch[$spec->getIdentity()] = $searchRow = $this->createRow();
$searchRow->item_id = $spec->getIdentity();
}
// Index
$indexedValues = array();
foreach( $values as $row ) {
$searchField = $metaData->getRowMatching('field_id', $row->field_id);
if( !$searchField ) continue;
$searchCol = $this->_getSearchColumn($searchField);
if( !engine_in_array($searchCol, $cols) ) continue;
if( !isset($indexedValues[$searchCol]) ) {
$indexedValues[$searchCol] = $row->value;
} else {
// Fixes #1393 courtesy of RadCodes
if( !is_array($indexedValues[$searchCol]) ) {
$indexedValues[$searchCol] = array($indexedValues[$searchCol]);
}
$indexedValues[$searchCol][] = $row->value;
}
}
// Update
foreach( $indexedValues as $col => $value ) {
$colMeta = $colsMeta[$col];
if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 3)) === 'SET' ) {
preg_match('/\((.+)\)/', $colMeta['DATA_TYPE'], $m);
if( empty($m[1]) ) continue;
$allowed = $m[1];
$allowed = explode(',', $m[1]);
foreach( $allowed as &$al ) {
$al = trim($al, '\'",');
}
$value = (array) $value;
$value = array_intersect($allowed, $value);
$value = join(',', $value);
} else if( is_array($value) ){
$value = array_filter($value);
$value = array_shift($value);
}
$searchRow->$col = $value;
}
$searchRow->save();
return $searchRow;
}
public function removeItemValues($item)
{
if( !($item instanceof Core_Model_Item_Abstract) ) {
throw new Fields_Model_Exception('Not an item');
}
$this->delete(array(
'item_id = ?' => $item->getIdentity(),
));
return $this;
}
public function checkSearchIndex($field)
{
// Note: this is called on create and edit
// Note: and now delete
if( !$field->search ) {
$this->deleteFieldSearch($field);
return $this;
}
$this->setMetadataCacheInClass(false);
$this->setDefaultMetadataCache(null);
// Prepare table info
$name = $this->info('name');
$cols = $this->info('cols');
// Make column name
$searchCol = null;
if( !empty($field->alias) ) {
$searchCol = $field->alias;
} else {
$searchCol = sprintf('field_%d', $field->field_id);
}
// Get sql params
$sqlParams = null;
// Pull sql params from field class
$class = 'Fields_Form_Element_'.Engine_Api::_()->fields()->inflectFieldType($field->type);
if( @class_exists($class) && method_exists($class, 'getSearchSqlParams') ) {
$sqlParams = call_user_func(array($class, 'getSearchSqlParams'));
}
// Infer sql params from field type
else {
$info = Engine_Api::_()->fields()->getFieldInfo($field->type);
$genericType = null;
if( !empty($info['base']) ) {
$genericType = $info['base'];
} else {
$genericType = $field->type;
}
switch( $genericType ) {
case 'text':
$sqlParams = array(
'type' => 'varchar',
'length' => '255',
'charset' => 'default',
'collate' => 'default',
);
break;
case 'textarea':
$sqlParams = array(
//'type' => 'text',
'type' => 'varchar',
'length' => '255',
'charset' => 'default',
'collate' => 'default',
);
break;
case 'select':
case 'radio':
// Note: this won't work for options that have text keys (some of the pre-made ones)
$option_ids = $field->getOptionIds();
if( !empty($option_ids) ) {
$sqlParams = array(
'type' => 'enum',
'length' => $option_ids,
);
} else if( !empty($info['multiOptions']) ) {
$sqlParams = array(
'type' => 'enum',
'length' => array_keys($info['multiOptions']),
);
} else {
$sqlParams = array(
'type' => 'varchar',
'length' => '255',
'charset' => 'default',
'collate' => 'default',
);
}
break;
case 'multiselect':
case 'multi_checkbox':
$option_ids = $field->getOptionIds();
if( !empty($option_ids) ) {
$sqlParams = array(
'type' => 'set',
'length' => $option_ids,
);
} else if( !empty($info['multiOptions']) ) {
$sqlParams = array(
'type' => 'set',
'length' => array_keys($info['multiOptions']),
);
} else {
$sqlParams = array(
'type' => 'varchar',
'length' => '255',
'charset' => 'default',
'collate' => 'default',
);
}
break;
case 'checkbox':
$sqlParams = array(
'type' => 'tinyint',
'length' => 1,
);
break;
case 'integer':
$sqlParams = array(
'type' => 'int',
);
break;
case 'float':
$sqlParams = array(
'type' => 'float',
);
break;
case 'date':
$sqlParams = array(
'type' => 'date',
);
break;
case 'datetime':
$sqlParams = array(
'type' => 'datetime',
);
break;
}
}
// Exit if no params
if( empty($sqlParams) ) {
return $this;
}
// Action
$sqlParams['column'] = $searchCol;
// Create
$exists = false;
$tmp_e = null;
if( !engine_in_array($searchCol, $cols) ) {
try {
$alterSql = $this->_constructAlterQuery($sqlParams, 'create');
$indexSql = $this->_constructIndexQuery($sqlParams, 'create');
$this->getAdapter()->query($alterSql);
$this->getAdapter()->query($indexSql);
$this->flushMetaData();
$exists = true;
// Populating Data for newly created column
$valueTable = Engine_Api::_()->fields()->getTable($this->getFieldType(), 'values');
$select = $valueTable->select()
->from($valueTable, array('item_id' => 'item_id', 'field_value' => new Zend_Db_Expr('GROUP_CONCAT(DISTINCT value ORDER BY value)')))
->where('field_id = ?', $field->field_id)
->group('item_id');
foreach( $valueTable->fetchAll($select) as $fieldVal ) {
if( empty($fieldVal->field_value) ) {
continue;
}
$this->update(array(
$searchCol => $fieldVal->field_value,
), array(
'item_id = ?' => $fieldVal->item_id,
));
}
} catch( Exception $e ) {
$tmp_e = $e;
throw $e; // Debug
}
}
// Edit
else {
try {
$sql = $this->_constructAlterQuery($sqlParams, 'edit');
$this->getAdapter()->query($sql);
$sql = $this->_constructIndexQuery($sqlParams, 'edit');
$this->getAdapter()->query($sql);
$this->flushMetaData();
$exists = true;
} catch( Exception $e ) {
$tmp_e = $e;
throw $e; // Debug
}
}
// Check if exception was throw during alter
if( !$exists ) {
$field->search = 0;
$field->save();
if( $tmp_e instanceof Exception ) throw $tmp_e;
}
return $this;
}
public function deleteFieldSearch($field)
{
// Note: this is called on delete (duh)
// Note: and also on check
if( $field->search ) {
return $this;
}
// Prepare table info
$name = $this->info('name');
$cols = $this->info('cols');
// Make column name
$searchCol = null;
if( !empty($field->alias) ) {
$searchCol = $field->alias;
} else {
$searchCol = sprintf('field_%d', $field->field_id);
}
// Don't delete if any other column with the same alias exist
if( !empty($field->alias) ) {
$sameAliasedFields = Engine_Api::_()->fields()->getFieldsMeta($this->getFieldType())->getRowsMatching('alias', $field->alias);
if( engine_count($sameAliasedFields) > 1 ) {
return $this;
}
}
// Only delete if the col exists (duh)
if( engine_in_array($searchCol, $cols) ) {
try {
$sql = $this->_constructIndexQuery($searchCol, 'delete');
$this->getAdapter()->query($sql);
$sql = $this->_constructAlterQuery($searchCol, 'delete');
$this->getAdapter()->query($sql);
$this->flushMetaData();
} catch( Exception $e ) {
$exists = true;
$tmp_e = $e;
throw $e; // Debug
}
}
return $this;
}
public function flushOptionSearch($option)
{
}
protected function _getIdentity($item)
{
$id = null;
if( $item instanceof Core_Model_Item_Abstract ) {
if( $item->getType() != $this->_fieldType ) {
throw new Fields_Model_Exception('field type does not match item type');
}
$id = $item->getIdentity();
} else if( is_numeric($item) ) {
$id = $item;
} else {
throw new Fields_Model_Exception('invalid item');
}
return $id;
}
protected function _constructAlterQuery($params, $method = 'create')
{
// Construct
$sql = '';
$name = $this->info('name');
// Check column
$column = null;
if( is_string($params) ) {
$column = $params;
} else if( isset($params['column']) && is_string($params['column']) ) {
$column = $params['column'];
}
if( !$column || ($method !== 'delete' && !is_array($params)) ) {
throw new Fields_Model_Exception('Invalid alter params');
}
// Only main part for create/edit
if( $method != 'delete' ) {
// Check params
if( empty($params['type']) || !is_string($params['type']) ) {
throw new Fields_Model_Exception('Invalid alter params');
}
// Column type
$sql .= $params['type'];
// Column length
if( !empty($params['length']) ) {
if( is_array($params['length']) ) {
$sql .= '(\'' . join("','", $params['length']) . '\')';
} else if( is_numeric($params['length']) || is_string($params['length']) ) {
$sql .= '('.$params['length'].')';
} else {
// ignore for now
}
}
// Column charset
if( !empty($params['charset']) ) {
if( $params['charset'] == 'default' || $params['charset'] === true ) {
$sql .= ' CHARACTER SET utf8mb4';
} else if( is_string($params['charset']) ) {
$sql .= ' CHARACTER SET ' . $params['charset'];
}
}
// Column collate
if( !empty($params['collate']) ) {
if( $params['collate'] == 'default' || $params['collate'] === true ) {
$sql .= ' COLLATE utf8mb4_unicode_ci';
} else if( is_string($params['collate']) ) {
$sql .= ' COLLATE ' . $params['collate'];
}
}
// Column null
$sql .= ' ' . ( !isset($params['null']) || $params['null'] ? 'NULL' : 'NOT NULL' );
// Column default
if( isset($params['default']) ) {
$sql .= ' default \''.$params['default'].'\'';
} else {
if( isset($params['null']) && !$params['null'] ) {
$sql .= ' default \'\'';
}
}
}
// Make full query
switch( $method ) {
case 'create':
$sql = "ALTER TABLE `{$name}` ADD COLUMN `$column` {$sql}";
break;
case 'edit':
$sql = "ALTER TABLE `{$name}` CHANGE COLUMN `$column` `$column` {$sql}";
break;
case 'delete':
$sql = "ALTER TABLE `{$name}` DROP COLUMN `$column`";
break;
default:
throw new Fields_Model_Exception('invalid operation');
break;
}
return $sql;
}
protected function _constructIndexQuery($column, $method = 'create')
{
if( is_array($column) && !empty($column['column']) && is_string($column['column']) ) {
$column = $column['column'];
} else if( !is_string($column) ) {
throw new Fields_Model_Exception('invalid column name');
}
$sql = '';
$name = $this->info('name');
switch( $method ) {
case 'create':
$sql = "ALTER TABLE `{$name}` ADD INDEX ( `{$column}` )";
break;
case 'edit':
// Do we need to do this?
$sql = "ALTER TABLE `{$name}` DROP INDEX `{$column}`, ADD INDEX `{$column}` ( `{$column}` )";
break;
case 'delete':
$sql = "ALTER TABLE `{$name}` DROP INDEX `{$column}`";
break;
default:
throw new Fields_Model_Exception('invalid operation');
break;
}
return $sql;
}
protected function _getSearchColumn($field)
{
if( !($field instanceof Fields_Model_Meta) ) {
throw new Fields_Model_Exception('not a field');
} else if( !empty($field->alias) ) {
return $field->alias;
} else {
return sprintf('field_%d', $field->field_id);
}
}
}