#!/usr/bin/tclsh
package require sqlite3

set urllog_orig_file "data.urllog"
set urllog_db_file "urllog.sqlite"


proc escape { str } {
	return [string map {' ''} $str]
}

proc lescape { lst pos } {
	return [escape [lindex $lst $pos]]
}


if {[catch {set fd [open $urllog_orig_file r]} uerrmsg]} {
	puts "Could not open '$urllog_orig_file' for reading: $uerrmsg"
	exit 1
}

if {[catch {sqlite3 urldb $urllog_db_file} uerrmsg]} {
	puts "Could not open SQLite3 database '$urllog_db_file': $uerrmsg"
	exit 2
}

puts "Dropping current urls table."
if {[catch {urldb eval {DROP TABLE urls}} uerrmsg]} {
	puts "Dropping table resulted in error (ignored): $uerrmsg"
}

puts "Creating urls table."
if {[catch {urldb eval {CREATE TABLE urls(id INTEGER PRIMARY KEY AUTOINCREMENT, utime INT, url VARCHAR(2048), user VARCHAR(32), host VARCHAR(256), chan VARCHAR(32))}} uerrmsg]} {
	puts "Error creating table! $uerrmsg"
	exit 3
}

puts -nonewline "Detecting database version: "
set nline 0
set minentries 9999
set maxentries 0
while {![eof $fd]} {
	incr nline
	set line [gets $fd]
	if {$line != ""} {
		set items [split $line " "]
		set tmp [llength $items]
		if {$tmp > $maxentries} { set maxentries $tmp }
		if {$tmp < $minentries} { set minentries $tmp }
	}
}

if {$maxentries != 5 || $maxentries != $minentries} {
	puts "old / variable"
} else {
	puts "new"
}

puts "Database contains $nline records, with $minentries / $maxentries entries."

puts -nonewline "Converting database, please wait ... round #1 "
set nline 0
seek $fd 0 start
while {![eof $fd]} {
	incr nline
	set line [gets $fd]
	set items [split $line " "]
	if {$line != ""} {
		set host [lindex $items 3]
		if {[regexp {^\((.+)\)$} $host ures uhost]} {
			set host $uhost
		}
		set uid [lindex $items 4]
		if {$uid != ""} {
			set sql "INSERT INTO urls (id,utime,url,user,host) VALUES ($uid, [lindex $items 1], '[lescape $items 0]', '[lescape $items 2]', '[escape $host]')"
			if {[catch {urldb eval $sql} uerrmsg]} {
				puts "\nError ($nline): $uerrmsg on:\n$sql"
				exit 15
			}
		}
	}
	if {[expr $nline % 10] == 1} {
		puts -nonewline "."
		flush stdout
	}
}
puts "OK"

puts -nonewline "\nRound #2 "
set nline 0
seek $fd 0 start
while {![eof $fd]} {
	incr nline
	set line [gets $fd]
	set items [split $line " "]
	if {$line != ""} {
		set host [lindex $items 3]
		if {[regexp {^\((.+)\)$} $host ures uhost]} {
			set host $uhost
		}
		set uid [lindex $items 4]
		if {$uid == ""} {
			set sql "INSERT INTO urls (utime,url,user,host) VALUES ([lindex $items 1], '[lescape $items 0]', '[lescape $items 2]', '[escape $host]')"
			if {[catch {urldb eval $sql} uerrmsg]} {
				puts "\nError ($nline): $uerrmsg on:\n$sql"
				exit 15
			}
		}
	}
	if {[expr $nline % 10] == 1} {
		puts -nonewline "."
		flush stdout
	}
}

urldb close
close $fd

puts "OK"
puts "New SQLite3 database is in file '$urllog_db_file'"