/ Check-in [ba85bf8c]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ba85bf8cb88f7ae220d919f5c23f51d9dcedc843
User & Date: shaneh 2011-03-10 21:13:18
Context
2011-03-10
21:48
Simplification of tests and more added for SQLITE_OMIT_UNIQUE_ENFORCEMENT tests. check-in: 75a38411 user: shaneh tags: trunk
21:13
Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT. check-in: ba85bf8c user: shaneh tags: trunk
03:54
Minor clean-up of previous mem5 allocator fix. check-in: 36438423 user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c. 

1306
1307
1308
1309
1310
1311
1312

1313
1314

1315
1316
1317
1318
1319
1320
1321
....
1324
1325
1326
1327
1328
1329
1330






1331
1332
1333
1334
1335
1336
1337
....
1398
1399
1400
1401
1402
1403
1404

1405
1406
1407
1408
1409
1410
1411


  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Add the new records to the indices as we go.
  */
  for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
    int regIdx;

    int regR;


    if( aRegIdx[iCur]==0 ) continue;  /* Skip unused indices */

    /* Create a key for accessing the index entry */
    regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1);
    for(i=0; i<pIdx->nColumn; i++){
      int idx = pIdx->aiColumn[i];
      if( idx==pTab->iPKey ){
................................................................................
        sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i);
      }
    }
    sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
    sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);







    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ){ 
      sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
      continue;  /* pIdx is not a UNIQUE index */
    }
................................................................................
        );
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);

  }
  
  if( pbMayReplace ){
    *pbMayReplace = seenReplace;
  }
}









>

<
>







 







>
>
>
>
>
>







 







>








1306
1307
1308
1309
1310
1311
1312
1313
1314

1315
1316
1317
1318
1319
1320
1321
1322
....
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
....
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419


  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Add the new records to the indices as we go.
  */
  for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
    int regIdx;
#ifndef SQLITE_OMIT_UNIQUE_ENFORCEMENT
    int regR;

#endif
    if( aRegIdx[iCur]==0 ) continue;  /* Skip unused indices */

    /* Create a key for accessing the index entry */
    regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1);
    for(i=0; i<pIdx->nColumn; i++){
      int idx = pIdx->aiColumn[i];
      if( idx==pTab->iPKey ){
................................................................................
        sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i);
      }
    }
    sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
    sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);

#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT
    pIdx->onError = OE_None;
    sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
    continue;  /* Treat pIdx as if it is not a UNIQUE index */
#else

    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ){ 
      sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
      continue;  /* pIdx is not a UNIQUE index */
    }
................................................................................
        );
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);
#endif
  }
  
  if( pbMayReplace ){
    *pbMayReplace = seenReplace;
  }
}

Changes to src/test_config.c. 

470
471
472
473
474
475
476






477
478
479
480
481
482
483

#endif

#ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "1", TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_OMIT_UTF16
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "1", TCL_GLOBAL_ONLY);
#endif









>
>
>
>
>
>








470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489

#endif

#ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT
  Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_UTF16
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "1", TCL_GLOBAL_ONLY);
#endif

Added test/omitunique.test. 























































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170

# 2011 March 10
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SQLITE_OMIT_UNIQUE_ENFORCEMENT
# compiler option.
#

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

# table with UNIQUE keyword on column
do_test omitunique-1.1.1 {
  catchsql {CREATE TABLE t1(a TEXT UNIQUE);  }
} {0 {}}
do_test omitunique-1.1.2 {
  catchsql {INSERT INTO t1(a) VALUES('abc'); }
} {0 {}}
do_test omitunique-1.1.3 {
  catchsql {INSERT INTO t1(a) VALUES('123'); }
} {0 {}}

# table with UNIQUE index on column
do_test omitunique-1.2.1 {
  catchsql {
    CREATE TABLE t2(a TEXT);
    CREATE UNIQUE INDEX t2a ON t2(a);
  }
} {0 {}}
do_test omitunique-1.2.2 {
  catchsql {INSERT INTO t2(a) VALUES('abc'); }
} {0 {}}
do_test omitunique-1.2.3 {
  catchsql {INSERT INTO t2(a) VALUES('123'); }
} {0 {}}

# table with regular index on column
do_test omitunique-1.3.1 {
  catchsql {
    CREATE TABLE t3(a TEXT);
    CREATE INDEX t3a ON t3(a);
  }
} {0 {}}
do_test omitunique-1.3.2 {
  catchsql {INSERT INTO t3(a) VALUES('abc'); }
} {0 {}}
do_test omitunique-1.3.3 {
  catchsql {INSERT INTO t3(a) VALUES('123'); }
} {0 {}}

# table with no index on column
do_test omitunique-1.4.1 {
  catchsql {
    CREATE TABLE t4(a TEXT);
  }
} {0 {}}
do_test omitunique-1.4.2 {
  catchsql {INSERT INTO t4(a) VALUES('abc'); }
} {0 {}}
do_test omitunique-1.4.3 {
  catchsql {INSERT INTO t4(a) VALUES('123'); }
} {0 {}}

# run our tests using several table/index forms
foreach {j tbl uniq cnt_enforce cnt_omit qp_est} {
1 {t1} 1 1 9 1
2 {t2} 1 1 9 1
3 {t3} 0 9 9 10
4 {t4} 0 9 9 100000
} {

  # check various INSERT commands
  foreach {i cmd err} {
    1 {INSERT}             1   
    2 {INSERT OR IGNORE}   0 
    3 {INSERT OR REPLACE}  0
    4 {REPLACE}            0
    5 {INSERT OR FAIL}     1
    6 {INSERT OR ABORT}    1
    7 {INSERT OR ROLLBACK} 1
  } {

    if { $uniq==0 || $err==0 } { 
      set msg {0 {}} 
    } {
      set msg {1 {column a is not unique}}
    }

    ifcapable unique_enforcement {
      ifcapable explain {
        do_test omitunique-2.1.$j.$i.1 {
          set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
          regexp { IsUnique } $x
        } $uniq
      }
      do_test omitunique-2.1.$j.$i.2 {
        catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}]
      } $msg
    }
    ifcapable !unique_enforcement {
      ifcapable explain {
        do_test omitunique-2.1.$j.$i.1 {
          set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
          regexp { IsUnique } $x
        } {0}
      }
      do_test omitunique-2.1.$j.$i.2 {
        catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}]
      } {0 {}}
    }

  }
  # end foreach cmd

  # check UPDATE command
  ifcapable unique_enforcement {
    ifcapable explain {
      do_test omitunique-2.2.$j.1 {
        set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
        regexp { IsUnique } $x
      } $uniq
    }
    do_test omitunique-2.2.$j.2 {
      catchsql [ subst {UPDATE $tbl SET a='abc'}]
    } $msg
  }
  ifcapable !unique_enforcement {
    ifcapable explain {
      do_test omitunique-2.2.$j.1 {
        set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
        regexp { IsUnique } $x
      } {0}
    }
    do_test omitunique-2.2.$j.2 {
      catchsql [ subst {UPDATE $tbl SET a='abc' }]
    } {0 {}}
  }

  # check record counts
  ifcapable unique_enforcement {
    do_test omitunique-2.3.$j {
      execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }]
    } $cnt_enforce
  }
  ifcapable !unique_enforcement {
    do_test omitunique-2.3.$j {
      execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }]
    } $cnt_omit
  }

  # make sure the query planner row estimate not affected because of omit enforcement
  ifcapable explain {
    do_test omitunique-2.4.$j {
      set x [execsql [ subst {EXPLAIN QUERY PLAN SELECT count(*) FROM $tbl WHERE a='abc' }]]
      set y [ subst {~$qp_est row} ]
      regexp $y $x
    } {1}
  }

}
# end foreach tbl

finish_test

This page was generated in about 0.011s by Fossil 2.11 [eee161e823] 2020-01-22 19:08:54