SQLite

Check-in [1fa74930ab]
Login

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

Overview
Comment:Enhance triggers so that they can use table-valued-functions that exist in schemas outside of the schema in which the trigger is defined.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1fa74930ab56171e2e840d4a5b259abafb0ad1e0320fc3030066570a6dd10002
User & Date: drh 2018-11-02 17:38:39.450
References
2018-11-05
16:38
Eponymous virtual tables appear to exist in all schemas. This is an alternative and improved fix to the eponymous virtual table in trigger problem that was previously addressed by checkin [1fa74930ab56171e]. (check-in: b8d35c4a7c user: drh tags: trunk)
Context
2018-11-03
13:11
Fix a assert() in the query planner that can arise when doing row-value operations on a PRIMARY KEY that contains duplicate columns. Ticket [1a84668dcfdebaf12415d]. (check-in: dcb8c73594 user: drh tags: trunk)
2018-11-02
17:38
Enhance triggers so that they can use table-valued-functions that exist in schemas outside of the schema in which the trigger is defined. (check-in: 1fa74930ab user: drh tags: trunk)
2018-10-31
20:52
Deploy the sqlite3Strlen30NN() function (argument guaranteed to be non-NULL) for a small performance improvement. (check-in: 4a6ad5190b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/attach.c.
495
496
497
498
499
500
501

502

503
504
505
506
507
508
509
        sqlite3ErrorMsg(pFix->pParse,
            "%s %T cannot reference objects in database %s",
            pFix->zType, pFix->pName, pItem->zDatabase);
        return 1;
      }
      sqlite3DbFree(pFix->pParse->db, pItem->zDatabase);
      pItem->zDatabase = 0;

      pItem->pSchema = pFix->pSchema;

    }
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
    if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;
    if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1;
#endif
    if( pItem->fg.isTabFunc && sqlite3FixExprList(pFix, pItem->u1.pFuncArg) ){
      return 1;







>
|
>







495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
        sqlite3ErrorMsg(pFix->pParse,
            "%s %T cannot reference objects in database %s",
            pFix->zType, pFix->pName, pItem->zDatabase);
        return 1;
      }
      sqlite3DbFree(pFix->pParse->db, pItem->zDatabase);
      pItem->zDatabase = 0;
      if( !pItem->fg.isTabFunc ){
        pItem->pSchema = pFix->pSchema;
      }
    }
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
    if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;
    if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1;
#endif
    if( pItem->fg.isTabFunc && sqlite3FixExprList(pFix, pItem->u1.pFuncArg) ){
      return 1;
Changes to test/attach.test.
722
723
724
725
726
727
728

























729
730
731
732
733
734
735
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
} ;# endif subquery

























} ;# endif trigger

# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {







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







722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
} ;# endif subquery
ifcapable json1 {
  do_test attach-5.10 {
    db close
    forcedelete test.db
    sqlite3 db test.db
    db eval {
      CREATE TABLE t1(x);
      CREATE TABLE t2(a,b);
      CREATE TRIGGER x1 AFTER INSERT ON t1 BEGIN
        INSERT INTO t2(a,b) SELECT key, value FROM json_each(NEW.x);
      END;
      INSERT INTO t1(x) VALUES('{"a":1}');
      SELECT * FROM t2;
    }
  } {a 1}
  do_test attach-5.11 {
    sqlite3 db2 :memory:
    db2 eval {
      CREATE TABLE t3(y);
      ATTACH 'test.db' AS aux;
      INSERT INTO aux.t1(x) VALUES('{"b":2}');
      SELECT * FROM aux.t2;
    }
  } {a 1 b 2}
} ;# endif json1
} ;# endif trigger

# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {