Enhancement: Include new table type "VIRTUAL TABLE"

[sualeh]

Include new table type "VIRTUAL TABLE". This way, by default, virtual tables will not be shown in a get tables call. Also, if an extension is not loaded, the call will not error out.

A technique described in How to fetch names of virtual tables? could be used, with the get tables SQL looking something like:

SELECT
  NULL AS TABLE_CAT,
  NULL AS TABLE_SCHEM,
  NAME AS TABLE_NAME,
  TYPE AS TABLE_TYPE,
  NULL AS REMARKS,
  NULL AS TYPE_CAT,
  NULL AS TYPE_SCHEM,
  NULL AS TYPE_NAME,
  NULL AS SELF_REFERENCING_COL_NAME,
  NULL AS REF_GENERATION
FROM
  (
    SELECT
      NAME,
      UPPER(TYPE) AS TYPE
    FROM
      sqlite_master
    WHERE
      NAME NOT LIKE 'sqlite\_%' ESCAPE '\'
      AND SQL NOT LIKE 'CREATE VIRTUAL TABLE%';
      AND UPPER(TYPE) IN ('TABLE', 'VIEW')
    UNION ALL    
    SELECT
      NAME,
      'VIRTUAL TABLE' AS TYPE
    FROM
      sqlite_master
    WHERE
      NAME NOT LIKE 'sqlite\_%' ESCAPE '\'
      AND SQL LIKE 'CREATE VIRTUAL TABLE%';
      AND UPPER(TYPE) IN ('TABLE', 'VIEW')
    UNION ALL
    SELECT
      NAME,
      'GLOBAL TEMPORARY' AS TYPE
    FROM
      sqlite_temp_master
    UNION ALL
    SELECT
      NAME,
      'SYSTEM TABLE' AS TYPE
    FROM
      sqlite_master
    WHERE
      NAME LIKE 'sqlite\_%' ESCAPE '\'
  )
 WHERE TABLE_NAME LIKE '%' AND TABLE_TYPE IN ('TABLE','VIEW') ORDER BY TABLE_TYPE, TABLE_NAME;

[sualeh]

@xerial - any interest in this enhancement? If there are virtual tables, and an extension is not loaded, some metadata calls can error out. If we implement this enhancement, by default, virtual tables will not be reported in the metadata calls.

[gotson]

i just looked into this, i think it would be a good idea to have support for that. Do you want to submit a PR with associated unit tests? Seems like you figured out most of the complexities.

[gotson]

If there are virtual tables, and an extension is not loaded, some metadata calls can error out.

could you expand on that ?