Revision as of 01:36, 10 August 2009

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. Importing a SQL database can take up to 1 second a record to read in.

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

address

CREATE TABLE address (
      handle  CHARACTER(25) PRIMARY KEY,
      private BOOLEAN);

attribute

CREATE TABLE attribute (
       handle    CHARACTER(25) PRIMARY KEY,
       the_type0 INTEGER, 
       the_type1 TEXT, 
       value     TEXT, 
       private   BOOLEAN);

child_ref

CREATE TABLE child_ref (
       handle   CHARACTER(25) PRIMARY KEY,
       ref      CHARACTER(25), 
       frel0    INTEGER,
       frel1    CHARACTER(25),
       mrel0    INTEGER,
       mrel1    CHARACTER(25),
       private  BOOLEAN);

datamap

CREATE TABLE datamap (
       handle      CHARACTER(25) PRIMARY KEY,
       key_field   TEXT, 
       value_field TXT);

date

CREATE TABLE date (
        handle     CHARACTER(25) PRIMARY KEY,
        calendar   INTEGER, 
        modifier   INTEGER, 
        quality    INTEGER,
        day1       INTEGER, 
        month1     INTEGER, 
        year1      INTEGER, 
        slash1     BOOLEAN,
        day2       INTEGER, 
        month2     INTEGER, 
        year2      INTEGER, 
        slash2     BOOLEAN,
        text       TEXT, 
        sortval    INTEGER, 
        newyear    INTEGER);

event

CREATE TABLE event (
       handle      CHARACTER(25) PRIMARY KEY,
       gid         CHARACTER(25), 
       the_type0   INTEGER, 
       the_type1   TEXT, 
       description TEXT, 
       change      INTEGER, 
       marker0     INTEGER, 
       marker1     TEXT, 
       private     BOOLEAN);

event_ref

CREATE TABLE event_ref (
       handle  CHARACTER(25) PRIMARY KEY,
       ref     CHARACTER(25), 
       role0   INTEGER, 
       role1   TEXT, 
       private BOOLEAN);

family

CREATE TABLE family (
       handle CHARACTER(25) PRIMARY KEY,
       gid CHARACTER(25), 
       father_handle CHARACTER(25), 
       mother_handle CHARACTER(25), 
       the_type0 INTEGER, 
       the_type1 TEXT, 
       change INTEGER, 
       marker0 INTEGER, 
       marker1 TEXT, 
       private BOOLEAN);

lds

CREATE TABLE lds (
       handle CHARACTER(25) PRIMARY KEY,
       type INTEGER, 
       place CHARACTER(25), 
       famc CHARACTER(25), 
       temple TEXT, 
       status INTEGER, 
       private BOOLEAN);

link

CREATE TABLE link (
       from_type CHARACTER(25), 
       from_handle CHARACTER(25), 
       to_type CHARACTER(25), 
       to_handle CHARACTER(25));

CREATE INDEX idx_link_to ON 
       link(from_type, from_handle, to_type);

location

CREATE TABLE location (
       handle CHARACTER(25) PRIMARY KEY,
       street TEXT, 
       city TEXT, 
       county TEXT, 
       state TEXT, 
       country TEXT, 
       postal TEXT, 
       phone TEXT,
       parish TEXT);

markup

CREATE TABLE markup (
       handle CHARACTER(25) PRIMARY KEY,
       markup0 INTEGER, 
       markup1 TEXT, 
       value TEXT, 
       start_stop_list TEXT);

media

CREATE TABLE media (
       handle CHARACTER(25) PRIMARY KEY,
       gid CHARACTER(25), 
       path TEXT, 
       mime TEXT, 
       desc TEXT,
       change INTEGER, 
       marker0 INTEGER, 
       marker1 TEXT, 
       private BOOLEAN);

media_ref

CREATE TABLE media_ref (
       handle CHARACTER(25) PRIMARY KEY,
       ref CHARACTER(25),
       role0 INTEGER,
       role1 INTEGER,
       role2 INTEGER,
       role3 INTEGER,
       private BOOLEAN);

name

CREATE TABLE name (
        handle CHARACTER(25) PRIMARY KEY,
        primary_name BOOLEAN,
        private BOOLEAN, 
        first_name TEXT, 
        surname TEXT, 
        suffix TEXT, 
        title TEXT, 
        name_type0 INTEGER, 
        name_type1 TEXT, 
        prefix TEXT, 
        patronymic TEXT, 
        group_as TEXT, 
        sort_as INTEGER,
        display_as INTEGER, 
        call TEXT);

note

CREATE TABLE note (
        handle CHARACTER(25) PRIMARY KEY,
        gid    CHARACTER(25),
        text   TEXT,
        format INTEGER,
        note_type1   INTEGER,
        note_type2   TEXT,
        change INTEGER,
        marker0 INTEGER,
        marker1 TEXT,
        private BOOLEAN);

person

CREATE TABLE person (
        handle CHARACTER(25) PRIMARY KEY,
        gid CHARACTER(25), 
        gender INTEGER, 
        death_ref_handle TEXT, 
        birth_ref_handle TEXT, 
        change INTEGER, 
        marker0 INTEGER, 
        marker1 TEXT, 
        private BOOLEAN);

person_ref

CREATE TABLE person_ref (
       handle CHARACTER(25) PRIMARY KEY,
       description TEXT,
       private BOOLEAN);

place

CREATE TABLE place (
       handle CHARACTER(25) PRIMARY KEY,
       gid CHARACTER(25), 
       title TEXT, 
       main_location CHARACTER(25),
       long TEXT, 
       lat TEXT, 
       change INTEGER, 
       marker0 INTEGER, 
       marker1 TEXT, 
       private BOOLEAN);

repository

CREATE TABLE repository_ref (
       handle CHARACTER(25) PRIMARY KEY,
       ref CHARACTER(25), 
       call_number TEXT, 
       source_media_type0 INTEGER,
       source_media_type1 TEXT,
       private BOOLEAN);

repository_ref

CREATE TABLE repository (
       handle CHARACTER(25) PRIMARY KEY,
       gid CHARACTER(25), 
       the_type0 INTEGER, 
       the_type1 TEXT,
       name TEXT, 
       change INTEGER, 
       marker0 INTEGER, 
       marker1 TEXT, 
       private BOOLEAN);

source

CREATE TABLE source (
       handle CHARACTER(25) PRIMARY KEY,
       gid CHARACTER(25), 
       title TEXT, 
       author TEXT, 
       pubinfo TEXT, 
       abbrev TEXT, 
       change INTEGER,
       marker0 INTEGER, 
       marker1 TEXT, 
       private BOOLEAN);

source_ref

CREATE TABLE source_ref (
       handle CHARACTER(25) PRIMARY KEY,
       ref CHARACTER(25), 
       confidence INTEGER,
       page CHARACTER(25),
       private BOOLEAN);

url

CREATE TABLE url (
       handle CHARACTER(25) PRIMARY KEY,
       path TEXT, 
       desc TXT, 
       type0 INTEGER,
       type1 TEXT,                  
       private BOOLEAN);

Difference between revisions of "Gramps SQL Database"