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

Last change on this file was 196, checked in by rudi, 14 years ago

Add SQL module (currently it isn't build by default, however it's needed for QtDesigner)
File size: 31.5 KB
Line 
1/****************************************************************************
2**
3** Implementation of PostgreSQL driver classes
4**
5** Created : 001103
6**
7** Copyright (C) 1992-2007 Trolltech ASA. All rights reserved.
8**
9** This file is part of the sql module of the Qt GUI Toolkit.
10**
11** This file may be distributed under the terms of the Q Public License
12** as defined by Trolltech ASA of Norway and appearing in the file
13** LICENSE.QPL included in the packaging of this file.
14**
15** This file may be distributed and/or modified under the terms of the
16** GNU General Public License version 2 as published by the Free Software
17** Foundation and appearing in the file LICENSE.GPL included in the
18** packaging of this file.
19**
20** Licensees holding valid Qt Enterprise Edition licenses may use this
21** file in accordance with the Qt Commercial License Agreement provided
22** with the Software.
23**
24** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
25** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
26**
27** See http://www.trolltech.com/pricing.html or email sales@trolltech.com for
28** information about Qt Commercial License Agreements.
29** See http://www.trolltech.com/qpl/ for QPL licensing information.
30** See http://www.trolltech.com/gpl/ for GPL licensing information.
31**
32** Contact info@trolltech.com if any conditions of this licensing are
33** not clear to you.
34**
35**********************************************************************/
36
37#include "qsql_psql.h"
38#include <private/qsqlextension_p.h>
39
40#include <math.h>
41
42#include <qpointarray.h>
43#include <qsqlrecord.h>
44#include <qregexp.h>
45#include <qdatetime.h>
46// PostgreSQL header <utils/elog.h> included by <postgres.h> redefines DEBUG.
47#if defined(DEBUG)
48# undef DEBUG
49#endif
50#include <postgres.h>
51#include <libpq/libpq-fs.h>
52// PostgreSQL header <catalog/pg_type.h> redefines errno erroneously.
53#if defined(errno)
54# undef errno
55#endif
56#define errno qt_psql_errno
57#include <catalog/pg_type.h>
58#undef errno
59
60QPtrDict<QSqlDriverExtension> *qSqlDriverExtDict();
61QPtrDict<QSqlOpenExtension> *qSqlOpenExtDict();
62
63class QPSQLPrivate
64{
65public:
66 QPSQLPrivate():connection(0), result(0), isUtf8(FALSE) {}
67 PGconn *connection;
68 PGresult *result;
69 bool isUtf8;
70};
71
72class QPSQLDriverExtension : public QSqlDriverExtension
73{
74public:
75 QPSQLDriverExtension( QPSQLDriver *dri )
76 : QSqlDriverExtension(), driver(dri) { }
77 ~QPSQLDriverExtension() {}
78
79 bool isOpen() const;
80private:
81 QPSQLDriver *driver;
82};
83
84bool QPSQLDriverExtension::isOpen() const
85{
86 return PQstatus( driver->connection() ) == CONNECTION_OK;
87}
88
89class QPSQLOpenExtension : public QSqlOpenExtension
90{
91public:
92 QPSQLOpenExtension( QPSQLDriver *dri )
93 : QSqlOpenExtension(), driver(dri) { }
94 ~QPSQLOpenExtension() {}
95
96 bool open( const QString& db,
97 const QString& user,
98 const QString& password,
99 const QString& host,
100 int port,
101 const QString& connOpts );
102private:
103 QPSQLDriver *driver;
104};
105
106bool QPSQLOpenExtension::open( const QString& db,
107 const QString& user,
108 const QString& password,
109 const QString& host,
110 int port,
111 const QString& connOpts )
112{
113 return driver->open( db, user, password, host, port, connOpts );
114}
115
116static QSqlError qMakeError( const QString& err, int type, const QPSQLPrivate* p )
117{
118 const char *s = PQerrorMessage(p->connection);
119 QString msg = p->isUtf8 ? QString::fromUtf8(s) : QString::fromLocal8Bit(s);
120 return QSqlError("QPSQL: " + err, msg, type);
121}
122
123static QVariant::Type qDecodePSQLType( int t )
124{
125 QVariant::Type type = QVariant::Invalid;
126 switch ( t ) {
127 case BOOLOID :
128 type = QVariant::Bool;
129 break;
130 case INT8OID :
131 type = QVariant::LongLong;
132 break;
133 case INT2OID :
134 // case INT2VECTOROID : // 7.x
135 case INT4OID :
136 type = QVariant::Int;
137 break;
138 case NUMERICOID :
139 case FLOAT4OID :
140 case FLOAT8OID :
141 type = QVariant::Double;
142 break;
143 case ABSTIMEOID :
144 case RELTIMEOID :
145 case DATEOID :
146 type = QVariant::Date;
147 break;
148 case TIMEOID :
149#ifdef TIMETZOID // 7.x
150 case TIMETZOID :
151#endif
152 type = QVariant::Time;
153 break;
154 case TIMESTAMPOID :
155#ifdef DATETIMEOID
156 // Postgres 6.x datetime workaround.
157 // DATETIMEOID == TIMESTAMPOID (only the names have changed)
158 case DATETIMEOID :
159#endif
160#ifdef TIMESTAMPTZOID
161 // Postgres 7.2 workaround
162 // TIMESTAMPTZOID == TIMESTAMPOID == DATETIMEOID
163 case TIMESTAMPTZOID :
164#endif
165 type = QVariant::DateTime;
166 break;
167 // case ZPBITOID : // 7.x
168 // case VARBITOID : // 7.x
169 case OIDOID :
170 case BYTEAOID :
171 type = QVariant::ByteArray;
172 break;
173 case REGPROCOID :
174 case TIDOID :
175 case XIDOID :
176 case CIDOID :
177 // case OIDVECTOROID : // 7.x
178 case UNKNOWNOID :
179 // case TINTERVALOID : // 7.x
180 type = QVariant::Invalid;
181 break;
182 default:
183 case CHAROID :
184 case BPCHAROID :
185 // case LZTEXTOID : // 7.x
186 case VARCHAROID :
187 case TEXTOID :
188 case NAMEOID :
189 case CASHOID :
190 case INETOID :
191 case CIDROID :
192 case CIRCLEOID :
193 type = QVariant::String;
194 break;
195 }
196 return type;
197}
198
199QPSQLResult::QPSQLResult( const QPSQLDriver* db, const QPSQLPrivate* p )
200: QSqlResult( db ),
201 currentSize( 0 )
202{
203 d = new QPSQLPrivate();
204 (*d) = (*p);
205}
206
207QPSQLResult::~QPSQLResult()
208{
209 cleanup();
210 delete d;
211}
212
213PGresult* QPSQLResult::result()
214{
215 return d->result;
216}
217
218void QPSQLResult::cleanup()
219{
220 if ( d->result )
221 PQclear( d->result );
222 d->result = 0;
223 setAt( -1 );
224 currentSize = 0;
225 setActive( FALSE );
226}
227
228bool QPSQLResult::fetch( int i )
229{
230 if ( !isActive() )
231 return FALSE;
232 if ( i < 0 )
233 return FALSE;
234 if ( i >= currentSize )
235 return FALSE;
236 if ( at() == i )
237 return TRUE;
238 setAt( i );
239 return TRUE;
240}
241
242bool QPSQLResult::fetchFirst()
243{
244 return fetch( 0 );
245}
246
247bool QPSQLResult::fetchLast()
248{
249 return fetch( PQntuples( d->result ) - 1 );
250}
251
252// some Postgres conversions
253static QPoint pointFromString( const QString& s)
254{
255 // format '(x,y)'
256 int pivot = s.find( ',' );
257 if ( pivot != -1 ) {
258 int x = s.mid( 1, pivot-1 ).toInt();
259 int y = s.mid( pivot+1, s.length()-pivot-2 ).toInt();
260 return QPoint( x, y ) ;
261 } else
262 return QPoint();
263}
264
265QVariant QPSQLResult::data( int i )
266{
267 if ( i >= PQnfields( d->result ) ) {
268 qWarning( "QPSQLResult::data: column %d out of range", i );
269 return QVariant();
270 }
271 int ptype = PQftype( d->result, i );
272 QVariant::Type type = qDecodePSQLType( ptype );
273 const QString val = ( d->isUtf8 && ptype != BYTEAOID ) ?
274 QString::fromUtf8( PQgetvalue( d->result, at(), i ) ) :
275 QString::fromLocal8Bit( PQgetvalue( d->result, at(), i ) );
276 if ( PQgetisnull( d->result, at(), i ) ) {
277 QVariant v;
278 v.cast( type );
279 return v;
280 }
281 switch ( type ) {
282 case QVariant::Bool:
283 {
284 QVariant b ( (bool)(val == "t"), 0 );
285 return ( b );
286 }
287 case QVariant::String:
288 return QVariant( val );
289 case QVariant::LongLong:
290 if ( val[0] == '-' )
291 return QVariant( val.toLongLong() );
292 else
293 return QVariant( val.toULongLong() );
294 case QVariant::Int:
295 return QVariant( val.toInt() );
296 case QVariant::Double:
297 if ( ptype == NUMERICOID )
298 return QVariant( val );
299 return QVariant( val.toDouble() );
300 case QVariant::Date:
301 if ( val.isEmpty() ) {
302 return QVariant( QDate() );
303 } else {
304 return QVariant( QDate::fromString( val, Qt::ISODate ) );
305 }
306 case QVariant::Time:
307 if ( val.isEmpty() )
308 return QVariant( QTime() );
309 if ( val.at( val.length() - 3 ) == '+' )
310 // strip the timezone
311 return QVariant( QTime::fromString( val.left( val.length() - 3 ), Qt::ISODate ) );
312 return QVariant( QTime::fromString( val, Qt::ISODate ) );
313 case QVariant::DateTime: {
314 if ( val.length() < 10 )
315 return QVariant( QDateTime() );
316 // remove the timezone
317 QString dtval = val;
318 if ( dtval.at( dtval.length() - 3 ) == '+' )
319 dtval.truncate( dtval.length() - 3 );
320 // milliseconds are sometimes returned with 2 digits only
321 if ( dtval.at( dtval.length() - 3 ).isPunct() )
322 dtval += '0';
323 if ( dtval.isEmpty() )
324 return QVariant( QDateTime() );
325 else
326 return QVariant( QDateTime::fromString( dtval, Qt::ISODate ) );
327 }
328 case QVariant::Point:
329 return QVariant( pointFromString( val ) );
330 case QVariant::Rect: // format '(x,y),(x',y')'
331 {
332 int pivot = val.find( "),(" );
333 if ( pivot != -1 )
334 return QVariant( QRect( pointFromString( val.mid(pivot+2,val.length()) ), pointFromString( val.mid(0,pivot+1) ) ) );
335 return QVariant( QRect() );
336 }
337 case QVariant::PointArray: // format '((x,y),(x1,y1),...,(xn,yn))'
338 {
339 QRegExp pointPattern("\\([0-9-]*,[0-9-]*\\)");
340 int points = val.contains( pointPattern );
341 QPointArray parray( points );
342 int idx = 1;
343 for ( int i = 0; i < points; i++ ){
344 int start = val.find( pointPattern, idx );
345 int end = -1;
346 if ( start != -1 ) {
347 end = val.find( ')', start+1 );
348 if ( end != -1 ) {
349 parray.setPoint( i, pointFromString( val.mid(idx, end-idx+1) ) );
350 }
351 else
352 parray.setPoint( i, QPoint() );
353 } else {
354 parray.setPoint( i, QPoint() );
355 break;
356 }
357 idx = end+2;
358 }
359 return QVariant( parray );
360 }
361 case QVariant::ByteArray: {
362 if ( ptype == BYTEAOID ) {
363 uint i = 0;
364 int index = 0;
365 uint len = val.length();
366 static const QChar backslash( '\\' );
367 QByteArray ba( (int)len );
368 while ( i < len ) {
369 if ( val.at( i ) == backslash ) {
370 if ( val.at( i + 1 ).isDigit() ) {
371 ba[ index++ ] = (char)(val.mid( i + 1, 3 ).toInt( 0, 8 ));
372 i += 4;
373 } else {
374 ba[ index++ ] = val.at( i + 1 );
375 i += 2;
376 }
377 } else {
378 ba[ index++ ] = val.at( i++ ).unicode();
379 }
380 }
381 ba.resize( index );
382 return QVariant( ba );
383 }
384
385 QByteArray ba;
386 ((QSqlDriver*)driver())->beginTransaction();
387 Oid oid = val.toInt();
388 int fd = lo_open( d->connection, oid, INV_READ );
389#ifdef QT_CHECK_RANGE
390 if ( fd < 0) {
391 qWarning( "QPSQLResult::data: unable to open large object for read" );
392 ((QSqlDriver*)driver())->commitTransaction();
393 return QVariant( ba );
394 }
395#endif
396 int size = 0;
397 int retval = lo_lseek( d->connection, fd, 0L, SEEK_END );
398 if ( retval >= 0 ) {
399 size = lo_tell( d->connection, fd );
400 lo_lseek( d->connection, fd, 0L, SEEK_SET );
401 }
402 if ( size == 0 ) {
403 lo_close( d->connection, fd );
404 ((QSqlDriver*)driver())->commitTransaction();
405 return QVariant( ba );
406 }
407 char * buf = new char[ size ];
408
409#ifdef Q_OS_WIN32
410 // ### For some reason lo_read() fails if we try to read more than
411 // ### 32760 bytes
412 char * p = buf;
413 int nread = 0;
414
415 while( size < nread ){
416 retval = lo_read( d->connection, fd, p, 32760 );
417 nread += retval;
418 p += retval;
419 }
420#else
421 retval = lo_read( d->connection, fd, buf, size );
422#endif
423
424 if (retval < 0) {
425 qWarning( "QPSQLResult::data: unable to read large object" );
426 } else {
427 ba.duplicate( buf, size );
428 }
429 delete [] buf;
430 lo_close( d->connection, fd );
431 ((QSqlDriver*)driver())->commitTransaction();
432 return QVariant( ba );
433 }
434 default:
435 case QVariant::Invalid:
436#ifdef QT_CHECK_RANGE
437 qWarning("QPSQLResult::data: unknown data type");
438#endif
439 ;
440 }
441 return QVariant();
442}
443
444bool QPSQLResult::isNull( int field )
445{
446 PQgetvalue( d->result, at(), field );
447 return PQgetisnull( d->result, at(), field );
448}
449
450bool QPSQLResult::reset ( const QString& query )
451{
452 cleanup();
453 if ( !driver() )
454 return FALSE;
455 if ( !driver()->isOpen() || driver()->isOpenError() )
456 return FALSE;
457 setActive( FALSE );
458 setAt( QSql::BeforeFirst );
459 if ( d->result )
460 PQclear( d->result );
461 if ( d->isUtf8 ) {
462 d->result = PQexec( d->connection, query.utf8().data() );
463 } else {
464 d->result = PQexec( d->connection, query.local8Bit().data() );
465 }
466 int status = PQresultStatus( d->result );
467 if ( status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK ) {
468 if ( status == PGRES_TUPLES_OK ) {
469 setSelect( TRUE );
470 currentSize = PQntuples( d->result );
471 } else {
472 setSelect( FALSE );
473 currentSize = -1;
474 }
475 setActive( TRUE );
476 return TRUE;
477 }
478 setLastError( qMakeError( "Unable to create query", QSqlError::Statement, d ) );
479 return FALSE;
480}
481
482int QPSQLResult::size()
483{
484 return currentSize;
485}
486
487int QPSQLResult::numRowsAffected()
488{
489 return QString( PQcmdTuples( d->result ) ).toInt();
490}
491
492///////////////////////////////////////////////////////////////////
493
494static bool setEncodingUtf8( PGconn* connection )
495{
496 PGresult* result = PQexec( connection, "SET CLIENT_ENCODING TO 'UNICODE'" );
497 int status = PQresultStatus( result );
498 PQclear( result );
499 return status == PGRES_COMMAND_OK;
500}
501
502static void setDatestyle( PGconn* connection )
503{
504 PGresult* result = PQexec( connection, "SET DATESTYLE TO 'ISO'" );
505#ifdef QT_CHECK_RANGE
506 int status = PQresultStatus( result );
507 if ( status != PGRES_COMMAND_OK )
508 qWarning( "%s", PQerrorMessage( connection ) );
509#endif
510 PQclear( result );
511}
512
513static QPSQLDriver::Protocol getPSQLVersion( PGconn* connection )
514{
515 PGresult* result = PQexec( connection, "select version()" );
516 int status = PQresultStatus( result );
517 if ( status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK ) {
518 QString val( PQgetvalue( result, 0, 0 ) );
519 PQclear( result );
520 QRegExp rx( "(\\d+)\\.(\\d+)" );
521 rx.setMinimal ( TRUE ); // enforce non-greedy RegExp
522 if ( rx.search( val ) != -1 ) {
523 int vMaj = rx.cap( 1 ).toInt();
524 int vMin = rx.cap( 2 ).toInt();
525 if ( vMaj < 6 ) {
526#ifdef QT_CHECK_RANGE
527 qWarning( "This version of PostgreSQL is not supported and may not work." );
528#endif
529 return QPSQLDriver::Version6;
530 }
531 if ( vMaj == 6 ) {
532 return QPSQLDriver::Version6;
533 } else if ( vMaj == 7 ) {
534 if ( vMin < 1 )
535 return QPSQLDriver::Version7;
536 else if ( vMin < 3 )
537 return QPSQLDriver::Version71;
538 }
539 return QPSQLDriver::Version73;
540 }
541 } else {
542#ifdef QT_CHECK_RANGE
543 qWarning( "This version of PostgreSQL is not supported and may not work." );
544#endif
545 }
546
547 return QPSQLDriver::Version6;
548}
549
550QPSQLDriver::QPSQLDriver( QObject * parent, const char * name )
551 : QSqlDriver(parent,name ? name : "QPSQL"), pro( QPSQLDriver::Version6 )
552{
553 init();
554}
555
556QPSQLDriver::QPSQLDriver( PGconn * conn, QObject * parent, const char * name )
557 : QSqlDriver(parent,name ? name : "QPSQL"), pro( QPSQLDriver::Version6 )
558{
559 init();
560 d->connection = conn;
561 if ( conn ) {
562 pro = getPSQLVersion( d->connection );
563 setOpen( TRUE );
564 setOpenError( FALSE );
565 }
566}
567
568void QPSQLDriver::init()
569{
570 qSqlDriverExtDict()->insert( this, new QPSQLDriverExtension(this) );
571 qSqlOpenExtDict()->insert( this, new QPSQLOpenExtension(this) );
572
573 d = new QPSQLPrivate();
574}
575
576QPSQLDriver::~QPSQLDriver()
577{
578 if ( d->connection )
579 PQfinish( d->connection );
580 delete d;
581 if ( !qSqlDriverExtDict()->isEmpty() ) {
582 QSqlDriverExtension *ext = qSqlDriverExtDict()->take( this );
583 delete ext;
584 }
585 if ( !qSqlOpenExtDict()->isEmpty() ) {
586 QSqlOpenExtension *ext = qSqlOpenExtDict()->take( this );
587 delete ext;
588 }
589}
590
591PGconn* QPSQLDriver::connection()
592{
593 return d->connection;
594}
595
596
597bool QPSQLDriver::hasFeature( DriverFeature f ) const
598{
599 switch ( f ) {
600 case Transactions:
601 return TRUE;
602 case QuerySize:
603 return TRUE;
604 case BLOB:
605 return pro >= QPSQLDriver::Version71;
606 case Unicode:
607 return d->isUtf8;
608 default:
609 return FALSE;
610 }
611}
612
613bool QPSQLDriver::open( const QString&,
614 const QString&,
615 const QString&,
616 const QString&,
617 int )
618{
619 qWarning("QPSQLDriver::open(): This version of open() is no longer supported." );
620 return FALSE;
621}
622
623bool QPSQLDriver::open( const QString & db,
624 const QString & user,
625 const QString & password,
626 const QString & host,
627 int port,
628 const QString& connOpts )
629{
630 if ( isOpen() )
631 close();
632 QString connectString;
633 if ( host.length() )
634 connectString.append( "host=" ).append( host );
635 if ( db.length() )
636 connectString.append( " dbname=" ).append( db );
637 if ( user.length() )
638 connectString.append( " user=" ).append( user );
639 if ( password.length() )
640 connectString.append( " password=" ).append( password );
641 if ( port > -1 )
642 connectString.append( " port=" ).append( QString::number( port ) );
643
644 // add any connect options - the server will handle error detection
645 if ( !connOpts.isEmpty() )
646 connectString += " " + QStringList::split( ';', connOpts ).join( " " );
647
648 d->connection = PQconnectdb( connectString.local8Bit().data() );
649 if ( PQstatus( d->connection ) == CONNECTION_BAD ) {
650 setLastError( qMakeError("Unable to connect", QSqlError::Connection, d ) );
651 setOpenError( TRUE );
652 return FALSE;
653 }
654
655 pro = getPSQLVersion( d->connection );
656 d->isUtf8 = setEncodingUtf8( d->connection );
657 setDatestyle( d->connection );
658
659 setOpen( TRUE );
660 setOpenError( FALSE );
661 return TRUE;
662}
663
664void QPSQLDriver::close()
665{
666 if ( isOpen() ) {
667 if (d->connection)
668 PQfinish( d->connection );
669 d->connection = 0;
670 setOpen( FALSE );
671 setOpenError( FALSE );
672 }
673}
674
675QSqlQuery QPSQLDriver::createQuery() const
676{
677 return QSqlQuery( new QPSQLResult( this, d ) );
678}
679
680bool QPSQLDriver::beginTransaction()
681{
682 if ( !isOpen() ) {
683#ifdef QT_CHECK_RANGE
684 qWarning( "QPSQLDriver::beginTransaction: Database not open" );
685#endif
686 return FALSE;
687 }
688 PGresult* res = PQexec( d->connection, "BEGIN" );
689 if ( !res || PQresultStatus( res ) != PGRES_COMMAND_OK ) {
690 PQclear( res );
691 setLastError( qMakeError( "Could not begin transaction", QSqlError::Transaction, d ) );
692 return FALSE;
693 }
694 PQclear( res );
695 return TRUE;
696}
697
698bool QPSQLDriver::commitTransaction()
699{
700 if ( !isOpen() ) {
701#ifdef QT_CHECK_RANGE
702 qWarning( "QPSQLDriver::commitTransaction: Database not open" );
703#endif
704 return FALSE;
705 }
706 PGresult* res = PQexec( d->connection, "COMMIT" );
707 if ( !res || PQresultStatus( res ) != PGRES_COMMAND_OK ) {
708 PQclear( res );
709 setLastError( qMakeError( "Could not commit transaction", QSqlError::Transaction, d ) );
710 return FALSE;
711 }
712 PQclear( res );
713 return TRUE;
714}
715
716bool QPSQLDriver::rollbackTransaction()
717{
718 if ( !isOpen() ) {
719#ifdef QT_CHECK_RANGE
720 qWarning( "QPSQLDriver::rollbackTransaction: Database not open" );
721#endif
722 return FALSE;
723 }
724 PGresult* res = PQexec( d->connection, "ROLLBACK" );
725 if ( !res || PQresultStatus( res ) != PGRES_COMMAND_OK ) {
726 setLastError( qMakeError( "Could not rollback transaction", QSqlError::Transaction, d ) );
727 PQclear( res );
728 return FALSE;
729 }
730 PQclear( res );
731 return TRUE;
732}
733
734QStringList QPSQLDriver::tables( const QString& typeName ) const
735{
736 QStringList tl;
737 if ( !isOpen() )
738 return tl;
739 int type = typeName.toInt();
740 QSqlQuery t = createQuery();
741 t.setForwardOnly( TRUE );
742
743 if ( typeName.isEmpty() || ((type & (int)QSql::Tables) == (int)QSql::Tables) ) {
744
745 QString query("select relname from pg_class where (relkind = 'r') "
746 "and (relname !~ '^Inv') "
747 "and (relname !~ '^pg_') ");
748 if (pro >= QPSQLDriver::Version73)
749 query.append("and (relnamespace not in "
750 "(select oid from pg_namespace where nspname = 'information_schema')) "
751 "and pg_table_is_visible(pg_class.oid) ");
752 t.exec(query);
753 while ( t.next() )
754 tl.append( t.value(0).toString() );
755 }
756 if ( (type & (int)QSql::Views) == (int)QSql::Views ) {
757 QString query("select relname from pg_class where ( relkind = 'v' ) "
758 "and ( relname !~ '^Inv' ) "
759 "and ( relname !~ '^pg_' ) ");
760 if (pro >= QPSQLDriver::Version73)
761 query.append("and (relnamespace not in "
762 "(select oid from pg_namespace where nspname = 'information_schema')) "
763 "and pg_table_is_visible(pg_class.oid) ");
764 t.exec(query);
765 while ( t.next() )
766 tl.append( t.value(0).toString() );
767 }
768 if ( (type & (int)QSql::SystemTables) == (int)QSql::SystemTables ) {
769 QString query( "select relname from pg_class where ( relkind = 'r' ) "
770 "and ( relname like 'pg_%' ) " );
771 if (pro >= QPSQLDriver::Version73)
772 query.append( "and pg_table_is_visible(pg_class.oid) " );
773 t.exec(query);
774 while ( t.next() )
775 tl.append( t.value(0).toString() );
776 }
777
778 return tl;
779}
780
781QSqlIndex QPSQLDriver::primaryIndex( const QString& tablename ) const
782{
783 QSqlIndex idx( tablename );
784 if ( !isOpen() )
785 return idx;
786 QSqlQuery i = createQuery();
787 QString stmt;
788
789 switch( pro ) {
790 case QPSQLDriver::Version6:
791 stmt = "select pg_att1.attname, int(pg_att1.atttypid), pg_att2.attnum, pg_cl.relname "
792 "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
793 "where lower(pg_cl.relname) = '%1_pkey' ";
794 break;
795 case QPSQLDriver::Version7:
796 case QPSQLDriver::Version71:
797 stmt = "select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
798 "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
799 "where lower(pg_cl.relname) = '%1_pkey' ";
800 break;
801 case QPSQLDriver::Version73:
802 stmt = "select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
803 "from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
804 "where lower(pg_cl.relname) = '%1_pkey' "
805 "and pg_table_is_visible(pg_cl.oid) "
806 "and pg_att1.attisdropped = false ";
807 break;
808 }
809 stmt += "and pg_cl.oid = pg_ind.indexrelid "
810 "and pg_att2.attrelid = pg_ind.indexrelid "
811 "and pg_att1.attrelid = pg_ind.indrelid "
812 "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
813 "order by pg_att2.attnum";
814
815 i.exec( stmt.arg( tablename.lower() ) );
816 while ( i.isActive() && i.next() ) {
817 QSqlField f( i.value(0).toString(), qDecodePSQLType( i.value(1).toInt() ) );
818 idx.append( f );
819 idx.setName( i.value(2).toString() );
820 }
821 return idx;
822}
823
824QSqlRecord QPSQLDriver::record( const QString& tablename ) const
825{
826 QSqlRecord fil;
827 if ( !isOpen() )
828 return fil;
829 QString stmt;
830 switch( pro ) {
831 case QPSQLDriver::Version6:
832 stmt = "select pg_attribute.attname, int(pg_attribute.atttypid) "
833 "from pg_class, pg_attribute "
834 "where lower(pg_class.relname) = '%1' "
835 "and pg_attribute.attnum > 0 "
836 "and pg_attribute.attrelid = pg_class.oid ";
837 break;
838 case QPSQLDriver::Version7:
839 case QPSQLDriver::Version71:
840 stmt = "select pg_attribute.attname, pg_attribute.atttypid::int "
841 "from pg_class, pg_attribute "
842 "where lower(pg_class.relname) = '%1' "
843 "and pg_attribute.attnum > 0 "
844 "and pg_attribute.attrelid = pg_class.oid ";
845 break;
846 case QPSQLDriver::Version73:
847 stmt = "select pg_attribute.attname, pg_attribute.atttypid::int "
848 "from pg_class, pg_attribute "
849 "where lower(pg_class.relname) = '%1' "
850 "and pg_table_is_visible(pg_class.oid) "
851 "and pg_attribute.attnum > 0 "
852 "and pg_attribute.attisdropped = false "
853 "and pg_attribute.attrelid = pg_class.oid ";
854 break;
855 }
856
857 QSqlQuery fi = createQuery();
858 fi.exec( stmt.arg( tablename.lower() ) );
859 while ( fi.next() ) {
860 QSqlField f( fi.value(0).toString(), qDecodePSQLType( fi.value(1).toInt() ) );
861 fil.append( f );
862 }
863 return fil;
864}
865
866QSqlRecord QPSQLDriver::record( const QSqlQuery& query ) const
867{
868 QSqlRecord fil;
869 if ( !isOpen() )
870 return fil;
871 if ( query.isActive() && query.driver() == this ) {
872 QPSQLResult* result = (QPSQLResult*)query.result();
873 int count = PQnfields( result->d->result );
874 for ( int i = 0; i < count; ++i ) {
875 QString name = PQfname( result->d->result, i );
876 QVariant::Type type = qDecodePSQLType( PQftype( result->d->result, i ) );
877 QSqlField rf( name, type );
878 fil.append( rf );
879 }
880 }
881 return fil;
882}
883
884QSqlRecordInfo QPSQLDriver::recordInfo( const QString& tablename ) const
885{
886 QSqlRecordInfo info;
887 if ( !isOpen() )
888 return info;
889
890 QString stmt;
891 switch( pro ) {
892 case QPSQLDriver::Version6:
893 stmt = "select pg_attribute.attname, int(pg_attribute.atttypid), pg_attribute.attnotnull, "
894 "pg_attribute.attlen, pg_attribute.atttypmod, int(pg_attribute.attrelid), pg_attribute.attnum "
895 "from pg_class, pg_attribute "
896 "where lower(pg_class.relname) = '%1' "
897 "and pg_attribute.attnum > 0 "
898 "and pg_attribute.attrelid = pg_class.oid ";
899 break;
900 case QPSQLDriver::Version7:
901 stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
902 "pg_attribute.attlen, pg_attribute.atttypmod, pg_attribute.attrelid::int, pg_attribute.attnum "
903 "from pg_class, pg_attribute "
904 "where lower(pg_class.relname) = '%1' "
905 "and pg_attribute.attnum > 0 "
906 "and pg_attribute.attrelid = pg_class.oid ";
907 break;
908 case QPSQLDriver::Version71:
909 stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
910 "pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc "
911 "from pg_class, pg_attribute "
912 "left join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
913 "where lower(pg_class.relname) = '%1' "
914 "and pg_attribute.attnum > 0 "
915 "and pg_attribute.attrelid = pg_class.oid "
916 "order by pg_attribute.attnum ";
917 break;
918 case QPSQLDriver::Version73:
919 stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
920 "pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc "
921 "from pg_class, pg_attribute "
922 "left join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
923 "where lower(pg_class.relname) = '%1' "
924 "and pg_table_is_visible(pg_class.oid) "
925 "and pg_attribute.attnum > 0 "
926 "and pg_attribute.attrelid = pg_class.oid "
927 "and pg_attribute.attisdropped = false "
928 "order by pg_attribute.attnum ";
929 break;
930 }
931
932 QSqlQuery query = createQuery();
933 query.exec( stmt.arg( tablename.lower() ) );
934 if ( pro >= QPSQLDriver::Version71 ) {
935 while ( query.next() ) {
936 int len = query.value( 3 ).toInt();
937 int precision = query.value( 4 ).toInt();
938 // swap length and precision if length == -1
939 if ( len == -1 && precision > -1 ) {
940 len = precision - 4;
941 precision = -1;
942 }
943 QString defVal = query.value( 5 ).toString();
944 if ( !defVal.isEmpty() && defVal.startsWith( "'" ) )
945 defVal = defVal.mid( 1, defVal.length() - 2 );
946 info.append( QSqlFieldInfo( query.value( 0 ).toString(),
947 qDecodePSQLType( query.value( 1 ).toInt() ),
948 query.value( 2 ).toBool(),
949 len,
950 precision,
951 defVal,
952 query.value( 1 ).toInt() ) );
953 }
954 } else {
955 // Postgres < 7.1 cannot handle outer joins
956 while ( query.next() ) {
957 QString defVal;
958 QString stmt2 = "select pg_attrdef.adsrc from pg_attrdef where "
959 "pg_attrdef.adrelid = %1 and pg_attrdef.adnum = %2 ";
960 QSqlQuery query2 = createQuery();
961 query2.exec( stmt2.arg( query.value( 5 ).toInt() ).arg( query.value( 6 ).toInt() ) );
962 if ( query2.isActive() && query2.next() )
963 defVal = query2.value( 0 ).toString();
964 if ( !defVal.isEmpty() && defVal.startsWith( "'" ) )
965 defVal = defVal.mid( 1, defVal.length() - 2 );
966 int len = query.value( 3 ).toInt();
967 int precision = query.value( 4 ).toInt();
968 // swap length and precision if length == -1
969 if ( len == -1 && precision > -1 ) {
970 len = precision - 4;
971 precision = -1;
972 }
973 info.append( QSqlFieldInfo( query.value( 0 ).toString(),
974 qDecodePSQLType( query.value( 1 ).toInt() ),
975 query.value( 2 ).toBool(),
976 len,
977 precision,
978 defVal,
979 query.value( 1 ).toInt() ) );
980 }
981 }
982
983 return info;
984}
985
986QSqlRecordInfo QPSQLDriver::recordInfo( const QSqlQuery& query ) const
987{
988 QSqlRecordInfo info;
989 if ( !isOpen() )
990 return info;
991 if ( query.isActive() && query.driver() == this ) {
992 QPSQLResult* result = (QPSQLResult*)query.result();
993 int count = PQnfields( result->d->result );
994 for ( int i = 0; i < count; ++i ) {
995 QString name = PQfname( result->d->result, i );
996 int len = PQfsize( result->d->result, i );
997 int precision = PQfmod( result->d->result, i );
998 // swap length and precision if length == -1
999 if ( len == -1 && precision > -1 ) {
1000 len = precision - 4;
1001 precision = -1;
1002 }
1003 info.append( QSqlFieldInfo( name,
1004 qDecodePSQLType( PQftype( result->d->result, i ) ),
1005 -1,
1006 len,
1007 precision,
1008 QVariant(),
1009 PQftype( result->d->result, i ) ) );
1010 }
1011 }
1012 return info;
1013}
1014
1015QString QPSQLDriver::formatValue( const QSqlField* field,
1016 bool ) const
1017{
1018 QString r;
1019 if ( field->isNull() ) {
1020 r = nullText();
1021 } else {
1022 switch ( field->type() ) {
1023 case QVariant::DateTime:
1024 if ( field->value().toDateTime().isValid() ) {
1025 QDate dt = field->value().toDateTime().date();
1026 QTime tm = field->value().toDateTime().time();
1027 // msecs need to be right aligned otherwise psql
1028 // interpretes them wrong
1029 r = "'" + QString::number( dt.year() ) + "-" +
1030 QString::number( dt.month() ) + "-" +
1031 QString::number( dt.day() ) + " " +
1032 tm.toString() + "." +
1033 QString::number( tm.msec() ).rightJustify( 3, '0' ) + "'";
1034 } else {
1035 r = nullText();
1036 }
1037 break;
1038 case QVariant::Time:
1039 if ( field->value().toTime().isValid() ) {
1040 r = field->value().toTime().toString( Qt::ISODate );
1041 } else {
1042 r = nullText();
1043 }
1044 case QVariant::String:
1045 case QVariant::CString: {
1046 switch ( field->value().type() ) {
1047 case QVariant::Rect: {
1048 QRect rec = field->value().toRect();
1049 // upper right corner then lower left according to psql docs
1050 r = "'(" + QString::number( rec.right() ) +
1051 "," + QString::number( rec.bottom() ) +
1052 "),(" + QString::number( rec.left() ) +
1053 "," + QString::number( rec.top() ) + ")'";
1054 break;
1055 }
1056 case QVariant::Point: {
1057 QPoint p = field->value().toPoint();
1058 r = "'(" + QString::number( p.x() ) +
1059 "," + QString::number( p.y() ) + ")'";
1060 break;
1061 }
1062 case QVariant::PointArray: {
1063 QPointArray pa = field->value().toPointArray();
1064 r = "' ";
1065 for ( int i = 0; i < (int)pa.size(); ++i ) {
1066 r += "(" + QString::number( pa[i].x() ) +
1067 "," + QString::number( pa[i].y() ) + "),";
1068 }
1069 r.truncate( r.length() - 1 );
1070 r += "'";
1071 break;
1072 }
1073 default:
1074 // Escape '\' characters
1075 r = QSqlDriver::formatValue( field );
1076 r.replace( "\\", "\\\\" );
1077 break;
1078 }
1079 break;
1080 }
1081 case QVariant::Bool:
1082 if ( field->value().toBool() )
1083 r = "TRUE";
1084 else
1085 r = "FALSE";
1086 break;
1087 case QVariant::ByteArray: {
1088 QByteArray ba = field->value().asByteArray();
1089 QString res;
1090 r = "'";
1091 unsigned char uc;
1092 for ( int i = 0; i < (int)ba.size(); ++i ) {
1093 uc = (unsigned char) ba[ i ];
1094 if ( uc > 40 && uc < 92 ) {
1095 r += uc;
1096 } else {
1097 r += "\\\\";
1098 r += QString::number( (unsigned char) ba[ i ], 8 ).rightJustify( 3, '0', TRUE );
1099 }
1100 }
1101 r += "'";
1102 break;
1103 }
1104 default:
1105 r = QSqlDriver::formatValue( field );
1106 break;
1107 }
1108 }
1109 return r;
1110}
Note: See TracBrowser for help on using the repository browser.