Sql.inc and adodb

OpenEMR Development
#1

Thanks to CV19 got around to reconciling this project’s use of adodb. Does anyone have background as to why use of functions was propagated throughout the code instead of the connection object? That single choice has led to developers using local methods instead of adodb helper functions. Here are random two of these examples:

  1. Use getAll to get all records from sql - instead of current practice of getting a recordset and fetching each record in a loop
  2. Use selectLimit which is superset of selectQ since it lets you select 1 record AND also lets you select n records with an offset … something commonly needed in paging.

Here is a quick PoC example of implementing multidb without needing zend module - hardcodes used to avoid need for globals.php which brings in sql.inc and current code.

/src/Core/SqlAdodb.php
<?php
/**
* ADODB custom wrapper class to support ssl option in main.
*/

namespace OpenEMR\Core;

require_once(dirname(__FILE__, 3) . "/vendor/adodb/adodb-php/adodb.inc.php");
require_once(dirname(__FILE__, 3) . "/vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php");

class SqlAdodb extends \ADODB_mysqli
{
    private $db;

    /**
     * 
     * @param string|object $sqlconf - Either pathname for php include or standard object.
     */
    function __construct($sqlconf = null, $driver = 'mysqli') {
        // Fetch sqlconf
        if (is_null($sqlconf)) {
            $sqlconf = $GLOBALS['OE_SITE_DIR'] . "/sqlconf.php";
        }
        if (is_string($sqlconf) && file_exists($sqlconf)) {
            require_once($sqlconf);
            // sqlconf file must define all connection properties - required $host, $login, $pass, $dbase, $port
            $sqlconf = (object)(compact('host', 'port', 'login', 'pass', 'dbase', 'disable_utf8_flag'));
        }

        // Assign default port if missing
        if (!(property_exists($sqlconf, 'port'))) {
            $sqlconf->port = 3306;
        }

        // Persistent connections?
        if (!property_exists($sqlconf, 'persist')) $sqlconf->persist = true;
        $sqlconf->persist = ($sqlconf->persist ? '&persist' : '');

        // Debug connections?
        if (!property_exists($sqlconf, 'debug')) $sqlconf->persist = false;
        $sqlconf->debug = ($sqlconf->debug ? '&debug' : '');

        // Create connection using dsn (including port)
        $strDSN = sprintf(
            '%s:%s@%s/%s?port=%s%s%s', 
            $sqlconf->login, $sqlconf->pass, $sqlconf->host, $sqlconf->dbase, $sqlconf->port,
            $sqlconf->persist, $sqlconf->debug
        );
        $db = adoNewConnection($driver . '://' . $strDSN);
        if (!$db) die("Connection failed: ".$sqlconf->host);

        // Set utf8 charset - not sure why double negative (disable = false) construct is used!
        // It would be better to accept charset as attribute.
        if (!(property_exists($sqlconf, 'disable_utf8_flag')) || (!($sqlconf->disable_utf8_flag))) {
            $db->setCharset('utf8');
        }

        // All EMR code expects recordset to be associative array.  Ensure connection knows that.
        // ADODB_FETCH_ASSOC - The recordset is returned as an associative array
        $db->setFetchMode(ADODB_FETCH_ASSOC);

        /*
         * If SSL certificate files are present in the sites/<site>/documents/certificates/ directory
         * in the format <hostname>_ssl_key, set the property
         */
        $fpfx = $GLOBALS['OE_SITE_DIR'] . "/documents/certificates/".$sqlconf->host."sql_";
        foreach (['key', 'cert', 'ca'] as $type) {
            $dbProp = 'ssl_'.$type;
            if (file_exists("$fpfx$dbProp"))
                $db->$dbProp = "$fpfx$dbProp";
        }
        // Currently not used
        $db->ssl_capath = null;
        $db->ssl_cipher = null;

        $this->db = $db;
    }

    function getall($sql, $binds = []) {
        return $this->db->getAll($sql, $binds);
    }
}

/custom/test_db.php
<?php
require_once ‘…/vendor/autoload.php’;
$GLOBALS[‘OE_SITE_DIR’] = ‘/var/www/localhost/htdocs/openemr/sites/default’;

use \OpenEMR\Core\SqlAdodb;

$db1 = new SqlAdodb();

// Other db
$db2conf = [
    'host' => '192.168.1.15', 
    'login' => 'root',
    'pass' => 'ubus1',
    'dbase' => 'openemr'
];
$db2 = new SqlAdodb((object)$db2conf);

$rows1 = $db1->getall('select pid, fname, lname from patient_data where pid=?', [1]);
var_dump($rows1);

$rows2 = $db2->getall('select pid, fname, lname from patient_data where pid<?', [400]);
var_dump($rows2);