-- Import_RM8_to_RM7-V3.sql /* 2022-04-16 Tom Holden ve3meo Imports a RM8 database into a RM7 database except for ConfigTable, Folders or ResearchItems Rev 2022-04-18 imports basic Tasks into ResearchTable Rev 2022-04-20 imports TreeShare and FamilySearch linkages Rev 2022-04-22 imports Tasks & Folders to To-Do's and Logs more or less fully, given changes in database structuer Rev 2022-06-12 import ConfigTable - so far appears compatible V3 Rev 2022-06-19 corrected failure to convert reused Citations to individual Citations - plus related Media tags, Web tags, TreeShare links Rev 2022-06-23 corrected typos throwing media and web tags off EDIT 3 STATEMENTS FLAGGED BY **** FOR YOUR RM8 FILE AND MEDIA PATHS */ SELECT 'Import_RM8_to_RM7 Script started. Very large databases may take a couple of minutes to complete...' ; REINDEX ; -- ****REVISE THE NEXT LINE FOR THE FULL PATH TO SOURCE RM8 DATABASE**** ATTACH DATABASE "C:\Users\Tom\Documents\FamilyTree\RM8\Cornelius R Krieghoff (Photographer).rmtree" AS RM8db ; BEGIN Transaction ; DELETE FROM ConfigTable ; INSERT OR REPLACE INTO ConfigTable SELECT RecID ,RecType ,Title ,DataRec FROM RM8db.ConfigTable ; DELETE FROM PersonTable ; INSERT OR REPLACE INTO PersonTable SELECT PersonID ,UniqueID ,Sex ,UTCModDate AS EditDate ,ParentID ,SpouseID ,Color -- have to map 28 color values to 15 yet ,Relate1 ,Relate2 ,Flags ,Living ,IsPrivate ,Proof ,0 AS Bookmark ,Note -- should this be cast as blob? FROM RM8db.PersonTable ; DELETE FROM NameTable ; INSERT OR REPLACE INTO NameTable SELECT NameID ,OwnerID ,Surname ,Given ,Prefix ,Suffix ,Nickname ,NameType ,Date ,SortDate ,IsPrimary ,IsPrivate ,Proof ,UTCModDate AS EditDate ,Sentence ,Note ,BirthYear ,DeathYear FROM RM8db.NameTable ; DELETE FROM FamilyTable ; INSERT OR REPLACE INTO FamilyTable SELECT FamilyID ,FatherID ,MotherID ,ChildID ,HusbOrder ,WifeOrder ,IsPrivate ,Proof ,SpouseLabel ,FatherLabel ,MotherLabel ,Note FROM RM8db.FamilyTable ; DELETE FROM ChildTable ; INSERT OR REPLACE INTO ChildTable SELECT RecID ,ChildID ,FamilyID ,RelFather ,RelMother ,ChildOrder ,IsPrivate ,ProofFather ,ProofMother ,Note FROM RM8db.ChildTable ; DELETE FROM FactTypeTable ; INSERT OR REPLACE INTO FactTypeTable SELECT FactTypeID ,OwnerType ,Name ,Abbrev ,GedcomTag ,UseValue ,UseDate ,UsePlace ,Sentence ,Flags FROM RM8db.FactTypeTable ; DELETE FROM EventTable ; INSERT OR REPLACE INTO EventTable SELECT EventID ,EventType ,OwnerType ,OwnerID ,FamilyID ,PlaceID ,SiteID ,Date ,SortDate ,IsPrimary ,IsPrivate ,Proof ,Status ,UTCModDate AS EditDate ,Sentence ,Details ,Note FROM RM8db.EventTable ; DELETE FROM GroupTable ; INSERT OR REPLACE INTO GroupTable SELECT RecID ,GroupID-1000 ,StartID ,EndID FROM RM8db.GroupTable ; DELETE FROM LabelTable ; INSERT OR REPLACE INTO LabelTable SELECT TagID AS LabelID ,TagType AS LabelType ,TagValue-1000 AS LabelValue ,TagName AS LabelName ,Description FROM RM8db.TagTable WHERE TagType=0 --Group Names only ; DELETE FROM PlaceTable ; INSERT OR REPLACE INTO PlaceTable SELECT PlaceID ,PlaceType ,Name ,Abbrev ,Normalized ,Latitude ,Longitude ,LatLongExact ,MasterID ,Note FROM RM8db.PlaceTable ; DELETE FROM MultiMediaTable ; INSERT OR REPLACE INTO MultiMediaTable SELECT MediaID ,MediaType ,MediaPath ,MediaFile ,URL ,Thumbnail ,Caption ,RefNumber ,Date ,SortDate ,Description FROM RM8db.MultiMediaTable ; DELETE FROM SourceTemplateTable WHERE TemplateID>9999 ; INSERT OR REPLACE INTO SourceTemplateTable SELECT TemplateID ,Name ,Description ,Favorite ,Category ,Footnote ,ShortFootnote ,Bibliography ,FieldDefs FROM RM8db.SourceTemplateTable WHERE RM8db.SourceTemplateTable.TemplateID>9999 ; DELETE FROM SourceTable ; INSERT OR REPLACE INTO SourceTable SELECT SourceID ,Name ,RefNumber ,ActualText ,Comments ,IsPrivate ,TemplateID ,Fields FROM RM8db.SourceTable ; -- Generate a temp table to create individual citations from all RM8uses (Citation Links) rev 2022-06-19 DROP TABLE IF EXISTS NewCitationTable ; CREATE TEMP TABLE NewCitationTable AS SELECT Null AS RM7CitationID ,CitationID AS RM8CitationID ,OwnerType ,SourceID ,OwnerID ,Quality ,IsPrivate ,Comments ,ActualText ,RefNumber ,Flags ,Fields FROM RM8db.CitationTable JOIN RM8db.CitationLinkTable USING(CitationID) ORDER BY CitationID ; -- Assign new CitationIDs for RM7 rev 2022-06-19 UPDATE NewCitationTable SET RM7CitationID=ROWID ; -- Transfer new Citations to RM7 rev 2022-06-19 DELETE FROM CitationTable ; INSERT OR REPLACE INTO CitationTable SELECT RM7CitationID ,OwnerType ,SourceID ,OwnerID ,Quality ,IsPrivate ,Comments ,ActualText ,RefNumber ,Flags ,Fields FROM NewCitationTable ; DELETE FROM MediaLinkTable ; --Tags for all items other than Citations rev 2022-06-19 INSERT OR REPLACE INTO MediaLinkTable SELECT LinkID ,MediaID ,OwnerType ,OwnerID ,IsPrimary ,Include1 ,Include2 ,Include3 ,Include4 ,SortOrder ,RectLeft ,RectTop ,RectRight ,RectBottom ,Comments AS Note ,NULL AS Caption ,NULL AS RefNumber ,NULL AS Date ,NULL AS SortDate ,NULL AS Description FROM RM8db.MediaLinkTable WHERE OwnerType<>4 -- except links to Citations rev 2022-06-19 ; --Tags for Citations including reused now converted to individual rev 2022-06-19 INSERT INTO MediaLinkTable SELECT Null AS LinkID ,MediaID ,4 AS OwnerType ,RM7CitationID AS OwnerID ,IsPrimary ,Include1 ,Include2 ,Include3 ,Include4 ,SortOrder ,RectLeft ,RectTop ,RectRight ,RectBottom ,RM8ML.Comments AS Note ,NULL AS Caption ,NULL AS RefNumber ,NULL AS Date ,NULL AS SortDate ,NULL AS Description FROM RM8db.MediaLinkTable RM8ML JOIN NewCitationTable RM7NC ON RM8ML.OwnerID = RM7NC.RM8CitationID WHERE RM8ML.OwnerType=4 -- only links to Citations ; DELETE FROM RoleTable ; INSERT OR REPLACE INTO RoleTable SELECT RoleID ,RoleName ,EventType ,RoleType ,Sentence FROM RM8db.RoleTable ; DELETE FROM WitnessTable ; INSERT OR REPLACE INTO WitnessTable SELECT WitnessID ,EventID ,PersonID ,WitnessOrder ,Role ,Sentence ,Note ,Given ,Surname ,Prefix ,Suffix FROM RM8db.WitnessTable ; DELETE FROM URLTable ; INSERT OR REPLACE INTO URLTable SELECT LinkID ,OwnerType ,OwnerID ,LinkType ,Name ,URL ,Note FROM RM8db.URLTable WHERE OwnerType<>4 -- except links to Citations rev 2022-06-19 ; -- WebTags for individual Citations generated fro RM8 Citation Links rev 2022-06-19 INSERT INTO URLTable SELECT Null AS LinkID ,4 AS OwnerType ,RM7CitationID AS OwnerID ,LinkType ,Name ,URL ,Note FROM RM8db.URLTable RM8U JOIN NewCitationTable RM7NC ON RM8U.OwnerID = RM7NC.RM8CitationID WHERE RM8U.OwnerType=4 -- only links to Citations ; DELETE FROM AddressTable ; INSERT OR REPLACE INTO AddressTable SELECT AddressID ,AddressType ,Name ,Street1 ,Street2 ,City ,State ,Zip ,Country ,Phone1 ,Phone2 ,Fax ,Email ,URL ,Latitude ,Longitude ,Note FROM RM8db.AddressTable ; DELETE FROM AddressLinkTable ; INSERT OR REPLACE INTO AddressLinkTable SELECT LinkID ,OwnerType ,AddressID ,OwnerID ,AddressNum ,Details FROM RM8db.AddressLinkTable ; DELETE FROM ExclusionTable ; INSERT OR REPLACE INTO ExclusionTable SELECT RecID ,ExclusionType ,ID1 ,ID2 FROM RM8db.ExclusionTable ; DELETE FROM LinkAncestryTable ; INSERT OR REPLACE INTO LinkAncestryTable SELECT LinkID ,2 AS extSystem ,LinkType ,rmID ,anID AS extID ,Modified ,anVersion as extVersion ,anDate as extDate ,Status ,NULL AS Note FROM RM8db.AncestryTable WHERE LinkType<>4 -- except links to Citations rev 2022-06-19 ; -- TreeShare links for indiv RM7 Citations generated from RM8 uses rev 2022-06-19 INSERT INTO LinkAncestryTable SELECT Null AS LinkID ,2 AS extSystem ,4 AS LinkType ,rmID ,anID AS extID ,Modified ,anVersion as extVersion ,anDate as extDate ,Status ,NULL AS Note FROM RM8db.AncestryTable RM8 JOIN NewCitationTable RM7 ON RM8.rmID = RM7.RM8CitationID WHERE RM8.LinkType=4 -- only links to Citations ; DELETE FROM LinkTable ; INSERT OR REPLACE INTO LinkTable SELECT LinkID ,1 AS extSystem ,LinkType ,rmID ,fsID AS extID ,Modified ,fsVersion as extVersion ,fsDate as extDate ,Status ,NULL AS Note FROM RM8db.FamilySearchTable ; --RM8toRM7Research.sql DELETE FROM ResearchTable ; --Create basic tasks in ResearchTable INSERT OR REPLACE INTO ResearchTable SELECT TaskID ,MAX(0,TT.TaskType-2) AS TaskType -- 2=To-Do=>0, 3=Correspondence=>1, ignore 1=Log/Folder ,IFNULL(OwnerID,0) AS OwnerID ,IFNULL(OwnerType,8) AS OwnerType ,RefNumber ,Name ,Status-1 AS Status -- RM8 New and Cancelled have no equivalent status in RM7 ,Priority ,Date1 ,Date2 ,Date3 ,SortDate1 ,SortDate2 ,SortDate3 ,Filename ,Details FROM RM8db.TaskTable TT LEFT JOIN RM8db.TaskLinkTable TL USING(TaskID) -- WHERE TaskType IN (0,2) WHERE TL.OwnerType<>18 -- ignore Log/Folder for now -- AND TT.TaskType>1 ; --Convert Tasks for Events to Individual or Family owning the event UPDATE ResearchTable SET (OwnerID, OwnerType) = (SELECT E.OwnerID, E.OwnerType FROM EventTable E WHERE ResearchTable.OwnerID=E.EventID ) WHERE OwnerType=2 ; --Temp list of TagIDs for RM8 Folders --and the TaskID to be assigned for a RM7 Log in ResearchTable DROP TABLE IF EXISTS FolderLogID ; CREATE Temp Table IF NOT EXISTS FolderLogID AS SELECT (SELECT MAX(TaskID)FROM ResearchTable)+TG.TagID AS LogID ,TL.TaskID -- to infer owner of folder(log) from a task tied to it ,TL2.OwnerType -- ditto ,TL2.OwnerID -- ditto ,TG.* -- folder name and description FROM TagTable TG -- contains group names and folder names (TagTypes 0,1 resp) LEFT JOIN TaskLinkTable TL ON TG.TagID=TL.OwnerID AND TL.OwnerType=18 -- filter for records that link to TagTable LEFT JOIN TaskLinkTable TL2 USING(TaskID) WHERE TL2.OwnerType <>18 GROUP BY TG.TagID -- to get one record only for each folder --WHERE TL.OwnerType = 18 ; --Create Log records from TagNames INSERT OR REPLACE INTO ResearchTable SELECT FL.LogID AS TaskID ,2 AS TaskType --could we infer owner from the TaskLinkTable for the same TaskID? ,IFNULL(FL.OwnerID,0) AS OwnerID ,IFNULL(FL.OwnerType,8) AS OwnerType ,'' AS RefNumber ,FL.TagName AS Name ,NULL AS Status --Status-1 AS Status -- RM8 New and Cancelled have no equivalent status in RM7 ,NULL AS Priority ,'.' AS Date1 ,'.' AS Date2 ,'.' AS Date3 ,null AS SortDate1 ,null AS SortDate2 ,null AS SortDate3 ,'' AS Filename ,FL.Description AS Details FROM FolderLogID FL ; DELETE FROM ResearchItemTable ; --Create ResearchItemsTable records (items in Research Log) INSERT OR REPLACE INTO ResearchItemTable SELECT TL.LinkID AS ItemID --could be null, just using LinkID to help relate back to TLTable ,FL.LogID ,TT.Date1 AS Date ,TT.SortDate1 AS SortDate ,TT.RefNumber AS RefNumber ,'' AS Repository -- ,TT.Name ||CHAR(13)||CHAR(10)||TT.Details AS Goal ,TT.Details AS Goal ,'' AS Source ,TT.Results AS Result FROM RM8db.TaskLinkTable TL JOIN FolderLogID FL ON TL.OwnerID=FL.TagID AND TL.OwnerType=18 JOIN RM8db.TaskTable TT USING(TaskID) ; COMMIT Transaction ; DETACH DATABASE RM8db ; --****EDIT FOLLOWING STATEMENT TO SET THE ~ SYMBOL TO YOUR USER PATH**** UPDATE MultiMediaTable SET MediaPath=REPLACE(MediaPath,'~\','C:\Users\Tom\') ; --****EDIT FOLLOWING STATEMENT TO SET THE * SYMBOL TO YOUR MEDIA PATH**** UPDATE MultiMediaTable SET MediaPath=REPLACE(MediaPath,'*\','C:\Users\Tom\Documents\FamilyTree\RM8\') ; SELECT 'Script completed. ' ||'RUN DATABASE TOOLS IN RM7' AS Status ;