true, PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION ); } public static function fields(){ return array( array('id'=>'host','label'=>'Serveur','default'=>'localhost','comment'=>''), array('id'=>'login','label'=>'Identifiant','default'=>'','comment'=>''), array('id'=>'password','label'=>'Mot de passe','default'=>'','comment'=>''), array('id'=>'name','label'=>'Nom de la base','default'=>'','comment'=>'') ); } public static function operators($key=null){ $operators = array( 'in'=> array( 'slug'=> 'in', 'label'=>'Contient une des valeurs', 'sql'=>function($column,$values,&$query,&$data){ $places = array(); foreach ($values as $value) { $places[] = '?'; $data[] = $value; } $query .= $column.' IN ('. implode(',',$places).')'; }), 'not in'=> array( 'slug'=> 'not in', 'label'=>'Ne contient aucune des valeurs', 'sql'=>function($column,$values,&$query,&$data){ $places = array(); foreach ($values as $value) { $places[] = '?'; $data[] = $value; } $query .= $column.' NOT IN ('. implode(',',$places).')'; }), 'null'=> array( 'slug'=> 'null', 'label'=>'Non renseigné', 'sql'=>'{{column}} IS NULL' ), 'not null'=> array( 'slug'=> 'not null', 'label'=>'Renseigné', 'sql'=>'{{column}} IS NOT NULL' ), 'like'=> array( 'slug'=> 'like', 'label'=>'Contient', 'sql'=>function($column,$values,&$query,&$data){ $data[] = '%'.$values[0].'%'; $query .= ' '.$column.' LIKE ?' ; } ), 'not like'=> array( 'slug'=> 'not like', 'label'=>'Ne contient pas', 'sql'=>function($column,$values,&$query,&$data){ $data[] = '%'.$values[0].'%'; $query .= ' '.$column.' NOT LIKE ?' ; } ), '='=> array( 'slug'=> '=', 'label'=>'Egal', 'sql'=>'{{column}} = {{value}}' ), '!='=> array( 'slug'=> '!=', 'label'=>'Différent', 'sql'=>'{{column}} != {{value}}' ), '<'=> array( 'slug'=> '<', 'label'=>'Inférieur', 'sql'=>'{{column}} < {{value}}' ), '>'=> array( 'slug'=> '>', 'label'=>'Supérieur', 'sql'=>'{{column}} > {{value}}' ), 'between'=> array( 'slug'=> 'between', 'label'=>'Entre', 'values' => 2, 'sql'=>function($column,$values,&$query,&$data){ $query .= ' '.$column.' between ? AND ? ' ; $data[] = $values[0]; $data[] = $values[1]; } ), 'inline-or'=> array( 'slug'=> 'inline-or', 'label'=>'Contient une des valeurs', 'sql'=>function($column,$values,&$query,&$data){ $values = explode(',', $values[0]); $query .= ' ( '; foreach($values as $i=>$value){ $query .= ' '.($i!=0?' OR ':' ').$column.' LIKE ? '; $data[] = '%,'.$value.',%'; } $query .= ' ) '; }), 'inline-and'=> array( 'slug'=> 'inline-and', 'label'=>'Contient toutes les valeurs', 'sql'=>function($column,$values,&$query,&$data){ $values = explode(',', $values[0]); $query .= ' ( '; foreach($values as $i=>$value){ $query .= ' '.($i!=0?' AND ':' ').$column.' LIKE ? '; $data[] = '%,'.$value.',%'; } $query .= ' ) '; }) ); if(!isset($key)) return $operators; return isset($operators[$key]) ?$operators[$key] : array('label'=>'Non définit','sql'=>''); } public static function processField(&$field,&$value,&$values,&$i){ if($field['operator'] == 'BETWEEN' || $field['operator'] == 'NOT BETWEEN'){ foreach ($value as $v2) { $tag = ':'.$i; $field['tag'][]= $tag; $values[$tag] = $v2; $i++; } $field['tag'] = implode(' AND ',$field['tag']); }else if($field['operator'] == 'IN' || $field['operator'] == 'NOT IN'){ $field['tag'] = array(); $valueArray = !is_array($value)?explode(',',$value) : $value; foreach ($valueArray as $v2) { $tag = ':'.$i; $field['tag'][]= $tag; $values[$tag] = $v2; $i++; } $field['tag'] = implode(',',$field['tag']); $field['operator'] = $field['operator'].'('; $field['postoperator'] = ')'; }else if(is_null($value)){ $field['operator'] = ' IS'.($field['operator']=='!=' ? ' NOT ':' ').'NULL '; $field['tag'] = ''; }else{ $tag = ':'.$i; $field['tag'] = $tag; $values[$tag] = $value; $i++; } } public static function types(){ $types = array(); $types['string'] = 'VARCHAR(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}}" {{jointableAlias}} ON "{{jointable1}}".{{field1}}= {{jointableAlias}}.{{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}} {{?filter}} WHERE {{:filter}} "{{table}}"."{{key}}" {{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{/?filters}}'; return $sql; } public static function insert_head(){ $sql = 'INSERT INTO "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{;}} , {{/;}} {{/:fields}} {{/?fields}})VALUES'; return $sql; } public static function insert_body(){ $sql = '({{?fields}} {{:fields}}{{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}})'; return $sql; } public static function insert(){ $sql = self::insert_head().self::insert_body(); 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 sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = \'U\''; return $sql; } public static function show_columns(){ $sql = "select * from information_schema.columns where table_name = '{{table}}'"; return $sql; } } ?>