-- WebTags-MakeFromAncestryComments.sql
/* 
2012-12-10 Tom Holden ve3meo

Inspects for Citation Comments beginning with "http://" and generates 
a corresponding WebTag. GEDCOMs downloaded from Ancestry.com have this 
characteristic, typically followed by two linefeed-carriage returns that 
are stripped by this query.

Due to limitations of SQLite, should the Comment contain more than the trailing
white space, the complete Comment is placed in the URL field and will 
almost certainly fail to open the web page.
*/
-- Make Citation webtags for Citation Comments starting with "http://"
INSERT OR REPLACE INTO URLTable
SELECT
  (SELECT LinkID FROM URLTable WHERE OwnerType=4 AND OwnerID=Cit.CitationID) AS LinkID, 
  4 AS OwnerType, 
  Cit.CitationID AS OwnerID, 
  0 AS LinkType,
  Src.Name AS Name,
  SUBSTR(Cit.Comments,1, LENGTH(Cit.Comments)-4) AS URL, -- strip four bytes off the end, typ two pairs of CR-LF
  Cit.ActualText AS Note 
  FROM CitationTable AS Cit 
  INNER JOIN SourceTable AS Src
  USING (SourceID)
  
WHERE Cit.Comments LIKE 'http://%'
;