Difference between revisions of "Gramps SQL Database"

From Gramps
Jump to: navigation, search
(External Access)
(Database Structure)
Line 57: Line 57:
 
All data are accessed by their ''handles''. Links between tables are made through the '''link''' table. Rather than having many tables representing joined data, a single table (link) represents the conection. This is made by linking a '''from_type''' and '''from_handle''' to a '''to_type''' and '''to_handle'''.
 
All data are accessed by their ''handles''. Links between tables are made through the '''link''' table. Rather than having many tables representing joined data, a single table (link) represents the conection. This is made by linking a '''from_type''' and '''from_handle''' to a '''to_type''' and '''to_handle'''.
  
== address ==
+
 
 +
== markertype ==
  
 
<pre>
 
<pre>
CREATE TABLE address (
+
CREATE TABLE "markertype" (
      handle  CHARACTER(25) PRIMARY KEY,
+
    "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) PRIMARY KEY,
+
    "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) PRIMARY KEY,
+
    "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) PRIMARY KEY,
+
    "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) PRIMARY KEY,
+
     "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) PRIMARY KEY,
+
    "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) PRIMARY KEY,
+
    "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 ==
 +
 
 +
CREcreated ATE 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 149: Line 224:
  
 
<pre>
 
<pre>
CREATE TABLE family (
+
CREATE TABLE "family" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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>
 +
 
 +
== source ==
 +
 
 +
<pre>
 +
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
 +
);
 +
</pre>
 +
 
 +
== event ==
 +
 
 +
<pre>
 +
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
 +
);
 +
</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>
 +
 
 +
== note ==
 +
 
 +
<pre>
 +
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
 +
);
 +
</pre>
 +
 
 +
== name ==
 +
 
 +
<pre>
 +
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"),
 +
    "primary_name" 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 165: Line 387:
  
 
<pre>
 
<pre>
CREATE TABLE lds (
+
CREATE TABLE "lds" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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>
  
CREATE INDEX idx_link_to ON
+
== address ==
      link(from_type, from_handle, to_type);
+
 
 +
<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 191: Line 438:
  
 
<pre>
 
<pre>
CREATE TABLE location (
+
CREATE TABLE "location" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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>
 
</pre>
  
== markup ==
+
== noteref ==
  
 
<pre>
 
<pre>
CREATE TABLE markup (
+
CREATE TABLE "noteref" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      markup0 INTEGER,  
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      markup1 TEXT,  
+
    "object_id" integer unsigned NOT NULL,
      value TEXT,  
+
    "order" integer unsigned NOT NULL,
      start_stop_list TEXT);
+
    "last_changed" datetime NOT NULL,
 +
    "private" bool NOT NULL,
 +
    "ref_object_id" integer NOT NULL REFERENCES "note" ("id")
 +
);
 
</pre>
 
</pre>
  
== media ==
+
== sourceref ==
  
 
<pre>
 
<pre>
CREATE TABLE media (
+
CREATE TABLE "sourceref" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "calendar" integer NOT NULL,
      path TEXT,  
+
    "modifier" integer NOT NULL,
      mime TEXT,  
+
    "quality" integer NOT NULL,
      desc 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>
  
== media_ref ==
+
== eventref ==
  
 
<pre>
 
<pre>
CREATE TABLE media_ref (
+
CREATE TABLE "eventref" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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 "event" ("id"),
 +
    "role_type_id" integer NOT NULL REFERENCES "eventroletype" ("id")
 +
);
 
</pre>
 
</pre>
  
== name ==
+
== repositoryref ==
  
 
<pre>
 
<pre>
CREATE TABLE name (
+
CREATE TABLE "repositoryref" (
        handle CHARACTER(25) PRIMARY KEY,
+
    "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 "repository" ("id"),
        name_type0 INTEGER,  
+
    "source_media_type_id" integer NOT NULL REFERENCES "sourcemediatype" ("id"),
        name_type1 TEXT,
+
    "call_number" varchar(50) NOT NULL
        prefix TEXT,
+
);
        patronymic TEXT,
+
        group_as TEXT,
+
        sort_as INTEGER,
+
        display_as INTEGER,
+
        call TEXT);
+
 
</pre>
 
</pre>
  
== note ==
+
== personref ==
  
 
<pre>
 
<pre>
CREATE TABLE note (
+
CREATE TABLE "personref" (
        handle CHARACTER(25) PRIMARY KEY,
+
    "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,
+
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id"),
        marker0 INTEGER,
+
    "description" varchar(50) NOT NULL
        marker1 TEXT,
+
);
        private BOOLEAN);
+
 
</pre>
 
</pre>
  
== person ==
+
== childref ==
  
 
<pre>
 
<pre>
CREATE TABLE person (
+
CREATE TABLE "childref" (
        handle CHARACTER(25) PRIMARY KEY,
+
    "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,  
+
    "father_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
        marker1 TEXT,  
+
    "mother_rel_type_id" integer NOT NULL REFERENCES "childreftype" ("id"),
        private BOOLEAN);
+
    "ref_object_id" integer NOT NULL REFERENCES "person" ("id")
 +
);
 
</pre>
 
</pre>
  
== person_ref ==
+
== mediaref ==
  
 
<pre>
 
<pre>
CREATE TABLE person_ref (
+
CREATE TABLE "mediaref" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      description TEXT,
+
    "object_type_id" integer NOT NULL REFERENCES "django_content_type" ("id"),
      private BOOLEAN);
+
    "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>
 
</pre>
  
== place ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE place (
+
CREATE TABLE "person_names" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      title TEXT,
+
    "name_id" integer NOT NULL REFERENCES "name" ("id"),
      main_location CHARACTER(25),
+
    UNIQUE ("person_id", "name_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_families" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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_parent_families" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      the_type0 INTEGER,  
+
    "family_id" integer NOT NULL REFERENCES "family" ("id"),
      the_type1 TEXT,
+
    UNIQUE ("person_id", "family_id")
      name TEXT,
+
);
      change INTEGER,
+
      marker0 INTEGER,
+
      marker1 TEXT,  
+
      private BOOLEAN);
+
 
</pre>
 
</pre>
  
== source ==
+
== person ==
  
 
<pre>
 
<pre>
CREATE TABLE source (
+
CREATE TABLE "person_addresses" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      gid CHARACTER(25),  
+
    "person_id" integer NOT NULL REFERENCES "person" ("id"),
      title TEXT,  
+
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
      author TEXT,
+
    UNIQUE ("person_id", "address_id")
      pubinfo TEXT,
+
);
      abbrev TEXT,
+
      change INTEGER,
+
      marker0 INTEGER,
+
      marker1 TEXT,  
+
      private BOOLEAN);
+
 
</pre>
 
</pre>
  
== source_ref ==
+
== repository ==
  
 
<pre>
 
<pre>
CREATE TABLE source_ref (
+
CREATE TABLE "repository_addresses" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "id" integer NOT NULL PRIMARY KEY,
      ref CHARACTER(25),  
+
    "repository_id" integer NOT NULL REFERENCES "repository" ("id"),
      confidence INTEGER,
+
    "address_id" integer NOT NULL REFERENCES "address" ("id"),
      page CHARACTER(25),
+
    UNIQUE ("repository_id", "address_id")
      private BOOLEAN);
+
);
 
</pre>
 
</pre>
  
== url ==
+
== 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>
 
<pre>
CREATE TABLE url (
+
CREATE TABLE "address_locations" (
      handle CHARACTER(25) PRIMARY KEY,
+
    "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>

Revision as of 08:19, 16 August 2009

This page documents the format of a SQL database version of the GRAMPS BSDDB datastore. This can be seen as a step towards a GEPS 010: SQL Backend for GRAMPS, and for supplying data to other applications, such as a GEPS 013: GRAMPS Webapp.

GRAMPS SQL database Overview

The current version of this project outputs the GRAMPS BSDDB data into a relational SQLite database. You can access this functionality through the ExportSql and ImportSql programs of the Third-party Plugins.

Sample usage

After dropping the above plugins into your .gramps/plugins subdirectory, you can:

python src/gramps.py -O "My Family Tree" -e familytree.sql

This will export the entire contents of your database (not including bookmarks, gramplets, nor any meta data, etc.) into a sqlite3 database named "familytree.sql'. Likewise, you can read the data back in to a new database:

python src/gramps.py -i familytree.sql

NOTE: if you import this file into an existing family tree, you will get corrupted data for any items that overlap with internal numbering! This has the same effect as importing with GRAMPS XML.

Exporting a SQL database can take up to 2 seconds per record to output. 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 read sqlite files. For example, on Linux:

$ sqlite3 export.sql 
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
address         event           location        note            repository_ref
attribute       event_ref       markup          person          source        
child_ref       family          media           person_ref      source_ref    
datamap         lds             media_ref       place           url           
date            link            name            repository    
sqlite> .headers on
sqlite> .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 for 0 to 1.

Database Structure

The GRAMPS BSDDB data is broken up into the following 24 tables. The structure and names of the tables and fields were designed to match the naming and operation of the GRAMPS source code. All of the tables have singular form names (e.g., 'person' rather than 'people').

All data are accessed by their handles. Links between tables are made through the link table. Rather than having many tables representing joined data, a single table (link) represents the conection. This is made by linking a from_type and from_handle to a to_type and to_handle.


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

CREcreated ATE TABLE "config" (

   "id" integer NOT NULL PRIMARY KEY,
   "db_version" varchar(25) NOT NULL,
   "created" datetime NOT NULL

); </pre>

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