<?php
global $pdo;
/* starts processing */
$pdo = new PDO("sqlite:so.sqlite.android");
if (!$pdo) {
echo "Error when trying to connect to database, exiting\n";
exit(-1);
}
$pdo->query('BEGIN TRANSACTION');
echo "==Extracting tag graph from post titles and bodies==\n";
$progress = 1;
foreach ($pdo->query("SELECT id, tags, parent_id, post_type_id FROM posts ") as $row) {
$post_type = $row['post_type_id'];
$post_id = $post_type == '1' ? 'id' : 'parent_id';
$post_id = $row[$post_id];
$tagmap = extracttags($row['tags']);
foreach ($tagmap as $tag1) {
foreach ($tagmap as $tag2) {
$query = "SELECT count(*) FROM tag_graph WHERE tag1=" . $pdo->quote($tag1) . " AND tag2=" . $pdo->quote($tag2);
echo "{$query}\n";
$result = $pdo->query($query);
$result = $result->fetch();
if ($result['count(*)']) {
$query = "UPDATE tag_graph SET weight=weight+1 WHERE (" . "tag1=" . $pdo->quote($tag1) . " AND " . "tag2=" . $pdo->quote($tag2) . ") OR (" . "tag2=" . $pdo->quote($tag1) . " AND " . "tag1=" . $pdo->quote($tag2) . ")";
echo "{$query}\n";
$pdo->query($query);
} else {
$query = "INSERT INTO tag_graph (tag1, tag2, weight) VALUES (" . $pdo->quote($tag1) . " , " . $pdo->quote($tag2) . " , " . "1" . ")";
echo "\n{$query}";
$pdo->query($query);
$query = "INSERT INTO tag_graph (tag1, tag2, weight) VALUES (" . $pdo->quote($tag2) . " , " . $pdo->quote($tag1) . " , " . "1" . ")";
$index_tables = $post_type == '1' ? 'searchindex_questions' : 'searchindex_responses';
$post_id = $post_type == '1' ? 'id' : 'parent_id';
$post_id = $row[$post_id];
echo "{$progress} " . ($post_type == '1' ? "Question" : "Answer") . " " . $row['id'] . "\n";
$progress++;
if ($post_type == '1') {
foreach (extractwords($row['title']) as $word) {
$query = "INSERT OR IGNORE INTO searchindex_question_titles (id, word) VALUES (" . $pdo->quote($post_id) . "," . $pdo->quote($word) . ")\n";
$pdo->query($query);
}
}
foreach (extractwords($row['body']) as $word) {
$query = "INSERT OR IGNORE INTO " . $index_tables . " (id, word) VALUES (" . $pdo->quote($post_id) . "," . $pdo->quote($word) . ")\n";
$pdo->query($query);
}
foreach (extracttags($row['tags']) as $tag) {
$query = "INSERT OR IGNORE INTO searchindex_tags (id, tag) VALUES (" . $pdo->quote($post_id) . "," . $pdo->quote($tag) . ")\n";
$pdo->query($query);
}
}
$pdo->query('END TRANSACTION');
$pdo->query('BEGIN TRANSACTION');
echo "== Indexing post comments ==\n";
$progress = 1;
foreach ($pdo->query("SELECT posts.post_type_id AS post_type_id, comments.post_id AS id,comments.text AS text, posts.parent_id AS parent_id FROM comments JOIN posts ON comments.post_id=posts.id") as $row) {
$post_type = $row['post_type_id'];
$post_id = $post_type == '1' ? 'id' : 'parent_id';
$post_id = $row[$post_id];
echo "{$progress} Comment " . $row['id'] . "\n";
$progress++;
foreach (extractwords($row['text']) as $word) {