[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)

I installed the nightly build r30898 (07-Jun-2010 10:06) for windows and I am getting an error with the MySQL stuff. I upgraded from the previous nightly build r28256. This repeats in the log file over and over and I think it will try indefinitely (as my log file kept growing and growing and is almost entirely filled with it)


error 1060 is a duplicate column error. I checked the settings table and it has a NonLinStretch column in it (defaulted to null).

Probably as you've already updated it at some other point and that failed to take?

You can probably bump the version in the db to 35 to skip over that update. Something like:

update version set iVersion=35

Cheers,
Jonathan

Hello,

I'm very interested in the MySQL database development. Where can I get the latest info on what is being done in this field? Is there a Wiki or a mailing list with the latest discussions?

Are there any plans to add fanart and thumbnails to the MySQL database using blob type data? It would be awesome if you could upload your images directly to the database and then have the option of downloading them locally if you want for faster loading. That way the images stay in sync, and it doesn't waste extra internet bandwidth.


Thanks for the great software BTW!


Edit: Never Mind, I found this:
http://forum.xbmc.org/showthread.php?tid=73831

lozbrown85 Wrote:

Code:

15:49:26 T:139638986062096 M:1513873408   ERROR: SQL: Undefined MySQL error: Code (1064)
15:49:26 T:139638986062096 M:1513873408   ERROR: CleanDatabase failed

Any ideas what's going wrong with this?

I have the exact same problem

Quote:18:24:42 T:2831772528 M:3286151168 DEBUG: Mysql Start transaction
18:24:42 T:2831772528 M:3286151168 ERROR: SQL: Undefined MySQL error: Code (1064)
Query: select idMovie from movie where idFile in ()
18:24:42 T:2831772528 M:3286319104 ERROR: CleanDatabase failed

So it looks to me as xbmc didnt read/receive the correct "idFile" as i dont think the is a empty idFile.

I have to say it worked with svn 28256 and after i switched to an svn ~31000 the problem started.

My mysql and also the clients run under Linux (ubuntu 10.04)


EDIT: i installed now an xbmc (svn 31938) under W7 and have there the same problem.

I also have the same problem. It only shows when "&unplayed=True&totals=True" is added in the home.xml file where it call up the recently added script.

EDIT: Nothing to do with this thread.

known issue, we're thinking about how to resolve it properly. absolutely 110% nothing to do with this thread though!

firnsy Wrote:Synchronise the thumbnail cache in the userdata directory.

Will the SQL server eventually be able to handle the thumbs or is this how it's gonna stay?

RckStr Wrote:Will the SQL server eventually be able to handle the thumbs or is this how it's gonna stay?

Eventually, I would like to have the option for db storage of thumbs.

Great news. Thanks for the info, and please keep us posted

On the topic of the original database abstraction proposal (standardization of names, etc.), it would be great if the database facilities for foreign keys were used. SQLite (requires enabling a pragma), MySQL, and PostgreSQL all have foreign key support. It would help ensure data integrity and make the schema more intelligible. More primary keys and "not null" columns (possibly with defaults) might be useful too (does a movie with no title make sense?), although frequently unique indexes seem to take on part of the role of primary keys.

I'll also second renaming the 'cxx' columns to something more descriptive (although fortunately SQLAlchemy lets me assign them a key that I can use to refer to them).

Columns containing XML (thumbs, fan art) should be broken out into another table, since they're really an associated list, not flat data; since it would be one to many, no link tables would be necessary.

Given a general understanding of foreign keys, I wouldn't expect I would need to explain where foreign keys would be useful, but just in case, as an example, the actorlinkmovie table's idActor should be a foreign key to actors.idActor, and idMovie a foreign key to movie.idMovie, with an 'on delete cascade' on idMovie (if the movie is deleted, so are the referring actorlinkmovie row(s)) and 'on delete restrict' on idActor (deleting a linked actor produces a constraint violation).

Unless there's a good reason why this is infeasible - or all these things are already being done quietly by firnsy or others - I will attempt to add foreign keys in my database (using SQLite), and, all going well, make a patch to create them in the table creation/update code and add it to a Trac ticket. (And perhaps implement some of the other ideas above too.)

As to why it came up: I'm writing some Python code to add movie details directly to the database, so I'm getting quite up close and personal with the tables. I'll eventually submit the code for inclusion in the tools folder, in case the modules can prove useful to others. (Naturally, I keep database backups in case I muck something up, and was initially working with a copy of the database.)