SQL Schema

From Gramps
Revision as of 00:04, 1 December 2023 by Bamaustin (talk | contribs) (Undo revision 100693 by Bamaustin (talk))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search


Tables

person

CREATE TABLE person(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  given_name TEXT,
  surname TEXT,
  order_by TEXT,
  gramps_id TEXT,
  blob_data BLOB,
  private INTEGER,
  change INTEGER,
  birth_ref_index INTEGER,
  gender INTEGER,
  death_ref_index INTEGER
);

family

CREATE TABLE family(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  father_handle VARCHAR(50),
  mother_handle VARCHAR(50),
  gramps_id TEXT,
  blob_data BLOB,
  change INTEGER,
  private INTEGER
);

source

CREATE TABLE source(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  gramps_id TEXT,
  blob_data BLOB,
  author TEXT,
  private INTEGER,
  title TEXT,
  pubinfo TEXT,
  change INTEGER,
  abbrev TEXT
);

citation

CREATE TABLE citation(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  gramps_id TEXT,
  blob_data BLOB,
  page TEXT,
  source_handle VARCHAR(50),
  private INTEGER,
  confidence TEXT,
  change INTEGER
);

event

CREATE TABLE event(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  gramps_id TEXT,
  blob_data BLOB,
  private INTEGER,
  description TEXT,
  place VARCHAR(50),
  change INTEGER
);

media

CREATE TABLE media(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  gramps_id TEXT,
  blob_data BLOB,
  private INTEGER,
  mime TEXT,
  change INTEGER,
  path TEXT,
  checksum TEXT,
  desc TEXT
);

place

CREATE TABLE place(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  gramps_id TEXT,
  blob_data BLOB,
  lat TEXT,
  code TEXT,
  title TEXT,
  private INTEGER,
  change INTEGER,
  long TEXT
);

repository

CREATE TABLE repository(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  gramps_id TEXT,
  blob_data BLOB,
  name TEXT,
  change INTEGER,
  private INTEGER
);

note

CREATE TABLE note(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  gramps_id TEXT,
  blob_data BLOB,
  format INTEGER,
  change INTEGER,
  private INTEGER
);

tag

CREATE TABLE tag(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  blob_data BLOB,
  color TEXT,
  change INTEGER,
  priority INTEGER,
  name TEXT
);

reference

CREATE TABLE reference(
  obj_handle VARCHAR(50),
  obj_class TEXT,
  ref_handle VARCHAR(50),
  ref_class TEXT
);

name_group

CREATE TABLE name_group(
  name VARCHAR(50) PRIMARY KEY NOT NULL, 
  grouping TEXT
);

metadata

CREATE TABLE metadata(
  setting VARCHAR(50) PRIMARY KEY NOT NULL, 
  value BLOB
);

gender_stats

CREATE TABLE gender_stats(
  given_name TEXT,
  female INTEGER,
  male INTEGER,
  unknown INTEGER
);

Indices

CREATE INDEX person_order_by ON person(order_by);
CREATE INDEX person_gramps_id ON person(gramps_id);
CREATE INDEX person_surname ON person(surname);
CREATE INDEX person_given_name ON person(given_name);
CREATE INDEX source_order_by ON source(order_by);
CREATE INDEX source_gramps_id ON source(gramps_id);
CREATE INDEX citation_order_by ON citation(order_by);
CREATE INDEX citation_gramps_id ON citation(gramps_id);
CREATE INDEX media_order_by ON media(order_by);
CREATE INDEX media_gramps_id ON media(gramps_id);
CREATE INDEX place_order_by ON place(order_by);
CREATE INDEX place_gramps_id ON place(gramps_id);
CREATE INDEX tag_order_by ON tag(order_by);
CREATE INDEX reference_ref_handle ON reference(ref_handle);
CREATE INDEX family_gramps_id ON family(gramps_id);
CREATE INDEX event_gramps_id ON event(gramps_id);
CREATE INDEX repository_gramps_id ON repository(gramps_id);
CREATE INDEX note_gramps_id ON note(gramps_id);
CREATE INDEX reference_obj_handle ON reference(obj_handle);

See also

Because Documention about data structures becomes quickly outdated, the following cross-references have a parenthetical notation of the vintage - plus the most recent major revision.