<?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) {