<?php

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

class Sqlite
{
	const label = 'SQLite3';
	const connection = 'sqlite:{{ROOT}}db/{{BASE_NAME}}.db';
	const description = 'Base légere monofichier sans authentification, simple d\'utilisation/installation mais limitée en performances';

	public static function fields(){
		return array(
			array('id'=>'name','label'=>'Nom de la base','default'=>'.database','comment'=>'')
		);
	}
	
	public static function types(){
		$types = array();
        $types['string'] = $types['timestamp'] = $types['datetime'] = $types['date'] = 'VARCHAR(255)';
        $types['longstring'] = 'TEXT';
        $types['key'] = 'INTEGER NOT NULL PRIMARY KEY';
        $types['object'] = $types['integer'] = 'bigint(20)';
    	$types['int'] = 'INTEGER';
        $types['boolean'] = 'INTEGER(1)';
        $types['blob'] = ' BLOB';
        $types['float'] = 'REAL';
        $types['decimal'] = 'DECIMAL(20,2)';
        $types['default'] = 'TEXT';
		return $types;
	}
	
	
	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}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/: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}} {{;}} 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}})';
		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 IF NOT EXISTS `{{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(){
		//On desactive le check sur sqlite, la notion IF NOT EXISTS existant dans ce sgbd
		$sql = "SELECT 0 `exists`";
		return $sql;
	}


}

?>