Saturday, 16 July 2016

Notes about SQLite

I'm using SQLite in several project, mainly in mobile app (Android SDK or with Cordova plugin).
SQLite is the best solution to store data locally, but when using it?  Maybe it's better to understand when does't use it:

IF data are detached from the app OR there are lots of concurrent writers OR Big Data

THEN t's better build a server side services (client-server)

OTHERWISE  SQLite


Main Properties:

  1. self-contained
  2. serverless
  3. zero-configuration
  4. transactional: Atomic,Consistent,Isolated, Durable (ACID)

SQLite han't all feature that usually a server side db (as MySQL) have:

  • Right and full outer join.
  • ALTER TABLE isn't completely supported.
  • There isn't Date and Time DataType but it's possible to store data as a REAL (Julian day number), INTEGER (Unix time) or TEXT (ISO8601) and extract it by specific function. I prefer store date as text so for example the today's date is strftime("%s",'now') or use in a query as "strftime('%Y-%m-%d',DATA_RILIEVO) DESC" where DATA_RILIEVO is the columns name.
  • If an INTEGER column is a primary key then it's an alias of the ROWID column. ROWID set is value to first available integer, otherwise it's possible to use AUTOINCREMENT.
  • I have noticed that multiple primary key isn't always supported.


reference

site SQLite



No comments:

Post a Comment