yann64
236d37976c
DbCompat (app/Support/DbCompat.php) : - like() → ilike (pgsql) ou like (mysql) - jsonRegexRaw() → data::text ~* ? (pgsql) ou CAST(data AS CHAR) REGEXP ? (mysql) - ftsRaw() → to_tsvector/plainto_tsquery (pgsql) ou null/fallback LIKE (mysql) - generatedJsonCol() → syntaxe colonne générée JSON selon le SGBD - generatedJsonNestedCol() → idem pour champs imbriqués Migrations : - create_releves_table : JSON/JSONB selon SGBD, colonnes générées adaptées, index GIN uniquement pour PostgreSQL Controllers : - LieuController (search + index) : ilike → DbCompat::like() - Admin\UserController (index) : ilike → DbCompat::like() - RechercheController : FTS + regex → DbCompat, fallback LIKE MySQL - ExportController : regex → DbCompat::jsonRegexRaw() UpdateService : - backupDatabase() : pg_dump (pgsql) ou mysqldump (mysql) - restoreBackup() : psql (pgsql) ou mysql (mysql) Docker : - docker-compose.yml : suppression Redis (plus requis) - docker-compose.mysql.yml : nouveau fichier pour dev MySQL - docker-compose.prod.yml : suppression Redis, DB_IMAGE configurable, CACHE_STORE/SESSION_DRIVER/QUEUE_CONNECTION → database par défaut .env.example : - DB_PORT commenté avec les deux valeurs (5432/3306) - CACHE_STORE et QUEUE_CONNECTION commentés (database par défaut) - Redis marqué optionnel Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
61 lines
2.7 KiB
PHP
61 lines
2.7 KiB
PHP
<?php
|
|
|
|
use App\Support\DbCompat;
|
|
use Illuminate\Database\Migrations\Migration;
|
|
use Illuminate\Database\Schema\Blueprint;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
return new class extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
$isPgsql = DbCompat::isPgsql();
|
|
|
|
Schema::create('releves', function (Blueprint $table) use ($isPgsql) {
|
|
$table->id();
|
|
$table->foreignId('source_id')->constrained()->cascadeOnDelete();
|
|
// PostgreSQL : JSONB (binary JSON, indexable par GIN)
|
|
// MySQL : JSON
|
|
if ($isPgsql) {
|
|
$table->jsonb('data');
|
|
} else {
|
|
$table->json('data');
|
|
}
|
|
$table->foreignId('created_by')->constrained('users')->restrictOnDelete();
|
|
$table->foreignId('updated_by')->constrained('users')->restrictOnDelete();
|
|
$table->timestamps();
|
|
});
|
|
|
|
// ── Colonnes générées stockées (syntaxe différente selon le SGBD) ──────
|
|
$nomExpr = DbCompat::generatedJsonCol('nom');
|
|
$prenomExpr = DbCompat::generatedJsonCol('prenom');
|
|
$dateEvtExpr = DbCompat::generatedJsonNestedCol('date_evenement.valeur');
|
|
|
|
if ($isPgsql) {
|
|
DB::statement("ALTER TABLE releves ADD COLUMN nom TEXT GENERATED ALWAYS AS ({$nomExpr}) STORED");
|
|
DB::statement("ALTER TABLE releves ADD COLUMN prenom TEXT GENERATED ALWAYS AS ({$prenomExpr}) STORED");
|
|
DB::statement("ALTER TABLE releves ADD COLUMN date_evenement TEXT GENERATED ALWAYS AS ({$dateEvtExpr}) STORED");
|
|
} else {
|
|
DB::statement("ALTER TABLE releves ADD COLUMN nom VARCHAR(255) GENERATED ALWAYS AS ({$nomExpr}) STORED");
|
|
DB::statement("ALTER TABLE releves ADD COLUMN prenom VARCHAR(255) GENERATED ALWAYS AS ({$prenomExpr}) STORED");
|
|
DB::statement("ALTER TABLE releves ADD COLUMN date_evenement VARCHAR(255) GENERATED ALWAYS AS ({$dateEvtExpr}) STORED");
|
|
}
|
|
|
|
// ── Index B-tree sur les colonnes générées ───────────────────────────
|
|
DB::statement('CREATE INDEX releves_nom_idx ON releves (nom)');
|
|
DB::statement('CREATE INDEX releves_prenom_idx ON releves (prenom)');
|
|
DB::statement('CREATE INDEX releves_date_evenement_idx ON releves (date_evenement)');
|
|
|
|
// ── Index GIN sur le JSON complet (PostgreSQL uniquement) ─────────────
|
|
if ($isPgsql) {
|
|
DB::statement('CREATE INDEX releves_data_gin_idx ON releves USING gin (data)');
|
|
}
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
Schema::dropIfExists('releves');
|
|
}
|
|
};
|