migrations: add symbol_search_documents
A table is added to contain information for searching for symbols.
Build contexts are added as a text array instead of as individual rows,
since the only use case for that information is linking users to the
correct URL if the symbol is not present in the default build context.
For golang/go#44142
Change-Id: I759b22e0ced7fec547778aba11a1b5f641530fb8
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/318650
Trust: Julie Qiu <julie@golang.org>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
diff --git a/migrations/000098_add_symbol_search_documents.down.sql b/migrations/000098_add_symbol_search_documents.down.sql
new file mode 100644
index 0000000..4c97bca
--- /dev/null
+++ b/migrations/000098_add_symbol_search_documents.down.sql
@@ -0,0 +1,9 @@
+-- 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;
+
+DROP TABLE symbol_search_documents;
+
+END;
diff --git a/migrations/000098_add_symbol_search_documents.up.sql b/migrations/000098_add_symbol_search_documents.up.sql
new file mode 100644
index 0000000..bb5302e
--- /dev/null
+++ b/migrations/000098_add_symbol_search_documents.up.sql
@@ -0,0 +1,26 @@
+-- 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;
+
+CREATE TABLE symbol_search_documents (
+ id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+ package_path_id INTEGER NOT NULL,
+ symbol_name_id INTEGER NOT NULL,
+ build_contexts TEXT[] NOT NULL,
+ tsv_symbol_tokens TSVECTOR NOT NULL,
+
+ UNIQUE(package_path_id, symbol_name_id),
+ FOREIGN KEY (package_path_id) REFERENCES paths(id) ON DELETE CASCADE,
+ FOREIGN KEY (symbol_name_id) REFERENCES symbol_names(id) ON DELETE CASCADE
+
+ -- Ideally this FK would be added now, but we need to populate
+ -- search_documents.package_path_id first.
+ -- FOREIGN KEY (package_path_id) REFERENCES search_documents(package_path_id) ON DELETE CASCADE,
+);
+
+CREATE INDEX idx_symbols_search_documents_tsv_symbol_tokens ON symbol_search_documents
+ USING gin (tsv_symbol_tokens);
+
+END;
