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

Last change on this file since 117 was 2, checked in by Dmitry A. Kuminov, 16 years ago

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