Annotation of embedaddon/sqlite3/tool/shell5.test, revision 1.1
1.1 ! misho 1: # 2010 August 4
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # a legal notice, here is a blessing:
! 5: #
! 6: # May you do good and not evil.
! 7: # May you find forgiveness for yourself and forgive others.
! 8: # May you share freely, never taking more than you give.
! 9: #
! 10: #***********************************************************************
! 11: #
! 12: # The focus of this file is testing the CLI shell tool.
! 13: # These tests are specific to the .import command.
! 14: #
! 15: # $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
! 16: #
! 17:
! 18: # Test plan:
! 19: #
! 20: # shell5-1.*: Basic tests specific to the ".import" command.
! 21: #
! 22:
! 23: set CLI "./sqlite3"
! 24:
! 25: proc do_test {name cmd expected} {
! 26: puts -nonewline "$name ..."
! 27: set res [uplevel $cmd]
! 28: if {$res eq $expected} {
! 29: puts Ok
! 30: } else {
! 31: puts Error
! 32: puts " Got: $res"
! 33: puts " Expected: $expected"
! 34: exit
! 35: }
! 36: }
! 37:
! 38: proc catchcmd {db {cmd ""}} {
! 39: global CLI
! 40: set out [open cmds.txt w]
! 41: puts $out $cmd
! 42: close $out
! 43: set line "exec $CLI $db < cmds.txt"
! 44: set rc [catch { eval $line } msg]
! 45: list $rc $msg
! 46: }
! 47:
! 48: file delete -force test.db test.db.journal
! 49:
! 50: #----------------------------------------------------------------------------
! 51: # Test cases shell5-1.*: Basic handling of the .import and .separator commands.
! 52: #
! 53:
! 54: # .import FILE TABLE Import data from FILE into TABLE
! 55: do_test shell5-1.1.1 {
! 56: catchcmd "test.db" ".import"
! 57: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
! 58: do_test shell5-1.1.2 {
! 59: catchcmd "test.db" ".import FOO"
! 60: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
! 61: do_test shell5-1.1.2 {
! 62: catchcmd "test.db" ".import FOO BAR"
! 63: } {1 {Error: no such table: BAR}}
! 64: do_test shell5-1.1.3 {
! 65: # too many arguments
! 66: catchcmd "test.db" ".import FOO BAR BAD"
! 67: } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}}
! 68:
! 69: # .separator STRING Change separator used by output mode and .import
! 70: do_test shell1-1.2.1 {
! 71: catchcmd "test.db" ".separator"
! 72: } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}}
! 73: do_test shell1-1.2.2 {
! 74: catchcmd "test.db" ".separator FOO"
! 75: } {0 {}}
! 76: do_test shell1-1.2.3 {
! 77: # too many arguments
! 78: catchcmd "test.db" ".separator FOO BAD"
! 79: } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}}
! 80:
! 81: # separator should default to "|"
! 82: do_test shell5-1.3.1 {
! 83: set res [catchcmd "test.db" ".show"]
! 84: list [regexp {separator: \"\|\"} $res]
! 85: } {1}
! 86:
! 87: # set separator to different value.
! 88: # check that .show reports new value
! 89: do_test shell5-1.3.2 {
! 90: set res [catchcmd "test.db" {.separator ,
! 91: .show}]
! 92: list [regexp {separator: \",\"} $res]
! 93: } {1}
! 94:
! 95: # import file doesn't exist
! 96: do_test shell5-1.4.1 {
! 97: file delete -force FOO
! 98: set res [catchcmd "test.db" {CREATE TABLE t1(a, b);
! 99: .import FOO t1}]
! 100: } {1 {Error: cannot open "FOO"}}
! 101:
! 102: # empty import file
! 103: do_test shell5-1.4.2 {
! 104: file delete -force shell5.csv
! 105: set in [open shell5.csv w]
! 106: close $in
! 107: set res [catchcmd "test.db" {.import shell5.csv t1
! 108: SELECT COUNT(*) FROM t1;}]
! 109: } {0 0}
! 110:
! 111: # import file with 1 row, 1 column (expecting 2 cols)
! 112: do_test shell5-1.4.3 {
! 113: set in [open shell5.csv w]
! 114: puts $in "1"
! 115: close $in
! 116: set res [catchcmd "test.db" {.import shell5.csv t1}]
! 117: } {1 {Error: shell5.csv line 1: expected 2 columns of data but found 1}}
! 118:
! 119: # import file with 1 row, 3 columns (expecting 2 cols)
! 120: do_test shell5-1.4.4 {
! 121: set in [open shell5.csv w]
! 122: puts $in "1|2|3"
! 123: close $in
! 124: set res [catchcmd "test.db" {.import shell5.csv t1}]
! 125: } {1 {Error: shell5.csv line 1: expected 2 columns of data but found 3}}
! 126:
! 127: # import file with 1 row, 2 columns
! 128: do_test shell5-1.4.5 {
! 129: set in [open shell5.csv w]
! 130: puts $in "1|2"
! 131: close $in
! 132: set res [catchcmd "test.db" {.import shell5.csv t1
! 133: SELECT COUNT(*) FROM t1;}]
! 134: } {0 1}
! 135:
! 136: # import file with 2 rows, 2 columns
! 137: # note we end up with 3 rows because of the 1 row
! 138: # imported above.
! 139: do_test shell5-1.4.6 {
! 140: set in [open shell5.csv w]
! 141: puts $in "2|3"
! 142: puts $in "3|4"
! 143: close $in
! 144: set res [catchcmd "test.db" {.import shell5.csv t1
! 145: SELECT COUNT(*) FROM t1;}]
! 146: } {0 3}
! 147:
! 148: # import file with 1 row, 2 columns, using a comma
! 149: do_test shell5-1.4.7 {
! 150: set in [open shell5.csv w]
! 151: puts $in "4,5"
! 152: close $in
! 153: set res [catchcmd "test.db" {.separator ,
! 154: .import shell5.csv t1
! 155: SELECT COUNT(*) FROM t1;}]
! 156: } {0 4}
! 157:
! 158: # import file with 1 row, 2 columns, text data
! 159: do_test shell5-1.4.8.1 {
! 160: set in [open shell5.csv w]
! 161: puts $in "5|Now is the time for all good men to come to the aid of their country."
! 162: close $in
! 163: set res [catchcmd "test.db" {.import shell5.csv t1
! 164: SELECT COUNT(*) FROM t1;}]
! 165: } {0 5}
! 166:
! 167: do_test shell5-1.4.8.2 {
! 168: catchcmd "test.db" {SELECT b FROM t1 WHERE a='5';}
! 169: } {0 {Now is the time for all good men to come to the aid of their country.}}
! 170:
! 171: # import file with 1 row, 2 columns, quoted text data
! 172: # note that currently sqlite doesn't support quoted fields, and
! 173: # imports the entire field, quotes and all.
! 174: do_test shell5-1.4.9.1 {
! 175: set in [open shell5.csv w]
! 176: puts $in "6|'Now is the time for all good men to come to the aid of their country.'"
! 177: close $in
! 178: set res [catchcmd "test.db" {.import shell5.csv t1
! 179: SELECT COUNT(*) FROM t1;}]
! 180: } {0 6}
! 181:
! 182: do_test shell5-1.4.9.2 {
! 183: catchcmd "test.db" {SELECT b FROM t1 WHERE a='6';}
! 184: } {0 {'Now is the time for all good men to come to the aid of their country.'}}
! 185:
! 186: # import file with 1 row, 2 columns, quoted text data
! 187: do_test shell5-1.4.10.1 {
! 188: set in [open shell5.csv w]
! 189: puts $in "7|\"Now is the time for all good men to come to the aid of their country.\""
! 190: close $in
! 191: set res [catchcmd "test.db" {.import shell5.csv t1
! 192: SELECT COUNT(*) FROM t1;}]
! 193: } {0 7}
! 194:
! 195: do_test shell5-1.4.10.2 {
! 196: catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
! 197: } {0 {"Now is the time for all good men to come to the aid of their country."}}
! 198:
! 199: # check importing very long field
! 200: do_test shell5-1.5.1 {
! 201: set str [string repeat X 999]
! 202: set in [open shell5.csv w]
! 203: puts $in "8|$str"
! 204: close $in
! 205: set res [catchcmd "test.db" {.import shell5.csv t1
! 206: SELECT length(b) FROM t1 WHERE a='8';}]
! 207: } {0 999}
! 208:
! 209: # try importing into a table with a large number of columns.
! 210: # This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.
! 211: set cols 999
! 212: do_test shell5-1.6.1 {
! 213: set sql {CREATE TABLE t2(}
! 214: set data {}
! 215: for {set i 1} {$i<$cols} {incr i} {
! 216: append sql "c$i,"
! 217: append data "$i|"
! 218: }
! 219: append sql "c$cols);"
! 220: append data "$cols"
! 221: catchcmd "test.db" $sql
! 222: set in [open shell5.csv w]
! 223: puts $in $data
! 224: close $in
! 225: set res [catchcmd "test.db" {.import shell5.csv t2
! 226: SELECT COUNT(*) FROM t2;}]
! 227: } {0 1}
! 228:
! 229: # try importing a large number of rows
! 230: set rows 999999
! 231: do_test shell5-1.7.1 {
! 232: set in [open shell5.csv w]
! 233: for {set i 1} {$i<=$rows} {incr i} {
! 234: puts $in $i
! 235: }
! 236: close $in
! 237: set res [catchcmd "test.db" {CREATE TABLE t3(a);
! 238: .import shell5.csv t3
! 239: SELECT COUNT(*) FROM t3;}]
! 240: } [list 0 $rows]
! 241:
! 242:
! 243: puts "CLI tests completed successfully"
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to shell5.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool