#!/bin/env ruby

require "rubygems"
require "postgres"
require "sqlite3"
require "yaml"

`rm -f /home/albert/miezaru/public/sqlite.db.gz`
`rm -f /home/albert/miezaru/public/sqlite.db`

pg = PGconn.new("127.0.0.1", nil, nil, nil, "danbooru")
sl = SQLite3::Database.new("/home/albert/miezaru/public/sqlite.db")

sl.transaction do |db|
	db.execute("CREATE TABLE cached_tags (tag_id INTEGER, related_tag_id INTEGER, post_count INTEGER)")
	pg.exec("SELECT id, cached_related FROM tags").result.each do |row|
		cached = YAML::load(row[1])
		cached.each do |foo|
			begin
				tag_id = row[0]
				related_tag_id = pg.exec("SELECT id FROM tags WHERE name = '%s'" % foo[0].gsub(/'/, "''"))[0][0]
				post_count = foo[1]
				db.execute("INSERT INTO cached_tags (tag_id, related_tag_id, post_count) VALUES (?, ?, ?)", tag_id, related_tag_id, post_count)
			rescue NoMethodError
			end
		end
	end
end

`gzip /home/albert/miezaru/public/sqlite.db`