|
1
|
|
|
<?php |
|
2
|
|
|
/* |
|
3
|
|
|
* ***************************************************************************** |
|
4
|
|
|
* Contributions to this work were made on behalf of the GÉANT project, a |
|
5
|
|
|
* project that has received funding from the European Union’s Framework |
|
6
|
|
|
* Programme 7 under Grant Agreements No. 238875 (GN3) and No. 605243 (GN3plus), |
|
7
|
|
|
* Horizon 2020 research and innovation programme under Grant Agreements No. |
|
8
|
|
|
* 691567 (GN4-1) and No. 731122 (GN4-2). |
|
9
|
|
|
* On behalf of the aforementioned projects, GEANT Association is the sole owner |
|
10
|
|
|
* of the copyright in all material which was developed by a member of the GÉANT |
|
11
|
|
|
* project. GÉANT Vereniging (Association) is registered with the Chamber of |
|
12
|
|
|
* Commerce in Amsterdam with registration number 40535155 and operates in the |
|
13
|
|
|
* UK as a branch of GÉANT Vereniging. |
|
14
|
|
|
* |
|
15
|
|
|
* Registered office: Hoekenrode 3, 1102BR Amsterdam, The Netherlands. |
|
16
|
|
|
* UK branch address: City House, 126-130 Hills Road, Cambridge CB2 1PQ, UK |
|
17
|
|
|
* |
|
18
|
|
|
* License: see the web/copyright.inc.php file in the file structure or |
|
19
|
|
|
* <base_url>/copyright.php after deploying the software |
|
20
|
|
|
*/ |
|
21
|
|
|
|
|
22
|
|
|
/** |
|
23
|
|
|
* This script will download all data from the views in eduroam database and |
|
24
|
|
|
* update the local copy |
|
25
|
|
|
*/ |
|
26
|
|
|
namespace utils; |
|
27
|
|
|
require_once dirname(dirname(__FILE__)) . "/config/_config.php"; |
|
28
|
|
|
|
|
29
|
|
|
setlocale(LC_CTYPE, "en_US.UTF-8"); |
|
30
|
|
|
|
|
31
|
|
|
class UpdateFromMonitor { |
|
32
|
|
|
public $fields = [ |
|
33
|
|
|
'eduroam' => [ |
|
34
|
|
|
'admin' => [ |
|
35
|
|
|
['id', 'i'], |
|
36
|
|
|
['eptid', 's'], |
|
37
|
|
|
['email', 's'], |
|
38
|
|
|
['common_name', 's'], |
|
39
|
|
|
['id_role', 'i'], |
|
40
|
|
|
['role', 's'], |
|
41
|
|
|
['realm', 's'] |
|
42
|
|
|
], |
|
43
|
|
|
], |
|
44
|
|
|
'eduroamv2' => [ |
|
45
|
|
|
'tls_ro' => [ |
|
46
|
|
|
['ROid', 's'], |
|
47
|
|
|
['country', 's'], |
|
48
|
|
|
['stage', 'i'], |
|
49
|
|
|
['servers', 's'], |
|
50
|
|
|
['contacts', 's'], |
|
51
|
|
|
['ts', 's'] |
|
52
|
|
|
], |
|
53
|
|
|
'tls_inst' => [ |
|
54
|
|
|
['ROid', 's'], |
|
55
|
|
|
['country', 's'], |
|
56
|
|
|
['instid', 's'], |
|
57
|
|
|
['stage', 'i'], |
|
58
|
|
|
['type', 'i'], |
|
59
|
|
|
['inst_name', 's'], |
|
60
|
|
|
['servers', 's'], |
|
61
|
|
|
['contacts', 's'], |
|
62
|
|
|
['ts', 's'] |
|
63
|
|
|
], |
|
64
|
|
|
'active_institution' => [ |
|
65
|
|
|
['instid', 's'], |
|
66
|
|
|
['ROid', 's'], |
|
67
|
|
|
['inst_realm', 's'], |
|
68
|
|
|
['country', 's'], |
|
69
|
|
|
['name', 's'], |
|
70
|
|
|
['contact', 's'], |
|
71
|
|
|
['type', 'i'], |
|
72
|
|
|
['last_change', 's'] |
|
73
|
|
|
], |
|
74
|
|
|
] |
|
75
|
|
|
]; |
|
76
|
|
|
|
|
77
|
|
|
public function __construct() { |
|
78
|
|
|
$DB_EXT = \config\Master::DB['EXTERNAL_SOURCE']; |
|
79
|
|
|
$DB_LOCAL = \config\Master::DB['EXTERNAL']; |
|
80
|
|
|
$this->db_ext = new \mysqli($DB_EXT['host'], $DB_EXT['user'], $DB_EXT['pass']); |
|
|
|
|
|
|
81
|
|
|
$this->db_local = new \mysqli($DB_LOCAL['host'], $DB_LOCAL['user'], $DB_LOCAL['pass'], $DB_LOCAL['db']); |
|
|
|
|
|
|
82
|
|
|
} |
|
83
|
|
|
|
|
84
|
|
|
/** |
|
85
|
|
|
* Creates a temporary table with data collected from the source |
|
86
|
|
|
* |
|
87
|
|
|
* @param type $db |
|
|
|
|
|
|
88
|
|
|
* @param type $table_name |
|
89
|
|
|
*/ |
|
90
|
|
|
|
|
91
|
|
|
public function updateTable($db, $table_name) { |
|
92
|
|
|
$timeStart = microtime(true); |
|
93
|
|
|
print "Updating from $db.$table_name\n"; |
|
94
|
|
|
$table = 'view_'.$table_name; |
|
95
|
|
|
$tmpTable = 'tmp_'.$table_name; |
|
96
|
|
|
$this->db_local->query("CREATE TEMPORARY TABLE $tmpTable SELECT * FROM $table LIMIT 0"); |
|
97
|
|
|
$this->db_ext->select_db($db); |
|
98
|
|
|
$this->db_ext->query("SET NAMES 'utf8'"); |
|
99
|
|
|
$this->db_local->query("SET NAMES 'utf8mb4'"); |
|
100
|
|
|
$result = $this->db_ext->query("SELECT * FROM $table"); |
|
101
|
|
|
$queryFields = implode(',', array_column($this->fields[$db][$table_name],0)); |
|
102
|
|
|
while ($row = $result->fetch_assoc()) { |
|
103
|
|
|
$v = []; |
|
104
|
|
|
foreach ($this->fields[$db][$table_name] as $field) { |
|
105
|
|
|
if ($field[1] === 's') { |
|
106
|
|
|
if (isset($row[$field[0]])) { |
|
107
|
|
|
$v[] = $this->escape($row[$field[0]]); |
|
108
|
|
|
} else { |
|
109
|
|
|
$v[] = "NULL"; |
|
110
|
|
|
} |
|
111
|
|
|
} else { |
|
112
|
|
|
if (isset($row[$field[0]])) { |
|
113
|
|
|
$v[] = $row[$field[0]]; |
|
114
|
|
|
} else { |
|
115
|
|
|
$v[] = "NULL"; |
|
116
|
|
|
} |
|
117
|
|
|
} |
|
118
|
|
|
} |
|
119
|
|
|
$queryValues = implode(',',$v); |
|
120
|
|
|
$query = "INSERT INTO $tmpTable (".$queryFields.") VALUES (".$queryValues.")"; |
|
121
|
|
|
$this->db_local->query($query); |
|
122
|
|
|
} |
|
123
|
|
|
$timeEnd = microtime(true); |
|
124
|
|
|
$timeElapsed = $timeEnd - $timeStart; |
|
125
|
|
|
printf("Done updating temporary table $table_name in %.2fs\n", $timeElapsed); |
|
126
|
|
|
} |
|
127
|
|
|
|
|
128
|
|
|
public function updateInstAdminTable() { |
|
129
|
|
|
$q = "SELECT convert(contact using utf8mb4), inst_realm, instid, ROid FROM view_active_institution"; |
|
130
|
|
|
$this->db_local->query("CREATE TEMPORARY TABLE tmp_institution_admins SELECT * FROM view_institution_admins LIMIT 0"); |
|
131
|
|
|
$result = $this->db_local->query($q); |
|
132
|
|
|
while ($row = $result->fetch_row()) { |
|
133
|
|
|
$contacts = \core\ExternalEduroamDBData::dissectCollapsedContacts($row[0]); |
|
134
|
|
|
$realms = explode(',', $row[1]); |
|
135
|
|
|
foreach ($contacts as $contact) { |
|
136
|
|
|
foreach ($realms as $realm) { |
|
137
|
|
|
$email = empty($contact['mail']) ? 'NULL' :'"'.$contact['mail'].'"'; |
|
138
|
|
|
$name = empty($contact['name']) ? 'NULL' :'"'.$contact['name'].'"'; |
|
139
|
|
|
$phone = empty($contact['phone']) ? 'NULL' :'"'.$contact['phone'].'"'; |
|
140
|
|
|
$id = '"'.$row[2].'"'; |
|
141
|
|
|
$ROid = '"'.$row[3].'"'; |
|
142
|
|
|
$query = "INSERT INTO tmp_institution_admins (name, email, phone, inst_realm, instid, ROid)" |
|
143
|
|
|
. ' VALUES ('.$name.','.$email.','.$phone.',"'.$realm.'",'.$id.','.$ROid.')'; |
|
144
|
|
|
$this->db_local->query($query); |
|
145
|
|
|
} |
|
146
|
|
|
} |
|
147
|
|
|
} |
|
148
|
|
|
} |
|
149
|
|
|
|
|
150
|
|
|
public function fillTable($table_name) { |
|
151
|
|
|
$timeStart = microtime(true); |
|
152
|
|
|
print "Filling table $table_name\n"; |
|
153
|
|
|
$table = 'view_'.$table_name; |
|
154
|
|
|
$tmpTable = 'tmp_'.$table_name; |
|
155
|
|
|
$this->db_local->query("SET NAMES 'utf8mb4'"); |
|
156
|
|
|
$this->db_local->query("DELETE FROM $table"); |
|
157
|
|
|
$this->db_local->query("INSERT INTO $table SELECT * from $tmpTable"); |
|
158
|
|
|
$timeEnd = microtime(true); |
|
159
|
|
|
$timeElapsed = $timeEnd - $timeStart; |
|
160
|
|
|
printf("Done filling table $table_name in %.2fs\n", $timeElapsed); |
|
161
|
|
|
} |
|
162
|
|
|
|
|
163
|
|
|
private function escape($inp) { |
|
164
|
|
|
$out=str_replace('\\','\\\\',$inp); |
|
165
|
|
|
$out=str_replace('"','\"',$out); |
|
166
|
|
|
$out=str_replace('?','\?',$out); |
|
167
|
|
|
$out = 'convert(cast(convert("'.$out.'" using latin1) as binary) using utf8)'; |
|
168
|
|
|
return($out); |
|
169
|
|
|
} |
|
170
|
|
|
} |
|
171
|
|
|
|
|
172
|
|
|
|
GEANT /
CAT
Loading history...