Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
class CleanupDuplicatesAndAddUniqueIndexes < ActiveRecord::Migration
class Post < ActiveRecord::Base
end
class StatusMessage < Post
end
class Photo < ActiveRecord::Base
belongs_to :status_message, foreign_key: :status_message_guid, primary_key: :guid
end
class ShareVisibility < ActiveRecord::Base
end
def up
# temporary index to speed up the migration
add_index :photos, :guid, length: 191
# fix share visibilities for private photos
if AppConfig.postgres?
execute "UPDATE share_visibilities" \
" SET shareable_id = (SELECT MIN(p3.id) FROM photos as p3 WHERE p3.guid = p1.guid)" \
" FROM photos as p1, photos as p2" \
" WHERE p1.id = share_visibilities.shareable_id AND (p1.guid = p2.guid AND p1.id > p2.id)" \
" AND share_visibilities.shareable_type = 'Photo'"
else
execute "UPDATE share_visibilities" \
" INNER JOIN photos as p1 ON p1.id = share_visibilities.shareable_id" \
" INNER JOIN photos as p2 ON p1.guid = p2.guid AND p1.id > p2.id" \
" SET share_visibilities.shareable_id = (SELECT MIN(p3.id) FROM photos as p3 WHERE p3.guid = p1.guid)" \
" WHERE share_visibilities.shareable_type = 'Photo'"
end
%i(conversations messages photos polls poll_answers poll_participations).each do |table|
delete_duplicates_and_create_unique_index(table)
end
# fix photo public flag again ...
Photo.joins(:status_message).where(posts: {public: true}).update_all(public: true)
ShareVisibility.joins("INNER JOIN photos ON photos.id = share_visibilities.shareable_id")
.where(shareable_type: "Photo", photos: {public: true}).delete_all
end
def down
raise ActiveRecord::IrreversibleMigration
end
private
def delete_duplicates_and_create_unique_index(table)
# temporary index to speed up the migration
add_index table, :guid, length: 191 unless table == :photos
if AppConfig.postgres?
execute "DELETE FROM #{table} AS t1 USING #{table} AS t2 WHERE t1.guid = t2.guid AND t1.id > t2.id"
else
execute "DELETE t1 FROM #{table} t1, #{table} t2 WHERE t1.guid = t2.guid AND t1.id > t2.id"
end
remove_index table, column: :guid
# now create unique index \o/
add_index table, :guid, length: 191, unique: true
end
end