Entity Framework Core ORM to work with SQLite databases created by
RootsMagic ©
·
Report Bug
·
Request Feature
Table of Contents
RootsMagic© is a genealogy application which uses an SQLite database to store its data. rmSharp helps working with this database in an object oriented way without deeper knowledge of the underlying SQL query language. Probably, the main use case is to perform more complicated queries which might not be provided by the RootsMagic © application.
However, rmSharp can also be used to manipulate the database. E.g., to add new persons, events, tasks, groups programmatically.
Disclaimer: Be sure to back up your database before using rmSharp to make changes. rmSharp is well tested, but bugs that could potentially corrupt your database cannot be excluded. It is recommended that you carefully check the integrity of your database after making changes with rmSharp.
The library was compiled using the community edition of Visual Studio 2022. It depends on the following packages available on nuget:
- Entity Framework Core (Microsoft)
- Entity Framework Core Proxies (Microsoft)
- Entity Framework Core SQLite (Microsoft)
- Delegate Decompiler (Alexander Zaytsev)
- Geolocation (Scott Schluer)
- For ease of use, rmSharp is available as a nuget package to be used in your own projects.
- If you prefer to install rmSharp locally, you can download the repository from GitHub. It contains the required solution (.sln) and project (.csproj) files to build the library and the included examples. During the first build, the VS package manager will automatically install all dependencies.
The Entity Framework is the standard Microsoft tool for generating and working with object-oriented models which abstract the underlying database. See this Wikipedia article for details about object relational mapping (ORM). Corresponding tutorials are readily available on the web. See e.g:
- https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app
- https://learn.microsoft.com/en-us/dotnet/csharp/linq/standard-query-operators/
The rmSharp model uses the following naming convention to map database tables to C# tables (i.e., dbSet<T> types) and model entities. First of all, the model tables are named after the database tables but remove the "Table" postfix and pluralize the original name. The table rows map to simple POCOs (plain old C# objects) which are named using the singular of the table names. E.g., the database table NameTable maps to the model table Names which consists of a collection of Name objects. The TaskTable maps to a set of Task objects and is named Tasks etc.
Here an example using the database table NameTable which is structured in the following way. (Detailed information about all tables can be found here: https://sqlitetoolsforrootsmagic.com/)
CREATE TABLE NameTable (
NameID INTEGER PRIMARY KEY,
OwnerID INTEGER,
Surname TEXT COLLATE RMNOCASE,
Given TEXT COLLATE RMNOCASE,
Prefix TEXT COLLATE RMNOCASE,
Suffix TEXT COLLATE RMNOCASE,
Nickname TEXT COLLATE RMNOCASE,
NameType INTEGER,
Date TEXT,
SortDate BIGINT,
IsPrimary INTEGER,
IsPrivate INTEGER,
Proof INTEGER,
Sentence TEXT,
Note TEXT,
BirthYear INTEGER,
DeathYear INTEGER,
Display INTEGER,
Language TEXT,
UTCModDate FLOAT,
SurnameMP TEXT,
GivenMP TEXT,
NicknameMP TEXT
);
rmSharp maps the rows of this table to the following entity class:
public partial class Name
{
public long NameId { get; set; }
public long OwnerId { get; set; }
public string Surname { get; set; } = string.Empty;
public string Given { get; set; } = string.Empty;
public string Prefix { get; set; } = string.Empty;
public string Suffix { get; set; } = string.Empty;
public string Nickname { get; set; } = string.Empty;
public NameTypes NameType { get; set; } = NameTypes.Primary; // <- mapped from long to NameType enumeration
public string Date { get; set; } = ".";
public long SortDate { get; set; }
public bool IsPrimary { get; set; } = true; // <- mapped from long to bool
public bool IsPrivate { get; set; } = false; // <- mapped from long to bool
public Proof Proof { get; set; } = Proof.unknown; // <- mapped from long to Proof enumeration
public string Sentence { get; set; } = string.Empty;
public string Note { get; set; } = string.Empty;
public long BirthYear { get; set; }
public long DeathYear { get; set; }
public long Display { get; set; }
public string Language { get; set; } = string.Empty;
public string SurnameMp { get; set; } = string.Empty;
public string GivenMp { get; set; } = string.Empty;
public string NicknameMp { get; set; } = string.Empty;
public DateTime ChangeDate { get; set; } // <- mapped type from float to DateTime and changed name from UTCModDate to ChangeDate
// Navigation
public virtual ICollection<Citation> Citations { get; set; } = [];
public virtual ICollection<Task> Tasks { get; set; } = [];
// Helpers
public override string ToString() => $"{Surname} {Given}";
}where the enums NameTypes and Proof are defined as
public enum NameTypes { Primary = 0, AKA = 1, Birth = 2, Immigrant = 3, Maiden = 4, Married = 5, Nickname = 6, OtherSpelling = 7 };
public enum Proof { unknown = 0, proven = 1, disproven = 2, disputed = 3 };The first part of the Name entity is a more or less a 1:1 translation of the table columns to C# types. For convenience, however, rmSharp introduces
the following bidirectional conversions:
- The table columns
NameTypeandProofare converted fromlongto C# enumerations. - The
IsPrimaryandIsPrivateflags are converted fromlongto boolean values. - In the model the table column
UTCModDateis renamed toChangeDateand is converted from an encodedfloatvalue to the standard C#-typeDateTime.
The properties in the navigation section of the Name entity model the relation to other entities. A Name can have zero or many citations which is modeled by the Citations collection. Also, it can have one or many Tasks, modeled by the Tasks collection. If you query for a Name in the Names table, the Entity Framework automatically generates an SQL query joining the tables CitationTable and TaskTable and fills the Citations and Tasks collections correspondingly.
Here some simple examples showing how to use the library. The corresponding project files can be found in the folder src/Examples of the repository. The examples use various databases stored in the folder src/Examples/example_databases
RM can store more than one name for a person. It uses the NameType column to distinguish between 'primary', 'birth', 'maiden',... names.
The following code shows how to extract and print all primary names, i.e. names with NameType set to `NameType.Primary' from the database.
static void Main(string[] args)
{
DB.sqLiteFile = "../../../../example_databases/US_Presidents.rmTree"; // database file
using (var db = new DB()) // connect to the database
{
var primaryNames = db.Names // query the Names table
.Where(n => n.NameType == NameTypes.Primary) // filter out all primary names...
.OrderBy(n => n.Surname); // ...and order them by surname
foreach (var name in primaryNames)
{
WriteLine(name.Surname + " " + name.Given);
}
}
}Which prints:
...
Reade George
Reade Robert
Reade Andrew
REAGAN Michael
REAGAN John
REAGAN John Edward
REAGAN Ronald Wilson
REAGAN Patricia_Ann DAVIS
REAGAN Ronald Prescott
REAGAN John Neil
REAGAN Maureen Elizabeth
REAGAN Michael Edward
REAGAN Cameron Michael
REAGAN ? (Girl)
Record Margaret
...
To query the database the Entity Framework automatically generates the required SQL command and sends it to the database. We can have a look at the generated SQL command by adding
Console.WriteLine(primaryNames.ToQueryString());to the code above. This will print:
SELECT *
FROM "NameTable" AS "n"
WHERE "n"."NameType" = 0
ORDER BY "n"."Surname"
Here a more interesting example showing how to query the PersonTable. The code first filters the persons for all male individuals having the surname "Jefferson". It then uses the Events navigation property of the found individuals and filters those for a birth event. If it found one, it reads out the Date property of the event and adds it to the printout of the individuals name. Finally it uses the Children navigation property to print all children belonging to the person.
static void Main(string[] args)
{
DB.sqLiteFile = "../../../../example_databases/US_Presidents.rmTree"; // database file to be set only once
using (var db = new DB())
{
var jeffersons = db.Persons.Where(p => p.PrimaryName.Surname == "Jefferson" && p.Sex == Sex.Male); // get all male Jeffersons from the database
foreach (var person in jeffersons)
{
Write(person.PrimaryName);
var birthEvent =
person
.Events // query the events belonging to this person
.Where(e => e.FactType.Name == "Birth") // filter for birth events
.SingleOrDefault(); // get the event or null if none - or more than one which should not happen of course)
if (birthEvent != null) // if we have a birth event we print its Date property
{
Write($" (*{birthEvent.Date})");
}
WriteLine();
foreach (var child in person.Children) // print primary name and sex of all children
{
WriteLine($" - {child.PrimaryName} ({child.Sex})");
}
}
}
}It prints:
Jefferson Thomas (*13 APR 1743)
- Jefferson Martha (Patsy) (Female)
- Jefferson Jane Randolph (Female)
- Jefferson ? (unnamed son) (Male)
- Jefferson Mary (Maria) (Female)
- Jefferson Lucy Elizabeth (Female)
- Jefferson Lucy Elizabeth (Female)
Jefferson ? (unnamed son) (*28 MAY 1777)
Jefferson Peter (*1708)
- Jefferson Jane (Female)
- Jefferson Mary (Female)
- Jefferson Thomas (Male)
- Jefferson Elizabeth (Female)
- Jefferson Martha (Female)
- Jefferson Peter Field (Male)
- Jefferson ? (unnamed Son) (Male)
- Jefferson Lucy (Female)
- Jefferson Anna Scott (Female)
- Jefferson Randolph (Male)
Jefferson Peter Field (*1748)
Jefferson ? (unnamed Son) (*1750)
Jefferson Randolph (*1755)
Jefferson Thomas (*)
- Jefferson Peter (Male)
For more examples, please refer to the Example folder
Distributed under the MIT License. See LICENSE.txt for more information.