Split up database into much smaller chunks

[johannesjo]

The approach to save the data always on a feature level is limiting us very much. Better syncing (#159) and possible other adapters (#221) would benifit from this. This is also a prerequisite for a cross project task list #163

The idea is that each entity (e.g. a task) gets their own entry.

I would be glad if anybody could share their experience with splitting up data for indexeddb in a similar fashion.

[KonTy]

Is this a precursor for creating different API adapters, like plugging it into MSTodo, Todoist apps? This would be so awesome. I'm just thinking about possibilities of plugging it into other ecosystems this would be so wild!!!! Todoist for example runs everywhere and MS Todo also runs premmy much everywhere. I'm thinking about things like Cortana/Siri/Hey Google integration, location based tasks, Outlook, OneNote integration and the list goes on and most beautiful thing here is that you wouldn't have to lift a finger it would just work.

[johannesjo]

@KonTy probably not even though this might be a possibility. I opened up an issue for MsTodo #228.

[KonTy]

@johannesjo where is this problem at is it on the DB level or is this on the View/Model level? I'm thinking about like normal relational DB like mySQL or SQLite. This would be such a simple schema. Project->Task->Subtask. And each of those would have attributes. Like Tags, Dates, TimeSpent etc. Then is SQL like it would be so simple to query on any of those fields and get all of the tasks with a tag #blah for example. Or are you storing not just values for projects/tasks? are you strong javascript objects themselves? Is that why you can't simple just switch to SQLite for example?

I honestly think a key/value store is not the right solution/technology for storing this type of data, and perhaps that is why it became such a big crutch? Again I never look at the code and don't know where the problem is but on the surface IndexDB seems like the wrong choice here.

[johannesjo]

SQLite won't work on the web, so it's not an option.

[KonTy]

Ok I didn't know Web was a requirement for some reason I though you are keeping it as an Electron App.

[AtjonTV]

Then how about LevelDB?

As IndexedDB is based on LevelDB and IndexedDB is inside your Browser, you can have support for both .. (When you open the IndexedDB folder of the Electron version or of a Browser, you can see a .leveldb folder.)

Using LevelDB would work in Electron, in Browser and on Server.

You just have to build a data structure that likes to be inside the key-value store.

The multiple stores you get over the IndexedDB API can be emulated in LevelDB by using key prefixes like SUP_STORE.SUP_GLOBAL_CFG when you are not inside Electron or a Browser with IndexedDB

Local- and Session Storage are also based on LevelDB.

---

If it wheren't deprecated, another option would be WebSQL (SQLite).

[johannesjo]

@AtjonTV thanks for the suggestion! I'll give it a try!

[KonTy]

If it wheren't deprecated, another option would be WebSQL (SQLite).

Why do you say that SQLite I deprecated? SQLite3 is alive and well you don't have to use WebSQL. In fact it is recommended over IndexedDB for electron apps, because it is a true database and not a keyvalue store.

https://medium.com/free-code-camp/creating-an-electron-app-using-angular-and-sqlite3-24ca7d892810 http://kripken.github.io/sql.js/documentation/#http://kripken.github.io/sql.js/documentation/extra/README.md.html https://github.com/kripken/sql.js https://stackoverflow.com/questions/13192643/is-it-possible-to-access-an-sqlite-database-from-javascript https://stackoverflow.com/questions/32504307/how-to-use-sqlite3-module-with-electron https://www.youtube.com/watch?v=c76FTxLRwAw

https://www.npmjs.com/package/electron-sqlite3

[AtjonTV]

@KonTy I did not say that SQLite is deprecated, I said that WebSQL is.

Yes, using SQLite in Electron may be recommended, but you cant use it in the browser.

IE 11, Safari 13 and all versions of Firefox do NOT support WebSQL.

I dont think that writing a database layer, that on Electron uses SQLite and in browser uses IndexedDB is worth the effort. (One is SQL one is Key-Value)

Using IndexedDB in both is the way to go then.

---

Just look at the WebSQL Support: https://caniuse.com/#search=WebSQL And at the IndexedDB Support: https://caniuse.com/#search=IndexedDB

and you will see that IndexedDB is the better choice.

When using NodeJS, there is a LevelDB API: https://www.npmjs.com/package/levelup

It is not one-to-one compatible to IndexedDB due to how IndexedDB's comperator is implemented, but both are key-value and both work the same in terms of data storage.

---

If it where after my choice, I would use WebSQL in browser, SQLite in electron and MySQL on server, but this will not work if you want to support all those browsers without WebSQL.

So using IndexedDB in browser, IndexedDB in electron and LevelDB on server, even though you would have to write a database layer that wrapps the IndexedDB and LevelDB APIs, is the way to go. (But both are Key-Value)

---

My main though here is to support server-side usage of super-productivity.

I love hosting software on my servers and I want to have SP running there so I have all data in one place.

For this SP would have to store data on the Server (for example, using LevelDB).

But to not break support for In-Browser and In-Electron, a similar database is needed. And here comes IndexedDB!

[KonTy]

First of all thanks for your reply I think we are having a healthy discussion here. I couple of thoughts.

If some technologies are not supported in some browsers I don't see a problem of restricting it to a couple of Modern browsers that 90% of people use anyway.

IE 11, Safari 13 and all versions of Firefox do NOT support WebSQL.

Who cares? 90%+ of people run Chrome. We can restrict to those browsers that are supported. BTW I'm just making a statement I'm not saying we should use outdated not supported tech like WebSQL

My main though here is to support server-side usage of super-productivity.

That's fine if we switch the schema and the underlaying Database from IndexedDB we will write a WebDAV connector, the Google, Todoist, Microsoft Todo, plug in any other backend service connector here, That is one of the biggest reasons we are looking at switching from IndexedDB.

IndexedDB is the tech that got us into this situation. Using non relational db for relational data is tough.

Another point I'm trying to make is if you want to still use SP you still can but for 2.0 if you want things like #tag views, my day views, starred view, any other custom view we will probably have to restrict the amount of Browsers and features supported at least in the begging.

I would suggest carefully study this thread #163 (https://github.com/johannesjo/super-productivity/issues/163#issuecomment-542778303). This is a big reason why we need to switch off of IndexedDB. Have you seen how Todoist and MSTODO are very conveniently handles things like My Daily task view, tag views, label views? The main motivation is to support this so you can improve productivity and flow inside the SP app.

PS It sounds like you are very familiar with IndexedDB if you are pushing for it would you be willing to write a simple proof of concept based on IndexedDB that would support this scenario https://github.com/johannesjo/super-productivity/issues/163#issuecomment-542778303 ???

Populate it with MBs worth of data to see how fast it would perform when you are creating tag/label views. I think that would be a tremendous help to this project!

Again thank you for the discussion, it always helps to get a different perspective.

[johannesjo]

Thank you everybody for your input. Currently I'm experimenting with gunjs which would lead to a completely different approach. Might not be the best fit, but it would offer very interesting possibilities and it's super fast. Nevertheless it's probably more realistic that I will end up implementing indexeddb, possibly with a wrapper like dexie.

While I would like using a relational database, it's possible that WebSQL support will be removed in the future even in the browsers it's currently working in and I really don't want to go through another migration, if it can be avoided. It seems to me like there is no way around using indexeddb as it is the current de facto standard for saving data in the browser

[KonTy]

Thank you everybody for your input. Currently I'm experimenting with gunjs which would lead to a completely different approach. Might not be the best fit,

@johannesjo I've been looking as well, I'm still not sure why you guys are not even considering SQLite3 I think there is an article or something that I'm missing. Anyway I was looking for other easy alternatives and a lot of people here seem to like PouchDB. I don't know if it was ever considered by you, But it seems like it's very light and easy to use. https://pouchdb.com/

Funny thing in their sample they use TODO app to show how it works https://pouchdb.com/getting-started.html

Here is an example on how to do a WHERE clause query. It's a bit kludgy but oh well, it works https://stackoverflow.com/questions/18979473/couchdb-where-conjunction

and here is another one for grouping: https://stackoverflow.com/questions/49717336/pouchdb-grouping-and-summarizing

Interesting article with other alternatives: https://developer.ibm.com/clouddataservices/2016/11/08/building-offline-first-progressive-web-apps/

[AtjonTV]

After a quick read of the Docs from PouchDB, I would say: use it.

It is a very nice wrapper around IndexedDB, so using it will support all the major browsers, and it already handles LevelDB when used with NodeJS.

It also allows syncing to CouchDB which is interesting for Server use :D (which is still the reason I am here)

[johannesjo]

Pouch and couch are unfortunately a little bit slow.

I wrote a little article on dev.to about my search so far: https://dev.to/johannesjo/choosing-the-right-frontend-database-for-a-single-page-application-3i0

[KonTy]

I like your article quite a bit especially I like your requirements list and priorities very much! I agree that speed should be at the top.

Also I still didn't know why SQLite is not a consideration (I think I'm missing some big piece and no one is telling me what it is :) ), I know you said it won't work on the web. But I think the benefits outweigh this. I think the race to support many different technologies is killing this, I think imho the scope should be reduced and if you say "Electron is it" (which gives you all popular OSes, including Android) that would be good enough. Web? For the amount users, why do you even want to go that route? Pay hosting fees etc? I think syncing with other services like MSTodo, Todoist are much more valuable and cover "web scenarios" and give you much more.

Getting back to SQLite from what I can see it can work flawlessly with Electron from this post it seems that it will be straight forward to try SQLite https://stackoverflow.com/questions/32504307/how-to-use-sqlite3-module-with-electron imho it is worth putting it on the table for consideration.

[johannesjo]

@KonTy I am working for a lot of different companies and sometimes it's not possible to use my own computer and to install software by myself. For those case web support is essential so there is no way I am gonna drop it. Furthermore currently there is no mobile app so to run the app on my phone I can use the web version as well. Also it makes things easier, as the web layer should run in the same way on all platforms. That is not necessarily the case for the stuff running inside the node layer.

Furthermore SQLite is just a database. There might be some advantages of using a relational database and it even might be a slightly better fit for the application but there are advantages for using nosql or graph databases as well.

[AtjonTV]

After reading this thread again, I think that I didnt really tell what I mean with "server side" use.

I mean it to run SP on a server using NodeJS and having the Server store the data. Not inside the Webbrowser.

So the data would live on the server. You could then open up SP from any PC in the world and have all your Tasks there.

I know that this could be done using sync to Google Drive, MS Todo or Todoist. But .. I dislike my data being hosted at Google, I dont have a MS account (and dont want one as I dont trust them either) and I dont know Todoist, so theres no trust there also.

I want my data to be on my server without fighting to setup Nextcloud sync where I need nextcloud on any device I want to use SP, or to have to store my data at companies I dont trust.

---

Also, if SP wants to only target Chrome which has WebSQL, then thats fine with me as I only use Brave anyways.

This would actually mean that is some what of the ability to use MySQL or SQLite on the server.

[johannesjo]

@AtjonTV I wonder: Would you ideally like to see the app to come as a complete self hosted package with user account management and so on?

[AtjonTV]

Yes! That would be the deam of deams for me.

[johannesjo]

After spending way too much time on this I came to the conclusion that the costs outweigh the benefits of this change. Thanks everybody for the input.

@AtjonTV the user management thing might be something for the future, but currently there are many others things I'd like to do first.