<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 See docs/LICENSE.md for full licensing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/*EXTRA FUNCTIONS: odbc\_.+*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    core_database_drivers
 */

/*
This driver works by ODBC. You create a mdb database in access, then create a mapping in the
ODBC part of control panel. You need to add a 'System DSN' (the DSN is the database name mapping
to the mdb file). In the properties there is option to choose username and password.

We have not used the PHP DB2 extension, although we probably could have done so (http://php.net/manual/en/book.ibm-db2.php).
*/

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__database__ibm()
{
    cms_ini_set('odbc.defaultlrl', '20M');
}

/**
 * Database driver class.
 *
 * @package core_database_drivers
 */
class Database_Static_ibm extends DatabaseDriver
{
    protected $cache_db = [];

    protected $table_prefix;

    /**
     * Set up the database driver.
     *
     * @param  string $table_prefix Table prefix
     */
    public function __construct(string $table_prefix)
    {
        $this->table_prefix = $table_prefix;
    }

    /**
     * Get the default user for making db connections (used by the installer as a default).
     *
     * @return string The default user for db connections
     */
    public function default_user() : string
    {
        return ''; // db2admin  ... ODBC does not need to use this
    }

    /**
     * Get the default password for making db connections (used by the installer as a default).
     *
     * @return string The default password for db connections
     */
    public function default_password() : string
    {
        return '';
    }

    /**
     * Get a database connection. This function shouldn't be used by you, as a connection to the database is established automatically.
     *
     * @param  boolean $persistent Whether to create a persistent connection
     * @param  string $db_name The database name
     * @param  string $db_host The database host (the server)
     * @param  string $db_user The database connection username
     * @param  string $db_password The database connection password
     * @param  boolean $fail_ok Whether to on error echo an error and return with a null, rather than giving a critical error
     * @return ?mixed A database connection (null: failed)
     */
    public function get_connection(bool $persistent, string $db_name, string $db_host, string $db_user, string $db_password, bool $fail_ok = false)
    {
        if ($db_host != 'localhost') {
            fatal_exit(do_lang_tempcode('ONLY_LOCAL_HOST_FOR_TYPE'));
        }

        // Potential caching
        if (isset($this->cache_db[$db_name][$db_host])) {
            return $this->cache_db[$db_name][$db_host];
        }

        if (!function_exists('odbc_connect')) {
            $error = 'The ODBC PHP extension not installed (anymore?). You need to contact the system administrator of this server.';
            if ($fail_ok) {
                echo ((running_script('install')) && (get_param_string('type', '') == 'ajax_db_details')) ? strip_html($error) : $error;
                return null;
            }
            critical_error('PASSON', $error);
        }

        $connection = $persistent ? @odbc_pconnect($db_name, $db_user, $db_password) : @odbc_connect($db_name, $db_user, $db_password);
        if ($connection === false) {
            $error = 'Could not connect to database-server (' . odbc_errormsg() . ', ' . cms_error_get_last() . ')';
            if ($fail_ok) {
                echo ((running_script('install')) && (get_param_string('type', '') == 'ajax_db_details')) ? strip_html($error) : $error;
                return null;
            }
            critical_error('PASSON', $error);
        }

        $this->cache_db[$db_name][$db_host] = $connection;
        return $connection;
    }

    /**
     * Get a map of software field types, to actual database types.
     *
     * @param  boolean $for_alter Whether this is for adding a table field
     * @return array The map
     */
    public function get_type_remap(bool $for_alter = false) : array
    {
        $type_remap = [
            'AUTO' => $for_alter ? 'integer' : 'integer GENERATED BY DEFAULT AS IDENTITY',
            'AUTO_LINK' => 'integer',
            'INTEGER' => 'integer',
            'UINTEGER' => 'bigint',
            'SHORT_INTEGER' => 'smallint',
            'REAL' => 'real',
            'BINARY' => 'smallint',
            'MEMBER' => 'integer',
            'GROUP' => 'integer',
            'TIME' => 'bigint',
            'LONG_TRANS' => 'bigint',
            'SHORT_TRANS' => 'bigint',
            'LONG_TRANS__COMCODE' => 'integer',
            'SHORT_TRANS__COMCODE' => 'integer',
            'SHORT_TEXT' => 'varchar(255)',
            'TEXT' => 'lvarchar(4000)', // Set consistently as 4000 across all drivers due to SQL Server having the lowest limit ; this field type should only be used as an alternative to LONG_TEXT that can be defaulted to '' if not specified, necessary for adding fields to the table's of external systems
            'LONG_TEXT' => 'clob',
            'ID_TEXT' => 'varchar(80)',
            'MINIID_TEXT' => 'varchar(40)',
            'IP' => 'varchar(40)',
            'LANGUAGE_NAME' => 'varchar(5)',
            'TOKEN' => 'varchar(15)',
            'URLPATH' => 'varchar(255)',
        ];
        return $type_remap;
    }

    /**
     * Get SQL for creating a new table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  array $fields A map of field names to software field types (with *#? encodings)
     * @param  mixed $connection The DB connection to make on
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  boolean $save_bytes Whether to use lower-byte table storage, with trade-offs of not being able to support all unicode characters; use this if key length is an issue
     * @return array List of SQL queries to run
     */
    public function create_table__sql(string $table_name, array $fields, $connection, string $raw_table_name, bool $save_bytes = false) : array
    {
        $type_remap = $this->get_type_remap();

        $_fields = '';
        $keys = '';
        foreach ($fields as $name => $type) {
            if ($type[0] == '*') { // Is a key
                $type = substr($type, 1);
                if ($keys != '') {
                    $keys .= ', ';
                }
                $keys .= $name;
            }

            if ($type[0] == '?') { // Is perhaps null
                $type = substr($type, 1);
                $perhaps_null = '';
            } else {
                $perhaps_null = 'NOT NULL';
            }

            $type = isset($type_remap[$type]) ? $type_remap[$type] : $type;

            $_fields .= '    ' . $name . ' ' . $type;

            // We specify default values for special Comcode fields, so we don't need to worry about populating them when manually editing the database
            if (substr($name, -13) == '__text_parsed') {
                $_fields .= ' DEFAULT \'\'';
            } elseif (substr($name, -13) == '__source_user') {
                $_fields .= ' DEFAULT ' . strval(db_get_first_id());
            }

            $_fields .= ' ' . $perhaps_null . ',' . "\n";
        }

        $query = 'CREATE TABLE ' . $table_name . ' (' . "\n" . $_fields . '    PRIMARY KEY (' . $keys . ")\n)";
        return [$query];
    }

    /**
     * Get SQL for renaming a table.
     *
     * @param  ID_TEXT $old Old name
     * @param  ID_TEXT $new New name
     * @return string SQL query to run
     */
    public function rename_table__sql(string $old, string $new) : string
    {
        return 'RENAME TABLE ' . $old . ' TO ' . $new;
    }

    /**
     * Find the maximum number of indexes supported.
     *
     * @return ?integer Maximum number of indexes (null: no limit or inconsequentially-large limit)
     */
    public function get_max_indexes() : ?int
    {
        return null;
    }

    /**
     * Get SQL for changing the type of a DB field in a table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $db_type The new field type
     * @param  boolean $may_be_null If the field may be null
     * @param  ?boolean $is_autoincrement Whether it is an autoincrement field (null: could not set it, returned by reference)
     * @param  ID_TEXT $new_name The new field name
     * @return array List of SQL queries to run
     */
    public function alter_table_field__sql(string $table_name, string $name, string $db_type, bool $may_be_null, ?bool &$is_autoincrement, string $new_name) : array
    {
        if ($is_autoincrement) {
            $is_autoincrement = null; // Error
        }

        $sql_type = $db_type . ' ' . ($may_be_null ? 'NULL' : 'NOT NULL');

        $delimiter_start = $this->get_delimited_identifier(false);
        $delimiter_end = $this->get_delimited_identifier(true);

        $queries = [];

        $queries[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $delimiter_start . $name . $delimiter_end . ' SET DATA TYPE ' . $sql_type;

        if ($name != $new_name) {
            $queries[] = 'ALTER TABLE ' . $table_name . ' RENAME COLUMN ' . $delimiter_start . $name . $delimiter_end . ' TO ' . $new_name;
        }

        return $queries;
    }

    /**
     * Get SQL for creating a table index.
     *
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  ID_TEXT $index_name The index name (not really important at all)
     * @param  string $_fields Part of the SQL query: a comma-separated list of fields to use on the index
     * @param  mixed $connection_read The DB connection, may be used for running checks
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  string $unique_key_fields The name of the unique key field for the table
     * @param  string $table_prefix The table prefix
     * @return array List of SQL queries to run
     */
    public function create_index__sql(string $table_name, string $index_name, string $_fields, $connection_read, string $raw_table_name, string $unique_key_fields, string $table_prefix) : array
    {
        if ($index_name[0] == '#') {
            return [];
        }
        return ['CREATE INDEX ' . $index_name . '__' . $table_name . ' ON ' . $table_name . '(' . $_fields . ')'];
    }

    /**
     * Get SQL for deleting a table index.
     *
     * @param  ID_TEXT $table_name The name of the table the index is on
     * @param  ID_TEXT $index_name The index name
     * @return ?string SQL query to run (null: not supported)
     */
    public function drop_index__sql(string $table_name, string $index_name) : ?string
    {
        return 'DROP INDEX ' . $index_name . '__' . $table_name;
    }

    /**
     * Get SQL for changing the primary key of a table.
     *
     * @param  string $table_prefix The table prefix
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  array $new_key A list of fields to put in the new key
     * @return array List of SQL queries to run
     */
    public function change_primary_key__sql(string $table_prefix, string $table_name, array $new_key) : array
    {
        $queries = [];
        $queries[] = 'ALTER TABLE ' . $table_prefix . $table_name . ' DROP PRIMARY KEY';
        if (!empty($new_key)) {
            $queries[] = 'ALTER TABLE ' . $table_prefix . $table_name . ' ADD PRIMARY KEY (' . implode(',', $new_key) . ')';
        }
        return $queries;
    }

    /**
     * Adjust an SQL query to apply offset/limit restriction.
     *
     * @param  string $query The complete SQL query
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  integer $start The start row to affect
     */
    public function apply_sql_limit_clause(string &$query, ?int $max = null, int $start = 0)
    {
        if ($max !== null) {
            $max += $start;

            if ((cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'SELECT ') || (cms_strtoupper_ascii(substr(ltrim($query), 0, 8)) == '(SELECT ')) { // Unfortunately we can't apply to DELETE FROM and update :(. But its not too important, LIMIT'ing them was unnecessarily anyway
                $query .= ' FETCH FIRST ' . strval($max + $start) . ' ROWS ONLY';
            }
        }
    }

    /**
     * Find whether expression ordering can happen using ALIASes from the SELECT clause.
     *
     * @return boolean Whether it is
     */
    public function has_expression_ordering_by_alias() : bool
    {
        return true;
    }

    /**
     * This function is a very basic query executor. It shouldn't usually be used by you, as there are abstracted versions available.
     *
     * @param  string $query The complete SQL query
     * @param  mixed $connection The DB connection
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  integer $start The start row to affect
     * @param  boolean $fail_ok Whether to output an error on failure
     * @param  boolean $get_insert_id Whether to get the autoincrement ID created for an insert query
     * @param  boolean $save_as_volatile Whether we are saving as a 'volatile' file extension
     * @return ?mixed The results (null: no results), or the insert ID
     */
    public function query(string $query, $connection, ?int $max = null, int $start = 0, bool $fail_ok = false, bool $get_insert_id = false, bool $save_as_volatile = false)
    {
        $this->apply_sql_limit_clause($query, $max, $start);

        if ((cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'SELECT ') || (cms_strtoupper_ascii(substr(ltrim($query), 0, 8)) == '(SELECT ')) { // Unfortunately we can't apply to DELETE FROM and update :(. But its not too important, LIMIT'ing them was unnecessarily anyway
            $query .= ' FETCH FIRST ' . strval($max) . ' ROWS ONLY';
        }

        $results = @odbc_exec($connection, $query);
        if (($results === false) && (!$fail_ok)) {
            $err = odbc_errormsg($connection);
            if (function_exists('ocp_mark_as_escaped')) {
                ocp_mark_as_escaped($err);
            }
            if ((!running_script('upgrader')) && ((!get_mass_import_mode()) || (current_fatalistic() > 0))) {
                if ((!function_exists('do_lang')) || (do_lang('QUERY_FAILED', null, null, null, null, false) === null)) {
                    $this->failed_query_exit(htmlentities('Query failed: ' . $query . ' : ' . $err));
                }

                $this->failed_query_exit(do_lang_tempcode('QUERY_FAILED', escape_html($query), ($err)));
            } else {
                $this->failed_query_echo(htmlentities('Database query failed: ' . $query . ' [') . ($err) . htmlentities(']'));
                return null;
            }
        }

        if ((cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'SELECT ') || (cms_strtoupper_ascii(substr(ltrim($query), 0, 8)) == '(SELECT ') && (!$results !== false)) {
            return $this->get_query_rows($results, $query, $start);
        }

        if ($get_insert_id) {
            if (cms_strtoupper_ascii(substr(ltrim($query), 0, 7)) == 'UPDATE ') {
                return null;
            }

            $pos = strpos($query, '(');
            $table_name = substr(ltrim($query), 12, $pos - 13);

            $res2 = odbc_exec($connection, 'SELECT MAX(id) FROM ' . $table_name);
            odbc_fetch_row($res2);
            return odbc_result($res2, 1);
        }

        return null;
    }

    /**
     * Get the rows returned from a SELECT query.
     *
     * @param  resource $results The query result pointer
     * @param  string $query The complete SQL query (useful for debugging)
     * @param  integer $start Where to start reading from
     * @return array A list of row maps
     */
    protected function get_query_rows($results, string $query, int $start) : array
    {
        $out = [];
        $i = 0;

        $num_fields = odbc_num_fields($results);
        $types = [];
        $names = [];
        for ($x = 1; $x <= $num_fields; $x++) {
            $types[$x] = odbc_field_type($results, $x);
            $names[$x] = odbc_field_name($results, $x);
        }

        while (odbc_fetch_row($results)) {
            if ($i >= $start) {
                $newrow = [];

                for ($j = 1; $j <= $num_fields; $j++) {
                    $v = odbc_result($results, $j);

                    $type = $types[$j];
                    $name = cms_strtolower_ascii($names[$j]);

                    if (($type == 'INTEGER') || ($type == 'SMALLINT') || ($type == 'UINTEGER')) {
                        if ($v !== null) {
                            $newrow[$name] = intval($v);
                        } else {
                            $newrow[$name] = null;
                        }
                    } elseif ((substr($type, 0, 5) == 'FLOAT') || substr($type, 0, 6) == 'NUMBER') {
                        $newrow[$name] = floatval($v);
                    } else {
                        $newrow[$name] = $v;
                    }
                }

                $out[] = $newrow;
            }

            $i++;
        }
        odbc_free_result($results);
        return $out;
    }

    /**
     * Encode an SQL statement fragment for a conditional to see if two strings are equal.
     *
     * @param  ID_TEXT $attribute The attribute
     * @param  string $compare The comparison
     * @return string The SQL
     */
    public function string_equal_to(string $attribute, string $compare) : string
    {
        return $attribute . " LIKE '" . $this->escape_string($compare) . "'";
    }

    /**
     * Escape a string so it may be inserted into a query. If SQL statements are being built up and passed using db_query then it is essential that this is used for security reasons. Otherwise, the abstraction layer deals with the situation.
     *
     * @param  string $string The string
     * @return string The escaped string
     */
    public function escape_string(string $string) : string
    {
        $string = fix_bad_unicode($string);
        return str_replace("'", "''", $string);
    }

    /**
     * Close the database connections. We don't really need to close them (will close at exit), just disassociate so we can refresh them.
     */
    public function close_connections()
    {
        foreach ($this->cache_db as $connection) {
            foreach ($connection as $_db) {
                odbc_commit($_db);
            }
        }
    }
}
