Mercurial > hg > egg-tcls

annotate convert_spede_mn_tuksu.tcl @ 24:ac38d12e3b72

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add conversion script for Spede, MN and Tuksu style quote databases.
author Matti Hamalainen <ccr@tnsp.org>
date Mon, 05 Sep 2011 18:39:03 +0300
parents
children 8a6bfcf1f57b
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
24
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
1 #!/usr/bin/tclsh
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
2 # TCL script for converting Spede, MN and Tuksu databases from
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
3 # flatfile format to SQLite3 database.
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
4 #
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
5 # Written by Matti 'ccr' Hamalainen <ccr@tnsp.org>
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
6 # (C) Copyright 2011 Tecnic Software productions (TNSP)
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
7 #
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
8 package require sqlite3
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
9
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
10 ### Check commandline arguments
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
11 if {$argc != 3} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
12 puts "Usage: $argv0 <input_flat_file_db> <output_sqlite_db_file> <table_name>"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
13 exit 0
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
14 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
15
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
16 set db_input [lindex $argv 0]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
17 set db_output [lindex $argv 1]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
18 set db_table [lindex $argv 2]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
19
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
20
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
21 ### Ask for confirmation
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
22 puts "Conversion of '$db_input' to SQLite database '$db_output', to table '$db_table'."
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
23 puts "NOTICE! This WILL destroy the current data in table '$db_table'!"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
24
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
25 puts -nonewline "Proceed \[y/N\]? "
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
26 flush stdout
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
27 set response [gets stdin]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
28 if {[string tolower $response] != "y"} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
29 puts "OK, aborting."
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
30 exit 0
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
31 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
32
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
33 ### Helper functions
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
34 proc escape { str } {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
35 return [string map {' ''} $str]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
36 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
37
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
38 proc lescape { lst pos } {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
39 return [escape [lindex $lst $pos]]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
40 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
41
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
42 ### Open flatfile for reading
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
43 if {[catch {set fd [open $db_input r]} uerrmsg]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
44 puts "Could not open file '$db_input' for reading: $uerrmsg"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
45 exit 1
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
46 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
47
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
48 ### Open SQLite database
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
49 if {[catch {sqlite3 urldb $db_output} uerrmsg]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
50 puts "Could not open SQLite3 database '$db_output': $uerrmsg"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
51 exit 2
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
52 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
53
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
54 ### Drop old table
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
55 puts "Dropping current '$db_table' table."
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
56 if {[catch {urldb eval "DROP TABLE $db_table"} uerrmsg]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
57 puts "Dropping table resulted in error (ignored): $uerrmsg"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
58 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
59
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
60 ### Create new
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
61 puts "Creating new table '$db_table'."
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
62 if {[catch {urldb eval "CREATE TABLE $db_table (id INTEGER PRIMARY KEY AUTOINCREMENT, utime INT, utext VARCHAR(2048), user VARCHAR(32), host VARCHAR(256), chan VARCHAR(32))"} uerrmsg]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
63 puts "Error creating table! $uerrmsg"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
64 exit 3
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
65 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
66
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
67 ### Convert data
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
68 puts -nonewline "Converting database, please wait "
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
69 set nline 0
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
70 seek $fd 0 start
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
71 while {![eof $fd]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
72 incr nline
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
73 set line [gets $fd]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
74 if {$line != ""} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
75 set items [split $line "|"]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
76 set host [lindex $items 3]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
77 set uid [lindex $items 4]
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
78 set sql "INSERT INTO $db_table (id,utime,utext,user,host,chan) VALUES ([lindex $items 0], [lindex $items 2], '[lescape $items 1]', '[lescape $items 3]', '[lescape $items 4]', '[lescape $items 5]')"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
79 if {[catch {urldb eval $sql} uerrmsg]} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
80 puts "\nError ($nline): $uerrmsg on:\n$sql"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
81 exit 15
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
82 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
83 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
84 if {[expr $nline % 10] == 1} {
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
85 puts -nonewline "."
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
86 flush stdout
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
87 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
88 }
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
89
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
90 urldb close
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
91 close $fd
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
92
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
93 puts "OK"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
94 puts "New SQLite3 database is in file '$db_output'"
ac38d12e3b72 Add conversion script for Spede, MN and Tuksu style quote databases.
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
95