patch - centralized DB fixes. (1 Viewer)

[gibman]

Hi!

Several issues with moving pictures have been bothering me for too long now.
Primarily because I run a centralized sqllite database over a common network share.

A common practice for those who have more than one htpc rig in their setup and at the same time want to be able to share the database amongst these clients.

No idea how popular this setup is ?

But here goes.

My fixes include:

1) The database connection was not closed when going into standby/hibernation mode. This would cause all kinds of SQL lite exceptions once the HTPC was resumed again. This would often cause the db image (file) to become malformed. The only solution in these situations would be to rebuild the database once again. Tedious job.

My fix for this includes having the movpic plugin now properly react to the onsuspend and onresume events.
On "OnSuspend" it will close the DB connection. On resume it will reestablish connection.

2) When resuming the client, no new movies would be added to the GUI. I have to restart MP in order to have the GUI refreshed.

The solution for this was to have the worker threads abort when going into standby/hibernation mode and restart when resuming.
Likewise it will also detach any of the listening events to the database manager and reestablish these on resume.
Now I am able to see newly added movies on a resumed movpics client.

3) Optical and Removable drives are added to the "import_path" table as internallymanaged=1.

consider this problem (both clients share the same DB).

client1:
c: HD drive
d: Optical drive

client2:
c: HD drive
d: network drive, not containing movies, but other irrelevant stuff.

client1 starts movpics.
"D:\" is added with internallymanaged=1.
client1 exits.

client2 starts movpics.
now movpics detects "D:\" as an internally managed drive and starts to scan/analyze it.
The problem is that drive D on client2 is a network drive.
So movpics start to crunch the networked HD for hours (depending on size etc), and adding irrelevant stuff to the DB.

The solution to this problem is to always test an internally managed drive type before scanning.
The drivetype must NOT be a fixed or network type.
If so, the import path entries will be deleted.


No doubt that movpics would be performing better on a real SQL engine like "ms SQL express" or "mySQL".
Changing the SQL dataprovider would be the best option.

we can only wait


Edit: wait's over

Ok, patch ready for some testing.

It should work under both sqlite and ms sql express (ex. the same as tvserver uses).

It initially looks for :
C:\ProgramData\Team MediaPortal\MediaPortal\MovingPictures.xml
If it doesnt exists, it will create it.

<?xml version="1.0" encoding="utf-8"?>
<profile>
<section name="database">
<entry name="SQLexpressEnabled">yes</entry>
<entry name="SQLexpressConnectString">Data Source=myserver\SQLEXPRESS;Initial Catalog=MovingPictures;User Id=sa; password=mypassword;Trusted_Connection=False;MultipleActiveResultSets=true</entry>
</section>
</profile>

if u wish to re-enable sqlite, then please use:
SQLexpressEnabled = no.

use the correct connect string here.

You currently have to create the DB yourself.
Use this script:

USE [master]
GO

/****** Object:  Database [MovingPictures]    Script Date: 01/22/2010 21:45:04 ******/
CREATE DATABASE [MovingPictures] ON  PRIMARY 
( NAME = N'MovingPictures', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MovingPictures.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MovingPictures_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MovingPictures_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MovingPictures] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MovingPictures].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [MovingPictures] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [MovingPictures] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [MovingPictures] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [MovingPictures] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [MovingPictures] SET ARITHABORT OFF 
GO

ALTER DATABASE [MovingPictures] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [MovingPictures] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [MovingPictures] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [MovingPictures] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [MovingPictures] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [MovingPictures] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [MovingPictures] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [MovingPictures] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [MovingPictures] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [MovingPictures] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [MovingPictures] SET  DISABLE_BROKER 
GO

ALTER DATABASE [MovingPictures] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [MovingPictures] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [MovingPictures] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [MovingPictures] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [MovingPictures] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [MovingPictures] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [MovingPictures] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [MovingPictures] SET  READ_WRITE 
GO

ALTER DATABASE [MovingPictures] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [MovingPictures] SET  MULTI_USER 
GO

ALTER DATABASE [MovingPictures] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [MovingPictures] SET DB_CHAINING OFF 
GO

When u run mov pics plugin, take a look in :
C:\ProgramData\Team MediaPortal\MediaPortal\log\movingpictures.log

It should read like this:
22-Jan-2010 21:42:52 Info [ DatabaseManager]: SQL express initialized

or

22-Jan-2010 21:42:52 Info [ DatabaseManager]: SQLite initialized

note: the patch also consists of the subtitles patch I did here -
https://forum.team-mediaportal.com/...-retriever-library-movpic-tvseries-etc-75614/
Some external DLLs etc. are needed.

Or you can locally remove the subtitles part of the patch.
so in essence it's a subtitles retriever + sql express patch, all combined.

/gibman

 

[armandp]

I like the power management additions (1 + 2) !

However the patch for the network setup internal drive management is hacky at best. It's just not designed for multi-seat (yet).
Because now the two clients will compete in and adding/removing paths as internal or even deleting them which can result in the deletion of movies.

Could you split the patch so that the power management stuff is seperate from the drive management part ?

 

[gibman]

Yeah, the drive management part is hacky.
And yes it's quite clear that movpics wasn't designed for multi client usage.
It needs a "client" table that would map to the import_path "table" etc.

One way to fix it without touching any code would be to NOT have any network mappings on all movpics clients.

I've split up the patches into 2 as requested.
part 1+2
part 3.

/gibman

I like the power management additions (1 + 2) !

However the patch for the network setup internal drive management is hacky at best. It's just not designed for multi-seat (yet).
Because now the two clients will compete in and adding/removing paths as internal or even deleting them which can result in the deletion of movies.

Could you split the patch so that the power management stuff is seperate from the drive management part ?

 

[disaster123]

AW: patch - centralized DB fixes.

gibman
same is with mp tvseries (especially not closing the DB connection when shutting down) will you also provide patches for this one? would be really nice.

 

[RoChess]

3) Optical and Removable drives are added to the "import_path" table as internallymanaged=1.

The problem is that drive D on client2 is a network drive.

You can also just adjust the drive letter on either the optical drive on client1, or the network drive on client2. I personally always adjust optical drive to R: myself, S: for a 2nd optical. Unless you have a giant amount of drives and/or shares, you should have no problem using the 23 letters available to avoid conflicts.

PS: Harddrive conflicts can be solved via UNC, so if client1 has a D: harddisk with movies, you can use the UNC path "\\Client1\D_Share\" on client1 itself as well, which will then also work on client2 and not conflict with the D: harddisk on client2.

 

[gibman]

Re: AW: patch - centralized DB fixes.

done.

https://forum.team-mediaportal.com/my-tvseries-162/patch-centralized-db-fixes-76440/#post563495

/gibman

@gibman
same is with mp tvseries (especially not closing the DB connection when shutting down) will you also provide patches for this one? would be really nice.

 

[disaster123]

AW: Re: AW: patch - centralized DB fixes.

done.

https://forum.team-mediaportal.com/my-tvseries-162/patch-centralized-db-fixes-76440/#post563495

/gibman

@gibman
same is with mp tvseries (especially not closing the DB connection when shutting down) will you also provide patches for this one? would be really nice.

COOL! Thanks so much!!!

 

[Shukuyen]

I think that setup is getting more and more popular, at least I have it since the beginning of this month, too :-D
Real multiseat support would be great and appreciated and there are already is an issue on the tracker (Issue 23 - moving-pictures - Centralized Database / Multi-Seat Support - Project Hosting on Google Code) for this. At the moment I disabled the importer on the second client and hope nothing goes wrong -.-

 

[disaster123]

AW: patch - centralized DB fixes.

at least i still not understand why mp tvseries and movingpictures are not simply using MSSQL or MySQL. And for shure it is already installed by the tvserver.

 

[gibman]

Re: AW: patch - centralized DB fixes.

It seems a bit silly to me as well that movpics was designed in this way.
Also considering the fact that movpics isn't that old.

Ofcourse single user (SQL lite engine) is a fast way of delivering a database without the user having to fiddle with sql express installation etc.

/gibman

at least i still not understand why mp tvseries and movingpictures are not simply using MSSQL or MySQL. And for shure it is already installed by the tvserver.