I keep seeing that people are using SQLlite to acces the RM database. I have recently upgraded to RM10, but would like to delve more into the database with SQLlite in order to get more detailed infoformation So…
Can I use SQLlite to do this?
If so where do I get it from and what do I need to do to acces my RM database?
Where are the RM database files stored?
One resource you will definitely want to use is : https://sqlitetoolsforrootsmagic.com/
the simple answer is yes. You need a data connector (usually a ODBC connector). You can use many “tools” Including EXCEL , Power BI, and other SQLITE tools.
Should have some basic understanding of Database structure. You should understand which tools are READ ONLY and which are NOT. You should make back of database before using and tool that might Write. ALso you should avoid running RootsMagic (unless on same database) when using any of those tools and and only use ONLY tool at time - making sure you close database so no changes are lost or database locked issues do not occur. Not trying to scare you from using SQLITE tools but you need to understand any potential risk(s).
as far as the benefits of using Sqlite they are almost endless and only limited by the scripts and tools you use.
Kevin
You may get something out a piece I wrote on the topic at:
SQLite is relational database software. There are many, many relational database software packages in the world, including such things as MySQL, Oracle, IBM DB2, Microsoft SQL server, etc. The actual list of relational databases in the world is much longer than that.
SQLite is open source software that is free for applications such as RootsMagic to use. It is surely the mostly widely deployed relational database software in the world, surely having billions if not almost trillions of installations. Somewhere in your smartphone, some app is probably using SQLite and possibly the phone’s operating system is using SQLite as well. Somewhere in your Windows or Mac computer, some app is probably using SQLite and possibly the computer’s operating system is using SQLite as well. Your smart car is probably running SQLite somewhere. Your smart watch is probably running SQLite somewhere. Your smart refrigerator is probably running running SQLite somewhere. Etc. So in a sense, SQLite has it’s own niche and for the most part it really doesn’t compete (or compete very much) with other relational database software in the world.
What separates SQLite from other relational databases is primarily that it is intended to be used as an embedded system, the way it is embedded in RM, and that is only intended to be a single user system. So you and your spouse or you and your cousin cannot be sharing the same RM database at the same time. So it is software that is embedded in other software, and software that is one user at a time. It is also software that is running on your local computer against a database that is stored on your local computer. The SQLite databases for your RM databases are on your local computer and have file names of the form *.rmtree
SQLite databases such as your RM database can be accessed through an SQLite manager or through a data connector such as an ODBC connector. I usually access my RM database through an SQLite manager called SQLiteSpy, and I have also accessed my RM database using Microsoft Access and Microsoft Excel by using an ODBC connector. With an SQlite manager, you typically have to have some programming skills and have to be able to write SQL code. With a data connector, you can usually access the data using non-programming tools such as Access, Excel, or Power BI. A third way to access the data is through programming interfaces in languages such as Java.
I would strongly urge you to move further discussions to SQLite Tools for RootsMagic This is a site created by RM users. It is not owned or operated by RM, nor is it endorsed by RM.
Can one add a SQLite extension, such as RMNOCASE, using ODBC ?
(only an issue with data modifying queries…)
good question – even read only query can run into issues with “fields” that require it. Unfortunately a user can run into issues even for read only. RMNOCASE can be a PIA at times even when not writing or updating so have to do things differently using Powery Query (in case of Excel or Power BI)
Kevin
I don’t know. When I have researched these kinds of questions, the answers are all over the place. The best I can tell, it depends in part on the particular ODBC driver, so there appears not to be an ODBC solution that is one size fits all. I know of only one ODBC driver for SQLite, and I don’t think it has any collation options. I could be wrong about that. There could be an ODBC drive for SQLite that supports collation options.
Collation can be a very serious problem for developers. I found a post by one developer who was developing code for a Web site for users who accessed the site using many languages. He wanted to be able to use collation appropriate for French if the user was French, to use collation appropriate for German if the user was German, etc. He didn’t have a solution for his collation problem. So there is a problem even if the underlying database is using only the standard NOCASE collation rather than something like RM’s special RMNOCASE collation.
Any SQLite database uses UTF-8 data, which is an encoding of UNICODE. It’s not just RM that uses UTF-8 with SQLite. It’s any SQLite database. So any SQLite database can store pretty much any characters known to humankind, up to and including Egyptian hieroglyphics. But SQLite’s version of NOCASE really only supports English letters as far as collation goes. For example, SQLite’s version of NOCASE doesn’t understand that Ø and ø are uppercase and lowercase versions of the same Norwegian letter. I think RMNOCASE makes it even worse because it makes Ø and ø sort as if they were O and o. For these and other reasons, I quit using Microsoft Access and Microsoft Excel with RM’s database, and for a rather long time now have used only native SQL with an SQLite manager.
Back to RM itself and collation, Michael Booth once answered a question to the effect that RM has future plans for supporting languages other than English. That would be a wonderful new feature. However, I think that language appropriate collation is an important part of supporting languages other than English. But I don’t have a clue as to how language appropriate collation could be implemented in RM. I hope the RM developers figure it out if ever RM begins to support languages other than English.
And it was 15 years ago that we last heard that work towards that goal was being deferred in favour of other priorities…
ChatGPT seems to think so. I’m not currently using ODBC and have only dabbled in it years ago but it seemed to me that if you can get a RMNOCASE collation by loading unifuzz.dll directly in SQLite that you should be able to tell it to do so via ODBC. Here’s the response to my question:
It looks like it might work, but I don’t have the time or energy to go back to ODBC and Access and Excel right now to try it out.
I have no problem using ODBC with Excel and selecting the SQLITE 3 Datasource.
I have the unifuzz64.dll for SQLite Studio and also SQLite Spy with the RMNOCASE_fake-SQLiteSpy64 dll extension so I don’t know whether they contribute. I only use SQLite Studio now though and test my SQL there before pasting it into Excel
For Advanced Options - connection string I add
DSN=SQLite3 Datasource;Database=T:\Roots Magic Data\xxx.rmtree;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;
where T:\Roots Magic Data\xxx.rmtree is my full path and file name of my datatabase.
I’ve just started experimenting with Access and RM using the SQLite ODBC connector. I have discovered that you can specify the unifuzz extension for RMNOCASE when you set up your ODB data source. I use the 64-bit version but I’m sure the 32-bit works too.
Update: I had to copy unifuzz64.dll into C:\Windows\System32, and then just enter “unifuzz64.dll” in the “Load Extensions” field.
Access did not throw an error when I used the previous approach to setup the ODBC connection. However, as soon as I started query design and testing, I kept getting errors messages that Access couldn’t find the dll.
I was expecting the extension to allow for sorting on fields such as [NameTable][Surname], but it does not appear to help with that. Possibly user error; I’m continuing to experiment.
Sorting either goes in the query as an ORDER BY clause or is done in Excel after importing the data. I tend to favour the latter as refreshing the data then preserves the sort order
