git.dj3c1t.com Git - mtweb/blob - mw/app/data/upgrades/sql/mtweb_sql_version_0_11_2.php

   1 <?php
   2
   3   class mtweb_sql_version_0_11_2 extends mw_data_version{
   4
   5     function version(){
   6       return "0.11.2";
   7     }
   8
   9     function do_upgrade(){
  10
  11       $env = $this->env();
  12       $data = $env->data();
  13       $sgbd = $data->sgbd();
  14
  15       if(($env->bdd("sgbd") == "pdo_mysql") || ($env->bdd("sgbd") == "mysql")){
  16         if(!$data->set_sgbd_param("SQL_MODE", "NO_AUTO_VALUE_ON_ZERO")){
  17           return "imposible de parametrer SQL_MODE en NO_AUTO_VALUE_ON_ZERO";
  18         }
  19       }
  20
  21       /* ----------------------------------------------------------------------
  22                                                                   action_status
  23                                                           devient actions_roles
  24                                               element id_status devient id_role
  25       */
  26
  27       if(!$sgbd->table_exists("#--actions_roles")){
  28         if(!$sgbd->table_exists("#--action_status")){
  29          return "impossible de trouver la table action_status";
  30         }
  31         if(
  32           !$data->data_create_table(
  33             array(
  34               "table_name" => "actions_roles",
  35               "fields" => array(
  36                 "id" => array("type" => "int", "autoincrement" => true),
  37                 "action" => array("type" => "varchar"),
  38                 "id_role" => array("type" => "int")
  39               ),
  40               "keys" => array(
  41                 array(
  42                   "fields" => array("id"),
  43                   "primary" => true
  44                 ),
  45                 array(
  46                   "fields" => array("id_role")
  47                 )
  48               ),
  49               "options" => array("default_charset" => "utf8")
  50             )
  51           )
  52         ){
  53           return "impossible de créer la table actions_roles";
  54         }
  55
  56         $sql =
  57          "INSERT INTO #--actions_roles(action, id_role)"
  58         ." SELECT action, id_status"
  59         ." FROM #--action_status";
  60         try{
  61           $sgbd->query($sql);
  62         }
  63         catch(Exception $e){
  64           return "impossible d'importer action_status dans actions_roles";
  65         }
  66         $sql =
  67          "INSERT INTO #--actions_roles(action, id_role) VALUES('config', 3)";
  68         try{
  69           $sgbd->query($sql);
  70         }
  71         catch(Exception $e){
  72           return "impossible d'ajouter la protection d'accès webmaster dans actions_roles";
  73         }
  74         try{
  75           $sgbd->query("DROP TABLE #--action_status");
  76         }
  77         catch(Exception $e){
  78           return "impossible de supprimer la table action_status";
  79         }
  80       }
  81
  82       /* ----------------------------------------------------------------------
  83                                                                     user_status
  84                                                                   devient roles
  85                                                 plus d'element creation_default
  86                                                         nouvel element intitule
  87       */
  88
  89       if(!$sgbd->table_exists("#--roles")){
  90         if(!$sgbd->table_exists("#--user_status")){
  91          return "impossible de trouver la table user_status";
  92         }
  93         if(
  94           !$data->data_create_table(
  95             array(
  96               "table_name" => "roles",
  97               "fields" => array(
  98                 "id" => array("type" => "int", "autoincrement" => true),
  99                 "nom" => array("type" => "varchar"),
 100                 "intitule" => array("type" => "varchar")
 101               ),
 102               "keys" => array(
 103                 array(
 104                   "fields" => array("id"),
 105                   "primary" => true
 106                 )
 107               ),
 108               "options" => array("default_charset" => "utf8")
 109             )
 110           )
 111         ){
 112           return "impossible de créer la table roles";
 113         }
 114         try{
 115           $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (0, 'guest', 'invité')";\r
 116           $sgbd->query($sql);
 117           $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (1, 'admin', 'administrateur')";
 118           $sgbd->query($sql);\r
 119           $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (2, 'membre', 'membre')";
 120           $sgbd->query($sql);\r
 121           $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (3, 'webmaster', 'webmaster')";
 122           $sgbd->query($sql);
 123         }
 124         catch(Exception $e){
 125           return "impossible de remplir la table roles";
 126         }
 127         try{
 128           $sgbd->query("DROP TABLE #--user_status");
 129         }
 130         catch(Exception $e){
 131           return "impossible de supprimer la table user_status";
 132         }
 133       }
 134
 135       /* ----------------------------------------------------------------------
 136                                                      nouvelle table users_roles
 137                                  importer users.status dans users_roles.id_role
 138       */
 139
 140       if(!$sgbd->table_exists("#--users_roles")){
 141         if(
 142           !$data->data_create_table(
 143             array(
 144               "table_name" => "users_roles",
 145               "fields" => array(
 146                 "id_user" => array("type" => "int"),
 147                 "id_role" => array("type" => "int")
 148               ),
 149               "keys" => array(
 150                 array(
 151                   "fields" => array("id_user", "id_role"),
 152                   "primary" => true
 153                 ),
 154                 array(
 155                   "fields" => array("id_role")
 156                 )
 157               ),
 158               "options" => array("default_charset" => "utf8")
 159             )
 160           )
 161         ){
 162           return "impossible de créer la table users_roles";
 163         }
 164         if(($users = $data->data_list(array("table_name" => "users"))) === false){
 165           return "impossible de lire la table des utilisateurs";
 166         }
 167         foreach($users["list"] as $user){
 168           if(
 169             !$data->data_insert(
 170               array(
 171                 "table_name" => "users_roles",
 172                 "values" => array(
 173                   "id_user" => $user["id"],
 174                   "id_role" => $user["status"]
 175                 )
 176               )
 177             )
 178           ){
 179             return "erreur lors de l'insertion des roles dans users_roles";
 180           }
 181         }
 182         $sql = "ALTER TABLE #--users RENAME TO #--users_tmp";
 183         try{
 184           $sgbd->query($sql);
 185         }
 186         catch(Exception $e){
 187           return "impossible de renommer la table users en users_tmp";
 188         }
 189         if(
 190           !$data->data_create_table(
 191             array(
 192               "table_name" => "users",
 193               "fields" => array(
 194                 "id" => array("type" => "int", "autoincrement" => true),
 195                 "login" => array("type" => "varchar"),
 196                 "password" => array("type" => "varchar"),
 197                 "email" => array("type" => "varchar")
 198               ),
 199               "keys" => array(
 200                 array(
 201                   "fields" => array("id"),
 202                   "primary" => true
 203                 )
 204               ),
 205               "options" => array("default_charset" => "utf8")
 206             )
 207           )
 208         ){
 209           return "impossible de créer la table users";
 210         }
 211         $sql =
 212          "INSERT INTO #--users(id, login, password, email)"
 213         ." SELECT id, login, password, email"
 214         ." FROM #--users_tmp";
 215         try{
 216           $sgbd->query($sql);
 217         }
 218         catch(Exception $e){
 219           return "impossible d'importer users_tmp dans users";
 220         }
 221         try{
 222           $sgbd->query("DROP TABLE #--users_tmp");
 223         }
 224         catch(Exception $e){
 225           return "impossible de supprimer la table users_tmp";
 226         }
 227       }
 228
 229       /* ----------------------------------------------------------------------
 230                                                         nouvelle table versions
 231       */
 232
 233       if(!$sgbd->table_exists("#--versions")){
 234         if(
 235           !$data->data_create_table(
 236             array(
 237               "table_name" => "versions",
 238               "fields" => array(
 239                 "id" => array("type" => "int", "autoincrement" => true),
 240                 "application" => array("type" => "varchar"),
 241                 "version" => array("type" => "varchar")
 242               ),
 243               "keys" => array(
 244                 array(
 245                   "fields" => array("id"),
 246                   "primary" => true
 247                 )
 248               ),
 249               "options" => array("default_charset" => "utf8")
 250             )
 251           )
 252         ){
 253           return "impossible de créer la table versions";
 254         }
 255       }
 256
 257       return true;
 258     }
 259
 260   }