+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Discussions (https://forum.kodi.tv/forumdisplay.php?fid=222)
+--- Forum: Feature Requests (https://forum.kodi.tv/forumdisplay.php?fid=9)
+--- Thread: Req XBMC Database: Make "genre" a foreign key to STRING in genreTable (/showthread.php?tid=149937)
Pages:
1
2
XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2012-12-29
Today I decided to retouche my TV shows library the efficient way: directly altering the database using an SQL editor. To my astonishment, I discovered that it is not possible to modify the string for a genreID, then have this modification propagated onto the tvshows table. This is by design so: cell c08 in tvshows table is a string value, not an integer (foreign key to genre table), therefor the only way to modify the string is to do it on a row by row basis.
I think this is a slip on the part of the database designer. Genres being pretty far the only tag in an nfo file that is 100%-ly customizable, it should come as no surprise that users shall play endlessly with the names of their custom strings, and in consequence need a way to propagate any modifications across the whole database with guaranteed consistency.
I am not sure this is the only place in the database where it says string whereas it should say integer (foreign key). But if there is a place, surely this is it.
Thanks.
RE: XBMC Database: Link "Genre" Cell to STRING in genreTable - Montellese - 2012-12-29
It's not a slip on the database design it's actually by design. Genres are a many-to-many relationship and if you would only rely on a link table to get the genres for a specific video it wouldn't be possible to have the genre information in the views we create. Therefore on adding/updating a video in the library we also store the concatenated string of genres (same for directors, writers etc) in the movie/tvshow/musicvideo/... table to have it directly accessible. This allows a single query on a view to get all videos with the genre information. The alternative would be one query to get all videos without the genre (and director and writer and ...) information and then one additional query for every retrieved video to get the genre information (and one additional query to get the director information and so on) for every item.
So let's say you have 500 movies. With the current implementation you need 1 sql query. With the alternative implementation you have 1 + 500 * X queries where X is 1 if you only get the genre info, 2 if you get genres and directors etc. You can easily see that the number of sql queries would increase with every additional video and every additional many-to-many relationship. These additional sql queries slow the whole retrieval process down significantly (you can try it with JSON-RPC where you can retrieve e.g. tags for all movies which will slow the response down from 100+ms to 5+s) which is unacceptable for GUI browsing.
The problem is more that our database schema is not made for manual "manipulation" unless you execute the same logic as our code does.
I agree that it complicates the database logic a lot and is not very "clean" but the performance impact of a "clean" solution would be unacceptable.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2012-12-29
I see the logic. Performance considerations must indeed be paramount.
Next question. Do you know of an SQL editor that allows for full text search/replace operations across all tables? This would solve my problem with consistent editing of the db, and keep the performance-optimized design intact.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - da-anda - 2012-12-30
Montellese, the overall number of queries wouldn't increase that much - all to be done is to use a local cache and check if this information was already fetched. So for genres, we could simply query all genres in one go, cache them, and then assign them f.e. by using a CSV list of genre IDs (instead of CSV gerne names/strings). Same for directors etc. The list of IDs could even be dynamically created with a subselect. Performance will ofc not be as good as right now, but I'd also prefer a normalized database (at least only cache CSV IDs instead of strings - that way genres would be translateable)
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - Montellese - 2012-12-30
Yeah because we already have that caching engine... uhm wait, no we don't but I'm sure it's very easy to implement ... uhm wait, it's probably not.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - da-anda - 2012-12-30
why a caching engine - it's just a temporary vector with uid => dbrow values.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - Montellese - 2012-12-30
Yeah but you want to do it in a generic way (i.e. not specific to genre or director or whatever) and then you need to be able to update that vector or reset it etc depending on the action on the database. And you have to actually copy the values from the db result because those are only valid as long as a db connection is open and we open and close the connection all the time. So it's (as always) not as easy as it may sound.
Plus in the end you still run into the problem that the first retrieval will take much longer (because skins rely on genres etc to be available with the list of video items) which is still unacceptable if the first retrieval is e.g. listing my 1000+ movies (which is probably very common). At least I don't want to wait several seconds for the movie list to load the first time (even if it's faster afterwards).
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - jmarshall - 2012-12-31
IMO the best way to do efficient caching is to set it up in a completely separate table.
i.e. we'd have a table of finalized content that can be directly queried. Then we'd have everything else fully normalized. Updating the database would then update the normalized data and then re-generate the finalized content (i.e. the slow queries are done at the end of an update transaction). The key would be ensuring the finalized content is updated somewhat optimally for the "fast" updates that occur (playcounts etc.)
The trick is balancing tailoring for the type of data we store against genericity of the implementation so that we don't need too much special-casing. If I understand correctly, this is essentially what garbear has done.
Cheers,
Jonathan
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2012-12-31
@jmarshall wrote:
Quote:i.e. we'd have a table of finalized content that can be directly queried. Then we'd have everything else fully normalized. Updating the database would then update the normalized data and then re-generate the finalized content...
On the face of it, what jmarshall proposes is not only a very elegant solution, but, if you want to look at it philosophically, the admonition to go back to the basic commandments of Dr. Codd: Express every logical structure in tables. Every logical relationship between them in foreign keys. Worry not about speed -- if you keep each table down to two columns, speed will result naturally.
With hindsight, I chide myself for giving way so easily to Montellese's thesis that a clean (meaning normalized) database design isn't speed optimal. I should have asked myself the following question: "How is it possible that a desktop system (XBMC), using the most speed efficient SQL engine available (SQLite3), has trouble keeping tabs on genres, the single (actually no longer true: Frodo has added tags) customizable categorization property allowed? For god's sake, what is so monumental about using genres to categorize movies and tvshows?"
I do not want to, nor do I have for that matter the requisite technical knowledge, push this point further. Yet after this post by jmarshall, a suspicion keeps creeping up my mind: Many database-related user requests, which on the face of it seem reasonable yet get routinely rejected by developers (take the persistent request for sub-genres, persistently rejected), are the telltale symptoms of bad database design. Not technological insufficiency (the SQL engine can do the job) --- just bad design.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - jmarshall - 2012-12-31
It's a very well known problem with normalized databases. If you have just 2 tables with a many:many relation between the two and wish to retrieve all items from table 1 with all items that match in the second table for all those items, it requires a separate query per item.
Nothing you can do will sped that up other than denormalising, and the more things like it, the more queries are required.
A completely normalized database works well only if you rarely wish to retrieve all the linked data at once. This is something we want almost all the time, so is not appropriate in this case.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2012-12-31
But you agree that it is a bad design to not have genres, the only user-customizable category, normalized?
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - jmarshall - 2012-12-31
Since when was genres more user configurable than anything else?
The issue is that the denormalized table is not generated from the normalized one.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2013-01-03
jmarshall wrote:
Genres are more customizable than any other categorization tag because, according to the schema of movie.nfo and tvshow.nfo, it is only genres that can be edited by users. The next as customizable categorization tag was sets, whose usability was however limited by design. Frodo brought tags, which are as customizable, and as usable, as genres. So I suppose I should have said "genres and tags". But I was referring to my actual experience with SQL-editing the database, and so far I only touched genres.
If you say so I accept it, you are the database guru.
But I understand that even though you think this is the correct design choice, yet you are not fully happy with the loss of normalization, and you are consequently looking for ways to make the database behave as though it was fully normalized. I would say that you do not worry about a denormalized design until you try to SQL-edit the database. Perhaps the solution is an editor which makes the edits as if the database was normalized, that is an editor which does not edit the tables as they exist, but as they should logically correlate before the sacrifices to database speed were made.
Quote:Since when was genres more user configurable than anything else?
Genres are more customizable than any other categorization tag because, according to the schema of movie.nfo and tvshow.nfo, it is only genres that can be edited by users. The next as customizable categorization tag was sets, whose usability was however limited by design. Frodo brought tags, which are as customizable, and as usable, as genres. So I suppose I should have said "genres and tags". But I was referring to my actual experience with SQL-editing the database, and so far I only touched genres.
Quote:The issue is that the denormalized table is not generated from the normalized one.
If you say so I accept it, you are the database guru.
But I understand that even though you think this is the correct design choice, yet you are not fully happy with the loss of normalization, and you are consequently looking for ways to make the database behave as though it was fully normalized. I would say that you do not worry about a denormalized design until you try to SQL-edit the database. Perhaps the solution is an editor which makes the edits as if the database was normalized, that is an editor which does not edit the tables as they exist, but as they should logically correlate before the sacrifices to database speed were made.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - Montellese - 2013-01-03
You can change almost any database field using the proper XML tag in an NFO file, this is by far not limited to genres, sets or tags. Tags is the only property that can be changed through xbmc's GUI.
RE: XBMC Database: Make "genre" a foreign key to STRING in genreTable - DiMag - 2013-01-03
Quote:@ Montellese:
Quote:You can change almost any database field using the proper XML tag in an NFO file, this is by far not limited to genres, sets ofr tags.
What I wrote is that, before tags, genres used to be the only user customizable tag which was usable as a general catalogization property. It is true that you can change almost every database field using the proper XML tag in an NFO file, but can you then use it as a filter for viewing and selecting files? Take directors. You can change the content, but the content of <directors> is not a catalogization property in itself: if you want, say, to filter your movies using "Japanese Masters", the proper tag to use ist <genre> or <tag>, not <director>. There is no director called "Japanese Masters", and if you select "Japanese Masters" you won't find an entry named "Akira Kurosawa", you will find nothing.
If you imply that you can create any tag which can be useful as a general catelogization property, then use it as a filter in an XSP, then this is new to me, and indeed very interesting.
Quote:Tags is the only property that can be changed through xbmc's GUI.
Not the only one: so are titles. Genres should be in this league too, BTW [FEATURE REQUEST].