Oracle.class.php 8.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276
  1. <?php
  2. /**
  3. * Define SQL for Oracle V 12c database system
  4. * @author valentin carruesco
  5. * @category Core
  6. * @license MIT
  7. */
  8. class Oracle
  9. {
  10. const label = 'Oracle';
  11. const connection = 'odbc:{{name}}';
  12. const description = 'Base authentifiée necessitant un serveur Oracle Database XE ou Oracle Database Entreprise, creation de source ODBC à prévoir';
  13. const table_escape = '"';
  14. const column_escape = '"';
  15. public static function pdo_attributes(){
  16. return array(
  17. PDO::ATTR_PERSISTENT => true,
  18. PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION
  19. );
  20. }
  21. public static function fields(){
  22. return array(
  23. array('id'=>'name','label'=>'Nom de la source ODBC','default'=>'','comment'=>''),
  24. array('id'=>'login','label'=>'Identifiant','default'=>'','comment'=>''),
  25. array('id'=>'password','label'=>'Mot de passe','default'=>'','comment'=>'')
  26. );
  27. }
  28. public static function operators($key=null){
  29. $operators =
  30. array(
  31. 'in'=> array(
  32. 'slug'=> 'in',
  33. 'label'=>'Contient une des valeurs',
  34. 'sql'=>function($column,$values,&$query,&$data){
  35. $places = array();
  36. foreach ($values as $value) {
  37. $places[] = '?';
  38. $data[] = $value;
  39. }
  40. $query .= $column.' IN ('. implode(',',$places).')';
  41. }),
  42. 'not in'=> array(
  43. 'slug'=> 'not in',
  44. 'label'=>'Ne contient aucune des valeurs',
  45. 'sql'=>function($column,$values,&$query,&$data){
  46. $places = array();
  47. foreach ($values as $value) {
  48. $places[] = '?';
  49. $data[] = $value;
  50. }
  51. $query .= $column.' NOT IN ('. implode(',',$places).')';
  52. }),
  53. 'null'=> array(
  54. 'slug'=> 'null',
  55. 'label'=>'Non renseigné',
  56. 'sql'=>'{{column}} IS NULL'
  57. ),
  58. 'not null'=> array(
  59. 'slug'=> 'not null',
  60. 'label'=>'Renseigné',
  61. 'sql'=>'{{column}} IS NOT NULL'
  62. ),
  63. 'like'=> array(
  64. 'slug'=> 'like',
  65. 'label'=>'Contient',
  66. 'sql'=>function($column,$values,&$query,&$data){
  67. $data[] = '%'.$values[0].'%';
  68. $query .= ' '.$column.' LIKE ?' ;
  69. }
  70. ),
  71. 'not like'=> array(
  72. 'slug'=> 'not like',
  73. 'label'=>'Ne contient pas',
  74. 'sql'=>function($column,$values,&$query,&$data){
  75. $data[] = '%'.$values[0].'%';
  76. $query .= ' '.$column.' NOT LIKE ?' ;
  77. }
  78. ),
  79. '='=> array(
  80. 'slug'=> '=',
  81. 'label'=>'Egal',
  82. 'sql'=>'{{column}} = {{value}}'
  83. ),
  84. '!='=> array(
  85. 'slug'=> '!=',
  86. 'label'=>'Différent',
  87. 'sql'=>'{{column}} != {{value}}'
  88. ),
  89. '<'=> array(
  90. 'slug'=> '<',
  91. 'label'=>'Inférieur',
  92. 'sql'=>'{{column}} < {{value}}'
  93. ),
  94. '>'=> array(
  95. 'slug'=> '>',
  96. 'label'=>'Supérieur',
  97. 'sql'=>'{{column}} > {{value}}'
  98. ),
  99. 'between'=> array(
  100. 'slug'=> 'between',
  101. 'label'=>'Entre',
  102. 'values' => 2,
  103. 'sql'=>function($column,$values,&$query,&$data){
  104. $query .= ' '.$column.' between ? AND ? ' ;
  105. $data[] = $values[0];
  106. $data[] = $values[1];
  107. }
  108. ),
  109. 'inline-or'=> array(
  110. 'slug'=> 'inline-or',
  111. 'label'=>'Contient une des valeurs',
  112. 'sql'=>function($column,$values,&$query,&$data){
  113. $values = explode(',', $values[0]);
  114. $query .= ' ( ';
  115. foreach($values as $i=>$value){
  116. $query .= ' '.($i!=0?' OR ':' ').$column.' LIKE ? ';
  117. $data[] = '%,'.$value.',%';
  118. }
  119. $query .= ' ) ';
  120. }),
  121. 'inline-and'=> array(
  122. 'slug'=> 'inline-and',
  123. 'label'=>'Contient toutes les valeurs',
  124. 'sql'=>function($column,$values,&$query,&$data){
  125. $values = explode(',', $values[0]);
  126. $query .= ' ( ';
  127. foreach($values as $i=>$value){
  128. $query .= ' '.($i!=0?' AND ':' ').$column.' LIKE ? ';
  129. $data[] = '%,'.$value.',%';
  130. }
  131. $query .= ' ) ';
  132. })
  133. );
  134. if(!isset($key)) return $operators;
  135. return isset($operators[$key]) ?$operators[$key] : array('label'=>'Non définit','sql'=>'');
  136. }
  137. public static function processField(&$field,&$value,&$values,&$i){
  138. if($field['type'] == 'date'){
  139. $field['operator'].= ' to_date( ';
  140. $field['postoperator'].= ',\'YYYY-MM-DD\')';
  141. }
  142. switch($field['operator']){
  143. case 'IN':
  144. case 'NOT IN':
  145. $field['tag'] = array();
  146. $valueArray = !is_array($value)?explode(',',$value) : $value;
  147. foreach ($valueArray as $v2) {
  148. $tag = ':'.$i;
  149. $field['tag'][]= $tag;
  150. $values[$tag] = $v2;
  151. $i++;
  152. }
  153. $field['tag'] = implode(',',$field['tag']);
  154. $field['operator'] = $field['operator'].'(';
  155. $field['postoperator'] = ')';
  156. break;
  157. default:
  158. $tag = ':'.$i;
  159. $field['tag'] = $tag;
  160. $values[$tag] = $value;
  161. $i++;
  162. break;
  163. }
  164. if(is_null($value)){
  165. $field['operator'] = ' IS'.($field['operator']=='!=' ? ' NOT ':' ').'NULL ';
  166. $field['tag'] = '';
  167. }
  168. }
  169. public static function types(){
  170. $types = array();
  171. $types['string'] = 'VARCHAR2(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
  172. $types['longstring'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  173. $types['key'] = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
  174. $types['object'] = $types['timestamp'] = $types['datetime'] = $types['date'] = $types['int'] = 'INT(11)';
  175. $types['boolean'] = 'tinyint(1) NOT NULL DEFAULT \'0\'';
  176. $types['blob'] = ' BLOB';
  177. $types['float'] = 'FLOAT';
  178. $types['decimal'] = 'DECIMAL(20,2)';
  179. $types['default'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  180. return $types;
  181. }
  182. public static function select(){
  183. $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}}FETCH NEXT {{:limit}}{{value}}{{;}}{{/;}} ROWS ONLY{{/:limit}}{{/?limit}}';
  184. return $sql;
  185. }
  186. public static function delete(){
  187. $sql = 'DELETE FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?limit}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/:limit}}{{/?limit}}';
  188. return $sql;
  189. }
  190. public static function count(){
  191. $sql = 'SELECT COUNT({{selected}}) number FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}}{{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
  192. return $sql;
  193. }
  194. public static function update(){
  195. $sql = 'UPDATE "{{table}}" SET {{?fields}} {{:fields}}"{{key}}"={{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}} {{?filter}} WHERE {{:filter}} "{{table}}"."{{key}}" {{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
  196. return $sql;
  197. }
  198. public static function insert_head(){
  199. $sql = 'INSERT INTO "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{;}} , {{/;}} {{/:fields}} {{/?fields}})';
  200. return $sql;
  201. }
  202. public static function insert_body(){
  203. $sql = '({{?fields}} {{:fields}}{{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}})';
  204. return $sql;
  205. }
  206. public static function insert(){
  207. $sql = self::insert_head().self::insert_body();
  208. return $sql;
  209. }
  210. public static function create(){
  211. $sql = 'CREATE TABLE IF NOT EXISTS "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{value}}{{;}} , {{/;}} {{/:fields}} {{/?fields}}) DEFAULT CHARSET=utf8;';
  212. return $sql;
  213. }
  214. public static function drop(){
  215. $sql = 'DROP TABLE IF EXISTS "{{table}}";';
  216. return $sql;
  217. }
  218. public static function truncate(){
  219. $sql = 'TRUNCATE TABLE "{{table}}";';
  220. return $sql;
  221. }
  222. public static function create_index(){
  223. $sql = 'CREATE INDEX "{{index_name}}" ON "{{table}}" ({{column}})';
  224. return $sql;
  225. }
  226. public static function drop_index(){
  227. $sql = 'DROP INDEX "{{index_name}}" ON "{{table}}"';
  228. return $sql;
  229. }
  230. public static function count_index(){
  231. $sql = 'SELECT COUNT(1) "exists" FROM INFORMATION_SCHEMA.STATISTICS
  232. WHERE table_schema=DATABASE() AND table_name=\'{{table}}\' AND index_name=\'{{index_name}}\'';
  233. return $sql;
  234. }
  235. public static function show_tables(){
  236. $sql = 'SELECT table_name FROM user_tables ORDER BY table_name;';
  237. return $sql;
  238. }
  239. public static function show_columns(){
  240. $sql = "SELECT COLUMN_NAME \"column\",DATA_TYPE \"type\"
  241. FROM user_tab_cols
  242. WHERE table_name = '{{table}}' AND USER_GENERATED = 'YES'";
  243. return $sql;
  244. }
  245. }
  246. ?>