SQLite

/*
** 2003 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    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 ATTACH and DETACH commands.
**
** $Id: attach.c,v 1.5 2003/06/03 01:47:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called by the parser to process an ATTACH statement:
**
**     ATTACH DATABASE filename AS dbname

  const Token *pName  /* Name of the view, trigger, or index */
){
  sqlite *db;

  if( iDb<0 || iDb==1 ) return 0;
  db = pParse->db;
  assert( db->nDb>iDb );
  pFix->pParse = pParse;
  pFix->zDb = db->aDb[iDb].zName;
  pFix->zType = zType;
  pFix->pName = pName;
  return 1;
}

/*

  if( pList==0 ) return 0;
  zDb = pFix->zDb;
  for(i=0; i<pList->nSrc; i++){
    if( pList->a[i].zDatabase==0 ){
      pList->a[i].zDatabase = sqliteStrDup(zDb);
    }else if( sqliteStrICmp(pList->a[i].zDatabase,zDb)!=0 ){
      sqliteErrorMsg(pFix->pParse,
         "%s %z cannot reference objects in database %s",
         pFix->zType, sqliteStrNDup(pFix->pName->z, pFix->pName->n),
         pList->a[i].zDatabase);
      return 1;
    }
    if( sqliteFixSelect(pFix, pList->a[i].pSelect) ) return 1;
    if( sqliteFixExpr(pFix, pList->a[i].pOn) ) return 1;
  }
  return 0;
}

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    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.191 2003/06/03 01:47:11 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"

  u8 iTabDb;              /* Database containing Trigger.table              */
  u8 op;                  /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
  u8 tr_tm;               /* One of TK_BEFORE, TK_AFTER */
  Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                             the <column-list> is stored here */
  int foreach;            /* One of TK_ROW or TK_STATEMENT */
  Token nameToken;        /* Token containing zName. Use during parsing only */

  TriggerStep *step_list; /* Link list of trigger program steps             */
  Trigger *pNext;         /* Next trigger associated with the table */
};

/*
 * An instance of struct TriggerStep is used to store a single SQL statement

  int isTemp          /* True if the TEMPORARY keyword is present */
){
  Trigger *nt;
  Table   *tab;
  char *zName = 0;        /* Name of the trigger */
  sqlite *db = pParse->db;
  int iDb;                /* When database to store the trigger in */
  DbFixer sFix;

  /* Check that: 
  ** 1. the trigger name does not already exist.
  ** 2. the table (or view) does exist in the same database as the trigger.
  ** 3. that we are not trying to create a trigger on the sqlite_master table
  ** 4. That we are not trying to create an INSTEAD OF trigger on a table.
  ** 5. That we are not trying to create a BEFORE or AFTER trigger on a view.
  */
  if( sqlite_malloc_failed ) goto trigger_cleanup;
  assert( pTableName->nSrc==1 );

  if( pParse->initFlag 
   && sqliteFixInit(&sFix, pParse, pParse->iDb, "trigger", pName)
   && sqliteFixSrcList(&sFix, pTableName)
  ){
    goto trigger_cleanup;
  }
  tab = sqliteSrcListLookup(pParse, pTableName);

  if( !tab ){
    goto trigger_cleanup;
  }
  iDb = isTemp ? 1 : tab->iDb;
  if( iDb>=2 && !pParse->initFlag ){
    sqliteErrorMsg(pParse, "triggers may not be added to auxiliary "
       "database %s", db->aDb[tab->iDb].zName);

  nt->iDb = iDb;
  nt->iTabDb = tab->iDb;
  nt->op = op;
  nt->tr_tm = tr_tm;
  nt->pWhen = sqliteExprDup(pWhen);
  nt->pColumns = sqliteIdListDup(pColumns);
  nt->foreach = foreach;
  sqliteTokenCopy(&nt->nameToken,pName);
  assert( pParse->pNewTrigger==0 );
  pParse->pNewTrigger = nt;

trigger_cleanup:
  sqliteFree(zName);
  sqliteSrcListDelete(pTableName);
  sqliteIdListDelete(pColumns);

  nt = pParse->pNewTrigger;
  pParse->pNewTrigger = 0;
  nt->step_list = pStepList;
  while( pStepList ){
    pStepList->pTrig = nt;
    pStepList = pStepList->pNext;
  }
  if( sqliteFixInit(&sFix, pParse, nt->iDb, "trigger", &nt->nameToken) 
          && sqliteFixTriggerStep(&sFix, nt->step_list) ){
    goto triggerfinish_cleanup;
  }


  /* if we are not initializing, and this trigger is not on a TEMP table, 
  ** build the sqlite_master entry
  */
  if( !pParse->initFlag ){
    static VdbeOp insertTrig[] = {
      { OP_NewRecno,   0, 0,  0          },

void sqliteDeleteTrigger(Trigger *pTrigger){
  if( pTrigger==0 ) return;
  sqliteDeleteTriggerStep(pTrigger->step_list);
  sqliteFree(pTrigger->name);
  sqliteFree(pTrigger->table);
  sqliteExprDelete(pTrigger->pWhen);
  sqliteIdListDelete(pTrigger->pColumns);
  if( pTrigger->nameToken.dyn ) sqliteFree((char*)pTrigger->nameToken.z);
  sqliteFree(pTrigger);
}

/*
 * This function is called to drop a trigger from the database schema. 
 *
 * This may be called directly from the parser and therefore identifies

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.7 2003/06/03 01:47:12 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db

} {102 910 607 1314}
do_test attach-4.13 {
  execsql {
    SELECT * FROM main.v3;
  }
} {910 1112 1516}

# Tests for the sqliteFix...() routines in attach.c
#
do_test attach-5.1 {
  db close
  sqlite db test.db
  file delete -force test2.db
  sqlite db2 test2.db
  catchsql {
    ATTACH DATABASE 'test.db' AS orig;
    CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN;
      SELECT 'no-op';
    END;
  } db2
} {1 {triggers may not be added to auxiliary database orig}}
do_test attach-5.2 {
  catchsql {
    CREATE TABLE t5(x,y);
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op';
    END;
  } db2
} {0 {}}
do_test attach-5.3 {
  catchsql {
    DROP TRIGGER r5;
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM orig.t1;
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database orig}}
do_test attach-5.4 {
  catchsql {
    CREATE TEMP TABLE t6(p,q,r);
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM temp.t6;
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.5 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' || (SELECT * FROM temp.t6);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.6 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.7 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.7 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.8 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
do_test attach-5.9 {
  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}}

# 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 {
    ATTACH DATABASE 'no-such-file' AS nosuch;
  }
} {1 {cannot attach empty database: nosuch}}
file delete -force no-such-file
do_test attach-6.2 {
  sqlite dbx cannot-read
  dbx eval {CREATE TABLE t1(a,b,c)}
  dbx close
  catch {file attributes cannot-read -permission 0000}
  catch {file attributes cannot-read -readonly 1}
  catchsql {
    ATTACH DATABASE 'cannot-read' AS noread;
  }
} {1 {unable to open database: cannot-read}}
file delete -force cannot-read

for {set i 2} {$i<=15} {incr i} {
  catch {db$i close}
}
file delete -force test2.db


finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the the library is able to correctly
# handle file-format 3 (version 2.6.x) databases.
#
# $Id: format3.test,v 1.3 2003/06/03 01:47:12 drh Exp $

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

# Create a bunch of data to sort against
#
do_test format3-1.0 {

    INSERT INTO t7 VALUES(0,0,1);
    INSERT INTO t7 VALUES(0.0,0,2);
    INSERT INTO t7 VALUES(0,0.0,3);
    INSERT INTO t7 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t7 ORDER BY z;
  }
} {0 0}

# Make sure attempts to attach a format 3 database fail.
#
do_test format3-12.1 {
  file delete -force test2.db
  sqlite db2 test2.db
  catchsql {
    CREATE TABLE t8(x,y);
    ATTACH DATABASE 'test.db' AS format3;
  } db2;
} {1 {incompatible file format in auxiliary database: format3}}
do_test format3-12.2 {
  catchsql {
    ATTACH DATABASE 'test2.db' AS test2;
  }
} {1 {cannot attach auxiliary databases to an older format master database}}
db2 close

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.22 2003/06/03 01:47:12 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#

do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}

# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
      INSERT INTO RealTable(TestString) 
         SELECT new.TestString FROM TempTable LIMIT 1;
    END;
    INSERT INTO TempTable(TestString) VALUES ('1');
    INSERT INTO TempTable(TestString) VALUES ('2');
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID IN (1, 2);
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}

finish_test