We have this code which works well most of the time, except for a case I will describe slightly latter:
/**
* Recovers archived survey responses
*
* @param int $surveyId survey ID
* @param string $archivedResponseTableName archived response table name to be imported
* @param bool $preserveIDs if archived response IDs should be preserved
* @param array $validatedColumns the columns that are validated and can be inserted again
* @return integer number of rows affected by the execution.
* @throws Exception execution failed
*/
function recoverSurveyResponses(int $surveyId, string $archivedResponseTableName, $preserveIDs, array $validatedColumns = []): int
{
if (!is_array($validatedColumns)) {
$validatedColumns = [];
}
$pluginDynamicArchivedResponseModel = PluginDynamic::model($archivedResponseTableName);
$targetSchema = SurveyDynamic::model($surveyId)->getTableSchema();
$encryptedAttributes = Response::getEncryptedAttributes($surveyId);
if ((App()->db->tablePrefix) && (strpos($archivedResponseTableName, App()->db->tablePrefix) === 0)) {
$tbl_name = str_replace('old_survey', 'old_tokens', substr($archivedResponseTableName, strlen(App()->db->tablePrefix)));
} else {
$tbl_name = str_replace('old_survey', 'old_tokens', $archivedResponseTableName);
}
$archivedTableSettings = ArchivedTableSettings::model()->findByAttributes(['tbl_name' => $tbl_name, 'tbl_type' => 'response']);
$archivedEncryptedAttributes = [];
if ($archivedTableSettings) {
$archivedEncryptedAttributes = json_decode($archivedTableSettings->properties, true);
}
$archivedResponses = new CDataProviderIterator(new CActiveDataProvider($pluginDynamicArchivedResponseModel), 500);
$tableName = "{{survey_$surveyId}}";
$importedResponses = 0;
$batchData = [];
foreach ($archivedResponses as $archivedResponse) {
$dataRow = [];
// Using plugindynamic model because I dont trust surveydynamic.
$targetResponse = new PluginDynamic($tableName);
if ($preserveIDs) {
$targetResponse->id = $archivedResponse->id;
$dataRow['id'] = $archivedResponse->id;
}
$to = 'new_c';
$from = 'old_c';
for ($index = 0; $index < count($validatedColumns[$to]); $index++) {
$source = $validatedColumns[$from][$index];
$target = $validatedColumns[$to][$index];
$targetResponse->{$target} = $archivedResponse[$source];
if (in_array($source, $archivedEncryptedAttributes, false) && !in_array($target, $encryptedAttributes, false)) {
$targetResponse->{$target} = $archivedResponse->decryptSingle($archivedResponse[$source]);
} elseif (!in_array($source, $archivedEncryptedAttributes, false) && in_array($target, $encryptedAttributes, false)) {
$targetResponse->{$target} = $archivedResponse->encryptSingle($archivedResponse[$source]);
} else {
$targetResponse->{$target} = $archivedResponse[$source];
}
$dataRow[$target] = $targetResponse->{$target};
}
$additionalFields = [
'token',
'submitdate',
'lastpage',
'startlanguage',
'seed',
'startdate',
'datestamp',
'version_number'
];
if (isset($targetSchema->columns['startdate']) && empty($targetResponse['startdate'])) {
$targetResponse->{'startdate'} = date("Y-m-d H:i", (int)mktime(0, 0, 0, 1, 1, 1980));
$dataRow['startdate'] = $targetResponse->{'startdate'};
}
if (isset($targetSchema->columns['datestamp']) && empty($targetResponse['datestamp'])) {
$targetResponse->{'datestamp'} = date("Y-m-d H:i", (int)mktime(0, 0, 0, 1, 1, 1980));
$dataRow['datestamp'] = $targetResponse->{'datestamp'};
}
foreach ($additionalFields as $additionalField) {
if (isset($archivedResponse->{$additionalField}) && isset($targetSchema->columns[$additionalField])) {
$dataRow[$additionalField] = $archivedResponse->{$additionalField};
}
}
$beforeDataEntryImport = new PluginEvent('beforeDataEntryImport');
$beforeDataEntryImport->set('iSurveyID', $surveyId);
$beforeDataEntryImport->set('oModel', $targetResponse);
App()->getPluginManager()->dispatchEvent($beforeDataEntryImport);
if ($targetResponse->validate()){
$batchData[] = $dataRow;
}
if (count($batchData) % 500 === 0) {
if ($preserveIDs) {
switchMSSQLIdentityInsert("survey_$surveyId", true);
}
$builder = App()->db->getCommandBuilder();
$command = $builder->createMultipleInsertCommand($tableName, $batchData);
$importedResponses += $command->execute();
if ($preserveIDs) {
switchMSSQLIdentityInsert("survey_$surveyId", false);
}
$batchData = [];
}
unset($targetResponse);
}
if (count($batchData)) {
if ($preserveIDs) {
switchMSSQLIdentityInsert("survey_$surveyId", true);
}
$builder = App()->db->getCommandBuilder();
$command = $builder->createMultipleInsertCommand($tableName, $batchData);
$importedResponses += $command->execute();
if ($preserveIDs) {
switchMSSQLIdentityInsert("survey_$surveyId", false);
}
}
return $importedResponses;
}
in short, it's building batches dynamically not knowing in advance which the target table will be, what its fields were. A survey is activated and an archive of responses is being imported back. It has been released in the spring and there were no complaints. But recently, while testing another feature we were about to release I realized this is not working well for a particular survey...
So I debugged the issue and realized that the culprit is that responses for some question types are stored in fields containing #, like 282267X506X5569SQ04#0. Now, when calling CDbCommandBuilder::createMultipleInsertCommand (see https://www.yiiframework.com/doc/api/1.1/CDbCommandBuilder#createMultipleInsertCommand-detail and https://www.yiiframework.com/doc/api/1.1/CDbCommandBuilder#composeMultipleInsertCommand-detail), I mean at
$command = $builder->createMultipleInsertCommand($tableName, $batchData);
we are to pass $batchData, which is an array of data rows, each data row is an associative array that maps columnname with column value. For example:
[
[
//...
"282267X506X5569SQ04#0" => "some answher here"
//...
],
//...
]
And this generates a command that looks like this:
INSERT INTO `lime_survey_282267` (`282267X506X5530`,
`282267X506X5531`,
`282267X507X5534SQ001`,
`282267X507X5534SQ002`,
`282267X507X5534SQ003`,
`282267X507X5534SQ004`,
`282267X504X5513`,
`282267X504X5514`,
`282267X504X5515`,
`282267X504X5516`,
`282267X504X5517`,
`282267X504X5518SQ01`,
`282267X504X5518SQ02`,
`282267X504X5518SQ03`,
`282267X504X5518SQ04`,
`282267X504X5519SQ01`,
`282267X504X5519SQ02`,
`282267X504X5519SQ03`,
`282267X504X5519SQ04`,
`282267X504X5520SQ01`,
`282267X504X5520SQ02`,
`282267X504X5520SQ03`,
`282267X504X5520SQ04`,
`282267X505X5521`,
`282267X505X5522SQ01`,
`282267X505X5522SQ02`,
`282267X505X5522SQ03`,
`282267X505X5522SQ04`,
`282267X505X5523SQ001`,
`282267X505X5523SQ002`,
`282267X505X5523SQ003`,
`282267X505X5524SQ01`,
`282267X505X5524SQ02`,
`282267X505X5524SQ03`,
`282267X505X5524SQ04`,
`282267X506X5525`,
`282267X506X5526`,
`282267X506X5527`,
`282267X506X5528`,
`282267X506X5549`,
`282267X506X5529`,
`282267X506X5530other`,
`282267X506X5531comment`,
`282267X506X5535`,
`282267X506X5536`,
`282267X506X5542`,
`282267X506X5538SQ01`,
`282267X506X5538SQ02`,
`282267X506X5538SQ03`,
`282267X506X5538SQ04`,
`282267X506X5547SQ01`,
`282267X506X5547SQ02`,
`282267X506X5547SQ03`,
`282267X506X5547SQ04`,
`282267X506X5539SQ01`,
`282267X506X5539SQ02`,
`282267X506X5539SQ03`,
`282267X506X5539SQ04`,
`282267X506X5540SQ01`,
`282267X506X5540SQ02`,
`282267X506X5540SQ03`,
`282267X506X5540SQ04`,
`282267X506X5541SQ01`,
`282267X506X5541SQ02`,
`282267X506X5541SQ03`,
`282267X506X5541SQ04`,
`282267X506X5550SQ01`,
`282267X506X5550SQ02`,
`282267X506X5550SQ03`,
`282267X506X5550SQ04`,
`282267X506X5546SQ01#0`,
`282267X506X5546SQ01#1`,
`282267X506X5546SQ02#0`,
`282267X506X5546SQ02#1`,
`282267X506X5546SQ03#0`,
`282267X506X5546SQ03#1`,
`282267X506X5546SQ04#0`,
`282267X506X5546SQ04#1`,
`282267X506X5569SQ01#0`,
`282267X506X5569SQ01#1`,
`282267X506X5569SQ02#0`,
`282267X506X5569SQ02#1`,
`282267X506X5569SQ03#0`,
`282267X506X5569SQ03#1`,
`282267X506X5569SQ04#0`,
`282267X506X5569SQ04#1`,
`282267X507X5532SQ01`,
`282267X507X5532SQ02`,
`282267X507X5532SQ03`,
`282267X507X5532SQ04`,
`282267X507X5532other`,
`282267X507X5533SQ01`,
`282267X507X5533SQ02`,
`282267X507X5533SQ03`,
`282267X507X5533SQ04`,
`282267X507X5533SQ05`,
`282267X507X5534SQ001comment`,
`282267X507X5534SQ002comment`,
`282267X507X5534SQ003comment`,
`282267X507X5534SQ004comment`,
`282267X507X5552`,
`282267X507X5553`,
`282267X507X55371`,
`282267X507X55372`,
`282267X507X55373`,
`282267X507X55374`,
`282267X507X55511`,
`282267X507X55512`,
`282267X507X55513`,
`282267X507X55514`,
`282267X508X5554`,
`282267X508X5555`,
`282267X508X5556`,
`282267X508X5557`,
`282267X508X5566`,
`282267X508X5567`,
`282267X508X5568`,
`282267X508X5558SH101`,
`282267X508X5558SH102`,
`282267X508X5559SQ201`,
`282267X508X5559SQ202`,
`282267X508X5562`,
`282267X508X5560`,
`282267X508X5561`,
`282267X509X5563SQ01`,
`282267X509X5563SQ02`,
`282267X509X5563SQ03`,
`282267X509X5563SQ04`,
`282267X509X5564SQ01`,
`282267X509X5564SQ02`,
`282267X509X5564SQ03`,
`282267X509X5564SQ04`,
`282267X509X5565SQ01`,
`282267X509X5565SQ02`,
`282267X509X5565SQ03`,
`282267X509X5565SQ04`,
`startdate`,
`datestamp`,
`lastpage`,
`startlanguage`,
`seed`) VALUES (:282267X506X5530_0,
:282267X506X5531_0,
:282267X507X5534SQ001_0,
:282267X507X5534SQ002_0,
:282267X507X5534SQ003_0,
:282267X507X5534SQ004_0,
:282267X504X5513_0,
:282267X504X5514_0,
:282267X504X5515_0,
:282267X504X5516_0,
:282267X504X5517_0,
:282267X504X5518SQ01_0,
:282267X504X5518SQ02_0,
:282267X504X5518SQ03_0,
:282267X504X5518SQ04_0,
:282267X504X5519SQ01_0,
:282267X504X5519SQ02_0,
:282267X504X5519SQ03_0,
:282267X504X5519SQ04_0,
:282267X504X5520SQ01_0,
:282267X504X5520SQ02_0,
:282267X504X5520SQ03_0,
:282267X504X5520SQ04_0,
:282267X505X5521_0,
:282267X505X5522SQ01_0,
:282267X505X5522SQ02_0,
:282267X505X5522SQ03_0,
:282267X505X5522SQ04_0,
:282267X505X5523SQ001_0,
:282267X505X5523SQ002_0,
:282267X505X5523SQ003_0,
:282267X505X5524SQ01_0,
:282267X505X5524SQ02_0,
:282267X505X5524SQ03_0,
:282267X505X5524SQ04_0,
:282267X506X5525_0,
:282267X506X5526_0,
:282267X506X5527_0,
:282267X506X5528_0,
:282267X506X5549_0,
:282267X506X5529_0,
:282267X506X5530other_0,
:282267X506X5531comment_0,
:282267X506X5535_0,
:282267X506X5536_0,
:282267X506X5542_0,
:282267X506X5538SQ01_0,
:282267X506X5538SQ02_0,
:282267X506X5538SQ03_0,
:282267X506X5538SQ04_0,
:282267X506X5547SQ01_0,
:282267X506X5547SQ02_0,
:282267X506X5547SQ03_0,
:282267X506X5547SQ04_0,
:282267X506X5539SQ01_0,
:282267X506X5539SQ02_0,
:282267X506X5539SQ03_0,
:282267X506X5539SQ04_0,
:282267X506X5540SQ01_0,
:282267X506X5540SQ02_0,
:282267X506X5540SQ03_0,
:282267X506X5540SQ04_0,
:282267X506X5541SQ01_0,
:282267X506X5541SQ02_0,
:282267X506X5541SQ03_0,
:282267X506X5541SQ04_0,
:282267X506X5550SQ01_0,
:282267X506X5550SQ02_0,
:282267X506X5550SQ03_0,
:282267X506X5550SQ04_0,
:282267X506X5546SQ01#0_0,
:282267X506X5546SQ01#1_0,
:282267X506X5546SQ02#0_0,
:282267X506X5546SQ02#1_0,
:282267X506X5546SQ03#0_0,
:282267X506X5546SQ03#1_0,
:282267X506X5546SQ04#0_0,
:282267X506X5546SQ04#1_0,
:282267X506X5569SQ01#0_0,
:282267X506X5569SQ01#1_0,
:282267X506X5569SQ02#0_0,
:282267X506X5569SQ02#1_0,
:282267X506X5569SQ03#0_0,
:282267X506X5569SQ03#1_0,
:282267X506X5569SQ04#0_0,
:282267X506X5569SQ04#1_0,
:282267X507X5532SQ01_0,
:282267X507X5532SQ02_0,
:282267X507X5532SQ03_0,
:282267X507X5532SQ04_0,
:282267X507X5532other_0,
:282267X507X5533SQ01_0,
:282267X507X5533SQ02_0,
:282267X507X5533SQ03_0,
:282267X507X5533SQ04_0,
:282267X507X5533SQ05_0,
:282267X507X5534SQ001comment_0,
:282267X507X5534SQ002comment_0,
:282267X507X5534SQ003comment_0,
:282267X507X5534SQ004comment_0,
:282267X507X5552_0,
:282267X507X5553_0,
:282267X507X55371_0,
:282267X507X55372_0,
:282267X507X55373_0,
:282267X507X55374_0,
:282267X507X55511_0,
:282267X507X55512_0,
:282267X507X55513_0,
:282267X507X55514_0,
:282267X508X5554_0,
:282267X508X5555_0,
:282267X508X5556_0,
:282267X508X5557_0,
:282267X508X5566_0,
:282267X508X5567_0,
:282267X508X5568_0,
:282267X508X5558SH101_0,
:282267X508X5558SH102_0,
:282267X508X5559SQ201_0,
:282267X508X5559SQ202_0,
:282267X508X5562_0,
:282267X508X5560_0,
:282267X508X5561_0,
:282267X509X5563SQ01_0,
:282267X509X5563SQ02_0,
:282267X509X5563SQ03_0,
:282267X509X5563SQ04_0,
:282267X509X5564SQ01_0,
:282267X509X5564SQ02_0,
:282267X509X5564SQ03_0,
:282267X509X5564SQ04_0,
:282267X509X5565SQ01_0,
:282267X509X5565SQ02_0,
:282267X509X5565SQ03_0,
:282267X509X5565SQ04_0,
:startdate_0,
:datestamp_0,
:lastpage_0,
:startlanguage_0,
:seed_0),
(:282267X506X5530_1,
:282267X506X5531_1,
:282267X507X5534SQ001_1,
:282267X507X5534SQ002_1,
:282267X507X5534SQ003_1,
:282267X507X5534SQ004_1,
:282267X504X5513_1,
:282267X504X5514_1,
:282267X504X5515_1,
:282267X504X5516_1,
:282267X504X5517_1,
:282267X504X5518SQ01_1,
:282267X504X5518SQ02_1,
:282267X504X5518SQ03_1,
:282267X504X5518SQ04_1,
:282267X504X5519SQ01_1,
:282267X504X5519SQ02_1,
:282267X504X5519SQ03_1,
:282267X504X5519SQ04_1,
:282267X504X5520SQ01_1,
:282267X504X5520SQ02_1,
:282267X504X5520SQ03_1,
:282267X504X5520SQ04_1,
:282267X505X5521_1,
:282267X505X5522SQ01_1,
:282267X505X5522SQ02_1,
:282267X505X5522SQ03_1,
:282267X505X5522SQ04_1,
:282267X505X5523SQ001_1,
:282267X505X5523SQ002_1,
:282267X505X5523SQ003_1,
:282267X505X5524SQ01_1,
:282267X505X5524SQ02_1,
:282267X505X5524SQ03_1,
:282267X505X5524SQ04_1,
:282267X506X5525_1,
:282267X506X5526_1,
:282267X506X5527_1,
:282267X506X5528_1,
:282267X506X5549_1,
:282267X506X5529_1,
:282267X506X5530other_1,
:282267X506X5531comment_1,
:282267X506X5535_1,
:282267X506X5536_1,
:282267X506X5542_1,
:282267X506X5538SQ01_1,
:282267X506X5538SQ02_1,
:282267X506X5538SQ03_1,
:282267X506X5538SQ04_1,
:282267X506X5547SQ01_1,
:282267X506X5547SQ02_1,
:282267X506X5547SQ03_1,
:282267X506X5547SQ04_1,
:282267X506X5539SQ01_1,
:282267X506X5539SQ02_1,
:282267X506X5539SQ03_1,
:282267X506X5539SQ04_1,
:282267X506X5540SQ01_1,
:282267X506X5540SQ02_1,
:282267X506X5540SQ03_1,
:282267X506X5540SQ04_1,
:282267X506X5541SQ01_1,
:282267X506X5541SQ02_1,
:282267X506X5541SQ03_1,
:282267X506X5541SQ04_1,
:282267X506X5550SQ01_1,
:282267X506X5550SQ02_1,
:282267X506X5550SQ03_1,
:282267X506X5550SQ04_1,
:282267X506X5546SQ01#0_1,
:282267X506X5546SQ01#1_1,
:282267X506X5546SQ02#0_1,
:282267X506X5546SQ02#1_1,
:282267X506X5546SQ03#0_1,
:282267X506X5546SQ03#1_1,
:282267X506X5546SQ04#0_1,
:282267X506X5546SQ04#1_1,
:282267X506X5569SQ01#0_1,
:282267X506X5569SQ01#1_1,
:282267X506X5569SQ02#0_1,
:282267X506X5569SQ02#1_1,
:282267X506X5569SQ03#0_1,
:282267X506X5569SQ03#1_1,
:282267X506X5569SQ04#0_1,
:282267X506X5569SQ04#1_1,
:282267X507X5532SQ01_1,
:282267X507X5532SQ02_1,
:282267X507X5532SQ03_1,
:282267X507X5532SQ04_1,
:282267X507X5532other_1,
:282267X507X5533SQ01_1,
:282267X507X5533SQ02_1,
:282267X507X5533SQ03_1,
:282267X507X5533SQ04_1,
:282267X507X5533SQ05_1,
:282267X507X5534SQ001comment_1,
:282267X507X5534SQ002comment_1,
:282267X507X5534SQ003comment_1,
:282267X507X5534SQ004comment_1,
:282267X507X5552_1,
:282267X507X5553_1,
:282267X507X55371_1,
:282267X507X55372_1,
:282267X507X55373_1,
:282267X507X55374_1,
:282267X507X55511_1,
:282267X507X55512_1,
:282267X507X55513_1,
:282267X507X55514_1,
:282267X508X5554_1,
:282267X508X5555_1,
:282267X508X5556_1,
:282267X508X5557_1,
:282267X508X5566_1,
:282267X508X5567_1,
:282267X508X5568_1,
:282267X508X5558SH101_1,
:282267X508X5558SH102_1,
:282267X508X5559SQ201_1,
:282267X508X5559SQ202_1,
:282267X508X5562_1,
:282267X508X5560_1,
:282267X508X5561_1,
:282267X509X5563SQ01_1,
:282267X509X5563SQ02_1,
:282267X509X5563SQ03_1,
:282267X509X5563SQ04_1,
:282267X509X5564SQ01_1,
:282267X509X5564SQ02_1,
:282267X509X5564SQ03_1,
:282267X509X5564SQ04_1,
:282267X509X5565SQ01_1,
:282267X509X5565SQ02_1,
:282267X509X5565SQ03_1,
:282267X509X5565SQ04_1,
:startdate_1,
:datestamp_1,
:lastpage_1,
:startlanguage_1,
:seed_1)
and then the placeholders get some values from the archives. I have done some debugging and I can say with absolute certainty that the culprit is that the column names contain hashtags and those hashtags do not work well together with the placeholders (see PDO valid characters for placeholders).
Changing the column name format is not a solution we would like to have because there are many forks of the product and many downloads in the last two decades and it would be very cumbersome to 1. Refactor all references to column names that have hashtags 2. Lobby for all plugins to comply to it, even those that are outside our grasp, 3. Communicating this breaking change to people whom were using the product for years.
So, instead of changing the column format we will need to fix the way the batches are being inserted. If all else fails then I will find some other batch functionality or implement my own from scratch. However, I'm hesitating, because multiple RDBMS needs to be supported, such as MySQL, PostgreSQL, SQL Server, etc. etc. and while I have implemented raw queries for them, the team did not like that practice very much, so before doing it I aim to find a framework-way to do it. Internally this is what the function we have been using doing:
/**
* Creates a multiple INSERT command.
* This method could be used to achieve better performance during insertion of the large
* amount of data into the database tables.
* @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
* @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
* If a key is not a valid column name, the corresponding value will be ignored.
* @return CDbCommand multiple insert command
* @since 1.1.14
*/
public function createMultipleInsertCommand($table,array $data)
{
return $this->composeMultipleInsertCommand($table,$data);
}
and of course the function it calls:
/**
* Creates a multiple INSERT command.
* This method compose the SQL expression via given part templates, providing ability to adjust
* command for different SQL syntax.
* @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
* @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
* If a key is not a valid column name, the corresponding value will be ignored.
* @param array $templates templates for the SQL parts.
* @return CDbCommand multiple insert command
* @throws CDbException if $data is empty.
*/
protected function composeMultipleInsertCommand($table,array $data,array $templates=array())
{
if (empty($data))
throw new CDbException(Yii::t('yii','Can not generate multiple insert command with empty data set.'));
$templates=array_merge(
array(
'main'=>'INSERT INTO {{tableName}} ({{columnInsertNames}}) VALUES {{rowInsertValues}}',
'columnInsertValue'=>'{{value}}',
'columnInsertValueGlue'=>', ',
'rowInsertValue'=>'({{columnInsertValues}})',
'rowInsertValueGlue'=>', ',
'columnInsertNameGlue'=>', ',
),
$templates
);
$this->ensureTable($table);
$tableName=$table->rawName;
$params=array();
$columnInsertNames=array();
$rowInsertValues=array();
$columns=array();
foreach($data as $rowData)
{
foreach($rowData as $columnName=>$columnValue)
{
if(!in_array($columnName,$columns,true))
if($table->getColumn($columnName)!==null)
$columns[]=$columnName;
}
}
foreach($columns as $name)
$columnInsertNames[$name]=$this->getDbConnection()->quoteColumnName($name);
$columnInsertNamesSqlPart=implode($templates['columnInsertNameGlue'],$columnInsertNames);
foreach($data as $rowKey=>$rowData)
{
$columnInsertValues=array();
foreach($columns as $columnName)
{
$column=$table->getColumn($columnName);
$columnValue=array_key_exists($columnName,$rowData) ? $rowData[$columnName] : new CDbExpression('NULL');
if($columnValue instanceof CDbExpression)
{
$columnInsertValue=$columnValue->expression;
foreach($columnValue->params as $columnValueParamName=>$columnValueParam)
$params[$columnValueParamName]=$columnValueParam;
}
else
{
$columnInsertValue=':'.$columnName.'_'.$rowKey;
$params[':'.$columnName.'_'.$rowKey]=$column->typecast($columnValue);
}
$columnInsertValues[]=strtr($templates['columnInsertValue'],array(
'{{column}}'=>$columnInsertNames[$columnName],
'{{value}}'=>$columnInsertValue,
));
}
$rowInsertValues[]=strtr($templates['rowInsertValue'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{columnInsertValues}}'=>implode($templates['columnInsertValueGlue'],$columnInsertValues)
));
}
$sql=strtr($templates['main'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{rowInsertValues}}'=>implode($templates['rowInsertValueGlue'], $rowInsertValues),
));
$command=$this->getDbConnection()->createCommand($sql);
foreach($params as $name=>$value)
$command->bindValue($name,$value);
return $command;
}
So, as far as I know I need to pass the precise column name so the insert clause will identify it properly. And, at the same time I am getting the
CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined.
error because the parameterized query does not work well with # inside the placeholder. The thing is that the same key is identifying the column and therefore has to have # in its name, and, paradoxically, the placeholder is generated from that too.
I was even thinking of extending the class and implementing my own version:
class LSCDbCommandBuilder extends CdbCommandBuilder {
/**
* Creates a multiple INSERT command.
* This method compose the SQL expression via given part templates, providing ability to adjust
* command for different SQL syntax.
* @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
* @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
* If a key is not a valid column name, the corresponding value will be ignored.
* @param array $templates templates for the SQL parts.
* @return CDbCommand multiple insert command
* @throws CDbException if $data is empty.
*/
protected function composeMultipleInsertCommand($table,array $data,array $templates=array())
{
if (empty($data))
throw new CDbException(Yii::t('yii','Can not generate multiple insert command with empty data set.'));
$templates=array_merge(
array(
'main'=>'INSERT INTO {{tableName}} ({{columnInsertNames}}) VALUES {{rowInsertValues}}',
'columnInsertValue'=>'{{value}}',
'columnInsertValueGlue'=>', ',
'rowInsertValue'=>'({{columnInsertValues}})',
'rowInsertValueGlue'=>', ',
'columnInsertNameGlue'=>', ',
),
$templates
);
$this->ensureTable($table);
$tableName=$table->rawName;
$params=array();
$columnInsertNames=array();
$rowInsertValues=array();
$columns=array();
foreach($data as $rowData)
{
foreach($rowData as $columnName=>$columnValue)
{
if(!in_array($columnName,$columns,true))
if($table->getColumn($columnName)!==null)
$columns[]=$columnName;
}
}
foreach($columns as $name)
$columnInsertNames[$name]=$this->getDbConnection()->quoteColumnName($name);
$columnInsertNamesSqlPart=implode($templates['columnInsertNameGlue'],$columnInsertNames);
foreach($data as $rowKey=>$rowData)
{
$columnInsertValues=array();
foreach($columns as $columnName)
{
$placeholder = str_replace("#", "hashtag", $columnName);
$column=$table->getColumn($columnName);
$columnValue=array_key_exists($columnName,$rowData) ? $rowData[$columnName] : new CDbExpression('NULL');
if($columnValue instanceof CDbExpression)
{
$columnInsertValue=$columnValue->expression;
foreach($columnValue->params as $columnValueParamName=>$columnValueParam)
$params[$columnValueParamName]=$columnValueParam;
}
else
{
$columnInsertValue=':'.$placeholder.'_'.$rowKey;
$params[':'.$placeholder.'_'.$rowKey]=$column->typecast($columnValue);
}
$columnInsertValues[]=strtr($templates['columnInsertValue'],array(
'{{column}}'=>$columnInsertNames[$placeholder],
'{{value}}'=>$columnInsertValue,
));
}
$rowInsertValues[]=strtr($templates['rowInsertValue'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{columnInsertValues}}'=>implode($templates['columnInsertValueGlue'],$columnInsertValues)
));
}
$sql=strtr($templates['main'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{rowInsertValues}}'=>implode($templates['rowInsertValueGlue'], $rowInsertValues),
));
$command=$this->getDbConnection()->createCommand($sql);
foreach($params as $name=>$value)
$command->bindValue($name,$value);
return $command;
}
}
but I wonder whether there is another way which would not require me to implement raw queries or extend the class just to override this method.
I have implemented a solution along the ideas outlined in the question. Extending the class and handling the placeholder:
<?php
namespace LimeSurvey\Models\Services;
class LSCDbCommandBuilder extends \CDbCommandBuilder
{
/**
* Creates a multiple INSERT command.
* This method compose the SQL expression via given part templates, providing ability to adjust
* command for different SQL syntax.
* @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
* @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
* If a key is not a valid column name, the corresponding value will be ignored.
* @param array $templates templates for the SQL parts.
* @return \CDbCommand multiple insert command
* @throws \CDbException if $data is empty.
*/
protected function composeMultipleInsertCommand($table,array $data,array $templates=array())
{
if (empty($data))
throw new \CDbException(\Yii::t('yii','Can not generate multiple insert command with empty data set.'));
$templates=array_merge(
array(
'main'=>'INSERT INTO {{tableName}} ({{columnInsertNames}}) VALUES {{rowInsertValues}}',
'columnInsertValue'=>'{{value}}',
'columnInsertValueGlue'=>', ',
'rowInsertValue'=>'({{columnInsertValues}})',
'rowInsertValueGlue'=>', ',
'columnInsertNameGlue'=>', ',
),
$templates
);
$this->ensureTable($table);
$tableName=$table->rawName;
$params=array();
$columnInsertNames=array();
$rowInsertValues=array();
$columns=array();
foreach($data as $rowData)
{
foreach($rowData as $columnName=>$columnValue)
{
if(!in_array($columnName,$columns,true))
if($table->getColumn($columnName)!==null)
$columns[]=$columnName;
}
}
foreach($columns as $name)
$columnInsertNames[$name]=$this->getDbConnection()->quoteColumnName($name);
$columnInsertNamesSqlPart=implode($templates['columnInsertNameGlue'],$columnInsertNames);
foreach($data as $rowKey=>$rowData)
{
$columnInsertValues=array();
foreach($columns as $columnName)
{
$placeholder = str_replace("#", "hashtag", $columnName);
$column=$table->getColumn($columnName);
$columnValue=array_key_exists($columnName,$rowData) ? $rowData[$columnName] : new \CDbExpression('NULL');
if($columnValue instanceof \CDbExpression)
{
$columnInsertValue=$columnValue->expression;
foreach($columnValue->params as $columnValueParamName=>$columnValueParam)
$params[$columnValueParamName]=$columnValueParam;
}
else
{
$columnInsertValue=':'.$placeholder.'_'.$rowKey;
$params[':'.$placeholder.'_'.$rowKey]=$column->typecast($columnValue);
}
$columnInsertValues[]=strtr($templates['columnInsertValue'],array(
'{{column}}'=>$columnInsertNames[$columnName],
'{{value}}'=>$columnInsertValue,
));
}
$rowInsertValues[]=strtr($templates['rowInsertValue'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{columnInsertValues}}'=>implode($templates['columnInsertValueGlue'],$columnInsertValues)
));
}
$sql=strtr($templates['main'],array(
'{{tableName}}'=>$tableName,
'{{columnInsertNames}}'=>$columnInsertNamesSqlPart,
'{{rowInsertValues}}'=>implode($templates['rowInsertValueGlue'], $rowInsertValues),
));
$command=$this->getDbConnection()->createCommand($sql);
foreach($params as $name=>$value)
$command->bindValue($name,$value);
return $command;
}
}
Usage:
use LimeSurvey\Models\Services\LSCDbCommandBuilder;
//...
/**
* Recovers archived survey responses
*
* @param int $surveyId survey ID
* @param string $archivedResponseTableName archived response table name to be imported
* @param bool $preserveIDs if archived response IDs should be preserved
* @param array $validatedColumns the columns that are validated and can be inserted again
* @return integer number of rows affected by the execution.
* @throws Exception execution failed
*/
function recoverSurveyResponses(int $surveyId, string $archivedResponseTableName, $preserveIDs, array $validatedColumns = []): int
{
//...
$builder = new LSCDbCommandBuilder(App()->db->getSchema());
$command = $builder->createMultipleInsertCommand($tableName, $batchData);
//...
}
If a better answer is given at some point I will be happy to accept it instead of mine.