arrow_back Binære filer i databasefelt beregnet på tekst

Question

Vi opplever av og til at binære filer (Word-dokumenter, PDF-filer ++) blir lagret i tekstfelter i database det skal gjøres uttrekk av. Dette resulterer ofte i uleselige filer etter at databasen har blitt konvertert til f.eks. SIARD-format eller dersom databasen er flyttet til en annen server.

Noen av felttypene som kan skape problemer er:

• SQL Server: NTEXT, TEXT, NVARCHAR, VARCHAR
• Oracle: LONG, CLOB, NCLOB

Hva er beste praksis for å redde binære dokumenter lagret i tekst-felt?

Answer 1

Vi endte opp med å korrigere databasen i SQL Server før nytt uttrekk ble tatt. Datafeltet som hadde felttype ntext ble først konvertert til varchar(max) og deretter til varbinary(max) før nytt SIARD-uttrekk ble tatt. På den måten ble både RTF-filer, tekstfiler og Word-dokumenter håndtert korrekt ved SIARD-eksport.

ALTER TABLE {tabellnavn}
ADD konvertering varbinary(max)
GO

UPDATE table {tabellnavn}
SET konvertering = cast(cast {ntextfelt} as varchar(max)) as varbinary(max)
GO

EXEC sp_rename '{schema}.{tabellnavn}.konvertering' , '{ntextfelt}', 'COLUMN';
GO

Comments

Syntaks over virket ikke på vår MSSQL 2014 server.
Se eget svar fra meg som virket på vår server.

Answer 2

Har ikke problemer med dette lenger i PWCode etter siste endringer i koden. Alle slike felt blir eksportert som filer på disk og opprinnelig felt blir et tekstfelt med filnavn for fil på disk. Disse filene blir i neste omgang sjekket med Tika for reell filtype og konvertert til arkivformat. Alt dette skjer automatisk hvis kolonnen har minst ett tilfelle av tekst over 4000 tegn (maks grense før en beveger seg over i potensielt mer ustandard datatyper ift interoperabilitet mellom databasemotorer). Kan i teorien være noen som beholdes som binære da men veldig usannsynlig.
Hvis en absolutt vil ha siard-uttrekk kan en bruke den automatisk genererte koden (DDL + import) for å laste opp til ny database og så gjøre et siard-uttrekk hvor en ikke har disse problemene. Mange andre potensielle problemer med siard er da også eliminert. Skrev en del om dette i rapporten vi slapp nylig.

Comments

Hei Morten, den rapporten du henviser til, er den publisert noe sted?

Answer 3

Det problemet som diskuteres her gjelder f.eks. databasen som programmet Visma Flyktning og Voksenopplæring (FlyVo) bygger på. Ved IKA Rogaland fikk vi tidlig i 2021 et siard-uttrekk fra FlyVo som viste seg å ha dette problemet knyttet til tabellen 'rDokument' og feltet 'DokumentTekstRTF'. Etter at problemet var påvist i forbindelse med testing ved IKA Rogaland ble det bestemt at det skulle lages et nytt siard-uttrekk etter at følgende korrigerende operasjoner var gjort av Sandnes kommune.

1)Sandnes kommune har et eget teknisk miljø hvor de produserer uttrekk fra kommunens systemer som inneholder bevaringsverdig dokumentasjon. Sentrale applikasjoner i dette uttrekksmiljøet er databaseplattformen MSSQL med tilhørende programvare og SpectralCore FullConvert for produksjon av siard-uttrekk. Både IKT-avdelingen og arkivtjenesten i Sandnes kommune har tilgang til dette miljøet.
2) En kopi av FlyVo ble lagt over på dette uttrekksmiljøet.
3) På denne databasen ble så laget en kopi av tabellen 'rDokument' som fikk navnet 'rDokument2'.
4) I tabellen 'rDokument2' ble formatet på feltet 'DokumentTekstRTF' endret til: varbinary
5) Deretter ble tabellen 'rDokument2' populert med data fra tabellen 'rDokument'.
6) Neste trinn var sletting av den originale tabellen 'rDokument'.
7) Så ble navnet på tabellen 'rDokument2' endret til 'rDokument'.

Disse operasjonen ble utført og dokumentert av Helge Monsen ved IKT-avdelingen i Sandnes kommune. Han kjørte følgende SQL kommandoer i det nevnte MSSQL-miljøet:

USE [mdflykt7]
GO
--lager ny tabell
CREATE TABLE [dbo].[rDokument2](
[Dato] [smalldatetime] NULL,
[DokId] [int] IDENTITY(1,1) NOT NULL,
[DokumentTekstRTF] [varbinary](max) NULL,
[Gruppe] [nvarchar](10) NULL,
[UserId] [nvarchar](100) NULL,
[DokDir] [nvarchar](255) NULL,
[DokNavn] [nvarchar](50) NULL,
[DokPass] [nvarchar](50) NULL,
[DokEkst] [nvarchar](5) NULL,
[DokumentTekst] [ntext] NULL,
[UtsjekketAv] [nvarchar](100) NULL,
[UtsjekketDato] [datetime] NULL
)
--kopierer data fra den gamle
INSERT INTO [dbo].[rDokument2]

       ([Dato]
       ,[DokumentTekstRTF]
       ,[Gruppe]
       ,[UserId]
       ,[DokDir]
       ,[DokNavn]
       ,[DokPass]
       ,[DokEkst]
       ,[DokumentTekst]
       ,[UtsjekketAv]
       ,[UtsjekketDato])

select

Dato,
cast(cast(rDokument.DokumentTekstRTF as varchar(max)) as varbinary(max)),

Gruppe, UserId,DokDir, DokNavn, DokPass, DokEkst, DokumentTekst, UtsjekketAv, UtsjekketDato
FROM [mdflykt7].[dbo].[rDokument]

USE [mdflykt7]
GO

--finner avhengigheter

SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc

  ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN
sys.tables t

  ON t.OBJECT_ID = fc.referenced_object_id

WHERE
OBJECT_NAME (f.referenced_object_id) ='rDokument'

-fjerner avhengigheter

ALTER TABLE [dbo].[rBrevJournal]
DROP CONSTRAINT [FK_rBrevJournal_rDokument]
GO

ALTER TABLE [dbo].[rDokument]
DROP CONSTRAINT PK_rDokument

--sletter den gamle tabellen
DROP TABLE [dbo].[rDokument]
GO
-gir den nye det samme snavn som den gamle
EXEC sp_rename 'rDokument2', 'rDokument';

8) Som et siste trinn i prosessen produserte Arkivtjenesten i Sandnes kommune et nytt siard-uttrekk som ble mottatt ved IKA Rogaland via den sikre VPN-kanalen KDRSFileSender den 31. august 2021.

Answer 4

Følgende transformasjon fra ntext CLOB tekstfelt til varbinary(mac) BLOB binært felt virker på MSSQL 2014 Server.
- mySchema: erstatt med ditt skjemanavn
- myTable: erstatt med ditt tabellnavn
- myTableColumn: erstatt med ditt kolonne/feltnavn som har ntext CLOB, men må transformeres til varbinary(max) BLOB fordi binære filer er lagret i tekstfeltet

-- For å konvertere til ntext til varbinary(max) i SQL-server må vi gå veien om nvarchar(max)
-- Dette for at arkivverket skal klare å lese ut alle dokument

-- 1) Oppretter en kolonne for konvertere til
ALTER TABLE [myTable]
ADD konvertering varbinary(max)
GO

-- 2) konverterer innholdet i myTableColum til varbinary(max) i konvertering kolonnen
UPDATE rDokument SET [myDatabase].[mySchema].[myTable].konvertering = cast(cast (myTable.myTableColumn as nvarchar(max)) as varbinary(max))
GO

-- 3) rename myTableColumn til old
EXEC sp_rename '[mySchema].[myTable.myTableColumn' , 'myTableColumn_old', 'COLUMN';
GO
-- 4) rename konvertering kolonnen til myTableColumn
EXEC sp_rename '[mySchema].[myTable].konvertering' , 'myTableColumn', 'COLUMN';
GO

-- 5) Slette original kolonne myTableColumn_old fordi vi ikke ønsker denne med i SIARD-uttrekket

Answer 5

Ved IKA Rogaland oppdaget vi i forbindelse med en innsynsak at at skriptet som ble laget i Sandnes kommune (se ovenfor) skapte en ny feil i siard-uttrekket. Det opprinnelige skriptet som Sandnes kommune laget førte til at vi fikk ut dokumentene uskadd slik at de lot seg konvertere til arkivformat ved hjel av programmet Documaster Decom og LibreOffice. Det var også kobling mellom metadata og dokument i uttrekket, men pga. følgende setningen

[DokId] [int] IDENTITY(1,1) NOT NULL,

i skriptet ble feil dokument koblet til metadata. Det virket som om dokumentene var tilfeldig koblet til metadata pga. denne feilen i CREATE TABLE kommandoen.

Ved IKA Rogaland gjorde vi en del grundige undersøkelser omkring feilen som det første skriptet skapte, spesielt i samarbeid med IKT-tjenesten og arkivtjenesten i Hå kommune. Vi fant og dokumenterte feilen og hvordan den kunne rettes. Til slutt ble skriptet revidert og ved IKA Rogaland prøvde vi ut hele produksjonslinja på ei back-up file vi fikk fra Hå kommune. Denne back-up fila satt vi opp på en lokal MSSQL server ved IKA Rogaland. Vi kjørte det reviderte skriptet mot denne kopi databasen. Vi laget et nytt siard-uttrekk ved hjelp av SpectralCore fullConvert (SIP). Vi laget ny arkivpakke AIP) i Documaster Decom som inkluderte konvertering av tilhørende dokument til arkivformat. Den endelige siard-fila lastet vi så opp på en MariaDB (MySQL) plattform. Til slutt koblet vi programmet DataBase Preservation Toolkit (DBPTK) opp mot MariaDB versjonen av FlyVo og laget et nytt siard-uttrekk som skal brukes til innsyn i dette elektroniske arkivmaterialet. På denne innsynsløsingen kontrollerte vi så med stikkprøver at riktig dokument var koblet til metadata, og dette stemte. Vi testet ut på 8 klienter og koblingen mellom dokument og metadata knyttet til person stemte.

Det reviderte skriptet er slik:

USE [VFlyktning]
GO

--lager ny tabell

CREATE TABLE [dbo].[rDokument2](
[Dato] [smalldatetime] NULL,
[DokId] [int] NULL,
[DokumentTekstRTF] [varbinary](max) NULL,
[Gruppe] [nvarchar](10) NULL,
[UserId] [nvarchar](100) NULL,
[DokDir] [nvarchar](255) NULL,
[DokNavn] [nvarchar](50) NULL,
[DokPass] [nvarchar](50) NULL,
[DokEkst] [nvarchar](5) NULL,
[DokumentTekst] [ntext] NULL,
[UtsjekketAv] [nvarchar](100) NULL,
[UtsjekketDato] [datetime] NULL
)

--kopierer data fra den gamle

INSERT INTO [dbo].[rDokument2]
([Dato],
[DokId],
[DokumentTekstRTF],
[Gruppe],
[UserId],
[DokDir],
[DokNavn],
[DokPass],
[DokEkst],
[DokumentTekst],
[UtsjekketAv],
[UtsjekketDato])

select

Dato, DokId,

cast(cast(rDokument.DokumentTekstRTF as varchar(max)) as varbinary(max)),

Gruppe, UserId, DokDir, DokNavn, DokPass, DokEkst, DokumentTekst, UtsjekketAv, UtsjekketDato
FROM [VFlyktning].[dbo].[rDokument]

USE [VFlyktning]
GO

-- Finner avhengigheter

SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc

  ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN
sys.tables t

  ON t.OBJECT_ID = fc.referenced_object_id

WHERE
OBJECT_NAME (f.referenced_object_id) ='rDokument'

-- Fjerner avhengigheter og dermed relasjoner på original database

ALTER TABLE [dbo].[rBrevJournal]
DROP CONSTRAINT [FK_rBrevJournal_rDokument]
GO

ALTER TABLE [dbo].[rDokument]
DROP CONSTRAINT PK_rDokument

-- Sletter den gamle tabellen

DROP TABLE [dbo].[rDokument]
GO

-- Gir den nye det samme snavn som den gamle

EXEC sp_rename 'rDokument2', 'rDokument';

Comments

Ved mottak av Visma Flyktning og Voksenopplæring (FlyVo) fra en annen kommune (Time) fant vi en annen tabell med samme problem, ‘mm_rDokument’ med feltet DocumentTekstRTF. Denne tabellen behandles med samme metodikk som over. Merk at feltene i denne tabellen er forskjellige fra feltene i ‘rDokument’.