SQL Schema
Contents
Tables
person
CREATE TABLE person( handle VARCHAR(50) PRIMARY KEY NOT NULL, given_name TEXT, surname TEXT, order_by TEXT, gramps_id TEXT, blob_data BLOB, private INTEGER, change INTEGER, birth_ref_index INTEGER, gender INTEGER, death_ref_index INTEGER );
family
CREATE TABLE family( handle VARCHAR(50) PRIMARY KEY NOT NULL, father_handle VARCHAR(50), mother_handle VARCHAR(50), gramps_id TEXT, blob_data BLOB, change INTEGER, private INTEGER );
source
CREATE TABLE source( handle VARCHAR(50) PRIMARY KEY NOT NULL, order_by TEXT, gramps_id TEXT, blob_data BLOB, author TEXT, private INTEGER, title TEXT, pubinfo TEXT, change INTEGER, abbrev TEXT );
citation
CREATE TABLE citation( handle VARCHAR(50) PRIMARY KEY NOT NULL, order_by TEXT, gramps_id TEXT, blob_data BLOB, page TEXT, source_handle VARCHAR(50), private INTEGER, confidence TEXT, change INTEGER );
event
CREATE TABLE event( handle VARCHAR(50) PRIMARY KEY NOT NULL, gramps_id TEXT, blob_data BLOB, private INTEGER, description TEXT, place VARCHAR(50), change INTEGER );
media
CREATE TABLE media( handle VARCHAR(50) PRIMARY KEY NOT NULL, order_by TEXT, gramps_id TEXT, blob_data BLOB, private INTEGER, mime TEXT, change INTEGER, path TEXT, checksum TEXT, desc TEXT );
place
CREATE TABLE place( handle VARCHAR(50) PRIMARY KEY NOT NULL, order_by TEXT, gramps_id TEXT, blob_data BLOB, lat TEXT, code TEXT, title TEXT, private INTEGER, change INTEGER, long TEXT );
repository
CREATE TABLE repository( handle VARCHAR(50) PRIMARY KEY NOT NULL, gramps_id TEXT, blob_data BLOB, name TEXT, change INTEGER, private INTEGER );
note
CREATE TABLE note( handle VARCHAR(50) PRIMARY KEY NOT NULL, gramps_id TEXT, blob_data BLOB, format INTEGER, change INTEGER, private INTEGER );
tag
CREATE TABLE tag( handle VARCHAR(50) PRIMARY KEY NOT NULL, order_by TEXT, blob_data BLOB, color TEXT, change INTEGER, priority INTEGER, name TEXT );
reference
CREATE TABLE reference( obj_handle VARCHAR(50), obj_class TEXT, ref_handle VARCHAR(50), ref_class TEXT );
name_group
CREATE TABLE name_group( name VARCHAR(50) PRIMARY KEY NOT NULL, grouping TEXT );
metadata
CREATE TABLE metadata( setting VARCHAR(50) PRIMARY KEY NOT NULL, value BLOB );
gender_stats
CREATE TABLE gender_stats( given_name TEXT, female INTEGER, male INTEGER, unknown INTEGER );
Indices
CREATE INDEX person_order_by ON person(order_by);
CREATE INDEX person_gramps_id ON person(gramps_id);
CREATE INDEX person_surname ON person(surname);
CREATE INDEX person_given_name ON person(given_name);
CREATE INDEX source_order_by ON source(order_by);
CREATE INDEX source_gramps_id ON source(gramps_id);
CREATE INDEX citation_order_by ON citation(order_by);
CREATE INDEX citation_gramps_id ON citation(gramps_id);
CREATE INDEX media_order_by ON media(order_by);
CREATE INDEX media_gramps_id ON media(gramps_id);
CREATE INDEX place_order_by ON place(order_by);
CREATE INDEX place_gramps_id ON place(gramps_id);
CREATE INDEX tag_order_by ON tag(order_by);
CREATE INDEX reference_ref_handle ON reference(ref_handle);
CREATE INDEX family_gramps_id ON family(gramps_id);
CREATE INDEX event_gramps_id ON event(gramps_id);
CREATE INDEX repository_gramps_id ON repository(gramps_id);
CREATE INDEX note_gramps_id ON note(gramps_id);
CREATE INDEX reference_obj_handle ON reference(obj_handle);
See also
- QueryGramplet
- Gramps-devel Gramps database design, From: Nick Hall - 2016-10-03 18:59:37 which mentions 9392 Do we need to pickle DB-API blobs?
Because Documention about data structures becomes quickly outdated, the following cross-references have a parenthetical notation of the vintage - plus the most recent major revision.
- Gramps Data Model (v4.1) - version 4.1 data diagram (2014)
- GEPS 032: Database Backend API (proposed 2013, implemented 2015)
- GEPS 010: SQL Backend/Relational Backend (proposed 2008, superseded 2013)
- SQLite Export Import - a third party addon which expands the Family Trees ➡ Import…/Export… menu options (2009, revised 2019)
- Gramps SQL Database - documents the format of a SQL database version of the Gramps BSDDB datastore. (2009, revised 2015)
- Pickle Python Tutorial - not specific to Gramps (2018)