In the database they are stored as actual timestamps, the "high value" you see is epoch seconds (http://en.wikipedia.org/wiki/Unix_time)
The reason why originallyAvailableAt is done in actual YYYY-MM-DD is because epoch seconds don't go back as far as some old TV shows do :)
Hm, to me that looks like garbage actually. When I browse through my data fields they all follow the same YYYY-DD-MM pattern. Does it occur only once or do you have a lot of these strange date entries?
Smil
How strange, mine are all YYYY-MM-DD
In the database, they are all YYYY-MM-DD, but in the XML you'd see epoch seconds :)
I added the image stuff in the first post. Probably not suitable for the everyday user but maybe helpful for someone someday. 
Cheers
Smil
Hello,
Okay, cristal clear now! effectively I have made a few checks and that's right (I'm not pretending here that I was trying to check if you were right :), this was for my personal interest). Something new learned today on my side.
I don't understand why LITA is not translating this format to his display date, maybe LITA has not this capability? anyway, this is far too much work to pretend manually modifying 'Added date' to match the movie file creation date for all my movies (this was my initial goal).
Thank you for your answers on my question, was instructive :)
Cheers
friisco
Oh, what a fantastic write up, thanks SolarPlex!!!
I have all my movies split into two Categories, Adults & Childrens. I know there are genreās but Iāve always found them unreliable, so I prefer to tag each movie I add to the system into one of these two categories.
Unfortunately at some point I had added a couple of movies without tagging them and thereās no easy way of finding a movie at the top level that is not assigned to one of the secondary āAdultsā or āChildrensā category from within the Media Manager.
With 3 simple steps from your concise instructions, I am now able to easily find any movies that are missing a category settings:
<br />
cd ~/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases<br />
sqlite3 com.plexapp.plugins.library.db<br />
SELECT title FROM metadata_items WHERE library_section_id=9 AND tags_collection = "";<br />
Awesome :)
If I understand you correctly you would like to replace the value of "added_at" in the table "metadata_items" with the respective value of "updated_at" (=last file modification date) from the table "media_items".
Have a look there to make sure that it contains the dates you expect.
I don't think it's a good idea because you would overwrite a value that is reserved for a different purpose. This SQLite command, however, would do exactly that. Just a proof of concept, I do not recommend do this,
at least not without backing up the database file.
update metadata_items set added_at=(select media_items.updated_at from media_items where media_items.metadata_item_id=metadata_items.id);
Nice to know accounts support is not far away.
Hi everybody,
I fixed up some stuff in the first post. Yesterday I had some time to write a small script that will export all movies to an XML file (following the quasi standard of XBMC XML/NFO. I will post it if anyone is interested. If there is anyone who could write a nice XSL definition to produce some good looking and printable HTML from an XML file that would be very helpful.
Cheers
Smil
Yeah, in fact, I had almost the same problem originally and that was my motivation to find out about the SQLite stuff. I'm glad it helped. :)
Cheers
Smil
I will try this on my test Plex DB this week end. Need time to prepare myself and the data :) . Nice clean SQL code
Thank you for the help
Here is a new issue of Things you had been told not to do but could not resist trying out anyway for the automatomaniacs among you.
Today I bowed over the question what I could do with my movie ratings. They are all from TMDB and they tend to be somewhat extreme. Actually, I rather trust the ratings found on IMDB. So I wondered whether I could replace all TMDB ratings by IMDB ratings automatically while keeping everything else unchanged. It was easier than I had thought, a few lines of BASH script did the trick:
<br />
#! /bin/bash<br />
<br />
# Database path<br />
DATABASE=$HOME/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/\<br />
Databases/com.plexapp.plugins.library.db<br />
<br />
# Library Section ID to process<br />
LSID=1<br />
<br />
# Make a list (array) with all database items that have an IMDB-based guid<br />
ID_LIST=($(sqlite3 "$DATABASE" "SELECT id FROM metadata_items WHERE guid LIKE '%imdb://tt%' AND library_section_id IN ( $LSID )"))<br />
<br />
# Just a counter<br />
I=1<br />
<br />
clear<br />
<br />
# Loop through all elements of the list<br />
for ID in ${ID_LIST[@]} ; do<br />
# Extract the relevant part from the guid that contains the "tt12345667"<br />
IMDB_ID=$(echo "SELECT substr(replace(guid,'com.plexapp.agents.imdb://',''),1,9) FROM metadata_items WHERE id=$ID ;" | sqlite3 "$DATABASE") <br />
<br />
# Retrieve info from imdbapi.com using curl and then extract only the rating using sed's picket fences<br />
IMDB_RATING=$(curl -s -f http://www.imdbapi.com/?i=$IMDB_ID | sed -e 's/^.*Rating":"//' -e 's/\(.\{3\}\).*/\1/') <br />
<br />
# If a rating could be retrieved write it to the database<br />
if [ "$IMDB_RATING" != "" ] ; then<br />
<br />
# This is the dangerous stuff<br />
echo "UPDATE metadata_items SET rating=\"$IMDB_RATING\" WHERE id=$ID ;" | sqlite3 "$DATABASE" <br />
<br />
# Let the user know that we are actually doing something<br />
tput cup 0 0<br />
echo "$I of ${#ID_LIST[@]} processed, updated rating of movie $ID to $IMDB_RATING "<br />
fi<br />
<br />
# increase counter, duh...<br />
I=$(($I+1))<br />
done<br />
<br />
<br />
What I did here was first ask the database how many of my movies had a guid value based on an IMDB id (the tt12345677 stuff). Most of them. Fine. That meant that I could easily extract the IMDB id from the guid value. So I looped through all matching movies, extracted their IMDB id, then requested the IMDB rating for that id from the website www.imdbapi.com and finally updated the values of "rating" in metadata_items accordingly. Not complicated actually, the script went through all ~ 1000 movies without errors and within about 20 minutes all TMDB ratings were replaced by IMDB ratings.
Same warning as always: Make a backup before you try out stuff like that.
Cheers
Smil
In fact, to clarify the reason of my (tentative of) modifications; I want to have the movies sorted by file creation date when selecting the list 'Recently added'. Because I'm quite new to Plex, the displayed order of files doesn't reflect the real addition date. Hoping I'm clear :)
You said that the field "added_at" is reserved for a different purpose, which one? it's not meant for sorting?
Cheers
Hi,
try this one ( I revoke the earlier command, it was wrong):
<br />
<br />
update metadata_items set added_at=(select media_items.updated_at from media_items where media_items.metadata_item_id=metadata_items.id);<br />
<br />
The effect of it is that PLEX will sort your movies by the time they were last modified (= usually the file creation date), i.e. those movies that are the oldest on your hard disc will appear to PLEX as those that were added to the library first.
Cheers
Smil
Post looks great! Just ran into an issue running the first SELECT command (SELECT * FROM metadata_items 
I just get:
SQL Error: No Such Table: metadata_items
I checked the file (~/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.library.db) and it shows 0kb
Am I looking at the wrong file?
Thanks!
First let me say this thread is awesome. I had very brief training doing sql queries at my last job. but never had much reason to use it in my day to day this gives me an excuse to play around and learn a few things so thanks for starting it.
Yes you are looking at the wrong file. first post he typed in the wrong database file which i copied and pasted and got the same error you did. the correct file is (notice the "plugins" in the middle)
com.plexapp.plugins.library.db
Thanks, I fixed that in the first post.
BTW, there are a lot of awesome things you can do with some simple scripting such as
- merging genres (like 'Action & Adventure', 'Action and Adventure', 'Action/Adventure')
- batch edit missing tags manually or automatically from IMDB
- fixing double tags caused by spaces or accented letters ('Pierce Brosnan', ' Pierce Brosnan')
- manually add actors
- full text search
- add keywords, quotes, trivia, alternative titles, cross references etc. to the database
- export to html, xml, csv, nfo
- database statistics
- fix empty or non-linked tag entries
I tried to put all these things together in one easy to use program which is okay for me personally but not safe to use for everyone yet. I'm not a programmer but in principle all of the above functions are easy to realize so I wonder why there is no such thing as a PLEX database tool for Mac yet.
Cheers
smil
A big thank you for writing this up. Itās very useful as well as informative to have such well written instructions and explanations like this. I donāt know much about working in the Terminal and really would like to learn more, but it seems that every time I see instruction posted in a forum on how to do something they assume the reader knows a thing or two and I get lost quickly. Thank you for this great information. Very helpful for quickly listing my movies. Now I just need to compare the list I get from the SQlite database to the list of actual movie files on my server to see which ones Plex isnāt recognizing.
I agree that it would be a really great feature if Plex had a way to do this from within the media manager or similar.
Most fantastic post. Thanks for sharing, saved me hours of work.
