[Montellese]

I do not have MySQL in mind, I have in mind a shared DB scenario
99,9999% of the users has a library on a nas and 99% of the nas natively supports DB or can host SQLite db files (thus being a shared DB).

This (invalid) assumption is the main reason of the whole discussion in the last two days. First of all I'm guessing (I don't have any facts) 99+% of Kodi users don't have a NAS. They have a single Kodi installation on some machine. They also never make it into this or any other Kodi-related forum (otherwise we'd have a million users registered).

Therefore you can't assume that everyone will use a "central" shared database. There may be users who simply have a box running Kodi with a local library. Then one day they decide that it would be nice to also be able to use the the same library on their phone but they don't have the necessary knowledge to setup a NAS or MySQL or whatever. They just want to be able to import all the items from their existing Kodi installation on their phone and maybe take some movies with them on a trip to watch them their while they have no connection to their existing Kodi installation at home.

In that case there's no way the user will use one "central"/shared database so there needs to be a way to synchronise the library of the local Kodi installation with the installation on the mobile device. It doesn't matter how that synchronisation is done (we've started with UPnP because it's already well integrated into Kodi itself but ideally we'd use something else). I have been working on this for a while now and I first tried to do it on a per-file basis. Then I realized that there are items that don't have a file (tvshows, seasons, albums, artist, ... - in general all collections of media items). Then I went over to using the path. But in the end the SQL queries got so complicated that I had to introduce separate tables for sources. Then I realized that for some sources it might make sense to import different media types from different source paths. You could e.g. import a few very specific movies from an addon without importing all of them. In that case both come from the same source (i.e. addon XYZ) but for the automatic synchronisation it is important to know that we shouldn't sync all movies from that source. Therefore we need to have "import locations" belonging to a source which specify the actual locations on the source that need to be synchronised. A source can have as many import locations as the user requires. In case of single items being imported every item has its own import location. In other cases the import location may be the same as the source if the user simply wants to import all movies from a source. So in the end I ended up with three additional tables: "source" contains a list of sources from which a Kodi installation can import items from. "imports" contains a list of imports (each belonging to a source) which is basically a path and a media type and tells Kodi which media items to import from where. "importlinks" links an import to a media item.

Note that there is a high probability that when Kodi 1 detects Kodi 2 the reverse happens so Kodi 2 will detect Kodi 1 library... this because both PC have UPnP active.
Now how you avoid circular references? Kodi 1 detects a movie on Kodi 2 and adds it to the library. Kodi 2 detects Kodi 1 asks for the library and gets even the reference to it's own library via UPnP... this becomes an infinite loop

This is very easy to solve by following the rule that every Kodi instance must only offer items for synchronisation that it can access itself directly. So when Kodi 1 syncs with Kodi 2 and vice versa Kodi 1 will only offer the items from its local database for synchronisation and after having synchronised with Kodi 2 and Kodi 2 synchronises with Kodi 1 it doesn't get the items it offered to Kodi 1. The same rule makes sense when Kodi 1 also imports items from an addon. You can't synchronise those items because you can't know whether Kodi 2 has the same addon installed or not. If not it wouldn't be able to play that media item which would be a bad user experience.

[Fice]

Note that there is a high probability that when Kodi 1 detects Kodi 2 the reverse happens so Kodi 2 will detect Kodi 1 library... this because both PC have UPnP active.
Now how you avoid circular references? Kodi 1 detects a movie on Kodi 2 and adds it to the library. Kodi 2 detects Kodi 1 asks for the library and gets even the reference to it's own library via UPnP... this becomes an infinite loop

This is very easy to solve by following the rule that every Kodi instance must only offer items for synchronisation that it can access itself directly. So when Kodi 1 syncs with Kodi 2 and vice versa Kodi 1 will only offer the items from its local database for synchronisation and after having synchronised with Kodi 2 and Kodi 2 synchronises with Kodi 1 it doesn't get the items it offered to Kodi 1. The same rule makes sense when Kodi 1 also imports items from an addon. You can't synchronise those items because you can't know whether Kodi 2 has the same addon installed or not. If not it wouldn't be able to play that media item which would be a bad user experience.

Here is how it works in the content sync specification:
When you import sth. from an UPnP Sync Service, you get the 'remoteObjectID'. So, if we import items from an kodi isntance we could map each created asset to that kodi instance and the id that asset it has there. If someone now imports from us, we can tell him what id that asset has in our db as well as what id that asset has on the original kodi instance it came from. That way we can prevent adding an item several times. And we can make sure that changesets get applied to the correct item. So, basically an item can have several sources assigned (and some sort of id it has there).
For changesets the requirement is, that they need a UUID. so the device where the change originally happened has to create a uuid along the changeset. If I get a changeset, I can later forward them, because whoever receives that changeset, can check if he already received a changeset with the same UUID (and ignore the change).

Dependencies whether or not an addon is installed is another story.

EDIT: if we allow items to have several sources would also help in allowing users to make a local copy of a movie to take with on a trip.

[Tolriq]

well, there are two conflicting use cases here - profiles vs users and I think we have to separate them in future. A (system) profile can't be in the DB because profile specific settings can mean that a different DB should be used. So profiles are client specific IMO, but could probably also be synced if we'd use UUIDs as identifiers. Users otoh do not necessarily depend on a specific profile. The same user could have two profiles, or be part of more (private/adult profile, family profile). Maybe we need to differenciate even more (contexts?) and decouple system profiles from user profiles/accounts? Needs some more thinking I guess.

Yes the whole things needs more thinking before acting and not the opposite

What is the final purpose of the profiles in future ? If users and groups / permissions are integrated is there really a need to switch DB configuration ? Is there really any user case for that if users are correctly implemented ?
Split DB means no way to easily exchange sources for example.

[Martijn]

There could be a variety of reasons. Different keymaps, settings and whatever. So why shouldn't the db be completely separable as well.
Each profile with it's own skin,settings, locks, keymaps and DB

[Tolriq]

You mix profiles as system settings and user settings as reported by da-anda ...

User settings does include 99% of those settings are could be in a single db / different folder.

The only important part are the real system settings like DB and remote access like airplay and such.
This with the current problem of what settings are active before choosing profile ?
IE how a remote could select if profile if no profile is active this leads to system profiles vs users profiles that well can be handled as users.

And well this is the question of da-anda and quick answer without full reading is not the solution

[jjd-uk]

The problem then is if each profile has a separate db, then there must be a way to automatically keep the metadata in all db's in sync.

Use case:

Profile1 (housemate1 during the day) logs on and adds 5 media items to library, and watches 3 of them.

Profile2 (housemate2 later that night) logs on, assuming profile has access to the same sources as Profile1 (as they mostly have the exact same tastes but watch at different times), they should see the 5 added media items in the library without having to add them and they should be shown unwatched.

[m.savazzi]

It's not so simple. Kodi needs to know if it has to update a source so you can't just say a path is a source. You need to distinguish them from regular paths and some option to store if the user wants it in the db or not. Also kodi can't every time dig into all the paths only to estract the upnp already in place. And if the user adds as a source a upnp server and scrape the data? How did you distinguish kodi synced libraries from locally scraped upnp servers?
Sources needs to be separated from actual content because they're not the same thing and behave very differently..

phate89 I understood what you said but I need an example.
Let's say there is Kodi1 and Kodi2 with UPnP.
On Kodi 2 there the movie Movie1.mp4

Can you give me the source, path to file etc?

I'm getting lost

---

Thanks, will do!

Bikeshedding this now but Director for movies and Composer for (classical) music should be on the same level as Actors, Singers, and Music Groups IMHO. Many movie entusiasts also like to follow Writters and Sceenplay writters. Though bike shed color should not really be that important at this early phase or?

I do agree. Director, Composer, Writers, Screenplay are childs of Person and can be bound to any asset.

My question was: do they have any specific field for them?
If they have I will create a separate logic element.
If they have not then we need a "type" field in person that will contain "Actor, Director, Compser, Singer, etc..."

---

I think if you have a "type" field, it should not be in the Person, but should be a part of the relationship metadata. The person per se, does not have a type, it is the relationship which is typed, so a Person is bound to the asset with a relationship of type Actor, Director, Composer, etc

I'm talking at logical level, not (yet) at physical level.
At logical level you have Persons, then as "child" of persons you have Director, Actor, Singer, etc... they inherit Persons properties and add their own.
At physical level there will be different tables

---

We basically need two things:
a) we have an asset, then we need to be able to get the data of that asset, e.g. to feed it to our players
b) we have an asset, we would like to know where it came from. An addon, a tv backend (e.g. a recorded asset) or a upnp server.
When both are possible (and m.savazzi said it is), we should be fine. I (for my upnp sync stuff) can't think of any other necessary restrictions on the overall DB layout. So, I guess we could move on from 'wheter or not upnp syncing is useful' back to what should the new db layout provide,
and I have one more thing: Have you thought of a 'mobile mode'? Where someone wants to temporarely copy a file to a tablet, watch that file on holiday, and delete the copy once you're back home? During that holiday we would have 2 paths... the one of the original file/source (probably a nas) and the one on the local harddrive. Would it be doable to allow sth. like that in the long run? The goal of the distributed, syncable DB is to make such a mobile mode possible.

Fice,
can you provide me examples on how you reach an asset on a TV backend, on UPnP? in this way I can show how I would save them in the DB

[m.savazzi]

Keep in mind also that the way to access media can be dual as a file might be accessible by one instance and not another one and then should access the media from the first instance (Either automatic fallback or configurable per source).

Or include a way to sync sources with password but with tons of security problems.

About scraping you also need to keep in mind some actual limitation / configuration of XBMC. Images are cached at some defined sizes depending on lots of things.

And since each client could need different size in it's cache the main source either needs to store the best quality one and the local cache for it's need. As sending a low resolution image to be upscaled on the client would just be awful...

There's really a lot of things to think and as Montellese said it needs to be possible to do it step by step. (This image quality problem is already present in current XBMC UPnP sharing from an rpi for example, don't know if something is planned about it).

To have UPnP work well is a HUGE amount of work and troubles, security, circular references (direct and multiple ways, consider you have 3 PC with Kodi each one will have K1, K2, K3 library. Now K1 will import K2, K2 import K3, K3 imports K1.... BUM!)

Not saying it should not be done but do consider not even Microsoft, Samsung, LG, Google, Netflix got it working right... UPnP has been there for years and it's usage is very limited...
Maybe is better to focus efforts to get DB and Library management working very well as it will impact all users... not just a few.

Sharing the folder and a shared DB achieves the same functionality and is far easier.

IF thumbs are small enough they can be saved in the DB as Objects... thus no duplicates at all!

Anyway as future reference note that once the new DB is in place a Kodi istance can connect to multiple DBs on multiple NAS/PC if you want.
It's just a code problem... not anymore a data issue

You need to read better before answering
I never talk about UPnP in what I said. And I do not talk either about source import (that if done correctly as source with a master, does handle the 3 cases easily ...)

Ops sorry I got rumbled away by the UPnP story

I talk about media access and thumbnail access.

And no storing images in DB for display is not a good idea at all, and you'll always need images at the correct size stored in cache as you'll need local cache as there's no faster way to handle this .... (And sharing the source path does not work for local thumbs near media for local media for example) ....

Well to add BLOBs to DBs is very easy once we have a good structure in place. To save them locally is not an issue...

For storage, if Kodi1 have access to a local drive, Kodi2 need to have access to the media too or source sharing is useless ... So a path like /var/media does not work and you either need upnp / http sharing this is the base of central db.

Same for NAS sources with for example NFS exported only to one host and not all host, or password protected smb shares.

I do agree. The path story should be defined quite well.
From a DB structure it is independent but we need to clean it.

Using a shared DB for local content can be done but some prerequisite should apply, IF the content should be always available to remote Kodi clients the folder where content is saved should be shared (o reachable from a share)
Otherwise all other Kodi instances will see the content as offline (or will not see it, this is application logic, using the path structure I designed is very easy to exclude paths not accessible at query level)

My suggestion is to always use a share path even for local files. On the same machine it will be resolved by the local OS without using the network layer so without any cost in terms of perfomarce.
Let's assume on Kodi 1 you have folder c:\TV Shows and is shared as TVShows
on the DB you will not add contents as c:\TV Shows\show 1 but as \\Kodi 1\TVShows\show 1

In this way the content is accessible even from remote

If you too can provide me examples of contents / paths I can check the DB structure if it works and show how I would apply it

---

as for the "source" identification. To be able to indicate that some asset is offline (NAS down, Kodi instance holding the file down), we need to know where it came from, and this must not be some query parsing for a specific flag in a "path" field - so along with the "path" you need some "source" field holding the unique local identifier for the source. And with local I really mean local. From how I understood Montellese, the import sources are speficic to each installation (and probably stored in a XML?) - thus each source will have a instance specific unique ID and this ID should be linked to in a separate field within the files table (along with the path). Having an UNC path in the table and your instance is not able to access it (maybe credentials required) doesn't necessarily mean that the source which could forward it to you isn't able to access the file. So file access checks by just the "path" only won't probably be enough - and at least then you'd need to know from which configured import source this is coming to a) check if that source is even online and b) it can serve that file.

On the UNC it works and you have 2 options either the share is mounted at OS level OR you have to store username and password. This is not complex..., I can add username and Password at Path level in the db

And I don't agree that users MUST be stored in the DB and also don't like the bitfield for user IDs. I'd rather see a permission groups table IN the DB which profiles can be assigned to. So permission check in DB will be done via permission groups and not users/profiles. This also allows profiles to share permissions + simplifies reusing of permission configurations. Also, profiles could have their own DB (completely separate) for which there might be good reasons - so it wouldn't be possible to store them IN the db anyways.

You must have users in the DB if you want to filter content. If you do not like the bitfield we can use a table.

---

I do not have MySQL in mind, I have in mind a shared DB scenario
99,9999% of the users has a library on a nas and 99% of the nas natively supports DB or can host SQLite db files (thus being a shared DB).

This (invalid) assumption is the main reason of the whole discussion in the last two days. First of all I'm guessing (I don't have any facts) 99+% of Kodi users don't have a NAS. They have a single Kodi installation on some machine. They also never make it into this or any other Kodi-related forum (otherwise we'd have a million users registered).

Therefore you can't assume that everyone will use a "central" shared database. There may be users who simply have a box running Kodi with a local library. Then one day they decide that it would be nice to also be able to use the the same library on their phone but they don't have the necessary knowledge to setup a NAS or MySQL or whatever. They just want to be able to import all the items from their existing Kodi installation on their phone and maybe take some movies with them on a trip to watch them their while they have no connection to their existing Kodi installation at home.

In that case there's no way the user will use one "central"/shared database so there needs to be a way to synchronise the library of the local Kodi installation with the installation on the mobile device. It doesn't matter how that synchronisation is done (we've started with UPnP because it's already well integrated into Kodi itself but ideally we'd use something else). I have been working on this for a while now and I first tried to do it on a per-file basis. Then I realized that there are items that don't have a file (tvshows, seasons, albums, artist, ... - in general all collections of media items). Then I went over to using the path. But in the end the SQL queries got so complicated that I had to introduce separate tables for sources. Then I realized that for some sources it might make sense to import different media types from different source paths. You could e.g. import a few very specific movies from an addon without importing all of them. In that case both come from the same source (i.e. addon XYZ) but for the automatic synchronisation it is important to know that we shouldn't sync all movies from that source. Therefore we need to have "import locations" belonging to a source which specify the actual locations on the source that need to be synchronised. A source can have as many import locations as the user requires. In case of single items being imported every item has its own import location. In other cases the import location may be the same as the source if the user simply wants to import all movies from a source. So in the end I ended up with three additional tables: "source" contains a list of sources from which a Kodi installation can import items from. "imports" contains a list of imports (each belonging to a source) which is basically a path and a media type and tells Kodi which media items to import from where. "importlinks" links an import to a media item.

Note that there is a high probability that when Kodi 1 detects Kodi 2 the reverse happens so Kodi 2 will detect Kodi 1 library... this because both PC have UPnP active.
Now how you avoid circular references? Kodi 1 detects a movie on Kodi 2 and adds it to the library. Kodi 2 detects Kodi 1 asks for the library and gets even the reference to it's own library via UPnP... this becomes an infinite loop

This is very easy to solve by following the rule that every Kodi instance must only offer items for synchronisation that it can access itself directly. So when Kodi 1 syncs with Kodi 2 and vice versa Kodi 1 will only offer the items from its local database for synchronisation and after having synchronised with Kodi 2 and Kodi 2 synchronises with Kodi 1 it doesn't get the items it offered to Kodi 1. The same rule makes sense when Kodi 1 also imports items from an addon. You can't synchronise those items because you can't know whether Kodi 2 has the same addon installed or not. If not it wouldn't be able to play that media item which would be a bad user experience.

Got your point and is good. As I said UPnP and the new DB are not conflicting but complementing each other.
Also the new DB is not only a SHARED DB, is a better DB for all cases.
It will allow better library management, asset management, filtering, user bindign to content... AND it will work well in a shared scenario

[m.savazzi]

There could be a variety of reasons. Different keymaps, settings and whatever. So why shouldn't the db be completely separable as well.
Each profile with it's own skin,settings, locks, keymaps and DB

I think we have two different layers:
Configuration files
Database profile

A user in the DB is needed to allow content filtering etc...

I would Envision something like this:
1) Kodi config contains DB logon information
2) DB contains all users
3) On DB for each user you can specify a local configuration file (that in case of shared DB should be replicated among implementations) OR a configuration table with all the paramenters per user (that is unique and should be modifed by the UI, or you can have an Import user configuration menu that reads the local configuration file and pushes it in the DB...)

in this way we keep the best of the two worlds

---

The problem then is if each profile has a separate db, then there must be a way to automatically keep the metadata in all db's in sync.

Use case:

Profile1 (housemate1 during the day) logs on and adds 5 media items to library, and watches 3 of them.

Profile2 (housemate2 later that night) logs on, assuming profile has access to the same sources as Profile1 (as they mostly have the exact same tastes but watch at different times), they should see the 5 added media items in the library without having to add them and they should be shown unwatched.

Yes,
the DB Schema I proposed does this perfectly

[phate89]

phate89 I understood what you said but I need an example.
Let's say there is Kodi1 and Kodi2 with UPnP.
On Kodi 2 there the movie Movie1.mp4

Can you give me the source, path to file etc?

I'm getting lost

I'm trying to explain why you need a "source" table at least and path actually doesn't matter.
For example if you have your kodi device (kodi1) and 2 other kodi devices (kodi2 and kodi3) but you want to get only movies from kodi2 and not from kodi3 and both tells kodi1 "hey we have new movies". So you need a table with for example (i know nothing about montellese implementation and it's an extreme semplification) sourceid, syncmovies, synctvshows, syncmusic. so kodi will search a row where sourceid is equal to kodi2 and find that syncmovies is true so it will add kodi2 new movies. After that will do the same for kodi3 but in that case syncmovies is false so it will ignore it.
To do this you need a separate folder with separate options. You can't connect these options to paths or files.
You should read better the montellese's post. he's not only talking about why he thinks to use this method but it explains well why and what tables it needs..

[Fice]

Let's say I have a file on: smb://192.168.0.13/movies/SUPER_MOVIE_NAME/SUPER_MOVIE_NAME.mkv
And Kodi instance 1 imports files from smb://192.168.0.13/movies.
Now, Kodi instance 2 imports all files from Kodi instance 1. It will tell me about 'SUPER_MOVIE_NAME' and that it has the id 13 in Kodi 1s db. The path to play the file would be 'smb://192.168.0.13/movies/SUPER_MOVIE_NAME/SUPER_MOVIE_NAME.mkv'. In order to tell kodi 1 of changesets I have to send a changeset to the upnp device with a uuid of '38e15bd0-3152-11e4-8c21-0800200c9a66' and to the item with the id 13.(not really a path, but you could store it as sth. like upnp://38e15bd0-3152-11e4-8c21-0800200c9a66/ObjectID/13/ if you would like.).

I don't know of examples with TV backends or when the asset has been importet from an addon. That's Montellese's work. The goal is to make it as generic as possible to import from all kinds of sources.

[Tolriq]

I talk about media access and thumbnail access.

And no storing images in DB for display is not a good idea at all, and you'll always need images at the correct size stored in cache as you'll need local cache as there's no faster way to handle this .... (And sharing the source path does not work for local thumbs near media for local media for example) ....

Well to add BLOBs to DBs is very easy once we have a good structure in place. To save them locally is not an issue...

Well BLOB are not only slow for big images, but for big libraries right now we are talking about multiple GB of images both full size images and client specific size ones will double this space.
Database for end users are not made to handle this efficiently try to compress a sqlite file of 3 GB. (And yes compress in sqlite is mandatory )

[da-anda]

I do agree. Director, Composer, Writers, Screenplay are childs of Person and can be bound to any asset.

My question was: do they have any specific field for them?
If they have I will create a separate logic element.
If they have not then we need a "type" field in person that will contain "Actor, Director, Compser, Singer, etc..."

---

I think if you have a "type" field, it should not be in the Person, but should be a part of the relationship metadata. The person per se, does not have a type, it is the relationship which is typed, so a Person is bound to the asset with a relationship of type Actor, Director, Composer, etc

I'm talking at logical level, not (yet) at physical level.
At logical level you have Persons, then as "child" of persons you have Director, Actor, Singer, etc... they inherit Persons properties and add their own.
At physical level there will be different tables

Actor, Singer, Director must not be "childs" in terms of "inheritance" of a person. I see though that we might need "generic" additional data about a certain "job" of a person that is not bound to the asset->role link table (which only describes that particular role) because there is meta data that applies to every role of a certain type, like for actors:
- when did he become an actor
- how many oscars did he get so far
- in how many and which movies, tvshows did he act so far
- ...

Then, if we stick to the actor example, we could go even further and provide additional metadata for a character the actor played. Like "Hannibal Lector" or "Batman", who is he, in which movies did he appear, background story, ... - but this might be going a bit too far for now. Although a character would also just be a person dataset.

[da-anda]

And I don't agree that users MUST be stored in the DB and also don't like the bitfield for user IDs. I'd rather see a permission groups table IN the DB which profiles can be assigned to. So permission check in DB will be done via permission groups and not users/profiles. This also allows profiles to share permissions + simplifies reusing of permission configurations. Also, profiles could have their own DB (completely separate) for which there might be good reasons - so it wouldn't be possible to store them IN the db anyways.

You must have users in the DB if you want to filter content. If you do not like the bitfield we can use a table.

You need to have the permission configuration related info in the DB, but this doesn't have to be users. As said, I'd use group based permissions and not user based. And if you have groups, they are the only thing you reference to in your DB. Where users are stored doesn't matter, as long as Kodi knows which groups are assigned to the user so that they can be applied on queries.
But as we found out - we have to split using persons from system configuration - so a Kodi profile is more like a system configuration which can't be in the DB, while a user profile (name, settings, ...) actually could.

[m.savazzi]

I think if you have a "type" field, it should not be in the Person, but should be a part of the relationship metadata. The person per se, does not have a type, it is the relationship which is typed, so a Person is bound to the asset with a relationship of type Actor, Director, Composer, etc

I'm talking at logical level, not (yet) at physical level.
At logical level you have Persons, then as "child" of persons you have Director, Actor, Singer, etc... they inherit Persons properties and add their own.
At physical level there will be different tables

Actor, Singer, Director must not be "childs" in terms of "inheritance" of a person. I see though that we might need "generic" additional data about a certain "job" of a person that is not bound to the asset->role link table (which only describes that particular role) because there is meta data that applies to every role of a certain type, like for actors:
- when did he become an actor
- how many oscars did he get so far
- in how many and which movies, tvshows did he act so far
- ...

Then, if we stick to the actor example, we could go even further and provide additional metadata for a character the actor played. Like "Hannibal Lector" or "Batman", who is he, in which movies did he appear, background story, ... - but this might be going a bit too far for now. Although a character would also just be a person dataset.

In my understanding you will have:
1) Person: John Belushi
Birthday:
Deathday:
2) Actor:
- when did he become an actor
- how many oscars did he get so far
- in how many and which movies, tvshows did he act so far
+ all the data in actor table
3) Role:
- Movie Blues Brothers:

Essentially is how IMDB structures the data: http://www.imdb.com/name/nm0000004/?ref_=nv_sr_1

1 John Belushi has 1 actor with 15 roles, 1 writer with 5 roles, 1 singer 9 roles, etc...

In Role you have the Bindign to the Asset, not at person or Actor level.

Hoping this makes it more clear

---

And I don't agree that users MUST be stored in the DB and also don't like the bitfield for user IDs. I'd rather see a permission groups table IN the DB which profiles can be assigned to. So permission check in DB will be done via permission groups and not users/profiles. This also allows profiles to share permissions + simplifies reusing of permission configurations. Also, profiles could have their own DB (completely separate) for which there might be good reasons - so it wouldn't be possible to store them IN the db anyways.

You must have users in the DB if you want to filter content. If you do not like the bitfield we can use a table.

You need to have the permission configuration related info in the DB, but this doesn't have to be users. As said, I'd use group based permissions and not user based. And if you have groups, they are the only thing you reference to in your DB. Where users are stored doesn't matter, as long as Kodi knows which groups are assigned to the user so that they can be applied on queries.
But as we found out - we have to split using persons from system configuration - so a Kodi profile is more like a system configuration which can't be in the DB, while a user profile (name, settings, ...) actually could.

Ok,
so to the Users tab I will add a Group Tab and then we can link Libraries to the Groups-Library table

---

There is another issue I discovered with CURRENT db structure. The smartplaylist does not work you move the data from MySQL to SQLite.
It appears the query return 0 every time... probably due to Path format.

That is something we need to fix in the new DB too.