xref: /webtrees/app/Schema/Migration0.php (revision 52550490b7095dd69811f3ec21ed5a3ca1a8968d)

<?php

/**
 * webtrees: online genealogy
 * Copyright (C) 2023 webtrees development team
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */

declare(strict_types=1);

namespace Fisharebest\Webtrees\Schema;

use Fisharebest\Webtrees\DB;
use Illuminate\Database\Schema\Blueprint;

/**
 * Upgrade the database schema from version 0 (empty database) to version 1.
 */
class Migration0 implements MigrationInterface
{
    public function upgrade(): void
    {
        DB::schema()->create('gedcom', static function (Blueprint $table): void {
            $table->integer('gedcom_id', true);
            $table->string('gedcom_name', 255);
            $table->integer('sort_order')->default(0);

            $table->unique('gedcom_name');
            $table->index('sort_order');
        });

        DB::schema()->create('site_setting', static function (Blueprint $table): void {
            $table->string('setting_name', 32);
            $table->string('setting_value', 2000);

            $table->primary('setting_name');
        });

        DB::schema()->create('gedcom_setting', static function (Blueprint $table): void {
            $table->integer('gedcom_id');
            $table->string('setting_name', 32);
            $table->string('setting_value', 255);

            $table->primary(['gedcom_id', 'setting_name']);

            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('user', static function (Blueprint $table): void {
            $table->integer('user_id', true);
            $table->string('user_name', 32);
            $table->string('real_name', 64);
            $table->string('email', 64);
            $table->string('password', 128);

            $table->unique('user_name');
            $table->unique('email');
        });

        DB::schema()->create('user_setting', static function (Blueprint $table): void {
            $table->integer('user_id');
            $table->string('setting_name', 32);
            $table->string('setting_value', 255);

            $table->primary(['user_id', 'setting_name']);

            $table->foreign('user_id')->references('user_id')->on('user');
        });

        DB::schema()->create('user_gedcom_setting', static function (Blueprint $table): void {
            $table->integer('user_id');
            $table->integer('gedcom_id');
            $table->string('setting_name', 32);
            $table->string('setting_value', 255);

            // Default constraint names are too long for MySQL.
            $key = DB::prefix($table->getTable() . '_primary');

            $table->primary(['user_id', 'gedcom_id', 'setting_name'], $key);
            $table->index('gedcom_id');

            $table->foreign('user_id')->references('user_id')->on('user');
            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('log', static function (Blueprint $table): void {
            $table->integer('log_id', true);
            $table->timestamp('log_time')->useCurrent();
            $table->enum('log_type', ['auth', 'config', 'debug', 'edit', 'error', 'media', 'search']);
            $table->longText('log_message');
            $table->ipAddress('ip_address');
            $table->integer('user_id')->nullable();
            $table->integer('gedcom_id')->nullable();

            $table->index('log_time');
            $table->index('log_type');
            $table->index('ip_address');
            $table->index('user_id');
            $table->index('gedcom_id');

            $table->foreign('user_id')->references('user_id')->on('user');
            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('change', static function (Blueprint $table): void {
            $table->integer('change_id', true);
            $table->timestamp('change_time')->useCurrent();
            $table->enum('status', ['accepted', 'pending', 'rejected'])->default('pending');
            $table->integer('gedcom_id');
            $table->string('xref', 20);
            $table->longText('old_gedcom');
            $table->longText('new_gedcom');
            $table->integer('user_id');

            $table->index(['gedcom_id', 'status', 'xref']);
            $table->index('user_id');

            $table->foreign('user_id')->references('user_id')->on('user');
            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('message', static function (Blueprint $table): void {
            $table->integer('message_id', true);
            $table->string('sender', 64);
            $table->ipAddress('ip_address');
            $table->integer('user_id');
            $table->string('subject', 255);
            $table->longText('body');
            $table->timestamp('created')->useCurrent();

            $table->index('user_id');

            $table->foreign('user_id')->references('user_id')->on('user');
        });

        DB::schema()->create('default_resn', static function (Blueprint $table): void {
            $table->integer('default_resn_id', true);
            $table->integer('gedcom_id');
            $table->string('xref', 20)->nullable();
            $table->string('tag_type', 15)->nullable();
            $table->enum('resn', ['none', 'privacy', 'confidential', 'hidden']);
            $table->string('comment', 255)->nullable();
            $table->timestamp('updated')->useCurrent();

            $table->unique(['gedcom_id', 'xref', 'tag_type']);

            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('individuals', static function (Blueprint $table): void {
            $table->string('i_id', 20);
            $table->integer('i_file');
            $table->string('i_rin', 20);
            $table->enum('i_sex', ['U', 'M', 'F']);
            $table->longText('i_gedcom');

            $table->primary(['i_id', 'i_file']);
            $table->unique(['i_file', 'i_id']);
        });

        DB::schema()->create('families', static function (Blueprint $table): void {
            $table->string('f_id', 20);
            $table->integer('f_file');
            $table->string('f_husb', 20)->nullable();
            $table->string('f_wife', 20)->nullable();
            $table->longText('f_gedcom');
            $table->integer('f_numchil');

            $table->primary(['f_id', 'f_file']);
            $table->unique(['f_file', 'f_id']);
            $table->index('f_husb');
            $table->index('f_wife');
        });

        DB::schema()->create('places', static function (Blueprint $table): void {
            $table->integer('p_id', true);
            $table->string('p_place', 150);
            $table->integer('p_parent_id')->nullable();
            $table->integer('p_file');
            $table->longText('p_std_soundex')->nullable();
            $table->longText('p_dm_soundex')->nullable();

            $table->index(['p_file', 'p_place']);
            $table->unique(['p_parent_id', 'p_file', 'p_place']);
        });

        DB::schema()->create('placelinks', static function (Blueprint $table): void {
            $table->integer('pl_p_id');
            $table->string('pl_gid', 20);
            $table->integer('pl_file');

            $table->primary(['pl_p_id', 'pl_gid', 'pl_file']);
            $table->index('pl_p_id');
            $table->index('pl_gid');
            $table->index('pl_file');
        });

        DB::schema()->create('dates', static function (Blueprint $table): void {
            $table->tinyInteger('d_day');
            $table->char('d_month', 5)->nullable();
            $table->tinyInteger('d_mon');
            $table->smallInteger('d_year');
            $table->mediumInteger('d_julianday1');
            $table->mediumInteger('d_julianday2');
            $table->string('d_fact', 15);
            $table->string('d_gid', 20);
            $table->integer('d_file');
            $table->enum('d_type', ['@#DGREGORIAN@', '@#DJULIAN@', '@#DHEBREW@', '@#DFRENCH R@', '@#DHIJRI@', '@#DROMAN@', '@#DJALALI@']);

            $table->index('d_day');
            $table->index('d_month');
            $table->index('d_mon');
            $table->index('d_year');
            $table->index('d_julianday1');
            $table->index('d_julianday2');
            $table->index('d_gid');
            $table->index('d_file');
            $table->index('d_type');
            $table->index(['d_fact', 'd_gid']);
        });

        DB::schema()->create('media', static function (Blueprint $table): void {
            $table->string('m_id', 20);
            $table->string('m_ext', 6)->nullable();
            $table->string('m_type', 20)->nullable();
            $table->string('m_titl', 248)->nullable();
            $table->string('m_filename', 248)->nullable();
            $table->integer('m_file');
            $table->longText('m_gedcom')->nullable();

            $table->primary(['m_file', 'm_id']);
            $table->unique(['m_id', 'm_file']);
            // Originally, this migration created an index on m_ext and m_type,
            // but we drop those columns in migration 37.
        });

        DB::schema()->create('next_id', static function (Blueprint $table): void {
            $table->integer('gedcom_id');
            $table->string('record_type', 15);
            $table->decimal('next_id', 20, 0);

            $table->primary(['gedcom_id', 'record_type']);

            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('other', static function (Blueprint $table): void {
            $table->string('o_id', 20);
            $table->integer('o_file');
            $table->string('o_type', 15);
            $table->longText('o_gedcom');

            $table->primary(['o_id', 'o_file']);
            $table->unique(['o_file', 'o_id']);
        });

        DB::schema()->create('sources', static function (Blueprint $table): void {
            $table->string('s_id', 20);
            $table->integer('s_file');
            $table->string('s_name', 255);
            $table->longText('s_gedcom');

            $table->primary(['s_id', 's_file']);
            $table->unique(['s_file', 's_id']);
            $table->index('s_name');
        });

        DB::schema()->create('link', static function (Blueprint $table): void {
            $table->integer('l_file');
            $table->string('l_from', 20);
            $table->string('l_type', 15);
            $table->string('l_to', 20);

            $table->primary(['l_from', 'l_file', 'l_type', 'l_to']);
            $table->unique(['l_to', 'l_file', 'l_type', 'l_from']);
        });

        DB::schema()->create('name', static function (Blueprint $table): void {
            $table->integer('n_file');
            $table->string('n_id', 20);
            $table->integer('n_num');
            $table->string('n_type', 15);
            $table->string('n_sort', 255);
            $table->string('n_full', 255);
            $table->string('n_surname', 255)->nullable();
            $table->string('n_surn', 255)->nullable();
            $table->string('n_givn', 255)->nullable();
            $table->string('n_soundex_givn_std', 255)->nullable();
            $table->string('n_soundex_surn_std', 255)->nullable();
            $table->string('n_soundex_givn_dm', 255)->nullable();
            $table->string('n_soundex_surn_dm', 255)->nullable();

            $table->primary(['n_id', 'n_file', 'n_num']);
            $table->index(['n_full', 'n_id', 'n_file']);
            $table->index(['n_surn', 'n_file', 'n_type', 'n_id']);
            $table->index(['n_givn', 'n_file', 'n_type', 'n_id']);
        });

        DB::schema()->create('module', static function (Blueprint $table): void {
            $table->string('module_name', 32);
            $table->enum('status', ['enabled', 'disabled'])->default('enabled');
            $table->integer('tab_order')->nullable();
            $table->integer('menu_order')->nullable();
            $table->integer('sidebar_order')->nullable();

            $table->primary('module_name');
        });

        DB::schema()->create('module_setting', static function (Blueprint $table): void {
            $table->string('module_name', 32);
            $table->string('setting_name', 32);
            $table->longText('setting_value');

            $table->primary(['module_name', 'setting_name']);

            $table->foreign('module_name')->references('module_name')->on('module');
        });

        DB::schema()->create('module_privacy', static function (Blueprint $table): void {
            $table->string('module_name', 32);
            $table->integer('gedcom_id');
            $table->enum('component', ['block', 'chart', 'menu', 'report', 'sidebar', 'tab', 'theme']);
            $table->tinyInteger('access_level');

            // Default constraint names are too long for MySQL.
            $key0 = DB::prefix($table->getTable() . '_primary');
            $key1 = DB::prefix($table->getTable() . '_ix1');

            $table->primary(['module_name', 'gedcom_id', 'component'], $key0);
            $table->unique(['gedcom_id', 'module_name', 'component'], $key1);

            $table->foreign('module_name')->references('module_name')->on('module');
            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('block', static function (Blueprint $table): void {
            $table->integer('block_id', true);
            $table->integer('gedcom_id')->nullable();
            $table->integer('user_id')->nullable();
            $table->string('xref', 20)->nullable();
            $table->enum('location', ['main', 'side'])->nullable();
            $table->integer('block_order');
            $table->string('module_name', 32);

            $table->index('module_name');
            $table->index('gedcom_id');
            $table->index('user_id');

            $table->foreign('module_name')->references('module_name')->on('module');
            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
            $table->foreign('user_id')->references('user_id')->on('user');
        });

        DB::schema()->create('block_setting', static function (Blueprint $table): void {
            $table->integer('block_id');
            $table->string('setting_name', 32);
            $table->longText('setting_value');

            $table->primary(['block_id', 'setting_name']);

            $table->foreign('block_id')->references('block_id')->on('block');
        });

        DB::schema()->create('hit_counter', static function (Blueprint $table): void {
            $table->integer('gedcom_id');
            $table->string('page_name', 32);
            $table->string('page_parameter', 32);
            $table->integer('page_count');

            // Default constraint names are too long for MySQL.
            $key = DB::prefix($table->getTable() . '_primary');

            $table->primary(['gedcom_id', 'page_name', 'page_parameter'], $key);

            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });

        DB::schema()->create('session', static function (Blueprint $table): void {
            $table->string('session_id', 256);
            $table->timestamp('session_time')->useCurrent();
            $table->integer('user_id');
            $table->ipAddress('ip_address');
            $table->longText('session_data');

            $table->primary('session_id');
            $table->index('session_time');
            $table->index(['user_id', 'ip_address']);
        });

        DB::schema()->create('gedcom_chunk', static function (Blueprint $table): void {
            $table->integer('gedcom_chunk_id', true);
            $table->integer('gedcom_id');
            $table->longText('chunk_data');
            $table->boolean('imported')->default(0);

            $table->index(['gedcom_id', 'imported']);

            $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
        });
    }
}