Add Firebird/PDO support via lsces/illuminate-firebirdHEADv2.2.7-lscfirebird

9 files changed, 115 insertions, 46 deletions

diff --git a/app/DB.php b/app/DB.php
index f71ecd8d71..1e04911cee 100644
--- a/app/DB.php
+++ b/app/DB.php
@@ -20,6 +20,8 @@ declare(strict_types=1);
 namespace Fisharebest\Webtrees;
 
 use Closure;
+use Illuminate\Container\Container;
+use Illuminate\Database\Connection;
 use Illuminate\Database\Capsule\Manager;
 use Illuminate\Database\Query\Builder;
 use Illuminate\Database\Query\Expression;
@@ -27,6 +29,8 @@ use PDO;
 use PDOException;
 use RuntimeException;
 use SensitiveParameter;
+use Firebird\Illuminate\FirebirdConnection;
+use Firebird\Illuminate\FirebirdConnector;
 
 final class DB extends Manager
 {
@@ -119,8 +123,19 @@ final class DB extends Manager
             $database = Webtrees::ROOT_DIR . 'data/' . $database . '.sqlite';
         }
 
-        $capsule = new self();
-        $capsule->addConnection([
+        $container = new Container();
+		$capsule = new self($container);
+		
+		if ($driver === self::FIREBIRD) {
+			Connection::resolverFor('firebird', 
+				function ($connection, $database, $tablePrefix, $config)
+				{
+					return new FirebirdConnection($connection, $database, $tablePrefix, $config);
+				});
+            $container->instance('db.connector.firebird', new FirebirdConnector);
+        }
+
+	    $capsule->addConnection([
             'driver'                   => $driver,
             'host'                     => $host,
             'port'                     => $port,
@@ -160,7 +175,14 @@ final class DB extends Manager
 
     public static function lastInsertId(): int
     {
-        $return = self::pdo()->lastInsertId();
+        if (self::driverName() != self::FIREBIRD) {
+            $return = self::pdo()->lastInsertId();
+        } else {
+			// Get the connection instance and access its lastInsertId property
+			$connection = self::connection(); // Get the connection instance
+        	$return = $connection->getLastInsertId();
+		}
+		        
 
         if ($return === false) {
             throw new RuntimeException('Unable to retrieve last insert ID');
diff --git a/app/Http/RequestHandlers/CheckTree.php b/app/Http/RequestHandlers/CheckTree.php
index 803c98c1f1..b6e58154e4 100644..100755
--- a/app/Http/RequestHandlers/CheckTree.php
+++ b/app/Http/RequestHandlers/CheckTree.php
@@ -91,24 +91,24 @@ final class CheckTree implements RequestHandlerInterface
 
         $q1 = DB::table('individuals')
             ->where('i_file', '=', $tree->id())
-            ->select(['i_id AS xref', 'i_gedcom AS gedcom', new Expression("'INDI' AS type")]);
+            ->select(['i_id AS xref', 'i_gedcom AS gedcom', new Expression("'INDI' AS type"), '0 AS change_id']);
         $q2 = DB::table('families')
             ->where('f_file', '=', $tree->id())
-            ->select(['f_id AS xref', 'f_gedcom AS gedcom', new Expression("'FAM' AS type")]);
+            ->select(['f_id AS xref', 'f_gedcom AS gedcom', new Expression("'FAM' AS type"), '0 AS change_id']);
         $q3 = DB::table('media')
             ->where('m_file', '=', $tree->id())
-            ->select(['m_id AS xref', 'm_gedcom AS gedcom', new Expression("'OBJE' AS type")]);
+            ->select(['m_id AS xref', 'm_gedcom AS gedcom', new Expression("'OBJE' AS type"), '0 AS change_id']);
         $q4 = DB::table('sources')
             ->where('s_file', '=', $tree->id())
-            ->select(['s_id AS xref', 's_gedcom AS gedcom', new Expression("'SOUR' AS type")]);
+            ->select(['s_id AS xref', 's_gedcom AS gedcom', new Expression("'SOUR' AS type"), '0 AS change_id']);
         $q5 = DB::table('other')
             ->where('o_file', '=', $tree->id())
-            ->select(['o_id AS xref', 'o_gedcom AS gedcom', 'o_type']);
+            ->select(['o_id AS xref', 'o_gedcom AS gedcom', 'o_type', '0 AS change_id']);
         $q6 = DB::table('change')
             ->where('gedcom_id', '=', $tree->id())
             ->where('status', '=', 'pending')
-            ->orderBy('change_id')
-            ->select(['xref', 'new_gedcom AS gedcom', new Expression("'' AS type")]);
+            ->orderBy(DB::driverName() === DB::FIREBIRD ? '4' : 'change_id')
+            ->select(['xref', 'new_gedcom AS gedcom', new Expression("'' AS type"), 'change_id']);
 
         $rows = $q1
             ->unionAll($q2)
diff --git a/app/Schema/Migration0.php b/app/Schema/Migration0.php
index 9ce32de43d..6c51f55d46 100644..100755
--- a/app/Schema/Migration0.php
+++ b/app/Schema/Migration0.php
@@ -86,7 +86,10 @@ class Migration0 implements MigrationInterface
             $key = DB::prefix($table->getTable() . '_primary');
 
             $table->primary(['user_id', 'gedcom_id', 'setting_name'], $key);
-            $table->index('gedcom_id');
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since index exists for gedcom_id will not add duplicate
+	            	$table->index('gedcom_id');
+			}
 
             $table->foreign('user_id')->references('user_id')->on('user');
             $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom');
@@ -164,7 +167,11 @@ class Migration0 implements MigrationInterface
             $table->longText('i_gedcom');
 
             $table->primary(['i_id', 'i_file']);
-            $table->unique(['i_file', 'i_id']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$table->unique(['i_file', 'i_id']);
+			}
+
         });
 
         DB::schema()->create('families', static function (Blueprint $table): void {
@@ -176,7 +183,10 @@ class Migration0 implements MigrationInterface
             $table->integer('f_numchil');
 
             $table->primary(['f_id', 'f_file']);
-            $table->unique(['f_file', 'f_id']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$table->unique(['f_file', 'f_id']);
+			}
             $table->index('f_husb');
             $table->index('f_wife');
         });
@@ -238,7 +248,10 @@ class Migration0 implements MigrationInterface
             $table->longText('m_gedcom')->nullable();
 
             $table->primary(['m_file', 'm_id']);
-            $table->unique(['m_id', 'm_file']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$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.
         });
@@ -260,7 +273,10 @@ class Migration0 implements MigrationInterface
             $table->longText('o_gedcom');
 
             $table->primary(['o_id', 'o_file']);
-            $table->unique(['o_file', 'o_id']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$table->unique(['o_file', 'o_id']);
+			}
         });
 
         DB::schema()->create('sources', static function (Blueprint $table): void {
@@ -270,7 +286,10 @@ class Migration0 implements MigrationInterface
             $table->longText('s_gedcom');
 
             $table->primary(['s_id', 's_file']);
-            $table->unique(['s_file', 's_id']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$table->unique(['s_file', 's_id']);
+			}
             $table->index('s_name');
         });
 
@@ -281,7 +300,10 @@ class Migration0 implements MigrationInterface
             $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']);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$table->unique(['l_to', 'l_file', 'l_type', 'l_from']);
+			}
         });
 
         DB::schema()->create('name', static function (Blueprint $table): void {
@@ -336,7 +358,10 @@ class Migration0 implements MigrationInterface
             $key1 = DB::prefix($table->getTable() . '_ix1');
 
             $table->primary(['module_name', 'gedcom_id', 'component'], $key0);
-            $table->unique(['gedcom_id', 'module_name', 'component'], $key1);
+			if (DB::driverName() != DB::FIREBIRD) {
+		            // since a usable index exists firebird will not add duplicate
+	            	$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');
diff --git a/app/Schema/Migration37.php b/app/Schema/Migration37.php
index 58701552e1..fa8cc73c24 100644
--- a/app/Schema/Migration37.php
+++ b/app/Schema/Migration37.php
@@ -32,8 +32,12 @@ class Migration37 implements MigrationInterface
     public function upgrade(): void
     {
         // These tables were created by webtrees 1.x, and may not exist if we first installed webtrees 2.x
-        DB::schema()->dropIfExists('site_access_rule');
-        DB::schema()->dropIfExists('next_id');
+        if (DB::schema()->hasTable('site_access_rule')) {
+			DB::schema()->drop('site_access_rule');
+		}
+        if (DB::schema()->hasTable('next_id')) {
+			DB::schema()->drop('next_id');
+		}
 
         // Split the media table into media/media_file so that we can store multiple media
         // files in each media object.
@@ -50,9 +54,12 @@ class Migration37 implements MigrationInterface
                 $table->index(['m_id', 'm_file']);
                 $table->index(['m_file', 'm_id']);
                 $table->index(['m_file', 'multimedia_file_refn']);
-                $table->index(['m_file', 'multimedia_format']);
-                $table->index(['m_file', 'source_media_type']);
-                $table->index(['m_file', 'descriptive_title']);
+				if (DB::driverName() != DB::FIREBIRD) {
+		            // firebird will not add duplicate indexs, but not quite sure why these are a problem
+					$table->index(['m_file', 'multimedia_format']);
+					$table->index(['m_file', 'source_media_type']);
+					$table->index(['m_file', 'descriptive_title']);
+				}
             });
         }
 
@@ -68,14 +75,25 @@ class Migration37 implements MigrationInterface
                 // SQLite also supports SUBSTRING() from 3.34.0 (2020-12-01)
                 $substring_function = DB::driverName() === DB::SQLITE ? 'SUBSTR' : 'SUBSTRING';
 
-                $query->select([
-                    'm_id',
-                    'm_file',
-                    new Expression($substring_function . '(m_filename, 1, 248)'),
-                    new Expression($substring_function . '(m_ext, 1, 4)'),
-                    new Expression($substring_function . '(m_type, 1, 15)'),
-                    new Expression($substring_function . '(m_titl, 1, 248)'),
-                ])->from('media');
+				if (DB::driverName() != DB::FIREBIRD) {
+	                $query->select([
+	                    'm_id',
+	                    'm_file',
+	                    new Expression($substring_function . '(m_filename, 1, 248)'),
+	                    new Expression($substring_function . '(m_ext, 1, 4)'),
+	                    new Expression($substring_function . '(m_type, 1, 15)'),
+	                    new Expression($substring_function . '(m_titl, 1, 248)'),
+	                ])->from('media');
+				} else {
+	                $query->select([
+	                    'm_id',
+	                    'm_file',
+						new Expression($substring_function . '(m_filename FROM 1 FOR 248)'),
+	                    new Expression($substring_function . '(m_ext FROM 1 FOR 4)'),
+	                    new Expression($substring_function . '(m_type FROM 1 FOR 15)'),
+	                    new Expression($substring_function . '(m_titl FROM 1 FOR 248)'),
+	                ])->from('media');
+				}
             });
 
             // The Laravel database library for SQLite can only drop one column at a time.
diff --git a/app/Schema/Migration42.php b/app/Schema/Migration42.php
index 3b24443c54..38c00ecba5 100644
--- a/app/Schema/Migration42.php
+++ b/app/Schema/Migration42.php
@@ -62,10 +62,12 @@ class Migration42 implements MigrationInterface
 
             // Default constraint names are too long for MySQL.
             $key1 = DB::prefix($table->getTable() . '_ix1');
-            $key2 = DB::prefix($table->getTable() . '_ix2');
-
             $table->unique(['gedcom_id', 'module_name', 'interface'], $key1);
-            $table->unique(['module_name', 'gedcom_id', 'interface'], $key2);
+			if (DB::driverName() != DB::FIREBIRD) {
+				// since unique index exists for fields firebird will not add duplicate
+	            $key2 = DB::prefix($table->getTable() . '_ix2');
+				$table->unique(['module_name', 'gedcom_id', 'interface'], $key2);
+			}
 
             $table->foreign('module_name')->references('module_name')->on('module')->cascadeOnDelete();
             $table->foreign('gedcom_id')->references('gedcom_id')->on('gedcom')->cascadeOnDelete();
diff --git a/app/Schema/Migration44.php b/app/Schema/Migration44.php
index 0495552c0f..8855083291 100644
--- a/app/Schema/Migration44.php
+++ b/app/Schema/Migration44.php
@@ -42,7 +42,10 @@ class Migration44 implements MigrationInterface
                 $table->double('longitude')->nullable();
 
                 $table->unique(['parent_id', 'place']);
-                $table->unique(['place', 'parent_id']);
+ 				if (DB::driverName() != DB::FIREBIRD) {
+		            // since unique index exists for fields firebird will not add duplicate
+	            	$table->unique(['place', 'parent_id']);
+		        }
 
                 $table->index(['latitude']);
                 $table->index(['longitude']);
diff --git a/app/Schema/Migration45.php b/app/Schema/Migration45.php
index b8e7a6e774..da55bed172 100644
--- a/app/Schema/Migration45.php
+++ b/app/Schema/Migration45.php
@@ -54,10 +54,9 @@ final readonly class Migration45 implements MigrationInterface
                     // SQL-Server can't use CASCADE or SET NULL constraints here, as it can't handle multiple paths
                     $table->foreign(columns: ['contact_user_id'])->references(['user_id'])->on('user');
                     $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user');
-                } else {
+                } else if !(DB::driverName() === DB::FIREBIRD) {
                     $table->foreign(columns: ['contact_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
-                    $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();
-                }
+                    $table->foreign(columns: ['support_user_id'])->references(['user_id'])->on('user')->nullOnDelete()->cascadeOnUpdate();                }
             });
         }
 
diff --git a/app/Services/AdminService.php b/app/Services/AdminService.php
index 31c372e2d1..0b053257dd 100644
--- a/app/Services/AdminService.php
+++ b/app/Services/AdminService.php
@@ -157,8 +157,8 @@ class AdminService
 
         $families = DB::table('families')
             ->where('f_file', '=', $tree->id())
-            ->groupBy([new Expression('LEAST(f_husb, f_wife)')])
-            ->groupBy([new Expression('GREATEST(f_husb, f_wife)')])
+            ->groupBy([new Expression( DB::driverName() === DB::FIREBIRD ? 'MINVALUE(f_husb, f_wife)' : 'LEAST(f_husb, f_wife)' )])
+            ->groupBy([new Expression( DB::driverName() === DB::FIREBIRD ? 'MAXVALUE(f_husb, f_wife)' : 'GREATEST(f_husb, f_wife)' )])
             ->having(new Expression('COUNT(f_id)'), '>', '1')
             ->select([new Expression(DB::groupConcat('f_id') . ' AS xrefs')])
             ->orderBy('xrefs')
diff --git a/app/Services/GedcomExportService.php b/app/Services/GedcomExportService.php
index 18018d1e1c..d5fcb1bdaa 100644..100755
--- a/app/Services/GedcomExportService.php
+++ b/app/Services/GedcomExportService.php
@@ -236,7 +236,7 @@ class GedcomExportService
                         $datum->f_gedcom ??
                         $datum->s_gedcom ??
                         $datum->m_gedcom ??
-                        $datum->o_gedcom;
+                        $datum->o_gedcom ?? '';
                 }
 
                 if ($media_path !== null && preg_match('/^0 @' . Gedcom::REGEX_XREF . '@ OBJE/', $gedcom) === 1) {
@@ -378,7 +378,7 @@ class GedcomExportService
 
         if ($sort_by_xref) {
             $query
-                ->orderBy(new Expression('LENGTH(f_id)'))
+                ->orderBy(new Expression((DB::driverName() === DB::FIREBIRD ? 'CHAR_' : '' ).'LENGTH(f_id)'))
                 ->orderBy('f_id');
         }
 
@@ -393,7 +393,7 @@ class GedcomExportService
 
         if ($sort_by_xref) {
             $query
-                ->orderBy(new Expression('LENGTH(i_id)'))
+                ->orderBy(new Expression((DB::driverName() === DB::FIREBIRD ? 'CHAR_' : '' ).'LENGTH(i_id)'))
                 ->orderBy('i_id');
         }
 
@@ -408,7 +408,7 @@ class GedcomExportService
 
         if ($sort_by_xref) {
             $query
-                ->orderBy(new Expression('LENGTH(s_id)'))
+               ->orderBy(new Expression((DB::driverName() === DB::FIREBIRD ? 'CHAR_' : '' ).'LENGTH(s_id)'))
                 ->orderBy('s_id');
         }
 
@@ -423,7 +423,7 @@ class GedcomExportService
 
         if ($sort_by_xref) {
             $query
-                ->orderBy(new Expression('LENGTH(m_id)'))
+                ->orderBy(new Expression((DB::driverName() === DB::FIREBIRD ? 'CHAR_' : '' ).'LENGTH(m_id)'))
                 ->orderBy('m_id');
         }
 
@@ -440,7 +440,7 @@ class GedcomExportService
         if ($sort_by_xref) {
             $query
                 ->orderBy('o_type')
-                ->orderBy(new Expression('LENGTH(o_id)'))
+                ->orderBy(new Expression((DB::driverName() === DB::FIREBIRD ? 'CHAR_' : '' ).'LENGTH(o_id)'))
                 ->orderBy('o_id');
         }