This page accumulates proposed changes to SQLite that, while technically not 100% backwards compatible, only present very minor changes but offer substantial benefits. We put the proposals here so that they can accumulate and so we will not forget the next time we do an incompatible release. And, by putting the proposals here, we provide users an opportunity to complain if a proposed change would seriously inconvenience them in ways that we have not anticipated.
Support A DATE Type
Change SQLite so that it understands a new fundamental type for dates. There are two spare typecodes left - this would use one. Dates would be stored as 64-bit floating point values - the julian day number. This would have the added benefit of using only 8 bytes per date instead of 19 bytes for the current TEXT-based date. The 64-bit date would provide more precision, take up less disk space and this would ultimately make date comparisons faster.
The incompatibility arises from the fact that a table column with a declared type of DATE would now undergo type coercion whereas formerly it wouldn't. This might lead to subtle problems in programs that currently use columns of this type.
The DATE type would only work on new databases. Legacy databases would continue to exhibit the legacy behavior. Older versions of SQLite would not be able to read or write databases that used the new DATE type. Perhaps older versions of SQLite could interpret the date column as a double to provide some measure of backwards compatibility.
Is this talking about DATE or about DATETIME?
If DATE, then why not use an integer type? A 32-bit integer would allow a range from about -11.7 million years to +11.7 million years, which is not enough for archaeological or astronomical work, so a 64-bit integer might make sense.
If DATETIME, then I assume non-integer values would represent times within a day, but does a 64-bit float provide sufficient range and precision? I expect people to want microsecond or nanosecond precision, and a range of billions of years.
PRIMARY KEY NOT NULL
Due to a coding error, the NOT NULL constraint on a primary key is not enforced. It probably should be. For both INTEGER PRIMARY KEYs and for PRIMARY KEYs of other types.
Index Only Tables
A great deal of space is wasted in an SQLite database if every column
is also included in the index. Index-only tables could alleviate this
problem. For larger in-memory tables this space saving ofter translates into avoiding SWAP usage, which in turn can improve performance dramatically.
You can follow the progress of experimental implementation of this feature here.
When dealing with low-cardinality columns it would be advantageous in terms of both space and speed to support Bitmap Indexes rather than b-tree based data structures.
When dealing with high-selectivity columns it would be useful in terms of both disk I/O and space to support Hashed Indexes rather than b-tree based data structures. A side-effect of this choice would be that in_order retrieval of this column could not use the index.
More Efficient Disk Format to Accommodate Random Access to BLOBs
SQLite currently has to read the entire BLOB into memory to manipulate parts of it. It would be extremely convenient to offer an API that could quickly seek to any part of the BLOB without reading the leading data first. It would also be faster and require less memory for the host application. (Note: This item seems to have been addressed by the new sqlite3_blob_open/read/write experimental API within the same backwards compatible sqlite3 disk format. Can anyone comment on the efficiency of the disk seeks within these blobs using sqlite3_blob_read and sqlite3_blob_write's iOffset parameter - is it random access?)
Database Meta Data in Virtual Tables Instead of PRAGMAs
Currently SQLite's meta data resides in the form of ad-hoc PRAGMA commands. The problem with this approach is that you cannot use a PRAGMA as a sub-select in a complex SQL query to find meta-data relationships without writing custom (non-SQL) code.