From 0dc41b7ca1d7254b2fc06593304560f457b11b06 Mon Sep 17 00:00:00 2001 From: Marcel Werk Date: Tue, 26 Jun 2012 13:52:39 +0200 Subject: [PATCH] Changed the behaviour of fulltext indices for PostgreSQL --- .../editor/PostgreSQLDatabaseEditor.class.php | 29 +++++++++++++++++-- 1 file changed, 26 insertions(+), 3 deletions(-) diff --git a/wcfsetup/install/files/lib/system/database/editor/PostgreSQLDatabaseEditor.class.php b/wcfsetup/install/files/lib/system/database/editor/PostgreSQLDatabaseEditor.class.php index bdc05e6dc0..973b7d4fcb 100644 --- a/wcfsetup/install/files/lib/system/database/editor/PostgreSQLDatabaseEditor.class.php +++ b/wcfsetup/install/files/lib/system/database/editor/PostgreSQLDatabaseEditor.class.php @@ -238,13 +238,36 @@ class PostgreSQLDatabaseEditor extends DatabaseEditor { } if ($indexData['type'] == 'FULLTEXT') { - $sql = "CREATE INDEX ".$indexName." ON ".$tableName." USING gin(to_tsvector('english', \"".implode('" || \' \' || "', $columns)."\"))"; + // add new column for fulltext index + $sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$indexName." tsvector"; + $statement = $this->dbObj->prepareStatement($sql); + $statement->execute(); + + // add gin index + $sql = "CREATE INDEX ".$tableName."_".$indexName."_fulltext_key ON ".$tableName." USING gin(".$indexName.")"; + $statement = $this->dbObj->prepareStatement($sql); + $statement->execute(); + + // update fulltext index + $sql = "UPDATE ".$tableName." + SET ".$indexName." = to_tsvector('english', \"".implode('" || \' \' || "', $columns)."\")"; + $statement = $this->dbObj->prepareStatement($sql); + $statement->execute(); + + // add trigger + $sql = "CREATE TRIGGER ".$tableName."_".$indexName."_trigger + BEFORE INSERT OR UPDATE + ON ".$tableName." + FOR EACH ROW EXECUTE PROCEDURE + tsvector_update_trigger(".$indexName.", 'pg_catalog.english', ".implode(', ', $columns).");"; + $statement = $this->dbObj->prepareStatement($sql); + $statement->execute(); } else { $sql = "CREATE ".($indexData['type'] == 'UNIQUE' ? "UNIQUE " : "")."INDEX ".$indexName." ON ".$tableName." (".$indexData['columns'].")"; + $statement = $this->dbObj->prepareStatement($sql); + $statement->execute(); } - $statement = $this->dbObj->prepareStatement($sql); - $statement->execute(); } /** -- 2.20.1