Whats The Best Sqlite Data Type for a Long String

Whats the best SQLite data type for a long string

You should use TEXT.

Although, that's the same thing as VARCHAR:

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

And also:

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.

"SQLite does not impose any length restrictions"

Large string in SQLite database

SQLite in Android supports the data types TEXT for String data and BLOB for binary data. Length of TEXT would be enough to handle any data that is already shown in the screen. Images and other binary content can be stored using BLOBs.

How SQLite on Android handles long strings?

As Android is using SQLite as the backend, all fields are variable length. The default sqlite field length limit is 1 billion chars, but android may have changed this.

String storage size in sqlite

There is a managable string or blob limit in SQLite. The default is 10^9 bytes. See documentation
BTW you don't need to specify it on column declaration anyway.

Store very long text: BLOB or TEXT

In SQLite's database file format, TEXT and BLOB values are stored in exactly the same way.
The only difference is that the bytes of a TEXT value are assumed to be valid UTF-8.

Making sense of date-time and datatypes in SQLite

What you see in the column type when you execute:

PRAGMA table_info(tablename)

or:

SELECT * FROM pragma_table_info('tablename');

is the data type that was used for the definition of the column in the CREATE TABLE statement.

SQLite allows the use of anything (even nothing), as long as it is not a reserved word, as a data type.

This:

CREATE TABLE tablename (
  column0 integer primary key, 
  column1 integer,
  column2 text,
  column3 real, 
  column4 string,
  column5 something,
  column6 Jack,
  column7, -- no data type defined
  column8 real char int -- ??,
  column9 datetime  
);

is a valid statement!

From all the above column definitions, SQLite will enforce type checking only for the column column0 which is defined as integer primary key.

From Datatypes In SQLite Version 3/Storage Classes and Datatypes:

Any column in an SQLite version 3 database, except an INTEGER PRIMARY
KEY column, may be used to store a value of any storage class.

When you define a column as datetime, don't expect SQLite to understand your intention and set any internal constraints so that the values you store in it will be datetime-like.

Actually, by the rules described in Determination Of Column Affinity, this column will have NUMERIC affinity.

Of course you can store datetime values in a column, as described in Date and Time Datatype, by using INTEGER, REAL or TEXT data type, depending on the form of the datetimes that you want: Unix Times, Julian day numbers or ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") strings respectively.

In conclusion, SQLite will never complain for any value in any column defined as any data type (except for integer primary key).

It is your responsibility to make sure that you store values in the proper format that you can read/write, make calculations, compare etc.

For example, never store text datetimes in any other format than "YYYY-MM-DD HH:MM:SS.SSS" because all SQLite's datetime functions work with this format only.

---

Related Topics