Jump to content


Photo

Restoring 'Recently Added' view after Restoring from backup

sqlite mediaitems recently added datecreated datemodified datelastsaved datelastrefreshed

  • Please log in to reply
21 replies to this topic

#1 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 30 January 2019 - 08:09 PM

Hey there, I've recently had to perform a restore from backup of Emby on my Qnap TS-451+, For the most part everything is restored and is currently functioning to my satisfaction. 

 

Having said that, my current issue is a cosmetic effect rather than some issue that concerns Emby's functioning or operation.

 

I'm trying to restore the 'Recently Added' view. In restoring from backup, I first did a complete uninstall of the previous instance of Emby Server. I then re-installed Emby Server and restored from backup. The detailed process that I followed came from Emby Wiki. As you might expect, the order of movies displayed in the Recently Added view has been randomized. My desire is to have the order of this view restored to what it was when the backup was originally taken.

 

What I've been able to gather is that the library.db file is the database for the Emby library. There are many tables in this database but I think the table that has the info I wish to restore/change is the MediaItems table. There are 4 date fields in that table. DateCreated, DateModified, DateLastSaved & DateLastRefreshed. Using a single movie, I queried those fields from the backed up version of the library.db database. I constructed a SQL query that would update the timestamps for this movie in a copy of the Live library.db file. The query finished without issue, but I didn't get the desired result.

 

So, my question is, do I have the correct database and fields? Are there any additional tables/fields that I need to be changing/updating in addition to these fields? What other precautions should I be considering?

 

TIA



#2 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 02 February 2019 - 03:19 AM

Hi there @serendrewpity, yes, DateCreated is the one you want. Please let us know if this helps. Thanks !



#3 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 04 February 2019 - 12:04 PM

Thank you. For those who might find themselves in a similar situation, it might be a useful exercise to make modifications to the database, but in the end it proved more effective to use a tool like Attribute Changer to change file date & time stamps to achieve the desired results.



#4 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 04 February 2019 - 02:54 PM

Thanks for the feedback !



#5 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 05 February 2019 - 08:52 AM

Interesting learning experience. Linux doesn't maintain a file's datecreated attribute. So modifying this attribute of movie files in your library may work on Windows versions of Emby Server, it does not for the Linux Qnap versions.

 

So what does this mean?

 

5c598688c7034_20190205_74448.jpg

 

Does this render the 'Date created' equal to the 'Date Scanned into the Library'??

 

If so then I am back to modifying the mediaitems table of the library database. Why doesn't this work either?



#6 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 05 February 2019 - 08:56 AM

Perhaps SAMBA is used to keep track of files datecreated attribute. Although I can't seem to find any samba settings in Emby Server 4.1.0.5 [yes, I'm aware this is beta]



#7 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 01:18 PM

 

 

Linux doesn't maintain a file's datecreated attribute

 

Are you sure?



#8 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 04:34 PM

My filesystem is ext4; the stat command lists datecreated as 'birth' which is blank

I don't know how Modify differs from Change [maybe attributes -v- data]. 

 

Anyway, Change appears to be irrelevant since in windows the Date created and Date modified both use Linux's Modify attribute

  File: ‘/share/CACHEDEV7_DATA/Videos/Movies/Mission - Impossible - Fallout (2018) [1080p]/Mission - Impossible - Fallout (2018) ’
  Size: 4692416897	Blocks: 9164888    IO Block: 4096   regular file
Device: fc15h/64533d	Inode: 47055084    Links: 1
Access: (0664/-rw-rw-r--)  Uid: (  9999/xxxAdmin)   Gid: (    0/administrators)
Access: 2019-02-04 11:36:04.711811512 -0500
Modify: 2018-11-06 19:00:00.000000000 -0500
Change: 2019-01-25 02:28:33.980285893 -0500
 Birth: -


Edited by serendrewpity, 08 February 2019 - 07:36 AM.


#9 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 04:50 PM

wouldn't let me add this to the post above....

 

 

5c5c9a03577d7_20190207_154011.jpg



#10 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 04:57 PM

For giggles, Using Attribute Changer I changed the date created in windows and it changed the Change attribute in Linux. Which is wrong. It should change Birth ... if Linux supported Date created.

 

5c5c9b9094720_20190207_155558.jpg

  File: ‘/share/CACHEDEV7_DATA/Videos/Movies/Mission - Impossible - Fallout (2018) [1080p]/Mission - Impossible - Fallout (2018) ’
  Size: 4692416897	Blocks: 9164896    IO Block: 4096   regular file
Device: fc15h/64533d	Inode: 47055084    Links: 1
Access: (0664/-rw-rw-r--)  Uid: (  9999/xxxAdmin)   Gid: (    0/administrators)
Access: 2019-02-07 15:53:35.792991725 -0500
Modify: 2018-11-06 19:00:00.000000000 -0500
Change: 2019-02-07 15:52:55.450990790 -0500
 Birth: -


Edited by serendrewpity, 07 February 2019 - 05:03 PM.


#11 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 04:58 PM

In many environments it will be supported though.



#12 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 05:17 PM

I changed the file permission attribute on Linux/Qnap/EXT4 from 0664 to 0666 and that changed the Change attribute of the file. Birth is still null and Windows still shows the previous Date created timestamp.

 

5c5ca00253362_20190207_161116.jpg

  File: ‘/share/CACHEDEV7_DATA/Videos/Movies/Mission - Impossible - Fallout (2018) [1080p]/Mission - Impossible - Fallout (2018) [1080p].mkv’
  Size: 4692416897	Blocks: 9164896    IO Block: 4096   regular file
Device: fc15h/64533d	Inode: 47055084    Links: 1
Access: (0666/-rw-rw-rw-)  Uid: (  9999/xxxAdmin)   Gid: (    0/administrators)
Access: 2019-02-07 15:53:35.792991725 -0500
Modify: 2018-11-06 19:00:00.000000000 -0500
Change: 2019-02-07 16:09:03.355013209 -0500
 Birth: -



#13 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 05:30 PM

Same result on Ubuntu 16.4.5 as on Qnap 4.3.5 both running Linux kernel  4.2.8



#14 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 05:34 PM

What version of Linux and what command should I use to change the Birth date of files???

 

I can quickly spawn a new docker container of whatever version of linux you suggest.



#15 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 05:40 PM

Also doesn't explain why modifying the SQLite Library db's mediaitems table 'datecreated' field didn't seem to work either.

 

This route has more points of potential issues though. The 'Emby for Kodi' client is beta as well as Emby Server

 

Also the Emby client has two Recently Added views that has two distinct list of movies. One gets changed with the setting on the Emby Server and the other does not. Both say they're sorted by the date added to the database/server. Although, I suspect one is referring to the local client database.


Edited by serendrewpity, 07 February 2019 - 05:48 PM.


#16 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 07 February 2019 - 11:40 PM

Modifying the DB will work, however emby for Kodi keeps a local copy of the data and that will need to be resynced.

#17 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 08 February 2019 - 06:49 AM

I appreciate your help with all this.

 

I have done that tho.

In fact, full disclosure,

  • I've reset the local Kodi database on the client.
  • I uninstalled the Emby for Kodi client.
  • In SSH I cleaned up any remnants of Emby.
  • In Kodi I removed any video sources generated by Emby. There were several despite previous step.  
  • In Emby Server, I shut it down.
  • Then in Qnap OS I disabled Emby to makre sure it was shut down.
  • On my working laptop I closed browsers and cleared cache.
  • I made a copy of the PROD/LIVE library.db and moved it to a working directory along with a backup copy that has a Recently added view with a desired order.
  • I opened the backup database in 'DB Browser for SQLite' as 'main'.
  • I then attached the PROD/LIVE copy as 'NewDB'.
  • I then ran the SQL Query below.... [which claimed success and didn't generate any syntax, run-time errors]
Update NewDB.MediaItems 
	Set DateCreated = (SELECT DateCreated FROM main.MediaItems WHERE type = 5 AND TopParentId = 1 AND name like "Trainspotting%"),
	    DateModified = (SELECT DateModified FROM main.MediaItems WHERE type = 5 AND TopParentId = 1 AND name like "Trainspotting%"),
	    DateLastSaved = (SELECT DateLastSaved FROM main.MediaItems WHERE type = 5 AND TopParentId = 1 AND name like "Trainspotting%"),
	    DateLastRefreshed = (SELECT DateLastRefreshed FROM main.MediaItems WHERE type = 5 AND TopParentId = 1 AND name like "Trainspotting%")
	WHERE
	    name = (SELECT name FROM main.MediaItems WHERE type = 5 AND TopParentId = 1 AND name like "Trainspotting%")
  • I committed all changes and copied the PROD/LIVE copy back to the PROD/LIVE location and restarted Emby Server
  • I ran a library sync from the server. [ I think this point needs further discussion. See below...]
  • I then re-installed Emby for Kodi on the client and allowed it to complete initial sync
  • The order of the movie 'Trainspotting' didn't change on the Emby for Kodi client or in the broswer of my working laptop.

So either there is at least one flaw in the process above or its not working.

 

Note: The query affected 2 records. The record for the movie [type = 5] and one for the folder location [type = 3]. It makes sure they both have the same timestamps.

 

Point of discussion: After restoring the SQL modified database, restarting Emby Server and re-syncing it could be argued that should not be done because it will completely undo the modifications made by the SQL query. However, what about all the subsequent scheduled tasks where library syncs occur? Those will certainly undo our changes. Also, what would it undo our changes with? We've already shown that the Linux/Qnap filesystem shows a date created as null or - [dash] and all efforts to change date created fail. So what would the datecreated field be changed to in the database? Null? Even so, the Recently added view in Emby would change, wouldn't it?

 

Maybe an unfortunate conclusion of this whole discussion is that Recently added views can't be changed on Linux based instances of Emby Server.

 

Another possible conclusion is that the Recently added view can be changed but the process is broken.

 

...or....  this process is not the correct process. In which case, what is the correct process?


Edited by serendrewpity, 08 February 2019 - 07:20 AM.


#18 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 08 February 2019 - 02:04 PM

I would just update datecreated and that's it.

#19 serendrewpity OFFLINE  

serendrewpity

    Member

  • Members
  • 25 posts
  • Local time: 01:11 AM

Posted 09 February 2019 - 01:07 PM

ok, thanks. I tried that and it didn't work for me.



#20 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146738 posts
  • Local time: 01:11 AM

Posted 09 February 2019 - 03:36 PM

What didn't work?







Also tagged with one or more of these keywords: sqlite, mediaitems, recently added, datecreated, datemodified, datelastsaved, datelastrefreshed

1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users