Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the --upsert option to the wordcount test program. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
ee1e750baaf4c66b4e1f103d8b80362f |
| User & Date: | drh 2018-04-19 16:52:37.885 |
Context
|
2018-04-19
| ||
| 20:18 | Avoid unnecessary OP_Goto instructions on an upsert of a table that only has a single secondary index. (Leaf check-in: 97dd21ab90 user: drh tags: upsert-opt) | |
| 20:06 | Modify a test case in zipfile2.test to take into account that with some platform/file-system combinations it is possible to fopen() and fread() (but not fwrite()) a directory. (check-in: 893e6089c8 user: dan tags: trunk) | |
| 16:52 | Add the --upsert option to the wordcount test program. (check-in: ee1e750baa user: drh tags: trunk) | |
| 16:14 | Add the ext/misc/templatevtab.c template for virtual tables. This is a work-in-progress as it still needs improvements to the comments in order to be useful as a template. (check-in: 22358fb549 user: drh tags: trunk) | |
Changes
Changes to test/wordcount.c.
| ︙ | ︙ | |||
24 25 26 27 28 29 30 31 32 33 34 35 36 37 | ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,0) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new ** ** Replace mode means: ** (1) REPLACE INTO wordcount ** VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1); ** ** Select mode means: ** (1) SELECT 1 FROM wordcount WHERE word=$new ** (2) INSERT INTO wordcount VALUES($new,1) -- if (1) returns nothing ** (3) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new --if (1) return TRUE ** ** Delete mode means: ** (1) DELETE FROM wordcount WHERE word=$new | > > > > | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,0) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new ** ** Replace mode means: ** (1) REPLACE INTO wordcount ** VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1); ** ** Upsert mode means: ** (1) INSERT INTO wordcount VALUES($new,1) ** ON CONFLICT(word) DO UPDATE SET cnt=cnt+1 ** ** Select mode means: ** (1) SELECT 1 FROM wordcount WHERE word=$new ** (2) INSERT INTO wordcount VALUES($new,1) -- if (1) returns nothing ** (3) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new --if (1) return TRUE ** ** Delete mode means: ** (1) DELETE FROM wordcount WHERE word=$new |
| ︙ | ︙ | |||
86 87 88 89 90 91 92 93 94 95 96 97 98 99 | " --select Use SELECT mode\n" " --stats Show sqlite3_status() results at the end.\n" " --summary Show summary information on the collected data.\n" " --tag NAME Tag all output using NAME. Use only stdout.\n" " --timer Time the operation of this program\n" " --trace Enable sqlite3_trace() output.\n" " --update Use UPDATE mode\n" " --without-rowid Use a WITHOUT ROWID table to store the words.\n" ; /* Output tag */ char *zTag = "--"; /* Return the current wall-clock time */ | > | 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | " --select Use SELECT mode\n" " --stats Show sqlite3_status() results at the end.\n" " --summary Show summary information on the collected data.\n" " --tag NAME Tag all output using NAME. Use only stdout.\n" " --timer Time the operation of this program\n" " --trace Enable sqlite3_trace() output.\n" " --update Use UPDATE mode\n" " --upsert Use UPSERT mode\n" " --without-rowid Use a WITHOUT ROWID table to store the words.\n" ; /* Output tag */ char *zTag = "--"; /* Return the current wall-clock time */ |
| ︙ | ︙ | |||
204 205 206 207 208 209 210 |
sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
}
}
/* Define operating modes */
#define MODE_INSERT 0
#define MODE_REPLACE 1
| > | | | | | > | 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 |
sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
}
}
/* Define operating modes */
#define MODE_INSERT 0
#define MODE_REPLACE 1
#define MODE_UPSERT 2
#define MODE_SELECT 3
#define MODE_UPDATE 4
#define MODE_DELETE 5
#define MODE_QUERY 6
#define MODE_COUNT 7
#define MODE_ALL (-1)
/* Mode names */
static const char *azMode[] = {
"--insert",
"--replace",
"--upsert",
"--select",
"--update",
"--delete",
"--query"
};
/*
|
| ︙ | ︙ | |||
288 289 290 291 292 293 294 295 296 297 298 299 300 301 |
const char *z = argv[i];
if( z[0]=='-' ){
do{ z++; }while( z[0]=='-' );
if( strcmp(z,"without-rowid")==0 ){
useWithoutRowid = 1;
}else if( strcmp(z,"replace")==0 ){
iMode = MODE_REPLACE;
}else if( strcmp(z,"select")==0 ){
iMode = MODE_SELECT;
}else if( strcmp(z,"insert")==0 ){
iMode = MODE_INSERT;
}else if( strcmp(z,"update")==0 ){
iMode = MODE_UPDATE;
}else if( strcmp(z,"delete")==0 ){
| > > | 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 |
const char *z = argv[i];
if( z[0]=='-' ){
do{ z++; }while( z[0]=='-' );
if( strcmp(z,"without-rowid")==0 ){
useWithoutRowid = 1;
}else if( strcmp(z,"replace")==0 ){
iMode = MODE_REPLACE;
}else if( strcmp(z,"upsert")==0 ){
iMode = MODE_UPSERT;
}else if( strcmp(z,"select")==0 ){
iMode = MODE_SELECT;
}else if( strcmp(z,"insert")==0 ){
iMode = MODE_INSERT;
}else if( strcmp(z,"update")==0 ){
iMode = MODE_UPDATE;
}else if( strcmp(z,"delete")==0 ){
|
| ︙ | ︙ | |||
463 464 465 466 467 468 469 470 471 472 473 474 475 476 |
rc = sqlite3_prepare_v2(db,
"REPLACE INTO wordcount(word,cnt)"
"VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)",
-1, &pInsert, 0);
if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n",
sqlite3_errmsg(db));
}
if( iMode2==MODE_DELETE ){
rc = sqlite3_prepare_v2(db,
"DELETE FROM wordcount WHERE word=?1",
-1, &pDelete, 0);
if( rc ) fatal_error("Could not prepare the DELETE statement: %s\n",
sqlite3_errmsg(db));
}
| > > > > > > > > | 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 |
rc = sqlite3_prepare_v2(db,
"REPLACE INTO wordcount(word,cnt)"
"VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)",
-1, &pInsert, 0);
if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n",
sqlite3_errmsg(db));
}
if( iMode2==MODE_UPSERT ){
rc = sqlite3_prepare_v2(db,
"INSERT INTO wordcount(word,cnt) VALUES(?1,1) "
"ON CONFLICT(word) DO UPDATE SET cnt=cnt+1",
-1, &pInsert, 0);
if( rc ) fatal_error("Could not prepare the UPSERT statement: %s\n",
sqlite3_errmsg(db));
}
if( iMode2==MODE_DELETE ){
rc = sqlite3_prepare_v2(db,
"DELETE FROM wordcount WHERE word=?1",
-1, &pDelete, 0);
if( rc ) fatal_error("Could not prepare the DELETE statement: %s\n",
sqlite3_errmsg(db));
}
|
| ︙ | ︙ |