27 #include "../../../common/StringUtils.h"
28 #include "../../../dataaccess/dataset/DataSet.h"
29 #include "../../../dataaccess/dataset/DataSetAdapter.h"
30 #include "../../../dataaccess/dataset/DataSetTypeConverter.h"
31 #include "../../../dataaccess/dataset/DataSetType.h"
32 #include "../../../dataaccess/dataset/ObjectIdSet.h"
33 #include "../../../dataaccess/datasource/DataSourceCapabilities.h"
34 #include "../../../dataaccess/datasource/DataSourceInfoManager.h"
35 #include "../../../dataaccess/datasource/DataSourceInfo.h"
36 #include "../../../dataaccess/datasource/DataSourceManager.h"
37 #include "../../../dataaccess/query/SQLDialect.h"
38 #include "../../../dataaccess/query/SQLFunctionEncoder.h"
39 #include "../../../dataaccess/utils/Utils.h"
40 #include "../../../datatype/NumericProperty.h"
41 #include "../../../datatype/SimpleProperty.h"
42 #include "../../../datatype/StringProperty.h"
43 #include "../../../geometry/GeometryProperty.h"
44 #include "../../../maptools/DataSetLayer.h"
45 #include "../datasource/selector/DataSourceSelectorDialog.h"
46 #include "../layer/utils/DataSet2Layer.h"
47 #include "../utils/ScopedCursor.h"
49 #include "ui_QueryDataSourceDialogForm.h"
53 #include <QFileDialog>
54 #include <QGridLayout>
55 #include <QMessageBox>
56 #include <QTextStream>
63 #include <boost/filesystem.hpp>
64 #include <boost/uuid/random_generator.hpp>
65 #include <boost/uuid/uuid_io.hpp>
71 m_ui(new
Ui::QueryDataSourceDialogForm)
75 m_ui->m_applyToolButton->setIcon(QIcon::fromTheme(
"media-playback-start-green"));
76 m_ui->m_clearToolButton->setIcon(QIcon::fromTheme(
"edit-clear"));
77 m_ui->m_applySelToolButton->setIcon(QIcon::fromTheme(
"pointer-selection"));
78 m_ui->m_targetDatasourceToolButton->setIcon(QIcon::fromTheme(
"datasource"));
79 m_ui->m_createLayerlToolButton->setIcon(QIcon::fromTheme(
"layer-new"));
82 m_ui->m_saveSqlToolButton->setIcon(QIcon::fromTheme(
"document-save-as"));
83 m_ui->m_openSqlToolButton->setIcon(QIcon::fromTheme(
"document-open"));
94 QGridLayout* displayGridLayout =
new QGridLayout(
m_ui->m_displayWidget);
95 displayGridLayout->setContentsMargins(0, 0, 0, 0);
101 connect(
m_ui->m_dataSetListWidget, SIGNAL(itemClicked(QListWidgetItem*)),
this, SLOT(
onDataSetItemClicked(QListWidgetItem*)));
117 m_ui->m_helpPushButton->setPageReference(
"widgets/query/query_datasource.html");
127 m_layerList = layerList;
129 if(m_ui->m_baseDataSetComboBox->count() > 0)
130 onBaseDataSetSelected(0);
135 m_appMapDisplay = appMapDisplay;
140 m_ui->m_dataSourceComboBox->clear();
152 m_ui->m_dataSourceComboBox->addItem(it->second->getTitle().c_str(), QVariant(it->second->getId().c_str()));
157 if(m_ui->m_dataSourceComboBox->count() != 0)
158 onDataSourceSelected(m_ui->m_dataSourceComboBox->currentIndex());
166 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"SELECT", Qt::blue));
167 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"FROM", Qt::blue));
168 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"WHERE", Qt::blue));
169 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"JOIN", Qt::blue));
170 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"INNER", Qt::blue));
171 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"LEFT", Qt::blue));
172 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"RIGHT", Qt::blue));
173 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"AS", Qt::blue));
174 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"GROUP", Qt::blue));
175 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"ORDER", Qt::blue));
176 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(
"BY", Qt::blue));
179 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
187 for(std::size_t t = 0; t < names.size(); ++t)
189 QString s = names[t].c_str();
190 QString sUpper = s.toUpper();
192 m_keyWords.insert(std::map<std::string, Qt::GlobalColor>::value_type(sUpper.toStdString(), Qt::red));
199 m_ui->m_baseDataSetComboBox->clear();
200 m_ui->m_dataSetListWidget->clear();
201 m_ui->m_attrDataSetListWidget->clear();
202 m_ui->m_pkTableComboBox->clear();
203 m_ui->m_pkAttrComboBox->clear();
205 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(index).toString().toStdString();
212 std::vector<std::string> dataSetNames;
215 for(std::size_t t = 0; t < dataSetNames.size(); ++t)
217 m_ui->m_baseDataSetComboBox->addItem(dataSetNames[t].c_str());
218 m_ui->m_dataSetListWidget->addItem(dataSetNames[t].c_str());
219 m_ui->m_pkTableComboBox->addItem(dataSetNames[t].c_str());
222 if(m_ui->m_baseDataSetComboBox->count() > 0)
223 onBaseDataSetSelected(0);
225 if (m_ui->m_pkTableComboBox->count() > 0)
226 onPkTableComboBoxSelected(0);
233 std::string dataSet = m_ui->m_baseDataSetComboBox->itemText(index).toStdString();
235 m_ui->m_layerComboBox->clear();
237 std::list<te::map::AbstractLayerPtr>::iterator it = m_layerList.begin();
239 while(it != m_layerList.end())
243 std::auto_ptr<te::da::DataSetType> dsType = l->getSchema();
247 if(dsLayer && dsType->getName() == dataSet)
248 m_ui->m_layerComboBox->addItem(l->getTitle().c_str(), QVariant::fromValue(l));
253 m_ui->m_pkTableComboBox->setCurrentIndex(index);
254 onPkTableComboBoxSelected(index);
259 m_ui->m_attrDataSetListWidget->clear();
264 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
266 std::string dataSetName = item->text().toStdString();
270 std::vector<te::dt::Property*> propVec = dsType->
getProperties();
272 for(std::size_t t = 0; t < propVec.size(); ++t)
274 m_ui->m_attrDataSetListWidget->addItem(propVec[t]->getName().c_str());
282 std::string dataSetName = m_ui->m_pkTableComboBox->itemText(index).toStdString();
284 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
288 std::vector<te::dt::Property*> propVec = dsType->
getProperties();
290 for (std::size_t t = 0; t < propVec.size(); ++t)
292 m_ui->m_pkAttrComboBox->addItem(propVec[t]->getName().c_str());
302 m_ui->m_sqlEditorTextEdit->setFocus();
304 if (m_ui->m_sqlEditorTextEdit->toPlainText().isEmpty())
309 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
313 std::string dataSetName = m_ui->m_baseDataSetComboBox->currentText().toStdString();
317 std::string sql =
"";
319 if(m_ui->m_sqlEditorTextEdit->textCursor().selectedText().isEmpty())
320 sql = m_ui->m_sqlEditorTextEdit->toPlainText().toStdString();
322 sql = m_ui->m_sqlEditorTextEdit->textCursor().selectedText().toStdString();
325 std::auto_ptr<te::da::DataSet> dataSet;
329 dataSet = ds->query(sql);
331 catch(
const std::exception& e)
333 m_dataSetDisplay->clear();
334 m_tableModel->setDataSet(0, ds->getEncoding());
336 std::string errorMessage =
"SQL Error: ";
337 errorMessage += e.what();
338 errorMessage +=
"\n";
339 errorMessage +=
"-------------------------------------------------------------------------\n";
341 m_ui->m_logPlainTextEdit->appendPlainText(errorMessage.c_str());
343 m_ui->m_tabWidget->setCurrentIndex(1);
348 std::string message =
"SQL Done: ";
351 message +=
"-------------------------------------------------------------------------\n";
353 m_ui->m_logPlainTextEdit->appendPlainText(message.c_str());
356 m_dataSetDisplay->clear();
359 for(std::size_t t = 0; t < dataSet->getNumProperties(); ++t)
361 int type = dataSet->getPropertyDataType(t);
371 m_dataSetDisplay->draw(dsType, ds, dataSet.get());
373 m_dataSetDisplay->clear();
376 m_tableModel->setDataSet(dataSet.release(), ds->getEncoding());
378 m_ui->m_tabWidget->setCurrentIndex(0);
383 m_ui->m_sqlEditorTextEdit->clear();
385 m_dataSetDisplay->clear();
392 disconnect(m_ui->m_sqlEditorTextEdit, SIGNAL(textChanged()),
this, SLOT(onSQLEditorTextChanged()));
394 QString sql = m_ui->m_sqlEditorTextEdit->toPlainText();
396 int curPos = m_ui->m_sqlEditorTextEdit->textCursor().position();
398 disconnect(m_ui->m_sqlEditorTextEdit, SIGNAL(textChanged()),
this, SLOT(onSQLEditorTextChanged()));
400 m_ui->m_sqlEditorTextEdit->clear();
402 QStringList words = sql.split(
' ');
404 bool isAttrValue =
false;
406 for(
int i = 0; i < words.size(); ++i)
408 QString w = words.value(i).toUpper();
410 std::string strW = w.toStdString();
412 std::map<std::string, Qt::GlobalColor>::iterator it = m_keyWords.find(strW);
414 bool removeAttrValue =
false;
416 if(it != m_keyWords.end())
418 m_ui->m_sqlEditorTextEdit->setFontWeight(
QFont::Bold);
419 m_ui->m_sqlEditorTextEdit->setTextColor(it->second);
425 words.value(i).toDouble(&okNum);
430 m_ui->m_sqlEditorTextEdit->setTextColor(Qt::darkGreen);
435 std::string initChar = strW.substr(0, 1);
436 if(!isAttrValue && (initChar ==
"'" || initChar ==
"\""))
441 std::string lastChar = strW.substr(strW.size() - 1, 1);
442 if(isAttrValue && (lastChar ==
"'" || lastChar ==
"\""))
443 removeAttrValue =
true;
448 m_ui->m_sqlEditorTextEdit->setTextColor(Qt::magenta);
450 m_ui->m_sqlEditorTextEdit->setTextColor(Qt::black);
454 m_ui->m_sqlEditorTextEdit->insertPlainText(words.value(i));
459 if(i < words.size() - 1)
460 m_ui->m_sqlEditorTextEdit->insertPlainText(
" ");
463 QTextCursor c = m_ui->m_sqlEditorTextEdit->textCursor();
464 c.setPosition(curPos);
465 m_ui->m_sqlEditorTextEdit->setTextCursor(c);
467 connect(m_ui->m_sqlEditorTextEdit, SIGNAL(textChanged()),
this, SLOT(onSQLEditorTextChanged()));
473 QString path = QFileDialog::getSaveFileName(
this, tr(
"Set a SQL file..."),
"", tr(
"SQL File *.sql"));
481 if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
483 QMessageBox::warning(
this, tr(
"Query DataSource"), file.errorString());
488 QTextStream out(&file);
490 out << m_ui->m_sqlEditorTextEdit->toPlainText();
498 QString path = QFileDialog::getOpenFileName(
this, tr(
"Select a SQL file..."),
"", tr(
"SQL File *.sql"));
506 if(!file.open(QIODevice::ReadOnly))
508 QMessageBox::warning(
this, tr(
"Query DataSource"), file.errorString());
513 QTextStream in(&file);
515 m_ui->m_sqlEditorTextEdit->clear();
519 QString line = in.readLine();
521 m_ui->m_sqlEditorTextEdit->append(line);
529 if(m_ui->m_sqlEditorTextEdit->toPlainText().isEmpty())
531 QMessageBox::information(
this, tr(
"Warning"), tr(
"SQL not defined."));
535 QVariant varLayer = m_ui->m_layerComboBox->itemData(m_ui->m_layerComboBox->currentIndex(), Qt::UserRole);
540 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"No layer selected."));
546 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
550 std::string dataSetName = m_ui->m_baseDataSetComboBox->currentText().toStdString();
554 std::string sql =
"";
556 if(m_ui->m_sqlEditorTextEdit->textCursor().selectedText().isEmpty())
557 sql = m_ui->m_sqlEditorTextEdit->toPlainText().toStdString();
559 sql = m_ui->m_sqlEditorTextEdit->textCursor().selectedText().toStdString();
562 std::auto_ptr<te::da::DataSet> dataSet;
566 dataSet = ds->query(sql);
570 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Error executing SQL."));
576 if(m_ui->m_newSelRadioButton->isChecked())
579 dataSet->moveBeforeFirst();
584 layer->clearSelected();
587 else if(m_ui->m_addSelRadioButton->isChecked())
590 dataSet->moveBeforeFirst();
600 m_appMapDisplay->refresh();
605 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Error selecting objects: ") + e.
what());
608 QMessageBox::information(
this, tr(
"Query DataSource"), tr(
"Selection done."));
615 if(m_ui->m_sqlEditorTextEdit->toPlainText().isEmpty())
617 QMessageBox::information(
this, tr(
"Warning"), tr(
"SQL not defined."));
621 if(m_ui->m_repositoryLineEdit->text().isEmpty())
623 QMessageBox::information(
this, tr(
"Warning"), tr(
"Define a repository for the result."));
627 if(m_ui->m_newLayerNameLineEdit->text().isEmpty())
629 QMessageBox::information(
this, tr(
"Warning"), tr(
"Define a name for the resulting layer."));
636 std::string dataSourceId = m_ui->m_dataSourceComboBox->itemData(m_ui->m_dataSourceComboBox->currentIndex()).toString().toStdString();
640 std::string inputDataSetName = m_ui->m_baseDataSetComboBox->currentText().toStdString();
644 std::string sql =
"";
646 if(m_ui->m_sqlEditorTextEdit->textCursor().selectedText().isEmpty())
647 sql = m_ui->m_sqlEditorTextEdit->toPlainText().toStdString();
649 sql = m_ui->m_sqlEditorTextEdit->textCursor().selectedText().toStdString();
651 std::auto_ptr<te::da::DataSet> dataSet;
655 dataSet = ds->query(sql);
659 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Error executing SQL."));
663 if (dataSet->size() == 0)
665 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Query result is empty."));
675 boost::filesystem::path uri(m_ui->m_repositoryLineEdit->text().toStdString());
677 std::map<std::string, std::string> dsInfo;
678 dsInfo[
"URI"] = uri.string();
680 boost::uuids::basic_random_generator<boost::mt19937> gen;
681 boost::uuids::uuid u = gen();
682 std::string id_ds = boost::uuids::to_string(u);
685 dsInfoPtr->setConnInfo(dsInfo);
686 dsInfoPtr->setTitle(uri.stem().string());
687 dsInfoPtr->setAccessDriver(
"OGR");
688 dsInfoPtr->setType(
"OGR");
689 dsInfoPtr->setDescription(uri.string());
690 dsInfoPtr->setId(id_ds);
702 std::string dataSetName = m_ui->m_newLayerNameLineEdit->text().toStdString();
704 std::size_t idx = dataSetName.find(
".");
705 if (idx != std::string::npos)
706 dataSetName=dataSetName.substr(0,idx);
711 dataSet->moveFirst();
713 std::set<std::string> names;
717 for(std::size_t t = 0; t < dataSet->getNumProperties(); ++t)
720 std::string propName = dataSet->getPropertyName(t);
723 while(names.find(propName) != names.end())
729 names.insert(propName);
748 std::auto_ptr<te::gm::Geometry> geom = dataSet->getGeometry(t);
750 srid = geom->getSRID();
761 if (m_ui->m_pkCheckBox->isChecked())
763 std::string pkAttrName = m_ui->m_pkAttrComboBox->itemText(m_ui->m_pkAttrComboBox->currentIndex()).toStdString();
767 std::string pkName = dataSetName +
"_" + p->
getName() +
"_pk";
775 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Error creating output dataset."));
780 dataSet->moveBeforeFirst();
790 std::map<std::string, std::string> options;
792 outputDataSource->createDataSet(dsType.get(), options);
794 outputDataSource->add(dataSetName, dsAdapter.get(), options);
805 emit createNewLayer(layer);
809 QMessageBox::warning(
this, tr(
"Query DataSource"), tr(
"Error creating layer. ") + e.
what());
812 QMessageBox::information(
this, tr(
"Query DataSource"), tr(
"Layer created."));
817 m_ui->m_newLayerNameLineEdit->clear();
818 m_ui->m_newLayerNameLineEdit->setEnabled(
true);
823 std::list<te::da::DataSourceInfoPtr> dsPtrList = dlg.
getSelecteds();
825 if(dsPtrList.empty())
828 std::list<te::da::DataSourceInfoPtr>::iterator it = dsPtrList.begin();
830 m_ui->m_repositoryLineEdit->setText(QString(it->get()->getTitle().c_str()));
832 m_outputDatasource = *it;
839 m_ui->m_newLayerNameLineEdit->clear();
840 m_ui->m_repositoryLineEdit->clear();
842 QString fileName = QFileDialog::getSaveFileName(
this, tr(
"Save as..."), QString(), tr(
"Shapefile (*.shp *.SHP);;"),0, QFileDialog::DontConfirmOverwrite);
844 if (fileName.isEmpty())
847 boost::filesystem::path outfile(fileName.toStdString());
849 m_ui->m_repositoryLineEdit->setText(outfile.string().c_str());
851 m_ui->m_newLayerNameLineEdit->setText(outfile.leaf().string().c_str());
853 m_ui->m_newLayerNameLineEdit->setEnabled(
false);
TEDATAACCESSEXPORT DataSourcePtr GetDataSource(const std::string &datasourceId, const bool opened=true)
Search for a data source with the informed id in the DataSourceManager.
te::da::SQLDialect * dialect
void add(te::dt::Property *p)
It adds a property to the list of properties of the primary key.
boost::shared_ptr< DataSetType > DataSetTypePtr
An atomic property like an integer or double.
boost::shared_ptr< DataSource > DataSourcePtr
TEDATAACCESSEXPORT void AssociateDataSetTypeConverterSRID(DataSetTypeConverter *converter, const int &inputSRID, const int &outputSRID=TE_UNKNOWN_SRS)
A class that models the description of a dataset.
virtual const char * what() const
It outputs the exception message.
virtual Property * clone() const =0
It returns a clone of the object.
It represents the SQL query dialect accepted by a given data source.
std::string Convert2UCase(const std::string &value)
It converts a string to upper case.
It models a property definition.
An converter for DataSetType.
The type for arbitrary precison numbers, like numeric(p, q).
const std::vector< Property * > & getProperties() const
It returns the list of properties describing the CompositeProperty.
static DataSourceInfoManager & getInstance()
It returns a reference to the singleton instance.
This class represents a set of unique ids created in the same context. i.e. from the same data set...
void setExpressionByInClause(const std::string source="")
The type for string types: FIXED_STRING, VAR_STRING or STRING.
TEDATAACCESSEXPORT ObjectIdSet * GenerateOIDSet(DataSet *dataset, const DataSetType *type)
TEDATAACCESSEXPORT void GetDataSetNames(std::vector< std::string > &datasetNames, const std::string &datasourceId)
Q_DECLARE_METATYPE(te::map::AbstractLayerPtr)
This class is designed to declare objects to be thrown as exceptions by TerraLib. ...
It describes a primary key (pk) constraint.
A class that represents a data source component.
std::vector< std::string > getRegisteredNames() const
It gets the all registered names from registed functions.
TEDATAACCESSEXPORT DataSetType * GetDataSetType(const std::string &name, const std::string &datasourceId)
A layer with reference to a dataset.
std::string Convert2String(boost::int16_t value)
It converts a short integer value to a string.
TEDATAACCESSEXPORT DataSetAdapter * CreateAdapter(DataSet *ds, DataSetTypeConverter *converter, bool isOwner=false)
boost::intrusive_ptr< AbstractLayer > AbstractLayerPtr
This file defines a class for a Query DataSource Dialog Dialog.
boost::shared_ptr< DataSourceInfo > DataSourceInfoPtr
const std::string & getName() const
It returns the property name.