github.com/ncruces/go-sqlite3@v0.15.1-0.20240520133447-53eef1510ff0/vfs/tests/mptest/testdata/multiwrite01.test (about)

     1  /*
     2  ** This script sets up five different tasks all writing and updating
     3  ** the database at the same time, but each in its own table.
     4  */
     5  --task 1 build-t1
     6    DROP TABLE IF EXISTS t1;
     7    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
     8    --sleep 1
     9    INSERT INTO t1 VALUES(1, randomblob(2000));
    10    INSERT INTO t1 VALUES(2, randomblob(1000));
    11    --sleep 1
    12    INSERT INTO t1 SELECT a+2, randomblob(1500) FROM t1;
    13    INSERT INTO t1 SELECT a+4, randomblob(1500) FROM t1;
    14    INSERT INTO t1 SELECT a+8, randomblob(1500) FROM t1;
    15    --sleep 1
    16    INSERT INTO t1 SELECT a+16, randomblob(1500) FROM t1;
    17    --sleep 1
    18    INSERT INTO t1 SELECT a+32, randomblob(1500) FROM t1;
    19    SELECT count(*) FROM t1;
    20    --match 64
    21    SELECT avg(length(b)) FROM t1;
    22    --match 1500.0
    23    --sleep 2
    24    UPDATE t1 SET b='x'||a||'y';
    25    SELECT sum(length(b)) FROM t1;
    26    --match 247
    27    SELECT a FROM t1 WHERE b='x17y';
    28    --match 17
    29    CREATE INDEX t1b ON t1(b);
    30    SELECT a FROM t1 WHERE b='x17y';
    31    --match 17
    32    SELECT a FROM t1 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;
    33    --match 29 28 27 26 25
    34  --end
    35  
    36  
    37  --task 2 build-t2
    38    DROP TABLE IF EXISTS t2;
    39    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    40    --sleep 1
    41    INSERT INTO t2 VALUES(1, randomblob(2000));
    42    INSERT INTO t2 VALUES(2, randomblob(1000));
    43    --sleep 1
    44    INSERT INTO t2 SELECT a+2, randomblob(1500) FROM t2;
    45    INSERT INTO t2 SELECT a+4, randomblob(1500) FROM t2;
    46    INSERT INTO t2 SELECT a+8, randomblob(1500) FROM t2;
    47    --sleep 1
    48    INSERT INTO t2 SELECT a+16, randomblob(1500) FROM t2;
    49    --sleep 1
    50    INSERT INTO t2 SELECT a+32, randomblob(1500) FROM t2;
    51    SELECT count(*) FROM t2;
    52    --match 64
    53    SELECT avg(length(b)) FROM t2;
    54    --match 1500.0
    55    --sleep 2
    56    UPDATE t2 SET b='x'||a||'y';
    57    SELECT sum(length(b)) FROM t2;
    58    --match 247
    59    SELECT a FROM t2 WHERE b='x17y';
    60    --match 17
    61    CREATE INDEX t2b ON t2(b);
    62    SELECT a FROM t2 WHERE b='x17y';
    63    --match 17
    64    SELECT a FROM t2 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;
    65    --match 29 28 27 26 25
    66  --end
    67  
    68  --task 3 build-t3
    69    DROP TABLE IF EXISTS t3;
    70    CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
    71    --sleep 1
    72    INSERT INTO t3 VALUES(1, randomblob(2000));
    73    INSERT INTO t3 VALUES(2, randomblob(1000));
    74    --sleep 1
    75    INSERT INTO t3 SELECT a+2, randomblob(1500) FROM t3;
    76    INSERT INTO t3 SELECT a+4, randomblob(1500) FROM t3;
    77    INSERT INTO t3 SELECT a+8, randomblob(1500) FROM t3;
    78    --sleep 1
    79    INSERT INTO t3 SELECT a+16, randomblob(1500) FROM t3;
    80    --sleep 1
    81    INSERT INTO t3 SELECT a+32, randomblob(1500) FROM t3;
    82    SELECT count(*) FROM t3;
    83    --match 64
    84    SELECT avg(length(b)) FROM t3;
    85    --match 1500.0
    86    --sleep 2
    87    UPDATE t3 SET b='x'||a||'y';
    88    SELECT sum(length(b)) FROM t3;
    89    --match 247
    90    SELECT a FROM t3 WHERE b='x17y';
    91    --match 17
    92    CREATE INDEX t3b ON t3(b);
    93    SELECT a FROM t3 WHERE b='x17y';
    94    --match 17
    95    SELECT a FROM t3 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;
    96    --match 29 28 27 26 25
    97  --end
    98  
    99  --task 4 build-t4
   100    DROP TABLE IF EXISTS t4;
   101    CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
   102    --sleep 1
   103    INSERT INTO t4 VALUES(1, randomblob(2000));
   104    INSERT INTO t4 VALUES(2, randomblob(1000));
   105    --sleep 1
   106    INSERT INTO t4 SELECT a+2, randomblob(1500) FROM t4;
   107    INSERT INTO t4 SELECT a+4, randomblob(1500) FROM t4;
   108    INSERT INTO t4 SELECT a+8, randomblob(1500) FROM t4;
   109    --sleep 1
   110    INSERT INTO t4 SELECT a+16, randomblob(1500) FROM t4;
   111    --sleep 1
   112    INSERT INTO t4 SELECT a+32, randomblob(1500) FROM t4;
   113    SELECT count(*) FROM t4;
   114    --match 64
   115    SELECT avg(length(b)) FROM t4;
   116    --match 1500.0
   117    --sleep 2
   118    UPDATE t4 SET b='x'||a||'y';
   119    SELECT sum(length(b)) FROM t4;
   120    --match 247
   121    SELECT a FROM t4 WHERE b='x17y';
   122    --match 17
   123    CREATE INDEX t4b ON t4(b);
   124    SELECT a FROM t4 WHERE b='x17y';
   125    --match 17
   126    SELECT a FROM t4 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;
   127    --match 29 28 27 26 25
   128  --end
   129  
   130  --task 5 build-t5
   131    DROP TABLE IF EXISTS t5;
   132    CREATE TABLE t5(a INTEGER PRIMARY KEY, b);
   133    --sleep 1
   134    INSERT INTO t5 VALUES(1, randomblob(2000));
   135    INSERT INTO t5 VALUES(2, randomblob(1000));
   136    --sleep 1
   137    INSERT INTO t5 SELECT a+2, randomblob(1500) FROM t5;
   138    INSERT INTO t5 SELECT a+4, randomblob(1500) FROM t5;
   139    INSERT INTO t5 SELECT a+8, randomblob(1500) FROM t5;
   140    --sleep 1
   141    INSERT INTO t5 SELECT a+16, randomblob(1500) FROM t5;
   142    --sleep 1
   143    INSERT INTO t5 SELECT a+32, randomblob(1500) FROM t5;
   144    SELECT count(*) FROM t5;
   145    --match 64
   146    SELECT avg(length(b)) FROM t5;
   147    --match 1500.0
   148    --sleep 2
   149    UPDATE t5 SET b='x'||a||'y';
   150    SELECT sum(length(b)) FROM t5;
   151    --match 247
   152    SELECT a FROM t5 WHERE b='x17y';
   153    --match 17
   154    CREATE INDEX t5b ON t5(b);
   155    SELECT a FROM t5 WHERE b='x17y';
   156    --match 17
   157    SELECT a FROM t5 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;
   158    --match 29 28 27 26 25
   159  --end
   160  
   161  --wait all
   162  SELECT count(*), sum(length(b)) FROM t1;
   163  --match 64 247
   164  SELECT count(*), sum(length(b)) FROM t2;
   165  --match 64 247
   166  SELECT count(*), sum(length(b)) FROM t3;
   167  --match 64 247
   168  SELECT count(*), sum(length(b)) FROM t4;
   169  --match 64 247
   170  SELECT count(*), sum(length(b)) FROM t5;
   171  --match 64 247
   172  
   173  --task 1
   174    SELECT t1.a FROM t1, t2
   175     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   176     ORDER BY t1.a LIMIT 4
   177    --match 33 34 35 36
   178    SELECT t3.a FROM t3, t4
   179     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   180     ORDER BY t3.a LIMIT 7
   181    --match 45 46 47 48 49 50 51
   182  --end
   183  --task 5
   184    SELECT t1.a FROM t1, t2
   185     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   186     ORDER BY t1.a LIMIT 4
   187    --match 33 34 35 36
   188    SELECT t3.a FROM t3, t4
   189     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   190     ORDER BY t3.a LIMIT 7
   191    --match 45 46 47 48 49 50 51
   192  --end
   193  --task 3
   194    SELECT t1.a FROM t1, t2
   195     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   196     ORDER BY t1.a LIMIT 4
   197    --match 33 34 35 36
   198    SELECT t3.a FROM t3, t4
   199     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   200     ORDER BY t3.a LIMIT 7
   201    --match 45 46 47 48 49 50 51
   202  --end
   203  --task 2
   204    SELECT t1.a FROM t1, t2
   205     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   206     ORDER BY t1.a LIMIT 4
   207    --match 33 34 35 36
   208    SELECT t3.a FROM t3, t4
   209     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   210     ORDER BY t3.a LIMIT 7
   211    --match 45 46 47 48 49 50 51
   212  --end
   213  --task 4
   214    SELECT t1.a FROM t1, t2
   215     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   216     ORDER BY t1.a LIMIT 4
   217    --match 33 34 35 36
   218    SELECT t3.a FROM t3, t4
   219     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   220     ORDER BY t3.a LIMIT 7
   221    --match 45 46 47 48 49 50 51
   222  --end
   223  --wait all
   224  
   225  --task 5
   226    DROP INDEX t5b;
   227    --sleep 5
   228    PRAGMA integrity_check(10);
   229    --match ok
   230    CREATE INDEX t5b ON t5(b DESC);
   231  --end
   232  --task 3
   233    DROP INDEX t3b;
   234    --sleep 5
   235    PRAGMA integrity_check(10);
   236    --match ok
   237    CREATE INDEX t3b ON t3(b DESC);
   238  --end
   239  --task 1
   240    DROP INDEX t1b;
   241    --sleep 5
   242    PRAGMA integrity_check(10);
   243    --match ok
   244    CREATE INDEX t1b ON t1(b DESC);
   245  --end
   246  --task 2
   247    DROP INDEX t2b;
   248    --sleep 5
   249    PRAGMA integrity_check(10);
   250    --match ok
   251    CREATE INDEX t2b ON t2(b DESC);
   252  --end
   253  --task 4
   254    DROP INDEX t4b;
   255    --sleep 5
   256    PRAGMA integrity_check(10);
   257    --match ok
   258    CREATE INDEX t4b ON t4(b DESC);
   259  --end
   260  --wait all
   261  
   262  --task 1
   263    SELECT t1.a FROM t1, t2
   264     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   265     ORDER BY t1.a LIMIT 4
   266    --match 33 34 35 36
   267    SELECT t3.a FROM t3, t4
   268     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   269     ORDER BY t3.a LIMIT 7
   270    --match 45 46 47 48 49 50 51
   271  --end
   272  --task 5
   273    SELECT t1.a FROM t1, t2
   274     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   275     ORDER BY t1.a LIMIT 4
   276    --match 33 34 35 36
   277    SELECT t3.a FROM t3, t4
   278     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   279     ORDER BY t3.a LIMIT 7
   280    --match 45 46 47 48 49 50 51
   281  --end
   282  --task 3
   283    SELECT t1.a FROM t1, t2
   284     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   285     ORDER BY t1.a LIMIT 4
   286    --match 33 34 35 36
   287    SELECT t3.a FROM t3, t4
   288     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   289     ORDER BY t3.a LIMIT 7
   290    --match 45 46 47 48 49 50 51
   291  --end
   292  --task 2
   293    SELECT t1.a FROM t1, t2
   294     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   295     ORDER BY t1.a LIMIT 4
   296    --match 33 34 35 36
   297    SELECT t3.a FROM t3, t4
   298     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   299     ORDER BY t3.a LIMIT 7
   300    --match 45 46 47 48 49 50 51
   301  --end
   302  --task 4
   303    SELECT t1.a FROM t1, t2
   304     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   305     ORDER BY t1.a LIMIT 4
   306    --match 33 34 35 36
   307    SELECT t3.a FROM t3, t4
   308     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   309     ORDER BY t3.a LIMIT 7
   310    --match 45 46 47 48 49 50 51
   311  --end
   312  --wait all
   313  
   314  VACUUM;
   315  PRAGMA integrity_check(10);
   316  --match ok
   317  
   318  --task 1
   319    UPDATE t1 SET b=randomblob(20000);
   320    --sleep 5
   321    UPDATE t1 SET b='x'||a||'y';
   322    SELECT a FROM t1 WHERE b='x63y';
   323    --match 63
   324  --end
   325  --task 2
   326    UPDATE t2 SET b=randomblob(20000);
   327    --sleep 5
   328    UPDATE t2 SET b='x'||a||'y';
   329    SELECT a FROM t2 WHERE b='x63y';
   330    --match 63
   331  --end
   332  --task 3
   333    UPDATE t3 SET b=randomblob(20000);
   334    --sleep 5
   335    UPDATE t3 SET b='x'||a||'y';
   336    SELECT a FROM t3 WHERE b='x63y';
   337    --match 63
   338  --end
   339  --task 4
   340    UPDATE t4 SET b=randomblob(20000);
   341    --sleep 5
   342    UPDATE t4 SET b='x'||a||'y';
   343    SELECT a FROM t4 WHERE b='x63y';
   344    --match 63
   345  --end
   346  --task 5
   347    UPDATE t5 SET b=randomblob(20000);
   348    --sleep 5
   349    UPDATE t5 SET b='x'||a||'y';
   350    SELECT a FROM t5 WHERE b='x63y';
   351    --match 63
   352  --end
   353  --wait all
   354  
   355  --task 1
   356    SELECT t1.a FROM t1, t2
   357     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   358     ORDER BY t1.a LIMIT 4
   359    --match 33 34 35 36
   360    SELECT t3.a FROM t3, t4
   361     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   362     ORDER BY t3.a LIMIT 7
   363    --match 45 46 47 48 49 50 51
   364    PRAGMA integrity_check;
   365    --match ok
   366  --end
   367  --task 5
   368    SELECT t1.a FROM t1, t2
   369     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   370     ORDER BY t1.a LIMIT 4
   371    --match 33 34 35 36
   372    SELECT t3.a FROM t3, t4
   373     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   374     ORDER BY t3.a LIMIT 7
   375    --match 45 46 47 48 49 50 51
   376    PRAGMA integrity_check;
   377    --match ok
   378  --end
   379  --task 3
   380    SELECT t1.a FROM t1, t2
   381     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   382     ORDER BY t1.a LIMIT 4
   383    --match 33 34 35 36
   384    SELECT t3.a FROM t3, t4
   385     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   386     ORDER BY t3.a LIMIT 7
   387    --match 45 46 47 48 49 50 51
   388    PRAGMA integrity_check;
   389    --match ok
   390  --end
   391  --task 2
   392    SELECT t1.a FROM t1, t2
   393     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   394     ORDER BY t1.a LIMIT 4
   395    --match 33 34 35 36
   396    SELECT t3.a FROM t3, t4
   397     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   398     ORDER BY t3.a LIMIT 7
   399    --match 45 46 47 48 49 50 51
   400    PRAGMA integrity_check;
   401    --match ok
   402  --end
   403  --task 4
   404    SELECT t1.a FROM t1, t2
   405     WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')
   406     ORDER BY t1.a LIMIT 4
   407    --match 33 34 35 36
   408    SELECT t3.a FROM t3, t4
   409     WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')
   410     ORDER BY t3.a LIMIT 7
   411    --match 45 46 47 48 49 50 51
   412    PRAGMA integrity_check;
   413    --match ok
   414  --end
   415  --wait all