/ Check-in [5232b066]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Add a "pk" column to the table_info pragma which is true for each column that is part of the primary key. Add a "foreign_key_list" pragma that gives information about all foreign keys for a table. Both changes are experimental. (CVS 1081)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5232b0665b84a9339a85918570093a376a0ea91f
User & Date: drh 2003-08-23 22:40:54
Context
2003-08-24
16:38
Fix a bug in the new "pk" column of the table_info pragma that was added by check-in (1081). (CVS 1082) check-in: ad7b017a user: drh tags: trunk
2003-08-23
22:40
Add a "pk" column to the table_info pragma which is true for each column that is part of the primary key. Add a "foreign_key_list" pragma that gives information about all foreign keys for a table. Both changes are experimental. (CVS 1081) check-in: 5232b066 user: drh tags: trunk
2003-08-22
03:00
Version 2.8.6 (CVS 1080) check-in: 0bde7ae2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c. 

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715


716
717
718
719



720
721
722
723
724
725
726

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.157 2003/07/30 12:34:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
**
** If the key is not an INTEGER PRIMARY KEY, then create a unique
** index for the key.  No index is created for INTEGER PRIMARY KEYs.
*/
void sqliteAddPrimaryKey(Parse *pParse, IdList *pList, int onError){
  Table *pTab = pParse->pNewTable;
  char *zType = 0;
  int iCol = -1;
  if( pTab==0 ) goto primary_key_exit;
  if( pTab->hasPrimKey ){
    sqliteSetString(&pParse->zErrMsg, "table \"", pTab->zName, 
        "\" has more than one primary key", 0);
    pParse->nErr++;
    goto primary_key_exit;
  }
  pTab->hasPrimKey = 1;
  if( pList==0 ){
    iCol = pTab->nCol - 1;


  }else if( pList->nId==1 ){
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( sqliteStrICmp(pList->a[0].zName, pTab->aCol[iCol].zName)==0 ) break;
    }



  }
  if( iCol>=0 && iCol<pTab->nCol ){
    zType = pTab->aCol[iCol].zType;
  }
  if( pParse->db->file_format>=1 && 
           zType && sqliteStrICmp(zType, "INTEGER")==0 ){
    pTab->iPKey = iCol;








|







 







|










>
>
|
|
|
|
>
>
>








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.158 2003/08/23 22:40:54 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
**
** If the key is not an INTEGER PRIMARY KEY, then create a unique
** index for the key.  No index is created for INTEGER PRIMARY KEYs.
*/
void sqliteAddPrimaryKey(Parse *pParse, IdList *pList, int onError){
  Table *pTab = pParse->pNewTable;
  char *zType = 0;
  int iCol = -1, i;
  if( pTab==0 ) goto primary_key_exit;
  if( pTab->hasPrimKey ){
    sqliteSetString(&pParse->zErrMsg, "table \"", pTab->zName, 
        "\" has more than one primary key", 0);
    pParse->nErr++;
    goto primary_key_exit;
  }
  pTab->hasPrimKey = 1;
  if( pList==0 ){
    iCol = pTab->nCol - 1;
    pTab->aCol[iCol].isPrimKey = 1;
  }else{
    for(i=0; i<pList->nId; i++){
      for(iCol=0; iCol<pTab->nCol; iCol++){
        if( sqliteStrICmp(pList->a[0].zName, pTab->aCol[iCol].zName)==0 ) break;
      }
      if( iCol<pTab->nCol ) pTab->aCol[iCol].isPrimKey = 1;
    }
    if( pList->nId>1 ) iCol = -1;
  }
  if( iCol>=0 && iCol<pTab->nCol ){
    zType = pTab->aCol[iCol].zType;
  }
  if( pParse->db->file_format>=1 && 
           zType && sqliteStrICmp(zType, "INTEGER")==0 ){
    pTab->iPKey = iCol;

Changes to src/pragma.c. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
339
340
341
342
343
344
345

346
347
348
349
350
351
352
...
353
354
355
356
357
358
359

360
361
362
363
364
365
366
367
...
411
412
413
414
415
416
417







































418
419
420
421
422
423
424
...
433
434
435
436
437
438
439


440
441
442
443
444
445
446

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.10 2003/06/15 23:42:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Interpret the given string as a boolean value.
*/
................................................................................
    if( pTab ){
      static VdbeOp tableInfoPreface[] = {
        { OP_ColumnName,  0, 0,       "cid"},
        { OP_ColumnName,  1, 0,       "name"},
        { OP_ColumnName,  2, 0,       "type"},
        { OP_ColumnName,  3, 0,       "notnull"},
        { OP_ColumnName,  4, 0,       "dflt_value"},

      };
      int i;
      sqliteVdbeAddOpList(v, ArraySize(tableInfoPreface), tableInfoPreface);
      sqliteViewGetColumnNames(pParse, pTab);
      for(i=0; i<pTab->nCol; i++){
        sqliteVdbeAddOp(v, OP_Integer, i, 0);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
................................................................................
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zName, P3_STATIC);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, 
           pTab->aCol[i].zType ? pTab->aCol[i].zType : "numeric", P3_STATIC);
        sqliteVdbeAddOp(v, OP_Integer, pTab->aCol[i].notNull, 0);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);

        sqliteVdbeAddOp(v, OP_Callback, 5, 0);
      }
    }
  }else

  if( sqliteStrICmp(zLeft, "index_info")==0 ){
    Index *pIdx;
    Table *pTab;
................................................................................
        sqliteVdbeAddOp(v, OP_Integer, pIdx->onError!=OE_None, 0);
        sqliteVdbeAddOp(v, OP_Callback, 3, 0);
        ++i;
        pIdx = pIdx->pNext;
      }
    }
  }else








































  if( sqliteStrICmp(zLeft, "database_list")==0 ){
    int i;
    static VdbeOp indexListPreface[] = {
      { OP_ColumnName,  0, 0,       "seq"},
      { OP_ColumnName,  1, 0,       "name"},
      { OP_ColumnName,  2, 0,       "file"},
................................................................................
      sqliteVdbeChangeP3(v, -1, db->aDb[i].zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, sqliteBtreeGetFilename(db->aDb[i].pBt),
           P3_STATIC);
      sqliteVdbeAddOp(v, OP_Callback, 3, 0);
    }
  }else


  /*
  **   PRAGMA temp_store
  **   PRAGMA temp_store = "default"|"memory"|"file"
  **
  ** Return or set the local value of the temp_store flag.  Changing
  ** the local value does not make changes to the disk file and the default
  ** value will be restored the next time the database is opened.








|







 







>







 







>
|







 







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







 







>
>








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
...
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
...
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.11 2003/08/23 22:40:54 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Interpret the given string as a boolean value.
*/
................................................................................
    if( pTab ){
      static VdbeOp tableInfoPreface[] = {
        { OP_ColumnName,  0, 0,       "cid"},
        { OP_ColumnName,  1, 0,       "name"},
        { OP_ColumnName,  2, 0,       "type"},
        { OP_ColumnName,  3, 0,       "notnull"},
        { OP_ColumnName,  4, 0,       "dflt_value"},
        { OP_ColumnName,  5, 0,       "pk"},
      };
      int i;
      sqliteVdbeAddOpList(v, ArraySize(tableInfoPreface), tableInfoPreface);
      sqliteViewGetColumnNames(pParse, pTab);
      for(i=0; i<pTab->nCol; i++){
        sqliteVdbeAddOp(v, OP_Integer, i, 0);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
................................................................................
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zName, P3_STATIC);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, 
           pTab->aCol[i].zType ? pTab->aCol[i].zType : "numeric", P3_STATIC);
        sqliteVdbeAddOp(v, OP_Integer, pTab->aCol[i].notNull, 0);
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
        sqliteVdbeAddOp(v, OP_Integer, pTab->aCol[i].isPrimKey, 0);
        sqliteVdbeAddOp(v, OP_Callback, 6, 0);
      }
    }
  }else

  if( sqliteStrICmp(zLeft, "index_info")==0 ){
    Index *pIdx;
    Table *pTab;
................................................................................
        sqliteVdbeAddOp(v, OP_Integer, pIdx->onError!=OE_None, 0);
        sqliteVdbeAddOp(v, OP_Callback, 3, 0);
        ++i;
        pIdx = pIdx->pNext;
      }
    }
  }else

  if( sqliteStrICmp(zLeft, "foreign_key_list")==0 ){
    FKey *pFK;
    Table *pTab;
    pTab = sqliteFindTable(db, zRight, 0);
    if( pTab ){
      v = sqliteGetVdbe(pParse);
      pFK = pTab->pFKey;
    }
    if( pTab && pFK ){
      int i = 0; 
      static VdbeOp indexListPreface[] = {
        { OP_ColumnName,  0, 0,       "id"},
        { OP_ColumnName,  1, 0,       "seq"},
        { OP_ColumnName,  2, 0,       "table"},
        { OP_ColumnName,  3, 0,       "from"},
        { OP_ColumnName,  4, 0,       "to"},
      };

      sqliteVdbeAddOpList(v, ArraySize(indexListPreface), indexListPreface);
      while(pFK){
        int j;
        for(j=0; j<pFK->nCol; j++){
          sqliteVdbeAddOp(v, OP_Integer, i, 0);
          sqliteVdbeAddOp(v, OP_Integer, j, 0);
          sqliteVdbeAddOp(v, OP_String, 0, 0);
          sqliteVdbeChangeP3(v, -1, pFK->zTo, P3_STATIC);
          sqliteVdbeAddOp(v, OP_String, 0, 0);
          sqliteVdbeChangeP3(v, -1, pTab->aCol[pFK->aCol[j].iFrom].zName,
                             P3_STATIC);
          sqliteVdbeAddOp(v, OP_String, 0, 0);
          sqliteVdbeChangeP3(v, -1, pFK->aCol[j].zCol, P3_STATIC);
          sqliteVdbeAddOp(v, OP_Callback, 5, 0);
        }
        ++i;
        pFK = pFK->pNextFrom;
      }
    }
  }else

  if( sqliteStrICmp(zLeft, "database_list")==0 ){
    int i;
    static VdbeOp indexListPreface[] = {
      { OP_ColumnName,  0, 0,       "seq"},
      { OP_ColumnName,  1, 0,       "name"},
      { OP_ColumnName,  2, 0,       "file"},
................................................................................
      sqliteVdbeChangeP3(v, -1, db->aDb[i].zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, sqliteBtreeGetFilename(db->aDb[i].pBt),
           P3_STATIC);
      sqliteVdbeAddOp(v, OP_Callback, 3, 0);
    }
  }else


  /*
  **   PRAGMA temp_store
  **   PRAGMA temp_store = "default"|"memory"|"file"
  **
  ** Return or set the local value of the temp_store flag.  Changing
  ** the local value does not make changes to the disk file and the default
  ** value will be restored the next time the database is opened.

Changes to src/sqliteInt.h. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.196 2003/08/09 21:32:28 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
** of this structure.
*/
struct Column {
  char *zName;     /* Name of this column */
  char *zDflt;     /* Default value of this column */
  char *zType;     /* Data type for this column */
  u8 notNull;      /* True if there is a NOT NULL constraint */
  u8 isPrimKey;    /* True if this column is an INTEGER PRIMARY KEY */
  u8 sortOrder;    /* Some combination of SQLITE_SO_... values */
};

/*
** The allowed sort orders.
**
** The TEXT and NUM values use bits that do not overlap with DESC and ASC.








|







 







|








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.197 2003/08/23 22:40:54 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
** of this structure.
*/
struct Column {
  char *zName;     /* Name of this column */
  char *zDflt;     /* Default value of this column */
  char *zType;     /* Data type for this column */
  u8 notNull;      /* True if there is a NOT NULL constraint */
  u8 isPrimKey;    /* True if this column is part of the PRIMARY KEY */
  u8 sortOrder;    /* Some combination of SQLITE_SO_... values */
};

/*
** The allowed sort orders.
**
** The TEXT and NUM values use bits that do not overlap with DESC and ASC.

Changes to test/tclsqlite.test. 

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.14 2003/08/19 14:31:02 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check the error messages generated by tclsqlite
#
do_test tcl-1.1 {
................................................................................
  lappend v $msg
} {1 {syntax error in expression "x*"}}

if {[sqlite -encoding]=="UTF-8" && [sqlite -tcl-uses-utf]} {
  do_test tcl-2.1 {
    execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"
    execsql "PRAGMA table_info(t\u0123x)"
  } "0 a int 0 {} 1 b\u1235 float 0 {}"
  do_test tcl-2.2 {
    execsql "INSERT INTO t\u0123x VALUES(1,2.3)"
    db eval "SELECT * FROM t\u0123x" result break
    set result(*)
  } "a b\u1235"
}

if {[sqlite -encoding]=="iso8859" && [sqlite -tcl-uses-utf]} {
  do_test tcl-2.1 {
    execsql "CREATE TABLE t\251x(a int, b\306 float)"
    execsql "PRAGMA table_info(t\251x)"
  } "0 a int 0 {} 1 b\306 float 0 {}"
  do_test tcl-2.2 {
    execsql "INSERT INTO t\251x VALUES(1,2.3)"
    db eval "SELECT * FROM t\251x" result break
    set result(*)
  } "a b\306"
}









|







 







|











|








11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.15 2003/08/23 22:40:54 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Check the error messages generated by tclsqlite
#
do_test tcl-1.1 {
................................................................................
  lappend v $msg
} {1 {syntax error in expression "x*"}}

if {[sqlite -encoding]=="UTF-8" && [sqlite -tcl-uses-utf]} {
  do_test tcl-2.1 {
    execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"
    execsql "PRAGMA table_info(t\u0123x)"
  } "0 a int 0 {} 0 1 b\u1235 float 0 {} 0"
  do_test tcl-2.2 {
    execsql "INSERT INTO t\u0123x VALUES(1,2.3)"
    db eval "SELECT * FROM t\u0123x" result break
    set result(*)
  } "a b\u1235"
}

if {[sqlite -encoding]=="iso8859" && [sqlite -tcl-uses-utf]} {
  do_test tcl-2.1 {
    execsql "CREATE TABLE t\251x(a int, b\306 float)"
    execsql "PRAGMA table_info(t\251x)"
  } "0 a int 0 {} 0 1 b\306 float 0 {} 0"
  do_test tcl-2.2 {
    execsql "INSERT INTO t\251x VALUES(1,2.3)"
    db eval "SELECT * FROM t\251x" result break
    set result(*)
  } "a b\306"
}

This page was generated in about 0.061s by Fossil 2.8 [9c7fdea89b] 2019-01-20 23:57:07