| 1 | <?php
|
|---|
| 2 |
|
|---|
| 3 | #imposta il charset su utf8, qualsiasi altro valore per cambiarlo in latin1;
|
|---|
| 4 | $newcs='utf8';
|
|---|
| 5 |
|
|---|
| 6 | @require_once("config.php");
|
|---|
| 7 | try{
|
|---|
| 8 | $dbi = new PDO("mysql:host=$dbhost", $dbuname, $dbpass, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
|
|---|
| 9 | $sql = "use $dbname";
|
|---|
| 10 | $dbi->exec($sql);
|
|---|
| 11 | }
|
|---|
| 12 | catch(PDOException $e)
|
|---|
| 13 | {
|
|---|
| 14 | die( $sql . "<br>" . $e->getMessage());
|
|---|
| 15 | }
|
|---|
| 16 |
|
|---|
| 17 |
|
|---|
| 18 | ###############
|
|---|
| 19 | function aggiorna($sql,$dbi,$sql2,$num){
|
|---|
| 20 | $ret=0;
|
|---|
| 21 | try{
|
|---|
| 22 | $res = $dbi->prepare("$sql");
|
|---|
| 23 | $res->execute();
|
|---|
| 24 | $ret= 1;
|
|---|
| 25 | }
|
|---|
| 26 | catch(PDOException $e)
|
|---|
| 27 | {
|
|---|
| 28 | $ret=0;
|
|---|
| 29 | echo "<br><span style=\"color: red;\">- Aggiornamento Fallito: $sql</span>";
|
|---|
| 30 | return $ret;
|
|---|
| 31 | }
|
|---|
| 32 |
|
|---|
| 33 | if("$sql2"!=""){
|
|---|
| 34 | try{
|
|---|
| 35 | $res = $dbi->prepare("$sql2");
|
|---|
| 36 | $res->execute();
|
|---|
| 37 | $ret=2;
|
|---|
| 38 | }
|
|---|
| 39 | catch(PDOException $e)
|
|---|
| 40 | {
|
|---|
| 41 | $ret=0;
|
|---|
| 42 | echo "<br><span style=\"color: red;\">- Aggiornamento Fallito: $sql2</span>";
|
|---|
| 43 | return $ret;
|
|---|
| 44 | }
|
|---|
| 45 | }
|
|---|
| 46 | echo "<br><span style=\"color: green;\">- Aggiornamento eseguito correttamente</span>";
|
|---|
| 47 | return $ret;
|
|---|
| 48 | }
|
|---|
| 49 | function aggiorna_index($tab,$ind,$dbi,$sql2,$num){
|
|---|
| 50 | $ret=0;
|
|---|
| 51 | # $conn->getAttribute( constant( "PDO::ATTR_$val" ) )
|
|---|
| 52 | $sqltest="SHOW INDEX FROM `$tab` WHERE KEY_NAME = '$ind'";
|
|---|
| 53 | $res = $dbi->prepare("$sqltest");
|
|---|
| 54 | $res->execute();
|
|---|
| 55 |
|
|---|
| 56 | if($res->rowCount()) {
|
|---|
| 57 | if($ind=='PRIMARY')
|
|---|
| 58 | $sql="ALTER TABLE `$tab` DROP PRIMARY KEY , $sql2 ";
|
|---|
| 59 | else
|
|---|
| 60 | $sql="ALTER TABLE `$tab` DROP INDEX `$ind`";
|
|---|
| 61 | try{
|
|---|
| 62 | $res = $dbi->prepare("$sql");
|
|---|
| 63 | $res->execute();
|
|---|
| 64 | $ret= 1;
|
|---|
| 65 | }
|
|---|
| 66 | catch(PDOException $e)
|
|---|
| 67 | {
|
|---|
| 68 | $ret=0;
|
|---|
| 69 | echo "<br><span style=\"color: red;\">- Tabella: $tab - Indice: $ind - Aggiornamento Fallito: $sql</span>";
|
|---|
| 70 | return $ret;
|
|---|
| 71 | }
|
|---|
| 72 | }
|
|---|
| 73 | if("$sql2"!="" and $ind!='PRIMARY'){
|
|---|
| 74 | try{
|
|---|
| 75 | $res = $dbi->prepare("$sql2");
|
|---|
| 76 | $res->execute();
|
|---|
| 77 | $ret=2;
|
|---|
| 78 | }
|
|---|
| 79 | catch(PDOException $e)
|
|---|
| 80 | {
|
|---|
| 81 | $ret=0;
|
|---|
| 82 | echo "<br><span style=\"color: red;\">- Tabella: $tab - Indice: $ind - Aggiornamento Fallito: $sql2</span>";
|
|---|
| 83 | return $ret;
|
|---|
| 84 | }
|
|---|
| 85 | }
|
|---|
| 86 | echo "<br><span style=\"color: green;\">- Tabella: $tab - Indice: $ind - Index aggiornato</span>";
|
|---|
| 87 | return $ret;
|
|---|
| 88 | }
|
|---|
| 89 |
|
|---|
| 90 |
|
|---|
| 91 |
|
|---|
| 92 |
|
|---|
| 93 | function controllo($tabella,$campo,$num)
|
|---|
| 94 | {
|
|---|
| 95 | global $dbi, $dbname;
|
|---|
| 96 | $sql="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbname' AND TABLE_NAME = '$tabella'";
|
|---|
| 97 | $res = $dbi->prepare("$sql");
|
|---|
| 98 | $res->execute();
|
|---|
| 99 | if($res->rowCount() and $campo=='') return 1;
|
|---|
| 100 | while(list($nome)=$res->fetch(PDO::FETCH_NUM)) {if($nome==$campo) { echo "<br>".$num.") Il campo: $campo Ú presente nella tabella: $tabella"; return 1;}}
|
|---|
| 101 | if($campo) echo "<br>$num) Il campo: $campo non Ú presente nella tabella: $tabella";
|
|---|
| 102 | return 0;
|
|---|
| 103 | }
|
|---|
| 104 |
|
|---|
| 105 | $num=0;
|
|---|
| 106 |
|
|---|
| 107 | if(controllo($prefix.'_authors','admincomune',++$num))
|
|---|
| 108 | {
|
|---|
| 109 | $sql="UPDATE `soraldo_authors` SET `adminop` = '1', `adminsuper` = '0',`admincomune` = '0' WHERE `soraldo_authors`.`aid` != 'admin' AND `soraldo_authors`.`adminsuper` != '1'";
|
|---|
| 110 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 111 | $sql="UPDATE `soraldo_authors` SET `adminop` = '0', `adminsuper` = '0',`admincomune` = '1' WHERE `soraldo_authors`.`aid` = 'admin'";
|
|---|
| 112 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 113 | $sql="UPDATE `soraldo_authors` SET `adminop` = '0', `adminsuper` = '1',`admincomune` = '0' WHERE `soraldo_authors`.`aid` = 'suser' or `soraldo_authors`.`adminsuper` = '1'";
|
|---|
| 114 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 115 | echo "<br> La tabella dei permessi Ú stata aggiornata<br>";
|
|---|
| 116 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_authors non richiede questo aggiornamento</span><br>";
|
|---|
| 117 |
|
|---|
| 118 | if(!controllo($prefix.'_config','versione',++$num))
|
|---|
| 119 | {
|
|---|
| 120 | $sql="alter table `".$prefix."_config` change column `Versione` `versione` int(3)";
|
|---|
| 121 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 122 | } else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_config non richiede questo aggiornamento</span><br>";
|
|---|
| 123 |
|
|---|
| 124 |
|
|---|
| 125 | if(controllo($prefix.'_config','secret',++$num))
|
|---|
| 126 | {
|
|---|
| 127 | $sql="alter table `".$prefix."_config` DROP `secret`";
|
|---|
| 128 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 129 | } else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_config non richiede questo aggiornamento</span><br>";
|
|---|
| 130 |
|
|---|
| 131 | if(controllo($prefix.'_ele_voti_parziale','data',++$num))
|
|---|
| 132 | {
|
|---|
| 133 | $sql="ALTER TABLE `".$prefix."_ele_voti_parziale` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
|
|---|
| 134 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 135 | echo "<br>";
|
|---|
| 136 | }
|
|---|
| 137 | ++$num;
|
|---|
| 138 | $sql="ALTER TABLE `".$prefix."_ele_come` CHANGE `title` `title` VARCHAR(150) NOT NULL DEFAULT ' ', CHANGE `preamble` `preamble` TEXT, CHANGE `content` `content` TEXT, CHANGE `editimage` `editimage` VARCHAR(100) NOT NULL DEFAULT ' '";
|
|---|
| 139 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 140 | echo "<br>";
|
|---|
| 141 | if(!$ret) echo "Il tuo sistema non necessita di questo aggiornamento, questo avviso di errore va ignorato<br>";
|
|---|
| 142 | ++$num;
|
|---|
| 143 | $sql="ALTER TABLE `".$prefix."_ele_link` CHANGE `title` `title` VARCHAR(150) NOT NULL DEFAULT ' ', CHANGE `preamble` `preamble` TEXT, CHANGE `content` `content` TEXT, CHANGE `editimage` `editimage` VARCHAR(100) NOT NULL DEFAULT ' '";
|
|---|
| 144 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 145 | echo "<br>";
|
|---|
| 146 | if(!$ret) echo "Il tuo sistema non necessita di questo aggiornamento, questo avviso di errore va ignorato<br>";
|
|---|
| 147 | ++$num;
|
|---|
| 148 | $sql="ALTER TABLE `".$prefix."_ele_servizi` CHANGE `title` `title` VARCHAR(150) NOT NULL DEFAULT ' ', CHANGE `preamble` `preamble` TEXT, CHANGE `content` `content` TEXT, CHANGE `editimage` `editimage` VARCHAR(100) NOT NULL DEFAULT ' '";
|
|---|
| 149 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 150 | echo "<br>";
|
|---|
| 151 | if(!$ret) echo "Il tuo sistema non necessita di questo aggiornamento, questo avviso di errore va ignorato<br>";
|
|---|
| 152 |
|
|---|
| 153 | if(controllo($prefix.'_ele_rilaff','data',++$num))
|
|---|
| 154 | {
|
|---|
| 155 | $sql="ALTER TABLE `".$prefix."_ele_rilaff` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
|
|---|
| 156 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 157 | echo "<br>";
|
|---|
| 158 | }
|
|---|
| 159 |
|
|---|
| 160 | if(!controllo($prefix.'_ele_gruppo','num_circ',++$num))
|
|---|
| 161 | {
|
|---|
| 162 | $sql="ALTER TABLE `".$prefix."_ele_gruppo` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
|
|---|
| 163 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 164 | } else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_gruppo non richiede questo aggiornamento</span><br>";
|
|---|
| 165 |
|
|---|
| 166 | if(!controllo($prefix.'_ele_voti_gruppo','num_gruppo',++$num))
|
|---|
| 167 | {
|
|---|
| 168 | $sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
|
|---|
| 169 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 170 | } else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_gruppo non richiede questo aggiornamento</span><br>";
|
|---|
| 171 |
|
|---|
| 172 | if(!controllo($prefix.'_ele_lista','num_gruppo',++$num))
|
|---|
| 173 | {
|
|---|
| 174 | $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_gruppo`";
|
|---|
| 175 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 176 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_lista non richiede questo aggiornamento</span><br>";
|
|---|
| 177 |
|
|---|
| 178 | if(!controllo($prefix.'_ele_lista','num_circ',++$num))
|
|---|
| 179 | {
|
|---|
| 180 | $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
|
|---|
| 181 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 182 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_lista non richiede questo aggiornamento</span><br>";
|
|---|
| 183 |
|
|---|
| 184 | if(!controllo($prefix.'_ele_operatori','id_circ',++$num))
|
|---|
| 185 | {
|
|---|
| 186 | $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_circ` INT(11) NOT NULL DEFAULT 0 AFTER `aid`";
|
|---|
| 187 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 188 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_operatori non richiede questo aggiornamento</span><br>";
|
|---|
| 189 |
|
|---|
| 190 | if(!controllo($prefix.'_ele_operatori','id_sez',++$num))
|
|---|
| 191 | {
|
|---|
| 192 | $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_sez` INT(11) NOT NULL DEFAULT 0 AFTER `id_circ`";
|
|---|
| 193 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 194 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_operatori non richiede questo aggiornamento</span><br>";
|
|---|
| 195 |
|
|---|
| 196 | if(!controllo($prefix.'_ele_voti_lista','num_lista',++$num))
|
|---|
| 197 | {
|
|---|
| 198 | $sql="ALTER TABLE `".$prefix."_ele_voti_lista` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
|
|---|
| 199 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 200 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_voti_lista non richiede questo aggiornamento</span><br>";
|
|---|
| 201 |
|
|---|
| 202 | if(!controllo($prefix.'_ele_voti_ref','num_gruppo',++$num))
|
|---|
| 203 | {
|
|---|
| 204 | $sql="ALTER TABLE `".$prefix."_ele_voti_ref` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
|
|---|
| 205 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 206 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_voti_ref non richiede questo aggiornamento</span><br>";
|
|---|
| 207 |
|
|---|
| 208 | if(!controllo($prefix.'_ele_voti_candidati','num_cand',++$num))
|
|---|
| 209 | {
|
|---|
| 210 | $sql="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD `num_cand` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
|
|---|
| 211 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 212 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_voti_candidati non richiede questo aggiornamento</span><br>";
|
|---|
| 213 |
|
|---|
| 214 | if(!controllo($prefix.'_ele_candidati','num_lista',++$num))
|
|---|
| 215 | {
|
|---|
| 216 | $sql="ALTER TABLE `".$prefix."_ele_candidati` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_lista`";
|
|---|
| 217 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 218 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_candidati non richiede questo aggiornamento</span><br>";
|
|---|
| 219 |
|
|---|
| 220 | if(!controllo($prefix.'_ele_sezioni','colore',++$num))
|
|---|
| 221 | {
|
|---|
| 222 | $sql="ALTER TABLE `".$prefix."_ele_sezioni` ADD `colore` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '#FAFAD2' AFTER `solo_lista`";
|
|---|
| 223 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 224 | }else{
|
|---|
| 225 | $sql="ALTER TABLE `".$prefix."_ele_sezioni` CHANGE `colore` `colore` VARCHAR(50) DEFAULT '#FAFAD2'";
|
|---|
| 226 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 227 | } echo "<br>";
|
|---|
| 228 |
|
|---|
| 229 | echo "<br>Aggiornamento per nuovo sistema dei controlli di congruità ";
|
|---|
| 230 |
|
|---|
| 231 | if(!controllo($prefix.'_ele_controlli','id_cons',++$num))
|
|---|
| 232 | {
|
|---|
| 233 | $sql="CREATE TABLE if not exists`".$prefix."_ele_controlli` ( `id_cons` INT(11) NOT NULL , `id_sez` INT(11) NOT NULL , `tipo` VARCHAR(10) NOT NULL , `id` INT(11) NOT NULL , INDEX `sezione` (`id_sez`)) ENGINE = MyISAM";
|
|---|
| 234 | echo "<br>".$num.") Creazione tabella dei controlli: ";
|
|---|
| 235 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 236 | }else echo "<br><span style=\"color: green;\">- La tabella ".$prefix."_ele_controlli non richiede questo aggiornamento</span><br>";
|
|---|
| 237 |
|
|---|
| 238 | echo "<br>".++$num.") Eliminazione della vecchia tabella dei controlli: ";
|
|---|
| 239 | if(controllo($prefix.'_ele_controllisez','',$num))
|
|---|
| 240 | {
|
|---|
| 241 | $sql="DROP TABLE if exists `".$prefix."_ele_controllisez`";
|
|---|
| 242 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 243 | } else echo "<br><span style=\"color: green;\">- Tabella non presente</span><br>";
|
|---|
| 244 |
|
|---|
| 245 | echo "<br>".++$num.") Aggiornamento tabella ".$prefix."_ele_voti_gruppo";
|
|---|
| 246 | $sql="update `".$prefix."_ele_voti_gruppo` as t1 left join `".$prefix."_ele_gruppo` as t2 on t1.id_gruppo=t2.id_gruppo set t1.num_gruppo=t2.num_gruppo;";
|
|---|
| 247 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 248 |
|
|---|
| 249 | echo "<br><br>".++$num.") Aggiornamento dei valori di default: ".$prefix."_ele_voti_lista";
|
|---|
| 250 | $sql="ALTER TABLE `".$prefix."_ele_voti_lista` CHANGE `num_lista` `num_lista` INT(2) UNSIGNED NULL DEFAULT '0';";
|
|---|
| 251 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 252 |
|
|---|
| 253 | echo "<br><br>".++$num.") Aggiornamento tabella ".$prefix."_ele_voti_lista";
|
|---|
| 254 | $sql="update `".$prefix."_ele_voti_lista` as t1 left join `".$prefix."_ele_lista` as t2 on t1.id_lista=t2.id_lista set t1.num_lista=t2.num_lista;";
|
|---|
| 255 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 256 | ##############################################
|
|---|
| 257 |
|
|---|
| 258 | echo "<br><br>".++$num.") Aggiornamento dei valori di default: ".$prefix."_ele_gruppo";
|
|---|
| 259 | $sql="ALTER TABLE `".$prefix."_ele_gruppo` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
|
|---|
| 260 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 261 |
|
|---|
| 262 | $sql="ALTER TABLE `soraldo_authors` CHANGE `adminsuper` `adminsuper` TINYINT(2) NOT NULL DEFAULT '0';";
|
|---|
| 263 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 264 |
|
|---|
| 265 | $sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_gruppo` `num_gruppo` INT(2) UNSIGNED NOT NULL DEFAULT '0';";
|
|---|
| 266 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 267 |
|
|---|
| 268 | $sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
|
|---|
| 269 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 270 |
|
|---|
| 271 | echo "<br><br>".++$num.") Ricostruzione della tabella ".$prefix."_ele_fasce`";
|
|---|
| 272 | $sql="DROP TABLE `".$prefix."_ele_fasce`";
|
|---|
| 273 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 274 |
|
|---|
| 275 | $sql="CREATE TABLE `".$prefix."_ele_fasce` (
|
|---|
| 276 | `id_fascia` int(2) NOT NULL,
|
|---|
| 277 | `abitanti` int(11) NOT NULL,
|
|---|
| 278 | `seggi` int(4) NOT NULL,
|
|---|
| 279 | `id_conf` int(11) DEFAULT 1
|
|---|
| 280 | ) ENGINE=MyISAM DEFAULT CHARSET 'utf8';";
|
|---|
| 281 |
|
|---|
| 282 |
|
|---|
| 283 | $sql2="INSERT INTO `".$prefix."_ele_fasce` (`id_fascia`, `abitanti`, `seggi`, `id_conf`) VALUES
|
|---|
| 284 | (1, 3000, 12, 1),
|
|---|
| 285 | (2, 10000, 16, 1),
|
|---|
| 286 | (3, 15000, 20, 1),
|
|---|
| 287 | (4, 30000, 20, 1),
|
|---|
| 288 | (5, 100000, 30, 1),
|
|---|
| 289 | (6, 250000, 40, 1),
|
|---|
| 290 | (7, 500000, 46, 1),
|
|---|
| 291 | (8, 1000000, 50, 1),
|
|---|
| 292 | (9, 100000000, 60, 1),
|
|---|
| 293 | (1, 3000, 12, 2),
|
|---|
| 294 | (2, 10000, 16, 2),
|
|---|
| 295 | (3, 15000, 20, 2),
|
|---|
| 296 | (4, 30000, 20, 2),
|
|---|
| 297 | (5, 100000, 30, 2),
|
|---|
| 298 | (6, 250000, 40, 2),
|
|---|
| 299 | (7, 500000, 46, 2),
|
|---|
| 300 | (8, 1000000, 50, 2),
|
|---|
| 301 | (9, 100000000, 60, 2),
|
|---|
| 302 | (1, 3000, 9, 3),
|
|---|
| 303 | (2, 5000, 9, 3),
|
|---|
| 304 | (4, 15000, 16, 3),
|
|---|
| 305 | (3, 10000, 12, 3),
|
|---|
| 306 | (5, 30000, 16, 3),
|
|---|
| 307 | (6, 100000, 24, 3),
|
|---|
| 308 | (7, 250000, 32, 3),
|
|---|
| 309 | (8, 500000, 36, 3),
|
|---|
| 310 | (9, 1000000, 40, 3),
|
|---|
| 311 | (10, 100000000, 48, 3),
|
|---|
| 312 | (1, 3000, 6, 4),
|
|---|
| 313 | (2, 5000, 7, 4),
|
|---|
| 314 | (3, 10000, 12, 4),
|
|---|
| 315 | (4, 15000, 16, 4),
|
|---|
| 316 | (5, 30000, 16, 4),
|
|---|
| 317 | (6, 100000, 24, 4),
|
|---|
| 318 | (7, 250000, 32, 4),
|
|---|
| 319 | (8, 500000, 36, 4),
|
|---|
| 320 | (9, 1000000, 40, 4),
|
|---|
| 321 | (10, 100000000, 48, 4),
|
|---|
| 322 | (1, 3000, 9, 5),
|
|---|
| 323 | (2, 5000, 9, 5),
|
|---|
| 324 | (3, 10000, 12, 5),
|
|---|
| 325 | (4, 15000, 16, 5),
|
|---|
| 326 | (5, 30000, 16, 5),
|
|---|
| 327 | (6, 100000, 24, 5),
|
|---|
| 328 | (7, 250000, 32, 5),
|
|---|
| 329 | (8, 500000, 36, 5),
|
|---|
| 330 | (9, 1000000, 40, 5),
|
|---|
| 331 | (10, 100000000, 48, 5),
|
|---|
| 332 | (1, 3000, 6, 6),
|
|---|
| 333 | (2, 5000, 7, 6),
|
|---|
| 334 | (3, 10000, 10, 6),
|
|---|
| 335 | (4, 15000, 16, 6),
|
|---|
| 336 | (5, 30000, 16, 6),
|
|---|
| 337 | (6, 100000, 24, 6),
|
|---|
| 338 | (7, 250000, 32, 6),
|
|---|
| 339 | (8, 500000, 36, 6),
|
|---|
| 340 | (9, 1000000, 40, 6),
|
|---|
| 341 | (10, 100000000, 48, 6),
|
|---|
| 342 | (1, 3000, 10, 7),
|
|---|
| 343 | (2, 10000, 12, 7),
|
|---|
| 344 | (3, 15000, 16, 7),
|
|---|
| 345 | (4, 30000, 16, 7),
|
|---|
| 346 | (5, 100000, 24, 7),
|
|---|
| 347 | (6, 250000, 32, 7),
|
|---|
| 348 | (7, 500000, 36, 7),
|
|---|
| 349 | (8, 1000000, 40, 7),
|
|---|
| 350 | (9, 100000000, 48, 7);";
|
|---|
| 351 | $ret=aggiorna($sql,$dbi,$sql2,$num);
|
|---|
| 352 | #if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
|
|---|
| 353 |
|
|---|
| 354 | $sql="ALTER TABLE `".$prefix."_ele_fasce`
|
|---|
| 355 | ADD KEY `id_fascia` (`id_fascia`);";
|
|---|
| 356 | $ret=aggiorna($sql,$dbi,'',$num);
|
|---|
| 357 | #if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
|
|---|
| 358 |
|
|---|
| 359 |
|
|---|
| 360 |
|
|---|
| 361 | #if(!$ret)
|
|---|
| 362 | # echo "<br>".$num++.") Fallito: $sql";
|
|---|
| 363 | #elseif ($ret==1)
|
|---|
| 364 | # echo "<br>".$num++.") Index eliminato";
|
|---|
| 365 | #else
|
|---|
| 366 | # echo "<br>".$num++.") Index aggiunto";
|
|---|
| 367 | #$sql="ALTER TABLE `".$prefix."_ele_voti_lista` DROP INDEX if exists `id_cons`";
|
|---|
| 368 | echo "<br><br>".++$num.") Ricostruzione e aggioramento indici";
|
|---|
| 369 | $tab=$prefix."_ele_voti_ref";
|
|---|
| 370 | $ind="id_cons";
|
|---|
| 371 | # $sql="ALTER TABLE `".$prefix."_ele_voti_ref` DROP INDEX `id_cons`; ";
|
|---|
| 372 | $sql2="ALTER TABLE `".$prefix."_ele_voti_ref` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
|
|---|
| 373 | $ret=aggiorna_index($tab, $ind,$dbi,$sql2,$num);
|
|---|
| 374 |
|
|---|
| 375 | $tab=$prefix."_ele_sezioni";
|
|---|
| 376 | $ind="id_cons";
|
|---|
| 377 | $sql2="ALTER TABLE `".$prefix."_ele_sezioni` ADD UNIQUE `id_cons` (`id_cons`, `num_sez`) USING BTREE";
|
|---|
| 378 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 379 |
|
|---|
| 380 | $tab=$prefix."_ele_voti_lista";
|
|---|
| 381 | $ind="id_cons";
|
|---|
| 382 | $sql2="ALTER TABLE `".$prefix."_ele_voti_lista` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_lista`) USING BTREE";
|
|---|
| 383 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 384 |
|
|---|
| 385 | $tab=$prefix."_ele_voti_gruppo";
|
|---|
| 386 | $ind="id_cons";
|
|---|
| 387 | #$sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` DROP INDEX if exists `id_cons`";
|
|---|
| 388 | $sql2="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_gruppo`) USING BTREE";
|
|---|
| 389 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 390 |
|
|---|
| 391 | $tab=$prefix."_ele_voti_candidati";
|
|---|
| 392 | $ind="id_cons";
|
|---|
| 393 | #$sql="ALTER TABLE `".$prefix."_ele_voti_candidati` DROP INDEX if exists `id_cons`";
|
|---|
| 394 | $sql2="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_cand`) USING BTREE";
|
|---|
| 395 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 396 |
|
|---|
| 397 | $tab=$prefix."_ele_lista";
|
|---|
| 398 | $ind="id_cons";
|
|---|
| 399 | #$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `id_cons`";
|
|---|
| 400 | $sql2="ALTER TABLE `".$prefix."_ele_lista` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
|
|---|
| 401 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 402 |
|
|---|
| 403 | $tab=$prefix."_ele_lista";
|
|---|
| 404 | $ind="PRIMARY";
|
|---|
| 405 | #$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `PRIMARY`";
|
|---|
| 406 | $sql2="ADD PRIMARY KEY (`id_lista`) USING BTREE";
|
|---|
| 407 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 408 |
|
|---|
| 409 | $tab=$prefix."_ele_gruppo";
|
|---|
| 410 | $ind="id_cons";
|
|---|
| 411 | #$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `id_cons`";
|
|---|
| 412 | $sql2="ALTER TABLE `".$prefix."_ele_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_circ`) USING BTREE";
|
|---|
| 413 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 414 |
|
|---|
| 415 | $tab=$prefix."_ele_gruppo";
|
|---|
| 416 | $ind="PRIMARY";
|
|---|
| 417 | #$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `PRIMARY`";
|
|---|
| 418 | $sql2="ADD PRIMARY KEY (`id_gruppo`) USING BTREE";
|
|---|
| 419 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 420 |
|
|---|
| 421 | $tab=$prefix."_ele_candidati";
|
|---|
| 422 | $ind="id_cons";
|
|---|
| 423 | #$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `id_cons`";
|
|---|
| 424 | $sql2="ALTER TABLE `".$prefix."_ele_candidati` ADD INDEX `id_cons` (`id_cons`, `id_lista`) USING BTREE";
|
|---|
| 425 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 426 |
|
|---|
| 427 | $tab=$prefix."_ele_candidati";
|
|---|
| 428 | $ind="PRIMARY";
|
|---|
| 429 | #$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `PRIMARY`";
|
|---|
| 430 | $sql2="ADD PRIMARY KEY (`id_cand`) USING BTREE";
|
|---|
| 431 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 432 |
|
|---|
| 433 | $tab=$prefix."_ele_consultazione";
|
|---|
| 434 | $ind="descrizione";
|
|---|
| 435 | #$sql="ALTER TABLE `".$prefix."_ele_consultazione` DROP INDEX if exists `descrizione`";
|
|---|
| 436 | $sql2="ALTER TABLE `".$prefix."_ele_consultazione` ADD UNIQUE `descrizione` (`descrizione`(100))";
|
|---|
| 437 | $ret=aggiorna_index($tab,$ind,$dbi,$sql2,$num);
|
|---|
| 438 |
|
|---|
| 439 | echo "<br><br>".++$num.") Modifica Charset del database";
|
|---|
| 440 | if($newcs=='utf8') {
|
|---|
| 441 | $cset='utf8';
|
|---|
| 442 | $ccollate='utf8_general_ci';
|
|---|
| 443 | $preset='latin1';
|
|---|
| 444 | }else{
|
|---|
| 445 | $cset='latin1';
|
|---|
| 446 | $ccollate='latin1_swedish_ci';
|
|---|
| 447 | $preset='utf8';
|
|---|
| 448 | }
|
|---|
| 449 | #$sql="ALTER DATABASE $dbname CHARACTER SET '$cset' COLLATE '$ccollate'";
|
|---|
| 450 | #$res = $dbi->prepare("$sql");
|
|---|
| 451 | #$res->execute();
|
|---|
| 452 |
|
|---|
| 453 | $sql="SELECT table_name,column_name,column_default,column_type,is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbname' and (character_set_name='$preset' or collation_name like '$preset%')";
|
|---|
| 454 | $res = $dbi->prepare("$sql");
|
|---|
| 455 | $res->execute();
|
|---|
| 456 | $tab='';
|
|---|
| 457 | $agg=$res->rowCount();
|
|---|
| 458 |
|
|---|
| 459 | while(list($nometab,$campo,$def,$tipo,$nul)=$res->fetch(PDO::FETCH_NUM)) {
|
|---|
| 460 | if($tab!=$nometab){
|
|---|
| 461 | $sql="alter table $nometab DEFAULT CHARSET=$cset COLLATE $ccollate";
|
|---|
| 462 | try{
|
|---|
| 463 | $res2 = $dbi->prepare("$sql");
|
|---|
| 464 | $res2->execute();
|
|---|
| 465 | }
|
|---|
| 466 | catch(PDOException $e)
|
|---|
| 467 | {
|
|---|
| 468 | die( $sql . "<br>" . $e->getMessage());
|
|---|
| 469 | }
|
|---|
| 470 | $tab=$nometab; echo "<br><span style=\"color: green;\">- Tabella: $nometab</span>";
|
|---|
| 471 | }
|
|---|
| 472 | if($def!='') $default="DEFAULT '$def'"; else $default='';
|
|---|
| 473 | if($nul=='NO') $nullable='NOT NULL'; else $nullable='NULL';
|
|---|
| 474 | $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET '$cset' COLLATE '$ccollate' $nullable $default;";
|
|---|
| 475 | try{
|
|---|
| 476 | $res2 = $dbi->prepare("$sql");
|
|---|
| 477 | $res2->execute();
|
|---|
| 478 | }
|
|---|
| 479 | catch(PDOException $e)
|
|---|
| 480 | {
|
|---|
| 481 | $default="DEFAULT $def";
|
|---|
| 482 | $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET '$cset' COLLATE '$ccollate' $nullable $default;";
|
|---|
| 483 | try{
|
|---|
| 484 | $res2 = $dbi->prepare("$sql");
|
|---|
| 485 | $res2->execute();
|
|---|
| 486 | }
|
|---|
| 487 | catch(PDOException $e)
|
|---|
| 488 | {
|
|---|
| 489 | die( $sql . "<br>" . $e->getMessage());
|
|---|
| 490 | }
|
|---|
| 491 | }
|
|---|
| 492 | echo "<br><span style=\"color: green;\">-- $campo</span>";
|
|---|
| 493 | }
|
|---|
| 494 | $sql="SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = '$dbname' and table_collation like '$preset%'";
|
|---|
| 495 | $res = $dbi->prepare("$sql");
|
|---|
| 496 | $res->execute();
|
|---|
| 497 | $tab='';
|
|---|
| 498 | if(!$agg) $agg=$res->rowCount();
|
|---|
| 499 | while(list($nometab)=$res->fetch(PDO::FETCH_NUM)) {
|
|---|
| 500 | $sql="alter table $nometab DEFAULT CHARSET=$cset COLLATE $ccollate";
|
|---|
| 501 | try{
|
|---|
| 502 | $res2 = $dbi->prepare("$sql");
|
|---|
| 503 | $res2->execute();
|
|---|
| 504 | }
|
|---|
| 505 | catch(PDOException $e)
|
|---|
| 506 | {
|
|---|
| 507 | die( $sql . "<br>" . $e->getMessage());
|
|---|
| 508 | }
|
|---|
| 509 | echo "<br><span style=\"color: green;\">- Tabella: $nometab</span>";
|
|---|
| 510 | }
|
|---|
| 511 | if(!$agg) echo "<br><span style=\"color: green;\">- Nessuna tabella da aggiornare</span>";
|
|---|
| 512 | echo "<br><br>";
|
|---|
| 513 | ?>
|
|---|
