MYSQLでプレイリストを自作する

曲名をデータベース登録しておくことで、最後に聞いた曲の次の曲から再生を開始するプレイリストを作成してみました。ただし事情が有って最新曲は再生の対象とはしません。「超・最新曲」がプレイリストに追加されると、最新曲も再生されるようになります。

    
mysql> select * from playlist;
+----+-----------------+------+---------------------+
| id | file            | seq  | last_play           |
+----+-----------------+------+---------------------+
|  1 | 第01曲目.mp3    |    1 | 2020-06-06 11:26:30 |
|  2 | 第02曲目.mp3    |    2 | NULL                |
|  3 | 第03曲目.mp3    |    3 | NULL                |
|  4 | 第04曲目.mp3    |    4 | NULL                |
+----+-----------------+------+---------------------+
4 rows in set (0.00 sec)

この状態で実行すると

4曲目を抜かして1曲目から3曲目をローテーションします。

mysql> insert into playlist(file,seq) values("第05曲目.mp3",6),("間奏.mp3",5),("最新曲.mp3",10);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from playlist;                                                  +----+-----------------+------+---------------------+
| id | file            | seq  | last_play           |
+----+-----------------+------+---------------------+
|  1 | 第01曲目.mp3    |    1 | 2020-06-06 11:28:46 |
|  2 | 第02曲目.mp3    |    2 | 2020-06-06 11:28:39 |
|  3 | 第03曲目.mp3    |    3 | 2020-06-06 11:28:42 |
|  4 | 第04曲目.mp3    |    4 | NULL                |
| 11 | 第05曲目.mp3    |    6 | NULL                |
| 12 | 間奏.mp3        |    5 | NULL                |
| 13 | 最新曲.mp3      |   10 | NULL                |
+----+-----------------+------+---------------------+
7 rows in set (0.00 sec)

レコードを追加してテーブルを上記の状態に更新しました。

これまで対象とされてなかった4曲目が再生されるようになり、さらに4曲目と5曲目の間に間奏が演奏されるようになりました。後から付け加えた最新曲は再生の対象とはなりません。  

#!/bin/bash
play_dir="."
host=127.0.0.1
DB=life_log
table="playlist"

# テーブル playlist の存在を確認
exist=`\
mysql -N -h ${host} ${DB} << _SQL_
 show tables like "${table}";
_SQL_
`
# 存在しない場合作成する
if [ -z ${exist} ]
then
 temp=`mktemp ../XXXX.tmp`
 ls -1 ${play_dir} |\
  awk '{print $1,NR}' > ${temp}
mysql -h ${host} ${DB} << _SQL_
create table if not exists ${table} (
 id int primary key auto_increment,
 file text,
 seq int unique,
 last_play datetime
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* ファイル名のload */
set character_set_database=utf8;
load data local infile "${temp}"
 into table ${table}
 fields terminated by " "
 lines terminated by "\n"
 (file,seq);
_SQL_
rm ${temp}
fi

# 最新曲のseqを取得する
max_seq=\
`mysql -h ${host} -N ${DB} << _SQL_
 select max(seq) from ${table};
_SQL_
`

# 最後に再生した曲の${seq}を取得する
last_seq=\
`mysql -h ${host} -N ${DB} << _SQL_
 select seq from ${table}
  where last_play=
  (select max(last_play) from ${table});
_SQL_
`

# 次に再生する曲の${seq}を決定する
next_seq=\
`mysql -h ${host} -N ${DB} << _SQL_
  select min(seq) from ${table} where seq>${last_seq};
_SQL_
`
if [ ${next_seq} -eq ${max_seq} ]
then
next_seq=\
`mysql -h ${host} -N ${DB} << _SQL_
  select min(seq) from ${table};
_SQL_
`
fi

#次に再生する曲のファイル名を取得する
mp3file=\
`mysql -h ${host} -N ${DB} << _SQL_
 select file from ${table} where seq=${next_seq};
_SQL_
`
echo "next song is " ${mp3file}

# ここで mplayer などを用いて ${mp3file} を再生する。
sleep 2

# 最後に再生した曲をデータベース登録する
mysql -h ${host} -N ${DB} << _SQL_
 update ${table} set last_play=now() where seq="${next_seq}";
_SQL_