Add support for IF EXISTS on CREATE/DROP TRIGGER/VIEW. Ticket #1899. (CVS 3406)

FossilOrigin-Name: e4fe736cfbbdc081581911a01690576034877b72

5 files changed, 37 insertions, 41 deletions

diff --git a/src/build.c b/src/build.c
index 1f7a9267d..eeceded71 100644
--- a/src/build.c
+++ b/src/build.c
@@ -22,7 +22,7 @@
 **     COMMIT
 **     ROLLBACK
 **
-** $Id: build.c,v 1.410 2006/08/14 14:23:42 drh Exp $
+** $Id: build.c,v 1.411 2006/09/11 23:45:49 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -1590,7 +1590,8 @@ void sqlite3CreateView(
   Token *pName1,     /* The token that holds the name of the view */
   Token *pName2,     /* The token that holds the name of the view */
   Select *pSelect,   /* A SELECT statement that will become the new view */
-  int isTemp         /* TRUE for a TEMPORARY view */
+  int isTemp,        /* TRUE for a TEMPORARY view */
+  int noErr          /* Suppress error messages if VIEW already exists */
 ){
   Table *p;
   int n;
@@ -1605,7 +1606,7 @@ void sqlite3CreateView(
     sqlite3SelectDelete(pSelect);
     return;
   }
-  sqlite3StartTable(pParse, pName1, pName2, isTemp, 1, 0, 0);
+  sqlite3StartTable(pParse, pName1, pName2, isTemp, 1, 0, noErr);
   p = pParse->pNewTable;
   if( p==0 || pParse->nErr ){
     sqlite3SelectDelete(pSelect);
diff --git a/src/parse.y b/src/parse.y
index f9445113c..122d7842c 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -14,7 +14,7 @@
 ** the parser.  Lemon will also generate a header file containing
 ** numeric codes for all of the tokens.
 **
-** @(#) $Id: parse.y,v 1.208 2006/08/25 23:42:53 drh Exp $
+** @(#) $Id: parse.y,v 1.209 2006/09/11 23:45:49 drh Exp $
 */
 
 // All token codes are small integers with #defines that begin with "TK_"
@@ -355,8 +355,8 @@ ifexists(A) ::= .            {A = 0;}
 ///////////////////// The CREATE VIEW statement /////////////////////////////
 //
 %ifndef SQLITE_OMIT_VIEW
-cmd ::= CREATE(X) temp(T) VIEW nm(Y) dbnm(Z) AS select(S). {
-  sqlite3CreateView(pParse, &X, &Y, &Z, S, T);
+cmd ::= CREATE(X) temp(T) VIEW ifnotexists(E) nm(Y) dbnm(Z) AS select(S). {
+  sqlite3CreateView(pParse, &X, &Y, &Z, S, T, E);
 }
 cmd ::= DROP VIEW ifexists(E) fullname(X). {
   sqlite3DropTable(pParse, X, 1, E);
@@ -931,10 +931,10 @@ cmd ::= CREATE trigger_decl(A) BEGIN trigger_cmd_list(S) END(Z). {
   sqlite3FinishTrigger(pParse, S, &all);
 }
 
-trigger_decl(A) ::= temp(T) TRIGGER nm(B) dbnm(Z) trigger_time(C)
-                    trigger_event(D)
+trigger_decl(A) ::= temp(T) TRIGGER ifnotexists(NOERR) nm(B) dbnm(Z) 
+                    trigger_time(C) trigger_event(D)
                     ON fullname(E) foreach_clause(F) when_clause(G). {
-  sqlite3BeginTrigger(pParse, &B, &Z, C, D.a, D.b, E, F, G, T);
+  sqlite3BeginTrigger(pParse, &B, &Z, C, D.a, D.b, E, F, G, T, NOERR);
   A = (Z.n==0?B:Z);
 }
 
@@ -1019,8 +1019,8 @@ raisetype(A) ::= FAIL.      {A = OE_Fail;}
 
 ////////////////////////  DROP TRIGGER statement //////////////////////////////
 %ifndef SQLITE_OMIT_TRIGGER
-cmd ::= DROP TRIGGER fullname(X). {
-  sqlite3DropTrigger(pParse,X);
+cmd ::= DROP TRIGGER ifexists(NOERR) fullname(X). {
+  sqlite3DropTrigger(pParse,X,NOERR);
 }
 %endif // !SQLITE_OMIT_TRIGGER
 
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 582685414..fae55f2ec 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.525 2006/09/02 20:57:52 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.526 2006/09/11 23:45:50 drh Exp $
 */
 #ifndef _SQLITEINT_H_
 #define _SQLITEINT_H_
@@ -1600,7 +1600,7 @@ void sqlite3AddDefaultValue(Parse*,Expr*);
 void sqlite3AddCollateType(Parse*, const char*, int);
 void sqlite3EndTable(Parse*,Token*,Token*,Select*);
 
-void sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int);
+void sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int,int);
 
 #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
   int sqlite3ViewGetColumnNames(Parse*,Table*);
@@ -1691,9 +1691,9 @@ void sqlite3ChangeCookie(sqlite3*, Vdbe*, int);
 
 #ifndef SQLITE_OMIT_TRIGGER
   void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
-                           int,Expr*,int);
+                           int,Expr*,int, int);
   void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
-  void sqlite3DropTrigger(Parse*, SrcList*);
+  void sqlite3DropTrigger(Parse*, SrcList*, int);
   void sqlite3DropTriggerPtr(Parse*, Trigger*);
   int sqlite3TriggersExist(Parse*, Table*, int, ExprList*);
   int sqlite3CodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, 
diff --git a/src/trigger.c b/src/trigger.c
index 15992df38..efff8b833 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -49,7 +49,8 @@ void sqlite3BeginTrigger(
   SrcList *pTableName,/* The name of the table/view the trigger applies to */
   int foreach,        /* One of TK_ROW or TK_STATEMENT */
   Expr *pWhen,        /* WHEN clause */
-  int isTemp          /* True if the TEMPORARY keyword is present */
+  int isTemp,         /* True if the TEMPORARY keyword is present */
+  int noErr           /* Suppress errors if the trigger already exists */
 ){
   Trigger *pTrigger = 0;
   Table *pTab;
@@ -115,7 +116,9 @@ void sqlite3BeginTrigger(
     goto trigger_cleanup;
   }
   if( sqlite3HashFind(&(db->aDb[iDb].pSchema->trigHash), zName,strlen(zName)) ){
-    sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);
+    if( !noErr ){
+      sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);
+    }
     goto trigger_cleanup;
   }
 
@@ -439,7 +442,7 @@ void sqlite3DeleteTrigger(Trigger *pTrigger){
 ** same job as this routine except it takes a pointer to the trigger
 ** instead of the trigger name.
 **/
-void sqlite3DropTrigger(Parse *pParse, SrcList *pName){
+void sqlite3DropTrigger(Parse *pParse, SrcList *pName, int noErr){
   Trigger *pTrigger = 0;
   int i;
   const char *zDb;
@@ -463,7 +466,9 @@ void sqlite3DropTrigger(Parse *pParse, SrcList *pName){
     if( pTrigger ) break;
   }
   if( !pTrigger ){
-    sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0);
+    if( !noErr ){
+      sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0);
+    }
     goto drop_trigger_cleanup;
   }
   sqlite3DropTriggerPtr(pParse, pTrigger);
diff --git a/src/vacuum.c b/src/vacuum.c
index f15648c3d..f7737f88a 100644
--- a/src/vacuum.c
+++ b/src/vacuum.c
@@ -14,7 +14,7 @@
 ** Most of the code in this file may be omitted by defining the
 ** SQLITE_OMIT_VACUUM macro.
 **
-** $Id: vacuum.c,v 1.60 2006/09/11 11:13:27 drh Exp $
+** $Id: vacuum.c,v 1.61 2006/09/11 23:45:50 drh Exp $
 */
 #include "sqliteInt.h"
 #include "vdbeInt.h"
@@ -188,13 +188,6 @@ int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
   /* Query the schema of the main database. Create a mirror schema
   ** in the temporary database.
   */
-  rc = execSql(db,
-      "INSERT INTO vacuum_db.sqlite_master "
-      "  SELECT 'table', name, name, 0, sql"
-      "    FROM sqlite_master"
-      "   WHERE type='table' AND rootpage==0"
-  );
-  if( rc ) goto end_of_vacuum;
   rc = execExecSql(db, 
       "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14,100000000) "
       "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
@@ -209,11 +202,6 @@ int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
       "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
       "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
-  rc = execExecSql(db, 
-      "SELECT 'CREATE VIEW vacuum_db.' || substr(sql,13,100000000) "
-      "  FROM sqlite_master WHERE type='view'"
-  );
-  if( rc!=SQLITE_OK ) goto end_of_vacuum;
 
   /* Loop through the tables in the main database. For each, do
   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
@@ -244,17 +232,19 @@ int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 
 
-  /* Copy the triggers from the main database to the temporary database.
-  ** This was deferred before in case the triggers interfered with copying
-  ** the data. It's possible the indices should be deferred until this
-  ** point also.
+  /* Copy the triggers, views, and virtual tables from the main database
+  ** over to the temporary database.  None of these objects has any
+  ** associated storage, so all we have to do is copy their entries
+  ** from the SQLITE_MASTER table.
   */
-  rc = execExecSql(db, 
-      "SELECT 'CREATE TRIGGER  vacuum_db.' || substr(sql, 16, 1000000) "
-      "FROM sqlite_master WHERE type='trigger'"
+  rc = execSql(db,
+      "INSERT INTO vacuum_db.sqlite_master "
+      "  SELECT type, name, tbl_name, rootpage, sql"
+      "    FROM sqlite_master"
+      "   WHERE type='view' OR type='trigger'"
+      "      OR (type='table' AND rootpage=0)"
   );
-  if( rc!=SQLITE_OK ) goto end_of_vacuum;
-
+  if( rc ) goto end_of_vacuum;
 
   /* At this point, unless the main db was completely empty, there is now a
   ** transaction open on the vacuum database, but not on the main database.