PHP Dal::query方法代码示例

 public function delete($leaf)
 {
     //TODO: use innodb so this actually matters
     list($file_id, $servers) = Dal::query_one("SELECT file_id, servers FROM local_files WHERE filename=? FOR UPDATE", array($leaf));
     try {
         if (!$file_id) {
             throw new PAException(FILE_NOT_FOUND, "Unable to find file {$leaf} in local_files table:");
         }
         $path = $this->getPath($leaf);
         $server_ids = explode(",", $servers);
         if (in_array(PA::$server_id, $server_ids)) {
             if (empty($path)) {
                 throw new PAException(FILE_NOT_FOUND, "Unable to delete nonexistent file {$path}");
             }
             if (!@unlink($path)) {
                 throw new PAException(STORAGE_ERROR, "Error deleting {$path}");
             }
             $server_ids = array_filter($server_ids, "not_this_server");
             $servers = implode(",", $server_ids);
         }
         Dal::query("UPDATE local_files SET is_deletion=1, timestamp=NOW(), servers=? WHERE file_id=?", array($file_id, $servers));
     } catch (PAException $e) {
         Dal::rollback();
         throw $e;
     }
     return TRUE;
 }

 function testOwnerIdMemberCount()
 {
     $networks = array();
     $sth = Dal::query("SELECT network_id, address, member_count, owner_id FROM networks WHERE is_active=1");
     while (list($net_id, $address, $member_count, $owner_id) = Dal::row($sth)) {
         $networks[$net_id] = array("address" => $address, "member_count" => $member_count, "owner_id" => $owner_id);
     }
     // count all members for all networks
     $sth = Dal::query("SELECT network_id, COUNT(user_id) FROM networks_users GROUP BY network_id");
     while (list($net_id, $member_count) = Dal::row($sth)) {
         $networks[$net_id]['calc_member_count'] = $member_count;
     }
     // find all owners
     $sth = Dal::query("SELECT network_id, user_id FROM networks_users where user_type='owner'");
     while (list($net_id, $owner_id) = Dal::row($sth)) {
         $networks[$net_id]['calc_owner_id'] = $owner_id;
     }
     // verify them all
     $ok = TRUE;
     foreach ($networks as $nid => $net) {
         $address = $net['address'];
         $mc = $net['member_count'];
         $cmc = $net['calc_member_count'];
         $oi = $net['owner_id'];
         $coi = (int) $net['calc_owner_id'];
         if ($cmc && ($mc != $cmc || $oi != $coi)) {
             echo "NetworkDataTest ERROR: Network {$nid} [{$address}]: member_count {$mc}, calc {$cmc} | owner_id {$oi}, found {$coi}\n";
             $ok = FALSE;
         }
     }
     $this->assertTrue($ok);
 }

 public static function save_tags_for_item($user_id, $subject_type, $subject_id, $tags_array)
 {
     // remove all existig tags of this user for this item
     Dal::query("DELETE FROM {itemtags} WHERE  user_id=? AND subject_type=? AND subject_id=?", array($user_id, $subject_type, $subject_id));
     // add the given ones
     foreach ($tags_array as $i => $tag_string) {
         Dal::query("INSERT INTO {itemtags} SET \n\t\t  user_id=?, subject_type=?, subject_id=?, \n\t\t  tag_string=?", array($user_id, $subject_type, $subject_id, $tag_string));
     }
 }

 /**
  * Loads the given setting for the given user id.
  *
  * @param integer $type The type of the entity for whom settings are to be loaded.
  * @param integer $assoc_id The id of the association entity for whom settings are to be added.
  * @return $value string This string contains the object of given settings.
  */
 static function load_setting($page_id, $assoc_id, $assoc_type = "network", $child_type = null, $only_configurable = false)
 {
     Logger::log("Enter: function ModuleSetting::load_setting");
     $settings = null;
     $sql = "SELECT page_id, settings FROM {page_settings} WHERE assoc_id=? AND page_id=? AND assoc_type =?";
     $data = array($assoc_id, $page_id, $assoc_type);
     $res = Dal::query($sql, $data);
     $dynamic_page = new DynamicPage($page_id);
     if (!is_object($dynamic_page) or !$dynamic_page->docLoaded) {
         throw new Exception("Page XML config file for page ID: {$page_id} - not found!");
     }
     $dynamic_page->initialize();
     $page_settings = $xml_settings = $dynamic_page->getPageSettings();
     if ($res->numRows() > 0) {
         $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
         $settings = unserialize($row->settings);
         foreach ($settings as $key => $value) {
             // merge DB and XML settings
             $page_settings[$key] = $value;
         }
         if (!is_null($child_type)) {
             if (false !== strpos($dynamic_page->page_type, $child_type)) {
                 $settings = $page_settings;
             } else {
                 $settings = null;
             }
         } else {
             $settings = $page_settings;
         }
     } else {
         if ($assoc_type == 'user' || $assoc_type == 'group') {
             // try to get default settings for current network
             $settings = self::load_setting($page_id, PA::$network_info->network_id, "network", $assoc_type, $only_configurable);
         } else {
             if ($only_configurable == false) {
                 $settings = $dynamic_page->getPageSettings();
             } else {
                 if ($only_configurable == true && $dynamic_page->is_configurable) {
                     if (!is_null($child_type)) {
                         if (false !== strpos($dynamic_page->page_type, $child_type)) {
                             $settings = $dynamic_page->getPageSettings();
                         } else {
                             $settings = null;
                         }
                     } else {
                         $settings = $dynamic_page->getPageSettings();
                     }
                 }
             }
         }
     }
     // Fix: always return navigation_code and boot_code from XML file
     $settings['navigation_code'] = $xml_settings['navigation_code'];
     $settings['boot_code'] = $xml_settings['boot_code'];
     Logger::log("Exit: function ModuleSetting::load_setting");
     return $settings;
 }

 static function get($module_name)
 {
     Logger::log("Enter: function ModuleData::get");
     $sql = "SELECT  data AS data FROM {moduledata}  WHERE modulename LIKE ? ";
     $data = array($module_name);
     $res = Dal::query($sql, $data);
     if ($res->numRows() > 0) {
         $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
         Logger::log("Exit: function ModuleData::get");
         return $row->data;
     }
 }

 public function get_tasks()
 {
     Logger::log("Enter: function Tasks::get_tasks");
     //get tasks from db but its ok to define it in array for now
     // this should go in Cache
     $tasks = array();
     $res = Dal::query('SELECT * FROM {tasks} ');
     while ($r = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
         $tasks[] = $r;
     }
     Logger::log("Exit: function Tasks::get_tasks");
     return $tasks;
 }

 public static function get_recent_by_user($uid, $cnt = 5)
 {
     $sql = 'SELECT * FROM {rating} WHERE user_id=? 
 ORDER BY index_id
 LIMIT ?';
     $data = array($uid, $cnt);
     $res = Dal::query($sql, $data);
     $return = array();
     if ($res->numRows()) {
         while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
             array_push($return, $row);
         }
     }
     return $return;
 }

 public function update()
 {
     Logger::log("Enter: function ConfigurableText::update");
     $sql = "SELECT * FROM {configurable_text} WHERE caption = ? AND id <> ?";
     $data = array('caption' => $this->caption, 'id' => $this->id);
     $res = Dal::query($sql, $data);
     if ($res->numRows() == 0) {
         $sql = "UPDATE {configurable_text} SET caption = ?, caption_value = ? WHERE id = ?";
         $data = array('caption' => $this->caption, 'caption_value' => $this->caption_value, 'id' => $this->id);
         $res = Dal::query($sql, $data);
     } else {
         // Caption already exists
         Logger::log("Throwing exception CAPTION_NAME_EXISTS | Caption with the given name already exists", LOGGER_ERROR);
         throw new PAException(CAPTION_NAME_EXISTS, "Caption with the given name already exists");
     }
     Logger::log("Enter: function ConfigurableText::update");
     return;
 }

 public static function get($conditions = NULL, $params = NULL)
 {
     Logger::log("Enter: CNRatingData::get()");
     $sql = 'SELECT * FROM {rating} WHERE 1';
     $data = array();
     if (!empty($params)) {
         foreach ($params as $key => $value) {
             $sql .= ' AND ' . $key . ' = ?';
             array_push($data, $value);
         }
     }
     $res = Dal::query($sql, $data);
     $return = array();
     if ($res->numRows()) {
         while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
             array_push($return, $row);
         }
     }
     Logger::log("Exit: CNRatingData::get()");
     return $return;
 }

 public function update($params)
 {
     $updates = array();
     $args = array();
     foreach ($params as $k => $v) {
         $col = @Item::$columns[$k];
         if (empty($col)) {
             throw new Exception("Unknown item table column '{$k}'");
         }
         if ($this->{$col} != $v) {
             $updates[] = "{$col}=?";
             $args[] = $v;
         }
     }
     if (count($updates)) {
         $args[] = $this->item_id;
         Dal::query("UPDATE {items} SET " . implode(",", $updates) . " WHERE item_id=?", $args);
         Cache::setValue("item:" . $params['type'] . ":" . $params['id'], $this);
         // update cache
     }
 }

 public static function get_valid_networks()
 {
     $sth = Dal::query("SHOW TABLES");
     $tables = array();
     while ($r = Dal::row($sth)) {
         $tables[$r[0]] = 1;
     }
     $sth = Dal::query("SELECT address FROM networks WHERE is_active=1");
     $networks = array();
     while ($r = Dal::row($sth)) {
         $address = $r[0];
         if ($address == 'default' || isset($tables[$address . "_comments"])) {
             // comments table available - assume network has been initialised
             $networks[] = $address;
         }
     }
     // if we haven't run net_extra yet, the default network won't have an entry, so we add it in manually now.
     if (!in_array("default", $networks)) {
         $networks[] = "default";
     }
     return $networks;
 }

 /**
  * Function to process the user feed.
  * This function will check for the feed url added by the user in 
  * user_profile in external feeds. If it exists then it will return the associated 
  * feed_id otherwise it will add 
  */
 private function import_user_feed()
 {
     Logger::log("Enter: UserProfileFeed::import_user_feed");
     $sql = 'SELECT feed_id FROM {external_feed} WHERE import_url = ? AND is_active = ?';
     try {
         $res = Dal::query($sql, array($this->import_url, ACTIVE));
     } catch (PAException $e) {
         Logger::log("Exit UserProfileFeed::import_user_feed.Not able to get feed details for given import_url. Associated sql = {$sql}, import_url = {$this->import_url}");
         throw $e;
     }
     if ($res->numRows()) {
         //given import url exists already in the external feed list
         $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
         //TODO: refresh the feed data
         $this->feed_id = $row->feed_id;
     } else {
         // Given feed does not exists in the external feeds. So add it first and then return the associated feed_id.
         //setting the feed_type to user_profile feed
         $this->feed_type = USER_PROFILE_FEED;
         $this->save();
     }
     Logger::log("Exit: UserProfileFeed::import_user_feed");
     return $this->feed_id;
 }

 /**
     For loading group information in the basis of group_id
     Requirement :- take a group id
     Return :- all the information of group as well as group_owner name, ID and number of members in the group
 */
 public static function load_group($group_id = FALSE, $cnt = FALSE, $show = 'ALL', $page = 1, $sort_by = 'created', $direction = 'DESC', $speacial_condition = FALSE)
 {
     Logger::log("Enter: Group::load_group() ");
     if ($sort_by == 'members') {
         $order_by = 'members' . ' ' . $direction;
     } else {
         $order_by = ' CC.' . $sort_by . ' ' . $direction;
     }
     if ($show == 'ALL' || $cnt == TRUE) {
         $limit = '';
     } else {
         $start = ($page - 1) * $show;
         $limit = 'LIMIT ' . $start . ',' . $show;
     }
     if ($group_id) {
         $sql = "SELECT count(GU.user_id) AS members,CC.collection_id AS group_id,CC.title AS group_name,CC.*,U.first_name AS owner_first_name,U.login_name AS owner_login_name,U.user_id  AS owner_id FROM {contentcollections} AS CC INNER JOIN {groups_users} AS GU on GU.group_id = CC.collection_id AND CC.is_active =1 LEFT JOIN {users} AS U on CC.author_id = U.user_id WHERE CC.collection_id = ? {$speacial_condition} GROUP BY CC.collection_id ORDER BY {$order_by} {$limit}";
         $res = Dal::query($sql, $group_id);
         if ($cnt) {
             return $res->numRows();
         }
         if ($res->numRows()) {
             while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
                 $group_description = $row;
             }
         }
         Logger::log("Exit: Group::load_group() ");
         return $group_description;
     }
 }

 /**
  *   Method to load a links or links.
  *   @param $condition_array associative array of the class variables with their values.
  *   @access public
  */
 public function network_owner_link($condition = NULL, $limit = NULL)
 {
     Logger::log("Enter: function Links::network_owner_link");
     $sql = "SELECT L.* FROM {links} AS L INNER JOIN {linkcategories} AS LC ON L.category_id = LC.category_id";
     if (count($condition) > 0) {
         foreach ($condition as $key => $value) {
             $sql .= " AND L.{$key} = ?";
             $data[] = $value;
         }
     }
     $sql .= " ORDER BY L.created";
     if (!empty($limit)) {
         $sql .= " LIMIT {$limit}";
     }
     $res = Dal::query($sql, $data);
     $return = array();
     if ($res->numRows() > 0) {
         while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
             $return[] = $row;
         }
     }
     Logger::log("Exit: function Links::load_link");
     return $return;
 }

 public static function content_type_exists()
 {
     $sql = "SELECT type_id FROM {content_types} WHERE name LIKE ?";
     $res = Dal::query($sql, array(self::TYPE_NAME));
     if ($res->numRows()) {
         return true;
     }
     return false;
 }