Migrate storage from NoSQL to SQL (plus a little rant)

[mmontag]

Firebase is good at all the wrong things for Chip Player JS. Realtime updates/messaging is not nearly as important as having a nice relational schema that can evolve incrementally. All Chip Player data would amount to less than 100 MB; that can be copied around and refactored locally.

User Favorites are currently stored in Firebase. It's terribly unsophisticated and I wouldn't want to build a playlist feature on Firebase.

The music catalog is completely derived from the filesystem. It does not expose any more metadata than the filename and file size. For example, it's impossible to filter on composer (which is frequently embedded inside individual file metadata) because it's not part of the filename.

The principal advantage of the filesystem is that the directory structure provides a single fixed organizational tree. This is often more intuitive and "browsable" than a collection of documents where the user has to "browse" by progressive filtering on arbitrary dimensions; i.e. a parts catalog on mouser.com that has 30 different filtering criteria. This is not browsing at all, but "narrowing." The directory structure gives the user a fixed map (directory tree) of territory to explore, while narrowing gives a map that changes based on the order in which filters are applied.

Anyway, the point is that one can maintain the directory structure metaphor even if the source of truth is a database.

Another consideration: I'm currently generating a prefix trie of the entire catalog (scripts/build-catalog.js) in order to make full-text search really fast. This optimization can also continue independent of the storage system.

Restated, there are four independent goals:

1. Directory structure-based browsing
2. Fast full-text search
   • accomplished with in-memory trie that resides in node.js server process
3. Simple catalog update workflow
   • currently just a matter of adding files, then running build-catalog.js
4. Relational data storage
   • playlists
   • query on metadata, e.g. composer, ripper, release date
   • enable "more by this artist"
   • enable playcounts

This issue is concerned with building 4 while maintaining 1, 2, and 3.