Closed
Bug 481303
Opened 15 years ago
Closed 15 years ago
Design new schema for database table for storing l10n dashboard data
Categories
(support.mozilla.org :: Localization, task, P1)
support.mozilla.org
Localization
Tracking
(Not tracked)
RESOLVED
FIXED
0.9.5
People
(Reporter: ecooper, Assigned: ecooper)
References
()
Details
(Whiteboard: tiki_feature, tiki_upstreamed)
Attachments
(1 file, 1 obsolete file)
We'll be adding a table to the current SUMO database instead of relying on the tracker functionality built into tikiwiki. What will need to be stored in the table can be found under the second item of this list https://wiki.mozilla.org/Support/l10nPriorityPRD#Implementation I'll have a suggested schema attached shortly.
Assignee | ||
Comment 1•15 years ago
|
||
David I've been playing around with this as I've been developing the library for bug 418305. Are we really going to see a difference between 'score' and 'priority'? Would it be easier to just have a general field/column called 'weight'? Then we could have a simple, small three-column table: page, type (think 'list', like kb, navigation, etc), weight.
Comment 2•15 years ago
|
||
The score can theoretically be the same on more than one article. In our case, we'll use the relative page hits % as the score, so something like 0.0145 might be a score for any given article (that is, 1.45% of the total page hits of the articles in that list). What if both article #7 and #8 in that list has the same score of 0.0145 but we still want one article to be sorted before the other? Or what if we want to have the list prioritized on popularity (as a combination of page hits, poll votes, and searches), but we still want to keep our 50% page hits baseline? In that case, the order of articles in the list will be relatively independent of the score. I'd say we keep the priority field, even though it will essentially just be an int starting from 1 to number_of_items in the list. About the type field, do you mean that we would use the same table for all lists? That sounds doable, but then I'd create another table schema for the list types and point to its primary key in the other (big) table. Does that make sense?
Updated•15 years ago
|
Priority: -- → P1
Updated•15 years ago
|
Severity: enhancement → normal
OS: Linux → All
Hardware: x86 → All
Comment 3•15 years ago
|
||
To clarify, the tables would look something like this (# = primary key): TABLE: articletype # articletype_id articletype_title: (string) articletype_description: (string) TABLE: articlelist # articletype_id (reference to articletype table) # articlelist_priority: (int) articlelist_score: float articlelist_article Note: my db skills are limited, so I might be missing something obvious, or overdesigning this. Use your best judgment. :)
Comment 4•15 years ago
|
||
DOH! s/articletype/listtype/
Assignee | ||
Comment 5•15 years ago
|
||
I thought about this for a while, and at first I thought it might be a bit overkill...I mean, if we expand it too far, it ends up just being pseudo categories. After playing with this schema and modeling the library around it, I'm with David's proposal with one minor change--using list_type_id and page_name as primary keys instead of list_type_id and priority. Using page_name over priority simplifies updating lists. I'm not too worried about duplicate priorities because they will be calculated by the library before insert/update.
Attachment #366204 -
Flags: review?(djst)
Assignee | ||
Comment 6•15 years ago
|
||
Comment on attachment 366204 [details]
Schema of new article list tables
Ugh. Wrong table. Ignore this.
Attachment #366204 -
Flags: review?(djst)
Assignee | ||
Comment 7•15 years ago
|
||
Apparently, I mislabeled the SQL patches locally. This should be the one mentioned comment 5. Every time I work over a Sunday, I do something like this. :(
Attachment #366204 -
Attachment is obsolete: true
Attachment #366205 -
Flags: review?(djst)
Comment 8•15 years ago
|
||
Comment on attachment 366205 [details]
Schema for new tables (v2)
Looks good to me. page_name will be unique for every list anyway, so works as primary key together with list_type_id.
Attachment #366205 -
Flags: review?(djst) → review+
Assignee | ||
Comment 9•15 years ago
|
||
I just had a major duh moment. We need to get these tables on staging before we test. What's the preferred method here? Can we just run with the raw SQL? Or do I need to make an update script?
Comment 10•15 years ago
|
||
Forgive me Laura for possibly saying the wrong things here, but I think it's fine to run the SQL statements as long as you document them so we can re-run the same commands on prod when we push.
Assignee | ||
Updated•15 years ago
|
Attachment #366205 -
Flags: review?(laura)
Comment 11•15 years ago
|
||
Comment on attachment 366205 [details]
Schema for new tables (v2)
Needs foreign keys list_type_id and likely page_name, other than that ok.
Attachment #366205 -
Flags: review?(laura) → review+
Assignee | ||
Comment 12•15 years ago
|
||
Tiki doesn't use foreign keys as far as I could see, so I didn't either to be consistent.
Assignee | ||
Comment 13•15 years ago
|
||
The tables are up in staging: https://bugzilla.mozilla.org/show_bug.cgi?id=483966 We're currently working on getting the cron script going as well.
Assignee | ||
Comment 14•15 years ago
|
||
I'm going to mark this as resolved for the time being. If we run into related problems, we can reopen.
Status: NEW → RESOLVED
Closed: 15 years ago
Resolution: --- → FIXED
Comment 15•15 years ago
|
||
LP: Could you please verify that the db schema for the l10n dashboard was also upstreamed?
Whiteboard: tiki_feature
Comment 16•15 years ago
|
||
Yes, they were added. 20091021_moz_pagelist_tiki.sql
Updated•15 years ago
|
Whiteboard: tiki_feature → tiki_feature, tiki_upstreamed
You need to log in
before you can comment on or make changes to this bug.
Description
•