PHP Notice::query方法代码示例

 function getNoticeIds($offset, $limit, $since_id, $max_id)
 {
     $notice = new Notice();
     $qry = null;
     $qry = 'SELECT notice.* FROM notice ';
     $qry .= 'INNER JOIN happening ON happening.uri = notice.uri ';
     $qry .= 'AND notice.is_local != ' . Notice::GATEWAY . ' ';
     if ($since_id != 0) {
         $qry .= 'AND notice.id > ' . $since_id . ' ';
     }
     if ($max_id != 0) {
         $qry .= 'AND notice.id <= ' . $max_id . ' ';
     }
     // NOTE: we sort by event time, not by notice time!
     $qry .= 'ORDER BY happening.created DESC ';
     if (!is_null($offset)) {
         $qry .= "LIMIT {$limit} OFFSET {$offset}";
     }
     $notice->query($qry);
     $ids = array();
     while ($notice->fetch()) {
         $ids[] = $notice->id;
     }
     $notice->free();
     unset($notice);
     return $ids;
 }

 function getUpdates($seconds)
 {
     $notice = new Notice();
     # XXX: cache this. Depends on how big this protocol becomes;
     # Re-doing this query every 15 seconds isn't the end of the world.
     $notice->query('SELECT profile_id, max(id) AS max_id ' . 'FROM notice ' . (common_config('db', 'type') == 'pgsql' ? 'WHERE extract(epoch from created) > (extract(epoch from now()) - ' . $seconds . ') ' : 'WHERE created > (now() - ' . $seconds . ') ') . 'GROUP BY profile_id');
     $updates = array();
     while ($notice->fetch()) {
         $updates[] = array($notice->profile_id, $notice->max_id);
     }
     return $updates;
 }

 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
define('INSTALLDIR', realpath(dirname(__FILE__) . '/../../..'));
$helptext = <<<ENDOFHELP
USAGE: initialize_notice_to_status.php

Initializes the notice_to_status table with existing Twitter synch
data. Only necessary if you've had the Twitter bridge enabled before
version 0.9.5.

ENDOFHELP;
require_once INSTALLDIR . '/scripts/commandline.inc';
// We update any notices that may have come in from
// Twitter that we don't have a status_id for. Note that
// this won't catch notices that originated at this StatusNet site.
$n = new Notice();
$n->query('SELECT notice.id, notice.uri ' . 'FROM notice LEFT JOIN notice_to_status ' . 'ON notice.id = notice_to_status.notice_id ' . 'WHERE notice.source = "twitter"' . 'AND notice_to_status.status_id IS NULL');
while ($n->fetch()) {
    if (preg_match('#^http://twitter.com/[\\w_.]+/status/(\\d+)$#', $n->uri, $match)) {
        $status_id = $match[1];
        Notice_to_status::saveNew($n->id, $status_id);
    }
}

 public static function beforeSchemaUpdate()
 {
     $table = strtolower(get_called_class());
     $schema = Schema::get();
     $schemadef = $schema->getTableDef($table);
     // 2015-09-04 We move Notice location data to Notice_location
     // First we see if we have to do this at all
     if (!isset($schemadef['fields']['lat']) && !isset($schemadef['fields']['lon']) && !isset($schemadef['fields']['location_id']) && !isset($schemadef['fields']['location_ns'])) {
         // We have already removed the location fields, so no need to migrate.
         return;
     }
     // Then we make sure the Notice_location table is created!
     $schema->ensureTable('notice_location', Notice_location::schemaDef());
     // Then we continue on our road to migration!
     echo "\nFound old {$table} table, moving location data to 'notice_location' table... (this will probably take a LONG time, but can be aborted and continued)";
     $notice = new Notice();
     $notice->query(sprintf('SELECT id, lat, lon, location_id, location_ns FROM %1$s ' . 'WHERE lat IS NOT NULL ' . 'OR lon IS NOT NULL ' . 'OR location_id IS NOT NULL ' . 'OR location_ns IS NOT NULL', $schema->quoteIdentifier($table)));
     print "\nFound {$notice->N} notices with location data, inserting";
     while ($notice->fetch()) {
         $notloc = Notice_location::getKV('notice_id', $notice->id);
         if ($notloc instanceof Notice_location) {
             print "-";
             continue;
         }
         $notloc = new Notice_location();
         $notloc->notice_id = $notice->id;
         $notloc->lat = $notice->lat;
         $notloc->lon = $notice->lon;
         $notloc->location_id = $notice->location_id;
         $notloc->location_ns = $notice->location_ns;
         $notloc->insert();
         print ".";
     }
     print "\n";
 }

 function _repeatsOfMeDirect($offset, $limit, $since_id, $max_id)
 {
     $qry = 'SELECT DISTINCT original.id AS id ' . 'FROM notice original JOIN notice rept ON original.id = rept.repeat_of ' . 'WHERE original.profile_id = ' . $this->id . ' ';
     if ($since_id != 0) {
         $qry .= 'AND original.id > ' . $since_id . ' ';
     }
     if ($max_id != 0) {
         $qry .= 'AND original.id <= ' . $max_id . ' ';
     }
     // NOTE: we sort by fave time, not by notice time!
     $qry .= 'ORDER BY original.id DESC ';
     if (!is_null($offset)) {
         $qry .= "LIMIT {$limit} OFFSET {$offset}";
     }
     $ids = array();
     $notice = new Notice();
     $notice->query($qry);
     while ($notice->fetch()) {
         $ids[] = $notice->id;
     }
     $notice->free();
     $notice = NULL;
     return $ids;
 }

 function _streamTaggedDirect($tag, $offset, $limit, $since_id, $max_id)
 {
     // XXX It would be nice to do this without a join
     // (necessary to do it efficiently on accounts with long history)
     $notice = new Notice();
     $query = "select id from notice join notice_tag on id=notice_id where tag='" . $notice->escape($tag) . "' and profile_id=" . intval($this->id);
     $since = Notice::whereSinceId($since_id, 'id', 'notice.created');
     if ($since) {
         $query .= " and ({$since})";
     }
     $max = Notice::whereMaxId($max_id, 'id', 'notice.created');
     if ($max) {
         $query .= " and ({$max})";
     }
     $query .= ' order by notice.created DESC, id DESC';
     if (!is_null($offset)) {
         $query .= " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
     }
     $notice->query($query);
     $ids = array();
     while ($notice->fetch()) {
         $ids[] = $notice->id;
     }
     return $ids;
 }

 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
define('INSTALLDIR', realpath(dirname(__FILE__) . '/..'));
require_once INSTALLDIR . '/scripts/commandline.inc';
common_log(LOG_INFO, 'Fixing up conversations.');
$nid = new Notice();
$nid->query('select id, reply_to from notice where conversation is null');
while ($nid->fetch()) {
    $cid = null;
    $notice = new Notice();
    if (empty($nid->reply_to)) {
        $cid = $nid->id;
    } else {
        $reply = Notice::staticGet('id', $notice->reply_to);
        if (empty($reply)) {
            common_log(LOG_WARNING, "Replied-to notice {$notice->reply_to} not found.");
            $notice->conversation = $notice->id;
        } else {
            if (empty($reply->conversation)) {
                common_log(LOG_WARNING, "Replied-to notice {$reply->id} has no conversation ID.");
                $notice->conversation = $notice->id;
            } else {

 function showContent()
 {
     $this->elementStart('div', 'qna-full-question');
     $this->raw($this->question->asHTML());
     $answer = $this->question->getAnswers();
     $this->elementStart('div', 'qna-full-question-answers');
     $answerIds = array();
     // @fixme use a filtered stream!
     if (!empty($answer)) {
         while ($answer->fetch()) {
             $answerIds[] = $answer->getNotice()->id;
         }
     }
     if (count($answerIds) > 0) {
         $notice = new Notice();
         $notice->query(sprintf('SELECT notice.* FROM notice WHERE notice.id IN (%s)', implode(',', $answerIds)));
         $nli = new NoticeList($notice, $this);
         $nli->show();
     }
     $user = common_current_user();
     if (!empty($user)) {
         $profile = $user->getProfile();
         $answer = QnA_Question::getAnswer($profile);
         if (empty($answer)) {
             $form = new QnanewanswerForm($this, $this->question, false);
             $form->show();
         }
     }
     $this->elementEnd('div');
     $this->elementEnd('div');
 }

function initConversation()
{
    printfnq("Ensuring all conversations have a row in conversation table...");
    $notice = new Notice();
    $notice->query('select distinct notice.conversation from notice ' . 'where notice.conversation is not null ' . 'and not exists (select conversation.id from conversation where id = notice.conversation)');
    while ($notice->fetch()) {
        $id = $notice->conversation;
        $uri = common_local_url('conversation', array('id' => $id));
        // @fixme db_dataobject won't save our value for an autoincrement
        // so we're bypassing the insert wrappers
        $conv = new Conversation();
        $sql = "insert into conversation (id,uri,created) values(%d,'%s','%s')";
        $sql = sprintf($sql, $id, $conv->escape($uri), $conv->escape(common_sql_now()));
        $conv->query($sql);
    }
    printfnq("DONE.\n");
}

 function _streamDirect($offset, $limit, $since_id, $max_id)
 {
     $notice = new Notice();
     // Temporary hack until notice_profile_id_idx is updated
     // to (profile_id, id) instead of (profile_id, created, id).
     // It's been falling back to PRIMARY instead, which is really
     // very inefficient for a profile that hasn't posted in a few
     // months. Even though forcing the index will cause a filesort,
     // it's usually going to be better.
     if (common_config('db', 'type') == 'mysql') {
         $index = '';
         $query = "select id from notice force index (notice_profile_id_idx) " . "where profile_id=" . $notice->escape($this->id);
         if ($since_id != 0) {
             $query .= " and id > {$since_id}";
         }
         if ($max_id != 0) {
             $query .= " and id < {$max_id}";
         }
         $query .= ' order by id DESC';
         if (!is_null($offset)) {
             $query .= " LIMIT {$limit} OFFSET {$offset}";
         }
         $notice->query($query);
     } else {
         $index = '';
         $notice->profile_id = $this->id;
         $notice->selectAdd();
         $notice->selectAdd('id');
         if ($since_id != 0) {
             $notice->whereAdd('id > ' . $since_id);
         }
         if ($max_id != 0) {
             $notice->whereAdd('id <= ' . $max_id);
         }
         $notice->orderBy('id DESC');
         if (!is_null($offset)) {
             $notice->limit($offset, $limit);
         }
         $notice->find();
     }
     $ids = array();
     while ($notice->fetch()) {
         $ids[] = $notice->id;
     }
     return $ids;
 }

 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
define('INSTALLDIR', realpath(dirname(__FILE__) . '/..'));
require_once INSTALLDIR . '/scripts/commandline.inc';
common_log(LOG_INFO, 'Initializing conversation table...');
$notice = new Notice();
$notice->query('select distinct conversation from notice');
while ($notice->fetch()) {
    $id = $notice->conversation;
    if ($id) {
        $uri = common_local_url('conversation', array('id' => $id));
        // @fixme db_dataobject won't save our value for an autoincrement
        // so we're bypassing the insert wrappers
        $conv = new Conversation();
        $sql = "insert into conversation (id,uri,created) values(%d,'%s','%s')";
        $sql = sprintf($sql, $id, $conv->escape($uri), $conv->escape(common_sql_now()));
        echo "{$id} ";
        $conv->query($sql);
        print "... ";
    }
}
print "done.\n";

 function _repeatsOfMeDirect($offset, $limit, $since_id, $max_id)
 {
     $qry = 'SELECT DISTINCT original.id AS id ' . 'FROM notice original JOIN notice rept ON original.id = rept.repeat_of ' . 'WHERE original.profile_id = ' . $this->id . ' ';
     $since = Notice::whereSinceId($since_id, 'original.id', 'original.created');
     if ($since) {
         $qry .= "AND ({$since}) ";
     }
     $max = Notice::whereMaxId($max_id, 'original.id', 'original.created');
     if ($max) {
         $qry .= "AND ({$max}) ";
     }
     $qry .= 'ORDER BY original.created, original.id DESC ';
     if (!is_null($offset)) {
         $qry .= "LIMIT {$limit} OFFSET {$offset}";
     }
     $ids = array();
     $notice = new Notice();
     $notice->query($qry);
     while ($notice->fetch()) {
         $ids[] = $notice->id;
     }
     $notice->free();
     $notice = NULL;
     return $ids;
 }

 static function getStreamDirect($qry, $offset, $limit, $since_id, $before_id, $order, $since)
 {
     $needAnd = false;
     $needWhere = true;
     if (preg_match('/\\bWHERE\\b/i', $qry)) {
         $needWhere = false;
         $needAnd = true;
     }
     if ($since_id > 0) {
         if ($needWhere) {
             $qry .= ' WHERE ';
             $needWhere = false;
         } else {
             $qry .= ' AND ';
         }
         $qry .= ' notice.id > ' . $since_id;
     }
     if ($before_id > 0) {
         if ($needWhere) {
             $qry .= ' WHERE ';
             $needWhere = false;
         } else {
             $qry .= ' AND ';
         }
         $qry .= ' notice.id < ' . $before_id;
     }
     if ($since) {
         if ($needWhere) {
             $qry .= ' WHERE ';
             $needWhere = false;
         } else {
             $qry .= ' AND ';
         }
         $qry .= ' notice.created > \'' . date('Y-m-d H:i:s', $since) . '\'';
     }
     # Allow ORDER override
     if ($order) {
         $qry .= $order;
     } else {
         $qry .= ' ORDER BY notice.created DESC, notice.id DESC ';
     }
     if (common_config('db', 'type') == 'pgsql') {
         $qry .= ' LIMIT ' . $limit . ' OFFSET ' . $offset;
     } else {
         $qry .= ' LIMIT ' . $offset . ', ' . $limit;
     }
     $notice = new Notice();
     $notice->query($qry);
     return $notice;
 }