Database#pragma returns only 1 result and raw values, even for complex result sets

[fractaledmind]

I believe Database#pragma needs to be polymorphic in its results. The current implementation works great for something like #pragma 'page_size', but poorly with something like #pragma 'table_xinfo(t1):

# frozen_string_literal: true

require "./lib/extralite"

db = Extralite::Database.new(":memory:")
db.execute "create table t1 (id integer primary key autoincrement, created_at datetime not null)"

db.pragma 'table_xinfo(t1)'
# => [0, "id", "INTEGER", 0, nil, 1, 0]

db.query 'pragma table_xinfo(t1)'
# =>
# [{:cid=>0, :name=>"id", :type=>"INTEGER", :notnull=>0, :dflt_value=>nil, :pk=>1, :hidden=>0},
#  {:cid=>1, :name=>"created_at", :type=>"datetime", :notnull=>1, :dflt_value=>nil, :pk=>0, :hidden=>0}]

[noteflakes]

Thanks for reporting this, I remember changing #pragma to return a single record and then asking myself if this is not gonna cause problems down the line...

Some possible solutions:

• Keep the implementation as is, requiring the developer to use #query('pragma table_xinfo(...)').
• Add a #pragma_all method that returns multiple records.
• Make #pragma return multiple records when the pragma key is a string. So #pragma('foo') returns multiple records, #pragma(:foo) returns a single record.
• Make #pragma always return multiple records, but then you have to write pragma.

Personally I prefer solution 3. What do you prefer?

[fractaledmind]

Option 3 leads to be believe that it is possible for the single #pragma method to return either multiple records or a single record. If that is true, my first thought was to suggest to just check the result—make a query that returns multiple records; if the result is a collection with only one item, extract that item and return it. As I thought about that tho, I realized that would probably be too much polymorphism. For a table with only one column, table_xinfo returns an array of scalars; for a table with more than one column, the same method with the same argument returns an array of arrays.

So, given that realization, I like 2 and 3 most. Looking at SQLite's pragmas, it seems like the only ones that return multiple values have the form foo(arg), so maybe adding a method something like pragma_func(pragma_name, func_value) might make sense. Such a method would always return multiple records, and would construct the pragma_name(func_value) string to pass to execute?