Move to SQL server instead of db3 file? (1 Viewer)

[MrLobster]

As it currently stands, you can point the plugin to the files stored on a shared directory on another PC (say a PC in the loft). But its just all the DB stuff and images that are stored at client level. But streaming the images etc from the PC in the loft isnt hard.

Using SQL (MySQL prefered for me), would help a lot in an attempt to get a multiseat working. I dont think multi user for marking watched etc are needed as it would over complicate things too much.

Client+Server (Singleseat) setup:

HTPC
Mediaportal
All plugins point to the relevent Media Folder on clients PC.
MySQL DB for TvServer/TvSeries/MovingPictures.
All Movies/Episodes/Videos/Music/Pictures
All Fanart
All Episodes Images


Client with Server (Multiseat) setup:

HTPC 1 (Client), HTPC 2 (Client)
Mediaportal
Mapped Drive using relevent folders to the media on the MediaCenter (Server)*
All plugins point to the Mapped Drive*

MediaCenter (Server)
MySQL DB for TvServer/TvSeries/MovingPictures.
All Movies/Episodes/Videos/Music/Pictures
All Fanart
All Episodes Images
Shared Drive for the HDD(s) which hold the media above*

* This could be configured to a single drive being mapped and then the plugin is given the folder name.

Is this too far from what people are wanting?

As for being too complicated for those wanting singleseat ability only, its not too difficult, as most people will think about using tvserver at some time, and its pretty harmless to install MySQL (all you need to remember is a username and password, which tvserver provides on install).

For those wanting Multiseat, they are more likely a little more aware of how networks function, and will be easy for them to setup this system.

 

[Zasurus]

But it seems to me that it is possible to use a shared SQLITE DB.
Read:
File Locking And Concurrency In SQLite Version 3
and Read:
SQLite Shared-Cache Mode

I believe fforde means the solution could be one application (server) accesses and updates the db and the others (Clients) ask it for and give it information.

That way it won't matter that it locks the db. This is why I am voting that we go with a SQL Server type setup anyway even if we did go for a server/client setup for the plugin/MediaPortal.

Zas

 

[disaster123]

But i think staying at sqlite but follow the rules described in these documents is easier / faster doable.

 

[Zasurus]

I dont think multi user for marking watched etc are needed as it would over complicate things too much.

This part could be done later anyway. And if it was done you would have to consider things like when the whole family are watching a show at the end credits rolling you could have the option to quickly mark the other users as watched. But I do agree that this would be a later addon as it isn't required for multi-PC setup.

But it seems to me that it is possible to use a shared SQLITE DB.
Read:
File Locking And Concurrency In SQLite Version 3
and Read:
SQLite Shared-Cache Mode

But i think staying at sqlite but follow the rules described in these documents is easier / faster doable.

Oh I didn't read your first post correctly. I might have a read of that and see if it helps me out with my program reading and maybe even writing to the db... :-D



Also you could be right for a few users that MAY be doable... Worth a try... Isn't the code for this plugin open source? I'm sure someone could try it... (I already have my fingers in more pies than I can maintain! :-|)

Zas

 

[disaster123]

Code is open source - but i wasn't able to figure the SQlite stuff out. Perhaps someone with more C# / .NET knowledge can do that

 

[fforde]

I agree with aj1405. Concurrency is the main issue in multi seat setups (afaik) and sqlite is not made to handle concurrent transactions, MySQL and MSSQL on the other hand are so it's a logical choice especially since the tvserver needs the same (for the same reason I suppose)

No I understand. And I disagree. It MAY be possible to crate a "multi-seat" system on a SQLite db structure for ONE application but as "aj1405" pointed out bellow SQLite does completely lock the complete DB file during read/write operations making it next to impossible to use with more than one application at once.

You two are misinformed, as disaster123 pointed out, SQLite handles mulitple connects just fine.

Most likely the problem in 95% of cases isn't even that your databases are becoming "corrupted" at least not in the sense you are thinking. The database files are still valid SQLite databases. The problem is the data in the database gets into an invalid state or applications get out of sync with the database when multiple changes from multiple locations are made in series. For example say two instances of Moving Pictures are running on two machines, one machine is a sleep. You delete a movie from your network share and the first instance of Moving Pictures removes the entry from the database. You wake up your second system, it sees the file is gone and thinks the file is still in the database due to cached information in memory. It tries to delete the record, and fails. Hopefully it will fail gracefully and not crash the program, but the problem is this is not a scenario that Moving Pictures was designed for so in situations like this (and others like it) often there is not logic to deal with these sorts of problems.

I hope that makes sense to you. Basically the problem is not the database engine, it is how the plugins interact with the database. The "corruption" people talk about does not refer to a broken database file, it refers to bad data in the database or applications out of sync with it.

I believe fforde means the solution could be one application (server) accesses and updates the db and the others (Clients) ask it for and give it information.

That way it won't matter that it locks the db. This is why I am voting that we go with a SQL Server type setup anyway even if we did go for a server/client setup for the plugin/MediaPortal.

Yes I think that is the best solution. A client/server setup where the server acts as an abstraction layer to the database. This guarantees there is open piece of logic that is in charge of maintaining database integrity. This does not solve all problems, but it is by far the cleanest solution in my opinion. Again though this has nothing to do with implementing the database via MS SQL Server. With this kind of setup, a more "robust" database system brings virtually zero benefits, consumes far more system resources, and potentially places a higher burden on the user maintenance of their setup.

 

[disaster123]

fforde
moving pictures is working fine for me also with two clients online. Only MP-Tvseries does not. I often get a "REAL" corrupted file. Not only an inconsistency between clients. It is really corrupt and when i do a "PRAGMA integrity_check;" it says corrupted database. i don't know why this has never happend with MovingPictures only with MP-Tvseries everyweek but ...

 

[fforde]

Well I stand corrected then about the corruption issue. I was under the impression the frequency of actual database corruption was much much lower than that. Sorry for probably coming across so strong. :/

Anyway in this situation it's probable that MySQL or SQL Server would prevent the corruption issues although I still think it is very likely the database data itself would become invalid or out of sync.

 

[tourettes]

Well I stand corrected then about the corruption issue. I was under the impression the frequency of actual database corruption was much much lower than that. Sorry for probably coming across so strong.

Just thinking aloud. Isn't SQLite completely file basis SQL database? And file systems aren't quaranteening atomic access
to files (well, NTFS in Vista supports transactions, but I bet SQLite arent using that feature).

So, following could be possible?

Process A - Reads file a file content to determine that there is no DB access ongoing
Process B - Reads file a file content to determine that there is no DB access ongoing
Process A - Writes that DB access starts (locks database)
Process B - Writes that DB access starts (locks database)

After that both processes A & B write content to the database file.

Is there any working way to make that read and write from a process to be atomic? Isn't that normally handled by the DBMS?

 

[fforde]

Most file systems provide the ability to lock a file and SQLite does indeed use this feature. According to their documentation though, with many configurations, file locks on network shares are not consistently honored. I suspect this is a big cause of the corrupt database issue. If you can't guarantee atomic access to the database though, I am not sure there is a work around aside from limiting access to the database to only one app (I am referring to a client/server scenario again).