SQL Script to Fix Missing Relations

--tKW2IUtsqtDRztdT
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

I often find that when modifying the metadata in my library, entire
albums, genres or artists will suddenly disappear.  Upon examination of
the tracks table, I find that they still exist there, but NetJuke has
inexplicably deleted the relevant row from the albums, genres or artists
table, even though they are still in use.  This renders all tracks that
reference that particular ID "lost" (meaning they no longer show up in
any search or browse operations).

So, I wrote the attached SQL script that identifies and recreates these
missing keys, making the lost tracks reappear.  However, since the true
name of the artist, genre or album has been lost, the new entry is of
the form "LOST_ALBUM_NAME"; but then it is a simple matter to use the
web interface to correct these instances.

Use at your own risk, but I know it's saved my database numerous times
by correcting this type of inconsistency.  Perhaps something like this
can be included among the database maintenance functions.  Or better yet,
someone might correct the bug that causes this problem in the first place.

--Pete

--tKW2IUtsqtDRztdT
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="fix_missing_rows.sql"

use netjuke;

#mysql> describe netjuke_albums;
#+-----------+--------------+------+-----+---------+----------------+
#| Field     | Type         | Null | Key | Default | Extra          |
#+-----------+--------------+------+-----+---------+----------------+
#| id        | int(11)      |      | PRI | NULL    | auto_increment |
#| name      | varchar(100) |      | MUL | N/A     |                |
#| img_src   | text         | YES  |     | NULL    |                |
#| track_cnt | int(11)      |      |     | 0       |                |
#| comments  | text         | YES  |     | NULL    |                |
#| license   | text         | YES  |     | NULL    |                |
#| exclude   | char(1)      |      |     | f       |                |
#+-----------+--------------+------+-----+---------+----------------+
#mysql> select * from netjuke_albums where id=42;
#+----+--------------+---------+-----------+----------+---------+---------+
#| id | name         | img_src | track_cnt | comments | license | exclude |
#+----+--------------+---------+-----------+----------+---------+---------+
#| 42 | Counterparts |         |         7 | NULL     | NULL    | f       |
#+----+--------------+---------+-----------+----------+---------+---------+

create table netjuke_albums_v
  (id int(11),
  name varchar(100),
  img_src text,
  track_cnt int(11),
  comments text,
  license text,
  exclude char(1));

insert into netjuke_albums_v
select tr.al_id, 'LOST_ALBUM_NAME', '', count(*), NULL, NULL, 'f'
  from netjuke_tracks tr left join netjuke_albums al on tr.al_id=al.id
  where al.id is null group by tr.al_id;

insert into netjuke_albums
select * from netjuke_albums_v;

drop table netjuke_albums_v;


#mysql> describe netjuke_genres;
#+-----------+--------------+------+-----+---------+----------------+
#| Field     | Type         | Null | Key | Default | Extra          |
#+-----------+--------------+------+-----+---------+----------------+
#| id        | int(11)      |      | PRI | NULL    | auto_increment |
#| name      | varchar(100) |      | MUL | N/A     |                |
#| img_src   | text         | YES  |     | NULL    |                |
#| track_cnt | int(11)      |      |     | 0       |                |
#| parent_id | int(11)      | YES  |     | 0       |                |
#| comments  | text         | YES  |     | NULL    |                |
#| license   | text         | YES  |     | NULL    |                |
#| exclude   | char(1)      |      |     | f       |                |
#+-----------+--------------+------+-----+---------+----------------+
#mysql> select * from netjuke_genres where id=2;
#+----+------+---------+-----------+-----------+----------+---------+---------+
#| id | name | img_src | track_cnt | parent_id | comments | license | exclude |
#+----+------+---------+-----------+-----------+----------+---------+---------+
#|  2 | Rock |         |       914 |         0 | NULL     | NULL    | f       |
#+----+------+---------+-----------+-----------+----------+---------+---------+

create table netjuke_genres_v
  (id int(11),
  name varchar(100),
  img_src text,
  track_cnt int(11),
  parent_id int(11),
  comments text,
  license text,
  exclude char(1));

insert into netjuke_genres_v
select tr.ge_id, 'LOST_GENRE_NAME', '', count(*), 0, NULL, NULL, 'f'
  from netjuke_tracks tr left join netjuke_genres ge on tr.ge_id=ge.id
  where ge.id is null group by tr.ge_id;

insert into netjuke_genres
select * from netjuke_genres_v;

drop table netjuke_genres_v;


#mysql> describe netjuke_artists;
#+-----------+--------------+------+-----+---------+----------------+
#| Field     | Type         | Null | Key | Default | Extra          |
#+-----------+--------------+------+-----+---------+----------------+
#| id        | int(11)      |      | PRI | NULL    | auto_increment |
#| name      | varchar(100) |      | MUL | N/A     |                |
#| img_src   | text         | YES  |     | NULL    |                |
#| track_cnt | int(11)      |      |     | 0       |                |
#| comments  | text         | YES  |     | NULL    |                |
#| license   | text         | YES  |     | NULL    |                |
#| exclude   | char(1)      |      |     | f       |                |
#+-----------+--------------+------+-----+---------+----------------+
#mysql> select * from netjuke_artists where id=3;
#+----+------+---------+-----------+----------+---------+---------+
#| id | name | img_src | track_cnt | comments | license | exclude |
#+----+------+---------+-----------+----------+---------+---------+
#|  3 | Yes  |         |         1 | NULL     | NULL    | f       |
#+----+------+---------+-----------+----------+---------+---------+

create table netjuke_artists_v
  (id int(11),
  name varchar(100),
  img_src text,
  track_cnt int(11),
  comments text,
  license text,
  exclude char(1));

insert into netjuke_artists_v
select tr.ar_id, 'LOST_ARTIST_NAME', '', count(*), NULL, NULL, 'f'
  from netjuke_tracks tr left join netjuke_artists ar on tr.ar_id=ar.id
  where ar.id is null group by tr.ar_id;

insert into netjuke_artists
select * from netjuke_artists_v;

drop table netjuke_artists_v;


--tKW2IUtsqtDRztdT--


-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt