Xyon (xyon) wrote,
Xyon
xyon

  • Mood:
  • Music:

IMDb importing

So I finally finished importing the IMDb database into a reasonable schema. I let the JMDB project (no link, not worth it) do the importing for me since they obviously know some trick to dealing with MySQL over JDBC that I don't (they got it all to import in ~30-60 minutes; I couldn't import actors in 3 hours). Then I spent a long time getting it into a good schema.


And now a part of the database that has nothing to do with the IMDb contents:

mysql> SELECT count(*), p.pref_code_id, p.pref_code_short_desc, p.pref_code_desc FROM personal_movies pm INNER JOIN pref_code p on pm.pref_code_id = p.pref_code_id GROUP BY 2, 3, 4;
+----------+--------------+----------------------+--------------------------------------------------------------------------------------+
| count(*) | pref_code_id | pref_code_short_desc | pref_code_desc                                                                       |
+----------+--------------+----------------------+--------------------------------------------------------------------------------------+
|       13 |            1 | Quite Excellent      | This movie will be purchased, and likely watched often.                              |
|       23 |            2 | Pretty Good          | This movie will be purchased, and recommended to others                              |
|       16 |            3 | Good                 | This movie will likely be purchased, though it may not be viewed often               |
|        3 |            4 | Alright              | This movie does not warrant purchase, but was good enough to have seen once          |
|        1 |            5 | Bad                  | This movie barely qualified as entertainment                                         |
|        1 |            6 | Horrible             | This movie never should have been made, it will be disrecommended.                   |
|        1 |            7 | Unwatched            | This movie has been purchased, but never watched.                                    |
|        1 |            8 | Unpurchase           | This movie has been purchased, and if anyone wanted mine they could have it for free |
+----------+--------------+----------------------+--------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> select m.title, p.pref_code_short_desc from personal_movies pm, movies m, pref_code p where pm.movie_id = m.movie_id and p.pref_code_id = pm.pref_code_id and p.pref_code_id > 4;
+---------------------------+----------------------+
| title                     | pref_code_short_desc |
+---------------------------+----------------------+
| Ocean's Twelve (2004)     | Bad                  |
| American Wedding (2003)   | Horrible             |
| Animatrix, The (2003) (V) | Unwatched            |
| Two Weeks Notice (2002)   | Unpurchase           |
+---------------------------+----------------------+


Note: I did not intend to purchase Two Weeks Notice (nor have I watched it -- no interest), it became mine through Columbia House, USPS forwarding delays, and my Expedia internship. (It took so long to get to me out there that it was past the return point)
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 2 comments