Gramps SQL Database

From Gramps
Revision as of 13:27, 9 August 2009 by Dsblank (Talk | contribs) (External Access)

Jump to: navigation, search

This page documents the format of a SQL database version of the GRAMPS BSDDB datastore. This can be seen as a step towards a SQL backend for GRAMPS, and for supplying data to other applications, such as a 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 it 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