456126 - Set up database for collections

Status: VERIFIED FIXED

(addons.mozilla.org Graveyard :: Collections, defect, P1)

Description

[Justin Scott [:fligtar]]

From the specs:
*  The database tables and schema for supporting user-created collections needs to be created for use with the web service and website.

We need to plan and implement the schema, figuring out what tables and columns are going to be needed.

Comment 1

[Michael Morgan [:morgamic]]

Ryan - let's work on this together this week.

Comment 2

[Ryan Doherty (:rdoherty)]

From the mockups/wireframes it looks like we need:

* Bandwagon categories table
  * id
  * description
* Addon <-> Bandwagon categories table
  * addon_id
  * bandwagon_category_id

Anything else?

Comment 3

[Ryan Doherty (:rdoherty)]

* Date added to addon <-> categories table

Comment 4

[Michael Morgan [:morgamic]]

Some of the other tables have created and modified, not sure if that is needed here -- Justin?

Comment 5

[Justin Scott [:fligtar]]

I think we'd only need the creation date for Addon <-> Bandwagon categories table.

We do need a tables for the lists themselves, though:

* Bandwagon lists table
** id
** name (l10n)
** description (l10n)
** access (open or closed)
** listed (boolean - whether listed in directory)
** password
** subscribers (int)
** created
** modified

* Addon <-> Bandwagon lists table
** addon_id
** list_id
** created

* Bandwagon lists <-> Users
** list_id
** user_id
** role

* Bandwagon lists <-> tags/categories
Lists can be in categories. We need to decide whether to make a special list categories table or make List an addontype_id and just use the normal categories table (would be easier).

Comment 6

[Ryan Doherty (:rdoherty)]

Thanks Justin for the detailed list. 

Are there any possible drawbacks to making the lists an addontype_id? I'm not familiar enough with how that table is used to know if it will cause any problems.

Comment 7

[Ryan Doherty (:rdoherty)]

Attachment: Patch for database structure

Here's my first run at the table structures.

Comment 8

[Michael Morgan [:morgamic]]

Comment on attachment 340361 [details] [diff] [review]
Patch for database structure

Looks okay to me, unless you want collection name to be unique, in which case you'd want to add it to the collections table primary key.

You may also want an index on `listed` if it's going to be in retrieval queries but we can always optimize after we have our queries formed in the controllers.

Comment 9

[Ryan Doherty (:rdoherty)]

Attachment: SQL with index and proper foreign key constraints

Added the index, forgot to put in foreign key constraints.

Comment 10

[Michael Morgan [:morgamic]]

Comment on attachment 341336 [details] [diff] [review]
SQL with index and proper foreign key constraints

wfm

Comment 11

[Ryan Doherty (:rdoherty)]

r18827

Comment 12

[Wil Clouser [:clouserw]]

I added "drop table if exists" statements to your SQL in r18999.  If there was a reason they were omitted let me know and I can back it out.

Comment 13

[Stephen Donner [:stephend] Not actively reading bugmail]

Collections are in and seem to be working fine; verified FIXED.