leftovers in database (1 Viewer)

[Slurm]

I've recently added a new node for actors to moving pictures and after deleting it again all actors are still in various tables (node, ...). My database grew from 100 MB to over 1 GB and I want to know if there's a way to cleanup all unnecessary leftovers.

 

[RoChess]

You can of course use an SQLite database tool and manually clean things up, but the time required to figure out the table and cell connections can add up quick. So unless you have a backup of the 'before' that you can compare to the 'after', it is usually easier to just start with a fresh new database. Or restore that backup to begin with

 

[Slurm]

You can of course use an SQLite database tool and manually clean things up, but the time required to figure out the table and cell connections can add up quick. So unless you have a backup of the 'before' that you can compare to the 'after', it is usually easier to just start with a fresh new database. Or restore that backup to begin with

Thanks, I tried to find out which entries are not needed, but as you said this is really hard work. I hoped that there are some handy sql commands to delete all these entries.

The manual changes from my old backup are quite big (added new descriptions, ...) and I don't want to start all over again also because of all the changes.

Maybe there's some way to export all relevant data and import them into a fresh install?

 

[m3rcury]

You can of course use an SQLite database tool and manually clean things up, but the time required to figure out the table and cell connections can add up quick. So unless you have a backup of the 'before' that you can compare to the 'after', it is usually easier to just start with a fresh new database. Or restore that backup to begin with

Thanks, I tried to find out which entries are not needed, but as you said this is really hard work. I hoped that there are some handy sql commands to delete all these entries.

The manual changes from my old backup are quite big (added new descriptions, ...) and I don't want to start all over again also because of all the changes.

Maybe there's some way to export all relevant data and import them into a fresh install?

You can try SQLite Database Browser 2.0 b1 to export into a csv/sql and import back.

 

[Slurm]

You can try SQLite Database Browser 2.0 b1 to export into a csv/sql and import back.

How can I import the data directly into moving pictures, because importing them back into the sqlite database wouldn't change anything?

 

[m3rcury]

You can try SQLite Database Browser 2.0 b1 to export into a csv/sql and import back.

How can I import the data directly into moving pictures, because importing them back into the sqlite database wouldn't change anything?

When you export the required tables to say csv and clean the unwanted data, you then have to rename the respective existing table in movingpictures.db3 and import back the amended csv giving it the correct table name. When you are satisfied, you can then delete the old renamed table and choose the pack option to compact the db3 file, thus reducing its size.

 

[Slurm]

You can try SQLite Database Browser 2.0 b1 to export into a csv/sql and import back.

How can I import the data directly into moving pictures, because importing them back into the sqlite database wouldn't change anything?

When you export the required tables to say csv and clean the unwanted data, you then have to rename the respective existing table in movingpictures.db3 and import back the amended csv giving it the correct table name. When you are satisfied, you can then delete the old renamed table and choose the pack option to compact the db3 file, thus reducing its size.

The problem is to find the unwanted rows in the tables, because they are related to each other (node and node__node seem to be related, and so are others tables too).

Either there are still leftovers if I try to delete only the ones I'm definitly sure of or I will end up with a non working moving pictures if I delete too much.

 

[Slurm]

I did some trial and error again and found a way to determine the tables needed to build a clean database. Simply said all tables with movie or media in its name and watched_history are needed except the filters tables and movie_node_settings. I dumped those tables and restored them in a clean database. After starting moving pictures configuration I only had to add my custom scraper and everything worked fine and the table size decreased dramatically. I'm not sure if I miss something with this method but it works for me.

Thanks to RoChess and m3rcury for your help!

 

[RoChess]

And next time make a backup

There are plugins that will do it for you, or you can use your own method. I launch MediaPortal via a batch-script to make sure it doesn't launch again when it is already running (concurrent RDP), and I've added a verification and backup method to it. To verify the databases did not corrupt I use the open source commandline util from the SQLite project via:

echo pragma integrity_check; | sqlite3.exe "%~dpf1" | find /c "ok" &&SET Pass=Yes
IF "%Pass%"=="Yes" (
  ::DB3 good
)
IF "%Pass%"=="No" (
  ::DB3 corrupt
)

And if it passes it will become the new backup and if it fails the previous backup file is restored. Was forced to do this because the FanartHandler database tends to corrupt on occasion for me. The script also checks to see if it is a new month and then makes an additional backup. The entire script is very specific for my usage, but if there is interest I will clean it up so others can use it.

 

[Slurm]

And next time make a backup

There are plugins that will do it for you, or you can use your own method. I launch MediaPortal via a batch-script to make sure it doesn't launch again when it is already running (concurrent RDP), and I've added a verification and backup method to it. To verify the databases did not corrupt I use the open source commandline util from the SQLite project via:

echo pragma integrity_check; | sqlite3.exe "%~dpf1" | find /c "ok" &&SET Pass=Yes
IF "%Pass%"=="Yes" (
  ::DB3 good
)
IF "%Pass%"=="No" (
  ::DB3 corrupt
)

And if it passes it will become the new backup and if it fails the previous backup file is restored. Was forced to do this because the FanartHandler database tends to corrupt on occasion for me. The script also checks to see if it is a new month and then makes an additional backup. The entire script is very specific for my usage, but if there is interest I will clean it up so others can use it.

Thanks for that. I do backups on a regular base, but my oldest backup is 30 days old and the changes were made before that. I will reconsider my backup strategy for cases like this.