Factory Method Design Pattern

BaseSchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;

use Core\Exceptions\BadRequestHttpException;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;

class BaseSchemaFetcher implements ISchemaFetcher
{
  protected $vdb = null;
  protected ?string $driver = null;

  public function __construct(SchemaParam $vdb)
  {
    $this->vdb = $vdb;
  }

  public function getSchemas()
  {
    $connectionName = $this->createNewConnection();

    // Establish connection
    $connection = DB::connection($connectionName);

    // List database from information_schema
    $rawSchemas = $connection->select($this->getSchemaQuery());

    if (!count($rawSchemas)) {
      return [];
    }

    // Get column names from the first record (assuming it's a schema)
    $columnNames = array_keys((array) $rawSchemas[0]);

    // Extract only the schema names
    $schemas = array_map(function ($item) use ($columnNames) {
      return $item->{$columnNames[0]};
    }, $rawSchemas);

    return $schemas;
  }

  public function getTables(string $schemaName)
  {
    // Create virtual connection
    $connectionName = $this->createNewConnection();

    // Establish connection
    $connection = DB::connection($connectionName);

    // List database from information_schema
    $rawTables = $connection->select($this->getTableQuery(), ['schemaName' => $schemaName]);

    if (!count($rawTables)) {
      return [];
    }
    // Get column names from the first record (assuming it's a table)
    $columnNames = array_keys((array) $rawTables[0]);

    // Extract only the table names
    $tables = [];
    foreach ($rawTables as $table) {
      $tables[] = $table->{$columnNames[1]};
    }

    return $tables;
  }

  public function getColumns(string $schemaName, string $tableName)
  {
    // Create virtual connection
    $connectionName = $this->createNewConnection();

    // Establish connection
    $connection = DB::connection($connectionName);

    // List database from information_schema
    $rawColumns = $connection->select($this->getColumnQuery(), ['schemaName' => $schemaName, 'tableName' => $tableName]);

    if (!count($rawColumns)) {
      throw new BadRequestHttpException('Table not found');
    }

    // Get column names from the first record (assuming it's a column)
    $columnNames = array_keys((array) $rawColumns[0]);

    // Extract only the column names
    $columns = [];
    foreach ($rawColumns as $column) {
      $columns[] = [
        'tableSchema' => $column->{$columnNames[0]},
        'tableName' => $column->{$columnNames[1]},
        'columnName' => $column->{$columnNames[2]},
        'dataType' => $column->{$columnNames[3]},
      ];
    }

    return $columns;
  }

  public function createNewConnection(): string
  {
    // check if config exists
    $connectionName = "database.connections.{$this->vdb->name}";

    if (Config::has($connectionName)) {
      return $this->vdb->name;
    }

    $serverIps = explode('/', $this->vdb->serverIp);
    $url = $serverIps[0];
    $dbname = $serverIps[1] ?? '';

    $serverIps = explode(':', $url);
    $host = $serverIps[0];
    $port = $serverIps[1];

    $configValue = [
      'driver' => $this->driver,
      'host' => $host,
      'port' => $port,
      'database' => $dbname,
      'username' => $this->vdb->username,
      'password' => $this->vdb->password,
    ];

    if ($this->driver === "oracle") {
      // service name active
      if ($this->vdb->isServiceName) {
        $dbnames = explode("/", $dbname);
        $configValue['service_name'] = $dbnames[0];
        $configValue['database'] = $dbnames[1];
      }
    }

    Config::set($connectionName, $configValue);

    return $this->vdb->name;
  }

  public function ping(): bool
  {
    $connectionName = $this->createNewConnection();
    $connection = DB::connection($connectionName);

    try {
      $connection->getPdo();

      return true;
    } catch (\Throwable $th) {
      return false;
    }
  }

  public function execute(string $query, array $bindings = []): array
  {
    try {
      $connectionName = $this->createNewConnection();
      $connection = DB::connection($connectionName);

      // Execute the query
      return $connection->select($query, $bindings);
    } catch (\Throwable $th) {
      $message = $th->getMessage();

      // explode \n and get index 0
      $errorParts = explode("\n", $message);
      $errorLine = $errorParts[0] ?? 'ERROR Query: ' . $message;

      throw new BadRequestHttpException($errorLine);
    }
  }



  public function getSchemaQuery()
  {
    return "";
  }

  public function getTableQuery()
  {
    return "";
  }

  public function getColumnQuery()
  {
    return "";
  }

  public function wrapQuery(string $query, int $limit): string
  {
    return "";
  }

  public function wrapCount(string $query, string $columnIndex): string
  {
    return "";
  }
}

ISchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;

interface ISchemaFetcher
{
    public function __construct(SchemaParam $vdb);

    public function createNewConnection(): string;

    public function getSchemas();
    public function getTables(string $schemaName);
    public function getColumns(string $schemaName, string $tableName);

    public function ping(): bool;

    public function execute(string $query, array $bindings = []): array;

    public function wrapQuery(string $query, int $limit): string;

    public function wrapCount(string $query, string $columnIndex): string;
}

MariadbSchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;

use Core\Exceptions\BadRequestHttpException;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;

class MariadbSchemaFetcher extends BaseSchemaFetcher
{
    protected ?string $driver = 'mariadb';

    public function getSchemaQuery()
    {
        return "select 
                  table_schema
                from information_schema.columns 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys')
                group by table_schema";
    }

    public function getTableQuery()
    {
        return "select 
                  table_schema, 
                  table_name
                from information_schema.tables 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys') and table_schema = :schemaName
                group by table_schema, table_name";
    }

    public function getColumnQuery()
    {
        return "select 
                  table_schema, 
                  table_name,
                  column_name,
                  data_type 
                from information_schema.columns 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys') and table_schema = :schemaName and table_name = :tableName
                order by table_schema asc, table_name asc, ORDINAL_POSITION asc";
    }

    public function wrapQuery(string $query, int $limit): string
    {
        return "select * from ($query) as t limit $limit";
    }

    public function wrapCount(string $query, string $columnIndex): string
    {
        return "SELECT COUNT($columnIndex) as total FROM ($query) as t";
    }
}

MysqlSchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;

class MysqlSchemaFetcher extends BaseSchemaFetcher
{
    protected ?string $driver = 'mysql';

    public function getSchemaQuery()
    {
        return "select 
                  table_schema
                from information_schema.columns 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys')
                group by table_schema";
    }

    public function getTableQuery()
    {
        return "select 
                  table_schema, 
                  table_name
                from information_schema.tables 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys') and table_schema = :schemaName
                group by table_schema, table_name";
    }

    public function getColumnQuery()
    {
        return "select 
                  table_schema, 
                  table_name,
                  column_name,
                  data_type 
                from information_schema.columns 
                where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys') and table_schema = :schemaName and table_name = :tableName
                order by table_schema asc, table_name asc, ORDINAL_POSITION asc";
    }

    public function wrapQuery(string $query, int $limit): string
    {
        return "select * from ($query) as t limit $limit";
    }

    public function wrapCount(string $query, string $columnIndex): string
    {
        return "SELECT COUNT($columnIndex) as total FROM ($query) as t";
    }
}

OracleSchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;


class OracleSchemaFetcher extends BaseSchemaFetcher
{
    protected ?string $driver = 'oracle';

    public function getSchemaQuery()
    {
        return "SELECT
                    OWNER AS TABLE_SCHEMA
                FROM
                    ALL_TAB_COLUMNS
                WHERE
                    OWNER NOT IN ('SYS', 'SYSTEM', 'APEX_040000', 'CTXSYS', 'FLOWS_FILES', 'MDSYS', 'XDB', 'OUTLN')
                GROUP BY OWNER";
    }

    public function getTableQuery()
    {
        return "SELECT
                    OWNER AS TABLE_SCHEMA,
                    TABLE_NAME
                FROM
                    ALL_TAB_COLUMNS
                WHERE
                    OWNER NOT IN ('SYS', 'SYSTEM', 'APEX_040000', 'CTXSYS', 'FLOWS_FILES', 'MDSYS', 'XDB', 'OUTLN')
                    AND OWNER = :schemaName
                GROUP BY OWNER, TABLE_NAME";
    }

    public function getColumnQuery()
    {
        return "SELECT
                    OWNER AS TABLE_SCHEMA,
                    TABLE_NAME,
                    COLUMN_NAME,
                    DATA_TYPE,
                    OWNER
                FROM
                    ALL_TAB_COLUMNS
                WHERE
                    OWNER NOT IN ('SYS', 'SYSTEM', 'APEX_040000', 'CTXSYS', 'FLOWS_FILES', 'MDSYS', 'XDB', 'OUTLN')
                    AND OWNER = :schemaName
                    AND TABLE_NAME = :tableName
                ORDER BY
                    OWNER ASC,
                    TABLE_NAME ASC,
                    COLUMN_ID ASC";
    }

    public function wrapQuery(string $query, int $limit): string
    {
        return "SELECT * FROM ({$query}) t WHERE ROWNUM <= $limit";
    }

    public function wrapCount(string $query, string $columnIndex): string
    {
        return "SELECT COUNT({$columnIndex}) as total FROM ({$query}) AS subquery";
    }
}

PostgreSchemaFetcher.php

<?php

namespace Modules\Vdb\Components\Vdb;

class PostgreSchemaFetcher extends BaseSchemaFetcher
{
    protected ?string $driver = 'pgsql';

    public function getSchemaQuery()
    {
        return "select 
                TABLE_SCHEMA
              from information_schema.columns 
              where TABLE_SCHEMA not in ('pg_catalog', 'information_schema')
              group by TABLE_SCHEMA";
    }

    public function getTableQuery()
    {
        return "select 
                TABLE_SCHEMA, 
                TABLE_NAME 
              from information_schema.columns 
              where TABLE_SCHEMA not in ('pg_catalog', 'information_schema')
              and TABLE_SCHEMA = :schemaName
              group by TABLE_SCHEMA,TABLE_NAME";
    }

    public function getColumnQuery()
    {
        return "select 
                TABLE_SCHEMA, 
                TABLE_NAME,
                COLUMN_NAME,
                DATA_TYPE 
              from information_schema.columns 
              where TABLE_SCHEMA not in ('pg_catalog', 'information_schema')
              and TABLE_SCHEMA = :schemaName and TABLE_NAME = :tableName
              order by TABLE_SCHEMA asc, TABLE_NAME asc, ordinal_position asc";
    }

    public function wrapQuery(string $query, int $limit): string
    {
        return "select * from ($query) as t limit $limit";
    }

    public function wrapCount(string $query, string $columnIndex): string
    {
        return "SELECT COUNT($columnIndex) as total FROM ($query) as t";
    }
}

SchemaParam.php

<?php

namespace Modules\Vdb\Components\Vdb;

class SchemaParam
{
    public function __construct(
        public string $name,
        public string $serverIp,
        public string $databaseType,
        public string $username,
        public string $password,
        public bool $isServiceName = false
    ) {}
}