From d7b01517714d16c3d8f57c34607a2579bd7ae8eb Mon Sep 17 00:00:00 2001
From: Jochen Topf <jochen@topf.org>
Date: Mon, 22 Nov 2010 17:34:58 +0100
Subject: Improved search and some more translations

---
 sources/master/master.sql | 20 ++++++++++++++++++++
 sources/master/search.sql | 23 +++++++++++++++++++++++
 sources/master/update.sh  |  5 +++++
 3 files changed, 48 insertions(+)
 create mode 100644 sources/master/search.sql

(limited to 'sources')

diff --git a/sources/master/master.sql b/sources/master/master.sql
index a71c584..3d5463e 100644
--- a/sources/master/master.sql
+++ b/sources/master/master.sql
@@ -129,5 +129,25 @@ UPDATE languages SET wiki_tag_pages=(SELECT count(distinct key) FROM wiki.wikipa
 
 -- ============================================================================
 
+DROP TABLE IF EXISTS suggestions;
+CREATE TABLE suggestions (
+    key     TEXT,
+    value   TEXT,
+    count   INTEGER,
+    in_wiki INTEGER DEFAULT 0,
+    score   INTEGER
+);
+
+INSERT INTO suggestions (key, value, count) SELECT key, NULL, count_all FROM db.keys WHERE count_all > 10000 OR in_wiki = 1;
+INSERT INTO suggestions (key, value, count) SELECT key, value, count FROM db.prevalent_values WHERE count > 1000;
+
+INSERT INTO suggestions (key, value, in_wiki) SELECT key, value, 1 FROM wiki.wikipages WHERE value IS NOT NULL AND key || '=' || value NOT IN (SELECT key || '=' || value FROM suggestions);
+
+DELETE FROM suggestions WHERE count < 100;
+
+UPDATE suggestions SET score = count * (1+in_wiki);
+
+-- ============================================================================
+
 ANALYZE;
 
diff --git a/sources/master/search.sql b/sources/master/search.sql
new file mode 100644
index 0000000..6c45182
--- /dev/null
+++ b/sources/master/search.sql
@@ -0,0 +1,23 @@
+-- ============================================================================
+--
+--  Taginfo
+--
+--  search.sql
+--
+-- ============================================================================
+
+.bail ON
+
+ATTACH DATABASE '__DIR__/db/taginfo-db.db' AS db;
+
+DROP TABLE IF EXISTS ftsearch;
+CREATE VIRTUAL TABLE ftsearch USING fts3 (
+    key       TEXT,
+    value     TEXT,
+    count_all INTEGER
+);
+
+INSERT INTO ftsearch (key, value, count_all) SELECT key, value, count_all FROM db.tags;
+
+ANALYZE;
+
diff --git a/sources/master/update.sh b/sources/master/update.sh
index f1598d9..6b85f64 100755
--- a/sources/master/update.sh
+++ b/sources/master/update.sh
@@ -16,6 +16,11 @@ if [ "x" = "x$DIR" ]; then
     exit 1
 fi
 
+echo "`$DATECMD` Create search database..."
+
+rm -f $DIR/taginfo-search.db
+perl -pe "s|__DIR__|$DIR|" search.sql | sqlite3 $DIR/taginfo-search.db
+
 echo "`$DATECMD` Start master..."
 
 DATABASE=$DIR/taginfo-master.db
-- 
cgit v1.2.3