Extends Database Library

Hello,

I'm trying to extend the database library in order to "prepare" all the queries (raw and generated by builder); in this method I'll take care of virtual views names replacement. 

For example this query:

Code:

SELECT 1 FROM @vw_users@ LIMIT 10;

Will turn into this:

Code:

SELECT
    1
FROM
    (
        SELECT
            id, name, address
        FROM
            users
        WHERE
            status='active'
    ) AS vw_users
LIMIT 10;

Based on this definition:

PHP Code:

$views["vw_users"] = 
"
    SELECT
        id, name, address
    FROM
        users
    WHERE
        status='active'
"; 


I already read the answer posted by kilishan at https://forum.codeigniter.com/thread-741...#pid366477 but I can't resign myself to that.

How can I achieved this like I did it in CI3 extending core classes?

Regards.

You're on your own for now! If you come up for an elegant solution, please share it There are plans to rework the database layer, likely with support for NoSQL and to make extensions easier, but for now there is no easy way to do this.

Hello,

After several attempts, the only way it works is extending all the MySQLi driver like kilishan says in his referenced reply. Here I show some code detail of my solution.

I created a folder "MySQLi" inside App/Database, and then created all the files listed (extending the original driver provided):

• Builder.php
  
• Connection.php
  
• Forge.php
  
• PreparedQuery.php
  
• Result.php
  
• Utils.php
  

As example, this is the Builder.php file content:

PHP Code:

<?php namespace App\Database\MySQLi;

use CodeIgniter\Database\MySQLi\Builder as BaseBuilder;

/**
* Builder for MySQLi
*/
class Builder extends BaseBuilder
{

} 


Particularly I just needed to customize the "execute" and "query" method in Connection.php file. All the other files stays same as the original one (by extending of course, like Builder.php example), but I must be present at the same folder.

The last step is to point the database driver to the defined in \App\Database\MySQLi.

The file to edit is App/Config/Database.php, it also works with the .env file.

PHP Code:

'DBDriver' => '\App\Database\MySQLi', 


Regards.

Hello,

After several attempts, the only way it works is extending all the MySQLi driver like kilishan says in his referenced reply. Here I show some code detail of my solution.

I created a folder "MySQLi" inside App/Database, and then created all the files listed (extending the original driver provided):

• Builder.php
  
• Connection.php
  
• Forge.php
  
• PreparedQuery.php
  
• Result.php
  
• Utils.php
  

As example, this is the Builder.php file content:

PHP Code:

<?php namespace App\Database\MySQLi;

use CodeIgniter\Database\MySQLi\Builder as BaseBuilder;

/**
* Builder for MySQLi
*/
class Builder extends BaseBuilder
{

} 


Particularly I just needed to customize the "execute" and "query" method in Connection.php file. All the other files stays same as the original one (by extending of course, like Builder.php example), but I must be present at the same folder.

The last step is to point the database driver to the defined in \App\Database\MySQLi.

The file to edit is App/Config/Database.php, it also works with the .env file.

PHP Code:

'DBDriver' => '\App\Database\MySQLi', 


Regards.

Thank you!

For some reason this is not described in the official documentation. I wanted to be able to reset only part of the query (for example only the Having clause, not the entire query) and to be able to clone the builder - in case I want to have to similar queries one after another.

For those who are about to encounter this issue:
Create a new folder in App\Database\MySQLi. Copy all files from vendor\codeigniter4\framework\system\Database\MySQLi there and delete their content - in case CodeIgniter gets updated, we wouldn't want to use old code.

Then open each file and replace the content with something similar to this one:

Code:

<?php

namespace App\Database\MySQLi;

use CodeIgniter\Database\MySQLi\Connection as BaseConnection;

class Connection extends BaseConnection {

}

Thats all! 

Unfortunately it will work only for the MySQLi driver and I wanted it to work for the BaseBuilder class, but that's the limitation I'm willing to work with.

ps: If somebody is interested, here is my extended MySQLi/Builder class:

Code:

<?php

namespace App\Database\MySQLi;

use CodeIgniter\Database\MySQLi\Builder as BaseBuilder;

/**
* Builder for MySQLi
*/
class Builder extends BaseBuilder {
  /**
  * @param array $listOfPartsToReset
  * Available options: select, join, where, group, order, limit, having
  * @return Builder
  */
  public function resetPartQuery(array $listOfPartsToReset): Builder {
    foreach ($listOfPartsToReset as $item) {
      switch ($item) {
        case 'select':
          $this->resetRun(['QBSelect'  => []]);
          break;
        case 'join':
          $this->resetRun(['QBJoin'  => []]);
          break;
        case 'where':
          $this->resetRun(['QBWhere'  => []]);
          break;
        case 'group':
          $this->resetRun(['QBGroupBy'  => []]);
          break;
        case 'order':
          $this->resetRun(['QBOrderBy'  => []]);
          break;
        case 'limit':
          $this->resetRun(['QBLimit'  => false]);
          break;
        case 'having':
          $this->resetRun(['QBHaving'  => []]);
          break;
      }
    }

    return $this;
  }

  /**
  * @param array $listOfPartsToReset
  * @return Builder
  */
  public function cloneBuilder(array $listOfPartsToReset = []): Builder {
    $newInstance = clone $this;

    return $newInstance->resetPartQuery($listOfPartsToReset);
  }
}