migrations: drop tsv_parent_directories
All references to tsv_parent_directories are dropped.
For golang/go#39629
Change-Id: Ie5a4a6995cd181c6976b430f4b7d1c3fd2a82c2d
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/262699
Trust: Julie Qiu <julie@golang.org>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
diff --git a/migrations/000031_drop_tsv_parent_directories.down.sql b/migrations/000031_drop_tsv_parent_directories.down.sql
new file mode 100644
index 0000000..6f697ff
--- /dev/null
+++ b/migrations/000031_drop_tsv_parent_directories.down.sql
@@ -0,0 +1,68 @@
+-- Copyright 2020 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 FUNCTION to_tsvector_parent_directories(package_path text, module_path text) RETURNS tsvector
+ LANGUAGE plpgsql PARALLEL SAFE
+ AS $$
+ DECLARE
+ current_directory TEXT;
+ parent_directories TEXT;
+ sub_path TEXT;
+ sub_directories TEXT[][];
+ BEGIN
+ IF package_path = module_path THEN
+ RETURN module_path::tsvector;
+ END IF;
+
+ IF module_path = 'std' THEN
+ sub_path := package_path;
+ ELSE
+ sub_path := substr(package_path, length(module_path) + 2);
+ current_directory := module_path;
+ parent_directories := module_path;
+ END IF;
+
+ sub_directories := regexp_split_to_array(sub_path, '/');
+ FOR i IN 1..cardinality(sub_directories) LOOP
+ IF current_directory IS NULL THEN
+ current_directory := sub_directories[i];
+ ELSE
+ current_directory := COALESCE(current_directory, '') || '/' || sub_directories[i];
+ END IF;
+ parent_directories = COALESCE(parent_directories, '') || ' ' || current_directory;
+ END LOOP;
+ RETURN parent_directories::tsvector;
+END;
+$$;
+COMMENT ON FUNCTION to_tsvector_parent_directories IS
+'FUNCTION to_tsvector_parent_directories computes all directories that exist between module_path and package_path, inclusive of both module_path and package_path. Return the result as a tsvector.';
+
+CREATE FUNCTION trigger_modify_packages_tsv_parent_directories() RETURNS TRIGGER
+ LANGUAGE plpgsql
+ AS $$
+ BEGIN
+ NEW.tsv_parent_directories = to_tsvector_parent_directories(NEW.path, NEW.module_path);
+ RETURN NEW;
+END;
+$$;
+COMMENT ON FUNCTION trigger_modify_packages_tsv_parent_directories IS
+'FUNCTION trigger_modify_packages_tsv_parent_directories invokes FUNCTION to_tsvector_parent_directories and sets the value of tsv_parent_directories to the output.';
+
+ALTER TABLE packages ADD COLUMN tsv_parent_directories tsvector;
+
+CREATE TRIGGER set_tsv_parent_directories BEFORE INSERT ON packages
+ FOR EACH ROW EXECUTE PROCEDURE trigger_modify_packages_tsv_parent_directories();
+COMMENT ON TRIGGER set_tsv_parent_directories ON packages IS
+'TRIGGER set_tsv_parent_directories sets the value of tsv_parent_directories to the output of FUNCTION trigger_modify_search_documents_tsv_parent_directories when a new row in inserted.';
+
+ALTER TABLE search_documents ADD COLUMN tsv_parent_directories tsvector;
+
+CREATE TRIGGER set_tsv_parent_directories BEFORE INSERT ON search_documents
+ FOR EACH ROW EXECUTE PROCEDURE trigger_modify_search_documents_tsv_parent_directories();
+COMMENT ON TRIGGER set_tsv_parent_directories ON search_documents IS
+'TRIGGER set_tsv_parent_directories sets the value of tsv_parent_directories to the output of FUNCTION trigger_modify_search_documents_tsv_parent_directories when a new row in inserted.';
+
+END;
diff --git a/migrations/000031_drop_tsv_parent_directories.up.sql b/migrations/000031_drop_tsv_parent_directories.up.sql
new file mode 100644
index 0000000..f8bf80d
--- /dev/null
+++ b/migrations/000031_drop_tsv_parent_directories.up.sql
@@ -0,0 +1,16 @@
+-- Copyright 2020 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 TRIGGER set_tsv_parent_directories ON search_documents;
+ALTER TABLE search_documents DROP COLUMN tsv_parent_directories;
+
+DROP TRIGGER set_tsv_parent_directories ON packages;
+ALTER TABLE packages DROP COLUMN tsv_parent_directories;
+
+DROP FUNCTION trigger_modify_packages_tsv_parent_directories;
+DROP FUNCTION to_tsvector_parent_directories;
+
+END;
