PHP Symphony::Database方法代码示例

 public static function upgrade()
 {
     // Add date field options
     try {
         Symphony::Database()->query('
                 ALTER TABLE `tbl_fields_date`
                 ADD `calendar` enum("yes","no") COLLATE utf8_unicode_ci NOT NULL DEFAULT "no",
                 ADD `time` enum("yes","no") COLLATE utf8_unicode_ci NOT NULL DEFAULT "yes";
             ');
     } catch (Exception $ex) {
     }
     // Add namespace field to the cache table. RE: #2162
     try {
         Symphony::Database()->query('
                 ALTER TABLE `tbl_cache` ADD `namespace` VARCHAR(255) COLLATE utf8_unicode_ci;
             ');
     } catch (Exception $ex) {
     }
     // Add UNIQUE key constraint to the `hash` RE: #2163
     try {
         Symphony::Database()->import('
                 ALTER TABLE `tbl_cache` DROP INDEX `hash`;
                 ALTER TABLE `tbl_cache` ADD UNIQUE INDEX `hash` (`hash`)
             ');
     } catch (Exception $ex) {
     }
     // Update the version information
     return parent::upgrade();
 }

 public function grab(&$param_pool)
 {
     self::__init();
     $result = new XMLElement($this->dsParamROOTELEMENT);
     $rows = Symphony::Database()->fetch("SELECT *\n\t\t\t\tFROM `tbl_sessions` \n\t\t\t\tWHERE `session_data` != 'sym-|a:0:{}sym-members|a:0:{}' \n\t\t\t\tAND `session_data` REGEXP 'sym-members'\n\t\t\t\tAND `session_expires` > (UNIX_TIMESTAMP() - " . self::AGE . ") \n\t\t\t\tORDER BY `session_expires` DESC");
     $added = array();
     if (count($rows) > 0) {
         foreach ($rows as $r) {
             $raw = $r['session_data'];
             $data = self::session_real_decode($raw);
             if (!isset($data['sym-members'])) {
                 continue;
             }
             $record = ASDCLoader::instance()->query(sprintf("SELECT\n\t\t\t\t\t\t\t\temail.value AS `email`,\n\t\t\t\t\t\t\t\tMD5(email.value) AS `hash`,\n\t\t\t\t\t\t\t\tcreated_by.username AS `username`\n\t\t\t\t\t\t\n\t\t\t\t\t\t\tFROM `tbl_entries_data_%d` AS `created_by`\n\t\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `email` ON created_by.member_id = email.entry_id\n\t\t\t\t\t\t\tWHERE `created_by`.username = '%s'\n\t\t\t\t\t\t\tLIMIT 1", self::findFieldID('created-by', 'comments'), self::findFieldID('email-address', 'members'), ASDCLoader::instance()->escape($data['sym-members']['username'])));
             if ($record->length() == 0) {
                 continue;
             }
             $member = $record->current();
             // This is so we dont end up with accidental duplicates. No way to select
             // distinct via the SQL since we grab raw session data
             if (in_array($member->username, $added)) {
                 continue;
             }
             $added[] = $member->username;
             $result->appendChild(new XMLElement('member', General::sanitize($member->username), array('email-hash' => $member->hash)));
         }
     } else {
         $result->setValue('No Records Found.');
         //This should never happen!
     }
     return $result;
 }

 public function execute(&$param_pool)
 {
     $result = new XMLElement($this->dsParamROOTELEMENT);
     $type_sql = $parent_sql = null;
     if (trim($this->dsParamFILTERS['type']) != '') {
         $type_sql = $this->__processNavigationTypeFilter($this->dsParamFILTERS['type'], $this->__determineFilterType($this->dsParamFILTERS['type']));
     }
     if (trim($this->dsParamFILTERS['parent']) != '') {
         $parent_sql = $this->__processNavigationParentFilter($this->dsParamFILTERS['parent']);
     }
     // Build the Query appending the Parent and/or Type WHERE clauses
     $pages = Symphony::Database()->fetch(sprintf("\n\t\t\t\t\tSELECT DISTINCT p.id, p.title, p.handle, (SELECT COUNT(id) FROM `tbl_pages` WHERE parent = p.id) AS children\n\t\t\t\t\tFROM `tbl_pages` AS p\n\t\t\t\t\tLEFT JOIN `tbl_pages_types` AS pt ON (p.id = pt.page_id)\n\t\t\t\t\tWHERE 1 = 1\n\t\t\t\t\t%s\n\t\t\t\t\t%s\n\t\t\t\t\tORDER BY p.`sortorder` ASC\n\t\t\t\t", !is_null($parent_sql) ? $parent_sql : " AND p.parent IS NULL ", !is_null($type_sql) ? $type_sql : ""));
     if (!is_array($pages) || empty($pages)) {
         if ($this->dsParamREDIRECTONEMPTY == 'yes') {
             throw new FrontendPageNotFoundException();
         }
         $result->appendChild($this->__noRecordsFound());
     } else {
         // Build an array of all the types so that the page's don't have to do
         // individual lookups.
         $page_types = PageManager::fetchAllPagesPageTypes();
         foreach ($pages as $page) {
             $result->appendChild($this->__buildPageXML($page, $page_types));
         }
     }
     return $result;
 }

 private function buildTree($parent = null, $title = '')
 {
     if ($parent == null) {
         $results = Symphony::Database()->fetch('SELECT `id`, `title`, `handle`, `path` FROM `tbl_pages` WHERE `parent` IS NULL ORDER BY `sortorder` ASC;');
     } else {
         $results = Symphony::Database()->fetch('SELECT `id`, `title`, `handle`, `path` FROM `tbl_pages` WHERE `parent` = ' . $parent . ' ORDER BY `sortorder` ASC;');
     }
     $tree = array();
     foreach ($results as $result) {
         $info = array('handle' => $result['handle'], 'path' => $result['path']);
         if ($result['path'] == null) {
             $info['url'] = '/' . $result['handle'] . '/';
             $info['title'] = $result['title'];
         } else {
             $info['url'] = '/' . $result['path'] . '/' . $result['handle'] . '/';
             $info['title'] = $title . $result['title'];
         }
         $tree[] = $info;
         // Get the children:
         $children = $this->buildTree($result['id'], $info['title'] . ' : ');
         // Join arrays:
         $tree = array_merge($tree, $children);
     }
     return $tree;
 }

 public function update($previousVersion)
 {
     if (version_compare($previousVersion, '1.1.0', '<')) {
         Symphony::Database()->query("\n\t\t\t\t\tALTER TABLE `tbl_fields_bilink`\n\t\t\t\t\tADD COLUMN `allow_editing` ENUM('yes','no') DEFAULT 'no';\n\t\t\t\t");
     }
     return true;
 }

 protected function _getAuthorIds()
 {
     $where_and_joins = $this->_getWhereAndJoins();
     $start = ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT;
     $limit = $this->dsParamLIMIT;
     return Symphony::Database()->fetchCol('id', "SELECT `a`.`id` FROM `tbl_authors` as `a` " . $where_and_joins['joins'] . ' WHERE 1 ' . $where_and_joins['where'] . ($limit ? " LIMIT " . ($start ? $start . ',' : '') . $limit : ''));
 }

 public function getXPath($entry)
 {
     $fieldManager = new FieldManager(Symphony::Engine());
     $entry_xml = new XMLElement('entry');
     $section_id = $entry->get('section_id');
     $data = $entry->getData();
     $fields = array();
     $entry_xml->setAttribute('id', $entry->get('id'));
     $associated = $entry->fetchAllAssociatedEntryCounts();
     if (is_array($associated) and !empty($associated)) {
         foreach ($associated as $section => $count) {
             $handle = Symphony::Database()->fetchVar('handle', 0, "\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\ts.handle\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t`tbl_sections` AS s\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\ts.id = '{$section}'\n\t\t\t\t\t\tLIMIT 1\n\t\t\t\t\t");
             $entry_xml->setAttribute($handle, (string) $count);
         }
     }
     // Add fields:
     foreach ($data as $field_id => $values) {
         if (empty($field_id)) {
             continue;
         }
         $field = $fieldManager->fetch($field_id);
         $field->appendFormattedElement($entry_xml, $values, false, null);
     }
     $xml = new XMLElement('data');
     $xml->appendChild($entry_xml);
     $dom = new DOMDocument();
     $dom->strictErrorChecking = false;
     $dom->loadXML($xml->generate(true));
     $xpath = new DOMXPath($dom);
     if (version_compare(phpversion(), '5.3', '>=')) {
         $xpath->registerPhpFunctions();
     }
     return $xpath;
 }

 public function generate($subsection_field, $subsection_id, $items = NULL, $recurse = 0, $flags = self::GETEVERYTHING)
 {
     static $done = array();
     if ($done[$subsection_field] >= $recurse + 1) {
         return array('options' => array(), 'html' => '', 'preview' => '');
     }
     $done[$subsection_field] += 1;
     // Fetch subsection meta data
     $meta = Symphony::Database()->fetch("SELECT filter_tags, caption, droptext, show_preview\n\t\t\t\tFROM tbl_fields_subsectionmanager\n\t\t\t\tWHERE field_id = '" . intval($subsection_field) . "'\n\t\t\t\tLIMIT 1");
     // Get display mode
     if ($meta[0]['show_preview'] == 1) {
         $mode = 'preview';
         $flags |= self::GETPREVIEW;
     } else {
         $mode = 'plain';
     }
     // Fetch entry data
     $subsection = SectionManager::fetch($subsection_id, 'ASC', 'name');
     $fields = $subsection->fetchFields();
     $entries = $this->__filterEntries($subsection_id, $fields, $meta[0]['filter_tags'], $items, $flags & self::GETALLITEMS);
     $droptext = $meta[0]['droptext'];
     // Check caption
     $caption = $meta[0]['caption'];
     if ($caption == '') {
         // Fetch name of primary field in subsection
         $primary = Symphony::Database()->fetch("SELECT element_name\n\t\t\t\t\tFROM tbl_fields\n\t\t\t\t\tWHERE parent_section = '" . intval($subsection_id) . "'\n\t\t\t\t\tAND sortorder = '0'\n\t\t\t\t\tLIMIT 1");
         $caption = '{$' . $primary[0]['element_name'] . '}';
     }
     // Layout subsection data
     $data = $this->__layoutSubsection($entries, $fields, $caption, $droptext, $mode, $flags);
     $done[$subsection_field] -= 1;
     return $data;
 }

 public function action()
 {
     if (isset($_POST['action']['save'])) {
         $fields = $_POST['fields'];
         $permissions = $fields['permissions'];
         $name = trim($fields['name']);
         $page_access = $fields['page_access'];
         if (strlen($name) == 0) {
             $this->_errors['name'] = __('This is a required field');
             return;
         } elseif ($this->_driver->roleExists($name)) {
             $this->_errors['name'] = __('A role with the name <code>%s</code> already exists.', array($name));
             return;
         }
         ASDCLoader::instance()->query("INSERT INTO `tbl_members_roles` VALUES (NULL, '{$name}')");
         $role_id = ASDCLoader::instance()->lastInsertID();
         if (is_array($page_access) && !empty($page_access)) {
             foreach ($page_access as $page_id) {
                 ASDCLoader::instance()->query("INSERT INTO `tbl_members_roles_forbidden_pages` VALUES (NULL, {$role_id}, {$page_id})");
             }
         }
         if (is_array($permissions) && !empty($permissions)) {
             $sql = "INSERT INTO `tbl_members_roles_event_permissions` VALUES ";
             foreach ($permissions as $event_handle => $p) {
                 foreach ($p as $action => $level) {
                     $sql .= "(NULL,  {$role_id}, '{$event_handle}', '{$action}', '{$level}'),";
                 }
             }
             Symphony::Database()->query(trim($sql, ','));
         }
         redirect(extension_members::baseURL() . 'roles_edit/' . $role_id . '/created/');
     }
 }

 private function checkTemplates($pageId, $prefix = '')
 {
     // Link templates:
     $templates = Symphony::Database()->fetch(sprintf('SELECT * FROM `tbl_ckeditor_link_templates` WHERE `page_id` = %d;', $pageId));
     $entryTree = array();
     foreach ($templates as $template) {
         $section = SectionManager::fetch($template['section_id']);
         $entries = EntryManager::fetch(null, $template['section_id']);
         $fields = $section->fetchFields();
         foreach ($entries as $entry) {
             $link = $template['link'];
             // Replace the ID:
             $link = str_replace('{$id}', $entry->get('id'), $link);
             $data = $entry->getData();
             foreach ($fields as $field) {
                 // Replace the placeholders with the value:
                 // Check if the field has a 'handle':
                 $testData = $field->processRawFieldData('test', $field->__OK__);
                 if (isset($testData['handle'])) {
                     $link = str_replace('{$' . $field->get('element_name') . '}', $data[$field->get('id')]['handle'], $link);
                 }
             }
             $entryTree[] = array('handle' => $data[$field->get('id')]['handle'], 'path' => '', 'url' => $link, 'title' => $prefix . ' › ' . General::sanitize($data[$template['field_id']]['value']));
         }
     }
     return $entryTree;
 }

    public static function render($e)
    {
        $lines = NULL;
        foreach (self::__nearByLines($e->getLine(), $e->getFile()) as $line => $string) {
            $lines .= sprintf('%d: %s', ++$line, $string);
        }
        $trace = NULL;
        foreach ($e->getTrace() as $t) {
            $trace .= sprintf('[%s:%d] %s%s%s();' . "\n", isset($t['file']) ? $t['file'] : NULL, isset($t['line']) ? $t['line'] : NULL, isset($t['class']) ? $t['class'] : NULL, isset($t['type']) ? $t['type'] : NULL, $t['function']);
        }
        $queries = NULL;
        if (is_object(Symphony::Database())) {
            $debug = Symphony::Database()->debug();
            if (count($debug['query']) > 0) {
                foreach ($debug['query'] as $query) {
                    $queries .= sprintf('%s; [%01.4f]' . "\n", preg_replace('/[\\r\\n\\t]+/', ' ', $query['query']), isset($query['time']) ? $query['time'] : NULL);
                }
            }
        }
        return sprintf('%s: %s
			
An error occurred in %s around line %d
%s

Backtrace
===========================
%s

Database Query Log
===========================
%s', $e instanceof ErrorException ? GenericErrorHandler::$errorTypeStrings[$e->getSeverity()] : 'Fatal Error', $e->getMessage(), $e->getFile(), $e->getLine(), $lines, $trace, $queries);
    }

 public function __processAuthorFilter($field, $filter)
 {
     //, $filtertype=DS_FILTER_OR){
     //$bits = preg_split('/'.($filtertype == DS_FILTER_AND ? '\+' : ',').'\s*/', $filter);
     if (!is_array($filter)) {
         $bits = preg_split('/,\\s*/', $filter, -1, PREG_SPLIT_NO_EMPTY);
         $bits = array_map('trim', $bits);
     } else {
         $bits = $filter;
     }
     //switch($filtertype){
     /*case DS_FILTER_AND:
     
     					$sql = "SELECT `a`.`id`
     							FROM (
     
     								SELECT `tbl_authors`.id, COUNT(`tbl_authors`.id) AS `count`
     								FROM  `tbl_authors`
     								WHERE `tbl_authors`.`".$field."` IN ('".implode("', '", $bits)."')
     								GROUP BY `tbl_authors`.`id`
     
     							) AS `a`
     							WHERE `a`.`count` >= " . count($bits);
     
     					break;*/
     //case DS_FILTER_OR:
     $sql = "SELECT `id` FROM `tbl_authors` WHERE `" . $field . "` IN ('" . implode("', '", $bits) . "')";
     //break;
     //}
     $authors = Symphony::Database()->fetchCol('id', $sql);
     return is_array($authors) && !empty($authors) ? $authors : NULL;
 }

 function action()
 {
     if (@array_key_exists('save', $_POST['action'])) {
         $this->_errors = array();
         // Polish up some field content
         $fields = $_POST['fields'];
         if (isset($fields['pages'])) {
             $fields['pages'] = implode(',', $fields['pages']);
         }
         $fields['content_formatted'] = DocumentationForm::applyFormatting($fields['content'], true, $this->_errors);
         if ($fields['content_formatted'] === false) {
             $fields['content_formatted'] = General::sanitize(DocumentationForm::applyFormatting($fields['content']));
         }
         if (!isset($fields['title']) || trim($fields['title']) == '') {
             $this->_errors['title'] = __('Title is a required field');
         }
         if (!isset($fields['pages']) || trim($fields['pages']) == '') {
             $this->_errors['pages'] = __('Page is a required field');
         }
         if (!isset($fields['content']) || trim($fields['content']) == '') {
             $this->_errors['content'] = __('Content is a required field');
         }
         if (empty($this->_errors)) {
             if (!Symphony::Database()->insert($fields, 'tbl_documentation')) {
                 $this->pageAlert(__('Unknown errors occurred while attempting to save. Please check your <a href="%s">activity log</a>.', array(URL . '/symphony/system/log/')), Alert::ERROR);
             } else {
                 $doc_id = Symphony::Database()->getInsertID();
                 redirect(URL . "/symphony/extension/documenter/edit/{$doc_id}/created/");
             }
         }
     }
     if (is_array($this->_errors) && !empty($this->_errors)) {
         $this->pageAlert(__('An error occurred while processing this form. <a href="#error">See below for details.</a>'), Alert::ERROR);
     }
 }

 public function delete($section_id)
 {
     $query = "SELECT `id`, `sortorder` FROM tbl_sections WHERE `id` = '{$section_id}'";
     $details = Symphony::Database()->fetchRow(0, $query);
     ## Delete all the entries
     include_once TOOLKIT . '/class.entrymanager.php';
     $entryManager = new EntryManager($this->_Parent);
     $entries = Symphony::Database()->fetchCol('id', "SELECT `id` FROM `tbl_entries` WHERE `section_id` = '{$section_id}'");
     $entryManager->delete($entries);
     ## Delete all the fields
     $fieldManager = new FieldManager($this->_Parent);
     $fields = Symphony::Database()->fetchCol('id', "SELECT `id` FROM `tbl_fields` WHERE `parent_section` = '{$section_id}'");
     if (is_array($fields) && !empty($fields)) {
         foreach ($fields as $field_id) {
             $fieldManager->delete($field_id);
         }
     }
     ## Delete the section
     Symphony::Database()->delete('tbl_sections', " `id` = '{$section_id}'");
     ## Update the sort orders
     Symphony::Database()->query("UPDATE tbl_sections SET `sortorder` = (`sortorder` - 1) WHERE `sortorder` > '" . $details['sortorder'] . "'");
     ## Delete the section associations
     Symphony::Database()->delete('tbl_sections_association', " `parent_section_id` = '{$section_id}'");
     return true;
 }

 public function update($previousVersion)
 {
     if (version_compare($previousVersion, '1.4', '<')) {
         try {
             Symphony::Database()->query("ALTER TABLE `tbl_fields_referencelink` ADD COLUMN `show_association` enum('yes','no') NOT NULL default 'yes'");
         } catch (Exception $e) {
             // Discard
         }
     }
     if (version_compare($previousVersion, '1.3.1', '<')) {
         try {
             $fields = Symphony::Database()->fetchCol('field_id', "SELECT `field_id` FROM `tbl_fields_referencelink`");
         } catch (Exception $e) {
             // Discard
         }
         if (is_array($fields) && !empty($fields)) {
             foreach ($fields as $field_id) {
                 try {
                     Symphony::Database()->query("ALTER TABLE `tbl_entries_data_{$field_id}`\n\t\t\t\t\t\t\t\tCHANGE `relation_id` `relation_id` INT(11) UNSIGNED NULL DEFAULT NULL");
                 } catch (Exception $e) {
                     // Discard
                 }
             }
         }
     }
     if (version_compare($previousVersion, '1.2', '<')) {
         Symphony::Database()->query("ALTER TABLE `tbl_fields_referencelink` ADD `limit` INT(4) UNSIGNED NOT NULL DEFAULT '20'");
         Symphony::Database()->query("ALTER TABLE `tbl_fields_referencelink` CHANGE `related_field_id` `related_field_id` VARCHAR(255) NOT NULL");
     }
     return true;
 }