/ Check-in [50ecdfc4]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Figure out the primary-key type of a table using queries of sqlite_master and the table_info and index_list pragmas, obviating the need for SQLITE_TESTCTRL_TBLTYPE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 50ecdfc443b51e3569c6add2fba5132f959c61cb
User & Date: drh 2015-02-05 01:49:31
Context
2015-02-05
17:36
Prevent ota updates from violating NOT NULL constraints. Add a comment to the "limitations" section of sqlite3ota.h saying that CHECK constraints are not enforced. check-in: 74e073dd user: dan tags: ota-update
01:49
Figure out the primary-key type of a table using queries of sqlite_master and the table_info and index_list pragmas, obviating the need for SQLITE_TESTCTRL_TBLTYPE. check-in: 50ecdfc4 user: drh tags: ota-update
2015-02-04
23:51
Merge all changes from index_xinfo, including the move of the pragma table into the separate pragma.h file. check-in: 21e95d28 user: drh tags: ota-update
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/ota/sqlite3ota.c.

133
134
135
136
137
138
139

140
141
142
143
144
145

146
147
148
149
150
151
152
...
445
446
447
448
449
450
451

























































































































452
453
454
455
456
457
458
...
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
....
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
  char *zMask;                    /* Copy of update mask used with pUpdate */
  sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
};

/*
** Values for OtaObjIter.eType
**

**     1: Table has an implicit rowid.
**     2: Table has an explicit IPK column.
**     3: Table has an external PK index.
**     4: Table is WITHOUT ROWID.
**     5: Table is a virtual table.
*/

#define OTA_PK_NONE           1
#define OTA_PK_IPK            2
#define OTA_PK_EXTERNAL       3
#define OTA_PK_WITHOUT_ROWID  4
#define OTA_PK_VTAB           5

/*
................................................................................
      *pRc = SQLITE_NOMEM;
    }
  }

  return zRet;
}



























































































































/*
** If they are not already populated, populate the pIter->azTblCol[],
** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to
** the table (not index) that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
................................................................................
    int i;                        /* for() loop iterator variable */
    int rc2;                      /* sqlite3_finalize() return value */
    int bOtaRowid = 0;            /* If input table has column "ota_rowid" */
    int iOrder = 0;

    /* Figure out the type of table this step will deal with. */
    assert( pIter->eType==0 );
    sqlite3_test_control(
        SQLITE_TESTCTRL_TBLTYPE, p->db, "main", pIter->zTbl, &pIter->eType,
        &pIter->iPkTnum
    );
    assert( pIter->eType==OTA_PK_NONE || pIter->eType==OTA_PK_IPK 
         || pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_WITHOUT_ROWID
         || pIter->eType==OTA_PK_VTAB
    );

    /* Populate the azTblCol[] and nTblCol variables based on the columns
    ** of the input table. Ignore any input table columns that begin with
................................................................................
}
#endif                  /* ifdef SQLITE_TEST */
#else   /* !SQLITE_CORE || SQLITE_ENABLE_OTA */
# ifdef SQLITE_TEST
#include <tcl.h>
int SqliteOta_Init(Tcl_Interp *interp){ return TCL_OK; }
# endif
#endif 










>






>







 







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







 







|
|
|
<







 







|
<
<
<
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
...
589
590
591
592
593
594
595
596
597
598

599
600
601
602
603
604
605
....
2223
2224
2225
2226
2227
2228
2229
2230



  char *zMask;                    /* Copy of update mask used with pUpdate */
  sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
};

/*
** Values for OtaObjIter.eType
**
**     0: Table does not exist (error)
**     1: Table has an implicit rowid.
**     2: Table has an explicit IPK column.
**     3: Table has an external PK index.
**     4: Table is WITHOUT ROWID.
**     5: Table is a virtual table.
*/
#define OTA_PK_NOTABLE        0
#define OTA_PK_NONE           1
#define OTA_PK_IPK            2
#define OTA_PK_EXTERNAL       3
#define OTA_PK_WITHOUT_ROWID  4
#define OTA_PK_VTAB           5

/*
................................................................................
      *pRc = SQLITE_NOMEM;
    }
  }

  return zRet;
}

/* Determine the type of a table.
**
**   peType is of type (int*), a pointer to an output parameter of type
**   (int). This call sets the output parameter as follows, depending
**   on the type of the table specified by parameters dbName and zTbl.
**
**     OTA_PK_NOTABLE:       No such table.
**     OTA_PK_NONE:          Table has an implicit rowid.
**     OTA_PK_IPK:           Table has an explicit IPK column.
**     OTA_PK_EXTERNAL:      Table has an external PK index.
**     OTA_PK_WITHOUT_ROWID: Table is WITHOUT ROWID.
**     OTA_PK_VTAB:          Table is a virtual table.
**
**   Argument *piPk is also of type (int*), and also points to an output
**   parameter. Unless the table has an external primary key index 
**   (i.e. unless *peType is set to 3), then *piPk is set to zero. Or,
**   if the table does have an external primary key index, then *piPk
**   is set to the root page number of the primary key index before
**   returning.
**
** ALGORITHM:
**
**   if( no entry exists in sqlite_master ){
**     return OTA_PK_NOTABLE
**   }else if( sql for the entry starts with "CREATE VIRTUAL" ){
**     return OTA_PK_VTAB
**   }else if( "PRAGMA index_list()" for the table contains a "pk" index ){
**     if( the index that is the pk exists in sqlite_master ){
**       *piPK = rootpage of that index.
**       return OTA_PK_EXTERNAL
**     }else{
**       return OTA_PK_WITHOUT_ROWID
**     }
**   }else if( "PRAGMA table_info()" lists one or more "pk" columns ){
**     return OTA_PK_IPK
**   }else{
**     return OTA_PK_NONE
**   }
*/
static int otaTableType(
  sqlite3 *db,
  const char *zTab,
  int *peType,
  int *piPk
){
  sqlite3_stmt *pStmt = 0;
  int rc = SQLITE_OK;
  int rc2;
  char *zSql = 0;

  *peType = OTA_PK_NOTABLE;
  *piPk = 0;
  zSql = sqlite3_mprintf(
          "SELECT (sql LIKE 'create virtual%%')"
          "  FROM main.sqlite_master"
          " WHERE name=%Q", zTab);
  if( zSql==0 ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  sqlite3_free(zSql);
  zSql = 0;
  if( pStmt==0 ) goto otaTableType_end;
  if( sqlite3_step(pStmt)!=SQLITE_ROW ){
     goto otaTableType_end;                    /* no such table */
  }
  if( sqlite3_column_int(pStmt,0) ){
    *peType = OTA_PK_VTAB;                     /* virtual table */
    goto otaTableType_end;
  }
  rc = sqlite3_finalize(pStmt);
  if( rc ) return rc;
  zSql = sqlite3_mprintf("PRAGMA index_list=%Q",zTab);
  if( zSql==0 ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  sqlite3_free(zSql);
  zSql = 0;
  if( pStmt==0 ) goto otaTableType_end;
  while( sqlite3_step(pStmt)==SQLITE_ROW ){
    const unsigned char *zOrig = sqlite3_column_text(pStmt,3);
    if( zOrig && zOrig[0]=='p' ){
      zSql = sqlite3_mprintf("SELECT rootpage FROM main.sqlite_master"
                             " WHERE name=%Q", sqlite3_column_text(pStmt,1));
      if( zSql==0 ){ rc = SQLITE_NOMEM; goto otaTableType_end; }
      break;
    }
  }
  rc = sqlite3_finalize(pStmt);
  pStmt = 0;
  if( rc ) return rc;
  if( zSql ){
    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    zSql = 0;
    if( pStmt==0 ) goto otaTableType_end;
    if( sqlite3_step(pStmt)==SQLITE_ROW ){
      *piPk = sqlite3_column_int(pStmt, 0);
      *peType = OTA_PK_EXTERNAL;             /* external PK index */
    }else{
      *peType = OTA_PK_WITHOUT_ROWID;        /* WITHOUT ROWID table */
    }
  }else{
    zSql = sqlite3_mprintf("PRAGMA table_info=%Q", zTab);
    if( zSql==0 ) return SQLITE_NOMEM;
    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    zSql = 0;
    if( pStmt==0 ) goto otaTableType_end;
    *peType = OTA_PK_NONE;                   /* (default) implicit ROWID */
    while( sqlite3_step(pStmt)==SQLITE_ROW ){
      if( sqlite3_column_int(pStmt,5)>0 ){
        *peType = OTA_PK_IPK;                /* explicit IPK column */
        break;
      }
    }
  }

otaTableType_end:
  sqlite3_free(zSql);
  rc2 = sqlite3_finalize(pStmt);
  return rc ? rc : rc2;
}


/*
** If they are not already populated, populate the pIter->azTblCol[],
** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to
** the table (not index) that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
................................................................................
    int i;                        /* for() loop iterator variable */
    int rc2;                      /* sqlite3_finalize() return value */
    int bOtaRowid = 0;            /* If input table has column "ota_rowid" */
    int iOrder = 0;

    /* Figure out the type of table this step will deal with. */
    assert( pIter->eType==0 );
    p->rc = otaTableType(p->db, pIter->zTbl, &pIter->eType, &pIter->iPkTnum);
    if( p->rc ) return p->rc;


    assert( pIter->eType==OTA_PK_NONE || pIter->eType==OTA_PK_IPK 
         || pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_WITHOUT_ROWID
         || pIter->eType==OTA_PK_VTAB
    );

    /* Populate the azTblCol[] and nTblCol variables based on the columns
    ** of the input table. Ignore any input table columns that begin with
................................................................................
}
#endif                  /* ifdef SQLITE_TEST */
#else   /* !SQLITE_CORE || SQLITE_ENABLE_OTA */
# ifdef SQLITE_TEST
#include <tcl.h>
int SqliteOta_Init(Tcl_Interp *interp){ return TCL_OK; }
# endif
#endif



Changes to src/main.c.

3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
      sqlite3_mutex_enter(db->mutex);
      db->init.iDb = sqlite3FindDbName(db, va_arg(ap,const char*));
      db->init.busy = db->init.imposterTable = va_arg(ap,int);
      db->init.newTnum = va_arg(ap,int);
      if( db->init.busy==0 && db->init.newTnum>0 ){
        sqlite3ResetAllSchemasOfConnection(db);
      }
      sqlite3_mutex_leave(db->mutex);
      break;
    }

    /* sqlite3_test_control(TESTCTRL_TBLTYPE, db, dbName, zTbl, peType, piPk)
    **
    **   peType is of type (int*), a pointer to an output parameter of type
    **   (int). This call sets the output parameter as follows, depending
    **   on the type of the table specified by parameters dbName and zTbl.
    **
    **     0: No such table.
    **     1: Table has an implicit rowid.
    **     2: Table has an explicit IPK column.
    **     3: Table has an external PK index.
    **     4: Table is WITHOUT ROWID.
    **     5: Table is a virtual table.
    **
    **   Argument *piPk is also of type (int*), and also points to an output
    **   parameter. Unless the table has an external primary key index 
    **   (i.e. unless *peType is set to 3), then *piPk is set to zero. Or,
    **   if the table does have an external primary key index, then *piPk
    **   is set to the root page number of the primary key index before
    **   returning.
    */
    case SQLITE_TESTCTRL_TBLTYPE: {
      sqlite3 *db = va_arg(ap, sqlite3*);
      const char *zDb = va_arg(ap, const char*);
      const char *zTab = va_arg(ap, const char*);
      int *peType = va_arg(ap, int*);
      int *piPk = va_arg(ap, int*);
      Table *pTab;
      *piPk = 0;
      sqlite3_mutex_enter(db->mutex);
      sqlite3BtreeEnterAll(db);
      pTab = sqlite3FindTable(db, zTab, zDb);
      if( pTab==0 ){
        *peType = 0;
      }else if( IsVirtual(pTab) ){
        *peType = 5;
      }else if( HasRowid(pTab)==0 ){
        *peType = 4;
      }else if( pTab->iPKey>=0 ){
        *peType = 2;
      }else{
        Index *pPk = sqlite3PrimaryKeyIndex(pTab);
        if( pPk ){
          *peType = 3;
          *piPk = pPk->tnum;
        }else{
          *peType = 1;
        }
      }
      sqlite3BtreeLeaveAll(db);
      sqlite3_mutex_leave(db->mutex);
      break;
    }
  }
  va_end(ap);
#endif /* SQLITE_OMIT_BUILTIN_TEST */
  return rc;







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







3647
3648
3649
3650
3651
3652
3653





















































3654
3655
3656
3657
3658
3659
3660
      sqlite3_mutex_enter(db->mutex);
      db->init.iDb = sqlite3FindDbName(db, va_arg(ap,const char*));
      db->init.busy = db->init.imposterTable = va_arg(ap,int);
      db->init.newTnum = va_arg(ap,int);
      if( db->init.busy==0 && db->init.newTnum>0 ){
        sqlite3ResetAllSchemasOfConnection(db);
      }





















































      sqlite3_mutex_leave(db->mutex);
      break;
    }
  }
  va_end(ap);
#endif /* SQLITE_OMIT_BUILTIN_TEST */
  return rc;

Changes to src/sqlite.h.in.

6262
6263
6264
6265
6266
6267
6268
6269
6270
6271
6272
6273
6274
6275
6276
6277
#define SQLITE_TESTCTRL_EXPLAIN_STMT            19  /* NOT USED */
#define SQLITE_TESTCTRL_NEVER_CORRUPT           20
#define SQLITE_TESTCTRL_VDBE_COVERAGE           21
#define SQLITE_TESTCTRL_BYTEORDER               22
#define SQLITE_TESTCTRL_ISINIT                  23
#define SQLITE_TESTCTRL_SORTER_MMAP             24
#define SQLITE_TESTCTRL_IMPOSTER                25
#define SQLITE_TESTCTRL_TBLTYPE                 26
#define SQLITE_TESTCTRL_LAST                    26

/*
** CAPI3REF: SQLite Runtime Status
**
** ^This interface is used to retrieve runtime status information
** about the performance of SQLite, and optionally to reset various
** highwater marks.  ^The first argument is an integer code for







<
|







6262
6263
6264
6265
6266
6267
6268

6269
6270
6271
6272
6273
6274
6275
6276
#define SQLITE_TESTCTRL_EXPLAIN_STMT            19  /* NOT USED */
#define SQLITE_TESTCTRL_NEVER_CORRUPT           20
#define SQLITE_TESTCTRL_VDBE_COVERAGE           21
#define SQLITE_TESTCTRL_BYTEORDER               22
#define SQLITE_TESTCTRL_ISINIT                  23
#define SQLITE_TESTCTRL_SORTER_MMAP             24
#define SQLITE_TESTCTRL_IMPOSTER                25

#define SQLITE_TESTCTRL_LAST                    25

/*
** CAPI3REF: SQLite Runtime Status
**
** ^This interface is used to retrieve runtime status information
** about the performance of SQLite, and optionally to reset various
** highwater marks.  ^The first argument is an integer code for