541215 - alexa_topsites table is not created when setupDatabase.py is run

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[Tom Cramer]

User-Agent:       Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_2; en-us) AppleWebKit/531.21.8 (KHTML, like Gecko) Version/4.0.4 Safari/531.21.10
Build Identifier: Trunk

I downloaded the Trunk release on Jan 18th, and followed the instructions at http://code.google.com/p/socorro/wiki/SocorroInstallation to do a clean install on a new machine.  In doing so, it appears the alexa_topsites table is not created when the setupDatabase.py file is run, nor do the install instructions contain information on updating the table with valid data.  Checking the sql/socorro.sql file reveals no entry for creating the table.

Reproducible: Always

Steps to Reproduce:
1. Download the trunk version of socorro
2. Follow the setup instructions on the wiki (for this bug, you only need to finish the database setup)

Actual Results:  
alexa_topsites table is not created in the database, leading to a warning page when trying to load the socorro web site.  The error is:

pg_query() [function.pg-query]: Query failed: ERROR:  relation "alexa_topsites" does not exist
LINE 12:    LEFT JOIN alexa_topsites at ON ud.domain LIKE '%' || at.d...

Expected Results:  
The alexa_topsites table should have been created by the database setup, and the site should have loaded normally.

Information on the table and how to populate it are in the Socorro 1.2 update notes.  This information should likely also be in the install guide during the database setup.

http://code.google.com/p/socorro/wiki/SocorroUpgrade#Alexa_Topsites

Comment 1

[Frank Griswold [:griswolf] [:fgriswold]]

comment: sql/socorro.sql is incorrect and out of date. If anyone has a reason to keep it around, please let us know.
 
The script setupDatabase.py looks at socorro/database/schema.py

schema.py is also out of date w.r.t. alexa_topsites. I'll fix that in a few minutes. However filling it is another matter. I think it may be a push process from Socorro's point of view? I recall some discussion, but the best info I can find is bug #508592

Comment 2

[Ryan Snyder [:ryansnyder] [:rsnyder] [:rysny]]

The instructions for upgrading from Socorro 1.1 to Socorro 1.2 include the instructions for creating and populating the alexa_topsites table:
http://code.google.com/p/socorro/wiki/SocorroUpgrade#Alexa_Topsites

Specifically, there is a cron script that is used to pull down the .csv and populate the alexa_topsites table with the top 1,000 sites.

Comment 3

[Ryan Snyder [:ryansnyder] [:rsnyder] [:rysny]]

I've updated the Socorro Installation document by adding a link to the alexa topsites installation in the Soccoro Upgrade document.

http://code.google.com/p/socorro/wiki/SocorroUIInstallation

Comment 4

[Frank Griswold [:griswolf] [:fgriswold]]

raw_adu and alexa_topsites tables are now available in socorro/database/schema.py as of rev 1712 . setupDatabase.py now creates those tables.

I believe, based on Ryan's comments and the schema.py changes, that this bug is now fixed, and am closing it.