better/extract.rb

#!/usr/bin/ruby

require 'sqlite3'
require 'json'

db = SQLite3::Database.open '/tmp/tbc.db'
db.results_as_hash = true

items = db.execute 'select i.entry, i.class, i.subclass, i.name, i.inventorytype, i.itemlevel, i.requiredlevel, i.dmg_min1, i.dmg_max1, "" as mobs, delay, %s from item_template i;' % [(1..10).to_a.map { |n| "stat_type#{n} as k#{n}, stat_value#{n} as v#{n}" }.join(', ')]

total = items.length

items_w_loot = []
 
keys = (1..10).map { |n| "k#{n}" }
vals = (1..10).map { |n| "v#{n}" }

skip_keys = keys + ['delay', 'mobs']

def truncate(s, len)
  s.length > len ? "#{s[0...len]}..." : s
end

items.each_with_index do |item, idx|
  mobs = db.execute 'select c.name as name, cl.ChanceOrQuestChance as chance from creature_loot_template cl left join creature_template c on c.entry=cl.entry where cl.item=%d order by chance desc' % [item['entry']]

  new = []

  item.each_pair do |k, v|
    if skip_keys.include?(k) or vals.include?(k)
      next
    end

    new.push k
    new.push v
  end
  
  new.push 'mobs'
  new.push(truncate ((mobs.map {|m| m['name']}.join ',') or ""), 300)

  (0..56).each do |type|
    val = 0
    keys.each_with_index do |k, kidx|
      if item[k] == type
        val = item[vals[kidx]]
      end
    end

    new.push val
  end

  if item['delay']
    new.push item['delay']
  end

  if idx % 100 == 0
    print "\r%f%%" % [(idx * 100).to_f / total]
  end

  if idx > 50
    # break
  end

  File.write(File.join(Dir.home, 'items_w_loot.json'), (new.to_json.concat ",\n"), mode: 'a+')
end