Mercurial > hg > egg-tcls

annotate convert_urllog_db.tcl @ 10:fbf718c24de4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add script for converting URLLog text database to SQLite.
author Matti Hamalainen <ccr@tnsp.org>
date Mon, 05 Sep 2011 14:10:07 +0300
parents
children 06f5e5b25930
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
10
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
1 #!/usr/bin/tclsh
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
2 package require sqlite3
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
3
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
4 set urllog_orig_file "data.urllog"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
5 set urllog_db_file "urllog.sqlite"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
6
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
7
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
8 proc escape { str } {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
9 return [string map {' ''} $str]
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
10 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
11
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
12 proc lescape { lst pos } {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
13 return [escape [lindex $lst $pos]]
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
14 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
15
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
16
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
17 if {[catch {set fd [open $urllog_orig_file r]} uerrmsg]} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
18 puts "Could not open '$urllog_orig_file' for reading: $uerrmsg"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
19 exit 1
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
20 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
21
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
22 if {[catch {sqlite3 urldb $urllog_db_file} uerrmsg]} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
23 puts "Could not open SQLite3 database '$urllog_db_file': $uerrmsg"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
24 exit 2
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
25 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
26
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
27 urldb eval "CREATE TABLE urls(id INT, utime INT, url VARCHAR(2048), user VARCHAR(32), host VARCHAR(256))"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
28
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
29 puts -nonewline "Converting database, please wait ..."
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
30 set nline 0
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
31 while {![eof $fd]} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
32 incr nline
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
33 set line [gets $fd]
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
34 set items [split $line " "]
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
35 if {$line != ""} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
36 set host [lindex $items 3]
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
37 if {[regexp {^\((.+)\)$} $host ures uhost]} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
38 set host $uhost
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
39 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
40 set sql "INSERT INTO urls VALUES([lindex $items 4], [lindex $items 1], '[lescape $items 0]', '[lescape $items 2]', '[escape $host]')"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
41 if {[catch {urldb eval $sql} uerrmsg]} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
42 puts "\nError: $uerrmsg on:\n$sql"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
43 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
44 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
45 if {[expr $nline % 10] == 1} {
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
46 puts -nonewline "."
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
47 flush stdout
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
48 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
49 }
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
50 urldb close
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
51 close $fd
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
52
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
53 puts "OK"
fbf718c24de4 Add script for converting URLLog text database to SQLite.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
54 puts "New SQLite3 database is in file '$urllog_db_file'"