Jay Brown
105 lines
3.5 KiB
PL/PgSQL
105 lines
3.5 KiB
PL/PgSQL
-- Rollback migration to restore text extraction functionality
|
|
-- NOTE: This recreates text extraction tables and views
|
|
|
|
-- First drop the simplified fullActiveCollectors view
|
|
DROP VIEW IF EXISTS fullActiveCollectors;
|
|
|
|
-- Recreate documentTextExtractions table
|
|
CREATE TABLE documentTextExtractions (
|
|
id uuid primary key DEFAULT uuid_generate_v7(),
|
|
cleanId uuid not null,
|
|
bucket text not null,
|
|
key text not null,
|
|
hash text not null,
|
|
createdAt timestamp not null,
|
|
part unsignedsmallint not null,
|
|
foreign key (cleanId) references documentCleans(id)
|
|
);
|
|
|
|
-- Recreate documentTextExtractionEntries table
|
|
CREATE TABLE documentTextExtractionEntries (
|
|
id uuid primary key DEFAULT uuid_generate_v7(),
|
|
textId uuid not null,
|
|
version bigint not null,
|
|
foreign key (textId) references documentTextExtractions(id)
|
|
);
|
|
|
|
-- Recreate collectorMinTextVersions table
|
|
CREATE TABLE collectorMinTextVersions (
|
|
id uuid primary key DEFAULT uuid_generate_v7(),
|
|
clientId varchar(255) not null,
|
|
versionId bigint not null,
|
|
addedVersion int not null,
|
|
removedVersion int,
|
|
foreign key (clientId, addedVersion) references collectorVersions(clientId, id),
|
|
foreign key (clientId, removedVersion) references collectorVersions(clientId, id),
|
|
foreign key (clientId) references clients(clientId)
|
|
);
|
|
|
|
-- Recreate removeMinTextVersion function
|
|
CREATE OR REPLACE FUNCTION removeMinTextVersion()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE collectorMinTextVersions
|
|
SET removedVersion = NEW.addedVersion
|
|
WHERE clientId = NEW.clientId and removedVersion is null;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Recreate trigger for collectorMinTextVersions
|
|
CREATE TRIGGER removeMinTextVersionTrigger
|
|
BEFORE INSERT ON collectorMinTextVersions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION removeMinTextVersion();
|
|
|
|
-- Recreate currentCollectorMinTextVersions view
|
|
CREATE VIEW currentCollectorMinTextVersions as
|
|
SELECT DISTINCT
|
|
av.clientId,
|
|
coalesce(ctv.versionId, 0) as minTextVersion
|
|
FROM collectorCurrentActiveVersions as av
|
|
LEFT JOIN collectorMinTextVersions AS ctv ON av.clientId = ctv.clientId
|
|
and isInVersion(av.activeVersion, ctv.addedVersion, ctv.removedVersion);
|
|
|
|
-- Recreate fullActiveCollectors view with minTextVersion
|
|
CREATE VIEW fullActiveCollectors AS
|
|
SELECT DISTINCT av.clientId,
|
|
ccv.minCleanVersion, ctv.minTextVersion,
|
|
av.activeVersion, lv.latestVersion
|
|
FROM collectorCurrentActiveVersions as av
|
|
JOIN collectorLatestVersions as lv on lv.clientId = av.clientId
|
|
JOIN currentCollectorMinCleanVersions AS ccv ON av.clientId = ccv.clientId
|
|
JOIN currentCollectorMinTextVersions AS ctv ON av.clientId = ctv.clientId;
|
|
|
|
-- Recreate currentTextEntries view
|
|
CREATE VIEW currentTextEntries as
|
|
WITH RankedExtractions AS (
|
|
SELECT
|
|
extract.id,
|
|
cc.documentId,
|
|
extract.bucket,
|
|
extract.key,
|
|
extract.hash,
|
|
extract.cleanId,
|
|
dtee.version as extractionVersion,
|
|
ROW_NUMBER() OVER (PARTITION BY cc.documentId ORDER BY dtee.id DESC) as row_num
|
|
FROM documents d
|
|
JOIN currentCleanEntries cc on cc.documentId = d.id
|
|
JOIN currentCollectorMinTextVersions ctv ON ctv.clientId = d.clientId
|
|
JOIN documentTextExtractions extract ON extract.cleanId = cc.id
|
|
JOIN documentTextExtractionEntries dtee on dtee.textId = extract.id
|
|
AND dtee.version >= ctv.minTextVersion
|
|
)
|
|
SELECT
|
|
id,
|
|
documentId,
|
|
bucket,
|
|
key,
|
|
hash,
|
|
cleanId,
|
|
extractionVersion
|
|
FROM RankedExtractions
|
|
WHERE row_num = 1;
|