SQLite: Check-in [810d9f63]

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

Overview

Comment:	Remove the MySQL upsert syntax. As an optional alias to the insert table name in order to finish out PostgreSQL upsert syntax emulation.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| upsert
Files:	files \| file ages \| folders
SHA3-256:	810d9f63be46c1452718b05e51c519fb64e9fabf2f1813b5e07b1041dbb71880
User & Date:	drh 2018-04-16 13:26:53

Context

2018-04-16
14:36		Merge the Lemon enhancement from trunk, resulting in smaller parser tables. (check-in: 641f4172 user: drh tags: upsert)
13:26		Remove the MySQL upsert syntax. As an optional alias to the insert table name in order to finish out PostgreSQL upsert syntax emulation. (check-in: 810d9f63 user: drh tags: upsert)
13:00		Add support for the "excluded.*" names in the UPDATE clause of an upsert. (check-in: 0203f34f user: drh tags: upsert)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.


// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH DO DUPLICATE
  EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
  ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
................................................................................
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
        upsert(U). {
  sqlite3Insert(pParse, X, S, F, R, U);





}
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}
upsert(A) ::= ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,0,0,Z,W); }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
upsert(A) ::= ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,0,0,0,0); }

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}


// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH DO
  EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
  ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
................................................................................
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
        upsert(U). {
  sqlite3Insert(pParse, X, S, F, R, U);
}
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) AS nm(A) select(S)
        upsert(U). {
  if( X ) X->a[0].zAlias = sqlite3NameFromToken(pParse->db, &A);
  sqlite3Insert(pParse, X, S, F, R, U);
}
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}


upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
upsert(A) ::= ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,0,0,0,0); }

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}

Changes to src/resolve.c.

        if( iCol<pTab->nCol ){
          cnt++;
#ifndef SQLITE_OMIT_UPSERT
          if( pExpr->iTable==2 ){
            pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
            eNewExprOp = TK_REGISTER;
          }else
#endif
          {
#ifndef SQLITE_OMIT_TRIGGER
            if( iCol<0 ){
              pExpr->affinity = SQLITE_AFF_INTEGER;
            }else if( pExpr->iTable==0 ){
              testcase( iCol==31 );
              testcase( iCol==32 );
................................................................................
              testcase( iCol==31 );
              testcase( iCol==32 );
              pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
            }
            pExpr->pTab = pTab;
            pExpr->iColumn = (i16)iCol;
            eNewExprOp = TK_TRIGGER;
#endif /* SQLITE_OMIT_TRIGGER
          }
        }
      }
    }
#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */

    /*

        if( iCol<pTab->nCol ){
          cnt++;
#ifndef SQLITE_OMIT_UPSERT
          if( pExpr->iTable==2 ){
            pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
            eNewExprOp = TK_REGISTER;
          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
#ifndef SQLITE_OMIT_TRIGGER
            if( iCol<0 ){
              pExpr->affinity = SQLITE_AFF_INTEGER;
            }else if( pExpr->iTable==0 ){
              testcase( iCol==31 );
              testcase( iCol==32 );
................................................................................
              testcase( iCol==31 );
              testcase( iCol==32 );
              pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
            }
            pExpr->pTab = pTab;
            pExpr->iColumn = (i16)iCol;
            eNewExprOp = TK_TRIGGER;
#endif /* SQLITE_OMIT_TRIGGER */
          }
        }
      }
    }
#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */

    /*

Changes to tool/mkkeywordhash.c.

 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203

 { "DEFERRABLE", { "DELETE", { "DESC", { "DETACH", { "DISTINCT", { "DO", { "DROP",  { "END", { "EACH", { "ELSE", { "ESCAPE", { "EXCEPT", { "EXCLUSIVE", { "EXISTS",

 189 190 191 192 193 194 195

196 197 198 199 200 201 202

 { "DEFERRABLE", { "DELETE", { "DESC", { "DETACH", { "DISTINCT", { "DO", { "DROP",

{ "END", { "EACH", { "ELSE", { "ESCAPE", { "EXCEPT", { "EXCLUSIVE", { "EXISTS",

"TK_DEFERRABLE", FKEY }, "TK_DELETE", ALWAYS }, "TK_DESC", ALWAYS }, "TK_DETACH", ATTACH }, "TK_DISTINCT", ALWAYS }, "TK_DO", UPSERT }, "TK_DROP", ALWAYS }, { "DUPLICATE", "TK_DUPLICATE", UPSERT }, "TK_END", ALWAYS }, "TK_EACH", TRIGGER }, "TK_ELSE", ALWAYS }, "TK_ESCAPE", ALWAYS }, "TK_EXCEPT", COMPOUND }, "TK_EXCLUSIVE", ALWAYS }, "TK_EXISTS", ALWAYS }, "TK_DEFERRABLE", FKEY }, "TK_DELETE", ALWAYS }, "TK_DESC", ALWAYS }, "TK_DETACH", ATTACH }, "TK_DISTINCT", ALWAYS }, "TK_DO", UPSERT }, "TK_DROP", ALWAYS }, "TK_END", ALWAYS }, "TK_EACH", TRIGGER }, "TK_ELSE", ALWAYS }, "TK_ESCAPE", ALWAYS }, "TK_EXCEPT", COMPOUND }, "TK_EXCLUSIVE", ALWAYS }, "TK_EXISTS", ALWAYS },