Get rows and fieldDescriptions from a query

[giads]

In a project of mine, I needed not only the rows of the query but also the field descriptions in the order returned by the query. This project of mine has a sort of SQL console that shows, in table format, the rows with the names of the columns at the header. The columns are visualized in the order in which they appear in the result of the query. I didn't find public API in postgresql-dart to execute a query obtaining rows and field descriptions as desired. The method mappedResultsQuery doesn't seem to fit my use case.

So I forked your library in giads/postgresql-dart and created the branch results-query. I added the method:

Future<PostgreSQLResults> resultsQuery(String fmtString, {Map<String, dynamic> substitutionValues: null, bool allowReuse: true, int timeoutInSeconds}) async

likewise query and mappedResultsQuery methods.

The new class PostgreSQLResults contains rows and fieldDescriptions.

  final List<List<dynamic>> rows;
  final List<FieldDescription> fieldDescriptions;

I wonder if this feature can be interesting to other users of your library and if you intend to add it.

[isoos]

I like this, I had a similar design with package:cassandart:

abstract class ResultPage implements Page<Row> {
  List<Column> get columns;
  Uint8List get pagingState;
  List<Row> get rows => items;
}

abstract class Row {
  List<Column> get columns;
  List get values;
  Map<String, dynamic> asMap();
}

I'd also prefer if we would have a single query method instead of the variety of methods. (It would be a major version change / fork, due to its breaking nature).

[itsjoeconway]

It is also possible to publicize this information (since it already exists) through another mechanism. I believe the cache for statement reuse also contains this information once a query has been executed. I do want to avoid adding more query methods, it's already too confusing. I do like the idea of the Row type that contains a reference to metadata about the query.

[isoos]

I do like the idea of the Row type that contains a reference to metadata about the query.

Small thing, but worth to point out: if there are no rows, there is still a benefit of having the query metadata information somewhere, which could be a top-level query-result type. (I think it also helps to repeat the column-metadata on the row type too).

[itsjoeconway]

👍 Makes sense - each row could have a reference to the metadata. Row could implement List<T> as well to make the change non-breaking.

[giads]

Very interesting the idea of implementing List<T> to make the change not breaking. But, if I have well understood, it's not breaking unless someone overrides the query method with the new extended result type. It could be still a good trade-off.

If so, I wonder if it's better to use a Results object that extends ListBase<List<dynamic>> instead of using a List<Row> as result type. This results object contains only one reference to metadata and not one for each Row. I don't see a reason to keep a reference in each Row: memory costs ;-) If there are not Rows, there is anyway the metadata description that could be useful, like @isoos said.

I made an experiment. Changed the original query method, this way:

Future<PostgreSQLResults> query(String fmtString,
      {Map<String, dynamic> substitutionValues: null, bool allowReuse: true, int timeoutInSeconds}) async {
... code unchanged ...
    final rows = await _enqueue(query, timeoutInSeconds: timeoutInSeconds);
    return PostgreSQLResults(rows, query.fieldDescriptions);
}

with PostgreSQLResults like that:

class PostgreSQLResults extends ListBase<List<dynamic>> {
  PostgreSQLResults(this.rows, this.fieldDescriptions);
  final List<FieldDescription> fieldDescriptions;
  final List<List<dynamic>> rows;

  ...code with ListBase abstract methods implementation...
}

I had to change, in map_return_test.dart, the return type of the query method overridden by InterceptingConnection. Inheriting of query method from PostgreSQLConnection seems broken by my change of the result type!!! :-(

I run the new library in my project that uses Aqueduct and It seems there are not errors. The changed query method seems retrocompatible if it's called but not overridden. Even, I can access to metadata where I need them.

My humble 5 cents...

[isoos]

I'm working on this and will land it soon on the dev branch. Here is the current interface in short:

class PostgreSQLQueryResult extends UnmodifiableListView<PostgreSQLRow> {
  final PostgreSQLQueryMetaData metaData;
  PostgreSQLQueryResult(this.metaData, List<PostgreSQLRow> rows) : super(rows);
}

class PostgreSQLQueryMetaData {
  final List<FieldDescription> fieldDescriptions;
  [...]
  PostgreSQLQueryMetaData({this.fieldDescriptions});

  List<String> get tableNames [...]
}

class PostgreSQLRow extends UnmodifiableListView {
  final PostgreSQLQueryMetaData metaData;
  [...]
  PostgreSQLRow(this.metaData, List columns) : super(columns);

  Map<String, Map<String, dynamic>> toTableMap() [...]
}

And in the execution context:

  Future<PostgreSQLQueryResult> query(String fmtString,
      {Map<String, dynamic> substitutionValues,
      bool allowReuse = true,
      int timeoutInSeconds});

I'm wondering if FieldDescription is the best name for this, or is there a better one? A few candidates:

• PostgreSQLFieldDescription - to keep consistency
• PostgreSQLColumnDescription - it is a column description after all, but then fieldName should be renamed to columnName?

And with that fieldDescription could be rather fields or columns?

(btw. @giads I'm working on a largish refactoring - no breaking changes yet -, if you have ideas that you want to get into that, let me know)

[giads]

Glad to hear that you @isoos are working on this issue. I agree with you that the ideal prefix is column and not field. IMHO I'd prefer columnDescriptions, instead of only columns that could make think to values of columns... Currently, I've no other ideas. In Java, I use JDBC API and I think that some good ideas could be borrowed from there, where applicable, in future extensions of postgresql-dart.

[isoos]

@giads: please take a look at the dev branch, a let me know what you think of it: https://github.com/stablekernel/postgresql-dart/blob/dev/lib/src/execution_context.dart#L90-L119

[giads]

Thanks for sharing @isoos !!! The solution looks good and seems to fulfill this issue.

I understand that if someday, it was necessary to add some more metadata i.e. if a column is nullable or the column type or the table schema, you'd put this informations in ColumnDescription.

I add here an other consideration about database metadata that is related to this issue, but at the same time it could be all another new issue. It could be useful to query some metadata from the database. For example query what are the primary keys of a table. It could be probably too heavy, cpu/net/db consuming, add to "columnDescription" if a column is a primary key and its order in the case of a composite key.

The alternative it could be a DatabaseDescription get databaseDescription in PostgreSQLExecutionContext, where

abstract class DatabaseDescription {
  List<ColumnDescription> primaryKeys(String schema, String table);
  
  ...methods to query other useful metadata
}

[isoos]

@giads: the hard part is to structure the metadata queries as async. I'm still going back and forth whether the column description metadata should be async or not. The tradeoff is the following:

• If we resolve OIDs to table names right away, the ColumnDescription.tableName can be sync (the current status in the dev branch). However, for the first few queries of the connection, this would mean that we are executing OID metadata queries after each of them.

• If we resolve OIDs on demand, then ColumnDescription or its tableName property gets async, e.f. Future<String> get tableName. However, in this case some of the dependent methods will become async instead of sync, e.g. the mapping to table-column double map.

Having said that, I think your suggestion is a bit outside of the scope of the query results: a SELECT col_name as alias_name FROM t; will return alias_name as column name, and with that, it would be not correct to attach additional column properties to it (like primary key or default values). That could go into a separate metadata query API.

[knopp]

Bit off-topic maybe, but would it be possible to add rowsAffected in PostgreSQLResult? Right now to gets row affected one needs to use execute and thus no query reuse or binary protocol.