-- Import_RM8_to_RM7-Part2A.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 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\Bartels-Cremer-2.7 (3).rmtree" AS RM8db ; /* TBD ConfigTable --Can anything useful be copied from RM8? */ 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 MediaLinkTable ; 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 ; 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 ; DELETE FROM CitationTable ; INSERT OR REPLACE INTO CitationTable SELECT CitationID ,OwnerType ,SourceID ,OwnerID ,Quality ,IsPrivate ,Comments ,ActualText ,RefNumber ,Flags ,Fields FROM RM8db.CitationTable JOIN RM8db.CitationLinkTable USING(CitationID) ; 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 ; 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 ; 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 ;