se/SE/se-lib/ApiDataSourceTodo.php

<?php

class ApiDataSourceTodo {

	private $_dataSourceName;
	private $_sourceConnection;
	private $_tblName;
	private $_primaryKey;
	private $_fields = array();

	public function __construct($dataSourceName) {
		$this->_dataSourceName = $dataSourceName;
	}

	public function getSchema() {
		// TODO: read from schema files xml/ini
		// TODO: build data source by name $this->_dataSourceName (Mysql|SchemaFile)

		if (empty($this->_fields)) {
			$db = $this->getConnection();
			$sql = "show fields from `{$this->_tblName}` ";
			$res = $db->query($sql);
			if (!$res) {
				IF(V::get('DBG','',$_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				IF(V::get('DBG','',$_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">db errors (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($db->get_errors());echo'</pre>';}
				throw new HttpException("Read schema errors", 404);
			}
			while ($h = $db->fetch_row($res)) {
				$fieldName = $h[0];
				$fieldType = $h[1];
				$this->_fields[$fieldName] = array('type'=>$h[1], 'null'=>('YES' == $h[2]), 'default'=>$h[4]);
				if ('PRI' == $h[3]) {
					$this->_primaryKey = $fieldName;
				}
			}
			IF(V::get('DBG','',$_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">_fields (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->_fields);echo'</pre>';}
			if (empty($this->_fields)) {
				throw new HttpException("Fields list empty", 404);
			}
		}
	}

	private function getConnection() {
		$this->_sourceConnection = DB::getDB($this->_dataSourceName);
		if (!$this->_sourceConnection) {
			throw new HttpException("Connect to data source error", 404);
		}
		return $this->_sourceConnection;
	}

	public function getItems($args) {
		IF(V::get('DBG','',$_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">args (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($args);echo'</pre>';}
		$items = array();
		$db = $this->getConnection();
		$fields = $this->getFields();
		$sqlFields = array();
		foreach ($fields as $fieldName => $field) {
			$sqlFields[] = "t.`{$fieldName}`";
		}
		$sqlFields = implode(", ", $sqlFields);
		if (!empty($args['limit']) && is_numeric($args['limit'])) {
			$sqlLimit = "limit {$args['limit']}";
			if (!empty($args['offset']) && is_numeric($args['offset'])) {
				$sqlLimit .= " offset {$args['offset']}";
			}
		}

		$sqlWhere = "1=1";
		$sqlQueryArgs = array();
		foreach ($fields as $fieldName => $field) {
			$queryValue = V::get("f_{$fieldName}", null, $_GET);
			if (!empty($queryValue)) {
				$sqlQueryValue = $db->_($queryValue);
				$sqlQueryArgs[] = "t.`{$fieldName}` like '{$sqlQueryValue}'";
			}
		}
		if (!empty($sqlQueryArgs)) {
			$sqlWhere .= " and " . implode(" and ", $sqlQueryArgs);
		}

		$sql = "select {$sqlFields}
			from `{$this->_tblName}` as t
			where {$sqlWhere}
			{$sqlLimit}
			-- order by
		";
		IF(V::get('DBG','',$_GET)>2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		IF(V::get('DBG','',$_GET)>2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">this->_primaryKey (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->_primaryKey);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if (!$this->_primaryKey) {
				$items[] = $r;
			} else {
				$items[$r->{$this->_primaryKey}] = $r;
			}
		}
		return $items;
	}

	public function getItem($id, $params = []) {
		$items = array();
		$db = $this->getConnection();
		$fields = $this->getFields();
		$sqlFields = array();
		foreach ($fields as $fieldName => $field) {
			$sqlFields[] = "t.`{$fieldName}`";
		}
		$sqlFields = implode(", ", $sqlFields);

		$sqlWhere = "t.`{$this->_primaryKey}`='{$id}'";

		$sql = "select {$sqlFields}
			from `{$this->_tblName}` as t
			where {$sqlWhere}
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$item = $r;
		}
		return $item;
	}

	public function createItem($itemRaw) {
		$item = array();
		$db = $this->getConnection();
		$fields = $this->getFields();
		foreach ($fields as $fieldName => $field) {
			if (array_key_exists($fieldName, $itemRaw)) {
				$item[$fieldName] = $itemRaw[$fieldName];
			}
		}
		if (array_key_exists($this->_primaryKey, $item)) {
			if (!empty($item[$this->_primaryKey])) {
				$item[$this->_primaryKey] = null;
			}
		}
//echo "itemRaw:\n";print_r($itemRaw);
//echo "fields:\n";print_r($fields);
//echo "item:\n";var_dump($item);
//exit;
		foreach ($item as $k => $v) {
			if ($k == $this->_primaryKey) {
				$v = 'NULL';
			} else if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL') {
				$v = 'NULL';
			} else if (substr($v, 0, strlen('GeomFromText')) == 'GeomFromText') {

			} else {
				$v = $db->_($v);
				$v = "'{$v}'";
			}
			$sql_arr ["`{$k}`"] = $v;
		}
		$sql = "insert into `{$this->_tblName}` (".implode(",", array_keys($sql_arr)).") values (".implode(",", array_values($sql_arr))."); ";
//echo "sql:{$sql}\n";
//exit;
		$db->query($sql);
		if ($db->has_errors()) {
			throw new Exception("Database Errors: " . implode("\n", $db->get_errors()));
		}
		return $db->insert_id();
	}

	public function getFields() {
		$this->getSchema();
		return $this->_fields;
	}

	public function setTable($tblName) {
		$this->_tblName = $tblName;
	}

}