12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970 |
- require "yaml"
- require 'db'
- require 'db-config'
- def do_update(verbose)
- now = $db.query_row("SELECT max(last_seen) AS max FROM router_claims_nickname")['max']
- unless now
- STDERR.puts "Could not find the latest last_seen timestamp. Is the database empty still?"
- return
- end
- now = "TIMESTAMP '" + now.to_s + "'"
- denamed = $db.do("
- UPDATE router_claims_nickname
- SET named=false
- WHERE named
- AND last_seen < #{now} - INTERVAL '6 months'")
- puts "de-named: #{denamed}" if verbose
- named = $db.do("
- UPDATE router_claims_nickname
- SET named=true
- WHERE NOT named
- AND first_seen < #{now} - INTERVAL '2 weeks'
- AND last_seen > #{now} - INTERVAL '2 days'
- AND NOT EXISTS (SELECT *
- FROM router_claims_nickname AS innertable
- WHERE named
- AND router_claims_nickname.nickname_id=innertable.nickname_id) "+
- " AND NOT EXISTS (SELECT *
- FROM router_claims_nickname AS innertable
- WHERE router_claims_nickname.nickname_id=innertable.nickname_id
- AND router_claims_nickname.router_id <> innertable.router_id
- AND last_seen > #{now} - INTERVAL '1 month') ")
- puts "named: #{named}" if verbose
- end
- if __FILE__ == $0
- $db = Db.new($CONFIG['database']['dbhost'], $CONFIG['database']['dbname'], $CONFIG['database']['user'], $CONFIG['database']['password'])
- verbose = ARGV.first == "-v"
- $db.transaction_begin
- do_update verbose
- $db.transaction_commit
- end
|