include/classes/CSVImport.class.php

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