PHP xf_db_query函数代码示例

/**
 * This function acts as a member method of the PreferencesTool class.  It has
 * been factored out for efficiency because it only needs to be run once.
 * @see Dataface_PreferncesTool::_createPreferencesTable()
 */
function Dataface_PreferencesTool__createPreferencesTable()
{
    $res = xf_db_query("create table if not exists `dataface__preferences` (\n\t\t\t`pref_id` int(11) unsigned not null auto_increment,\n\t\t\t`username` varchar(64) not null,\n\t\t\t`table` varchar(128) not null,\n\t\t\t`record_id` varchar(255) not null,\n\t\t\t`key` varchar(128) not null,\n\t\t\t`value` varchar(255) not null,\n\t\t\tprimary key (pref_id),\n\t\t\tindex `username` (`username`),\n\t\t\tindex `table` (`table`),\n\t\t\tindex `record_id` (`record_id`))", df_db());
    if (!$res) {
        throw new Exception(xf_db_error(df_db()), E_USER_ERROR);
    }
}

 function testTranslate()
 {
     //echo $this->DB->translate_query('select PubType, BiblioString from Publications', 'fr');
     //exit;
     $sql = 'select PubType, BiblioString from Publications limit 5';
     $start = microtime_float();
     $res = $this->DB->query($sql, $this->db) or die(xf_db_error($this->db));
     while ($row = xf_db_fetch_assoc($res)) {
         print_r($row);
     }
     $stop1 = microtime_float() - $start;
     $start = microtime_float();
     $res3 = $this->DB->query($sql, $this->db) or die(xf_db_error($this->db));
     while ($row = xf_db_fetch_assoc($res3)) {
         print_r($row);
     }
     $stop3 = microtime_float() - $start;
     $start = microtime_float();
     $res2 = xf_db_query($sql, $this->db) or die(xf_db_error($this->db));
     while ($row = xf_db_fetch_assoc($res2)) {
         print_r($row);
     }
     $stop2 = microtime_float() - $start;
     echo "MySQL: {$stop2} ; Translated: {$stop1} ; Second translated: {$stop3}";
     $parser = new SQL_Parser(null, 'MySQL');
     //$sql = 'select IFNULL(f.PubType,d.PubType) as PubType, IFNULL(f.BiblioString,d.BiblioString) as BiblioString from Publications d left join Publications_fr f on d.PubID=f.PubID';
     $data = $parser->parse($sql);
     //print_r($data);
     $compiler = new SQL_Compiler();
     echo $compiler->compile($data);
 }

 function test_add_value()
 {
     $vt = Dataface_ValuelistTool::getInstance();
     $people = Dataface_Table::loadTable('People');
     $vt->addValueToValuelist($people, 'Publications', 'My Test Publication');
     $res = xf_db_query("select * from Publications where `BiblioString` = 'My Test Publication'");
     $this->assertTrue(xf_db_num_rows($res) === 1);
 }

 public static function createRecordsTable()
 {
     $sql = sprintf("create table `%s` (\n\t\t\t\tid int(11) not null auto_increment,\n\t\t\t\tschema_id int(11) not null,\n\t\t\t\t`base_record_id_hash` varchar(32) not null,\n\t\t\t\t`base_record_id` text not null,\n\t\t\t\t`version_hash` varchar(32) not null,\n\t\t\t\t`lang` varchar(2) not null,\n\t\t\t\t`record_data` text not null,\n\t\t\t\tprimary key (`id`),\n\t\t\t\tkey (`schema_id`, `base_record_id_hash`, `version_hash`)\n\t\t\t)", self::$RECORDS_TABLE);
     $res = xf_db_query($sql, df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
     true;
 }

 function handle(&$params)
 {
     $app =& Dataface_Application::getInstance();
     if (df_get_database_version() == df_get_file_system_version()) {
         $app->redirect(DATAFACE_SITE_HREF . '?--msg=' . urlencode('The application database is up to date at version ' . df_get_database_version()));
     }
     if (df_get_database_version() > df_get_file_system_version()) {
         $app->redirect(DATAFACE_SITE_HREF . '?--msg=' . urlencode('The database version is greater than the file system version.  Please upgrade your application to match the version in the database (version ' . df_get_database_version()));
     }
     $res = xf_db_query("select count(*) from dataface__version", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
     $row = xf_db_fetch_row($res);
     if ($row[0] == 0) {
         $res2 = xf_db_query("insert into dataface__version (`version`) values (0)", df_db());
         if (!$res2) {
             throw new Exception(xf_db_error(df_db()));
         }
     }
     if (file_exists('conf/Installer.php')) {
         import('conf/Installer.php');
         $installer = new conf_Installer();
         $methods = get_class_methods('conf_Installer');
         $methods = preg_grep('/^update_([0-9]+)$/', $methods);
         $updates = array();
         foreach ($methods as $method) {
             preg_match('/^update_([0-9]+)$/', $method, $matches);
             $version = intval($matches[1]);
             if ($version > df_get_database_version() and $version <= df_get_file_system_version()) {
                 $updates[] = $version;
             }
         }
         sort($updates);
         foreach ($updates as $update) {
             $method = 'update_' . $update;
             $res = $installer->{$method}();
             if (PEAR::isError($res)) {
                 return $res;
             }
             $res = xf_db_query("update dataface__version set `version`='" . addslashes($update) . "'", df_db());
             if (!$res) {
                 throw new Exception(xf_db_error(df_db()), E_USER_ERROR);
             }
         }
     }
     $res = xf_db_query("update dataface__version set `version`='" . addslashes(df_get_file_system_version()) . "'", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()), E_USER_ERROR);
     }
     if (function_exists('apc_clear_cache')) {
         apc_clear_cache('user');
     }
     df_clear_views();
     df_clear_cache();
     $app->redirect(DATAFACE_SITE_HREF . '?--msg=' . urlencode('The database has been successfully updated to version ' . df_get_file_system_version()));
 }

 function _build()
 {
     $type = $this->getRelationshipType($this->_relationshipName);
     if ($type != "many_to_many") {
         trigger_error("Attempt to build reference browser widget for relationship '" . $this->_relationshipName . "' in tablle '" . $this->_table->tablename . "' where the type is not 'many_to_many'.  The type found was '{$type}'.", E_USER_ERROR);
     }
     $domainSQL = $this->_table->getRelationshipDomainSQL($this->_relationshipName);
     $res = xf_db_query($domainSQL, $this->_table->db);
 }

/**
 * A method to create the configuration table in the database.  The configuration
 * table is where configuration (e.g. fields.ini etc..) may be stored.  This is
 * a new feature in 0.6.14.
 *
 * @author Steve Hannah <shannah@sfu.ca>
 * @created Feb. 26, 2007
 */
function Dataface_ConfigTool_createConfigTable()
{
    $self =& Dataface_ConfigTool::getInstance();
    if (!Dataface_Table::tableExists($self->configTableName, false)) {
        $sql = "CREATE TABLE `" . $self->configTableName . "` (\n\t\t\t\t\tconfig_id int(11) NOT NULL auto_increment primary key,\n\t\t\t\t\t`file` varchar(255) NOT NULL,\n\t\t\t\t\t`section` varchar(128),\n\t\t\t\t\t`key` varchar(128) NOT NULL,\n\t\t\t\t\t`value` text NOT NULL,\n\t\t\t\t\t`lang` varchar(2),\n\t\t\t\t\t`username` varchar(32),\n\t\t\t\t\t`priority` int(5) default 5\n\t\t\t\t\t)";
        $res = xf_db_query($sql, df_db());
        if (!$res) {
            throw new Exception(xf_db_error(df_db()), E_USER_ERROR);
        }
    }
}

 function testSync1()
 {
     $app =& Dataface_Application::getInstance();
     $s = new DB_Sync($app->db(), $app->db(), 'a', 'b');
     $s->syncTables();
     $res = xf_db_query("show create table b", $app->db());
     if (!$res) {
         trigger_error(xf_db_error($app->db()), E_USER_ERROR);
     }
     $row = xf_db_fetch_assoc($res);
     @xf_db_free_result($res);
     $this->assertEquals("CREATE TABLE `b` (\n  `id` int(11) NOT NULL auto_increment,\n  `a` varchar(32) default 'b',\n  `b` datetime default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`id`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1", $row['Create Table']);
 }

 function dbmtime()
 {
     $tables =& $this->tables;
     $lookup = array_flip($tables);
     $app =& Dataface_Application::getInstance();
     $res = xf_db_query("SHOW TABLE STATUS", $app->_db);
     $latestMod = 0;
     while ($row = xf_db_fetch_array($res)) {
         if ((sizeof($tables) === 0 || isset($lookup[$row['Name']])) && strtotime($row['Update_time']) > $latestMod) {
             $latestMod = strtotime($row['Update_time']);
         }
     }
     return $latestMod;
 }

 public function query($sql, $vars = null)
 {
     $sql = $this->prepareQuery($sql, $vars);
     if (isset($this->translator)) {
         $sql = $this->translator->translateQuery($sql);
     }
     $res = xf_db_query($sql, $this->db());
     if (!$res) {
         //echo $sql;
         error_log("Query Failed: " . $sql);
         throw new \Exception(xf_db_error($this->db()));
     }
     return $res;
 }

 function setUp()
 {
     parent::setUp();
     xf_db_query("CREATE TABLE `Pages` (\n\t\t\t`PageID` INT(11) auto_increment NOT NULL,\n\t\t\t`ParentID` INT(11),\n\t\t\t`ShortName` VARCHAR(32) NOT NULL,\n\t\t\t`Description` TEXT,\n\t\t\tPRIMARY KEY (`PageID`),\n\t\t\tUNIQUE (`ParentID`,`ShortName`))") or trigger_error(xf_db_error() . __LINE__);
     xf_db_query("INSERT INTO `Pages` (`PageID`,`ShortName`,`Description`)\n\t\t\tVALUES (1,'index_page','Main page')") or trigger_error(xf_db_error() . __LINE__);
     xf_db_query("INSERT INTO `Pages` (`ParentID`,`ShortName`,`Description`)\n\t\t\tVALUES \n\t\t\t(1,'about','About us'),\n\t\t\t(1,'jobs','Now hiring'),\n\t\t\t(1,'products','About our products'),\n\t\t\t(1,'services','About our services'),\n\t\t\t(1,'contact','Contact us')") or trigger_error(xf_db_error() . __LINE__);
     xf_db_query("INSERT INTO `Pages` (`ParentID`,`ShortName`,`Description`)\n\t\t\tVALUES\n\t\t\t(2,'history','Our history'),\n\t\t\t(2,'future', 'The direction of the company'),\n\t\t\t(3,'application', 'Job application'),\n\t\t\t(3,'current_listing', 'Current job listings'),\n\t\t\t(4,'awards','Product awards'),\n\t\t\t(4,'downloads','Product downlaods'),\n\t\t\t(5,'consultation','Free consultation')") or trigger_error(xf_db_error() . __LINE__);
     $table =& Dataface_Table::loadTable('Pages');
     $r =& $table->relationships();
     if (!isset($r['children'])) {
         $table->addRelationship('children', array('__sql__' => 'select * from Pages where ParentID=\'$PageID\'', 'meta:class' => 'children'));
     }
     $this->indexpage =& df_get_record('Pages', array('PageID' => 1));
     $this->t = new Dataface_TreeTable($this->indexpage);
 }

/**
 * Sets a configuration parameter in the configuration table.
 * This should not be called directly.  It should be called through the 
 * Dataface_ConfigTool class as its setConfigParam method.
 *
 * @param string $file The name of the ini file in which the config value is being set.
 * @param string $section The name of the section (could be null).
 * @param string $key The name of the parameter's key (not null)
 * @param string $value The value to set (not null)
 * @param string $username The username for which the parameter is being set (null for all users)
 * @param string $lang The 2-digit language code for which the parameter is being set (null for all languages).
 * @param integer $priority The priority of this config variable (priority dictates which 
 *					parameters take priority. Default vallue of 5.
 * @returns true if success or PEAR_Error if failure.
 *
 * This will create the configuration table if it doesn't already exist.
 *
 *	@author Steve Hannah <shannah@sfu.ca>
 * @created Feb. 26, 2007
 */
function Dataface_ConfigTool_setConfigParam($file, $section, $key, $value, $username = null, $lang = null, $priority = 5)
{
    $self =& Dataface_ConfigTool::getInstance();
    // See if this parameter has already been set:
    $where = array();
    $where[] = "`key`='" . addslashes($key) . "'";
    $where[] = "`file`='" . addslashes($file) . "'";
    $where[] = "`section`" . (isset($section) ? "='" . addslashes($section) . "'" : ' IS NULL');
    $where[] = "`username`" . (isset($username) ? "='" . addslashes($username) . "'" : ' IS NULL');
    $where[] = "`lang`" . (isset($lang) ? "='" . addslashes($lang) . "'" : ' IS NULL');
    $where = implode(' and ', $where);
    $sql = "select `config_id` from `" . $self->configTableName . "` where {$where} limit 1";
    $res = xf_db_query($sql, df_db());
    if (!$res) {
        $self->createConfigTable();
        $res = xf_db_query($sql, df_db());
    }
    if (!$res) {
        return PEAR::raiseError("Failed to get config parameter: " . xf_db_error(df_db()));
    }
    $vals = array("section" => isset($section) ? "'" . addslashes($section) . "'" : 'NULL', "key" => "'" . addslashes($key) . "'", "value" => "'" . addslashes($value) . "'", "username" => "'" . addslashes($username) . "'", "lang" => "'" . addslashes($lang) . "'", "priority" => $priority);
    if (xf_db_num_rows($res) > 0) {
        $row = xf_db_fetch_assoc($res);
        // We need to perform an update
        $updates = array();
        foreach ($vals as $vkey => $vval) {
            $updates[] = '`' . $vkey . '`=' . $vval;
        }
        $sets = implode(' and ', $updates);
        $sql = "update `" . $self->configTableName . "` set " . $sets . " where `config_id`='" . $row['config_id'] . "' limit 1";
    } else {
        $values = array();
        $cols = array();
        foreach ($vals as $vkey => $vval) {
            $cols[] = "`{$vkey}`";
            $values[] = $vval;
        }
        $cols = implode(',', $cols);
        $values = implode(',', $values);
        $sql = "insert into `" . $self->configTableName . "` ({$cols}) VALUES ({$values})";
    }
    @xf_db_free_result($res);
    $res = xf_db_query($sql, df_db());
    if (!$res) {
        return PEAR::raiseError("Could not write config value: " . xf_db_error(df_db()));
    }
    return true;
}

 function handle(&$params)
 {
     $res = xf_db_query("show tables like 'dataface__view_%'", df_db());
     $views = array();
     while ($row = xf_db_fetch_row($res)) {
         $views[] = $row[0];
     }
     if ($views) {
         $sql = "drop view `" . implode('`,`', $views) . "`";
         echo $sql;
         echo "<br/>";
         $res = xf_db_query("drop view `" . implode('`,`', $views) . "`", df_db());
         if (!$res) {
             throw new Exception(xf_db_error(df_db()));
         }
     }
     echo "done";
 }

 function test_save()
 {
     $app =& Dataface_Application::getInstance();
     $builder = new Dataface_Table_builder($this->mytable);
     $this->assertTrue(!isset($builder->table));
     $this->assertEquals(0, xf_db_num_rows(xf_db_query("show tables like '" . $this->mytable . "'", $app->db())));
     $builder->addField(array('Field' => 'name', 'Type' => 'varchar(32)'));
     $res = $builder->save();
     if (PEAR::isError($res)) {
         trigger_error($res->toString(), E_USER_ERROR);
     }
     $this->assertEquals(array('name', 'id'), array_keys($builder->table->fields()));
     $this->assertEquals(1, xf_db_num_rows(xf_db_query("show tables like '" . $this->mytable . "'", $app->db())));
     $builder->addField(array('Field' => 'email', 'Type' => 'varchar(28)'));
     $res = $builder->save();
     if (PEAR::isError($res)) {
         trigger_error($res->toString(), E_USER_ERROR);
     }
     $this->assertEquals(array('name', 'id', 'email'), array_keys($builder->table->fields()));
     $this->assertEquals(1, xf_db_num_rows(xf_db_query("show tables like '" . $this->mytable . "'", $app->db())));
 }

 function afterCopy(Dataface_Record $orig, Dataface_Record $copy)
 {
     $rand = md5(rand(0, 1000000));
     $copytable = 'copy_' . $rand;
     $res = xf_db_query("create temporary table `{$copytable}` select * from formula_ingredients where formula_id='" . addslashes($orig->val('formula_id')) . "'", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
     $res = xf_db_query("update `{$copytable}` set formula_id='" . addslashes($copy->val('formula_id')) . "'", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
     $res = xf_db_query("insert into formula_ingredients select * from `{$copytable}`", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
     $res = xf_db_query("drop table `{$copytable}`", df_db());
     if (!$res) {
         throw new Exception(xf_db_error(df_db()));
     }
 }