source: trunk/src/sql/drivers/psql/qsql_psql.cpp

Last change on this file was 846, checked in by Dmitry A. Kuminov, 14 years ago

trunk: Merged in qt 4.7.2 sources from branches/vendor/nokia/qt.
File size: 43.1 KB
Line 
1/****************************************************************************
2**
3** Copyright (C) 2011 Nokia Corporation and/or its subsidiary(-ies).
4** All rights reserved.
5** Contact: Nokia Corporation (qt-info@nokia.com)
6**
7** This file is part of the QtSql module of the Qt Toolkit.
8**
9** $QT_BEGIN_LICENSE:LGPL$
10** Commercial Usage
11** Licensees holding valid Qt Commercial licenses may use this file in
12** accordance with the Qt Commercial License Agreement provided with the
13** Software or, alternatively, in accordance with the terms contained in
14** a written agreement between you and Nokia.
15**
16** GNU Lesser General Public License Usage
17** Alternatively, this file may be used under the terms of the GNU Lesser
18** General Public License version 2.1 as published by the Free Software
19** Foundation and appearing in the file LICENSE.LGPL included in the
20** packaging of this file. Please review the following information to
21** ensure the GNU Lesser General Public License version 2.1 requirements
22** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
23**
24** In addition, as a special exception, Nokia gives you certain additional
25** rights. These rights are described in the Nokia Qt LGPL Exception
26** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
27**
28** GNU General Public License Usage
29** Alternatively, this file may be used under the terms of the GNU
30** General Public License version 3.0 as published by the Free Software
31** Foundation and appearing in the file LICENSE.GPL included in the
32** packaging of this file. Please review the following information to
33** ensure the GNU General Public License version 3.0 requirements will be
34** met: http://www.gnu.org/copyleft/gpl.html.
35**
36** If you have questions regarding the use of this file, please contact
37** Nokia at qt-info@nokia.com.
38** $QT_END_LICENSE$
39**
40****************************************************************************/
41
42#include "qsql_psql.h"
43
44#include <qcoreapplication.h>
45#include <qvariant.h>
46#include <qdatetime.h>
47#include <qregexp.h>
48#include <qsqlerror.h>
49#include <qsqlfield.h>
50#include <qsqlindex.h>
51#include <qsqlrecord.h>
52#include <qsqlquery.h>
53#include <qsocketnotifier.h>
54#include <qstringlist.h>
55#include <qmutex.h>
56
57#include <libpq-fe.h>
58#include <pg_config.h>
59
60#include <stdlib.h>
61#include <math.h>
62// below code taken from an example at http://www.gnu.org/software/hello/manual/autoconf/Function-Portability.html
63#ifndef isnan
64 # define isnan(x) \
65 (sizeof (x) == sizeof (long double) ? isnan_ld (x) \
66 : sizeof (x) == sizeof (double) ? isnan_d (x) \
67 : isnan_f (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; }
71#endif
72
73#ifndef isinf
74 # define isinf(x) \
75 (sizeof (x) == sizeof (long double) ? isinf_ld (x) \
76 : sizeof (x) == sizeof (double) ? isinf_d (x) \
77 : isinf_f (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); }
81#endif
82
83
84// workaround for postgres defining their OIDs in a private header file
85#define QBOOLOID 16
86#define QINT8OID 20
87#define QINT2OID 21
88#define QINT4OID 23
89#define QNUMERICOID 1700
90#define QFLOAT4OID 700
91#define QFLOAT8OID 701
92#define QABSTIMEOID 702
93#define QRELTIMEOID 703
94#define QDATEOID 1082
95#define QTIMEOID 1083
96#define QTIMETZOID 1266
97#define QTIMESTAMPOID 1114
98#define QTIMESTAMPTZOID 1184
99#define QOIDOID 2278
100#define QBYTEAOID 17
101#define QREGPROCOID 24
102#define QXIDOID 28
103#define QCIDOID 29
104
105/* This is a compile time switch - if PQfreemem is declared, the compiler will use that one,
106 otherwise it'll run in this template */
107template <typename T>
108inline void PQfreemem(T *t, int = 0) { free(t); }
109
110Q_DECLARE_METATYPE(PGconn*)
111Q_DECLARE_METATYPE(PGresult*)
112
113QT_BEGIN_NAMESPACE
114
115inline void qPQfreemem(void *buffer)
116{
117 PQfreemem(buffer);
118}
119
120class QPSQLDriverPrivate
121{
122public:
123 QPSQLDriverPrivate() : connection(0), isUtf8(false), pro(QPSQLDriver::Version6), sn(0) {}
124 PGconn *connection;
125 bool isUtf8;
126 QPSQLDriver::Protocol pro;
127 QSocketNotifier *sn;
128 QStringList seid;
129
130 void appendTables(QStringList &tl, QSqlQuery &t, QChar type);
131};
132
133void QPSQLDriverPrivate::appendTables(QStringList &tl, QSqlQuery &t, QChar type)
134{
135 QString query;
136 if (pro >= QPSQLDriver::Version73) {
137 query = QString::fromLatin1("select pg_class.relname, pg_namespace.nspname from pg_class "
138 "left join pg_namespace on (pg_class.relnamespace = pg_namespace.oid) "
139 "where (pg_class.relkind = '%1') and (pg_class.relname !~ '^Inv') "
140 "and (pg_class.relname !~ '^pg_') "
141 "and (pg_namespace.nspname != 'information_schema') ").arg(type);
142 } else {
143 query = QString::fromLatin1("select relname, null from pg_class where (relkind = '%1') "
144 "and (relname !~ '^Inv') "
145 "and (relname !~ '^pg_') ").arg(type);
146 }
147 t.exec(query);
148 while (t.next()) {
149 QString schema = t.value(1).toString();
150 if (schema.isEmpty() || schema == QLatin1String("public"))
151 tl.append(t.value(0).toString());
152 else
153 tl.append(t.value(0).toString().prepend(QLatin1Char('.')).prepend(schema));
154 }
155}
156
157class QPSQLResultPrivate
158{
159public:
160 QPSQLResultPrivate(QPSQLResult *qq): q(qq), driver(0), result(0), currentSize(-1), preparedQueriesEnabled(false) {}
161
162 QPSQLResult *q;
163 const QPSQLDriverPrivate *driver;
164 PGresult *result;
165 int currentSize;
166 bool preparedQueriesEnabled;
167 QString preparedStmtId;
168
169 bool processResults();
170};
171
172static QSqlError qMakeError(const QString& err, QSqlError::ErrorType type,
173 const QPSQLDriverPrivate *p)
174{
175 const char *s = PQerrorMessage(p->connection);
176 QString msg = p->isUtf8 ? QString::fromUtf8(s) : QString::fromLocal8Bit(s);
177 return QSqlError(QLatin1String("QPSQL: ") + err, msg, type);
178}
179
180bool QPSQLResultPrivate::processResults()
181{
182 if (!result)
183 return false;
184
185 int status = PQresultStatus(result);
186 if (status == PGRES_TUPLES_OK) {
187 q->setSelect(true);
188 q->setActive(true);
189 currentSize = PQntuples(result);
190 return true;
191 } else if (status == PGRES_COMMAND_OK) {
192 q->setSelect(false);
193 q->setActive(true);
194 currentSize = -1;
195 return true;
196 }
197 q->setLastError(qMakeError(QCoreApplication::translate("QPSQLResult",
198 "Unable to create query"), QSqlError::StatementError, driver));
199 return false;
200}
201
202static QVariant::Type qDecodePSQLType(int t)
203{
204 QVariant::Type type = QVariant::Invalid;
205 switch (t) {
206 case QBOOLOID:
207 type = QVariant::Bool;
208 break;
209 case QINT8OID:
210 type = QVariant::LongLong;
211 break;
212 case QINT2OID:
213 case QINT4OID:
214 case QOIDOID:
215 case QREGPROCOID:
216 case QXIDOID:
217 case QCIDOID:
218 type = QVariant::Int;
219 break;
220 case QNUMERICOID:
221 case QFLOAT4OID:
222 case QFLOAT8OID:
223 type = QVariant::Double;
224 break;
225 case QABSTIMEOID:
226 case QRELTIMEOID:
227 case QDATEOID:
228 type = QVariant::Date;
229 break;
230 case QTIMEOID:
231 case QTIMETZOID:
232 type = QVariant::Time;
233 break;
234 case QTIMESTAMPOID:
235 case QTIMESTAMPTZOID:
236 type = QVariant::DateTime;
237 break;
238 case QBYTEAOID:
239 type = QVariant::ByteArray;
240 break;
241 default:
242 type = QVariant::String;
243 break;
244 }
245 return type;
246}
247
248static void qDeallocatePreparedStmt(QPSQLResultPrivate *d)
249{
250 const QString stmt = QLatin1String("DEALLOCATE ") + d->preparedStmtId;
251 PGresult *result = PQexec(d->driver->connection,
252 d->driver->isUtf8 ? stmt.toUtf8().constData()
253 : stmt.toLocal8Bit().constData());
254
255 if (PQresultStatus(result) != PGRES_COMMAND_OK)
256 qWarning("Unable to free statement: %s", PQerrorMessage(d->driver->connection));
257 PQclear(result);
258 d->preparedStmtId.clear();
259}
260
261QPSQLResult::QPSQLResult(const QPSQLDriver* db, const QPSQLDriverPrivate* p)
262 : QSqlResult(db)
263{
264 d = new QPSQLResultPrivate(this);
265 d->driver = p;
266 d->preparedQueriesEnabled = db->hasFeature(QSqlDriver::PreparedQueries);
267}
268
269QPSQLResult::~QPSQLResult()
270{
271 cleanup();
272
273 if (d->preparedQueriesEnabled && !d->preparedStmtId.isNull())
274 qDeallocatePreparedStmt(d);
275
276 delete d;
277}
278
279QVariant QPSQLResult::handle() const
280{
281 return qVariantFromValue(d->result);
282}
283
284void QPSQLResult::cleanup()
285{
286 if (d->result)
287 PQclear(d->result);
288 d->result = 0;
289 setAt(QSql::BeforeFirstRow);
290 d->currentSize = -1;
291 setActive(false);
292}
293
294bool QPSQLResult::fetch(int i)
295{
296 if (!isActive())
297 return false;
298 if (i < 0)
299 return false;
300 if (i >= d->currentSize)
301 return false;
302 if (at() == i)
303 return true;
304 setAt(i);
305 return true;
306}
307
308bool QPSQLResult::fetchFirst()
309{
310 return fetch(0);
311}
312
313bool QPSQLResult::fetchLast()
314{
315 return fetch(PQntuples(d->result) - 1);
316}
317
318QVariant QPSQLResult::data(int i)
319{
320 if (i >= PQnfields(d->result)) {
321 qWarning("QPSQLResult::data: column %d out of range", i);
322 return QVariant();
323 }
324 int ptype = PQftype(d->result, i);
325 QVariant::Type type = qDecodePSQLType(ptype);
326 const char *val = PQgetvalue(d->result, at(), i);
327 if (PQgetisnull(d->result, at(), i))
328 return QVariant(type);
329 switch (type) {
330 case QVariant::Bool:
331 return QVariant((bool)(val[0] == 't'));
332 case QVariant::String:
333 return d->driver->isUtf8 ? QString::fromUtf8(val) : QString::fromAscii(val);
334 case QVariant::LongLong:
335 if (val[0] == '-')
336 return QString::fromLatin1(val).toLongLong();
337 else
338 return QString::fromLatin1(val).toULongLong();
339 case QVariant::Int:
340 return atoi(val);
341 case QVariant::Double:
342 if (ptype == QNUMERICOID) {
343 if (numericalPrecisionPolicy() != QSql::HighPrecision) {
344 QVariant retval;
345 bool convert;
346 double dbl=QString::fromAscii(val).toDouble(&convert);
347 if (numericalPrecisionPolicy() == QSql::LowPrecisionInt64)
348 retval = (qlonglong)dbl;
349 else if (numericalPrecisionPolicy() == QSql::LowPrecisionInt32)
350 retval = (int)dbl;
351 else if (numericalPrecisionPolicy() == QSql::LowPrecisionDouble)
352 retval = dbl;
353 if (!convert)
354 return QVariant();
355 return retval;
356 }
357 return QString::fromAscii(val);
358 }
359 return QString::fromAscii(val).toDouble();
360 case QVariant::Date:
361 if (val[0] == '\0') {
362 return QVariant(QDate());
363 } else {
364#ifndef QT_NO_DATESTRING
365 return QVariant(QDate::fromString(QString::fromLatin1(val), Qt::ISODate));
366#else
367 return QVariant(QString::fromLatin1(val));
368#endif
369 }
370 case QVariant::Time: {
371 const QString str = QString::fromLatin1(val);
372#ifndef QT_NO_DATESTRING
373 if (str.isEmpty())
374 return QVariant(QTime());
375 if (str.at(str.length() - 3) == QLatin1Char('+') || str.at(str.length() - 3) == QLatin1Char('-'))
376 // strip the timezone
377 // TODO: fix this when timestamp support comes into QDateTime
378 return QVariant(QTime::fromString(str.left(str.length() - 3), Qt::ISODate));
379 return QVariant(QTime::fromString(str, Qt::ISODate));
380#else
381 return QVariant(str);
382#endif
383 }
384 case QVariant::DateTime: {
385 QString dtval = QString::fromLatin1(val);
386#ifndef QT_NO_DATESTRING
387 if (dtval.length() < 10)
388 return QVariant(QDateTime());
389 // remove the timezone
390 // TODO: fix this when timestamp support comes into QDateTime
391 if (dtval.at(dtval.length() - 3) == QLatin1Char('+') || dtval.at(dtval.length() - 3) == QLatin1Char('-'))
392 dtval.chop(3);
393 // milliseconds are sometimes returned with 2 digits only
394 if (dtval.at(dtval.length() - 3).isPunct())
395 dtval += QLatin1Char('0');
396 if (dtval.isEmpty())
397 return QVariant(QDateTime());
398 else
399 return QVariant(QDateTime::fromString(dtval, Qt::ISODate));
400#else
401 return QVariant(dtval);
402#endif
403 }
404 case QVariant::ByteArray: {
405 size_t len;
406 unsigned char *data = PQunescapeBytea((unsigned char*)val, &len);
407 QByteArray ba((const char*)data, len);
408 qPQfreemem(data);
409 return QVariant(ba);
410 }
411 default:
412 case QVariant::Invalid:
413 qWarning("QPSQLResult::data: unknown data type");
414 }
415 return QVariant();
416}
417
418bool QPSQLResult::isNull(int field)
419{
420 PQgetvalue(d->result, at(), field);
421 return PQgetisnull(d->result, at(), field);
422}
423
424bool QPSQLResult::reset (const QString& query)
425{
426 cleanup();
427 if (!driver())
428 return false;
429 if (!driver()->isOpen() || driver()->isOpenError())
430 return false;
431 d->result = PQexec(d->driver->connection,
432 d->driver->isUtf8 ? query.toUtf8().constData()
433 : query.toLocal8Bit().constData());
434 return d->processResults();
435}
436
437int QPSQLResult::size()
438{
439 return d->currentSize;
440}
441
442int QPSQLResult::numRowsAffected()
443{
444 return QString::fromLatin1(PQcmdTuples(d->result)).toInt();
445}
446
447QVariant QPSQLResult::lastInsertId() const
448{
449 if (isActive()) {
450 Oid id = PQoidValue(d->result);
451 if (id != InvalidOid)
452 return QVariant(id);
453 }
454 return QVariant();
455}
456
457QSqlRecord QPSQLResult::record() const
458{
459 QSqlRecord info;
460 if (!isActive() || !isSelect())
461 return info;
462
463 int count = PQnfields(d->result);
464 for (int i = 0; i < count; ++i) {
465 QSqlField f;
466 if (d->driver->isUtf8)
467 f.setName(QString::fromUtf8(PQfname(d->result, i)));
468 else
469 f.setName(QString::fromLocal8Bit(PQfname(d->result, i)));
470 f.setType(qDecodePSQLType(PQftype(d->result, i)));
471 int len = PQfsize(d->result, i);
472 int precision = PQfmod(d->result, i);
473 // swap length and precision if length == -1
474 if (len == -1 && precision > -1) {
475 len = precision - 4;
476 precision = -1;
477 }
478 f.setLength(len);
479 f.setPrecision(precision);
480 f.setSqlType(PQftype(d->result, i));
481 info.append(f);
482 }
483 return info;
484}
485
486void QPSQLResult::virtual_hook(int id, void *data)
487{
488 Q_ASSERT(data);
489
490 switch (id) {
491 default:
492 QSqlResult::virtual_hook(id, data);
493 }
494}
495
496static QString qReplacePlaceholderMarkers(const QString &query)
497{
498 const int originalLength = query.length();
499 bool inQuote = false;
500 int markerIdx = 0;
501 QString result;
502 result.reserve(originalLength + 23);
503 for (int i = 0; i < originalLength; ++i) {
504 const QChar ch = query.at(i);
505 if (ch == QLatin1Char('?') && !inQuote) {
506 result += QLatin1Char('$');
507 result += QString::number(++markerIdx);
508 } else {
509 if (ch == QLatin1Char('\''))
510 inQuote = !inQuote;
511 result += ch;
512 }
513 }
514
515 result.squeeze();
516 return result;
517}
518
519static QString qCreateParamString(const QVector<QVariant> boundValues, const QSqlDriver *driver)
520{
521 if (boundValues.isEmpty())
522 return QString();
523
524 QString params;
525 QSqlField f;
526 for (int i = 0; i < boundValues.count(); ++i) {
527 const QVariant &val = boundValues.at(i);
528
529 f.setType(val.type());
530 if (val.isNull())
531 f.clear();
532 else
533 f.setValue(val);
534 if(!params.isNull())
535 params.append(QLatin1String(", "));
536 params.append(driver->formatValue(f));
537 }
538 return params;
539}
540
541Q_GLOBAL_STATIC(QMutex, qMutex)
542QString qMakePreparedStmtId()
543{
544 qMutex()->lock();
545 static unsigned int qPreparedStmtCount = 0;
546 QString id = QLatin1String("qpsqlpstmt_") + QString::number(++qPreparedStmtCount, 16);
547 qMutex()->unlock();
548 return id;
549}
550
551bool QPSQLResult::prepare(const QString &query)
552{
553 if (!d->preparedQueriesEnabled)
554 return QSqlResult::prepare(query);
555
556 cleanup();
557
558 if (!d->preparedStmtId.isEmpty())
559 qDeallocatePreparedStmt(d);
560
561 const QString stmtId = qMakePreparedStmtId();
562 const QString stmt = QString::fromLatin1("PREPARE %1 AS ").arg(stmtId).append(qReplacePlaceholderMarkers(query));
563
564 PGresult *result = PQexec(d->driver->connection,
565 d->driver->isUtf8 ? stmt.toUtf8().constData()
566 : stmt.toLocal8Bit().constData());
567
568 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
569 setLastError(qMakeError(QCoreApplication::translate("QPSQLResult",
570 "Unable to prepare statement"), QSqlError::StatementError, d->driver));
571 PQclear(result);
572 d->preparedStmtId.clear();
573 return false;
574 }
575
576 PQclear(result);
577 d->preparedStmtId = stmtId;
578 return true;
579}
580
581bool QPSQLResult::exec()
582{
583 if (!d->preparedQueriesEnabled)
584 return QSqlResult::exec();
585
586 cleanup();
587
588 QString stmt;
589 const QString params = qCreateParamString(boundValues(), d->q->driver());
590 if (params.isEmpty())
591 stmt = QString::fromLatin1("EXECUTE %1").arg(d->preparedStmtId);
592 else
593 stmt = QString::fromLatin1("EXECUTE %1 (%2)").arg(d->preparedStmtId).arg(params);
594
595 d->result = PQexec(d->driver->connection,
596 d->driver->isUtf8 ? stmt.toUtf8().constData()
597 : stmt.toLocal8Bit().constData());
598
599 return d->processResults();
600}
601
602///////////////////////////////////////////////////////////////////
603
604static bool setEncodingUtf8(PGconn* connection)
605{
606 PGresult* result = PQexec(connection, "SET CLIENT_ENCODING TO 'UNICODE'");
607 int status = PQresultStatus(result);
608 PQclear(result);
609 return status == PGRES_COMMAND_OK;
610}
611
612static void setDatestyle(PGconn* connection)
613{
614 PGresult* result = PQexec(connection, "SET DATESTYLE TO 'ISO'");
615 int status = PQresultStatus(result);
616 if (status != PGRES_COMMAND_OK)
617 qWarning("%s", PQerrorMessage(connection));
618 PQclear(result);
619}
620
621static QPSQLDriver::Protocol qMakePSQLVersion(int vMaj, int vMin)
622{
623 switch (vMaj) {
624 case 6:
625 return QPSQLDriver::Version6;
626 case 7:
627 {
628 switch (vMin) {
629 case 1:
630 return QPSQLDriver::Version71;
631 case 3:
632 return QPSQLDriver::Version73;
633 case 4:
634 return QPSQLDriver::Version74;
635 default:
636 return QPSQLDriver::Version7;
637 }
638 break;
639 }
640 case 8:
641 {
642 switch (vMin) {
643 case 1:
644 return QPSQLDriver::Version81;
645 case 2:
646 return QPSQLDriver::Version82;
647 case 3:
648 return QPSQLDriver::Version83;
649 case 4:
650 return QPSQLDriver::Version84;
651 default:
652 return QPSQLDriver::Version8;
653 }
654 break;
655 }
656 case 9:
657 return QPSQLDriver::Version9;
658 break;
659 default:
660 break;
661 }
662 return QPSQLDriver::VersionUnknown;
663}
664
665static QPSQLDriver::Protocol getPSQLVersion(PGconn* connection)
666{
667 QPSQLDriver::Protocol serverVersion = QPSQLDriver::Version6;
668 PGresult* result = PQexec(connection, "select version()");
669 int status = PQresultStatus(result);
670 if (status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK) {
671 QString val = QString::fromAscii(PQgetvalue(result, 0, 0));
672
673 QRegExp rx(QLatin1String("(\\d+)\\.(\\d+)"));
674 rx.setMinimal(true); // enforce non-greedy RegExp
675
676 if (rx.indexIn(val) != -1) {
677 int vMaj = rx.cap(1).toInt();
678 int vMin = rx.cap(2).toInt();
679 serverVersion = qMakePSQLVersion(vMaj, vMin);
680#ifdef PG_MAJORVERSION
681 if (rx.indexIn(QLatin1String(PG_MAJORVERSION)) != -1) {
682 vMaj = rx.cap(1).toInt();
683 vMin = rx.cap(2).toInt();
684 }
685 QPSQLDriver::Protocol clientVersion = qMakePSQLVersion(vMaj, vMin);
686
687 if (serverVersion >= QPSQLDriver::Version9 && clientVersion < QPSQLDriver::Version9) {
688 //Client version before QPSQLDriver::Version9 only supports escape mode for bytea type,
689 //but bytea format is set to hex by default in PSQL 9 and above. So need to force the
690 //server use the old escape mode when connects to the new server with old client library.
691 result = PQexec(connection, "SET bytea_output=escape; ");
692 status = PQresultStatus(result);
693 } else if (serverVersion == QPSQLDriver::VersionUnknown) {
694 serverVersion = clientVersion;
695 if (serverVersion != QPSQLDriver::VersionUnknown)
696 qWarning("The server version of this PostgreSQL is unknown, falling back to the client version.");
697 }
698#endif
699 }
700 }
701 PQclear(result);
702
703 //keep the old behavior unchanged
704 if (serverVersion == QPSQLDriver::VersionUnknown)
705 serverVersion = QPSQLDriver::Version6;
706
707 if (serverVersion < QPSQLDriver::Version71) {
708 qWarning("This version of PostgreSQL is not supported and may not work.");
709 }
710
711 return serverVersion;
712}
713
714QPSQLDriver::QPSQLDriver(QObject *parent)
715 : QSqlDriver(parent)
716{
717 init();
718}
719
720QPSQLDriver::QPSQLDriver(PGconn *conn, QObject *parent)
721 : QSqlDriver(parent)
722{
723 init();
724 d->connection = conn;
725 if (conn) {
726 d->pro = getPSQLVersion(d->connection);
727 setOpen(true);
728 setOpenError(false);
729 }
730}
731
732void QPSQLDriver::init()
733{
734 d = new QPSQLDriverPrivate();
735}
736
737QPSQLDriver::~QPSQLDriver()
738{
739 if (d->connection)
740 PQfinish(d->connection);
741 delete d;
742}
743
744QVariant QPSQLDriver::handle() const
745{
746 return qVariantFromValue(d->connection);
747}
748
749bool QPSQLDriver::hasFeature(DriverFeature f) const
750{
751 switch (f) {
752 case Transactions:
753 case QuerySize:
754 case LastInsertId:
755 case LowPrecisionNumbers:
756 case EventNotifications:
757 return true;
758 case PreparedQueries:
759 case PositionalPlaceholders:
760 return d->pro >= QPSQLDriver::Version82;
761 case BatchOperations:
762 case NamedPlaceholders:
763 case SimpleLocking:
764 case FinishQuery:
765 case MultipleResultSets:
766 return false;
767 case BLOB:
768 return d->pro >= QPSQLDriver::Version71;
769 case Unicode:
770 return d->isUtf8;
771 }
772 return false;
773}
774
775/*
776 Quote a string for inclusion into the connection string
777 \ -> \\
778 ' -> \'
779 surround string by single quotes
780 */
781static QString qQuote(QString s)
782{
783 s.replace(QLatin1Char('\\'), QLatin1String("\\\\"));
784 s.replace(QLatin1Char('\''), QLatin1String("\\'"));
785 s.append(QLatin1Char('\'')).prepend(QLatin1Char('\''));
786 return s;
787}
788
789bool QPSQLDriver::open(const QString & db,
790 const QString & user,
791 const QString & password,
792 const QString & host,
793 int port,
794 const QString& connOpts)
795{
796 if (isOpen())
797 close();
798 QString connectString;
799 if (!host.isEmpty())
800 connectString.append(QLatin1String("host=")).append(qQuote(host));
801 if (!db.isEmpty())
802 connectString.append(QLatin1String(" dbname=")).append(qQuote(db));
803 if (!user.isEmpty())
804 connectString.append(QLatin1String(" user=")).append(qQuote(user));
805 if (!password.isEmpty())
806 connectString.append(QLatin1String(" password=")).append(qQuote(password));
807 if (port != -1)
808 connectString.append(QLatin1String(" port=")).append(qQuote(QString::number(port)));
809
810 // add any connect options - the server will handle error detection
811 if (!connOpts.isEmpty()) {
812 QString opt = connOpts;
813 opt.replace(QLatin1Char(';'), QLatin1Char(' '), Qt::CaseInsensitive);
814 connectString.append(QLatin1Char(' ')).append(opt);
815 }
816
817 d->connection = PQconnectdb(connectString.toLocal8Bit().constData());
818 if (PQstatus(d->connection) == CONNECTION_BAD) {
819 setLastError(qMakeError(tr("Unable to connect"), QSqlError::ConnectionError, d));
820 setOpenError(true);
821 PQfinish(d->connection);
822 d->connection = 0;
823 return false;
824 }
825
826 d->pro = getPSQLVersion(d->connection);
827 d->isUtf8 = setEncodingUtf8(d->connection);
828 setDatestyle(d->connection);
829
830 setOpen(true);
831 setOpenError(false);
832 return true;
833}
834
835void QPSQLDriver::close()
836{
837 if (isOpen()) {
838
839 d->seid.clear();
840 if (d->sn) {
841 disconnect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
842 delete d->sn;
843 d->sn = 0;
844 }
845
846 if (d->connection)
847 PQfinish(d->connection);
848 d->connection = 0;
849 setOpen(false);
850 setOpenError(false);
851 }
852}
853
854QSqlResult *QPSQLDriver::createResult() const
855{
856 return new QPSQLResult(this, d);
857}
858
859bool QPSQLDriver::beginTransaction()
860{
861 if (!isOpen()) {
862 qWarning("QPSQLDriver::beginTransaction: Database not open");
863 return false;
864 }
865 PGresult* res = PQexec(d->connection, "BEGIN");
866 if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
867 PQclear(res);
868 setLastError(qMakeError(tr("Could not begin transaction"),
869 QSqlError::TransactionError, d));
870 return false;
871 }
872 PQclear(res);
873 return true;
874}
875
876bool QPSQLDriver::commitTransaction()
877{
878 if (!isOpen()) {
879 qWarning("QPSQLDriver::commitTransaction: Database not open");
880 return false;
881 }
882 PGresult* res = PQexec(d->connection, "COMMIT");
883
884 bool transaction_failed = false;
885
886 // XXX
887 // This hack is used to tell if the transaction has succeeded for the protocol versions of
888 // PostgreSQL below. For 7.x and other protocol versions we are left in the dark.
889 // This hack can dissapear once there is an API to query this sort of information.
890 if (d->pro == QPSQLDriver::Version8 ||
891 d->pro == QPSQLDriver::Version81 ||
892 d->pro == QPSQLDriver::Version82 ||
893 d->pro == QPSQLDriver::Version83 ||
894 d->pro == QPSQLDriver::Version84 ||
895 d->pro == QPSQLDriver::Version9) {
896 transaction_failed = qstrcmp(PQcmdStatus(res), "ROLLBACK") == 0;
897 }
898
899 if (!res || PQresultStatus(res) != PGRES_COMMAND_OK || transaction_failed) {
900 PQclear(res);
901 setLastError(qMakeError(tr("Could not commit transaction"),
902 QSqlError::TransactionError, d));
903 return false;
904 }
905 PQclear(res);
906 return true;
907}
908
909bool QPSQLDriver::rollbackTransaction()
910{
911 if (!isOpen()) {
912 qWarning("QPSQLDriver::rollbackTransaction: Database not open");
913 return false;
914 }
915 PGresult* res = PQexec(d->connection, "ROLLBACK");
916 if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
917 setLastError(qMakeError(tr("Could not rollback transaction"),
918 QSqlError::TransactionError, d));
919 PQclear(res);
920 return false;
921 }
922 PQclear(res);
923 return true;
924}
925
926QStringList QPSQLDriver::tables(QSql::TableType type) const
927{
928 QStringList tl;
929 if (!isOpen())
930 return tl;
931 QSqlQuery t(createResult());
932 t.setForwardOnly(true);
933
934 if (type & QSql::Tables)
935 d->appendTables(tl, t, QLatin1Char('r'));
936 if (type & QSql::Views)
937 d->appendTables(tl, t, QLatin1Char('v'));
938 if (type & QSql::SystemTables) {
939 t.exec(QLatin1String("select relname from pg_class where (relkind = 'r') "
940 "and (relname like 'pg_%') "));
941 while (t.next())
942 tl.append(t.value(0).toString());
943 }
944
945 return tl;
946}
947
948static void qSplitTableName(QString &tablename, QString &schema)
949{
950 int dot = tablename.indexOf(QLatin1Char('.'));
951 if (dot == -1)
952 return;
953 schema = tablename.left(dot);
954 tablename = tablename.mid(dot + 1);
955}
956
957QSqlIndex QPSQLDriver::primaryIndex(const QString& tablename) const
958{
959 QSqlIndex idx(tablename);
960 if (!isOpen())
961 return idx;
962 QSqlQuery i(createResult());
963 QString stmt;
964
965 QString tbl = tablename;
966 QString schema;
967 qSplitTableName(tbl, schema);
968
969 if (isIdentifierEscaped(tbl, QSqlDriver::TableName))
970 tbl = stripDelimiters(tbl, QSqlDriver::TableName);
971 else
972 tbl = tbl.toLower();
973
974 if (isIdentifierEscaped(schema, QSqlDriver::TableName))
975 schema = stripDelimiters(schema, QSqlDriver::TableName);
976 else
977 schema = schema.toLower();
978
979 switch(d->pro) {
980 case QPSQLDriver::Version6:
981 stmt = QLatin1String("select pg_att1.attname, int(pg_att1.atttypid), pg_cl.relname "
982 "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
983 "where pg_cl.relname = '%1_pkey' "
984 "and pg_cl.oid = pg_ind.indexrelid "
985 "and pg_att2.attrelid = pg_ind.indexrelid "
986 "and pg_att1.attrelid = pg_ind.indrelid "
987 "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
988 "order by pg_att2.attnum");
989 break;
990 case QPSQLDriver::Version7:
991 case QPSQLDriver::Version71:
992 stmt = QLatin1String("select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
993 "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
994 "where pg_cl.relname = '%1_pkey' "
995 "and pg_cl.oid = pg_ind.indexrelid "
996 "and pg_att2.attrelid = pg_ind.indexrelid "
997 "and pg_att1.attrelid = pg_ind.indrelid "
998 "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
999 "order by pg_att2.attnum");
1000 break;
1001 case QPSQLDriver::Version73:
1002 case QPSQLDriver::Version74:
1003 case QPSQLDriver::Version8:
1004 case QPSQLDriver::Version81:
1005 case QPSQLDriver::Version82:
1006 case QPSQLDriver::Version83:
1007 case QPSQLDriver::Version84:
1008 case QPSQLDriver::Version9:
1009 stmt = QLatin1String("SELECT pg_attribute.attname, pg_attribute.atttypid::int, "
1010 "pg_class.relname "
1011 "FROM pg_attribute, pg_class "
1012 "WHERE %1 pg_class.oid IN "
1013 "(SELECT indexrelid FROM pg_index WHERE indisprimary = true AND indrelid IN "
1014 " (SELECT oid FROM pg_class WHERE relname = '%2')) "
1015 "AND pg_attribute.attrelid = pg_class.oid "
1016 "AND pg_attribute.attisdropped = false "
1017 "ORDER BY pg_attribute.attnum");
1018 if (schema.isEmpty())
1019 stmt = stmt.arg(QLatin1String("pg_table_is_visible(pg_class.oid) AND"));
1020 else
1021 stmt = stmt.arg(QString::fromLatin1("pg_class.relnamespace = (select oid from "
1022 "pg_namespace where pg_namespace.nspname = '%1') AND ").arg(schema));
1023 break;
1024 }
1025
1026 i.exec(stmt.arg(tbl));
1027 while (i.isActive() && i.next()) {
1028 QSqlField f(i.value(0).toString(), qDecodePSQLType(i.value(1).toInt()));
1029 idx.append(f);
1030 idx.setName(i.value(2).toString());
1031 }
1032 return idx;
1033}
1034
1035QSqlRecord QPSQLDriver::record(const QString& tablename) const
1036{
1037 QSqlRecord info;
1038 if (!isOpen())
1039 return info;
1040
1041 QString tbl = tablename;
1042 QString schema;
1043 qSplitTableName(tbl, schema);
1044
1045 if (isIdentifierEscaped(tbl, QSqlDriver::TableName))
1046 tbl = stripDelimiters(tbl, QSqlDriver::TableName);
1047 else
1048 tbl = tbl.toLower();
1049
1050 if (isIdentifierEscaped(schema, QSqlDriver::TableName))
1051 schema = stripDelimiters(schema, QSqlDriver::TableName);
1052 else
1053 schema = schema.toLower();
1054
1055 QString stmt;
1056 switch(d->pro) {
1057 case QPSQLDriver::Version6:
1058 stmt = QLatin1String("select pg_attribute.attname, int(pg_attribute.atttypid), "
1059 "pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1060 "int(pg_attribute.attrelid), pg_attribute.attnum "
1061 "from pg_class, pg_attribute "
1062 "where pg_class.relname = '%1' "
1063 "and pg_attribute.attnum > 0 "
1064 "and pg_attribute.attrelid = pg_class.oid ");
1065 break;
1066 case QPSQLDriver::Version7:
1067 stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1068 "pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1069 "pg_attribute.attrelid::int, pg_attribute.attnum "
1070 "from pg_class, pg_attribute "
1071 "where pg_class.relname = '%1' "
1072 "and pg_attribute.attnum > 0 "
1073 "and pg_attribute.attrelid = pg_class.oid ");
1074 break;
1075 case QPSQLDriver::Version71:
1076 stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1077 "pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1078 "pg_attrdef.adsrc "
1079 "from pg_class, pg_attribute "
1080 "left join pg_attrdef on (pg_attrdef.adrelid = "
1081 "pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
1082 "where pg_class.relname = '%1' "
1083 "and pg_attribute.attnum > 0 "
1084 "and pg_attribute.attrelid = pg_class.oid "
1085 "order by pg_attribute.attnum ");
1086 break;
1087 case QPSQLDriver::Version73:
1088 case QPSQLDriver::Version74:
1089 case QPSQLDriver::Version8:
1090 case QPSQLDriver::Version81:
1091 case QPSQLDriver::Version82:
1092 case QPSQLDriver::Version83:
1093 case QPSQLDriver::Version84:
1094 case QPSQLDriver::Version9:
1095 stmt = QLatin1String("select pg_attribute.attname, pg_attribute.atttypid::int, "
1096 "pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, "
1097 "pg_attrdef.adsrc "
1098 "from pg_class, pg_attribute "
1099 "left join pg_attrdef on (pg_attrdef.adrelid = "
1100 "pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
1101 "where %1 "
1102 "and pg_class.relname = '%2' "
1103 "and pg_attribute.attnum > 0 "
1104 "and pg_attribute.attrelid = pg_class.oid "
1105 "and pg_attribute.attisdropped = false "
1106 "order by pg_attribute.attnum ");
1107 if (schema.isEmpty())
1108 stmt = stmt.arg(QLatin1String("pg_table_is_visible(pg_class.oid)"));
1109 else
1110 stmt = stmt.arg(QString::fromLatin1("pg_class.relnamespace = (select oid from "
1111 "pg_namespace where pg_namespace.nspname = '%1')").arg(schema));
1112 break;
1113 }
1114
1115 QSqlQuery query(createResult());
1116 query.exec(stmt.arg(tbl));
1117 if (d->pro >= QPSQLDriver::Version71) {
1118 while (query.next()) {
1119 int len = query.value(3).toInt();
1120 int precision = query.value(4).toInt();
1121 // swap length and precision if length == -1
1122 if (len == -1 && precision > -1) {
1123 len = precision - 4;
1124 precision = -1;
1125 }
1126 QString defVal = query.value(5).toString();
1127 if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\''))
1128 defVal = defVal.mid(1, defVal.length() - 2);
1129 QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()));
1130 f.setRequired(query.value(2).toBool());
1131 f.setLength(len);
1132 f.setPrecision(precision);
1133 f.setDefaultValue(defVal);
1134 f.setSqlType(query.value(1).toInt());
1135 info.append(f);
1136 }
1137 } else {
1138 // Postgres < 7.1 cannot handle outer joins
1139 while (query.next()) {
1140 QString defVal;
1141 QString stmt2 = QLatin1String("select pg_attrdef.adsrc from pg_attrdef where "
1142 "pg_attrdef.adrelid = %1 and pg_attrdef.adnum = %2 ");
1143 QSqlQuery query2(createResult());
1144 query2.exec(stmt2.arg(query.value(5).toInt()).arg(query.value(6).toInt()));
1145 if (query2.isActive() && query2.next())
1146 defVal = query2.value(0).toString();
1147 if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\''))
1148 defVal = defVal.mid(1, defVal.length() - 2);
1149 int len = query.value(3).toInt();
1150 int precision = query.value(4).toInt();
1151 // swap length and precision if length == -1
1152 if (len == -1 && precision > -1) {
1153 len = precision - 4;
1154 precision = -1;
1155 }
1156 QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()));
1157 f.setRequired(query.value(2).toBool());
1158 f.setLength(len);
1159 f.setPrecision(precision);
1160 f.setDefaultValue(defVal);
1161 f.setSqlType(query.value(1).toInt());
1162 info.append(f);
1163 }
1164 }
1165
1166 return info;
1167}
1168
1169QString QPSQLDriver::formatValue(const QSqlField &field, bool trimStrings) const
1170{
1171 QString r;
1172 if (field.isNull()) {
1173 r = QLatin1String("NULL");
1174 } else {
1175 switch (field.type()) {
1176 case QVariant::DateTime:
1177#ifndef QT_NO_DATESTRING
1178 if (field.value().toDateTime().isValid()) {
1179 QDate dt = field.value().toDateTime().date();
1180 QTime tm = field.value().toDateTime().time();
1181 // msecs need to be right aligned otherwise psql
1182 // interpretes them wrong
1183 r = QLatin1Char('\'') + QString::number(dt.year()) + QLatin1Char('-')
1184 + QString::number(dt.month()) + QLatin1Char('-')
1185 + QString::number(dt.day()) + QLatin1Char(' ')
1186 + tm.toString() + QLatin1Char('.')
1187 + QString::number(tm.msec()).rightJustified(3, QLatin1Char('0'))
1188 + QLatin1Char('\'');
1189 } else {
1190 r = QLatin1String("NULL");
1191 }
1192#else
1193 r = QLatin1String("NULL");
1194#endif // QT_NO_DATESTRING
1195 break;
1196 case QVariant::Time:
1197#ifndef QT_NO_DATESTRING
1198 if (field.value().toTime().isValid()) {
1199 r = QLatin1Char('\'') + field.value().toTime().toString(QLatin1String("hh:mm:ss.zzz")) + QLatin1Char('\'');
1200 } else
1201#endif
1202 {
1203 r = QLatin1String("NULL");
1204 }
1205 break;
1206 case QVariant::String:
1207 {
1208 // Escape '\' characters
1209 r = QSqlDriver::formatValue(field, trimStrings);
1210 r.replace(QLatin1String("\\"), QLatin1String("\\\\"));
1211 break;
1212 }
1213 case QVariant::Bool:
1214 if (field.value().toBool())
1215 r = QLatin1String("TRUE");
1216 else
1217 r = QLatin1String("FALSE");
1218 break;
1219 case QVariant::ByteArray: {
1220 QByteArray ba(field.value().toByteArray());
1221 size_t len;
1222#if defined PG_VERSION_NUM && PG_VERSION_NUM-0 >= 80200
1223 unsigned char *data = PQescapeByteaConn(d->connection, (unsigned char*)ba.constData(), ba.size(), &len);
1224#else
1225 unsigned char *data = PQescapeBytea((unsigned char*)ba.constData(), ba.size(), &len);
1226#endif
1227 r += QLatin1Char('\'');
1228 r += QLatin1String((const char*)data);
1229 r += QLatin1Char('\'');
1230 qPQfreemem(data);
1231 break;
1232 }
1233 case QVariant::Double: {
1234 double val = field.value().toDouble();
1235 if (isnan(val))
1236 r = QLatin1String("'NaN'");
1237 else {
1238 int res = isinf(val);
1239 if (res == 1)
1240 r = QLatin1String("'Infinity'");
1241 else if (res == -1)
1242 r = QLatin1String("'-Infinity'");
1243 else
1244 r = QSqlDriver::formatValue(field, trimStrings);
1245 }
1246 break;
1247 }
1248 default:
1249 r = QSqlDriver::formatValue(field, trimStrings);
1250 break;
1251 }
1252 }
1253 return r;
1254}
1255
1256QString QPSQLDriver::escapeIdentifier(const QString &identifier, IdentifierType) const
1257{
1258 QString res = identifier;
1259 if(!identifier.isEmpty() && !identifier.startsWith(QLatin1Char('"')) && !identifier.endsWith(QLatin1Char('"')) ) {
1260 res.replace(QLatin1Char('"'), QLatin1String("\"\""));
1261 res.prepend(QLatin1Char('"')).append(QLatin1Char('"'));
1262 res.replace(QLatin1Char('.'), QLatin1String("\".\""));
1263 }
1264 return res;
1265}
1266
1267bool QPSQLDriver::isOpen() const
1268{
1269 return PQstatus(d->connection) == CONNECTION_OK;
1270}
1271
1272QPSQLDriver::Protocol QPSQLDriver::protocol() const
1273{
1274 return d->pro;
1275}
1276
1277bool QPSQLDriver::subscribeToNotificationImplementation(const QString &name)
1278{
1279 if (!isOpen()) {
1280 qWarning("QPSQLDriver::subscribeToNotificationImplementation: database not open.");
1281 return false;
1282 }
1283
1284 if (d->seid.contains(name)) {
1285 qWarning("QPSQLDriver::subscribeToNotificationImplementation: already subscribing to '%s'.",
1286 qPrintable(name));
1287 return false;
1288 }
1289
1290 int socket = PQsocket(d->connection);
1291 if (socket) {
1292 QString query = QLatin1String("LISTEN ") + escapeIdentifier(name, QSqlDriver::TableName);
1293 if (PQresultStatus(PQexec(d->connection,
1294 d->isUtf8 ? query.toUtf8().constData()
1295 : query.toLocal8Bit().constData())
1296 ) != PGRES_COMMAND_OK) {
1297 setLastError(qMakeError(tr("Unable to subscribe"), QSqlError::StatementError, d));
1298 return false;
1299 }
1300
1301 if (!d->sn) {
1302 d->sn = new QSocketNotifier(socket, QSocketNotifier::Read);
1303 connect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
1304 }
1305 }
1306
1307 d->seid << name;
1308 return true;
1309}
1310
1311bool QPSQLDriver::unsubscribeFromNotificationImplementation(const QString &name)
1312{
1313 if (!isOpen()) {
1314 qWarning("QPSQLDriver::unsubscribeFromNotificationImplementation: database not open.");
1315 return false;
1316 }
1317
1318 if (!d->seid.contains(name)) {
1319 qWarning("QPSQLDriver::unsubscribeFromNotificationImplementation: not subscribed to '%s'.",
1320 qPrintable(name));
1321 return false;
1322 }
1323
1324 QString query = QLatin1String("UNLISTEN ") + escapeIdentifier(name, QSqlDriver::TableName);
1325 if (PQresultStatus(PQexec(d->connection,
1326 d->isUtf8 ? query.toUtf8().constData()
1327 : query.toLocal8Bit().constData())
1328 ) != PGRES_COMMAND_OK) {
1329 setLastError(qMakeError(tr("Unable to unsubscribe"), QSqlError::StatementError, d));
1330 return false;
1331 }
1332
1333 d->seid.removeAll(name);
1334
1335 if (d->seid.isEmpty()) {
1336 disconnect(d->sn, SIGNAL(activated(int)), this, SLOT(_q_handleNotification(int)));
1337 delete d->sn;
1338 d->sn = 0;
1339 }
1340
1341 return true;
1342}
1343
1344QStringList QPSQLDriver::subscribedToNotificationsImplementation() const
1345{
1346 return d->seid;
1347}
1348
1349void QPSQLDriver::_q_handleNotification(int)
1350{
1351 PQconsumeInput(d->connection);
1352
1353 PGnotify *notify = 0;
1354 while((notify = PQnotifies(d->connection)) != 0) {
1355 QString name(QLatin1String(notify->relname));
1356 if (d->seid.contains(name))
1357 emit notification(name);
1358 else
1359 qWarning("QPSQLDriver: received notification for '%s' which isn't subscribed to.",
1360 qPrintable(name));
1361
1362 qPQfreemem(notify);
1363 }
1364}
1365
1366QT_END_NAMESPACE
Note: See TracBrowser for help on using the repository browser.