FieldtypeSelectExtOption.module

<?php namespace ProcessWire;

/**
 * ProcessWire Module Fieldtype Select External Option 
 * extend all Selectfieldtypes
 * provide all Inputfields
 * 
 * pulls options (value/ label pairs) from other datatable (also other fields) 
 * access to selected datatable row via Api
 * 
 * 
 * made by kixe (Christoph Thelen) 2015-03-09
 * Licensed under GNU/GPL v3
 * @version 2.1.3 2024-02-12
 * 
 * ProcessWire 2.x, 3.x
 * Copyright (C) 2013 by Ryan Cramer 
 * Licensed under GNU/GPL v2, see LICENSE.TXT
 * 
 * http://processwire.com
 *
 * @since 1.2.3 - added config field for initial selection (default value) - 2017-02-12
 * @since 1.2.4 - added config field for other language labels - 2017-02-20
 * @since 1.2.5 - fixed bug added filter to function getTableRow(), allow 0 as value (data) - 2017-03-01
 * @since 1.2.6 - allow no pre-selection if value 0 is selectable - 2017-03-02
 * @since 1.2.7 - modified field render and added error message in case of an empty options array - 2017-03-03
 * @since 1.2.8 - fixed multilanguage bug, fixed issues related to allowance of 0 values as selectable option - 2017-03-03
 * @since 1.2.9 - added hookable function label() - 2017-03-03
 * @since 1.3.0 - modified arguments of function label() - 2017-03-06
 * @since 1.3.1 - added hook to remove InputfieldSelect::defaultValue doubled by init_value - 2017-04-19
 * @since 1.3.2 - fixed issue: quick exit of getTableRow() if external database connection fails - 2017-07-25
 * @since 1.3.3 - fixed issues: setting default port to external db, return value of of getDatabaseColumns - 2017-07-25
 * @since 1.3.4 - better error handling - external databases - 2017-07-25
 * @since 1.3.5 - modified arguments of function label() - 2017-08-28
 * @since 1.3.6 - fixed bug: in function options() - 2017-10-15
 * @since 1.3.7 - fixed bug: attribute value not provided if page instance of NullPage - 2018-03-27
 * @since 1.3.8 - fixed bug: return language sensitive label getSingleValue() - 2018-03-30
 * @since 1.3.9 - switched return of SelectExtOption::__toString() from value (int) to label - 2018-05-31
 * @since 1.4.0 - fixed bug: render select, apply selected attribute - removed changes from version 1.3.9 - 2018-06-13
 * @since 1.4.1 - added config field to define return of SelectExtOption::__toString() - 2019-06-20
 * @since 1.4.2 - fixed bug: #12 thanks @tiefenb - 2019-10-03
 * @since 1.4.3 - fixed bug: after some changes in InputfieldSelect::renderOptions() since PW 3.0.140 - 2019-12-10
 * @since 1.4.4 - fixed bug: hook of last update affected config inputfields - 2019-12-11
 * @since 1.4.5 - allow InputfieldToggle - 2019-12-21
 * @since 1.4.6 - fixed render and processInput bugs with InputfieldToggle - 2019-12-21
 * @since 1.4.7 - fixed bug: typo in InputfieldSelect::render hook - 2019-12-24
 * @since 1.4.8 - added language argument to function options() and language sensitive output for toString() - 2019-12-25
 * @since 1.4.9 - fixed multi value bug, fixed for single values in 1.4.3 - 2019-12-25
 * @since 2.0.0 - major update, switched to ProcessWire namespace, added functions for better import and export, cleanup - 2019-12-28
 * @since 2.0.1 - extended filter, use pipes to create OR groups - 2020-01-11
 * @since 2.0.2 - added function getDefaultValue() - 2020-04-04
 * @since 2.0.3 - removed hook, updated requirement PW version - 2020-04-04
 * @since 2.0.4 - changed a lot of code for better handling default value and zero vs. null - 2020-04-04
 * @since 2.0.5 - fixed bug #13 - https://github.com/kixe/FieldtypeSelectExtOption/issues/13 - 2020-04-05
 * @since 2.0.6 - fixed bug: filter didn't allow zero as value - 2020-04-09
 * @since 2.0.7 - added constant to limit number of selectable options show error if exceeded - 2020-04-09
 * @since 2.0.8 - added function getSelectorInfo() to distinguish between single and multiple values for selector fields (remove subfield 'count' if single), added function getMatchQuery() to allow searching via label - 2020-12-11
 * @since 2.0.9 - force to sort by final label - 2021-02-13
 * @since 2.1.0 - fixed bug: removed line 841 @see https://processwire.com/talk/topic/9320-fieldtype-select-external-option/?do=findComment&comment=212453 - 2021-02-13
 * @since 2.1.1 - fixed bug: surround custom filter with brackets - 2022-01-27
 * @since 2.1.2 - extended function row() to retrieve any row by int value independend from a page value - 2022-08-30
 * @since 2.1.3 - fixed bug: Option list does not take into account 'Order by' and 'Order direction'. - 2024-02-12
 * @since 2.1.31 - add formatValue method to output option_label value if option_output=value, to prevent unchanged field change on page save (override gh issue #16 in original repo) (bug not fixed; other values remain problematic) (by chimmel, forked)
 */

class FieldtypeSelectExtOption extends FieldtypeMulti {

	public static function getModuleInfo() {
		return array(
			'title' => 'Select External Option',
			'version' => 2131,
			'summary' => __('Fieldtype which generates the options for a Select Inputfield from *any* table in *any* database hosted *anywhere*. Define database, source table, columns (to pull value & label) and the preferred Inputfieldtype in field settings.'),
			'author' => 'kixe',
			'href' => 'http://modules.processwire.com/modules/fieldtype-select-ext-option/',
			'license' => 'GNU-GPLv3',
			'hreflicense' => 'http://www.gnu.org/licenses/gpl-3.0.html',
			'icon' => 'database',
			'requires' => 'ProcessWire>=3.0.148'
		);
	}
	
	protected $database = null;

	/**
	 * Max allowed number of options
	 * 
	 */
	const OPTIONSLIMIT = 500;

	/**
	 * Create a new PDO instance from field settings. set database property
	 * 
	 * 
	 * @param Field $field
	 * @return bool if WireDatabasePDO could be set
	 * 
	 */
	protected function setDatabase(Field $field) {
		if (!$field) $this->database = $this->wire('database');
		else if (!strlen($field->db_user.$field->db_pass.$field->db_name)) {
			$this->database = $this->wire('database');
		}
		else if (strlen($field->db_user) == 0 || strlen($field->db_pass) == 0 || strlen($field->db_name) == 0) {
			$this->database= $this->wire('database');
			if (!count($_POST)) $this->error('Setting an external database failed: Missing value/s');
			return false;
		}
		else {
			try	{
				$host = $field->db_host;
				$username = $field->db_user;
				$password = $field->db_pass;
				$name = $field->db_name;
				$socket = $field->db_socket; 
				$charset = $this->wire('config')->dbCharset; // charset set in config, don't change here!
				$port = $field->db_port; // default port set in config

				if ($socket) {
					// if socket is provided ignore $host and $port and use $socket instead:
					$dsn = "mysql:unix_socket=$socket;dbname=$name;";
				} else {
					$dsn = "mysql:dbname=$name;host=$host";
					if($port) $dsn .= ";port=$port";
				}
				if (!$this->checkAccess($host, $port)) {
					$field->db_host = 'localhost'; // we reset to localhost 
					$field->save('db_host');
					return false;
				}
				$driver_options = array(
					\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES '$charset'",
					\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
					\PDO::ATTR_TIMEOUT => 5
					);
				$database = new WireDatabasePDO($dsn, $username, $password, $driver_options); 
				$database->setDebugMode($this->wire('config')->debug);
				$this->database = $database;			
			} catch (Exception $e) {
				if (!count($_POST)) {
					$this->error('Setting an external database failed: '.$e->getMessage());
					return false;
				}
			}
		}
		return true;
	}

	/**
	 * Check accessibility of external host:port before trying to create a database connection
	 *
	 * @param string $host
	 * @param string/int $port
	 * @return bool
	 *
	 */
	protected function checkAccess($host, $port) {
		if (!function_exists('socket_create')) return true;
		// make all errors, warnings, notices exception
		// set_error_handler(function($errno, $errstr) {throw new Exception($errstr, $errno);});

		if (!filter_var($host, FILTER_VALIDATE_IP)) {
			$_host = gethostbyname($host);	
			if (empty($_POST) && $_host == $host) $this->error("Unable to translate '$host' to valid IP Adress (IPv4)");
			else if (empty($_POST) && !filter_var($_host, FILTER_VALIDATE_IP)) $this->error('Invalid IPv4 syntax for host');
			else $host = $_host;	
		}
		if (!empty($this->errors(array('array')))) return false;

		$socket = socket_create(AF_INET, SOCK_STREAM, 0);
		$message = '';
		try {
			if (socket_connect($socket, $host, $port)) {
				socket_close($socket);
				return true;
			}
		}
		catch (Exception $e) {
			$message = "Error: ".$e->getMessage();

		}
		if (empty($_POST)) $this->error("No response from [$host:$port]. $message");
		restore_error_handler();
		return false;
	}

	/**
	 * prevent database queries before database is set
	 *
	 */
	public function getLoadQueryAutojoin(Field $field, DatabaseQuerySelect $query) {
		if (!$this->database) return null;
		return parent::getLoadQueryAutojoin($field, $query);
	}

	/**
	 * Get an associative array of all values of the row depending to the selected single value.
	 *
	 * @param object $field
	 * @param int $value
	 * @return array / empty if column holding the values is not defined
	 *
	 */
	protected function getTableRow(Field $field, $value) {
		if ($value === null) return array();
		if (!$this->setDatabase($field)) return array();
		$table = $field->option_table;
		if (!$table) return array();
		$columns = $this->getDatabaseColumns($table);
		$valuecolumn = $field->option_value;
		if (!in_array($valuecolumn,$columns)) return array();
		$table = $this->database->escapeTable($table);
		$value = (int)$value;
		$filter = $this->filter($field);
		$filter = ($filter)?" AND $filter":'';
		$sql = "SELECT * FROM `$table` WHERE $valuecolumn = '$value'$filter";
		$query = $this->database->query($sql);
		if (!$query->rowCount()) return array();
		return $query->fetch(\PDO::FETCH_ASSOC); // single return, unique value
	}

	/**
	 * Access to all column values of the selected row/rows.
	 *
	 * @param string fieldname, default: first field in page of type SelectExtOption
	 * @param selector null|string|bool to get a page, default: current page
	 * @param value int|array to get a specific row, selector must be set to false
	 * @return assoc array
	 *
	 */
	public function row($name = null, $selector = null, $value = null) {
		$return = array();
		$n = $name;
		if ($name) $name = ',name='.$name;

		// get a page value
		if ($value === null && $selector !== false) {
			$page = ($selector)? $this->wire('pages')->get($selector) : $this->wire('page');
			if ($page instanceof NullPage) throw new WireException("Page not found. Selector string '$selector' doesn't match.");
			$field = $page->fields->get('type=FieldtypeSelectExtOption'.$name);
			//field does not belong to pages fieldgroup
			if (!$name && !$field) throw new WireException("Page '$page->name' doesn't contain any field of type 'SelectExtOption'!");
			if (!$field) throw new WireException("Field '$n' doesn't belong to page '$page->name'.");
			$name = $field->name;
			$value = $page->$name;
		} else {
			$field = wire('fields')->get($n);
			if (is_numeric($value)) $value = (int) $value;
			else if (!is_array($value)) throw new WireException("3d argument 'value' must be of type numeric (int) or array.");
		}

		if (is_int($value)) {
			$return = $this->getTableRow($field, $value);
		}
		else if (is_array()) {
			foreach ($value as $key => $val) {
				$row = $this->getTableRow($field, $value);
				$return[$key] = $row;
			}
		}
		else if ($value instanceof WireArray) {
			foreach ($value as $key => $val) {
				$row = $this->getTableRow($field, $val->value);
				$return[$key] = $row;
			}
		} else {
			$return = $this->getTableRow($field, $value->value);
		}
		return $return;	
	}

	public function getInputfield(Page $page, Field $field) {
		$this->addHookAfter('InputfieldSelect::getConfigInputfields', function($e) {
			$inputfields = $e->return;
			$e->return = $inputfields->remove('defaultValue');
		});
		
		// get (set default) name of Inputfieldtype
		$class = ($field->input_type)? $field->input_type:'InputfieldSelect';
		// set Inputfield
		$inputfield = $this->modules->get($class);

		$this->addHookBefore("Inputfield::render", function ($e) use ($field, $page) {
			// quick exit
			if ($field->type != $this) return;
			
			// set default value
			$field->defaultValue = $this->getDefaultValue($page, $field);

			// get / set int value or array of int values and set attribute		
			/*
			$values = $e->object->attr('value');
			if (($e->object->intValue === null || $e->object->intValue === '') && !empty($values)) {
				if (is_numeric($values)) {
					$e->object->intValue = (int) $values;
				} else if (is_array($values)) {
					foreach ($values as &$value) {
						$value = (int) $value;
					}
					$e->object->intValue = $values;
				}
			}
			*/
			$e->object->attr('value', $e->object->intValue);
		});

		/**
		 * hook for special case:
		 * - field required
		 * - InputfieldSelect
		 * - value 0 is a selectable option
		 * - 0 is selected
		 * in this case InputfieldSelect::renderOptions() does not make a difference between 0 and null and provides the 'deselect' option tag. We remove it here
		 * 
		 */
		$this->addHookAfter("InputfieldSelect::render", function ($e) use ($field) {
			if ($field->type != $this) return;
			if ($field->required && $e->object->intValue === array(0)) {
				$string = $e->return;
				$e->return = str_replace("<option value=''>&nbsp;</option>", '', $string);
			}
		});

		// get the options array
		$options = $this->options($field);
		// any selectable options provided?
		if (empty($options))  {
			$inputfield = $this->modules->get('InputfieldMarkup');
			$inputfield->textformatters = array('TextformatterMarkdownExtra');
			$inputfield->markupText = "Field: *$field->name* doesn't provide selectable options. Check field settings!";
			return $inputfield;
		}

		// prevent inputfield taking value from toString()
		$v = $this->sleepValue($page, $field, $page->$field);
		$inputfield->set('intValue', $v);

		foreach($options as $optval => &$label) $label = $this->label($label, $optval, $page, $field);
		if (!$field->option_order) {
			if ($field->option_asc) arsort($options, SORT_LOCALE_STRING); // sort descending by final label
			else asort($options, SORT_LOCALE_STRING); // sort ascending by final label
		}
		// else ksort($options); // DO NOT SORT, because already sorted by database query "order by"
		$inputfield->addOptions($options, true);
			
		/**
		 * attributes needed?
		 *
		 foreach ($options as $optval => $label) {
			$inputfield->addOption($optval, $label, array());
		}
		*/

		// InputfieldToggle requires a value, the property 'useDeselect' is not provided here
		// value must be a valid selectable option
		if ($class == 'InputfieldToggle') {
			if ($page->{$field->name} instanceof WireArray && $page->{$field->name}->value === null) {
				reset($options);
				$value = $field->init_value? $field->init_value : key($options);
				$page->setAndSave($field->name, $value);
			}		
		}
		return $inputfield;
	}

	/**
	 * Get an array of all columns in a given table of the database.
	 *
	 * @param string name of the table
	 * @return array
	 *
	 */
	protected function getDatabaseColumns($table) {
		if (!$this->database || !in_array($table, $this->database->getTables())) return array();
		$columns = array();
		$table = $this->database->escapeTable($table);
		$sql = "SHOW COLUMNS FROM $table"; 			
		$query = $this->database->query($sql);
		if (!$query->rowCount()) return array();
		$rows = $query->fetchAll();
		foreach ($rows as $row) $columns[] = $row['Field'];
		return $columns; 	
	}

	/**
	 * Return true if column in a given table of the database is of type (int).
	 *
	 * @param string datatable and column
	 * @return null (if table and/or column doesn't exist)
	 * @return bool (true if int)
	 *
	 */
	protected function isIntColumn($table,$column) {
		$result = array();
		$columns = $this->getDatabaseColumns($table);
		if (!$columns) return null;
		if (!in_array($column,$columns)) return null;
		$table = $this->database->escapeTable($table);
		$column = $this->database->escapeCol($column);
		$sql = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$column'";
		$query = $this->database->query($sql);
		if (!$query->rowCount()) return null;
		foreach ($query->fetchAll() as $type) if(strpos($type[0],'int') !== false) return true;
		return false;
	}

	/**
	 * Return array of all selectable options, key ≈ (int)optionvalue, value ≈ optionlabel
	 *
	 * @param string/int/object $field - Field, field->id, field->name
	 * @param object $language - Language - default: user language
	 * @return array
	 *
	 */
	public function options($field = null, Language $language = null) {
		if (!$field instanceof Field) {
			$selector = '';
			if (is_int($field)) $selector = ",id=$field";
			elseif (is_string($field)) $selector = ",name=$field";
			$_field = $field;
			$field = $this->wire('fields')->get('type=FieldtypeSelectExtOption'.$selector);
			if (!$field) throw new WireException("Field '$_field' doesn't exist or is not of type FieldtypeSelectExtOption.");
		}
		elseif ($field->type != 'FieldtypeSelectExtOption') throw new WireException("Expecting FieldtypeSelectExtOption.");
		$this->setDatabase($field);
		// set language
		$langID = null;
		if (!$language &&
			$this->wire('modules')->isInstalled("LanguageSupport") &&
			!$this->wire('user')->get('language')->isDefault()) {
			$language = $this->wire('user')->get('language');
		}
		if ($language && !empty($field->{"option_label_$language->id"})) $langID = "_$language->id";
		// create options array
		$options = $this->getExtOptions($field->option_table,$field->option_value,$field->{"option_label$langID"},$this->filter($field),$field->option_order,$field->option_asc,$field->option_label);
		return $options;
	}

	/**
	 * Get options array from external field or datatable
	 *
	 * @return bool false if table and/or column doesn't exist
	 * @return null if table doesn't contain any data
	 * @return array(value => label)
	 *
	 */
	protected function getExtOptions($table, $value = false, $label = false, $filter = false, $order = false, $dir = false, $defaultLabel = null) {

		// quick exit
		if (!$this->database) return false;

		// check if table exist
		$table = $this->database->escapeTable($table);
		if (!in_array($table,$this->database->getTables())) return false;

		// get columns array
		$columns = $this->getDatabaseColumns($table);

		// we need minimum one inttype column 
		foreach ($columns as $column) {
			if ($this->isIntColumn($table,$column)) $intcolumn = $column; break;
		}
		if (!isset($intcolumn)) return false;

		// check if column isset and exist, default 1st column
		if ($label === false) $label = $columns[0];
		else if (!in_array($label,$columns)) return false;

		// check if column isset and exist, default 1st column
		if($defaultLabel === null) $defaultLabel = $label;
		else if (!in_array($defaultLabel,$columns)) return false;

		// check if column isset and exist, default 1st column of type int
		if($value === false) $value = $intcolumn;
		else if (!in_array($value,$columns)) return false;

		$options = array();
		// @see getConfigInputfields()
		if ($dir !== 'LIMIT 1') {
			$dir = ((bool)$dir === true)? 'DESC':'ASC';
		}

		$order = ($order)?$order:$label;
		$order = $this->database->escapeCol($order);
		// validate/sanitize filterstring
		$filter = $filter? "WHERE $filter" : '';
		// $statement = "SELECT * FROM `$table` $filter ORDER BY `$order` $dir LIMIT " . self::OPTIONSLIMIT;
		$statement = "SELECT * FROM `$table` $filter ORDER BY `$order` $dir";
		$query = $this->database->prepare($statement);
		$query->execute();
		if (!$query->rowCount()) return null;
		
		if ($query->rowCount() > self::OPTIONSLIMIT) { 
			$this->error($this->className() . sprintf(': Maximum number (%1$d < %2$d) of selectable options exceeded. Use filter function in field settings to limit.', self::OPTIONSLIMIT, $query->rowCount()));
			return false;
		}
		$this->errors('clear all');
		
        while ($row = $query->fetch(\PDO::FETCH_ASSOC)) $options[$row[$value]] = strlen((string) $row[$label])? $row[$label] : $row[$defaultLabel];
        return $options;
	}

	/**
	 * Hookable function called from Inputield provides option to edit option labels
	 *
	 * @param string $label (option label)
	 * @param int $value (option value)
	 * @param object $page
	 * @param object $field
	 * @return string
	 *
	 */
	protected function ___label($label, $value, Page $page, Field $field) {
		return $label;
	}

	/**
	 * Hookable function provides option to filter options array
	 *
	 * @param object $field
	 * @return string SQL command filter part. example: "column = 'value'"
	 *
	 */
	protected function ___filter(Field $field) {
		// all parts set?
		if (!$field->filter_column||!$field->filter_selector||$field->filter_value === null) return false;
		// valid operator?
		$operators = array(' LIKE ',' NOT LIKE ');
		// we use pipe to allow OR
		if (strpos($field->filter_value, '|')) {
			$values = explode('|', $field->filter_value);
			foreach ($values as &$v) {
				$v = $this->database->quote($v);
			}
			unset($v);
		} else $values = array($this->database->quote($field->filter_value));

		if (!$this->database->isOperator($field->filter_selector) && !in_array($field->filter_selector,$operators))  return false;
		// escape column, compose filter string
		$return = '';
		foreach ($values as $value) {
			$return .= '`'.$this->database->escapeCol($field->filter_column).'`'.$field->filter_selector.$value. ' OR ';
		}
		return '(' . rtrim($return, ' OR ') . ')';
	}

	public function getBlankValue(Page $page, Field $field) {
		$return = (!$field->input_type)?new SelectExtOption():new WireArray();
		$return->setTrackChanges(true);
		return $return;
	}

	/**
	 * Return the defaultValue property for this field (if set), or null otherwise.
	 *
	 * @param object $page
	 * @param object $field
	 * @return null|string defaultValue separated by linefeed in multiple
	 * 
	 * @see Fieldtype::getDefaultValue(), Field::getDefaultValue(), InputfieldSelect::checkDefaultValue()
	 *
	 */
	public function getDefaultValue(Page $page, Field $field) {
		if ($field->init_value !== null) {
			return is_array($field->init_value)? implode("\n", $field->init_value) : "$field->init_value";
		}
		return null;
	}

	public function getSingleValue(Field $field, $value) {
		if ($value === null) return new SelectExtOption();
		$new = new SelectExtOption();
		$new->value = (int) $value;	
		$row = $this->getTableRow($field, $new->value);
		if (empty($row)) return new SelectExtOption();
		foreach ($row as $key => $value) {
			// skip reserved words
			if (in_array($key, array('value','label','row','data','options','toString'))) continue;
			$new->set($key, $value);
		}
		if ($field->option_output) $new->toString = $field->option_output;
		$new->row = $row;
		$new->options = $this->options($field);
		$new->label = $new->options[$new->value];
		return $new;
	}

	public function sanitizeValue(Page $page, Field $field, $value) {
		if ($value instanceof SelectExtOption) return $value;
		if ($value instanceof WireArray) return $value;
		if (is_array($value)) {
			$return = new WireArray();
			foreach ($value as $val) $return->add($this->getSingleValue($field,$val));
			return $return;
		}
		if ($value === null || !is_numeric($value)) return $this->getBlankValue($page,$field);
		return $this->getSingleValue($field,$value);
	}

	public function wakeupValue(Page $page, Field $field, $value) {
		if ($value === null || empty($value)) return $this->getBlankValue($page,$field);
		if (!is_array($value)) $value = array($value);
		$module = $this->modules->get($field->input_type);
		if (!$module instanceof InputfieldHasArrayValue) return $this->getSingleValue($field,array_pop($value));
		$return = new WireArray();
		foreach ($value as $val) $return->set($val,$this->getSingleValue($field,$val));
		$return->data('options',$this->options($field));
		$return->resetTrackChanges();
		return $return;
	}

	public function sleepValue(Page $page, Field $field, $value) {
		if ($value instanceof SelectExtOption) return ($value->value !== null)?array($value->value):null;
		$return = array();
		if ($value instanceof WireArray) {
	 		foreach ($value as $object) {
		    	if (!$object instanceof SelectExtOption) throw new WireException("Expecting an instance of SelectExtOption");
		    	if ($object->value === null) continue;
				$return[] = $object->value;
			}
		}
		return $return;
	}

	/**
	 * Given a value, return an portable version of it
	 *
	 * @param Page $page
	 * @param Field $field
	 * @param string|int|float|array|object|null $value
	 * @param array $options Optional settings to shape the exported value, if needed:
	 *  - `human` (boolean): When true, Fieldtype may optionally emphasize human readability over importability 
	 *  - `language` (null|int|object): Language or language id
	 * @return int|string|array
	 *
	 */
	public function ___exportValue(Page $page, Field $field, $value, array $options = array()) {
		$defaultOptions = array(
			'human' => false,
			'language' => null
		);
		$options = array_merge($defaultOptions, $options);

		// default return int (single) or array of int (multiple)
		if (!$options['human'] || $value->toString == 'value') return $this->sleepValue($page, $field, $value);

		// get language
		$language = null;
		if ($this->wire('modules')->isInstalled("LanguageSupport") && $options['language']) {
			if (!$options['language'] instanceof Language) $language = $this->wire('languages')->get($options['language']);
			else $language = $options['language'];
		}

		// return label or array of labels (language sensitive)
		if ($language->id && $value->toString == 'label') {
			if ($value instanceof SelectExtOption) return $this->options($field, $language)[$value->value];
			else {
				$return = [];
				foreach ($value as $v) $return[] = $this->options($field, $language)[$v->value];
				return $return;
			}
		}

		// return human readable string or array of strings
		if ($value instanceof SelectExtOption) return "$value";
		else {
			$return = [];
			foreach ($value as $v) $return[] = "$v";
			return $return;
		}
	}

	/**
	 * get a saveable value (array of selectable option values [int]), by searching for matches of $search in label columns or any column
	 * $page->set('myfield', $return) or $page->setAndSave('myfield', $return)
	 *
	 * @param Field $field
	 * @param string|int|float|array $search
	 * @param int $full 
	 *  - 0: searching in label columns only (multilanguage if set)
	 *  - 1: full search in all columns
	 * @return array
	 *
	 */
	public function getSaveableValue(Field $field, $search, $full = 0) {

		$this->setDatabase($field);

		// search for labels
		if (!$full) {
			$columns = [$field->option_label];
			$otherLanguagePageIDs = $this->modules->isInstalled("LanguageSupport")? $this->modules->get("LanguageSupport")->otherLanguagePageIDs: null;
			if (!empty($otherLanguagePageIDs)) {
				foreach ($otherLanguagePageIDs as $lid) {
					if (empty($field->{"option_label_$lid"})) continue;
					$columns[] = $field->{"option_label_$lid"};
				}
			}
		}

		// full search
		else $columns = $this->getDatabaseColumns($field->option_table);

		// compose statement
		$columnsString = '`' . implode('`,`', $columns) . '`';
		$filter = $this->filter($field);
		$filter = ($filter)? "WHERE $filter AND ":'WHERE ';
		if (is_array($search)) $search = implode("' IN ($columnsString) OR '" , $search);
		$filter .= "'$search' IN ($columnsString)";
		$statement = "SELECT * FROM `$field->option_table` $filter";

		// execute statement
		$query = $this->database->prepare($statement);
		$query->execute();
		if (!$query->rowCount()) return null;

		$result = [];
        while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
        	$result[] = (int) $row[$field->option_value];
        }
        if (empty($result)) return null;
        return $result;
	}
		
	/**
	 * Format the given value for output and return a string of the formatted value
	 * 
	 * (Addresses kixe/FieldtypeSelectExtOption gh issue #16 by override)
	 *
	 * #pw-group-formatting
	 * 
	 * @param Page $page Page that the value lives on
	 * @param Field $field Field that represents the value
	 * @param string|int|object $value The value to format
	 * @return mixed
	 *
	 */
	public function ___formatValue(Page $page, Field $field, $value) {
		if ($field->option_output == $field->option_value) return $value->{$field->option_value};
		if ($field->option_output == 'value') return $value->{$field->option_label};
		// other options result in field change false alarm on page save?
	}
	
	/**
	 * Get the database query that matches a Fieldtype table’s data
	 * translate label to related value (always integer)
	 *
	 */
	public function getMatchQuery($query, $table, $subfield, $operator, $value) {
		if (!is_numeric($value)) $value = $this->getSaveableValue($query->field, $value, 0);
		if (empty($value)) $value = '';
		return parent::getMatchQuery($query, $table, $subfield, $operator, $value);
	}

	/**
	 * Return array with information about what properties and operators can be used with this field
	 * corresponding to the selected Inputfieldtype (single, multiple)
	 *
	 */
	public function ___getSelectorInfo(Field $field, array $data = array()) {
		$class = $field->input_type;
		$module = $this->modules->get($class);
		if($module instanceof InputfieldHasArrayValue) return parent::___getSelectorInfo($field, $data); 
		else return Fieldtype::___getSelectorInfo($field, $data);
	}

	public function ___getConfigInputfields(Field $field) {

		$inputfields = parent::___getConfigInputfields($field);
		$this->setDatabase($field);

		// usage
		$markup = file_exists(dirname(__FILE__) . '/README.md')?file_get_contents(dirname(__FILE__) . '/README.md'):null;
		if ($markup) {
			$f = $this->modules->get("InputfieldMarkup");
			$f->label = $this->_('Usage');
			// call textformatter before wrapping
			$this->modules->get('TextformatterMarkdownExtra')->format($markup); 
			$f->markupText = "<div style=\"padding:0 16px; border-left: 16px solid #29c4bc;\"><strong>README.md</strong><hr/>$markup</div>";
			$f->collapsed = Inputfield::collapsedYes;
			$inputfields->append($f);
		}

		// choose inputfieldtype
		$f = $this->modules->get('InputfieldSelect');
		$f->label = $this->_('Inputfieldtype');
		$f->attr('name', 'input_type');
		$f->attr('value', $field->input_type);
		$f->required = true;
		$f->description = $this->_('Select the type of Inputfield.');
		$f->notes = '* ' . $this->_('Types indicated with an asterisk are for multiple selection. Maybe 3rd party Inputfields will not work as expected. Please test carefully.');
		$f->addOption('InputfieldSelect', 'Select');
		foreach($this->modules->get("InputfieldPage")->inputfieldClasses as $class) {
			$module = $this->modules->get($class);
			if (!$module instanceof InputfieldSelect && !$module instanceof InputfieldToggle) continue;
			$label = str_replace("Inputfield", '', $class);
			if ($module instanceof InputfieldHasArrayValue) $label .= "*";
			$f->addOption($class, $label);
		}
		$inputfields->append($f);

		// external Database
		$fieldset = $this->modules->get("InputfieldFieldset");
		$fieldset->label = $this->_('External MySQL Database/ Host');
		$fieldset->description = $this->_("Optionally specify an external MySQL 5.x database. If the database is not accessible for any reason default database will be selected.");
		$fieldset->notes = $this->_('Incorrect entries in the field \'DB Host\' produce unsightly error messages! Be aware of this.');
		$fieldset->collapsed = strlen($field->db_name.$field->db_user.$field->db_pass)? Inputfield::collapsedNo : Inputfield::collapsedYes;
		$inputfields->add($fieldset);

		// DB Name
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("DB Name");
		$f->attr('name', 'db_name');
		$f->attr('value', $field->db_name);
		$f->columnWidth = 20;
		$fieldset->append($f);

		// DB User
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("DB User");
		$f->attr('name', 'db_user');
		$f->attr('value', $field->db_user);
		$f->columnWidth = 20;
		$fieldset->append($f);

		// DB Pass
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("DB Pass");
		$f->attr('name', 'db_pass');
		$f->attr('value', $field->db_pass);
		$f->columnWidth = 20;
		$fieldset->append($f);

		// DB Host
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("DB Host");
		$f->attr('name', 'db_host');
		$host = (isset($field->db_host))?$field->db_host:'localhost';
		$f->attr('value', $host);
		$f->columnWidth = 20;
		$fieldset->append($f);

		/*
		// DB Socket
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("Socket");
		$f->attr('name', 'db_socket');
		$f->attr('value', $field->db_socket);
		$f->columnWidth = 16.666;
		$fieldset->append($f);
		*/

		// DB Port
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("DB Port");
		$f->attr('name', 'db_port');
		$field->db_port? $f->attr('value', $field->db_port) : $f->attr('value', $this->wire('config')->dbPort);
		$f->columnWidth = 20;
		$fieldset->append($f);

		// create options
		$fieldset = $this->modules->get("InputfieldFieldset");
		$fieldset->label = $this->_('Create options from any database table');
		$fieldset->notes = $this->_("Save after selecting 'Source Table' to populate the appropriate select for 'Option Label' and 'Option Value'. Make a selection and save again.");
		$inputfields->add($fieldset);

		// source table
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Source Table");
		$f->attr('name', 'option_table');
		$f->required = true;
		$f->attr('value', $field->option_table);
		if (!$field->option_table) $f->addOption(null, 'no table selected!',array('selected'=>'selected'));
		// we use $dir for limit to check if one single row exists
		if ($this->getExtOptions($field->option_table,false,false,false,false,'LIMIT 1') === null && !count($_POST)) $f->error('Table doesn\'t contain any data!');
		if ($this->database) foreach ($this->database->getTables() as $table) $f->addOption($table, $table);
		$f->description = $this->_("Choose a table in your database.");
		$f->columnWidth = 33;
		$fieldset->append($f);

		$columns = $this->getDatabaseColumns($field->option_table);
		// option value column
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Option Value");
		$f->attr('name', 'option_value');
		$f->attr('value', $field->option_value);
		$f->description = $this->_("Choose an integer type column.");
		if ($columns) foreach ($columns as $value) {
			if ($this->isIntColumn($field->option_table,$value)) $f->addOption($value, $value);
		}
		if ($this->database && $field->option_table && !count($f->getOptions()) && !count($_POST)) $f->error("Only tables with columns of type 'integer' allowed!");
		if (!$field->option_value) $f->addOption(null, 'no column selected!',array('selected'=>'selected'));		
		$f->columnWidth = 34; 
		$fieldset->append($f);

		// multilanguage environment
		$otherLanguagePageIDs = $this->modules->isInstalled("LanguageSupport")? $this->modules->get("LanguageSupport")->otherLanguagePageIDs:null;
		// option label column (default language)
		$f = $this->modules->get("InputfieldSelect");
		$appendLabelText = $otherLanguagePageIDs?  " (default language)":'';
		$f->label = $this->_("Option Label$appendLabelText");
		$f->attr('name', 'option_label');
		$f->attr('value', $field->option_label);
		$f->description = $this->_("Choose from all columns.");
		if (!$field->option_label) $f->addOption(null, 'no column selected!',array('selected'=>'selected'));
		if ($columns) foreach ($columns as $label) $f->addOption($label, $label);
		$f->columnWidth = 33; 
		$fieldset->append($f);

		// option label column (other languages)
		if ($otherLanguagePageIDs) {
			$columnWidth = floor(100/ count($otherLanguagePageIDs));

			foreach ($otherLanguagePageIDs as $otherLanguagePageID) {
				$langName = $this->wire('languages')->get($otherLanguagePageID)->name;
				$f = $this->modules->get("InputfieldSelect");
				$f->label = sprintf($this->_("Option Label (%s)"),$langName);
				$f->attr('name', "option_label_$otherLanguagePageID");
				$f->attr('value', $field->{"option_label_$otherLanguagePageID"});
				$f->description = $this->_("Choose from all columns.");
				if (!$field->option_label) $f->addOption(null, 'no column selected!',array('selected'=>'selected'));
				if ($columns) foreach ($columns as $label) $f->addOption($label, $label);
				$f->columnWidth = $columnWidth;
				$f->collapsed = Inputfield::collapsedBlank;
				$fieldset->append($f);
			}
		}

		// option filter
		$fieldset = $this->modules->get("InputfieldFieldset");
		$fieldset->label = $this->_('Filter');
		$fieldset->description = $this->_("Configure to filter the option list. Use pipes to create OR groups.");
		$filter = $this->filter($field);
		$fieldset->notes = $this->_("SELECT * FROM $field->option_table WHERE $filter");
		$fieldset->collapsed = Inputfield::collapsedBlank;
		$fieldset->showIf = "option_value!=''";
		$inputfields->add($fieldset);

		// filter column
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Column");
		$f->attr('name', 'filter_column');
		$f->attr('value', $field->filter_column);
		if ($columns) foreach ($columns as $filtercol) $f->addOption($filtercol, $filtercol);
		$f->columnWidth = 33; 
		$fieldset->append($f);

		// filter selector
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Selector Operator");
		$f->attr('name', 'filter_selector');
		$f->attr('value', $field->filter_selector);
		$selectors = array('=', '<', '>', '>=', '<=', '<>', '!=', ' LIKE ',' NOT LIKE ');
		foreach ($selectors as $selector) $f->addOption($selector, $selector);
		$f->columnWidth = 34; 
		$fieldset->append($f);

		// filter value
		$f = $this->modules->get("InputfieldText");
		$f->label = $this->_("Value");
		$f->attr('name', 'filter_value');
		$f->attr('value', $field->filter_value);
		$f->columnWidth = 33; 
		$fieldset->append($f);

		// option orderby
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Order by");
		$f->description = $this->_("Default: Order by label.");
		$f->notes = $this->_("Hook in function `___label(\$label, \$value, \$page, \$field)` to modify the label for your needs.");
		$f->attr('name', 'option_order');
		$f->attr('value', $field->option_order);
		if ($columns) foreach ($columns as $ordercol) {
			if ($ordercol == $field->option_label) continue;
			$f->addOption($ordercol, $ordercol);
		}
		$f->collapsed = Inputfield::collapsedBlank;
		$f->showIf = "option_value!=''";
		// $f->columnWidth = 50;
		$inputfields->append($f);

		// option order asc/ desc
		$f = $this->modules->get("InputfieldRadios");
		$f->label = $this->_("Order Direction");
		$f->attr('name', 'option_asc');
		($field->option_asc)?$f->attr('value', $field->option_asc):$f->attr('value', 0);
		$f->addOption(0,$this->_("Ascending"));
		$f->addOption(1,$this->_("Descending"));
		$f->collapsed = $field->option_asc? Inputfield::collapsedBlank : Inputfield::collapsedYes;
		$f->showIf = "option_value!=''";
		// $f->columnWidth = 50;
		$inputfields->append($f);

		// option output
		$f = $this->modules->get("InputfieldSelect");
		$f->label = $this->_("Output");
		$f->attr('name', 'option_output');
		$f->attr('value', $field->option_output? $field->option_output : 'value');
		$f->description = $this->_("Select a property for direct output `__toString()`.");
		$labelLabel = $otherLanguagePageIDs? $this->_('label (language sensitive)') : 'label';
		$f->addOption($this->_('previously set'), array('value' => 'value', 'label' => $labelLabel));
		// disallow reserved properties
		if ($columns) $columns = array_diff($columns, array('value','label','row','data','options','toString'));
		$f->addOption($this->_('table columns'), array_combine($columns, $columns));
		$f->collapsed = Inputfield::collapsedYes;
		$f->showIf = "option_value!=''";
		$f->required = true;
		$inputfields->append($f);

		// initial selection field preview
		$options = $this->options($field);
		if (!empty($options) && $field->input_type && $f = $this->wire('modules')->get($field->input_type)) {
			$f->optionColumns = $field->optionColumns? $field->optionColumns:null;
			$f->attr('name', 'init_value'); 
			$f->label = $this->_('What options do you want pre-selected? (if any)');

			$f->collapsed = $field->option_value && $field->option_label? Inputfield::collapsedBlank : Inputfield::collapsedYes;
			$f->description = sprintf($this->_('This field also serves as a preview of your selected input type (%s) and options.'), $field->input_type); 
			// allow no pre-selection if value 0 is selectable
			if (array_key_exists(0, $options) && $f instanceof InputfieldSelectMultiple === false) {
				$label = $field->input_type == 'InputfieldRadios'? '*no pre-selection*':'&nbsp;';
				$f->addOption('',$label);
			}
			foreach ($options as $value => $label) {
				$f->addOption($value, $label); 
			}
			$f->attr('value', $field->init_value); 
			if (!$field->required) {
				$f->notes = $this->_('Please note: your selections here do not become active unless a value is *always* required for this field. See the "required" option on the Input tab of your field settings.');
			} else {
				$f->notes = $this->_('This feature is active since a value is always required.'); 
			}
			$f->showIf = "option_value!='',option_label!=''";
			$inputfields->add($f); 
		}
		// no selectable options provided
		else if ($field->option_table && $field->option_lable && $field->option_value && !count($_POST) && empty($options))  {
			$this->error("No selectable options provided. Check your 'Source' and 'Filter' settings.");
		}
		return $inputfields;
	}
}

/**
* Helper WireData Class to hold a SelectExtOption object
*
*/
class SelectExtOption extends WireData {

	/**
	 * Array where get/set properties are stored
	 *
	 */
	protected $data = array();

	public function __construct($value = null) {
		$this->set('value', null);
		$this->set('label', null);
		$this->set('toString', 'value');
		$this->set('row', array());
		$this->set('options', array());
	}

	public function set($key, $value) {
		if (in_array($key,array('value','label','row','data','options'))) {
			if ($key == 'data') throw new WireException("'data' property is reserved by WireData class. (array where get/set properties are stored)");
			// validation of label ? 
			if ($key == 'value' && !is_null($value) && !is_int($value)) throw new WireException("SelectExtOption object only accepts integer (int) as value property");
			if ($key == ('row'|'options') && !is_array($value)) throw new WireException("SelectExtOption object only accepts arrays as '$key' property");
		}
		return parent::set($key, $value);
	}

	public function get($key) {
		return parent::get($key);
	}

	public function __toString() {
		// get label (language support: current user language)
		if ($this->toString == 'label') return $this->options[$this->value];
		return (string) $this->{$this->toString};
    }
}