qsql_psql.cpp

qt专门用于嵌入式的图形开发GUI

    if ( typeName.isEmpty() || ((type & (int)QSql::Tables) == (int)QSql::Tables) ) {

        QString query("select relname from pg_class where (relkind = 'r') "
                      "and (relname !~ '^Inv') "
                      "and (relname !~ '^pg_') ");
        if (pro >= QPSQLDriver::Version73)
            query.append("and (relnamespace not in "
                         "(select oid from pg_namespace where nspname = 'information_schema')) "
                         "and pg_table_is_visible(pg_class.oid) ");
        t.exec(query);
	while ( t.next() )
	    tl.append( t.value(0).toString() );
    }
    if ( (type & (int)QSql::Views) == (int)QSql::Views ) {
        QString query("select relname from pg_class where ( relkind = 'v' ) "
                "and ( relname !~ '^Inv' ) "
                "and ( relname !~ '^pg_' ) ");
        if (pro >= QPSQLDriver::Version73)
            query.append("and (relnamespace not in "
                         "(select oid from pg_namespace where nspname = 'information_schema')) "
                         "and pg_table_is_visible(pg_class.oid) ");
        t.exec(query);
	while ( t.next() )
	    tl.append( t.value(0).toString() );
    }
    if ( (type & (int)QSql::SystemTables) == (int)QSql::SystemTables ) {
        QString query( "select relname from pg_class where ( relkind = 'r' ) "
		"and ( relname like 'pg_%' ) " );
        if (pro >= QPSQLDriver::Version73)
		query.append( "and pg_table_is_visible(pg_class.oid) " );
        t.exec(query);
	while ( t.next() )
	    tl.append( t.value(0).toString() );
    }

    return tl;
}

QSqlIndex QPSQLDriver::primaryIndex( const QString& tablename ) const
{
    QSqlIndex idx( tablename );
    if ( !isOpen() )
	return idx;
    QSqlQuery i = createQuery();
    QString stmt;

    switch( pro ) {
    case QPSQLDriver::Version6:
	stmt = "select pg_att1.attname, int(pg_att1.atttypid), pg_att2.attnum, pg_cl.relname "
		"from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
		"where lower(pg_cl.relname) = '%1_pkey' ";
	break;
    case QPSQLDriver::Version7:
    case QPSQLDriver::Version71:
	stmt = "select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
		"from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
		"where lower(pg_cl.relname) = '%1_pkey' ";
	break;
    case QPSQLDriver::Version73:
	stmt = "select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname "
		"from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind "
		"where lower(pg_cl.relname) = '%1_pkey' "
		"and pg_table_is_visible(pg_cl.oid) "
		"and pg_att1.attisdropped = false ";
	break;
    }
    stmt += "and pg_cl.oid = pg_ind.indexrelid "
	    "and pg_att2.attrelid = pg_ind.indexrelid "
	    "and pg_att1.attrelid = pg_ind.indrelid "
	    "and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] "
	    "order by pg_att2.attnum";

    i.exec( stmt.arg( tablename.lower() ) );
    while ( i.isActive() && i.next() ) {
	QSqlField f( i.value(0).toString(), qDecodePSQLType( i.value(1).toInt() ) );
	idx.append( f );
	idx.setName( i.value(2).toString() );
    }
    return idx;
}

QSqlRecord QPSQLDriver::record( const QString& tablename ) const
{
    QSqlRecord fil;
    if ( !isOpen() )
	return fil;
    QString stmt;
    switch( pro ) {
    case QPSQLDriver::Version6:
	stmt = "select pg_attribute.attname, int(pg_attribute.atttypid) "
			"from pg_class, pg_attribute "
			"where lower(pg_class.relname) = '%1' "
			"and pg_attribute.attnum > 0 "
			"and pg_attribute.attrelid = pg_class.oid ";
	break;
    case QPSQLDriver::Version7:
    case QPSQLDriver::Version71:
	stmt = "select pg_attribute.attname, pg_attribute.atttypid::int "
			"from pg_class, pg_attribute "
			"where lower(pg_class.relname) = '%1' "
			"and pg_attribute.attnum > 0 "
			"and pg_attribute.attrelid = pg_class.oid ";
	break;
    case QPSQLDriver::Version73:
	stmt = "select pg_attribute.attname, pg_attribute.atttypid::int "
			"from pg_class, pg_attribute "
			"where lower(pg_class.relname) = '%1' "
			"and pg_table_is_visible(pg_class.oid) "
			"and pg_attribute.attnum > 0 "
			"and pg_attribute.attisdropped = false "
			"and pg_attribute.attrelid = pg_class.oid ";
	break;
    }

    QSqlQuery fi = createQuery();
    fi.exec( stmt.arg( tablename.lower() ) );
    while ( fi.next() ) {
	QSqlField f( fi.value(0).toString(), qDecodePSQLType( fi.value(1).toInt() ) );
	fil.append( f );
    }
    return fil;
}

QSqlRecord QPSQLDriver::record( const QSqlQuery& query ) const
{
    QSqlRecord fil;
    if ( !isOpen() )
	return fil;
    if ( query.isActive() && query.driver() == this ) {
	QPSQLResult* result = (QPSQLResult*)query.result();
	int count = PQnfields( result->d->result );
	for ( int i = 0; i < count; ++i ) {
	    QString name = PQfname( result->d->result, i );
	    QVariant::Type type = qDecodePSQLType( PQftype( result->d->result, i ) );
	    QSqlField rf( name, type );
	    fil.append( rf );
	}
    }
    return fil;
}

QSqlRecordInfo QPSQLDriver::recordInfo( const QString& tablename ) const
{
    QSqlRecordInfo info;
    if ( !isOpen() )
	return info;

    QString stmt;
    switch( pro ) {
    case QPSQLDriver::Version6:
	stmt = "select pg_attribute.attname, int(pg_attribute.atttypid), pg_attribute.attnotnull, "
		"pg_attribute.attlen, pg_attribute.atttypmod, int(pg_attribute.attrelid), pg_attribute.attnum "
		"from pg_class, pg_attribute "
		"where lower(pg_class.relname) = '%1' "
		"and pg_attribute.attnum > 0 "
		"and pg_attribute.attrelid = pg_class.oid ";
	break;
    case QPSQLDriver::Version7:
	stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
		"pg_attribute.attlen, pg_attribute.atttypmod, pg_attribute.attrelid::int, pg_attribute.attnum "
		"from pg_class, pg_attribute "
		"where lower(pg_class.relname) = '%1' "
		"and pg_attribute.attnum > 0 "
		"and pg_attribute.attrelid = pg_class.oid ";
	break;
    case QPSQLDriver::Version71:
	stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
		"pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc "
		"from pg_class, pg_attribute "
		"left join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
		"where lower(pg_class.relname) = '%1' "
		"and pg_attribute.attnum > 0 "
		"and pg_attribute.attrelid = pg_class.oid "
		"order by pg_attribute.attnum ";
	break;
    case QPSQLDriver::Version73:
	stmt = "select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, "
		"pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc "
		"from pg_class, pg_attribute "
		"left join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) "
		"where lower(pg_class.relname) = '%1' "
		"and pg_table_is_visible(pg_class.oid) "
		"and pg_attribute.attnum > 0 "
		"and pg_attribute.attrelid = pg_class.oid "
		"and pg_attribute.attisdropped = false "
		"order by pg_attribute.attnum ";
	break;
    }

    QSqlQuery query = createQuery();
    query.exec( stmt.arg( tablename.lower() ) );
    if ( pro >= QPSQLDriver::Version71 ) {
	while ( query.next() ) {
	    int len = query.value( 3 ).toInt();
	    int precision = query.value( 4 ).toInt();
	    // swap length and precision if length == -1
	    if ( len == -1 && precision > -1 ) {
		len = precision - 4;
		precision = -1;
	    }
	    QString defVal = query.value( 5 ).toString();
	    if ( !defVal.isEmpty() && defVal.startsWith( "'" ) )
		defVal = defVal.mid( 1, defVal.length() - 2 );
	    info.append( QSqlFieldInfo( query.value( 0 ).toString(),
					qDecodePSQLType( query.value( 1 ).toInt() ),
					query.value( 2 ).toBool(),
					len,
					precision,
					defVal,
					query.value( 1 ).toInt() ) );
	}
    } else {
	// Postgres < 7.1 cannot handle outer joins
	while ( query.next() ) {
	    QString defVal;
	    QString stmt2 = "select pg_attrdef.adsrc from pg_attrdef where "
			    "pg_attrdef.adrelid = %1 and pg_attrdef.adnum = %2 ";
	    QSqlQuery query2 = createQuery();
	    query2.exec( stmt2.arg( query.value( 5 ).toInt() ).arg( query.value( 6 ).toInt() ) );
	    if ( query2.isActive() && query2.next() )
		defVal = query2.value( 0 ).toString();
	    if ( !defVal.isEmpty() && defVal.startsWith( "'" ) )
		defVal = defVal.mid( 1, defVal.length() - 2 );
	    int len = query.value( 3 ).toInt();
	    int precision = query.value( 4 ).toInt();
	    // swap length and precision if length == -1
	    if ( len == -1 && precision > -1 ) {
		len = precision - 4;
		precision = -1;
	    }
	    info.append( QSqlFieldInfo( query.value( 0 ).toString(),
					qDecodePSQLType( query.value( 1 ).toInt() ),
					query.value( 2 ).toBool(),
					len,
					precision,
					defVal,
					query.value( 1 ).toInt() ) );
	}
    }

    return info;
}

QSqlRecordInfo QPSQLDriver::recordInfo( const QSqlQuery& query ) const
{
    QSqlRecordInfo info;
    if ( !isOpen() )
	return info;
    if ( query.isActive() && query.driver() == this ) {
	QPSQLResult* result = (QPSQLResult*)query.result();
	int count = PQnfields( result->d->result );
	for ( int i = 0; i < count; ++i ) {
	    QString name = PQfname( result->d->result, i );
	    int len = PQfsize( result->d->result, i );
	    int precision = PQfmod( result->d->result, i );
	    // swap length and precision if length == -1
	    if ( len == -1 && precision > -1 ) {
		len = precision - 4;
		precision = -1;
	    }
	    info.append( QSqlFieldInfo( name,
					qDecodePSQLType( PQftype( result->d->result, i ) ),
					-1,
					len,
					precision,
					QVariant(),
					PQftype( result->d->result, i ) ) );
	}
    }
    return info;
}

QString QPSQLDriver::formatValue( const QSqlField* field,
				  bool ) const
{
    QString r;
    if ( field->isNull() ) {
	r = nullText();
    } else {
	switch ( field->type() ) {
	case QVariant::DateTime:
	    if ( field->value().toDateTime().isValid() ) {
		QDate dt = field->value().toDateTime().date();
		QTime tm = field->value().toDateTime().time();
		// msecs need to be right aligned otherwise psql
		// interpretes them wrong
		r = "'" + QString::number( dt.year() ) + "-" +
			  QString::number( dt.month() ) + "-" +
			  QString::number( dt.day() ) + " " +
			  tm.toString() + "." +
			  QString::number( tm.msec() ).rightJustify( 3, '0' ) + "'";
	    } else {
		r = nullText();
	    }
	    break;
	case QVariant::Time:
	    if ( field->value().toTime().isValid() ) {
		r = field->value().toTime().toString( Qt::ISODate );
	    } else {
		r = nullText();
	    }
	case QVariant::String:
	case QVariant::CString: {
	    switch ( field->value().type() ) {
		case QVariant::Rect: {
		    QRect rec = field->value().toRect();
		    // upper right corner then lower left according to psql docs
		    r = "'(" + QString::number( rec.right() ) +
			"," + QString::number( rec.bottom() ) +
			"),(" + QString::number( rec.left() ) +
			"," + QString::number( rec.top() ) + ")'";
		    break;
		}
		case QVariant::Point: {
		    QPoint p = field->value().toPoint();
		    r = "'(" + QString::number( p.x() ) +
			"," + QString::number( p.y() ) + ")'";
		    break;
		}
		case QVariant::PointArray: {
		    QPointArray pa = field->value().toPointArray();
		    r = "' ";
		    for ( int i = 0; i < (int)pa.size(); ++i ) {
			r += "(" + QString::number( pa[i].x() ) +
			     "," + QString::number( pa[i].y() ) + "),";
		    }
		    r.truncate( r.length() - 1 );
		    r += "'";
		    break;
		}
		default:
		    // Escape '\' characters
		    r = QSqlDriver::formatValue( field );
		    r.replace( "\\", "\\\\" );
		    break;
	    }
	    break;
	}
	case QVariant::Bool:
	    if ( field->value().toBool() )
		r = "TRUE";
	    else
		r = "FALSE";
	    break;
	case QVariant::ByteArray: {
	    QByteArray ba = field->value().asByteArray();
	    QString res;
	    r = "'";
	    unsigned char uc;
	    for ( int i = 0; i < (int)ba.size(); ++i ) {
		uc = (unsigned char) ba[ i ];
		if ( uc > 40 && uc < 92 ) {
		    r += uc;
		} else {
		    r += "\\\\";
		    r += QString::number( (unsigned char) ba[ i ], 8 ).rightJustify( 3, '0', TRUE );
		}
	    }
	    r += "'";
	    break;
	}
	default:
	    r = QSqlDriver::formatValue( field );
	    break;
	}
    }
    return r;
}