git.roojs.org Git - Pman.Core/blob - UpdateDatabase/MysqlEngineCharset.php

   1 <?php
   2 /**
   3   fixes character set and engine=InnoDB.. in Mysql
   4   more efficent
   5  */
   6
   7 class Pman_Core_UpdateDatabase_MysqlEngineCharset {
   8
   9     var $dburl;
  10     var $schema = array();
  11     var $links = array();
  12     var $views = array();
  13
  14     function __construct()
  15     {
  16         // this might get run before we have imported the database
  17         // and hence not have any db.
  18         $this->loadIniFiles(); //?? shared???
  19
  20
  21         $dbo = DB_DataObject::factory('core_enum');
  22
  23
  24
  25         if (is_a($dbo, 'PDO_DataObject')) {
  26
  27             $this->views = $dbo->generator()->introspection()->getListOf('views');
  28         } else {
  29             $db = DB_DataObject::factory('core_enum')->getDatabaseConnection();
  30             $this->views = $db->getListOf( 'views');  // needs updated pear...
  31         }
  32
  33         // update the engine first - get's around 1000 character limit on indexes..cd
  34         // however - Innodb does not support fulltext indexes, so this may fail...
  35         $this->updateEngine();
  36
  37         $this->updateCharacterSet();
  38
  39     }
  40
  41     function loadIniFiles()
  42     {
  43         // will create the combined ini cache file for the running user.
  44
  45         $ff = HTML_FlexyFramework::get();
  46         $ff->generateDataobjectsCache(true);
  47         $this->dburl = parse_url($ff->database);
  48
  49
  50         $dbini = 'ini_'. basename($this->dburl['path']);
  51
  52
  53         $iniCache = isset( $ff->PDO_DataObject) ?  $ff->PDO_DataObject['schema_location'] : $ff->DB_DataObject[$dbini];
  54         if (!file_exists($iniCache)) {
  55             return;
  56         }
  57
  58         $this->schema = parse_ini_file($iniCache, true);
  59         $this->links = parse_ini_file(preg_replace('/\.ini$/', '.links.ini', $iniCache), true);
  60
  61
  62
  63     }
  64
  65     function updateCharacterSet()
  66     {
  67         $views = $this->views;
  68
  69
  70         foreach (array_keys($this->schema) as $tbl){
  71
  72             if(strpos($tbl, '__keys') !== false ){
  73                 continue;
  74             }
  75
  76             if(in_array($tbl , $views)) {
  77                 continue;
  78             }
  79
  80             $ce = DB_DataObject::factory('core_enum');
  81
  82             $ce->query("
  83                 SELECT
  84                         CCSA.character_set_name csname,
  85                         CCSA.collation_name collatename
  86                 FROM
  87                         information_schema.`TABLES` T,
  88                         information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
  89                 WHERE
  90                         CCSA.collation_name = T.table_collation
  91                     AND
  92                         T.table_schema = DATABASE() -- COLLATE utf8_general_ci
  93                     AND
  94                         T.table_name = '{$tbl}' -- COLLATE utf8_general_ci
  95             ");
  96
  97             if (!$ce->fetch()) {
  98                 continue;
  99             }
 100
 101             if($ce->csname == 'utf8' && $ce->collatename == 'utf8_general_ci'){
 102                 echo "utf8: SKIP $tbl\n";
 103                 continue;
 104             }
 105             // this used to be utf8_unicode_ci
 106             //as the default collation for stored procedure parameters is utf8_general_ci and you can't mix collations.
 107
 108             $ce = DB_DataObject::factory('core_enum');
 109             // not sure why, but convert to does not actually change the 'charset=' bit..
 110             $ce->query("ALTER TABLE $tbl CHARSET=utf8");
 111             $ce->query("ALTER TABLE {$tbl} CONVERT TO CHARACTER SET  utf8 COLLATE utf8_general_ci");
 112             echo "utf8: FIXED {$tbl}\n";
 113
 114         }
 115     }
 116     function updateEngine()
 117     {
 118         $db = DB_DataObject::factory('core_enum');
 119         $db->query("show variables like 'innodb_file_per_table'");
 120
 121         $db->fetch();
 122
 123         $pg = HTML_FlexyFramework::get()->page;
 124
 125         if (empty($pg->opts['skip-mysql-checks'])) {
 126             if ($db->Value == 'OFF') {
 127                 die("Error: set innodb_file_per_table = 1 in my.cnf\n\n");
 128             }
 129
 130         }
 131
 132
 133         // get a list of table views...
 134         // innodb in single files is far more efficient that MYD or one big innodb file.
 135         // first check if database is using this format.
 136
 137
 138
 139         $views = $this->views;
 140
 141          echo "a3\n";
 142         foreach (array_keys($this->schema) as $tbl){
 143
 144             if(strpos($tbl, '__keys') !== false ){
 145                 continue;
 146             }
 147
 148             if(in_array($tbl , $views)) {
 149                 continue;
 150             }
 151
 152             $ce = DB_DataObject::factory('core_enum');
 153
 154             $ce->query("
 155                 select
 156                     engine
 157                 from
 158                     information_schema.tables
 159                 where
 160                     table_schema= DATABASE()
 161                     and
 162                     table_name = '{$tbl}'
 163             ");
 164
 165             if (!$ce->fetch()) {
 166                 continue;
 167             }
 168             //AWS is returning captials?
 169             $engine = isset($ce->engine) ? $ce->engine : $ce->ENGINE;
 170
 171             if($engine == 'InnoDB' ){
 172                 echo "InnoDB: SKIP $tbl\n";
 173                 continue;
 174             }
 175             if($engine == 'ndbcluster' ){
 176                 echo "ndbcluster: SKIP $tbl\n";
 177                 continue;
 178             }
 179
 180             // should really determine if we are running in cluster ready ...
 181
 182             // this used to be utf8_unicode_ci
 183             //as the default collation for stored procedure parameters is utf8_general_ci and you can't mix collations.
 184
 185             $ce = DB_DataObject::factory('core_enum');
 186             $ce->query("ALTER TABLE $tbl ENGINE=InnoDB");
 187             echo "InnoDB: FIXED {$tbl}\n";
 188
 189         }
 190     }
 191
 192 }
 193