SqlServer.class.php 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135
  1. <?php
  2. /**
  3. * Define SQL for SQL Server database system
  4. * @author valentin carruesco
  5. * @category Core
  6. * @license copyright
  7. */
  8. class SqlServer
  9. {
  10. const label = 'SQLServer';
  11. const connection = 'sqlsrv:Server={{host}};Database={{name}}';
  12. const description = 'Base microsoft SQL Server authentifiée';
  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'=>'host','label'=>'Serveur','default'=>'localhost','comment'=>''),
  24. array('id'=>'login','label'=>'Identifiant','default'=>'','comment'=>''),
  25. array('id'=>'password','label'=>'Mot de passe','default'=>'','comment'=>''),
  26. array('id'=>'name','label'=>'Nom de la base','default'=>'','comment'=>'')
  27. );
  28. }
  29. public static function processField(&$field,&$value,&$values,&$i){
  30. if($field['operator'] == 'IN'){
  31. $field['tag'] = array();
  32. foreach (explode(',',$value) as $v2) {
  33. $tag = ':'.$i;
  34. $field['tag'][]= $tag;
  35. $values[$tag] = $v2;
  36. $i++;
  37. }
  38. $field['tag'] = implode(',',$field['tag']);
  39. $field['operator'] = 'IN(';
  40. $field['postoperator'] = ')';
  41. }else{
  42. $tag = ':'.$i;
  43. $field['tag'] = $tag;
  44. $values[$tag] = $value;
  45. $i++;
  46. }
  47. }
  48. public static function types(){
  49. $types = array();
  50. $types['string'] = 'VARCHAR(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
  51. $types['longstring'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  52. $types['key'] = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
  53. $types['object'] = $types['timestamp'] = $types['datetime'] = $types['date'] = $types['int'] = 'INT(11)';
  54. $types['boolean'] = 'tinyint(1) NOT NULL DEFAULT \'0\'';
  55. $types['blob'] = ' BLOB';
  56. $types['float'] = 'FLOAT';
  57. $types['decimal'] = 'DECIMAL(20,2)';
  58. $types['default'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  59. return $types;
  60. }
  61. public static function beforeTransaction($pdo){
  62. $pdo->exec("set names utf8");
  63. }
  64. public static function select(){
  65. $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}}';
  66. return $sql;
  67. }
  68. public static function delete(){
  69. $sql = 'DELETE FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?limit}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/:limit}}{{/?limit}}';
  70. return $sql;
  71. }
  72. public static function count(){
  73. $sql = 'SELECT COUNT({{selected}}) number FROM "{{table}}" {{?filter}}WHERE {{:filter}}"{{key}}"{{operator}}{{value}}{{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
  74. return $sql;
  75. }
  76. public static function update(){
  77. $sql = 'UPDATE "{{table}}" SET {{?fields}} {{:fields}}"{{key}}"={{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}} {{?filters}}WHERE {{:filters}}"{{key}}"{{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filters}} {{/?filters}}';
  78. return $sql;
  79. }
  80. public static function insert(){
  81. $sql = 'INSERT INTO "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{;}} , {{/;}} {{/:fields}} {{/?fields}})VALUES({{?fields}} {{:fields}}{{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}})';
  82. return $sql;
  83. }
  84. public static function create(){
  85. $sql = 'CREATE TABLE IF NOT EXISTS "{{table}}" ({{?fields}} {{:fields}}"{{key}}" {{value}}{{;}} , {{/;}} {{/:fields}} {{/?fields}}) DEFAULT CHARSET=utf8;';
  86. return $sql;
  87. }
  88. public static function drop(){
  89. $sql = 'DROP TABLE IF EXISTS "{{table}}";';
  90. return $sql;
  91. }
  92. public static function truncate(){
  93. $sql = 'TRUNCATE TABLE "{{table}}";';
  94. return $sql;
  95. }
  96. public static function create_index(){
  97. $sql = 'CREATE INDEX "{{index_name}}" ON "{{table}}" ({{column}})';
  98. return $sql;
  99. }
  100. public static function drop_index(){
  101. $sql = 'DROP INDEX "{{index_name}}" ON "{{table}}"';
  102. return $sql;
  103. }
  104. public static function count_index(){
  105. $sql = "SELECT COUNT(1) \"exists\" FROM INFORMATION_SCHEMA.STATISTICS
  106. WHERE table_schema=DATABASE() AND table_name='{{table}}' AND index_name='{{index_name}}'";
  107. return $sql;
  108. }
  109. public static function show_tables(){
  110. $sql = 'SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = \'U\'';
  111. return $sql;
  112. }
  113. public static function show_columns(){
  114. $sql = "select * from information_schema.columns where table_name = '{{table}}'";
  115. return $sql;
  116. }
  117. }
  118. ?>