C:/Develope/SVN/upload/framework/classes/lepton_database.php

Public "shortcut" for executing a single mySql-query without passing values.

Parameters

string	$aQuery	A valid mySQL query.
bool	$bFetch	Fetching the result - default is false.
array	$aStorage	A storage array for the fetched results. Pass by reference!
bool	$bFetchAll	Try to get all entries. Default is true.

Returns

int If success number of affected rows.

$results_array = [];
$database->execute_query( "SELECT * from ".TABLE_PREFIX."pages WHERE page_id = ".$page_id." ", true, $results_array, false );

<?php
 
declare(strict_types=1);
 
class LEPTON_database
{
    use LEPTON_secure_database;
    
    const DESCRIBE_RAW           = 0;
    const DESCRIBE_ASSOC         = 1;
    const DESCRIBE_ONLY_NAMES    = 2;
    
    private static LEPTON_database|null $instance = null;
 
    private bool $bXrunInstalled = false;
 
     
    private string $error = '';
    
    private PDO $db_handle;
 
    public bool $bHaltOnError = true;
    
    public bool $bForceDieOnError = true;
    
    public static function getInstance(array &$settings = []): object
    {
        if (null === static::$instance)
        {
            static::$instance = new static();
            
            static::$instance->bXrunInstalled = class_exists("lib_comp", true);         
            
            static::$instance->connect($settings);
        }
        
        return static::$instance;
    }
    
    public function __construct(array &$settings = [])
    {
        $this->connect($settings);
    }
    
    public function __destruct()
    {
 
    }
    
    protected function set_error(string $error = ''): void
    {
        $this->error = $error;
    }
    
    public function get_error(): string
    {
        return $this->error;
    }
    
    public function is_error(): bool
    {
        return !empty($this->error);
    }
        
    public function get_db_handle(): object
    {
        return $this->db_handle;
    }
 
    public function get_db_key(): string
    {
        return self::$db_key;
    }  
    
    final function connect(array &$settings = []): void
    {
        if (!defined("DB_USER"))
        {
            $ini_file_name = LEPTON_PATH."/config/lepton.ini.php";
 
            if (true === file_exists($ini_file_name))
            {
                $config = parse_ini_string(";" . file_get_contents($ini_file_name), true);
                
                // [3.1]
                if (!isset($settings['host']))
                {
                    $settings['host'] = $config['database']['host'];
                }
                // [3.2]
                if (!isset($settings['user']))
                {
                    $settings['user'] = $config['database']['user'];
                }
                // [3.3]
                if (!isset($settings['pass']))
                {
                    $settings['pass'] = $config['database']['pass'];
                }
                // [3.4]
                if (!isset($settings['name']))
                {
                    $settings['name'] = $config['database']['name'];
                }
                // [3.5]
                if (!isset($settings['port']))
                {
                    $settings['port'] = $config['database']['port'];
                }
                // [3.6]
                if ((isset($config['database']['charset'])) && (!isset($settings['charset'])))
                {
                    $settings['charset'] = $config['database']['charset'];
                }
                // [3.7]
                if (!defined("TABLE_PREFIX"))
                {
                    define("TABLE_PREFIX", $config['database']['prefix']);
                }
                
                // [4]  For the new secure methods in L* IV
                if (isset($config['database']['key']))
                {
                    self::$db_key = $config['database']['key'];
                }
 
                if (isset($config['database']['options']))
                {
                    self::$default_openssl_options = intval($config['database']['options']);
                }
 
                if (isset($config['database']['cipher']))
                {
                    if ("0" === $config['database']['cipher'])
                    {
                        $config['database']['cipher'] = 0;
                    }
                    self::$default_openssl_method = $config['database']['cipher'];
                }
 
                if (isset($config['database']['iv']))
                {
                    self::$default_openssl_iv = $config['database']['iv'];
                }
 
                if (isset($config['database']['ivlen']))
                {
                    self::$default_openssl_ivlen = $config['database']['ivlen'];
                }
                
                if (isset($config['system_const']))
                {
                    foreach ($config['system_const'] as $key => $value)
                    {
                        if (!defined($key))
                        {
                            define($key, $value);
                        }
                    }
                }
                
            } else {
                // Problem: no lepton.ini.php 
                exit('<p><b>Sorry, but this installation seems to be damaged! Please contact your webmaster!</b></p>');
            }
        }
        
        // [4.1]  For the new secure methods in L* IV
        if (isset($settings['key']))
        {
            self::$db_key = $settings['key'];
        }
        if (isset($settings['options']))
        {
            self::$default_openssl_options = $settings['options'];
        }
        if (isset($settings['cipher']))
        {
            self::$default_openssl_method = $settings['cipher'];
        }
        if (isset($settings['iv']))
        {
            self::$default_openssl_iv = $settings['iv'];
        }
        if (isset($settings['ivlen']))
        {
            self::$default_openssl_ivlen = $settings['ivlen'];
        }
        
        $setup = [
            'host' => array_key_exists('host', $settings) ? $settings['host'] : DB_HOST,
            'user' => array_key_exists('user', $settings) ? $settings['user'] : DB_USERNAME,
            'pass' => array_key_exists('pass', $settings) ? $settings['pass'] : DB_PASSWORD,
            'name' => array_key_exists('name', $settings) ? $settings['name'] : DB_NAME,
            'port' => array_key_exists('port', $settings) ? $settings['port'] : DB_PORT
        ];
 
        if (array_key_exists('charset', $settings))
        {
            $setup['charset'] = $settings['charset'];
        } else {
            $setup['charset'] = (defined('DB_CHARSET') ? DB_CHARSET : "utf8");
        }
        
        // use DB_PORT only if it differs from the standard port 3306
        if ($setup['port'] !== '3306')
        {
            $setup['host'] .= ';port=' . $setup['port'];            
        }
 
       
        $dsn = "mysql:dbname=".$setup['name'].";host=".$setup['host'].";charset=".$setup['charset'];
        
        try {
        
            $this->db_handle = new PDO(
                $dsn,
                $setup['user'],
                $setup['pass'],
                array(
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_PERSISTENT => true,
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                )
            );
            
            $this->simple_query("SET NAMES '".$setup['charset']."'");
            
            if(isset($config)) { 
                if(isset( $config['database']['mode'] ))
                {
                    $this->simple_query("SET GLOBAL sql_mode='".$config['database']['mode']."'"); 
                }
                unset($config); 
            }
 
            $this->simple_query("USE `".$setup['name']."`");
 
        } catch (PDOException $oError) {
            $this->set_error( $oError->getMessage() );
            echo LEPTON_tools::display('Connection failed: ' . $oError->getMessage(),'pre','ui message');
            echo LEPTON_tools::display('Runtime Code: ' . $oError->getCode(),'pre','ui message');
        }
        
    }
          
    public function simple_query(string $sMySQL_Query = "", array $aParams = []): int
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( $sMySQL_Query );
            if (!empty($aParams))
            {
                if ((isset($aParams[0])) && (is_array($aParams[0])))
                {
                    foreach ($aParams as $temp_params)
                    {
                        $oStatement->execute($temp_params);
                    }
                } else {
                    $oStatement->execute($aParams);
                }
            } else {
                $oStatement->execute();
            }
            return $oStatement->rowCount();
        } catch (PDOException $error) 
        {
            $this->error = $error->getMessage() . "\n<p>Query: " . $sMySQL_Query . "\n</p>\n";
            $this->HandleDisplayError("301");
            return -1;  // conform to mysql
        }
    }
    
    public function get_one(string $SQL): mixed
    {
        $this->error = "";
        
        try
        {
            $oStatement = $this->db_handle->prepare($SQL);
            $oStatement->execute();
 
            $data = $oStatement->fetch(PDO::FETCH_ASSOC);
            if (false === $data)
            {
                return null;
            } else {
                return array_shift($data);
            }
        }
        catch( Exception $error ) 
        {   
            $this->error = $error->getMessage();
            $this->HandleDisplayError("GO 101");
            return null;
        }
    }
   
    
    public function list_tables(string $strip = ""): array|bool
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( "SHOW tables" );
            $oStatement->execute();
            
            $data = $oStatement->fetchAll();
        }
        catch (Exception $error) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("1");
            return false;
        }
        
        $ret_value = [];
        foreach($data as &$ref)
        {
            $ret_value[] = array_shift( $ref );
        }
        unset($ref);
        
        if ($strip != "")
        {
            foreach ($ret_value as &$ref2)
            {
                $ref2 = str_replace($strip, "", $ref2);             
            }
        }
        unset($ref2);
        
        return $ret_value;
    }
    
    public function describe_table(string $sTableName, array &$aStorage = [], int $iForm = self::DESCRIBE_RAW ): bool
    {
        $this->error = "";
        try
        {
            $oStatement=$this->db_handle->prepare( "DESCRIBE `" . $sTableName . "`" );
            $oStatement->execute();
            
            $aStorage = $oStatement->fetchAll();
 
            switch ($iForm)
            {
                case (self::DESCRIBE_ASSOC):
                    $aTemp = [];
                    foreach($aStorage as $values)
                    {
                        $aTemp[ $values["Field"] ] = $values;
                    }
                    unset($values);
                    $aStorage = $aTemp;
                    break;
                    
                case (self::DESCRIBE_ONLY_NAMES):
                    $aTemp = [];
                    foreach($aStorage as $values)
                    {
                        $aTemp[] = $values["Field"];
                    }
                    unset($values);
                    $aStorage = $aTemp;
                    break;
                    
                default:
                    // nothing - keep the storage as it is
                    break;
            }
            
            return true;
        }
        catch (Exception $error) 
        {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("2");
            return false;
        }
    }
 
    public function execute_query(string $aQuery="", bool $bFetch=false, array &$aStorage=[], bool $bFetchAll=true ) : int
    {
        $this->error = "";
        try{
            $oStatement=$this->db_handle->prepare($aQuery);
            $oStatement->execute();
 
            if (($oStatement->rowCount() > 0) && (true === $bFetch))
            {
                $aStorage = (true === $bFetchAll)
                    ? $oStatement->fetchAll()
                    : $oStatement->fetch()
                    ;
            
            }
            return $oStatement->rowCount();
        } catch( PDOException $error) {
            $this->error = $error->getMessage();
            $this->HandleDisplayError("10");
            return -1;
        }
    }
 
    public function build_and_execute(string $type, string $table_name, array $table_values, string $condition="", string $key=""): int
    {
        $this->error = "";
        switch( strtolower($type) )
        {
            case 'update':
                $q = "UPDATE `" . $table_name . "` SET ";
                foreach ($table_values as $field => $value)
                {
                    $q .= "`" . $field . "`= :" . $field . ", ";
                }
                $q = substr($q, 0, -2) . (($condition != "") ? " WHERE " . $condition : "");
                break;
               
            case 'insert':
                $keys = array_keys($table_values);
                $q = "INSERT into `" . $table_name . "` (`";
                $q .= implode("`,`", $keys) . "`) VALUES (:";
                $q .= implode(", :", $keys) . ")";
                break;
 
            case 'insert_on_duplicate_key_update':
                $keys = array_keys($table_values);
                $q = "INSERT into `" . $table_name . "` (`";
                $q .= implode("`,`", $keys) . "`) VALUES (:";
                $q .= implode(", :", $keys) . ")";
                $q .= "ON DUPLICATE KEY UPDATE";
                foreach($table_values as $field => $value)
                {
                    if ($field != $key)
                    {
                        $q .= "`" . $field . "`= :" . $field . ", ";
                    }
                }
                $q = substr($q, 0, -2);
                break;
 
            default:
                die(__line__.": build_and_execute-> type unknown!");
        }
 
        try 
        {
        
            $oStatement=$this->db_handle->prepare($q);
            $oStatement->execute( $table_values );
            return $oStatement->rowCount();
        } 
        catch( PDOException $error) 
        {
            $this->error = $error->getMessage()."\n<p>Query: ".$q."\n</p>\n";
            $this->HandleDisplayError("12");
            return -1;
        }
 
    }
 
    private function HandleDisplayError(string $sPrefix = "" ): void
    {
        if (true === $this->bHaltOnError)
        {    
            //  [1]
            $sMessage = LEPTON_tools::display(
                (($sPrefix != "") ? "[" . $sPrefix . "] " : "") . $this->error,
                "pre",
                "ui message red"
            );
            
            //  [2]
            ob_start();
                debug_print_backtrace();
                $sBugTrace = ob_get_clean();
            
            $sMessage .= LEPTON_tools::display(
                $sBugTrace,
                "pre",
                "ui message orange"
            );
            
            //  [3]
            if (true === $this->bForceDieOnError)
            {
                die($sMessage);
                
            } else {
                echo $sMessage;
            }
        }
    }
 
 
    public function query(string $sSqlQuery = ""): object|null
    {
        $this->error = "";
        try{
            $oStatement = $this->db_handle->prepare($sSqlQuery);
            $oStatement->execute();
 
            if ((true === $this->bXrunInstalled) && (true === lib_comp::$bXRunInit))
            {
                return new lib_comp_query($oStatement);
            } else {
                return $oStatement;
            } 
 
        } catch( PDOException $error ) {
            $this->set_error( $error->getMessage() );
            $this->HandleDisplayError("xRun 101");
            return null;
        }
    }
 
    public function add_column(string $table = '', string $column = '', string $desc = ''): bool
    {
        $result = []; 
        $this->execute_query(
            "DESCRIBE `" . TABLE_PREFIX . $table . "` `" . $column . "`",
            true,
            $result,
            false
        );
 
        if (empty($result)) // no entry
        {
            $this->simple_query("ALTER TABLE  `" . TABLE_PREFIX . $table . "` ADD `" . $column . "` " . $desc);
        }
 
        return true;
    }
}