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.