Packages
phoenix_kit
1.7.151
1.7.208
1.7.207
1.7.206
1.7.205
1.7.204
1.7.203
1.7.202
1.7.201
1.7.200
1.7.199
1.7.198
1.7.197
1.7.196
1.7.194
1.7.193
1.7.192
1.7.191
1.7.190
1.7.189
1.7.187
1.7.186
1.7.185
1.7.184
1.7.183
1.7.182
1.7.181
1.7.180
1.7.179
1.7.178
1.7.177
1.7.176
1.7.175
1.7.174
1.7.173
1.7.172
1.7.171
1.7.170
1.7.169
1.7.168
1.7.167
1.7.166
1.7.165
1.7.164
1.7.162
1.7.161
1.7.160
1.7.159
1.7.157
1.7.156
1.7.155
1.7.154
1.7.153
1.7.152
1.7.151
1.7.150
1.7.149
1.7.146
1.7.145
1.7.144
1.7.143
1.7.138
1.7.133
1.7.132
1.7.131
1.7.130
1.7.128
1.7.126
1.7.125
1.7.121
1.7.120
1.7.119
1.7.118
1.7.117
1.7.116
1.7.115
1.7.114
1.7.113
1.7.112
1.7.111
1.7.110
1.7.109
1.7.108
1.7.107
1.7.106
1.7.105
1.7.104
1.7.103
1.7.102
1.7.101
1.7.100
1.7.99
1.7.98
1.7.97
1.7.96
1.7.95
1.7.94
1.7.93
1.7.92
1.7.91
1.7.90
1.7.89
1.7.88
1.7.87
1.7.86
1.7.85
1.7.84
1.7.83
1.7.82
1.7.81
1.7.80
1.7.79
1.7.78
1.7.77
1.7.76
1.7.75
1.7.74
1.7.71
1.7.70
1.7.69
1.7.66
1.7.65
1.7.64
1.7.63
1.7.62
1.7.61
1.7.59
1.7.58
1.7.57
1.7.56
1.7.55
1.7.54
1.7.53
1.7.52
1.7.51
1.7.49
1.7.44
1.7.43
1.7.42
1.7.41
1.7.39
1.7.38
1.7.37
1.7.36
1.7.34
1.7.33
1.7.31
1.7.30
1.7.29
1.7.28
1.7.27
1.7.26
1.7.25
1.7.24
1.7.23
1.7.22
1.7.21
1.7.20
1.7.19
1.7.18
1.7.17
1.7.16
1.7.15
1.7.14
1.7.13
1.7.12
1.7.11
1.7.10
1.7.9
1.7.8
1.7.7
1.7.6
1.7.5
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.20
1.6.19
1.6.18
1.6.17
1.6.16
1.6.15
1.6.14
1.6.13
1.6.12
1.6.11
1.6.10
1.6.9
1.6.8
1.6.7
1.6.6
1.6.5
1.6.4
1.6.3
1.5.2
1.5.1
1.5.0
1.4.9
1.4.8
1.4.7
1.4.6
1.4.5
1.4.4
1.4.3
1.4.2
1.4.1
1.4.0
1.3.2
1.3.1
1.3.0
1.2.10
1.2.9
1.2.8
1.2.7
1.2.5
1.2.4
1.2.2
1.2.1
1.2.0
1.1.0
1.0.0
A foundation for building Elixir Phoenix apps — SaaS, social networks, ERP systems, marketplaces, and more
Current section
Files
Jump to
Current section
Files
lib/phoenix_kit/migrations/postgres/v52.ex
defmodule PhoenixKit.Migrations.Postgres.V52 do
@moduledoc """
V52: Shop localized slug functional unique index
After V47 converted slug fields to JSONB maps, the old unique constraint
on the slug column no longer works correctly for upsert operations.
PostgreSQL's ON CONFLICT compares entire JSONB objects, so:
- {"en-US": "my-slug"} and {"en-US": "my-slug", "es-ES": "otro"} are different
This migration creates a functional unique index that extracts the primary
slug value for uniqueness checking.
## Changes
- Creates extract_primary_slug() SQL function to get the primary slug value
- Creates unique functional index on products using the function
- Creates unique functional index on categories using the function
- Removes old incorrect unique indexes if they exist
## Primary Slug Resolution
The function extracts the slug value from the alphabetically first language key.
This is deterministic, language-agnostic, and works regardless of which language
is configured as default. The function must be IMMUTABLE for the unique index
to work, so it cannot query the settings table for default_language.
"""
use Ecto.Migration
def up(%{prefix: prefix} = _opts) do
prefix_str = if prefix && prefix != "public", do: "#{prefix}.", else: ""
schema_name = if prefix && prefix != "public", do: prefix, else: "public"
# Step 1: Create SQL function to extract primary slug from JSONB
# Uses alphabetically first key for deterministic, language-agnostic extraction.
# The function is IMMUTABLE (required for unique index) so it cannot query
# the settings table for default_language. Alphabetical key order ensures
# consistent behavior regardless of which language is configured as default.
execute """
CREATE OR REPLACE FUNCTION #{prefix_str}extract_primary_slug(slug_jsonb JSONB)
RETURNS TEXT AS $$
BEGIN
RETURN (SELECT value FROM jsonb_each_text(slug_jsonb) ORDER BY key LIMIT 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT
"""
# Step 2: Fix shop products indexes (only if table exists)
execute """
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = '#{schema_name}' AND table_name = 'phoenix_kit_shop_products') THEN
-- Drop any old incorrect unique indexes
DROP INDEX IF EXISTS #{prefix_str}phoenix_kit_shop_products_slug_unique_idx;
-- Drop constraint-based unique if it exists on JSONB
ALTER TABLE #{prefix_str}phoenix_kit_shop_products
DROP CONSTRAINT IF EXISTS phoenix_kit_shop_products_slug_unique;
ALTER TABLE #{prefix_str}phoenix_kit_shop_products
DROP CONSTRAINT IF EXISTS phoenix_kit_shop_products_slug_key;
-- Create functional unique index for products
CREATE UNIQUE INDEX idx_shop_products_slug_primary
ON #{prefix_str}phoenix_kit_shop_products (
(#{prefix_str}extract_primary_slug(slug))
)
WHERE #{prefix_str}extract_primary_slug(slug) IS NOT NULL;
END IF;
END $$;
"""
# Step 3: Fix shop categories indexes (only if table exists)
execute """
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = '#{schema_name}' AND table_name = 'phoenix_kit_shop_categories') THEN
-- Drop any old incorrect unique indexes
DROP INDEX IF EXISTS #{prefix_str}phoenix_kit_shop_categories_slug_unique_idx;
-- Drop constraint-based unique if it exists on JSONB
ALTER TABLE #{prefix_str}phoenix_kit_shop_categories
DROP CONSTRAINT IF EXISTS phoenix_kit_shop_categories_slug_unique;
ALTER TABLE #{prefix_str}phoenix_kit_shop_categories
DROP CONSTRAINT IF EXISTS phoenix_kit_shop_categories_slug_key;
-- Create functional unique index for categories
CREATE UNIQUE INDEX idx_shop_categories_slug_primary
ON #{prefix_str}phoenix_kit_shop_categories (
(#{prefix_str}extract_primary_slug(slug))
)
WHERE #{prefix_str}extract_primary_slug(slug) IS NOT NULL;
END IF;
END $$;
"""
# Record migration version
execute "COMMENT ON TABLE #{prefix_str}phoenix_kit IS '52'"
end
def down(%{prefix: prefix} = _opts) do
prefix_str = if prefix && prefix != "public", do: "#{prefix}.", else: ""
# Drop functional indexes
execute """
DROP INDEX IF EXISTS #{prefix_str}idx_shop_products_slug_primary
"""
execute """
DROP INDEX IF EXISTS #{prefix_str}idx_shop_categories_slug_primary
"""
# Drop the function
execute """
DROP FUNCTION IF EXISTS #{prefix_str}extract_primary_slug(JSONB)
"""
# Record migration version
execute "COMMENT ON TABLE #{prefix_str}phoenix_kit IS '51'"
end
end