1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
|
<input type="submit" name="update" value="go update">
</p>
</form>
<?
if ($update=="go update"){
ini_set("max_execution_time",$maxExecTime);
$sql = new cSQL;
$sql->connect($sql_type, $sql_host, $sql_user, $sql_pass, $sql_db);
echo "<b><font color='blue'>User ".$sql_user.": working on ".$sql->sql_type.", using database ".$sql_db." on ".$sql_host."</font></b><br><br>";
/*** for each unit (directory) ***/
foreach ($unit as $unit_tmp){
$i=0;
/*** for each unit dbase (dbf file in directory) ***/
foreach ($db_file[$unit_tmp] as $db_file_tmp) {
$sql_id="";
$sql_unit="";
$i++;
$fields = array();
$unit_orig=$unit_tmp."_".$db_file_name[$unit_tmp][$i];
if ($$unit_orig == "yes"){
echo "<font color='blue'>".ucfirst(strtolower($db_file_tmp))."</font>: ";
$file = $unit_tmp."/".$db_file_tmp;
/*** check for memo file & get size of blocks ***/
$memo_file="";
if (file_exists(substr($file, 0, -1)."t")) $memo_file=substr($file, 0, -1)."t";
if (file_exists(substr($file, 0, -1)."T")) $memo_file=substr($file, 0, -1)."T";
if (file_exists(substr($file, 0, -3)."fpt")) $memo_file=substr($file, 0, -3)."fpt";
if (file_exists(substr($file, 0, -3)."FPT")) $memo_file=substr($file, 0, -3)."FPT";
if ($memo_file!=""){
$memo_fp = fopen ($memo_file, 'rb')
or die ("File <i>$memo_file</i> cannot be opened.");
$memo_data = fread ($memo_fp, filesize($memo_file))
or die ("Could not read data from file <i>$memo_file</i>");
$memo_header_format = 'SNext aviable/'.'SSize of blocks/'.'A8Dbf name';
$memo_header = unpack ($memo_header_format, $memo_data);
if ($memo_header["Size of blocks"]==0) $memo_header["Size of blocks"]=512;
}
/*** get field names, types & lenght fom dbf files ***/
$fp = fopen ($file, 'rb')
or die ("File <i>$file</i> cannot be opened.");
$data = fread ($fp, 32)
or die ("Could not read data from file <i>$file</i>");
$header_format = 'H2id/' . 'CYear/' . 'CMonth/' . 'CDay/' . 'L# of Records/' . 'SHeader Size/' . 'SRecord Size';
$header = unpack ($header_format, $data);
$data = fread ($fp, $header['Header Size'] - 34)
or die ("Could not read data from file <i>$file</i>");
$record_format = 'A11Field Name/' . 'AField Type/' . 'x4/' . 'CField Length/' . 'CField Precision';
$x=0;
for ($offset = 0; $offset < strlen ($data); $offset += 32) {
$x++;
$fields[$x] = unpack("@$offset/$record_format", $data);
foreach ($fields[$x] as $key => $value) {$fields[$x][$key] = DropAfterNULL(trim($value));}
}
/*** check if mysql table wit this name exist ***/
$ii=0;
$y=0;
$mysql_struct=array();
$mysql_table=$sql->show_tables();
while ($sql->result()){
$y++;
$tb_names[$y]=$sql->myrow['0'];
}
/*** get field names & types from mysql table ***/
if (is_array($tb_names) && in_array($sql_table_name[$unit_tmp][$i], $tb_names)){
$sql->describe($sql_table_name[$unit_tmp][$i]);
if ($sql->num_rows>=1){
//while ($myrow = mysql_fetch_array($mysql_table)){
while ($sql->result()){
$ii++;
$mysql_struct[$ii]["Field"]=$sql->myrow['0'];
$mysql_struct[$ii]["Type"]=$sql->myrow['1'];
}
}
}
/*** create query for table create ***/
if ($needID[$sql_table_name[$unit_tmp][$i]]==1 && $sql->sql_type=="MySql") $sql_id="`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ";
if ($needID[$sql_table_name[$unit_tmp][$i]]==1 && $sql->sql_type=="PostgreSQL") $sql_id="\"id\" SERIAL, PRIMARY KEY (\"id\"), ";
if ($needUnitName[$sql_table_name[$unit_tmp][$i]]==1) $sql_unit="`" . $fieldUnitName . "` VARCHAR(15) NOT NULL, ";
$quer="CREATE TABLE `" . $sql_table_name[$unit_tmp][$i] . "` (" . $sql_id . $sql_unit;
$exist=0;
$y=0;
foreach ($fields as $field){
$field['Field Name']=ucfirst(strtolower($field['Field Name']));
if ($field['Field Type']=="M" && $memo_file==""){
echo "<br><b>warning:</b> memo field exist, but memo file <b>".strtolower(substr($file, 0, -1)."t")."</b> not fund. <b><font color=red> memo field ignored</font></b><br>";
continue 1;
}elseif ($field['Field Type']=="M" && $memo_file!=""){
echo "including <b>dbt</b> memo file, ";
}
if (($field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file==""){
echo "<br><b>warning:</b> memo field exist, but memo file <b>".strtolower(substr($file, 0, -3)."fpt")."</b> not fund. <b><font color=red> memo field ignored</font></b><br>";
continue 1;
}elseif (($field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file!=""){
echo "including <b>fpt</b> memo file, ";
}
if ($field['Field Type']=="B" && $memo_file!=""){
echo "including <b>dbt</b> memo file, ";
}
$quer .= "`" . $field['Field Name'] . "` " . GetFType($sql_type,$field['Field Type'],$field['Field Length'], $field['Field Precision']) . " NOT NULL" . ", ";
$y++;
if (is_array($mysql_struct)){
foreach ($mysql_struct as $ii){
if ($field['Field Name']==$ii["Field"] && GetFType($sql_type,$field['Field Type'],$field['Field Length'], $field['Field Precision'])==$ii["Type"]){$exist++;}
}
}
}
$quer=substr($quer, 0, -2) . ") TYPE=MyISAM";
/*** create mysql table if not exist ***/
$new_table="";
if ($exist==0){
if ($sql->sql_type == "PostgreSQL"){
$quer=str_replace("`", "\"", $quer);
$quer=str_replace("TYPE=MyISAM", "", $quer);
}
$sql->query($quer);
$exist=$y;
if ($needUnitName[$sql_table_name[$unit_tmp][$i]]==1) $mysql_struct[0]["Field"]=$fieldUnitName;
echo "creating table <b>".$sql_table_name[$unit_tmp][$i]."</b>, ";
$new_table="yes";
}else{
echo "table <b>".$sql_table_name[$unit_tmp][$i]."</b> exist, ";
}
/*** mysql table exist, but it's not the same table, skip ***/
if ($exist!=$y){
echo "but not have the same Field names or Field types! <font color='red'><b>skipped</b></font>";
}else{
/*** delete old data from mysql table (update mode 1) or get last record number (update mode 2) ***/
$update_from=1;
if ($updateMode[$sql_table_name[$unit_tmp][$i]]==2){
$que_del="";
if (is_array($mysql_struct)){
foreach ($mysql_struct as $ii){
if ($ii["Field"]==$fieldUnitName){
$que_del="SELECT * FROM " . $sql_table_name[$unit_tmp][$i] . " where " . $fieldUnitName . " = '" . strtoupper($unit_tmp) . "'";
}
}
if ($que_del=="") $que_del="SELECT * FROM " . $sql_table_name[$unit_tmp][$i];
$sql->query($que_del);
$update_from=$sql->num_rows+1;
if ($update_from<=0) $update_from=1;
}
}else{
$update_from=1;
$que_del="";
if (is_array($mysql_struct)){
foreach ($mysql_struct as $ii){
if ($ii["Field"]==$fieldUnitName){
$que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] . "` where `" . $fieldUnitName . "` = '" . strtoupper($unit_tmp) . "'";
}
}
if ($que_del=="") $que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] ."`";
if ($sql->sql_type == "PostgreSQL"){
$que_del=str_replace("`", "\"", $que_del);
}
$sql->query($que_del);
}
}
/*** rad data from dbf file & insert in mysql table ***/
fread ($fp, 3);
if ($header["# of Records"]!=0){
$data = fread ($fp, ($header["# of Records"]*$header["Record Size"])-1);
}else{
$data="";
}
$row_format="";
foreach ($fields as $field){
$row_format .= 'A' . $field["Field Length"] . $field["Field Name"] . "/";
}
$row_format = substr($row_format, 0, -1);
?><script>
window.parent.graf.f_enota.enota.value=<?echo "\"making ".$sql_table_name[$unit_tmp][$i].":\""?>;
</script><?
$on_ever = floor(($header["# of Records"]-$update_from+1)/100);
$tempo=1;
if ($on_ever<1){
$tempo=($header["# of Records"]-$update_from+1)/100;
if ($tempo!=0){
$tempo=1/$tempo;
}else{
$tempo=1;
}
$on_ever=1;
}
$perc=0;
$where_we_are=$on_ever;
for ($rec_no=$update_from; $rec_no<=$header["# of Records"]; $rec_no++){
if ($rec_no>=$where_we_are){
$where_we_are+=$on_ever;
$perc+=$tempo;if ($perc>=100) $perc=100;?>
<script>
window.parent.graf.procent.perc.value=<?echo $perc?>;
</script><?
flush();
}
$offset = $header["Record Size"]*$rec_no;
$record = (@unpack ("@$offset/$row_format", $data));
$sql_where="INSERT INTO `".$sql_table_name[$unit_tmp][$i]."` (";
$sql_data=" VALUES (";
if (is_array($mysql_struct)){
foreach ($mysql_struct as $ii){
if ($ii["Field"]==$fieldUnitName){
$sql_where.="`".$fieldUnitName."`, ";
$sql_data.="\"" . strtoupper($unit_tmp) . "\"" . ", ";
}
}
}
foreach ($fields as $field){
if (($field['Field Type']=="M" || $field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file==""){
continue 1;
}
if (($field['Field Type']=="M" || $field['Field Type']=="G") && $memo_file!="" && trim($record[$field['Field Name']])!=""){
$record[$field['Field Name']]=trim(GetMemo($memo_data, $record[$field['Field Name']],$memo_header["Size of blocks"],filesize($memo_file)));
}
if (($field['Field Type']=="B" || $field['Field Type']=="P") && $memo_file!="" && trim($record[$field['Field Name']])!=""){
$record[$field['Field Name']]=GetMemo($memo_data, $record[$field['Field Name']],$memo_header["Size of blocks"],filesize($memo_file));
}
$sql_where.="`".ucfirst(strtolower($field['Field Name']))."`, ";
/*** do not transform binary files ***/
if ($field['Field Type']=="B" || $field['Field Type']=="P"){
$sql_data.="\"".addslashes($record[$field['Field Name']])."\"".", ";
}else{
/*** pg dont like empty decimal fields ***/
if ($field['Field Type']=="N" && $field['Field Precision']>=1 && trim($record[$field['Field Name']])=="") {$record[$field['Field Name']]=0;}
/*** and empty date fields and dont like 00000000 too :( ***/
if ($field['Field Type']=="D" && trim($record[$field['Field Name']])=="") {$record[$field['Field Name']]="19700101";}
$sql_data.="\"".addslashes(strtr($record[$field['Field Name']], $transform))."\"".", ";
}
}
$sql_where=substr($sql_where, 0, -2).")";
$sql_data=substr($sql_data, 0, -2).")";
$quer=$sql_where.$sql_data;
if ($sql->sql_type == "PostgreSQL"){
$quer=str_replace("\"", "'", $quer);
$quer=str_replace("`", "\"", $quer);
}
$sql->query($quer);
}
if ($updateMode[$sql_table_name[$unit_tmp][$i]]==2){
$newer_records=$header["# of Records"]-$update_from+1;
if ($newer_records >= 1){
echo "successfully inserting <b>". $newer_records ."</b> newer records from <b>" . strtolower($file) . "</b>";
}else{
if ($new_table!="yes"){
echo "no newer records found in <b>" . strtolower($file) . "</b>";
}else{
echo "no records found in <b>" . strtolower($file) . "</b>";
}
}
}else{
if ($header["# of Records"]>=1){
echo "successfully ";
if ($new_table!="yes") echo "delete old records and ";
echo "inserting <b>". $header["# of Records"] . "</b> new records from <b>" . strtolower($file) . "</b>";
}else{
echo "no records found in <b>" . strtolower($file) . "</b>";
}
}
fclose ($fp);
if ($memo_file!="") fclose ($memo_fp);
}
/*** delete some rows whitch we dont need ***/
if (is_array($data_delete)){
if ($data_delete[$sql_table_name[$unit_tmp][$i]]){
$que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] . "` WHERE " . $data_delete[$sql_table_name[$unit_tmp][$i]];
if ($sql->sql_type == "PostgreSQL"){
$que_del=str_replace("`", "\"", $que_del);
}
echo "<br>deleting data from <b>".$sql_table_name[$unit_tmp][$i]."</b> where <b>".$data_delete[$sql_table_name[$unit_tmp][$i]]."</b>";
if ($sql->query($que_del) === FALSE){
//printf("<BR>mysql error: %s\n", mysql_error());
echo "<br><b>check \$data_delete[\"".$sql_table_name[$unit_tmp][$i]."\"]=<font color='red'>\"".$data_delete[$sql_table_name[$unit_tmp][$i]]."\"</font>; in config.php</b><br>";
}
}
}
echo ", optimizing table <b>".$sql_table_name[$unit_tmp][$i].",";
$sql->optimize($sql_table_name[$unit_tmp][$i]);
echo "<font color='blue'> done</font></b><br>";
}
}
}
?><script>
window.parent.graf.f_enota.enota.value=<?echo "\"done\""?>;
</script><?
echo "<br><b><font color='blue'>Done in all</font></b><br>";
}
?>
</body>
</html> |
[SQL] Faire un DROP de toutes mes tables
Répondre avec citation
Partager