mirror of
https://gitlab.com/PronounsPage/PronounsPage.git
synced 2025-09-25 22:19:28 -04:00
115 lines
4.3 KiB
SQL
115 lines
4.3 KiB
SQL
-- Up
|
|
|
|
ALTER TABLE nouns
|
|
ADD COLUMN words TEXT NOT NULL DEFAULT '{}';
|
|
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.masc.singular',
|
|
json('["' || replace(replace(replace(masc, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE masc != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.masc.plural',
|
|
json('["' || replace(replace(replace(mascPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE mascPl != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.fem.singular',
|
|
json('["' || replace(replace(replace(fem, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE fem != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.fem.plural',
|
|
json('["' || replace(replace(replace(femPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE femPl != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.neutr.singular',
|
|
json('["' || replace(replace(replace(neutr, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE neutr != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.neutr.plural',
|
|
json('["' || replace(replace(replace(neutrPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE neutrPl != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.nb.singular',
|
|
json('["' || replace(replace(replace(nb, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE nb != '';
|
|
UPDATE nouns
|
|
SET words = json_set(words, '$.nb.plural',
|
|
json('["' || replace(replace(replace(nbPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
|
|
WHERE nbPl != '';
|
|
|
|
ALTER TABLE nouns
|
|
ADD COLUMN key TEXT;
|
|
UPDATE nouns
|
|
SET key = lower(trim(json(words) -> '$.masc.singular[0]', '"'))
|
|
WHERE 1 = 1;
|
|
UPDATE nouns
|
|
SET key = replace(key, 'der ', '')
|
|
WHERE locale = 'de';
|
|
|
|
CREATE INDEX nouns_key ON nouns (key);
|
|
|
|
DROP INDEX nouns_masc;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN masc;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN mascPl;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN fem;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN femPl;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN neutr;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN neutrPl;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN nb;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN nbPl;
|
|
|
|
-- Down
|
|
|
|
ALTER TABLE nouns
|
|
ADD COLUMN masc TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN mascPl TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN fem TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN femPl TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN neutr TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN neutrPl TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN nb TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE nouns
|
|
ADD COLUMN nbPl TEXT NOT NULL DEFAULT '';
|
|
|
|
CREATE INDEX nouns_masc ON nouns (masc);
|
|
|
|
DROP INDEX nouns_key;
|
|
ALTER TABLE nouns
|
|
DROP COLUMN key;
|
|
|
|
UPDATE
|
|
nouns
|
|
SET masc = coalesce(replace(replace(replace(trim(json(words) -> '$.masc.singular', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
mascPl = coalesce(replace(replace(replace(trim(json(words) -> '$.masc.plural', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
fem = coalesce(replace(replace(replace(trim(json(words) -> '$.fem.singular', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
femPl = coalesce(replace(replace(replace(trim(json(words) -> '$.fem.plural', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
neutr = coalesce(replace(replace(replace(trim(json(words) -> '$.neutr.singular', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
neutrPl = coalesce(replace(replace(replace(trim(json(words) -> '$.neutr.plural', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
nb = coalesce(replace(replace(replace(trim(json(words) -> '$.nb.singular', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), ''),
|
|
nbPl = coalesce(replace(replace(replace(trim(json(words) -> '$.nb.plural', '["]'),
|
|
'","', '|'), '\"', '"'), '\\', '\'), '')
|
|
WHERE 1 = 1;
|
|
|
|
ALTER TABLE nouns
|
|
DROP COLUMN words;
|