Gramps SQL Database

This page documents the format of a SQL database version of the GRAMPS BSDDB datastore. This can be seen as a step towards a GEPS 010: SQL Backend for GRAMPS, and for supplying data to other applications, such as a GEPS 013: GRAMPS Webapp.

GRAMPS SQL database Overview

The current version of this project outputs the GRAMPS BSDDB data into a relational SQLite database. You can access this functionality through the ExportSql and ImportSql programs of the Third-party Plugins.

Sample usage

After dropping the above plugins into your .gramps/plugins subdirectory, you can:

python src/gramps.py -O "My Family Tree" -e familytree.sql

This will export the entire contents of your database (not including bookmarks, gramplets, nor any meta data, etc.) into a sqlite3 database named "familytree.sql'. Likewise, you can read the data back in to a new database:

python src/gramps.py -i familytree.sql

NOTE: if you import this file into an existing family tree, you will get corrupted data for any items that overlap with internal numbering! This has the same effect as importing with GRAMPS XML.

Exporting a SQL database can take up to 2 seconds per record to output. Prior to adding indexes and primary keys, importing a SQL database can take up to 1 second a record to read in. However, with the keys and indexes enable, it only takes about 0.01 seconds per record.

External Access

After exporting, you can then access your data using any program that can read sqlite files. For example, on Linux:

$ sqlite3 export.sql 
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
address         event           location        note            repository_ref
attribute       event_ref       markup          person          source        
child_ref       family          media           person_ref      source_ref    
datamap         lds             media_ref       place           url           
date            link            name            repository    
sqlite> .headers on
sqlite> .mode columns
sqlite> select * from person;
handle              gid             gender death_ref_handle    birth_ref_handle    change     marker0 marker1  private
------------------  --------------  ------ ------------------- ------------------- ---------- ------- -------- --------
b5dc6d9aa5766513709 I0010           0      b5dc6d9aa07279205ad b5dc6d9aa3e7b41b0f1 1249739601 1       Complete 1
b5dc6d9add708e5ba9e Amle achba      0      b5dc6d9adc539e3085e                     1249739601 2       ToDo     0
b5dc6d9cd9c134a0c39 I0017           1                                              1249739602 -1               0
b5dc6d9dfd3719d4e00 imgoabim Miulka 1      b5dc6d9dfca6a342e45                     1249739603 1       Complete 0
b5dc6d9f38779e2275b I0024           0                          b5dc6d9f37b685b9607 1249739603 -1               0
...

If you change the data, you can then read it back in and GRAMPS will reflect the changes.

NOTE: You should only attempt editing textual fields or fields for which you know the meaning. For example, in the above, do not change the handle fields, but you can change one's gender for 0 to 1.

Database Structure

The GRAMPS BSDDB data is broken up into the following 24 tables. The structure and names of the tables and fields were designed to match the naming and operation of the GRAMPS source code. All of the tables have singular form names (e.g., 'person' rather than 'people').

All data are accessed by their handles. Links between tables are made through the link table. Rather than having many tables representing joined data, a single table (link) represents the conection. This is made by linking a from_type and from_handle to a to_type and to_handle.

markertype

CREATE TABLE "markertype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

nametype

CREATE TABLE "nametype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

attributetype

CREATE TABLE "attributetype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

urltype

CREATE TABLE "urltype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

childreftype

CREATE TABLE "childreftype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

repositorytype

CREATE TABLE "repositorytype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

eventtype

CREATE TABLE "eventtype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

familyreltype

CREATE TABLE "familyreltype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

sourcemediatype

CREATE TABLE "sourcemediatype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

eventroletype

CREATE TABLE "eventroletype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

notetype

CREATE TABLE "notetype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

gendertype

CREATE TABLE "gendertype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

ldstype

CREATE TABLE "ldstype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

ldsstatus

CREATE TABLE "ldsstatus" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "val" integer NOT NULL
);

config

CREcreated ATE TABLE "config" (

   "id" integer NOT NULL PRIMARY KEY,
   "db_version" varchar(25) NOT NULL,
   "created" datetime NOT NULL

);

person

CREATE TABLE "person" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "gender_type_id" integer NOT NULL REFERENCES "gendertype" ("id")
);

family

CREATE TABLE "family" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "father_id" integer REFERENCES "person" ("id"),
    "mother_id" integer REFERENCES "person" ("id"),
    "family_rel_type_id" integer NOT NULL REFERENCES "familyreltype" ("id")
);

source

CREATE TABLE "source" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "title" varchar(50) NOT NULL,
    "author" varchar(50) NOT NULL,
    "pubinfo" varchar(50) NOT NULL,
    "abbrev" varchar(50) NOT NULL
);

event

CREATE TABLE "event" (
    "calendar" integer NOT NULL,
    "modifier" integer NOT NULL,
    "quality" integer NOT NULL,
    "day1" integer NOT NULL,
    "month1" integer NOT NULL,
    "year1" integer NOT NULL,
    "slash1" bool NOT NULL,
    "day2" integer,
    "month2" integer,
    "year2" integer,
    "slash2" bool,
    "text" varchar(80) NOT NULL,
    "sortval" integer NOT NULL,
    "newyear" integer NOT NULL,
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "event_type_id" integer NOT NULL REFERENCES "eventtype" ("id"),
    "description" varchar(50) NOT NULL,
    "place_id" integer
);

repository

CREATE TABLE "repository" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "repository_type_id" integer NOT NULL REFERENCES "repositorytype" ("id"),
    "name" text NOT NULL
);

place

CREATE TABLE "place" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "title" text NOT NULL,
    "long" text NOT NULL,
    "lat" text NOT NULL
);

media

CREATE TABLE "media" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "path" text NOT NULL,
    "mime" text NOT NULL,
    "desc" text NOT NULL
);

note

CREATE TABLE "note" (
    "id" integer NOT NULL PRIMARY KEY,
    "handle" varchar(19) NOT NULL UNIQUE,
    "gramps_id" varchar(25) NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
    "note_type_id" integer NOT NULL REFERENCES "notetype" ("id"),
    "text" text NOT NULL,
    "preformatted" bool NOT NULL
);

name

CREATE TABLE "name" (
    "id" integer NOT NULL PRIMARY KEY,
    "calendar" integer NOT NULL,
    "modifier" integer NOT NULL,
    "quality" integer NOT NULL,
    "day1" integer NOT NULL,
    "month1" integer NOT NULL,
    "year1" integer NOT NULL,
    "slash1" bool NOT NULL,
    "day2" integer,
    "month2" integer,
    "year2" integer,
    "slash2" bool,
    "text" varchar(80) NOT NULL,
    "sortval" integer NOT NULL,
    "newyear" integer NOT NULL,
    "private" bool NOT NULL,
    "last_changed" datetime NOT NULL,
    "order" integer unsigned NOT NULL,
    "name_type_id" integer NOT NULL REFERENCES "nametype" ("id"),
    "primary_name" bool NOT NULL,
    "first_name" text NOT NULL,
    "surname" text NOT NULL,
    "suffix" text NOT NULL,
    "title" text NOT NULL,
    "prefix" text NOT NULL,
    "patronymic" text NOT NULL,
    "call" text NOT NULL,
    "group_as" text NOT NULL,
    "sort_as" integer NOT NULL,
    "display_as" integer NOT NULL
);

lds

CREATE TABLE "lds" (
    "id" integer NOT NULL PRIMARY KEY,
    "private" bool NOT NULL,
    "last_changed" datetime NOT NULL,
    "lds_type_id" integer NOT NULL REFERENCES "ldstype" ("id"),
    "place_id" integer REFERENCES "place" ("id"),
    "famc_id" integer REFERENCES "family" ("id"),
    "temple" text NOT NULL,
    "status_id" integer NOT NULL REFERENCES "ldsstatus" ("id")
);

markup

CREATE TABLE "markup" (
    "id" integer NOT NULL PRIMARY KEY,
    "note_id" integer NOT NULL REFERENCES "note" ("id"),
    "order" integer unsigned NOT NULL,
    "string" text NOT NULL,
    "start_stop_list" text NOT NULL
);

address

CREATE TABLE "address" (
    "id" integer NOT NULL PRIMARY KEY,
    "calendar" integer NOT NULL,
    "modifier" integer NOT NULL,
    "quality" integer NOT NULL,
    "day1" integer NOT NULL,
    "month1" integer NOT NULL,
    "year1" integer NOT NULL,
    "slash1" bool NOT NULL,
    "day2" integer,
    "month2" integer,
    "year2" integer,
    "slash2" bool,
    "text" varchar(80) NOT NULL,
    "sortval" integer NOT NULL,
    "newyear" integer NOT NULL,
    "private" bool NOT NULL,
    "last_changed" datetime NOT NULL
);

location

CREATE TABLE "location" (
    "id" integer NOT NULL PRIMARY KEY,
    "street" text NOT NULL,
    "city" text NOT NULL,
    "county" text NOT NULL,
    "state" text NOT NULL,
    "country" text NOT NULL,
    "postal" text NOT NULL,
    "phone" text NOT NULL,
    "parish" text,
    "order" integer unsigned NOT NULL
);

noteref

CREATE TABLE "noteref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "note" ("id")
);

sourceref

CREATE TABLE "sourceref" (
    "id" integer NOT NULL PRIMARY KEY,
    "calendar" integer NOT NULL,
    "modifier" integer NOT NULL,
    "quality" integer NOT NULL,
    "day1" integer NOT NULL,
    "month1" integer NOT NULL,
    "year1" integer NOT NULL,
    "slash1" bool NOT NULL,
    "day2" integer,
    "month2" integer,
    "year2" integer,
    "slash2" bool,
    "text" varchar(80) NOT NULL,
    "sortval" integer NOT NULL,
    "newyear" integer NOT NULL,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "source" ("id"),
    "page" varchar(50) NOT NULL,
    "confidence" integer NOT NULL
);

eventref

CREATE TABLE "eventref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "event" ("id"),
    "role_type_id" integer NOT NULL REFERENCES "eventroletype" ("id")
);

repositoryref

CREATE TABLE "repositoryref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "repository" ("id"),
    "source_media_type_id" integer NOT NULL REFERENCES "sourcemediatype" ("id"),
    "call_number" varchar(50) NOT NULL
);

personref

CREATE TABLE "personref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id"),
    "description" varchar(50) NOT NULL
);

childref

CREATE TABLE "childref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "father_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
    "mother_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id")
);

mediaref

CREATE TABLE "mediaref" (
    "id" integer NOT NULL PRIMARY KEY,
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
    "object_id" integer unsigned NOT NULL,
    "order" integer unsigned NOT NULL,
    "last_changed" datetime NOT NULL,
    "private" bool NOT NULL,
    "x1" integer NOT NULL,
    "y1" integer NOT NULL,
    "x2" integer NOT NULL,
    "y2" integer NOT NULL,
    "ref_object_id" integer NOT NULL REFERENCES "media" ("id")
);

person

CREATE TABLE "person_names" (
    "id" integer NOT NULL PRIMARY KEY,
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
    "name_id" integer NOT NULL REFERENCES "name" ("id"),
    UNIQUE ("person_id", "name_id")
);

person

CREATE TABLE "person_families" (
    "id" integer NOT NULL PRIMARY KEY,
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
    "family_id" integer NOT NULL REFERENCES "family" ("id"),
    UNIQUE ("person_id", "family_id")
);

person

CREATE TABLE "person_parent_families" (
    "id" integer NOT NULL PRIMARY KEY,
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
    "family_id" integer NOT NULL REFERENCES "family" ("id"),
    UNIQUE ("person_id", "family_id")
);

person

CREATE TABLE "person_addresses" (
    "id" integer NOT NULL PRIMARY KEY,
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
    UNIQUE ("person_id", "address_id")
);

repository

CREATE TABLE "repository_addresses" (
    "id" integer NOT NULL PRIMARY KEY,
    "repository_id" integer NOT NULL REFERENCES "repository" ("id"),
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
    UNIQUE ("repository_id", "address_id")
);

place

CREATE TABLE "place_locations" (
    "id" integer NOT NULL PRIMARY KEY,
    "place_id" integer NOT NULL REFERENCES "place" ("id"),
    "location_id" integer NOT NULL REFERENCES "location" ("id"),
    UNIQUE ("place_id", "location_id")
);

address

CREATE TABLE "address_locations" (
    "id" integer NOT NULL PRIMARY KEY,
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
    "location_id" integer NOT NULL REFERENCES "location" ("id"),
    UNIQUE ("address_id", "location_id")
);