Difference between revisions of "Gramps SQL Database"

From Gramps
Jump to: navigation, search
m
 
(33 intermediate revisions by 4 users not shown)
Line 1: Line 1:
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]].
+
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 =
+
= 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 [http://sourceforge.net/p/gramps/source/ci/master/tree/gramps/webapp/ /master/tree/gramps/webapp/], [http://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/data/templates/ src/data/templates/] and [http://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/data/javascript/ /src/data/javascript/].
 +
 
 +
*An ExportSql/ImportSql version. - You can access the older functionality through the ''ExportSql'' and ''ImportSql'' programs of the [[Third-party Plugins]].
  
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 ==
 
== 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 [[Addon:SQLite Export Import|ExportSql/ImportSql]] plugins.
  
 
After dropping the above plugins into your .gramps/plugins subdirectory, you can:
 
After dropping the above plugins into your .gramps/plugins subdirectory, you can:
Line 11: Line 30:
 
  python src/gramps.py -O "My Family Tree" -e familytree.sql
 
  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:
+
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
 
  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.
+
{{man warn|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. Importing a SQL database can take up to 1 second a record to read in.
+
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 ==
 
== External Access ==
  
After exporting, you can then access your data using any program that can read sqlite files. For example, on Linux:
+
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:
  
 
<pre>
 
<pre>
Line 35: Line 54:
 
date            link            name            repository     
 
date            link            name            repository     
 
sqlite> .headers on
 
sqlite> .headers on
 +
sqlite> .mode columns
 
sqlite> select * from person;
 
sqlite> select * from person;
handle            |gid            |gender|death_ref_handle   |birth_ref_handle   |change         |marker0|marker1 |private
+
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
+
b5dc6d9aa5766513709 I0010           0     b5dc6d9aa07279205ad b5dc6d9aa3e7b41b0f1 1249739601 1       Complete 1
b5dc6d9cd9c134a0c39|I0017         |1     |                  |                  |1249739602     |-1     |        |0
+
b5dc6d9add708e5ba9e Amle achba     0     b5dc6d9adc539e3085e                     1249739601 2       ToDo     0
b5dc6d9dfd3719d4e00|imgoabim Miulka|1     |b5dc6d9dfca6a342e45|                  |1249739603     |1     |Complete|0
+
b5dc6d9cd9c134a0c39 I0017           1                                             1249739602 -1               0
b5dc6d9f38779e2275b|I0024         |0     |                  |b5dc6d9f37b685b9607|1249739603     |-1     |        |0
+
b5dc6d9dfd3719d4e00 imgoabim Miulka 1     b5dc6d9dfca6a342e45                     1249739603 1       Complete 0
 +
b5dc6d9f38779e2275b I0024           0                         b5dc6d9f37b685b9607 1249739603 -1               0
 
...
 
...
 
</pre>
 
</pre>
  
If you change the data, you can then read it back in and GRAMPS will reflect the changes.  
+
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.
+
{{man note|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 =
 
= 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).
+
''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 [http://svn.code.sf.net/p/gramps/code/trunk/gramps/webapp/grampsdb/models.py /trunk/gramps/webapp/grampsdb/models.py] file as Python code.
 +
 
 +
== markertype ==
 +
 
 +
<pre>
 +
CREATE TABLE "markertype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== nametype ==
 +
 
 +
<pre>
 +
CREATE TABLE "nametype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== attributetype ==
 +
 
 +
<pre>
 +
CREATE TABLE "attributetype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== urltype ==
 +
 
 +
<pre>
 +
CREATE TABLE "urltype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== childreftype ==
 +
 
 +
<pre>
 +
CREATE TABLE "childreftype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== repositorytype ==
 +
 
 +
<pre>
 +
CREATE TABLE "repositorytype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== eventtype ==
 +
 
 +
<pre>
 +
CREATE TABLE "eventtype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== familyreltype ==
 +
 
 +
<pre>
 +
CREATE TABLE "familyreltype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== sourcemediatype ==
 +
 
 +
<pre>
 +
CREATE TABLE "sourcemediatype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== eventroletype ==
 +
 
 +
<pre>
 +
CREATE TABLE "eventroletype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== notetype ==
 +
 
 +
<pre>
 +
CREATE TABLE "notetype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== gendertype ==
 +
 
 +
<pre>
 +
CREATE TABLE "gendertype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
  
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 a from_type and from_handle to a to_type and to_handle.
+
== ldstype ==
  
== address ==
+
<pre>
 +
CREATE TABLE "ldstype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== ldsstatus ==
  
 
<pre>
 
<pre>
CREATE TABLE address (
+
CREATE TABLE "ldsstatus" (
      handle  CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      private BOOLEAN);
+
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 
</pre>
 
</pre>
  
== attribute ==
+
== config ==
  
 
<pre>
 
<pre>
CREATE TABLE attribute (
+
CREATE TABLE "config" (
      handle    CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      the_type0 INTEGER,
+
    "db_version" varchar(25) NOT NULL,
      the_type1 TEXT,
+
     "created" datetime NOT NULL
      value     TEXT,
+
);
      private  BOOLEAN);
 
 
</pre>
 
</pre>
  
== child_ref ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE child_ref (
+
CREATE TABLE "person" (
      handle   CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref      CHARACTER(25),  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      frel0    INTEGER,
+
    "gramps_id" varchar(25) NOT NULL,
      frel1    CHARACTER(25),
+
    "last_changed" datetime NOT NULL,
      mrel0    INTEGER,
+
    "private" bool NOT NULL,
      mrel1    CHARACTER(25),
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      private  BOOLEAN);
+
    "gender_type_id" integer NOT NULL REFERENCES "gendertype" ("id")
 +
);
 
</pre>
 
</pre>
  
== datamap ==
+
== family ==
  
 
<pre>
 
<pre>
CREATE TABLE datamap (
+
CREATE TABLE "family" (
      handle     CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      key_field  TEXT,  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      value_field TXT);
+
    "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")
 +
);
 
</pre>
 
</pre>
               
 
  
== date ==
+
== source ==
  
 
<pre>
 
<pre>
CREATE TABLE date (
+
CREATE TABLE "source" (
        handle    CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
        calendar  INTEGER,  
+
    "handle" varchar(19) NOT NULL UNIQUE,
        modifier  INTEGER,  
+
     "gramps_id" varchar(25) NOT NULL,
        quality    INTEGER,
+
    "last_changed" datetime NOT NULL,
        day1      INTEGER,  
+
    "private" bool NOT NULL,
        month1     INTEGER,
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
        year1      INTEGER,  
+
     "title" varchar(50) NOT NULL,
        slash1     BOOLEAN,
+
     "author" varchar(50) NOT NULL,
        day2      INTEGER,
+
     "pubinfo" varchar(50) NOT NULL,
        month2     INTEGER,  
+
     "abbrev" varchar(50) NOT NULL
        year2      INTEGER,
+
);
        slash2     BOOLEAN,
 
        text      TEXT,
 
        sortval    INTEGER,
 
        newyear    INTEGER);
 
 
</pre>
 
</pre>
  
Line 121: Line 278:
  
 
<pre>
 
<pre>
CREATE TABLE event (
+
CREATE TABLE "event" (
      handle     CHARACTER(25),  
+
    "calendar" integer NOT NULL,
      gid        CHARACTER(25),  
+
    "modifier" integer NOT NULL,
      the_type0  INTEGER,  
+
    "quality" integer NOT NULL,
      the_type1  TEXT,  
+
    "day1" integer NOT NULL,
      description TEXT,  
+
    "month1" integer NOT NULL,
      change      INTEGER,  
+
    "year1" integer NOT NULL,
      marker0     INTEGER,  
+
    "slash1" bool NOT NULL,
      marker1     TEXT,  
+
    "day2" integer,
      private    BOOLEAN);
+
    "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
 +
);
 +
</pre>
 +
 
 +
== repository ==
 +
 
 +
<pre>
 +
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
 +
);
 +
</pre>
 +
 
 +
== place ==
 +
 
 +
<pre>
 +
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
 +
);
 +
</pre>
 +
 
 +
== media ==
 +
 
 +
<pre>
 +
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
 +
);
 
</pre>
 
</pre>
  
== event_ref ==
+
== note ==
  
 
<pre>
 
<pre>
CREATE TABLE event_ref (
+
CREATE TABLE "note" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref     CHARACTER(25),  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      role0  INTEGER,  
+
    "gramps_id" varchar(25) NOT NULL,
      role1  TEXT,  
+
    "last_changed" datetime NOT NULL,
      private BOOLEAN);
+
    "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
 +
);
 
</pre>
 
</pre>
  
== family ==
+
== name ==
  
 
<pre>
 
<pre>
CREATE TABLE family (
+
CREATE TABLE "name" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "calendar" integer NOT NULL,
      father_handle CHARACTER(25),  
+
    "modifier" integer NOT NULL,
      mother_handle CHARACTER(25),  
+
    "quality" integer NOT NULL,
      the_type0 INTEGER,  
+
    "day1" integer NOT NULL,
      the_type1 TEXT,  
+
    "month1" integer NOT NULL,
      change INTEGER,  
+
    "year1" integer NOT NULL,
      marker0 INTEGER,  
+
    "slash1" bool NOT NULL,
      marker1 TEXT,  
+
    "day2" integer,
      private BOOLEAN);
+
    "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
 +
);
 
</pre>
 
</pre>
  
Line 163: Line 408:
  
 
<pre>
 
<pre>
CREATE TABLE lds (
+
CREATE TABLE "lds" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      type INTEGER,
+
    "private" bool NOT NULL,
      place CHARACTER(25),  
+
    "last_changed" datetime NOT NULL,
      famc CHARACTER(25),  
+
    "lds_type_id" integer NOT NULL REFERENCES "ldstype" ("id"),
      temple TEXT,  
+
    "place_id" integer REFERENCES "place" ("id"),
      status INTEGER,
+
    "famc_id" integer REFERENCES "family" ("id"),
      private BOOLEAN);
+
    "temple" text NOT NULL,
 +
    "status_id" integer NOT NULL REFERENCES "ldsstatus" ("id")
 +
);
 
</pre>
 
</pre>
  
== link ==
+
== markup ==
  
 
<pre>
 
<pre>
CREATE TABLE link (
+
CREATE TABLE "markup" (
      from_type CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      from_handle CHARACTER(25),  
+
    "note_id" integer NOT NULL REFERENCES "note" ("id"),
      to_type CHARACTER(25),  
+
    "order" integer unsigned NOT NULL,
      to_handle CHARACTER(25));
+
    "string" text NOT NULL,
 +
    "start_stop_list" text NOT NULL
 +
);
 +
</pre>
 +
 
 +
== address ==
 +
 
 +
<pre>
 +
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
 +
);
 
</pre>
 
</pre>
  
Line 186: Line 459:
  
 
<pre>
 
<pre>
CREATE TABLE location (
+
CREATE TABLE "location" (
      handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      street TEXT,  
+
    "street" text NOT NULL,
      city TEXT,  
+
    "city" text NOT NULL,
      county TEXT,  
+
    "county" text NOT NULL,
      state TEXT,  
+
    "state" text NOT NULL,
      country TEXT,  
+
    "country" text NOT NULL,
      postal TEXT,  
+
    "postal" text NOT NULL,
      phone TEXT,
+
    "phone" text NOT NULL,
      parish TEXT);
+
    "parish" text,
 +
    "order" integer unsigned NOT NULL
 +
);
 +
</pre>
 +
 
 +
== noteref ==
 +
 
 +
<pre>
 +
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")
 +
);
 
</pre>
 
</pre>
  
== markup ==
+
== sourceref ==
  
 
<pre>
 
<pre>
CREATE TABLE markup (
+
CREATE TABLE "sourceref" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      markup0 INTEGER,  
+
    "calendar" integer NOT NULL,
      markup1 TEXT,  
+
    "modifier" integer NOT NULL,
      value TEXT,  
+
    "quality" integer NOT NULL,
      start_stop_list TEXT);
+
    "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
 +
);
 
</pre>
 
</pre>
  
== media ==
+
== eventref ==
  
 
<pre>
 
<pre>
CREATE TABLE media (
+
CREATE TABLE "eventref" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      path TEXT,  
+
    "object_id" integer unsigned NOT NULL,
      mime TEXT,  
+
    "order" integer unsigned NOT NULL,
      desc TEXT,
+
    "last_changed" datetime NOT NULL,
      change INTEGER,  
+
    "private" bool NOT NULL,
      marker0 INTEGER,  
+
    "ref_object_id" integer NOT NULL REFERENCES "event" ("id"),
      marker1 TEXT,
+
    "role_type_id" integer NOT NULL REFERENCES "eventroletype" ("id")
      private BOOLEAN);
+
);
 
</pre>
 
</pre>
  
== media_ref ==
+
== repositoryref ==
  
 
<pre>
 
<pre>
CREATE TABLE media_ref (
+
CREATE TABLE "repositoryref" (
      handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      ref CHARACTER(25),
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      role0 INTEGER,
+
    "object_id" integer unsigned NOT NULL,
      role1 INTEGER,
+
    "order" integer unsigned NOT NULL,
      role2 INTEGER,
+
    "last_changed" datetime NOT NULL,
      role3 INTEGER,
+
    "private" bool NOT NULL,
      private BOOLEAN);
+
    "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
 +
);
 
</pre>
 
</pre>
  
== name ==
+
== personref ==
  
 
<pre>
 
<pre>
CREATE TABLE name (
+
CREATE TABLE "personref" (
        handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
        primary_name BOOLEAN,
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
        private BOOLEAN,  
+
    "object_id" integer unsigned NOT NULL,
        first_name TEXT,  
+
    "order" integer unsigned NOT NULL,
        surname TEXT,  
+
    "last_changed" datetime NOT NULL,
        suffix TEXT,  
+
    "private" bool NOT NULL,
        title TEXT,
+
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id"),
        name_type0 INTEGER,
+
    "description" varchar(50) NOT NULL
        name_type1 TEXT,
+
);
        prefix TEXT,
 
        patronymic TEXT,
 
        group_as TEXT,
 
        sort_as INTEGER,
 
        display_as INTEGER,
 
        call TEXT);
 
 
</pre>
 
</pre>
  
== note ==
+
== childref ==
  
 
<pre>
 
<pre>
CREATE TABLE note (
+
CREATE TABLE "childref" (
        handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
        gid    CHARACTER(25),
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
        text  TEXT,
+
    "object_id" integer unsigned NOT NULL,
        format INTEGER,
+
    "order" integer unsigned NOT NULL,
        note_type1  INTEGER,
+
    "last_changed" datetime NOT NULL,
        note_type2  TEXT,
+
    "private" bool NOT NULL,
        change INTEGER,
+
    "father_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
        marker0 INTEGER,
+
    "mother_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
        marker1 TEXT,
+
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id")
        private BOOLEAN);
+
);
 
</pre>
 
</pre>
  
== person ==
+
== mediaref ==
  
 
<pre>
 
<pre>
CREATE TABLE person (
+
CREATE TABLE "mediaref" (
        handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
        gid CHARACTER(25),  
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
        gender INTEGER,  
+
    "object_id" integer unsigned NOT NULL,
        death_ref_handle TEXT,  
+
    "order" integer unsigned NOT NULL,
        birth_ref_handle TEXT,  
+
    "last_changed" datetime NOT NULL,
        change INTEGER,  
+
    "private" bool NOT NULL,
        marker0 INTEGER,  
+
    "x1" integer NOT NULL,
        marker1 TEXT,  
+
    "y1" integer NOT NULL,
        private BOOLEAN);
+
    "x2" integer NOT NULL,
 +
    "y2" integer NOT NULL,
 +
    "ref_object_id" integer NOT NULL REFERENCES "media" ("id")
 +
);
 
</pre>
 
</pre>
  
== person_ref ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE person_ref (
+
CREATE TABLE "person_names" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      description TEXT,
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      private BOOLEAN);
+
    "name_id" integer NOT NULL REFERENCES "name" ("id"),
 +
    UNIQUE ("person_id", "name_id")
 +
);
 
</pre>
 
</pre>
  
== place ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE place (
+
CREATE TABLE "person_families" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      title TEXT,
+
    "family_id" integer NOT NULL REFERENCES "family" ("id"),
      main_location CHARACTER(25),
+
    UNIQUE ("person_id", "family_id")
      long TEXT,
+
);
      lat TEXT,
 
      change INTEGER,
 
      marker0 INTEGER,
 
      marker1 TEXT,  
 
      private BOOLEAN);
 
 
</pre>
 
</pre>
  
== repository ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE repository_ref (
+
CREATE TABLE "person_parent_families" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      call_number TEXT,
+
    "family_id" integer NOT NULL REFERENCES "family" ("id"),
      source_media_type0 INTEGER,
+
    UNIQUE ("person_id", "family_id")
      source_media_type1 TEXT,
+
);
      private BOOLEAN);
 
 
</pre>
 
</pre>
  
== repository_ref ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE repository (
+
CREATE TABLE "person_addresses" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      the_type0 INTEGER,
+
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
      the_type1 TEXT,
+
    UNIQUE ("person_id", "address_id")
      name TEXT,
+
);
      change INTEGER,
 
      marker0 INTEGER,
 
      marker1 TEXT,  
 
      private BOOLEAN);
 
 
</pre>
 
</pre>
  
== source ==
+
== repository ==
  
 
<pre>
 
<pre>
CREATE TABLE source (
+
CREATE TABLE "repository_addresses" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "repository_id" integer NOT NULL REFERENCES "repository" ("id"),
      title TEXT,
+
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
      author TEXT,
+
    UNIQUE ("repository_id", "address_id")
      pubinfo TEXT,
+
);
      abbrev TEXT,
 
      change INTEGER,
 
      marker0 INTEGER,
 
      marker1 TEXT,  
 
      private BOOLEAN);
 
 
</pre>
 
</pre>
  
== source_ref ==
+
== place ==
  
 
<pre>
 
<pre>
CREATE TABLE source_ref (
+
CREATE TABLE "place_locations" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref CHARACTER(25),  
+
    "place_id" integer NOT NULL REFERENCES "place" ("id"),
      confidence INTEGER,
+
    "location_id" integer NOT NULL REFERENCES "location" ("id"),
      page CHARACTER(25),
+
    UNIQUE ("place_id", "location_id")
      private BOOLEAN);
+
);
 
</pre>
 
</pre>
  
== url ==
+
== address ==
  
 
<pre>
 
<pre>
CREATE TABLE url (
+
CREATE TABLE "address_locations" (
      handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      path TEXT,  
+
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
      desc TXT,  
+
    "location_id" integer NOT NULL REFERENCES "location" ("id"),
      type0 INTEGER,
+
    UNIQUE ("address_id", "location_id")
      type1 TEXT,                 
+
);
      private BOOLEAN);
 
 
</pre>
 
</pre>
 +
 +
 +
[[Category:GEPS|S]]

Latest revision as of 00:21, 31 January 2022

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 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
Gnome-important.png
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.

Gramps-notes.png
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")
);