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