Difference between revisions of "Gramps SQL Database"

From Gramps
Jump to: navigation, search
(Sample usage)
m (Gramps)
(20 intermediate revisions by 3 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 =
  
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]].
+
There are two versions of exporting a SQL database: an ExportDjango version and an ExportSql/ImportSql version. Currently, the ExportDjango is the latest SQL design. You can access the older functionality through the ''ExportSql'' and ''ImportSql'' programs of the [[Third-party Plugins]] and the newer ExportDjango in SVN in [http://svn.code.sf.net/p/gramps/code/trunk/gramps/webapp/ /trunk/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/].
  
== Sample usage ==
+
== Older version sample usage ==
  
 
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 17: Line 17:
 
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.
 
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.
+
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.
 +
 
 +
== Newer Version ==
 +
 
 +
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. There does not yet exist an ImportDjango, but hopefully later today...
 +
 
 +
Exporting a SQL database can take up to 2 seconds per record to output.
  
 
== 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 47:
 
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.
+
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.''
  
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.
+
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').
  
== address ==
+
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>
 
<pre>
CREATE TABLE address (
+
CREATE TABLE "markertype" (
      handle  CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      private BOOLEAN);
+
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 
</pre>
 
</pre>
  
== attribute ==
+
== nametype ==
  
 
<pre>
 
<pre>
CREATE TABLE attribute (
+
CREATE TABLE "nametype" (
      handle    CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      the_type0 INTEGER,
+
    "name" varchar(40) NOT NULL,
      the_type1 TEXT,
+
     "val" integer NOT NULL
      value     TEXT,
+
);
      private  BOOLEAN);
+
 
</pre>
 
</pre>
  
== child_ref ==
+
== attributetype ==
  
 
<pre>
 
<pre>
CREATE TABLE child_ref (
+
CREATE TABLE "attributetype" (
      handle  CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref      CHARACTER(25),  
+
    "name" varchar(40) NOT NULL,
      frel0    INTEGER,
+
    "val" integer NOT NULL
      frel1    CHARACTER(25),
+
);
      mrel0    INTEGER,
+
      mrel1    CHARACTER(25),
+
      private  BOOLEAN);
+
 
</pre>
 
</pre>
  
== datamap ==
+
== urltype ==
  
 
<pre>
 
<pre>
CREATE TABLE datamap (
+
CREATE TABLE "urltype" (
      handle      CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      key_field  TEXT,
+
    "name" varchar(40) NOT NULL,
      value_field TXT);
+
    "val" integer NOT NULL
 +
);
 
</pre>
 
</pre>
               
 
  
== date ==
+
== childreftype ==
  
 
<pre>
 
<pre>
CREATE TABLE date (
+
CREATE TABLE "childreftype" (
        handle     CHARACTER(25),
+
     "id" integer NOT NULL PRIMARY KEY,
        calendar  INTEGER,
+
     "name" varchar(40) NOT NULL,
        modifier  INTEGER,
+
     "val" integer NOT NULL
        quality    INTEGER,
+
);
        day1      INTEGER,
+
        month1     INTEGER,  
+
        year1      INTEGER,
+
        slash1     BOOLEAN,
+
        day2      INTEGER,
+
        month2    INTEGER,
+
        year2      INTEGER,
+
        slash2    BOOLEAN,
+
        text      TEXT,
+
        sortval    INTEGER,
+
        newyear    INTEGER);
+
 
</pre>
 
</pre>
  
== event ==
+
== repositorytype ==
  
 
<pre>
 
<pre>
CREATE TABLE event (
+
CREATE TABLE "repositorytype" (
      handle      CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid        CHARACTER(25),  
+
    "name" varchar(40) NOT NULL,
      the_type0  INTEGER,
+
     "val" integer NOT NULL
      the_type1  TEXT,
+
);
      description TEXT,
+
      change      INTEGER,
+
      marker0     INTEGER,
+
      marker1    TEXT,
+
      private    BOOLEAN);
+
 
</pre>
 
</pre>
  
== event_ref ==
+
== eventtype ==
  
 
<pre>
 
<pre>
CREATE TABLE event_ref (
+
CREATE TABLE "eventtype" (
      handle  CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref     CHARACTER(25),  
+
    "name" varchar(40) NOT NULL,
      role0  INTEGER,  
+
     "val" integer NOT NULL
      role1  TEXT,  
+
);
      private BOOLEAN);
+
</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>
 +
 
 +
== ldstype ==
 +
 
 +
<pre>
 +
CREATE TABLE "ldstype" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== ldsstatus ==
 +
 
 +
<pre>
 +
CREATE TABLE "ldsstatus" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "name" varchar(40) NOT NULL,
 +
    "val" integer NOT NULL
 +
);
 +
</pre>
 +
 
 +
== config ==
 +
 
 +
<pre>
 +
CREATE TABLE "config" (
 +
    "id" integer NOT NULL PRIMARY KEY,
 +
    "db_version" varchar(25) NOT NULL,
 +
    "created" datetime NOT NULL
 +
);
 +
</pre>
 +
 
 +
== person ==
 +
 
 +
<pre>
 +
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")
 +
);
 
</pre>
 
</pre>
  
Line 147: Line 238:
  
 
<pre>
 
<pre>
CREATE TABLE family (
+
CREATE TABLE "family" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      father_handle CHARACTER(25),  
+
    "gramps_id" varchar(25) NOT NULL,
      mother_handle CHARACTER(25),  
+
    "last_changed" datetime NOT NULL,
      the_type0 INTEGER,  
+
    "private" bool NOT NULL,
      the_type1 TEXT,  
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      change INTEGER,  
+
    "father_id" integer REFERENCES "person" ("id"),
      marker0 INTEGER,  
+
    "mother_id" integer REFERENCES "person" ("id"),
      marker1 TEXT,
+
    "family_rel_type_id" integer NOT NULL REFERENCES "familyreltype" ("id")
      private BOOLEAN);
+
);
 
</pre>
 
</pre>
  
== lds ==
+
== source ==
  
 
<pre>
 
<pre>
CREATE TABLE lds (
+
CREATE TABLE "source" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      type INTEGER,  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      place CHARACTER(25),  
+
    "gramps_id" varchar(25) NOT NULL,
      famc CHARACTER(25),  
+
    "last_changed" datetime NOT NULL,
      temple TEXT,  
+
    "private" bool NOT NULL,
      status INTEGER,  
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      private BOOLEAN);
+
    "title" varchar(50) NOT NULL,
 +
    "author" varchar(50) NOT NULL,
 +
    "pubinfo" varchar(50) NOT NULL,
 +
    "abbrev" varchar(50) NOT NULL
 +
);
 
</pre>
 
</pre>
  
== link ==
+
== event ==
  
 
<pre>
 
<pre>
CREATE TABLE link (
+
CREATE TABLE "event" (
      from_type CHARACTER(25),  
+
    "calendar" integer NOT NULL,
      from_handle CHARACTER(25),  
+
    "modifier" integer NOT NULL,
      to_type CHARACTER(25),  
+
    "quality" integer NOT NULL,
      to_handle CHARACTER(25));
+
    "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
 +
);
 
</pre>
 
</pre>
  
== location ==
+
== repository ==
  
 
<pre>
 
<pre>
CREATE TABLE location (
+
CREATE TABLE "repository" (
      handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      street TEXT,  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      city TEXT,  
+
    "gramps_id" varchar(25) NOT NULL,
      county TEXT,  
+
    "last_changed" datetime NOT NULL,
      state TEXT,  
+
    "private" bool NOT NULL,
      country TEXT,  
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      postal TEXT,
+
    "repository_type_id" integer NOT NULL REFERENCES "repositorytype" ("id"),
      phone TEXT,
+
    "name" text NOT NULL
      parish TEXT);
+
);
 
</pre>
 
</pre>
  
== markup ==
+
== place ==
  
 
<pre>
 
<pre>
CREATE TABLE markup (
+
CREATE TABLE "place" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      markup0 INTEGER,  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      markup1 TEXT,  
+
    "gramps_id" varchar(25) NOT NULL,
      value TEXT,  
+
    "last_changed" datetime NOT NULL,
      start_stop_list TEXT);
+
    "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>
 
</pre>
  
Line 212: Line 332:
  
 
<pre>
 
<pre>
CREATE TABLE media (
+
CREATE TABLE "media" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "handle" varchar(19) NOT NULL UNIQUE,
      path TEXT,  
+
    "gramps_id" varchar(25) NOT NULL,
      mime TEXT,  
+
    "last_changed" datetime NOT NULL,
      desc TEXT,
+
    "private" bool NOT NULL,
      change INTEGER,  
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      marker0 INTEGER,  
+
    "path" text NOT NULL,
      marker1 TEXT,
+
    "mime" text NOT NULL,
      private BOOLEAN);
+
    "desc" text NOT NULL
 +
);
 
</pre>
 
</pre>
  
== media_ref ==
+
== note ==
  
 
<pre>
 
<pre>
CREATE TABLE media_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 INTEGER,
+
    "last_changed" datetime NOT NULL,
      role2 INTEGER,
+
    "private" bool NOT NULL,
      role3 INTEGER,
+
    "marker_type_id" integer NOT NULL REFERENCES "markertype" ("id"),
      private BOOLEAN);
+
    "note_type_id" integer NOT NULL REFERENCES "notetype" ("id"),
 +
    "text" text NOT NULL,
 +
    "preformatted" bool NOT NULL
 +
);
 
</pre>
 
</pre>
  
Line 240: Line 364:
  
 
<pre>
 
<pre>
CREATE TABLE name (
+
CREATE TABLE "name" (
        handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
        primary_name BOOLEAN,
+
    "calendar" integer NOT NULL,
        private BOOLEAN,  
+
    "modifier" integer NOT NULL,
        first_name TEXT,  
+
    "quality" integer NOT NULL,
        surname TEXT,  
+
    "day1" integer NOT NULL,
        suffix TEXT,  
+
    "month1" integer NOT NULL,
        title TEXT,  
+
    "year1" integer NOT NULL,
        name_type0 INTEGER,  
+
    "slash1" bool NOT NULL,
        name_type1 TEXT,  
+
    "day2" integer,
        prefix TEXT,  
+
    "month2" integer,
        patronymic TEXT,  
+
    "year2" integer,
        group_as TEXT,  
+
    "slash2" bool,
        sort_as INTEGER,
+
    "text" varchar(80) NOT NULL,
        display_as INTEGER,
+
    "sortval" integer NOT NULL,
        call TEXT);
+
    "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>
  
== note ==
+
== lds ==
  
 
<pre>
 
<pre>
CREATE TABLE note (
+
CREATE TABLE "lds" (
        handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
        gid    CHARACTER(25),
+
    "private" bool NOT NULL,
        text  TEXT,
+
    "last_changed" datetime NOT NULL,
        format INTEGER,
+
    "lds_type_id" integer NOT NULL REFERENCES "ldstype" ("id"),
        note_type1  INTEGER,
+
    "place_id" integer REFERENCES "place" ("id"),
        note_type2  TEXT,
+
    "famc_id" integer REFERENCES "family" ("id"),
        change INTEGER,
+
    "temple" text NOT NULL,
        marker0 INTEGER,
+
    "status_id" integer NOT NULL REFERENCES "ldsstatus" ("id")
        marker1 TEXT,
+
);
        private BOOLEAN);
+
 
</pre>
 
</pre>
  
== person ==
+
== markup ==
  
 
<pre>
 
<pre>
CREATE TABLE person (
+
CREATE TABLE "markup" (
        handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
        gid CHARACTER(25),  
+
    "note_id" integer NOT NULL REFERENCES "note" ("id"),
        gender INTEGER,  
+
    "order" integer unsigned NOT NULL,
        death_ref_handle TEXT,  
+
    "string" text NOT NULL,
        birth_ref_handle TEXT,
+
    "start_stop_list" text NOT NULL
        change INTEGER,
+
);
        marker0 INTEGER,
+
        marker1 TEXT,
+
        private BOOLEAN);
+
 
</pre>
 
</pre>
  
== person_ref ==
+
== address ==
  
 
<pre>
 
<pre>
CREATE TABLE person_ref (
+
CREATE TABLE "address" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      description TEXT,
+
    "calendar" integer NOT NULL,
      private BOOLEAN);
+
    "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>
  
== place ==
+
== location ==
  
 
<pre>
 
<pre>
CREATE TABLE place (
+
CREATE TABLE "location" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "street" text NOT NULL,
      title TEXT,  
+
    "city" text NOT NULL,
      main_location CHARACTER(25),
+
    "county" text NOT NULL,
      long TEXT,  
+
    "state" text NOT NULL,
      lat TEXT,  
+
    "country" text NOT NULL,
      change INTEGER,  
+
    "postal" text NOT NULL,
      marker0 INTEGER,  
+
    "phone" text NOT NULL,
      marker1 TEXT,  
+
    "parish" text,
      private BOOLEAN);
+
    "order" integer unsigned NOT NULL
 +
);
 
</pre>
 
</pre>
  
== repository ==
+
== noteref ==
  
 
<pre>
 
<pre>
CREATE TABLE repository_ref (
+
CREATE TABLE "noteref" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      ref CHARACTER(25),  
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      call_number TEXT,  
+
    "object_id" integer unsigned NOT NULL,
      source_media_type0 INTEGER,
+
    "order" integer unsigned NOT NULL,
      source_media_type1 TEXT,
+
    "last_changed" datetime NOT NULL,
      private BOOLEAN);
+
    "private" bool NOT NULL,
 +
    "ref_object_id" integer NOT NULL REFERENCES "note" ("id")
 +
);
 
</pre>
 
</pre>
  
== repository_ref ==
+
== sourceref ==
  
 
<pre>
 
<pre>
CREATE TABLE repository (
+
CREATE TABLE "sourceref" (
      handle CHARACTER(25),  
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "calendar" integer NOT NULL,
      the_type0 INTEGER,  
+
    "modifier" integer NOT NULL,
      the_type1 TEXT,
+
    "quality" integer NOT NULL,
      name TEXT,  
+
    "day1" integer NOT NULL,
      change INTEGER,  
+
    "month1" integer NOT NULL,
      marker0 INTEGER,  
+
    "year1" integer NOT NULL,
      marker1 TEXT,  
+
    "slash1" bool NOT NULL,
      private BOOLEAN);
+
    "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>
  
== source ==
+
== eventref ==
  
 
<pre>
 
<pre>
CREATE TABLE source (
+
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"),
      title TEXT,  
+
    "object_id" integer unsigned NOT NULL,
      author TEXT,  
+
    "order" integer unsigned NOT NULL,
      pubinfo TEXT,  
+
    "last_changed" datetime NOT NULL,
      abbrev TEXT,  
+
    "private" bool NOT NULL,
      change INTEGER,
+
    "ref_object_id" integer NOT NULL REFERENCES "event" ("id"),
      marker0 INTEGER,
+
    "role_type_id" integer NOT NULL REFERENCES "eventroletype" ("id")
      marker1 TEXT,
+
);
      private BOOLEAN);
+
 
</pre>
 
</pre>
  
== source_ref ==
+
== repositoryref ==
  
 
<pre>
 
<pre>
CREATE TABLE source_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"),
      confidence INTEGER,
+
    "object_id" integer unsigned NOT NULL,
      page CHARACTER(25),
+
    "order" integer unsigned NOT NULL,
      private BOOLEAN);
+
    "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
 +
);
 
</pre>
 
</pre>
  
== url ==
+
== personref ==
  
 
<pre>
 
<pre>
CREATE TABLE url (
+
CREATE TABLE "personref" (
      handle CHARACTER(25),
+
    "id" integer NOT NULL PRIMARY KEY,
      path TEXT,  
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      desc TXT,  
+
    "object_id" integer unsigned NOT NULL,
      type0 INTEGER,
+
    "order" integer unsigned NOT NULL,
      type1 TEXT,                
+
    "last_changed" datetime NOT NULL,
      private BOOLEAN);
+
    "private" bool NOT NULL,
 +
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id"),
 +
    "description" varchar(50) NOT NULL
 +
);
 
</pre>
 
</pre>
 +
 +
== childref ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== mediaref ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== person ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== person ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== person ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== person ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== repository ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== place ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
== address ==
 +
 +
<pre>
 +
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")
 +
);
 +
</pre>
 +
 +
 +
[[Category:GEPS|S]]

Revision as of 22:44, 31 January 2013

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 version and an ExportSql/ImportSql version. Currently, the ExportDjango is the latest SQL design. You can access the older functionality through the ExportSql and ImportSql programs of the Third-party Plugins and the newer ExportDjango in SVN in /trunk/gramps/webapp/, src/data/templates/ and /src/data/javascript/.

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

Newer Version

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. There does not yet exist an ImportDjango, but hopefully later today...

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

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")
);