protected static function convertCsvIntoRowsInTable($fileHandle, $tableName, $delimiter, $enclosure, $columns) { assert('gettype($fileHandle) == "resource"'); assert('is_string($tableName)'); assert('$tableName == strtolower($tableName)'); assert('$delimiter != null && is_string($delimiter)'); assert('$enclosure != null && is_string($enclosure)'); assert('is_array($columns)'); $bulkQuantity = 500; $importArray = array(); while (($data = fgetcsv($fileHandle, 0, $delimiter, $enclosure)) !== false) { if (count($data) > 1 || count($data) == 1 && trim($data['0']) != '') { foreach ($data as $k => $v) { //Convert characterser to UTF-8 $currentCharset = mb_detect_encoding($v, $other_charsets = 'UTF-8, UTF-7, ASCII, CP1252, EUC-JP, SJIS, eucJP-win, SJIS-win, JIS, ISO-2022-JP'); if (!empty($currentCharset) && $currentCharset != "UTF-8") { $data[$k] = mb_convert_encoding($v, "UTF-8"); } } $importArray[] = $data; } if (count($importArray) > $bulkQuantity) { DatabaseCompatibilityUtil::bulkInsert($tableName, $importArray, $columns, $bulkQuantity); $importArray = array(); } } if (count($importArray) > $bulkQuantity) { throw new NotSupportedException(); } if (count($importArray) > 0) { DatabaseCompatibilityUtil::bulkInsert($tableName, $importArray, $columns, $bulkQuantity); } }
public function testBulkInsert() { $model = new TestDatabaseBulkInsertModel(); $model->number = 9999; $model->string = 'adasd'; $model->save(); $model->delete(); // Test with different quatations. $tableName = TestDatabaseBulkInsertModel::getTableName('TestDatabaseBulkInsertModel'); $columnNames = array('number', 'string'); $insertData = array(array(999, 'It\'s string with quatation.'), array(1000, "It\\`s string with quatation."), array(1001, 'It\'s string with "quatation".')); DatabaseCompatibilityUtil::bulkInsert($tableName, $insertData, $columnNames, 3); $bulkInsertedRows = R::getAll("select * from {$tableName} order by id"); $this->assertEquals(count($bulkInsertedRows), 3); for ($i = 0; $i < 3; $i++) { $this->assertEquals($bulkInsertedRows[$i]['number'], $insertData[$i][0]); $this->assertEquals($bulkInsertedRows[$i]['string'], $insertData[$i][1]); } $models = TestDatabaseBulkInsertModel::getAll(); if (count($models) > 0) { foreach ($models as $model) { $model->delete(); } } // Test when there are less rows of data then bulk quantity for one loop. $tableName = TestDatabaseBulkInsertModel::getTableName('TestDatabaseBulkInsertModel'); $columnNames = array('number', 'string'); $numberOfRows = 50; $bulkQuantity = 100; $insertData = $this->createDumpDataForBulkInsert($numberOfRows); DatabaseCompatibilityUtil::bulkInsert($tableName, $insertData, $columnNames, $bulkQuantity); $bulkInsertedRows = R::getAll("select * from {$tableName} order by id"); $this->assertEquals(count($bulkInsertedRows), $numberOfRows); for ($i = 0; $i < $numberOfRows; $i++) { $this->assertEquals($bulkInsertedRows[$i]['number'], $insertData[$i][0]); $this->assertEquals($bulkInsertedRows[$i]['string'], $insertData[$i][1]); } $models = TestDatabaseBulkInsertModel::getAll(); if (count($models) > 0) { foreach ($models as $model) { $model->delete(); } } // Test when there is much data, for multiple loops of bulk insert. $numberOfRows = 520; $insertData = $this->createDumpDataForBulkInsert($numberOfRows); $bulkQuantity = 100; $importDataForOneLoop = array(); foreach ($insertData as $row) { $importDataForOneLoop[] = $row; if (count($importDataForOneLoop) > $bulkQuantity) { DatabaseCompatibilityUtil::bulkInsert($tableName, $importDataForOneLoop, $columnNames, $bulkQuantity); $importDataForOneLoop = array(); } } $this->assertFalse(count($importDataForOneLoop) > $bulkQuantity); if (count($importDataForOneLoop) > 0) { DatabaseCompatibilityUtil::bulkInsert($tableName, $importDataForOneLoop, $columnNames, $bulkQuantity); } $bulkInsertedRows = R::getAll("select * from {$tableName} order by id"); $this->assertEquals(count($bulkInsertedRows), $numberOfRows); for ($i = 0; $i < $numberOfRows; $i++) { $this->assertEquals($bulkInsertedRows[$i]['number'], $insertData[$i][0]); $this->assertEquals($bulkInsertedRows[$i]['string'], $insertData[$i][1]); } }
/** * Imports data from array to table * @param $tableName * @param $importArray * @param $columnNames * @throws NotSupportedException */ protected static function importArrayIntoTable($tableName, array &$importArray, array $columnNames) { assert('is_string($tableName)'); assert('$tableName == strtolower($tableName)'); assert('is_array($columnNames)'); assert('is_array($importArray)'); do { $importSubset = ArrayUtil::chopArray($importArray, static::BULK_INSERT_COUNT); // bulkInsert needs every subarray to have same number of columns as columnNames, pad with empty strings static::padSubArrays($importSubset, count($columnNames)); DatabaseCompatibilityUtil::bulkInsert($tableName, $importSubset, $columnNames, static::BULK_INSERT_COUNT, true); } while (count($importSubset) > 0); }