Add unique index to poll participations on author_id and poll_id

Previously we had only a Rails validation which ensured poll participation
uniqueness but this adds uniqueness control to the database level, so that
uniqueness is guaranteed even when changing data with avoiding Rails
validations.

closes #7798

1 files changed, 19 insertions, 0 deletions

diff --git a/db/migrate/20180425125409_add_poll_participations_unique_index_on_author_and_poll.rb b/db/migrate/20180425125409_add_poll_participations_unique_index_on_author_and_poll.rb
new file mode 100644
index 000000000..2f7376f91
--- /dev/null
+++ b/db/migrate/20180425125409_add_poll_participations_unique_index_on_author_and_poll.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+class AddPollParticipationsUniqueIndexOnAuthorAndPoll < ActiveRecord::Migration[5.1]
+  def change
+    reversible do |change|
+      change.up do
+        duplicate_query = "WHERE a1.poll_id = a2.poll_id AND a1.author_id = a2.author_id AND a1.id > a2.id"
+        if AppConfig.postgres?
+          execute("DELETE FROM poll_participations AS a1 USING poll_participations AS a2 #{duplicate_query}")
+        else
+          execute("DELETE a1 FROM poll_participations a1, poll_participations a2 #{duplicate_query}")
+        end
+      end
+    end
+
+    add_index :poll_participations, %i[poll_id author_id], unique: true
+    remove_index :poll_participations, :poll_id
+  end
+end