docs/include/classes/CSVImport.class.php

   1 <?php
   2 /************************************************************************/
   3 /* ATutor                                                                                                                               */
   4 /************************************************************************/
   5 /* Copyright (c) 2002-2010                                              */
   6 /* Inclusive Design Institute                                           */
   7 /* http://atutor.ca                                                     */
   8 /* This program is free software. You can redistribute it and/or        */
   9 /* modify it under the terms of the GNU General Public License          */
  10 /* as published by the Free Software Foundation.                        */
  11 /************************************************************************/
  12 // $Id$
  13
  14 class CSVImport {
  15         var $quote_search  = array('""', '\\\n', '\\\r');
  16         var $quote_replace = array('"', '\n', '\r');
  17
  18         // constructor
  19         function CSVImport() { }
  20
  21         // public
  22         // returns the primary_key, or false if there is none, or null if more than 1
  23         function getPrimaryFieldName($table_name) {
  24                 global $db;
  25
  26                 $field = false;
  27
  28                 $sql = "SELECT * FROM ".TABLE_PREFIX.$table_name .' WHERE 0';
  29                 $result = mysql_query($sql, $db);
  30                 $num_fields = mysql_num_fields($result);
  31                 for ($i= 0; $i<$num_fields; $i++) {
  32                         $flags = explode(' ', mysql_field_flags($result, $i));
  33                         if (in_array('primary_key', $flags)) {
  34                                 if ($field == false) {
  35                                         $field = mysql_field_name($result, $i);
  36                                 } else {
  37                                         // there is more than one primary_key
  38                                         return NULL;
  39                                 }
  40                         }
  41                 }
  42                 return $field;
  43         }
  44
  45
  46         // public
  47         // given a query result returns an array of field types.
  48         // possible field types are int, string, datetime, or blob...
  49         function detectFieldTypes($table_name) {
  50                 global $db;
  51
  52                 $field_types = array();
  53
  54                 $sql = "SELECT * FROM ".TABLE_PREFIX.$table_name .' WHERE 0';
  55                 $result = @mysql_query($sql, $db);
  56                 if (!$result) {
  57                         return array();
  58                 }
  59                 $num_fields = mysql_num_fields($result);
  60
  61                 for ($i=0; $i< $num_fields; $i++) {
  62                         $field_types[] = mysql_field_type($result, $i);
  63                 }
  64
  65                 return $field_types;
  66         }
  67
  68         function translateWhitespace($input) {
  69                 $input = addslashes($input);
  70                 $input = str_replace($this->quote_search, $this->quote_replace, $input);
  71
  72                 return $input;
  73         }
  74
  75         // public
  76         function import($tableName, $path, $course_id, $version) {
  77                 global $db;
  78                 static $table_id_map;
  79
  80                 $fn_name = $tableName.'_convert';
  81
  82                 // lock the tables
  83                 $lock_sql = 'LOCK TABLES ' . TABLE_PREFIX . $tableName. ', ' . TABLE_PREFIX . 'courses WRITE';
  84                 $result   = mysql_query($lock_sql, $db);
  85
  86                 // get the field types
  87                 $field_types = $this->detectFieldTypes($tableName);
  88                 if (!$field_types) {
  89                         return FALSE;
  90                 }
  91
  92                 // get the name of the primary field
  93                 $primary_key_field_name = $this->getPrimaryFieldName($tableName);
  94                 // read the rows into an array
  95                 $fp = @fopen($path . $tableName . '.csv', 'rb');
  96                 $i = 0;
  97
  98                 // get the name of the primary ID field and the next index
  99                 $next_id = 0;
 100                 if ($primary_key_field_name) {
 101                         // get the next primary ID
 102                         $sql     = 'SELECT MAX(' . $primary_key_field_name . ') AS next_id FROM ' . TABLE_PREFIX . $tableName;
 103                         $result  = mysql_query($sql, $db);
 104                         $next_id = mysql_fetch_assoc($result);
 105                         $next_id = $next_id['next_id']+1;
 106                 }
 107
 108                 $rows = array();
 109                 while ($row = @fgetcsv($fp, 70000)) {
 110                         if (count($row) && (trim($row[0]) == '')) {
 111                                 continue;
 112                         }
 113
 114                         if (function_exists($fn_name)) {
 115                                 $row = $fn_name($row, $course_id, $table_id_map, $version);
 116                         }
 117                         if (!$row) {
 118                                 continue;
 119                         }
 120                         if ($row[0] == 0) {
 121                                 $row[0] = $i;
 122                         }
 123
 124                         $table_id_map[$tableName][$row[0]] = $next_id;
 125                         if ($primary_key_field_name != NULL) {
 126                                 $row[0] = $next_id;
 127                         }
 128
 129                         $sql = 'REPLACE INTO '.TABLE_PREFIX.$tableName.' VALUES (';
 130
 131                         foreach($row as $id => $field) {
 132                                 if (($field_types[$id] != 'int') && ($field_types[$id] != 'real')) {
 133                                         $field = $this->translateWhitespace($field);
 134                                 } else if ($field_types[$id] == 'int') {
 135                                         $field = intval($field);
 136                                 }
 137                                 $sql .= "'" . $field."',";
 138                         }
 139                         $sql = substr($sql, 0, -1);
 140                         $sql .= ')';
 141
 142                         $result = mysql_query($sql, $db);
 143                         $i++;
 144                         $next_id++;
 145                 }
 146
 147                 // close the file
 148                 @fclose($fp);
 149
 150                 // unlock the tables
 151                 $lock_sql = 'UNLOCK TABLES';
 152                 $result   = mysql_query($lock_sql, $db);
 153         }
 154
 155 }
 156
 157 ?>