Version20170412120527.php 2.45 KiB
<?php
/*
 * Base de Données des Observatoires en Hydrologie
 * Copyright (C) 2012-2019 IRSTEA
 * Copyright (C) 2020-2021 INRAE
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU Affero 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 Affero General Public License
 * for more details.
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
namespace Irstea\Bdoh\Migrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 * @SuppressWarnings(PHPMD.ShortMethodName)
class Version20170412120527 extends AbstractMigration
    /**
     * @param Schema $schema
    public function up(Schema $schema)
        $this->addSql(
            <<<'SQL'
            CREATE TABLE interp_qualite (
                input1_id INTEGER NOT NULL REFERENCES qualite(id),
                input2_id INTEGER NOT NULL REFERENCES qualite(id),
                output_id INTEGER NOT NULL REFERENCES qualite(id),
                PRIMARY KEY (input1_id, input2_id)
SQL
        $this->addSql(
            <<<'SQL'
            WITH ordered_qualite AS (
                SELECT
                    DISTINCT jeu_id,
                    id,
                    ordre
                FROM qualite
                WHERE ordre IS NOT NULL
                AND code != 'gap'
            INSERT INTO interp_qualite
                SELECT
                    i1.id,
                    i2.id,
                    o.id
                FROM
                    ordered_qualite i1
                    JOIN ordered_qualite i2
                        ON (i1.jeu_id = i2.jeu_id)
                    JOIN ordered_qualite o
                        ON (i1.jeu_id = o.jeu_id AND o.ordre = bdoh_cross_quality_orders(i1.ordre, i2.ordre))
7172737475767778798081828384
ORDER by i1.ordre, i2.ordre; SQL ); } /** * @param Schema $schema */ public function down(Schema $schema) { $this->addSql('DROP TABLE interp_qualite'); } }