SQLite is an Open Source embedded database that has become very popular over the years. Our support for SQLite has been limited because of the lack of demand from large enterprises, but that seems to have changed over the last year. The inclusion of SQLite in Android and iOS(iPhone) seems to have increased adoption within our enterprise customers. It has become the default database for mobile platforms. Starting with version 13.0, we are including full support for SQLite.
SQLite was developed by Dwayne Richard Hipp for the US Navy as a replacement for Informix in their guided missile destroyers. He also authored CVSTrac, Fossil and the Lemon Parser Generator Open Source projects. See his Wikipedia page below.
We did run into some challenges in adding support, primarily because of limitations (or features) that SQLite has as a full RDBMS. Limited constraint functionality prevents reverse engineering of foreign key constraints for ER Models. The inclusion of a VARIANT datatype required us to make enhancements to the Aqua Data Studio design, but this enhanced design will aid us in the future NoSQL support we are planning in version 14.0. SQLite added two new column datatype requirements that the ADS design did not support :
1) It added the concept of a column datatype, which is a VARIANT. A VARIANT column datatype is a datatype that, allows the value to be different than the column datatype definition. So, an INTEGER column datatype could contain values of INTEGER, STRING or BLOB. In SQLite, all column datatypes are VARIANT.
2) It added the concept of a NULL datatype. This is an undefined datatype for a column, which is also a VARIANT.
In ADS 13.0 we added support for the 2 requirements above. In ADS 14.0 we are adding support for MongoDB, so we need to find a mapping from the ADS design to the MongoDB database. The first challenge is that MongoDB does not have any column datatypes because we can't predefine them, so MongoDB only has one datatype which is the NULL datatype and it is a VARIANT just like in SQLite. Enhancing our design for SQLite will better prepare ADS for supporting MongoDB and other NoSQL databases.
In SQLite, a NULL datatype is a bit of a last resort. Users can tell ADS what is the "preferred" datatype of a column even though the values are different (VARIANT). So, if a user wants to use the Table Data Editor and they have columns of NULL type, then we recommend that they not to do this, but instead to define the column datatype even though the values may be different. With MongoDB we can't do this, as the user is forced to only NULL datatypes. Having a NULL datatype is ok for a database, as long as it is not the only datatype option. So, how do we make the NULL datatype bearable in the MongoDB scenario. One thing we can do is predict the datatype for a column in the JDBC driver. We then enumerate the datatypes and in ADS, and we flag all of the datatypes as VARIANTS. We could have this as an optional JDBC driver parameter, so that the driver can predict the datatype based on the first row. This will make the Table Data Editor for MongoDB much more usable.
en.wikipedia.org/…/D._Richard_Hipp
en.wikipedia.org/…/Sqlite
en.wikipedia.org/…/Android_(operating_system)