Modified pages/datatype3.infrom [fd66f70a51]to [d80bf38958].12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849Datatypes In SQLite Version 3hd_keywords datatype dynamic typingDatatypes In SQLite Version 3Most SQL database engines (every SQL database engine other than SQLite,as far as we know) uses static, rigid typing. With static typing, the datatypeof a value is determined by its container - the particular column inwhich the value is stored.SQLite uses a more general dynamic type system. In SQLite, the datatypeof a value is associated with the value itself, not with its container.The dynamic type system of SQLite is backwardscompatible with the more common static type systems of other database enginesin the sense that SQL statements that work on statically typed databases shouldwork the same way in SQLite. However, the dynamic typing in SQLite allowsit to do things which are not possible in traditional rigidly typeddatabases.hd_fragment storageclasses storage class1.0 Storage Classes and DatatypesEach value stored in an SQLite database (or manipulated by thedatabase engine) has one of the following storage classes: NULL. The value is a NULL value. INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number. TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). BLOB. The value is a blob of data, stored exactly as it was input.Note that a storage class is slightly more general than a datatype.The INTEGER storage class, for example, includes 6 different integerdatatypes of different lengths. This makes a difference on disk. Butas soon as INTEGER values are read off of disk and into memory for processing,they are converted to the most general datatype (8-byte signed integer).And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.>1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950Datatypes In SQLite Version 3hd_keywords datatype dynamic typingDatatypes In SQLiteMost SQL database engines (every SQL database engine other than SQLite,as far as we know) uses static, rigid typing. With static typing, the datatypeof a value is determined by its container - the particular column inwhich the value is stored.SQLite uses a more general dynamic type system. In SQLite, the datatypeof a value is associated with the value itself, not with its container.The dynamic type system of SQLite is backwardscompatible with the more common static type systems of other database enginesin the sense that SQL statements that work on statically typed databases shouldwork the same way in SQLite. However, the dynamic typing in SQLite allowsit to do things which are not possible in traditional rigidly typeddatabases.hd_fragment storageclasses storage classStorage Classes and DatatypesEach value stored in an SQLite database (or manipulated by thedatabase engine) has one of the following storage classes: NULL. The value is a NULL value. INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number. TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). BLOB. The value is a blob of data, stored exactly as it was input.A storage class is more general than a datatype.The INTEGER storage class, for example, includes 6 different integerdatatypes of different lengths. This makes a difference on disk. Butas soon as INTEGER values are read off of disk and into memory for processing,they are converted to the most general datatype (8-byte signed integer).And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.57585960616263646566676869707172737475767778798081828384858687888990919293949596979899have an implicit storage class.Under circumstances described below, thedatabase engine may convert values between numeric storage classes(INTEGER and REAL) and TEXT during query execution. hd_fragment boolean boolean datatype1.1 Boolean DatatypeSQLite does not have a separate Boolean storage class.Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).hd_fragment datetime date and time datatype1.2 Date and Time DatatypeSQLite does not have a storage class set aside for storingdates and/or times.^(Instead, the built-in [Date And Time Functions] of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").REAL as Julian day numbers, the number of days sincenoon in Greenwich on November 24, 4714 B.C. according to theproleptic Gregorian calendar.INTEGER as Unix Time, the number of seconds since1970-01-01 00:00:00 UTC.)^Applications can chose to store dates and times in any of theseformats and freely convert between formats using the built-in dateand time functions.hd_fragment affinity affinity column affinity type affinity *affinities2.0 Type AffinityIn order to maximize compatibility between SQLite and other databaseengines, SQLite supports the concept of "type affinity" on columns.The type affinity of a column is the recommended type for data storedin that column. The important idea here is that the type is recommended, notrequired. Any column can still store any type of data.585960616263646566676869707172737475767778798081828384858687888990919293949596979899100have an implicit storage class.Under circumstances described below, thedatabase engine may convert values between numeric storage classes(INTEGER and REAL) and TEXT during query execution. hd_fragment boolean boolean datatypeBoolean DatatypeSQLite does not have a separate Boolean storage class.Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).hd_fragment datetime date and time datatypeDate and Time DatatypeSQLite does not have a storage class set aside for storingdates and/or times.^(Instead, the built-in [Date And Time Functions] of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").REAL as Julian day numbers, the number of days sincenoon in Greenwich on November 24, 4714 B.C. according to theproleptic Gregorian calendar.INTEGER as Unix Time, the number of seconds since1970-01-01 00:00:00 UTC.)^Applications can chose to store dates and times in any of theseformats and freely convert between formats using the built-in[date and time functions].hd_fragment affinity affinity column affinity type affinity *affinitiesType AffinityIn order to maximize compatibility between SQLite and other databaseengines, SQLite supports the concept of "type affinity" on columns.The type affinity of a column is the recommended type for data storedin that column. The important idea here is that the type is recommended, notrequired. Any column can still store any type of data.153154155156157158159160161162163164165166167be detected by examining the raw bits of the database file.)^A column with affinity BLOB does not prefer one storage class overanother and no attempt is made to coerce data from one storage class intoanother.hd_fragment affname rules for determining column affinity2.1 Determination Of Column Affinity^(The affinity of a column is determined by the declared typeof 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.154155156157158159160161162163164165166167168be detected by examining the raw bits of the database file.)^A column with affinity BLOB does not prefer one storage class overanother and no attempt is made to coerce data from one storage class intoanother.hd_fragment affname rules for determining column affinityDetermination Of Column Affinity^(The affinity of a column is determined by the declared typeof 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.183184185186187188189190191192193194195196197^Note that the order of the rules for determining column affinityis important. ^A column whose declared type is "CHARINT" will matchboth rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.2.2 Affinity Name ExamplesThe following table shows how many common datatype names frommore traditional SQL implementations are converted into affinities by the five rules of theprevious section. This table shows only a small subset of thedatatype names that SQLite will accept. Note that ^(numeric argumentsin parentheses that following the type name (ex: "VARCHAR(255)") areignored)^ by SQLite - SQLite does not impose any length restrictions184185186187188189190191192193194195196197198^Note that the order of the rules for determining column affinityis important. ^A column whose declared type is "CHARINT" will matchboth rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.Affinity Name ExamplesThe following table shows how many common datatype names frommore traditional SQL implementations are converted into affinities by the five rules of theprevious section. This table shows only a small subset of thedatatype names that SQLite will accept. Note that ^(numeric argumentsin parentheses that following the type name (ex: "VARCHAR(255)") areignored)^ by SQLite - SQLite does not impose any length restrictions255256257258259260261262263264265266267268269)^^Note that a declared type of "FLOATING POINT" would give INTEGERaffinity, 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.2.3 Column Affinity Behavior ExampleThe following SQL demonstrates how SQLite uses column affinityto do type conversions when values are inserted into a table.^(CREATE TABLE t1(256257258259260261262263264265266267268269270)^^Note that a declared type of "FLOATING POINT" would give INTEGERaffinity, 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.Column Affinity Behavior ExampleThe following SQL demonstrates how SQLite uses column affinityto do type conversions when values are inserted into a table.^(CREATE TABLE t1(302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;nullnullnullnullnull)^hd_fragment comparisons comparison expressions3.0 Comparison ExpressionsSQLite version 3 has the usual set of SQL comparison operatorsincluding "=", "==", "<", "<=", ">", ">=", "!=", "","IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .3.1 Sort OrderThe results of a comparison depend on the storage classes of theoperands, according to the following rules: ^A value with storage class NULL is considered less than any other value (including another value with storage class NULL). ^An INTEGER or REAL value is less than any TEXT or BLOB value. ^When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed. ^A TEXT value is less than a BLOB value. ^When two TEXT values are compared an appropriate collating sequence is used to determine the result. ^When two BLOB values are compared, the result is determined using memcmp().hd_fragment expraff expression affinity3.2 Affinity Of Comparison Operands^SQLite may attempt to convert values between the storage classesINTEGER, REAL, and/or TEXT before performing a comparison.^Whether or not any conversions are attempted before the comparison takesplace depends on the type affinity of the operands.Note that every table column as a type affinity (one of BLOB, TEXT, INTEGER,REAL, or NUMERIC) but expressions do no necessarily have an affinity.Operand affinity is determined by the following rules: ^The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;nullnullnullnullnull)^hd_fragment comparisons comparison expressionsComparison ExpressionsSQLite version 3 has the usual set of SQL comparison operatorsincluding "=", "==", "<", "<=", ">", ">=", "!=", "","IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .Sort OrderThe results of a comparison depend on the storage classes of theoperands, according to the following rules: ^A value with storage class NULL is considered less than any other value (including another value with storage class NULL). ^An INTEGER or REAL value is less than any TEXT or BLOB value. ^When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed. ^A TEXT value is less than a BLOB value. ^When two TEXT values are compared an appropriate collating sequence is used to determine the result. ^When two BLOB values are compared, the result is determined using memcmp().hd_fragment expraff expression affinityAffinity Of Comparison Operands^SQLite may attempt to convert values between the storage classesINTEGER, REAL, and/or TEXT before performing a comparison.^Whether or not any conversions are attempted before the comparison takesplace depends on the type affinity of the operands.Every table column has a type affinity (one of BLOB, TEXT, INTEGER,REAL, or NUMERIC) but expressions do no necessarily have an affinity.Operand affinity is determined by the following rules: ^The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same361362363364365366367368369370371372373374375 has an affinity that is the same as a column with a declared type of "type".)^ ^Otherwise, an expression has no affinity.hd_fragment compaff comparison affinity rules3.3 Type Conversions Prior To ComparisonTo "apply affinity" means to convert an operand to a particular storageclass if and only if the conversion is lossless and reversible.^(Affinity is applied to operands of a comparison operator prior tothe comparison according to the following rules in the order shown:)^362363364365366367368369370371372373374375376 has an affinity that is the same as a column with a declared type of "type".)^ ^Otherwise, an expression has no affinity.hd_fragment compaff comparison affinity rulesType Conversions Prior To ComparisonTo "apply affinity" means to convert an operand to a particular storageclass if and only if the conversion is lossless and reversible.^(Affinity is applied to operands of a comparison operator prior tothe comparison according to the following rules in the order shown:)^393394395396397398399400401402403404405406407^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x ORa = +y OR a = +z OR ...".)^ ^In other words, the values to the right of the IN operator (the "x", "y",and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. 3.4 Comparison Example^(CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity394395396397398399400401402403404405406407408^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x ORa = +y OR a = +z OR ...".)^ ^In other words, the values to the right of the IN operator (the "x", "y",and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. Comparison Example^(CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity459460461462463464465466467468469470471472473474475476477478479480481482483484)^^All of the result in the example are the same if the comparisons arecommuted - if expressions of the form "a<40" are rewrittenas "40>a".4.0 Operators^(All mathematical operators (+, -, *, /, %, <<, >>,&, and )cast both operands to the NUMERIC storage class prior to being carried out.)^^The cast is carried through even if it is lossy and irreversible.^A NULL operand on a mathematical operator yields a NULL result.^(An operand on a mathematical operator that does not look in any waynumeric and is not NULL is converted to 0 or 0.0.)^5.0 Sorting, Grouping and Compound SELECTs^When query results are sorted by an ORDER BY clause, values with storageclass NULL come first, followed by INTEGER and REAL valuesinterspersed in numeric order, followed by TEXT values in collatingsequence order, and finally BLOB values in memcmp() order. ^No storageclass conversions occur before the sort.460461462463464465466467468469470471472473474475476477478479480481482483484485)^^All of the result in the example are the same if the comparisons arecommuted - if expressions of the form "a<40" are rewrittenas "40>a".Operators^(All mathematical operators (+, -, *, /, %, <<, >>,&, and )cast both operands to the NUMERIC storage class prior to being carried out.)^^The cast is carried through even if it is lossy and irreversible.^A NULL operand on a mathematical operator yields a NULL result.^(An operand on a mathematical operator that does not look in any waynumeric and is not NULL is converted to 0 or 0.0.)^Sorting, Grouping and Compound SELECTs^When query results are sorted by an ORDER BY clause, values with storageclass NULL come first, followed by INTEGER and REAL valuesinterspersed in numeric order, followed by TEXT values in collatingsequence order, and finally BLOB values in memcmp() order. ^No storageclass conversions occur before the sort.495496497498499500501502503504505506507508509are compared as is.hd_fragment collation *collating sequence *collating sequences\ collating function *collation *BINARY *NOCASE *RTRIM \ BINARY collating function \ NOCASE collating function \ RTRIM collating function6.0 Collating Sequences^When SQLite compares two strings, it uses a collating sequence orcollating function (two words for the same thing) to determine whichstring is greater or if the two strings are equal.^SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.496497498499500501502503504505506507508509510are compared as is.hd_fragment collation *collating sequence *collating sequences\ collating function *collation *BINARY *NOCASE *RTRIM \ BINARY collating function \ NOCASE collating function \ RTRIM collating functionCollating Sequences^When SQLite compares two strings, it uses a collating sequence orcollating function (two words for the same thing) to determine whichstring is greater or if the two strings are equal.^SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.519520521522523524525526527528529530531532533534535536537538539540541542543544545546^(RTRIM - The same as binary, except that trailing space characters are ignored.)^
An application can register additional collating functions usingthe [sqlite3_create_collation()] interface.6.1 Assigning Collating Sequences from SQL^Every column of everytable has an associated collating function. ^If no collating functionis explicitly defined, then the collating function defaults to BINARY.^The COLLATE clause of the [column definition] is usedto define alternative collating functions for a column. ^(The rules for determining which collating function to use for abinary comparison operator (=, <, >, <=, >=, !=, IS, andIS NOT) are as follows and in the order shown:)^^If either operand has an explicit collating function assignmentusing the postfix [COLLATE operator], then the explicit collating functionis used for comparison, with precedence to the collating function of theleft operand.
>>520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549^(RTRIM - The same as binary, except that trailing space characters are ignored.)^
An application can register additional collating functions usingthe [sqlite3_create_collation()] interface.Assigning Collating Sequences from SQL^Every column of everytable has an associated collating function. ^If no collating functionis explicitly defined, then the collating function defaults to BINARY.^The COLLATE clause of the [column definition] is usedto define alternative collating functions for a column.hd_fragment colrules how collation is determined^(The rules for determining which collating function to use for abinary comparison operator (=, <, >, <=, >=, !=, IS, andIS NOT) are as follows:)^^If either operand has an explicit collating function assignmentusing the postfix [COLLATE operator], then the explicit collating functionis used for comparison, with precedence to the collating function of theleft operand.
585586587588589590591592593594595596597598599used for sorting.^Otherwise, if the expression sorted by an ORDER BY clause isa column, then the collating sequence of the column is used todetermine sort order. ^If the expression is not a column and has noCOLLATE clause, then the BINARY collating sequence is used. 6.2 Collation Sequence ExamplesThe examples below identify the collating sequences that would be used todetermine the results of text comparisons that may be performed by variousSQL statements. Note that a text comparison may not be required, and nocollating sequence used, in the case of numeric, blob or NULL values.^(588589590591592593594595596597598599600601602used for sorting.^Otherwise, if the expression sorted by an ORDER BY clause isa column, then the collating sequence of the column is used todetermine sort order. ^If the expression is not a column and has noCOLLATE clause, then the BINARY collating sequence is used. Collation Sequence ExamplesThe examples below identify the collating sequences that would be used todetermine the results of text comparisons that may be performed by variousSQL statements. Note that a text comparison may not be required, and nocollating sequence used, in the case of numeric, blob or NULL values.^(
Xojo Calendar Classes 1.0.3
Download Zip: https://tinurll.com/2vFh3Q
2ff7e9595c
コメント