SQLite4
Check-in [035fdd3f5e]
Not logged in

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

Overview
Comment:Enforce NOT NULL on all PRIMARY KEY columns.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | primary-keys
Files: files | file ages | folders
SHA1: 035fdd3f5ea09dbba8a081319cf02083eb8d4a31
User & Date: dan 2012-04-17 09:10:29
Context
2012-04-17
18:47
Fix a couple of issues with ORDER BY. check-in: 34138a088b user: dan tags: primary-keys
09:10
Enforce NOT NULL on all PRIMARY KEY columns. check-in: 035fdd3f5e user: dan tags: primary-keys
08:23
Fix more problems with triggers and triggers on views. check-in: 77b33bff0a user: dan tags: primary-keys
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1148
1149
1150
1151
1152
1153
1154

1155
1156
1157
1158
1159
1160
1161
1162
1163

1164
1165
1166
1167
1168
1169
1170
      "table \"%s\" has more than one primary key", pTab->zName);
    goto primary_key_exit;
  }
  pTab->tabFlags |= TF_HasPrimaryKey;
  if( pList==0 ){
    iCol = pTab->nCol - 1;
    pTab->aCol[iCol].isPrimKey = 1;

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

      }
    }
    if( pList->nExpr>1 ) iCol = -1;
  }

#if 0
  if( iCol>=0 && iCol<pTab->nCol ){







>









>







1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
      "table \"%s\" has more than one primary key", pTab->zName);
    goto primary_key_exit;
  }
  pTab->tabFlags |= TF_HasPrimaryKey;
  if( pList==0 ){
    iCol = pTab->nCol - 1;
    pTab->aCol[iCol].isPrimKey = 1;
    pTab->aCol[iCol].notNull = 1;
  }else{
    for(i=0; i<pList->nExpr; i++){
      for(iCol=0; iCol<pTab->nCol; iCol++){
        if( sqlite4StrICmp(pList->a[i].zName, pTab->aCol[iCol].zName)==0 ){
          break;
        }
      }
      if( iCol<pTab->nCol ){
        pTab->aCol[iCol].isPrimKey = 1;
        pTab->aCol[iCol].notNull = 1;
      }
    }
    if( pList->nExpr>1 ) iCol = -1;
  }

#if 0
  if( iCol>=0 && iCol<pTab->nCol ){

Changes to src/delete.c.

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
...
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
** return a pointer.  Set an error message and return NULL if the table 
** name is not found or if any other error occurs.
**
** The following fields are initialized appropriate in pSrc:
**
**    pSrc->a[0].pTab       Pointer to the Table object
**    pSrc->a[0].pIndex     Pointer to the INDEXED BY index, if there is one
**
*/
Table *sqlite4SrcListLookup(Parse *pParse, SrcList *pSrc){
  struct SrcList_item *pItem = pSrc->a;
  Table *pTab;
  assert( pItem && pSrc->nSrc==1 );
  pTab = sqlite4LocateTable(pParse, 0, pItem->zName, pItem->zDatabase);
  sqlite4DeleteTable(pParse->db, pItem->pTab);
................................................................................
    sqlite4VdbeAddOp2(v, OP_Goto, 0, addrTop);
    sqlite4VdbeJumpHere(v, addrTop);

    /* Close all open cursors */
    sqlite4CloseAllIndexes(pParse, pTab, baseCur);
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->pTriggerTab==0 ){
    sqlite4AutoincrementEnd(pParse);
  }

delete_from_cleanup:
  sqlite4AuthContextPop(&sContext);
  sqlite4SrcListDelete(db, pTabList);
  sqlite4ExprDelete(db, pWhere);
  return;
}








<







 







<
<
<
<
<
<
<
<







22
23
24
25
26
27
28

29
30
31
32
33
34
35
...
374
375
376
377
378
379
380








381
382
383
384
385
386
387
** return a pointer.  Set an error message and return NULL if the table 
** name is not found or if any other error occurs.
**
** The following fields are initialized appropriate in pSrc:
**
**    pSrc->a[0].pTab       Pointer to the Table object
**    pSrc->a[0].pIndex     Pointer to the INDEXED BY index, if there is one

*/
Table *sqlite4SrcListLookup(Parse *pParse, SrcList *pSrc){
  struct SrcList_item *pItem = pSrc->a;
  Table *pTab;
  assert( pItem && pSrc->nSrc==1 );
  pTab = sqlite4LocateTable(pParse, 0, pItem->zName, pItem->zDatabase);
  sqlite4DeleteTable(pParse->db, pItem->pTab);
................................................................................
    sqlite4VdbeAddOp2(v, OP_Goto, 0, addrTop);
    sqlite4VdbeJumpHere(v, addrTop);

    /* Close all open cursors */
    sqlite4CloseAllIndexes(pParse, pTab, baseCur);
  }









delete_from_cleanup:
  sqlite4AuthContextPop(&sContext);
  sqlite4SrcListDelete(db, pTabList);
  sqlite4ExprDelete(db, pWhere);
  return;
}

Changes to test/conflict.test.

355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
    execsql "INSERT into t1 values($i,[expr {$i+1}]);"
  }
  execsql {
    SELECT count(*), min(a), max(b) FROM t1;
  }
} {50 1 51}
do_test conflict-7.2 {
  explain {
    UPDATE OR IGNORE t1 SET a=1000;
  }
  execsql {
    UPDATE OR IGNORE t1 SET a=1000;
  }
} {}
do_test conflict-7.2.1 {
  db changes
} {1}







<
<
<







355
356
357
358
359
360
361



362
363
364
365
366
367
368
    execsql "INSERT into t1 values($i,[expr {$i+1}]);"
  }
  execsql {
    SELECT count(*), min(a), max(b) FROM t1;
  }
} {50 1 51}
do_test conflict-7.2 {



  execsql {
    UPDATE OR IGNORE t1 SET a=1000;
  }
} {}
do_test conflict-7.2.1 {
  db changes
} {1}

Changes to test/simple.test.

48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
...
694
695
696
697
698
699
700
701
702
703
704
705
706









707
708
709
710
711
712
713
    1 y {} 0 {} 0 
}

do_execsql_test 1.4 { 
  CREATE TABLE t3(k PRIMARY KEY, v);
  PRAGMA table_info = t3;
} {
    0 k {} 0 {} 1 
    1 v {} 0 {} 0 
}

do_execsql_test 1.5 { 
  SELECT name, rootpage FROM sqlite_master 
} {t1 2 t2 3 t3 4}

................................................................................
  INSERT INTO t1 VALUES(3, 4);

  CREATE VIEW v1 AS SELECT a, b FROM t1;
  CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN
    INSERT INTO log VALUES(old.b, old.a);
  END;
}

do_execsql_test 38.2 {
  DELETE FROM v1 WHERE a = 3;
  SELECT * FROM log;
} {4 3}











#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');







|







 







<





>
>
>
>
>
>
>
>
>







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
...
694
695
696
697
698
699
700

701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
    1 y {} 0 {} 0 
}

do_execsql_test 1.4 { 
  CREATE TABLE t3(k PRIMARY KEY, v);
  PRAGMA table_info = t3;
} {
    0 k {} 1 {} 1 
    1 v {} 0 {} 0 
}

do_execsql_test 1.5 { 
  SELECT name, rootpage FROM sqlite_master 
} {t1 2 t2 3 t3 4}

................................................................................
  INSERT INTO t1 VALUES(3, 4);

  CREATE VIEW v1 AS SELECT a, b FROM t1;
  CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN
    INSERT INTO log VALUES(old.b, old.a);
  END;
}

do_execsql_test 38.2 {
  DELETE FROM v1 WHERE a = 3;
  SELECT * FROM log;
} {4 3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 39.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}
breakpoint
do_catchsql_test 39.2 {
  INSERT INTO t1 VALUES(NULL, 'xyz');
} {1 {t1.a may not be NULL}}

#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');