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);
}
