Add "vtypes" field to API response to indicate actual value types

[otoolep]

Problem

Started with #1345, though others have brought it up before.

Like most other relational databases, SQLite allows you to specify the schema for a given table when you create that table. However SQLite also allows you to write data of any type into any column of that table, regardless of the declared type of the column.

This behaviour can result rqlite returning ambiguous results, in certain cases. Take the following example:

curl -XPOST 'localhost:4001/db/execute' -H "Content-Type: application/json" -d '[
    "CREATE TABLE foo (data BLOB)"
]'

curl -XPOST 'localhost:4001/db/execute' -H "Content-Type: application/json" -d '[
    "INSERT INTO foo(data) VALUES(X\'c0fe\')"
]'
curl -G 'localhost:4001/db/query' --data-urlencode 'q=SELECT * FROM foo'
{
    "results": [
        {
            "columns": [
                "data",
            ],
            "types": [
                "blob",
            ],
            "values": [
                [
                    "wP4=",
                ]
            ]
        }
    ]
}

As you can see, rqlite base64 encodes all blob data, when that data is included in a response. However, there is nothing preventing a literal string also being added to the data set, one that just happens to be a valid base64 value

curl -XPOST 'localhost:4001/db/execute' -H "Content-Type: application/json" -d '[
    "INSERT INTO foo(data) VALUES(\"wP4="\)"
]'

curl -G 'localhost:4001/db/query' --data-urlencode 'q=SELECT * FROM foo'
{
    "results": [
        {
            "columns": [
                "data",
            ],
            "types": [
                "blob",
            ],
            "values": [
                [
                    "wP4=",
                ]
            ]
        }
    ]
}

In this case the caller of this API has no way to knowing if this value is really a BLOB value, or just a plain old string.

Proposed Solution

Add a new, optional, key to the rqlite response, named "vtypes". This type will contain the actual types for each value, on a row-by-row- basis. An example is shown below.

curl -G 'localhost:4001/db/query?vtypes' --data-urlencode 'q=SELECT * FROM foo'
{
    "results": [
        {
            "columns": [
                "data",
            ],
            "types": [
                "blob",
            ],
            "vtypes": [
                [
                    "blob"
                ],
                [
                    "text"
                ]
            ],
            "values": [
                [
                    "wP4=",
                ],
                [
                    "wP4=",
                ]
            ]
        }
    ]
}

"vtypes" are optional because unless the data inserted actually differs from the "types" field, it bloats the response for no good reason.

The Associative response form would also be supported:

curl -G 'localhost:4001/db/query?vtypes&associative' --data-urlencode 'q=SELECT * FROM foo'
{
    "results": [
        {
            "types": {"data": "blob"},
            "vtypes": [
                {"data": "blob"},
                {"data": "text"},
            "rows": [
                { "data": "wP4="},
                { "data": "wP4="},
            ]
        }
    ]
}