Reply to thread

Message

The pictures and folder databases already don't have auto-increment for the primary keys. I understand your concern, but MP has a very low database hit-rate compared to other products. Therefore, I estimated that, to get multi-user capability as well as multi-threading to work, we would have to have the primary keys generated up-front. It's not a new idea - Oracle uses the same technique by storing the primary keys in a sequence table. I tried that but was prevented from using that technique by the multi-threading in MP - due to database thrashing.

I am using two layers for database processing:
a) The cached layer to deliver data to the front-end;
b) A queue to process database instructions like add, delete and update.

The queue processor, which runs in its own thread, handles any anomalies like different users generating different ID values for a movie.
I haven't experienced any data corruption so far, because the queue forces other processes to wait until the database is free - meaning that only one process can access the database for add, delete or update at a time.

SQLite locks the database for the duration of any write. SQLite supports multiple reads, but only one right. This is why I have adopted the 'using' clause for every database access to ensure that data accesses, and locks, are as short as possible. Up till now, MP has locked the database for the entirety of a session - which is why we needed MPSync. I have designed the proposed system to work as a single user or multi-user system without the user having to do anything other than decide where they want the databases to reside. Since the system depends upon a PC running TVService, then I guess it would be logical to make that the location of the central databases. But my design doesn't depend upon that. The databases can be located anywhere on the network and that location is defined in MediaPortalDirs.xml.

That's true, but anyone in the household can delete a file using MP at any time. MP relies on people working together to avoid doing that.
BTW, the whole point of this upgrade is to not need MPSync any more.

Assuming that people will not delete videos other people are watching, I have designed the system so that while you are in a session; i.e. you have opened MP and are using it, the data cache will be local to you. In other words, if you leave off watching something and, say, watch the news, when you go back to that video you will resume from where you left off - whether or not other users watch the same video. Once you leave MP, and go back in again, you will refresh your cache from the database and will see what everyone else sees so far as resumes are concerned. I thought this was the best way to keep everyone happy. But I can't, of course, do anything about anyone deleting stuff deliberately.
It's an interesting point about the use of temporary media, like USB sticks. I think users are responsible for using the file menu to delete a video, irrespective of where the video is held. If you simply remove the USB stick, the data will remain in the database. In my design, you would have to use the file menu to delete the movie for the data to be purged from the database. So, I think the procedure I am suggesting should stand up in that scenario.

One last thing, I have made sure that times watched, watched and percentage watched work in conjunction with the play time percentage (the percentage at which you want to see where you are in a video). I notice that, irrespective of where you are in a video, the data watched field in movieinfo is updated to DateTime.Now. Is that still what you want to happen. It seems to me to be a slight inconsistency but there may be a reason for it.

Tony

<blockquote data-quote="Anthony Vaughan" data-source="post: 1286639" data-attributes="member: 153283">The pictures and folder databases already don't have auto-increment for the primary keys. I understand your concern, but MP has a very low database hit-rate compared to other products. Therefore, I estimated that, to get multi-user capability as well as multi-threading to work, we would have to have the primary keys generated up-front. It's not a new idea - Oracle uses the same technique by storing the primary keys in a sequence table. I tried that but was prevented from using that technique by the multi-threading in MP - due to database thrashing.I am using two layers for database processing:&nbsp; &nbsp; a) The cached layer to deliver data to the front-end;&nbsp; &nbsp; b) A queue to process database instructions like add, delete and update.The queue processor, which runs in its own thread, handles any anomalies like different users generating different ID values for a movie.I haven't experienced any data corruption so far, because the queue forces other processes to wait until the database is free - meaning that only one process can access the database for add, delete or update at a time.SQLite locks the database for the duration of any write. SQLite supports multiple reads, but only one right. This is why I have adopted the 'using' clause for every database access to ensure that data accesses, and locks, are as short as possible. Up till now, MP has locked the database for the entirety of a session - which is why we needed MPSync. I have designed the proposed system to work as a single user or multi-user system without the user having to do anything other than decide where they want the databases to reside. Since the system depends upon a PC running TVService, then I guess it would be logical to make that the location of the central databases. But my design doesn't depend upon that. The databases can be located anywhere on the network and that location is defined in MediaPortalDirs.xml.That's true, but anyone in the household can delete a file using MP at any time. MP relies on people working together to avoid doing that.BTW, the whole point of this upgrade is to not need MPSync any more.Assuming that people will not delete videos other people are watching, I have designed the system so that while you are in a session; i.e. you have opened MP and are using it, the data cache will be local to you. In other words, if you leave off watching something and, say, watch the news, when you go back to that video you will resume from where you left off - whether or not other users watch the same video. Once you leave MP, and go back in again, you will refresh your cache from the database and will see what everyone else sees so far as resumes are concerned. I thought this was the best way to keep everyone happy. But I can't, of course, do anything about anyone deleting stuff deliberately.It's an interesting point about the use of temporary media, like USB sticks. I think users are responsible for using the file menu to delete a video, irrespective of where the video is held. If you simply remove the USB stick, the data will remain in the database. In my design, you would have to use the file menu to delete the movie for the data to be purged from the database. So, I think the procedure I am suggesting should stand up in that scenario.One last thing, I have made sure that times watched, watched and percentage watched work in conjunction with the play time percentage (the percentage at which you want to see where you are in a video). I notice that, irrespective of where you are in a video, the data watched field in movieinfo is updated to DateTime.Now. Is that still what you want to happen. It seems to me to be a slight inconsistency but there may be a reason for it.Tony</blockquote>

[QUOTE="Anthony Vaughan, post: 1286639, member: 153283"] The pictures and folder databases already don't have auto-increment for the primary keys. I understand your concern, but MP has a very low database hit-rate compared to other products. Therefore, I estimated that, to get multi-user capability as well as multi-threading to work, we would have to have the primary keys generated up-front. It's not a new idea - Oracle uses the same technique by storing the primary keys in a sequence table. I tried that but was prevented from using that technique by the multi-threading in MP - due to database thrashing. I am using two layers for database processing: a) The cached layer to deliver data to the front-end; b) A queue to process database instructions like add, delete and update. The queue processor, which runs in its own thread, handles any anomalies like different users generating different ID values for a movie. I haven't experienced any data corruption so far, because the queue forces other processes to wait until the database is free - meaning that only one process can access the database for add, delete or update at a time. SQLite locks the database for the duration of any write. SQLite supports multiple reads, but only one right. This is why I have adopted the 'using' clause for every database access to ensure that data accesses, and locks, are as short as possible. Up till now, MP has locked the database for the entirety of a session - which is why we needed MPSync. I have designed the proposed system to work as a single user or multi-user system without the user having to do anything other than decide where they want the databases to reside. Since the system depends upon a PC running TVService, then I guess it would be logical to make that the location of the central databases. But my design doesn't depend upon that. The databases can be located anywhere on the network and that location is defined in MediaPortalDirs.xml. That's true, but anyone in the household can delete a file using MP at any time. MP relies on people working together to avoid doing that. BTW, the whole point of this upgrade is to not need MPSync any more. Assuming that people will not delete videos other people are watching, I have designed the system so that while you are in a session; i.e. you have opened MP and are using it, the data cache will be local to you. In other words, if you leave off watching something and, say, watch the news, when you go back to that video you will resume from where you left off - whether or not other users watch the same video. Once you leave MP, and go back in again, you will refresh your cache from the database and will see what everyone else sees so far as resumes are concerned. I thought this was the best way to keep everyone happy. But I can't, of course, do anything about anyone deleting stuff deliberately. It's an interesting point about the use of temporary media, like USB sticks. I think users are responsible for using the file menu to delete a video, irrespective of where the video is held. If you simply remove the USB stick, the data will remain in the database. In my design, you would have to use the file menu to delete the movie for the data to be purged from the database. So, I think the procedure I am suggesting should stand up in that scenario. One last thing, I have made sure that times watched, watched and percentage watched work in conjunction with the play time percentage (the percentage at which you want to see where you are in a video). I notice that, irrespective of where you are in a video, the data watched field in movieinfo is updated to DateTime.Now. Is that still what you want to happen. It seems to me to be a slight inconsistency but there may be a reason for it. Tony [/QUOTE]