Reveal Review Publication

Before you kick off processing using the data imported into the SQL database, you need to run scripts to prepare the data and make it compatible with the formats Story Engine requires.

The following scripts provide a sample SQL script to update the data:

-- Find the database where the DAT file is imported

USE {database}

-- check the data imported

 SELECT * FROM Documents

-- 1. add an id field if it does not exist  

-- always a good practice to add it as primary key  

ALTER TABLE Documents ADD Id INT IDENTITY PRIMARY KEY

-- 2. check controlnumber, groupidentifier and attachments count  

-- if beg_bates exists, use beg_bates as the controlnumber  

-- if beg_attach exists, use beg_attach as the groupidentifier  

-- notice the groupidentifier for the parent must be equal to its controlnumber  

-- update the attachments count using the groupidentifier  

ALTER TABLE Documents ADD nexlp_attachcounts INT  

UPDATE a SET a.nexlp_attachcounts = b.cnt - 1  

FROM Documents a inner join (  

SELECT beg_attach, count(*) AS cnt FROM documents GROUP BY beg_attach

 ) b ON a.beg_attach = b.beg_attach

WHERE a.beg_bates = b.beg_attach

-- 3. check custodian, if custodian does not exists, give it a placeholder value  

SELECT COUNT(*) FROM Documents WHERE LEN(custodian) = 0 OR custodian IS NULL  

ALTER TABLE Documents ADD custodian VARCHAR(200)

-- if custodian names have unicode characters, use NVARCHAR(200)  

UPDATE Documents SET custodian = 'custodian' WHERE LEN(custodian) = 0 OR custodian IS NULL

-- 4. check MD5SUM, if it is empty, give it a unique value, for example, the control number

 SELECT COUNT(*) FROM Documents WHERE LEN(MD5SUM) = 0 or MD5SUM IS NULL

 UPDATE Documents SET MD5SUM = controlnumber WHERE LEN(MD5SUM) = 0 OR md5sum IS NULL

-- 5. check datatime, add nexlpdatetime field  

ALTER TABLE Documents ADD nexlpdatetime DATETIME  

UPDATE documents SET nexlpdatetime = CAST(REPLACE(primarydatetime, '.', ':') AS DATETIME)

-- the next step will depend on the data available in the DAT file,

-- if there are multiple date fields, try to check each of them (recommend precedence: DateSent, DateReceived, DateLastModified, DateCreated)  

-- if the Date and Time are stored in two separate fields, join date and time fields into one  

-- here is an example:  

UPDATE Documents SET nexlpdatetime = CAST(DATESENT + ' ' + TIMESENT AS DATETIME) WHERE LEN(DATESENT) > 0  

UPDATE Documents SET nexlpdatetime = CAST(DATERECEIVED + ' ' + TIMERECEIVED AS DATETIME) WHERE LEN(DATERECEIVED) > 0 AND nexlpdatetime IS NULL  

UPDATE Documents SET nexlpdatetime = CAST(DATEMOD + ' ' + TIMEMOD AS DATETIME) WHERE LEN(DATEMOD) > 0 AND nexlpdatetime IS NULL  

UPDATE Documents SET nexlpdatetime = CAST(DATECREATED + ' ' + TIMECREATED AS DATETIME) WHERE LEN(DATECREATED) > 0 AND nexlpdatetime IS NULL

-- 6. add nexlptimezone, typicall options are 'EST', 'CST', 'PST', 'GMT'  

ALTER TABLE Documents ADD nexlptimezone VARCHAR(20)  

UPDATE Documents SET nexlptimezone = 'EST'

-- 7. add processing status  

ALTER TABLE Documents ADD ProcessingStatus INT  

UPDATE Documents SET ProcessingStatus = 0  

-- to improve performance, also add a SQL index  

CREATE INDEX idx_processing ON Documents (ProcessingStatus ASC)

-- 8. update text file path  

-- provide the absolute path for the text files, UNC path recommended  

-- for example, the query below updates original path from "text\\CDPHP_0001.txt" to "\\relativity\client\Text\CDPHP_00001.txt  

UPDATE Documents SET text_path = REPLACE(text_path, 'text\\', '\\relativity\client\Text\')

The following table lists recommended fields for Story Engine processing purposes: