CODEWITHSUNDEEP
javaandroidsqlite
manshuai
manshuai

Reputation: 863

SQLite is typeless?

If sqlite is typeless, so why we declare the datatype of the column ? what's the use of the declare datatype?

Why android SQLite can store double value(java 8 bytes) into float column

Upvotes: 5

Views: 785

Answers (3)

dan04
dan04

Reputation: 90995

It's used to determine the "affinity" of the column.

CREATE TABLE Example (
    IntegerAffinity    INTEGER,
    RealAffinitity     REAL,
    NumericAffinity    NUMERIC,
    TextAffinity       TEXT,
    NoAffinity
);

Inserting some values into this table (with Python's sqlite3 module) gives):

>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [1234] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", ['1234'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [math.pi] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [str(math.pi)] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", ['ABC'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [b'5678'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [b'\xAB\xCD\xEF'] * 5)
>>> list(db.execute("SELECT * FROM Example"))
[(1234, 1234.0, 1234, '1234', 1234),
 (1234, 1234.0, 1234, '1234', '1234'),
 (3.141592653589793, 3.141592653589793, 3.141592653589793, '3.14159265358979', 3.141592653589793),
 (3.141592653589793, 3.141592653589793, 3.141592653589793, '3.141592653589793', '3.141592653589793'),
 ('ABC', 'ABC', 'ABC', 'ABC', 'ABC'),
 (b'5678', b'5678', b'5678', b'5678', b'5678'),
 (b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef')]
  • In text-affinity columns, numbers get converted to strings.
  • In integer-, real-, and numeric-affinity columns, strings that contain numbers get converted to numbers.
  • Strings that do not contain numbers remain strings, even in numeric columns.
  • Blobs remain blobs, regardless of the column affinity.
  • In columns with NONE affinity, values are inserted as-is.

Upvotes: 2

Joni
Joni

Reputation: 111239

The declared column data type is used as a hint (http://sqlite.org/faq.html#q3):

So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.

By the way, SQLite is not typeless; it uses dynamic typing. This means that any value that you insert maintains its own type information, instead of inferring the type from the table declaration. http://sqlite.org/datatype3.html

Upvotes: 3

styfle
styfle

Reputation: 24610

Check out the documentation. http://sqlite.org/datatype3.html

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".

It looks like they are trying to maintain compatibility with other DBMS's.

Also they want to be able to do the following:

...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.

Ultimately the affinity of a column is determined by the declared type of the column so this is why you declare the type of the column.

Upvotes: 1

Related Questions