29 lines
926 B
SQL
29 lines
926 B
SQL
-- Add project variant and reset codes from project names
|
|
|
|
ALTER TABLE qt_projects
|
|
ADD COLUMN variant VARCHAR(100) NOT NULL DEFAULT '' AFTER code;
|
|
|
|
-- Drop legacy unique index on code to allow duplicate codes
|
|
DROP INDEX IF EXISTS idx_qt_projects_code ON qt_projects;
|
|
DROP INDEX IF EXISTS idx_qt_projects_code_variant ON qt_projects;
|
|
|
|
-- Reset code from name and clear variant
|
|
UPDATE qt_projects
|
|
SET code = LEFT(TRIM(COALESCE(name, '')), 100),
|
|
variant = '';
|
|
|
|
-- De-duplicate by assigning variant numbers: -2, -3...
|
|
UPDATE qt_projects p
|
|
JOIN (
|
|
SELECT p1.id,
|
|
p1.code,
|
|
(SELECT COUNT(*)
|
|
FROM qt_projects p2
|
|
WHERE p2.code = p1.code AND p2.id <= p1.id) AS rn
|
|
FROM qt_projects p1
|
|
) r ON r.id = p.id
|
|
SET p.code = r.code,
|
|
p.variant = CASE WHEN r.rn = 1 THEN '' ELSE CONCAT('-', r.rn) END;
|
|
|
|
CREATE UNIQUE INDEX idx_qt_projects_code_variant ON qt_projects(code, variant);
|