xref: /webtrees/app/Schema/Migration0.php (revision 3fa66c660869af2f4c92ef37e06997aa8a0f55e1) 
1<?php
2/**
3 * webtrees: online genealogy
4 * Copyright (C) 2019 webtrees development team
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
9 * This program is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License for more details.
13 * You should have received a copy of the GNU General Public License
14 * along with this program. If not, see <http://www.gnu.org/licenses/>.
15 */
16declare(strict_types=1);
17
18namespace Fisharebest\Webtrees\Schema;
19
20use Illuminate\Database\Capsule\Manager as DB;
21use Illuminate\Database\Schema\Blueprint;
22
23/**
24 * Upgrade the database schema from version 0 (empty database) to version 1.
25 */
26class Migration0 implements MigrationInterface
27{
28    /**
29     * Upgrade to to the next version.
30     *
31     * @return void
32     */
33    public function upgrade(): void
34    {
35        DB::schema()->create('gedcom', function (Blueprint $table): void {
36            $table->integer('gedcom_id', true);
37            $table->string('gedcom_name', 255);
38            $table->integer('sort_order')->default(0);
39
40            $table->unique('gedcom_name');
41            $table->index('sort_order');
42        });
43
44        DB::schema()->create('site_setting', function (Blueprint $table): void {
45            $table->string('setting_name', 32);
46            $table->string('setting_value', 2000);
47
48            $table->primary('setting_name');
49        });
50
51        DB::schema()->create('gedcom_setting', function (Blueprint $table): void {
52            $table->integer('gedcom_id');
53            $table->string('setting_name', 32);
54            $table->string('setting_value', 255);
55
56            $table->primary(['gedcom_id', 'setting_name']);
57
58            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
59        });
60
61        DB::schema()->create('user', function (Blueprint $table): void {
62            $table->integer('user_id', true);
63            $table->string('user_name', 32);
64            $table->string('real_name', 64);
65            $table->string('email', 64);
66            $table->string('password', 128);
67
68            $table->unique('user_name');
69            $table->unique('email');
70        });
71
72        DB::schema()->create('user_setting', function (Blueprint $table): void {
73            $table->integer('user_id');
74            $table->string('setting_name', 32);
75            $table->string('setting_value', 255);
76
77            $table->primary(['user_id', 'setting_name']);
78
79            $table->foreign('user_id')->references('user_id')->on('user');
80        });
81
82        DB::schema()->create('user_gedcom_setting', function (Blueprint $table): void {
83            $table->integer('user_id');
84            $table->integer('gedcom_id');
85            $table->string('setting_name', 32);
86            $table->string('setting_value', 255);
87
88            $table->primary(['user_id', 'gedcom_id', 'setting_name']);
89            $table->index('gedcom_id');
90
91            $table->foreign('user_id')->references('user_id')->on('user');
92            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
93        });
94
95        DB::schema()->create('log', function (Blueprint $table): void {
96            $table->integer('log_id', true);
97            $table->timestamp('log_time')->useCurrent();
98            $table->enum('log_type', ['auth', 'config', 'debug', 'edit', 'error', 'media', 'search']);
99            $table->longText('log_message');
100            $table->ipAddress('ip_address');
101            $table->integer('user_id')->nullable();
102            $table->integer('gedcom_id')->nullable();
103
104            $table->index('log_time');
105            $table->index('log_type');
106            $table->index('ip_address');
107            $table->index('user_id');
108            $table->index('gedcom_id');
109
110            $table->foreign('user_id')->references('user_id')->on('user');
111            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
112        });
113
114        DB::schema()->create('change', function (Blueprint $table): void {
115            $table->integer('change_id', true);
116            $table->timestamp('change_time')->useCurrent();
117            $table->enum('status', ['accepted', 'pending', 'rejected'])->default('pending');
118            $table->integer('gedcom_id');
119            $table->string('xref', 20);
120            $table->longText('old_gedcom');
121            $table->longText('new_gedcom');
122            $table->integer('user_id');
123
124            $table->index(['gedcom_id', 'status', 'xref']);
125            $table->index('user_id');
126
127            $table->foreign('user_id')->references('user_id')->on('user');
128            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
129        });
130
131        DB::schema()->create('message', function (Blueprint $table): void {
132            $table->integer('message_id', true);
133            $table->string('sender', 64);
134            $table->ipAddress('ip_address');
135            $table->integer('user_id');
136            $table->string('subject', 255);
137            $table->longText('body');
138            $table->timestamp('created')->useCurrent();
139
140            $table->index('user_id');
141
142            $table->foreign('user_id')->references('user_id')->on('user');
143        });
144
145        DB::schema()->create('default_resn', function (Blueprint $table): void {
146            $table->integer('default_resn_id', true);
147            $table->integer('gedcom_id');
148            $table->string('xref', 20)->nullable();
149            $table->string('tag_type', 15)->nullable();
150            $table->enum('resn', ['none', 'privacy', 'confidential', 'hidden']);
151            $table->string('comment', 255)->nullable();
152            $table->timestamp('updated')->useCurrent();
153
154            $table->unique(['gedcom_id', 'xref', 'tag_type']);
155
156            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
157        });
158
159        DB::schema()->create('individuals', function (Blueprint $table): void {
160            $table->string('i_id', 20);
161            $table->integer('i_file');
162            $table->string('i_rin', 20);
163            $table->enum('i_sex', ['U', 'M', 'F']);
164            $table->longText('i_gedcom');
165
166            $table->primary(['i_id', 'i_file']);
167            $table->unique(['i_file', 'i_id']);
168        });
169
170        DB::schema()->create('families', function (Blueprint $table): void {
171            $table->string('f_id', 20);
172            $table->integer('f_file');
173            $table->string('f_husb', 20)->nullable();
174            $table->string('f_wife', 20)->nullable();
175            $table->longText('f_gedcom');
176            $table->integer('f_numchil');
177
178            $table->primary(['f_id', 'f_file']);
179            $table->unique(['f_file', 'f_id']);
180            $table->index('f_husb');
181            $table->index('f_wife');
182        });
183
184        DB::schema()->create('places', function (Blueprint $table): void {
185            $table->integer('p_id', true);
186            $table->string('p_place', 150);
187            $table->integer('p_parent_id')->nullable();
188            $table->integer('p_file');
189            $table->longText('p_std_soundex')->nullable();
190            $table->longText('p_dm_soundex')->nullable();
191
192            $table->index(['p_file', 'p_place']);
193            $table->unique(['p_parent_id', 'p_file', 'p_place']);
194        });
195
196        DB::schema()->create('placelinks', function (Blueprint $table): void {
197            $table->integer('pl_p_id');
198            $table->string('pl_gid', 20);
199            $table->integer('pl_file');
200
201            $table->primary(['pl_p_id', 'pl_gid', 'pl_file']);
202            $table->index('pl_p_id');
203            $table->index('pl_gid');
204            $table->index('pl_file');
205        });
206
207        DB::schema()->create('dates', function (Blueprint $table): void {
208            $table->tinyInteger('d_day');
209            $table->char('d_month', 5)->nullable();
210            $table->tinyInteger('d_mon');
211            $table->smallInteger('d_year');
212            $table->mediumInteger('d_julianday1');
213            $table->mediumInteger('d_julianday2');
214            $table->string('d_fact', 15);
215            $table->string('d_gid', 20);
216            $table->integer('d_file');
217            $table->enum('d_type', ['@#DGREGORIAN@', '@#DJULIAN@', '@#DHEBREW@', '@#DFRENCH R@', '@#DHIJRI@', '@#DROMAN@', '@#DJALALI@']);
218
219            $table->index('d_day');
220            $table->index('d_month');
221            $table->index('d_mon');
222            $table->index('d_year');
223            $table->index('d_julianday1');
224            $table->index('d_julianday2');
225            $table->index('d_gid');
226            $table->index('d_file');
227            $table->index('d_type');
228            $table->index(['d_fact', 'd_gid']);
229        });
230
231        DB::schema()->create('media', function (Blueprint $table): void {
232            $table->string('m_id', 20);
233            $table->string('m_ext', 6)->nullable();
234            $table->string('m_type', 20)->nullable();
235            $table->string('m_titl', 255)->nullable();
236            $table->string('m_filename', 512)->nullable();
237            $table->integer('m_file');
238            $table->longText('m_gedcom')->nullable();
239
240            $table->primary(['m_file', 'm_id']);
241            $table->unique(['m_id', 'm_file']);
242            $table->index(['m_ext', 'm_type']);
243        });
244
245        DB::schema()->create('next_id', function (Blueprint $table): void {
246            $table->integer('gedcom_id');
247            $table->string('record_type', 15);
248            $table->decimal('next_id', 20, 0);
249
250            $table->primary(['gedcom_id', 'record_type']);
251
252            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
253        });
254
255        DB::schema()->create('other', function (Blueprint $table): void {
256            $table->string('o_id', 20);
257            $table->integer('o_file');
258            $table->string('o_type', 15);
259            $table->longText('o_gedcom');
260
261            $table->primary(['o_id', 'o_file']);
262            $table->unique(['o_file', 'o_id']);
263        });
264
265        DB::schema()->create('sources', function (Blueprint $table): void {
266            $table->string('s_id', 20);
267            $table->integer('s_file');
268            $table->string('s_name', 255);
269            $table->longText('s_gedcom');
270
271            $table->primary(['s_id', 's_file']);
272            $table->unique(['s_file', 's_id']);
273            $table->index('s_name');
274        });
275
276        DB::schema()->create('link', function (Blueprint $table): void {
277            $table->integer('l_file');
278            $table->string('l_from', 20);
279            $table->string('l_type', 15);
280            $table->string('l_to', 20);
281
282            $table->primary(['l_from', 'l_file', 'l_type', 'l_to']);
283            $table->unique(['l_to', 'l_file', 'l_type', 'l_from']);
284        });
285
286        DB::schema()->create('name', function (Blueprint $table): void {
287            $table->integer('n_file');
288            $table->string('n_id', 20);
289            $table->integer('n_num');
290            $table->string('n_type', 15);
291            $table->string('n_sort', 255);
292            $table->string('n_full', 255);
293            $table->string('n_surname', 255)->nullable();
294            $table->string('n_surn', 255)->nullable();
295            $table->string('n_givn', 255)->nullable();
296            $table->string('n_soundex_givn_std', 255)->nullable();
297            $table->string('n_soundex_surn_std', 255)->nullable();
298            $table->string('n_soundex_givn_dm', 255)->nullable();
299            $table->string('n_soundex_surn_dm', 255)->nullable();
300
301            $table->primary(['n_id', 'n_file', 'n_num']);
302            $table->index(['n_full', 'n_id', 'n_file']);
303            $table->index(['n_surn', 'n_file', 'n_type', 'n_id']);
304            $table->index(['n_givn', 'n_file', 'n_type', 'n_id']);
305        });
306
307        DB::schema()->create('module', function (Blueprint $table): void {
308            $table->string('module_name', 32);
309            $table->enum('status', ['enabled', 'disabled'])->default('enabled');
310            $table->integer('tab_order')->nullable();
311            $table->integer('menu_order')->nullable();
312            $table->integer('sidebar_order')->nullable();
313
314            $table->primary('module_name');
315        });
316
317        DB::schema()->create('module_setting', function (Blueprint $table): void {
318            $table->string('module_name', 32);
319            $table->string('setting_name', 32);
320            $table->longText('setting_value');
321
322            $table->primary(['module_name', 'setting_name']);
323
324            $table->foreign('module_name')->references('module_name')->on('module');
325        });
326
327        DB::schema()->create('module_privacy', function (Blueprint $table): void {
328            $table->string('module_name', 32);
329            $table->integer('gedcom_id');
330            $table->enum('component', ['block', 'chart', 'menu', 'report', 'sidebar', 'tab', 'theme']);
331            $table->tinyInteger('access_level');
332
333            $table->primary(['module_name', 'gedcom_id', 'component']);
334            $table->unique(['gedcom_id', 'module_name', 'component']);
335
336            $table->foreign('module_name')->references('module_name')->on('module');
337            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
338        });
339
340        DB::schema()->create('block', function (Blueprint $table): void {
341            $table->integer('block_id', true);
342            $table->integer('gedcom_id')->nullable();
343            $table->integer('user_id')->nullable();
344            $table->string('xref', 20)->nullable();
345            $table->enum('location', ['main', 'side'])->nullable();
346            $table->integer('block_order');
347            $table->string('module_name', 32);
348
349            $table->index('module_name');
350            $table->index('gedcom_id');
351            $table->index('user_id');
352
353            $table->foreign('module_name')->references('module_name')->on('module');
354            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
355            $table->foreign('user_id')->references('user_id')->on('user');
356        });
357
358        DB::schema()->create('block_setting', function (Blueprint $table): void {
359            $table->integer('block_id');
360            $table->string('setting_name', 32);
361            $table->longText('setting_value');
362
363            $table->primary(['block_id', 'setting_name']);
364
365            $table->foreign('block_id')->references('block_id')->on('block');
366        });
367
368        DB::schema()->create('hit_counter', function (Blueprint $table): void {
369            $table->integer('gedcom_id');
370            $table->string('page_name', 32);
371            $table->string('page_parameter', 32);
372            $table->integer('page_count');
373
374            $table->primary(['gedcom_id', 'page_name', 'page_parameter']);
375
376            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
377        });
378
379        DB::schema()->create('session', function (Blueprint $table): void {
380            $table->string('session_id', 32);
381            $table->timestamp('session_time')->useCurrent();
382            $table->integer('user_id');
383            $table->ipAddress('ip_address');
384            $table->binary('session_data');
385
386            $table->primary('session_id');
387            $table->index('session_time');
388            $table->index(['user_id', 'ip_address']);
389        });
390
391        // See https://github.com/laravel/framework/issues/3544
392        if (DB::connection()->getDriverName() === 'mysql') {
393            $table = DB::connection()->getSchemaGrammar()->wrapTable('session');
394            $sql   = 'ALTER TABLE ' . $table . ' MODIFY session_data LONGBLOB';
395            DB::connection()->statement($sql);
396        }
397
398        DB::schema()->create('gedcom_chunk', function (Blueprint $table): void {
399            $table->integer('gedcom_chunk_id', true);
400            $table->integer('gedcom_id');
401            $table->binary('chunk_data');
402            $table->boolean('imported')->default(0);
403
404            $table->index(['gedcom_id', 'imported']);
405
406            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
407        });
408
409        // See https://github.com/laravel/framework/issues/3544
410        if (DB::connection()->getDriverName() === 'mysql') {
411            $table = DB::connection()->getSchemaGrammar()->wrapTable('gedcom_chunk');
412            $sql   = 'ALTER TABLE ' . $table . ' MODIFY chunk_data LONGBLOB';
413            DB::connection()->statement($sql);
414        }
415    }
416}
417