File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / capi2.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    1: # 2003 January 29
    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: # This file implements regression tests for SQLite library.  The
   12: # focus of this script testing the callback-free C/C++ API.
   13: #
   14: # $Id: capi2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   15: #
   16: 
   17: set testdir [file dirname $argv0]
   18: source $testdir/tester.tcl
   19: 
   20: # Return the text values from the current row pointed at by STMT as a list.
   21: proc get_row_values {STMT} {
   22:   set VALUES [list]
   23:   for {set i 0} {$i < [sqlite3_data_count $STMT]} {incr i} {
   24:     lappend VALUES [sqlite3_column_text $STMT $i]
   25:   }
   26:   return $VALUES
   27: }
   28: 
   29: # Return the column names followed by declaration types for the result set
   30: # of the SQL statement STMT.
   31: #
   32: # i.e. for:
   33: # CREATE TABLE abc(a text, b integer); 
   34: # SELECT * FROM abc;
   35: #
   36: # The result is {a b text integer}
   37: proc get_column_names {STMT} {
   38:   set VALUES [list]
   39:   for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
   40:     lappend VALUES [sqlite3_column_name $STMT $i]
   41:   }
   42:   for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
   43:     lappend VALUES [sqlite3_column_decltype $STMT $i]
   44:   }
   45:   return $VALUES
   46: }
   47: 
   48: # Check basic functionality
   49: #
   50: do_test capi2-1.1 {
   51:   set DB [sqlite3_connection_pointer db]
   52:   execsql {CREATE TABLE t1(a,b,c)}
   53:   set VM [sqlite3_prepare $DB {SELECT name, rowid FROM sqlite_master} -1 TAIL]
   54:   set TAIL
   55: } {}
   56: do_test capi2-1.2 {
   57:   sqlite3_step $VM
   58: } {SQLITE_ROW}
   59: do_test capi2-1.3 {
   60:   sqlite3_data_count $VM
   61: } {2}
   62: do_test capi2-1.4 {
   63:   get_row_values $VM
   64: } {t1 1}
   65: do_test capi2-1.5 {
   66:   get_column_names $VM
   67: } {name rowid text INTEGER}
   68: do_test capi2-1.6 {
   69:   sqlite3_step $VM 
   70: } {SQLITE_DONE}
   71: do_test capi2-1.7 {
   72:   list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
   73: } {2 {} {name rowid text INTEGER}}
   74: 
   75: # This used to be SQLITE_MISUSE.  But now we automatically reset prepared
   76: # statements.
   77: ifcapable autoreset {
   78:   do_test capi2-1.8 {
   79:     sqlite3_step $VM
   80:   } {SQLITE_ROW}
   81: } else {
   82:   do_test capi2-1.8 {
   83:     sqlite3_step $VM
   84:   } {SQLITE_MISUSE}
   85: }
   86: 
   87: # Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot
   88: # be interrogated for more information. However in v3, since the column
   89: # count, names and types are determined at compile time, these are still
   90: # accessible after an SQLITE_MISUSE error.
   91: do_test capi2-1.9 {
   92:   sqlite3_reset $VM
   93:   list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
   94: } {2 {} {name rowid text INTEGER}}
   95: do_test capi2-1.10 {
   96:   sqlite3_data_count $VM
   97: } {0}
   98: 
   99: do_test capi2-1.11 {
  100:   sqlite3_finalize $VM
  101: } {SQLITE_OK}
  102: 
  103: # Check to make sure that the "tail" of a multi-statement SQL script
  104: # is returned by sqlite3_prepare.
  105: #
  106: do_test capi2-2.1 {
  107:   set SQL {
  108:     SELECT name, rowid FROM sqlite_master;
  109:     SELECT name, rowid FROM sqlite_master WHERE 0;
  110:     -- A comment at the end
  111:   }
  112:   set VM [sqlite3_prepare $DB $SQL -1 SQL]
  113:   set SQL
  114: } {
  115:     SELECT name, rowid FROM sqlite_master WHERE 0;
  116:     -- A comment at the end
  117:   }
  118: do_test capi2-2.2 {
  119:   set r [sqlite3_step $VM]
  120:   lappend r [sqlite3_column_count $VM] \
  121:             [get_row_values $VM] \
  122:             [get_column_names $VM]
  123: } {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}}
  124: do_test capi2-2.3 {
  125:   set r [sqlite3_step $VM]
  126:   lappend r [sqlite3_column_count $VM] \
  127:             [get_row_values $VM] \
  128:             [get_column_names $VM]
  129: } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
  130: do_test capi2-2.4 {
  131:   sqlite3_finalize $VM
  132: } {SQLITE_OK}
  133: do_test capi2-2.5 {
  134:   set VM [sqlite3_prepare $DB $SQL -1 SQL]
  135:   set SQL
  136: } {
  137:     -- A comment at the end
  138:   }
  139: do_test capi2-2.6 {
  140:   set r [sqlite3_step $VM]
  141:   lappend r [sqlite3_column_count $VM] \
  142:             [get_row_values $VM] \
  143:             [get_column_names $VM]
  144: } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
  145: do_test capi2-2.7 {
  146:   sqlite3_finalize $VM
  147: } {SQLITE_OK}
  148: do_test capi2-2.8 {
  149:   set VM [sqlite3_prepare $DB $SQL -1 SQL]
  150:   list $SQL $VM
  151: } {{} {}}
  152: 
  153: # Check the error handling.
  154: #
  155: do_test capi2-3.1 {
  156:   set rc [catch {
  157:       sqlite3_prepare $DB {select bogus from sqlite_master} -1 TAIL
  158:   } msg]
  159:   lappend rc $msg $TAIL
  160: } {1 {(1) no such column: bogus} {}}
  161: do_test capi2-3.2 {
  162:   set rc [catch {
  163:       sqlite3_prepare $DB {select bogus from } -1 TAIL
  164:   } msg]
  165:   lappend rc $msg $TAIL
  166: } {1 {(1) near " ": syntax error} {}}
  167: do_test capi2-3.3 {
  168:   set rc [catch {
  169:       sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL
  170:   } msg]
  171:   lappend rc $msg $TAIL
  172: } {1 {(1) no such column: bogus} {}}
  173: do_test capi2-3.4 {
  174:   set rc [catch {
  175:       sqlite3_prepare $DB {select bogus from sqlite_master;x;} -1 TAIL
  176:   } msg]
  177:   lappend rc $msg $TAIL
  178: } {1 {(1) no such column: bogus} {x;}}
  179: do_test capi2-3.5 {
  180:   set rc [catch {
  181:       sqlite3_prepare $DB {select bogus from sqlite_master;;;x;} -1 TAIL
  182:   } msg]
  183:   lappend rc $msg $TAIL
  184: } {1 {(1) no such column: bogus} {;;x;}}
  185: do_test capi2-3.6 {
  186:   set rc [catch {
  187:       sqlite3_prepare $DB {select 5/0} -1 TAIL
  188:   } VM]
  189:   lappend rc $TAIL
  190: } {0 {}}
  191: do_test capi2-3.7 {
  192:   list [sqlite3_step $VM] \
  193:        [sqlite3_column_count $VM] \
  194:        [get_row_values $VM] \
  195:        [get_column_names $VM]
  196: } {SQLITE_ROW 1 {{}} {5/0 {}}}
  197: do_test capi2-3.8 {
  198:   sqlite3_finalize $VM
  199: } {SQLITE_OK}
  200: do_test capi2-3.9 {
  201:   execsql {CREATE UNIQUE INDEX i1 ON t1(a)}
  202:   set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,2,3)} -1 TAIL]
  203:   set TAIL
  204: } {}
  205: do_test capi2-3.9b {db changes} {0}
  206: do_test capi2-3.10 {
  207:   list [sqlite3_step $VM] \
  208:        [sqlite3_column_count $VM] \
  209:        [get_row_values $VM] \
  210:        [get_column_names $VM]
  211: } {SQLITE_DONE 0 {} {}}
  212: 
  213: # Update for v3 - the change has not actually happened until the query is
  214: # finalized. Is this going to cause trouble for anyone? Lee Nelson maybe?
  215: # (Later:) The change now happens just before SQLITE_DONE is returned.
  216: do_test capi2-3.10b {db changes} {1}
  217: do_test capi2-3.11 {
  218:   sqlite3_finalize $VM
  219: } {SQLITE_OK}
  220: do_test capi2-3.11b {db changes} {1}
  221: #do_test capi2-3.12-misuse {
  222: #  sqlite3_finalize $VM
  223: #} {SQLITE_MISUSE}
  224: do_test capi2-3.13 {
  225:   set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,3,4)} -1 TAIL]
  226:   list [sqlite3_step $VM] \
  227:        [sqlite3_column_count $VM] \
  228:        [get_row_values $VM] \
  229:        [get_column_names $VM]
  230: } {SQLITE_ERROR 0 {} {}}
  231: 
  232: # Update for v3: Preparing a statement does not affect the change counter.
  233: # (Test result changes from 0 to 1).  (Later:) change counter updates occur
  234: # when sqlite3_step returns, not at finalize time.
  235: do_test capi2-3.13b {db changes} {0}
  236: 
  237: do_test capi2-3.14 {
  238:   list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
  239: } {SQLITE_CONSTRAINT {column a is not unique}}
  240: do_test capi2-3.15 {
  241:   set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
  242:   set TAIL
  243: } {}
  244: do_test capi2-3.16 {
  245:   list [sqlite3_step $VM] \
  246:        [sqlite3_column_count $VM] \
  247:        [get_row_values $VM] \
  248:        [get_column_names $VM]
  249: } {SQLITE_DONE 0 {} {}}
  250: do_test capi2-3.17 {
  251:   list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
  252: } {SQLITE_OK {not an error}}
  253: do_test capi2-3.18 {
  254:   set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]
  255:   list [sqlite3_step $VM] \
  256:        [sqlite3_column_count $VM] \
  257:        [get_row_values $VM] \
  258:        [get_column_names $VM]
  259: } {SQLITE_ERROR 0 {} {}}
  260: do_test capi2-3.19 {
  261:   list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
  262: } {SQLITE_CONSTRAINT {t2.a may not be NULL}}
  263: 
  264: do_test capi2-3.20 {
  265:   execsql {
  266:     CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
  267:     INSERT INTO a1 VALUES(1, 1);
  268:   }
  269: } {}
  270: do_test capi2-3.21 {
  271:   set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL]
  272:   sqlite3_step $VM
  273: } {SQLITE_ERROR}
  274: do_test capi2-3.22 {
  275:   sqlite3_errcode $DB
  276: } {SQLITE_ERROR}
  277: do_test capi2-3.23 {
  278:   sqlite3_finalize $VM
  279: } {SQLITE_CONSTRAINT}
  280: do_test capi2-3.24 {
  281:   sqlite3_errcode $DB
  282: } {SQLITE_CONSTRAINT}
  283: 
  284: # Two or more virtual machines exists at the same time.
  285: #
  286: do_test capi2-4.1 {
  287:   set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]
  288:   set TAIL
  289: } {}
  290: do_test capi2-4.2 {
  291:   set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]
  292:   set TAIL
  293: } {}
  294: do_test capi2-4.3 {
  295:   set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL]
  296:   set TAIL
  297: } {}
  298: do_test capi2-4.4 {
  299:   list [sqlite3_step $VM2] \
  300:        [sqlite3_column_count $VM2] \
  301:        [get_row_values $VM2] \
  302:        [get_column_names $VM2]
  303: } {SQLITE_DONE 0 {} {}}
  304: do_test capi2-4.5 {
  305:   execsql {SELECT * FROM t2 ORDER BY a}
  306: } {2 3}
  307: do_test capi2-4.6 {
  308:   sqlite3_finalize $VM2
  309: } {SQLITE_OK}
  310: do_test capi2-4.7 {
  311:   list [sqlite3_step $VM3] \
  312:        [sqlite3_column_count $VM3] \
  313:        [get_row_values $VM3] \
  314:        [get_column_names $VM3]
  315: } {SQLITE_DONE 0 {} {}}
  316: do_test capi2-4.8 {
  317:   execsql {SELECT * FROM t2 ORDER BY a}
  318: } {2 3 3 4}
  319: do_test capi2-4.9 {
  320:   sqlite3_finalize $VM3
  321: } {SQLITE_OK}
  322: do_test capi2-4.10 {
  323:   list [sqlite3_step $VM1] \
  324:        [sqlite3_column_count $VM1] \
  325:        [get_row_values $VM1] \
  326:        [get_column_names $VM1]
  327: } {SQLITE_DONE 0 {} {}}
  328: do_test capi2-4.11 {
  329:   execsql {SELECT * FROM t2 ORDER BY a}
  330: } {1 2 2 3 3 4}
  331: do_test capi2-4.12 {
  332:   sqlite3_finalize $VM1
  333: } {SQLITE_OK}
  334: 
  335: # Interleaved SELECTs
  336: #
  337: do_test capi2-5.1 {
  338:   set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
  339:   set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
  340:   set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
  341:   list [sqlite3_step $VM1] \
  342:        [sqlite3_column_count $VM1] \
  343:        [get_row_values $VM1] \
  344:        [get_column_names $VM1]
  345: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
  346: do_test capi2-5.2 {
  347:   list [sqlite3_step $VM2] \
  348:        [sqlite3_column_count $VM2] \
  349:        [get_row_values $VM2] \
  350:        [get_column_names $VM2]
  351: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
  352: do_test capi2-5.3 {
  353:   list [sqlite3_step $VM1] \
  354:        [sqlite3_column_count $VM1] \
  355:        [get_row_values $VM1] \
  356:        [get_column_names $VM1]
  357: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
  358: do_test capi2-5.4 {
  359:   list [sqlite3_step $VM3] \
  360:        [sqlite3_column_count $VM3] \
  361:        [get_row_values $VM3] \
  362:        [get_column_names $VM3]
  363: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
  364: do_test capi2-5.5 {
  365:   list [sqlite3_step $VM3] \
  366:        [sqlite3_column_count $VM3] \
  367:        [get_row_values $VM3] \
  368:        [get_column_names $VM3]
  369: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
  370: do_test capi2-5.6 {
  371:   list [sqlite3_step $VM3] \
  372:        [sqlite3_column_count $VM3] \
  373:        [get_row_values $VM3] \
  374:        [get_column_names $VM3]
  375: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
  376: do_test capi2-5.7 {
  377:   list [sqlite3_step $VM3] \
  378:        [sqlite3_column_count $VM3] \
  379:        [get_row_values $VM3] \
  380:        [get_column_names $VM3]
  381: } {SQLITE_DONE 2 {} {a b {} {}}}
  382: do_test capi2-5.8 {
  383:   sqlite3_finalize $VM3
  384: } {SQLITE_OK}
  385: do_test capi2-5.9 {
  386:   list [sqlite3_step $VM1] \
  387:        [sqlite3_column_count $VM1] \
  388:        [get_row_values $VM1] \
  389:        [get_column_names $VM1]
  390: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
  391: do_test capi2-5.10 {
  392:   sqlite3_finalize $VM1
  393: } {SQLITE_OK}
  394: do_test capi2-5.11 {
  395:   list [sqlite3_step $VM2] \
  396:        [sqlite3_column_count $VM2] \
  397:        [get_row_values $VM2] \
  398:        [get_column_names $VM2]
  399: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
  400: do_test capi2-5.12 {
  401:   list [sqlite3_step $VM2] \
  402:        [sqlite3_column_count $VM2] \
  403:        [get_row_values $VM2] \
  404:        [get_column_names $VM2]
  405: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
  406: do_test capi2-5.11 {
  407:   sqlite3_finalize $VM2
  408: } {SQLITE_OK}
  409: 
  410: # Check for proper SQLITE_BUSY returns.
  411: #
  412: do_test capi2-6.1 {
  413:   execsql {
  414:     BEGIN;
  415:     CREATE TABLE t3(x counter);
  416:     INSERT INTO t3 VALUES(1);
  417:     INSERT INTO t3 VALUES(2);
  418:     INSERT INTO t3 SELECT x+2 FROM t3;
  419:     INSERT INTO t3 SELECT x+4 FROM t3;
  420:     INSERT INTO t3 SELECT x+8 FROM t3;
  421:     COMMIT;
  422:   }
  423:   set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL]
  424:   sqlite3 db2 test.db
  425:   execsql {BEGIN} db2
  426: } {}
  427: # Update for v3: BEGIN doesn't write-lock the database. It is quite
  428: # difficult to get v3 to write-lock the database, which causes a few
  429: # problems for test scripts.
  430: #
  431: # do_test capi2-6.2 {
  432: #   list [sqlite3_step $VM1] \
  433: #        [sqlite3_column_count $VM1] \
  434: #        [get_row_values $VM1] \
  435: #        [get_column_names $VM1]
  436: # } {SQLITE_BUSY 0 {} {}}
  437: do_test capi2-6.3 {
  438:   execsql {COMMIT} db2
  439: } {}
  440: do_test capi2-6.4 {
  441:   list [sqlite3_step $VM1] \
  442:        [sqlite3_column_count $VM1] \
  443:        [get_row_values $VM1] \
  444:        [get_column_names $VM1]
  445: } {SQLITE_ROW 1 1 {x counter}}
  446: do_test capi2-6.5 {
  447:   catchsql {INSERT INTO t3 VALUES(10);} db2
  448: } {1 {database is locked}}
  449: do_test capi2-6.6 {
  450:   list [sqlite3_step $VM1] \
  451:        [sqlite3_column_count $VM1] \
  452:        [get_row_values $VM1] \
  453:        [get_column_names $VM1]
  454: } {SQLITE_ROW 1 2 {x counter}}
  455: do_test capi2-6.7 {
  456:   execsql {SELECT * FROM t2} db2
  457: } {2 3 3 4 1 2}
  458: do_test capi2-6.8 {
  459:   list [sqlite3_step $VM1] \
  460:        [sqlite3_column_count $VM1] \
  461:        [get_row_values $VM1] \
  462:        [get_column_names $VM1]
  463: } {SQLITE_ROW 1 3 {x counter}}
  464: do_test capi2-6.9 {
  465:   execsql {SELECT * FROM t2} 
  466: } {2 3 3 4 1 2}
  467: do_test capi2-6.10 {
  468:   list [sqlite3_step $VM1] \
  469:        [sqlite3_column_count $VM1] \
  470:        [get_row_values $VM1] \
  471:        [get_column_names $VM1]
  472: } {SQLITE_ROW 1 4 {x counter}}
  473: do_test capi2-6.11 {
  474:   execsql {BEGIN}
  475: } {}
  476: do_test capi2-6.12 {
  477:   list [sqlite3_step $VM1] \
  478:        [sqlite3_column_count $VM1] \
  479:        [get_row_values $VM1] \
  480:        [get_column_names $VM1]
  481: } {SQLITE_ROW 1 5 {x counter}}
  482: 
  483: # A read no longer blocks a write in the same connection.
  484: #do_test capi2-6.13 {
  485: #  catchsql {UPDATE t3 SET x=x+1}
  486: #} {1 {database table is locked}}
  487: 
  488: do_test capi2-6.14 {
  489:   list [sqlite3_step $VM1] \
  490:        [sqlite3_column_count $VM1] \
  491:        [get_row_values $VM1] \
  492:        [get_column_names $VM1]
  493: } {SQLITE_ROW 1 6 {x counter}}
  494: do_test capi2-6.15 {
  495:   execsql {SELECT * FROM t1}
  496: } {1 2 3}
  497: do_test capi2-6.16 {
  498:   list [sqlite3_step $VM1] \
  499:        [sqlite3_column_count $VM1] \
  500:        [get_row_values $VM1] \
  501:        [get_column_names $VM1]
  502: } {SQLITE_ROW 1 7 {x counter}}
  503: do_test capi2-6.17 {
  504:   catchsql {UPDATE t1 SET b=b+1}
  505: } {0 {}}
  506: do_test capi2-6.18 {
  507:   list [sqlite3_step $VM1] \
  508:        [sqlite3_column_count $VM1] \
  509:        [get_row_values $VM1] \
  510:        [get_column_names $VM1]
  511: } {SQLITE_ROW 1 8 {x counter}}
  512: do_test capi2-6.19 {
  513:   execsql {SELECT * FROM t1}
  514: } {1 3 3}
  515: do_test capi2-6.20 {
  516:   list [sqlite3_step $VM1] \
  517:        [sqlite3_column_count $VM1] \
  518:        [get_row_values $VM1] \
  519:        [get_column_names $VM1]
  520: } {SQLITE_ROW 1 9 {x counter}}
  521: #do_test capi2-6.21 {
  522: #  execsql {ROLLBACK; SELECT * FROM t1}
  523: #} {1 2 3}
  524: do_test capi2-6.22 {
  525:   list [sqlite3_step $VM1] \
  526:        [sqlite3_column_count $VM1] \
  527:        [get_row_values $VM1] \
  528:        [get_column_names $VM1]
  529: } {SQLITE_ROW 1 10 {x counter}}
  530: #do_test capi2-6.23 {
  531: #  execsql {BEGIN TRANSACTION;}
  532: #} {}
  533: do_test capi2-6.24 {
  534:   list [sqlite3_step $VM1] \
  535:        [sqlite3_column_count $VM1] \
  536:        [get_row_values $VM1] \
  537:        [get_column_names $VM1]
  538: } {SQLITE_ROW 1 11 {x counter}}
  539: do_test capi2-6.25 {
  540:   execsql {
  541:     INSERT INTO t1 VALUES(2,3,4);
  542:     SELECT * FROM t1;
  543:   }
  544: } {1 3 3 2 3 4}
  545: do_test capi2-6.26 {
  546:   list [sqlite3_step $VM1] \
  547:        [sqlite3_column_count $VM1] \
  548:        [get_row_values $VM1] \
  549:        [get_column_names $VM1]
  550: } {SQLITE_ROW 1 12 {x counter}}
  551: do_test capi2-6.27 {
  552:   catchsql {
  553:     INSERT INTO t1 VALUES(2,4,5);
  554:     SELECT * FROM t1;
  555:   }
  556: } {1 {column a is not unique}}
  557: do_test capi2-6.28 {
  558:   list [sqlite3_step $VM1] \
  559:        [sqlite3_column_count $VM1] \
  560:        [get_row_values $VM1] \
  561:        [get_column_names $VM1]
  562: } {SQLITE_ROW 1 13 {x counter}}
  563: do_test capi2-6.99 {
  564:   sqlite3_finalize $VM1
  565: } {SQLITE_OK}
  566: catchsql {ROLLBACK}
  567: 
  568: do_test capi2-7.1 {
  569:   stepsql $DB {
  570:     SELECT * FROM t1
  571:   }
  572: } {0 1 2 3}
  573: do_test capi2-7.2 {
  574:   stepsql $DB {
  575:     PRAGMA count_changes=on
  576:   }
  577: } {0}
  578: do_test capi2-7.3 {
  579:   stepsql $DB {
  580:     UPDATE t1 SET a=a+10;
  581:   }
  582: } {0 1}
  583: do_test capi2-7.4 {
  584:   stepsql $DB {
  585:     INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1;
  586:   }
  587: } {0 1}
  588: do_test capi2-7.4b {sqlite3_changes $DB} {1}
  589: do_test capi2-7.5 {
  590:   stepsql $DB {
  591:     UPDATE t1 SET a=a+10;
  592:   }
  593: } {0 2}
  594: do_test capi2-7.5b {sqlite3_changes $DB} {2}
  595: do_test capi2-7.6 {
  596:   stepsql $DB {
  597:     SELECT * FROM t1;
  598:   }
  599: } {0 21 2 3 22 3 4}
  600: do_test capi2-7.7 {
  601:   stepsql $DB {
  602:     INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1;
  603:   }
  604: } {0 2}
  605: do_test capi2-7.8 {
  606:   sqlite3_changes $DB
  607: } {2}
  608: do_test capi2-7.9 {
  609:   stepsql $DB {
  610:     SELECT * FROM t1;
  611:   }
  612: } {0 21 2 3 22 3 4 23 4 5 24 5 6}
  613: do_test capi2-7.10 {
  614:   stepsql $DB {
  615:     UPDATE t1 SET a=a-20;
  616:     SELECT * FROM t1;
  617:   }
  618: } {0 4 1 2 3 2 3 4 3 4 5 4 5 6}
  619: 
  620: # Update for version 3: A SELECT statement no longer resets the change
  621: # counter (Test result changes from 0 to 4).
  622: do_test capi2-7.11 {
  623:   sqlite3_changes $DB
  624: } {4}
  625: do_test capi2-7.11a {
  626:   execsql {SELECT count(*) FROM t1}
  627: } {4}
  628: 
  629: ifcapable {explain} {
  630:   do_test capi2-7.12 {
  631:     set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
  632:     lindex $x 0
  633:   } {0}
  634: }
  635: 
  636: # Ticket #261 - make sure we can finalize before the end of a query.
  637: #
  638: do_test capi2-8.1 {
  639:   set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
  640:   sqlite3_finalize $VM1
  641: } {SQLITE_OK}
  642:   
  643: # Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare
  644: # and all of the return pointers in sqlite_step can be null.
  645: #
  646: do_test capi2-9.1 {
  647:   set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY]
  648:   sqlite3_step $VM1
  649:   sqlite3_finalize $VM1
  650: } {SQLITE_OK}
  651: 
  652: # Test that passing a NULL pointer to sqlite3_finalize() or sqlite3_reset
  653: # does not cause an error.
  654: do_test capi2-10.1 {
  655:   sqlite3_finalize 0
  656: } {SQLITE_OK}
  657: do_test capi2-10.2 {
  658:   sqlite3_reset 0
  659: } {SQLITE_OK}
  660: 
  661: #---------------------------------------------------------------------------
  662: # The following tests - capi2-11.* - test the "column origin" APIs.
  663: #
  664: #   sqlite3_column_origin_name()
  665: #   sqlite3_column_database_name()
  666: #   sqlite3_column_table_name()
  667: #
  668: 
  669: ifcapable columnmetadata {
  670: 
  671: # This proc uses the database handle $::DB to compile the SQL statement passed
  672: # as a parameter. The return value of this procedure is a list with one
  673: # element for each column returned by the compiled statement. Each element of
  674: # this list is itself a list of length three, consisting of the origin
  675: # database, table and column for the corresponding returned column.
  676: proc check_origins {sql} {
  677:   set ret [list]
  678:   set ::STMT [sqlite3_prepare $::DB $sql -1 dummy]
  679:   for {set i 0} {$i < [sqlite3_column_count $::STMT]} {incr i} {
  680:     lappend ret [list                           \
  681:       [sqlite3_column_database_name $::STMT $i] \
  682:       [sqlite3_column_table_name $::STMT $i]    \
  683:       [sqlite3_column_origin_name $::STMT $i]   \
  684:     ]
  685:   }
  686:   sqlite3_finalize $::STMT
  687:   return $ret
  688: }
  689: do_test capi2-11.1 {
  690:   execsql {
  691:     CREATE TABLE tab1(col1, col2);
  692:   }
  693: } {}
  694: do_test capi2-11.2 {
  695:   check_origins {SELECT col2, col1 FROM tab1}
  696: } [list {main tab1 col2} {main tab1 col1}]
  697: do_test capi2-11.3 {
  698:   check_origins {SELECT col2 AS hello, col1 AS world FROM tab1}
  699: } [list {main tab1 col2} {main tab1 col1}]
  700: 
  701: ifcapable subquery {
  702:   do_test capi2-11.4 {
  703:     check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM tab1)}
  704:   } [list {main tab1 col2} {main tab1 col1}]
  705:   do_test capi2-11.5 {
  706:     check_origins {SELECT (SELECT col2 FROM tab1), (SELECT col1 FROM tab1)}
  707:   } [list {main tab1 col2} {main tab1 col1}]
  708:   do_test capi2-11.6 {
  709:     check_origins {SELECT (SELECT col2), (SELECT col1) FROM tab1}
  710:   } [list {main tab1 col2} {main tab1 col1}]
  711:   do_test capi2-11.7 {
  712:     check_origins {SELECT * FROM tab1}
  713:   } [list {main tab1 col1} {main tab1 col2}]
  714:   do_test capi2-11.8 {
  715:     check_origins {SELECT * FROM (SELECT * FROM tab1)}
  716:   } [list {main tab1 col1} {main tab1 col2}]
  717: }
  718: 
  719: ifcapable view&&subquery {
  720:   do_test capi2-12.1 {
  721:     execsql {
  722:       CREATE VIEW view1 AS SELECT * FROM  tab1;
  723:     }
  724:   } {}
  725:   do_test capi2-12.2 {
  726:     check_origins {SELECT col2, col1 FROM view1}
  727:   } [list {main tab1 col2} {main tab1 col1}]
  728:   do_test capi2-12.3 {
  729:     check_origins {SELECT col2 AS hello, col1 AS world FROM view1}
  730:   } [list {main tab1 col2} {main tab1 col1}]
  731:   do_test capi2-12.4 {
  732:     check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view1)}
  733:   } [list {main tab1 col2} {main tab1 col1}]
  734:   do_test capi2-12.5 {
  735:     check_origins {SELECT (SELECT col2 FROM view1), (SELECT col1 FROM view1)}
  736:   } [list {main tab1 col2} {main tab1 col1}]
  737:   do_test capi2-12.6 {
  738:     check_origins {SELECT (SELECT col2), (SELECT col1) FROM view1}
  739:   } [list {main tab1 col2} {main tab1 col1}]
  740:   do_test capi2-12.7 {
  741:     check_origins {SELECT * FROM view1}
  742:   } [list {main tab1 col1} {main tab1 col2}]
  743:   do_test capi2-12.8 {
  744:     check_origins {select * from (select * from view1)}
  745:   } [list {main tab1 col1} {main tab1 col2}]
  746:   do_test capi2-12.9 {
  747:     check_origins {select * from (select * from (select * from view1))}
  748:   } [list {main tab1 col1} {main tab1 col2}]
  749:   do_test capi2-12.10 {
  750:     db close
  751:     sqlite3 db test.db
  752:     set ::DB [sqlite3_connection_pointer db]
  753:     check_origins {select * from (select * from (select * from view1))}
  754:   } [list {main tab1 col1} {main tab1 col2}]
  755:   
  756:   # This view will thwart the flattening optimization.
  757:   do_test capi2-13.1 {
  758:     execsql {
  759:       CREATE VIEW view2 AS SELECT * FROM tab1 limit 10 offset 10;
  760:     }
  761:   } {}
  762:   do_test capi2-13.2 {
  763:     check_origins {SELECT col2, col1 FROM view2}
  764:   } [list {main tab1 col2} {main tab1 col1}]
  765:   do_test capi2-13.3 {
  766:     check_origins {SELECT col2 AS hello, col1 AS world FROM view2}
  767:   } [list {main tab1 col2} {main tab1 col1}]
  768:   do_test capi2-13.4 {
  769:     check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view2)}
  770:   } [list {main tab1 col2} {main tab1 col1}]
  771:   do_test capi2-13.5 {
  772:     check_origins {SELECT (SELECT col2 FROM view2), (SELECT col1 FROM view2)}
  773:   } [list {main tab1 col2} {main tab1 col1}]
  774:   do_test capi2-13.6 {
  775:     check_origins {SELECT (SELECT col2), (SELECT col1) FROM view2}
  776:   } [list {main tab1 col2} {main tab1 col1}]
  777:   do_test capi2-13.7 {
  778:     check_origins {SELECT * FROM view2}
  779:   } [list {main tab1 col1} {main tab1 col2}]
  780:   do_test capi2-13.8 {
  781:     check_origins {select * from (select * from view2)}
  782:   } [list {main tab1 col1} {main tab1 col2}]
  783:   do_test capi2-13.9 {
  784:     check_origins {select * from (select * from (select * from view2))}
  785:   } [list {main tab1 col1} {main tab1 col2}]
  786:   do_test capi2-13.10 {
  787:     db close
  788:     sqlite3 db test.db
  789:     set ::DB [sqlite3_connection_pointer db]
  790:     check_origins {select * from (select * from (select * from view2))}
  791:   } [list {main tab1 col1} {main tab1 col2}]
  792:   do_test capi2-13.11 {
  793:     check_origins {select * from (select * from tab1 limit 10 offset 10)}
  794:   } [list {main tab1 col1} {main tab1 col2}]
  795: }
  796: 
  797: 
  798: } ;# ifcapable columnmetadata
  799: 
  800: db2 close
  801: finish_test

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>