-
Hrodriguez18
Hernan
-
Posts: 15
Threads: 6
Joined: Sep 2019
Reputation:
0
02-02-2021, 11:55 PM
(This post was last modified: 02-07-2021, 02:57 PM by Hrodriguez18.)
I have the following configuration using SQLSRV in database
PHP Code: 'DBPrefix' => '', 'schema' => 'adm', 'pConnect' => false,
I necessarily have to use adm schema instead of dbo, however I see that the selection statement is not taking the schema that I am assigning to it in the configuration file.
PHP Code: class UserController extends BaseController { public function index() { $userModel = new UserModel(); $entity = $userModel->find(2); dd($userModel->db->getLastQuery());
} }
result :
PHP Code: SELECT * FROM "users" WHERE "users"."id" = 2
PHP Code: SELECT * FROM "users" WHERE "users"."id" = :users.id:
PHP Code: class UserModel extends Model { protected $DBGroup = 'default'; protected $table = 'users'; protected $primaryKey = 'id'; protected $useAutoIncrement = true; protected $insertID = 0;
On the other hand, if I rename the table to "adm.user" the selection is done correctly, but the insert or update statement then if I use the getFullName it returns DB.schema.tableName => "DB". "Schema" . "schema.tablename".
-
nfaiz
Member
-
Posts: 50
Threads: 6
Joined: Apr 2015
Reputation:
3
(02-02-2021, 11:55 PM)Hrodriguez18 Wrote: I have the following configuration using SQLSRV in database
PHP Code: 'DBPrefix' => '', 'schema' => 'adm', 'pConnect' => false,
I necessarily have to use adm schema instead of dbo, however I see that the selection statement is not taking the schema that I am assigning to it in the configuration file.
PHP Code: class UserController extends BaseController { public function index() { $userModel = new UserModel(); $entity = $userModel->find(2); dd($userModel->db->getLastQuery());
} }
result :
PHP Code: SELECT * FROM "users" WHERE "users"."id" = 2
PHP Code: SELECT * FROM "users" WHERE "users"."id" = :users.id:
PHP Code: class UserModel extends Model { protected $DBGroup = 'default'; protected $table = 'users'; protected $primaryKey = 'id'; protected $useAutoIncrement = true; protected $insertID = 0;
On the other hand, if I rename the table to "adm.user" the selection is done correctly, but the insert or update statement then if I use the getFullName it returns DB.schema.tableName => "DB". "Schema" . "schema.tablename". Yes, same problem here after we changed our scheme from dbo to a different name. We prefer to use tables and schemas such as "schema.table" because sometimes we need to combine a table with other schemas in a SQL statement. We can also connect tables with other databases if necessary such as "db1.schema.table" without having to make another database connection.
-
Hrodriguez18
Hernan
-
Posts: 15
Threads: 6
Joined: Sep 2019
Reputation:
0
02-06-2021, 07:32 PM
(This post was last modified: 02-06-2021, 07:42 PM by Hrodriguez18.)
#########################################################################################################
I have found a solution for now, it works for me in the same way I will try to make a pull request to improve this situation.
system/Database/SQLSRV/Builder.php:197
PHP Code: private function getFullName(string $table): string { $table_exploded = explode('.', $table); if(count($table_exploded) === 2) { $this->db->schema = str_replace('"', '', $table_exploded[0]); $tableName = $table_exploded[1]; }else { $tableName = $table; }
if ($this->db->escapeChar === '"') { return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $tableName) . '"'; } return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $tableName) . ']'; }
I hope it works for you.
-
nfaiz
Member
-
Posts: 50
Threads: 6
Joined: Apr 2015
Reputation:
3
(02-06-2021, 07:32 PM)Hrodriguez18 Wrote: #########################################################################################################
I have found a solution for now, it works for me in the same way I will try to make a pull request to improve this situation.
system/Database/SQLSRV/Builder.php:197
PHP Code: private function getFullName(string $table): string { $table_exploded = explode('.', $table); if(count($table_exploded) === 2) { $this->db->schema = str_replace('"', '', $table_exploded[0]); $tableName = $table_exploded[1]; }else { $tableName = $table; }
if ($this->db->escapeChar === '"') { return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $tableName) . '"'; } return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $tableName) . ']'; }
I hope it works for you.
Yes pretty much same with me, except I'm breaking it to 3 segments. My Changes are;
system/Database/SQLSRV/Builder.php
PHP Code: private function getFullName(string $table): string { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = (is_null($dbase)) ? $this->db->getDatabase() : str_replace('"', '', $dbase);
$schema = (is_null($schema)) ? $this->db->schema : str_replace('"', '', $schema);
$table = str_replace('"', '', $table);
if ($this->db->escapeChar === '"') { return '"' . $dbase . '"."' . $schema . '"."' . $table . '"'; }
return '[' . $dbase . '].[' . $schema . '].[' . $table . ']'; }
system/Database/SQLSRV/Connection.php
PHP Code: protected function _listColumns(string $table = ''): string { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = ($dbase) ?: $this->database;
$schema = ($schema) ?: $this->schema;
return 'SELECT [COLUMN_NAME] ' . ' FROM [INFORMATION_SCHEMA].[COLUMNS]' . ' WHERE [TABLE_NAME] = ' . $this->escape($this->DBPrefix . $table) . ' AND [TABLE_SCHEMA] = ' . $this->escape($schema);
}
and
PHP Code: public function _fieldData(string $table): array { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = ($dbase) ?: $this->database;
$schema = ($schema) ?: $this->schema;
$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= ' . $this->escape(($table)) . ' AND [TABLE_SCHEMA] = ' . $this->escape($schema) ;
... ...
Change this file also if you are using rules. I'm not sure whether we can extend it.
system/Validation/Rules.php
PHP Code: public function is_not_unique(string $str = null, string $field, array $data): bool { // Grab any data for exclusion of a single row. list($field, $whereField, $whereValue) = array_pad(explode(',', $field), 3, null);
$fieldName = explode('.', $field);
$fieldCount = count($fieldName);
if ($fieldCount > 3) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.].%[^.]', $dbase, $schema, $table, $field);
$table = $dbase . '.' . $schema . '.' . $table; } elseif ($fieldCount > 2) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.]', $schema, $table, $field);
$table = $schema . '.' . $table; } else { // Break the table and field apart scanf($field, '%[^.].%[^.]', $table, $field); }
... ...
and
PHP Code: public function is_unique(string $str = null, string $field, array $data): bool { // Grab any data for exclusion of a single row. list($field, $ignoreField, $ignoreValue) = array_pad(explode(',', $field), 3, null);
$fieldName = explode('.', $field);
$fieldCount = count($fieldName);
if ($fieldCount > 3) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.].%[^.]', $dbase, $schema, $table, $field);
$table = $dbase . '.' . $schema . '.' . $table; } elseif ($fieldCount > 2) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.]', $schema, $table, $field);
$table = $schema . '.' . $table; } else { // Break the table and field apart scanf($field, '%[^.].%[^.]', $table, $field); }
... ...
-
Hrodriguez18
Hernan
-
Posts: 15
Threads: 6
Joined: Sep 2019
Reputation:
0
02-07-2021, 03:01 PM
(This post was last modified: 02-07-2021, 03:01 PM by Hrodriguez18.)
(02-07-2021, 12:45 AM)nfaiz Wrote: (02-06-2021, 07:32 PM)Hrodriguez18 Wrote: ################################################ ################################################ #####
He encontrado una solución por ahora, me funciona de la misma manera que intentaré hacer una solicitud de extracción para mejorar esta situación.
system / Database / SQLSRV / Builder.php: 197 Espero que funcione para usted.
PHP Code: private function getFullName(string $table): string { $table_exploded = explode('.', $table); if(count($table_exploded) === 2) { $this->db->schema = str_replace('"', '', $table_exploded[0]); $tableName = $table_exploded[1]; }else { $tableName = $table; }
if ($this->db->escapeChar === '"') { return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $tableName) . '"'; } return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $tableName) . ']'; }
Sí, más o menos lo mismo conmigo, excepto que lo estoy dividiendo en 3 segmentos. Mis cambios son;
system / Database / SQLSRV / Builder.php system / Database / SQLSRV / Connection.php y cambie este archivo también si está usando reglas. No estoy seguro de que podamos ampliarlo. system / Validation / Rules.php y
PHP Code: private function getFullName(string $table): string { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = (is_null($dbase)) ? $this->db->getDatabase() : str_replace('"', '', $dbase);
$schema = (is_null($schema)) ? $this->db->schema : str_replace('"', '', $schema);
$table = str_replace('"', '', $table);
if ($this->db->escapeChar === '"') { return '"' . $dbase . '"."' . $schema . '"."' . $table . '"'; }
return '[' . $dbase . '].[' . $schema . '].[' . $table . ']'; }
PHP Code: protected function _listColumns(string $table = ''): string { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = ($dbase) ?: $this->database;
$schema = ($schema) ?: $this->schema;
return 'SELECT [COLUMN_NAME] ' . ' FROM [INFORMATION_SCHEMA].[COLUMNS]' . ' WHERE [TABLE_NAME] = ' . $this->escape($this->DBPrefix . $table) . ' AND [TABLE_SCHEMA] = ' . $this->escape($schema);
}
PHP Code: public function _fieldData(string $table): array { list($dbase, $schema, $table) = array_pad(explode('.', $table), -3, null);
$dbase = ($dbase) ?: $this->database;
$schema = ($schema) ?: $this->schema;
$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= ' . $this->escape(($table)) . ' AND [TABLE_SCHEMA] = ' . $this->escape($schema) ;
... ...
PHP Code: public function is_not_unique(string $str = null, string $field, array $data): bool { // Grab any data for exclusion of a single row. list($field, $whereField, $whereValue) = array_pad(explode(',', $field), 3, null);
$fieldName = explode('.', $field);
$fieldCount = count($fieldName);
if ($fieldCount > 3) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.].%[^.]', $dbase, $schema, $table, $field);
$table = $dbase . '.' . $schema . '.' . $table; } elseif ($fieldCount > 2) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.]', $schema, $table, $field);
$table = $schema . '.' . $table; } else { // Break the table and field apart scanf($field, '%[^.].%[^.]', $table, $field); }
... ...
PHP Code: public function is_unique(string $str = null, string $field, array $data): bool { // Grab any data for exclusion of a single row. list($field, $ignoreField, $ignoreValue) = array_pad(explode(',', $field), 3, null);
$fieldName = explode('.', $field);
$fieldCount = count($fieldName);
if ($fieldCount > 3) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.].%[^.]', $dbase, $schema, $table, $field);
$table = $dbase . '.' . $schema . '.' . $table; } elseif ($fieldCount > 2) { // Break the table and field apart sscanf($field, '%[^.].%[^.].%[^.]', $schema, $table, $field);
$table = $schema . '.' . $table; } else { // Break the table and field apart scanf($field, '%[^.].%[^.]', $table, $field); }
... ...
make a pull request for these changes
|