Closed
Bug 734095
Opened 14 years ago
Closed 14 years ago
Move SQL queries from branch model to middleware
Categories
(Socorro Graveyard :: Middleware, defect)
Socorro Graveyard
Middleware
Tracking
(Not tracked)
RESOLVED
FIXED
16
People
(Reporter: adrian, Assigned: adrian)
References
Details
(Whiteboard: [qa-])
The file webapp-php/application/models/branch.php contains a whole lot of SQL queries that need to be moved to the middleware. There may be some ways of refactoring those so the middleware services are simple and we can make good use of caching.
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 2.5.2 → 2.5.3
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 3 → 4
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 4 → 5
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 5 → 6
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 6 → 7
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 7 → 8
| Assignee | ||
Comment 1•14 years ago
|
||
That bug implies a lot of SQL queries all related to branches / products / versions. It will need a bit of work, refactoring, and maybe some changes considering new SQL functions Josh recently added. It won't be done before 10.
Target Milestone: 8 → 10
| Assignee | ||
Comment 2•14 years ago
|
||
@Josh: As I said in my previous comment, there is a lot going on in the branch model. I made a list of everything that is needed. Here are the tables, views and functions currently used:
Functions
* edit_product_info
Tables or views
* branches
* productdims
* product_info
* product_visibility
* reports
The one query that uses the reports table can be refactored to use reports_clean, it shouldn't be a problem. The thing I am wondering is, which of those tables/views/functions should I use or not? I think we are trying to get rid of the branches view, right? Same goes for productdims?
I also wonder if we can use existing middleware services. Especially, :mrmiller recently added a few functionalities to insert builds into the releases_raw table. Is that the one table that we should use as much as possible?
Here are all the SQL queries in the branch model, associated with the PHP function calling them:
* add
SELECT * FROM edit_product_info(null, ?, ?, ?, ?, ?, ?, ?)
* delete
DELETE FROM product_visibility
WHERE productdims_id = ?
and
DELETE FROM productdims
WHERE product = ?
AND version = ?
* findMissingEntries
SELECT
reports.product,
reports.version,
COUNT(reports.product) AS total
FROM
reports
LEFT OUTER JOIN
branches ON
reports.product = branches.product AND
reports.version = branches.version
WHERE
branches.branch IS NULL AND
reports.product IS NOT NULL AND
reports.version IS NOT NULL AND
reports.date_processed >= timestamp without time zone '".$start_date."' AND
reports.date_processed < timestamp without time zone '".$end_date."'
GROUP BY
reports.product, reports.version
* getBranches
SELECT DISTINCT branch
FROM branches
ORDER BY branch
* getProducts
SELECT DISTINCT product
FROM branches
ORDER BY product
* getProductVisibility
SELECT *
FROM product_visibility
WHERE productdims_id = ?
* getFeaturedVersionsExcludingVersionCount
SELECT COUNT(*) as versions_count
FROM product_info pd
WHERE pd.product_name = ?
AND pd.version_string != ?
AND pd.is_featured = true
AND pd.start_date <= ?
AND pd.end_date >= ?
* update
SELECT * FROM edit_product_info(?, ?, ?, ?, ?, ?, ?, ?, ?)
* verifyVersions
SELECT version_string as version FROM product_info
WHERE product_name = ?
AND version_string IN (...)
Comment 3•14 years ago
|
||
Adrian,
So the stuff you have above is a mixture of current and abandoned functionality. For example, anything having to do with the branches or productdims tables is strictly OldTCBS and you shouldn't be trying to port it.
Also, edit_product_info needs to go away. In the future, we'll have add_new_release for non-FTP releases, and change_featured_versions for changing the featured versions.
Comment 4•14 years ago
|
||
> Functions
>
> * edit_product_info
This should be replaced by add_new_release() and change_featured_versions().
>
> Tables or views
>
> * branches
> * productdims
> * product_visibility
These three tables are obsolete by Socorro 11, if not sooner.
> * product_info
> * reports
Also:
* products
* product_versions
> The one query that uses the reports table can be refactored to use
> reports_clean, it shouldn't be a problem. The thing I am wondering is, which
> of those tables/views/functions should I use or not? I think we are trying
> to get rid of the branches view, right? Same goes for productdims?
Correct.
> I also wonder if we can use existing middleware services. Especially,
> :mrmiller recently added a few functionalities to insert builds into the
> releases_raw table. Is that the one table that we should use as much as
> possible?
You should never query releases_raw directly. It gets digested into the products, product_versions, and product_version_builds normalized tables.
> Here are all the SQL queries in the branch model, associated with the PHP
> function calling them:
>
> * add
> SELECT * FROM edit_product_info(null, ?, ?, ?, ?, ?, ?, ?)
See above
>
> * delete
> DELETE FROM product_visibility
> WHERE productdims_id = ?
No delete functionality. Only a DBA can delete products in the future.
> * findMissingEntries
> SELECT
> reports.product,
> reports.version,
> COUNT(reports.product) AS total
> FROM
> reports
> LEFT OUTER JOIN
> branches ON
> reports.product = branches.product AND
> reports.version = branches.version
> WHERE
> branches.branch IS NULL AND
> reports.product IS NOT NULL AND
> reports.version IS NOT NULL AND
> reports.date_processed >= timestamp without time zone
> '".$start_date."' AND
> reports.date_processed < timestamp without time zone '".$end_date."'
> GROUP BY
> reports.product, reports.version
This is obsolete.
> * getBranches
> SELECT DISTINCT branch
> FROM branches
> ORDER BY branch
This is obsolete.
> * getProducts
> SELECT DISTINCT product
> FROM branches
> ORDER BY product
This should be:
SELECT product_name
FROM products
ORDER BY product_name;
> * getProductVisibility
> SELECT *
> FROM product_visibility
> WHERE productdims_id = ?
What is this used for?
> * getFeaturedVersionsExcludingVersionCount
> SELECT COUNT(*) as versions_count
> FROM product_info pd
> WHERE pd.product_name = ?
> AND pd.version_string != ?
> AND pd.is_featured = true
> AND pd.start_date <= ?
> AND pd.end_date >= ?
What is this used for?
> * update
> SELECT * FROM edit_product_info(?, ?, ?, ?, ?, ?, ?, ?, ?)
See above.
> * verifyVersions
> SELECT version_string as version FROM product_info
> WHERE product_name = ?
> AND version_string IN (...)
This is fine.
| Assignee | ||
Updated•14 years ago
|
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 10 → 11
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 11 → 12
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 12 → 13
Comment 5•14 years ago
|
||
Commits pushed to admin-refactor at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/4506c8a0ae77a19f3a7ff79f4748b546c39e5d59
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
https://github.com/mozilla/socorro/commit/a40d9c65b6bc7730c930df2649dd1806d7fe6558
Merge pull request #646 from AdrianGaudebert/734095-move-branch-model-sql-queries
Fixes bug 734095 - Cleaned up the branch model
Updated•14 years ago
|
Status: NEW → RESOLVED
Closed: 14 years ago
Resolution: --- → FIXED
Updated•14 years ago
|
Whiteboard: [qa-]
| Assignee | ||
Updated•14 years ago
|
Target Milestone: 13 → 14
Comment 6•14 years ago
|
||
Commit pushed to admin-refactor at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/897326750c9170d2ec4f7baf33072faf680a0d98
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
Comment 7•14 years ago
|
||
Commits pushed to admin-refactor at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/4506c8a0ae77a19f3a7ff79f4748b546c39e5d59
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
https://github.com/mozilla/socorro/commit/a40d9c65b6bc7730c930df2649dd1806d7fe6558
Merge pull request #646 from AdrianGaudebert/734095-move-branch-model-sql-queries
Comment 8•14 years ago
|
||
Commit pushed to admin-refactor at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/dac5f156ed67347d16e2dfe89e704fcc5256da33
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
Comment 9•14 years ago
|
||
Commit pushed to admin-refactor at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/1a8b59f4d6f37b2510f9676fb81b4e6f7e1b56c1
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
Comment 10•14 years ago
|
||
Didn't get to review this today. Also, github robot has apparently gone weird on us here and resolved->fixed this because the branch is on mozilla/socorro. Setting this to reopened and kicking out.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Target Milestone: 14 → 15
Comment 12•14 years ago
|
||
Commit pushed to master at https://github.com/mozilla/socorro
https://github.com/mozilla/socorro/commit/1a8b59f4d6f37b2510f9676fb81b4e6f7e1b56c1
Fixes bug 734095 - Cleaned up the branch model, removed remaining, obsolete SQL calls or replaced them with middleware calls.
Updated•14 years ago
|
Status: REOPENED → RESOLVED
Closed: 14 years ago → 14 years ago
Resolution: --- → FIXED
Updated•10 years ago
|
Product: Socorro → Socorro Graveyard
You need to log in
before you can comment on or make changes to this bug.
Description
•