Jay Brown
c10fa98d0a
Restore original migrations (001-120) to fix schema_migrations version mismatch * Restore original migrations (001-120) to fix schema_migrations version mismatch The previous migration collapse broke deployed databases because: - Dev database was at version 120 (the real remove_text_extraction migration) - Main had collapsed migrations (001-012) + stub files (119_stub, 120_stub) - The migrate tool couldn't find version 119/120 with matching content This commit restores the original 27 migrations (versions 001-120) from before the collapse, ensuring backward compatibility with existing databases.
80 lines
2.4 KiB
PL/PgSQL
80 lines
2.4 KiB
PL/PgSQL
CREATE TYPE queryType AS ENUM ('context_full', 'json_extractor');
|
|
|
|
CREATE TABLE queries (
|
|
queryId uuid primary key DEFAULT uuid_generate_v7(),
|
|
queryType queryType not null
|
|
);
|
|
|
|
CREATE TABLE queryVersions (
|
|
queryId uuid not null,
|
|
versionId int not null,
|
|
addedAt timestamp not null default current_timestamp,
|
|
primary key (versionId, queryId),
|
|
foreign key (queryId) references queries(queryId)
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION setQueryVersionNumber()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
SELECT COALESCE(MAX(versionId), 0) + 1
|
|
INTO NEW.versionId
|
|
FROM queryVersions
|
|
WHERE queryId = NEW.queryId;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER setQueryVersionNumberTrigger
|
|
BEFORE INSERT ON queryVersions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION setQueryVersionNumber();
|
|
|
|
CREATE TABLE queryActiveVersions (
|
|
activeVersionEntryId uuid primary key DEFAULT uuid_generate_v7(),
|
|
queryId uuid not null,
|
|
versionId int not null,
|
|
foreign key (queryId, versionId) references queryVersions(queryId, versionId)
|
|
);
|
|
|
|
CREATE TABLE requiredQueries (
|
|
requiredQueryEntryId uuid primary key DEFAULT uuid_generate_v7(),
|
|
queryId uuid not null,
|
|
requiredQueryId uuid not null,
|
|
addedVersion int not null,
|
|
removedVersion int,
|
|
foreign key (queryId) references queries(queryId),
|
|
foreign key (requiredQueryId) references queries(queryId),
|
|
foreign key (queryId, addedVersion) references queryVersions(queryId, versionId),
|
|
foreign key (queryId, removedVersion) references queryVersions(queryId, versionId),
|
|
unique (queryId, requiredQueryId, removedVersion)
|
|
);
|
|
|
|
CREATE TABLE queryConfigs (
|
|
configId uuid primary key DEFAULT uuid_generate_v7(),
|
|
queryId uuid not null,
|
|
config jsonb not null,
|
|
addedVersion int not null,
|
|
removedVersion int,
|
|
foreign key (queryId) references queries(queryId),
|
|
foreign key (queryId, addedVersion) references queryVersions(queryId, versionId),
|
|
foreign key (queryId, removedVersion) references queryVersions(queryId, versionId),
|
|
unique (queryId, removedVersion)
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION removeQueryConfig()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE queryConfigs
|
|
SET removedVersion = NEW.addedVersion
|
|
WHERE queryId = NEW.queryId and removedVersion is null;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER removeQueryConfigTrigger
|
|
BEFORE INSERT ON queryConfigs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION removeQueryConfig();
|