1,582
edits
Changes
no edit summary
Gramps 5.0 comes with a new, alternative database backend called DB-API. The default database will be the old standard Berkeley DB (often abbreviated as BSDDB). Gramps 5.0 is the initial test of the DB-API backend. If it works well, then Gramps 5.1 will make it the default.
What is DB-API? DB-API is a very loose standard to connect to "SQL engines". DB-API doesn't standardize any of the SQL, but merely standardizes the ability to connect, query, and get results back. Gramps has been designed to work on three SQL enginesYou can read more about the [https: //www.python.org/dev/peps/pep-0249/ DB-API specification].
Gramps has been designed to work with three DB-API SQL engines: * sqlite3- single or multi-user on single operating system* postgresql- local or remote, single or multi-user* mysql- local or remote, single or multi-user
sqlite3 has been tested the most and is the default SQL engine. However, advanced users can adjust the details of the SQL connect. See [[#Advanced uses]] for more details.
There are other possibilities, and even combinations of the above.
==SQL DetailsAdvanced uses== The easiest way to use a variation in SQL engines is to make a new DB-API database. You can do that by changing the default backend in Preferences -> Family Tree -> Database backend, Converting an existing database (new button in Manager), or CLI importing:
from dbapi_support.sqlite import Sqlitepath_to_db =os.path.join(os.path.dirname(os.path.realpath(__file__)), 'sqlite.db')dbapi =Advanced uses=Sqlite(path_to_db, password="MyPassW0rd8723")
from dbapi_support.postgresql import Postgresql
Getting a postgresql server running on Ubuntu is fairly easy.
==SQL Details==
Does the use of DB-API mean that Gramps data are now relational? Does it provide additional data integrity? No. Currently, we are using the SQL engines in exactly the same way that we used BSDDB: all of our data is pickled "blobs" (serialized tuples). However, the flat (non-hierarchical) data is also stored in their own column, and indexes are made on each of those. This allows us to have very fast ad hoc queries on any non-hierarchical data, and JOINS between primary objects. As far as I know, we always did sequential scans in BSDDB. If we connect the SQL WHERE capability up to Filters, then our filters will not require a linear scan through all data. This will be a huge win, but requires a some serious refactoring of views, filters, and the use of the database. Nick is looking at that.
Gramps could have used an SQL abstraction layer, such as SQLAlchemy or Django, but that would be a large dependency for Gramps. The DB-API layer is completely separate; each SQL engine provides its own DB-API interface. As this first step away from BSDDB, we avoided any dependencies. That allows us to write directly in SQL, but also requires that all of the SQL that we do write has to be understood by our SQL engines.
All of the SQL statements are in a single file: gramps/plugins/database/dbapi.py. If we wish to write more abstractly than raw SQL using, say, SQLAlchemy, then we need to just re-implement that file. That will add a dependency, but will allow us to add Oracle and SQL Server to the list of supported SQL engines, and allow us to write more sophisticated and optimized SQL. Of course, as this is a plugin, any new backend can be added to addons at any time. Two other possible future backends are MongoDB and CouchDB. Both of these match our data better (hierarchical), but are also newer/less stable, and limited in variability. Sqlite file structure is well-known, backwards compatible, and rock solid. Postgresql and MySQL provide server/shared abilities.
The SQL engines are all placed into "autocommit mode". That means that any updates automatically will be placed into a transaction. However, batch transactions (such as an import) will explicitly use a SQL "BEGIN TRANSACTION;" statement, and end with a "COMMIT TRANSACTION;" statement.
