migrations: add column search_documents.ln_imported_by_count
Column search_documents.ln_imported_by_count is added, since both symbol
and package search use that value for computing scores.
At the moment, symbol search uses
symbol_search_documents.ln_imported_by_count (which can be dropped after
this column is populated), and package search computes the value for
each result during search. Instead, this column can be used.
trigger_modify_ln_imported_by_count is used to set the value of
ln_imported_by_count every time imported_by_count is upserted.
For golang/go#44142
Change-Id: Icce55811e0c5fce5e5607787aaf11cb3bf65b818
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/347989
Trust: Julie Qiu <julie@golang.org>
Run-TryBot: Julie Qiu <julie@golang.org>
Reviewed-by: Jamal Carvalho <jamal@golang.org>
TryBot-Result: kokoro <noreply+kokoro@google.com>
diff --git a/migrations/000150_add_search_documents_ln_imported_by_count.down.sql b/migrations/000150_add_search_documents_ln_imported_by_count.down.sql
new file mode 100644
index 0000000..6c59e0d
--- /dev/null
+++ b/migrations/000150_add_search_documents_ln_imported_by_count.down.sql
@@ -0,0 +1,10 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+ALTER TABLE search_documents DROP COLUMN ln_imported_by_count;
+DROP TRIGGER set_ln_imported_by_count ON search_documents;
+
+END;
diff --git a/migrations/000150_add_search_documents_ln_imported_by_count.up.sql b/migrations/000150_add_search_documents_ln_imported_by_count.up.sql
new file mode 100644
index 0000000..3f70493
--- /dev/null
+++ b/migrations/000150_add_search_documents_ln_imported_by_count.up.sql
@@ -0,0 +1,14 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+-- ln_imported_by_count is used to rank package and symbol searches.
+ALTER TABLE search_documents ADD COLUMN ln_imported_by_count NUMERIC;
+CREATE INDEX idx_search_documents_ln_imported_by_count_desc ON search_documents (ln_imported_by_count DESC);
+
+CREATE TRIGGER set_ln_imported_by_count BEFORE INSERT OR UPDATE ON search_documents
+ FOR EACH ROW EXECUTE PROCEDURE trigger_modify_ln_imported_by_count();
+
+END;
