KDb

PostgresqlConnection.cpp
1 /* This file is part of the KDE project
2  Copyright (C) 2003 Adam Pigg <[email protected]>
3  Copyright (C) 2010-2016 JarosÅ‚aw Staniek <[email protected]>
4 
5  This program is free software; you can redistribute it and/or
6  modify it under the terms of the GNU Library General Public
7  License as published by the Free Software Foundation; either
8  version 2 of the License, or (at your option) any later version.
9 
10  This program is distributed in the hope that it will be useful,
11  but WITHOUT ANY WARRANTY; without even the implied warranty of
12  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13  Library General Public License for more details.
14 
15  You should have received a copy of the GNU Library General Public License
16  along with this program; see the file COPYING. If not, write to
17  the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18  * Boston, MA 02110-1301, USA.
19 */
20 
21 #include "PostgresqlConnection.h"
22 #include "PostgresqlConnection_p.h"
23 #include "PostgresqlPreparedStatement.h"
24 #include "PostgresqlCursor.h"
25 #include "postgresql_debug.h"
26 #include "KDbConnectionData.h"
27 #include "KDbError.h"
28 #include "KDbGlobal.h"
29 #include "KDbVersionInfo.h"
30 
31 #include <QFileInfo>
32 #include <QHostAddress>
33 
34 #define MIN_SERVER_VERSION_MAJOR 7
35 #define MIN_SERVER_VERSION_MINOR 1
36 
37 inline static QByteArray parameter(PGconn *conn, const char *paramName)
38 {
39  return PQparameterStatus(conn, paramName);
40 }
41 
42 PostgresqlTransactionData::PostgresqlTransactionData(KDbConnection *conn)
43  : KDbTransactionData(conn)
44 {
45 }
46 
47 PostgresqlTransactionData::~PostgresqlTransactionData()
48 {
49 }
50 
51 //==================================================================================
52 
53 PostgresqlConnection::PostgresqlConnection(KDbDriver *driver, const KDbConnectionData& connData,
54  const KDbConnectionOptions &options)
55  : KDbConnection(driver, connData, options)
56  , d(new PostgresqlConnectionInternal(this))
57 {
58 }
59 
60 PostgresqlConnection::~PostgresqlConnection()
61 {
62  //delete m_trans;
63  destroy();
64  delete d;
65 }
66 
68 {
69  return new PostgresqlCursor(this, sql, options);
70 }
71 
73 {
74  return new PostgresqlCursor(this, query, options);
75 }
76 
77 bool PostgresqlConnection::drv_connect()
78 {
79  return true;
80 }
81 
82 bool PostgresqlConnection::drv_getServerVersion(KDbServerVersionInfo* version)
83 {
84  // https://www.postgresql.org/docs/8.4/static/libpq-status.html
85  //postgresqlDebug() << "server_version:" << d->parameter("server_version");
86  version->setString(QString::fromLatin1(parameter(d->conn, "server_version")));
87  const int versionNumber = PQserverVersion(d->conn);
88  if (versionNumber > 0) {
89  version->setMajor(versionNumber / 10000);
90  version->setMinor((versionNumber % 1000) / 100);
91  version->setRelease(versionNumber % 100);
92  }
93 
94  if ( version->major() < MIN_SERVER_VERSION_MAJOR
95  || (version->major() == MIN_SERVER_VERSION_MAJOR && version->minor() < MIN_SERVER_VERSION_MINOR))
96  {
97  postgresqlWarning()
98  << QString::fromLatin1("PostgreSQL %d.%d is not supported and may not work. The minimum is %d.%d")
99  .arg(version->major()).arg(version->minor()).arg(MIN_SERVER_VERSION_MAJOR).arg(MIN_SERVER_VERSION_MINOR);
100  }
101  return true;
102 }
103 
104 bool PostgresqlConnection::drv_disconnect()
105 {
106  return true;
107 }
108 
109 bool PostgresqlConnection::drv_getDatabasesList(QStringList* list)
110 {
111  return queryStringList(KDbEscapedString("SELECT datname FROM pg_database WHERE datallowconn = TRUE"), list);
112 }
113 
114 bool PostgresqlConnection::drv_createDatabase(const QString &dbName)
115 {
116  return executeSql(KDbEscapedString("CREATE DATABASE ") + escapeIdentifier(dbName));
117 }
118 
119 QByteArray buildConnParameter(const QByteArray& key, const QVariant& value)
120 {
121  QByteArray result = key;
122 //! @todo optimize
123  result.replace('\\', "\\\\").replace('\'', "\\'");
124  return key + "='" + value.toString().toUtf8() + "' ";
125 }
126 
127 bool PostgresqlConnection::drv_useDatabase(const QString &dbName, bool *cancelled,
128  KDbMessageHandler* msgHandler)
129 {
130  Q_UNUSED(cancelled);
131  Q_UNUSED(msgHandler);
132 
133  QByteArray conninfo;
134 
135  if (data().hostName().isEmpty()
136  || 0 == QString::compare(data().hostName(), QLatin1String("localhost"), Qt::CaseInsensitive))
137  {
138  if (!data().localSocketFileName().isEmpty()) {
139  QFileInfo fileInfo(data().localSocketFileName());
140  if (fileInfo.exists()) {
141  conninfo += buildConnParameter("host", fileInfo.absolutePath());
142  }
143  }
144  }
145  else {
146  const QHostAddress ip(data().hostName());
147  if (ip.isNull()) {
148  conninfo += buildConnParameter("host", data().hostName());
149  }
150  else {
151  conninfo += buildConnParameter("hostaddr", ip.toString());
152  }
153  }
154 
155  //Build up the connection string
156  if (data().port() > 0)
157  conninfo += buildConnParameter("port", data().port());
158 
159  QString myDbName = dbName;
160  if (myDbName.isEmpty())
161  myDbName = data().databaseName();
162  if (!myDbName.isEmpty())
163  conninfo += buildConnParameter("dbname", myDbName);
164 
165  if (!data().userName().isEmpty())
166  conninfo += buildConnParameter("user", data().userName());
167 
168  if (!data().password().isEmpty())
169  conninfo += buildConnParameter("password", data().password());
170 
171  //postgresqlDebug() << conninfo;
172 
173  //! @todo other parameters: connect_timeout, options, options, sslmode, sslcert, sslkey, sslrootcert, sslcrl, krbsrvname, gsslib, service
174  // https://www.postgresql.org/docs/8.4/interactive/libpq-connect.html
175  d->conn = PQconnectdb(conninfo.constData());
176 
177  if (!d->connectionOK()) {
178  PQfinish(d->conn);
179  d->conn = nullptr;
180  return false;
181  }
182 
183  // pgsql 8.1 changed the default to no oids but we need them
184  PGresult* result = PQexec(d->conn, "SET DEFAULT_WITH_OIDS TO ON");
185  int status = PQresultStatus(result);
186  PQclear(result);
187 
188  // initialize encoding
189  result = PQexec(d->conn, "SET CLIENT_ENCODING TO 'UNICODE'");
190  status = PQresultStatus(result);
191  PQclear(result);
192  d->unicode = status == PGRES_COMMAND_OK;
193 
194  result = PQexec(d->conn, "SET DATESTYLE TO 'ISO'");
195  status = PQresultStatus(result);
196  if (status != PGRES_COMMAND_OK) {
197  postgresqlWarning() << "Failed to set DATESTYLE to 'ISO':" << PQerrorMessage(d->conn);
198  }
199  //! @todo call on first use of SOUNDEX(), etc.;
200  //! it's not possible now because we don't have connection context in KDbFunctionExpressionData
201  if (!d->fuzzystrmatchExtensionCreated) {
202  d->fuzzystrmatchExtensionCreated
203  = drv_executeSql(KDbEscapedString("CREATE EXTENSION IF NOT EXISTS fuzzystrmatch"));
204  }
205  PQclear(result);
206  return true;
207 }
208 
209 bool PostgresqlConnection::drv_closeDatabase()
210 {
211  PQfinish(d->conn);
212  d->conn = nullptr;
213  return true;
214 }
215 
216 bool PostgresqlConnection::drv_dropDatabase(const QString &dbName)
217 {
218  //postgresqlDebug() << dbName;
219 
220  //! @todo Maybe should check that dbname is no the currentdb
221  if (executeSql(KDbEscapedString("DROP DATABASE ") + escapeIdentifier(dbName)))
222  return true;
223 
224  return false;
225 }
226 
227 KDbSqlResult* PostgresqlConnection::drv_prepareSql(const KDbEscapedString& sql)
228 {
229  PGresult* result = d->executeSql(sql);
230  const ExecStatusType status = PQresultStatus(result);
231  if (status == PGRES_TUPLES_OK || status == PGRES_COMMAND_OK) {
232  return new PostgresqlSqlResult(this, result, status);
233  }
234  storeResult(result, status);
235  return nullptr;
236 }
237 
238 bool PostgresqlConnection::drv_executeSql(const KDbEscapedString& sql)
239 {
240  PGresult* result = d->executeSql(sql);
241  const ExecStatusType status = PQresultStatus(result);
242  d->storeResultAndClear(&m_result, &result, status);
243  return status == PGRES_TUPLES_OK || status == PGRES_COMMAND_OK;
244 }
245 
246 bool PostgresqlConnection::drv_isDatabaseUsed() const
247 {
248  return d->conn;
249 }
250 
251 tristate PostgresqlConnection::drv_containsTable(const QString &tableName)
252 {
253  return resultExists(KDbEscapedString("SELECT 1 FROM pg_class WHERE relkind='r' AND relname LIKE %1")
254  .arg(escapeString(tableName)));
255 }
256 
257 QString PostgresqlConnection::serverResultName() const
258 {
259  if (m_result.code() >= 0 && m_result.code() <= PGRES_SINGLE_TUPLE) {
260  return QString::fromLatin1(PQresStatus(ExecStatusType(m_result.code())));
261  }
262  return QString();
263 }
264 
265 KDbPreparedStatementInterface* PostgresqlConnection::prepareStatementInternal()
266 {
267  return new PostgresqlPreparedStatement(d);
268 }
269 
270 KDbEscapedString PostgresqlConnection::escapeString(const QByteArray& str) const
271 {
272  int error;
273  d->escapingBuffer.resize(str.length() * 2 + 1);
274  size_t count = PQescapeStringConn(d->conn,
275  d->escapingBuffer.data(), str.constData(), str.length(),
276  &error);
277  d->escapingBuffer.resize(count);
278 
279  if (error != 0) {
280  d->storeResult(const_cast<KDbResult*>(&m_result));
281  const_cast<KDbResult&>(m_result) = KDbResult(ERR_INVALID_ENCODING,
282  PostgresqlConnection::tr("Escaping string failed. Invalid multibyte encoding."));
283  return KDbEscapedString();
284  }
285  return KDbEscapedString("\'") + d->escapingBuffer + '\'';
286 }
287 
288 KDbEscapedString PostgresqlConnection::escapeString(const QString& str) const
289 {
290  return escapeString(d->unicode ? str.toUtf8() : str.toLocal8Bit());
291 }
292 
293 void PostgresqlConnection::storeResult(PGresult *pgResult, ExecStatusType execStatus)
294 {
295  d->storeResultAndClear(&m_result, &pgResult, execStatus);
296 }
Provides database cursor functionality.
Definition: KDbCursor.h:68
KDB_EXPORT QString escapeIdentifier(const QString &string)
Definition: KDb.cpp:1334
const QChar * constData() const const
CaseInsensitive
The KDbSqlResult class abstracts result of a raw SQL query preparation by KDbConnection::prepareSql()
Definition: KDbSqlResult.h:44
KDbCursor * prepareQuery(KDbQuerySchema *query, const QList< QVariant > &params, KDbCursor::Options options=KDbCursor::Option::None)
Specialized string for escaping.
Database driver's abstraction.
Definition: KDbDriver.h:49
void error(QWidget *parent, const QString &text, const QString &caption=QString(), Options options=Notify)
bool isEmpty() const const
NETWORKMANAGERQT_EXPORT NetworkManager::Status status()
QByteArray toUtf8() const const
int length() const const
3-state logical type with three values: true, false and cancelled and convenient operators.
Definition: KDbTristate.h:100
QByteArray & replace(int pos, int len, const char *after)
Prepared statement interface for backend-dependent implementations.
KDbQuerySchema provides information about database query.
const char * constData() const const
QString arg(qlonglong a, int fieldWidth, int base, QChar fillChar) const const
unsigned int version()
Definition: KDb.cpp:336
QString fromLatin1(const char *str, int size)
Database specific connection data, e.g. host, port.
Generic options for a single connection. The options are accessible using key/value pairs....
KDB_EXPORT QString escapeString(const QString &string)
Definition: KDb.cpp:1356
int compare(const QString &other, Qt::CaseSensitivity cs) const const
QByteArray toLocal8Bit() const const
Provides database connection, allowing queries and data modification.
Definition: KDbConnection.h:51
Internal prototype for storing transaction handle for KDbTransaction object.
QString toString() const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2022 The KDE developers.
Generated on Sat Jun 25 2022 06:21:34 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.