PHP DatabaseConnection::getRows方法代码示例

 function getAllPosts()
 {
     // Acquire a handle to the database.
     $db = new DatabaseConnection();
     // Query the posts and assign the result to a postcollection.
     $rs = $db->getRows("SELECT * FROM blogposts ORDER BY pubdate DESC");
     $coll = array();
     foreach ($rs as $post) {
         $coll[] = new BlogPost($post);
     }
     // Finally return it
     return $coll;
 }

 /**
  * Create a new collection using an uri or a pattern
  *
  * f.ex.  tag:foo, category:bar, title:*, *, user:bob
  *
  */
 public function __construct($selection, Paginator $paginator = null)
 {
     // Create a collection from a tag, category, title, user etc.
     $db = new DatabaseConnection();
     $sql = $db->quote('SELECT * FROM galleryitems');
     $count = $db->quote('SELECT COUNT(*) AS numitems FROM galleryitems');
     // If we have a paginator, make use of it
     if ($paginator) {
         $sql .= ' ' . $paginator->getSqlLimit();
     }
     // Then select the rows and the total count
     $rs = $db->getRows($sql);
     $rsc = $db->getSingleRow($count);
 }

 static function find($categoryslug)
 {
     $db = new DatabaseConnection();
     try {
         $cat = $db->getRows("SELECT * FROM productcategories WHERE slug=%s", $categoryslug);
         if ($cat) {
             $id = $cat['id'];
             return new ProductCategory($id);
         } else {
             return null;
         }
     } catch (Exception $e) {
         return null;
     }
 }

 /**
  * @brief Retrieve the explicitly defined access record.
  *
  * This function is used internally to retrieve a complete list of roles
  * with the defined access modifiers included.
  *
  * @param String $suuid The subject UUID
  * @param String $ouuid The object UUID
  * @return Array The access record
  */
 private static function getExplicitAccessRecord($suuid, $ouuid, $rlist)
 {
     $db = new DatabaseConnection();
     $rolesraw = $db->getRows("SELECT role,access FROM aclconf WHERE object=%s AND subject=%s", $ouuid, $suuid);
     $rolesraw = arr::flip($rolesraw, 'role');
     // Translate access into tristate booleans
     foreach ($rlist as $role) {
         if (arr::hasKey($rolesraw, $role)) {
             $rf = $rolesraw[$role]['access'];
             if ($rf == 'Y') {
                 $access = self::ACL_ALLOW;
             } elseif ($rf == 'N') {
                 $access = self::ACL_DENY;
             } else {
                 $access = self::ACL_NULL;
             }
         } else {
             $access = self::ACL_NULL;
         }
         $roles[$role] = $access;
     }
     return $roles;
 }

 /**
  * @brief Unlink an identity based on its id.
  *
  * @param int $iid The identity ID
  * @param int $userid The user ID that owns the identity
  * @return boolean True on success
  */
 static function unlinkIdentity($iid, $userid = null)
 {
     $db = new DatabaseConnection();
     // Default to the active user
     if (!$userid) {
         $userid = user::getActiveUser()->userid;
     }
     // And make sure we have an identity to unlink
     if ($iid != 0) {
         $identities = $db->getRows("SELECT * FROM userengage WHERE userid=%d", user::getActiveUser()->userid);
         $identity = $db->getSingleRow("SELECT * FROM userengage WHERE userid=%d AND id=%d", user::getActiveUser()->userid, $iid);
         if (count($identities) > 1) {
             if ($identity) {
                 $db->updateRow("DELETE FROM userengage WHERE userid=%d AND id=%d", user::getActiveUser()->userid, $iid);
                 return true;
             }
         } else {
             view::set('identity', $identity);
             return false;
         }
     } else {
         return false;
     }
 }

 function match($pattern = "*")
 {
     using('lepton.user.*');
     $ptn = str_replace('*', '%', $pattern);
     $db = new DatabaseConnection();
     $results = $db->getRows("SELECT * FROM users WHERE username LIKE %s", $ptn);
     console::writeLn(__astr("\\b{%-5s %-20s %-10s %-37s %-5s %s}"), 'Id', 'Username', 'Flags', 'UUID', 'Act', 'Last login');
     if (!$results) {
         console::writeLn("No matching user records found for %s.", $ptn);
         return;
     }
     foreach ($results as $user) {
         console::writeLn("%05d %-20s %-10s %-37s %-5s %s (from %s)", $user['id'], $user['username'], $user['flags'], $user['uuid'], $user['active'] == 1 ? 'Yes' : 'No', $user['lastlogin'] ? $user['lastlogin'] : 'Never', $user['lastip'] ? $user['lastip'] : 'Nowhere');
     }
 }

 /**
  *
  *
  *
  *
  */
 public function alias()
 {
     $this->checkData();
     console::writeLn("Generating geoalias table");
     $db = new DatabaseConnection();
     $db->exec('DROP TABLE IF EXISTS geoalias');
     $db->exec('CREATE TABLE geoalias (id INT PRIMARY KEY AUTO_INCREMENT, geoid BIGINT, locname VARCHAR(64) CHARSET utf8, INDEX locname(locname(5))) CHARSET utf8');
     $rows = $db->getRows("SELECT id,alternatenames FROM geonames WHERE alternatenames!=''");
     console::write('%8d / %8d ', 0, count($rows));
     foreach ($rows as $row) {
         $alt = explode(',', $row['alternatenames']);
         foreach ($alt as $altstr) {
             $db->insertRow("INSERT INTO geoalias (geoid,locname) VALUES (%d,%s)", $row['id'], $altstr);
         }
         $rc++;
         $rt++;
         if ($rt >= 100) {
             $rh++;
             if ($rh >= 50) {
                 console::write("\n%8d / %8d ", $rc, count($rows));
                 $rh = 0;
             } else {
                 console::write('.');
             }
             $rt = 0;
         }
     }
     console::writeLn(' Done!');
 }

 public function initialize($rootuser = null)
 {
     console::writeLn(__astr('\\b{Initializing database}'));
     $db = config::get('lepton.db.default');
     $dbc = config::get('lepton.db.default');
     console::writeLn("  Database:  %s", $dbc['database']);
     console::writeLn("  User:      %s", $dbc['username']);
     console::writeLn("  Host:      %s", $dbc['hostname']);
     switch ($db['driver']) {
         case 'pdo/mysql':
         case 'mysql':
             console::writeLn("  Driver:    MySQL");
             break;
         default:
             console::fatal('This version of the script does not support anything else than MySQL');
             exit(1);
     }
     console::writeLn(__astr("\n\\b{Creating database and user}"));
     console::writeLn("  The script can create the database and the user. Hit enter to skip this step.");
     console::write("  Password for root user: ");
     $pass = console::readPass();
     if ($pass) {
         $db['database'] = null;
         $db['username'] = 'root';
         $db['password'] = $pass;
         config::set('lepton.db.default', $db);
         $conn = new DatabaseConnection();
         $dblist = $conn->getRows("SHOW DATABASES LIKE %s", $dbc['database']);
         if (count($dblist) == 0) {
             console::writeLn("Creating database...");
             try {
                 $conn->exec(sprintf("CREATE DATABASE %s;", $dbc['database']));
             } catch (Exception $e) {
                 console::writeLn("Not successful, does the database already exist?");
             }
         }
         console::writeLn("Creating user...");
         $conn->exec(sprintf("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY '%s';", $dbc['database'], $dbc['username'], $dbc['password']));
         $conn->exec("USE " . $dbc['database']);
     } else {
         console::writeLn("No password specified, ignoring database and user creation.");
         $conn = new DatabaseConnection();
     }
     console::writeLn(__astr("\n\\b{Importing tables}"));
     $f = glob(base::basePath() . '/dist/sql/*.sql');
     foreach ($f as $fn) {
         $fc = file_get_contents($fn);
         console::writeLn("  [sys] %s", basename($fn));
         $conn->exec($fc);
     }
     $f = glob(base::appPath() . '/sql/*.sql');
     foreach ($f as $fn) {
         $fc = file_get_contents($fn);
         console::writeLn("  [app] %s", basename($fn));
         $conn->exec($fc);
     }
     console::writeLn("All done.");
 }

 public static function updateActiveCountries(callback $callback = null)
 {
     self::updateCache($callback);
     $db = new DatabaseConnection();
     $cache = base::expand('app:/cache/geonames/');
     // Update hierarchy
     cb($callback, 'Importing hierarchy ...', 1);
     $fin = fopen('compress.zlib://' . $cache . 'hierarchy.gz', 'r');
     $rows = 0;
     $ltime = 0;
     while (!feof($fin)) {
         $fd = trim(fgets($fin));
         $ds = explode("\t", $fd . "\t\t");
         $db->updateRow("REPLACE INTO geonames_hierarchy " . "(parentid,geoid,htype) " . "VALUES " . "(%d,%d,%s)", $ds[0], $ds[1], $ds[2]);
         if (microtime(true) > $ltime + 1) {
             cb($callback, 'Importing hierarchy ... ' . $rows . " records imported", 1);
             $ltime = microtime(true);
         }
         $rows++;
     }
     cb($callback, 'Imported hierarchy (' . $rows . ' records)');
     fclose($fin);
     // Pull the list of countries to import
     $rs = $db->getRows("SELECT * FROM geonames_datasets WHERE active=1");
     foreach ($rs as $ci) {
         cb($callback, 'Importing ' . $ci['setkey'] . ' ...', 1);
         $fin = fopen('compress.zlib://' . $cache . $ci['setkey'] . '.gz', 'r');
         $rows = 0;
         $ltime = 0;
         while (!feof($fin)) {
             $dl = fgets($fin);
             if (trim($dl) != '') {
                 $ds = explode("\t", $dl);
                 $db->updateRow('REPLACE INTO geonames ' . '(geoid,name,asciiname,alternatenames,' . 'latitude,longitude,featureclass,featurecode,' . 'countrycode,countrycodealt,admin1code,admin2code,' . 'admin3code,admin4code,population,elevation,' . 'gtopo30,timezone,modificationdate) ' . 'VALUES ' . '(%d,%s,%s,%s, %.5f,%.5f,%s,%s,' . '%s,%s,%s,%s, %s,%s,%d,%d,' . '%d,%s,%s)', $ds[0], $ds[1], $ds[2], $ds[3], $ds[4], $ds[5], $ds[6], $ds[7], $ds[8], $ds[9], $ds[10], $ds[11], $ds[12], $ds[13], $ds[14], $ds[15], $ds[16], $ds[17], $ds[18]);
             }
             if (microtime(true) > $ltime + 1) {
                 cb($callback, 'Importing ' . $ci['setkey'] . ' ... ' . $rows . " records imported", 1);
                 $ltime = microtime(true);
             }
             $rows++;
         }
         fclose($fin);
         cb($callback, 'Imported ' . $ci['setkey'] . " (" . $rows . " records)");
     }
 }