[V2] Implement SQLite / PostgresQL support

[perki]

The legacy database for Pryv.io is MongoDB, it was chosen for its ability to manage JSON structured data. With the current experience and usage, MongoDB is not providing any advantage compared to SQL in the scope of Pryv-HDS as the variable part of data (events’s content) is not indexed and not searchable.

The Stream structure challenge:

• The current implementation of the stream is hierarchical and the authorizations are mostly based on streams.
  • This implies many “getChilds”, “getParents” computations prior to any access to recompose the access rights and find the “events” matching the requested filer and the access rights.
  • Nested trees could help in setting the access rights and speed up the compilation of authorization REF

The database choice:

• PostgreSQL: widely used - scalable - free
• SQLite: offers per patient storage

Task list

• Finalize SQLite implementation for Streams, Access, Session
• Drop MongoDB and write Migration scirpts
• Implement PostgresQL

[perki]

Investigating nested set with SQLite https://github.com/perki/nested-tree-playground