3 replies to this topic

[TomH]

Built an evidence-driven tree on Ancestry.com and wanted to get the source images into RM. Importing the Ancestry GEDCOM gives me the URL to each citation page in the Comments field of the RM citation but with some 4300 citations, I couldn't face chasing down each image. So I bought Family Tree Maker 2012 because it apparently downloaded the images and synchronises with the Ancestry Family Tree. Great! It does BUT its GEDCOM export does not include the URL to the citation page. Caught betwixt and between!

Ancestry.com and RootsMagic 5 summarizes the tortuous story of how I was able to bring URLs and images together in one database despite the GEDCOMs producing totally disparate numberings for people, facts, citations, etc. Maybe it will be of some use to another determined soul who will be able to complete in minutes what has taken me days to understand and develop.

[arbopa]

Hmm, I am new to RM6, and have been using ancestry.com to find and update info I already had. Then I learned all the cool images were NOT passed on or downloaded into RM6. I was hoping this thread was going to provide an answer. Bummer. Guess it will be a matter of one or the other to keep up to date.

[pbooth99]

TomH, Thanks for sharing your torturous story- I wish that I had read it sooner.

 

I set out to duplicate your steps and got a little stuck and wante dto sanity checki my thinking.

 

Context: I have 8000 person ancestry.com tree that has lots of duplicate fact entries fwith multiple copies of same event - something that is easy to see in Rootsmagic but less glaring in ancestry.com.

 

If I take a subset tree with say 200 people then the MergeAncestryURLsToFTM.sql process with a RMNOCASE patched SQLiteSpy works perfectly and converts source URLs and imports media.

 

But if I try with my entire tree it fails with a "Maximum length of LIKE or GLOB error."

No problem, I thought, and built a new sqlite3 on my OSX box, increasing the SQLITE_LIMIT_LIKE_PATTERN_LENGTH value from 50000 to 500000. But I didn't realize that the  RMNOCASE extension depends on a Windows call and wont build on . I then tried cutting my tree into 16 branches, but even these triggered the "Maximum length of LIKE or GLOB error."

 

I'm not a windows guy, don't have a visual studio install. IS it possible to use something like mingw to build a pacthed SQLITE3 on Windows?  Or is it feasible to write an automated gedcom equivalent of the unix split command that breaks a gedcom into N arbitrary pieces that could then be attached?

Or is it feasible to run the MergeAncestryURLsToFtm.sql script without the RMNOCASE extension?

 

Curious a sto how you'd go about this. I guess I should be able to break apart the  script so that the LIKE clause thats failing is split in pieces?

[TomH]

Wow, that was three years ago! And if you are into compiling your own custom sqlite, you are way ahead of me. I had a quick look at the sql and see that there is only one (complex) statement using the LIKE operator so the focus is narrowed. Unfortunately, it ANDs 10 of them. Assuming SQLiteSpy does not reduce the limit on the LIKE pattern from 50000, then I suspect there is data that is unexpectedly large in some of the records. I would suggest that you test for that. 

 

Execute the script partially, up to the UPDATE with the LIKEs. Instead of running that UPDATE, use the commented SELECT on each of the comparisons to find the one that fails. Then examine the data in those columns. Maybe it is data you can discard or move to some other column. For example:

SELECT Pri.CitationID, Sec.CitationID, Pri.Comments, Sec.Comments
FROM Pri, Sec
WHERE 
REPLACE(REPLACE(Pri.ActualText, CAST(x'0D0A' AS TEXT), ''),' ','') 
LIKE REPLACE(Sec.ActualText, ' ', '')
; 

I imagine that with some further analysis, a different procedure could be developed that might lend itself to easier debugging. For example, creating a temp VIEW for each of the comparisons.

 

This discussion should continue on the Wiki page Ancestry.com and RootsMagic 5, not here, given that it is about that specific SQLite script.