db.columns: Output column metadata, not just names

[wenzeslaus]

I suggest to output information about column types to db.columns. Currently, the tool only gives columns names, so in JSON, it is a list. Instead, it should output objects (dictionaries) with information about type. This would allow users to do more with the output. Other tools, most notably v.info, are already outputting that information. db.columns is already a low-level tool, so it makes sense that outputs more, rather than less information.

The JSON output can look like this (same as v.info except not nested under a key "columns"):

[
    {
        "name": "cat",
        "sql_type": "INTEGER",
        "is_number": true
    },
    {
        "name": "onemap_pro",
        "sql_type": "DOUBLE PRECISION",
        "is_number": true
    }
]

Other formats than JSON

The default output needs to stay the same for now, i.e., a list of names, one name per line. This is currently the plain output, so the plan would be to add the type info to plain in v9.

To accommodate the old name-only use-case in the new interface and avoid users parsing the plain text output, we could add format="shell" or format="list" which would be just the list of names. This would be a step away from having all the formats print exactly the same info just in different format - we would basically say, that the "list" format cannot contain all the info JSON can.

To have non-JSON parse-able format with multiple values per record, format=csv could be used. v.info -c -g (v.info -c format=shell) prints basically CSV which is related to the discussion of the need to distinguish Bash-eval-parse-able key-values and CSV in #4492.

Alternatively or additionally, we could introduce a flag, e.g., -n to print names only even in JSON format which would produce the output we have now with #6042.

Other tools

Printing just the names does not make much sense to me and other tools show that's what we decided elsewhere. v.info -c format=json and v.db.select format=json print a list of dictionaries with info about types. While v.db.select columns info is more recent added by me, v.info -c was printing column names and types for really long time.

v.db.select is printing the columns as additional info for convenience and because you can get a different list or even new columns. v.info has it as alternative output for convenience and because there is no other tool now for vector maps. Nevertheless, the outputs should be the same unless there is a reason to make them different.

v.info

$ grass ~/grassdata/nc_spm_full_v2alpha2/PERMANENT/ --exec v.info geology -c format=json
{
    "columns": [
        {
            "name": "cat",
            "sql_type": "INTEGER",
            "is_number": true
        },
        {
            "name": "onemap_pro",
            "sql_type": "DOUBLE PRECISION",
            "is_number": true
        },
        {
            "name": "PERIMETER",
            "sql_type": "DOUBLE PRECISION",
            "is_number": true
        },
        {
            "name": "GEOL250_",
            "sql_type": "INTEGER",
            "is_number": true
        },
        {
            "name": "GEOL250_ID",
            "sql_type": "INTEGER",
            "is_number": true
        },
        {
            "name": "GEO_NAME",
            "sql_type": "CHARACTER",
            "is_number": false
        },
        {
            "name": "SHAPE_area",
            "sql_type": "DOUBLE PRECISION",
            "is_number": true
        },
        {
            "name": "SHAPE_len",
            "sql_type": "DOUBLE PRECISION",
            "is_number": true
        }
    ]
}

v.db.select

$ grass ~/grassdata/nc_spm_full_v2alpha2/PERMANENT/ --exec v.db.select geology columns="GEO_NAME, SHAPE_area * 100 AS area100" where="cat = 1820" format=json 
{"info":
{"columns":[
{"name":"GEO_NAME","sql_type":"CHARACTER","is_number":false},
{"name":"area100","sql_type":"DOUBLE PRECISION","is_number":true}
]},
"records":[
{"GEO_NAME":"Qp","area100":82944614.4862}
]}

Additional info

Create v.db.columns?

There is no v.db.columns, maybe there should be one. If we would have one, the difference from db.columns should be the interface (direct access to database versus vector map name and layer), but not in format. How would we design such a tool from scratch? Likely, it would output all the information we have about the columns rather than just the names.

Prior discussions

• db.columns: Add JSON support #6042
• Create a standardized format name for the shell script style (the g flag) #4492
• v.info: add json output for columns #4590
• v.info: Improve how parameters are handled #5844
• v.db.select: Add column names and types to JSON #3090

[wenzeslaus]

@HuidaeCho Given you touched db.columns in not-yet-merged #3173, do you want to check this out and say if this fits your use cases?

[HuidaeCho]

@HuidaeCho Given you touched db.columns in not-yet-merged #3173, do you want to check this out and say if this fits your use cases?

I agreed with you on:

db.columns is already a low-level tool, so it makes sense that outputs more, rather than less information.

but not on

There is no v.db.columns, maybe there should be one.

because, like you said, v.info -c already does the same thing and redundancy is generally not encouraged. v.db.select format=json is an exception because reading column metadata and actual data from one command is still useful.

[HuidaeCho]

For db.columns, I would keep the current CLI and add a new flag for full information because, in many cases at least for me, column names are sufficient.

[wenzeslaus]

Here is a possible way how to implement db.columns with more info based on @HuidaeCho's feedback:

Parameters

To output more, we would add a new flag to opt-in:

Flags:
  -e   Print type information about the columns

Parameters:
     table   Name of attribute table
    driver   Name of database driver
             options: dbf,mysql,odbc,ogr,pg,sqlite
             default: sqlite
  database   Name of database
             default: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
    format   Output format
             options: plain,json,csv,list
             default: plain
 separator   Separator for CSV and list formats

Plain and default

$ db.columns table=geology [format=plain]
cat
onemap_pro
PERIMETER
GEOL250_
GEOL250_ID
GEO_NAME
SHAPE_area
SHAPE_len
$ db.columns table=geology [format=plain] -e
INTEGER|cat
DOUBLE PRECISION|onemap_pro
DOUBLE PRECISION|PERIMETER
INTEGER|GEOL250_
INTEGER|GEOL250_ID
CHARACTER|GEO_NAME
DOUBLE PRECISION|SHAPE_area
DOUBLE PRECISION|SHAPE_len

JSON

$ db.columns table=geology format=json

[
    "cat",
    "OBJECTID",
    "WAKE_ZIPCO",
    "PERIMETER",
    "NAME",
    "SHAPE_Leng",
    "SHAPE_Area",
]

$ db.columns table=geology format=json -e

[
    {
        "name": "cat",
        "sql_type": "INTEGER",
        "is_number": true
    },
    {
        "name": "onemap_pro",
        "sql_type": "DOUBLE PRECISION",
        "is_number": true
    },
    {
        "name": "PERIMETER",
        "sql_type": "DOUBLE PRECISION",
        "is_number": true
    },
    {
        "name": "SHAPE_area",
        "sql_type": "DOUBLE PRECISION",
        "is_number": true
    },
    {
        "name": "SHAPE_len",
        "sql_type": "DOUBLE PRECISION",
        "is_number": true
    }
]

CSV

$ db.columns table=geology format=csv
name
cat
onemap_pro
PERIMETER
GEOL250_
GEOL250_ID
GEO_NAME
SHAPE_area
SHAPE_len
$ db.columns table=geology format=csv -e
name,type,is_number
cat,INTEGER,true
onemap_pro,DOUBLE PRECISION,true
PERIMETER,DOUBLE PRECISION,true
...

List

$ db.columns table=geology format=list [separator=newline]
cat
onemap_pro
PERIMETER
GEOL250_
GEOL250_ID
GEO_NAME
SHAPE_area
SHAPE_len

The point of this format would be being simple, so it is hard to add more information, but perhaps providing only name and SQL type as in SQL syntax column definition would be reasonable:

$ db.columns table=geology format=list [separator=newline] -e
cat INTEGER
onemap_pro DOUBLE PRECISION
PERIMETER DOUBLE PRECISION
...

(With comma as a separator, this would be a valid SQL.)

Variants

Flag name and description

The flags can be:

-e  Print type information about the columns
-e  Print extended column metadata
-i  Print info about the columns
-m  Print column metadata
-t  Print also column types
-t  Print also column types and other info 

Include additional formats?

At this point, we have plain which should not be used for parsing in the future (but will be okay till v9) and JSON. The above suggests "list" (aka "shell") which is an easy to parse format like the current output and CSV. Maybe, we don't need four. For example, CSV and JSON would both be parsed in Python through a standard library with an identical result since JSON is a list. The simple format is really too simple. Or, the plain human-readable format is perhaps not needed for the more low-level use cases and we could make that a stable, simple format for custom parsing.

More info in the simple parseable ("list") format

As an alternative to trying to include type, we could just rely on other formats and fail with the -e flag:

$ db.columns table=geology format=list -e
ERROR: Use JSON or CSV (format="json" or format="csv") to get more than just the names. 

Name for the simple format for parsing

The traditional, but perhaps here wrong name would be "shell". Using "list" would correspond with the list-oriented nature of the format - now the tool prints a list of names, one per line. The name "list" works with any separator, newline, comma, or pipe.

Default separator

Default separator for CSV should be comma, but the current format, if used as a simple list format for parsing has currently newline. In the interface, it would be introduced as a new format, so we don't need to keep the same separator, so we could go with comma too. v.info -c currently uses pipe. The separator can be left unset and it can be different for every format. After all, it does not make sense for JSON.

Header for CSV

Besides separator, CSV format can have several variants. One which some tools currently have is with and without header, so we could add another flag (-s, -h, -c, -v, ...) to skip the header and print only the values.

Feedback

You can consider providing feedback for this by filling out this table (fully or partially):

item	feedback
default is names only, flag for types
flag name and description
include human-readable plain
include format=csv
include format=list
simple list format for parsing should be "list"
simple list format should only allow names or allow more
default separator
allow disabling CSV header