SQLite

Check-in [506a0d7a71]
Login

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

Overview
Comment:Fix handling of schema mismatches in sqlite3session.c so that it matches the docs in sqlite3session.h.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 506a0d7a710e1ff2f367821e73f5080fcf63fbc5
User & Date: dan 2011-03-24 16:04:55.000
Context
2011-03-24
16:53
Fix handling of schema changes mid-session. (check-in: 76d2d2ad3b user: dan tags: sessions)
16:04
Fix handling of schema mismatches in sqlite3session.c so that it matches the docs in sqlite3session.h. (check-in: 506a0d7a71 user: dan tags: sessions)
11:22
Store primary key definitions for modified tables in changesets. Add the sqlite3changeset_pk() API to extract this data from a changeset iterator. (check-in: 54298ee5ed user: dan tags: sessions)
Changes
Unified Diff Ignore Whitespace Patch
Added ext/session/session3.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
# 2011 March 24
#
# 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 the session module.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source [file join [file dirname [info script]] session_common.tcl]
source $testdir/tester.tcl

set testprefix session3

# These tests - session3-1.* - verify that the session module behaves
# correctly when confronted with a schema mismatch when applying a 
# changeset (in function sqlite3changeset_apply()).
#
#   session3-1.1.*: Table does not exist in target db.
#   session3-1.2.*: Table has wrong number of columns in target db.
#   session3-1.3.*: Table has wrong PK columns in target db.
#

db close
sqlite3_shutdown
test_sqlite3_log log
sqlite3 db test.db

proc log {code msg} { lappend ::log $code $msg }

forcedelete test.db2
sqlite3 db2 test.db2

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}
do_test 1.1 {
  set ::log {}
  do_then_apply_sql {
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  }
  set ::log
} {SQLITE_SCHEMA {sqlite3changeset_apply(): no such table: t1}}

do_test 1.2.0 {
  execsql { CREATE TABLE t1(a PRIMARY KEY, b, c) } db2
} {}
do_test 1.2.1 {
  set ::log {}
  do_then_apply_sql {
    INSERT INTO t1 VALUES(5, 6);
    INSERT INTO t1 VALUES(7, 8);
  }
  set ::log
} {SQLITE_SCHEMA {sqlite3changeset_apply(): table t1 has 3 columns, expected 2}}

do_test 1.3.0 {
  execsql { 
    DROP TABLE t1;
    CREATE TABLE t1(a, b PRIMARY KEY);
  } db2
} {}
do_test 1.3.1 {
  set ::log {}
  do_then_apply_sql {
    INSERT INTO t1 VALUES(9, 10);
    INSERT INTO t1 VALUES(11, 12);
  }
  set ::log
} {SQLITE_SCHEMA {sqlite3changeset_apply(): primary key mismatch for table t1}}


catch { db close }
catch { db2 close }
sqlite3_shutdown
test_sqlite3_log
sqlite3_initialize

finish_test

Changes to ext/session/sqlite3session.c.
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
** pointer *pazCol should be freed to release all memory. Otherwise, pointer
** *pabPK. It is illegal for both pazCol and pabPK to be NULL.
*/
static int sessionTableInfo(
  sqlite3 *db,                    /* Database connection */
  const char *zDb,                /* Name of attached database (e.g. "main") */
  const char *zThis,              /* Table name */
  int nCol,                       /* Expected number of columns */
  const char **pzTab,             /* OUT: Copy of zThis */
  const char ***pazCol,           /* OUT: Array of column names for table */
  u8 **pabPK                      /* OUT: Array of booleans - true for PK col */
){
  char *zPragma;
  sqlite3_stmt *pStmt;
  int rc;







|







540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
** pointer *pazCol should be freed to release all memory. Otherwise, pointer
** *pabPK. It is illegal for both pazCol and pabPK to be NULL.
*/
static int sessionTableInfo(
  sqlite3 *db,                    /* Database connection */
  const char *zDb,                /* Name of attached database (e.g. "main") */
  const char *zThis,              /* Table name */
  int *pnCol,                     /* OUT: number of columns */
  const char **pzTab,             /* OUT: Copy of zThis */
  const char ***pazCol,           /* OUT: Array of column names for table */
  u8 **pabPK                      /* OUT: Array of booleans - true for PK col */
){
  char *zPragma;
  sqlite3_stmt *pStmt;
  int rc;
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
  nByte = nThis + 1;
  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    nByte += sqlite3_column_bytes(pStmt, 1);
    nDbCol++;
  }
  rc = sqlite3_reset(pStmt);

  if( nDbCol!=nCol ){
    rc = SQLITE_SCHEMA;
  }
  if( rc==SQLITE_OK ){
    nByte += nDbCol * (sizeof(const char *) + sizeof(u8) + 1);
    pAlloc = sqlite3_malloc(nByte);
    if( pAlloc==0 ){
      rc = SQLITE_NOMEM;
    }
  }
  if( rc==SQLITE_OK ){
    azCol = (char **)pAlloc;
    pAlloc = (u8 *)&azCol[nCol];
    abPK = (u8 *)pAlloc;
    pAlloc = &abPK[nCol];
    if( pzTab ){
      memcpy(pAlloc, zThis, nThis+1);
      *pzTab = (char *)pAlloc;
      pAlloc += nThis+1;
    }
  
    i = 0;







<
<
<









|

|







573
574
575
576
577
578
579



580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
  nByte = nThis + 1;
  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    nByte += sqlite3_column_bytes(pStmt, 1);
    nDbCol++;
  }
  rc = sqlite3_reset(pStmt);




  if( rc==SQLITE_OK ){
    nByte += nDbCol * (sizeof(const char *) + sizeof(u8) + 1);
    pAlloc = sqlite3_malloc(nByte);
    if( pAlloc==0 ){
      rc = SQLITE_NOMEM;
    }
  }
  if( rc==SQLITE_OK ){
    azCol = (char **)pAlloc;
    pAlloc = (u8 *)&azCol[nDbCol];
    abPK = (u8 *)pAlloc;
    pAlloc = &abPK[nDbCol];
    if( pzTab ){
      memcpy(pAlloc, zThis, nThis+1);
      *pzTab = (char *)pAlloc;
      pAlloc += nThis+1;
    }
  
    i = 0;
615
616
617
618
619
620
621

622
623
624

625
626
627
628
629
630
631

  /* If successful, populate the output variables. Otherwise, zero them and
  ** free any allocation made. An error code will be returned in this case.
  */
  if( rc==SQLITE_OK ){
    *pazCol = (const char **)azCol;
    *pabPK = abPK;

  }else{
    *pazCol = 0;
    *pabPK = 0;

    if( pzTab ) *pzTab = 0;
    sqlite3_free(azCol);
  }
  sqlite3_finalize(pStmt);
  return rc;
}








>



>







612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630

  /* If successful, populate the output variables. Otherwise, zero them and
  ** free any allocation made. An error code will be returned in this case.
  */
  if( rc==SQLITE_OK ){
    *pazCol = (const char **)azCol;
    *pabPK = abPK;
    *pnCol = nDbCol;
  }else{
    *pazCol = 0;
    *pabPK = 0;
    *pnCol = 0;
    if( pzTab ) *pzTab = 0;
    sqlite3_free(azCol);
  }
  sqlite3_finalize(pStmt);
  return rc;
}

644
645
646
647
648
649
650
651
652
653
654


655

656
657
658
659
660
661
662
** non-zero. Users are not allowed to change the number of columns in a table
** for which changes are being recorded by the session module. If they do so, 
** it is an error.
*/
static int sessionInitTable(sqlite3_session *pSession, SessionTable *pTab){
  if( pTab->nCol==0 ){
    assert( pTab->azCol==0 || pTab->abPK==0 );
    pTab->nCol = sqlite3_preupdate_count(pSession->db);
    pSession->rc = sessionTableInfo(pSession->db, pSession->zDb, 
        pTab->zName, pTab->nCol, 0, &pTab->azCol, &pTab->abPK
    );


  }else if( pTab->nCol!=sqlite3_preupdate_count(pSession->db) ){

    pSession->rc = SQLITE_SCHEMA;
  }
  return pSession->rc;
}

static void sessionPreupdateOneChange(
  int op,







<

|

>
>
|
>







643
644
645
646
647
648
649

650
651
652
653
654
655
656
657
658
659
660
661
662
663
** non-zero. Users are not allowed to change the number of columns in a table
** for which changes are being recorded by the session module. If they do so, 
** it is an error.
*/
static int sessionInitTable(sqlite3_session *pSession, SessionTable *pTab){
  if( pTab->nCol==0 ){
    assert( pTab->azCol==0 || pTab->abPK==0 );

    pSession->rc = sessionTableInfo(pSession->db, pSession->zDb, 
        pTab->zName, &pTab->nCol, 0, &pTab->azCol, &pTab->abPK
    );
  }
  if( pSession->rc==SQLITE_OK 
   && pTab->nCol!=sqlite3_preupdate_count(pSession->db) 
  ){
    pSession->rc = SQLITE_SCHEMA;
  }
  return pSession->rc;
}

static void sessionPreupdateOneChange(
  int op,
2398
2399
2400
2401
2402
2403
2404

2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422

2423
2424
2425



2426
2427
2428
2429
2430
2431
2432
2433
2434

2435
2436


2437


2438

















2439
2440
2441
2442
2443
2444
2445
2446
2447
2448




2449
2450
2451
2452
2453
2454
2455
  int(*xConflict)(
    void *pCtx,                   /* Copy of fifth arg to _apply() */
    int eConflict,                /* DATA, MISSING, CONFLICT, CONSTRAINT */
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx                      /* First argument passed to xConflict */
){

  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int rc;                         /* Return code */
  const char *zTab = 0;           /* Name of current table */
  int nTab = 0;                   /* Result of strlen(zTab) */
  SessionApplyCtx sApply;         /* changeset_apply() context object */

  memset(&sApply, 0, sizeof(sApply));
  rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);
  if( rc!=SQLITE_OK ) return rc;

  sqlite3_mutex_enter(sqlite3_db_mutex(db));
  rc = sqlite3_exec(db, "SAVEPOINT changeset_apply", 0, 0, 0);
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3changeset_next(pIter) ){
    int nCol;
    int op;
    int bReplace = 0;
    int bRetry = 0;
    const char *zNew;

    sqlite3changeset_op(pIter, &zNew, &nCol, &op, 0);

    if( zTab==0 || sqlite3_strnicmp(zNew, zTab, nTab+1) ){



      sqlite3_free(sApply.azCol);
      sqlite3_finalize(sApply.pDelete);
      sqlite3_finalize(sApply.pUpdate); 
      sqlite3_finalize(sApply.pInsert);
      sqlite3_finalize(sApply.pSelect);
      memset(&sApply, 0, sizeof(sApply));
      sApply.db = db;
      sApply.nCol = nCol;


      rc = sessionTableInfo(
          db, "main", zNew, nCol, &zTab, &sApply.azCol, &sApply.abPK);





      if( rc!=SQLITE_OK 

















       || (rc = sessionSelectRow(db, zTab, &sApply))
       || (rc = sessionUpdateRow(db, zTab, &sApply))
       || (rc = sessionDeleteRow(db, zTab, &sApply))
       || (rc = sessionInsertRow(db, zTab, &sApply))
      ){
        break;
      }

      nTab = strlen(zTab);
    }





    rc = sessionApplyOneOp(pIter, &sApply, xConflict, pCtx, &bReplace, &bRetry);

    if( rc==SQLITE_OK && bRetry ){
      rc = sessionApplyOneOp(pIter, &sApply, xConflict, pCtx, &bReplace, 0);
    }








>


















>



>
>
>







<

>

|
>
>

>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|






<


>
>
>
>







2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438

2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472

2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
  int(*xConflict)(
    void *pCtx,                   /* Copy of fifth arg to _apply() */
    int eConflict,                /* DATA, MISSING, CONFLICT, CONSTRAINT */
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx                      /* First argument passed to xConflict */
){
  int schemaMismatch = 0;
  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int rc;                         /* Return code */
  const char *zTab = 0;           /* Name of current table */
  int nTab = 0;                   /* Result of strlen(zTab) */
  SessionApplyCtx sApply;         /* changeset_apply() context object */

  memset(&sApply, 0, sizeof(sApply));
  rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);
  if( rc!=SQLITE_OK ) return rc;

  sqlite3_mutex_enter(sqlite3_db_mutex(db));
  rc = sqlite3_exec(db, "SAVEPOINT changeset_apply", 0, 0, 0);
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3changeset_next(pIter) ){
    int nCol;
    int op;
    int bReplace = 0;
    int bRetry = 0;
    const char *zNew;
    
    sqlite3changeset_op(pIter, &zNew, &nCol, &op, 0);

    if( zTab==0 || sqlite3_strnicmp(zNew, zTab, nTab+1) ){
      u8 *abPK;

      schemaMismatch = 0;
      sqlite3_free(sApply.azCol);
      sqlite3_finalize(sApply.pDelete);
      sqlite3_finalize(sApply.pUpdate); 
      sqlite3_finalize(sApply.pInsert);
      sqlite3_finalize(sApply.pSelect);
      memset(&sApply, 0, sizeof(sApply));
      sApply.db = db;


      sqlite3changeset_pk(pIter, &abPK, 0);
      rc = sessionTableInfo(
          db, "main", zNew, &sApply.nCol, &zTab, &sApply.azCol, &sApply.abPK
      );
      if( rc!=SQLITE_OK ) break;

      if( sApply.nCol==0 ){
        schemaMismatch = 1;
        sqlite3_log(SQLITE_SCHEMA, 
            "sqlite3changeset_apply(): no such table: %s", zTab
        );
      }
      else if( sApply.nCol!=nCol ){
        schemaMismatch = 1;
        sqlite3_log(SQLITE_SCHEMA, 
            "sqlite3changeset_apply(): table %s has %d columns, expected %d", 
            zTab, sApply.nCol, nCol
        );
      }
      else if( memcmp(sApply.abPK, abPK, nCol)!=0 ){
        schemaMismatch = 1;
        sqlite3_log(SQLITE_SCHEMA, 
            "sqlite3changeset_apply(): primary key mismatch for table %s", zTab
        );
      }
      else if( 
          (rc = sessionSelectRow(db, zTab, &sApply))
       || (rc = sessionUpdateRow(db, zTab, &sApply))
       || (rc = sessionDeleteRow(db, zTab, &sApply))
       || (rc = sessionInsertRow(db, zTab, &sApply))
      ){
        break;
      }

      nTab = strlen(zTab);
    }

    /* If there is a schema mismatch on the current table, proceed to the
    ** next change. A log message has already been issued. */
    if( schemaMismatch ) continue;

    rc = sessionApplyOneOp(pIter, &sApply, xConflict, pCtx, &bReplace, &bRetry);

    if( rc==SQLITE_OK && bRetry ){
      rc = sessionApplyOneOp(pIter, &sApply, xConflict, pCtx, &bReplace, 0);
    }

Changes to ext/session/sqlite3session.h.
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
** database handle that has one or more session objects attached. Nor is
** it possible to create a session object attached to a database handle for
** which a pre-update hook is already defined. The results of attempting 
** either of these things are undefined.
**
** The session object will be used to create changesets for tables in
** database zDb, where zDb is either "main", or "temp", or the name of an
** attached database. It is not an error if database zDb does not exist
** to the database when the session object is created.
*/
int sqlite3session_create(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of db (e.g. "main") */
  sqlite3_session **ppSession     /* OUT: New session object */
);







|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
** database handle that has one or more session objects attached. Nor is
** it possible to create a session object attached to a database handle for
** which a pre-update hook is already defined. The results of attempting 
** either of these things are undefined.
**
** The session object will be used to create changesets for tables in
** database zDb, where zDb is either "main", or "temp", or the name of an
** attached database. It is not an error if database zDb is not attached
** to the database when the session object is created.
*/
int sqlite3session_create(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of db (e.g. "main") */
  sqlite3_session **ppSession     /* OUT: New session object */
);