Peewee

[hobeone]

A more complete version of pull request 575. Converts most everything to go through the peewee ORM.

I've added a few tests to check some of the changed code, but there's still a lot of coverage missing. It's working for me though. There's one or two complex SQL commands that I still need to convert but everything else is converted.

A big open question is what the upgrade path for this will be. The easiest may be to have a script to dump the current db, delete it, create it from scratch and then repopulate it. The cache.db is easier in that you can just blow it away and start over.

Another question is what to do in tvcache.py. I'd like to change it to just use one table and add a column for provider name. That'll be easier to use with Peewee and should work just as well.

After everything is going through peewee it should be easy to start using mysql or postgres as the backend.

[midgetspy]

Wow great work! I have wanted to convert it to an ORM for years now but never had the time. How does Peewee handle threading? I tried STORM once and kept getting problems with threading when doing a lot of database accesses (doing stuff like adding large shows and searching at the same time).

[hobeone]

Thanks.

I think the threading issues are handled internally by Peewee: http://peewee.readthedocs.org/en/latest/peewee/cookbook.html#multi-threaded-applications

I haven't seen any issues as I've been running this but these are not the sort of things that are immediately visible.

What are your thoughts on how to handle the DB configuration and migrations?

• I think it makes sense to keep it hard coded to sqlite for the first cut and then let people switch to mysql or postgres backends by hand.
• I'm not sure how to handle the cutover from the old schema to the new one.
• It might make sense to declare that SB no longer supports old schemas and will only support upgrades from this new one.

[hobeone]

Is there anything I can do to help with the merge? Keeping it synced with the dev branch takes a lot of time.

Thanks!

[thezoggy]

so with the recent 1080p migration (part of recent push to master) we are getting reports of how slow it is for users on traditional hdd.. while people on ssd have it completed in a few seconds. looks like the main culprit is the fact each update/insert in sqlite is its own transaction and due to the way our coding is.. we cant just wrap the migration into one transaction. will your code allow this? (i havent had a chance to glance through it yet. just curious if this is a solution for a current problem or if we should waste any time in trying to solve it to get this new build pushed out)

[thezoggy]

oh i see, looks like this pull hasnt tackled the whole schema upgrade/migration stuff yet

[hobeone]

Yeah, that's been a sticking point. Peewee added some schema editing support but I think it only support postgres.

I think it would only be only changes to the schema (adding tables or columns) that would be an issue though. All of the changes in Add1080pAndRawHDQualities are just updates which would be easy to do in peewee (which would make wrapping it all in a transaction easy too). One path forward would be to go to Peewee but explicitly support only sqlite. That would at least get the DB abstraction in and allow for further cleanups.

On a side note I don't really understand why quality was implemented as a bit field rather than an enum (for episodes) and a One to many relationship (for shows). What's the thinking behind that?

On Wed, Mar 27, 2013 at 8:04 PM, thezoggy [email protected] wrote:

oh i see, looks like this pull hasnt tackled the whole schema upgrade/migration stuff yet

— Reply to this email directly or view it on GitHubhttps://github.com/midgetspy/Sick-Beard/pull/576#issuecomment-15565820 .

Daniel Hobe [email protected]

[thezoggy]

another user made a mass_action function which allowed us to do a transaction.. which significantly sped up the process of the migration. so for this current build cycle we should be good.