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