<commit_before><?php
/**
 * OpenEyes
 *
 * (C) Moorfields Eye Hospital NHS Foundation Trust, 2008-2011
 * (C) OpenEyes Foundation, 2011-2012
 * This file is part of OpenEyes.
 * OpenEyes is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 * OpenEyes is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License along with OpenEyes in a file titled COPYING. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package OpenEyes
 * @link http://www.openeyes.org.uk
 * @author OpenEyes <<EMAIL>>
 * @copyright Copyright (c) 2008-2011, Moorfields Eye Hospital NHS Foundation Trust
 * @copyright Copyright (c) 2011-2012, OpenEyes Foundation
 * @license http://www.gnu.org/licenses/gpl-3.0.html The GNU General Public License V3.0
 */

class ImportDataCommand extends CConsoleCommand {
	
	const DATA_FOLDER = 'data/import';

	public function getName() {
		return 'Import Data Command.';
	}
	
	public function getHelp() {
		return "Import data from csv into the database.\n";
	}

	/**
	 * Parse csv files from Google Docs, process them into the right format for MySQL import, and import them
	 */
	public function run($args) {
		
		// Initialise db
		$connection = Yii::app()->db;
		$command = $connection->createCommand("SET foreign_key_checks = 0;");
		$row_count = $command->execute();
		
		$path = Yii::app()->basePath . '/' . self::DATA_FOLDER . '/';
		foreach(glob($path."*.map") as $map_path) {
			
			// Convert csv file into format suitable for MySQL import
			$table = substr(basename($map_path), 0, -4);
			$map = file($map_path);
			$import_file_path = $path . trim($map[0]);
			$export_columns = explode(',',trim($map[1]));
			$tmp_path = $path . 'tmp';
			if(!file_exists($tmp_path)) {
				mkdir($path . 'tmp');
			}
			$file = file($import_file_path);
			$file_output = fopen($tmp_path . '/' . $table . '.csv', 'w+');
			foreach($file as $index => $line) {
				if(!$index) {
					$columns = str_getcsv($line, ',', '"');
					fputcsv($file_output, $export_columns, ',', '"');
				} else {
					$output = array(); 
					$record = str_getcsv($line, ',', '"');
					foreach($export_columns as $column) {
						$column_index = array_search($column, $columns);
						// FIXME: This is probably bad
						$output[] = $record[$column_index];
					}
					fputcsv($file_output, $output, ',', '"');
				}
			}
			fclose($file_output);
			
			// Import file into database
			$columns_string = implode(',', $export_columns);
			$query = "
			LOAD DATA LOCAL INFILE '$tmp_path/$table.csv' INTO TABLE $table
			FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
			LINES TERMINATED BY '\\n'
			IGNORE 1 LINES
			($columns_string);
			";
			
			// Truncate table
			$command = $connection->createCommand("TRUNCATE $table");
			$row_count = $command->execute();
				
			// Import records
			$command = $connection->createCommand($query);
			$row_count = $command->execute();
			
			// Remove import file
			unlink("$tmp_path/$table.csv");
			
		}
	}
}<commit_msg>Change import data command to avoid load data infile<commit_after><?php
/**
 * OpenEyes
 *
 * (C) Moorfields Eye Hospital NHS Foundation Trust, 2008-2011
 * (C) OpenEyes Foundation, 2011-2012
 * This file is part of OpenEyes.
 * OpenEyes is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 * OpenEyes is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License along with OpenEyes in a file titled COPYING. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package OpenEyes
 * @link http://www.openeyes.org.uk
 * @author OpenEyes <<EMAIL>>
 * @copyright Copyright (c) 2008-2011, Moorfields Eye Hospital NHS Foundation Trust
 * @copyright Copyright (c) 2011-2012, OpenEyes Foundation
 * @license http://www.gnu.org/licenses/gpl-3.0.html The GNU General Public License V3.0
 */

class ImportDataCommand extends CConsoleCommand {

	const DATA_FOLDER = 'data/import';

	public function getName() {
		return 'Import Data Command.';
	}

	public function getHelp() {
		return "Import data from csv into the database.\n";
	}

	/**
	 * Parse csv files from Google Docs, process them into the right format for MySQL import, and import them
	 */
	public function run($args) {

		// Initialise db
		$connection = Yii::app()->db;
		$command = $connection->createCommand("SET foreign_key_checks = 0;");
		$row_count = $command->execute();

		$path = Yii::app()->basePath . '/' . self::DATA_FOLDER . '/';
		foreach(glob($path."*.map") as $map_path) {
			$table = substr(basename($map_path), 0, -4);
			echo "Importing $table data...";
				
			// Truncate existing data
			$row_count = $connection->createCommand("TRUNCATE $table")->execute();

			// Get mapping info
			$map = file($map_path);
			$import_file_path = $path . trim($map[0]);
			$export_columns = explode(',',trim($map[1]));
				
			$file = file($import_file_path);
			$row_count = 0;
			$block_size = 1000;
			$values = array();
			foreach($file as $index => $line) {
				if(!$index) {
					$columns = str_getcsv($line, ',', '"');
				} else {
					$row_count++;
					$output = array();
					$record = str_getcsv($line, ',', '"');
					foreach($export_columns as $column) {
						$column_index = array_search($column, $columns);
						$output[] = $record[$column_index];
					}
					$values[] = $output;
					if(!($row_count % $block_size)) {
						// Insert values in blocks to better handle very large tables
						$this->insertBlock($table, $export_columns, $values);
						$values = array();
					}
				}
			}
			if(!empty($values)) {
				// Insert remaining values
				$this->insertBlock($table, $export_columns, $values);
			}
			echo "imported $row_count records, done.\n";
		}
	}

	/**
	 * Insert a block of records into a table
	 * @param string $table
	 * @param array $columns
	 * @param array $records
	 */
	protected function insertBlock($table, $columns, $records) {
		$db = Yii::app()->db;
		foreach($columns as &$column) {
			$column = $db->quoteColumnName($column);
		}
		$insert = array();
		foreach($records as $record) {
			foreach($record as &$field) {
				if($field == '') {
					$field = 'NULL';
				} else {
					$field = $db->quoteValue($field);
				}
			}
			$insert[] = '('.implode(',', $record).')';
		}
		$query = "INSERT INTO ".$db->quoteTableName($table)." (".implode(',',$columns).") VALUES ".implode(',', $insert);
		$db->createCommand($query)->execute();
	}

}
{'fraction_non_alphanumeric': 0.14870568373663479, 'fraction_numerical': 0.012661789532920653, 'mean_word_length': 3.5022165927802407, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1775015', 'n_tokens_mistral': 2606, 'n_tokens_neox': 2173, 'n_words': 685}