PHP Pimcore\Db类代码示例

 public function showAction()
 {
     $offset = $this->getParam("start");
     $limit = $this->getParam("limit");
     $orderby = "ORDER BY id DESC";
     $sortingSettings = \Pimcore\Admin\Helper\QueryParams::extractSortingSettings($this->getAllParams());
     if ($sortingSettings['orderKey']) {
         $orderby = "ORDER BY " . $sortingSettings['orderKey'] . " " . $sortingSettings['order'];
     }
     $queryString = " WHERE 1=1";
     if ($this->getParam("priority") != "-1" && ($this->getParam("priority") == "0" || $this->getParam("priority"))) {
         $levels = [];
         foreach (["emergency", "alert", "critical", "error", "warning", "notice", "info", "debug"] as $level) {
             $levels[] = "priority = '" . $level . "'";
             if ($this->getParam("priority") == $level) {
                 break;
             }
         }
         $queryString .= " AND (" . implode(" OR ", $levels) . ")";
     }
     if ($this->getParam("fromDate")) {
         $datetime = $this->getParam("fromDate");
         if ($this->getParam("fromTime")) {
             $datetime = substr($datetime, 0, 11) . $this->getParam("fromTime") . ":00";
         }
         $queryString .= " AND timestamp >= '" . $datetime . "'";
     }
     if ($this->getParam("toDate")) {
         $datetime = $this->getParam("toDate");
         if ($this->getParam("toTime")) {
             $datetime = substr($datetime, 0, 11) . $this->getParam("toTime") . ":00";
         }
         $queryString .= " AND timestamp <= '" . $datetime . "'";
     }
     if ($this->getParam("component")) {
         $queryString .= " AND component =  '" . $this->getParam("component") . "'";
     }
     if ($this->getParam("relatedobject")) {
         $queryString .= " AND relatedobject = " . $this->getParam("relatedobject");
     }
     if ($this->getParam("message")) {
         $queryString .= " AND message like '%" . $this->getParam("message") . "%'";
     }
     $db = Db::get();
     $count = $db->fetchCol("SELECT count(*) FROM " . \Pimcore\Log\Handler\ApplicationLoggerDb::TABLE_NAME . $queryString);
     $total = $count[0];
     $result = $db->fetchAll("SELECT * FROM " . \Pimcore\Log\Handler\ApplicationLoggerDb::TABLE_NAME . $queryString . " {$orderby} LIMIT {$offset}, {$limit}");
     $errorDataList = array();
     if (!empty($result)) {
         foreach ($result as $r) {
             $parts = explode("/", $r['filelink']);
             $filename = $parts[count($parts) - 1];
             $fileobject = str_replace(PIMCORE_DOCUMENT_ROOT, "", $r['fileobject']);
             $errorData = array("id" => $r['id'], "pid" => $r['pid'], "message" => $r['message'], "timestamp" => $r['timestamp'], "priority" => $this->getPriorityName($r['priority']), "filename" => $filename, "fileobject" => $fileobject, "relatedobject" => $r['relatedobject'], "component" => $r['component'], "source" => $r['source']);
             $errorDataList[] = $errorData;
         }
     }
     $results = array("p_totalCount" => $total, "p_results" => $errorDataList);
     $this->_helper->json($results);
 }

 /**
  * @param $file
  * @throws \Zend_Db_Adapter_Exception
  */
 public function insertDump($file)
 {
     $sql = file_get_contents($file);
     //replace document root placeholder with current document root
     $docRoot = str_replace("\\", "/", PIMCORE_DOCUMENT_ROOT);
     // Windows fix
     $sql = str_replace("~~DOCUMENTROOT~~", $docRoot, $sql);
     // we have to use the raw connection here otherwise \Zend_Db uses prepared statements, which causes problems with inserts (: placeholders)
     // and mysqli causes troubles because it doesn't support multiple queries
     if ($this->db->getResource() instanceof \Zend_Db_Adapter_Mysqli) {
         $mysqli = $this->db->getConnection();
         $mysqli->multi_query($sql);
         // loop through results, because ->multi_query() is asynchronous
         do {
             if ($result = $mysqli->store_result()) {
                 $mysqli->free_result();
             }
         } while ($mysqli->next_result());
     } elseif ($this->db->getResource() instanceof \Zend_Db_Adapter_Pdo_Mysql) {
         $this->db->getConnection()->exec($sql);
     }
     \Pimcore\Db::reset();
     // set the id of the system user to 0
     $this->db->update("users", ["id" => 0], $this->db->quoteInto("name = ?", "system"));
 }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     $storeId = $input->getArgument('storeId');
     if (!is_numeric($storeId)) {
         throw new \Exception('Invalid store ID');
     }
     $db = Db::get();
     $tableList = $db->fetchAll("show tables like 'object_classificationstore_data_%'");
     foreach ($tableList as $table) {
         $theTable = current($table);
         $sql = "delete from " . $theTable . " where keyId In (select id from classificationstore_keys where storeId = " . $db->quote($storeId) . ")";
         echo $sql . "\n";
         $db->query($sql);
     }
     $tableList = $db->fetchAll("show tables like 'object_classificationstore_groups_%'");
     foreach ($tableList as $table) {
         $theTable = current($table);
         $sql = "delete from " . $theTable . " where groupId In (select id from classificationstore_groups where storeId = " . $db->quote($storeId) . ")";
         echo $sql . "\n";
         $db->query($sql);
     }
     $sql = "delete from classificationstore_keys where storeId = " . $db->quote($storeId);
     echo $sql . "\n";
     $db->query($sql);
     $sql = "delete from classificationstore_groups where storeId = " . $db->quote($storeId);
     echo $sql . "\n";
     $db->query($sql);
     $sql = "delete from classificationstore_collections where storeId = " . $db->quote($storeId);
     echo $sql . "\n";
     $db->query($sql);
     $sql = "delete from classificationstore_stores where id = " . $db->quote($storeId);
     echo $sql . "\n";
     $db->query($sql);
     Cache::clearAll();
 }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     // display error message
     if (!$input->getOption("mode")) {
         $this->writeError("Please specify the mode!");
         exit;
     }
     $db = \Pimcore\Db::get();
     if ($input->getOption("mode") == "optimize") {
         $tables = $db->fetchAll("SHOW TABLES");
         foreach ($tables as $table) {
             $t = current($table);
             try {
                 \Logger::debug("Running: OPTIMIZE TABLE " . $t);
                 $db->query("OPTIMIZE TABLE " . $t);
             } catch (\Exception $e) {
                 \Logger::error($e);
             }
         }
     } elseif ($input->getOption("mode") == "warmup") {
         $tables = $db->fetchAll("SHOW TABLES");
         foreach ($tables as $table) {
             $t = current($table);
             try {
                 \Logger::debug("Running: SELECT COUNT(*) FROM {$t}");
                 $res = $db->fetchOne("SELECT COUNT(*) FROM {$t}");
                 \Logger::debug("Result: " . $res);
             } catch (\Exception $e) {
                 \Logger::error($e);
             }
         }
     }
 }

 /**
  * @param $classId
  * @param null $idField
  * @param null $storetable
  * @param null $querytable
  * @param null $relationtable
  */
 public function __construct($classId, $idField = null, $storetable = null, $querytable = null, $relationtable = null)
 {
     $this->db = \Pimcore\Db::get();
     $this->fields = array();
     $this->relations = array();
     $this->fieldIds = array();
     $this->deletionFieldIds = array();
     $this->fieldDefinitions = [];
     if ($storetable == null) {
         $this->storetable = self::STORE_TABLE . $classId;
     } else {
         $this->storetable = $storetable;
     }
     if ($querytable == null) {
         $this->querytable = self::QUERY_TABLE . $classId;
     } else {
         $this->querytable = $querytable;
     }
     if ($relationtable == null) {
         $this->relationtable = self::RELATION_TABLE . $classId;
     } else {
         $this->relationtable = $relationtable;
     }
     if ($idField == null) {
         $this->idField = self::ID_FIELD;
     } else {
         $this->idField = $idField;
     }
 }

 public function indexAction()
 {
     $this->enableLayout();
     // get a list of news objects and order them by date
     $blogList = new Object\BlogArticle\Listing();
     $blogList->setOrderKey("date");
     $blogList->setOrder("DESC");
     $conditions = [];
     if ($this->getParam("category")) {
         $conditions[] = "categories LIKE " . $blogList->quote("%," . (int) $this->getParam("category") . ",%");
     }
     if ($this->getParam("archive")) {
         $conditions[] = "DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%c') = " . $blogList->quote($this->getParam("archive"));
     }
     if (!empty($conditions)) {
         $blogList->setCondition(implode(" AND ", $conditions));
     }
     $paginator = \Zend_Paginator::factory($blogList);
     $paginator->setCurrentPageNumber($this->getParam('page'));
     $paginator->setItemCountPerPage(5);
     $this->view->articles = $paginator;
     // get all categories
     $categories = Object\BlogCategory::getList();
     // this is an alternative way to get an object list
     $this->view->categories = $categories;
     // archive information, we have to do this in pure SQL
     $db = \Pimcore\Db::get();
     $ranges = $db->fetchCol("SELECT DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%c') as ranges FROM object_5 GROUP BY DATE_FORMAT(FROM_UNIXTIME(date), '%b-%Y') ORDER BY ranges ASC");
     $this->view->archiveRanges = $ranges;
 }

 /**
  * @return \Zend_Db_Adapter_Abstract
  */
 protected function getDb()
 {
     if (!$this->db) {
         // we're using a new mysql connection here to avoid problems with active (nested) transactions
         \Logger::debug("Initialize dedicated MySQL connection for the cache adapter");
         $this->db = Db::getConnection();
     }
     return $this->db;
 }

 /**
  * Creates a condition string from the passed ExtJs filter definitions
  *
  * @param $filterString
  * @param array $matchExact
  * @param bool $returnString
  * @param array $callbacks
  * @return array|string
  * @throws \Exception
  */
 public static function getFilterCondition($filterString, $matchExact = ['id', 'o_id'], $returnString = true, $callbacks = [])
 {
     if (!$filterString) {
         return '';
     }
     $conditions = [];
     $filters = json_decode($filterString);
     $db = \Pimcore\Db::get();
     foreach ($filters as $f) {
         if ($f->type == 'string') {
             if (in_array($f->property, $matchExact)) {
                 $conditions[$f->property][] = ' ' . $db->getQuoteIdentifierSymbol() . $f->property . $db->getQuoteIdentifierSymbol() . " = " . $db->quote($f->value) . ' ';
             } else {
                 $conditions[$f->property][] = ' ' . $db->getQuoteIdentifierSymbol() . $f->property . $db->getQuoteIdentifierSymbol() . " LIKE " . $db->quote("%" . $f->value . "%") . ' ';
             }
         } elseif ($f->type == 'numeric') {
             if ($f->operator == 'eq') {
                 $symbol = ' = ';
             } elseif ($f->operator == 'lt') {
                 $symbol = ' < ';
             } elseif ($f->operator == 'gt') {
                 $symbol = ' > ';
             }
             $conditions[$f->property][] = ' ' . $db->getQuoteIdentifierSymbol() . $f->property . $db->getQuoteIdentifierSymbol() . $symbol . $db->quote($f->value) . ' ';
         } elseif ($f->type == 'date') {
             /**
              * make sure you pass the date as timestamp
              *
              * filter: {type : 'date',dateFormat: 'timestamp'}
              */
             $date = Carbon::createFromTimestamp($f->value)->setTime(0, 0, 0);
             if ($f->operator == 'eq') {
                 $conditions[$f->property][] = ' ' . $f->property . ' >= ' . $db->quote($date->getTimestamp());
                 $conditions[$f->property][] = ' ' . $f->property . ' <= ' . $db->quote($date->addDay(1)->subSecond(1)->getTimestamp());
             } elseif ($f->operator == 'lt') {
                 $conditions[$f->property][] = ' ' . $f->property . ' < ' . $db->quote($date->getTimestamp());
             } elseif ($f->operator == 'gt') {
                 $conditions[$f->property][] = ' ' . $f->property . ' > ' . $db->quote($date->addDay(1)->subSecond(1)->getTimestamp());
             }
         } else {
             throw new \Exception("Filer of type " . $f->type . " not jet supported.");
         }
     }
     $conditionsGrouped = [];
     foreach ($conditions as $fieldName => $fieldConditions) {
         if (count($fieldConditions) > 1) {
             $conditionsGrouped[$fieldName] = ' (' . implode(' AND ', $fieldConditions) . ') ';
         } else {
             $conditionsGrouped[$fieldName] = $fieldConditions[0];
         }
     }
     if ($returnString) {
         return implode(' OR ', $conditionsGrouped);
     } else {
         return $conditionsGrouped;
     }
 }

 public function showAction()
 {
     $offset = $this->_getParam("start");
     $limit = $this->_getParam("limit");
     $orderby = "ORDER BY id DESC";
     $sortingSettings = \Pimcore\Admin\Helper\QueryParams::extractSortingSettings($this->getAllParams());
     if ($sortingSettings['orderKey']) {
         $orderby = "ORDER BY " . $sortingSettings['orderKey'] . " " . $sortingSettings['order'];
     }
     $queryString = " WHERE 1=1";
     if ($this->_getParam("priority") != "-1" && ($this->_getParam("priority") == "0" || $this->_getParam("priority"))) {
         $queryString .= " AND priority <= " . $this->_getParam("priority");
     } else {
         $queryString .= " AND (priority = 6 OR priority = 5 OR priority = 4 OR priority = 3 OR priority = 2 OR priority = 1 OR priority = 0)";
     }
     if ($this->_getParam("fromDate")) {
         $datetime = $this->_getParam("fromDate");
         if ($this->_getParam("fromTime")) {
             $datetime = substr($datetime, 0, 11) . $this->_getParam("fromTime") . ":00";
         }
         $queryString .= " AND timestamp >= '" . $datetime . "'";
     }
     if ($this->_getParam("toDate")) {
         $datetime = $this->_getParam("toDate");
         if ($this->_getParam("toTime")) {
             $datetime = substr($datetime, 0, 11) . $this->_getParam("toTime") . ":00";
         }
         $queryString .= " AND timestamp <= '" . $datetime . "'";
     }
     if ($this->_getParam("component")) {
         $queryString .= " AND component =  '" . $this->_getParam("component") . "'";
     }
     if ($this->_getParam("relatedobject")) {
         $queryString .= " AND relatedobject = " . $this->_getParam("relatedobject");
     }
     if ($this->_getParam("message")) {
         $queryString .= " AND message like '%" . $this->_getParam("message") . "%'";
     }
     $db = Db::get();
     $count = $db->fetchCol("SELECT count(*) FROM " . Log\Helper::ERROR_LOG_TABLE_NAME . $queryString);
     $total = $count[0];
     $result = $db->fetchAll("SELECT * FROM " . Log\Helper::ERROR_LOG_TABLE_NAME . $queryString . " {$orderby} LIMIT {$offset}, {$limit}");
     $errorDataList = array();
     if (!empty($result)) {
         foreach ($result as $r) {
             $parts = explode("/", $r['filelink']);
             $filename = $parts[count($parts) - 1];
             $fileobject = str_replace(PIMCORE_DOCUMENT_ROOT, "", $r['fileobject']);
             $errorData = array("id" => $r['id'], "message" => $r['message'], "timestamp" => $r['timestamp'], "priority" => $this->getPriorityName($r['priority']), "filename" => $filename, "fileobject" => $fileobject, "relatedobject" => $r['relatedobject'], "component" => $r['component'], "source" => $r['source']);
             $errorDataList[] = $errorData;
         }
     }
     $results = array("p_totalCount" => $total, "p_results" => $errorDataList);
     $this->_helper->json($results);
 }

 /**
  * @static
  * @return string[]
  */
 public static function getPriorities()
 {
     $priorities = array();
     $priorityNames = array("debug" => "DEBUG", "info" => "INFO", "notice" => "NOTICE", "warning" => "WARN", "error" => "ERR", "critical" => "CRIT", "alert" => "ALERT", "emergency" => "EMERG");
     $db = Database::get();
     $priorityNumbers = $db->fetchCol("SELECT priority FROM " . \Pimcore\Log\Handler\ApplicationLoggerDb::TABLE_NAME . " WHERE NOT ISNULL(priority) GROUP BY priority;");
     foreach ($priorityNumbers as $priorityNumber) {
         $priorities[$priorityNumber] = $priorityNames[$priorityNumber];
     }
     return $priorities;
 }

 /**
  * @param \Zend_Controller_Request_Abstract $request
  * @return bool|void
  */
 public function routeShutdown(\Zend_Controller_Request_Abstract $request)
 {
     if (!Tool::useFrontendOutputFilters($request)) {
         return $this->disable();
     }
     $db = \Pimcore\Db::get();
     $enabled = $db->fetchOne("SELECT id FROM targeting_personas UNION SELECT id FROM targeting_rules LIMIT 1");
     if (!$enabled) {
         return $this->disable();
     }
     if ($request->getParam("document") instanceof Document\Page) {
         $this->document = $request->getParam("document");
     }
 }

 /**
  *
  */
 public function writeLog()
 {
     $code = (string) $this->getResponse()->getHttpResponseCode();
     $db = \Pimcore\Db::get();
     try {
         $uri = $this->getRequest()->getScheme() . "://" . $this->getRequest()->getHttpHost() . $this->getRequest()->getRequestUri();
         $exists = $db->fetchOne("SELECT date FROM http_error_log WHERE uri = ?", $uri);
         if ($exists) {
             $db->query("UPDATE http_error_log SET `count` = `count` + 1, date = ? WHERE uri = ?", [time(), $uri]);
         } else {
             $db->insert("http_error_log", ["uri" => $uri, "code" => (int) $code, "parametersGet" => serialize($_GET), "parametersPost" => serialize($_POST), "cookies" => serialize($_COOKIE), "serverVars" => serialize($_SERVER), "date" => time(), "count" => 1]);
         }
     } catch (\Exception $e) {
         \Logger::error("Unable to log http error");
         \Logger::error($e);
     }
 }

 /**
  * @param $key
  * @param $language
  * @return \Pimcore\Model\Metadata\Predefined
  */
 public static function getByKeyAndLanguage($key, $language, $targetSubtype = null)
 {
     $db = \Pimcore\Db::get();
     $list = new self();
     $condition = "name = " . $db->quote($key);
     if ($language) {
         $condition .= " AND language = " . $db->quote($language);
     } else {
         $condition .= " AND (language = '' OR LANGUAGE IS NULL)";
     }
     if ($targetSubtype) {
         $condition .= " AND targetSubtype = " . $db->quote($targetSubtype);
     }
     $list->setCondition($condition);
     $list = $list->load();
     if ($list) {
         return $list[0];
     }
     return null;
 }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     // clear all data
     $db = \Pimcore\Db::get();
     $db->query("TRUNCATE `search_backend_data`;");
     $elementsPerLoop = 100;
     $types = ["asset", "document", "object"];
     foreach ($types as $type) {
         $listClassName = "\\Pimcore\\Model\\" . ucfirst($type) . "\\Listing";
         $list = new $listClassName();
         if (method_exists($list, "setUnpublished")) {
             $list->setUnpublished(true);
         }
         $elementsTotal = $list->getTotalCount();
         for ($i = 0; $i < ceil($elementsTotal / $elementsPerLoop); $i++) {
             $list->setLimit($elementsPerLoop);
             $list->setOffset($i * $elementsPerLoop);
             $this->output->writeln("Processing " . $type . ": " . ($list->getOffset() + $elementsPerLoop) . "/" . $elementsTotal);
             $elements = $list->load();
             foreach ($elements as $element) {
                 try {
                     $searchEntry = Search\Backend\Data::getForElement($element);
                     if ($searchEntry instanceof Search\Backend\Data and $searchEntry->getId() instanceof Search\Backend\Data\Id) {
                         $searchEntry->setDataFromElement($element);
                     } else {
                         $searchEntry = new Search\Backend\Data($element);
                     }
                     $searchEntry->save();
                 } catch (\Exception $e) {
                     Logger::err($e);
                 }
             }
             \Pimcore::collectGarbage();
         }
     }
     $db->query("OPTIMIZE TABLE search_backend_data;");
 }

 public function portletModificationStatisticsAction()
 {
     $db = \Pimcore\Db::get();
     $days = 31;
     $startDate = mktime(23, 59, 59, date("m"), date("d"), date("Y"));
     $currentDate = $startDate;
     $data = [];
     for ($i = 0; $i < $days; $i++) {
         // documents
         $end = $startDate - $i * 86400;
         $start = $end - 86399;
         $o = $db->fetchOne("SELECT COUNT(*) AS count FROM objects WHERE o_modificationDate > " . $start . " AND o_modificationDate < " . $end);
         $a = $db->fetchOne("SELECT COUNT(*) AS count FROM assets WHERE modificationDate > " . $start . " AND modificationDate < " . $end);
         $d = $db->fetchOne("SELECT COUNT(*) AS count FROM documents WHERE modificationDate > " . $start . " AND modificationDate < " . $end);
         $date = new \DateTime();
         $date->setTimestamp($start);
         $data[] = ["timestamp" => $start, "datetext" => $date->format("Y-m-d"), "objects" => (int) $o, "documents" => (int) $d, "assets" => (int) $a];
     }
     $data = array_reverse($data);
     $this->_helper->json(["data" => $data]);
 }