Most recent edit on 2007-01-20 13:38:16 by SiddFinch
Additions:
Edited on 2007-01-03 20:39:29 by SiddFinch
Additions:
mek@barker:~/retrosheet/csv$ ../tclkit get-capnkipe-loaded.tcl pgsql
Deletions:
mek@barker:~/retrosheet/csv$ ../tclkit get-capnkipe-loaded pgsql
Edited on 2007-01-03 20:39:10 by SiddFinch
Additions:
%%(tcl;get-capnkipe-loaded.tcl)
Deletions:
%%(tcl;get-capnkipe-loaded)
Edited on 2007-01-03 20:31:40 by SiddFinch
Additions:
~- You'll need to update the cfg(_bin) and cfg(_args) commands to taste for you system.
- The script looks in the directory it is run from for the PSV files.
- You'll also need the latest retrosheet-sql files available from the Files∞ dowload section and store them in the directory that contains the script.
Deletions:
You'll need to update the cfg(_bin) and cfg(_args) commands to taste for you system.
The script looks in the directory it is run from for the PSV files.
You'll also need the latest retrosheet-sql files available from the Files∞ dowload section and store them in the directory that contains the script.
Edited on 2007-01-03 20:31:11 by SiddFinch
Additions:
You'll also need the latest retrosheet-sql files available from the Files∞ dowload section and store them in the directory that contains the script.
Deletions:
You'll also need the latest retrosheet-sql files available from the Files∞ dowload section and store them in the directory that contains the script.
Edited on 2007-01-03 20:20:50 by SiddFinch
Additions:
array set cfg {}
set db_types {}
Categories
Deletions:
array set cfg {}set db_types {}
cd $cdir
}
Edited on 2007-01-03 20:19:36 by SiddFinch
Additions:
You'll also need the latest retrosheet-sql files available from the Files∞ dowload section and store them in the directory that contains the script.
Deletions:
set cdir [pwd]
cd sql
Edited on 2007-01-03 20:18:33 by SiddFinch
Additions:
You'll also need the latest retrosheet-sql files available from the Files∞ dowload section.
Deletions:
You'll also need the sql files available from the Files∞ dowload section.
Edited on 2007-01-03 20:10:36 by SiddFinch
Additions:
}
You'll also need the sql files available from the Files∞ dowload section.
Edited on 2007-01-03 20:07:00 by SiddFinch
Additions:
array set cfg {}set db_types {}
set cmd "$db_exec
\"copy $type from \'$f\' delimiters \'|\'csv;\"\n"
set cmd "$db_exec
\"load data infile \'$f\' into table $type fields terminated by \'|\';\"\n"
Deletions:
array set cfg {}
set cmd "$db_exec
\"copy $type from \'$f\' delimi
ters \'|\'csv;\"\n"
set cmd "$db_exec
\"load data infile \'$f\' into
Edited on 2007-01-03 20:05:49 by SiddFinch
Additions:
set f [file join [pwd ] ${year}-${type}.${ext}]
Deletions:
set f [file join [pwd ] csv ${year}-${type}.${ext}]
Edited on 2007-01-03 19:39:45 by SiddFinch
Additions:
CategorySoftware
Edited on 2007-01-03 19:14:12 by SiddFinch
Additions:
mek@barker:~/retrosheet/csv$ ../tclkit get-capnkipe-loaded pgsql
Deletions:
mek@barker:~/retrosheet$ ./tclkit get-capnkipe-loaded pgsql
Edited on 2007-01-03 19:10:00 by SiddFinch
Additions:
This is a small script I created the load the PSV files created with CapNKipe into MySQL∞, PostgreSQL∞, and Sqlite∞. It requires you have TCL∞ or TCLKIT∞ installed on your system.
Deletions:
This is a small script I created the load the PSV files into MySQL∞, PostgreSQL∞, and Sqlite∞. It requires you have TCL∞ or TCLKIT∞ installed on your system.
Edited on 2007-01-03 19:09:20 by SiddFinch
Additions:
%%(tcl;get-capnkipe-loaded)
mek@barker:~/retrosheet$ ./tclkit get-capnkipe-loaded pgsql
Deletions:
%%(tcl)
Oldest known version of this page was edited on 2007-01-03 19:00:22 by SiddFinch []
Page view:
This is a small script I created the load the
PSV files into
MySQL∞,
PostgreSQL∞, and
Sqlite∞. It requires you have
TCL∞ or
TCLKIT∞ installed on your system.
You'll need to update the cfg(_bin) and cfg(_args) commands to taste for you system.
The script looks in the directory it is run from for the PSV files.
#!/usr/bin/tclsh
array set cfg {}
set db_types {}
set valid_types [list pgsql mysql sqlite3]
set cfg(pgsql_bin) "/usr/bin/psql"
set cfg(pgsql_args) {retrosheet}
set cfg(mysql_bin) "/usr/bin/mysql"
set cfg(mysql_args) {-uroot retrosheet}
set cfg(sqlite3_bin) "/home/mek/bin/sqlite3"
set cfg(sqlite3_args) {/home/mek/retrosheet/db/retrosheet.db}
proc run { command } {
if {[catch {eval $command} err]} {
foreach line [split $err "\n"] {
if {[string length $line] > 0} {
puts stderr $line
}
}
}
}
proc db_cmd {bin args} {
set c exec
lappend c $bin
foreach arg $args {
lappend c $arg
}
return $c
}
proc create_db { cfg db_types } {
upvar $cfg cf
set cdir [pwd]
cd sql
foreach db $db_types {
set db_exec "[db_cmd $cf(${db}_bin) $cf(${db}_args)]"
set cmd "$db_exec < $db-create-database.sql"
puts "Creating tables for $db ... "
run $cmd
}
cd $cdir
}
foreach db $argv {
if {[lsearch -exact $valid_types $db] != -1} {
lappend db_types $db
create_db cfg $db_types
} else {
puts stderr "Type $db invalid, skipping."
}
}
for {set year 1957} {$year <= 2006} {incr year} {
foreach type {games events teams rosters} {
foreach ext {psv} {
set f [file join [pwd ] csv ${year}-${type}.${ext}]
if [file exists $f] {
foreach db $db_types {
set db_exec "[db_cmd $cfg(${db}_bin) $cfg(${db}_args)]"
switch [string toupper $db] {
PGSQL {
set cmd "$db_exec << \"copy $type from \'$f\' delimi
ters \'|\'csv;\"\n"
}
MYSQL {
set cmd "$db_exec << \"load data infile \'$f\' into
table $type fields terminated by \'|\';\"\n"
}
SQLITE3 {
set cmd "$db_exec << \".import \'$f\' $type\"\n"
}
}
puts "Loading $year $type files into $db database ... "
run $cmd
}
}
}
}
}
}
For example to load files into
PostgreSQL, run: