# coding: utf-8 class UserDB def initialize(db = "db/otp.sq3") @from = MAILFROM @expire_after = '+5 minutes' @db = SQLite3::Database.new(db) @db.execute_batch(<<~EOF) CREATE TABLE IF NOT EXISTS users( user TEXT PRIMARY KEY, gecos TEXT, last DATETIME, expire DATETIME ); CREATE TABLE IF NOT EXISTS session( user TEXT, keytype TEXT, -- 'tmpkey' or 'sesskey' key TEXT, -- key itself expire DATETIME, -- expiration date-time UNIQUE(user, keytype, key) ); CREATE TABLE IF NOT EXISTS property( user TEXT, prop TEXT, val TEXT, time DATETIME, FOREIGN KEY(user) REFERENCES users(user) ON UPDATE CASCADE ON DELETE CASCADE ); PRAGMA FOREIGN_KEYS=on; EOF @db end def sendmail(rcpt, subj, body) # INPUT: rcpt = string or array of to-addresses # subj = Subject header of email # body = Message body require 'nkf' rcpt = rcpt.join(" ") if rcpt.is_a?(Array) sj = NKF.nkf('-jM', subj) body = NKF.nkf('-j', body) ENV["PATH"] = "/bin:/usr/bin:/usr/sbin:/usr/local/bin " open("| sendmail -f #{@from} #{rcpt}", "w") do |m| m.puts(<<~EOF) Subject: #{sj} From: OTPsample Admin <#{@from}> Mime-Version: 1.0 Content-type: text/plain; charset=iso-2022-jp EOF m.puts(body) end end def sendPasscode(email) if /.+@.+\..+/ =~ email passcode = sprintf("%04d", rand(10000)) sendmail(email, "OTP: passcode", "Your passcode of OTP = #{passcode}") passcode end end def expire @db.execute("DELETE FROM session WHERE expire<datetime('now','localtime')") end def genKey(n=50) rand(10**n).to_s(36) # 10^50の乱数の36進数表記 end def genSkey(user) expire # まず時間切れのキーを消す sKey = genKey @db.execute("INSERT INTO session VALUES(?, 'sesskey', ?, datetime('now', 'localtime', '#{@expire_after}'));", user, sKey) sKey end def genTmpkey(user) passcode = sendPasscode(user) STDERR.puts "passcode: #{passcode}" tmpkey = genKey joined = tmpkey + "-" + passcode @db.execute("INSERT INTO session VALUES(?, 'tmpkey', ?, datetime('now', 'localtime', '#{@expire_after}'));", user, joined) tmpkey end def authTmpKey(user, tmpkey, code) # tmpkeyとパスコードの組み合わせで認証 expire # 先に期限切れのキーを消す joined = tmpkey + "-" + code r = @db.execute( "SELECT user FROM session WHERE user=? AND keytype='tmpkey' AND key=?", user, joined) if r[0] && r[0].length==1 # このように認証が通ったときは単に true を返すのではなく # その後の処理に使い回せる値を返す(updateSkeyも参照) updateSkey(user, genKey) end end def authSkey(user, skey) # ブラウザに残るセッションキーで認証 expire r = @db.execute( "SELECT user FROM session WHERE user=? AND keytype='sesskey' AND key=?", user, skey) if r[0] && r[0].length==1 r = @db.execute("SELECT user FROM users WHERE user=?", user) expire = "datetime('now', 'localtime', '+50 days')" if r[0] # 既にユーザがいる場合はUPDATE @db.execute(<<~EOF, user) UPDATE users SET last=datetime('now', 'localtime'), expire=#{expire} WHERE user=? EOF else # 既存ユーザに対して REPLACE INTO を使うと # 一瞬削除されるのでFOREIGN KEY制約で関連データが消える @db.execute(<<~EOF, user) INSERT INTO users(user, last, expire) VALUES(?, datetime('now', 'localtime'), datetime('now', 'localtime', '+50 days')) EOF end updateSkey(user, skey) end end def updateSkey(user, sKey) expire # まず時間切れのキーを消す @db.execute("REPLACE INTO session VALUES(?, 'sesskey', ?, datetime('now', 'localtime', '#{@expire_after}'));", user, sKey) addProps(user, "login", Time.now.strftime("%F %T")) sKey # updateした値そのものを返すことで呼び主が次の処理に使える end # ここから下のメソッドは遊びチャットなので無視してよい def addProps(user, prop, val) @db.execute(<<~EOF, user, prop, val) INSERT INTO property VALUES(?, ?, ?, datetime('now', 'localtime')) EOF end def countProps(user, prop, val) r = @db.execute(<<~EOF, user, prop, val) SELECT count(*) FROM property WHERE user=? AND prop=? AND val=? EOF # p r[0] (r[0] && r[0][0].to_i > 0) ? r[0][0].to_i : 0 # count>1ならそれ さもなくば0 end def notAI(hash, user) # これも遊びメソッドなので無視してよい if (msg = hash["note"]) addProps(user, "note", msg) if msg == "?" r = @db.execute("SELECT COUNT(*) FROM property WHERE user=?", user) r[0] and return sprintf("主に会うのは%d度目じゃ", r[0][0].to_i) end case countProps(user, "note", msg) when 1 %w(ほう なるほど ふむ それは? わからぬ しらぬ おっぉう はて ああ 御意 まて そうか だな んだす んぐ ... まじか え、 初耳だ それな けしからぬ).sample when 5, 10 "しつこい" when 4 msg+"が好きなようじゃな。もっと深く語ってくれてもよいぞ。" when 2..11 "それは聞いた" else "暇なのか? わしは暇だ" end end end end