1
/****************************************************************************
3
** Copyright (C) 2016 The Qt Company Ltd.
4
** Contact: https://www.qt.io/licensing/
6
** This file is part of the QtSql module of the Qt Toolkit.
8
** $QT_BEGIN_LICENSE:LGPL$
9
** Commercial License Usage
10
** Licensees holding valid commercial Qt licenses may use this file in
11
** accordance with the commercial license agreement provided with the
12
** Software or, alternatively, in accordance with the terms contained in
13
** a written agreement between you and The Qt Company. For licensing terms
14
** and conditions see https://www.qt.io/terms-conditions. For further
15
** information use the contact form at https://www.qt.io/contact-us.
17
** GNU Lesser General Public License Usage
18
** Alternatively, this file may be used under the terms of the GNU Lesser
19
** General Public License version 3 as published by the Free Software
20
** Foundation and appearing in the file LICENSE.LGPL3 included in the
21
** packaging of this file. Please review the following information to
22
** ensure the GNU Lesser General Public License version 3 requirements
23
** will be met: https://www.gnu.org/licenses/lgpl-3.0.html.
25
** GNU General Public License Usage
26
** Alternatively, this file may be used under the terms of the GNU
27
** General Public License version 2.0 or (at your option) the GNU General
28
** Public license version 3 or any later version approved by the KDE Free
29
** Qt Foundation. The licenses are as published by the Free Software
30
** Foundation and appearing in the file LICENSE.GPL2 and LICENSE.GPL3
31
** included in the packaging of this file. Please review the following
32
** information to ensure the GNU General Public License requirements will
33
** be met: https://www.gnu.org/licenses/gpl-2.0.html and
34
** https://www.gnu.org/licenses/gpl-3.0.html.
38
****************************************************************************/
40
#include "qsql_psql_p.h"
42
#include <qcoreapplication.h>
44
#include <qdatetime.h>
46
#include <qsqlerror.h>
47
#include <qsqlfield.h>
48
#include <qsqlindex.h>
49
#include <qsqlrecord.h>
50
#include <qsqlquery.h>
51
#include <qsocketnotifier.h>
52
#include <qstringlist.h>
54
#include <QtSql/private/qsqlresult_p.h>
55
#include <QtSql/private/qsqldriver_p.h>
58
#include <pg_config.h>
62
// below code taken from an example at http://www.gnu.org/software/hello/manual/autoconf/Function-Portability.html
65
(sizeof (x) == sizeof (long double) ? isnan_ld (x) \
66
: sizeof (x) == sizeof (double) ? isnan_d (x) \
68
static inline int isnan_f (float x) { return x != x; }
69
static inline int isnan_d (double x) { return x != x; }
70
static inline int isnan_ld (long double x) { return x != x; }
75
(sizeof (x) == sizeof (long double) ? isinf_ld (x) \
76
: sizeof (x) == sizeof (double) ? isinf_d (x) \
78
static inline int isinf_f (float x) { return isnan (x - x); }
79
static inline int isinf_d (double x) { return isnan (x - x); }
80
static inline int isinf_ld (long double x) { return isnan (x - x); }
84
// workaround for postgres defining their OIDs in a private header file
89
#define QNUMERICOID 1700
90
#define QFLOAT4OID 700
91
#define QFLOAT8OID 701
92
#define QABSTIMEOID 702
93
#define QRELTIMEOID 703
96
#define QTIMETZOID 1266
97
#define QTIMESTAMPOID 1114
98
#define QTIMESTAMPTZOID 1184
101
#define QREGPROCOID 24
106
#define QVARBITOID 1562
110
/* This is a compile time switch - if PQfreemem is declared, the compiler will use that one,
111
otherwise it'll run in this template */
112
template <typename T>
113
inline void PQfreemem(T *t, int = 0) { free(t); }
115
Q_DECLARE_OPAQUE_POINTER(PGconn*)
116
Q_DECLARE_METATYPE(PGconn*)
118
Q_DECLARE_OPAQUE_POINTER(PGresult*)
119
Q_DECLARE_METATYPE(PGresult*)
123
inline void qPQfreemem(void *buffer)
128
class QPSQLResultPrivate;
130
class QPSQLResult: public QSqlResult
132
Q_DECLARE_PRIVATE(QPSQLResult)
135
QPSQLResult(const QPSQLDriver *db);
138
QVariant handle() const Q_DECL_OVERRIDE;
139
void virtual_hook(int id, void *data) Q_DECL_OVERRIDE;
143
bool fetch(int i) Q_DECL_OVERRIDE;
144
bool fetchFirst() Q_DECL_OVERRIDE;
145
bool fetchLast() Q_DECL_OVERRIDE;
146
QVariant data(int i) Q_DECL_OVERRIDE;
147
bool isNull(int field) Q_DECL_OVERRIDE;
148
bool reset (const QString &query) Q_DECL_OVERRIDE;
149
int size() Q_DECL_OVERRIDE;
150
int numRowsAffected() Q_DECL_OVERRIDE;
151
QSqlRecord record() const Q_DECL_OVERRIDE;
152
QVariant lastInsertId() const Q_DECL_OVERRIDE;
153
bool prepare(const QString &query) Q_DECL_OVERRIDE;
154
bool exec() Q_DECL_OVERRIDE;
157
class QPSQLDriverPrivate : public QSqlDriverPrivate
159
Q_DECLARE_PUBLIC(QPSQLDriver)
161
QPSQLDriverPrivate() : QSqlDriverPrivate(),
164
pro(QPSQLDriver::Version6),
166
pendingNotifyCheck(false),
167
hasBackslashEscape(false)
168
{ dbmsType = QSqlDriver::PostgreSQL; }
172
QPSQLDriver::Protocol pro;
175
mutable bool pendingNotifyCheck;
176
bool hasBackslashEscape;
178
void appendTables(QStringList &tl, QSqlQuery &t, QChar type);
179
PGresult * exec(const char * stmt) const;
180
PGresult * exec(const QString & stmt) const;
181
QPSQLDriver::Protocol getPSQLVersion();
182
bool setEncodingUtf8();
184
void detectBackslashEscape();
187
void QPSQLDriverPrivate::appendTables(QStringList &tl, QSqlQuery &t, QChar type)
190
if (pro >= QPSQLDriver::Version73) {
191
query = QString::fromLatin1("select pg_class.relname, pg_namespace.nspname from pg_class "
192
"left join pg_namespace on (pg_class.relnamespace = pg_namespace.oid) "
193
"where (pg_class.relkind = '%1') and (pg_class.relname !~ '^Inv') "
194
"and (pg_class.relname !~ '^pg_') "
195
"and (pg_namespace.nspname != 'information_schema') ").arg(type);
197
query = QString::fromLatin1("select relname, null from pg_class where (relkind = '%1') "
198
"and (relname !~ '^Inv') "
199
"and (relname !~ '^pg_') ").arg(type);
203
QString schema = t.value(1).toString();
204
if (schema.isEmpty() || schema == QLatin1String("public"))
205
tl.append(t.value(0).toString());
207
tl.append(t.value(0).toString().prepend(QLatin1Char('.')).prepend(schema));
211
PGresult * QPSQLDriverPrivate::exec(const char * stmt) const
213
Q_Q(const QPSQLDriver);
214
PGresult *result = PQexec(connection, stmt);
215
if (seid.size() && !pendingNotifyCheck) {
216
pendingNotifyCheck = true;
217
QMetaObject::invokeMethod(const_cast<QPSQLDriver*>(q), "_q_handleNotification", Qt::QueuedConnection, Q_ARG(int,0));
222
PGresult * QPSQLDriverPrivate::exec(const QString & stmt) const
224
return exec(isUtf8 ? stmt.toUtf8().constData() : stmt.toLocal8Bit().constData());
227
class QPSQLResultPrivate : public QSqlResultPrivate
229
Q_DECLARE_PUBLIC(QPSQLResult)
231
Q_DECLARE_SQLDRIVER_PRIVATE(QPSQLDriver);
232
QPSQLResultPrivate(QPSQLResult *q, const QPSQLDriver *drv)
233
: QSqlResultPrivate(q, drv),
236
preparedQueriesEnabled(false)
239
QString fieldSerial(int i) const Q_DECL_OVERRIDE { return QLatin1Char('$') + QString::number(i + 1); }
240
void deallocatePreparedStmt();
244
bool preparedQueriesEnabled;
245
QString preparedStmtId;
247
bool processResults();
250
static QSqlError qMakeError(const QString& err, QSqlError::ErrorType type,
251
const QPSQLDriverPrivate *p, PGresult* result = 0)
253
const char *s = PQerrorMessage(p->connection);
254
QString msg = p->isUtf8 ? QString::fromUtf8(s) : QString::fromLocal8Bit(s);
257
errorCode = QString::fromLatin1(PQresultErrorField(result, PG_DIAG_SQLSTATE));
258
msg += QString::fromLatin1("(%1)").arg(errorCode);
260
return QSqlError(QLatin1String("QPSQL: ") + err, msg, type, errorCode);
263
bool QPSQLResultPrivate::processResults()
269
int status = PQresultStatus(result);
270
if (status == PGRES_TUPLES_OK) {
273
currentSize = PQntuples(result);
275
} else if (status == PGRES_COMMAND_OK) {
281
q->setLastError(qMakeError(QCoreApplication::translate("QPSQLResult",
282
"Unable to create query"), QSqlError::StatementError, drv_d_func(), result));
286
static QVariant::Type qDecodePSQLType(int t)
288
QVariant::Type type = QVariant::Invalid;
291
type = QVariant::Bool;
294
type = QVariant::LongLong;
302
type = QVariant::Int;
307
type = QVariant::Double;
312
type = QVariant::Date;
316
type = QVariant::Time;
319
case QTIMESTAMPTZOID:
320
type = QVariant::DateTime;
323
type = QVariant::ByteArray;
326
type = QVariant::String;
332
void QPSQLResultPrivate::deallocatePreparedStmt()
334
const QString stmt = QLatin1String("DEALLOCATE ") + preparedStmtId;
335
PGresult *result = drv_d_func()->exec(stmt);
337
if (PQresultStatus(result) != PGRES_COMMAND_OK)
338
qWarning("Unable to free statement: %s", PQerrorMessage(drv_d_func()->connection));
340
preparedStmtId.clear();
343
QPSQLResult::QPSQLResult(const QPSQLDriver* db)
344
: QSqlResult(*new QPSQLResultPrivate(this, db))
347
d->preparedQueriesEnabled = db->hasFeature(QSqlDriver::PreparedQueries);
350
QPSQLResult::~QPSQLResult()
355
if (d->preparedQueriesEnabled && !d->preparedStmtId.isNull())
356
d->deallocatePreparedStmt();
359
QVariant QPSQLResult::handle() const
361
Q_D(const QPSQLResult);
362
return QVariant::fromValue(d->result);
365
void QPSQLResult::cleanup()
371
setAt(QSql::BeforeFirstRow);
376
bool QPSQLResult::fetch(int i)
378
Q_D(const QPSQLResult);
383
if (i >= d->currentSize)
391
bool QPSQLResult::fetchFirst()
396
bool QPSQLResult::fetchLast()
398
Q_D(const QPSQLResult);
399
return fetch(PQntuples(d->result) - 1);
402
QVariant QPSQLResult::data(int i)
404
Q_D(const QPSQLResult);
405
if (i >= PQnfields(d->result)) {
406
qWarning("QPSQLResult::data: column %d out of range", i);
409
int ptype = PQftype(d->result, i);
410
QVariant::Type type = qDecodePSQLType(ptype);
411
const char *val = PQgetvalue(d->result, at(), i);
412
if (PQgetisnull(d->result, at(), i))
413
return QVariant(type);
416
return QVariant((bool)(val[0] == 't'));
417
case QVariant::String:
418
return d->drv_d_func()->isUtf8 ? QString::fromUtf8(val) : QString::fromLatin1(val);
419
case QVariant::LongLong:
421
return QString::fromLatin1(val).toLongLong();
423
return QString::fromLatin1(val).toULongLong();
426
case QVariant::Double:
427
if (ptype == QNUMERICOID) {
428
if (numericalPrecisionPolicy() != QSql::HighPrecision) {
431
double dbl=QString::fromLatin1(val).toDouble(&convert);
432
if (numericalPrecisionPolicy() == QSql::LowPrecisionInt64)
433
retval = (qlonglong)dbl;
434
else if (numericalPrecisionPolicy() == QSql::LowPrecisionInt32)
436
else if (numericalPrecisionPolicy() == QSql::LowPrecisionDouble)
442
return QString::fromLatin1(val);
444
return QString::fromLatin1(val).toDouble();
446
if (val[0] == '\0') {
447
return QVariant(QDate());
449
#ifndef QT_NO_DATESTRING
450
return QVariant(QDate::fromString(QString::fromLatin1(val), Qt::ISODate));
452
return QVariant(QString::fromLatin1(val));
455
case QVariant::Time: {
456
const QString str = QString::fromLatin1(val);
457
#ifndef QT_NO_DATESTRING
459
return QVariant(QTime());
461
return QVariant(QTime::fromString(str, Qt::ISODate));
463
return QVariant(str);
466
case QVariant::DateTime: {
467
QString dtval = QString::fromLatin1(val);
468
#ifndef QT_NO_DATESTRING
469
if (dtval.length() < 10) {
470
return QVariant(QDateTime());
472
QChar sign = dtval[dtval.size() - 3];
473
if (sign == QLatin1Char('-') || sign == QLatin1Char('+')) dtval += QLatin1String(":00");
474
return QVariant(QDateTime::fromString(dtval, Qt::ISODate).toLocalTime());
477
return QVariant(dtval);
480
case QVariant::ByteArray: {
482
unsigned char *data = PQunescapeBytea((const unsigned char*)val, &len);
483
QByteArray ba(reinterpret_cast<const char *>(data), int(len));
488
case QVariant::Invalid:
489
qWarning("QPSQLResult::data: unknown data type");
494
bool QPSQLResult::isNull(int field)
496
Q_D(const QPSQLResult);
497
PQgetvalue(d->result, at(), field);
498
return PQgetisnull(d->result, at(), field);
501
bool QPSQLResult::reset (const QString& query)
507
if (!driver()->isOpen() || driver()->isOpenError())
509
d->result = d->drv_d_func()->exec(query);
510
return d->processResults();
513
int QPSQLResult::size()
515
Q_D(const QPSQLResult);
516
return d->currentSize;
519
int QPSQLResult::numRowsAffected()
521
Q_D(const QPSQLResult);
522
return QString::fromLatin1(PQcmdTuples(d->result)).toInt();
525
QVariant QPSQLResult::lastInsertId() const
527
Q_D(const QPSQLResult);
528
if (d->drv_d_func()->pro >= QPSQLDriver::Version81) {
529
QSqlQuery qry(driver()->createResult());
530
// Most recent sequence value obtained from nextval
531
if (qry.exec(QLatin1String("SELECT lastval();")) && qry.next())
533
} else if (isActive()) {
534
Oid id = PQoidValue(d->result);
535
if (id != InvalidOid)
541
QSqlRecord QPSQLResult::record() const
543
Q_D(const QPSQLResult);
545
if (!isActive() || !isSelect())
548
int count = PQnfields(d->result);
549
for (int i = 0; i < count; ++i) {
551
if (d->drv_d_func()->isUtf8)
552
f.setName(QString::fromUtf8(PQfname(d->result, i)));
554
f.setName(QString::fromLocal8Bit(PQfname(d->result, i)));
555
int ptype = PQftype(d->result, i);
556
f.setType(qDecodePSQLType(ptype));
557
int len = PQfsize(d->result, i);
558
int precision = PQfmod(d->result, i);
562
case QTIMESTAMPTZOID:
567
if (precision != -1) {
568
len = (precision >> 16);
569
precision = ((precision - VARHDRSZ) & 0xffff);
578
if (len == -1 && precision >= VARHDRSZ) {
579
len = precision - VARHDRSZ;
585
f.setPrecision(precision);
592
void QPSQLResult::virtual_hook(int id, void *data)
596
QSqlResult::virtual_hook(id, data);
599
static QString qCreateParamString(const QVector<QVariant> &boundValues, const QSqlDriver *driver)
601
if (boundValues.isEmpty())
606
for (int i = 0; i < boundValues.count(); ++i) {
607
const QVariant &val = boundValues.at(i);
609
f.setType(val.type());
615
params.append(QLatin1String(", "));
616
params.append(driver->formatValue(f));
621
Q_GLOBAL_STATIC(QMutex, qMutex)
622
QString qMakePreparedStmtId()
625
static unsigned int qPreparedStmtCount = 0;
626
QString id = QLatin1String("qpsqlpstmt_") + QString::number(++qPreparedStmtCount, 16);
631
bool QPSQLResult::prepare(const QString &query)
634
if (!d->preparedQueriesEnabled)
635
return QSqlResult::prepare(query);
639
if (!d->preparedStmtId.isEmpty())
640
d->deallocatePreparedStmt();
642
const QString stmtId = qMakePreparedStmtId();
643
const QString stmt = QString::fromLatin1("PREPARE %1 AS ").arg(stmtId).append(d->positionalToNamedBinding(query));
645
PGresult *result = d->drv_d_func()->exec(stmt);
647
if (PQresultStatus(result) != PGRES_COMMAND_OK) {
648
setLastError(qMakeError(QCoreApplication::translate("QPSQLResult",
649
"Unable to prepare statement"), QSqlError::StatementError, d->drv_d_func(), result));
651
d->preparedStmtId.clear();
656
d->preparedStmtId = stmtId;
660
bool QPSQLResult::exec()
663
if (!d->preparedQueriesEnabled)
664
return QSqlResult::exec();
669
const QString params = qCreateParamString(boundValues(), driver());
670
if (params.isEmpty())
671
stmt = QString::fromLatin1("EXECUTE %1").arg(d->preparedStmtId);
673
stmt = QString::fromLatin1("EXECUTE %1 (%2)").arg(d->preparedStmtId).arg(params);
675
d->result = d->drv_d_func()->exec(stmt);
677
return d->processResults();
680
///////////////////////////////////////////////////////////////////
682
bool QPSQLDriverPrivate::setEncodingUtf8()
684
PGresult* result = exec("SET CLIENT_ENCODING TO 'UNICODE'");
685
int status = PQresultStatus(result);
687
return status == PGRES_COMMAND_OK;
690
void QPSQLDriverPrivate::setDatestyle()
692
PGresult* result = exec("SET DATESTYLE TO 'ISO'");
693
int status = PQresultStatus(result);
694
if (status != PGRES_COMMAND_OK)
695
qWarning("%s", PQerrorMessage(connection));
699
void QPSQLDriverPrivate::detectBackslashEscape()
701
// standard_conforming_strings option introduced in 8.2
702
// http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html
703
if (pro < QPSQLDriver::Version82) {
704
hasBackslashEscape = true;
706
hasBackslashEscape = false;
707
PGresult* result = exec(QLatin1Literal("SELECT '\\\\' x"));
708
int status = PQresultStatus(result);
709
if (status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK)
710
if (QString::fromLatin1(PQgetvalue(result, 0, 0)) == QLatin1Literal("\\"))
711
hasBackslashEscape = true;
716
static QPSQLDriver::Protocol qMakePSQLVersion(int vMaj, int vMin)
720
return QPSQLDriver::Version6;
725
return QPSQLDriver::Version71;
727
return QPSQLDriver::Version73;
729
return QPSQLDriver::Version74;
731
return QPSQLDriver::Version7;
739
return QPSQLDriver::Version81;
741
return QPSQLDriver::Version82;
743
return QPSQLDriver::Version83;
745
return QPSQLDriver::Version84;
747
return QPSQLDriver::Version8;
752
return QPSQLDriver::Version9;
757
return QPSQLDriver::VersionUnknown;
760
QPSQLDriver::Protocol QPSQLDriverPrivate::getPSQLVersion()
762
QPSQLDriver::Protocol serverVersion = QPSQLDriver::Version6;
763
PGresult* result = exec("select version()");
764
int status = PQresultStatus(result);
765
if (status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK) {
766
QString val = QString::fromLatin1(PQgetvalue(result, 0, 0));
768
QRegExp rx(QLatin1String("(\\d+)\\.(\\d+)"));
769
rx.setMinimal(true); // enforce non-greedy RegExp
771
if (rx.indexIn(val) != -1) {
772
int vMaj = rx.cap(1).toInt();
773
int vMin = rx.cap(2).toInt();
774
serverVersion = qMakePSQLVersion(vMaj, vMin);
775
#if defined(PG_MAJORVERSION)
776
if (rx.indexIn(QLatin1String(PG_MAJORVERSION)) != -1)
777
#elif defined(PG_VERSION)
778
if (rx.indexIn(QLatin1String(PG_VERSION)) != -1)
783
vMaj = rx.cap(1).toInt();
784
vMin = rx.cap(2).toInt();
785
QPSQLDriver::Protocol clientVersion = qMakePSQLVersion(vMaj, vMin);
787
if (serverVersion >= QPSQLDriver::Version9 && clientVersion < QPSQLDriver::Version9) {
788
//Client version before QPSQLDriver::Version9 only supports escape mode for bytea type,
789
//but bytea format is set to hex by default in PSQL 9 and above. So need to force the
790
//server use the old escape mode when connects to the new server with old client library.
792
result = exec("SET bytea_output=escape; ");
793
status = PQresultStatus(result);
794
} else if (serverVersion == QPSQLDriver::VersionUnknown) {
795
serverVersion = clientVersion;
796
if (serverVersion != QPSQLDriver::VersionUnknown)
797
qWarning("The server version of this PostgreSQL is unknown, falling back to the client version.");
804
//keep the old behavior unchanged
805
if (serverVersion == QPSQLDriver::VersionUnknown)
806
serverVersion = QPSQLDriver::Version6;
808
if (serverVersion < QPSQLDriver::Version71) {
809
qWarning("This version of PostgreSQL is not supported and may not work.");
812
return serverVersion;
815
QPSQLDriver::QPSQLDriver(QObject *parent)
816
: QSqlDriver(*new QPSQLDriverPrivate, parent)
820
QPSQLDriver::QPSQLDriver(PGconn *conn, QObject *parent)
821
: QSqlDriver(*new QPSQLDriverPrivate, parent)
824
d->connection = conn;
826
d->pro = d->getPSQLVersion();
827
d->detectBackslashEscape();
833
QPSQLDriver::~QPSQLDriver()
837
PQfinish(d->connection);
840
QVariant QPSQLDriver::handle() const
842
Q_D(const QPSQLDriver);
843
return QVariant::fromValue(d->connection);
846
bool QPSQLDriver::hasFeature(DriverFeature f) const
848
Q_D(const QPSQLDriver);
853
case LowPrecisionNumbers:
854
case EventNotifications:
856
case PreparedQueries:
857
case PositionalPlaceholders:
858
return d->pro >= QPSQLDriver::Version82;
859
case BatchOperations:
860
case NamedPlaceholders:
863
case MultipleResultSets:
867
return d->pro >= QPSQLDriver::Version71;
875
Quote a string for inclusion into the connection string
878
surround string by single quotes
880
static QString qQuote(QString s)
882
s.replace(QLatin1Char('\\'), QLatin1String("\\\\"));
883
s.replace(QLatin1Char('\''), QLatin1String("\\'"));
884
s.append(QLatin1Char('\'')).prepend(QLatin1Char('\''));
888
bool QPSQLDriver::open(const QString & db,
889
const QString & user,
890
const QString & password,
891
const QString & host,
893
const QString& connOpts)
898
QString connectString;
900
connectString.append(QLatin1String("host=")).append(qQuote(host));
902
connectString.append(QLatin1String(" dbname=")).append(qQuote(db));
904
connectString.append(QLatin1String(" user=")).append(qQuote(user));
905
if (!password.isEmpty())
906
connectString.append(QLatin1String(" password=")).append(qQuote(password));
908
connectString.append(QLatin1String(" port=")).append(qQuote(QString::number(port)));
910
// add any connect options - the server will handle error detection
911
if (!connOpts.isEmpty()) {
912
QString opt = connOpts;
913
opt.replace(QLatin1Char(';'), QLatin1Char(' '), Qt::CaseInsensitive);
914
connectString.append(QLatin1Char(' ')).append(opt);
917
d->connection = PQconnectdb(connectString.toLocal8Bit().constData());
918
if (PQstatus(d->connection) == CONNECTION_BAD) {
919
setLastError(qMakeError(tr("Unable to connect"), QSqlError::ConnectionError, d));
921
PQfinish(d->connection);
926
d->pro = d->getPSQLVersion();
927
d->detectBackslashEscape();
928
d->isUtf8 = d->setEncodingUtf8();
936
void QPSQLDriver::close()
943
disconnect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
949
PQfinish(d->connection);
956
QSqlResult *QPSQLDriver::createResult() const
958
return new QPSQLResult(this);
961
bool QPSQLDriver::beginTransaction()
963
Q_D(const QPSQLDriver);
965
qWarning("QPSQLDriver::beginTransaction: Database not open");
968
PGresult* res = d->exec("BEGIN");
969
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
970
setLastError(qMakeError(tr("Could not begin transaction"),
971
QSqlError::TransactionError, d, res));
979
bool QPSQLDriver::commitTransaction()
983
qWarning("QPSQLDriver::commitTransaction: Database not open");
986
PGresult* res = d->exec("COMMIT");
988
bool transaction_failed = false;
991
// This hack is used to tell if the transaction has succeeded for the protocol versions of
992
// PostgreSQL below. For 7.x and other protocol versions we are left in the dark.
993
// This hack can dissapear once there is an API to query this sort of information.
994
if (d->pro == QPSQLDriver::Version8 ||
995
d->pro == QPSQLDriver::Version81 ||
996
d->pro == QPSQLDriver::Version82 ||
997
d->pro == QPSQLDriver::Version83 ||
998
d->pro == QPSQLDriver::Version84 ||
999
d->pro == QPSQLDriver::Version9) {
1000
transaction_failed = qstrcmp(PQcmdStatus(res), "ROLLBACK") == 0;
1003
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK || transaction_failed) {
1004
setLastError(qMakeError(tr("Could not commit transaction"),
1005
QSqlError::TransactionError, d, res));
1013
bool QPSQLDriver::rollbackTransaction()
1017
qWarning("QPSQLDriver::rollbackTransaction: Database not open");
1020
PGresult* res = d->exec("ROLLBACK");
1021
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
1022
setLastError(qMakeError(tr("Could not rollback transaction"),
1023
QSqlError::TransactionError, d, res));
1031
QStringList QPSQLDriver::tables(QSql::TableType type) const
1033
Q_D(const QPSQLDriver);
1037
QSqlQuery t(createResult());
1038
t.setForwardOnly(true);
1040
if (type & QSql::Tables)
1041
const_cast<QPSQLDriverPrivate*>(d)->appendTables(tl, t, QLatin1Char('r'));
1042
if (type & QSql::Views)
1043
const_cast<QPSQLDriverPrivate*>(d)->appendTables(tl, t, QLatin1Char('v'));
1044
if (type & QSql::SystemTables) {
1045
t.exec(QLatin1String("select relname from pg_class where (relkind = 'r') "
1046
"and (relname like 'pg_%') "));
1048
tl.append(t.value(0).toString());
1054
static void qSplitTableName(QString &tablename, QString &schema)
1056
int dot = tablename.indexOf(QLatin1Char('.'));
1059
schema = tablename.left(dot);
1060
tablename = tablename.mid(dot + 1);
1063
QSqlIndex QPSQLDriver::primaryIndex(const QString& tablename) const
1065
Q_D(const QPSQLDriver);
1066
QSqlIndex idx(tablename);
1069
QSqlQuery i(createResult());
1072
QString tbl = tablename;
1074
qSplitTableName(tbl, schema);
1076
if (isIdentifierEscaped(tbl, QSqlDriver::TableName))
1077
tbl = stripDelimiters(tbl, QSqlDriver::TableName);
1079
tbl = tbl.toLower();
1081
if (isIdentifierEscaped(schema, QSqlDriver::TableName))
1082
schema = stripDelimiters(schema, QSqlDriver::TableName);
1084
schema = schema.toLower();
1087
case QPSQLDriver::Version6:
1088
stmt = QLatin1String("select pg_att1.attname, int(pg_att1.atttypid), pg_cl.relname "
1089
"from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
1090
"where pg_cl.relname = '%1_pkey' "
1091
"and pg_cl.oid = pg_ind.indexrelid "
1092
"and pg_att2.attrelid = pg_ind.indexrelid "
1093
"and pg_att1.attrelid = pg_ind.indrelid "
1094
"and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
1095
"order by pg_att2.attnum");
1097
case QPSQLDriver::Version7:
1098
case QPSQLDriver::Version71:
1099
stmt = QLatin1String("select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
1100
"from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
1101
"where pg_cl.relname = '%1_pkey' "
1102
"and pg_cl.oid = pg_ind.indexrelid "
1103
"and pg_att2.attrelid = pg_ind.indexrelid "
1104
"and pg_att1.attrelid = pg_ind.indrelid "
1105
"and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
1106
"order by pg_att2.attnum");
1108
case QPSQLDriver::Version73:
1109
case QPSQLDriver::Version74:
1110
case QPSQLDriver::Version8:
1111
case QPSQLDriver::Version81:
1112
case QPSQLDriver::Version82:
1113
case QPSQLDriver::Version83:
1114
case QPSQLDriver::Version84:
1115
case QPSQLDriver::Version9:
1116
stmt = QLatin1String("SELECT pg_attribute.attname, pg_attribute.atttypid::int, "
1118
"FROM pg_attribute, pg_class "
1119
"WHERE %1 pg_class.oid IN "
1120
"(SELECT indexrelid FROM pg_index WHERE indisprimary = true AND indrelid IN "
1121
" (SELECT oid FROM pg_class WHERE relname = '%2')) "
1122
"AND pg_attribute.attrelid = pg_class.oid "
1123
"AND pg_attribute.attisdropped = false "
1124
"ORDER BY pg_attribute.attnum");
1125
if (schema.isEmpty())
1126
stmt = stmt.arg(QLatin1String("pg_table_is_visible(pg_class.oid) AND"));
1128
stmt = stmt.arg(QString::fromLatin1("pg_class.relnamespace = (select oid from "
1129
"pg_namespace where pg_namespace.nspname = '%1') AND ").arg(schema));
1131
case QPSQLDriver::VersionUnknown:
1132
qFatal("PSQL version is unknown");
1136
i.exec(stmt.arg(tbl));
1137
while (i.isActive() && i.next()) {
1138
QSqlField f(i.value(0).toString(), qDecodePSQLType(i.value(1).toInt()));
1140
idx.setName(i.value(2).toString());
1145
QSqlRecord QPSQLDriver::record(const QString& tablename) const
1147
Q_D(const QPSQLDriver);
1152
QString tbl = tablename;
1154
qSplitTableName(tbl, schema);
1156
if (isIdentifierEscaped(tbl, QSqlDriver::TableName))
1157
tbl = stripDelimiters(tbl, QSqlDriver::TableName);
1159
tbl = tbl.toLower();
1161
if (isIdentifierEscaped(schema, QSqlDriver::TableName))
1162
schema = stripDelimiters(schema, QSqlDriver::TableName);
1164
schema = schema.toLower();
1168
case QPSQLDriver::Version6:
1169
stmt = QLatin1String("select pg_attribute.attname, int(pg_attribute.atttypid), "
1170
"pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1171
"int(pg_attribute.attrelid), pg_attribute.attnum "
1172
"from pg_class, pg_attribute "
1173
"where pg_class.relname = '%1' "
1174
"and pg_attribute.attnum > 0 "
1175
"and pg_attribute.attrelid = pg_class.oid ");
1177
case QPSQLDriver::Version7:
1178
stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1179
"pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1180
"pg_attribute.attrelid::int, pg_attribute.attnum "
1181
"from pg_class, pg_attribute "
1182
"where pg_class.relname = '%1' "
1183
"and pg_attribute.attnum > 0 "
1184
"and pg_attribute.attrelid = pg_class.oid ");
1186
case QPSQLDriver::Version71:
1187
stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1188
"pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1190
"from pg_class, pg_attribute "
1191
"left join pg_attrdef on (pg_attrdef.adrelid = "
1192
"pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
1193
"where pg_class.relname = '%1' "
1194
"and pg_attribute.attnum > 0 "
1195
"and pg_attribute.attrelid = pg_class.oid "
1196
"order by pg_attribute.attnum ");
1198
case QPSQLDriver::Version73:
1199
case QPSQLDriver::Version74:
1200
case QPSQLDriver::Version8:
1201
case QPSQLDriver::Version81:
1202
case QPSQLDriver::Version82:
1203
case QPSQLDriver::Version83:
1204
case QPSQLDriver::Version84:
1205
case QPSQLDriver::Version9:
1206
stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1207
"pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1209
"from pg_class, pg_attribute "
1210
"left join pg_attrdef on (pg_attrdef.adrelid = "
1211
"pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
1213
"and pg_class.relname = '%2' "
1214
"and pg_attribute.attnum > 0 "
1215
"and pg_attribute.attrelid = pg_class.oid "
1216
"and pg_attribute.attisdropped = false "
1217
"order by pg_attribute.attnum ");
1218
if (schema.isEmpty())
1219
stmt = stmt.arg(QLatin1String("pg_table_is_visible(pg_class.oid)"));
1221
stmt = stmt.arg(QString::fromLatin1("pg_class.relnamespace = (select oid from "
1222
"pg_namespace where pg_namespace.nspname = '%1')").arg(schema));
1224
case QPSQLDriver::VersionUnknown:
1225
qFatal("PSQL version is unknown");
1229
QSqlQuery query(createResult());
1230
query.exec(stmt.arg(tbl));
1231
if (d->pro >= QPSQLDriver::Version71) {
1232
while (query.next()) {
1233
int len = query.value(3).toInt();
1234
int precision = query.value(4).toInt();
1235
// swap length and precision if length == -1
1236
if (len == -1 && precision > -1) {
1237
len = precision - 4;
1240
QString defVal = query.value(5).toString();
1241
if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\''))
1242
defVal = defVal.mid(1, defVal.length() - 2);
1243
QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()));
1244
f.setRequired(query.value(2).toBool());
1246
f.setPrecision(precision);
1247
f.setDefaultValue(defVal);
1248
f.setSqlType(query.value(1).toInt());
1252
// Postgres < 7.1 cannot handle outer joins
1253
while (query.next()) {
1255
QString stmt2 = QLatin1String("select pg_attrdef.adsrc from pg_attrdef where "
1256
"pg_attrdef.adrelid = %1 and pg_attrdef.adnum = %2 ");
1257
QSqlQuery query2(createResult());
1258
query2.exec(stmt2.arg(query.value(5).toInt()).arg(query.value(6).toInt()));
1259
if (query2.isActive() && query2.next())
1260
defVal = query2.value(0).toString();
1261
if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\''))
1262
defVal = defVal.mid(1, defVal.length() - 2);
1263
int len = query.value(3).toInt();
1264
int precision = query.value(4).toInt();
1265
// swap length and precision if length == -1
1266
if (len == -1 && precision > -1) {
1267
len = precision - 4;
1270
QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()));
1271
f.setRequired(query.value(2).toBool());
1273
f.setPrecision(precision);
1274
f.setDefaultValue(defVal);
1275
f.setSqlType(query.value(1).toInt());
1283
template <class FloatType>
1284
inline void assignSpecialPsqlFloatValue(FloatType val, QString *target)
1287
*target = QLatin1String("'NaN'");
1289
switch (isinf(val)) {
1291
*target = QLatin1String("'Infinity'");
1294
*target = QLatin1String("'-Infinity'");
1300
QString QPSQLDriver::formatValue(const QSqlField &field, bool trimStrings) const
1302
Q_D(const QPSQLDriver);
1304
if (field.isNull()) {
1305
r = QLatin1String("NULL");
1307
switch (int(field.type())) {
1308
case QVariant::DateTime:
1309
#ifndef QT_NO_DATESTRING
1310
if (field.value().toDateTime().isValid()) {
1311
// we force the value to be considered with a timezone information, and we force it to be UTC
1312
// this is safe since postgresql stores only the UTC value and not the timezone offset (only used
1313
// while parsing), so we have correct behavior in both case of with timezone and without tz
1314
r = QLatin1String("TIMESTAMP WITH TIME ZONE ") + QLatin1Char('\'') + field.value().toDateTime().toUTC().toString(QLatin1String("yyyy-MM-ddThh:mm:ss.zzz")) + QLatin1Char('Z') + QLatin1Char('\'');
1316
r = QLatin1String("NULL");
1319
r = QLatin1String("NULL");
1320
#endif // QT_NO_DATESTRING
1322
case QVariant::Time:
1323
#ifndef QT_NO_DATESTRING
1324
if (field.value().toTime().isValid()) {
1325
r = QLatin1Char('\'') + field.value().toTime().toString(QLatin1String("hh:mm:ss.zzz")) + QLatin1Char('\'');
1329
r = QLatin1String("NULL");
1332
case QVariant::String:
1333
r = QSqlDriver::formatValue(field, trimStrings);
1334
if (d->hasBackslashEscape)
1335
r.replace(QLatin1String("\\"), QLatin1String("\\\\"));
1337
case QVariant::Bool:
1338
if (field.value().toBool())
1339
r = QLatin1String("TRUE");
1341
r = QLatin1String("FALSE");
1343
case QVariant::ByteArray: {
1344
QByteArray ba(field.value().toByteArray());
1346
#if defined PG_VERSION_NUM && PG_VERSION_NUM-0 >= 80200
1347
unsigned char *data = PQescapeByteaConn(d->connection, (const unsigned char*)ba.constData(), ba.size(), &len);
1349
unsigned char *data = PQescapeBytea((const unsigned char*)ba.constData(), ba.size(), &len);
1351
r += QLatin1Char('\'');
1352
r += QLatin1String((const char*)data);
1353
r += QLatin1Char('\'');
1357
case QMetaType::Float:
1358
assignSpecialPsqlFloatValue(field.value().toFloat(), &r);
1360
r = QSqlDriver::formatValue(field, trimStrings);
1362
case QVariant::Double:
1363
assignSpecialPsqlFloatValue(field.value().toDouble(), &r);
1365
r = QSqlDriver::formatValue(field, trimStrings);
1367
case QVariant::Uuid:
1368
r = QLatin1Char('\'') + field.value().toString() + QLatin1Char('\'');
1371
r = QSqlDriver::formatValue(field, trimStrings);
1378
QString QPSQLDriver::escapeIdentifier(const QString &identifier, IdentifierType) const
1380
QString res = identifier;
1381
if(!identifier.isEmpty() && !identifier.startsWith(QLatin1Char('"')) && !identifier.endsWith(QLatin1Char('"')) ) {
1382
res.replace(QLatin1Char('"'), QLatin1String("\"\""));
1383
res.prepend(QLatin1Char('"')).append(QLatin1Char('"'));
1384
res.replace(QLatin1Char('.'), QLatin1String("\".\""));
1389
bool QPSQLDriver::isOpen() const
1391
Q_D(const QPSQLDriver);
1392
return PQstatus(d->connection) == CONNECTION_OK;
1395
QPSQLDriver::Protocol QPSQLDriver::protocol() const
1397
Q_D(const QPSQLDriver);
1401
bool QPSQLDriver::subscribeToNotification(const QString &name)
1405
qWarning("QPSQLDriver::subscribeToNotificationImplementation: database not open.");
1409
if (d->seid.contains(name)) {
1410
qWarning("QPSQLDriver::subscribeToNotificationImplementation: already subscribing to '%s'.",
1415
int socket = PQsocket(d->connection);
1417
// Add the name to the list of subscriptions here so that QSQLDriverPrivate::exec knows
1418
// to check for notifications immediately after executing the LISTEN
1420
QString query = QLatin1String("LISTEN ") + escapeIdentifier(name, QSqlDriver::TableName);
1421
PGresult *result = d->exec(query);
1422
if (PQresultStatus(result) != PGRES_COMMAND_OK) {
1423
setLastError(qMakeError(tr("Unable to subscribe"), QSqlError::StatementError, d, result));
1430
d->sn = new QSocketNotifier(socket, QSocketNotifier::Read);
1431
connect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
1434
qWarning("QPSQLDriver::subscribeToNotificationImplementation: PQsocket didn't return a valid socket to listen on");
1441
bool QPSQLDriver::unsubscribeFromNotification(const QString &name)
1445
qWarning("QPSQLDriver::unsubscribeFromNotificationImplementation: database not open.");
1449
if (!d->seid.contains(name)) {
1450
qWarning("QPSQLDriver::unsubscribeFromNotificationImplementation: not subscribed to '%s'.",
1455
QString query = QLatin1String("UNLISTEN ") + escapeIdentifier(name, QSqlDriver::TableName);
1456
PGresult *result = d->exec(query);
1457
if (PQresultStatus(result) != PGRES_COMMAND_OK) {
1458
setLastError(qMakeError(tr("Unable to unsubscribe"), QSqlError::StatementError, d, result));
1464
d->seid.removeAll(name);
1466
if (d->seid.isEmpty()) {
1467
disconnect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
1475
QStringList QPSQLDriver::subscribedToNotifications() const
1477
Q_D(const QPSQLDriver);
1481
void QPSQLDriver::_q_handleNotification(int)
1484
d->pendingNotifyCheck = false;
1485
PQconsumeInput(d->connection);
1487
PGnotify *notify = 0;
1488
while((notify = PQnotifies(d->connection)) != 0) {
1489
QString name(QLatin1String(notify->relname));
1490
if (d->seid.contains(name)) {
1492
#if defined PG_VERSION_NUM && PG_VERSION_NUM-0 >= 70400
1494
payload = d->isUtf8 ? QString::fromUtf8(notify->extra) : QString::fromLatin1(notify->extra);
1496
emit notification(name);
1497
QSqlDriver::NotificationSource source = (notify->be_pid == PQbackendPID(d->connection)) ? QSqlDriver::SelfSource : QSqlDriver::OtherSource;
1498
emit notification(name, source, payload);
1501
qWarning("QPSQLDriver: received notification for '%s' which isn't subscribed to.",