void CSVAtlas::setMap(const CSVMap & m)
{
for(int i = 0; i < _maps.count(); ++i)
{
if(_maps[i].name() == m.name())
{
_maps[i] = m;
return;
}
}
_maps.append(m);
}
bool CSVToolWindow::importStart()
{
QString mapname = atlasWindow()->map();
CSVAtlas *atlas = _atlasWindow->getAtlas();
if (mapname.isEmpty())
{
QStringList mList = atlas->mapList();
if(mList.isEmpty())
{
_msghandler->message(QtWarningMsg, tr("No Maps Loaded"),
tr("<p>There are no maps loaded to select from. "
"Either load an atlas that contains maps or "
"create a new one before continuing."));
return false;
}
mList.sort();
bool valid;
mapname = QInputDialog::getItem(this, tr("Select Map"), tr("Select Map:"),
mList, 0, false, &valid);
if (!valid)
return false;
}
CSVMap map = atlas->map(mapname);
map.simplify();
QList<CSVMapField> fields = map.fields();
if (map.name() != mapname || fields.isEmpty())
{
_msghandler->message(QtWarningMsg, tr("Invalid Map"),
tr("<p>The selected map does not appear to be valid."));
return false;
}
CSVMap::Action action = map.action();
if (action != CSVMap::Insert)
{
_msghandler->message(QtWarningMsg, tr("Action not implemented"),
tr("<p>The action %1 for this map is not supported.")
.arg(CSVMap::actionToName(action)));
return false;
}
if (!_data || _data->rows() < 1)
{
_msghandler->message(QtWarningMsg, tr("No data"),
tr("<p>There are no data to process. "
"Load a CSV file before continuing."));
return false;
}
int total = _data->rows();
int current = 0, error = 0, ignored = 0;
if (! _log)
_log = new LogWindow(this);
if(usetransaction) QSqlQuery begin("BEGIN;");
QString errMsg;
if(!map.sqlPre().trimmed().isEmpty())
{
if(usetransaction) QSqlQuery savepoint("SAVEPOINT presql;");
QSqlQuery pre;
if(!pre.exec(map.sqlPre()))
{
errMsg = QString("ERROR Running Pre SQL query: %1").arg(pre.lastError().text());
_log->_log->append("\n\n----------------------\n");
_log->_log->append(errMsg);
_log->show();
_log->raise();
if(map.sqlPreContinueOnError())
{
_log->_log->append(tr("\n\nContinuing with rest of import\n\n"));
if(usetransaction) QSqlQuery sprollback("ROLLBACK TO SAVEPOINT presql;");
if(usetransaction) QSqlQuery savepoint("RELEASE SAVEPOINT presql;");
}
else
{
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_msghandler->message(QtWarningMsg, tr("Error"),
tr("<p>There was an error running the Pre SQL "
"query. "
"Aborting transaction."
"\n\n----------------------\n%1").arg(errMsg));
return false;
}
}
}
QString progresstext(tr("Importing %1: %2 rows out of %3"));
int expected = total;
QProgressDialog *progress = new QProgressDialog(progresstext
.arg(map.name()).arg(0).arg(expected),
tr("Cancel"), 0, expected, this);
progress->setWindowModality(Qt::WindowModal);
bool userCanceled = false;
QString query;
QString front;
QString back;
QString value;
QString label;
QVariant var;
QStringList errorList;
for(current = 0; current < total; ++current)
{
if(usetransaction) QSqlQuery savepoint("SAVEPOINT csvinsert;");
if(action == CSVMap::Insert)
{
query = QString("INSERT INTO %1 ").arg(map.table());
front = "(";
back = " VALUES(";
QList<CSVMapField> fields = map.fields();
QMap<QString,QVariant> values;
for (int i = 0; i < fields.size(); i++)
{
switch(fields.at(i).action())
{
case CSVMapField::Action_UseColumn:
{
value = _data->value(current, fields.at(i).column()-1);
if(value.isNull())
{
switch (fields.at(i).ifNullAction())
{
case CSVMapField::UseDefault:
continue;
case CSVMapField::UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
case CSVMapField::UseAlternateColumn:
{
value = _data->value(current, fields.at(i).columnAlt()-1);
if(value.isNull())
{
switch (fields.at(i).ifNullActionAlt())
{
case CSVMapField::UseDefault:
continue;
case CSVMapField::UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
default: // Nothing
var = QVariant(QString::null);
}
}
else
var = QVariant(value);
break;
}
default: // Nothing
var = QVariant(QString::null);
}
}
else
var = QVariant(value);
break;
}
case CSVMapField::Action_UseEmptyString:
{
var = QVariant(QString(""));
break;
}
case CSVMapField::Action_UseAlternateValue:
{
var = QVariant(fields.at(i).valueAlt());
break;
}
case CSVMapField::Action_UseNull:
{
var = QVariant(QString::null);
break;
}
default:
continue;
}
label = ":" + fields.at(i).name();
if(!values.empty())
{
front += ", ";
back += ", ";
}
values.insert(label, var);
front += fields.at(i).name();
back += label;
}
if(values.empty())
{
ignored++;
errMsg = QString("IGNORED Record %1: There are no columns to insert").arg(current+1);
errorList.append(errMsg);
continue;
}
front += ") ";
back += ")";
query += front + back;
QSqlQuery qry;
qry.prepare(query);
QMap<QString,QVariant>::iterator vit;
for(vit = values.begin(); vit != values.end(); ++vit)
qry.bindValue(vit.key(), vit.value());
if(!qry.exec())
{
if(usetransaction) QSqlQuery sprollback("ROLLBACK TO SAVEPOINT csvinsert;");
error++;
errMsg = QString("ERROR Record %1: %2").arg(current+1).arg(qry.lastError().text());
errorList.append(errMsg);
}
}
if (progress->wasCanceled())
{
userCanceled = true;
break;
}
if(! (current % 1000))
{
progress->setLabelText(progresstext.arg(map.name()).arg(current).arg(expected));
progress->setValue(current);
}
}
progress->setValue(total);
if (error || ignored || userCanceled)
{
_log->_log->append(tr("Map: %1\n"
"Table: %2\n"
"Method: %3\n\n"
"Total Records: %4\n"
"# Processed: %5\n"
"# Ignored: %6\n"
"# Errors: %7\n\n")
.arg(map.name()).arg(map.table())
.arg(CSVMap::actionToName(map.action()))
.arg(total).arg(current).arg(ignored).arg(error));
_log->_log->append(errMsg);
_log->_log->append(errorList.join("\n"));
_log->show();
_log->raise();
if (_msghandler && // log messages there's a non-interactive message handler
qobject_cast<XAbstractMessageHandler*>(_msghandler) &&
! qobject_cast<InteractiveMessageHandler*>(_msghandler))
_msghandler->message(error ? QtCriticalMsg : QtWarningMsg,
tr("Import Processing Status"),
_log->_log->toPlainText());
}
if (! userCanceled && ! map.sqlPost().trimmed().isEmpty())
{
QSqlQuery post;
if(!post.exec(map.sqlPost()))
{
errMsg = QString("ERROR Running Post SQL query: %1").arg(post.lastError().text());
_log->_log->append("\n\n----------------------\n");
_log->_log->append(errMsg);
_log->show();
_log->raise();
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_msghandler->message(QtCriticalMsg, tr("Error"),
tr("<p>There was an error running the post sql "
"query and changes were rolled back. "
"\n\n----------------------\n%1").arg(errMsg));
return false;
}
}
if (userCanceled)
{
if(usetransaction) QSqlQuery rollback("ROLLBACK;");
_log->_log->append(tr("\n\nImport canceled by user. Changes were rolled back."));
return false;
}
if(usetransaction) QSqlQuery commit("COMMIT");
if (! error)
{
_msghandler->message(QtDebugMsg, tr("Import Complete"),
tr("The import of %1 completed successfully.")
.arg(_currentDir));
return true;
}
return false;
}