Skip to content

Commit

Permalink
Improve parsing in QgsDataSourceURI (adapted from PQconnectdb) and ga…
Browse files Browse the repository at this point in the history 
…ther

connection info parsing and creation there.   Fixes #817.

Please test!



git-svn-id: http://svn.osgeo.org/qgis/trunk/qgis@7672 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
jef committed Nov 27, 2007
1 parent ba3e5cd commit 3e0b7e4
Show file tree
Hide file tree
Showing 11 changed files with 418 additions and 395 deletions.
39 changes: 14 additions & 25 deletions python/core/qgsdatasourceuri.sip
View file
Expand Up @@ -19,8 +19,14 @@ public:
//! constructor which parses input URI
QgsDataSourceURI(QString uri);

//! All in a single string
QString text() const;
//! connection info
QString connInfo() const;

//! complete uri
QString uri() const;

//! quoted table name
QString quotedTablename() const;

//! Set all connection related members at once
void setConnection(const QString& aHost,
Expand All @@ -35,28 +41,11 @@ public:
const QString& aGeometryColumn,
const QString& aSql = QString());

/* data */
QString username() const;
QString schema() const;
QString table() const;
QString sql() const;
QString geometryColumn() const;

//! host name
QString host;
//! database name
QString database;
//! port the database server listens on
QString port;
//! schema
QString schema;
//! spatial table
QString table;
//! geometry column
QString geometryColumn;
//! SQL where clause used to limit features returned from the layer
QString sql;
//! username
QString username;
//! password
QString password;

//! whole connection info (host, db, port, name, pass)
QString connInfo;
void setSql(QString sql);
};

38 changes: 17 additions & 21 deletions src/app/qgsdbsourceselect.cpp
View file
Expand Up @@ -25,6 +25,7 @@ email : sherman at mrcc.com
#include "qgscontexthelp.h"
#include "qgsnewconnection.h"
#include "qgspgquerybuilder.h"
#include "qgsdatasourceuri.h"

#include <QInputDialog>
#include <QMessageBox>
Expand Down Expand Up @@ -351,45 +352,40 @@ void QgsDbSourceSelect::on_btnConnect_clicked()
// populate the table list
QSettings settings;

bool makeConnection = true;
QString key = "/PostgreSQL/connections/" + cmbConnections->currentText();
QString connString = "host=";
QString host = settings.readEntry(key + "/host");
connString += host;
connString += " dbname=";

QString database = settings.readEntry(key + "/database");
connString += database + " port=";
QString port = settings.readEntry(key + "/port");
if(port.length() == 0)
{
port = "5432";
}
connString += port + " user=";
QString username = settings.readEntry(key + "/username");
connString += username;
QString password = settings.readEntry(key + "/password");
bool searchPublicOnly = settings.readBoolEntry(key + "/publicOnly");
bool searchGeometryColumnsOnly = settings.readBoolEntry(key + "/geometryColumnsOnly");
bool makeConnection = true;

if (password == QString::null)
{
// get password from user
makeConnection = false;
QString password = QInputDialog::getText(tr("Password for ") + database + "@" + host,
QString password = QInputDialog::getText(tr("Password for ") + username,
tr("Please enter your password:"),
QLineEdit::Password, QString::null, &makeConnection, this);
// allow null password entry in case its valid for the database
}

QgsDataSourceURI uri;
uri.setConnection( settings.readEntry(key + "/host"),
settings.readEntry(key + "/port"),
database,
settings.readEntry(key + "/username"),
password );

bool searchPublicOnly = settings.readBoolEntry(key + "/publicOnly");
bool searchGeometryColumnsOnly = settings.readBoolEntry(key + "/geometryColumnsOnly");

// Need to escape the password to allow for single quotes and backslashes
password.replace('\\', "\\\\");
password.replace('\'', "\\'");
connString += " password='" + password + "'";

QgsDebugMsg("Connection info: " + connString);
QgsDebugMsg("Connection info: " + uri.connInfo());

if (makeConnection)
{
m_connInfo = connString; //host + " " + database + " " + username + " " + password;
m_connInfo = uri.connInfo();
//qDebug(m_connInfo);
// Tidy up an existing connection if one exists.
if (pd != 0)
Expand Down
78 changes: 36 additions & 42 deletions src/app/qgsnewconnection.cpp
View file
Expand Up @@ -22,6 +22,9 @@

#include "qgsnewconnection.h"
#include "qgscontexthelp.h"
#include "qgsdatasourceuri.h"
#include "qgslogger.h"

extern "C"
{
#include <libpq-fe.h>
Expand All @@ -31,38 +34,38 @@ QgsNewConnection::QgsNewConnection(QWidget *parent, const QString& connName, Qt:
{
setupUi(this);
if (!connName.isEmpty())
{
// populate the dialog with the information stored for the connection
// populate the fields with the stored setting parameters
QSettings settings;
{
// populate the dialog with the information stored for the connection
// populate the fields with the stored setting parameters
QSettings settings;

QString key = "/PostgreSQL/connections/" + connName;
txtHost->setText(settings.readEntry(key + "/host"));
txtDatabase->setText(settings.readEntry(key + "/database"));
QString port = settings.readEntry(key + "/port");
if(port.length() ==0){
port = "5432";
}
txtPort->setText(port);
txtUsername->setText(settings.readEntry(key + "/username"));
Qt::CheckState s = Qt::Checked;
if ( ! settings.readBoolEntry(key + "/publicOnly", false))
s = Qt::Unchecked;
cb_publicSchemaOnly->setCheckState(s);
s = Qt::Checked;
if ( ! settings.readBoolEntry(key + "/geometrycolumnsOnly", false))
s = Qt::Unchecked;
cb_geometryColumnsOnly->setCheckState(s);
// Ensure that cb_plublicSchemaOnly is set correctly
on_cb_geometryColumnsOnly_clicked();
QString key = "/PostgreSQL/connections/" + connName;
txtHost->setText(settings.readEntry(key + "/host"));
txtDatabase->setText(settings.readEntry(key + "/database"));
QString port = settings.readEntry(key + "/port");
if(port.length() ==0){
port = "5432";
}
txtPort->setText(port);
txtUsername->setText(settings.readEntry(key + "/username"));
Qt::CheckState s = Qt::Checked;
if ( ! settings.readBoolEntry(key + "/publicOnly", false))
s = Qt::Unchecked;
cb_publicSchemaOnly->setCheckState(s);
s = Qt::Checked;
if ( ! settings.readBoolEntry(key + "/geometrycolumnsOnly", false))
s = Qt::Unchecked;
cb_geometryColumnsOnly->setCheckState(s);
// Ensure that cb_plublicSchemaOnly is set correctly
on_cb_geometryColumnsOnly_clicked();

if (settings.readEntry(key + "/save") == "true")
{
txtPassword->setText(settings.readEntry(key + "/password"));
chkStorePassword->setChecked(true);
}
txtName->setText(connName);
if (settings.readEntry(key + "/save") == "true")
{
txtPassword->setText(settings.readEntry(key + "/password"));
chkStorePassword->setChecked(true);
}
txtName->setText(connName);
}
}
/** Autoconnected SLOTS **/
void QgsNewConnection::on_btnOk_clicked()
Expand Down Expand Up @@ -96,21 +99,12 @@ QgsNewConnection::~QgsNewConnection()
}
void QgsNewConnection::testConnection()
{
// following line uses Qt SQL plugin - currently not used
// QSqlDatabase *testCon = QSqlDatabase::addDatabase("QPSQL7","testconnection");
QgsDataSourceURI uri;
uri.setConnection( txtHost->text(), txtPort->text(), txtDatabase->text(), txtUsername->text(), txtPassword->text() );

// Need to escape the password to allow for single quotes and backslashes
QString password = txtPassword->text();
password.replace('\\', "\\\\");
password.replace('\'', "\\'");
QgsLogger::debug( "PQconnectdb(" + uri.connInfo() + ");" );

QString connInfo =
"host=" + txtHost->text() +
" dbname=" + txtDatabase->text() +
" port=" + txtPort->text() +
" user=" + txtUsername->text() +
" password='" + password + "'";
PGconn *pd = PQconnectdb(connInfo.toLocal8Bit().data());
PGconn *pd = PQconnectdb( uri.connInfo().toLocal8Bit().data() );
// std::cout << pd->ErrorMessage();
if (PQstatus(pd) == CONNECTION_OK)
{
Expand Down
64 changes: 15 additions & 49 deletions src/app/qgspgquerybuilder.cpp
View file
Expand Up @@ -34,28 +34,21 @@ QgsPgQueryBuilder::QgsPgQueryBuilder(QgsDataSourceURI *uri,
setupUi(this);
// The query builder must make its own connection to the database when
// using this constructor
QString connInfo = QString("host=%1 dbname=%2 port=%3 user=%4 password=%5")
.arg(mUri->host)
.arg(mUri->database)
.arg(mUri->port)
.arg(mUri->username)
.arg(mUri->password);
#ifdef QGISDEBUG
std::cerr << "Attempting connect using: " << connInfo.toLocal8Bit().data() << std::endl;
#endif
QString connInfo = mUri->connInfo();

QgsDebugMsg("Attempting connect using: " + connInfo);

mPgConnection = PQconnectdb(connInfo.toLocal8Bit().data());
// check the connection status
if (PQstatus(mPgConnection) == CONNECTION_OK) {
QString datasource = QString(tr("Table <b>%1</b> in database <b>%2</b> on host <b>%3</b>, user <b>%4</b>"))
.arg(mUri->table)
.arg(mUri->table() )
.arg(PQdb(mPgConnection))
.arg(PQhost(mPgConnection))
.arg(PQuser(mPgConnection));
mOwnConnection = true; // we own this connection since we created it
// tell the DB that we want text encoded in UTF8
PQsetClientEncoding(mPgConnection, "UNICODE");
// and strip any quotation as this code does it's own quoting.
trimQuotation();

lblDataUri->setText(datasource);
populateFields();
Expand All @@ -77,22 +70,12 @@ QgsPgQueryBuilder::QgsPgQueryBuilder(QString tableName, PGconn *con,
{
setupUi(this);
mOwnConnection = false; // we don't own this connection since it was passed to us
mUri = new QgsDataSourceURI();
mUri = new QgsDataSourceURI( "table=" + tableName);
QString datasource = QString(tr("Table <b>%1</b> in database <b>%2</b> on host <b>%3</b>, user <b>%4</b>"))
.arg(tableName)
.arg(PQdb(mPgConnection))
.arg(PQhost(mPgConnection))
.arg(PQuser(mPgConnection));
// populate minimum uri fields needed for the populate fields function
QRegExp reg("\"(.+)\"\\.\"(.+)\"");
reg.indexIn(tableName);
QStringList parts = reg.capturedTexts(); // table name contains table and schema
mUri->schema = parts[1];
// strip whitespace to make sure the table name is clean
mUri->table = parts[2];

// and strip any quotation as this code does it's own quoting.
trimQuotation();

lblDataUri->setText(datasource);
populateFields();
Expand All @@ -109,7 +92,7 @@ void QgsPgQueryBuilder::populateFields()
{
// Populate the field vector for this layer. The field vector contains
// field name, type, length, and precision (if numeric)
QString sql = "select * from \"" + mUri->schema + "\".\"" + mUri->table + "\" limit 1";
QString sql = "select * from " + mUri->quotedTablename() + " limit 1";
PGresult *result = PQexec(mPgConnection, (const char *) (sql.utf8()));
QgsLogger::debug("Query executed: " + sql);
if (PQresultStatus(result) == PGRES_TUPLES_OK)
Expand Down Expand Up @@ -152,38 +135,22 @@ void QgsPgQueryBuilder::populateFields()
mFieldMap[fieldName] = QgsField(fieldName, type, fieldType);
lstFields->insertItem(fieldName);
}
}else
}
else
{
#ifdef QGISDEBUG
std::cerr << "Error fetching a row from " << mUri->table.toLocal8Bit().data() << std::endl;
#endif
QgsDebugMsg( "Error fetching a row from " + mUri->table() );
}
PQclear(result);
}

void QgsPgQueryBuilder::trimQuotation()
{
// Trim " characters that may be surrounding the table and schema name
if (mUri->schema.at(0) == '"')
{
mUri->schema.remove(mUri->schema.length()-1, 1);
mUri->schema.remove(0, 1);
}
if (mUri->table.at(0) == '"')
{
mUri->table.remove(mUri->table.length()-1, 1);
mUri->table.remove(0, 1);
}
}

void QgsPgQueryBuilder::on_btnSampleValues_clicked()
{
if (lstFields->currentText().isEmpty())
return;

QString sql = "SELECT DISTINCT \"" + lstFields->currentText() + "\" " +
"FROM (SELECT \"" + lstFields->currentText() + "\" " +
"FROM \"" + mUri->schema + "\".\"" + mUri->table + "\" " +
"FROM " + mUri->quotedTablename() + " " +
"LIMIT 5000) AS foo " +
"ORDER BY \"" + lstFields->currentText() + "\" "+
"LIMIT 25";
Expand Down Expand Up @@ -224,7 +191,7 @@ void QgsPgQueryBuilder::on_btnGetAllValues_clicked()
return;

QString sql = "select distinct \"" + lstFields->currentText()
+ "\" from \"" + mUri->schema + "\".\"" + mUri->table + "\" order by \"" + lstFields->currentText() + "\"";
+ "\" from " + mUri->quotedTablename() + " order by \"" + lstFields->currentText() + "\"";
// clear the values list
lstValues->clear();
// determine the field type
Expand Down Expand Up @@ -272,8 +239,8 @@ void QgsPgQueryBuilder::on_btnTest_clicked()
else
{
QString numRows;
QString sql = "select count(*) from \"" + mUri->schema + "\".\"" + mUri->table
+ "\" where " + txtSQL->text();
QString sql = "select count(*) from " + mUri->quotedTablename()
+ " where " + txtSQL->text();
PGresult *result = PQexec(mPgConnection, (const char *)(sql.utf8()));
if (PQresultStatus(result) == PGRES_TUPLES_OK)
{
Expand All @@ -297,8 +264,7 @@ void QgsPgQueryBuilder::on_btnTest_clicked()
// XXX This should really throw an exception
long QgsPgQueryBuilder::countRecords(QString where)
{
QString sql = "select count(*) from \"" + mUri->schema + "\".\"" + mUri->table
+ "\" where " + where;
QString sql = "select count(*) from " + mUri->quotedTablename() + " where " + where;

long numRows;
PGresult *result = PQexec(mPgConnection, (const char *)(sql.utf8()));
Expand Down
5 changes: 0 additions & 5 deletions src/app/qgspgquerybuilder.h
View file
Expand Up @@ -119,11 +119,6 @@ class QgsPgQueryBuilder : public QDialog, private Ui::QgsPgQueryBuilderBase {
*/
void populateFields();

/*!
* Trims surround " characters from the schema and table name
*/
void trimQuotation();

/*! Get the number of records that would be returned by the current SQL
* @return Number of records or -1 if an error was encountered
*/
Expand Down

0 comments on commit 3e0b7e4

Please sign in to comment.