Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
json/jsonb type integration
an tests for map and list
  • Loading branch information
signedav authored and nyalldawson committed Sep 14, 2018
1 parent 7f9edca commit f92a2ad
Show file tree
Hide file tree
Showing 7 changed files with 156 additions and 26 deletions.
5 changes: 5 additions & 0 deletions src/core/qgsfield.cpp
Expand Up @@ -253,6 +253,11 @@ QString QgsField::displayString( const QVariant &v ) const
if ( ok )
return QLocale().toString( converted );
}
else if ( d->typeName.compare( "json" ) == 0 )
{
//QJsonDocument jsonDocument = variable.toJsonDocument();
//return QString::fromUtf8( jsonDocument.toJson() );
}
// Fallback if special rules do not apply
return v.toString();
}
Expand Down
2 changes: 2 additions & 0 deletions src/providers/postgres/qgspostgresconn.cpp
Expand Up @@ -989,6 +989,7 @@ static QString doubleQuotedMapValue( const QString &v )

static QString quotedMap( const QVariantMap &map )
{
//should it be stored in json/jsonb as well?
QString ret;
for ( QVariantMap::const_iterator i = map.constBegin(); i != map.constEnd(); ++i )
{
Expand Down Expand Up @@ -1393,6 +1394,7 @@ QString QgsPostgresConn::fieldExpression( const QgsField &fld, QString expr )
return QStringLiteral( "st_astext(%1)" ).arg( expr );
}
//TODO: add support for hstore
//TODO: add support for json/jsonb
else
{
return expr + "::text";
Expand Down
4 changes: 2 additions & 2 deletions src/providers/postgres/qgspostgresfeatureiterator.cpp
Expand Up @@ -771,7 +771,7 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int
{
QgsField fld = mSource->mFields.at( idx );

QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ) );
QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName() );
primaryKeyVals << v;

if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
Expand Down Expand Up @@ -814,7 +814,7 @@ void QgsPostgresFeatureIterator::getFeatureAttribute( int idx, QgsPostgresResult
return;

const QgsField fld = mSource->mFields.at( idx );
QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ) );
QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName() );
feature.setAttribute( idx, v );

col++;
Expand Down
54 changes: 36 additions & 18 deletions src/providers/postgres/qgspostgresprovider.cpp
Expand Up @@ -230,11 +230,13 @@ QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOpti
<< QgsVectorDataProvider::NativeType( tr( "Date & Time" ), QStringLiteral( "timestamp without time zone" ), QVariant::DateTime, -1, -1, -1, -1 )

// complex types
<< QgsVectorDataProvider::NativeType( tr( "Map" ), QStringLiteral( "hstore" ), QVariant::Map, -1, -1, -1, -1, QVariant::String )
<< QgsVectorDataProvider::NativeType( tr( "Map (hstore)" ), QStringLiteral( "hstore" ), QVariant::Map, -1, -1, -1, -1, QVariant::String )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (integer - 32bit)" ), QStringLiteral( "int4[]" ), QVariant::List, -1, -1, -1, -1, QVariant::Int )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (integer - 64bit)" ), QStringLiteral( "int8[]" ), QVariant::List, -1, -1, -1, -1, QVariant::LongLong )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (double)" ), QStringLiteral( "double precision[]" ), QVariant::List, -1, -1, -1, -1, QVariant::Double )
<< QgsVectorDataProvider::NativeType( tr( "Array of text" ), QStringLiteral( "text[]" ), QVariant::StringList, -1, -1, -1, -1, QVariant::String )
<< QgsVectorDataProvider::NativeType( tr( "Map (json)" ), QStringLiteral( "json" ), QVariant::Map, -1, -1, -1, -1, QVariant::String )
<< QgsVectorDataProvider::NativeType( tr( "Map (jsonb)" ), QStringLiteral( "jsonb" ), QVariant::Map, -1, -1, -1, -1, QVariant::String )

// boolean
<< QgsVectorDataProvider::NativeType( tr( "Boolean" ), QStringLiteral( "bool" ), QVariant::Bool, -1, -1, -1, -1 )
Expand Down Expand Up @@ -985,7 +987,7 @@ bool QgsPostgresProvider::loadFields()
fieldPrec = -1;
}
}
else if ( fieldTypeName == QLatin1String( "hstore" ) )
else if ( fieldTypeName == QLatin1String( "hstore" ) || fieldTypeName == QLatin1String( "json" ) || fieldTypeName == QLatin1String( "jsonb" ) )
{
fieldType = QVariant::Map;
fieldSubType = QVariant::String;
Expand Down Expand Up @@ -1586,7 +1588,7 @@ QVariant QgsPostgresProvider::minimumValue( int index ) const
sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );

QgsPostgresResult rmin( connectionRO()->PQexec( sql ) );
return convertValue( fld.type(), fld.subType(), rmin.PQgetvalue( 0, 0 ) );
return convertValue( fld.type(), fld.subType(), rmin.PQgetvalue( 0, 0 ), fld.typeName() );
}
catch ( PGFieldNotFound )
{
Expand Down Expand Up @@ -1625,7 +1627,7 @@ QSet<QVariant> QgsPostgresProvider::uniqueValues( int index, int limit ) const
if ( res.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < res.PQntuples(); i++ )
uniqueValues.insert( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ) ) );
uniqueValues.insert( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) );
}
}
catch ( PGFieldNotFound )
Expand Down Expand Up @@ -1668,7 +1670,7 @@ QStringList QgsPostgresProvider::uniqueStringsMatching( int index, const QString
{
for ( int i = 0; i < res.PQntuples(); i++ )
{
results << ( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ) ) ).toString();
results << ( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) ).toString();
if ( feedback && feedback->isCanceled() )
break;
}
Expand Down Expand Up @@ -1822,7 +1824,7 @@ QVariant QgsPostgresProvider::maximumValue( int index ) const

QgsPostgresResult rmax( connectionRO()->PQexec( sql ) );

return convertValue( fld.type(), fld.subType(), rmax.PQgetvalue( 0, 0 ) );
return convertValue( fld.type(), fld.subType(), rmax.PQgetvalue( 0, 0 ), fld.typeName() );
}
catch ( PGFieldNotFound )
{
Expand Down Expand Up @@ -1859,7 +1861,7 @@ QVariant QgsPostgresProvider::defaultValue( int fieldId ) const
QgsPostgresResult res( connectionRO()->PQexec( QStringLiteral( "SELECT %1" ).arg( defVal ) ) );

if ( res.result() )
return convertValue( fld.type(), fld.subType(), res.PQgetvalue( 0, 0 ) );
return convertValue( fld.type(), fld.subType(), res.PQgetvalue( 0, 0 ), fld.typeName() );
else
{
pushError( tr( "Could not execute query" ) );
Expand Down Expand Up @@ -2130,7 +2132,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
.arg( delim,
quotedValue( v.toString() ) );
}
//TODO: convert arrays and hstore to native types
//TODO: convert arrays and hstore to native types and json/jsonb
else
{
values += delim + quotedValue( v );
Expand Down Expand Up @@ -2209,7 +2211,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
{
QgsField fld = field( attrIdx );
v = paramValue( defaultValues[ i ], defaultValues[ i ] );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v ) );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
}
else
{
Expand All @@ -2218,7 +2220,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
if ( v != value.toString() )
{
QgsField fld = field( attrIdx );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v ) );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
}
}

Expand All @@ -2233,7 +2235,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
{
const int idx = mPrimaryKeyAttrs.at( i );
const QgsField fld = mAttributeFields.at( idx );
features->setAttribute( idx, convertValue( fld.type(), fld.subType(), result.PQgetvalue( 0, i ) ) );
features->setAttribute( idx, convertValue( fld.type(), fld.subType(), result.PQgetvalue( 0, i ), fld.typeName() ) );
}
}
else if ( result.PQresultStatus() != PGRES_COMMAND_OK )
Expand Down Expand Up @@ -3708,6 +3710,7 @@ bool QgsPostgresProvider::convertField( QgsField &field, const QMap<QString, QVa

case QVariant::Map:
fieldType = QStringLiteral( "hstore" );
//or json/jsonb
fieldPrec = -1;
break;

Expand Down Expand Up @@ -4212,7 +4215,19 @@ static QVariant parseHstore( const QString &txt )
return result;
}

static QVariant parseOtherArray( const QString &txt, QVariant::Type subType )
static QVariant parseJson( const QString &txt )
{
QVariant result;
QJsonDocument jsonResponse = QJsonDocument::fromJson( txt.toUtf8() );
//it's null when no json format
result = jsonResponse.toVariant();

//TODO json/jsonb convert toVariantMap from QJsonObject in case it's a map etc.
//we can check there with jsonResponse.isArray if it's an array etc.
return result;
}

static QVariant parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName )
{
int i = 0;
QVariantList result;
Expand All @@ -4224,7 +4239,7 @@ static QVariant parseOtherArray( const QString &txt, QVariant::Type subType )
QgsLogger::warning( "Error parsing array: " + txt );
break;
}
result.append( QgsPostgresProvider::convertValue( subType, QVariant::Invalid, value ) );
result.append( QgsPostgresProvider::convertValue( subType, QVariant::Invalid, value, typeName ) );
}
return result;
}
Expand All @@ -4246,7 +4261,7 @@ static QVariant parseStringArray( const QString &txt )
return result;
}

static QVariant parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType )
static QVariant parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName )
{
if ( !txt.startsWith( '{' ) || !txt.endsWith( '}' ) )
{
Expand All @@ -4258,20 +4273,23 @@ static QVariant parseArray( const QString &txt, QVariant::Type type, QVariant::T
if ( type == QVariant::StringList )
return parseStringArray( inner );
else
return parseOtherArray( inner, subType );
return parseOtherArray( inner, subType, typeName );
}

QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value )
QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName )
{
QVariant result;
switch ( type )
{
case QVariant::Map:
result = parseHstore( value );
if ( typeName.compare( QLatin1String( "json" ) ) == 0 || typeName.compare( QLatin1String( "jsonb" ) ) == 0 )
result = parseJson( value );
else
result = parseHstore( value );
break;
case QVariant::StringList:
case QVariant::List:
result = parseArray( value, type, subType );
result = parseArray( value, type, subType, typeName );
break;
case QVariant::Bool:
if ( value == QChar( 't' ) )
Expand Down
2 changes: 1 addition & 1 deletion src/providers/postgres/qgspostgresprovider.h
Expand Up @@ -209,7 +209,7 @@ class QgsPostgresProvider : public QgsVectorDataProvider
* \param value the value to convert
* \returns a QVariant of the given type or a null QVariant
*/
static QVariant convertValue( QVariant::Type type, QVariant::Type subType, const QString &value );
static QVariant convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName );

QList<QgsRelation> discoverRelations( const QgsVectorLayer *self, const QList<QgsVectorLayer *> &layers ) const override;
QgsAttrPalIndexNameHash palAttributeIndexNames() const override;
Expand Down
78 changes: 73 additions & 5 deletions tests/src/providers/testqgspostgresprovider.cpp
Expand Up @@ -23,7 +23,7 @@ class TestQgsPostgresProvider: public QObject
private slots:
void decodeHstore()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "\"1\"=>\"2\", \"a\"=>\"b, \\\"c'\", \"backslash\"=>\"\\\\\"" ) );
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "\"1\"=>\"2\", \"a\"=>\"b, \\\"c'\", \"backslash\"=>\"\\\\\"" ), QStringLiteral( "hstore" ) );
QCOMPARE( decoded.type(), QVariant::Map );

QVariantMap expected;
Expand All @@ -36,7 +36,7 @@ class TestQgsPostgresProvider: public QObject

void decodeHstoreNoQuote()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "1=>2, a=>b c" ) );
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "1=>2, a=>b c" ), QStringLiteral( "hstore" ) );
QCOMPARE( decoded.type(), QVariant::Map );

QVariantMap expected;
Expand All @@ -48,7 +48,7 @@ class TestQgsPostgresProvider: public QObject

void decodeArray2StringList()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{\"1\",\"2\", \"a\\\\1\" , \"\\\\\",\"b, \\\"c'\"}" ) );
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{\"1\",\"2\", \"a\\\\1\" , \"\\\\\",\"b, \\\"c'\"}" ), QStringLiteral( "hstore" ) );
QCOMPARE( decoded.type(), QVariant::StringList );

QStringList expected;
Expand All @@ -59,7 +59,7 @@ class TestQgsPostgresProvider: public QObject

void decodeArray2StringListNoQuote()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{1,2, a ,b, c}" ) );
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{1,2, a ,b, c}" ), QStringLiteral( "hstore" ) );
QCOMPARE( decoded.type(), QVariant::StringList );

QStringList expected;
Expand All @@ -70,14 +70,82 @@ class TestQgsPostgresProvider: public QObject

void decodeArray2IntList()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{1, 2, 3,-5,10}" ) );
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::StringList, QVariant::String, QStringLiteral( "{1, 2, 3,-5,10}" ), QStringLiteral( "hstore" ) );
QCOMPARE( decoded.type(), QVariant::StringList );

QVariantList expected;
expected << QVariant( 1 ) << QVariant( 2 ) << QVariant( 3 ) << QVariant( -5 ) << QVariant( 10 );
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toList(), expected );
}
void decodeJsonList()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "[1,2,3]" ), QStringLiteral( "json" ) );
QCOMPARE( decoded.type(), QVariant::List );

QVariantList expected;
expected.append( 1 );
expected.append( 2 );
expected.append( 3 );
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toList(), expected );
}
void decodeJsonbList()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "[1,2,3]" ), QStringLiteral( "jsonb" ) );
QCOMPARE( decoded.type(), QVariant::List );

QVariantList expected;
expected.append( 1 );
expected.append( 2 );
expected.append( 3 );
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toList(), expected );
}
void decodeJsonMap()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "{\"a\":1,\"b\":2}" ), QStringLiteral( "json" ) );
QCOMPARE( decoded.type(), QVariant::Map );

QVariantMap expected;
expected[QStringLiteral( "a" )] = "1";
expected[QStringLiteral( "b" )] = "2";
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toMap(), expected );
}
void decodeJsonbMap()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "{\"a\":1,\"b\":2}" ), QStringLiteral( "jsonb" ) );
QCOMPARE( decoded.type(), QVariant::Map );

QVariantMap expected;
expected[QStringLiteral( "a" )] = "1";
expected[QStringLiteral( "b" )] = "2";
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toMap(), expected );
}
/* now working yet
void decodeJsonInt()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "'123'" ), QStringLiteral( "json" ) );
QCOMPARE( decoded.type(), QVariant::Int );
int expected;
expected=123;
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toInt(), expected );
}
void decodeJsonbInt()
{
const QVariant decoded = QgsPostgresProvider::convertValue( QVariant::Map, QVariant::String, QStringLiteral( "'123'" ), QStringLiteral( "jsonb" ) );
QCOMPARE( decoded.type(), QVariant::Int );
int expected;
expected=123;
qDebug() << "actual: " << decoded;
QCOMPARE( decoded.toInt(), expected );
}
*/
};

QGSTEST_MAIN( TestQgsPostgresProvider )
Expand Down
37 changes: 37 additions & 0 deletions tests/src/python/test_provider_postgres.py
Expand Up @@ -615,6 +615,43 @@ def testHstore(self):
self.assertTrue(vl.deleteFeatures([new_pk]))
self.assertTrue(vl.commitChanges())

def testJson(self):
vl = QgsVectorLayer('%s table="qgis_test"."json" sql=' % (self.dbconn), "testjson", "postgres")
self.assertTrue(vl.isValid())

fields = vl.dataProvider().fields()
self.assertEqual(fields.at(fields.indexFromName('jvalue')).type(), QVariant.Map)
self.assertEqual(fields.at(fields.indexFromName('jbvalue')).type(), QVariant.Map)

fi = vl.getFeatures(QgsFeatureRequest())
f = QgsFeature()

#test list
fi.nextFeature(f)
value_idx = vl.fields().lookupField('jvalue')
self.assertIsInstance(f.attributes()[value_idx], list)
self.assertEqual(f.attributes()[value_idx], [1, 2, 3])
self.assertEqual(f.attributes()[value_idx], [1.0, 2.0, 3.0])

value_idx = vl.fields().lookupField('jbvalue')
self.assertIsInstance(f.attributes()[value_idx], list)
self.assertEqual(f.attributes()[value_idx], [4, 5, 6])
self.assertEqual(f.attributes()[value_idx], [4.0, 5.0, 6.0])

#test dict
fi.nextFeature(f)
value_idx = vl.fields().lookupField('jvalue')
self.assertIsInstance(f.attributes()[value_idx], dict)
self.assertEqual(f.attributes()[value_idx], {'a': 1, 'b': 2})
self.assertEqual(f.attributes()[value_idx], {'a': 1.0, 'b': 2.0})

value_idx = vl.fields().lookupField('jbvalue')
self.assertIsInstance(f.attributes()[value_idx], dict)
self.assertEqual(f.attributes()[value_idx], {'c': 4, 'd': 5})
self.assertEqual(f.attributes()[value_idx], {'c': 4.0, 'd': 5.0})

#test int - not yet implemented

def testStringArray(self):
vl = QgsVectorLayer('%s table="qgis_test"."string_array" sql=' % (self.dbconn), "teststringarray", "postgres")
self.assertTrue(vl.isValid())
Expand Down

0 comments on commit f92a2ad

Please sign in to comment.