SQL Schema

From Gramps
Revision as of 22:55, 13 October 2016 by Nick H (talk | contribs) (Updated)
Jump to: navigation, search
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
);
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
);
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
);
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
);
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
);
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
);
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
);
CREATE TABLE repository(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  gramps_id TEXT,
  blob_data BLOB,
  name TEXT,
  change INTEGER,
  private INTEGER
);
CREATE TABLE note(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  gramps_id TEXT,
  blob_data BLOB,
  format INTEGER,
  change INTEGER,
  private INTEGER
);
CREATE TABLE tag(
  handle VARCHAR(50) PRIMARY KEY NOT NULL,
  order_by TEXT,
  blob_data BLOB,
  color TEXT,
  change INTEGER,
  priority INTEGER,
  name TEXT
);
CREATE TABLE reference(
  obj_handle VARCHAR(50),
  obj_class TEXT,
  ref_handle VARCHAR(50),
  ref_class TEXT
);
CREATE TABLE name_group(name VARCHAR(50) PRIMARY KEY NOT NULL, grouping TEXT);
CREATE TABLE metadata(setting VARCHAR(50) PRIMARY KEY NOT NULL, value BLOB);
CREATE TABLE gender_stats(
  given_name TEXT,
  female INTEGER,
  male INTEGER,
  unknown INTEGER
);
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);