Made downloads and subscriptions unique by programme or episode id.

[ribbons]

Original report from Matt Robinson at 12:35:48 on 2010-05-29

Currently, the database will quite happily allow multiple entries for a single episode in the downloads table, and for a single programme in the subscriptions table. This is incorrect, as if this does happen, the application will crash because it assumes there will not be multiple entries for a single programme or episode.

To resolve this issue, a unique constraint should be added to the progid column in the subscriptions table, and to the epid column in the downloads table.

---

Imported from Bug 270 in the NerdoftheHerd.com Bugzilla.

[ribbons]

Original comment from Matt Robinson at 16:17:51 on 2010-05-29

Updated subscriptions table from:

CREATE TABLE [subscriptions]([progid] integer)

to:

CREATE TABLE [subscriptions]([progid] integer UNIQUE NOT NULL)

in revisions 861 & 862.

[ribbons]

Original comment from Matt Robinson at 16:31:17 on 2010-05-29

Updated downloads table from:

CREATE TABLE [downloads]([epid] integer, [status] integer NOT NULL DEFAULT 0, [filepath] varchar %28255%29, [errorcount] integer NOT NULL DEFAULT 0, [totalerrors] integer NOT NULL DEFAULT 0, [errortime] datetime, [errordetails] memo, [playcount] integer NOT NULL DEFAULT 0, [errortype] integer)

to:

CREATE TABLE [downloads]([epid] integer UNIQUE NOT NULL, [status] integer NOT NULL DEFAULT 0, [filepath] varchar %28255%29, [errorcount] integer NOT NULL DEFAULT 0, [totalerrors] integer NOT NULL DEFAULT 0, [errortime] datetime, [errordetails] memo, [playcount] integer NOT NULL DEFAULT 0, [errortype] integer)

in 7289a60.

[ribbons]

Original comment from Matt Robinson at 17:10:32 on 2010-05-29

Added locks and extra checks to the AddDownloadAsync and AddSubscriptionAsync subs in 3db071d to prevent the application from trying to insert duplicate downloads or subscriptions because of race conditions.