The SQLite3 documentation on data types makes it clear that SQLite does not enforce column data types. Rather a data type "affinity" is defined which is the recommended data type for the column. The recommendation is not a requirement as in other database systems, and the column can store data in any type.

Am looking for a programmatic way to access the affinity for a column (i.e. what SQLite interpreted as the data type hint from the CREATE TABLE statement).

Sample C# code using the wrapper System.Data.SQLite.dll in Visual Studio 2019 on this table:

-- SQL CREATE TABLE
CREATE TABLE "MyTable" (
    "txt_Text"  TEXT,
    "txt_Text20"    TEXT(20),
    "txt_Memo"  MEMO,
    "num_Int"   INTEGER,
    "num_Bit"   BIT,
    "num_Bool"  BOOLEAN,
    "num_Byte"  BYTE,
    "float_Single"  FLOAT,
    "float_Double"  DOUBLE,
    "float_Decimal" DECIMAL,
    "float_Currency"    CURRENCY,
    "date_Date" DATE,
    "date_DateTime" DATETIME,
    "random"    fronk
)

// C# sample code
string[] columnRestrictions = new string[4];
columnRestrictions[2] = "MyTable";
DataTable schemaColumns =
    ((DbConnection)connection).GetSchema("Columns", columnRestrictions);
if (schemaColumns.Rows.Count > 0)
{
    string typeNameSQLite;
    string typeNameEntity;
    foreach (DataRow row in schemaColumns.Rows)
    {
        typeNameSQLite = (string)row["DATA_TYPE"];
        typeNameEntity = (string)row["EDM_TYPE"];
        // These would be saved in a List<string>...
    }
}

// Results
COLUMN NAME         CREATE      ASSUMED AFFINITY(1)     DATA_TYPE(2)    EDM_TYPE(2)
txt_Text            TEXT        TEXT                    text            nvarchar
txt_Text20          TEXT(20)    TEXT                    text            nvarchar
txt_Memo            MEMO        NUMERIC                 memo            nvarchar
num_Int             INTEGER     INTEGER                 integer         integer
num_Bit             BIT         NUMERIC                 bit             bit
num_Bool            BOOLEAN     NUMERIC                 boolean         bit
num_Byte            BYTE        NUMERIC                 byte            (blank)
float_Single        FLOAT       REAL                    float           real
float_Double        DOUBLE      REAL                    double          real
float_Decimal       DECIMAL     NUMERIC                 decimal         decimal
float_Currency      CURRENCY    NUMERIC                 currency        decimal
date_Date           DATE        NUMERIC                 date            datetime
date_DateTime       DATETIME    NUMERIC                 datetime        datetime
random              fronk       NUMERIC                 fronk           (blank)

Notes:
(1) Assumed affinity derived from [the SQLite3 documentation][2]
(2) "DATA_TYPE" and "EDM_TYPE" values taken directly from the "Columns" schema

The examples given in the SQLite documentation do not mention the "MEMO" or "BIT" types, though they appear to have been processed. Also, why is the type fronk understood at all?

How do you reliably access data types and the internal storage class affinity in SQLite using code?

UPDATE

@DinoCoderSaurus provided a useful comment: Run the query SELECT typeof(field_name) FROM table_name; and process the rows. This shows the storage class SQLite used to actually save the data. For example:

 ----------------- 
| typeof(num_Int) |
 ----------------- 
| null            |
| integer         |
 ----------------- 

This is helpful, but is the answer to a different question. I'm trying to confirm what internal data type affinity SQLite determined for the column from the TABLE CREATE statement (e.g. before any data is put into the table).

CodePudding user response：

By following the rules described in Determination Of Column Affinity, I created a query that returns the affinities of all columns in a table:

SELECT cid, name, type,
       CASE
         WHEN type LIKE '%INT%' THEN 'INTEGER'
         WHEN (type LIKE '%CHAR%') OR (type LIKE '%CLOB%') OR (type LIKE '%TEXT%') THEN 'TEXT'
         WHEN (type LIKE '%BLOB%') OR (type = '') THEN 'BLOB'
         WHEN (type LIKE '%REAL%') OR (type LIKE '%FLOA%') OR (type LIKE '%DOUB%') THEN 'REAL' 
         ELSE 'NUMERIC'
       END affinity
FROM pragma_table_info('MyTable');

See the demo.

You can use it as it is, or apply its logic with the use of a programming language of your choice.

CodePudding user response：

Column affinity is available through the SQLite C API. Look for sqlite3_column_decltype and sqlite3_column_type, which return one of:

• SQLITE_INTEGER
• SQLITE_FLOAT
• SQLITE_TEXT
• SQLITE_BLOB
• SQLITE_NULL

This is currently only available through the C/C API and not exposed in the System.Data.SQLite.dll .NET wrapper.

Affinity is not the actual storage class of saved values in SQLite. To determine the actual storage class that SQLite applied, use the typeof operator as suggested by @DinoCoderSaurus (with an example in the question).