core/db/migrate/20240303174340_fix_spree_stock_item_unique_index.rb
class FixSpreeStockItemUniqueIndex < ActiveRecord::Migration[6.1]
def change
remove_index :spree_stock_items, name: 'stock_item_by_loc_var_id_deleted_at' if index_exists?(:spree_stock_items, [:stock_location_id, :variant_id], name: 'stock_item_by_loc_var_id_deleted_at')
unless index_exists?(:spree_stock_items, ['variant_id', 'stock_location_id'], name: 'index_spree_stock_items_unique_without_deleted_at')
# MySQL doesn't support partial indexes
if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
reversible do |dir|
dir.up do
execute <<-SQL
CREATE UNIQUE INDEX index_spree_stock_items_unique_without_deleted_at
ON spree_stock_items(
stock_location_id,
variant_id,
(COALESCE(deleted_at, CAST('1970-01-01' AS DATETIME)))
);
SQL
end
dir.down do
remove_index :spree_stock_items, name: :index_spree_stock_items_unique_without_deleted_at
end
end
else
add_index(
:spree_stock_items,
['variant_id', 'stock_location_id'],
name: 'index_spree_stock_items_unique_without_deleted_at',
unique: true,
where: 'deleted_at IS NULL',
)
end
end
end
end