SQLite User Forum

Retrieve the storage class affinity using C#?
Login

Retrieve the storage class affinity using C#?

(1) By alain (abdekker) on 2022-02-10 15:47:01 [link] [source]

SQLite does not force column data types. Rather a data type "affinity" is defined which is the recommended data type for the column. This is not a requirement, and the column can still store data in any type.[1]

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

If I use the sqlite3.exe utility that comes with SQLite, and run this command:
    SELECT * FROM pragma_table_info("MyTable");
Then the "type" values are as defined in the "CREATE" statement (e.g. "BIT" and "fronk").

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

How do you reliably get data types and (more importantly) the column storage class affinity in SQLite?

  [1]: https://www.sqlite.org/datatype3.html
  [2]: https://www.sqlite.org/datatype3.html#determination_of_column_affinity

(2) By SeverKetor on 2022-02-13 15:11:34 in reply to 1 [source]

You can get data types using typeof(Column) in queries, though it's on a per-row basis not the type of the column in general. If someone put text in an integer column it won't help. As far as I know you can't check the affinity of a column. You could use pragma_table_info and apply the affinity rules yourself or as part of a view. Maybe this could also just be added to the actual table_info pragma.

Fronk is allowed because it's a classic datatype and everyone knows at a fronk is SQLite doesn't force you to use predefined datatype names, only affinities. Only strict tables require the official datatype names, for better or for worse.

(4) By alain (abdekker) on 2022-02-15 16:49:02 in reply to 2 [link] [source]

Thanks. The query "SELECT typeof(field_name) FROM table_name;" returns the actual storage class that SQLite applied for each row.

This is useful, but as you say this is not the type of the column in general.

(3) By Larry Brasfield (larrybr) on 2022-02-13 16:46:40 in reply to 1 [link] [source]

Answering questions out of order:

How do you reliably get data types and (more importantly) the column storage class affinity in SQLite?

You can retrieve the "declared datatype" from a prepared statement with this API. I assume that you want the "effective declared datatype", which your other question shows may not yet be a clear concept for you. Read on for clarity.

Re "the column storage class affinity", (assuming I understand what you intend by the term), the sqlite3_value_type() API will provide it, subject to the caveats stated with the doc paragraph where that API is described.

These APIs reliably yield the documented outputs when the documented input preconditions are met. Other input conditions may yield unexpected results.

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

This is one of the harder aspects of SQLite for those new to its use to fathom, until they attentively and diligently read the documentation about it. The topic is addressed conclusively in section 3.1 Determination Of Column Affinity. The 5 listed criteria are applied, in order, to putative (or "declared") typenames such as "BIT", "fronk", "That thingy I mean", or any text whatsoever that is not recognized as a column constraint or trigger up to the next comma or table-definition-closing ')'. All putative typenames fall into one of the 5 criteria, including yours.

That covers the "why?" with respect to what the library does. As to why it was made to do that many years ago, I cannot answer with certainty. My guess is that so much variation exists in the way typenames are declared in other DBMSs that Richard (SQLite's creator) wanted to ease use of SQLite to accept DDL written for other systems, without implementing a complex and ever-changing set of parsing and semantic analysis rules to give meaning to some and reject others.

You now have a simple option, STRICT Tables, which may help you attain "reliably" as you conceive it.