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; }