/ Check-in [9029274b]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Remove the <ON CONFLICT> clause from BEGIN (CVS 1501)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9029274b6129140064bd7ac34df7eaba00d28efb
User & Date: danielk1977 2004-05-31 08:55:34
Context
2004-05-31
10:01
Add read-transactions to the btree and vdbe. The compiler doesn't invoke them yet. (CVS 1502) check-in: 6b43633a user: danielk1977 tags: trunk
08:55
Remove the <ON CONFLICT> clause from BEGIN (CVS 1501) check-in: 9029274b user: danielk1977 tags: trunk
08:26
Replace OP_Begin, OP_Commit and OP_Rollback with OP_AutoCommit. (CVS 1500) check-in: b8ed812c user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c. 

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
....
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.201 2004/05/31 08:26:49 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  }
  sqliteFree(pList);
}

/*
** Begin a transaction
*/
void sqlite3BeginTransaction(Parse *pParse, int onError){
  sqlite *db;
  Vdbe *v;

  if( pParse==0 || (db=pParse->db)==0 || db->aDb[0].pBt==0 ) return;
  if( pParse->nErr || sqlite3_malloc_failed ) return;
  if( sqlite3AuthCheck(pParse, SQLITE_TRANSACTION, "BEGIN", 0, 0) ) return;

  v = sqlite3GetVdbe(pParse);
  if( !v ) return;
  sqlite3VdbeAddOp(v, OP_AutoCommit, 0, 0);

  /* FIX ME: Need to deal with onError */
}

/*
** Commit a transaction
*/
void sqlite3CommitTransaction(Parse *pParse){
  sqlite *db;








|







 







|










<
<








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
....
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185


2186
2187
2188
2189
2190
2191
2192

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.202 2004/05/31 08:55:34 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  }
  sqliteFree(pList);
}

/*
** Begin a transaction
*/
void sqlite3BeginTransaction(Parse *pParse){
  sqlite *db;
  Vdbe *v;

  if( pParse==0 || (db=pParse->db)==0 || db->aDb[0].pBt==0 ) return;
  if( pParse->nErr || sqlite3_malloc_failed ) return;
  if( sqlite3AuthCheck(pParse, SQLITE_TRANSACTION, "BEGIN", 0, 0) ) return;

  v = sqlite3GetVdbe(pParse);
  if( !v ) return;
  sqlite3VdbeAddOp(v, OP_AutoCommit, 0, 0);


}

/*
** Commit a transaction
*/
void sqlite3CommitTransaction(Parse *pParse){
  sqlite *db;

Changes to src/insert.c. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
...
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.108 2004/05/29 11:24:50 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
    if( i==pTab->iPKey ){
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqlite3VdbeAddOp(v, OP_Dup, nCol-1-i, 1);
................................................................................
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.
  */
  if( recnoChng ){
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    
    if( isUpdate ){
      sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
................................................................................
    sqlite3IndexAffinityStr(v, pIdx);

    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( seenReplace ){
      if( onError==OE_Ignore ) onError = OE_Replace;
      else if( onError==OE_Fail ) onError = OE_Abort;
    }








|







 







<
<







 







<
<







 







<
<








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
743
744
745
746
747
748
749


750
751
752
753
754
755
756
...
789
790
791
792
793
794
795


796
797
798
799
800
801
802
...
864
865
866
867
868
869
870


871
872
873
874
875
876
877

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.109 2004/05/31 08:55:34 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
    if( i==pTab->iPKey ){
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;


    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqlite3VdbeAddOp(v, OP_Dup, nCol-1-i, 1);
................................................................................
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.
  */
  if( recnoChng ){
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;


    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    
    if( isUpdate ){
      sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
................................................................................
    sqlite3IndexAffinityStr(v, pIdx);

    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
    if( overrideError!=OE_Default ){
      onError = overrideError;


    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( seenReplace ){
      if( onError==OE_Ignore ) onError = OE_Replace;
      else if( onError==OE_Fail ) onError = OE_Abort;
    }

Changes to src/main.c. 

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
....
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.197 2004/05/31 08:26:49 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
    }
  }
#endif

  /* Allocate the sqlite data structure */
  db = sqliteMalloc( sizeof(sqlite) );
  if( db==0 ) goto opendb_out;
  db->onError = OE_Default;
  db->priorNewRowid = 0;
  db->magic = SQLITE_MAGIC_BUSY;
  db->nDb = 2;
  db->aDb = db->aDbStatic;
  db->enc = def_enc;
  db->autoCommit = 1;
  /* db->flags |= SQLITE_ShortColNames; */








|







 







<








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
....
1010
1011
1012
1013
1014
1015
1016

1017
1018
1019
1020
1021
1022
1023

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.198 2004/05/31 08:55:34 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
    }
  }
#endif

  /* Allocate the sqlite data structure */
  db = sqliteMalloc( sizeof(sqlite) );
  if( db==0 ) goto opendb_out;

  db->priorNewRowid = 0;
  db->magic = SQLITE_MAGIC_BUSY;
  db->nDb = 2;
  db->aDb = db->aDbStatic;
  db->enc = def_enc;
  db->autoCommit = 1;
  /* db->flags |= SQLITE_ShortColNames; */

Changes to src/parse.y. 

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.124 2004/05/29 10:23:20 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
cmdx ::= cmd.           { sqlite3Exec(pParse); }
explain ::= EXPLAIN.    { sqlite3BeginParse(pParse, 1); }
explain ::= .           { sqlite3BeginParse(pParse, 0); }

///////////////////// Begin and end transactions. ////////////////////////////
//

cmd ::= BEGIN trans_opt onconf(R).  {sqlite3BeginTransaction(pParse,R);}
trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION nm.
cmd ::= COMMIT trans_opt.      {sqlite3CommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqlite3CommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqlite3RollbackTransaction(pParse);}









|







 







|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.125 2004/05/31 08:55:34 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
cmdx ::= cmd.           { sqlite3Exec(pParse); }
explain ::= EXPLAIN.    { sqlite3BeginParse(pParse, 1); }
explain ::= .           { sqlite3BeginParse(pParse, 0); }

///////////////////// Begin and end transactions. ////////////////////////////
//

cmd ::= BEGIN trans_opt.  {sqlite3BeginTransaction(pParse);}
trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION nm.
cmd ::= COMMIT trans_opt.      {sqlite3CommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqlite3CommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqlite3RollbackTransaction(pParse);}

Changes to src/sqliteInt.h. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
....
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.262 2004/05/31 08:26:49 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
  Db *aDb;                      /* All backends */
  Db aDbStatic[2];              /* Static space for the 2 default backends */
  int flags;                    /* Miscellanous flags. See below */
  u8 file_format;               /* What file format version is this database? */
  u8 safety_level;              /* How aggressive at synching data to disk */
  u8 want_to_close;             /* Close after all VDBEs are deallocated */
  u8 temp_store;                /* 1=file, 2=memory, 0=compile-time default */
  u8 onError;                   /* Default conflict algorithm */
  int next_cookie;              /* Next value of aDb[0].schema_cookie */
  int cache_size;               /* Number of pages to use in the cache */
  int nTable;                   /* Number of tables in the database */
  void *pBusyArg;               /* 1st Argument to the busy callback */
  int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
  void *pCommitArg;             /* Argument to xCommitCallback() */   
  int (*xCommitCallback)(void*);/* Invoked at every commit. */
................................................................................
int sqliteFuncId(Token*);
int sqlite3ExprResolveIds(Parse*, SrcList*, ExprList*, Expr*);
int sqlite3ExprAnalyzeAggregates(Parse*, Expr*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3Randomness(int, void*);
void sqlite3RollbackAll(sqlite*);
void sqlite3CodeVerifySchema(Parse*, int);
void sqlite3BeginTransaction(Parse*, int);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsInteger(Expr*, int*);
int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
void sqlite3GenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);








|







 







<







 







|








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
362
363
364
365
366
367
368

369
370
371
372
373
374
375
....
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.263 2004/05/31 08:55:34 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
  Db *aDb;                      /* All backends */
  Db aDbStatic[2];              /* Static space for the 2 default backends */
  int flags;                    /* Miscellanous flags. See below */
  u8 file_format;               /* What file format version is this database? */
  u8 safety_level;              /* How aggressive at synching data to disk */
  u8 want_to_close;             /* Close after all VDBEs are deallocated */
  u8 temp_store;                /* 1=file, 2=memory, 0=compile-time default */

  int next_cookie;              /* Next value of aDb[0].schema_cookie */
  int cache_size;               /* Number of pages to use in the cache */
  int nTable;                   /* Number of tables in the database */
  void *pBusyArg;               /* 1st Argument to the busy callback */
  int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
  void *pCommitArg;             /* Argument to xCommitCallback() */   
  int (*xCommitCallback)(void*);/* Invoked at every commit. */
................................................................................
int sqliteFuncId(Token*);
int sqlite3ExprResolveIds(Parse*, SrcList*, ExprList*, Expr*);
int sqlite3ExprAnalyzeAggregates(Parse*, Expr*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3Randomness(int, void*);
void sqlite3RollbackAll(sqlite*);
void sqlite3CodeVerifySchema(Parse*, int);
void sqlite3BeginTransaction(Parse*);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsInteger(Expr*, int*);
int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
void sqlite3GenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);

Changes to test/attach.test. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.19 2004/05/31 08:26:49 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
................................................................................
    SELECT * FROM main.t4;
  }
} {main.11}
do_test attach-4.8 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
pragma vdbe_trace = on;
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}
do_test attach-4.9 {
  execsql {
pragma vdbe_trace = off;
    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
do_test attach-4.10 {
  execsql {
    DETACH DATABASE db2;








|







 







<





<








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
440
441
442
443
444
445
446

447
448
449
450
451

452
453
454
455
456
457
458

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.20 2004/05/31 08:55:34 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
................................................................................
    SELECT * FROM main.t4;
  }
} {main.11}
do_test attach-4.8 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);

    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}
do_test attach-4.9 {
  execsql {

    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
do_test attach-4.10 {
  execsql {
    DETACH DATABASE db2;

Changes to test/conflict.test. 

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
...
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
...
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.20 2004/05/31 08:26:49 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  if { $conf=={} } {

  do_test conflict-1.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-2.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}
  8 IGNORE   INSERT                  0 3   1
  9 IGNORE   {INSERT OR IGNORE}      0 3   1
 10 IGNORE   {INSERT OR REPLACE}     0 4   1
 11 IGNORE   REPLACE                 0 4   1
 12 IGNORE   {INSERT OR FAIL}        1 {}  1
 13 IGNORE   {INSERT OR ABORT}       1 {}  1
 14 IGNORE   {INSERT OR ROLLBACK}    1 {}  {}
 15 REPLACE  INSERT                  0 4   1
 16 FAIL     INSERT                  1 {}  1
 17 ABORT    INSERT                  1 {}  1
 18 ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-3.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
................................................................................
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 3   1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 4   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 3   1
 10 ABORT    {}       {INSERT OR REPLACE}     0 4   1
 11 ROLLBACK {}       {INSERT OR IGNORE }     0 3   1
 12 REPLACE  IGNORE   INSERT                  0 3   1
 13 IGNORE   REPLACE  INSERT                  0 4   1
 14 FAIL     IGNORE   INSERT                  0 3   1
 15 ABORT    REPLACE  INSERT                  0 4   1
 16 ROLLBACK IGNORE   INSERT                  0 3   1
 12 IGNORE   REPLACE  INSERT                  0 4   1
 13 IGNORE   FAIL     INSERT                  1 {}  1
 14 IGNORE   ABORT    INSERT                  1 {}  1
 15 IGNORE   ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
................................................................................
 10 ABORT    {}       {INSERT OR REPLACE}     0 5   1
 11 ROLLBACK {}       {INSERT OR IGNORE}      0 {}  1
 12 {}       {}       {INSERT OR IGNORE}      0 {}  1
 13 {}       {}       {INSERT OR REPLACE}     0 5   1
 14 {}       {}       {INSERT OR FAIL}        1 {}  1
 15 {}       {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {}       {INSERT OR ROLLBACK}    1 {}  {}
 17 {}       IGNORE   INSERT                  0 {}  1
 18 {}       REPLACE  INSERT                  0 5   1
 19 {}       FAIL     INSERT                  1 {}  1
 20 {}       ABORT    INSERT                  1 {}  1
 21 {}       ROLLBACK INSERT                  1 {}  {}
 22 REPLACE  FAIL     INSERT                  1 {}  1
 23 IGNORE   ROLLBACK INSERT                  1 {}  {}
} {
  if {$t0} {set t1 {t1.c may not be NULL}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
 10 ABORT    {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 11 ROLLBACK {}       {UPDATE OR IGNORE}      0 {6 7 3 9}   1
 12 {}       {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1
 13 {}       {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 14 {}       {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {column a is not unique}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {6}

do_test conflict-10.1 {
  catchsql {
    DELETE FROM t1;
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(1,3);
    COMMIT;
  }
  execsql {SELECT * FROM t1}
} {}
do_test conflict-10.2 {
  catchsql {
    CREATE TABLE t4(x);
    CREATE UNIQUE INDEX t4x ON t4(x);
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t4 VALUES(1);
    INSERT INTO t4 VALUES(1);
    COMMIT;
  }
  execsql {SELECT * FROM t4}
} {}

integrity_check conflict-99.0

finish_test








|







 







<
<
<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<







 







|
|
|








|
|
|









9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
41
42
43
44
45
46
47











48
49
50
51
52
53
54
..
92
93
94
95
96
97
98











99
100
101
102
103
104
105
...
139
140
141
142
143
144
145











146
147
148
149
150
151
152
...
187
188
189
190
191
192
193









194
195
196
197
198
199
200
...
242
243
244
245
246
247
248







249
250
251
252
253
254
255
...
303
304
305
306
307
308
309










310
311
312
313
314
315
316
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.21 2004/05/31 08:55:34 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}











} {
  if { $conf=={} } {

  do_test conflict-1.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}











} {
  do_test conflict-2.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
................................................................................
  1 {}       INSERT                  1 {}  1
  2 {}       {INSERT OR IGNORE}      0 3   1
  3 {}       {INSERT OR REPLACE}     0 4   1
  4 {}       REPLACE                 0 4   1
  5 {}       {INSERT OR FAIL}        1 {}  1
  6 {}       {INSERT OR ABORT}       1 {}  1
  7 {}       {INSERT OR ROLLBACK}    1 {}  {}











} {
  do_test conflict-3.$i {
    if {$conf!=""} {set conf "ON CONFLICT $conf"}
    set r0 [catch {execsql [subst {
      DELETE FROM t1;
      DELETE FROM t2;
      INSERT INTO t1 VALUES(1,2,3);
................................................................................
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 3   1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 4   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 3   1
 10 ABORT    {}       {INSERT OR REPLACE}     0 4   1
 11 ROLLBACK {}       {INSERT OR IGNORE }     0 3   1









} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
................................................................................
 10 ABORT    {}       {INSERT OR REPLACE}     0 5   1
 11 ROLLBACK {}       {INSERT OR IGNORE}      0 {}  1
 12 {}       {}       {INSERT OR IGNORE}      0 {}  1
 13 {}       {}       {INSERT OR REPLACE}     0 5   1
 14 {}       {}       {INSERT OR FAIL}        1 {}  1
 15 {}       {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {}       {INSERT OR ROLLBACK}    1 {}  {}







} {
  if {$t0} {set t1 {t1.c may not be NULL}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
 10 ABORT    {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 11 ROLLBACK {}       {UPDATE OR IGNORE}      0 {6 7 3 9}   1
 12 {}       {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1
 13 {}       {}       {UPDATE OR REPLACE}     0 {7 6 9}    1
 14 {}       {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0










} {
  if {$t0} {set t1 {column a is not unique}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {6}

do_test conflict-10.1 {
  catchsql {
    DELETE FROM t1;
    BEGIN;
    INSERT OR ROLLBACK INTO t1 VALUES(1,2);
    INSERT OR ROLLBACK INTO t1 VALUES(1,3);
    COMMIT;
  }
  execsql {SELECT * FROM t1}
} {}
do_test conflict-10.2 {
  catchsql {
    CREATE TABLE t4(x);
    CREATE UNIQUE INDEX t4x ON t4(x);
    BEGIN;
    INSERT OR ROLLBACK INTO t4 VALUES(1);
    INSERT OR ROLLBACK INTO t4 VALUES(1);
    COMMIT;
  }
  execsql {SELECT * FROM t4}
} {}

integrity_check conflict-99.0

finish_test

This page was generated in about 0.087s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13