Request -  Database access / exporting
#1
I understand from what I read and past conversations regarding the intentions of keeping tMM simple and to the point. So please hear me knowing I want to keep with that intent. I understand that the database is not a typical database in format, you cannot just open it in an SQL editor. I use both tMM and Trakt to store and organize my data. I have been using https://darekkay.com/blog/trakt-tv-backup/ as a means of backing up Trakt data and obtaining the data for further looking and corrections. Trakt is not open, and users do not really have access to their data.

Without a solid database to use, I prefer to keep a master database of sorts local, so should something happen to Trakt nothing is lost for me. Previously, I used Kodi for this. The database, although having its issues, was fully manageable and changes could be done within Kodi or within a standard SQL query tool. For a while, this was nice. I have since dropped Kodi for many reasons, none of which are relevant, and have replaced Kodi with a combination of tMM and VLC. My only real issue is a lack of a database, well, one that can be managed and queried.

So I am looking for ways I might be able to access the data, independent of tMM. I looked into the export tools, but was unable to locate anything where I can obtain a 'data dump'. Understanding there is not a typical database structure, there is still structured data. Is there some way I might be able to dump ALL data in its stored structure, where I might be able to import elsewhere to query? At this time, I am looking for export from tMM only... although an import after changes would be nice Smile I understand the issues that could pose though, and I understand the tMM database was not designed with that type of interaction in mind.
Reply
#2
well, yes, fairly good reason - but hard to archieve :|

We're using this as our database system.
It's not a conventional database, more an object storage.
Even with SQL access, you would find NO tables, and NO columns, just an BLOB array of JSON objects (with variable structure).

Dunno if there are tools to open such DB, but its quite an easy task for a developer.
In fact, there's already a hidden method, to dump a movie from the DB to logfile.
But i'm unsure, what you can do with such json dumps.... since, we always keep a fair amount of DB backups in backups folder....
tinyMediaManager - THE media manager of your choice :)
Wanna help translate TMM ?
[Image: project_thin_badge?format=gif]
Reply
#3
Thanks for the info. I had already found the link you sent to MVStore / h2database.com, based on a prior post. Only thing I am not understanding is the statement of not having structured data. It would seem to me that the data must be structured if you can query a movie and populate a screen with data, the same way each time... and modify that data. Sure you could just wipe the whole thing and create a whole new entry (vs updating a piece), but you can still find the data associated to a specific movie and place that data into named fields.

But if I am to understand what you are saying, I can work with JSON output as it is basically a database entry of its own, well, similar. When I use the export feature in tMM and use Excel or CSV, I can easily use that data, since again, CSV type like this are essentially database ready data. All I would really need is something like the Excel export template, but instead of only pulling three data pieces, pull all data pieces in the database and the name of the GUI field that data populates. With this, I can easily create a database in something more manageable (like mysql, mssql, sqlite, etc) and just to a periodic import from CSV.

The problem with the tMM database, for me, is the data is unusable except for tMM. There is no real way to view the data with ease or do anything with the data that is not done directly by tMM. One example I mentioned is comparing Trakt data to tMM data. Hope that makes sense. I really do not like to open such data files as corruption is too easy as there are no protections for anything.
Reply
#4
as you might know we do have many 1:n relations in our data model (movie:actor, movie:image, movieset:movie, ...) which lead us not to use a relational database structure (ORM from java objects to database tables is complicated/huge; database engines are not lightweight and we do not even have a benefit from such a structure).
So we decided to follow a document-orientated database model (https://en.wikipedia.org/wiki/Document-o...d_database ) with eager loading (so everything is always in memory, since the data in memory does not need many space). we store the serialized object data in a json representation where we can put deep/nested objects without a problem.
e.g. a dump of the movie bolt: https://pastebin.com/uZceWbhE

all I can imagine is to provide a way (exporter) to export the json contents, but importing is not in our focus yet..
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
[Image: 1qbFVy]
Reply
#5
(2018-07-06, 07:42)mlaggner Wrote: all I can imagine is to provide a way (exporter) to export the json contents
This would be close to exactly what I am looking for. As I mentioned before, there is already an export option for Excel, where I get a spreadsheet with three pieces of data for each movie. How can I expand that to all fields, not just three? Meaning, there are a list of fields for each movie in the GUI. How can I get an output of that data, each movie being in its own row? Same as the current Excel export option, but not just those three fields?

I have tried many ways to get data from the movies.db file, and nothing works at all. I tried SquirrelSql and h2 download for the browser based console downloaded from their web site with the most effort, and neither of them have been able to produce any sort of actual data. I understand tMM wants simplicity, which is why I am trying to just get the data out and use it elsewhere. What I am wanting to accomplish is very similar to what many others are also doing, but one difference is I am opting for tMM which is adding a complication that the others I an taking ideas from do not have. I see no issue with this, given a good option to get a data export. It is easy enough to work with it from there. Honestly, not all the pieces of data are of use, I just thought it would be easier to do a full data dump vs asking for a specific custom  list of things. I can take care of all that given a raw data dump.... if that makes sense.
Reply
#6
you can easily adopt the export templates to your needs: https://github.com/tinyMediaManager/tiny...-Templates
if you search in this forum, you may find some threads where more examples for exporting are (e.g. https://forum.kodi.tv/showthread.php?tid...ght=export )

writing your own jmte template for exporting will have the option to access _all_ fields of tmm and transforn it to your needed structure Wink
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
[Image: 1qbFVy]
Reply
#7
Ok, I did look at that a bit, but will review more. It seems there is really no other way of accessing the data than to use such methods. I was hoping for a way to pull data without the need to go through tMM, but this will be fine too. I will play around and report back, since others may have a use if I can get this working to my vision.
Reply
#8
I have looked at the custom export templates, and it turns out that it is way easier than I expected. That is my error as I let my disliking of java cloud my actions a bit. Using the packaged template for Excel, it is more than easy enough to get a data dump of relevant data. It is now quite easy to import a tMM export and a Trakt export into SQLite and query data.
Reply
#9
I use several external harddrives to store movies and series on. My collection is growing, so new haddrives keep coming. Every movie and season has it's own folder, poster art and textfile with short descriptions of plot, links to it's imdb page for further info and maybe some technical stats.

My hope is that tMM will replace those text files and artwork downloads some day. 

I was really amazed when the scraper found about 98% info and data for those files on one of these haddrives. Since every drive has a storage limit I replace it with a new and empty one until that one is full too and so on.

Not realizing, that I had a replacement in use I tried to edit some info on one of those movies already written to the database imported from an earlier harddrive the other day. Everything looked fine until I hit the button 'OK' to finish the new edit, ... that was when that particular data entry disappeard from the movie list alltogether all of a sudden. I tried to reenter it via the +button (offline entry), but that didn't work, no new enty possible apart from importing data from hd. 

Then my idea was to roll back by importing a backup version of the database. Only there is none. How is that possible? Being able to make a proper backup (for the last 3 to 5 days at least) is essential. How can that be achieved? So far that seems to be tMM's only drawback.

My guess is, editing existing database entries is only possible in conjunction with the harddrive that specific data was taken from and in return partially written on. If that's a fact, it would make it impossible to edit info on those entries not corrensponding with a new harddrive replacement thus jeopardizing the whole database in the long run. A local database could help. Something like a  'real' database (Like SQLite) could be utilized for backups as well. 
 
I'd rephrase this posting as a request, if necessary.
Reply
#10
You should find database backups in /backup/ folder.

And yes, editing metadata in TMM always tries to rewrite the changed NFO file in movie folder.
If the datasource is not available (unplugged hdd) the entry should still survive in TMM.
Please upload the logs from the day it happened to some pastebin site...
tinyMediaManager - THE media manager of your choice :)
Wanna help translate TMM ?
[Image: project_thin_badge?format=gif]
Reply
#11
Here you go!


Paste #1 is from the day the data entry 'Leaving.Las.Vegas.1995...' was made..: tmm.2018-10-01.0

Paste #2 The next day log shows no such entry is left: tmm.2018-10-02.0

The vanishing of that particular list entry 'Leaving.Las.Vegas.1995' occured after I manually edited it for additional artwork, which the scraper managed to download from imdb. tMM updated the entry, I hit the 'ÖK' button and it was gone! I'm not sure, wether one of these the logs shows that too. 

btw: The lack of hd storage space written at the end of log #1 was fixed by me durig the process of scraping. I didn't anticipate the need for additional download space before the fix. I can't figure out if that might be a reason for not downloading or storing any artwork for that movie in the first place.
Reply
 
Thread Rating:
  • 0 Vote(s) - 0 Average



Logout Mark Read Team Forum Stats Members Help
Database access / exporting00