SQLite

Check-in [9a431362dc]
Login

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

Overview
Comment:Test cases. Minor problems fixed. All appears to work now.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | cache_spill=N
Files: files | file ages | folders
SHA1: 9a431362dccbc9b8f93375f30a3b8955903cca79
User & Date: drh 2015-11-12 15:47:48.274
Context
2015-11-12
16:44
Enhance the "PRAGMA cache_spill" statement to accept an integer argument which is the threshold at which spilling will begin. (check-in: f79d264db2 user: drh tags: trunk)
15:47
Test cases. Minor problems fixed. All appears to work now. (Closed-Leaf check-in: 9a431362dc user: drh tags: cache_spill=N)
14:57
First attempt at enhancing the "PRAGMA cache_spill" statement to accept a cache threashold size. (check-in: 549d42be0d user: drh tags: cache_spill=N)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/pcache.c.
︙︙
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662

** be the larger of the szSpill and szCache.
*/
int sqlite3PcacheSetSpillsize(PCache *p, int mxPage){
  int res;
  assert( p->pCache!=0 );
  if( mxPage ){
    if( mxPage<0 ){
      mxPage = (int)((-1024*(i64)p->szCache)/(p->szPage+p->szExtra));
    }
    p->szSpill = mxPage;
  }
  res = numberOfCachePages(p);
  if( res<p->szSpill ) res = p->szSpill; 
  return res;
}








|








648
649
650
651
652
653
654
655
656
657
658
659
660
661
662

** be the larger of the szSpill and szCache.
*/
int sqlite3PcacheSetSpillsize(PCache *p, int mxPage){
  int res;
  assert( p->pCache!=0 );
  if( mxPage ){
    if( mxPage<0 ){
      mxPage = (int)((-1024*(i64)mxPage)/(p->szPage+p->szExtra));
    }
    p->szSpill = mxPage;
  }
  res = numberOfCachePages(p);
  if( res<p->szSpill ) res = p->szSpill; 
  return res;
}
︙︙
Changes to src/pragma.c.
︙︙
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779

780
781
782
783
784
785
786
787

788
789
790
791
792
793
794

  ** cache_size pages, no spilling occurs until the page count exceeds
  ** the number of cache_size pages.
  **
  ** The cache_spill=BOOLEAN setting applies to all attached schemas,
  ** not just the schema specified.
  */
  case PragTyp_CACHE_SPILL: {
    int size;
    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    if( !zRight ){
      if( sqlite3ReadSchema(pParse) ) goto pragma_out;
      returnSingleInt(v, "cache_spill", 
         (db->flags & SQLITE_CacheSpill)==0 ? 0 : 
            sqlite3BtreeSetSpillSize(pDb->pBt,0));
    }else{

      if( sqlite3GetInt32(zRight, &size) ){
        sqlite3BtreeSetSpillSize(pDb->pBt, size);
      }
      if( sqlite3GetBoolean(zRight, 0) ){
        db->flags |= SQLITE_CacheSpill;
      }else{
        db->flags &= ~SQLITE_CacheSpill;
      }

    }
    break;
  }

  /*
  **  PRAGMA [schema.]mmap_size(N)
  **








<







>



|




>








765
766
767
768
769
770
771

772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795

  ** cache_size pages, no spilling occurs until the page count exceeds
  ** the number of cache_size pages.
  **
  ** The cache_spill=BOOLEAN setting applies to all attached schemas,
  ** not just the schema specified.
  */
  case PragTyp_CACHE_SPILL: {

    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    if( !zRight ){
      if( sqlite3ReadSchema(pParse) ) goto pragma_out;
      returnSingleInt(v, "cache_spill", 
         (db->flags & SQLITE_CacheSpill)==0 ? 0 : 
            sqlite3BtreeSetSpillSize(pDb->pBt,0));
    }else{
      int size = 1;
      if( sqlite3GetInt32(zRight, &size) ){
        sqlite3BtreeSetSpillSize(pDb->pBt, size);
      }
      if( sqlite3GetBoolean(zRight, size!=0) ){
        db->flags |= SQLITE_CacheSpill;
      }else{
        db->flags &= ~SQLITE_CacheSpill;
      }
      setAllPagerFlags(db);
    }
    break;
  }

  /*
  **  PRAGMA [schema.]mmap_size(N)
  **
︙︙
Changes to test/pragma2.test.
︙︙
193
194
195
196
197
198
199
200
201
202
203
204











205
206
207
208
209
210
211
212
213
214
215
216

    ROLLBACK;
    PRAGMA cache_spill=100000;
    PRAGMA cache_spill;
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
  }
} {100000 main reserved temp unknown}   ;# Large cache spill threshold
do_test pragma2-4.5.3 {
  db eval {
    ROLLBACK;
    PRAGMA cache_spill=25;











    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
  }
} {main exclusive temp unknown}   ;# Large cache spill, so no exclusive lock


# Verify that newly attached databases inherit the cache_spill=OFF
# setting.
#
do_execsql_test pragma2-4.6 {
  ROLLBACK;








|




>
>
>
>
>
>
>
>
>
>
>




|








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
220
221
222
223
224
225
226
227

    ROLLBACK;
    PRAGMA cache_spill=100000;
    PRAGMA cache_spill;
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
  }
} {100000 main reserved temp unknown}   ;# Big spill threshold -> no excl lock
do_test pragma2-4.5.3 {
  db eval {
    ROLLBACK;
    PRAGMA cache_spill=25;
    PRAGMA main.cache_spill;
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
  }
} {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock
do_test pragma2-4.5.4 {
  db eval {
    ROLLBACK;
    PRAGMA cache_spill(-25);
    PRAGMA main.cache_spill;
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
  }
} {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock


# Verify that newly attached databases inherit the cache_spill=OFF
# setting.
#
do_execsql_test pragma2-4.6 {
  ROLLBACK;
︙︙
227
228
229
230
231
232
233




















234
235
236

sqlite3_release_memory
do_execsql_test pragma2-4.8 {
  PRAGMA cache_spill=ON; -- Applies to all databases
  BEGIN;
  UPDATE t2 SET c=c-1;
  PRAGMA lock_status;
} {main unlocked temp unknown aux1 exclusive}




















   
test_restore_config_pagecache
finish_test








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




238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267

sqlite3_release_memory
do_execsql_test pragma2-4.8 {
  PRAGMA cache_spill=ON; -- Applies to all databases
  BEGIN;
  UPDATE t2 SET c=c-1;
  PRAGMA lock_status;
} {main unlocked temp unknown aux1 exclusive}
db close
forcedelete test.db
sqlite3 db test.db

breakpoint
do_execsql_test pragma2-5.1 {
  PRAGMA page_size=16384;
  CREATE TABLE t1(x);
  PRAGMA cache_size=2;
  PRAGMA cache_spill=YES;
  PRAGMA cache_spill;
} {2}
do_execsql_test pragma2-5.2 {
  PRAGMA cache_spill=NO;
  PRAGMA cache_spill;
} {0}
do_execsql_test pragma2-5.3 {
  PRAGMA cache_spill(-51);
  PRAGMA cache_spill;
} {3}
   
test_restore_config_pagecache
finish_test