SQLite

Check-in [65444f2e35]
Login

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

Overview
Comment:Enhance faststat1.c to deal better with WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | est_count_pragma
Files: files | file ages | folders
SHA1: 65444f2e35cfd51ece1ba6e37b39d181da479137
User & Date: drh 2016-10-25 18:28:29.534
Context
2016-10-25
19:21
Further refinements to the faststat1.c utility. (check-in: b051fd19bc user: drh tags: est_count_pragma)
18:28
Enhance faststat1.c to deal better with WITHOUT ROWID tables. (check-in: 65444f2e35 user: drh tags: est_count_pragma)
17:28
Merge recent trunk changes, and especially the PRAGMA index_info enhancement which is needed on this branch. (check-in: c3570e462a user: drh tags: est_count_pragma)
Changes
Unified Diff Ignore Whitespace Patch
Changes to tool/faststat1.c.
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
      n++;
    }
    sqlite3_finalize(pStmt);
  }
  return n==0 ? 0 : (sqlite3_int64)(sum/n);
}

/*
** Stat1 for a table.
*/
static void analyzeTable(const char *zTab){
  sqlite3_int64 n = estEntryCount(zTab);
  sqlite3_stmt *pStmt;
  if( n==0 ){
    printf("-- empty table: %s\n", zTab);
    return;
  }
  pStmt = db_prepare(
     "INSERT INTO temp.est_stat1(tbl,idx,stat)"
     "VALUES(\"%w\",NULL,'%lld')", zTab, n
  );
  sqlite3_step(pStmt);
  sqlite3_finalize(pStmt);
}

/*
** Compare the i-th column of pStmt against pValue.  Return true if they
** are different.
*/
static int columnNotEqual(sqlite3_stmt *pStmt, int i, sqlite3_value *pValue){
  int n1, n2, n;
  if( sqlite3_column_type(pStmt,i)!=sqlite3_value_type(pValue) ) return 1;







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







115
116
117
118
119
120
121


















122
123
124
125
126
127
128
      n++;
    }
    sqlite3_finalize(pStmt);
  }
  return n==0 ? 0 : (sqlite3_int64)(sum/n);
}



















/*
** Compare the i-th column of pStmt against pValue.  Return true if they
** are different.
*/
static int columnNotEqual(sqlite3_stmt *pStmt, int i, sqlite3_value *pValue){
  int n1, n2, n;
  if( sqlite3_column_type(pStmt,i)!=sqlite3_value_type(pValue) ) return 1;
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
      return n1!=n2;
 
  }
  return 1;
}

/*
** Stat1 for an index;
*/
static void analyzeIndex(const char *zTab, const char *zIdx){
  sqlite3_int64 n = estEntryCount(zIdx);
  sqlite3_stmt *pStmt;
  sqlite3_uint64 *aCnt;
  sqlite3_value **apValue;
  int nCol = 0;
  int nByte;
  int i, j, k;
  int iLimit;
  int nRow = 0;
  char *zRes;
  int szRes;
  int rc;

# define N_SPAN  5
  if( n==0 ) return;
  pStmt = db_prepare("PRAGMA index_xinfo=\"%w\"", zIdx);
  while( sqlite3_step(pStmt)==SQLITE_ROW ){
    const char *zColl = (const char*)sqlite3_column_text(pStmt,4);
    if( sqlite3_stricmp(zColl,"binary")!=0 ){
      printf("-- cannot analyze index \"%s\" because column \"%s\" uses"
             " collating sequence \"%s\".\n",
             zIdx, sqlite3_column_text(pStmt, 2), zColl);
      sqlite3_finalize(pStmt);
      return;
    }
    if( sqlite3_column_int(pStmt, 5)==0 ) break;
    nCol++;
  }
  sqlite3_finalize(pStmt);

  nByte = (sizeof(aCnt[0]) + sizeof(apValue[0]))*nCol + 30*(nCol+1);
  aCnt = sqlite3_malloc( nByte );
  if( aCnt==0 ){
    runtimeError("out of memory");
  }
  memset(aCnt, 0, nByte);
  apValue = (sqlite3_value**)&aCnt[nCol];







|

|














|








|





>







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
      return n1!=n2;
 
  }
  return 1;
}

/*
** Stat1 for an index.  Return non-zero if an entry was created.
*/
static int analyzeIndex(const char *zTab, const char *zIdx){
  sqlite3_int64 n = estEntryCount(zIdx);
  sqlite3_stmt *pStmt;
  sqlite3_uint64 *aCnt;
  sqlite3_value **apValue;
  int nCol = 0;
  int nByte;
  int i, j, k;
  int iLimit;
  int nRow = 0;
  char *zRes;
  int szRes;
  int rc;

# define N_SPAN  5
  if( n==0 ) return 0;
  pStmt = db_prepare("PRAGMA index_xinfo=\"%w\"", zIdx);
  while( sqlite3_step(pStmt)==SQLITE_ROW ){
    const char *zColl = (const char*)sqlite3_column_text(pStmt,4);
    if( sqlite3_stricmp(zColl,"binary")!=0 ){
      printf("-- cannot analyze index \"%s\" because column \"%s\" uses"
             " collating sequence \"%s\".\n",
             zIdx, sqlite3_column_text(pStmt, 2), zColl);
      sqlite3_finalize(pStmt);
      return 0;
    }
    if( sqlite3_column_int(pStmt, 5)==0 ) break;
    nCol++;
  }
  sqlite3_finalize(pStmt);
  if( nCol==0 ) return 0;
  nByte = (sizeof(aCnt[0]) + sizeof(apValue[0]))*nCol + 30*(nCol+1);
  aCnt = sqlite3_malloc( nByte );
  if( aCnt==0 ){
    runtimeError("out of memory");
  }
  memset(aCnt, 0, nByte);
  apValue = (sqlite3_value**)&aCnt[nCol];
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258

259


































260
261
262
263
264
265
266
    pStmt = db_prepare("PRAGMA btree_sample(\"%w\",%g,%lld)",
                       zIdx, ((double)i)/(double)N_SPAN, n*2);
  }  
  for(j=0; j<nCol; j++) sqlite3_value_free(apValue[j]);
  sqlite3_snprintf(szRes, zRes, "%lld", n);
  k = (int)strlen(zRes);
  for(j=0; j<nCol; j++){
    sqlite3_snprintf(szRes-k, zRes+k, " %d", nRow/aCnt[j]);
    k += (int)strlen(zRes+k);
  }
  pStmt = db_prepare(
     "INSERT INTO temp.est_stat1(tbl,idx,stat)"
     "VALUES(\"%w\",\"%w\",'%s')", zTab, zIdx, zRes
  );
  sqlite3_step(pStmt);
  sqlite3_finalize(pStmt);

}



































/*
** Print the sqlite3_value X as an SQL literal.
*/
static void printQuoted(FILE *out, sqlite3_value *X){
  switch( sqlite3_value_type(X) ){
    case SQLITE_FLOAT: {







|








>

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







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
275
276
277
278
279
280
281
282
283
284
    pStmt = db_prepare("PRAGMA btree_sample(\"%w\",%g,%lld)",
                       zIdx, ((double)i)/(double)N_SPAN, n*2);
  }  
  for(j=0; j<nCol; j++) sqlite3_value_free(apValue[j]);
  sqlite3_snprintf(szRes, zRes, "%lld", n);
  k = (int)strlen(zRes);
  for(j=0; j<nCol; j++){
    sqlite3_snprintf(szRes-k, zRes+k, " %d", (nRow+aCnt[j]-1)/aCnt[j]);
    k += (int)strlen(zRes+k);
  }
  pStmt = db_prepare(
     "INSERT INTO temp.est_stat1(tbl,idx,stat)"
     "VALUES(\"%w\",\"%w\",'%s')", zTab, zIdx, zRes
  );
  sqlite3_step(pStmt);
  sqlite3_finalize(pStmt);
  return 1;
}

/*
** Stat1 for a table.
*/
static void analyzeTable(const char *zTab){
  sqlite3_int64 n = estEntryCount(zTab);
  sqlite3_stmt *pStmt;
  int nIndex = 0;
  int isWithoutRowid = 0;
  if( n==0 ){
    printf("-- empty table: %s\n", zTab);
    return;
  }
  if( analyzeIndex(zTab,zTab) ){
    isWithoutRowid = 1;
    nIndex++;
  }
  pStmt = db_prepare("PRAGMA index_list(\"%w\")", zTab);
  while( sqlite3_step(pStmt)==SQLITE_ROW ){
    if( sqlite3_column_text(pStmt,3)[0]=='p' && isWithoutRowid ) continue;
    if( sqlite3_column_int(pStmt,4)==0 ) nIndex++;
    analyzeIndex(zTab, (const char*)sqlite3_column_text(pStmt,1));
  }
  sqlite3_finalize(pStmt);
  if( nIndex==0 ){
    pStmt = db_prepare(
       "INSERT INTO temp.est_stat1(tbl,idx,stat)"
       "VALUES(\"%w\",NULL,'%lld')", zTab, n
    );
    sqlite3_step(pStmt);
    sqlite3_finalize(pStmt);
  }
}


/*
** Print the sqlite3_value X as an SQL literal.
*/
static void printQuoted(FILE *out, sqlite3_value *X){
  switch( sqlite3_value_type(X) ){
    case SQLITE_FLOAT: {
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
    cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb);
  }
  rc = sqlite3_exec(g.db, "CREATE TEMP TABLE est_stat1(tbl,idx,stat);",
                    0, 0, &zErrMsg);
  if( rc || zErrMsg ){
    cmdlineError("Cannot CREATE TEMP TABLE");
  }
  pStmt = db_prepare("SELECT type, name, tbl_name FROM sqlite_master"
                     " WHERE type IN ('table','index')"
                     "   AND rootpage>0"
                     "   AND (type='index' OR name NOT LIKE 'sqlite_%%')"
                     " ORDER BY tbl_name, type DESC, name");
  while( sqlite3_step(pStmt)==SQLITE_ROW ){
    const char *zType = (const char*)sqlite3_column_text(pStmt, 0);
    const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
    const char *zTblName = (const char*)sqlite3_column_text(pStmt, 2);
    if( zType[0]=='t' ){
      analyzeTable(zName);
    }else{
      analyzeIndex(zTblName, zName);
    }
  }
  sqlite3_finalize(pStmt);
  dump_table("temp.est_stat1","sqlite_stat1");
  sqlite3_close(g.db);
  return 0;
}







|
<
|
|
|

<
|
<
<
|
<
<
<






415
416
417
418
419
420
421
422

423
424
425
426

427


428



429
430
431
432
433
434
    cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb);
  }
  rc = sqlite3_exec(g.db, "CREATE TEMP TABLE est_stat1(tbl,idx,stat);",
                    0, 0, &zErrMsg);
  if( rc || zErrMsg ){
    cmdlineError("Cannot CREATE TEMP TABLE");
  }
  pStmt = db_prepare("SELECT name FROM sqlite_master"

                     " WHERE type='table' AND rootpage>0"
                     "   AND name NOT LIKE 'sqlite_%%'"
                     " ORDER BY name");
  while( sqlite3_step(pStmt)==SQLITE_ROW ){

    const char *zName = (const char*)sqlite3_column_text(pStmt, 0);


    analyzeTable(zName);



  }
  sqlite3_finalize(pStmt);
  dump_table("temp.est_stat1","sqlite_stat1");
  sqlite3_close(g.db);
  return 0;
}