SQLite

Check-in [39c56c836a]
Login

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

Overview
Comment:In sqlite3-rsync, open the replica database using ATTACH since the sqlite3_dbpage virtual table is technically a part of "main". This avoids locking problems.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 39c56c836a8ae52c5b42cc0d04b92f7c0e6889a03c4fdffd301209383b7c1f87
User & Date: drh 2024-09-16 10:58:11.340
Context
2024-09-16
14:11
Cleanup the sqlite3-rsync executable as part of "make clean" (check-in: 8c5e481b49 user: drh tags: trunk)
10:58
In sqlite3-rsync, open the replica database using ATTACH since the sqlite3_dbpage virtual table is technically a part of "main". This avoids locking problems. (check-in: 39c56c836a user: drh tags: trunk)
09:12
Improved error message in sqlite3-rsync if the SQL statement for ORIGIN_TXN fails. (check-in: 73bde71ed1 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to tool/sqlite3-rsync.c.
︙︙
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333

                      sqlite3_sql(pCkHash), sqlite3_errmsg(p->db));
        }
        sqlite3_reset(pCkHash);
        break;
      }
      case REPLICA_READY: {
        sqlite3_stmt *pStmt;
        int needPageOne = 0;
        sqlite3_finalize(pCkHash);
        pCkHash = 0;
        if( iPage+1<p->nPage ){
          runSql(p, "WITH RECURSIVE c(n) AS"
                    " (VALUES(%d) UNION ALL SELECT n+1 FROM c WHERE n<%d)"
                    " INSERT INTO badHash SELECT n FROM c",
                    iPage+1, p->nPage);
        }
        pStmt = prepareStmt(p,
               "SELECT pgno, data"
               "  FROM badHash JOIN sqlite_dbpage('main') USING(pgno)");
        if( pStmt==0 ) break;
        while( sqlite3_step(pStmt)==SQLITE_ROW && p->nErr==0 && p->nWrErr==0 ){
          unsigned int pgno = (unsigned int)sqlite3_column_int64(pStmt,0);
          const void *pContent = sqlite3_column_blob(pStmt, 1);
          if( pgno==1 ){
            needPageOne = 1;
          }else{
            writeByte(p, ORIGIN_PAGE);
            writeUint32(p, (unsigned int)sqlite3_column_int64(pStmt, 0));
            writeBytes(p, szPg, pContent);
            p->nPageSent++;
          }
        }
        sqlite3_finalize(pStmt);
        if( needPageOne ){
          pStmt = prepareStmt(p,
                 "SELECT data"
                 "  FROM sqlite_dbpage('main')"
                 " WHERE pgno=1"
          );
          if( pStmt==0 ) break;
          while( sqlite3_step(pStmt)==SQLITE_ROW && p->nErr==0 &&p->nWrErr==0 ){
            const void *pContent = sqlite3_column_blob(pStmt, 0);
            writeByte(p, ORIGIN_PAGE);
            writeUint32(p, 1);
            writeBytes(p, szPg, pContent);
            p->nPageSent++;
          }
          sqlite3_finalize(pStmt);
        }
        writeByte(p, ORIGIN_TXN);
        writeUint32(p, nPage);
        writeByte(p, ORIGIN_END);
        fflush(p->pOut);
        break;
      }
      default: {








<















<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
|
|
|
<








1278
1279
1280
1281
1282
1283
1284

1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299



1300
















1301
1302
1303
1304
1305

1306
1307
1308
1309
1310
1311
1312

                      sqlite3_sql(pCkHash), sqlite3_errmsg(p->db));
        }
        sqlite3_reset(pCkHash);
        break;
      }
      case REPLICA_READY: {
        sqlite3_stmt *pStmt;

        sqlite3_finalize(pCkHash);
        pCkHash = 0;
        if( iPage+1<p->nPage ){
          runSql(p, "WITH RECURSIVE c(n) AS"
                    " (VALUES(%d) UNION ALL SELECT n+1 FROM c WHERE n<%d)"
                    " INSERT INTO badHash SELECT n FROM c",
                    iPage+1, p->nPage);
        }
        pStmt = prepareStmt(p,
               "SELECT pgno, data"
               "  FROM badHash JOIN sqlite_dbpage('main') USING(pgno)");
        if( pStmt==0 ) break;
        while( sqlite3_step(pStmt)==SQLITE_ROW && p->nErr==0 && p->nWrErr==0 ){
          unsigned int pgno = (unsigned int)sqlite3_column_int64(pStmt,0);
          const void *pContent = sqlite3_column_blob(pStmt, 1);



          writeByte(p, ORIGIN_PAGE);
















          writeUint32(p, pgno);
          writeBytes(p, szPg, pContent);
          p->nPageSent++;
        }
        sqlite3_finalize(pStmt);

        writeByte(p, ORIGIN_TXN);
        writeUint32(p, nPage);
        writeByte(p, ORIGIN_END);
        fflush(p->pOut);
        break;
      }
      default: {
︙︙
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423





1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458

          ** a new ORIGIN_BEGIN with a reduced protocol version. */
          writeByte(p, REPLICA_BEGIN);
          writeByte(p, PROTOCOL_VERSION);
          break;
        }
        p->nPage = nOPage;
        p->szPage = szOPage;
        rc = sqlite3_open(p->zReplica, &p->db);
        if( rc ){
          reportError(p, "cannot open replica \"%s\": %s",
                      p->zReplica, sqlite3_errmsg(p->db));





          closeDb(p);
          break;
        }
        hashRegister(p->db);
        if( runSqlReturnUInt(p, &nRPage, "PRAGMA page_count") ){
          break;
        }
        if( nRPage==0 ){
          runSql(p, "PRAGMA page_size=%u", szOPage);
          runSql(p, "PRAGMA journal_mode=WAL");
          runSql(p, "SELECT * FROM sqlite_schema");
        }
        runSql(p, "BEGIN IMMEDIATE");
        runSqlReturnText(p, buf, "PRAGMA journal_mode");
        if( strcmp(buf, "wal")!=0 ){
          reportError(p, "replica is not in WAL mode");
          break;
        }
        runSqlReturnUInt(p, &nRPage, "PRAGMA page_count");
        runSqlReturnUInt(p, &szRPage, "PRAGMA page_size");
        if( szRPage!=szOPage ){
          reportError(p, "page size mismatch; origin is %d bytes and "
                         "replica is %d bytes", szOPage, szRPage);
          break;
        }

        pStmt = prepareStmt(p,
                   "SELECT hash(data) FROM sqlite_dbpage"
                   " WHERE pgno<=min(%d,%d)"
                   " ORDER BY pgno", nRPage, nOPage);
        while( sqlite3_step(pStmt)==SQLITE_ROW && p->nErr==0 && p->nWrErr==0 ){
          const unsigned char *a = sqlite3_column_blob(pStmt, 0);
          writeByte(p, REPLICA_HASH);
          writeBytes(p, 20, a);
          p->nHashSent++;








|

|
|
>
>
>
>
>




|



|
|
|


|




|
|







|








1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442

          ** a new ORIGIN_BEGIN with a reduced protocol version. */
          writeByte(p, REPLICA_BEGIN);
          writeByte(p, PROTOCOL_VERSION);
          break;
        }
        p->nPage = nOPage;
        p->szPage = szOPage;
        rc = sqlite3_open(":memory:", &p->db);
        if( rc ){
          reportError(p, "cannot open in-memory database: %s",
                      sqlite3_errmsg(p->db));
          closeDb(p);
          break;
        }
        runSql(p, "ATTACH %Q AS 'replica'", p->zReplica);
        if( p->nErr ){
          closeDb(p);
          break;
        }
        hashRegister(p->db);
        if( runSqlReturnUInt(p, &nRPage, "PRAGMA replica.page_count") ){
          break;
        }
        if( nRPage==0 ){
          runSql(p, "PRAGMA replica.page_size=%u", szOPage);
          runSql(p, "PRAGMA replica.journal_mode=WAL");
          runSql(p, "SELECT * FROM replica.sqlite_schema");
        }
        runSql(p, "BEGIN IMMEDIATE");
        runSqlReturnText(p, buf, "PRAGMA replica.journal_mode");
        if( strcmp(buf, "wal")!=0 ){
          reportError(p, "replica is not in WAL mode");
          break;
        }
        runSqlReturnUInt(p, &nRPage, "PRAGMA replica.page_count");
        runSqlReturnUInt(p, &szRPage, "PRAGMA replica.page_size");
        if( szRPage!=szOPage ){
          reportError(p, "page size mismatch; origin is %d bytes and "
                         "replica is %d bytes", szOPage, szRPage);
          break;
        }

        pStmt = prepareStmt(p,
                   "SELECT hash(data) FROM sqlite_dbpage('replica')"
                   " WHERE pgno<=min(%d,%d)"
                   " ORDER BY pgno", nRPage, nOPage);
        while( sqlite3_step(pStmt)==SQLITE_ROW && p->nErr==0 && p->nWrErr==0 ){
          const unsigned char *a = sqlite3_column_blob(pStmt, 0);
          writeByte(p, REPLICA_HASH);
          writeBytes(p, 20, a);
          p->nHashSent++;
︙︙
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503

      case ORIGIN_PAGE: {
        unsigned int pgno = 0;
        int rc;
        readUint32(p, &pgno);
        if( p->nErr ) break;
        if( pIns==0 ){
          pIns = prepareStmt(p,
            "INSERT INTO sqlite_dbpage(pgno,data) VALUES(?1,?2)"
          );
          if( pIns==0 ) break;
        }
        readBytes(p, szOPage, buf);
        if( p->nErr ) break;
        p->nPageSent++;
        sqlite3_bind_int64(pIns, 1, pgno);








|








1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487

      case ORIGIN_PAGE: {
        unsigned int pgno = 0;
        int rc;
        readUint32(p, &pgno);
        if( p->nErr ) break;
        if( pIns==0 ){
          pIns = prepareStmt(p,
            "INSERT INTO sqlite_dbpage(pgno,data,schema)VALUES(?1,?2,'replica')"
          );
          if( pIns==0 ) break;
        }
        readBytes(p, szOPage, buf);
        if( p->nErr ) break;
        p->nPageSent++;
        sqlite3_bind_int64(pIns, 1, pgno);
︙︙