SQLite

Check-in [cb236cb985]
Login

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

Overview
Comment:Enhance the virtual table in test_schemapool.c so that it can be used to check that SHARED_SCHEMA connections are not allocating and freeing schemas when they should not be.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256: cb236cb98564b870317ba3e481a3c7d7f9769b0294a01246bcb724f04e1e7b10
User & Date: dan 2019-02-15 19:36:47.869
Context
2019-02-18
18:16
Ensure that creating temp schema items does not cause an OPEN_SHARABLE_SCHEMA connection to load all schemas into memory. (check-in: 88cbf54eee user: dan tags: reuse-schema)
2019-02-15
19:36
Enhance the virtual table in test_schemapool.c so that it can be used to check that SHARED_SCHEMA connections are not allocating and freeing schemas when they should not be. (check-in: cb236cb985 user: dan tags: reuse-schema)
19:00
Fix a problem with eponymous virtual tables and SHARED_SCHEMA databases. Also, after preparing statements that require all database schemas (REINDEX, ANALYZE, CREATE, DROP and some PRAGMA statements), do not allow the database connection to return more than one schema to each schema-pool. (check-in: ecf6251ec0 user: dan tags: reuse-schema)
Changes
Side-by-Side Diff Show Whitespace Changes Patch
Changes to src/callback.c.
︙
29
30
31
32
33
34
35

36
37
38
39
40
41
42

29
30
31
32
33
34
35
36
37
38
39
40
41
42
43








+








**      SchemaPool. DB_SchemaLoaded flag is set.
**
**   3) pSPool!=0, pSchema points to the SchemaPool's static object
**      (SchemaPool.sSchema).
*/
struct SchemaPool {
  int nRef;                       /* Number of pointers to this object */
  int nDelete;                    /* Schema objects deleted by ReleaseAll() */
  u64 cksum;                      /* Checksum for this Schema contents */
  Schema *pSchema;                /* Linked list of Schema objects */
  Schema sSchema;                 /* The single dummy schema object */
  SchemaPool *pNext;              /* Next element in schemaPoolList */
};

#ifdef SQLITE_DEBUG
︙
618
619
620
621
622
623
624

625
626
627
628
629
630
631

619
620
621
622
623
624
625
626
627
628
629
630
631
632
633








+








  ** at least one other copy of the schema being released, delete it instead
  ** of returning it to the schema-pool.  */
  if( db->mDbFlags & DBFLAG_FreeSchema ){
    int i;
    for(i=0; i<db->nDb; i++){
      Db *p = &db->aDb[i];
      if( p!=pDb && p->pSchema!=&pSPool->sSchema && pDb->pSPool==p->pSPool ){
        pSPool->nDelete++;
        schemaDelete(pRelease);
        return;
      }
    }
  }

  pRelease->pNext = pDb->pSPool->pSchema;
︙
Changes to src/test_schemapool.c.
︙
25
26
27
28
29
30
31
32


33


34
35
36
37
38
39
40

25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43








-
+
+

+
+








/* The code in this file defines a sqlite3 virtual-table module with
** the following schema.
*/
#define SCHEMAPOOL_SCHEMA \
"CREATE TABLE x("         \
"  cksum   INTEGER, "     \
"  nref    INTEGER, "     \
"  nschema INTEGER  "     \
"  nschema INTEGER, "     \
"  ndelete INTEGER  "     \
")"

#define SCHEMAPOOL_NFIELD 4

typedef struct schemapool_vtab schemapool_vtab;
typedef struct schemapool_cursor schemapool_cursor;

/* A schema table object */
struct schemapool_vtab {
  sqlite3_vtab base;
︙
102
103
104
105
106
107
108
109
110


111
112
113
114
115
116
117

105
106
107
108
109
110
111


112
113
114
115
116
117
118
119
120








-
-
+
+








}

/*
** Retrieve a column of data.
*/
static int schemaPoolColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
  schemapool_cursor *pCur = (schemapool_cursor*)cur;
  assert( i==0 || i==1 || i==2 );
  sqlite3_result_int64(ctx, pCur->aData[pCur->iRow*3 + i]);
  assert( i==0 || i==1 || i==2 || i==3 );
  sqlite3_result_int64(ctx, pCur->aData[pCur->iRow*SCHEMAPOOL_NFIELD + i]);
  return SQLITE_OK;
}

/*
** Retrieve the current rowid.
*/
static int schemaPoolRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
︙
132
133
134
135
136
137
138

139
140
141
142
143
144
145

135
136
137
138
139
140
141
142
143
144
145
146
147
148
149








+








  schemapool_cursor *pCur = (schemapool_cursor*)cur;
  pCur->iRow++;
  return SQLITE_OK;
}

struct SchemaPool {
  int nRef;                       /* Number of pointers to this object */
  int nDelete;                    /* Schema objects deleted by ReleaseAll() */
  u64 cksum;                      /* Checksum for this Schema contents */
  Schema *pSchema;                /* Linked list of Schema objects */
  Schema sSchema;                 /* The single dummy schema object */
  SchemaPool *pNext;              /* Next element in schemaPoolList */
};
extern SchemaPool *sqlite3SchemaPoolList(void);
︙
161
162
163
164
165
166
167

168

169
170
171
172
173
174
175
176
177
178




179
180
181
182
183
184
185

165
166
167
168
169
170
171
172

173
174
175
176
177
178
179
180



181
182
183
184
185
186
187
188
189
190
191








+
-
+







-
-
-
+
+
+
+









  for(pSPool = sqlite3SchemaPoolList(); pSPool; pSPool=pSPool->pNext){
    pCur->nRow++;
  }

  if( pCur->nRow ){
    int iRow = 0;
    int nByte = SCHEMAPOOL_NFIELD * pCur->nRow * sizeof(i64);
    pCur->aData = (i64*)sqlite3_malloc(3 * pCur->nRow * sizeof(i64));
    pCur->aData = (i64*)sqlite3_malloc(nByte);
    if( pCur->aData==0 ) return SQLITE_NOMEM;
    for(pSPool = sqlite3SchemaPoolList(); pSPool; pSPool=pSPool->pNext){
      Schema *p;
      i64 nSchema = 0;
      for(p=pSPool->pSchema; p; p=p->pNext){
        nSchema++;
      }
      pCur->aData[0 + iRow*3] = pSPool->cksum;
      pCur->aData[1 + iRow*3] = (i64)pSPool->nRef;
      pCur->aData[2 + iRow*3] = nSchema;
      pCur->aData[0 + iRow*SCHEMAPOOL_NFIELD] = pSPool->cksum;
      pCur->aData[1 + iRow*SCHEMAPOOL_NFIELD] = (i64)pSPool->nRef;
      pCur->aData[2 + iRow*SCHEMAPOOL_NFIELD] = nSchema;
      pCur->aData[3 + iRow*SCHEMAPOOL_NFIELD] = (i64)pSPool->nDelete;
      iRow++;
    }
  }

  return SQLITE_OK;
}
︙
Changes to test/reuse2.test.
︙
70
71
72
73
74
75
76
77
78



79
80
81
82
83
84
85



86
87
88
89
90



91
92
93
94
95
96
97
98



99
100
101
102
103



104
105
106
107
108



109
110
111
112
113



114
115
116
117
118
119
120

70
71
72
73
74
75
76


77
78
79
80
81
82
83
84


85
86
87
88
89
90


91
92
93
94
95
96
97
98
99


100
101
102
103
104
105


106
107
108
109
110
111


112
113
114
115
116
117


118
119
120
121
122
123
124
125
126
127








-
-
+
+
+





-
-
+
+
+



-
-
+
+
+






-
-
+
+
+



-
-
+
+
+



-
-
+
+
+



-
-
+
+
+








} {}

do_execsql_test -db db1 3.2.1 { SELECT * FROM t1 }
do_execsql_test -db db2 3.2.2 { SELECT * FROM t1 }

register_schemapool_module db
do_execsql_test 3.3 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=2 nschema=1 ndelete=0}

sqlite3 db3 test.db -shared-schema 1
register_schemapool_module db3

do_execsql_test 3.5 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=2 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=2 nschema=1 ndelete=0}

do_execsql_test -db db3 3.6 { 
  SELECT * FROM t1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=3 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=3 nschema=1 ndelete=0}

do_execsql_test 3.7 { 
  CREATE TABLE t2(x);
}

do_execsql_test 3.8 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=3 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=3 nschema=1 ndelete=0}

do_execsql_test -db db1 3.9.1 { SELECT * FROM t1 }
do_execsql_test 3.9.2 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=1 nschema=1 nref=2 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=1 nschema=1 ndelete=0 nref=2 nschema=1 ndelete=0}

do_execsql_test -db db2 3.10.1 { SELECT * FROM t1 }
do_execsql_test 3.10.2 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=1 nschema=1 nref=2 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool ORDER BY 1;
} {nref=1 nschema=1 ndelete=0 nref=2 nschema=1 ndelete=0}

do_execsql_test -db db3 3.11.1 { SELECT * FROM t1 }
do_execsql_test 3.11.2 { 
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=3 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=3 nschema=1 ndelete=0}

#--------------------------------------------------------------------------
catch {db1 close}
catch {db2 close}
catch {db3 close}
reset_db
do_execsql_test 4.0.1 {
︙
130
131
132
133
134
135
136
137


138
139
140
141
142
143
144
145
146
147
148
149
150


151
152
153
154
155
156
157



158
159
160
161
162
163



164
165
166
167
168
169



170
171
172
173
174
175
176



177
178
179
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
220

221
222
223
224
225
226


227
228

229
230
231
232
233
234
235
236
237
238


239
240
241

242
243
244
245
246
247



248
249
250
251
252
253
254

137
138
139
140
141
142
143

144
145
146
147
148
149
150
151
152
153
154
155
156
157

158
159
160
161
162
163
164


165
166
167
168
169
170
171


172
173
174
175
176
177
178


179
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

220
221
222

223
224
225
226
227
228
229
230
231
232

233
234
235
236

237
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
268
269
270
271
272
273
274








-
+
+












-
+
+





-
-
+
+
+




-
-
+
+
+




-
-
+
+
+





-
-
+
+
+









-
-
+
+
+








-
+
+


-
+





-
+
+

-
+









-
+
+


-
+





-
+
+

-
+









-
+
+


-
+




-
-
+
+
+








  }
  sqlite3 db  test.db
  sqlite3 db2 test.db -shared-schema 1
} {}

register_schemapool_module db
do_execsql_test 4.0.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {}

do_test 4.1.1 {
  execsql { 
    ATTACH 'test.db1' AS db1; 
    ATTACH 'test.db2' AS db2;
    ATTACH 'test.db3' AS db3;
    ATTACH 'test.db4' AS db4;
    ATTACH 'test.db5' AS db5;
  } db2
} {}
do_execsql_test 4.1.2 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {}
do_execsql_test -db db2 4.1.3 {
  SELECT * FROM db3.x1
}
do_execsql_test 4.1.4 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=1 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=1 nschema=1 ndelete=0}
do_execsql_test -db db2 4.1.5 {
  SELECT * FROM db2.x1
}
do_execsql_test 4.1.6 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=2 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=2 nschema=1 ndelete=0}
do_execsql_test -db db2 4.1.7 {
  SELECT * FROM x1
}
do_execsql_test 4.1.8 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=6 nschema=1 ndelete=0}

do_test 4.2.1 {
  catchsql { SELECT * FROM abc } db2
} {1 {no such table: abc}}
do_execsql_test 4.2.2 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=6 nschema=1 ndelete=0}

register_schemapool_module db2
do_execsql_test -db db2 4.3.1 {
  INSERT INTO x1 VALUES(1, 2, 3);
  INSERT INTO db1.x1 VALUES(4, 5, 6);
  INSERT INTO db2.x1 VALUES(7, 8, 9);
  INSERT INTO db3.x1 VALUES(10, 11, 12);
  INSERT INTO db4.x1 VALUES(13, 14, 15);
  INSERT INTO db5.x1 VALUES(16, 17, 18);
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=6 nschema=1 ndelete=0}

do_execsql_test -db db2 4.3.2 {
  SELECT * FROM db5.x1;
  SELECT * FROM db4.x1;
  SELECT * FROM db3.x1;
  SELECT * FROM db2.x1;
  SELECT * FROM db1.x1;
  SELECT * FROM x1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {
  16 17 18  13 14 15  10 11 12  7 8 9  4 5 6  1 2 3
  nref=6 nschema=1
  nref=6 nschema=1 ndelete=0
}

do_execsql_test -db db2 4.3.3 {
  UPDATE x1 SET a=a+10;
  UPDATE db5.x1 SET a=a+10;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {
  nref=6 nschema=1
  nref=6 nschema=1 ndelete=0
}

do_execsql_test -db db2 4.3.4 {
  SELECT * FROM db5.x1;
  SELECT * FROM db4.x1;
  SELECT * FROM db3.x1;
  SELECT * FROM db2.x1;
  SELECT * FROM db1.x1;
  SELECT * FROM x1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {
  26 17 18  13 14 15  10 11 12  7 8 9  4 5 6  11 2 3
  nref=6 nschema=1
  nref=6 nschema=1 ndelete=0
}

do_execsql_test -db db2 4.3.5 {
  DELETE FROM db3.x1;
  DELETE FROM x1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {
  nref=6 nschema=1
  nref=6 nschema=1 ndelete=0
}

do_execsql_test -db db2 4.3.6 {
  SELECT * FROM db5.x1;
  SELECT * FROM db4.x1;
  SELECT * FROM db3.x1;
  SELECT * FROM db2.x1;
  SELECT * FROM db1.x1;
  SELECT * FROM x1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {
  26 17 18  13 14 15  7 8 9  4 5 6 
  nref=6 nschema=1
  nref=6 nschema=1 ndelete=0
}

do_execsql_test -db db2 4.3.6 {
  SELECT * FROM db5.x1, db4.x1, db1.x1;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {26 17 18 13 14 15 4 5 6 nref=6 nschema=3}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {26 17 18 13 14 15 4 5 6 nref=6 nschema=3 ndelete=0}

#--------------------------------------------------------------------------
# Test the incremental-blob API with REUSE_SCHEMA connections.
#
catch {db1 close}
catch {db2 close}
catch {db3 close}
︙
265
266
267
268
269
270
271
272


273
274

275
276
277
278
279
280
281
282
283
284
285
286
287
288



289
290
291
292
293
294
295
296
297
298
299



300
301
302
303
304
305



306
307
308

285
286
287
288
289
290
291

292
293
294

295
296
297
298
299
300
301
302
303
304
305
306
307


308
309
310
311
312
313
314
315
316
317
318
319


320
321
322
323

324
325


326
327
328
329
330
331








-
+
+

-
+












-
-
+
+
+









-
-
+
+
+

-


-
-
+
+
+




    forcecopy test.db test.db${i}
    sqlite3 db test.db${i}
    db eval { INSERT INTO bbb VALUES(123, 'database_' || $i) }
    db close
    db2 eval "ATTACH 'test.db${i}' AS db${i}"
  }
  execsql {
    SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
    SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
      FROM schemapool;
  } db2
} {nref=6 nschema=1}
} {nref=6 nschema=1 ndelete=0}

do_test 5.1.1 {
  set res [list]
  for {set i 1} {$i<6} {incr i} {
    set chan [db2 incrblob db${i} bbb b 123]
    lappend res [gets $chan]
    close $chan
  }
  set res
} {database_1 database_2 database_3 database_4 database_5}

do_execsql_test -db db2 5.1.2 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=6 nschema=1 ndelete=0}

do_test 5.2.1 {
  sqlite3_table_column_metadata db2 main bbb a
} {INTEGER BINARY 0 1 0}
do_test 5.2.2 {
  sqlite3_table_column_metadata db2 main bbb b
} {{} BINARY 0 0 0}

do_execsql_test -db db2 5.2.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {nref=6 nschema=1 ndelete=0}

breakpoint
do_execsql_test -db db2 5.2.4 {
  PRAGMA integrity_check;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {ok nref=6 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete
  FROM schemapool;
} {ok nref=6 nschema=1 ndelete=5}

finish_test
Changes to test/reuse3.test.
︙
152
153
154
155
156
157
158

159
160




161

162
163
164
165
166
167
168
169
170
171
172
173
174
175



176
177
178

152
153
154
155
156
157
158
159


160
161
162
163
164
165
166
167
168
169
170
171
172
173

174
175
176


177
178
179
180
181
182








+
-
-
+
+
+
+

+








-



-
-
+
+
+




  for {set i 1} {$i < 5} {incr i} {
    forcedelete test.db${i} test.db${i}-wal test.db${i}-journal
    forcecopy test.db test.db${i}
    execsql "ATTACH 'test.db${i}' AS db${i}"
  }
  register_schemapool_module db
  set {} {}
  execsql { 
  execsql { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool }
} {nref=5 nschema=1}
    SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete 
    FROM schemapool 
  }
} {nref=5 nschema=1 ndelete=0}

breakpoint
do_execsql_test 4.1.3 {
  REINDEX x1;
  REINDEX x1a;
  REINDEX x1b;
  REINDEX x1c;
  REINDEX db1.x1a;
  REINDEX db2.x1b;
  REINDEX db3.x1c;
  REINDEX;
}

do_execsql_test 4.1.4 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=5 nschema=1}
  SELECT 'nref=' || nRef, 'nschema=' || nSchema, 'ndelete=' || nDelete 
    FROM schemapool 
} {nref=5 nschema=1 ndelete=28}

finish_test