_alias = $config; $config = array('name' => Qs_Db::getTableName($this->_alias)); } elseif ($config instanceof Zend_Config) { $config = $config->toArray(); } if (isset($config['alias']) && !isset($config['name'])) { $this->_alias = $config['alias']; $config['name'] = Qs_Db::getTableName($this->_alias); } if (!isset($config['name'])) { if (null === $this->_alias) { throw new Zend_Db_Table_Exception('property _alias is not defined'); } $config['name'] = Qs_Db::getTableName($this->_alias); } if (!isset($config['db'])) { $config['db'] = Qs_Db::getInstance(); } parent::__construct($config); } public function getPrimary() { if (null === $this->_primary) { $this->_setupPrimaryKey(); } return (array) $this->_primary; } public function getMetaData() { $this->_setupMetadata(); return (array) $this->_metadata; } public function getName() { return $this->_name; } public function insert(array $data) { $date = date('Y-m-d H:i:s'); if (!isset($data['added'])) { $data['added'] = $date; } if (!isset($data['changed'])) { $data['changed'] = $date; } $this->unsetUnknownFields($data); return parent::insert($data); } public function update(array $data, $where) { if (!isset($data['changed'])) { $data['changed'] = date('Y-m-d H:i:s'); } $this->unsetUnknownFields($data); return parent::update($data, $where); } /** * @param array $list * @param array|null $data [OPTIONAL] * @param string $mode [OPTIONAL] * @return int * @throws Qs_Db_Obj_Exception */ public function insertList(array $list, array $data = null, $mode = self::INSERT_NORMAL) { if (empty($list)) { return 0; } if (!is_array($list) || !is_array(reset($list))) { throw new Qs_Db_Obj_Exception('Param $list is in wrong format'); } $data = (array) $data; $data['added'] = $data['changed'] = date('Y-m-d H:i:s'); $parts = array(); $fields = null; foreach ($list as $row) { $row = array_merge($data, $row); $row = Qs_Db::quoteRow($row, $this); if (null === $fields) { $fields = array_keys($row); } elseif ($fields !== array_keys($row)) { throw new Qs_Db_Obj_Exception('Inconsistent columns count'); } $parts[] = '(' . implode(', ', $row) . ')'; } $sql = 'INSERT'; if (self::INSERT_IGNORE === $mode) { $sql = 'INSERT IGNORE'; } elseif (self::INSERT_REPLACE === $mode) { $sql = 'REPLACE'; } $sql .= ' INTO ' . $this->_db->quoteIdentifier($this->getName()) . ' (`' . implode('`, `', $fields) . '`) VALUES ' . PHP_EOL . implode(', ' . PHP_EOL, $parts) . ';'; return $this->_db->query($sql)->rowCount(); } public function updateDataByKey(array $data, $key) { $this->unsetUnknownFields($data); if (empty($data)) { return false; } $where = $this->prepareWhere($key); return parent::update($data, $where); } public function updateDataBy(array $data, array $key) { $this->unsetUnknownFields($data); if (empty($data) || empty($key)) { return false; } $where = Qs_Db::getWhereSql($key); return parent::update($data, $where); } public function updateBy(array $data, array $key) { if (!isset($data['changed'])) { $data['changed'] = date('Y-m-d H:i:s'); } return $this->updateDataBy($data, $key); } public function updateByKey(array $data, $key) { if (!isset($data['changed'])) { $data['changed'] = date('Y-m-d H:i:s'); } return $this->updateDataByKey($data, $key); } public function save($data, $key) { if (($row = $this->findRow($key))) { $row->setFromArray($data); return $row->save(); } else { $row = $this->createRow($data); return $row->save(); } } /** * Search row by primary key * @param $key * @param bool $field * @param null $default * @return null */ public function search($key, $field = null, $default = null) { if (false === ($row = $this->findRow($key))) { return $default; } return Qs_Array::get($row->toArray(), $field, $default); } /** * Search row using custom filter * @param array $filter * @param bool $field * @param null $default * @return array|null */ public function searchBy(array $filter, $field = null, $default = null) { $select = $this->select(Qs_Db_Table::SELECT_WITH_FROM_PART); Qs_Db::filter($select, $filter, $this->_name); $select->limit(1); $result = $this->_db->fetchRow($select); return false === $result ? $result : Qs_Array::get($result, $field, $default); } /** * Search row by primary key * @param $key * @return bool | Zend_Db_Table_Row */ public function findRow($key) { /** @var $rowset Zend_Db_Table_Rowset */ $rowset = call_user_func_array(array($this, 'find'), (array) $key); if (count($rowset)) { return $rowset->current(); } return false; } /** * Returns associative array * @param array|string $columns [OPTIONAL] * @param null|array $where [OPTIONAL] Array of conditions compatible with Qs_Db::getWhereSql() * @param null|string $order [OPTIONAL] Sorter filed * @param null|int $limit [OPTIONAL] * @return array * @throws Qs_Db_Exception */ public function get4Select($columns = '*', $where = null, $order = null, $limit = null) { $select = $this->_db->select(); $meta = $this->info(Zend_Db_Table::METADATA); if ('*' == $columns) { $columns = array_keys($meta); if (in_array('sorter', $columns)) { $columns = Qs_Array::excludeValue($columns, 'sorter'); } $columnsCount = count($columns); if ($columnsCount) { if (count($columns) < 2) { $column = array_pop($columns); $columns['key'] = $column; $columns['value'] = $column; } else if (count($columns) > 2) { $columns = array_slice($columns, 0, 2); } } else { throw new Qs_Db_Exception('Wrong columns count'); } } $select->from($this->_name, $columns); if (null !== $where) { $select->where(Qs_Db::getWhereSql($where)); } if (null === $order) { // automatic order initialization if (array_key_exists('sorter', $meta)) { $order = 'sorter'; } else if ('*' == $columns) { $primary = $this->info(Zend_Db_Table::PRIMARY); $fields = Qs_Array::excludeArray($meta, $primary); if ($fields) { $order = key($fields); } else { $order = $primary; } } else if (is_array($columns)) { $order = next($columns); } } if ($order) { // use $order = false to disable automatic initialization $select->order($order); } if (null !== $limit) { $select->limit($limit); } return (is_array($columns) && count($columns) > 2) ? $this->_db->fetchAssoc($select) : $this->_db->fetchPairs($select); } public function saveRelationIds(array $ids, $referenceField, array $key) { $list = Qs_Array::toList($ids, $referenceField); return $this->saveRelations($list, $key); } public function saveRelations(array &$list, array $key) { $primary = $this->getPrimary(); $oldList = $this->getList('*', $key); $sorter = $this->fieldExists('sorter') ? 0 : null; foreach ($list as &$row) { if (null !== $sorter && !array_key_exists('sorter', $row)) { $row['sorter'] = $sorter++; } $row = array_merge($row, $key); $dataKey = array_combine($primary, Qs_Array::map($row, $primary)); if (false !== ($index = Qs_Array::search($oldList, $dataKey))) { $dataRow = array_intersect_key($oldList[$index], $row); unset($oldList[$index]); $row = array_merge($dataRow, $row); $this->updateByKey($row, $dataKey); continue; } $rowKey = $this->insert($row); if (!is_array($rowKey)) { $rowKey = array_combine($this->getPrimary(), (array) $rowKey); } $row = array_merge($row, $rowKey); } foreach ($oldList as $oldRow) { $oldKey = array_combine($primary, Qs_Array::map($oldRow, $primary)); $this->deleteByKey($oldKey); } return $this; } public function getList($columns = '*', array $filter = [], $fetch = null) { return $this->_db->fetchAll($this->getListSelect($columns, $filter), [], $fetch); } public function fetchColumn($column, array $filter = []) { return $this->getList([$column], $filter, Qs_Db::FETCH_COLUMN); } public function getPairs($columns = '*', array $filter = []) { return $this->_db->fetchPairs($this->getListSelect($columns, $filter)); } public function fetchOne($column, array $filter = []) { $select = $this->getListSelect($column, $filter); return $this->_db->fetchOne($select); } public function getListSelect($columns = '*', array $filter = []) { $select = $this->_db->select(); $select->from($this->_alias ? Qs_Db::getPair($this->_alias) : $this->_name, $columns); $select->where(Qs_Db::getWhereSql($filter)); if ($this->fieldExists('sorter')) { $select->order('sorter'); } return $select; } public function fieldExists($name) { return array_key_exists($name, $this->info(Zend_Db_Table::METADATA)); } public function deleteByKey($key) { $where = $this->prepareWhere($key); return parent::delete($where); } public function deleteBy(array $filter) { $select = $this->select(Qs_Db_Table::SELECT_WITH_FROM_PART); Qs_Db::filter($select, $filter, $this->_name); $where = $select->getPart(Zend_Db_Select::WHERE); $where = implode(' ', $where); $where = trim($where); if (!$where) { throw new Qs_Exception('Delete By: wrong filter'); } return parent::delete($where); } public function unsetUnknownFields(&$data) { $metaData = $this->getMetaData(); $data = array_intersect_key($data, $metaData); } public function prepareWhere($args, $alias = null) { if (!is_array($args)) { $args = (array)$args; } $args = array_values($args); $this->_setupPrimaryKey(); $keyNames = array_values((array) $this->_primary); if (count($args) < count($keyNames)) { // require_once 'Zend/Db/Table/Exception.php'; throw new Zend_Db_Table_Exception("Too few columns for the primary key"); } if (count($args) > count($keyNames)) { // require_once 'Zend/Db/Table/Exception.php'; throw new Zend_Db_Table_Exception("Too many columns for the primary key"); } $whereList = array(); $numberTerms = 0; foreach ($args as $keyPosition => $keyValues) { // Coerce the values to an array. // Don't simply typecast to array, because the values // might be Zend_Db_Expr objects. if (!is_array($keyValues)) { $keyValues = array($keyValues); } if ($numberTerms == 0) { $numberTerms = count($keyValues); } else if (count($keyValues) != $numberTerms) { // require_once 'Zend/Db/Table/Exception.php'; throw new Zend_Db_Table_Exception("Missing value(s) for the primary key"); } for ($i = 0; $i < count($keyValues); ++$i) { if (!isset($whereList[$i])) { $whereList[$i] = array(); } $whereList[$i][$keyPosition] = $keyValues[$i]; } } $whereClause = null; if (count($whereList)) { $whereOrTerms = array(); foreach ($whereList as $keyValueSets) { $whereAndTerms = array(); foreach ($keyValueSets as $keyPosition => $keyValue) { $type = $this->_metadata[$keyNames[$keyPosition]]['DATA_TYPE']; $tableName = $this->_db->quoteTableAs((null === $alias) ? $this->_name : $alias, null, true); $columnName = $this->_db->quoteIdentifier($keyNames[$keyPosition], true); $whereAndTerms[] = $this->_db->quoteInto( $tableName . '.' . $columnName . ' = ?', $keyValue, $type); } $whereOrTerms[] = '(' . implode(' AND ', $whereAndTerms) . ')'; } $whereClause = '(' . implode(' OR ', $whereOrTerms) . ')'; } while (substr($whereClause, 0, 1) == '(' && substr($whereClause, -1) == ')') { $whereClause = substr($whereClause, 1, -1); } return $whereClause; } public function emptyToNull(array &$data) { $meta = $this->getMetaData(); foreach ($data as $field => &$value) { if ('' === $value && array_key_exists($field, $meta) && $meta[$field]['NULLABLE']) { $value = null; } } return $this; } }