Database: Refactor MP1 database for multi seat usage (3 Viewers)

[Anthony Vaughan]

I have to disagree with that (or perhaps I'm misunderstanding you, do you indeed do a new SQLiteClient for each query?)
I'm using postgres at work quite extensively and if I create a new postgres connection for every query I want to do that certainly takes it's time. (don't ever see something like an internal database cache for that...)

I'm not using SQLiteClient - I'm using Entity Framework and LINQ. However, I have converted some SQLiteClient code because we still need some old code in MP - because of dynamic queries. They now connect - do stuff - disconnect; and there is no performance issue. As a user you simply wouldn't notice.

But, even when I used SQL, back in 1994, with stored procedures or in-line SQL, I did a project where I was tasked with testing the difference between Oracle connects first time and subsequently. The first connect always took a while and all connects after that were quick. I'm talking about connect - do stuff - disconnect - every time. Once you've done the first connect all connects thereafter are pretty much negligible, performance wise.

The same is true for SQLBase, SQL Server and now, in my experience, SQLite.

You're not the first person I have heard saying that .

 

[Anthony Vaughan]

I don't know.
How big do you anticipate a zip file containing the updates would be? Perhaps @high needs to comment. There may be a better way of providing the updates to all who want to get involved in testing.

Just for testing, the files won't be too large. I was thinking about later when I need to post a full release. I'm guessing there is a route through GIT to do that.

To test my work you can just install run a batch file and it will overwrite your installation (well a few parts of it anyway).

I assume you will be using a test machine for that .

 

[Anthony Vaughan]

Here's an example of what I mean using SQLClient:

SQLiteResultSet resultSet;

using (m_db = new SQLiteClient(GetDatabaseName()))
{
resultSet = m_db.Execute(strSql);
}

return resultSet;

At the end of the using scope, the connection is released.

That does not mean that m_db is closed it just means that any locks are released.

I don't know anyone nowadays that doesn't think that this is the way to go.

Google it

 

[ajs]

Can you tell me where I can deposit files for others to pick up? I'll try to make it easy to install by sending the files and a batch file to do the install - to keep it really simple. Alternatively, as you're in the UK, CyberSimian, I am happy to communicate by email to avoid cluttering up the forum should that work better - using the forum to get views from people generally.

Post them in this thread, as an attachment, that's the easiest way ... I don't think it's a big size.

One of the features I have placed in the code is to delete all database records related to a video when you delete the video from the hard disk.

You have to be careful with these, not everything has to be deleted when deleting a movie from the database. Actors, Genres, Collections, Groups, etc. only if they are not used in other movies. Information about actors' movies, and something else, I can't remember.

database has any records for files that are no longer in the current folder (LoadDirectory) and remove them

What about mapped directories on network drives? They may not be available at the moment, but 10 minutes later they will be and there will be movies there? What about removable media, etc.?

 

[ajs]

I'm not using SQLiteClient - I'm using Entity Framework and LINQ. However, I have converted some SQLiteClient code because we still need some old code in MP - because of dynamic queries. They now connect - do stuff - disconnect; and there is no performance issue. As a user you simply wouldn't notice.

Have you tried all modes of operation in display mode - MyVideo Database?

 

[Anthony Vaughan]

What about mapped directories on network drives? They may not be available at the moment, but 10 minutes later they will be and there will be movies there? What about removable media, etc.?

Only folders that you go into in MP will have data processed.

Here's an example:



So, you have to go into the folder for any database processing to happen.

There is no batch process that randomly deletes files.

I hope this clarifies the point .

 

[ajs]

Only folders that you go into in MP will have data processed.

But in these folders can be symlink, hardlink to the file I sometimes practice this (but rarely), and the files may be on a media not currently available

 

[Anthony Vaughan]

You have to be careful with these, not everything has to be deleted when deleting a movie from the database. Actors, Genres, Collections, Groups, etc. only if they are not used in other movies. Information about actors' movies, and something else, I can't remember.

Correct. Only data related to the movie being deleted and not link to anything else will be removed.

 

[Anthony Vaughan]

But in these folders can be symlink, hardlink to the file I sometimes practice this (but rarely), and the files may be on a media not currently available

Can you give me an example.

Be aware that we are only talking about movie database records belonging to the folder being displayed where the file in strFilename no longer exists.

I thought that was bullet-proof. Can you think of an example where that would be dangerous? I'll need to examples to understand the problem.

So far as I'm aware, the database ignores non-physical files so far as the movie table is concerned.

 

[ajs]

Can you give me an example.

I don't even know how to explain it. The symlink/hardlink to the file is described in the Microsoft knowledge base.
But the folder can look like this:
file1.avi
file2.avi
file3.cd1.avi
file3.cd2.avi
file3.cd3.avi
file4.avi ---> symlink/hardlink ---> other HDD, or network drive on other host
file5.avi

Be aware that we are only talking about movie database records belonging to the folder being displayed where the file in strFilename no longer exists.

We have two modes of operation, one is file mode and the other is database mode.
In the first mode (shares), movies may or may not be in the database, but in this mode, if the file is physically there, it will be visible.
In the second mode (database), we see only what is in the database, regardless of whether the file is physically present or not. Especially it can be a network resource and if you try to play it, the file can be mounted and then it will be physically available.
In database mode, we also have a lot of ways to display data, it all depends on the view settings. And the level of detail in the data can be quite large.

I thought that was bullet-proof. Can you think of an example where that would be dangerous? I'll need to examples to understand the problem.

Here it is difficult to provide for all options, so just delete what is not there at the moment is a bad solution, you have to do a lot of checks, etc. That's why automatic deletion is not the best solution. And the easiest solution is to let the user delete it himself. By hand.