MySQLとかよく知らないんで、作成はRedmine(Rails)のモデル作成スクリプトで。
アクセスはActiveRecordとかRedmineプラグインから。SQL文は書きません。
狙いは
- かねてから問題だった番組情報の更新がうまく行かず番組チケットが重複してしまう問題の解決手段
- 処理速度改善
- 動的スケジュールの下地（将来機能）
EPG情報は一週間分の情報が含まれている。新しい番組情報は一週間後の１日のものがほとんど。それ以外は既存の番組情報。厄介なのは一週間の間にプログラムが色々変更されたりすることだ。
今までは番組名や説明文に変更があれば更新する程度の対応はしていた。
それ以外の放送時間とかは変更されない。というか変更に対応できていなかった。
そのため大幅な変更があった場合は新規番組として認識して同じ番組が重複していた。
それが自動予約対象だったりすると、もう大変。手動でお掃除である。
オリンピック番組がよいサンプルになった
番組枠はあっても、内容がめっちゃ変わる。終了時間が１時間変更されたり、ジャンルが変わったり。もう番組の変更とは言えないくらい変わった。
こういうスペシャル番組が特に自分が残したい対象なのでどうにかしたい。
そろそろ本題
今までRedmineのモデルだけで何とかしてきたけれど、時間を扱うカスタムフィールドを作ることが出来ない。仕方なく自前のテーブルで対応することにした。Redmineプラグインで視聴や予約処理をする部分を作っているので専用モデル作っても大変じゃなさそうだし。
タイムラインテーブルを作ることにした
録画時間がRedmine既存モデルで日付と時間に分かれているが、検索に時間がかかるのと、今後のことも考えて日時を扱うシンプルなレコードを用意して、検索時間の短縮とシンプル化を計る。同時に大幅な変更にもある程度耐えられるような検索が行えるように。
ここから本題（前置き長くてすみません）
もちろんRedmineプラグインでモデルを作ったことがなかったんで試行錯誤した。その辺のこともちょっと書いておくよ。巷のハウツー情報はRedmine2.0以前の物が多く、手順が違ったりして最初は結構戸惑ったよ。
テーブル内容
issue_id : チケット番号
start_on : 開始日時
end_on : 終了日時
channel : チャンネル
reserved : 予約？
後からstart_on,end_onとかカッコつけずにstart_time,end_timeとかにすればよかったと後悔することになったけど、作ってしまったのでそのままである。
モデルテンプレート作成
$ sudo RAILS_ENV=production ruby script/rails generate redmine_plugin_model redmine_video timeline issue_id:integer start_on:datetime end_on:datetime channel:string reserved:booleanすると、以下の様にテンプレートスクリプトファイルが作成される。
/var/lib/gems/1.9.1/gems/activesupport-3.2.6/lib/active_support/dependencies.rb:251:in `block in require': iconv will be deprecated in the future, use String#encode instead.
create plugins/redmine_video/app/models/timeline.rb
create plugins/redmine_video/test/unit/timeline_test.rb
create plugins/redmine_video/db/migrate/001_create_timelines.rb
db/migrate/日付_create_timelines.rb ってなるから001_に直せという記事があるが、最近のRedmineでは001になるらしい。
やり直したかったら
$ sudo RAILS_ENV=production ruby script/rails d redmine_plugin_model redmine_video timelineで生成したテンプレートを削除出きる。
'd'はdestroyの省略形。
$ ruby script/railsでどんなコマンドがあるか確認できる。因みにgenerateは'g'。
レコード名が気に入らなかったので
$ sudo RAILS_ENV=production ruby script/rails g redmine_plugin_model redmine_video video_timeline issue_id:integer start_on:datetime end_on:datetime channel:string reserved:booleanで、以下のファイルが作成された。
/var/lib/gems/1.9.1/gems/activesupport-3.2.6/lib/active_support/dependencies.rb:251:in `block in require': iconv will be deprecated in the future, use String#encode instead.
create plugins/redmine_video/app/models/video_timeline.rb
create plugins/redmine_video/test/unit/video_timeline_test.rb
create plugins/redmine_video/db/migrate/002_create_video_timelines.rb
ん？002_create_video_timelines.rb？ なんで００２なんだ？ どうしてだか、001しかないのに002を消したと言っている。実際001が残ってしまっている。手動で消したよ。
気持ち悪いので、最初からやり直して、001が作られるようにした。
DBマイグレーション
$ sudo rake db:migrate:plugin NAME=redmine_video RAILS_ENV=productionじゃなくて、最近は
$ sudo rake redmine:plugins:migrate NAME=redmine_video RAILS_ENV=productionだそうだ。これで実際にDBにレコードが作成される。
失敗したら
$ sudo rake redmine:plugins:migrate NAME=redmine_video RAILS_ENV=production VERSION=0ってやればテーブルがドロップされる。
RAILS_ENVとか環境変数は、前につけたり後につけたりしてるけど、rubyなどのバイナリツールの場合は前に、rake等シェルツールの場合は後指定って感じかな？
DBを確認してみる
$ mysql -uredmine -predmine redmine
mysql> show tables;
video_timelinesってのができた。
mysql> show columns from video_timelines;
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | issue_id | int(11) | YES | | NULL | | | start_on | datetime | YES | | NULL | | | end_on | datetime | YES | | NULL | | | channel | varchar(255) | YES | | NULL | | | reserved | tinyint(1) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+うん。できているようだ。
こんな風に色々試行錯誤しながら進めた。自然と作ったり消したりの作業が気安い感じになってくる。
下準備
これからタイムラインベースの検索をしたいので既存番組チケットにタイムラインレコードを作成する。
まずはお掃除（clean.rb)
#!/usr/bin/ruby
# -*- coding: utf-8 -*-
require 'rubygems'
require 'active_record'
require 'active_support/core_ext'
ActiveRecord::Base.establish_connection(
:adapter => 'mysql2',
:host => 'localhost',
:username => 'redmine',
:password => 'redmine',
:database => 'redmine'
)
class Issue < ActiveRecord::Base
end
class CustomValues < ActiveRecord::Base
end
class IssueCategories < ActiveRecord::Base
end
#
# ダブった番組チケットができてしまうので、トラバースしてダブリを削除する
#
del_issues = []
issues = Issue.find(:all, :conditions => {:tracker_id => 1,:status_id => 1})
issues.each {|issue|
time = CustomValues.first( :conditions=> {
:customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 1 }).value
# チャンネル
ch = CustomValues.first( :conditions => {
:customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 2 }).value
same_date_issues = Issue.find(:all, :conditions =>{
:tracker_id => 1,
:status_id => 1,
:start_date => issue.start_date,
# :estimated_hours => issue.estimated_hours,
# :category_id => issue.category_id})
})
next if same_date_issues.size <= 1
# puts '%d %d' % [issue.id, same_date_issues.size]
same_date_issues.each {|same|
if same.id != issue.id
same_time = CustomValues.first( :conditions=> {
:customized_type => "Issue",
:customized_id => same.id,
:custom_field_id => 1 }).value
same_ch = CustomValues.first( :conditions => {
:customized_type => "Issue",
:customized_id => same.id,
:custom_field_id => 2 }).value
if same_time == time and same_ch == ch and same.id > issue.id
p 'find same ticket %d => %d' % [issue.id, same.id]
del_issues <<= same.id
end
end
}
}
puts 'deleted %d issues' % del_issues.size
Issue.delete(del_issues)
values = (del_issues.collect {|i| CustomValues.find(:all,:conditions=>{ :customized_id=>i })}).flatten
puts 'deleted %d custom values' % values.size
CustomValues.delete(values)
cate = IssueCategories.select {|c| not Issue.exists?(:category_id => c.id) }
puts 'deleted %d independency categories' % cate.size
IssueCategories.delete(cate)
今までの重複したチケットをできる限り認識して削除しておく。総当りに近いのでものすごい時間がかかった。
タイムライン作成(timeline.rb)
#!/usr/bin/ruby
# -*- coding: utf-8 -*-
require 'active_record'
ActiveRecord::Base.establish_connection(
:adapter => 'mysql2',
:host => 'localhost',
:username => 'redmine',
:password => 'redmine',
:database => 'redmine'
)
class Issue < ActiveRecord::Base; end
class CustomValues < ActiveRecord::Base; end
# 独自レコード
class VideoTimelines < ActiveRecord::Base
# :issue_id : integer
# :start_on : datetime
# :end_on : datetime
# :channel : string
# :reserved : boolean
end
#
# Timeline登録
#
Issue.find(:all, :conditions=> ["status_id<3"]).each {|issue|
unless VideoTimelines.exists?(:issue_id => issue.id)
channel = CustomValues.first( :conditions => {
:customized_id => issue.id,
:custom_field_id => 2}).value
time = CustomValues.first( :conditions => {
:customized_id => issue.id,
:custom_field_id => 1}).value.to_i
start_on = Time.parse( issue.start_date.strftime("%Y%m%d") + format("%04d",time) )
end_on = start_on + issue.estimated_hours.to_i * 60
timeline = VideoTimelines.create( :issue_id => issue.id,
:start_on => start_on,
:end_on => end_on,
:channel => channel,
:reserved => issue.tracker_id == 2
)
puts "%d %d %s %s %s %s" % [timeline.id,
timeline.issue_id,
timeline.start_on,
timeline.end_on,
timeline.channel,
timeline.reserved.to_s]
end
}
一度実行するだけの単発スクリプトだけど、実際には試行錯誤する上で何度も実行することになるんで、落ち着くまでは大活躍である。
間違ったりうまく行かなかったときは、SQLコマンドで直接削除してから再実行したりする。
なんだかんだと色々やって毎日のタスクが出来上がった。（まだ発展途上だけど）
修正版 entry2.rb
#!/usr/bin/ruby
# -*- coding: utf-8 -*-
require 'rubygems'
require 'active_record'
require 'rexml/document'
ActiveRecord::Base.establish_connection(
:adapter => 'mysql2',
:host => 'localhost',
:username => 'redmine',
:password => 'redmine',
:database => 'redmine'
)
class Issue < ActiveRecord::Base; end
class IssueCategorie < ActiveRecord::Base; end
class CustomValue < ActiveRecord::Base; end
# VideoTimelines 独自レコード
class VideoTimeline < ActiveRecord::Base; end
$entry_count = 0
$update_count = 0
$update_time_count = 0
def create_job id
# not yet
end
def delete_job id
# not yet
end
def delete_issue id
issue = Issue.find(id)
# 予約実行中？
delete_job id if issue.tracker_id == 2 and issue.status_id == 2
CustomValue.delete_all(:customized_id => id)
VideoTimeline.delete_all(:issue_id => id)
Issue.delete(id)
puts "deleted #{id}"
end
def create_issue info
# 日にちと時間に分離
start_date = info[:start_time].to_date
start_time = info[:start_time].strftime("%H%M").to_i
# チケット作成
issue = Issue.create( :project_id => 1, #'番組プログラム'
:tracker_id => 1, #'番組'
:lft => 1,
:rgt => 2,
:status_id => 1,
:priority_id => 2,
:done_ratio => 0,
:author_id => 1,
:subject => info[:title],
:description => info[:desc],
:start_date => start_date,
:due_date => start_date,
:estimated_hours => info[:duration],
:category_id => info[:category_id] )
# root_id 更新
# NULLのままだとRedmineからのチケット更新でクラッシュする
# 親がない場合は自分のIDを入れるらしい。自分？ 更新しか無い？
issue.root_id = issue.id
if issue.save
# 開始時間
CustomValue.create( :customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 1, #'開始時間'
:value => start_time )
# チャンネル
CustomValue.create( :customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 2, #'チャンネル'
:value => info[:channel] )
# 内容
CustomValue.create( :customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 4, #'内容'
:value => info[:desc] )
# descriptionをフィルタできないから。出来るフィールドを作った
end
# タイムライン作成
VideoTimeline.create( :issue_id => issue.id,
:start_on => info[:start_time],
:end_on => info[:end_time],
:channel => info[:channel],
:reserved => false )
$entry_count = $entry_count + 1
puts "NEW %s" % issue.id
end
def update_issue timeline, info
issue = Issue.find(timeline.issue_id)
update_f = false
update_time_f = false
# 題名
if issue.subject != info[:title]
issue.subject = info[:title]
update_f = true
end
# 内容
if issue.description != info[:desc]
issue.description = info[:desc]
content = CustomValue.first(:conditions => {
:customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 4 })
if content
content.value = info[:desc]
content.save
end
update_f = true
end
# カテゴリ
if issue.category_id != info[:category_id]
issue.category_id = info[:category_id]
update_f = true
end
# 時間
if issue.estimated_hours != info[:duration]
issue.estimated_hours = info[:duration]
update_f = update_time_f = true
end
# タイムライン
if timeline.start_on != info[:start_time]
timeline.start_on = info[:start_time]
update_f = update_time_f = true
# 録画時間
start_time = timeline.start_on.strftime("%H%M").to_i
time = CustomValue.first( :conditions=> {
:customized_type => "Issue",
:customized_id => issue.id,
:custom_field_id => 1 })
if time
time.value = start_time
time.save
end
end
if timeline.end_on != info[:end_time]
timeline.end_on = info[:end_time]
update_f = update_time_f = true
end
# 更新
if update_f
issue.save
# 更新 timeline
if update_time_f
timeline.save
# 予約実行中？
if issue.tracker_id == 2 and issue.status_id == 2
delete_job issue.id
create_job issue.id
end
$update_time_count = $update_time_count + 1
puts "UPDATE TIME %d" % issue.id
end
$update_count = $update_count + 1
puts "UPDATE %d" % issue.id
end
end
#
# チケット登録
#
def entry_issue info
# マッチするチケットを検索
# ５分前後の変動を許容する
range = 5 * 60
startL = info[:start_time] - range
startR = info[:start_time] + range
endL = info[:end_time] - range
endR = info[:end_time] + range
timelines = VideoTimeline.where(:channel => info[:channel],
:start_on => startL..startR,
:end_on => endL..endR)
# 大幅な終了時間変更？
timelines = VideoTimeline.where(:channel => info[:channel],
:start_on => info[:start_time]) unless timelines.size==0
if timelines.size>=1
if timelines.size==1
#特定された
update_issue timelines.first, info
return
else
#複数あった！もうなんだかわからないんで全消し
timelines.each {|vt| delete_issue vt.issue_id }
end
end
# 大幅な変更があると見つけるのが困難なので
# 何がどう変わったかをちゃんと判別するのはやめて、昔のは消して新規にする
# かぶっている番組があれば、削除する（消滅した？）
VideoTimeline.where("channel = ? and start_on >= ? and end_on <= ?",
info[:channel], info[:start_time], info[:end_time]).each {|vt|
delete_issue vt.issue_id
}
# 新規に作る
create_issue info
end # entry_issue
#
# TVプログラム登録
#
xml_file = ARGV.shift + '.xml'
xml = REXML::Document.new open(xml_file).read
ch_name = xml.elements['tv/channel/display-name'].text
xml.elements.each('/tv/programme') {|program|
start = Time.parse( program.attributes['start'] )
stop = Time.parse( program.attributes['stop'] )
duration = (stop - start) / 60
# ５分以下の短い番組は登録しない :todo: DBに設定できると良い
next if duration <= 5
# カテゴリ
category = program.elements.to_a('category').collect{|c| c.text}.join(".")
cate = IssueCategorie.where(:project_id => 1, :name => category).first
cate = IssueCategorie.create( :project_id => 1, :name => category ) if cate.nil?
info = {
:duration => duration,
:start_time => start,
:end_time => stop,
:channel => program.attributes['channel']+ '.'+ch_name,
:title => program.elements['title'].text,
:desc => program.elements['desc'].text,
:category_id => cate.id
}
info[:desc] = "" if info[:desc].nil?
info[:title].gsub!(/(【二】|【デ】|【Ｓ】|【字】|【SS】|【手】|【解】)/,"")
entry_issue info
}
puts "%d entried" % $entry_count
puts "%d updated" % $update_count
puts "%d time updated" % $update_time_count
ここで気づいたんだけれど、
class VideoTimeline < ActiveRecord::Base; endとか、テーブル名を単数形にしている。実際のテーブル名は'video_timelines'で複数形。おや？と思ってどっちも書いてみたけど単数形でも複数形でもどっちでも大丈夫だった。
Redmineのテーブルやカスタムフィールドテーブルも同じ。なのでいままで複数形で書いていた部分もわざと単数形にしてみた。ふむ。普通に動く。不思議だ。
新たに知った範囲検索
深夜番組とか時間が微妙にずれたりする場合があるんで、時間を範囲で検索したい。どうするんだ？と思ったら
:start_on => L..Rって簡単に書けた！素晴らしいです。
複雑な場合分け検索はやめてありがちな変更だけに絞る。それ以外は諦めて、古いやつを消して新しく作る。そんな考えで作って処理を簡素化した。実際これで結構いけているようだ。
Jenkinsとの連携はまだ
既に予約実行中の番組に時間変更があった場合、Jenkins上のジョブも更新する必要があるだろ。そう思って関数は用意したけど、今のところその必要性が無いのと、今後の対応で思うところがあって空白のままだ。将来の動的スケジュール機能とセットで考えようと思ってる。
自動予約も更新(query2.rb)
#!/usr/bin/ruby
# -*- coding: utf-8 -*-
require 'active_record'
require 'rest_client'
require 'hudson-remote-api'
key = '???'
api = "http://localhost/projects/%s/issues.xml?key="+key+"&query_id="
# レコード
class Issue < ActiveRecord::Base; end
class CustomValues < ActiveRecord::Base; end
class Query < ActiveRecord::Base; end
class Project < ActiveRecord::Base; end
# VideoTimelines
class VideoTimelines < ActiveRecord::Base; end
# DB接続
ActiveRecord::Base.establish_connection(
:adapter => 'mysql2',
:host => 'localhost',
:username => 'redmine',
:password => 'redmine',
:database => 'redmine'
)
# project識別名を合成
api = api % Project.first(1)[0].identifier.to_s
# Jenkins接続
Hudson.settings = {:url => 'http://localhost/jenkins', :crumb => false }
# ジョブ作成
def create_job info
# Config
return false if !Hudson::Job.list.include?("record_0")
config = REXML::Document.new(Hudson::Job.get("record_0").config)
# コマンド定義
commands = [
"/opt/task/ready.rb %d" % info[:issue_id],
"/opt/task/record2.sh %d %d %s" % [
info[:channel],
info[:duration],
info[:output]
],
"/opt/task/record2.rb %d" % info[:issue_id]
]
# 時間設定
config.elements["/project/triggers/hudson.triggers.TimerTrigger/spec"].text =
"%s %s %s %s *" % [
info[:datetime][10..11],
info[:datetime][8..9],
info[:datetime][6..7],
info[:datetime][4..5]
]
# コマンド登録
config.elements.each_with_index('/project/builders/hudson.tasks.Shell') {|shell, i|
if i<commands.size
shell.elements["command"].text = commands[i]
end
}
# 作成
job_name = "record_%d" % info[:issue_id]
job = Hudson::Job.get job_name
job = Hudson::Job.create job_name if job.nil?
# 設定
job.update config.to_s
job.enable
return true
end
# 予約クエリー検索
reserve = []
queries = Query.find :all, :conditions => { :project_id =>1, :is_public =>1 }
queries.each {|query|
# REST-APIでクエリー実行
doc = REXML::Document.new RestClient.get api+query.id.to_s
# REXMLでトラバース
doc.elements.each('issues/issue') {|issue|
if issue.elements['tracker'].attributes['id'].to_i == 1 &&
issue.elements['status'].attributes['id'].to_i == 1
# 予約チケットID収集
reserve << issue.elements['id'].text.to_i
end
}
}
Issue.find(reserve).each {|issue|
channel = CustomValues.first( :conditions => {
:customized_id => issue.id,
:custom_field_id => 2}).value.to_i
time = CustomValues.first( :conditions => {
:customized_id => issue.id,
:custom_field_id => 1}).value.to_i
# １分前にジョブがスタートするように
datetime = Time.parse( issue.start_date.strftime("%Y%m%d") + format("%04d",time) ) - 60
info = {
# チケット番号
:issue_id => issue.id,
# 録画開始日時
:datetime => datetime.strftime("%Y%m%d%H%M"),
# 録画時間
:duration => issue.estimated_hours.to_i * 60,
# チャンネル
:channel => channel,
# 保存場所
:output => "/opt/videos/%d" % issue.id
}
# ジョブ作成
if create_job info
# 実行中
issue.status_id = 2
end
# 予約トラッカー
issue.tracker_id = 2
issue.save
# タイムライン更新
vt = VideoTimelines.where(:issue_id =>issue.id).first
if vt
vt.reserved = true
vt.save
end
puts "ID: #{issue.id}"
}
redmineプラグインフックも更新(video_hooks.rb)
# -*- coding: utf-8 -*-
# 接続設定
Hudson.settings = {:url => 'http://localhost/jenkins', :crumb => false }
# ジョブ作成
def create_job info
# Config
return false if !Hudson::Job.list.include?("record_0")
config = REXML::Document.new(Hudson::Job.get("record_0").config)
# コマンド定義
commands = [
"/opt/task/ready.rb %d" % info[:issue_id],
"/opt/task/record2.sh %d %d %s" % [
info[:channel],
info[:duration],
info[:output]
],
"/opt/task/record2.rb %d" % info[:issue_id]
]
# 時間設定
config.elements["/project/triggers/hudson.triggers.TimerTrigger/spec"].text =
"%s %s %s %s *" % [
info[:datetime][10..11],
info[:datetime][8..9],
info[:datetime][6..7],
info[:datetime][4..5]
]
# コマンド登録
config.elements.each_with_index('/project/builders/hudson.tasks.Shell') {|shell, i|
if i<commands.size
shell.elements["command"].text = commands[i]
end
}
# 作成
job_name = "record_%d" % info[:issue_id]
job = Hudson::Job.get job_name
job = Hudson::Job.create job_name if job.nil?
# 設定
job.update config.to_s
job.enable
return true
end
# ジョブ削除
def delete_job id
job = Hudson::Job.get "record_#{id}"
if !job.nil?
job.delete
return true
end
return false
end
# タイムライン更新
def update_timeline issue
vt = VideoTimeline.first(:conditions => {:issue_id => issue.id})
if vt
vt.reserved = issue.tracker_id == 2
vt.save
end
#多分VideoTimelineクラス内にメソッドとして作るのが正しいと思うが・・・
end
class VideoHooks < Redmine::Hook::ViewListener
# チケット更新
def controller_issues_edit_before_save(context)
edit_hook context[:issue]
end
# バルク更新（チケットごとに呼ばれる）
def controller_issues_bulk_edit_before_save(context)
edit_hook context[:issue]
end
def edit_hook issue
# 番組内のチケット
if issue.project_id == 1
do_cancel issue if issue.tracker_id == 1 || issue.status_id == 5
do_reserve issue if issue.tracker_id == 2
end
end
# キャンセル
def do_cancel issue
# ジョブ削除して、録画キャンセルチケットに
if delete_job issue.id
issue.status_id = 5
issue.tracker_id = 1
update_timeline issue
end
end
# 予約？
def do_reserve issue
time = issue.custom_field_values[0].to_s
channel = issue.custom_field_values[1].to_s
# １分前にジョブがスタートするように
datetime = Time.parse( issue.start_date.strftime("%Y%m%d") + format("%04d",time) ) + 60
duration = issue.estimated_hours.to_i * 60
# 時間調整
nowtime = Time.now
if datetime < nowtime
if datetime + duration < nowtime
# 終わってる・・・
issue.tracker_id = 1
return
else
# 時間が過ぎてる！後半だけでも！
duration = duration - (nowtime - datetime) - 60
datetime = nowtime + 60
end
end
info = {
# チケット番号
:issue_id => issue.id,
# 録画開始日時
:datetime => datetime.strftime("%Y%m%d%H%M"),
# 録画時間
:duration => duration,
# チャンネル
:channel => channel.to_i,
# 保存場所
:output => "/opt/videos/%d" % issue.id
}
# ジョブ登録してチケットを実行中に更新
if create_job info
issue.status_id = 2
update_timeline issue
end
end
end
これで一応番組チケット更新処理と、今後のスケジューリングのための用意が整ったかな？
0 件のコメント:
コメントを投稿