+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Change in SQLSRV builder database name prefixes from v4.1 to v4.4 (/showthread.php?tid=88963)
Change in SQLSRV builder database name prefixes from v4.1 to v4.4 - Kaosweaver - 12-11-2023
The code is the same (using builder):
Code:
public function getAllEmployeesEvaluators() {
$forms_database = $_ENV['database.forms_prod.database'];
$builder = $this->builder();
$builder->distinct();
$builder->select("Employee_Number, Employee_Last, Employee_first, Job, Building.Description");
$builder->orderBy('Employee_Last', 'ASC');
$builder->orderBy('Employee_first', 'ASC');
$builder->join($forms_database.'..Evaluation_Takers','Employee_Number = Evaluator_ID');
$builder->join('Building','Building.Building_Code = '.$this->table.".Building_Code");
$query = $builder->get();
return $query->getResult($this->returnType);
}In v4.1.1, I get:
Code:
SELECT DISTINCT "Employee_Number", "Employee_Last", "Employee_first", "Job", "Building"."Description"
FROM "Employee"
JOIN "Forms_Test..Evaluation_Takers" ON "Employee_Number" = "Evaluatee_ID"
JOIN "Building" ON "Building"."Building_Code" = "Employee"."Building_Code"
ORDER BY "Employee_Last" ASC, "Employee_first" ASCin v4.4.3 I get:
Code:
SELECT DISTINCT "Employee_Number", "Employee_Last", "Employee_first", "Job", "Building"."Description"
FROM "Master_Test"."dbo"."Employee"
JOIN "Master_Test"."dbo"."Forms_Test..Evaluation_Takers" ON "Employee_Number" = "Evaluatee_ID"
JOIN "Master_Test"."dbo"."Building" ON "Building"."Building_Code" = "Employee"."Building_Code"
ORDER BY "Employee_Last" ASC, "Employee_first" ASCI've looked for change log notes on when this changed or how to address cross database queries, but I've not found anything. This seems like a bug, we have a qualified table name (Forms_Test..Evaluation_Takers) that doesn't need a prefix, yet the code is forcing one to happen.
Anyone have ideas on settings or some change log entry I've missed?
RE: Change in SQLSRV builder database name prefixes from v4.1 to v4.4 - Kaosweaver - 12-11-2023
Searching the *correct* previous version (edited it from 4.1.2 to 4.1.1 which is the one I am actually using) I found that the SQLSRV didn't have the join functionality in it, so the default join was being used from the base builder class.
HOWEVER (and undocumented in the v4.1.2 change log) they included the join in the SQLSRV builder.php file.
The key differences are here:
v4.1.2+
Code:
// Assemble the JOIN statement
$this->QBJoin[] = $join = $type . 'JOIN ' . $this->getFullName($table) . $cond;
return $this;v4.1.1 (using the default builder join code)
Code:
// Assemble the JOIN statement
$this->QBJoin[] = $join = $type . 'JOIN ' . $table . $cond;
return $this;and the getFullName function:
Code:
private function getFullName(string $table): string
{
$alias = '';
if (strpos($table, ' ') !== false)
{
$alias = explode(' ', $table);
$table = array_shift($alias);
$alias = ' ' . implode(' ', $alias);
}
if ($this->db->escapeChar === '"')
{
return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $table) . '"' . $alias;
}
return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $table) . ']' . str_replace('"', '', $alias);
}which is why I'm seeing the differences between the two returns.
Now I just need to sort out how to either force the base builder's join version - or - override the SQLSRV join's version to check for already declared database name and schema.
RE: Change in SQLSRV builder database name prefixes from v4.1 to v4.4 - kenjis - 12-11-2023
It is this bug fix: https://github.com/codeigniter4/CodeIgniter4/pull/4246
And as you said, it is not documented in the changelog:
https://github.com/codeigniter4/CodeIgniter4/blob/develop/changelogs/CHANGELOG_4.1.md#v412-2021-05-18
RE: Change in SQLSRV builder database name prefixes from v4.1 to v4.4 - kenjis - 12-11-2023
That is a bug fix, and your usage does not seem to be assumed.
So it seems better to extend the DB classes.
See https://forum.codeigniter.com/thread-78294-post-383813.html
RE: Change in SQLSRV builder database name prefixes from v4.1 to v4.4 - Kaosweaver - 12-12-2023
(12-11-2023, 05:11 PM)kenjis Wrote: That is a bug fix, and your usage does not seem to be assumed.
So it seems better to extend the DB classes.
See https://forum.codeigniter.com/thread-78294-post-383813.html
Thanks, between the two posts I was able to solve the issue.
Sadly, the way we do alternate databases doesn't work (the double periods was causing issues), so I just ended up replacing the join function with the basebuilder one.
In case someone wants to avoid the guessing game... here is what I did:
Created class files in App/Database/Mydriver for Connection, Builder, Forge, PreparedQuery, Result and Utils
The Connection.php file:
Code:
<?php
namespace App\Database\Mydriver;
class Connection extends \CodeIgniter\Database\SQLSRV\Connection
{
/**
* Class constructor
*
* @param array $params
* @return void
*/
public function __construct($params)
{
// Create the class aliases
self::aliasClasses();
parent::__construct($params);
// This is only supported as of SQLSRV 3.0
if ($this->scrollable === null)
{
$this->scrollable = defined('SQLSRV_CURSOR_CLIENT_BUFFERED') ? SQLSRV_CURSOR_CLIENT_BUFFERED : false;
}
}
/**
* Aliases the companion classes to the driver.
*/
private static function aliasClasses()
{
foreach (['Builder', 'Forge', 'PreparedQuery', 'Result', 'Utils'] as $name)
{
$original = 'CodeIgniter\Database\SQLSRV\\' . $name;
$alias = APPPATH.'Database\Mydriver\\' . $name;
if (! class_exists($alias))
{
class_alias($original, $alias);
}
}
}
}The Builder.php file:
Code:
<?php
namespace App\Database\Mydriver;
class Builder extends \CodeIgniter\Database\SQLSRV\Builder
{
/**
* Generates the JOIN portion of the query
*
* @param RawSql|string $cond
*
* @return $this
*/
public function join(string $table, $cond, string $type = '', ?bool $escape = null)
{
if ($type !== '') {
$type = strtoupper(trim($type));
if (! in_array($type, $this->joinTypes, true)) {
$type = '';
} else {
$type .= ' ';
}
}
// Extract any aliases that might exist. We use this information
// in the protectIdentifiers to know whether to add a table prefix
$this->trackAliases($table);
if (! is_bool($escape)) {
$escape = $this->db->protectIdentifiers;
}
// Do we want to escape the table name?
if ($escape === true) {
$table = $this->db->protectIdentifiers($table, true, null, false);
}
if ($cond instanceof RawSql) {
$this->QBJoin[] = $type . 'JOIN ' . $table . ' ON ' . $cond;
return $this;
}
if (! $this->hasOperator($cond)) {
$cond = ' USING (' . ($escape ? $this->db->escapeIdentifiers($cond) : $cond) . ')';
} elseif ($escape === false) {
$cond = ' ON ' . $cond;
} else {
// Split multiple conditions
if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE)) {
$conditions = [];
$joints = $joints[0];
array_unshift($joints, ['', 0]);
for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i--) {
$joints[$i][1] += strlen($joints[$i][0]); // offset
$conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]);
$pos = $joints[$i][1] - strlen($joints[$i][0]);
$joints[$i] = $joints[$i][0];
}
ksort($conditions);
} else {
$conditions = [$cond];
$joints = [''];
}
$cond = ' ON ';
foreach ($conditions as $i => $condition) {
$operator = $this->getOperator($condition);
$cond .= $joints[$i];
$cond .= preg_match('/(\(*)?([\[\]\w\.\'-]+)' . preg_quote($operator, '/') . '(.*)/i', $condition, $match) ? $match[1] . $this->db->protectIdentifiers($match[2]) . $operator . $this->db->protectIdentifiers($match[3]) : $condition;
}
}
// Assemble the JOIN statement
$this->QBJoin[] = $type . 'JOIN ' . $table . $cond;
return $this;
}
}Then in the .ENV file:
Code:
database.default.DBDriver = 'App\Database\Mydriver'or in the App/Config/Database.php file:
Code:
public array $default = [
'DSN' => '',
'hostname' => 'localhost',
'username' => '',
'password' => '',
'database' => '',
'DBDriver' => 'App\Database\Mydriver',
...
];All of the other class files look like this (each with their own name and extended from the correct class):
Code:
<?php
namespace App\Database\Mydriver;
class Forge extends \CodeIgniter\Database\SQLSRV\Forge
{
}Also, in the Autoloader.php in the App/Config, I added this (although I don't think it is needed):
Code:
public $classmap = [
'Builder' => APPATH.'Database\Mydrvier\Builder.php',
'Forge' => APPATH.'Database\Mydrvier\Forge.php',
'PreparedQuery' => APPATH.'Database\Mydrvier\PreparedQuery.php',
'Result' => APPATH.'Database\Mydrvier\Result.php',
'Utils' => APPATH.'Database\Mydrvier\Utils.php',
];There is likely a more elegant way to solve this, but this got this part done so I could move on to the rest of the issues.