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