|
1:
<?php
2: ####################################################################
3: # #
4: # PHP dbfConverter V0.9 #
5: # import data from dbase format to mysql or postgres #
6: # by kroket Jun.2002 kroket@bo-bo.si #
7: # http://server.bo-bo.si/php/dbfConverter/ #
8: # #
9: ####################################################################
10: require("config.php");
11: require_once("sql.class.php");
12: function GetFType($sql_type,$type, $length, $precision){
13: if ($sql_type=='MySQL'){
14: if ($type=="N" && $precision==0) Return "int(".$length.")";
15: if ($type=="N" && $precision>=1) Return "decimal(".$length.",".$precision.")";
16: if ($type=="C" && $length>=4) Return "varchar(".$length.")";
17: if ($type=="C" && $length<=3) Return "char(".$length.")";
18: if ($type=="D") Return "date";
19: if ($type=="L") Return "char(".$length.")";
20: if ($type=="F" && $precision==0) Return "float(".$length.")";
21: if ($type=="F" && $precision>=1) Return "float(".$length.",".$precision.")";
22: if ($type=="N/A" && $length>=4) Return "varchar(".$length.")";
23: if ($type=="N/A" && $length<=3) Return "char(".$length.")";
24: if ($type=="M" || $type=="G") Return "longtext";
25: if ($type=="P" || $type=="B") Return "longblob";
26: if ($type=="T") Return "datetime";
27: if ($type=="I") Return "int(".$length.")";
28: if ($type=="Y") Return "decimal(".$length.",".$precision.")";
29: }
30: if ($sql_type=='PostgreSQL'){
31: if ($type=="N" && $precision==0) Return "integer";//(".$length.")";
32: if ($type=="N" && $precision>=1) Return "numeric(".$length.",".$precision.")";
33: if ($type=="C" && $length>=4) Return "character varying(".$length.")";
34: if ($type=="C" && $length<=3) Return "character(".$length.")";
35: if ($type=="D") Return "date";
36: if ($type=="L") Return "boolean";
37: if ($type=="F" && $precision==0) Return "real";
38: if ($type=="F" && $precision>=1) Return "double precision";
39: if ($type=="N/A" && $length>=4) Return "character varying(".$length.")";
40: if ($type=="N/A" && $length<=3) Return "character(".$length.")";
41: if ($type=="M" || $type=="G") Return "text";
42: if ($type=="P" || $type=="B") Return "bytea";
43: if ($type=="T") Return "datetime";
44: if ($type=="I") Return "inet";//(".$length.")";
45: if ($type=="Y") Return "money";
46: }
47: }
48: function DropAfterNULL($string){
49: for ($i=-1; $i<=strlen($string); $i++){
50: $temp_s=implode(' ',unpack ('C*', substr($string, $i, 1)));
51: if ($temp_s==0) return substr($string, 0, $i);
52: }
53: Return $string;
54: }
55: function MemoEnd($string){
56: for ($i=-1; $i<=strlen($string); $i++){
57: $temp_s=implode(" ", unpack ('H*', substr($string, $i, 1)));
58: if ($temp_s=="1a") return substr($string, 0, $i);
59: }
60: Return $string;
61: }
62: function GetMemo($data, $block, $Size_of_block, $file_size){
63: $next="ok";
64: $i=0;
65: while ($next=="ok"){
66: $i=$i+$Size_of_block;
67: $offset=($block)*$Size_of_block;
68: $read_bytes=$file_size-$offset;
69: if ($read_bytes>=$Size_of_block) $read_bytes=$Size_of_block;
70: $block_format = 'A'.$read_bytes.'Memo';
71: $block_data=unpack ("@$offset/$block_format", $data);
72: $memo.=MemoEnd($block_data["Memo"]);
73: if (strlen($memo)!=$i) $next="";
74: $block++;
75: }
76: Return $memo;
77: }
78: ?>
79: <html>
80: <head>
81: <title>select & update area</title>
82: <meta http-equiv="Content-Type" content="text/html; charset=windows-1250">
83: </head>
84: <body bgstyle="color:#DDE8FF" text="#000000">
85: <form name="form1" method="post" action="<? echo $PHP_SELF ?>">
86: <table border="0">
87: <?
88: /*** Read units (directory) contents & show it with checkboxes ***/
89: foreach ($unit as $unit_tmp){
90: $i=0;
91: ?>
92: <tr>
93: <td nowrap> <?=$fieldUnitName?> <b>
94: <?echo strtoupper($unit_tmp)?>:
95: </b> </td>
96: <?$handle=opendir($unit_tmp);
97: while ($file = readdir($handle)){
98: if (substr($file, -3) == "dbf" OR substr($file, -3) == "DBF"){
99: $i++;
100: $db_file[$unit_tmp][$i]= $file;
101: $db_file_name[$unit_tmp][$i]= strtolower(substr($file, 0, -4));
102: $sql_table_name[$unit_tmp][$i] = $unit_tmp."_".$db_file_name[$unit_tmp][$i];?>
103: <td nowrap align="right"><b> <?=$db_file_name[$unit_tmp][$i]?>: </b>
104: <input type="checkbox" name="<?echo $sql_table_name[$unit_tmp][$i];?>" value="yes" <?if ($$sql_table_name[$unit_tmp][$i] == "yes") echo "checked"?>> <?
105: }
106: }
107: closedir($handle);?>
108: </td>
109: <?
110: }
111: /*** change names of sql tables - configured in config.php ***/
112: foreach ($sql_table_name as $key => $value) {
113: foreach($value as $key1 => $value1){
114: $sql_table_name[$key][$key1] = strtr($value1, $transformTableNames);
115: }
116: }
117: ?>
118: </tr>
119: </table>
120: <p>
121: <input type="submit" name="update" value="go update">
122: </p>
123: </form>
124: <?
125: if ($update=="go update"){
126: ini_set("max_execution_time",$maxExecTime);
127: $sql = new cSQL;
128: $sql->connect($sql_type, $sql_host, $sql_user, $sql_pass, $sql_db);
129: echo "<b><font color='blue'>User ".$sql_user.": working on ".$sql->sql_type.", using database ".$sql_db." on ".$sql_host."</font></b><br><br>";
130: /*** for each unit (directory) ***/
131: foreach ($unit as $unit_tmp){
132: $i=0;
133: /*** for each unit dbase (dbf file in directory) ***/
134: foreach ($db_file[$unit_tmp] as $db_file_tmp) {
135: $sql_id="";
136: $sql_unit="";
137: $i++;
138: $fields = array();
139: $unit_orig=$unit_tmp."_".$db_file_name[$unit_tmp][$i];
140: if ($$unit_orig == "yes"){
141: echo "<font color='blue'>".ucfirst(strtolower($db_file_tmp))."</font>: ";
142: $file = $unit_tmp."/".$db_file_tmp;
143: /*** check for memo file & get size of blocks ***/
144: $memo_file="";
145: if (file_exists(substr($file, 0, -1)."t")) $memo_file=substr($file, 0, -1)."t";
146: if (file_exists(substr($file, 0, -1)."T")) $memo_file=substr($file, 0, -1)."T";
147: if (file_exists(substr($file, 0, -3)."fpt")) $memo_file=substr($file, 0, -3)."fpt";
148: if (file_exists(substr($file, 0, -3)."FPT")) $memo_file=substr($file, 0, -3)."FPT";
149: if ($memo_file!=""){
150: $memo_fp = fopen ($memo_file, 'rb')
151: or die ("File <i>$memo_file</i> cannot be opened.");
152: $memo_data = fread ($memo_fp, filesize($memo_file))
153: or die ("Could not read data from file <i>$memo_file</i>");
154: $memo_header_format = 'SNext aviable/'.'SSize of blocks/'.'A8Dbf name';
155: $memo_header = unpack ($memo_header_format, $memo_data);
156: if ($memo_header["Size of blocks"]==0) $memo_header["Size of blocks"]=512;
157: }
158: /*** get field names, types & lenght fom dbf files ***/
159: $fp = fopen ($file, 'rb')
160: or die ("File <i>$file</i> cannot be opened.");
161: $data = fread ($fp, 32)
162: or die ("Could not read data from file <i>$file</i>");
163: $header_format = 'H2id/' . 'CYear/' . 'CMonth/' . 'CDay/' . 'L# of Records/' . 'SHeader Size/' . 'SRecord Size';
164: $header = unpack ($header_format, $data);
165: $data = fread ($fp, $header['Header Size'] - 34)
166: or die ("Could not read data from file <i>$file</i>");
167: $record_format = 'A11Field Name/' . 'AField Type/' . 'x4/' . 'CField Length/' . 'CField Precision';
168: $x=0;
169: for ($offset = 0; $offset < strlen ($data); $offset += 32) {
170: $x++;
171: $fields[$x] = unpack("@$offset/$record_format", $data);
172: foreach ($fields[$x] as $key => $value) {$fields[$x][$key] = DropAfterNULL(trim($value));}
173: }
174: /*** check if mysql table wit this name exist ***/
175: $ii=0;
176: $y=0;
177: $mysql_struct=array();
178: $mysql_table=$sql->show_tables();
179: while ($sql->result()){
180: $y++;
181: $tb_names[$y]=$sql->myrow['0'];
182: }
183: /*** get field names & types from mysql table ***/
184: if (is_array($tb_names) && in_array($sql_table_name[$unit_tmp][$i], $tb_names)){
185: $sql->describe($sql_table_name[$unit_tmp][$i]);
186: if ($sql->num_rows>=1){
187: //while ($myrow = mysql_fetch_array($mysql_table)){
188: while ($sql->result()){
189: $ii++;
190: $mysql_struct[$ii]["Field"]=$sql->myrow['0'];
191: $mysql_struct[$ii]["Type"]=$sql->myrow['1'];
192: }
193: }
194: }
195: /*** create query for table create ***/
196: if ($needID[$sql_table_name[$unit_tmp][$i]]==1 && $sql->sql_type=="MySql") $sql_id="`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ";
197: if ($needID[$sql_table_name[$unit_tmp][$i]]==1 && $sql->sql_type=="PostgreSQL") $sql_id="\"id\" SERIAL, PRIMARY KEY (\"id\"), ";
198: if ($needUnitName[$sql_table_name[$unit_tmp][$i]]==1) $sql_unit="`" . $fieldUnitName . "` VARCHAR(15) NOT NULL, ";
199: $quer="CREATE TABLE `" . $sql_table_name[$unit_tmp][$i] . "` (" . $sql_id . $sql_unit;
200: $exist=0;
201: $y=0;
202: foreach ($fields as $field){
203: $field['Field Name']=ucfirst(strtolower($field['Field Name']));
204: if ($field['Field Type']=="M" && $memo_file==""){
205: 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>";
206: continue 1;
207: }elseif ($field['Field Type']=="M" && $memo_file!=""){
208: echo "including <b>dbt</b> memo file, ";
209: }
210: if (($field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file==""){
211: 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>";
212: continue 1;
213: }elseif (($field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file!=""){
214: echo "including <b>fpt</b> memo file, ";
215: }
216: if ($field['Field Type']=="B" && $memo_file!=""){
217: echo "including <b>dbt</b> memo file, ";
218: }
219: $quer .= "`" . $field['Field Name'] . "` " . GetFType($sql_type,$field['Field Type'],$field['Field Length'], $field['Field Precision']) . " NOT NULL" . ", ";
220: $y++;
221: if (is_array($mysql_struct)){
222: foreach ($mysql_struct as $ii){
223: if ($field['Field Name']==$ii["Field"] && GetFType($sql_type,$field['Field Type'],$field['Field Length'], $field['Field Precision'])==$ii["Type"]){$exist++;}
224: }
225: }
226: }
227: $quer=substr($quer, 0, -2) . ") TYPE=MyISAM";
228: /*** create mysql table if not exist ***/
229: $new_table="";
230: if ($exist==0){
231: if ($sql->sql_type == "PostgreSQL"){
232: $quer=str_replace("`", "\"", $quer);
233: $quer=str_replace("TYPE=MyISAM", "", $quer);
234: }
235: $sql->query($quer);
236: $exist=$y;
237: if ($needUnitName[$sql_table_name[$unit_tmp][$i]]==1) $mysql_struct[0]["Field"]=$fieldUnitName;
238: echo "creating table <b>".$sql_table_name[$unit_tmp][$i]."</b>, ";
239: $new_table="yes";
240: }else{
241: echo "table <b>".$sql_table_name[$unit_tmp][$i]."</b> exist, ";
242: }
243: /*** mysql table exist, but it's not the same table, skip ***/
244: if ($exist!=$y){
245: echo "but not have the same Field names or Field types! <font color='red'><b>skipped</b></font>";
246: }else{
247: /*** delete old data from mysql table (update mode 1) or get last record number (update mode 2) ***/
248: $update_from=1;
249: if ($updateMode[$sql_table_name[$unit_tmp][$i]]==2){
250: $que_del="";
251: if (is_array($mysql_struct)){
252: foreach ($mysql_struct as $ii){
253: if ($ii["Field"]==$fieldUnitName){
254: $que_del="SELECT * FROM " . $sql_table_name[$unit_tmp][$i] . " where " . $fieldUnitName . " = '" . strtoupper($unit_tmp) . "'";
255: }
256: }
257: if ($que_del=="") $que_del="SELECT * FROM " . $sql_table_name[$unit_tmp][$i];
258: $sql->query($que_del);
259: $update_from=$sql->num_rows+1;
260: if ($update_from<=0) $update_from=1;
261: }
262: }else{
263: $update_from=1;
264: $que_del="";
265: if (is_array($mysql_struct)){
266: foreach ($mysql_struct as $ii){
267: if ($ii["Field"]==$fieldUnitName){
268: $que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] . "` where `" . $fieldUnitName . "` = '" . strtoupper($unit_tmp) . "'";
269: }
270: }
271: if ($que_del=="") $que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] ."`";
272: if ($sql->sql_type == "PostgreSQL"){
273: $que_del=str_replace("`", "\"", $que_del);
274: }
275: $sql->query($que_del);
276: }
277: }
278: /*** rad data from dbf file & insert in mysql table ***/
279: fread ($fp, 3);
280: if ($header["# of Records"]!=0){
281: $data = fread ($fp, ($header["# of Records"]*$header["Record Size"])-1);
282: }else{
283: $data="";
284: }
285: $row_format="";
286: foreach ($fields as $field){
287: $row_format .= 'A' . $field["Field Length"] . $field["Field Name"] . "/";
288: }
289: $row_format = substr($row_format, 0, -1);
290: ?><script>
291: window.parent.graf.f_enota.enota.value=<?echo "\"making ".$sql_table_name[$unit_tmp][$i].":\""?>;
292: </script><?
293: $on_ever = floor(($header["# of Records"]-$update_from+1)/100);
294: $tempo=1;
295: if ($on_ever<1){
296: $tempo=($header["# of Records"]-$update_from+1)/100;
297: if ($tempo!=0){
298: $tempo=1/$tempo;
299: }else{
300: $tempo=1;
301: }
302: $on_ever=1;
303: }
304: $perc=0;
305: $where_we_are=$on_ever;
306: for ($rec_no=$update_from; $rec_no<=$header["# of Records"]; $rec_no++){
307: if ($rec_no>=$where_we_are){
308: $where_we_are+=$on_ever;
309: $perc+=$tempo;if ($perc>=100) $perc=100;?>
310: <script>
311: window.parent.graf.procent.perc.value=<?echo $perc?>;
312: </script><?
313: flush();
314: }
315: $offset = $header["Record Size"]*$rec_no;
316: $record = (@unpack ("@$offset/$row_format", $data));
317: $sql_where="INSERT INTO `".$sql_table_name[$unit_tmp][$i]."` (";
318: $sql_data=" VALUES (";
319: if (is_array($mysql_struct)){
320: foreach ($mysql_struct as $ii){
321: if ($ii["Field"]==$fieldUnitName){
322: $sql_where.="`".$fieldUnitName."`, ";
323: $sql_data.="\"" . strtoupper($unit_tmp) . "\"" . ", ";
324: }
325: }
326: }
327: foreach ($fields as $field){
328: if (($field['Field Type']=="M" || $field['Field Type']=="G" || $field['Field Type']=="P") && $memo_file==""){
329: continue 1;
330: }
331: if (($field['Field Type']=="M" || $field['Field Type']=="G") && $memo_file!="" && trim($record[$field['Field Name']])!=""){
332: $record[$field['Field Name']]=trim(GetMemo($memo_data, $record[$field['Field Name']],$memo_header["Size of blocks"],filesize($memo_file)));
333: }
334: if (($field['Field Type']=="B" || $field['Field Type']=="P") && $memo_file!="" && trim($record[$field['Field Name']])!=""){
335: $record[$field['Field Name']]=GetMemo($memo_data, $record[$field['Field Name']],$memo_header["Size of blocks"],filesize($memo_file));
336: }
337: $sql_where.="`".ucfirst(strtolower($field['Field Name']))."`, ";
338: /*** do not transform binary files ***/
339: if ($field['Field Type']=="B" || $field['Field Type']=="P"){
340: $sql_data.="\"".addslashes($record[$field['Field Name']])."\"".", ";
341: }else{
342: /*** pg dont like empty decimal fields ***/
343: if ($field['Field Type']=="N" && $field['Field Precision']>=1 && trim($record[$field['Field Name']])=="") {$record[$field['Field Name']]=0;}
344: /*** and empty date fields and dont like 00000000 too :( ***/
345: if ($field['Field Type']=="D" && trim($record[$field['Field Name']])=="") {$record[$field['Field Name']]="19700101";}
346: $sql_data.="\"".addslashes(strtr($record[$field['Field Name']], $transform))."\"".", ";
347: }
348: }
349: $sql_where=substr($sql_where, 0, -2).")";
350: $sql_data=substr($sql_data, 0, -2).")";
351: $quer=$sql_where.$sql_data;
352: if ($sql->sql_type == "PostgreSQL"){
353: $quer=str_replace("\"", "'", $quer);
354: $quer=str_replace("`", "\"", $quer);
355: }
356: $sql->query($quer);
357: }
358: if ($updateMode[$sql_table_name[$unit_tmp][$i]]==2){
359: $newer_records=$header["# of Records"]-$update_from+1;
360: if ($newer_records >= 1){
361: echo "successfully inserting <b>". $newer_records ."</b> newer records from <b>" . strtolower($file) . "</b>";
362: }else{
363: if ($new_table!="yes"){
364: echo "no newer records found in <b>" . strtolower($file) . "</b>";
365: }else{
366: echo "no records found in <b>" . strtolower($file) . "</b>";
367: }
368: }
369: }else{
370: if ($header["# of Records"]>=1){
371: echo "successfully ";
372: if ($new_table!="yes") echo "delete old records and ";
373: echo "inserting <b>". $header["# of Records"] . "</b> new records from <b>" . strtolower($file) . "</b>";
374: }else{
375: echo "no records found in <b>" . strtolower($file) . "</b>";
376: }
377: }
378: fclose ($fp);
379: if ($memo_file!="") fclose ($memo_fp);
380: }
381: /*** delete some rows whitch we dont need ***/
382: if (is_array($data_delete)){
383: if ($data_delete[$sql_table_name[$unit_tmp][$i]]){
384: $que_del="DELETE FROM `" . $sql_table_name[$unit_tmp][$i] . "` WHERE " . $data_delete[$sql_table_name[$unit_tmp][$i]];
385: if ($sql->sql_type == "PostgreSQL"){
386: $que_del=str_replace("`", "\"", $que_del);
387: }
388: echo "<br>deleting data from <b>".$sql_table_name[$unit_tmp][$i]."</b> where <b>".$data_delete[$sql_table_name[$unit_tmp][$i]]."</b>";
389: if ($sql->query($que_del) === FALSE){
390: //printf("<BR>mysql error: %s\n", mysql_error());
391: 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>";
392: }
393: }
394: }
395: echo ", optimizing table <b>".$sql_table_name[$unit_tmp][$i].",";
396: $sql->optimize($sql_table_name[$unit_tmp][$i]);
397: echo "<font color='blue'> done</font></b><br>";
398: }
399: }
400: }
401: ?><script>
402: window.parent.graf.f_enota.enota.value=<?echo "\"done\""?>;
403: </script><?
404: echo "<br><b><font color='blue'>Done in all</font></b><br>";
405: }
406: ?>
407: </body>
408: </html>
|