Ejemplo n.º 1
0
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;
}
Ejemplo n.º 2
0
void CSVAtlasWindow::sMapChanged( int )
{
  CSVMap map;
  if(!_selectedMap.isEmpty())
  {
    map = _atlas->map(_selectedMap);
    if(tr("Insert") == _action->currentText())
      map.setAction(CSVMap::Insert);
    else if(tr("Update") == _action->currentText())
      map.setAction(CSVMap::Update);
    else if(tr("Append") == _action->currentText())
      map.setAction(CSVMap::Append);
    map.setDescription(_description->toPlainText());
    map.setSqlPre(_preSql->toPlainText().trimmed());
    map.setSqlPreContinueOnError(_sqlPreContinueOnError->isChecked());
    map.setSqlPost(_postSql->toPlainText().trimmed());
    for(int r = 0; r < _fields->rowCount(); r++)
    {
      CSVMapField field = map.field(_fields->item(r, 1)->data(Qt::EditRole).toString());
      field.setName(_fields->item(r, 1)->data(Qt::EditRole).toString());

      if (qobject_cast<QCheckBox*>(_fields->cellWidget(r, 0)))
        field.setIsKey(qobject_cast<QCheckBox*>(_fields->cellWidget(r,0))->isChecked());
      else
        field.setIsKey(FALSE);

      field.setType(QVariant::nameToType(_fields->item(r, 2)->data(Qt::EditRole).toString().toAscii().data()));

      if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 4)))
        field.setAction(CSVMapField::nameToAction(qobject_cast<QComboBox*>(_fields->cellWidget(r, 4))->currentText()));
      else
        field.setAction(CSVMapField::Action_Default);

      if (qobject_cast<QSpinBox*>(_fields->cellWidget(r, 5)))
        field.setColumn(qobject_cast<QSpinBox*>(_fields->cellWidget(r,5))->value());
      else
        field.setColumn(0);

      if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 6)))
        field.setIfNullAction(CSVMapField::nameToIfNull(qobject_cast<QComboBox*>(_fields->cellWidget(r, 6))->currentText()));
      else
        field.setIfNullAction(CSVMapField::Nothing);

      if (qobject_cast<QSpinBox*>(_fields->cellWidget(r, 7)))
        field.setColumnAlt(qobject_cast<QSpinBox*>(_fields->cellWidget(r, 7))->value());
      else
        field.setColumnAlt(1);

      if (qobject_cast<QComboBox*>(_fields->cellWidget(r, 8)))
        field.setIfNullActionAlt(CSVMapField::nameToIfNull(qobject_cast<QComboBox*>(_fields->cellWidget(r, 8))->currentText()));
      else
        field.setIfNullActionAlt(CSVMapField::Nothing);

      field.setValueAlt(_fields->item(r, 9)->data(Qt::EditRole).toString());
      map.setField(field);
    }
    map.simplify();
    _atlas->setMap(map);
  }
 
  QSqlDatabase db = QSqlDatabase::database();
  if (db.isValid())
  {
    _fields->setRowCount(0);
    if(_map->count() && ! _map->currentText().isEmpty())
    {
      // CODE TO SELECT MAP
      _selectedMap = _map->currentText();
      map = _atlas->map(_selectedMap);

      _table->setTitle(tr("Table: ") + map.table());
      _table->setEnabled(TRUE);

      _action->setCurrentIndex(map.action());
      _description->setText(map.description());
      _preSql->setText(map.sqlPre());
      _sqlPreContinueOnError->setChecked(map.sqlPreContinueOnError());
      _postSql->setText(map.sqlPost());

      QSqlRecord record = db.record(map.table());
      QStringList fieldnames;
      if (record.isEmpty())
      {
        _msghandler->message(QtWarningMsg, tr("No Existing Table"),
                             tr("<p>The table %1 does not exist in this "
                                "database. You may continue to use and edit "
                                "this map but only those fields that are known "
                                "will be shown.").arg(map.table()),
                             QUrl(), QSourceLocation());
        fieldnames = map.fieldList();
      }
      else
      {
        QStringList fList = map.fieldList();

        for(int i = 0; i < fList.size(); ++i)
        {
          CSVMapField f = map.field(fList.at(i));
          if(!record.contains(fList.at(i)))
          {
            map.removeField(fList.at(i));
            MissingField diag(this, f.name(), record);
            if(diag.exec() == QDialog::Accepted)
            {
              f.setName(diag._fields->currentText());
              map.setField(f);
            }
            _atlas->setMap(map);
          }
        }

        for (int i = 0; i < record.count(); i++)
          fieldnames.append(record.fieldName(i));
      }

      _fields->setRowCount(fieldnames.size());
      for(int row = 0; row < fieldnames.size(); ++row)
      {
        CSVMapField mf = map.field(fieldnames.at(row));

        QCheckBox *check = new QCheckBox(_fields);
        if(!mf.isEmpty())
          check->setChecked(mf.isKey());
        _fields->setCellWidget(row, 0, check);

        _fields->setItem(row, 1, new QTableWidgetItem(fieldnames.at(row)));
        if (record.isEmpty())
        {
          _fields->setItem(row, 2,
                           new QTableWidgetItem(QVariant::typeToName(mf.type())));
          _fields->setItem(row, 3, new QTableWidgetItem(tr("Unknown")));
        }
        else
        {
          _fields->setItem(row, 2,
                           new QTableWidgetItem(QVariant::typeToName(record.field(row).type())));
          _fields->setItem(row, 3, new QTableWidgetItem(
                           (record.field(row).requiredStatus() == QSqlField::Required) ? tr("Yes") :
                           (record.field(row).requiredStatus() == QSqlField::Optional) ? tr("No")  :
                           tr("Unknown")));
        }

        QComboBox *actcombo = new QComboBox(_fields);
        actcombo->addItems(CSVMapField::actionList());
        if (! mf.isEmpty())
          actcombo->setCurrentIndex(mf.action());
        _fields->setCellWidget(row, 4, actcombo);

        QSpinBox *colspinner = new QSpinBox(_fields);
        colspinner->setRange(1, 999);
        colspinner->setPrefix(tr("Column "));
        if(!mf.isEmpty())
          colspinner->setValue(mf.column());
        _fields->setCellWidget(row, 5, colspinner);

        QComboBox *nullcombo = new QComboBox(_fields);
        nullcombo->addItems(CSVMapField::ifNullList());
        if (! mf.isEmpty())
          nullcombo->setCurrentIndex(mf.ifNullAction());
        _fields->setCellWidget(row, 6, nullcombo);

        QSpinBox *altspinner = new QSpinBox(_fields);
        altspinner->setRange(1, 999);
        altspinner->setPrefix(tr("Column "));
        if (! mf.isEmpty())
          altspinner->setValue(mf.columnAlt());
        _fields->setCellWidget(row, 7, altspinner);

        QComboBox *altnullcombo = new QComboBox(_fields);
        altnullcombo->addItems(CSVMapField::ifNullList(TRUE));
        if (! mf.isEmpty())
          altnullcombo->setCurrentIndex(mf.ifNullActionAlt());
        _fields->setCellWidget(row, 8, altnullcombo);

        _fields->setItem(row, 9, new QTableWidgetItem(mf.valueAlt()));

        RowController *control = new RowController(_fields, row, colspinner);
        control->setAction(actcombo);
        control->setColumn(colspinner);
        control->setIfNull(nullcombo);
        control->setAltColumn(altspinner);
        control->setAltIfNull(altnullcombo);
        control->setAltValue(_fields->item(row, 9));
        control->finishSetup();
      }
    }
    else
    {
      _selectedMap = QString::null;
      _table->setTitle(tr("Table: "));
      _table->setEnabled(FALSE);
    }
  }
  else
    _msghandler->message(QtCriticalMsg, tr("No Database"),
                         tr("Could not get the database connection."));
}