/ Check-in [4d34a3d4]
Login
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:Fix the error message returned by sqlite3session_diff() for tables with no PRIMARY KEY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 4d34a3d40da210bebb2a2e6dff094f9a39c92798
User & Date: dan 2015-04-23 15:03:14
Context
2015-04-23
17:22
Modify the sqlite3session_diff() API so that tables with no PRIMARY KEYs are ignored. This matches the other sessions APIs. Also change sqlite3session_diff() so that it returns SQLITE_SCHEMA, instead of SQLITE_ERROR, if the tables being compared do not have compatible schemas. check-in: aada0ad0 user: dan tags: sessions
15:03
Fix the error message returned by sqlite3session_diff() for tables with no PRIMARY KEY. check-in: 4d34a3d4 user: dan tags: sessions
14:40
Fix a performance problem in sqlite3session_diff(). check-in: ea400eca user: dan tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/session/sessionD.test.

180
181
182
183
184
185
186































187
188
  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c));
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
}
do_empty_diff_test 3.1
































finish_test








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


180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c));
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
}
do_empty_diff_test 3.1


#-------------------------------------------------------------------------
# Test some error cases:
# 
#   1) schema mismatches between the two dbs, and 
#   2) tables with no primary keys.
#
reset_db
forcedelete test.db2
do_execsql_test 4.0 {
  ATTACH 'test.db2' AS ixua;
  CREATE TABLE ixua.t1(a, b, c);
  CREATE TABLE main.t1(a, b, c);

  CREATE TABLE ixua.t2(a PRIMARY KEY, b, c);
  CREATE TABLE main.t2(a PRIMARY KEY, b, x);
}

do_test 4.1 {
  sqlite3session S db main
  S attach t1
  list [catch { S diff ixua t1 } msg] $msg
} {1 {table has no primary key}}

do_test 4.2 {
  S attach t2
  list [catch { S diff ixua t2 } msg] $msg
} {1 {table schemas do not match}}

S delete

finish_test

Changes to ext/session/sqlite3session.c.

1469
1470
1471
1472
1473
1474
1475

1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486

1487

1488
1489
1490
1491
1492
1493
1494




1495
1496
1497
1498
1499
1500
1501
      rc = pSession->rc = sessionTableInfo(db, zDb, 
          pTo->zName, &pTo->nCol, 0, &pTo->azCol, &pTo->abPK
      );
    }

    /* Check the table schemas match */
    if( rc==SQLITE_OK ){

      int nCol;                   /* Columns in zFrom.zTbl */
      u8 *abPK;
      const char **azCol = 0;
      rc = sessionTableInfo(db, zFrom, zTbl, &nCol, 0, &azCol, &abPK);
      if( rc==SQLITE_OK ){
        int bMismatch = 0;
        if( pTo->nCol!=nCol || memcmp(pTo->abPK, abPK, nCol) ){
          bMismatch = 1;
        }else{
          int i;
          for(i=0; i<nCol; i++){

            if( sqlite3_stricmp(azCol[i], pTo->azCol[i]) ) bMismatch = 1;

          }
        }

        if( bMismatch ){
          *pzErrMsg = sqlite3_mprintf("table schemas do not match");
          rc = SQLITE_ERROR;
        }




      }
      sqlite3_free(azCol);
    }

    if( rc==SQLITE_OK ){
      zExpr = sessionExprComparePK(pTo->nCol, 
          zDb, zFrom, pTo->zName, pTo->azCol, pTo->abPK







>






|




>

>







>
>
>
>







1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
      rc = pSession->rc = sessionTableInfo(db, zDb, 
          pTo->zName, &pTo->nCol, 0, &pTo->azCol, &pTo->abPK
      );
    }

    /* Check the table schemas match */
    if( rc==SQLITE_OK ){
      int bHasPk = 0;
      int nCol;                   /* Columns in zFrom.zTbl */
      u8 *abPK;
      const char **azCol = 0;
      rc = sessionTableInfo(db, zFrom, zTbl, &nCol, 0, &azCol, &abPK);
      if( rc==SQLITE_OK ){
        int bMismatch = 0;
        if( pTo->nCol!=nCol ){
          bMismatch = 1;
        }else{
          int i;
          for(i=0; i<nCol; i++){
            if( pTo->abPK[i]!=abPK[i] ) bMismatch = 1;
            if( sqlite3_stricmp(azCol[i], pTo->azCol[i]) ) bMismatch = 1;
            if( abPK[i] ) bHasPk = 1;
          }
        }

        if( bMismatch ){
          *pzErrMsg = sqlite3_mprintf("table schemas do not match");
          rc = SQLITE_ERROR;
        }
        if( bHasPk==0 ){
          *pzErrMsg = sqlite3_mprintf("table has no primary key");
          rc = SQLITE_ERROR;
        }
      }
      sqlite3_free(azCol);
    }

    if( rc==SQLITE_OK ){
      zExpr = sessionExprComparePK(pTo->nCol, 
          zDb, zFrom, pTo->zName, pTo->azCol, pTo->abPK

Changes to ext/session/test_session.c.

224
225
226
227
228
229
230

231
232
233
234
235
236
237
          Tcl_GetString(objv[2]),
          Tcl_GetString(objv[3]),
          &zErr
      );
      assert( rc!=SQLITE_OK || zErr==0 );
      if( zErr ){
        Tcl_AppendResult(interp, zErr, 0);

        return TCL_ERROR;
      }
      if( rc ){
        return test_session_error(interp, rc);
      }
      break;
    }







>







224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
          Tcl_GetString(objv[2]),
          Tcl_GetString(objv[3]),
          &zErr
      );
      assert( rc!=SQLITE_OK || zErr==0 );
      if( zErr ){
        Tcl_AppendResult(interp, zErr, 0);
        sqlite3_free(zErr);
        return TCL_ERROR;
      }
      if( rc ){
        return test_session_error(interp, rc);
      }
      break;
    }