Drop sqlite support

[PVince81]

@pmaier1 @hodyroff

TODO: add reasons

[PVince81]

Central discussion from a while ago: https://central.owncloud.org/t/drop-sqlite-support-and-use-more-advanced-db-features/1708

[PVince81]

@kdslkdsaldsal @DeepDiver1975 @butonic

[mrow4a]

I could not make work foreign keys on sqlite with our current DB wrapping -> CASCADE DELETE does not work for sqlite for the same reason.

[PVince81]

And why do we want cascading delete ? => because we don't want 👻 in our data.

See details regarding foreign keys here: #13143

[DeepDiver1975]

Okay - drop sqlite for production use (already done actually) - can we still keep it for testing?
Might not make that much sense if more and more stuff is not working then .....

[mrow4a]

Ohhh... I just imagined how many functions calls won't be required because of CASCADE DELETE 😮

[PVince81]

and many repair steps with keyword "orphaned" can be removed as well...

[DeepDiver1975]

!gif kill sqlite with 🔥

[mrow4a]

To me, as mentioned on central:

CON:
Problem for people on webhosters. Their databases sometimes don't support InnoDB or they use small timeouts so SQLite is the only way to run ownCloud. Not sure how many people are affected by this.

PRO:
Many people will benefit from having their metadata (and in turn data) consistent, cloud more stable, and core code less bug prone and cleaner

[phil-davis]

It is not that difficult to have mySQL in a dev/test environment. That avoids the problems when something does not work properly in SQLite and thus breaks a test, or renders the test invalid/useless.

Are there dev use cases where SQLite makes life really a lot easier?

[DeepDiver1975]

Are there dev use cases where SQLite makes life really a lot easier?

• me personally: i only work on sqlite locally - reinstalling is easy:
  1. rm config/config.php
  2. ./occ m:i
  3. done
• when thinking about CI - sqlite jobs are done in less then a minute - mysql is double the time (values as they have been on jenkins the past month)

[mrow4a]

when thinking about CI - sqlite jobs are done in less then a minute - mysql is double the time (values as they have been on jenkins the past month)

Put MySQL in RAM. http://www.yiiframework.com/wiki/435/speedup-unit-tests-by-moving-mysql-data-to-memory-in-ubuntu/ for these jobs, or get faster server ;d

[DeepDiver1975]

or get faster server ;d

on a faster server sqlite would still be faster - that does not count ;-)

[PVince81]

Some people like to use Macs for developing, doesn't mean we'll support Macs for that.

If we were writing OC from scratch we'd likely not say "let's support sqlite so we can make our tests run faster". It's more of a legacy thing.

I personally also like the fact that it's faster locally when developing but would rather have more focus on the other DBs and better data integrity. I think the sacrifice is worth it. And I speak as someone who had to dig deep into data corruption shit for many weeks.

[PVince81]

Ref: #28253. <= Foreign keys and constraints could have avoided this nightmare.

[henriec]

mySQL not supporting innoDB tables should'nt be a pro for sqlite. You should see it as an extra CON: When you are testing on such a server you don't have 1 problem when you would go to production. Instead you will have 2 problems. Fix yourself a good DB before start using owncloud saves you a lot of trouble afterwards.

Everyone mentions only mySQL, don't forget postgres!

sqlite being fast is also not an argument. When you don't check anything you're database will be faster. The data in in will not be better maintained.

Main disadvantage when not dropping sqlite for testing is: most (a lot) people install it to test-drive and after a while when they decide to use the same installation/setup in a more permanent fashion they don't want to start over. Meaning: the conversion problem.

sqlite con:

• no good multi-user
• conversion broken
• no referential enforcement
• quickstart looks nice, but gives big troubles afterwards
• testdrive using this db backend and running production on another db backend sounds odd. What is the value of such a test?
• posibility of having a working test, but not being able to configure for production (see start of this post)

sqlite pro:

• ?

[PVince81]

@henriec thanks for your input.

Regarding the "trying it out" case, I think this problem would anyway apply to any other web software out there like Wordpress which doesn't support SQLite by default, so you anyway need Mysql knowledge. (interestingly googling "wordpress + sqlite" shows results for some extensions to make it work with sqlite)

[PVince81]

and people using sqlite with OC will quickly find out it's not enough and will want to convert to mysql... and as our current conversion code cannot work any more due to the new DB migrations... they're kind of stuck. Ref #27075

[PVince81]

we could start by removing sqlite from the database selection in the web UI and occ command.

people who still have sqlite selected in config.php would still have it working

later on we need to finish #27075 to help these people to migrate to mysql

[PVince81]

if we ever want to move forward and introduce foreign keys for better data consistency, we'll also need to drop SQLite, see #13143 (comment)