So I’m trying to understand the Nextcloud database schema a bit, starting with users/accounts and their profile information. I poked around a bit in the database of a Nextcloud instance which has some tens of manually added users and over a thousand provisioned users, so there is some test data to look at.
Why is there a users table (storing display name and password) and an accounts table (storing a JSON representation of user profile data), instead of just one table for a user and their profile data?
Is there a conceptual and practical difference between users and accounts in Nextcloud? I do see that the SAML app uses its own tables (user_saml_*) so I guess it has nothing to do with that.
Why is there the aforementioned accounts table with JSON data and also an accounts_data table with more or less the same data but not in JSON but individual rows? Seems redundant.
The preferences table is a bit unclear to me - in it I see e.g. a row with appid=settings, configkey=email and configvalue=<my e-mail address>. Since my e-mail address is already in the accounts and accounts_data tables, why is it in here too? What’s this table for?
There’s also a properties table, which in my instance actually only has one single row, for one user, containing VCARD data. What’s this table for?
If I need to store additional information for a user, e.g. some key-value data where the value might be any simple data type or more complex one needing JSON, where should I store this? I’m not talking about data that the user change themselves, but other data I need to record for the user at hand.
Finally, is there anywhere that one can read about the tables in the database schema and what their purpose and rationale is? I found very very little, nothing useful or substantial, about it in the developer manual.
Hello.
I cannot answer all your questions, but I try to give you some advice as far as I can get you (without digging my heels too much into the code myself).
That table holds system and per-app configuration. So, e.g. the IConfig::getAppParam and IConfig::getUserParam methods to obtain data from that table. Setting is possible as well. There should be a user column as well if I am not completely mislead.
Could this be the data the users are able to set in their personal settings? Just a wild guess.
I am not sure what the different notions are here. I guess one table consists of all valid accounts and the other just the accounts directly generated (in contrast to SSO and/or LDAP). Or they are managed by different apps.
Well, that part is really a good question and cannot be answered trivially.
If you want to to poke around in the server/core apps, it would be very hand to have some documentation there. I guess there is distributed knowledge over the leads of the various projects but no written documentation so far. → TODO
If you want to understand how the core is working internally, see above.
When writing a custom app, you should not work on the tables directly. The structure might change and should be considered an implementation detail. Use the API instead.
Many of the tables can be accessed via occ commands in case you need to fix some things.
The dev manual is significantly short in some ways and here is an example (needs advanced and architecture knowedge). If you find anything useful and are comfortable in writing this down, feel free to open PRs to get that into the docs. I would however suggest to consolidate that with the doc team to avoid redundant work.
That’s quite interesting considering there is another table named appconfig in which app specific configuration is saved. Based on what you say there are then at least two places where app configurations are stored, appconfig and preferences.
I figured this one out - if a user goes to edit their profile and on the Groupware section save the Availability settings, they are saved as a VCARD in this table (properties).
There’s no difference in number of lines in the users and accounts tables - I have the same number in both of them.
Yeah, I will use the API whenever possible. Considering there are custom tables (e.g. by the SAML app) I do however presume that when needed one might have to resort to using the DB abstraction layer directly (after all, that’s what the migrations are for).
All in all, most or almost all of the things I wrote above are still a mystery. If any core developer can clarify the things I asked about, that would be great At glance much of it seems terribly distributed and redundant.
Well no, that is not entirely true. You should not use foreign tables directly. Use always the API. That way any implementation change will be handled by the core code.
For example, you could register custom user manager as am app. Then you would manage these users and provide an API to access these.
That was the reason i asked for your motivation. If you want to understand the core, this is more in detail and closer to the database. If you want to know how the app interface works and how you can register various functionality, you have to look at the apps description and interfaces.
However, i confirm that a basic documentation is the architecture and principles might help contributing to the server.
Thanks @christianlupus and sorry for this incredibly delayer response.
Most things are cleared up, e.g. that the appconfig table is for apps’ configuration (not per user) and the preferences table is for users’ app-specific configuration.
Some other things are still unclear though, e.g. the accounts and account_data tables, but as long as I can find the API to use them, it doesn’t matter much.
I do get your point about not poking around in the database, but I have to disagree that one should “always” use the API. When there is no API to store what one needs to store, one simply has to use the DB abstraction layer in Nextcloud to manage custom tables. You can find several apps that does this, I’m sure. But again, I get your point.
I did not mean that custom tables are not to be accessed directly. This is perfectly fine. If your app creates and manages some tables you can do with the data whatever you think suitable.
You should however not poke in foreign database tables. That are tables not created and managed by your app. These might change anytime and doing so will introduce run-time dependencies between the apps involved that will make the whole system brittle to install and maintain.
Agreed, assuming that by “foreign” you effectively mean “tables you didn’t create yourself”
There might be problematic situations though where e.g. one needs to integrate with an app that does not provide a proper public interface, and where the only option is to do some dirty things like this. But they should be avoided as much as possible, because of the reasons you wrote.
At glance much of it seems terribly distributed and redundant.