/*
fix_trailing_whitespace.sql  Jerry Bryan   6/10/2023

This script can be used to remove trailing whitespace
in CitationTable.ActualText and SourceTable.ActualText in RM7.
It can also be used as a model for removing trailing whitespace
in any TEXT table (or any BLOB table that's actually TEXT) in
RM7 or RM8 or RM9.

To keep things simple, running the script only deletes the
rightmost trailing whitespace character. The easiest way to
run it therefore is just to keeping running it over and over
again until no further records are updated. The update for
SourceTable.ActualText is commented out so that the results from
running the update for CitationTable.Actual text can be seen. Running
the update for only one table at a time is the only way to tell
when all the trailing whitespace has been removed.

If you change this script for any column other than ActualText in
RM7, be sure to make the CAST AS BLOB or CAST AS TEXT match the
actual BLOB or TEXT status of the table being updated. In RM7,
CitationTable.ActualText is a BLOB and SourceTable.ActualText
is a TEXT.

Trailing whitespace characters that are removed are tab - CHAR(9),
line feeld - CHAR(10), carriage return - CHAR(13),
and blank - CHAR(32). The CHAR function seems to require the codes to
be in decimal rather than the more familiar hexadecimal for these codes.

The motivation for this script is to be able do a GEDCOM export
and import and then to be able to run a comparison between
the databases - table by table, row by row, and column by
column. The problem is that GEDCOM export/import drops the trailing
whitespace which makes the comparison very difficult. When I need
trailing whitespace that is not deleted in this manner, I follow it
with a dummy private note, viz. {}. The dummy private note prevents
any desirable trailing whitespace from being deleted by GEDCOM
export/import and also from being deleted by this script.
*/


UPDATE CitationTable  
SET ActualText = CAST(SUBSTR(ActualText,1,LENGTH(ActualText)-1) AS BLOB)                      
WHERE SUBSTR(ActualText,-1) LIKE '%' || CHAR(9) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(10) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(13) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(32) || '%';

/*
UPDATE SourceTable  
SET ActualText = CAST(SUBSTR(ActualText,1,LENGTH(ActualText)-1) AS TEXT)                      
WHERE SUBSTR(ActualText,-1) LIKE '%' || CHAR(9) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(10) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(13) || '%'
   OR SUBSTR(ActualText,-1) LIKE '%' || CHAR(32) || '%';
*/