source: trunk/admin/aggiornadb.php@ 362

Last change on this file since 362 was 362, checked in by roby, 3 years ago

Ritocchi e sistemazioni varie di completamento delle mofifiche precedenti
File size: 14.9 KB
Line 
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###############
19function 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>".$num.") Fallito: $sql";
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>".$num.") Fallito: $sql2";
43 return $ret;
44 }
45 }
46 echo "<br>".$num.") Aggiornato<br>";
47 return $ret;
48}
49function 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>".$num.") Fallito: $sql -- $e->getmessage()";
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>".$num.") Fallito: $sql2";
78 return $ret;
79 }
80 }
81 echo "<br>".$num.") Index aggiornato<br>";
82 return $ret;
83}
84
85
86
87
88function 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.") Aggiornamento già effettuato<br>"; return 1;}}
96 return 0;
97}
98
99$num=0;
100
101if(controllo($prefix.'_authors','admincomune',++$num))
102{
103 $sql="update `".$prefix."_authors` set admincomune='1' where aid='admin'";
104 $ret=aggiorna($sql,$dbi,'',$num);
105 $sql="update `".$prefix."_authors` set admincomune='0' where aid!='admin'";
106 $ret=aggiorna($sql,$dbi,'',$num);
107 echo "Aggiornata la tabella dei permessi<br>";
108}
109
110if(!controllo($prefix.'_config','versione',++$num))
111{
112 $sql="alter table `".$prefix."_config` change column `Versione` `versione` int(3)";
113 $ret=aggiorna($sql,$dbi,'',$num);
114}
115
116if(!controllo($prefix.'_config','versione',++$num))
117{
118 $sql="alter table `".$prefix."_config` change column `Versione` `versione` int(3)";
119 $ret=aggiorna($sql,$dbi,'',$num);
120}
121
122if(!controllo($prefix.'_config','secret',++$num))
123{
124 $sql="alter table `".$prefix."_config` DROP `secret`";
125 $ret=aggiorna($sql,$dbi,'',$num);
126}
127
128if(!controllo($prefix.'__ele_voti_parziale','data',++$num))
129{
130 $sql="ALTER TABLE `".$prefix."_ele_voti_parziale` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
131 $ret=aggiorna($sql,$dbi,'',$num);
132}
133
134if(!controllo($prefix.'__ele_rilaff','data',++$num))
135{
136 $sql="ALTER TABLE `".$prefix."_ele_rilaff` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
137 $ret=aggiorna($sql,$dbi,'',$num);
138}
139
140if(!controllo($prefix.'_ele_gruppo','num_circ',++$num))
141{
142 $sql="ALTER TABLE `".$prefix."_ele_gruppo` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
143 $ret=aggiorna($sql,$dbi,'',$num);
144# if(!$ret) echo "<br>".$num.") Fallito: $sql"; else echo "<br>".$num.") Aggiornato<br>";
145}
146
147if(!controllo($prefix.'_ele_voti_gruppo','num_gruppo',++$num))
148{
149 $sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
150 $ret=aggiorna($sql,$dbi,'',$num);
151}
152
153if(!controllo($prefix.'_ele_lista','num_gruppo',++$num))
154{
155 $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_gruppo`";
156 $ret=aggiorna($sql,$dbi,'',$num);
157}
158
159if(!controllo($prefix.'_ele_lista','num_circ',++$num))
160{
161 $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
162 $ret=aggiorna($sql,$dbi,'',$num);
163}
164if(!controllo($prefix.'_ele_operatori','id_circ',++$num))
165{
166 $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_circ` INT(11) NOT NULL DEFAULT 0 AFTER `aid`";
167 $ret=aggiorna($sql,$dbi,'',$num);
168}
169if(!controllo($prefix.'_ele_operatori','id_sez',++$num))
170{
171 $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_sez` INT(11) NOT NULL DEFAULT 0 AFTER `id_circ`";
172 $ret=aggiorna($sql,$dbi,'',$num);
173}
174if(!controllo($prefix.'_ele_voti_lista','num_lista',++$num))
175{
176 $sql="ALTER TABLE `".$prefix."_ele_voti_lista` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
177 $ret=aggiorna($sql,$dbi,'',$num);
178}
179
180if(!controllo($prefix.'_ele_voti_ref','num_gruppo',++$num))
181{
182 $sql="ALTER TABLE `".$prefix."_ele_voti_ref` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
183 $ret=aggiorna($sql,$dbi,'',$num);
184}
185
186if(!controllo($prefix.'_ele_voti_candidati','num_cand',++$num))
187{
188 $sql="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD `num_cand` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
189 $ret=aggiorna($sql,$dbi,'',$num);
190}
191
192if(!controllo($prefix.'_ele_candidati','num_lista',++$num))
193{
194 $sql="ALTER TABLE `".$prefix."_ele_candidati` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_lista`";
195 $ret=aggiorna($sql,$dbi,'',$num);
196}
197
198if(!controllo($prefix.'_ele_sezioni','colore',++$num))
199{
200 $sql="ALTER TABLE `".$prefix."_ele_sezioni` ADD `colore` VARCHAR(50) NULL AFTER `solo_lista`";
201 $ret=aggiorna($sql,$dbi,$sql2,$num);
202}
203echo "<br>Aggiornamento per nuovo sistema dei controlli di congruità";
204
205if(!controllo($prefix.'_ele_controlli','id_cons',++$num))
206{
207 $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";
208 echo "<br>".$num.") Creazione tabella dei controlli: ";
209 $ret=aggiorna($sql,$dbi,'',$num);
210}
211
212echo "<br>Eliminazione della vecchia tabella dei controlli: ";
213if(controllo($prefix.'_ele_controllisez','',++$num))
214{
215$sql="DROP TABLE if exists `".$prefix."_ele_controllisez`";
216$ret=aggiorna($sql,$dbi,'',$num);
217} else echo "<br>$num) Tabella non presente<br>";
218
219$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;";
220$ret=aggiorna($sql,$dbi,'',++$num);
221
222$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;";
223$ret=aggiorna($sql,$dbi,'',++$num);
224##############################################
225
226$sql="ALTER TABLE `".$prefix."_ele_gruppo` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
227$ret=aggiorna($sql,$dbi,'',++$num);
228
229$sql="ALTER TABLE `soraldo_authors` CHANGE `adminsuper` `adminsuper` TINYINT(2) NOT NULL DEFAULT '0';";
230$ret=aggiorna($sql,$dbi,'',++$num);
231
232$sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_gruppo` `num_gruppo` INT(2) UNSIGNED NOT NULL DEFAULT '0';";
233$ret=aggiorna($sql,$dbi,'',++$num);
234
235$sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
236$ret=aggiorna($sql,$dbi,'',++$num);
237
238$sql="DROP TABLE `".$prefix."_ele_fasce`";
239$ret=aggiorna($sql,$dbi,'',++$num);
240
241$sql="CREATE TABLE `".$prefix."_ele_fasce` (
242 `id_fascia` int(2) NOT NULL,
243 `abitanti` int(11) NOT NULL,
244 `seggi` int(4) NOT NULL,
245 `id_conf` int(11) DEFAULT 1
246) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
247
248
249$sql2="INSERT INTO `".$prefix."_ele_fasce` (`id_fascia`, `abitanti`, `seggi`, `id_conf`) VALUES
250(1, 3000, 12, 1),
251(2, 10000, 16, 1),
252(3, 15000, 20, 1),
253(4, 30000, 20, 1),
254(5, 100000, 30, 1),
255(6, 250000, 40, 1),
256(7, 500000, 46, 1),
257(8, 1000000, 50, 1),
258(9, 100000000, 60, 1),
259(1, 3000, 12, 2),
260(2, 10000, 16, 2),
261(3, 15000, 20, 2),
262(4, 30000, 20, 2),
263(5, 100000, 30, 2),
264(6, 250000, 40, 2),
265(7, 500000, 46, 2),
266(8, 1000000, 50, 2),
267(9, 100000000, 60, 2),
268(1, 3000, 9, 3),
269(2, 5000, 9, 3),
270(4, 15000, 16, 3),
271(3, 10000, 12, 3),
272(5, 30000, 16, 3),
273(6, 100000, 24, 3),
274(7, 250000, 32, 3),
275(8, 500000, 36, 3),
276(9, 1000000, 40, 3),
277(10, 100000000, 48, 3),
278(1, 3000, 6, 4),
279(2, 5000, 7, 4),
280(3, 10000, 12, 4),
281(4, 15000, 16, 4),
282(5, 30000, 16, 4),
283(6, 100000, 24, 4),
284(7, 250000, 32, 4),
285(8, 500000, 36, 4),
286(9, 1000000, 40, 4),
287(10, 100000000, 48, 4),
288(1, 3000, 9, 5),
289(2, 5000, 9, 5),
290(3, 10000, 12, 5),
291(4, 15000, 16, 5),
292(5, 30000, 16, 5),
293(6, 100000, 24, 5),
294(7, 250000, 32, 5),
295(8, 500000, 36, 5),
296(9, 1000000, 40, 5),
297(10, 100000000, 48, 5),
298(1, 3000, 6, 6),
299(2, 5000, 7, 6),
300(3, 10000, 10, 6),
301(4, 15000, 16, 6),
302(5, 30000, 16, 6),
303(6, 100000, 24, 6),
304(7, 250000, 32, 6),
305(8, 500000, 36, 6),
306(9, 1000000, 40, 6),
307(10, 100000000, 48, 6),
308(1, 3000, 10, 7),
309(2, 10000, 12, 7),
310(3, 15000, 16, 7),
311(4, 30000, 16, 7),
312(5, 100000, 24, 7),
313(6, 250000, 32, 7),
314(7, 500000, 36, 7),
315(8, 1000000, 40, 7),
316(9, 100000000, 48, 7);";
317$ret=aggiorna($sql,$dbi,$sql2,++$num);
318#if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
319
320$sql="ALTER TABLE `".$prefix."_ele_fasce`
321 ADD KEY `id_fascia` (`id_fascia`);";
322$ret=aggiorna($sql,$dbi,'',++$num);
323#if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
324
325
326
327#if(!$ret)
328# echo "<br>".$num++.") Fallito: $sql";
329#elseif ($ret==1)
330# echo "<br>".$num++.") Index eliminato";
331#else
332# echo "<br>".$num++.") Index aggiunto";
333#$sql="ALTER TABLE `".$prefix."_ele_voti_lista` DROP INDEX if exists `id_cons`";
334
335$tab=$prefix."_ele_voti_ref";
336$ind="id_cons";
337# $sql="ALTER TABLE `".$prefix."_ele_voti_ref` DROP INDEX `id_cons`; ";
338$sql2="ALTER TABLE `".$prefix."_ele_voti_ref` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
339$ret=aggiorna_index($tab, $ind,$dbi,$sql2,++$num);
340
341$tab=$prefix."_ele_voti_lista";
342$ind="id_cons";
343$sql2="ALTER TABLE `".$prefix."_ele_voti_lista` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_lista`) USING BTREE";
344$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
345
346$tab=$prefix."_ele_voti_gruppo";
347$ind="id_cons";
348#$sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` DROP INDEX if exists `id_cons`";
349$sql2="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_gruppo`) USING BTREE";
350$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
351
352$tab=$prefix."_ele_voti_candidati";
353$ind="id_cons";
354#$sql="ALTER TABLE `".$prefix."_ele_voti_candidati` DROP INDEX if exists `id_cons`";
355$sql2="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_cand`) USING BTREE";
356$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
357
358$tab=$prefix."_ele_lista";
359$ind="id_cons";
360#$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `id_cons`";
361$sql2="ALTER TABLE `".$prefix."_ele_lista` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
362$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
363
364$tab=$prefix."_ele_lista";
365$ind="PRIMARY";
366#$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `PRIMARY`";
367$sql2="ALTER TABLE `".$prefix."_ele_lista` ADD PRIMARY KEY (`id_lista`) USING BTREE";
368$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
369
370$tab=$prefix."_ele_gruppo";
371$ind="id_cons";
372#$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `id_cons`";
373$sql2="ALTER TABLE `".$prefix."_ele_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_circ`) USING BTREE";
374$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
375
376$tab=$prefix."_ele_gruppo";
377$ind="PRIMARY";
378#$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `PRIMARY`";
379$sql2="ALTER TABLE `".$prefix."_ele_gruppo` ADD PRIMARY KEY (`id_gruppo`) USING BTREE";
380$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
381
382$tab=$prefix."_ele_candidati";
383$ind="id_cons";
384#$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `id_cons`";
385$sql2="ALTER TABLE `".$prefix."_ele_candidati` ADD INDEX `id_cons` (`id_cons`, `id_lista`) USING BTREE";
386$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
387
388$tab=$prefix."_ele_candidati";
389$ind="PRIMARY";
390#$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `PRIMARY`";
391$sql2="ALTER TABLE `".$prefix."_ele_candidati` ADD PRIMARY KEY (`id_cand`) USING BTREE";
392$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
393
394$tab=$prefix."_ele_consultazione";
395$ind="descrizione";
396#$sql="ALTER TABLE `".$prefix."_ele_consultazione` DROP INDEX if exists `descrizione`";
397$sql2="ALTER TABLE `".$prefix."_ele_consultazione` ADD UNIQUE `descrizione` (`descrizione`(100))";
398$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
399
400echo "<br>Modifica Charset del database<br>";
401if($newcs=='utf8') {
402 $cset='utf8';
403 $ccollate='utf8_general_ci';
404 $preset='latin1';
405}else{
406 $cset='latin1';
407 $ccollate='latin1_swedish_ci';
408 $preset='utf8';
409}
410#$sql="ALTER DATABASE $dbname CHARACTER SET '$cset' COLLATE '$ccollate'";
411#$res = $dbi->prepare("$sql");
412#$res->execute();
413
414$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%')";
415$res = $dbi->prepare("$sql");
416$res->execute();
417$tab='';
418
419
420while(list($nometab,$campo,$def,$tipo,$nul)=$res->fetch(PDO::FETCH_NUM)) {
421 if($tab!=$nometab){
422 $sql="alter table $nometab DEFAULT CHARSET=$cset COLLATE $ccollate";
423 try{
424 $res2 = $dbi->prepare("$sql");
425 $res2->execute();
426 }
427 catch(PDOException $e)
428 {
429 die( $sql . "<br>" . $e->getMessage());
430 }
431 $tab=$nometab; echo "Tabella: $nometab<br>";
432 }
433 if($def!='') $default="DEFAULT '$def'"; else $default='';
434 if($nul=='NO') $nullable='NOT NULL'; else $nullable='NULL';
435 $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET '$cset' COLLATE '$ccollate' $nullable $default;";
436 echo "-- $campo<br>";
437 try{
438 $res2 = $dbi->prepare("$sql");
439 $res2->execute();
440 }
441 catch(PDOException $e)
442 {
443 $default="DEFAULT $def";
444 $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET '$cset' COLLATE '$ccollate' $nullable $default;";
445 try{
446 $res2 = $dbi->prepare("$sql");
447 $res2->execute();
448 }
449 catch(PDOException $e)
450 {
451 die( $sql . "<br>" . $e->getMessage());
452 }
453 }
454}
455 $sql="SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = '$dbname' and table_collation like '$preset%'";
456 $res = $dbi->prepare("$sql");
457 $res->execute();
458$tab='';
459while(list($nometab)=$res->fetch(PDO::FETCH_NUM)) {
460 $sql="alter table $nometab DEFAULT CHARSET=$cset COLLATE $ccollate";
461 try{
462 echo "Tabella: $nometab <br>";
463 $res2 = $dbi->prepare("$sql");
464 $res2->execute();
465 }
466 catch(PDOException $e)
467 {
468 die( $sql . "<br>" . $e->getMessage());
469 }
470}
471echo "<br><br>";
472?>
Note: See TracBrowser for help on using the repository browser.