Add column_info query for SQLite backend

[Sildra]

As SQLite doesn't have a dedicated table scheme we use the associated pragma to get those info.

We defer the column description statement preparation to the backend to take into account others data structures.

[Sildra]

I will try to check if it is possible to go back to the current backend in the type_conversion but do not expect anything. It is still a static method with only minimal type information.

I still need to solve the compilation issues.

[vadz]

After really looking at this I wonder why can't we use select whatever from pragma_table_info(:t) instead of pragma table_info(:t). Wouldn't it solve the problem in a much simpler way?

[Sildra]

It works but it doesn't solve the fact that type parsing will be missmatched between creation and retrieval of the column info. I will need to create a huge statement to translate the types to the ones defined by column_info.

[vadz]

It works but it doesn't solve the fact that type parsing will be missmatched between creation and retrieval of the column info. I will need to create a huge statement to translate the types to the ones defined by column_info.

It looks like the approach taken so far is to recognize all types for all databases in type_conversion<column_info>, so you could just add SQLite-specific types there too. I don't like it very much, IMO there should be a backend function for translating the database-specific type to data_type (and some way to access the backend from type_conversion), but at least it should be pretty simple to do it like this.

[Sildra]

select name as 'COLUMN_NAME',
    0 as 'CHARACTER_MAXIMUM_LENGTH',
    0 as 'NUMERIC_PRECISION',
    CASE WHEN type LIKE '%REAL%' OR type LIKE '%FLOAT%' OR type LIKE '%DOUBLE%' THEN 255 ELSE 0 END as 'NUMERIC_SCALE',
    CASE
        WHEN type LIKE 'TEXT'   OR type LIKE 'CLOB'     OR type LIKE '%CHAR%'    THEN 'TEXT'
        WHEN type LIKE '%INT%'  OR type LIKE '%NUMBER%' OR type LIKE '%NUMERIC%' THEN 'INTEGER'
        WHEN type LIKE '%REAL%' OR type LIKE '%FLOAT%'  OR type LIKE '%DOUBLE%'  THEN 'NUMBER'
    ELSE UPPER(type)
    END as 'DATA_TYPE',
    CASE WHEN "notnull" = 0 THEN 'YES' ELSE 'NO' END as 'IS_NULLABLE'
    from pragma_table_info(:t);

Should do the trick, I don't have access to git for the PR ATM.

[Sildra]

Appveyor uses sqlite version 3.12 and support for the pragma has been added in 3.14, I will skip the test in this case.

[vadz]

It looks like SQLite under macOS might be too old?

[Sildra]

Unexpectedly, SQLite requires version 3.36 (3.35 in the doc, but it had some bugs in it) for the drop column feature, I am adding it to the prechecks of the test.

[Sildra]

@vadz can you merge the PR if everything is good ? Thanks in advance.

[Sildra]

commit removed & compile checks replaced with runtime checks

[vadz]

Thanks! Will merge in a moment.