Contents
Introduction
With all the excitement surrounding how well Artificial Intelligence is doing to write papers that can fool teachers and having seen someone post a message about using it to generate some software coding, I was curious to see what the ChatGPT interactive AI research tool could say about creating a SQLite query of a RootsMagic database. The answer, at the moment, is ‘not much’ but it does tell you how to get started with the SQLite Command-Line Interface (CLI). The composition itself is very good!
How do I query my RootsMagic sqlite database?
You can use the SQLite command-line interface to query your RootsMagic database. To do this, follow these steps:
- Download and install the SQLite command-line interface if you don’t already have it: https://sqlite.org/download.html
- Open a terminal or command prompt window and navigate to the directory where the SQLite executable is located.
- Connect to your RootsMagic database by typing the following command, replacing “database_file” with the path to your database file:
sqlite3 database_file
- Once you are connected to the database, you can run SQL queries against it by typing them into the command prompt and hitting enter. For example, to get a list of all tables in the database, you can run the following query:
SELECT name FROM sqlite_master WHERE type='table';
- To see the schema for a particular table, you can use the .schema command, followed by the name of the table. For example:
.schema people
- To see the contents of a table, you can use the SELECT statement. For example:
SELECT * FROM people;
This will display all rows and columns in the “people” table. You can use various clauses in your SELECT statement, such as WHERE, GROUP BY, and ORDER BY, to filter and sort the results.
I hope this helps! Let me know if you have any questions. (How polite and generous is that!?)
What database engine is used for a RootsMagic 7 database?
RootsMagic 7 uses the Firebird (Oops! got that wrong- that’s pre-RM4) open-source database engine to store and manage data in its databases. Firebird is a powerful and reliable database engine that is well-suited for use in genealogy software like RootsMagic. It is capable of handling large amounts of data and can support a wide range of data types and structures. Firebird is also designed to be easy to use and maintain, making it a good choice for software like RootsMagic that is intended for use by non-technical users.
Firebird is based on the InterBase database engine, which was developed by Borland in the 1980s. Firebird was created as an open-source fork of InterBase in the early 2000s, and has since become a popular choice for a variety of applications that require a robust and reliable database engine.
Can multiple users access the same Rootsmagic file simultaneously?
From what I can tell, it appears that SQLite should be able to handle multiple users if it locks the database for several milliseconds whenever it is writing data. But is Rootsmagic able to handle two to three people at separate computers accessing the same file?
Curious that you would add this comment/question under this topic about using AI so I paraphrased your question to Co-Pilot:
———————-
Q:
Can multiple users access the same sqlite database file on a networked drive simultaneously at separate computers?
A:
Yes, multiple users can access the same SQLite database file simultaneously at separate computers ¹²³. However, SQLite locks the entire database when writing, so if there are many concurrent writes, it may not be the best database to use ¹². If most of the concurrent accesses are reads, SQLite can handle them very well ¹. Multiple processes can have the same database open at the same time, and multiple processes can be doing a SELECT at the same time ¹.
Please note that SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultaneously if the database is in “wal” or “wal2” mode, although the system still serializes COMMIT commands ⁵.
I hope this helps!
Source: Conversation with Bing, 2024-01-11
(1) SQLite Concurrent Access – Stack Overflow. https://stackoverflow.com/questions/4060772/sqlite-concurrent-access.
(2) Multiple concurrent connections to same large SQLite file on a network …. https://dba.stackexchange.com/questions/234461/multiple-concurrent-connections-to-same-large-sqlite-file-on-a-network-share.
(3) Can SQLite support multiple users? – Stack Overflow. https://stackoverflow.com/questions/5102027/can-sqlite-support-multiple-users.
(4) Multi User Access to SQLite on Network Drive – Databases – Xojo …. https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271.
(5) sqlite – Simple multi-user database solution – Stack Overflow. https://stackoverflow.com/questions/1856313/simple-multi-user-database-solution.
—————-
However, I think there is risk based on what the developer has stated at sqlite.org
Thanks Tom!
I guess I forgot to look at what the title of the thread is. I wasn’t sure how to start a new thread. I was looking at these last few paragraphs when I posed my question:
RootsMagic 7 uses the Firebird (Oops! got that wrong- that’s pre-RM4) open-source database engine to store and manage data in its databases. Firebird is a powerful and reliable database engine that is well-suited for use in genealogy software like RootsMagic. It is capable of handling large amounts of data and can support a wide range of data types and structures. Firebird is also designed to be easy to use and maintain, making it a good choice for software like RootsMagic that is intended for use by non-technical users.
Only site users with author or higher privileges can post new topics to the Blog but any logged in user can do so to the Forum (on the navigation menu).
If you read the references cited by Co-Pilot, you will see that it is a much more qualified “yes” than was conveyed by its answer text. For reads, it’s okay except for the performance hit. But writes risk collisions, crashes and corruption. RM uses the older and poorer of the two modes that prevent concurrent writes so it carries higher risk.
That said, there are users who say that they have had their RM database on a networked drive for years without a problem but I think that may have been for the convenience of one user working in different rooms.