Fossil

Check-in [22645e1f]
Login

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

Overview
Comment:Use the schema-table naming convention appropriate for SQLite 3.33.0.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 22645e1f0606b5ef4bd6351a97d5a88b78d01484d50fd61cb46d8d8829f01266
User & Date: drh 2020-06-19 16:44:56
Context
2020-06-19
17:54
Add the test-command-stats command. check-in: c7bb647f user: drh tags: trunk
16:44
Use the schema-table naming convention appropriate for SQLite 3.33.0. check-in: 22645e1f user: drh tags: trunk
15:33
Update the built-in SQLite to the latest 3.33.0 prerelease. check-in: 5cf17694 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/bundle.c.

23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/*
** SQL code used to initialize the schema of a bundle.
**
** The bblob.delta field can be an integer, a text string, or NULL.
** If an integer, then the corresponding blobid is the delta basis.
** If a text string, then that string is a SHA1 hash for the delta
** basis, which is presumably in the master repository.  If NULL, then
** data contains content without delta compression.
*/
static const char zBundleInit[] =
@ CREATE TABLE IF NOT EXISTS "%w".bconfig(
@   bcname TEXT,
@   bcvalue ANY
@ );







|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/*
** SQL code used to initialize the schema of a bundle.
**
** The bblob.delta field can be an integer, a text string, or NULL.
** If an integer, then the corresponding blobid is the delta basis.
** If a text string, then that string is a SHA1 hash for the delta
** basis, which is presumably in the main repository.  If NULL, then
** data contains content without delta compression.
*/
static const char zBundleInit[] =
@ CREATE TABLE IF NOT EXISTS "%w".bconfig(
@   bcname TEXT,
@   bcvalue ANY
@ );

Changes to src/db.c.

1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
....
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004

/*
** zDbName is the name of a database file.  Attach zDbName using
** the name zLabel.
*/
void db_attach(const char *zDbName, const char *zLabel){
  Blob key;
  if( db_table_exists(zLabel,"sqlite_master") ) return;
  blob_init(&key, 0, 0);
  db_maybe_obtain_encryption_key(zDbName, &key);
  if( fossil_getenv("FOSSIL_USE_SEE_TEXTKEY")==0 ){
    char *zCmd = sqlite3_mprintf("ATTACH DATABASE %Q AS %Q KEY %Q",
                                 zDbName, zLabel, blob_str(&key));
    db_exec_sql(zCmd);
    fossil_secure_zero(zCmd, strlen(zCmd));
................................................................................
  Stmt q;
  Blob allSql;
  int dryRun = find_option("dry-run", "n", 0)!=0;
  for(i=2; i<g.argc; i++){
    db_open_or_attach(g.argv[i], "main");
    blob_init(&allSql, "BEGIN;\n", -1);
    db_prepare(&q,
      "SELECT name, sql FROM main.sqlite_master "
      " WHERE type='table' AND sql NOT LIKE '%%WITHOUT ROWID%%'"
      "   AND name IN ('global_config','shun','concealed','config',"
                    "  'plink','tagxref','backlink','vcache');"
    );
    while( db_step(&q)==SQLITE_ROW ){
      const char *zTName = db_column_text(&q, 0);
      const char *zOrigSql = db_column_text(&q, 1);







|







 







|







1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
....
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004

/*
** zDbName is the name of a database file.  Attach zDbName using
** the name zLabel.
*/
void db_attach(const char *zDbName, const char *zLabel){
  Blob key;
  if( db_table_exists(zLabel,"sqlite_schema") ) return;
  blob_init(&key, 0, 0);
  db_maybe_obtain_encryption_key(zDbName, &key);
  if( fossil_getenv("FOSSIL_USE_SEE_TEXTKEY")==0 ){
    char *zCmd = sqlite3_mprintf("ATTACH DATABASE %Q AS %Q KEY %Q",
                                 zDbName, zLabel, blob_str(&key));
    db_exec_sql(zCmd);
    fossil_secure_zero(zCmd, strlen(zCmd));
................................................................................
  Stmt q;
  Blob allSql;
  int dryRun = find_option("dry-run", "n", 0)!=0;
  for(i=2; i<g.argc; i++){
    db_open_or_attach(g.argv[i], "main");
    blob_init(&allSql, "BEGIN;\n", -1);
    db_prepare(&q,
      "SELECT name, sql FROM main.sqlite_schema "
      " WHERE type='table' AND sql NOT LIKE '%%WITHOUT ROWID%%'"
      "   AND name IN ('global_config','shun','concealed','config',"
                    "  'plink','tagxref','backlink','vcache');"
    );
    while( db_step(&q)==SQLITE_ROW ){
      const char *zTName = db_column_text(&q, 0);
      const char *zOrigSql = db_column_text(&q, 1);

Changes to src/main.c.

671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
      raise(SIGTRAP);
#endif
    }
  }
#endif

  fossil_limit_memory(1);
  if( sqlite3_libversion_number()<3014000 ){
    fossil_panic("Unsuitable SQLite version %s, must be at least 3.14.0",
                 sqlite3_libversion());
  }
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
  sqlite3_config(SQLITE_CONFIG_LOG, fossil_sqlite_log, 0);
  memset(&g, 0, sizeof(g));
  g.now = time(0);
  g.httpHeader = empty_blob;







|
|







671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
      raise(SIGTRAP);
#endif
    }
  }
#endif

  fossil_limit_memory(1);
  if( sqlite3_libversion_number()<3033000 ){
    fossil_panic("Unsuitable SQLite version %s, must be at least 3.33.0",
                 sqlite3_libversion());
  }
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
  sqlite3_config(SQLITE_CONFIG_LOG, fossil_sqlite_log, 0);
  memset(&g, 0, sizeof(g));
  g.now = time(0);
  g.httpHeader = empty_blob;

Changes to src/rebuild.c.

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
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395

/*
** Update the repository schema for Fossil version 2.0.  (2017-02-28)
**   (1) Change the CHECK constraint on BLOB.UUID so that the length
**       is greater than or equal to 40, not exactly equal to 40.
*/
void rebuild_schema_update_2_0(void){
  char *z = db_text(0, "SELECT sql FROM repository.sqlite_master"
                       " WHERE name='blob'");
  if( z ){
    /* Search for:  length(uuid)==40
    **              0123456789 12345   */
    int i;
    for(i=10; z[i]; i++){
      if( z[i]=='=' && strncmp(&z[i-6],"(uuid)==40",10)==0 ){
        z[i] = '>';
        db_multi_exec(
           "PRAGMA writable_schema=ON;"
           "UPDATE repository.sqlite_master SET sql=%Q WHERE name LIKE 'blob';"
           "PRAGMA writable_schema=OFF;",
           z
        );
        break;
      }
    }
    fossil_free(z);
................................................................................
  if (ttyOutput && !g.fQuiet) {
    percent_complete(0);
  }
  alert_triggers_disable();
  rebuild_update_schema();
  blob_init(&sql, 0, 0);
  db_prepare(&q,
     "SELECT name FROM sqlite_master /*scan*/"
     " WHERE type='table'"
     " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
                       "'config','shun','private','reportfmt',"
                       "'concealed','accesslog','modreq',"
                       "'purgeevent','purgeitem','unversioned',"
                       "'subscriber','pending_alert','alert_bounce')"
     " AND name NOT GLOB 'sqlite_*'"







|










|







 







|







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
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395

/*
** Update the repository schema for Fossil version 2.0.  (2017-02-28)
**   (1) Change the CHECK constraint on BLOB.UUID so that the length
**       is greater than or equal to 40, not exactly equal to 40.
*/
void rebuild_schema_update_2_0(void){
  char *z = db_text(0, "SELECT sql FROM repository.sqlite_schema"
                       " WHERE name='blob'");
  if( z ){
    /* Search for:  length(uuid)==40
    **              0123456789 12345   */
    int i;
    for(i=10; z[i]; i++){
      if( z[i]=='=' && strncmp(&z[i-6],"(uuid)==40",10)==0 ){
        z[i] = '>';
        db_multi_exec(
           "PRAGMA writable_schema=ON;"
           "UPDATE repository.sqlite_schema SET sql=%Q WHERE name LIKE 'blob';"
           "PRAGMA writable_schema=OFF;",
           z
        );
        break;
      }
    }
    fossil_free(z);
................................................................................
  if (ttyOutput && !g.fQuiet) {
    percent_complete(0);
  }
  alert_triggers_disable();
  rebuild_update_schema();
  blob_init(&sql, 0, 0);
  db_prepare(&q,
     "SELECT name FROM sqlite_schema /*scan*/"
     " WHERE type='table'"
     " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
                       "'config','shun','private','reportfmt',"
                       "'concealed','accesslog','modreq',"
                       "'purgeevent','purgeitem','unversioned',"
                       "'subscriber','pending_alert','alert_bounce')"
     " AND name NOT GLOB 'sqlite_*'"

Changes to src/report.c.

509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525

/*
** Output a bunch of text that provides information about report
** formats
*/
static void report_format_hints(void){
  char *zSchema;
  zSchema = db_text(0,"SELECT sql FROM sqlite_master WHERE name='ticket'");
  if( zSchema==0 ){
    zSchema = db_text(0,"SELECT sql FROM repository.sqlite_master"
                        " WHERE name='ticket'");
  }
  @ <hr /><h3>TICKET Schema</h3>
  @ <blockquote><pre>
  @ %h(zSchema)
  @ </pre></blockquote>
  @ <h3>Notes</h3>







|

|







509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525

/*
** Output a bunch of text that provides information about report
** formats
*/
static void report_format_hints(void){
  char *zSchema;
  zSchema = db_text(0,"SELECT sql FROM sqlite_schema WHERE name='ticket'");
  if( zSchema==0 ){
    zSchema = db_text(0,"SELECT sql FROM repository.sqlite_schema"
                        " WHERE name='ticket'");
  }
  @ <hr /><h3>TICKET Schema</h3>
  @ <blockquote><pre>
  @ %h(zSchema)
  @ </pre></blockquote>
  @ <h3>Notes</h3>

Changes to src/setup.c.

1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
  @ <input type="submit" name="go" value="Run SQL">
  @ <input type="submit" name="schema" value="Show Schema">
  @ <input type="submit" name="tablelist" value="List Tables">
  @ <input type="submit" name="configtab" value="CONFIG Table Query">
  @ </form>
  if( P("schema") ){
    zQ = sqlite3_mprintf(
            "SELECT sql FROM repository.sqlite_master"
            " WHERE sql IS NOT NULL ORDER BY name");
    go = 1;
  }else if( P("tablelist") ){
    zQ = sqlite3_mprintf(
            "SELECT name FROM repository.sqlite_master WHERE type='table'"
            " ORDER BY name");
    go = 1;
  }
  if( go ){
    sqlite3_stmt *pStmt;
    int rc;
    const char *zTail;







|




|







1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
  @ <input type="submit" name="go" value="Run SQL">
  @ <input type="submit" name="schema" value="Show Schema">
  @ <input type="submit" name="tablelist" value="List Tables">
  @ <input type="submit" name="configtab" value="CONFIG Table Query">
  @ </form>
  if( P("schema") ){
    zQ = sqlite3_mprintf(
            "SELECT sql FROM repository.sqlite_sqlite"
            " WHERE sql IS NOT NULL ORDER BY name");
    go = 1;
  }else if( P("tablelist") ){
    zQ = sqlite3_mprintf(
            "SELECT name FROM repository.sqlite_schema WHERE type='table'"
            " ORDER BY name");
    go = 1;
  }
  if( go ){
    sqlite3_stmt *pStmt;
    int rc;
    const char *zTail;

Changes to src/stat.c.

510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
...
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
...
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
  style_adunit_config(ADUNIT_RIGHT_OK);
  style_submenu_element("Stat", "stat");
  style_submenu_element("URLs", "urllist");
  if( sqlite3_compileoption_used("ENABLE_DBSTAT_VTAB") ){
    style_submenu_element("Table Sizes", "repo-tabsize");
  }
  blob_init(&sql,
    "SELECT sql FROM repository.sqlite_master WHERE sql IS NOT NULL", -1);
  if( zArg ){
    style_submenu_element("All", "repo_schema");
    blob_appendf(&sql, " AND (tbl_name=%Q OR name=%Q)", zArg, zArg);
  }
  blob_appendf(&sql, " ORDER BY tbl_name, type<>'table', name");
  db_prepare(&q, "%s", blob_str(&sql)/*safe-for-%s*/);
  blob_reset(&sql);
................................................................................
  style_submenu_element("Stat", "stat");
  if( g.perm.Admin ){
    style_submenu_element("Schema", "repo_schema");
  }
  db_multi_exec(
    "CREATE TEMP TABLE trans(name TEXT PRIMARY KEY,tabname TEXT)WITHOUT ROWID;"
    "INSERT INTO trans(name,tabname)"
    "   SELECT name, tbl_name FROM repository.sqlite_master;"
    "CREATE TEMP TABLE piechart(amt REAL, label TEXT);"
    "INSERT INTO piechart(amt,label)"
    "  SELECT sum(pageno),"
    "  coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
    "    FROM dbstat('repository',TRUE)"
    "   GROUP BY 2 ORDER BY 2;"
  );
................................................................................
  piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
  @ </svg></center>

  if( g.localOpen ){
    db_multi_exec(
      "DELETE FROM trans;"
      "INSERT INTO trans(name,tabname)"
      "   SELECT name, tbl_name FROM localdb.sqlite_master;"
      "DELETE FROM piechart;"
      "INSERT INTO piechart(amt,label)"
      "  SELECT sum(pageno), "
      " coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
      "    FROM dbstat('localdb',TRUE)"
      "   GROUP BY 2 ORDER BY 2;"
    );







|







 







|







 







|







510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
...
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
...
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
  style_adunit_config(ADUNIT_RIGHT_OK);
  style_submenu_element("Stat", "stat");
  style_submenu_element("URLs", "urllist");
  if( sqlite3_compileoption_used("ENABLE_DBSTAT_VTAB") ){
    style_submenu_element("Table Sizes", "repo-tabsize");
  }
  blob_init(&sql,
    "SELECT sql FROM repository.sqlite_schema WHERE sql IS NOT NULL", -1);
  if( zArg ){
    style_submenu_element("All", "repo_schema");
    blob_appendf(&sql, " AND (tbl_name=%Q OR name=%Q)", zArg, zArg);
  }
  blob_appendf(&sql, " ORDER BY tbl_name, type<>'table', name");
  db_prepare(&q, "%s", blob_str(&sql)/*safe-for-%s*/);
  blob_reset(&sql);
................................................................................
  style_submenu_element("Stat", "stat");
  if( g.perm.Admin ){
    style_submenu_element("Schema", "repo_schema");
  }
  db_multi_exec(
    "CREATE TEMP TABLE trans(name TEXT PRIMARY KEY,tabname TEXT)WITHOUT ROWID;"
    "INSERT INTO trans(name,tabname)"
    "   SELECT name, tbl_name FROM repository.sqlite_schema;"
    "CREATE TEMP TABLE piechart(amt REAL, label TEXT);"
    "INSERT INTO piechart(amt,label)"
    "  SELECT sum(pageno),"
    "  coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
    "    FROM dbstat('repository',TRUE)"
    "   GROUP BY 2 ORDER BY 2;"
  );
................................................................................
  piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
  @ </svg></center>

  if( g.localOpen ){
    db_multi_exec(
      "DELETE FROM trans;"
      "INSERT INTO trans(name,tabname)"
      "   SELECT name, tbl_name FROM localdb.sqlite_schema;"
      "DELETE FROM piechart;"
      "INSERT INTO piechart(amt,label)"
      "  SELECT sum(pageno), "
      " coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
      "    FROM dbstat('localdb',TRUE)"
      "   GROUP BY 2 ORDER BY 2;"
    );