SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

SplitTree #splittreeancestorsleaves #application #visualc

Posted on by Tom Holden

The RMSplit C# program below allows you to create a group in RootsMagic5 which contains a selected person, their ancestors, and a selected number of “leaves” (collateral lines us a given depth). If you choose one leaf, you will get children of everyone in the direct line (and their spouses if that is checked). If you choose two leaves, you will also get the children and parents of those in the first leaf, and so forth. Once the group is created in RootsMagic 5, you can then use the group to create reports for that group or export just the people in that group to a GEDCOM file. There are two files below. RMSplitBinary.zip has the files you need to install the program on your computer. Unzip the file to a directory on your computer and then run SETUP.EXE from that directory. There is internal documentation in the program. RMSplitSource.zip has the C# source code necessary to build your own version. I developed the code using Microsoft Visual C# 2010 Express and System.Data.SQLite from http://system.data.sqlite.org/.

The usual caveats apply to using this program:

  • I am the only one who has tested this so far. Please try it on a backup copy of your database to make sure it works for you.
  • You are welcome to modify and use the source code in your own projects, but please give me credit for the part I wrote.
  • This is not supported by RootsMagic and has only been tested on the current version of RootsMagic 5 as of 3 Jan 2012. (caveat emptor)

(The previous binary didn’t work for many users. Please leave a message and let me know whether this one works for you. There are two binary files to choose from. Use RmSplitbinary64.zip if you are running a ‍‍64-bit version of Windows‍‍ and RmSplitBinary32 if you are running a 32-bit version of Windows.)
RmSplitBinary32.zip
RmSplitBinary64.zip
RmSplitSource.zip

Author: R. Steven Turley

Won’t run?

Probably the same problem and solution as described for RMtrix.

Screenshots From a Modified Build

These are from ve3meo’s variant.

RmSplit-SelectPersonScreen.png
Select Person Form called from the Main screen Change Root Person button. The full name list includes alternate names and birth-death years. On opening it defaults to the first name.
RmSplit-SelectPersonSearchScreen.png
Typing some letters filters the name list to only those containing the string. Click on any one to select it.
RmSplit-MainScreen.png
Main screen after selecting the database file, setting the root person, generations and leaves parameters, naming and creating the group. At the bottom is a status area showing the time it took (less than 1s) and the number of persons in the group. On the right is a log of actions,
RmSplit-RM5GroupSidebarScreen.PNG
Clip from RootsMagic 5 sidebar showing the members of the group created by RmSplit. To refresh the list after running RmSplit with different parameters, click on the Add, Edit, Delete Named Group icon next to the group name list box and cancel the resulting Named Groups dialog window.

Discussions & comments from Wikispaces site


ve3meo

Congrats! but no process

ve3meo
03 January 2012 22:16:55

Steve,
Congratulations on being the first member to post a high-level language app to work on the RM database! I have been feeling the urge to do so but don’t have the skills and maybe not the tools. I’m intrigued at your choice and wondering if they are free.

On the down note, although the app seems to have installed and can be found in the Start menu, no running process persists beyond a fraction of a second. This is on a AMD dual proc notebook running Win 7 64b Home Premium.

Tom


rsturley

rsturley
03 January 2012 23:53:54

Hmm. I’ll try installing it on a similar computer and see what happens.

I purposely chose free tools for this app so they would be easily available to others. It seemed to be in the spirit of the rest of the things here. Visual Studio 2010 Express can be downloaded for free from microsoft. The sqlite library for .NET is similarly free.

I’ll keep you posted on the installation problems. One option you obviously have is to download Visual Studio 2010 Express for C# from Microsoft and rebuild my application from the source on your machine.


ve3meo

Why Visual C#? Which wrapper?

ve3meo
04 January 2012 22:13:22

Steve, would you explain why you chose Visual C# over Visual C++? And which System.Data.SQLite setup or binary package do you use? I see there are several that all come with Visual C++ runtime (2008 or 2010) and wonder if that says anything about the preferable development tool.

Tom


rsturley

rsturley
05 January 2012 14:23:23

I chose Visual C# because I find it’s a quicker development environment for projects like this. The advantage of C++ is that it has a more direct connection to the base sqlite library which is written using unmanaged code. For other projects, C++ tends to be more portable, but an application written specifically for Windows like this one is tied to a single platform anyway.

Mostly, it’s a personal preference. I am a physics professor and do a lot of research work with students. I have found that I can bring them up to speed faster on C# programming than C++ so I tend to gravitate towards that for simple windows programs.

That said, if I was writing a big production code or if performance were at a premium, I’d probably use C++.

If you look at the supplied source code, you’ll note some conditional compilation sections (which are turned off) where I did some timing tests on this program. In the initial tests I did, most of the delays in processing the file were in the native sqlite library rather than the managed C# code.


ve3meo

New Wiki for Apps?

ve3meo
04 January 2012 22:40:23

I wonder if we should start a separate Wiki for, say, “Outboard Apps for RootsMagic” or “RootsMagic Add-Ons”. This one is already daunting to navigate and its focus could be blurred by including the development of standalone apps. If such development proved to be substantial, and were to occur across more than one platform, I could imagine a further forking into the different platforms.

Any comments?

Tom


rsturley

rsturley
05 January 2012 14:33:51

I suggest first seeing how much interest these is in stand-along apps and utilities. You noted that I’m the first one to contribute something like this. If there is a lot of interest, we could fork later.

Some of the challenge of navigating the current wiki is in organization. On the home page, you have some nice divisions for general information about writing queries, but no good place to organize the many queries people have contributed for different reasons. If there were a way to put these together in come kind of linked tree or web structure, it might be easier to browse through related contributions.

A reorganization might also address another question you raised about whether a fork to an RM5 query would be a good idea. If we tagged contributions as to whether they pertained to RM4 or RM5 (or both), they might profitably existed side-by-side in the same wiki.

If you do decide to fork the wiki, please be sure to provide two-way links between them. I learned a fair amount from this wiki in figuring out how to write the stand-alone application I have contributed here.


ve3meo

Speed

ve3meo
07 January 2012 03:54:14

It took 0.0816334 seconds to create nameList with 1196 elements and primaryNameList with 1157 elements.

I then created a group with gen and leaves =100, spouses not included. First time I tried it I thought it was in an endless loop. Tried it again – took 2:26 to complete:

Creating Group Tree

Creating group in RM database.
There are 1136 entries in the tree
Removing current group
Adding new group
Finished

In effect, it got almost everyone in my tree, except maybe for the spouse of an ancestor’s descendant with no child.


ve3meo

Compiled my own version

ve3meo
07 January 2012 04:35:47

Blundered around not knowing what I’m doing given little exposure to any IDE. Puzzled over which SQLite wrapper from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki and where it was to be unzipped and how to add it to Visual C#. Having installed Visual Studio 2010 C# Express successfully, I thought downloading Steve’s RmSplitSource.zip might show me. Turns out his project files include the necessary ones from system.data.sqlite, because I was able to compile a 64bit version of RmSplit.

And proved that I was not fooling myself by tinkering with the main screen layout, re-publishing and installing. Sure enough, I’m using RmSplit with my modified screen!

On to the Visual C# beginners’ course…


ve3meo

Bug with Leaves

ve3meo
07 January 2012 15:47:53

Seems that if Leaves is very large, it does not matter what Generations is set at – the entire tree is marked. Taking a root person at the bottom of the tree, 0,1,2,3,… generations, if Leaves is >=12, 1136 persons are marked. That’s using my compilation but I haven’t touched the underlying code – wouldn’t know where to begin.

Maybe I misunderstand how Leaves is intended to work?

Tom


ve3meo

ve3meo
07 January 2012 15:54:56

Moreover, once Leaves exceeds the value that marks the whole tree, larger values take longer to arrive at the same result. Seems that there is a loop which has no exit when all descendants have been identified and so keeps going until some limit set by the Leaves value.


ve3meo

Inline comment: “4-bit version of Windows‍”

ve3meo
04 September 2018 03:14:33

ve3meo Jan 5, 2012

64 bit success for me! But do not use an apostrophe in the name of the group as that will cause an unhandled exception from that point forward regardless of the name and requires the app to be closed in order to be reset.
rsturley Jan 5, 2012

Glad to hear it finally worked. I’ll work on trapping the apostrophe problem.
rsturley Jan 5, 2012

The problem with a ‘ is that it is a field delimiter in a SQL string that needs to be escaped. I’m thinking that % could cause grief as well. Can you think of anything else someone might want to put in there that will cause problems?
rsturley Jan 5, 2012

This problem has been addressed in the latest version now posted.
ve3meo Jan 5, 2012

Confirming that a ‘ (apostrophe) can now (ver 1.0.0.16) be used in the group name and that I had successfully tested every other character on the keyboard on the previous version (1.0.0.13).

Note that Help>About still erroneously reports ver as 1.0.0.3.

Inline comments


ve3meo

Comment: 64 bit success for me! But do not use…

ve3meo
06 January 2012 03:41:13

64 bit success for me! But do not use an apostrophe in the name of the group as that will cause an unhandled exception from that point forward regardless of the name and requires the app to be closed in order to be reset.


rsturley

rsturley
06 January 2012 04:08:23

Glad to hear it finally worked. I’ll work on trapping the apostrophe problem.


rsturley

rsturley
06 January 2012 05:39:16

The problem with a ‘ is that it is a field delimiter in a SQL string that needs to be escaped. I’m thinking that % could cause grief as well. Can you think of anything else someone might want to put in there that will cause problems?


rsturley

rsturley
06 January 2012 17:12:05

This problem has been addressed in the latest version now posted.


ve3meo

ve3meo
06 January 2012 18:01:51

Confirming that a ‘ (apostrophe) can now (ver 1.0.0.16) be used in the group name and that I had successfully tested every other character on the keyboard on the previous version (1.0.0.13).

Note that Help>About still erroneously reports ver as 1.0.0.3.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.