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