schubertgen/ext_tables.sql at main · ps-devstudio/schubertgen · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
CREATE TABLE tx_schubertgen_domain_model_person (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(64) DEFAULT '' NOT NULL,
  slug varchar(255) DEFAULT '' NOT NULL,
  full_name varchar(255) DEFAULT '' NOT NULL,
  given_name varchar(255) DEFAULT '' NOT NULL,
  surname varchar(255) DEFAULT '' NOT NULL,
  gender varchar(1) DEFAULT '' NOT NULL,
  birth_date_text varchar(64) DEFAULT '' NOT NULL,
  birth_sort_date int(11) DEFAULT 0 NOT NULL,
  birth_place varchar(255) DEFAULT '' NOT NULL,
  death_date_text varchar(64) DEFAULT '' NOT NULL,
  death_sort_date int(11) DEFAULT 0 NOT NULL,
  death_place varchar(255) DEFAULT '' NOT NULL,
  primary_media varchar(64) DEFAULT '' NOT NULL,
  primary_image int(11) unsigned DEFAULT 0 NOT NULL,
  events int(11) unsigned DEFAULT 0 NOT NULL,
  notes text,
  raw_gedcom mediumtext,
  PRIMARY KEY (uid),
  UNIQUE KEY external_id (external_id),
  KEY slug (slug),
  KEY full_name (full_name),
  KEY birth_sort_date (birth_sort_date),
  KEY death_sort_date (death_sort_date)
);

CREATE TABLE tx_schubertgen_domain_model_family (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(64) DEFAULT '' NOT NULL,
  husband_external_id varchar(64) DEFAULT '' NOT NULL,
  wife_external_id varchar(64) DEFAULT '' NOT NULL,
  husband int(11) unsigned DEFAULT 0 NOT NULL,
  wife int(11) unsigned DEFAULT 0 NOT NULL,
  children int(11) unsigned DEFAULT 0 NOT NULL,
  events int(11) unsigned DEFAULT 0 NOT NULL,
  marriage_date_text varchar(64) DEFAULT '' NOT NULL,
  marriage_sort_date int(11) DEFAULT 0 NOT NULL,
  marriage_place varchar(255) DEFAULT '' NOT NULL,
  raw_gedcom mediumtext,
  PRIMARY KEY (uid),
  UNIQUE KEY external_id (external_id),
  KEY husband_external_id (husband_external_id),
  KEY wife_external_id (wife_external_id),
  KEY marriage_sort_date (marriage_sort_date)
);

CREATE TABLE tx_schubertgen_family_child_mm (
  uid_local int(11) DEFAULT 0 NOT NULL,
  uid_foreign int(11) DEFAULT 0 NOT NULL,
  sorting int(11) DEFAULT 0 NOT NULL,
  KEY uid_local (uid_local),
  KEY uid_foreign (uid_foreign)
);

CREATE TABLE tx_schubertgen_domain_model_event (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(190) DEFAULT '' NOT NULL,
  parent_type varchar(16) DEFAULT '' NOT NULL,
  parent_external_id varchar(64) DEFAULT '' NOT NULL,
  person int(11) unsigned DEFAULT 0 NOT NULL,
  family int(11) unsigned DEFAULT 0 NOT NULL,
  event_type varchar(32) DEFAULT '' NOT NULL,
  date_text varchar(64) DEFAULT '' NOT NULL,
  date_year int(11) DEFAULT 0 NOT NULL,
  date_month int(11) DEFAULT 0 NOT NULL,
  date_day int(11) DEFAULT 0 NOT NULL,
  date_modifier varchar(32) DEFAULT '' NOT NULL,
  sort_date int(11) DEFAULT 0 NOT NULL,
  place varchar(255) DEFAULT '' NOT NULL,
  place_record int(11) unsigned DEFAULT 0 NOT NULL,
  latitude decimal(10,7) DEFAULT 0.0000000 NOT NULL,
  longitude decimal(10,7) DEFAULT 0.0000000 NOT NULL,
  description text,
  source_external_id varchar(64) DEFAULT '' NOT NULL,
  source int(11) unsigned DEFAULT 0 NOT NULL,
  PRIMARY KEY (uid),
  KEY external_id (external_id),
  KEY parent (parent_type,parent_external_id),
  KEY person (person),
  KEY family (family),
  KEY place_record (place_record),
  KEY source (source),
  KEY event_type (event_type),
  KEY sort_date (sort_date)
);

CREATE TABLE tx_schubertgen_domain_model_place (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(190) DEFAULT '' NOT NULL,
  name varchar(255) DEFAULT '' NOT NULL,
  original_name varchar(255) DEFAULT '' NOT NULL,
  city varchar(120) DEFAULT '' NOT NULL,
  county varchar(120) DEFAULT '' NOT NULL,
  state varchar(120) DEFAULT '' NOT NULL,
  country varchar(120) DEFAULT '' NOT NULL,
  latitude decimal(10,7) DEFAULT 0.0000000 NOT NULL,
  longitude decimal(10,7) DEFAULT 0.0000000 NOT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY external_id (external_id),
  KEY name (name),
  KEY country (country)
);

CREATE TABLE tx_schubertgen_domain_model_source (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(64) DEFAULT '' NOT NULL,
  title varchar(255) DEFAULT '' NOT NULL,
  reference_title varchar(255) DEFAULT '' NOT NULL,
  church varchar(255) DEFAULT '' NOT NULL,
  place varchar(255) DEFAULT '' NOT NULL,
  url varchar(1024) DEFAULT '' NOT NULL,
  media_external_ids text,
  raw_gedcom mediumtext,
  PRIMARY KEY (uid),
  UNIQUE KEY external_id (external_id),
  KEY title (title)
);

CREATE TABLE tx_schubertgen_domain_model_media (
  uid int(11) NOT NULL auto_increment,
  pid int(11) DEFAULT 0 NOT NULL,
  tstamp int(11) DEFAULT 0 NOT NULL,
  crdate int(11) DEFAULT 0 NOT NULL,
  deleted tinyint(1) unsigned DEFAULT 0 NOT NULL,
  hidden tinyint(1) unsigned DEFAULT 0 NOT NULL,
  external_id varchar(64) DEFAULT '' NOT NULL,
  title varchar(255) DEFAULT '' NOT NULL,
  file_name varchar(255) DEFAULT '' NOT NULL,
  file_extension varchar(32) DEFAULT '' NOT NULL,
  file int(11) unsigned DEFAULT 0 NOT NULL,
  raw_gedcom mediumtext,
  PRIMARY KEY (uid),
  UNIQUE KEY external_id (external_id),
  KEY file_name (file_name)
);