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

There are two versions of exporting a SQL database:

An ExportDjango/ImportDjango version - The ExportDjango/ImportDjango is the latest SQL design, and you can access the plugins through Third-party Plugins. The newer ExportDjango in SVN in /master/tree/gramps/webapp/, src/data/templates/ and /src/data/javascript/.

An ExportSql/ImportSql version. - You can access the older functionality through the ExportSql and ImportSql programs of the Third-party Plugins.

Sample usage

Newer Version

The ExportDjango/ImportDjango version plugins are the latest SQL design.

You can run the ExportDjango plugin in gramps32 once you have the setup finished.

After initial Django setup in the README in in branch, you can:

python src/gramps.py -O "My Family Tree" -e ignored.django

This will export the entire contents of your database (not including bookmarks, gramplets, nor any meta data, etc.) into your database.

Exporting a SQL database can take up to 2 seconds per record to output.

Older version

The is about the older ExportSql/ImportSql plugins.

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

Warning

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 access your database (postgresql, sqlite, etc). For example, on Linux with sqlite:

$ 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.

Database Structure

This section describes the latest SQL schema in the SVN branch.

The Gramps BSDDB data is broken up into the following 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').

The schema is created in the /trunk/gramps/webapp/grampsdb/models.py file as Python code.

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

CREATE 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"),
    "preferred" 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")
);

Gramps β

Gramps SQL Database

Contents

Gramps SQL database Overview

Sample usage

Newer Version

Older version

External Access

Database Structure

markertype

nametype

attributetype

urltype

childreftype

repositorytype

eventtype

familyreltype

sourcemediatype

eventroletype

notetype

gendertype

ldstype

ldsstatus

config

person

family

source

event

repository

place

media

note

name

lds

markup

address

location

noteref

sourceref

eventref

repositoryref

personref

childref

mediaref

person

person

person

person

repository

place

address

Gramps ^β