I just finished a session with the free Google Gemini Flash 2.5 model to develop a quite complex query to identify couples in a RootsMagic database who are cousins of each other (see the discussion that led to it in the Forum at https://sqlitetoolsforrootsmagic.com/forum/topic/cousin-marriages/). That was preceded a few months back by a session with DeepSeek trying to develop a query that could act as an alternative to RM’s Set Relationships tool – it was not as successful but it might be more so now as its model evolves and grows. Anyway, that caused me to ask Gemini some questions about how to effectively use it to develop SQLite3 queries for RootsMagic databases. That “conversation” can be read in full at https://g.co/gemini/share/b9ef037e35c6 but I asked it to generate a summary for this post, pasted below.
Unlocking Your RootsMagic 10 Data: A Deep Dive into SQLite Queries
RootsMagic 10, like its recent predecessors, stores all your precious genealogical data in a robust SQLite database file (typically with a
.rmtreeextension). This self-contained, file-based relational database is embedded directly within the RootsMagic software, making it incredibly efficient for personal use.While RootsMagic’s interface is fantastic for managing your family tree, sometimes you need to dig deeper – to run custom reports, analyze specific data patterns, or extract information in ways the software doesn’t natively offer. This is where direct SQLite querying comes in.
Key takeaways for RootsMagic users:
- SQLite is Your Data’s Foundation: Your entire family tree, including persons, events, sources, citations, and media, resides within a structured SQLite database.
- Access with Caution (and Backups!): You can explore your
.rmtreefile using third-party SQLite tools (like DB Browser for SQLite or SQLiteSpy). Always create a full backup of your database before attempting any direct access or modification!- Understanding the Schema is Key: To write effective queries, you need to know the specific table names (e.g.,
PersonTable,EventTable,SourceTable) and column names (e.g.,FirstName,LastName,Date1,Title) within the RootsMagic schema. These are the “building blocks” of your queries.- How AI Can Help (with your input): AI models like Gemini can assist in generating complex SQLite queries. However, because they don’t have real-time access to your specific database’s schema, you’ll need to provide the relevant table and column names to ensure the queries are accurate.
- Leverage Context in Conversations: If you’re working with an AI, you can generally continue to refine and develop multiple queries within the same chat session, as the AI will retain context of the schema details you’ve already provided. For new chat sessions, a quick reminder of the core table/column names will be helpful.
By understanding the SQLite foundation of RootsMagic and learning how to craft (or have an AI craft!) custom queries, you gain unparalleled power to analyze and utilize your genealogical data in new and exciting ways. Just remember the golden rule: Always back up your database before you begin!
That summary of the responses to my prompts up to that point of the session really missed a lot of what Gemini said so it’s worth delving into the conversation for more insight into points 4 & 5 and how an AI might become repetitive or forget or veer off due to an error in input or interpretation, as happened in my session with DeepSeek and even to a degree with Gemini that I was able to catch and correct.
I went on in the conversation with Gemini to discover that content lifted from the RM10 Data Dictionary in CSV format and pasted into the Chat could provide it with important parts of the database schema that it readily understood. Until now, I have been typing in the bare minimum description to provide the context with which the AI engine is to work. If a comprehensive description of the schema can be easily provided to the AI engine, that will make development of custom queries much easier. At the moment, the prompt capacity is not large enough to accept the entire sheet named “RM10 Data Def Project” as CSV text.
The raison d’etre of this website is to “Exploit your RootsMagic family tree database with SQLite Tools”. AI assistance in creating new and more powerful “SQLite Tools” looks to be within reach at no or low $cost for those with a good understanding of the RootsMagic database design and SQL.