epgrecで録画したテレビ番組内容情報を1つのcsvファイルにまとめるシェルスクリプト

2020 Oct. 31.
2020 Oct. 17.

epgrecで録画したテレビ番組内容情報をmysqlからcsvファイル形式で取り出し、それまでのテレビ番組内容情報csvと統合し、重複データを削除した1つのcsvファイルを取得するシェルスクリプト

cat_csvs4cron.sh

#!/bin/bash
# 2020 Oct. 17.
# Ryuichi Hashimoto.

# Update an unified recorded-tv-csv-file.
# Usage: command

# "output_csv_from_epgrec-db.sh" which I wrote is required.


RootDir='/DIR/TO/SAVE/DATA'
UnifiedDir=${RootDir}/unified
UnifiedCsv='unified.csv'
LatestMysqlCsvDir=${RootDir}/latest_mysql
BackupDir='/BACKUP-DATA/DIR'
BackupOwner='USER'
CommandName=${0##*/}

if [[ '-h' = $1 ]] || [[ '--help' = $1 ]]; then
  \echo "Usage: ${CommandName}"
  \echo "Update an unified recorded-tv-csv-file."
  \echo "Unified recorded-tv-csv-file is ${UnifiedDir}/${UnifiedCsv}"
  \exit 1
fi

if [ $# -ne 0 ]; then
  \echo "Error arguement."
  \echo "Usage: ${CommandName}"
  \exit 1
fi

# get latest csv-files
\output_csv_from_epgrec-db.sh $LatestMysqlCsvDir

# backup an old csv-file
if [ ! -f ${UnifiedDir}/${UnifiedCsv} ]; then
  \touch ${UnifiedDir}/${UnifiedCsv}
fi
if [ -f ${UnifiedDir}/${UnifiedCsv} ]; then 
  FileSize=$(\ls -l ${UnifiedDir}/${UnifiedCsv} | \sed -e "s/[ $'\t'][ $'\t']*/ /g" | \cut -d " " -f 5)
  if [[ $FileSize -lt 4000000 ]]; then
    \cp -p ${UnifiedDir}/${UnifiedCsv} ${UnifiedDir}/${UnifiedCsv}.bak.tooSmall
  else
    \cp -p ${UnifiedDir}/${UnifiedCsv} ${UnifiedDir}/${UnifiedCsv}.bak
  fi
fi

# unify csv-files
\find ${UnifiedDir}/ $LatestMysqlCsvDir/ -type f -name '*.csv' | xargs cat | sort | uniq > /tmp/${UnifiedCsv}
\cp /tmp/${UnifiedCsv} ${UnifiedDir}/${UnifiedCsv}
\cp /tmp/${UnifiedCsv} ${BackupDir}/${UnifiedCsv}
\chmod 644 ${UnifiedDir}/${UnifiedCsv} ${BackupDir}/${UnifiedCsv}
\chown ${BackupOwner}:${BackupOwner} ${BackupDir}/${UnifiedCsv}

\exit 0

epgrecで録画したテレビ番組内容情報をmysqlからcsvファイル形式で取り出すシェルスクリプト

output_csv_from_epgrec-db.sh

#!/bin/bash
# 2019 Oct. 22.
# Ryuichi Hashimoto.

# Get csv-files of recorded TV-programs from epgrec_una-db & epgrec-db in mysql.
#   In this script, line for epgrec-db is commented out.
#   So, epgrec_una-db is processed but epgrec-db is not processed.
# Csv-files are saved in the directory which is directed in arguement.


CommandName=$(basename $0)
if [[ '-h' = $1 ]] || [[ '--help' = $1 ]]; then
  \echo "Usage: ${CommandName} Dir"
  \echo "Get csv-files of recorded TV-programs from epgrec_una-db & epgrec-db in mysql."
  \echo "   In this script, line for epgrec-db is commented out."
  \echo "   So, epgrec_una-db is processed but epgrec-db is not processed."
  \echo "Csv-files are saved in the directory which is directed in arguement."
  exit 1
fi

# '-'で始まる引数があれば不適
if [[ '-' = ${1:0:1} ]]; then
  \echo "Error arguement."
  \echo "Usage: ${CommandName} Dir"
  \exit 1
fi
if [ $# -ne 1 ]; then
  \echo "Error arguement."
  \echo "Usage: ${CommandName} Dir"
  \exit 1
fi

DB1='DB-NAME-FOR-epgrec_una'
Table1='Recorder_reserveTbl'
User1='DB-USER-FOR-epgrec_una'
PW1='DB-USER-PASSWORD-FOR-epgrec_una'
DB2='DB-NAME-FOR-epgrec'
Table2='Recorder_reserveTbl'
User2='DB-USER-FOR-epgrec'
PW2='DB-USER-PASSWORD-FOR-epgrec'
CsvFileStem1='epgrec_una'
CsvFileStem2='epgrec'
TmpDir=/tmp
StoreDir=$1
if [ '/' = ${StoreDir: -1} ]; then
  StoreDir=${StoreDir/%?/}
fi
if [ ! -d $StoreDir ]; then
  \echo "${StoreDir} does not exist."
  \echo "Usage: ${CommandName} Dir"
  \exit 1
fi

Date=$(\date "+%y%m%d%H%M%S")
TmpCsvFile1=${CsvFileStem1}${Date}.csv
TmpCsvFile2=${CsvFileStem2}${Date}.csv
CsvFile1=${CsvFileStem1}.csv
CsvFile2=${CsvFileStem2}.csv

# access to mysql and output csv-file
\mysql -u $User1 -p${PW1} ${DB1} -e "select * from ${Table1} INTO OUTFILE \"${TmpDir}/${TmpCsvFile1}\" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY \"\n\";"
# \mysql -u $User2 -p${PW2} ${DB2} -e "select * from ${Table2} INTO OUTFILE \"${TmpDir}/${TmpCsvFile2}\" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY \"\n\";"

# copy csv-files to store-directory
if [ -f ${StoreDir}/${CsvFile1} ]; then
  \mv ${StoreDir}/${CsvFile1} ${StoreDir}/${CsvFile1}.bak
fi
if [ -f ${StoreDir}/${CsvFile2} ]; then
  \mv ${StoreDir}/${CsvFile2} ${StoreDir}/${CsvFile2}.bak
fi
if [ -f ${TmpDir}/${TmpCsvFile1} ]; then
  \cp ${TmpDir}/${TmpCsvFile1} ${StoreDir}/${CsvFile1}
fi
if [ -f ${TmpDir}/${TmpCsvFile2} ]; then
  \cp ${TmpDir}/${TmpCsvFile2} ${StoreDir}/${CsvFile2}
fi

\exit 0