<?php
/*
**	Copyright (C) 1995-2021 by the ITools Authors.
**	This file is part of ITools - the Internet Tools Library
**
**	ITools is free software; you can redistribute it and/or modify
**	it under the terms of the GNU General Public License as published by
**	the Free Software Foundation; either version 3 of the License, or
**	(at your option) any later version.
**
**	ITools is distributed in the hope that it will be useful,
**	but WITHOUT ANY WARRANTY; without even the implied warranty of
**	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
**	GNU General Public License for more details.
**
**	You should have received a copy of the GNU General Public License
**	along with this program.  If not, see <http://www.gnu.org/licenses/>.
**
**	dbi.class - UltraFlexible Database Interface 3000
*/

#[AllowDynamicProperties]
class it_dbi implements Iterator
{
	static $_global_key = 'it_dbi'; # $GLOBAL key to use for singleton

	var $_found_rows; # public: number of found rows if CALC_FOUND_ROWS was set
	var $_data;       # semi-public: current result as assoc array

	# Default configuration of dbi class
	var $_defaultconfig = array
	(
		'db' => null,
		'server' => "localhost",
		'server_update' => null,
		'user' => "itools",
		'pw' => "",
		'safety' => 1,		# 0= never die, 1=die if query invalid, 2=die also if no results
		#'keyfield' => 'ID',	# Don't set to null here, filled later by _get_field_info()
		#'charset' =>		# client charset (requires MySQL 5.0.7 or later)
		'classprefix' => "",
		'getfieldinfo' => true, # do not read schema. only select() allowed
		'localized_defaultlanguage' => "de",	# Localize fields with this suffix, e.g. copy title_de to title on read
		'throttle_writes' => 0, # sleep for 'throttle_writes' multiplied by the execution time after every write
		'ignored_warnings' => "", # regex of additional mysql warnings numbers to ignore
		'interruptible_queries' => false, # make queries interruptible by php signal handlers
		'timeout' => null, # timeout for queries
	);

	var $_key;         # Key of currently loaded record or null (public readonly)
	var $_fields;      # Array of name => array(Field,Type,Null,Key,Default,Extra,Length) of fields (public readonly)
	var $_fieldnames;  # All field names in schema for case mismatch alert
	var $_convertfunc; # Array of name => convert function (currently intval and floatval) for this field's values
	var $_link;        # DB link identifier (private)
	var $_dbid;        # string identifying db connection. used for _state_purgeshared from outside
	var $_affectedrows;# Affected rows (mysql_affected_rows() gets clobbered)
	var $_insertid;    # Last inserted id (mysqli_insert_id() gets clobbered)
	var $_writes;      # Number of (non-omittable) writes to sql using this instance


/**
 * Constructor: Initialize the DBI interface
 * @param $p optional array(key => value) of configuration data
 * @param $query Optional initial query to run
 */
function __construct($p = array(), $query = null, ...$args)
{
	# Shortcut: String config means use this table with default values
	if (!is_array($p))
		$p = array('table' => $p);

	if ($p['home'])
		$p['db'] = strtr(it::match('/www/([^/]*)', $p['home']), '.-', '__');

	# If the global singleton defaults are for this db/server/server_update then use them.
	$dp = (array)$GLOBALS[static::$_global_key]->_p;
	if ((!isset($p['db']) || ($p['db'] == $dp['db'])) && (!isset($p['server']) || ($p['server'] == $dp['server'])) && (!isset($p['server_update']) || ($p['server_update'] == $dp['server_update'])))
		$p += $dp;

	# Combine our settings with user's defaults and class defaults
	$p += (array)$GLOBALS[static::$_global_key . '_defaultconfig'] + array('db' => $GLOBALS['ULTRADB']) + $this->_defaultconfig + array('charset' => strtr(strtolower(ini_get('default_charset')), array('iso-8859-1' => 'latin1', 'utf-8' => 'utf8mb4', 'utf8' => 'utf8mb4')));
	unset($this->_defaultconfig);	# to shorten ED() output

	$this->_p = $p;

	if ($p['table'])		# Standard use: create a table object
	{
		if (!isset($GLOBALS[static::$_global_key]))
			new static::$_global_key;

		if ($p['getfieldinfo'])
			$this->_p += $this->_get_field_info();	# Get $this->_fields and p[keyfield, autoincrement, randomid]

		if (is_array($query))
			$this->select($query, ...$args); # Call with all arguments except first one
		else if (isset($query))
			$this->read($query);
	}
	else
		$GLOBALS[static::$_global_key] =& $this;
}

/**
 * function Tablename($query)
 * Constructor. Returns record object from table Tablename.
 * If $query is set, it encodes a SELECT to execute and store in the returned object, see select()
 * Note: Old second parameter $config has been deprecated and will be removed
 */ #:}

/**
 * Factory: Create classes of all database tables. Call statically.
 * @param $p array(key => value) of configuration data
 */
static function createclasses($p = array())
{
	# Make sure singleton exists
	$dbi = $GLOBALS[static::$_global_key] ?: new static::$_global_key($p);

	$p += $dbi->_p;

	$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
	$state = static::_state_get($dbid);

	if (!$tables = $state['tables'])
	{
		$tables = $dbi->tables($p);
		$state = static::_state_get($dbid);	# State could have been modified by $db->tables() call
		$state['tables'] = $tables;
		static::_state_put($dbid, $state);
	}

	foreach ($tables as $table)
	{
		# Either create class in autoloader or manually just below
		if (!class_exists($p['classprefix'] . $table))
			static::createclass(array('table' => $table) + $p);
	}
}


/**
 * Convert table given by name into a class
 */
static function createclass($p)
{
	# Shortcut: String config means use this table with default values
	if (!is_array($p))
		$p = array('table' => $p);

	# Make sure singleton exists
	$dbi = $GLOBALS[static::$_global_key] ? $GLOBALS[static::$_global_key] : new static::$_global_key(['table' => null] +  $p);
	$p += $dbi->_p;
	$dbid = $dbi->_dbid = "{$p['user']}@{$p['server']}:{$p['db']}";

	if (!isset($dbi->_tables[$dbid]))
	{
		$state = static::_state_get($dbid);
		$dbi->_tables[$dbid] = array();

		if (!($tables = $state['tables']))
		{
			$tables = $dbi->tables($p);

			$state = static::_state_get($dbid);	# State could have been modified by query above
			$state['tables'] = $tables;
			static::_state_put($dbid, $state);
		}

		foreach ($tables as $table)
			$dbi->_tables[$dbid][$table] = true;
	}

	if ($p['forcecreate'] || $dbi->_tables[$dbid][$p['table']])	# Do not generate classes for non-existant tables (can be overridden by forcecreate => true, used in tests/it_dbi.t)
	{
		$classname = $p['classprefix'] . $p['table'];

		if (substr($classname, 0, 4) != 'PMA_')	# It is designed behaviour that an error is generated if this class already exists!
		{
			$parentname = static::$_global_key;
			$code = "class $classname extends $parentname
			{
				function __construct(\$query = null, ...\$args)
				{
					foreach (\$args as \$arg)
						\$query = array_merge((array)\$query, (array)\$arg);

					parent::__construct(" . var_export($p, true) . ", \$query);
				}
			}";

			debug("it_dbi::createclass('{$p['table']}'): creating class $classname, dbid=$dbid", 5);
			eval($code);
		}
	}
}


/**
 * INTERNAL: Connect to mysql server and maintain a global link cache
 */
function _connect($p = array())
{
	$p += $this->_p;
	$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
	$state = static::_state_get($dbid);

	if ($p['reconnect'] || !($this->_link = $state['link']))
	{
		# Force new link if same server/user was seen before (mysql ignores selected db)
		if ($GLOBALS[static::$_global_key]->_connected["{$p['server']}/{$p['user']}"]++)
			list($this->_link, $error) = $this->_connect_db($p);
		else
			list($this->_link, $error) = $this->_connect_db($p);

		if (!$this->_link)
		{
			# One retry after a short delay
			it::log('sqllog', "it_dbi(): retrying DB link (_connect_db {$p['server']}, {$p['db']}): $error");
			sleep(1);
			list($this->_link, $error) = $this->_connect_db($p);
		}

		if (!$this->_link)
			$this->_fatal("_connect(): can't create DB link (_connect_db {$p['user']}@{$p['server']}, {$p['db']}): $error");

		# NOTE: This overwrites old state but that is on purpose. New link means we should refetch all info about connection
		$state['link'] = $this->_link;
		static::_state_put($dbid, $state, false);	# Store only locally as link is not shared anyway
	}
}


/**
 * INTERNAL: construct SQL expressions of changed values from tags array.
 * $force = must write all fields, dont try to optimize
 */
function _expressions($tags, $force = false)
{
	$result = array();
	$dyndata = $this->_dyndata;

	foreach((array)$tags as $field => $value)
	{
		if (is_int($field))	# No key specified; pass on unchanged
		{
			$result[$field] = $value;
			continue;
		}

		$f = trim($field, "-");
		if (!$this->_fields[$f] && it::match(",$f,", $this->_fieldnames))
			it::error("case mismatch on field $f, valid fields $this->_fieldnames");

		if ($this->_p['charset'] == "utf8") # NOTE: Mysql charset is simply utf8, not utf-8
			$value = it::any2utf8($value, "error in db-field $field");

		if (!$this->_fields[$f] && $this->_fields['dyncols'])
		{
			if (substr($field, 0, 1) === "-")
				$newdyns[$f] = $value;
			else if ($force || isset($value) && isset($dyndata[$f]) ? strval($value) !== strval($dyndata[$f]) : $value !== $dyndata[$f] || !array_key_exists($f, $dyndata))
			{
				if (is_null($value))
					$deldyns[] = $f;
				else if (is_int($value))
					$newdyns[$f] = $value;
				else
					$newdyns[$f] = $this->escape_string($value);
			}

			$alldyns[$f] = (substr($field, 0, 1) === "-" || is_int($value) ? $value : $this->escape_string($value));
			$dyndata[$f] = $value;
		}
		else if (substr($field, 0, 1) === '-')		# Unquoted value (always added)
			$result[substr($field, 1)] = $value;
		else if ($force || (isset($value) && isset($this->_data[$field]) ? strval($value) !== strval($this->_data[$field]) : $value !== $this->_data[$field] || !array_key_exists($field, $this->_data)))
		{
			if (isset($value))
				$result[$field] = $this->_escapefunc[$field] ? $this->_escapefunc[$field]($value) : $this->escape_string($value);
			else
				$result[$field] = 'NULL';
		}
	}

	if ($alldyns)
	{
		if ($force == "insert") # INSERT/REPLACE
			$result['dyncols'] = $this->_json_object($alldyns);
		else if ($newdyns || $deldyns)
		{
			$source = $this->_dyndata ? 'dyncols' : $this->escape_string('{}');
			if ($newdyns)
				$source = $this->_json_set($source, $newdyns);
			if ($deldyns)
				$source = $this->_json_remove($source, $deldyns);
			$result['dyncols'] = $source;
		}
	}

	$this->_writes += $result ? 1 : 0;

	return $result;
}

/**
 * INTERNAL: construct SQL SET clause of changed values from tags array.
 * $force = must write all fields, dont try to optimize
 */
function _set($tags, $force = false)
{
	$expressions = $this->_expressions($tags, $force);
	$append = [];
	foreach ((array)$expressions as $k => $v)
	{
		if (is_int($k)) /* no key specified; just append */
			$append[] = $v;
		else
			$strings[] = $this->escape_name($k) . "=$v";
	}

	return $strings ? 'SET ' . implode(', ', $strings) . implode(' ', $append) : '';
}

/**
 * INTERNAL: construct SQL VALUES clause of changed values from tags array.
 * $force = must write all fields, dont try to optimize
 */
function _values($tags, $force = false)
{
	$expressions = $this->_expressions($tags, $force);
	$append = [];
	foreach ((array)$expressions as $k => $v)
	{
		if (is_int($k)) /* no key specified; just append */
			$append[] = $v;
		else
		{
			$keys[] = $this->escape_name($k);
			$vals[] = $v;
		}
	}

	return $expressions ? '(' . implode(', ', $keys) . ') VALUES (' . implode(', ', $vals) . ')' . implode(' ', $append) : '';
}

/**
 * INTERNAL: construct SQL FROM clause of fields JOIN and FROM in query params
 * @see select()
 */
function _from($params, $omit_from = false)
{
	$result = $this->_p['table'];

	if (isset($params['JOIN']) || isset($params['FROM'])) # WARNING: this field gets abused for "tablename USE INDEX (fast2)
		$result = trim($params['FROM'] . " " . $params['JOIN']);

	return ($omit_from ? "" : "FROM ") . $result;
}


/**
 * Create an SQL query (the stuff after 'WHERE') according to an array of selection criteria.
 *
 * Example:
 * $sql = $table->_where(array('Type' => 'bar',
 *    'Date >=' => '1999-01-01', '-Date <' => 'NOW()',
 *    'Status' => array('foo', 'bar', 'qux'),		# same as 'Status IN' => ...
 *    'User NI' => 'chris'), 'ORDER BY Date');
 *
 * @param $params optional array of fieldname => value tupels. These are ANDed to form a WHERE clause.
 *   fieldname can contain an operator (separated by space), the default operator is '='.
 *   The special operator 'NI' specifies that the argument must be contained in a comma-separated list.
 * @param $link DB link used to escape values (not used anymore)
 * @param $omit_where Do not add 'WHERE ' to result
 * @return The generated SQL clause
 * @see select()
 * @see iterate()
 */
function _where($params)
{
	$dyncols_enabled = !strlen($params['JOIN'] . $params['FROM']);
	unset($params['JOIN'], $params['FROM']);

	if (is_array($params) && (count($params) > 0))
	{
		$query = '';
		$stringquery = '';
		$sep = '';

		foreach($params as $field => $value)
		{
			if (is_int($field)) /* no key specified; just append */
			{
				if (strcasecmp($value, 'OR'))
					$stringquery .= " $value";
				else
					$sep = ' OR ';
			}
			else if ($field == "LIMIT")
			{
				if ($value !== false) # only false no null; uninitialized values should not unintentionally omit LIMIT
					$stringquery .= " LIMIT " . (it::match('^[ ,\d]+$', $value) ?? it::error(['title' => "invalid LIMIT $value", 'body' => $params]) + 0);
			}
			else
			{
				$needs_where = true;

				if (!isset($value))
				{
					$op = 'IS';
					$qval = 'NULL';
				}
				else
				{
					if (preg_match('/^(\S+)\s+(\S.*)$/', $field, $regs))
					{
						$field = $regs[1];
						$op = strtoupper($regs[2]);
					}
					else
						$op = is_array($value) ? 'IN' : '=';

					# If field name starts with '-', the raw value is taken, no escaping is done and no quotes are put around it.
					if (substr($field, 0, 1) == '-')
					{
						$field = substr($field, 1);	# Strip that '-' sign
						$qval = $value;
					}
					else if (!is_array($value))
						$qval = $this->escape_string((string)$value);
				}

				if ($dyncols_enabled && $this->_fields['dyncols'] && !$this->_fields[$field] && strpos($field, '(') === false)
					$field = $this->_json_extract('dyncols', $field);
				else if (it::match('^\w*[A-Z]\w+$', $field, ['casesensitive' => 1]))
					$field = $this->escape_name($field);

				switch ($op)
				{
					case 'NI':
						if ($value)
						{
							$parts = array();
							foreach ((array)$value as $val)
								$parts[] = "CONCAT(',',$field,',') LIKE " . $this->escape_string("%,$val,%");

							$query .= $sep . "$field IS NOT NULL AND (" . implode(" OR ", $parts) . ")";	# Check for IS NOT NULL to take advantage of index
						}
						else
							$query .= $sep . "TRUE";
						break;

					case 'MATCH':
						$qval = implode(' ', (array)$value);
						$query .= "$sep$op ($field) AGAINST (" . $this->escape_string($qval) . " IN BOOLEAN MODE)";
						break;

					case 'IN':
					case 'NOT IN':
						if (is_array($value))
						{
							if ($value)
							{
								$qvals = array();

								foreach ($value as $val)
									$qvals[] = $this->escape_string($val);

								$query .= "$sep$field $op (" . implode(",", $qvals) . ")";	 # null is mapped to ''
							}
							else
								$query .= $sep . (($op == 'IN') ? "FALSE" : "TRUE");

							break;
						}
						/* FALLTHROUGH */

					default:
						if (isset($qval))
							$query .= "$sep$field $op $qval";
						else
							it::fatal('Undefined $qval when constructing query due to invalid $value (array)');
						break;
				}
				$sep = ' AND ';
			}
		}

		$query .= $stringquery;

		if ($needs_where)
			$query = "WHERE $query";
	}

	return $query;
}

/**
 * Internal: Output class name::error message and terminate execution.
 */
function _fatal($text, $body = null)
{
	it::fatal(['title' => $this->_error($text) . ", DB: " . $this->_p['db'] . ", Server: " . $this->_p['server'], 'body' => $body]);
	/* NOT REACHED */
}

/**
 * Hook to postprocess data after reading a record.
 * This is a stub-function that can be overloaded.
 * @param $data Data of record read from db
 * @return Record data including postprocess modifications
 */
static function _read_postprocess($data)
{
	return $data;
}


/**
 * Hook to preprocess data before writing the record.
 * This is a stub-function that can be overloaded.
 * @param $data Update/create data tags
 * @return Record data to be written including preprocess modifications/additions/deletions
 */
static function _write_preprocess($data)
{
	return $data;
}


/**
 * Return an array of all tables of this database
 */
function tables($p = array())
{
	return $this->_tables($p);
}


/**
 * Clear record
 */
function clear()
{
	foreach ((array)$this->_fields + (array)$this->_localizedfields + (array)$this->_data as $field => $dummy)
		unset($this->$field);
	unset($this->_key, $this->_data);
}


/**
 * Semi-internal: send a raw SQL query and return mysql result value
 * @param $query complete SQL query string
 * @return MySQL result which is false for errors. May die on error if safety is big enough
 */
function query($query, $p = array())
{
	$p += $this->_p;
	$start = gettimeofday(true);

	if (($writing = !it::match('^(EXPLAIN|SELECT|SHOW)', $query, array('utf8' => false))))
	{
		if ($p['server_update'])
		{
			debug("switching to update server \"{$p['server_update']}\"", 5);
			$this->_p['server'] = $p['server'] = $p['server_update'];
			unset($this->_p['server_update'], $p['server_update'], $this->_link);
		}
		else if ($p['server'] == "localhost" && $p['db'] == $GLOBALS['ULTRADB'] && preg_grep('/replicate-do/', (array)@it::file($GLOBALS['ULTRAHOME'] . "/etc/my.cnf")))
			if (($t = @it::file($GLOBALS['ULTRAHOME'] . "/doc/machines.txt")) && preg_grep("/^" . gethostname() . "/", array_slice($t, 2)))
				it::error("local mysql write on a replication slave machine?");
	}

	$this->_connect($p);	# must be called after update server switching code

	debug("{$p['user']}@{$p['server']}:{$p['db']}" . '.' . get_class($this) . "::query(\"$query\")", 4);

	if (!($result = $this->_query($query, $p)))
	{
		if ($result === null || !$p['safety'])
			return false;
		$this->_fatal("query() failed", $query);
	}
	else if (it::match('^(CREATE|ALTER|DROP) ', $query, array('utf8' => false)))
	{
		# Purge cache for schema changes (after modifying table)
		$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
		static::_state_purgeshared($dbid);
	}

	if ($writing && $this->_p['throttle_writes'])
	{
		it::log('debug', 'dbi-throttle', round(1000000 * (gettimeofday(true) - $start) * $this->_p['throttle_writes']));
		usleep(round(1000000 * (gettimeofday(true) - $start) * $this->_p['throttle_writes']));
	}

	$msec = round(1000 * (gettimeofday(true) - $start));
	$slow = $msec >= 2000;
	if ($GLOBALS['debug_sqllog'] || $GLOBALS['debug_sqltrace'] || $slow)
	{
		$backtrace = (EDC('sqltrace') || $slow) ? it_debug::backtrace(1) : null;
		$truncquery = strlen($query) > 1000 ? mb_substr($query, 0, 1000) . '...' : $query;
		it::log('sqllog', "$msec\t$truncquery\t$backtrace\t" . $this->_p['server'] . ($slow ? "\tSLOW" : ""));

		$this->_sqllog[] = array(
			'time' => $msec,
			'query' => $query,
		) + ($backtrace ? array('backtrace' => $backtrace) : array());
	}

	return $result;
}


/**
 * Read a single record by primary key, not destroying old query result $this->_result
 * @param $id primary key. If null, record is cleared
 * @return True if record could be read, false if not.
 */
function read($id=null)
{
	$old_result = $this->_result;
	$old_nofetch = $this->_nofetch;
	$result = $this->select(array($this->_p['keyfield'] => $id));
	$this->_result = $old_result;
	$this->_nofetch = $old_nofetch;
	return $result;
}


/**
 * Select a set of records from table and fetch the first one
 * @param $query Vararg. Arrays of (field => value) pairs or plain string query parts. Default: Select all
 *   Fields will be joined by AND
 *   Fields can contain a compare operator: 'name LIKE' => "j%" or 'amount >' => 100
 *   Fields can start with - to prevent quoting of right side: '-modified' => "CURDATE()"
 * @param $query['SELECT'] expression to be returned, e.g. 'SELECT' => 'DISTINCT foo'. defaults to '*'
 * @param $query['FROM'] table names to use for joins, e.g. 'FROM' => 'tableA LEFT JOIN tableB ON a=b'
 * @param $query['JOIN'] like 'FROM'
 * @param $query['CALC_FOUND_ROWS'] if true, if true member var _found_rows contains number of matching rows before LIMIT
 * @param $query['LIMIT'] max number of rows to return; false for no limit
 * @param $query['NOFETCH'] if true the first row is not prefetched (e.g. when directly using _result)
 * @return Number of matching rows, use iterate() to fetch the next record
 * @see iterate()
 * @see _where()
 */
function select(...$args)
{
	$query = array();
	foreach ($args as $arg)
		$query = array_merge($query, (array)$arg);

	$this->_connect();

	$result = 0;
	$calc_found_rows = false;

	$what = '*';
	if (isset($query['SELECT']))
	{
		$what = $query['SELECT'];
		unset($query['SELECT']);
	}

	unset($this->_found_rows);
	if (isset($query['CALC_FOUND_ROWS']) && $query['CALC_FOUND_ROWS'])
	{
		$calc_found_rows = true;
		$what = 'SQL_CALC_FOUND_ROWS '.$what;
	}
	unset($query['CALC_FOUND_ROWS']);	# Always unset, so CALC_FOUND_ROWS => false doesn't generate bogus query

	if (EDC('nocache') && static::$_global_key == 'it_dbi')
		$what = 'SQL_NO_CACHE ' . $what;

	$nofetch = $this->_nofetch = $query['NOFETCH'];
	unset($query['NOFETCH']);

	$this->clear();
	if ($this->_result = $this->query($sql = "SELECT $what " . $this->_from($query) . " " . $this->_where($query)))
	{
		$result = $this->_num_rows($this->_result);

		if ($calc_found_rows)
		{
			$count = $this->_fetch_assoc($this->query('SELECT FOUND_ROWS() AS count'))['count'];
			$this->_found_rows = intval($count);
		}

		if (!$this->iterate() && ($this->_p['safety'] >= 2))
			$this->_fatal("select(): query produced no results", $sql);
	}

	$this->_nofetch = !$nofetch;

	return $result;
}


/**
 * Puts next result from previous select() in member variables
 * @return true if another record was fetched, false if no more records
 * @see select()
 */
function iterate()
{
	if (!$this->_nofetch)
	{
		if ($this->_data = $this->_fetch_assoc($this->_result))
		{
			if ($localizedfields = $this->_localizedfields)
				foreach ($localizedfields as $field => $dummy)
					unset($this->$field);

			foreach ((array)$this->_dyndata as $field => $dummy)
				unset($this->$field);

			foreach (($this->_dyndata = ($t = $this->_data['dyncols']) ? (array)json_decode($t, true) : []) as $field => $value)
				$this->_data[$field] = $value;
			unset($this->_data['dyncols']);

			foreach (static::_read_postprocess($this->_data) as $field => $value)
			{
				$this->$field = (isset($value) && $this->_convertfunc[$field]) ? ($this->_data[$field] = $this->_convertfunc[$field]($value)) : $value;

				if (!array_key_exists($field, $this->_data))	# Register fields added by _read_postprocess() so they get clear()ed
					$this->_data[$field] = $value;
			}

			if ($localizedfields)
			{
				$lang = T_lang();
				foreach ($localizedfields as $field => $dummy)
				{
					$value = $this->{$field . "_" . $lang};

					if (!isset($value))
						$value = $this->{$field . "_" . $this->_p['localized_defaultlanguage']};

					if (isset($value))
					{
						if (isset($this->$field))
							it::fatal("Field name clash: Overwriting {$this->_p['table']}.$field with {$field}_{$lang}, only use one of those fields");
						else
							$this->$field = $value;
					}
				}
			}

			if (!empty($this->_p['keyfield']))
				$this->_key = $this->_data[$this->_p['keyfield']];
		}
		else
		{
			$this->clear();
		}
	}
	else
		$this->_nofetch = false;

	return (bool)$this->_data;
}


/**
 * Insert a record into table. Values are taken assoc array $tags. Keys in $tags
 * should contain row names unless "dyncols" exists in schema (they are stored as
 * dynamic columns then). Keys with - prefix suppress quoting of values.
 * After inserting, all values are valid (record is read back).
 * Does not destroy internal state of last select() call
 * @param $tags key => value pairs to set
 * @return true for success, false for failure (e.g. duplicate entry for key)
 */
function insert($tags = array(), $command = "INSERT")
{
	$this->_connect();

	/* Pre-processing, $tags is passed by reference and may be modified here */
	$tags = static::_write_preprocess($tags);

	if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']]))
		$tags[$this->_p['keyfield']] = bin2hex(random_bytes(16));

	$values = $this->_values($tags, "insert");

	if ($result = $this->query($query = "$command INTO {$this->_p['table']} " . $values))
	{
		$id = ($this->_p['autoincrement'] && !isset($tags[$this->_p['keyfield']])) ? $this->_insertid : $tags[$this->_p['keyfield']];
		if ($this->_p['keyfield'] && !$this->read($id) && $this->_p['safety'])
			$this->_fatal("insert(): can't read record back (key truncated?), id=\"$id\"", $query);

		$this->_touchedids[$this->_key] = true;
	}
	else
		$this->clear();

	return $result;
}


/**
 * Replace a record in a table
 * @param $tags Additional key => value pairs
 * Does not destroy internal state of last select() call
 * @see insert()
 */
function replace($tags = array())
{
	return $this->insert($tags, "REPLACE");
}


/**
 * Create a record like replace(), but optimize if similar or identical record already exists
 * MUST GIVE ALL FIELDS INCLUDING ID
 * @param $tags key => value pairs to set
 */
function store($tags = array())
{
	return $tags[$this->_p['keyfield']] && $this->read($tags[$this->_p['keyfield']]) ? $this->update($tags) : $this->replace($tags);
}


/**
 * Update current record or a number of records given by where condition
 * @param $tags key => value pairs (these have priority over changes in member vars)
 * @param $where condition to select records to be modified (if not current record)
 * @return number of modified records (or false on error). WARNING: read LIMIT docs before using it
 * Does not destroy internal state of last select() call
 */
function update($tags = array(), $where = null)
{
	$this->_connect();
	$result = 0; # in case we optimize away the query

	/* Pre-processing, $tags is passed by reference and may be modified here */
	$tags = static::_write_preprocess($tags);

	if ($set = $this->_set($tags, isset($where)))
	{
		if (!isset($where))
			$where = array($this->_p['keyfield'] => $this->_data[$this->_p['keyfield']]);

		if ($result = $this->query("UPDATE " . $this->_from($where, true) . " $set " . $this->_where($where)))
		{
			$result = $this->_affectedrows;

			if (array_key_exists($this->_p['keyfield'], $tags))	# Did we just update the key?
				$this->_key = $tags[$this->_p['keyfield']];

			if (isset($this->_key) && $this->read($this->_key))
				$this->_nofetch = false; # So we can do while(iterate()) update();
		}
	}

	$this->_touchedids[$this->_key] = true;

	return $result;
}


/**
 * Delete a record
 * Does not destroy query result $this->_result
 * @param $query optional query for WHERE, default: delete currently loaded record
 * @return number of deleted records
 */
function delete($query = null)
{
	$this->_connect();
	$result = 0;

	if (!$query && $this->_p['keyfield'])
	{
		$query = array($this->_p['keyfield'] => $this->_key);
		$this->clear();
	}

	if ($query && $this->query("DELETE " . $this->_from($query) . " " . $this->_where($query)))
		$result = $this->_affectedrows;

	return $result;
}


/**
 * Delete records matching query which were not touched using this object (since last call of this method)
 * @param $query            Query for entries to be checked
 * @param $p['mintouched']  Minimum records that have to have been touched, error otherwise
 * @param $p['it_error']    Parameters for it::error call when too few records touched
 * @return array with deleted IDs
 */
function delete_untouched($query = null, $p = [])
{
	if (count((array)$this->_touchedids) < $p['mintouched'])
		it::error((array)$p['it_error'] + ['title' => "skipping delete, only " . count((array)$this->_touchedids) . " records were touched"]);
	else if ($this->select(['SELECT' => $this->escape_name($this->_p['keyfield'])] + (array)$query))
		while ($this->iterate())
			if (($id = $this->_key) && !$this->_touchedids[$id] && $this->delete())
				$result[] = $id;

	unset($this->_touchedids);
	return (array)$result;
}

/**
 * Escapes a string for use in a DB query
 * @param The string to be quoted
 * @return The quoted value
 */
function escape_string($str)
{
	$this->_connect();
	return $this->_escape_string($str);
}

/**
 * Escapes an int for use in a DB query
 * @param The int to be quoted
 * @return The quoted value
 */
static function escape_int($val)
{
	return "'" . intval($val) . "'";
}

/**
 * Escapes a float for use in a DB query
 * @param The float to be quoted
 * @return The quoted value
 */
static function escape_float($val)
{
	return "'" .  floatval($val) . "'";
}

/**
 * Escapes a name/identifier for use in a DB query
 * @param The identifier to be quoted
 * @return The quoted value
 */
function escape_name($str)
{
	$this->_connect();
	return $this->_escape_name($str);
}

/**
 * Escapes a bool value for use in a DB query
 * @param The bool to be quoted
 * @return The quoted value
 */
static function escape_bool($bool)
{
	return $bool ? 'TRUE' : 'FALSE';
}

/**
 * INTERNAL: Store information about a table's fields in $this->_fields, possibly from cache.
 * @return array(keyfield, autoincrement, randomid)
 */
function _get_field_info()
{
	$dbid = "{$this->_p['user']}@{$this->_p['server']}:{$this->_p['db']}";
	$state = static::_state_get($dbid);

	if (!($this->_fields = $state['fields'][$this->_p['table']]))
	{
		debug("it_dbi(): no fields for {$dbid}.{$this->_p['table']}, calculating.", 5);
		foreach ($this->_get_field_defs() as $name => $field)
		{
			$this->_fields[$name] = $field + array('Length' => preg_match('/date|time/', $field['Type']) ? 20 : intval(it::match('\d+', $field['Type'])));
			if ($field['_convertfunc'] || $field['_escapefunc'])
			{
				$this->_convertfunc[$name] = $field['_convertfunc'];
				$this->_escapefunc[$name] = $field['_escapefunc'];
			}
			else if (preg_match('/^(tiny|small|medium|)int/', $field['Type']))
			{
				$this->_convertfunc[$name] = "intval";
				$this->_escapefunc[$name] = static::class . "::escape_int";
			}
			else if (preg_match('/^float|^double$/', $field['Type']))
			{
				$this->_convertfunc[$name] = "floatval";
				$this->_escapefunc[$name] = static::class . "::escape_float";
			}
		}
		$this->_fieldnames = "," . implode(",", array_keys((array)$this->_fields)) . ",";

		# Consider all fields which have _{localized_defaultlanguage} suffix as localized
		foreach (preg_grep('/_' . $this->_p['localized_defaultlanguage'] . '$/', array_keys((array)$this->_fields)) as $field)
			$this->_localizedfields[substr($field, 0, -1 - strlen($this->_p['localized_defaultlanguage']))] = true;

		$state = static::_state_get($dbid);	# State could have been modified by query above
		$state['fields'][$this->_p['table']] = $this->_fields;
		$state['convertfunc'][$this->_p['table']] = $this->_convertfunc;
		$state['escapefunc'][$this->_p['table']] = $this->_escapefunc;
		$state['localizedfields'][$this->_p['table']] = $this->_localizedfields;
		static::_state_put($dbid, $state);
	}
	else	# Existing _fields, copy other info too
	{
		$this->_convertfunc = $state['convertfunc'][$this->_p['table']];
		$this->_escapefunc = $state['escapefunc'][$this->_p['table']];
		$this->_localizedfields = $state['localizedfields'][$this->_p['table']];
	}

	$result = array();
	foreach((array)$this->_fields as $field)
	{
		if ($field['Key'] == 'PRI')
		{
			$result = array('keyfield' => $field['Field'], 'autoincrement' => (strpos($field['Extra'], "auto_increment") !== false),
					'randomid' => (strpos($field['Type'], "char") !== false));
			break;
		}
	}

	unset($GLOBALS[static::$_global_key]->_p['table'], $GLOBALS[static::$_global_key]->_p['keyfield']);	# Remove cruft
	return $result;
}

static function _state_get($dbid)
{
	if (!($result = $GLOBALS[static::$_global_key]->_state[$dbid]))
		$result = $GLOBALS[static::$_global_key]->_state[$dbid] = (array)it_cache::get(static::$_global_key . ":$dbid");

	#var_dump("get", $dbid, $result);
	return $result;
}

static function _state_put($dbid, $state, $shared = true)
{
	#var_dump("put", $dbid, $state);
	$GLOBALS[static::$_global_key]->_state[$dbid] = $state;
	if ($shared)
		it_cache::put(static::$_global_key .  ":$dbid", array('link' => null) + (array)$state);	# link is not transferable
}

static function _state_purgeshared($dbid = null)
{
	#var_dump("purgeshared", $dbid);
	it_cache::put(static::$_global_key. ":" . ($dbid ? $dbid : $GLOBALS[static::$_global_key]->_dbid), array());	# Nuke shared cache
}

#
# Implement PHP 5 Iterator interface to make foreach work
# Example: foreach (new T_User('firstname' => "foo") as $foouser) { ... }
#
#[ReturnTypeWillChange]
function current()
{
	return clone $this;
}

#[ReturnTypeWillChange]
function key()
{
	return isset($this->_key) ? $this->_key : $this->_iteratorkey++;
}

#[ReturnTypeWillChange]
function next()
{
	$this->iterate();
}

#[ReturnTypeWillChange]
function rewind()
{
	if (!$this->_result)	# Object without query used in foreach
		$this->select();

	# Only rewind if not already at start and results present
	if (!$this->_nofetch && $this->_num_rows($this->_result))
		$this->_seek($this->_result, 0);

	$this->_iteratorkey = 0;
	$this->iterate();
}

#[ReturnTypeWillChange]
function valid()
{
	return (bool)$this->_data;
}


/**
 * Updates or inserts record
 * @return false for error, true for insert, number of changed records (can be 0) for update
 */
function upsert($tags)
{
	return $this->read($tags[$this->_p['keyfield']]) ? $this->update($tags) : $this->replace($tags);
}

/**
 * STATIC: create an object and read its record from DB.
 * @param $id ID of record to read
 * @return initialized object or null if ID not found
 */
static function get($id)
{
	return isset($id) && ($rec = new static) && $rec->read($id) ? $rec : null;
}


/**
 * Start of mysqli specific default implmementation
 */

function _escape_string($str)
{
	return "'" . mysqli_real_escape_string($this->_link, $str) . "'";
}

function _escape_name($str)
{
	return "`" . $str . "`";
}

function _connect_db($p) {
	mysqli_report(MYSQLI_REPORT_OFF);	# it_dbi does not want exceptions for syntax errors
	$result = @mysqli_connect($p['server'], $p['user'], $p['pw']);

	if ($result)
	{
		if (!(@mysqli_select_db($result, $p['db'])))
			$this->_fatal("_connect(): can't select database \"{$p['db']}\"");

		# set charset used for this connection
		if ($p['charset'])
			if (!mysqli_set_charset($result, $p['charset']))
				$this->_fatal("_connect(): can't set charset \"{$p['charset']}\"");
	}

	return [$result, mysqli_connect_error()];
}

function _get_field_defs()
{
	for ($res = $this->query('SHOW COLUMNS FROM ' . $this->_p['table']); $res && ($field = $this->_fetch_assoc($res)); )
		$result[$field['Field']] = it::filter_keys($field, ['Field', 'Type', 'Key', 'Extra']);
	return (array)$result;
}

function _tables($p) {
	for ($qr = $this->query('SHOW TABLES', $p); $row = mysqli_fetch_row($qr);)
		$result[] = $row[0];

	return (array)$result;
}

function __query($query, $p)
{
	if ($p['timeout'] || $this->_p['interruptible_queries'])
	{
		$starttime = microtime(true);
		mysqli_query($this->_link, $query, MYSQLI_STORE_RESULT | MYSQLI_ASYNC);
		do {
			$read = $error = $reject = [$this->_link];
			if ($p['timeout'] && (microtime(true) - $starttime) > $p['timeout'])
				return false;
		} while (!mysqli_poll($read, $error, $reject, 1, 0));
		return mysqli_reap_async_query($this->_link);
	}
	else
	{
		return mysqli_query($this->_link, $query, MYSQLI_STORE_RESULT);
	}
}

function _query($query, $p)
{
	if (!($result = $this->__query($query, $p)) && $p['safety'])
	{
		$errno = mysqli_errno($this->_link);
		if (($p['safety'] < 2) && ($errno == 1062)) # Duplicate entry
			return null;

		if ($errno == 2006) # mysql server has gone away: retry
		{
			it::log('sqllog', "it_dbi(): reconnecting mysqli_connect {$p['server']}, {$p['db']}");
			$this->_connect(array('reconnect' => true));
			$result = $this->__query($query, $p);
		}
	}

	$this->_affectedrows = $this->_link->affected_rows; # get_warnings() clobbers this
	$this->_insertid = mysqli_insert_id($this->_link); # get_warnings() clobbers this
	if (($warning = $this->_link->get_warnings()))
	{
		do {
			if (!it::match(trim($this->_p['ignored_warnings'] . "|1364|1261|1051|1062", "|"), $warning->errno))
				$messages[] = $warning->message . " [error $warning->errno]";
		} while ($warning->next() && ++$checked < 20);

		if ($messages)
			it::error(['title' => "Mysql warning: " . $messages[0], 'body' => "$query\n\n" . implode("\n", $messages) . "\n"]);
	}

	return $result;
}

function _json_extract($col, $field)
{
	return "JSON_EXTRACT($col, " . $this->escape_string('$.' . $field) . ")";
}

function _json_object($tags)
{
	foreach ((array)$tags as $f => $v)
		$strings[] = $this->escape_string($f) . ', ' . $v;
	return "JSON_OBJECT(" . implode(', ', $strings) . ")";
}

function _json_set($source, $tags)
{
	foreach ((array)$tags as $f => $v)
		$strings[] = $this->escape_string('$.' . $f) . ', ' . $v;
	return "JSON_SET($source, " . implode(", ", $strings) . ')';
}

function _json_remove($source, $fields)
{
	foreach ((array)$fields as $f)
		$strings[] = $this->escape_string('$.' . $f);
	return "JSON_REMOVE($source, " . implode(", ", $strings) . ')';
}

function _fetch_assoc($res)
{
	return mysqli_fetch_assoc($res);
}

function _num_rows($res)
{
	return mysqli_num_rows($res);
}


function _seek($res, $offset)
{
	return mysqli_data_seek($res, $offset);
}

function _error($text)
{
	$text = get_class($this).'::'.$text;

	if ($this->_link && ($errstr = mysqli_error($this->_link)))
		$text = "\"$errstr\" in $text [errno " . mysqli_errno($this->_link) . "]";

	return $text;
}

} /* End class it_dbi */