34 lines
1.3 KiB
SQL
34 lines
1.3 KiB
SQL
|
-- PERS-02 (owner approval 2026-10-08): cities become a tree again, as in legacy
|
||
|
|
-- (country > province > city > district, henza `cities.parent`). The flat
|
||
|
|
-- `province` label is turned into parent rows, nothing is lost, then dropped.
|
||
|
|
|
||
|
|
ALTER TABLE "City" ADD COLUMN "parentId" TEXT;
|
||
|
|
|
||
|
|
-- One province row per distinct label, named as legacy names them
|
||
|
|
-- («استان تهران»), unless a city row already carries that name.
|
||
|
|
INSERT INTO "City" ("id", "name")
|
||
|
|
SELECT 'cprov' || substr(md5(p.pname), 1, 20), p.pname
|
||
|
|
FROM (
|
||
|
|
SELECT DISTINCT
|
||
|
|
CASE WHEN btrim("province") LIKE 'استان%' THEN btrim("province") ELSE 'استان ' || btrim("province") END AS pname
|
||
|
|
FROM "City"
|
||
|
|
WHERE "province" IS NOT NULL AND btrim("province") <> ''
|
||
|
|
) p
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM "City" c WHERE c."name" = p.pname);
|
||
|
|
|
||
|
|
UPDATE "City" c
|
||
|
|
SET "parentId" = (
|
||
|
|
SELECT p."id" FROM "City" p
|
||
|
|
WHERE p."id" <> c."id"
|
||
|
|
AND p."name" = CASE WHEN btrim(c."province") LIKE 'استان%' THEN btrim(c."province") ELSE 'استان ' || btrim(c."province") END
|
||
|
|
ORDER BY p."id"
|
||
|
|
LIMIT 1
|
||
|
|
)
|
||
|
|
WHERE c."province" IS NOT NULL AND btrim(c."province") <> '';
|
||
|
|
|
||
|
|
ALTER TABLE "City" DROP COLUMN "province";
|
||
|
|
|
||
|
|
CREATE INDEX "City_parentId_idx" ON "City"("parentId");
|
||
|
|
|
||
|
|
ALTER TABLE "City" ADD CONSTRAINT "City_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "City"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|