Looking for a code review

[HawkXP71 122]

I have a plugin in mind, but in order to learn how to develop it, I have been essentially re-creating an existing plugin.

Essentially I am re-creating the statistics plugin, and rather than storing everything in a XML configuration file, I am using an sqlite db

I am having some weird failures.  

If anyone is up for a code review, it would be most appreciated.

TIA
Scott

 

[Luke 43067]

Where is the code?

[softworkz 5376]

7 hours ago, HawkXP71 said:

I have a plugin in mind, but in order to learn how to develop it, I have been essentially re-creating an existing plugin.

Essentially I am re-creating the statistics plugin, and rather than storing everything in a XML configuration file, I am using an sqlite db

I am having some weird failures.  

That's not a surprise. Plugins need to be self-contained. They cannot have any references of their own, because it's solely the plugin dll's code which is loaded into the Emby Server process, so whatever SQLite libraries you have (presumably) added to your plugin as nuget packages won't be looaded. Emby Server uses SQLite itself, but through customized libraries. Some of your code's references may get redirected to Emby's customized implementation which have different behavior than the ones you are probably developing against.

This would exactly create something like "weird failures".

[HawkXP71 122]

16 hours ago, Luke said:

Where is the code?

https://github.com/towel42-com/Statistics2026  on the AllStatistics branch

 

[HawkXP71 122]

10 hours ago, softworkz said:

That's not a surprise. Plugins need to be self-contained. They cannot have any references of their own, because it's solely the plugin dll's code which is loaded into the Emby Server process, so whatever SQLite libraries you have (presumably) added to your plugin as nuget packages won't be looaded. Emby Server uses SQLite itself, but through customized libraries. Some of your code's references may get redirected to Emby's customized implementation which have different behavior than the ones you are probably developing against.

This would exactly create something like "weird failures".

Unfortunately that makes sense

Is there an exposed ISQLManager ???? Im using the same SQL interface used in the playback_reporting plugin

[softworkz 5376]

6 minutes ago, HawkXP71 said:

Unfortunately that makes sense

Is there an exposed ISQLManager ???? Im using the same SQL interface used in the playback_reporting plugin

I didn't mean to say it can't be done, that was just the most likely explanation.

If the playback reporting plugin can do it, you should be able to do that as well. 

From a quick look:

• Downgrade System.Memory to 4.6.0
• Remove Emby.ApiClient package
  (not needed, might contain conflicting types)

Then start by trying to do the same what the playback_reporting plugin does

[HawkXP71 122]

2 hours ago, softworkz said:

I didn't mean to say it can't be done, that was just the most likely explanation.

If the playback reporting plugin can do it, you should be able to do that as well. 

From a quick look:

• Downgrade System.Memory to 4.6.0
• Remove Emby.ApiClient package
  (not needed, might contain conflicting types)

Then start by trying to do the same what the playback_reporting plugin does

Done as stated:

        <PackageReference Include="MediaBrowser.Server.Core" Version="4.8.0.27-beta" />
        <PackageReference Include="SQLitePCL.pretty.core" Version="1.2.2" />
        <PackageReference Include="System.Memory" Version="4.5.5" />

No difference.  Have two sql calls, which randomly fail.  

with the following being reported

026-08-22 16:29:56.358 Debug App: Sqlite: 11 - database corruption at line 88897 of [17144570b0]
2026-08-22 16:29:56.358 Debug App: Sqlite: 11 - database corruption at line 88897 of [17144570b0]
2026-08-22 16:29:56.358 Debug App: Sqlite: 11 - statement aborts at 12: [SELECT DISTINCT StudioNames FROM Media WHERE IsEpisode AND StudioNames IS NOT NULL AND StudioNames IS NOT NULL AND StudioNames != ''] database disk image is malformed
2026-08-22 16:29:56.358 Debug App: Sqlite: 11 - statement aborts at 7: [SELECT COUNT(DISTINCT(PrimaryName)) FROM Media WHERE IsEpisode] database disk image is malformed
 

Though I can run the exact queries at from the command line with no issues.

Any thoughts? Is the best practice to not use SqlLite for large data extraction, analysis and reporting?

[softworkz 5376]

Is the database on the local file system or on a network location?

Are multiple processes accessing the db at the same time?

Are you reading/writing at the same time?

Did you copy the db file without copiying other files (like .wal or .shm)?

From cli (or SQLite Studio), run "PRAGMA integrity_check;" on the DB

[HawkXP71 122]

Qll

4 hours ago, softworkz said:

Is the database on the local file system or on a network location?

Are multiple processes accessing the db at the same time?

Are you reading/writing at the same time?

Did you copy the db file without copiying other files (like .wal or .shm)?

From cli (or SQLite Studio), run "PRAGMA integrity_check;" on the DB

All local. 

I lock the connection on each individual read and write. 

It's all created inside emby, no copying. 

I'll run that check.  Thanks. 

[HawkXP71 122]

On 8/22/2026 at 5:02 PM, softworkz said:

Is the database on the local file system or on a network location?

Are multiple processes accessing the db at the same time?

Are you reading/writing at the same time?

Did you copy the db file without copiying other files (like .wal or .shm)?

From cli (or SQLite Studio), run "PRAGMA integrity_check;" on the DB

Ran the integrity check, sqlite returned ok.

[HawkXP71 122]

Can you tell me what SQL Lite driver is used inside emby that could be causing the problem? Ill switch on my side and see if it clears up

[softworkz 5376]

Are you accessing Emby's db files or your own ones only?

[HawkXP71 122]

8 hours ago, softworkz said:

Are you accessing Emby's db files or your own ones only?

Only my own db

[softworkz 5376]

10 hours ago, HawkXP71 said:

Only my own db

Is the code in your repo up-to-date with your latest state?
I might be able to take a look in the next days.

[HawkXP71 122]

Yes it is

[HawkXP71 122]

@softworkz I think I figured out the issue.

It looks like I missed a lock on the DB connection in a couple of places.

This was allowing multiple threads to query the connection, which is a big no no.

I think I have it fixed.  
Thanks

[HawkXP71 122]

On 8/26/2026 at 8:07 PM, HawkXP71 said:

@softworkz I think I figured out the issue.

It looks like I missed a lock on the DB connection in a couple of places.

This was allowing multiple threads to query the connection, which is a big no no.

I think I have it fixed.  
Thanks

While creating a lock around every connection WILL solve the problem, it 100% will cause pauses and a less that ideal user experience

In my page, its a JS page that makes about 25 API calls to the server.  When the long running call hit, with a lock on every db call, the page would stop loading at that call.  Its only about 600ms but it was long enough to be frustrating.

There are two changes that I had to make to fix this correctly.

• Every DB connection gets its OWN db connection.  Dont share a global one.   Before I had 1 instance that was shared.
• Make sure you open it in with PRAGMA journal_mode=WAL.
  This allows sqlite3 to be reentrant which also fixed my random crash issue, and the user experience is much better.

The combination of these two changes, the page fills in the faster calls and sometime later fills in the one long running (its only about 600ms, but its enough to feel slow when holding up everything.

Since there are enough calls, you dont really feel like you are waiting at all