{"id":78,"date":"2016-02-12T11:07:03","date_gmt":"2016-02-12T11:07:03","guid":{"rendered":"https:\/\/ibex.tech\/csharp\/?p=78"},"modified":"2022-02-17T06:24:15","modified_gmt":"2022-02-17T06:24:15","slug":"columns-general","status":"publish","type":"post","link":"https:\/\/ibex.tech\/csharp\/c-sharp\/databases\/sqlite\/columns\/columns-general","title":{"rendered":".Columns General"},"content":{"rendered":"

\n\tSQLite much more simplistic data types compared to SQL.  The datatype limitations can be cumbersome especially if you add time durations, dates etc as SQLite has very few built-in functions for these. The up side however is that SQLite provides an easy way to make your own built in functions for adding things like time durations (the sqlite3_create_function library function). You use that facility in place of traditional stored procedures.\n<\/p>\n

\n\tA SQLite column does not have a type requirement, but it does have a type preference, called an affinity.  What this means is that under the hood sometimes SQLite will change the type of a value to fit match the affinity of the column, but you usually don't need to know this if you stick to storing an INTEGER in an INTEGER column, a REAL in a REAL column, etc.\n<\/p>\n

\n\tData Types
\n<\/h4>\n

\n\thttps:\/\/www.sqlite.org\/datatype3.html<\/a>\n<\/p>\n

\n\tSQLite 3 values can be one of the following types:\n<\/p>\n

\n\tTEXT\n<\/p>\n

\n\tStores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.\n<\/p>\n

\n\tDeclare as a type that includes the characters: "CHAR", "CLOB", or "TEXT"
\n\tNotice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.\n<\/p>\n

\n\tLength – Specifying the length of a TEXT column is allowed but the value is ignored (You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there).   There is no max length limitation for text.\n<\/p>\n

\n\tNUMERIC\n<\/p>\n

\n\tMay contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.\n<\/p>\n

\n\tDeclare as: anythign other than the other declaration character definitions, or simple "NUMERIC"\n<\/p>\n

\n\tINTEGER\n<\/p>\n

\n\tBehaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.
\n\tSigned integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.\n<\/p>\n

\n\tDeclare as a type that includes the characters: "INT"\n<\/p>\n

\n\tREAL\n<\/p>\n

\n\tBehaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.
\n\t(As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)\n<\/p>\n

\n\tDeclare as a type that includes the characters: "REAL", "FLOA", or "DOUB" \n<\/p>\n

\n\tNONE\n<\/p>\n

\n\tDoes not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.\n<\/p>\n

\n\tDeclare as a type that includes the characters: "BLOB" or don't specify a type\n<\/p>\n

\n\tConversion from more common SQL data types (SQLite 'Affinity')
\n<\/h4>\n

\n\t\"sqlite_affinity_examples\"<\/a>\n<\/p>\n

\n\t \n<\/p>\n

\n\t <\/p>\n","protected":false},"excerpt":{"rendered":"

SQLite much more simplistic data types compared to SQL.  The datatype limitations can be cumbersome especially if you add time durations, dates etc as SQLite has very few built-in functions for these. The up side however is that SQLite provides an easy way to make your own built in functions for adding things like time durations (the sqlite3_create_function library function). You use that […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-78","post","type-post","status-publish","format-standard","hentry","category-columns"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/posts\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":2,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":520,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/posts\/78\/revisions\/520"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/csharp\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}