source: trunk/admin/aggiornadb.php@ 361

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

Terzo aggiornamento con modifiche per la gestione delle funzioni per cellulare oltre modifiche alla funzione di autorizzazione.
File size: 14.2 KB
Line 
1<?php
2
3#implementare controllo con: SHOW INDEX FROM tua_tabella WHERE tua_tabella.tua_colonna='nome_colonna';
4@require_once("config.php");
5 try{
6 $dbi = new PDO("mysql:host=$dbhost;charset=latin1", $dbuname, $dbpass, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
7 $sql = "use $dbname";
8 $dbi->exec($sql);
9 }
10 catch(PDOException $e)
11 {
12 die( $sql . "<br>" . $e->getMessage());
13 }
14###############
15function aggiorna($sql,$dbi,$sql2,$num){
16 $ret=0;
17 try{
18 $res = $dbi->prepare("$sql");
19 $res->execute();
20 $ret= 1;
21 }
22 catch(PDOException $e)
23 {
24 $ret=0;
25 echo "<br>".$num.") Fallito: $sql";
26 return $ret;
27 }
28
29 if("$sql2"!=""){
30 try{
31 $res = $dbi->prepare("$sql2");
32 $res->execute();
33 $ret=2;
34 }
35 catch(PDOException $e)
36 {
37 $ret=0;
38 echo "<br>".$num.") Fallito: $sql2";
39 return $ret;
40 }
41 }
42 echo "<br>".$num.") Aggiornato<br>";
43 return $ret;
44}
45function aggiorna_index($tab,$ind,$dbi,$sql2,$num){
46 $ret=0;
47 $sqltest="SHOW INDEX FROM `$tab` WHERE KEY_NAME = '$ind'";
48 $res = $dbi->prepare("$sqltest");
49 $res->execute();
50 if($res->rowCount()) {
51 $sql="ALTER TABLE `$tab` DROP INDEX `$ind`";
52 try{
53 $res = $dbi->prepare("$sql");
54 $res->execute();
55 $ret= 1;
56 }
57 catch(PDOException $e)
58 {
59 $ret=0;
60 echo "<br>".$num.") Fallito: $sql -- $e->getmessage()";
61 return $ret;
62 }
63 }
64 if("$sql2"!=""){
65 try{
66 $res = $dbi->prepare("$sql2");
67 $res->execute();
68 $ret=2;
69 }
70 catch(PDOException $e)
71 {
72 $ret=0;
73 echo "<br>".$num.") Fallito: $sql2";
74 return $ret;
75 }
76 }
77 echo "<br>".$num.") Index aggiornato<br>";
78 return $ret;
79}
80
81
82
83
84function controllo($tabella,$campo,$num)
85{
86 global $dbi, $dbname;
87 $sql="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbname' AND TABLE_NAME = '$tabella'";
88 $res = $dbi->prepare("$sql");
89 $res->execute();
90 if($res->rowCount() and $campo=='') return 1;
91 while(list($nome)=$res->fetch(PDO::FETCH_NUM)) {if($nome==$campo) { echo "<br>".$num.") Aggiornamento già effettuato<br>"; return 1;}}
92 return 0;
93}
94
95$num=0;
96
97if(!controllo($prefix.'_config','versione',++$num))
98{
99 $sql="alter table `".$prefix."_config` change column `Versione` `versione` int(3)";
100 $ret=aggiorna($sql,$dbi,'',$num);
101}
102
103if(!controllo($prefix.'__ele_voti_parziale','data',++$num))
104{
105 $sql="ALTER TABLE `".$prefix."_ele_voti_parziale` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
106 $ret=aggiorna($sql,$dbi,'',$num);
107}
108
109if(!controllo($prefix.'__ele_rilaff','data',++$num))
110{
111 $sql="ALTER TABLE `".$prefix."_ele_rilaff` CHANGE `data` `data` DATE NOT NULL DEFAULT '1900-01-01'";
112 $ret=aggiorna($sql,$dbi,'',$num);
113}
114
115if(!controllo($prefix.'_ele_gruppo','num_circ',++$num))
116{
117 $sql="ALTER TABLE `".$prefix."_ele_gruppo` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
118 $ret=aggiorna($sql,$dbi,'',$num);
119# if(!$ret) echo "<br>".$num.") Fallito: $sql"; else echo "<br>".$num.") Aggiornato<br>";
120}
121
122if(!controllo($prefix.'_ele_voti_gruppo','num_gruppo',++$num))
123{
124 $sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
125 $ret=aggiorna($sql,$dbi,'',$num);
126}
127
128if(!controllo($prefix.'_ele_lista','num_gruppo',++$num))
129{
130 $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_gruppo`";
131 $ret=aggiorna($sql,$dbi,'',$num);
132}
133
134if(!controllo($prefix.'_ele_lista','num_circ',++$num))
135{
136 $sql="ALTER TABLE `".$prefix."_ele_lista` ADD `num_circ` INT(2) UNSIGNED NOT NULL AFTER `id_circ`";
137 $ret=aggiorna($sql,$dbi,'',$num);
138}
139if(!controllo($prefix.'_ele_operatori','id_circ',++$num))
140{
141 $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_circ` INT(11) NOT NULL DEFAULT 0 AFTER `aid`";
142 $ret=aggiorna($sql,$dbi,'',$num);
143}
144if(!controllo($prefix.'_ele_operatori','id_sez',++$num))
145{
146 $sql="ALTER TABLE `".$prefix."_ele_operatori` ADD `id_sez` INT(11) NOT NULL DEFAULT 0 AFTER `id_circ`";
147 $ret=aggiorna($sql,$dbi,'',$num);
148}
149if(!controllo($prefix.'_ele_voti_lista','num_lista',++$num))
150{
151 $sql="ALTER TABLE `".$prefix."_ele_voti_lista` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
152 $ret=aggiorna($sql,$dbi,'',$num);
153}
154
155if(!controllo($prefix.'_ele_voti_ref','num_gruppo',++$num))
156{
157 $sql="ALTER TABLE `".$prefix."_ele_voti_ref` ADD `num_gruppo` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
158 $ret=aggiorna($sql,$dbi,'',$num);
159}
160
161if(!controllo($prefix.'_ele_voti_candidati','num_cand',++$num))
162{
163 $sql="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD `num_cand` INT(2) UNSIGNED NOT NULL AFTER `id_sez`";
164 $ret=aggiorna($sql,$dbi,'',$num);
165}
166
167if(!controllo($prefix.'_ele_candidati','num_lista',++$num))
168{
169 $sql="ALTER TABLE `".$prefix."_ele_candidati` ADD `num_lista` INT(2) UNSIGNED NOT NULL AFTER `id_lista`";
170 $ret=aggiorna($sql,$dbi,'',$num);
171}
172
173if(!controllo($prefix.'_ele_sezioni','colore',++$num))
174{
175 $sql="ALTER TABLE `".$prefix."_ele_sezioni` ADD `colore` VARCHAR(50) NULL AFTER `solo_lista`";
176 $ret=aggiorna($sql,$dbi,$sql2,$num);
177}
178echo "<br>Aggiornamento per nuovo sistema dei controlli di congruità";
179
180if(!controllo($prefix.'_ele_controlli','id_cons',++$num))
181{
182 $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";
183 echo "<br>".$num.") Creazione tabella dei controlli: ";
184 $ret=aggiorna($sql,$dbi,'',$num);
185}
186
187echo "<br>Eliminazione della vecchia tabella dei controlli: ";
188if(controllo($prefix.'_ele_controllisez','',++$num))
189{
190$sql="DROP TABLE if exists `".$prefix."_ele_controllisez`";
191$ret=aggiorna($sql,$dbi,'',$num);
192} else echo "<br>$num) Tabella non presente<br>";
193
194$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;";
195$ret=aggiorna($sql,$dbi,'',++$num);
196
197$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;";
198$ret=aggiorna($sql,$dbi,'',++$num);
199##############################################
200
201$sql="ALTER TABLE `".$prefix."_ele_gruppo` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
202$ret=aggiorna($sql,$dbi,'',++$num);
203
204$sql="ALTER TABLE `soraldo_authors` CHANGE `adminsuper` `adminsuper` TINYINT(2) NOT NULL DEFAULT '0';";
205$ret=aggiorna($sql,$dbi,'',++$num);
206
207$sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_gruppo` `num_gruppo` INT(2) UNSIGNED NOT NULL DEFAULT '0';";
208$ret=aggiorna($sql,$dbi,'',++$num);
209
210$sql="ALTER TABLE `".$prefix."_ele_lista` CHANGE `num_circ` `num_circ` INT(2) UNSIGNED NOT NULL DEFAULT '1';";
211$ret=aggiorna($sql,$dbi,'',++$num);
212
213$sql="DROP TABLE `".$prefix."_ele_fasce`";
214$ret=aggiorna($sql,$dbi,'',++$num);
215
216$sql="CREATE TABLE `".$prefix."_ele_fasce` (
217 `id_fascia` int(2) NOT NULL,
218 `abitanti` int(11) NOT NULL,
219 `seggi` int(4) NOT NULL,
220 `id_conf` int(11) DEFAULT 1
221) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
222
223
224$sql2="INSERT INTO `".$prefix."_ele_fasce` (`id_fascia`, `abitanti`, `seggi`, `id_conf`) VALUES
225(1, 3000, 12, 1),
226(2, 10000, 16, 1),
227(3, 15000, 20, 1),
228(4, 30000, 20, 1),
229(5, 100000, 30, 1),
230(6, 250000, 40, 1),
231(7, 500000, 46, 1),
232(8, 1000000, 50, 1),
233(9, 100000000, 60, 1),
234(1, 3000, 12, 2),
235(2, 10000, 16, 2),
236(3, 15000, 20, 2),
237(4, 30000, 20, 2),
238(5, 100000, 30, 2),
239(6, 250000, 40, 2),
240(7, 500000, 46, 2),
241(8, 1000000, 50, 2),
242(9, 100000000, 60, 2),
243(1, 3000, 9, 3),
244(2, 5000, 9, 3),
245(4, 15000, 16, 3),
246(3, 10000, 12, 3),
247(5, 30000, 16, 3),
248(6, 100000, 24, 3),
249(7, 250000, 32, 3),
250(8, 500000, 36, 3),
251(9, 1000000, 40, 3),
252(10, 100000000, 48, 3),
253(1, 3000, 6, 4),
254(2, 5000, 7, 4),
255(3, 10000, 12, 4),
256(4, 15000, 16, 4),
257(5, 30000, 16, 4),
258(6, 100000, 24, 4),
259(7, 250000, 32, 4),
260(8, 500000, 36, 4),
261(9, 1000000, 40, 4),
262(10, 100000000, 48, 4),
263(1, 3000, 9, 5),
264(2, 5000, 9, 5),
265(3, 10000, 12, 5),
266(4, 15000, 16, 5),
267(5, 30000, 16, 5),
268(6, 100000, 24, 5),
269(7, 250000, 32, 5),
270(8, 500000, 36, 5),
271(9, 1000000, 40, 5),
272(10, 100000000, 48, 5),
273(1, 3000, 6, 6),
274(2, 5000, 7, 6),
275(3, 10000, 10, 6),
276(4, 15000, 16, 6),
277(5, 30000, 16, 6),
278(6, 100000, 24, 6),
279(7, 250000, 32, 6),
280(8, 500000, 36, 6),
281(9, 1000000, 40, 6),
282(10, 100000000, 48, 6),
283(1, 3000, 10, 7),
284(2, 10000, 12, 7),
285(3, 15000, 16, 7),
286(4, 30000, 16, 7),
287(5, 100000, 24, 7),
288(6, 250000, 32, 7),
289(7, 500000, 36, 7),
290(8, 1000000, 40, 7),
291(9, 100000000, 48, 7);";
292$ret=aggiorna($sql,$dbi,$sql2,++$num);
293#if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
294
295$sql="ALTER TABLE `".$prefix."_ele_fasce`
296 ADD KEY `id_fascia` (`id_fascia`);";
297$ret=aggiorna($sql,$dbi,'',++$num);
298#if(!$ret) echo "<br>".$num++.") Fallito: $sql"; else echo "<br>".$num++.") Aggiornato<br>";
299
300
301
302#if(!$ret)
303# echo "<br>".$num++.") Fallito: $sql";
304#elseif ($ret==1)
305# echo "<br>".$num++.") Index eliminato";
306#else
307# echo "<br>".$num++.") Index aggiunto";
308#$sql="ALTER TABLE `".$prefix."_ele_voti_lista` DROP INDEX if exists `id_cons`";
309
310$tab=$prefix."_ele_voti_ref";
311$ind="id_cons";
312# $sql="ALTER TABLE `".$prefix."_ele_voti_ref` DROP INDEX `id_cons`; ";
313$sql2="ALTER TABLE `".$prefix."_ele_voti_ref` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
314$ret=aggiorna_index($tab, $ind,$dbi,$sql2,++$num);
315
316$tab=$prefix."_ele_voti_lista";
317$ind="id_cons";
318$sql2="ALTER TABLE `".$prefix."_ele_voti_lista` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_lista`) USING BTREE";
319$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
320
321$tab=$prefix."_ele_voti_gruppo";
322$ind="id_cons";
323#$sql="ALTER TABLE `".$prefix."_ele_voti_gruppo` DROP INDEX if exists `id_cons`";
324$sql2="ALTER TABLE `".$prefix."_ele_voti_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_gruppo`) USING BTREE";
325$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
326
327$tab=$prefix."_ele_voti_candidati";
328$ind="id_cons";
329#$sql="ALTER TABLE `".$prefix."_ele_voti_candidati` DROP INDEX if exists `id_cons`";
330$sql2="ALTER TABLE `".$prefix."_ele_voti_candidati` ADD INDEX `id_cons` (`id_cons`, `id_sez`, `id_cand`) USING BTREE";
331$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
332
333$tab=$prefix."_ele_lista";
334$ind="id_cons";
335#$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `id_cons`";
336$sql2="ALTER TABLE `".$prefix."_ele_lista` ADD INDEX `id_cons` (`id_cons`, `id_gruppo`) USING BTREE";
337$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
338
339$tab=$prefix."_ele_lista";
340$ind="PRIMARY";
341#$sql="ALTER TABLE `".$prefix."_ele_lista` DROP INDEX if exists `PRIMARY`";
342$sql2="ALTER TABLE `".$prefix."_ele_lista` ADD PRIMARY KEY (`id_lista`) USING BTREE";
343$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
344
345$tab=$prefix."_ele_gruppo";
346$ind="id_cons";
347#$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `id_cons`";
348$sql2="ALTER TABLE `".$prefix."_ele_gruppo` ADD INDEX `id_cons` (`id_cons`, `id_circ`) USING BTREE";
349$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
350
351$tab=$prefix."_ele_gruppo";
352$ind="PRIMARY";
353#$sql="ALTER TABLE `".$prefix."_ele_gruppo` DROP INDEX if exists `PRIMARY`";
354$sql2="ALTER TABLE `".$prefix."_ele_gruppo` ADD PRIMARY KEY (`id_gruppo`) USING BTREE";
355$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
356
357$tab=$prefix."_ele_candidati";
358$ind="id_cons";
359#$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `id_cons`";
360$sql2="ALTER TABLE `".$prefix."_ele_candidati` ADD INDEX `id_cons` (`id_cons`, `id_lista`) USING BTREE";
361$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
362
363$tab=$prefix."_ele_candidati";
364$ind="PRIMARY";
365#$sql="ALTER TABLE `".$prefix."_ele_candidati` DROP INDEX if exists `PRIMARY`";
366$sql2="ALTER TABLE `".$prefix."_ele_candidati` ADD PRIMARY KEY (`id_cand`) USING BTREE";
367$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
368
369$tab=$prefix."_ele_consultazione";
370$ind="descrizione";
371#$sql="ALTER TABLE `".$prefix."_ele_consultazione` DROP INDEX if exists `descrizione`";
372$sql2="ALTER TABLE `".$prefix."_ele_consultazione` ADD UNIQUE `descrizione` (`descrizione`(100))";
373$ret=aggiorna_index($tab,$ind,$dbi,$sql2,++$num);
374
375echo "<br>Modifica Charset del database<br>";
376#$sql="ALTER DATABASE $dbname CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'";
377#$res = $dbi->prepare("$sql");
378#$res->execute();
379# alter table $table DEFAULT CHARSET=utf8 COLLATE utf8_general_ci
380$sql="SELECT table_name,column_name,column_default,column_type,is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbname' and (character_set_name='latin1' or collation_name like 'latin%')";
381$res = $dbi->prepare("$sql");
382$res->execute();
383$tab='';
384while(list($nometab,$campo,$def,$tipo,$nul)=$res->fetch(PDO::FETCH_NUM)) {
385 if($tab!=$nometab){
386 $sql="alter table $nometab DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";
387 try{
388 $res2 = $dbi->prepare("$sql");
389 $res2->execute();
390 }
391 catch(PDOException $e)
392 {
393 die( $sql . "<br>" . $e->getMessage());
394 }
395 $tab=$nometab; echo "Tabella: $nometab<br>";
396 }
397 if($def!='') $default="DEFAULT '$def'"; else $default='';
398 if($nul=='NO') $nullable='NOT NULL'; else $nullable='NULL';
399 $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' $nullable $default;";
400 echo "-- $campo<br>";
401 try{
402 $res2 = $dbi->prepare("$sql");
403 $res2->execute();
404 }
405 catch(PDOException $e)
406 {
407 $default="DEFAULT $def";
408 $sql="ALTER TABLE $nometab CHANGE $campo $campo $tipo CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' $nullable $default;";
409 try{
410 $res2 = $dbi->prepare("$sql");
411 $res2->execute();
412 }
413 catch(PDOException $e)
414 {
415 die( $sql . "<br>" . $e->getMessage());
416 }
417 }
418}
419 $sql="SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = '$dbname' and table_collation like 'latin%'";
420 $res = $dbi->prepare("$sql");
421 $res->execute();
422$tab='';
423while(list($nometab)=$res->fetch(PDO::FETCH_NUM)) {
424 $sql="alter table $nometab DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";
425 try{
426 echo "Tabella: $nometab <br>";
427 $res2 = $dbi->prepare("$sql");
428 $res2->execute();
429 }
430 catch(PDOException $e)
431 {
432 die( $sql . "<br>" . $e->getMessage());
433 }
434}
435echo "<br><br>";
436?>
Note: See TracBrowser for help on using the repository browser.