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