PHP ipTable函数代码示例

 public static function checkIfChildren($parentIds, $id)
 {
     $parentIds = implode(',', $parentIds);
     if (empty($parentIds)) {
         return false;
     }
     $table = ipTable('page');
     $sql = "SELECT `id` FROM {$table} WHERE `parentId` IN ({$parentIds}) AND `isVisible` = 1 AND `isDeleted` = 0 ";
     $results = ipDb()->fetchAll($sql);
     $ids = array();
     $found = false;
     foreach ($results as $result) {
         $ids[] = $result['id'];
         if ($result['id'] == $id) {
             $found = true;
             break;
         }
     }
     if ($found) {
         return true;
     } elseif (!empty($ids)) {
         return self::checkIfChildren($ids, $id);
     } else {
         return false;
     }
 }

 public static function ipWidgetDuplicated($data)
 {
     $oldId = $data['oldWidgetId'];
     $newId = $data['newWidgetId'];
     $newRevisionId = ipDb()->selectValue('widget', 'revisionId', array('id' => $newId));
     $widgetTable = ipTable('widget');
     $sql = "\n            UPDATE\n                {$widgetTable}\n            SET\n                `blockName` = REPLACE(`blockName`, 'block_" . (int) $oldId . "', 'block_" . (int) $newId . "')\n            WHERE\n                `revisionId` = :newRevisionId\n            ";
     ipDb()->execute($sql, array('newRevisionId' => $newRevisionId));
 }

 /**
  * Drops the datatable repository db table
  */
 private function dropDataTableRepository()
 {
     $ipTable = ipTable(TableRepository::DATA_TABLE_REPOSITORY);
     $sql = "\n            DROP TABLE {$ipTable}\n        ";
     try {
         ipDb()->execute($sql);
     } catch (\Ip\Exception\Db $e) {
         ipLog()->error("Could not drop repository table. Statement: {$sql}, Message: " . $e->getMessage());
     }
 }

 public static function unbindFile($file, $plugin, $instanceId, $baseDir = 'file/repository/')
 {
     $condition = array('fileName' => $file, 'plugin' => $plugin, 'instanceId' => $instanceId, 'baseDir' => $baseDir);
     $sql = 'DELETE FROM ' . ipTable('repository_file') . '
             WHERE filename = :fileName
             AND plugin = :plugin
             AND instanceId = :instanceId
             AND baseDir = :baseDir
             LIMIT 1';
     // it is important to delete only one record
     ipDb()->execute($sql, $condition);
     $usages = self::whoUsesFile($file);
     if (empty($usages)) {
         $reflectionModel = ReflectionModel::instance();
         $reflectionModel->removeReflections($file);
     }
 }

    public function activate()
    {
        $sql = '
        CREATE TABLE IF NOT EXISTS
           ' . ipTable(\Plugin\MasonryGrid\Config::TABLE_NAME) . '
        (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `itemOrder` double,
        `widgetId` int(11),
        `title` varchar(255),
        `image` varchar(255),
		`description` text,
		`url` varchar(255),
        `isVisible` int(1),
        PRIMARY KEY (`id`)
        )';
        ipDb()->execute($sql);
    }

 public static function getKeywordData()
 {
     $pageTable = ipTable('page');
     $keywordlistTable = ipTable(self::TABLE_NAME);
     $langCode = ipContent()->getCurrentLanguage()->getCode();
     $excludeFields = ' AND p.IsVisible = 1 AND p.IsDisabled = 0 AND p.IsDeleted = 0';
     $securedPages = ipGetOption('Keywordlist.securedPages') == 1 ? '' : 'AND p.IsSecured = 0';
     if (ipGetOption('Keywordlist.useMetadata') == 1) {
         $sql = "SELECT p.id, p.title, p.urlPath, p.keywords, p.description FROM {$pageTable} p WHERE p.languageCode = '{$langCode}' AND p.keywords != '' {$excludeFields} {$securedPages}";
     } else {
         $sql = "SELECT p.id, p.title, p.urlPath, k.keywords, k.description FROM {$pageTable} p, {$keywordlistTable} k WHERE p.id = k.pageid AND p.languageCode = '{$langCode}' AND k.keywords != '' {$excludeFields} {$securedPages}";
     }
     $keywordArray = array();
     $entries = array();
     $entries = ipDb()->fetchAll($sql);
     $existingChars = array();
     if (!empty($entries)) {
         // prepare array with keywords and pages
         $keywordArray = array();
         $charSource = array('ä', 'ö', 'ü', 'ß', 'Ä', 'Ö', 'Ü');
         $charReplace = array('a', 'o', 'u', 's', 'A', 'O', 'U');
         foreach ($entries as $entry) {
             $keywords = array_map('trim', explode(",", $entry['keywords']));
             foreach ($keywords as $keyword) {
                 $firstChar = strtoupper(str_replace($charSource, $charReplace, mb_substr($keyword, 0, 1, 'UTF-8')));
                 if (is_numeric($firstChar)) {
                     $firstChar = '0-9';
                 }
                 $keywordArray[] = array('keyword' => $keyword, 'char' => $firstChar, 'id' => $entry['id'], 'title' => $entry['title'], 'description' => $entry['description'], 'urlPath' => $entry['urlPath']);
             }
         }
         // sorting keywordarray depending first char of keyword
         foreach ($keywordArray as $key => $row) {
             $char[$key] = $row['char'];
         }
         array_multisort($char, SORT_ASC, SORT_STRING, $keywordArray);
         $existingChars = array_unique($char);
     }
     $keywordData = array();
     $keywordData['entries'] = $keywordArray;
     $keywordData['chars'] = $existingChars;
     return $keywordData;
 }

    public function activate()
    {
        $sql = '
        CREATE TABLE IF NOT EXISTS
           ' . ipTable('slideshow') . '
        (
        `id` int(11) NOT NULL AUTO_INCREMENT,
		`widgetId` int(11),
        `slideshowOrder` double,
        `title` varchar(255),
        `text` varchar(255),
        `url` varchar(255),
        `Enabled` boolean,
        `image` varchar(255),
		`lang` varchar(3),
		`itemOrder` double,
		`isVisible` int(1),
        PRIMARY KEY (`id`)
        )';
        ipDb()->execute($sql);
    }

 protected static function updateTableUrl($oldUrl, $newUrl, $table, $keyFields)
 {
     $old = parse_url($oldUrl);
     $new = parse_url($newUrl);
     $oldPart = $old['host'] . $old['path'];
     $newPart = $new['host'] . $new['path'];
     $quotedPart = substr(ipDb()->getConnection()->quote('://' . $oldPart), 1, -1);
     $search = '%' . addslashes($quotedPart) . '%';
     $tableWithPrefix = ipTable($table);
     $records = ipDb()->fetchAll("SELECT * FROM {$tableWithPrefix} WHERE `value` LIKE ?", array($search));
     if (!$records) {
         return;
     }
     if ($newUrl == ipConfig()->baseUrl()) {
         //the website has been moved
         $search = '%\\b(https?://)' . preg_quote($oldPart, '%') . '%';
     } else {
         //internal page url has changed
         // \b - start at word boundary
         // (https?://) - protocol
         // (/?) - allow optional slash at the end of url
         // (?= ) - symbols expected after url
         // \Z - end of subject or end of line
         $search = '%\\b(https?://)' . preg_quote($oldPart, '%') . '(/?)(?=["\'?]|\\s|\\Z)%';
     }
     foreach ($records as $row) {
         // ${1} - protocol, ${2} - optional '/'
         $after = preg_replace($search, '${1}' . $newPart . '${2}', $row['value']);
         if ($after != $row['value']) {
             $where = array();
             foreach ($keyFields as $keyField) {
                 $where[$keyField] = $row[$keyField];
             }
             ipDb()->update($table, array('value' => $after), $where);
         }
     }
 }

 /**
  * @param null $from
  * @param null $till
  * @param string $orderBy
  * @param string $direction
  * @return \Ip\Page[]
  * @throws Exception
  */
 public function getChildren($from = null, $till = null, $orderBy = 'pageOrder', $direction = 'ASC')
 {
     switch ($orderBy) {
         case 'pageOrder':
         case 'title':
         case 'metaTitle':
         case 'createdAt':
         case 'updatedAt':
         case 'deletedAt':
             //do nothing;
             break;
         default:
             throw new \Ip\Exception("getChildren can't accept " . esc($orderBy) . " as an order field.");
     }
     if (strtoupper($direction) == 'ASC') {
         $direction = 'ASC';
     } else {
         $direction = 'DESC';
     }
     $table = ipTable('page');
     $sql = "\n        SELECT\n            *\n        FROM\n            {$table}\n        WHERE\n            parentId = :parentId AND\n            isVisible = 1 AND\n            isDeleted = 0\n        ORDER BY\n            " . $orderBy . "\n            " . $direction . "\n        ";
     $params = array('parentId' => $this->id);
     if ($from !== null || $till !== null) {
         $sql .= " LIMIT " . (int) $from . " , " . (int) $till;
     }
     $list = ipDb()->fetchAll($sql, $params);
     return static::createList($list);
 }

 /**
  * Removes pages that were deleted before given time
  *
  * @param string $timestamp in mysql format.
  * @return int Count of deleted pages.
  */
 public static function removeDeletedBefore($timestamp)
 {
     $table = ipTable('page');
     $pages = ipDb()->fetchAll("SELECT `id` FROM {$table} WHERE `isDeleted` = 1 AND `deletedAt` < ?", array($timestamp));
     foreach ($pages as $page) {
         static::removeDeletedPage($page['id']);
     }
 }

 public static function sentOrLockedCount($minutes)
 {
     $table = ipTable('email_queue');
     $sql = "select count(*) as `sent` from {$table} where\n        (`send` is not NULL and " . ipDb()->sqlMaxAge('send', $minutes, 'MINUTE') . ")\n        or (`lock` is not NULL and send is null) ";
     return ipDb()->fetchValue($sql);
 }

 /**
  *
  * Delete all not published revisions that are older than X days.
  * @param int $days
  */
 public static function removeOldRevisions($days)
 {
     //
     // 1) Dynamic Widgets (including revisions)
     // Dynamic widgets have an associated revision.
     // That revision's creation time and publication
     // state indicates if a widget should be removed
     // or not from corresponding db table 'ip_widget'.
     //
     $table = ipTable('revision');
     $sql = "\n            SELECT `revisionId` FROM {$table}\n            WHERE (" . ipDb()->sqlMinAge('createdAt', $days * 24, 'HOUR') . ") AND `isPublished` = 0\n        ";
     $revisionList = ipDb()->fetchColumn($sql);
     foreach ($revisionList as $revisionId) {
         \Ip\Internal\Content\Service::removeRevision($revisionId);
     }
     //
     // 2) Static Widgets (from static blocks only!)
     // Static widgets are presisted with revisionId=0.
     // Therefore, we've to make the time check on widget's
     // 'createdAt' column combined with 'isDeleted=1' flag
     // and 'revisionId=0' indicating widget's removal state.
     //
     $table = ipTable('widget');
     $sql = $sql = "\n            SELECT `id` FROM {$table}\n            WHERE (" . ipDb()->sqlMinAge('createdAt', $days * 24, 'HOUR') . ") \n            AND `revisionId` = 0 AND `isDeleted` = 1 AND `deletedAt` IS NOT NULL\n        ";
     $staticWidgetList = ipDb()->fetchColumn($sql);
     foreach ($staticWidgetList as $staticWidgetId) {
         \Ip\Internal\Content\Service::removeWidget($staticWidgetId);
     }
 }

 /**
  * Creates the DB table
  */
 private function createDbTableIfNotExists()
 {
     $ipTable = ipTable($this->_name);
     $attributeDefinition = '';
     foreach ($this->_columns as $column) {
         $attributeDefinition .= ' `' . $column['attribute'] . '` varchar(255),';
     }
     $sql = "\n          CREATE TABLE IF NOT EXISTS {$ipTable}\n          (\n            `id` int(11) NOT NULL AUTO_INCREMENT,\n            {$attributeDefinition}\n            PRIMARY KEY (`id`)\n          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;";
     try {
         ipDb()->execute($sql);
     } catch (\Ip\Exception\Db $e) {
         ipLog()->error("Could not create data table. Statement: {$sql}, Message: " . $e->getMessage());
         throw $e;
     }
 }

 private function removeReflectionRecords($file)
 {
     $dbh = ipDb()->getConnection();
     $sql = "\n        DELETE FROM\n          " . ipTable('repository_reflection') . "\n        WHERE\n          original = :original\n        ";
     $params = array('original' => $file);
     $q = $dbh->prepare($sql);
     $q->execute($params);
 }

 /**
  * @param int $revisionId
  * @return string
  */
 protected static function revisionFingerprint($revisionId)
 {
     $table = ipTable('widget');
     // compare revision content
     $sql = "\n            SELECT\n                `name`\n            FROM\n                {$table}\n            WHERE\n              `revisionId` = :revisionId\n              AND\n              `name` != 'Columns'\n              AND\n              `isDeleted` = 0\n            ORDER BY\n              blockName, `position`\n        ";
     $params = array('revisionId' => $revisionId);
     $widgetNames = ipDb()->fetchColumn($sql, $params);
     // compare revision content
     $sql = "\n            SELECT\n                `data`\n            FROM\n                {$table}\n            WHERE\n              `revisionId` = :revisionId\n              AND\n              `name` != 'Columns'\n              AND\n              `isDeleted` = 0\n            ORDER BY\n              blockName, `position`\n        ";
     $params = array('revisionId' => $revisionId);
     $widgetData = ipDb()->fetchColumn($sql, $params);
     $fingerprint = implode('***|***', $widgetNames) . '|||' . implode('***|***', $widgetData);
     return $fingerprint;
 }