By tye SQLITE3 comminad - not yet ---------------- 2.0 Type Affinity In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
Each column in an SQLite 3 database is assigned one of the following type affinities: TEXT NUMERIC INTEGER REAL NONE A column with TEXT affinity stores 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. A column with NUMERIC affinity may 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. A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0. A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression. A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (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.) A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. 2.1 Determination Of Column Affinity The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown: If the declared type contains the string "INT" then it is assigned INTEGER affinity. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity. Otherwise, the affinity is NUMERIC. Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER. 2.2 Affinity Name Examples The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values. Example Typenames From The CREATE TABLE Statement or CAST Expression Resulting Affinity Rule Used To Determine Affinity INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 INTEGER 1 CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB TEXT 2 BLOB no datatype specified NONE 3 REAL DOUBLE DOUBLE PRECISION FLOAT REAL 4 NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME NUMERIC 5 Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT. ---------------- By other people have taken some research for instance http://sqlite.phxsoftware.com/forums/t/260.aspx http://comments.gmane.org/gmane.comp.db.sqlite.general/62514 http://www.mail-archive.com/[email protected]/msg26062.html ------------------------------------------------------------------------------ Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d _______________________________________________ mseide-msegui-talk mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/mseide-msegui-talk
