SQLite

Check-in [669bcf5ab6]
Login

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

Overview
Comment:Remove support for the non-standard ON CONFLICT clause on CREATE INDEX. Ticket #1486. The ON CONFLICT clause has never worked on CREATE INDEX so removing it should not break anything. (CVS 3042)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 669bcf5ab694359485ab30913d8d9e4926f41789
User & Date: drh 2006-01-30 23:04:51.000
Context
2006-01-31
14:28
Authorization callback on the ALTER TABLE ADD COLUMN command. Ticket #1479. (CVS 3043) (check-in: 461f586973 user: drh tags: trunk)
2006-01-30
23:04
Remove support for the non-standard ON CONFLICT clause on CREATE INDEX. Ticket #1486. The ON CONFLICT clause has never worked on CREATE INDEX so removing it should not break anything. (CVS 3042) (check-in: 669bcf5ab6 user: drh tags: trunk)
22:48
Memory DB works with autovacuum. (CVS 3041) (check-in: 34dff874a2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.193 2006/01/23 13:22:09 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.194 2006/01/30 23:04:51 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
exprlist(A) ::= expritem(X).            {A = sqlite3ExprListAppend(0,X,0);}
expritem(A) ::= expr(X).                {A = X;}
expritem(A) ::= .                       {A = 0;}

///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= CREATE(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP idxlist(Z) RP(E) onconf(R). {
  if( U!=OE_None ) U = R;
  if( U==OE_Default) U = OE_Abort;
  sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(0,&Y,0), Z, U,
                      &S, &E, SQLITE_SO_ASC, NE);
}

%type uniqueflag {int}
uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
uniqueflag(A) ::= .        {A = OE_None;}







|
<
<







836
837
838
839
840
841
842
843


844
845
846
847
848
849
850
exprlist(A) ::= expritem(X).            {A = sqlite3ExprListAppend(0,X,0);}
expritem(A) ::= expr(X).                {A = X;}
expritem(A) ::= .                       {A = 0;}

///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= CREATE(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP idxlist(Z) RP(E). {


  sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(0,&Y,0), Z, U,
                      &S, &E, SQLITE_SO_ASC, NE);
}

%type uniqueflag {int}
uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
uniqueflag(A) ::= .        {A = OE_None;}
Changes to www/lang.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.106 2006/01/23 18:14:22 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.107 2006/01/30 23:04:52 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452


Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously







<







438
439
440
441
442
443
444

445
446
447
448
449
450
451


Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )

} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.</p>

<p>The optional conflict-clause allows the specification of an alternative
default constraint conflict resolution algorithm for this index.
This only makes sense if the UNIQUE keyword is used since otherwise
there are not constraints on the index.  The default algorithm is
ABORT.  If a COPY, INSERT, or UPDATE statement specifies a particular
conflict resolution algorithm, that algorithm is used in place of
the default algorithm specified here.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>







<
<
<
<
<
<
<
<
<
<







464
465
466
467
468
469
470










471
472
473
474
475
476
477
<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.</p>











<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473


1474
1475
1476
1477
1478
1479
1480
puts {
<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE and CREATE INDEX commands.  For the COPY, INSERT, and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make
the syntax seem more natural.  But the meaning of the clause is the same


either way.</p>

<p>The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts.  There are five choices: ROLLBACK, ABORT,
FAIL, IGNORE, and REPLACE. The default algorithm is ABORT.  This
is what they mean:</p>








|

|
>
>







1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
puts {
<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE command.  For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make
the syntax seem more natural.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>

<p>The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts.  There are five choices: ROLLBACK, ABORT,
FAIL, IGNORE, and REPLACE. The default algorithm is ABORT.  This
is what they mean:</p>

1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
value, then the ABORT algorithm is used.</p>

<p>When this conflict resolution strategy deletes rows in order to
satisfy a constraint, it does not invoke delete triggers on those
rows.  But that may change in a future release.</p>
</dl>

<p>The algorithm specified in the OR clause of a COPY, INSERT, or UPDATE
overrides any algorithm specified in a CREATE TABLE or CREATE INDEX.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
}
# <p>For additional information, see 
# <a href="conflict.html">conflict.html</a>.</p>

Section REINDEX reindex

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {







|
|


<
<







1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520


1521
1522
1523
1524
1525
1526
1527
value, then the ABORT algorithm is used.</p>

<p>When this conflict resolution strategy deletes rows in order to
satisfy a constraint, it does not invoke delete triggers on those
rows.  But that may change in a future release.</p>
</dl>

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
}



Section REINDEX reindex

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {