<?php

/**
 * Define SQL for Mysql database system
 * @author valentin carruesco
 * @category Core
 * @license copyright
 */

class Oracle
{
	const label = 'Oracle';
	const connection = 'odbc:{{name}}';
	const description = 'Base  authentifiée necessitant un serveur Oracle Database XE ou Oracle Database Entreprise, creation de source ODBC à prévoir';
	const table_escape = '"';
	const column_escape = '"';

	public static function pdo_attributes(){
		return array(
			PDO::ATTR_PERSISTENT => true
		);
	}

	public static function fields(){
		return array(
			array('id'=>'name','label'=>'Nom de la source ODBC','default'=>'','comment'=>''),
			array('id'=>'login','label'=>'Identifiant','default'=>'','comment'=>''),
			array('id'=>'password','label'=>'Mot de passe','default'=>'','comment'=>'')
		);
	}

	public static function processField(&$field,&$value,&$values,&$i){
		
		if($field['type'] == 'date'){
			
			$field['operator'].= ' to_date( ';
			$field['postoperator'].= ',\'YYYY-MM-DD\')';
		}
		switch($field['operator']){
			case 'IN':
	            $field['tag'] = array(); 
	            foreach (explode(',',$value) as $v2) {
	                $tag = ':'.$i;
	                $field['tag'][]= $tag;
	                $values[$tag] = $v2;
	                $i++;
	            }
	            $field['tag'] = implode(',',$field['tag']);
	            $field['operator'] = 'IN(';
	            $field['postoperator'] = ')';
	        break;
	        default:
	            $tag = ':'.$i;
	            $field['tag'] = $tag;
	            $values[$tag] = $value;
	            $i++;
        	break;
    	}

	}

	public static function types(){
		$types = array();
		$types['string']  = 'VARCHAR2(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
		$types['longstring'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
		$types['key'] = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
		$types['object'] = $types['timestamp'] = $types['datetime'] = $types['date'] = $types['int'] = 'INT(11)';
		$types['boolean'] = 'tinyint(1) NOT NULL DEFAULT \'0\'';
		$types['blob'] = ' BLOB';
		$types['float'] = 'FLOAT';
		$types['decimal'] = 'DECIMAL(20,2)';
		$types['default'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
		return $types;
	}


	public static function beforeTransaction($pdo){
		$pdo->exec("set names utf8");
	}
	
	public static function select(){
		$sql = 'SELECT {{:selected}}{{value}}{{;}},{{/;}}{{/:selected}} FROM "{{table}}" {{?joins}}{{:joins}}LEFT JOIN "{{jointable2}}" ON "{{jointable1}}".{{field1}}= "{{jointable2}}".{{field2}} {{/:joins}}{{/?joins}} {{?filter}} WHERE {{:filter}} "{{table}}"."{{key}}" {{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?orderby}}ORDER BY {{:orderby}}{{value}}{{;}},{{/;}}{{/:orderby}} {{/?orderby}}{{?limit}}FETCH NEXT {{:limit}}{{value}}{{;}}{{/;}} ROWS ONLY{{/:limit}}{{/?limit}}';
		return $sql;
	}
	public static function delete(){
		$sql = 'DELETE FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?limit}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/:limit}}{{/?limit}}';
		return $sql;
	}
	public static function count(){
		$sql = 'SELECT COUNT({{selected}}) number FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}}{{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
		return $sql;
	}
	public static function update(){
		$sql = 'UPDATE "{{table}}" SET {{?fields}} {{:fields}}"{{key}}"={{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}} {{?filters}}WHERE {{:filters}}"{{key}}"{{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filters}} {{/?filters}}';
		return $sql;
	}
	public static function insert(){
		$sql = 'INSERT INTO  "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{;}} , {{/;}} {{/:fields}} {{/?fields}})VALUES({{?fields}} {{:fields}}{{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}})';
		return $sql;
	}
	public static function create(){
		$sql = 'CREATE TABLE IF NOT EXISTS "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{value}}{{;}} , {{/;}} {{/:fields}} {{/?fields}}) DEFAULT CHARSET=utf8;';
		return $sql;
	}
	public static function drop(){
		$sql = 'DROP TABLE IF EXISTS "{{table}}";';
		return $sql;
	}

	public static function truncate(){
		$sql = 'TRUNCATE TABLE "{{table}}";';
		return $sql;
	}

	public static function create_index(){
		$sql = 'CREATE INDEX "{{index_name}}" ON "{{table}}" ({{column}})';
		return $sql;
	}

	public static function drop_index(){
		$sql = 'DROP INDEX "{{index_name}}" ON "{{table}}"';
		return $sql;
	}

	public static function count_index(){
		$sql = 'SELECT COUNT(1) "exists" FROM INFORMATION_SCHEMA.STATISTICS
		WHERE table_schema=DATABASE() AND table_name=\'{{table}}\' AND index_name=\'{{index_name}}\'';
		return $sql;
	}

	public static function show_tables(){
		$sql = 'SELECT table_name FROM user_tables ORDER BY table_name;';
		return $sql;
	}
	public static function show_columns(){
	  $sql = "SELECT COLUMN_NAME \"column\",DATA_TYPE \"type\" 
		FROM user_tab_cols
		WHERE table_name = '{{table}}' AND USER_GENERATED = 'YES'";
	  return $sql;
	}


}

?>