利用taglib和sqlite实现简易的媒体数据库

#!/bin/bash
#

SQLITE_CMD=sqlite3
DB_NAME=test.db
CC=g++
TAG_READER=./tagreader

folder_cnt=0
track_id_idx=0
artist_id_idx=-1

#
# 在这里创建了数据库结构。结构很简单，我就不画图了。直接看吧。
function create_db(){
  echo --- creating database ---
  $SQLITE_CMD $DB_NAME "Create TABLE coretracks (track_id INTEGER PRIMARY KEY, album_id INTEGER, artist_id INTEGER, folder_id INTEGER, title TEXT, file_name TEXT, uri_path TEXT, uri_artwork TEXT);"
  $SQLITE_CMD $DB_NAME "Create TABLE corealbums (album_id INTEGER PRIMARY KEY, artist_id INTEGER, title TEXT, artist_name TEXT, uri_artwork TEXT);"
  $SQLITE_CMD $DB_NAME "Create TABLE coreartists (artist_id INTEGER PRIMARY KEY, name TEXT);"
  $SQLITE_CMD $DB_NAME "Create TABLE corefolders (folder_id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER);"
}

# 这个函数用来递归解析给定的路径及其子路径下的媒体文件。这里为了简化流程，仅对扩展名为mp3的文件进行了解析。
# 函数传入了四个参数，分别是：当前路径（绝对路径）、将要进入的子路径名称、当前已经计数到的folder id、父路径的folder id
function cntsana(){
  local full_working_folder=$1"/"$2
  local current_folder=$2
  local folder_id_idx=$[$3+1]
  local parent_folder_id=$4
  folder_cnt=$[$folder_cnt+1]
  $SQLITE_CMD $DB_NAME "insert into corefolders values( $folder_cnt, \"$current_folder\", $parent_folder_id);"
  for file in `ls $full_working_folder -A`
  do
    if [ -d $full_working_folder"/"$file ]
    then
      cntsana $full_working_folder $file $folder_cnt $folder_id_idx
    else
      local path=$full_working_folder"/"$file
      local name=$file
      ext=${path##*.}
      if [[ $ext == "mp3" ]];then
        # 调用前面的cpp程序进行tag解析
        tag_string=(`$TAG_READER $path`)
        title=${tag_string[0]}
        artist=${tag_string[1]}
        album=${tag_string[2]}
        artwork=${tag_string[3]}
        sql_result=`$SQLITE_CMD $DB_NAME "select count() from coreartists where name=\"$artist\";"`
        if [[ $sql_result == 0 ]];then
          artist_cnt=`$SQLITE_CMD $DB_NAME "select count() from coreartists;"`
          artist_id_idx=$[$artist_cnt+1]
          sql_result=`$SQLITE_CMD $DB_NAME "insert into coreartists values( $artist_id_idx, \"$artist\");"`
        else
          artist_id_idx=`$SQLITE_CMD $DB_NAME "select artist_id from coreartists where name=\"$artist\";"`
        fi

        sql_result=`$SQLITE_CMD $DB_NAME "select count() from corealbums where title=\"$album\" and artist_name=\"$artist\";"`
        if [[ $sql_result == 0 ]];then
          album_cnt=`$SQLITE_CMD $DB_NAME "select count() from corealbums;"`
          album_id_idx=$[$album_cnt+1]
          sql_result=`$SQLITE_CMD $DB_NAME "insert into corealbums values( $album_id_idx, $artist_id_idx, \"$album\", \"$artist\", \"$artwork\");"`
        else
          album_id_idx=`$SQLITE_CMD $DB_NAME "select album_id from corealbums where title=\"$album\" and artist_name=\"$artist\";"`
        fi

        $SQLITE_CMD $DB_NAME "insert into coretracks values( $track_id_idx, $album_id_idx, $artist_id_idx, $folder_id_idx, \"$title\", \"$name\",  \"$path\", \"$artwork\");"
        track_id_idx=$[$track_id_idx+1]
      fi
    fi
  done
}

# 以tag为基准进行的媒体查询函数
function tag_list(){
  local artist_list=(`$SQLITE_CMD $DB_NAME "select name from coreartists;"`)
  local artist_cnt=${#artist_list[@]}
  for i in $( seq 1 ${artist_cnt} )
  do
    echo $i -- ${artist_list[i-1]}
  done
  echo -n "Please choose the artist:"
  read chosen_artist_idx
  echo You are chosen the ${artist_list[$chosen_artist_idx-1]} ...
  chosen_artist_id=`$SQLITE_CMD $DB_NAME "select artist_id from coreartists where name=\"${artist_list[$chosen_artist_idx-1]}\";"`

  local album_list=(`$SQLITE_CMD $DB_NAME "select title from corealbums where artist_name=\"${artist_list[$chosen_artist_idx-1]}\";"`)
  local album_cnt=${#album_list[@]}
  for i in $( seq 1 ${album_cnt} )
  do
    echo $i -- ${album_list[i-1]}
  done
  echo -n "Please choose the album:"
  read chosen_album_idx
  echo You are chosen the ${album_list[$chosen_album_idx-1]} ...
  chosen_album_id=`$SQLITE_CMD $DB_NAME "select album_id from corealbums where artist_name=\"${artist_list[$chosen_artist_idx-1]}\" and title=\"${album_list[$chosen_album_idx-1]}\";"`

  local track_list=(`$SQLITE_CMD $DB_NAME "select title from coretracks where artist_id=$chosen_artist_id and album_id=$chosen_album_id;"`)
  local track_cnt=${#track_list[@]}
  for i in $( seq 1 ${track_cnt} )
  do
    echo $i -- ${track_list[i-1]}
  done
  echo -n "Please choose the track:"
  read chosen_track_idx
  local track_path_list=(`$SQLITE_CMD $DB_NAME "select uri_path from coretracks where artist_id=$chosen_artist_id and album_id=$chosen_album_id;"`)
  echo You are choosing the \"${track_list[$chosen_track_idx-1]}\" of the album \"${album_list[$chosen_album_idx-1]}\" singing by \"${artist_list[$chosen_artist_idx-1]}\" in the filesystem \"${track_path_list[$chosen_track_idx-1]}\".
}

# 传统的，以文件系统结构为基准进行的媒体查询函数
function folderfile_list(){
  local chosen_folder_id=-1
  local chosen_parent_id=1
  while ([[ $folder_chosen_idx != 0 ]])
  do
    local folder_list=(`$SQLITE_CMD $DB_NAME "select name from corefolders where parent_id=$chosen_parent_id;"`)
    local folder_cnt=${#folder_list[@]}
    if [[ $folder_cnt != 0 ]];then
      echo 0 -- show file list
      for i in $( seq 1 ${folder_cnt} )
      do
        echo $i -- ${folder_list[i-1]}
      done
      echo x -- back to parent
      echo -n "Please choose the folder:"
      read folder_chosen_idx
      if [[ $folder_chosen_idx == "x" ]];then
        echo You are chosen to back to parent ...
        chosen_folder_id=`$SQLITE_CMD $DB_NAME "select parent_id from corefolders where folder_id=$chosen_parent_id;"`
        chosen_parent_id=$chosen_folder_id
      elif [[ $folder_chosen_idx != 0 ]];then
        echo You are chosen the ${folder_list[$folder_chosen_idx-1]} ...
        chosen_folder_id=`$SQLITE_CMD $DB_NAME "select folder_id from corefolders where name=\"${folder_list[$folder_chosen_idx-1]}\";"`
        chosen_folder_name=${folder_list[$folder_chosen_idx-1]}
        chosen_parent_id=$chosen_folder_id
      fi
    else
      break
    fi
  done

  track_list=(`$SQLITE_CMD $DB_NAME "select file_name from coretracks where folder_id=$chosen_folder_id;"`)
  track_cnt=${#track_list[@]}
  for i in $( seq 1 ${track_cnt} )
  do
    echo $i -- ${track_list[i-1]}
  done
  echo -n "Please choose the file:"
  read chosen_track_idx
  track_path_list=(`$SQLITE_CMD $DB_NAME "select uri_path from coretracks where folder_id=$chosen_folder_id;"`)
  echo You are choosing the \"${track_list[$chosen_track_idx-1]}\" of the folder \"$chosen_folder_name\" in the filesystem \"${track_path_list[$chosen_track_idx-1]}\".
}

#main
if [[ ! -f $TAG_READER ]];then
  $CC tagreader.c -L/usr/local/lib -ltag -o $TAG_READER
fi

if [[ -f $DB_NAME ]];then
  rm -f $DB_NAME
fi
create_db

work_root_path=$1
if [[ $work_root_path == "" ]];then
  work_root_path=`pwd`
else
  if [[ ${work_root_path:0:1} != "/" && ${work_root_path:0:1} != "~" ]];then
    work_root_path=`pwd`/$work_root_path
  fi
fi

cntsana $work_root_path "." 0 0

echo 1 -- tag list
echo 2 -- folderfile list
echo x -- exit
echo -n "Please choose the list type:"
read list_type_idx
if [[ $list_type_idx == 1 ]];then
  tag_list
elif [[ $list_type_idx == 2 ]];then
  folderfile_list
fi