Sqlite introduced the latest version 3.0, which was partially funded by America Online. This new version hosted manifest typing& BLOB support, more compact database, format internationalization, text collating sequences which are user-defined, 64-bit ROWIDs, UTF-8 and UTF-16 were supported, and much more enhanced concurrency. The segment below will discuss about the changes made in the previous version 2.8 highlighting the improvement in Sqlite version 3.
Names of the key files and APIs for Sqlite version will be available including the character “3” in it. This has been done purposely to distinguish this version and letting the older version 2.8 and new version 3.0 coexists without any issues. For instance, the file sqlite.h has been changed to sqlite3.h and the shell program has been changed to sqlite3.exe. These changes make it is possible to have both Sqlite 2.8 and Sqlite 3.0 in the same system. It is also possible to link against both the versions and use both libraries simultaneously by same C Program.
Sqlite 3.0 also comes with revised file formats. File formats belonging to the versions 2.1 and 3.0 are not compatible with each other. Sqlite 2.8 version is incapable of reading Sqlite 3.0 version file and vice versa. In order to make the files accessible in the new version, it will have to be converted using below mentions command:
Sqlite 3.0 database file uses B+Trees for the tables where the data is stored in the structural leaves of tree and not in intermediate branch nodes. This B+Trees usage makes the database more scalable and stores more data fields.
A new feature in the file format is included, which omits the fields that are unused from the disk image. Only key part of B-tree is used by indices and the fields, which record length, are completely omitted. Integers are stored using variable-length encoding and hence less storage space is required. All these changes ultimately let users store the databases which are typically 25-35% lesser than previous version equivalent databases.
Earlier version of Sqlite (like 2.8) deals with all type of formats but when it comes to write this data to the disk database or interaction through API, it always converts this data to ASCII text. Sqlite 3.0 represents the internal data to user and uses binary representations for storing it to disk. This revelation of the non-ASCII representations was done to support BLOBs. These BLOBs are usually stored in TEXT columns in the BLOB form, as it cannot be converted to text. Manifest Typing feature was also included in the Sqlite 3.0, which supports the datatype model.
UTF-8 & UTF-16 Supported
Sqlite 3.0 has the new and advanced API that comprises of the routines, which accept the UTF-16 and UTF-8 formatted text. Sqlite is not specific to store particular text received by it and processes the text strings that are not standardized or normalized UTF-8/16. Thus in order to store the ISO8859 data, UTF-8 interfaces can be used.
Each of the rows of table has a specific rowid and if the table defines a column with “INTEGER PRIMARY KEY” type. But, with or without this “INTEGER PRIMARY KEY” column all the row has rowid. In this new Sqlite 3.0 version, rowid is 64-bit signed integer. In 2.8 versions, only rowids of 32 bits were allowed. Again, in order to save the storage space, this rowid has variable length integer.
Earlier versions of Sqlite allowed several simultaneous readers or a single writer but combination of both was not permitted. However, Sqlite 3.0 version is capable to allow one process to perform the writing database and the other processes perform task of reading simultaneously. It is recommended that the writer attain lock on the database to commit the changes. However, this lock is not required for the write operation. Sqlite also introduced a table-level of locking process. In case each table is stored in databases separately, these separate files can be attached to the main database using the ATTACHcommand.
Sqlite 3.0 version was an improved version for the Sqlite application. It involved many other improvements, which made the databases compact and more utile.