sourcemod-central: extensions/sqlite/sqlite-source/insert.c@bee90c8ef6c3 (annotated)

ds@1952	1	/*
ds@1952	2	** 2001 September 15
ds@1952	3	**
ds@1952	4	** The author disclaims copyright to this source code. In place of
ds@1952	5	** a legal notice, here is a blessing:
ds@1952	6	**
ds@1952	7	** May you do good and not evil.
ds@1952	8	** May you find forgiveness for yourself and forgive others.
ds@1952	9	** May you share freely, never taking more than you give.
ds@1952	10	**
ds@1952	11	*************************************************************************
ds@1952	12	** This file contains C code routines that are called by the parser
ds@1952	13	** to handle INSERT statements in SQLite.
ds@1952	14	**
ds@1952	15	** $Id$
ds@1952	16	*/
ds@1952	17	#include "sqliteInt.h"
ds@1952	18
ds@1952	19	/*
ds@1952	20	** Set P3 of the most recently inserted opcode to a column affinity
ds@1952	21	** string for index pIdx. A column affinity string has one character
ds@1952	22	** for each column in the table, according to the affinity of the column:
ds@1952	23	**
ds@1952	24	** Character Column affinity
ds@1952	25	** ------------------------------
ds@1952	26	** 'a' TEXT
ds@1952	27	** 'b' NONE
ds@1952	28	** 'c' NUMERIC
ds@1952	29	** 'd' INTEGER
ds@1952	30	** 'e' REAL
ds@1952	31	*/
ds@1952	32	void sqlite3IndexAffinityStr(Vdbe v, Index pIdx){
ds@1952	33	if( !pIdx->zColAff ){
ds@1952	34	/* The first time a column affinity string for a particular index is
ds@1952	35	** required, it is allocated and populated here. It is then stored as
ds@1952	36	** a member of the Index structure for subsequent use.
ds@1952	37	**
ds@1952	38	** The column affinity string will eventually be deleted by
ds@1952	39	** sqliteDeleteIndex() when the Index structure itself is cleaned
ds@1952	40	** up.
ds@1952	41	*/
ds@1952	42	int n;
ds@1952	43	Table *pTab = pIdx->pTable;
ds@1952	44	sqlite3 *db = sqlite3VdbeDb(v);
ds@1952	45	pIdx->zColAff = (char *)sqlite3DbMallocZero(db, pIdx->nColumn+1);
ds@1952	46	if( !pIdx->zColAff ){
ds@1952	47	return;
ds@1952	48	}
ds@1952	49	for(n=0; n<pIdx->nColumn; n++){
ds@1952	50	pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity;
ds@1952	51	}
ds@1952	52	pIdx->zColAff[pIdx->nColumn] = '\0';
ds@1952	53	}
ds@1952	54
ds@1952	55	sqlite3VdbeChangeP3(v, -1, pIdx->zColAff, 0);
ds@1952	56	}
ds@1952	57
ds@1952	58	/*
ds@1952	59	** Set P3 of the most recently inserted opcode to a column affinity
ds@1952	60	** string for table pTab. A column affinity string has one character
ds@1952	61	** for each column indexed by the index, according to the affinity of the
ds@1952	62	** column:
ds@1952	63	**
ds@1952	64	** Character Column affinity
ds@1952	65	** ------------------------------
ds@1952	66	** 'a' TEXT
ds@1952	67	** 'b' NONE
ds@1952	68	** 'c' NUMERIC
ds@1952	69	** 'd' INTEGER
ds@1952	70	** 'e' REAL
ds@1952	71	*/
ds@1952	72	void sqlite3TableAffinityStr(Vdbe v, Table pTab){
ds@1952	73	/* The first time a column affinity string for a particular table
ds@1952	74	** is required, it is allocated and populated here. It is then
ds@1952	75	** stored as a member of the Table structure for subsequent use.
ds@1952	76	**
ds@1952	77	** The column affinity string will eventually be deleted by
ds@1952	78	** sqlite3DeleteTable() when the Table structure itself is cleaned up.
ds@1952	79	*/
ds@1952	80	if( !pTab->zColAff ){
ds@1952	81	char *zColAff;
ds@1952	82	int i;
ds@1952	83	sqlite3 *db = sqlite3VdbeDb(v);
ds@1952	84
ds@1952	85	zColAff = (char *)sqlite3DbMallocZero(db, pTab->nCol+1);
ds@1952	86	if( !zColAff ){
ds@1952	87	return;
ds@1952	88	}
ds@1952	89
ds@1952	90	for(i=0; i<pTab->nCol; i++){
ds@1952	91	zColAff[i] = pTab->aCol[i].affinity;
ds@1952	92	}
ds@1952	93	zColAff[pTab->nCol] = '\0';
ds@1952	94
ds@1952	95	pTab->zColAff = zColAff;
ds@1952	96	}
ds@1952	97
ds@1952	98	sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0);
ds@1952	99	}
ds@1952	100
ds@1952	101	/*
ds@1952	102	** Return non-zero if SELECT statement p opens the table with rootpage
ds@1952	103	** iTab in database iDb. This is used to see if a statement of the form
ds@1952	104	** "INSERT INTO <iDb, iTab> SELECT ..." can run without using temporary
ds@1952	105	** table for the results of the SELECT.
ds@1952	106	**
ds@1952	107	** No checking is done for sub-selects that are part of expressions.
ds@1952	108	*/
ds@1952	109	static int selectReadsTable(Select p, Schema pSchema, int iTab){
ds@1952	110	int i;
ds@1952	111	struct SrcList_item *pItem;
ds@1952	112	if( p->pSrc==0 ) return 0;
ds@1952	113	for(i=0, pItem=p->pSrc->a; i<p->pSrc->nSrc; i++, pItem++){
ds@1952	114	if( pItem->pSelect ){
ds@1952	115	if( selectReadsTable(pItem->pSelect, pSchema, iTab) ) return 1;
ds@1952	116	}else{
ds@1952	117	if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1;
ds@1952	118	}
ds@1952	119	}
ds@1952	120	return 0;
ds@1952	121	}
ds@1952	122
ds@1952	123	#ifndef SQLITE_OMIT_AUTOINCREMENT
ds@1952	124	/*
ds@1952	125	** Write out code to initialize the autoincrement logic. This code
ds@1952	126	** looks up the current autoincrement value in the sqlite_sequence
ds@1952	127	** table and stores that value in a memory cell. Code generated by
ds@1952	128	** autoIncStep() will keep that memory cell holding the largest
ds@1952	129	** rowid value. Code generated by autoIncEnd() will write the new
ds@1952	130	** largest value of the counter back into the sqlite_sequence table.
ds@1952	131	**
ds@1952	132	** This routine returns the index of the mem[] cell that contains
ds@1952	133	** the maximum rowid counter.
ds@1952	134	**
ds@1952	135	** Two memory cells are allocated. The next memory cell after the
ds@1952	136	** one returned holds the rowid in sqlite_sequence where we will
ds@1952	137	** write back the revised maximum rowid.
ds@1952	138	*/
ds@1952	139	static int autoIncBegin(
ds@1952	140	Parse pParse, / Parsing context */
ds@1952	141	int iDb, /* Index of the database holding pTab */
ds@1952	142	Table pTab / The table we are writing to */
ds@1952	143	){
ds@1952	144	int memId = 0;
ds@1952	145	if( pTab->autoInc ){
ds@1952	146	Vdbe *v = pParse->pVdbe;
ds@1952	147	Db *pDb = &pParse->db->aDb[iDb];
ds@1952	148	int iCur = pParse->nTab;
ds@1952	149	int addr;
ds@1952	150	assert( v );
ds@1952	151	addr = sqlite3VdbeCurrentAddr(v);
ds@1952	152	memId = pParse->nMem+1;
ds@1952	153	pParse->nMem += 2;
ds@1952	154	sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
ds@1952	155	sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13);
ds@1952	156	sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
ds@1952	157	sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
ds@1952	158	sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12);
ds@1952	159	sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
ds@1952	160	sqlite3VdbeAddOp(v, OP_MemStore, memId-1, 1);
ds@1952	161	sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
ds@1952	162	sqlite3VdbeAddOp(v, OP_MemStore, memId, 1);
ds@1952	163	sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13);
ds@1952	164	sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4);
ds@1952	165	sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
ds@1952	166	}
ds@1952	167	return memId;
ds@1952	168	}
ds@1952	169
ds@1952	170	/*
ds@1952	171	** Update the maximum rowid for an autoincrement calculation.
ds@1952	172	**
ds@1952	173	** This routine should be called when the top of the stack holds a
ds@1952	174	** new rowid that is about to be inserted. If that new rowid is
ds@1952	175	** larger than the maximum rowid in the memId memory cell, then the
ds@1952	176	** memory cell is updated. The stack is unchanged.
ds@1952	177	*/
ds@1952	178	static void autoIncStep(Parse *pParse, int memId){
ds@1952	179	if( memId>0 ){
ds@1952	180	sqlite3VdbeAddOp(pParse->pVdbe, OP_MemMax, memId, 0);
ds@1952	181	}
ds@1952	182	}
ds@1952	183
ds@1952	184	/*
ds@1952	185	** After doing one or more inserts, the maximum rowid is stored
ds@1952	186	** in mem[memId]. Generate code to write this value back into the
ds@1952	187	** the sqlite_sequence table.
ds@1952	188	*/
ds@1952	189	static void autoIncEnd(
ds@1952	190	Parse pParse, / The parsing context */
ds@1952	191	int iDb, /* Index of the database holding pTab */
ds@1952	192	Table pTab, / Table we are inserting into */
ds@1952	193	int memId /* Memory cell holding the maximum rowid */
ds@1952	194	){
ds@1952	195	if( pTab->autoInc ){
ds@1952	196	int iCur = pParse->nTab;
ds@1952	197	Vdbe *v = pParse->pVdbe;
ds@1952	198	Db *pDb = &pParse->db->aDb[iDb];
ds@1952	199	int addr;
ds@1952	200	assert( v );
ds@1952	201	addr = sqlite3VdbeCurrentAddr(v);
ds@1952	202	sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
ds@1952	203	sqlite3VdbeAddOp(v, OP_MemLoad, memId-1, 0);
ds@1952	204	sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7);
ds@1952	205	sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
ds@1952	206	sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0);
ds@1952	207	sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
ds@1952	208	sqlite3VdbeAddOp(v, OP_MemLoad, memId, 0);
ds@1952	209	sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0);
ds@1952	210	sqlite3VdbeAddOp(v, OP_Insert, iCur, OPFLAG_APPEND);
ds@1952	211	sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
ds@1952	212	}
ds@1952	213	}
ds@1952	214	#else
ds@1952	215	/*
ds@1952	216	** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
ds@1952	217	** above are all no-ops
ds@1952	218	*/
ds@1952	219	# define autoIncBegin(A,B,C) (0)
ds@1952	220	# define autoIncStep(A,B)
ds@1952	221	# define autoIncEnd(A,B,C,D)
ds@1952	222	#endif /* SQLITE_OMIT_AUTOINCREMENT */
ds@1952	223
ds@1952	224
ds@1952	225	/* Forward declaration */
ds@1952	226	static int xferOptimization(
ds@1952	227	Parse pParse, / Parser context */
ds@1952	228	Table pDest, / The table we are inserting into */
ds@1952	229	Select pSelect, / A SELECT statement to use as the data source */
ds@1952	230	int onError, /* How to handle constraint errors */
ds@1952	231	int iDbDest /* The database of pDest */
ds@1952	232	);
ds@1952	233
ds@1952	234	/*
ds@1952	235	** This routine is call to handle SQL of the following forms:
ds@1952	236	**
ds@1952	237	** insert into TABLE (IDLIST) values(EXPRLIST)
ds@1952	238	** insert into TABLE (IDLIST) select
ds@1952	239	**
ds@1952	240	** The IDLIST following the table name is always optional. If omitted,
ds@1952	241	** then a list of all columns for the table is substituted. The IDLIST
ds@1952	242	** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted.
ds@1952	243	**
ds@1952	244	** The pList parameter holds EXPRLIST in the first form of the INSERT
ds@1952	245	** statement above, and pSelect is NULL. For the second form, pList is
ds@1952	246	** NULL and pSelect is a pointer to the select statement used to generate
ds@1952	247	** data for the insert.
ds@1952	248	**
ds@1952	249	** The code generated follows one of four templates. For a simple
ds@1952	250	** select with data coming from a VALUES clause, the code executes
ds@1952	251	** once straight down through. The template looks like this:
ds@1952	252	**
ds@1952	253	** open write cursor to <table> and its indices
ds@1952	254	** puts VALUES clause expressions onto the stack
ds@1952	255	** write the resulting record into <table>
ds@1952	256	** cleanup
ds@1952	257	**
ds@1952	258	** The three remaining templates assume the statement is of the form
ds@1952	259	**
ds@1952	260	** INSERT INTO <table> SELECT ...
ds@1952	261	**
ds@1952	262	** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
ds@1952	263	** in other words if the SELECT pulls all columns from a single table
ds@1952	264	** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
ds@1952	265	** if <table2> and <table1> are distinct tables but have identical
ds@1952	266	** schemas, including all the same indices, then a special optimization
ds@1952	267	** is invoked that copies raw records from <table2> over to <table1>.
ds@1952	268	** See the xferOptimization() function for the implementation of this
ds@1952	269	** template. This is the second template.
ds@1952	270	**
ds@1952	271	** open a write cursor to <table>
ds@1952	272	** open read cursor on <table2>
ds@1952	273	** transfer all records in <table2> over to <table>
ds@1952	274	** close cursors
ds@1952	275	** foreach index on <table>
ds@1952	276	** open a write cursor on the <table> index
ds@1952	277	** open a read cursor on the corresponding <table2> index
ds@1952	278	** transfer all records from the read to the write cursors
ds@1952	279	** close cursors
ds@1952	280	** end foreach
ds@1952	281	**
ds@1952	282	** The third template is for when the second template does not apply
ds@1952	283	** and the SELECT clause does not read from <table> at any time.
ds@1952	284	** The generated code follows this template:
ds@1952	285	**
ds@1952	286	** goto B
ds@1952	287	** A: setup for the SELECT
ds@1952	288	** loop over the rows in the SELECT
ds@1952	289	** gosub C
ds@1952	290	** end loop
ds@1952	291	** cleanup after the SELECT
ds@1952	292	** goto D
ds@1952	293	** B: open write cursor to <table> and its indices
ds@1952	294	** goto A
ds@1952	295	** C: insert the select result into <table>
ds@1952	296	** return
ds@1952	297	** D: cleanup
ds@1952	298	**
ds@1952	299	** The fourth template is used if the insert statement takes its
ds@1952	300	** values from a SELECT but the data is being inserted into a table
ds@1952	301	** that is also read as part of the SELECT. In the third form,
ds@1952	302	** we have to use a intermediate table to store the results of
ds@1952	303	** the select. The template is like this:
ds@1952	304	**
ds@1952	305	** goto B
ds@1952	306	** A: setup for the SELECT
ds@1952	307	** loop over the tables in the SELECT
ds@1952	308	** gosub C
ds@1952	309	** end loop
ds@1952	310	** cleanup after the SELECT
ds@1952	311	** goto D
ds@1952	312	** C: insert the select result into the intermediate table
ds@1952	313	** return
ds@1952	314	** B: open a cursor to an intermediate table
ds@1952	315	** goto A
ds@1952	316	** D: open write cursor to <table> and its indices
ds@1952	317	** loop over the intermediate table
ds@1952	318	** transfer values form intermediate table into <table>
ds@1952	319	** end the loop
ds@1952	320	** cleanup
ds@1952	321	*/
ds@1952	322	void sqlite3Insert(
ds@1952	323	Parse pParse, / Parser context */
ds@1952	324	SrcList pTabList, / Name of table into which we are inserting */
ds@1952	325	ExprList pList, / List of values to be inserted */
ds@1952	326	Select pSelect, / A SELECT statement to use as the data source */
ds@1952	327	IdList pColumn, / Column names corresponding to IDLIST. */
ds@1952	328	int onError /* How to handle constraint errors */
ds@1952	329	){
ds@1952	330	Table pTab; / The table to insert into */
ds@1952	331	char zTab; / Name of the table into which we are inserting */
ds@1952	332	const char zDb; / Name of the database holding this table */
ds@1952	333	int i, j, idx; /* Loop counters */
ds@1952	334	Vdbe v; / Generate code into this virtual machine */
ds@1952	335	Index pIdx; / For looping over indices of the table */
ds@1952	336	int nColumn; /* Number of columns in the data */
ds@1952	337	int base = 0; /* VDBE Cursor number for pTab */
ds@1952	338	int iCont=0,iBreak=0; /* Beginning and end of the loop over srcTab */
ds@1952	339	sqlite3 db; / The main database structure */
ds@1952	340	int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
ds@1952	341	int endOfLoop; /* Label for the end of the insertion loop */
ds@1952	342	int useTempTable = 0; /* Store SELECT results in intermediate table */
ds@1952	343	int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
ds@1952	344	int iSelectLoop = 0; /* Address of code that implements the SELECT */
ds@1952	345	int iCleanup = 0; /* Address of the cleanup code */
ds@1952	346	int iInsertBlock = 0; /* Address of the subroutine used to insert data */
ds@1952	347	int iCntMem = 0; /* Memory cell used for the row counter */
ds@1952	348	int newIdx = -1; /* Cursor for the NEW table */
ds@1952	349	Db pDb; / The database containing table being inserted into */
ds@1952	350	int counterMem = 0; /* Memory cell holding AUTOINCREMENT counter */
ds@1952	351	int appendFlag = 0; /* True if the insert is likely to be an append */
ds@1952	352	int iDb;
ds@1952	353
ds@1952	354	int nHidden = 0;
ds@1952	355
ds@1952	356	#ifndef SQLITE_OMIT_TRIGGER
ds@1952	357	int isView; /* True if attempting to insert into a view */
ds@1952	358	int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
ds@1952	359	#endif
ds@1952	360
ds@1952	361	db = pParse->db;
ds@1952	362	if( pParse->nErr \|\| db->mallocFailed ){
ds@1952	363	goto insert_cleanup;
ds@1952	364	}
ds@1952	365
ds@1952	366	/* Locate the table into which we will be inserting new information.
ds@1952	367	*/
ds@1952	368	assert( pTabList->nSrc==1 );
ds@1952	369	zTab = pTabList->a[0].zName;
ds@1952	370	if( zTab==0 ) goto insert_cleanup;
ds@1952	371	pTab = sqlite3SrcListLookup(pParse, pTabList);
ds@1952	372	if( pTab==0 ){
ds@1952	373	goto insert_cleanup;
ds@1952	374	}
ds@1952	375	iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
ds@1952	376	assert( iDb<db->nDb );
ds@1952	377	pDb = &db->aDb[iDb];
ds@1952	378	zDb = pDb->zName;
ds@1952	379	if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
ds@1952	380	goto insert_cleanup;
ds@1952	381	}
ds@1952	382
ds@1952	383	/* Figure out if we have any triggers and if the table being
ds@1952	384	** inserted into is a view
ds@1952	385	*/
ds@1952	386	#ifndef SQLITE_OMIT_TRIGGER
ds@1952	387	triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0);
ds@1952	388	isView = pTab->pSelect!=0;
ds@1952	389	#else
ds@1952	390	# define triggers_exist 0
ds@1952	391	# define isView 0
ds@1952	392	#endif
ds@1952	393	#ifdef SQLITE_OMIT_VIEW
ds@1952	394	# undef isView
ds@1952	395	# define isView 0
ds@1952	396	#endif
ds@1952	397
ds@1952	398	/* Ensure that:
ds@1952	399	* (a) the table is not read-only,
ds@1952	400	* (b) that if it is a view then ON INSERT triggers exist
ds@1952	401	*/
ds@1952	402	if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){
ds@1952	403	goto insert_cleanup;
ds@1952	404	}
ds@1952	405	assert( pTab!=0 );
ds@1952	406
ds@1952	407	/* If pTab is really a view, make sure it has been initialized.
ds@1952	408	** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual
ds@1952	409	** module table).
ds@1952	410	*/
ds@1952	411	if( sqlite3ViewGetColumnNames(pParse, pTab) ){
ds@1952	412	goto insert_cleanup;
ds@1952	413	}
ds@1952	414
ds@1952	415	/* Allocate a VDBE
ds@1952	416	*/
ds@1952	417	v = sqlite3GetVdbe(pParse);
ds@1952	418	if( v==0 ) goto insert_cleanup;
ds@1952	419	if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
ds@1952	420	sqlite3BeginWriteOperation(pParse, pSelect \|\| triggers_exist, iDb);
ds@1952	421
ds@1952	422	/* if there are row triggers, allocate a temp table for new.* references. */
ds@1952	423	if( triggers_exist ){
ds@1952	424	newIdx = pParse->nTab++;
ds@1952	425	}
ds@1952	426
ds@1952	427	#ifndef SQLITE_OMIT_XFER_OPT
ds@1952	428	/* If the statement is of the form
ds@1952	429	**
ds@1952	430	** INSERT INTO <table1> SELECT * FROM <table2>;
ds@1952	431	**
ds@1952	432	** Then special optimizations can be applied that make the transfer
ds@1952	433	** very fast and which reduce fragmentation of indices.
ds@1952	434	*/
ds@1952	435	if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
ds@1952	436	assert( !triggers_exist );
ds@1952	437	assert( pList==0 );
ds@1952	438	goto insert_cleanup;
ds@1952	439	}
ds@1952	440	#endif /* SQLITE_OMIT_XFER_OPT */
ds@1952	441
ds@1952	442	/* If this is an AUTOINCREMENT table, look up the sequence number in the
ds@1952	443	** sqlite_sequence table and store it in memory cell counterMem. Also
ds@1952	444	** remember the rowid of the sqlite_sequence table entry in memory cell
ds@1952	445	** counterRowid.
ds@1952	446	*/
ds@1952	447	counterMem = autoIncBegin(pParse, iDb, pTab);
ds@1952	448
ds@1952	449	/* Figure out how many columns of data are supplied. If the data
ds@1952	450	** is coming from a SELECT statement, then this step also generates
ds@1952	451	** all the code to implement the SELECT statement and invoke a subroutine
ds@1952	452	** to process each row of the result. (Template 2.) If the SELECT
ds@1952	453	** statement uses the the table that is being inserted into, then the
ds@1952	454	** subroutine is also coded here. That subroutine stores the SELECT
ds@1952	455	** results in a temporary table. (Template 3.)
ds@1952	456	*/
ds@1952	457	if( pSelect ){
ds@1952	458	/* Data is coming from a SELECT. Generate code to implement that SELECT
ds@1952	459	*/
ds@1952	460	int rc, iInitCode;
ds@1952	461	iInitCode = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
ds@1952	462	iSelectLoop = sqlite3VdbeCurrentAddr(v);
ds@1952	463	iInsertBlock = sqlite3VdbeMakeLabel(v);
ds@1952	464
ds@1952	465	/* Resolve the expressions in the SELECT statement and execute it. */
ds@1952	466	rc = sqlite3Select(pParse, pSelect, SRT_Subroutine, iInsertBlock,0,0,0,0);
ds@1952	467	if( rc \|\| pParse->nErr \|\| db->mallocFailed ){
ds@1952	468	goto insert_cleanup;
ds@1952	469	}
ds@1952	470
ds@1952	471	iCleanup = sqlite3VdbeMakeLabel(v);
ds@1952	472	sqlite3VdbeAddOp(v, OP_Goto, 0, iCleanup);
ds@1952	473	assert( pSelect->pEList );
ds@1952	474	nColumn = pSelect->pEList->nExpr;
ds@1952	475
ds@1952	476	/* Set useTempTable to TRUE if the result of the SELECT statement
ds@1952	477	** should be written into a temporary table. Set to FALSE if each
ds@1952	478	** row of the SELECT can be written directly into the result table.
ds@1952	479	**
ds@1952	480	** A temp table must be used if the table being updated is also one
ds@1952	481	** of the tables being read by the SELECT statement. Also use a
ds@1952	482	** temp table in the case of row triggers.
ds@1952	483	*/
ds@1952	484	if( triggers_exist \|\| selectReadsTable(pSelect,pTab->pSchema,pTab->tnum) ){
ds@1952	485	useTempTable = 1;
ds@1952	486	}
ds@1952	487
ds@1952	488	if( useTempTable ){
ds@1952	489	/* Generate the subroutine that SELECT calls to process each row of
ds@1952	490	** the result. Store the result in a temporary table
ds@1952	491	*/
ds@1952	492	srcTab = pParse->nTab++;
ds@1952	493	sqlite3VdbeResolveLabel(v, iInsertBlock);
ds@1952	494	sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
ds@1952	495	sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0);
ds@1952	496	sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
ds@1952	497	sqlite3VdbeAddOp(v, OP_Insert, srcTab, OPFLAG_APPEND);
ds@1952	498	sqlite3VdbeAddOp(v, OP_Return, 0, 0);
ds@1952	499
ds@1952	500	/* The following code runs first because the GOTO at the very top
ds@1952	501	** of the program jumps to it. Create the temporary table, then jump
ds@1952	502	** back up and execute the SELECT code above.
ds@1952	503	*/
ds@1952	504	sqlite3VdbeJumpHere(v, iInitCode);
ds@1952	505	sqlite3VdbeAddOp(v, OP_OpenEphemeral, srcTab, 0);
ds@1952	506	sqlite3VdbeAddOp(v, OP_SetNumColumns, srcTab, nColumn);
ds@1952	507	sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);
ds@1952	508	sqlite3VdbeResolveLabel(v, iCleanup);
ds@1952	509	}else{
ds@1952	510	sqlite3VdbeJumpHere(v, iInitCode);
ds@1952	511	}
ds@1952	512	}else{
ds@1952	513	/* This is the case if the data for the INSERT is coming from a VALUES
ds@1952	514	** clause
ds@1952	515	*/
ds@1952	516	NameContext sNC;
ds@1952	517	memset(&sNC, 0, sizeof(sNC));
ds@1952	518	sNC.pParse = pParse;
ds@1952	519	srcTab = -1;
ds@1952	520	useTempTable = 0;
ds@1952	521	nColumn = pList ? pList->nExpr : 0;
ds@1952	522	for(i=0; i<nColumn; i++){
ds@1952	523	if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){
ds@1952	524	goto insert_cleanup;
ds@1952	525	}
ds@1952	526	}
ds@1952	527	}
ds@1952	528
ds@1952	529	/* Make sure the number of columns in the source data matches the number
ds@1952	530	** of columns to be inserted into the table.
ds@1952	531	*/
ds@1952	532	if( IsVirtual(pTab) ){
ds@1952	533	for(i=0; i<pTab->nCol; i++){
ds@1952	534	nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
ds@1952	535	}
ds@1952	536	}
ds@1952	537	if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
ds@1952	538	sqlite3ErrorMsg(pParse,
ds@1952	539	"table %S has %d columns but %d values were supplied",
ds@1952	540	pTabList, 0, pTab->nCol, nColumn);
ds@1952	541	goto insert_cleanup;
ds@1952	542	}
ds@1952	543	if( pColumn!=0 && nColumn!=pColumn->nId ){
ds@1952	544	sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
ds@1952	545	goto insert_cleanup;
ds@1952	546	}
ds@1952	547
ds@1952	548	/* If the INSERT statement included an IDLIST term, then make sure
ds@1952	549	** all elements of the IDLIST really are columns of the table and
ds@1952	550	** remember the column indices.
ds@1952	551	**
ds@1952	552	** If the table has an INTEGER PRIMARY KEY column and that column
ds@1952	553	** is named in the IDLIST, then record in the keyColumn variable
ds@1952	554	** the index into IDLIST of the primary key column. keyColumn is
ds@1952	555	** the index of the primary key as it appears in IDLIST, not as
ds@1952	556	** is appears in the original table. (The index of the primary
ds@1952	557	** key in the original table is pTab->iPKey.)
ds@1952	558	*/
ds@1952	559	if( pColumn ){
ds@1952	560	for(i=0; i<pColumn->nId; i++){
ds@1952	561	pColumn->a[i].idx = -1;
ds@1952	562	}
ds@1952	563	for(i=0; i<pColumn->nId; i++){
ds@1952	564	for(j=0; j<pTab->nCol; j++){
ds@1952	565	if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
ds@1952	566	pColumn->a[i].idx = j;
ds@1952	567	if( j==pTab->iPKey ){
ds@1952	568	keyColumn = i;
ds@1952	569	}
ds@1952	570	break;
ds@1952	571	}
ds@1952	572	}
ds@1952	573	if( j>=pTab->nCol ){
ds@1952	574	if( sqlite3IsRowid(pColumn->a[i].zName) ){
ds@1952	575	keyColumn = i;
ds@1952	576	}else{
ds@1952	577	sqlite3ErrorMsg(pParse, "table %S has no column named %s",
ds@1952	578	pTabList, 0, pColumn->a[i].zName);
ds@1952	579	pParse->nErr++;
ds@1952	580	goto insert_cleanup;
ds@1952	581	}
ds@1952	582	}
ds@1952	583	}
ds@1952	584	}
ds@1952	585
ds@1952	586	/* If there is no IDLIST term but the table has an integer primary
ds@1952	587	** key, the set the keyColumn variable to the primary key column index
ds@1952	588	** in the original table definition.
ds@1952	589	*/
ds@1952	590	if( pColumn==0 && nColumn>0 ){
ds@1952	591	keyColumn = pTab->iPKey;
ds@1952	592	}
ds@1952	593
ds@1952	594	/* Open the temp table for FOR EACH ROW triggers
ds@1952	595	*/
ds@1952	596	if( triggers_exist ){
ds@1952	597	sqlite3VdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
ds@1952	598	sqlite3VdbeAddOp(v, OP_SetNumColumns, newIdx, pTab->nCol);
ds@1952	599	}
ds@1952	600
ds@1952	601	/* Initialize the count of rows to be inserted
ds@1952	602	*/
ds@1952	603	if( db->flags & SQLITE_CountRows ){
ds@1952	604	iCntMem = pParse->nMem++;
ds@1952	605	sqlite3VdbeAddOp(v, OP_MemInt, 0, iCntMem);
ds@1952	606	}
ds@1952	607
ds@1952	608	/* Open tables and indices if there are no row triggers */
ds@1952	609	if( !triggers_exist ){
ds@1952	610	base = pParse->nTab;
ds@1952	611	sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite);
ds@1952	612	}
ds@1952	613
ds@1952	614	/* If the data source is a temporary table, then we have to create
ds@1952	615	** a loop because there might be multiple rows of data. If the data
ds@1952	616	** source is a subroutine call from the SELECT statement, then we need
ds@1952	617	** to launch the SELECT statement processing.
ds@1952	618	*/
ds@1952	619	if( useTempTable ){
ds@1952	620	iBreak = sqlite3VdbeMakeLabel(v);
ds@1952	621	sqlite3VdbeAddOp(v, OP_Rewind, srcTab, iBreak);
ds@1952	622	iCont = sqlite3VdbeCurrentAddr(v);
ds@1952	623	}else if( pSelect ){
ds@1952	624	sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);
ds@1952	625	sqlite3VdbeResolveLabel(v, iInsertBlock);
ds@1952	626	}
ds@1952	627
ds@1952	628	/* Run the BEFORE and INSTEAD OF triggers, if there are any
ds@1952	629	*/
ds@1952	630	endOfLoop = sqlite3VdbeMakeLabel(v);
ds@1952	631	if( triggers_exist & TRIGGER_BEFORE ){
ds@1952	632
ds@1952	633	/* build the NEW.* reference row. Note that if there is an INTEGER
ds@1952	634	** PRIMARY KEY into which a NULL is being inserted, that NULL will be
ds@1952	635	** translated into a unique ID for the row. But on a BEFORE trigger,
ds@1952	636	** we do not know what the unique ID will be (because the insert has
ds@1952	637	** not happened yet) so we substitute a rowid of -1
ds@1952	638	*/
ds@1952	639	if( keyColumn<0 ){
ds@1952	640	sqlite3VdbeAddOp(v, OP_Integer, -1, 0);
ds@1952	641	}else if( useTempTable ){
ds@1952	642	sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn);
ds@1952	643	}else{
ds@1952	644	assert( pSelect==0 ); /* Otherwise useTempTable is true */
ds@1952	645	sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr);
ds@1952	646	sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
ds@1952	647	sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
ds@1952	648	sqlite3VdbeAddOp(v, OP_Integer, -1, 0);
ds@1952	649	sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
ds@1952	650	}
ds@1952	651
ds@1952	652	/* Cannot have triggers on a virtual table. If it were possible,
ds@1952	653	** this block would have to account for hidden column.
ds@1952	654	*/
ds@1952	655	assert(!IsVirtual(pTab));
ds@1952	656
ds@1952	657	/* Create the new column data
ds@1952	658	*/
ds@1952	659	for(i=0; i<pTab->nCol; i++){
ds@1952	660	if( pColumn==0 ){
ds@1952	661	j = i;
ds@1952	662	}else{
ds@1952	663	for(j=0; j<pColumn->nId; j++){
ds@1952	664	if( pColumn->a[j].idx==i ) break;
ds@1952	665	}
ds@1952	666	}
ds@1952	667	if( pColumn && j>=pColumn->nId ){
ds@1952	668	sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
ds@1952	669	}else if( useTempTable ){
ds@1952	670	sqlite3VdbeAddOp(v, OP_Column, srcTab, j);
ds@1952	671	}else{
ds@1952	672	assert( pSelect==0 ); /* Otherwise useTempTable is true */
ds@1952	673	sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr);
ds@1952	674	}
ds@1952	675	}
ds@1952	676	sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
ds@1952	677
ds@1952	678	/* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
ds@1952	679	** do not attempt any conversions before assembling the record.
ds@1952	680	** If this is a real table, attempt conversions as required by the
ds@1952	681	** table column affinities.
ds@1952	682	*/
ds@1952	683	if( !isView ){
ds@1952	684	sqlite3TableAffinityStr(v, pTab);
ds@1952	685	}
ds@1952	686	sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0);
ds@1952	687
ds@1952	688	/* Fire BEFORE or INSTEAD OF triggers */
ds@1952	689	if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_BEFORE, pTab,
ds@1952	690	newIdx, -1, onError, endOfLoop) ){
ds@1952	691	goto insert_cleanup;
ds@1952	692	}
ds@1952	693	}
ds@1952	694
ds@1952	695	/* If any triggers exists, the opening of tables and indices is deferred
ds@1952	696	** until now.
ds@1952	697	*/
ds@1952	698	if( triggers_exist && !isView ){
ds@1952	699	base = pParse->nTab;
ds@1952	700	sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite);
ds@1952	701	}
ds@1952	702
ds@1952	703	/* Push the record number for the new entry onto the stack. The
ds@1952	704	** record number is a randomly generate integer created by NewRowid
ds@1952	705	** except when the table has an INTEGER PRIMARY KEY column, in which
ds@1952	706	** case the record number is the same as that column.
ds@1952	707	*/
ds@1952	708	if( !isView ){
ds@1952	709	if( IsVirtual(pTab) ){
ds@1952	710	/* The row that the VUpdate opcode will delete: none */
ds@1952	711	sqlite3VdbeAddOp(v, OP_Null, 0, 0);
ds@1952	712	}
ds@1952	713	if( keyColumn>=0 ){
ds@1952	714	if( useTempTable ){
ds@1952	715	sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn);
ds@1952	716	}else if( pSelect ){
ds@1952	717	sqlite3VdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
ds@1952	718	}else{
ds@1952	719	VdbeOp *pOp;
ds@1952	720	sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr);
ds@1952	721	pOp = sqlite3VdbeGetOp(v, sqlite3VdbeCurrentAddr(v) - 1);
ds@1952	722	if( pOp && pOp->opcode==OP_Null ){
ds@1952	723	appendFlag = 1;
ds@1952	724	pOp->opcode = OP_NewRowid;
ds@1952	725	pOp->p1 = base;
ds@1952	726	pOp->p2 = counterMem;
ds@1952	727	}
ds@1952	728	}
ds@1952	729	/* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
ds@1952	730	** to generate a unique primary key value.
ds@1952	731	*/
ds@1952	732	if( !appendFlag ){
ds@1952	733	sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
ds@1952	734	sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
ds@1952	735	sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
ds@1952	736	sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
ds@1952	737	}
ds@1952	738	}else if( IsVirtual(pTab) ){
ds@1952	739	sqlite3VdbeAddOp(v, OP_Null, 0, 0);
ds@1952	740	}else{
ds@1952	741	sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
ds@1952	742	appendFlag = 1;
ds@1952	743	}
ds@1952	744	autoIncStep(pParse, counterMem);
ds@1952	745
ds@1952	746	/* Push onto the stack, data for all columns of the new entry, beginning
ds@1952	747	** with the first column.
ds@1952	748	*/
ds@1952	749	nHidden = 0;
ds@1952	750	for(i=0; i<pTab->nCol; i++){
ds@1952	751	if( i==pTab->iPKey ){
ds@1952	752	/* The value of the INTEGER PRIMARY KEY column is always a NULL.
ds@1952	753	** Whenever this column is read, the record number will be substituted
ds@1952	754	** in its place. So will fill this column with a NULL to avoid
ds@1952	755	** taking up data space with information that will never be used. */
ds@1952	756	sqlite3VdbeAddOp(v, OP_Null, 0, 0);
ds@1952	757	continue;
ds@1952	758	}
ds@1952	759	if( pColumn==0 ){
ds@1952	760	if( IsHiddenColumn(&pTab->aCol[i]) ){
ds@1952	761	assert( IsVirtual(pTab) );
ds@1952	762	j = -1;
ds@1952	763	nHidden++;
ds@1952	764	}else{
ds@1952	765	j = i - nHidden;
ds@1952	766	}
ds@1952	767	}else{
ds@1952	768	for(j=0; j<pColumn->nId; j++){
ds@1952	769	if( pColumn->a[j].idx==i ) break;
ds@1952	770	}
ds@1952	771	}
ds@1952	772	if( j<0 \|\| nColumn==0 \|\| (pColumn && j>=pColumn->nId) ){
ds@1952	773	sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
ds@1952	774	}else if( useTempTable ){
ds@1952	775	sqlite3VdbeAddOp(v, OP_Column, srcTab, j);
ds@1952	776	}else if( pSelect ){
ds@1952	777	sqlite3VdbeAddOp(v, OP_Dup, i+nColumn-j+IsVirtual(pTab), 1);
ds@1952	778	}else{
ds@1952	779	sqlite3ExprCode(pParse, pList->a[j].pExpr);
ds@1952	780	}
ds@1952	781	}
ds@1952	782
ds@1952	783	/* Generate code to check constraints and generate index keys and
ds@1952	784	** do the insertion.
ds@1952	785	*/
ds@1952	786	#ifndef SQLITE_OMIT_VIRTUALTABLE
ds@1952	787	if( IsVirtual(pTab) ){
ds@1952	788	pParse->pVirtualLock = pTab;
ds@1952	789	sqlite3VdbeOp3(v, OP_VUpdate, 1, pTab->nCol+2,
ds@1952	790	(const char*)pTab->pVtab, P3_VTAB);
ds@1952	791	}else
ds@1952	792	#endif
ds@1952	793	{
ds@1952	794	sqlite3GenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
ds@1952	795	0, onError, endOfLoop);
ds@1952	796	sqlite3CompleteInsertion(pParse, pTab, base, 0,0,0,
ds@1952	797	(triggers_exist & TRIGGER_AFTER)!=0 ? newIdx : -1,
ds@1952	798	appendFlag);
ds@1952	799	}
ds@1952	800	}
ds@1952	801
ds@1952	802	/* Update the count of rows that are inserted
ds@1952	803	*/
ds@1952	804	if( (db->flags & SQLITE_CountRows)!=0 ){
ds@1952	805	sqlite3VdbeAddOp(v, OP_MemIncr, 1, iCntMem);
ds@1952	806	}
ds@1952	807
ds@1952	808	if( triggers_exist ){
ds@1952	809	/* Close all tables opened */
ds@1952	810	if( !isView ){
ds@1952	811	sqlite3VdbeAddOp(v, OP_Close, base, 0);
ds@1952	812	for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
ds@1952	813	sqlite3VdbeAddOp(v, OP_Close, idx+base, 0);
ds@1952	814	}
ds@1952	815	}
ds@1952	816
ds@1952	817	/* Code AFTER triggers */
ds@1952	818	if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_AFTER, pTab,
ds@1952	819	newIdx, -1, onError, endOfLoop) ){
ds@1952	820	goto insert_cleanup;
ds@1952	821	}
ds@1952	822	}
ds@1952	823
ds@1952	824	/* The bottom of the loop, if the data source is a SELECT statement
ds@1952	825	*/
ds@1952	826	sqlite3VdbeResolveLabel(v, endOfLoop);
ds@1952	827	if( useTempTable ){
ds@1952	828	sqlite3VdbeAddOp(v, OP_Next, srcTab, iCont);
ds@1952	829	sqlite3VdbeResolveLabel(v, iBreak);
ds@1952	830	sqlite3VdbeAddOp(v, OP_Close, srcTab, 0);
ds@1952	831	}else if( pSelect ){
ds@1952	832	sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
ds@1952	833	sqlite3VdbeAddOp(v, OP_Return, 0, 0);
ds@1952	834	sqlite3VdbeResolveLabel(v, iCleanup);
ds@1952	835	}
ds@1952	836
ds@1952	837	if( !triggers_exist && !IsVirtual(pTab) ){
ds@1952	838	/* Close all tables opened */
ds@1952	839	sqlite3VdbeAddOp(v, OP_Close, base, 0);
ds@1952	840	for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
ds@1952	841	sqlite3VdbeAddOp(v, OP_Close, idx+base, 0);
ds@1952	842	}
ds@1952	843	}
ds@1952	844
ds@1952	845	/* Update the sqlite_sequence table by storing the content of the
ds@1952	846	** counter value in memory counterMem back into the sqlite_sequence
ds@1952	847	** table.
ds@1952	848	*/
ds@1952	849	autoIncEnd(pParse, iDb, pTab, counterMem);
ds@1952	850
ds@1952	851	/*
ds@1952	852	** Return the number of rows inserted. If this routine is
ds@1952	853	** generating code because of a call to sqlite3NestedParse(), do not
ds@1952	854	** invoke the callback function.
ds@1952	855	*/
ds@1952	856	if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
ds@1952	857	sqlite3VdbeAddOp(v, OP_MemLoad, iCntMem, 0);
ds@1952	858	sqlite3VdbeAddOp(v, OP_Callback, 1, 0);
ds@1952	859	sqlite3VdbeSetNumCols(v, 1);
ds@1952	860	sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", P3_STATIC);
ds@1952	861	}
ds@1952	862
ds@1952	863	insert_cleanup:
ds@1952	864	sqlite3SrcListDelete(pTabList);
ds@1952	865	sqlite3ExprListDelete(pList);
ds@1952	866	sqlite3SelectDelete(pSelect);
ds@1952	867	sqlite3IdListDelete(pColumn);
ds@1952	868	}
ds@1952	869
ds@1952	870	/*
ds@1952	871	** Generate code to do a constraint check prior to an INSERT or an UPDATE.
ds@1952	872	**
ds@1952	873	** When this routine is called, the stack contains (from bottom to top)
ds@1952	874	** the following values:
ds@1952	875	**
ds@1952	876	** 1. The rowid of the row to be updated before the update. This
ds@1952	877	** value is omitted unless we are doing an UPDATE that involves a
ds@1952	878	** change to the record number.
ds@1952	879	**
ds@1952	880	** 2. The rowid of the row after the update.
ds@1952	881	**
ds@1952	882	** 3. The data in the first column of the entry after the update.
ds@1952	883	**
ds@1952	884	** i. Data from middle columns...
ds@1952	885	**
ds@1952	886	** N. The data in the last column of the entry after the update.
ds@1952	887	**
ds@1952	888	** The old rowid shown as entry (1) above is omitted unless both isUpdate
ds@1952	889	** and rowidChng are 1. isUpdate is true for UPDATEs and false for
ds@1952	890	** INSERTs and rowidChng is true if the record number is being changed.
ds@1952	891	**
ds@1952	892	** The code generated by this routine pushes additional entries onto
ds@1952	893	** the stack which are the keys for new index entries for the new record.
ds@1952	894	** The order of index keys is the same as the order of the indices on
ds@1952	895	** the pTable->pIndex list. A key is only created for index i if
ds@1952	896	** aIdxUsed!=0 and aIdxUsed[i]!=0.
ds@1952	897	**
ds@1952	898	** This routine also generates code to check constraints. NOT NULL,
ds@1952	899	** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
ds@1952	900	** then the appropriate action is performed. There are five possible
ds@1952	901	** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
ds@1952	902	**
ds@1952	903	** Constraint type Action What Happens
ds@1952	904	** --------------- ---------- ----------------------------------------
ds@1952	905	** any ROLLBACK The current transaction is rolled back and
ds@1952	906	** sqlite3_exec() returns immediately with a
ds@1952	907	** return code of SQLITE_CONSTRAINT.
ds@1952	908	**
ds@1952	909	** any ABORT Back out changes from the current command
ds@1952	910	** only (do not do a complete rollback) then
ds@1952	911	** cause sqlite3_exec() to return immediately
ds@1952	912	** with SQLITE_CONSTRAINT.
ds@1952	913	**
ds@1952	914	** any FAIL Sqlite_exec() returns immediately with a
ds@1952	915	** return code of SQLITE_CONSTRAINT. The
ds@1952	916	** transaction is not rolled back and any
ds@1952	917	** prior changes are retained.
ds@1952	918	**
ds@1952	919	** any IGNORE The record number and data is popped from
ds@1952	920	** the stack and there is an immediate jump
ds@1952	921	** to label ignoreDest.
ds@1952	922	**
ds@1952	923	** NOT NULL REPLACE The NULL value is replace by the default
ds@1952	924	** value for that column. If the default value
ds@1952	925	** is NULL, the action is the same as ABORT.
ds@1952	926	**
ds@1952	927	** UNIQUE REPLACE The other row that conflicts with the row
ds@1952	928	** being inserted is removed.
ds@1952	929	**
ds@1952	930	** CHECK REPLACE Illegal. The results in an exception.
ds@1952	931	**
ds@1952	932	** Which action to take is determined by the overrideError parameter.
ds@1952	933	** Or if overrideError==OE_Default, then the pParse->onError parameter
ds@1952	934	** is used. Or if pParse->onError==OE_Default then the onError value
ds@1952	935	** for the constraint is used.
ds@1952	936	**
ds@1952	937	** The calling routine must open a read/write cursor for pTab with
ds@1952	938	** cursor number "base". All indices of pTab must also have open
ds@1952	939	** read/write cursors with cursor number base+i for the i-th cursor.
ds@1952	940	** Except, if there is no possibility of a REPLACE action then
ds@1952	941	** cursors do not need to be open for indices where aIdxUsed[i]==0.
ds@1952	942	**
ds@1952	943	** If the isUpdate flag is true, it means that the "base" cursor is
ds@1952	944	** initially pointing to an entry that is being updated. The isUpdate
ds@1952	945	** flag causes extra code to be generated so that the "base" cursor
ds@1952	946	** is still pointing at the same entry after the routine returns.
ds@1952	947	** Without the isUpdate flag, the "base" cursor might be moved.
ds@1952	948	*/
ds@1952	949	void sqlite3GenerateConstraintChecks(
ds@1952	950	Parse pParse, / The parser context */
ds@1952	951	Table pTab, / the table into which we are inserting */
ds@1952	952	int base, /* Index of a read/write cursor pointing at pTab */
ds@1952	953	char aIdxUsed, / Which indices are used. NULL means all are used */
ds@1952	954	int rowidChng, /* True if the record number will change */
ds@1952	955	int isUpdate, /* True for UPDATE, False for INSERT */
ds@1952	956	int overrideError, /* Override onError to this if not OE_Default */
ds@1952	957	int ignoreDest /* Jump to this label on an OE_Ignore resolution */
ds@1952	958	){
ds@1952	959	int i;
ds@1952	960	Vdbe *v;
ds@1952	961	int nCol;
ds@1952	962	int onError;
ds@1952	963	int addr;
ds@1952	964	int extra;
ds@1952	965	int iCur;
ds@1952	966	Index *pIdx;
ds@1952	967	int seenReplace = 0;
ds@1952	968	int jumpInst1=0, jumpInst2;
ds@1952	969	int hasTwoRowids = (isUpdate && rowidChng);
ds@1952	970
ds@1952	971	v = sqlite3GetVdbe(pParse);
ds@1952	972	assert( v!=0 );
ds@1952	973	assert( pTab->pSelect==0 ); /* This table is not a VIEW */
ds@1952	974	nCol = pTab->nCol;
ds@1952	975
ds@1952	976	/* Test all NOT NULL constraints.
ds@1952	977	*/
ds@1952	978	for(i=0; i<nCol; i++){
ds@1952	979	if( i==pTab->iPKey ){
ds@1952	980	continue;
ds@1952	981	}
ds@1952	982	onError = pTab->aCol[i].notNull;
ds@1952	983	if( onError==OE_None ) continue;
ds@1952	984	if( overrideError!=OE_Default ){
ds@1952	985	onError = overrideError;
ds@1952	986	}else if( onError==OE_Default ){
ds@1952	987	onError = OE_Abort;
ds@1952	988	}
ds@1952	989	if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
ds@1952	990	onError = OE_Abort;
ds@1952	991	}
ds@1952	992	sqlite3VdbeAddOp(v, OP_Dup, nCol-1-i, 1);
ds@1952	993	addr = sqlite3VdbeAddOp(v, OP_NotNull, 1, 0);
ds@1952	994	assert( onError==OE_Rollback \|\| onError==OE_Abort \|\| onError==OE_Fail
ds@1952	995	\|\| onError==OE_Ignore \|\| onError==OE_Replace );
ds@1952	996	switch( onError ){
ds@1952	997	case OE_Rollback:
ds@1952	998	case OE_Abort:
ds@1952	999	case OE_Fail: {
ds@1952	1000	char *zMsg = 0;
ds@1952	1001	sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
ds@1952	1002	sqlite3SetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
ds@1952	1003	" may not be NULL", (char*)0);
ds@1952	1004	sqlite3VdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
ds@1952	1005	break;
ds@1952	1006	}
ds@1952	1007	case OE_Ignore: {
ds@1952	1008	sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
ds@1952	1009	sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
ds@1952	1010	break;
ds@1952	1011	}
ds@1952	1012	case OE_Replace: {
ds@1952	1013	sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
ds@1952	1014	sqlite3VdbeAddOp(v, OP_Push, nCol-i, 0);
ds@1952	1015	break;
ds@1952	1016	}
ds@1952	1017	}
ds@1952	1018	sqlite3VdbeJumpHere(v, addr);
ds@1952	1019	}
ds@1952	1020
ds@1952	1021	/* Test all CHECK constraints
ds@1952	1022	*/
ds@1952	1023	#ifndef SQLITE_OMIT_CHECK
ds@1952	1024	if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){
ds@1952	1025	int allOk = sqlite3VdbeMakeLabel(v);
ds@1952	1026	assert( pParse->ckOffset==0 );
ds@1952	1027	pParse->ckOffset = nCol;
ds@1952	1028	sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1);
ds@1952	1029	assert( pParse->ckOffset==nCol );
ds@1952	1030	pParse->ckOffset = 0;
ds@1952	1031	onError = overrideError!=OE_Default ? overrideError : OE_Abort;
ds@1952	1032	if( onError==OE_Ignore ){
ds@1952	1033	sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
ds@1952	1034	sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
ds@1952	1035	}else{
ds@1952	1036	sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
ds@1952	1037	}
ds@1952	1038	sqlite3VdbeResolveLabel(v, allOk);
ds@1952	1039	}
ds@1952	1040	#endif /* !defined(SQLITE_OMIT_CHECK) */
ds@1952	1041
ds@1952	1042	/* If we have an INTEGER PRIMARY KEY, make sure the primary key
ds@1952	1043	** of the new record does not previously exist. Except, if this
ds@1952	1044	** is an UPDATE and the primary key is not changing, that is OK.
ds@1952	1045	*/
ds@1952	1046	if( rowidChng ){
ds@1952	1047	onError = pTab->keyConf;
ds@1952	1048	if( overrideError!=OE_Default ){
ds@1952	1049	onError = overrideError;
ds@1952	1050	}else if( onError==OE_Default ){
ds@1952	1051	onError = OE_Abort;
ds@1952	1052	}
ds@1952	1053
ds@1952	1054	if( isUpdate ){
ds@1952	1055	sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
ds@1952	1056	sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
ds@1952	1057	jumpInst1 = sqlite3VdbeAddOp(v, OP_Eq, 0, 0);
ds@1952	1058	}
ds@1952	1059	sqlite3VdbeAddOp(v, OP_Dup, nCol, 1);
ds@1952	1060	jumpInst2 = sqlite3VdbeAddOp(v, OP_NotExists, base, 0);
ds@1952	1061	switch( onError ){
ds@1952	1062	default: {
ds@1952	1063	onError = OE_Abort;
ds@1952	1064	/* Fall thru into the next case */
ds@1952	1065	}
ds@1952	1066	case OE_Rollback:
ds@1952	1067	case OE_Abort:
ds@1952	1068	case OE_Fail: {
ds@1952	1069	sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
ds@1952	1070	"PRIMARY KEY must be unique", P3_STATIC);
ds@1952	1071	break;
ds@1952	1072	}
ds@1952	1073	case OE_Replace: {
ds@1952	1074	sqlite3GenerateRowIndexDelete(v, pTab, base, 0);
ds@1952	1075	if( isUpdate ){
ds@1952	1076	sqlite3VdbeAddOp(v, OP_Dup, nCol+hasTwoRowids, 1);
ds@1952	1077	sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
ds@1952	1078	}
ds@1952	1079	seenReplace = 1;
ds@1952	1080	break;
ds@1952	1081	}
ds@1952	1082	case OE_Ignore: {
ds@1952	1083	assert( seenReplace==0 );
ds@1952	1084	sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
ds@1952	1085	sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
ds@1952	1086	break;
ds@1952	1087	}
ds@1952	1088	}
ds@1952	1089	sqlite3VdbeJumpHere(v, jumpInst2);
ds@1952	1090	if( isUpdate ){
ds@1952	1091	sqlite3VdbeJumpHere(v, jumpInst1);
ds@1952	1092	sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
ds@1952	1093	sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
ds@1952	1094	}
ds@1952	1095	}
ds@1952	1096
ds@1952	1097	/* Test all UNIQUE constraints by creating entries for each UNIQUE
ds@1952	1098	** index and making sure that duplicate entries do not already exist.
ds@1952	1099	** Add the new records to the indices as we go.
ds@1952	1100	*/
ds@1952	1101	extra = -1;
ds@1952	1102	for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
ds@1952	1103	if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */
ds@1952	1104	extra++;
ds@1952	1105
ds@1952	1106	/* Create a key for accessing the index entry */
ds@1952	1107	sqlite3VdbeAddOp(v, OP_Dup, nCol+extra, 1);
ds@1952	1108	for(i=0; i<pIdx->nColumn; i++){
ds@1952	1109	int idx = pIdx->aiColumn[i];
ds@1952	1110	if( idx==pTab->iPKey ){
ds@1952	1111	sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
ds@1952	1112	}else{
ds@1952	1113	sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
ds@1952	1114	}
ds@1952	1115	}
ds@1952	1116	jumpInst1 = sqlite3VdbeAddOp(v, OP_MakeIdxRec, pIdx->nColumn, 0);
ds@1952	1117	sqlite3IndexAffinityStr(v, pIdx);
ds@1952	1118
ds@1952	1119	/* Find out what action to take in case there is an indexing conflict */
ds@1952	1120	onError = pIdx->onError;
ds@1952	1121	if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */
ds@1952	1122	if( overrideError!=OE_Default ){
ds@1952	1123	onError = overrideError;
ds@1952	1124	}else if( onError==OE_Default ){
ds@1952	1125	onError = OE_Abort;
ds@1952	1126	}
ds@1952	1127	if( seenReplace ){
ds@1952	1128	if( onError==OE_Ignore ) onError = OE_Replace;
ds@1952	1129	else if( onError==OE_Fail ) onError = OE_Abort;
ds@1952	1130	}
ds@1952	1131
ds@1952	1132
ds@1952	1133	/* Check to see if the new index entry will be unique */
ds@1952	1134	sqlite3VdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRowids, 1);
ds@1952	1135	jumpInst2 = sqlite3VdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
ds@1952	1136
ds@1952	1137	/* Generate code that executes if the new index entry is not unique */
ds@1952	1138	assert( onError==OE_Rollback \|\| onError==OE_Abort \|\| onError==OE_Fail
ds@1952	1139	\|\| onError==OE_Ignore \|\| onError==OE_Replace );
ds@1952	1140	switch( onError ){
ds@1952	1141	case OE_Rollback:
ds@1952	1142	case OE_Abort:
ds@1952	1143	case OE_Fail: {
ds@1952	1144	int j, n1, n2;
ds@1952	1145	char zErrMsg[200];
ds@1952	1146	sqlite3_snprintf(sizeof(zErrMsg), zErrMsg,
ds@1952	1147	pIdx->nColumn>1 ? "columns " : "column ");
ds@1952	1148	n1 = strlen(zErrMsg);
ds@1952	1149	for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
ds@1952	1150	char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
ds@1952	1151	n2 = strlen(zCol);
ds@1952	1152	if( j>0 ){
ds@1952	1153	sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], ", ");
ds@1952	1154	n1 += 2;
ds@1952	1155	}
ds@1952	1156	if( n1+n2>sizeof(zErrMsg)-30 ){
ds@1952	1157	sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "...");
ds@1952	1158	n1 += 3;
ds@1952	1159	break;
ds@1952	1160	}else{
ds@1952	1161	sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "%s", zCol);
ds@1952	1162	n1 += n2;
ds@1952	1163	}
ds@1952	1164	}
ds@1952	1165	sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1],
ds@1952	1166	pIdx->nColumn>1 ? " are not unique" : " is not unique");
ds@1952	1167	sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
ds@1952	1168	break;
ds@1952	1169	}
ds@1952	1170	case OE_Ignore: {
ds@1952	1171	assert( seenReplace==0 );
ds@1952	1172	sqlite3VdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRowids, 0);
ds@1952	1173	sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
ds@1952	1174	break;
ds@1952	1175	}
ds@1952	1176	case OE_Replace: {
ds@1952	1177	sqlite3GenerateRowDelete(pParse->db, v, pTab, base, 0);
ds@1952	1178	if( isUpdate ){
ds@1952	1179	sqlite3VdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRowids, 1);
ds@1952	1180	sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
ds@1952	1181	}
ds@1952	1182	seenReplace = 1;
ds@1952	1183	break;
ds@1952	1184	}
ds@1952	1185	}
ds@1952	1186	#if NULL_DISTINCT_FOR_UNIQUE
ds@1952	1187	sqlite3VdbeJumpHere(v, jumpInst1);
ds@1952	1188	#endif
ds@1952	1189	sqlite3VdbeJumpHere(v, jumpInst2);
ds@1952	1190	}
ds@1952	1191	}
ds@1952	1192
ds@1952	1193	/*
ds@1952	1194	** This routine generates code to finish the INSERT or UPDATE operation
ds@1952	1195	** that was started by a prior call to sqlite3GenerateConstraintChecks.
ds@1952	1196	** The stack must contain keys for all active indices followed by data
ds@1952	1197	** and the rowid for the new entry. This routine creates the new
ds@1952	1198	** entries in all indices and in the main table.
ds@1952	1199	**
ds@1952	1200	** The arguments to this routine should be the same as the first six
ds@1952	1201	** arguments to sqlite3GenerateConstraintChecks.
ds@1952	1202	*/
ds@1952	1203	void sqlite3CompleteInsertion(
ds@1952	1204	Parse pParse, / The parser context */
ds@1952	1205	Table pTab, / the table into which we are inserting */
ds@1952	1206	int base, /* Index of a read/write cursor pointing at pTab */
ds@1952	1207	char aIdxUsed, / Which indices are used. NULL means all are used */
ds@1952	1208	int rowidChng, /* True if the record number will change */
ds@1952	1209	int isUpdate, /* True for UPDATE, False for INSERT */
ds@1952	1210	int newIdx, /* Index of NEW table for triggers. -1 if none */
ds@1952	1211	int appendBias /* True if this is likely to be an append */
ds@1952	1212	){
ds@1952	1213	int i;
ds@1952	1214	Vdbe *v;
ds@1952	1215	int nIdx;
ds@1952	1216	Index *pIdx;
ds@1952	1217	int pik_flags;
ds@1952	1218
ds@1952	1219	v = sqlite3GetVdbe(pParse);
ds@1952	1220	assert( v!=0 );
ds@1952	1221	assert( pTab->pSelect==0 ); /* This table is not a VIEW */
ds@1952	1222	for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
ds@1952	1223	for(i=nIdx-1; i>=0; i--){
ds@1952	1224	if( aIdxUsed && aIdxUsed[i]==0 ) continue;
ds@1952	1225	sqlite3VdbeAddOp(v, OP_IdxInsert, base+i+1, 0);
ds@1952	1226	}
ds@1952	1227	sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
ds@1952	1228	sqlite3TableAffinityStr(v, pTab);
ds@1952	1229	#ifndef SQLITE_OMIT_TRIGGER
ds@1952	1230	if( newIdx>=0 ){
ds@1952	1231	sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
ds@1952	1232	sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
ds@1952	1233	sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0);
ds@1952	1234	}
ds@1952	1235	#endif
ds@1952	1236	if( pParse->nested ){
ds@1952	1237	pik_flags = 0;
ds@1952	1238	}else{
ds@1952	1239	pik_flags = OPFLAG_NCHANGE;
ds@1952	1240	pik_flags \|= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
ds@1952	1241	}
ds@1952	1242	if( appendBias ){
ds@1952	1243	pik_flags \|= OPFLAG_APPEND;
ds@1952	1244	}
ds@1952	1245	sqlite3VdbeAddOp(v, OP_Insert, base, pik_flags);
ds@1952	1246	if( !pParse->nested ){
ds@1952	1247	sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
ds@1952	1248	}
ds@1952	1249
ds@1952	1250	if( isUpdate && rowidChng ){
ds@1952	1251	sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
ds@1952	1252	}
ds@1952	1253	}
ds@1952	1254
ds@1952	1255	/*
ds@1952	1256	** Generate code that will open cursors for a table and for all
ds@1952	1257	** indices of that table. The "base" parameter is the cursor number used
ds@1952	1258	** for the table. Indices are opened on subsequent cursors.
ds@1952	1259	*/
ds@1952	1260	void sqlite3OpenTableAndIndices(
ds@1952	1261	Parse pParse, / Parsing context */
ds@1952	1262	Table pTab, / Table to be opened */
ds@1952	1263	int base, /* Cursor number assigned to the table */
ds@1952	1264	int op /* OP_OpenRead or OP_OpenWrite */
ds@1952	1265	){
ds@1952	1266	int i;
ds@1952	1267	int iDb;
ds@1952	1268	Index *pIdx;
ds@1952	1269	Vdbe *v;
ds@1952	1270
ds@1952	1271	if( IsVirtual(pTab) ) return;
ds@1952	1272	iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
ds@1952	1273	v = sqlite3GetVdbe(pParse);
ds@1952	1274	assert( v!=0 );
ds@1952	1275	sqlite3OpenTable(pParse, base, iDb, pTab, op);
ds@1952	1276	for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
ds@1952	1277	KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
ds@1952	1278	assert( pIdx->pSchema==pTab->pSchema );
ds@1952	1279	sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
ds@1952	1280	VdbeComment((v, "# %s", pIdx->zName));
ds@1952	1281	sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF);
ds@1952	1282	}
ds@1952	1283	if( pParse->nTab<=base+i ){
ds@1952	1284	pParse->nTab = base+i;
ds@1952	1285	}
ds@1952	1286	}
ds@1952	1287
ds@1952	1288
ds@1952	1289	#ifdef SQLITE_TEST
ds@1952	1290	/*
ds@1952	1291	** The following global variable is incremented whenever the
ds@1952	1292	** transfer optimization is used. This is used for testing
ds@1952	1293	** purposes only - to make sure the transfer optimization really
ds@1952	1294	** is happening when it is suppose to.
ds@1952	1295	*/
ds@1952	1296	int sqlite3_xferopt_count;
ds@1952	1297	#endif /* SQLITE_TEST */
ds@1952	1298
ds@1952	1299
ds@1952	1300	#ifndef SQLITE_OMIT_XFER_OPT
ds@1952	1301	/*
ds@1952	1302	** Check to collation names to see if they are compatible.
ds@1952	1303	*/
ds@1952	1304	static int xferCompatibleCollation(const char z1, const char z2){
ds@1952	1305	if( z1==0 ){
ds@1952	1306	return z2==0;
ds@1952	1307	}
ds@1952	1308	if( z2==0 ){
ds@1952	1309	return 0;
ds@1952	1310	}
ds@1952	1311	return sqlite3StrICmp(z1, z2)==0;
ds@1952	1312	}
ds@1952	1313
ds@1952	1314
ds@1952	1315	/*
ds@1952	1316	** Check to see if index pSrc is compatible as a source of data
ds@1952	1317	** for index pDest in an insert transfer optimization. The rules
ds@1952	1318	** for a compatible index:
ds@1952	1319	**
ds@1952	1320	** * The index is over the same set of columns
ds@1952	1321	** * The same DESC and ASC markings occurs on all columns
ds@1952	1322	** * The same onError processing (OE_Abort, OE_Ignore, etc)
ds@1952	1323	** * The same collating sequence on each column
ds@1952	1324	*/
ds@1952	1325	static int xferCompatibleIndex(Index pDest, Index pSrc){
ds@1952	1326	int i;
ds@1952	1327	assert( pDest && pSrc );
ds@1952	1328	assert( pDest->pTable!=pSrc->pTable );
ds@1952	1329	if( pDest->nColumn!=pSrc->nColumn ){
ds@1952	1330	return 0; /* Different number of columns */
ds@1952	1331	}
ds@1952	1332	if( pDest->onError!=pSrc->onError ){
ds@1952	1333	return 0; /* Different conflict resolution strategies */
ds@1952	1334	}
ds@1952	1335	for(i=0; i<pSrc->nColumn; i++){
ds@1952	1336	if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
ds@1952	1337	return 0; /* Different columns indexed */
ds@1952	1338	}
ds@1952	1339	if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
ds@1952	1340	return 0; /* Different sort orders */
ds@1952	1341	}
ds@1952	1342	if( pSrc->azColl[i]!=pDest->azColl[i] ){
ds@1952	1343	return 0; /* Different sort orders */
ds@1952	1344	}
ds@1952	1345	}
ds@1952	1346
ds@1952	1347	/* If no test above fails then the indices must be compatible */
ds@1952	1348	return 1;
ds@1952	1349	}
ds@1952	1350
ds@1952	1351	/*
ds@1952	1352	** Attempt the transfer optimization on INSERTs of the form
ds@1952	1353	**
ds@1952	1354	** INSERT INTO tab1 SELECT * FROM tab2;
ds@1952	1355	**
ds@1952	1356	** This optimization is only attempted if
ds@1952	1357	**
ds@1952	1358	** (1) tab1 and tab2 have identical schemas including all the
ds@1952	1359	** same indices and constraints
ds@1952	1360	**
ds@1952	1361	** (2) tab1 and tab2 are different tables
ds@1952	1362	**
ds@1952	1363	** (3) There must be no triggers on tab1
ds@1952	1364	**
ds@1952	1365	** (4) The result set of the SELECT statement is "*"
ds@1952	1366	**
ds@1952	1367	** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY,
ds@1952	1368	** or LIMIT clause.
ds@1952	1369	**
ds@1952	1370	** (6) The SELECT statement is a simple (not a compound) select that
ds@1952	1371	** contains only tab2 in its FROM clause
ds@1952	1372	**
ds@1952	1373	** This method for implementing the INSERT transfers raw records from
ds@1952	1374	** tab2 over to tab1. The columns are not decoded. Raw records from
ds@1952	1375	** the indices of tab2 are transfered to tab1 as well. In so doing,
ds@1952	1376	** the resulting tab1 has much less fragmentation.
ds@1952	1377	**
ds@1952	1378	** This routine returns TRUE if the optimization is attempted. If any
ds@1952	1379	** of the conditions above fail so that the optimization should not
ds@1952	1380	** be attempted, then this routine returns FALSE.
ds@1952	1381	*/
ds@1952	1382	static int xferOptimization(
ds@1952	1383	Parse pParse, / Parser context */
ds@1952	1384	Table pDest, / The table we are inserting into */
ds@1952	1385	Select pSelect, / A SELECT statement to use as the data source */
ds@1952	1386	int onError, /* How to handle constraint errors */
ds@1952	1387	int iDbDest /* The database of pDest */
ds@1952	1388	){
ds@1952	1389	ExprList pEList; / The result set of the SELECT */
ds@1952	1390	Table pSrc; / The table in the FROM clause of SELECT */
ds@1952	1391	Index pSrcIdx, pDestIdx; /* Source and destination indices */
ds@1952	1392	struct SrcList_item pItem; / An element of pSelect->pSrc */
ds@1952	1393	int i; /* Loop counter */
ds@1952	1394	int iDbSrc; /* The database of pSrc */
ds@1952	1395	int iSrc, iDest; /* Cursors from source and destination */
ds@1952	1396	int addr1, addr2; /* Loop addresses */
ds@1952	1397	int emptyDestTest; /* Address of test for empty pDest */
ds@1952	1398	int emptySrcTest; /* Address of test for empty pSrc */
ds@1952	1399	Vdbe v; / The VDBE we are building */
ds@1952	1400	KeyInfo pKey; / Key information for an index */
ds@1952	1401	int counterMem; /* Memory register used by AUTOINC */
ds@1952	1402	int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
ds@1952	1403
ds@1952	1404	if( pSelect==0 ){
ds@1952	1405	return 0; /* Must be of the form INSERT INTO ... SELECT ... */
ds@1952	1406	}
ds@1952	1407	if( pDest->pTrigger ){
ds@1952	1408	return 0; /* tab1 must not have triggers */
ds@1952	1409	}
ds@1952	1410	#ifndef SQLITE_OMIT_VIRTUALTABLE
ds@1952	1411	if( pDest->isVirtual ){
ds@1952	1412	return 0; /* tab1 must not be a virtual table */
ds@1952	1413	}
ds@1952	1414	#endif
ds@1952	1415	if( onError==OE_Default ){
ds@1952	1416	onError = OE_Abort;
ds@1952	1417	}
ds@1952	1418	if( onError!=OE_Abort && onError!=OE_Rollback ){
ds@1952	1419	return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
ds@1952	1420	}
ds@1952	1421	assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
ds@1952	1422	if( pSelect->pSrc->nSrc!=1 ){
ds@1952	1423	return 0; /* FROM clause must have exactly one term */
ds@1952	1424	}
ds@1952	1425	if( pSelect->pSrc->a[0].pSelect ){
ds@1952	1426	return 0; /* FROM clause cannot contain a subquery */
ds@1952	1427	}
ds@1952	1428	if( pSelect->pWhere ){
ds@1952	1429	return 0; /* SELECT may not have a WHERE clause */
ds@1952	1430	}
ds@1952	1431	if( pSelect->pOrderBy ){
ds@1952	1432	return 0; /* SELECT may not have an ORDER BY clause */
ds@1952	1433	}
ds@1952	1434	/* Do not need to test for a HAVING clause. If HAVING is present but
ds@1952	1435	** there is no ORDER BY, we will get an error. */
ds@1952	1436	if( pSelect->pGroupBy ){
ds@1952	1437	return 0; /* SELECT may not have a GROUP BY clause */
ds@1952	1438	}
ds@1952	1439	if( pSelect->pLimit ){
ds@1952	1440	return 0; /* SELECT may not have a LIMIT clause */
ds@1952	1441	}
ds@1952	1442	assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */
ds@1952	1443	if( pSelect->pPrior ){
ds@1952	1444	return 0; /* SELECT may not be a compound query */
ds@1952	1445	}
ds@1952	1446	if( pSelect->isDistinct ){
ds@1952	1447	return 0; /* SELECT may not be DISTINCT */
ds@1952	1448	}
ds@1952	1449	pEList = pSelect->pEList;
ds@1952	1450	assert( pEList!=0 );
ds@1952	1451	if( pEList->nExpr!=1 ){
ds@1952	1452	return 0; /* The result set must have exactly one column */
ds@1952	1453	}
ds@1952	1454	assert( pEList->a[0].pExpr );
ds@1952	1455	if( pEList->a[0].pExpr->op!=TK_ALL ){
ds@1952	1456	return 0; /* The result set must be the special operator "" /
ds@1952	1457	}
ds@1952	1458
ds@1952	1459	/* At this point we have established that the statement is of the
ds@1952	1460	** correct syntactic form to participate in this optimization. Now
ds@1952	1461	** we have to check the semantics.
ds@1952	1462	*/
ds@1952	1463	pItem = pSelect->pSrc->a;
ds@1952	1464	pSrc = sqlite3LocateTable(pParse, pItem->zName, pItem->zDatabase);
ds@1952	1465	if( pSrc==0 ){
ds@1952	1466	return 0; /* FROM clause does not contain a real table */
ds@1952	1467	}
ds@1952	1468	if( pSrc==pDest ){
ds@1952	1469	return 0; /* tab1 and tab2 may not be the same table */
ds@1952	1470	}
ds@1952	1471	#ifndef SQLITE_OMIT_VIRTUALTABLE
ds@1952	1472	if( pSrc->isVirtual ){
ds@1952	1473	return 0; /* tab2 must not be a virtual table */
ds@1952	1474	}
ds@1952	1475	#endif
ds@1952	1476	if( pSrc->pSelect ){
ds@1952	1477	return 0; /* tab2 may not be a view */
ds@1952	1478	}
ds@1952	1479	if( pDest->nCol!=pSrc->nCol ){
ds@1952	1480	return 0; /* Number of columns must be the same in tab1 and tab2 */
ds@1952	1481	}
ds@1952	1482	if( pDest->iPKey!=pSrc->iPKey ){
ds@1952	1483	return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
ds@1952	1484	}
ds@1952	1485	for(i=0; i<pDest->nCol; i++){
ds@1952	1486	if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){
ds@1952	1487	return 0; /* Affinity must be the same on all columns */
ds@1952	1488	}
ds@1952	1489	if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){
ds@1952	1490	return 0; /* Collating sequence must be the same on all columns */
ds@1952	1491	}
ds@1952	1492	if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){
ds@1952	1493	return 0; /* tab2 must be NOT NULL if tab1 is */
ds@1952	1494	}
ds@1952	1495	}
ds@1952	1496	for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
ds@1952	1497	if( pDestIdx->onError!=OE_None ){
ds@1952	1498	destHasUniqueIdx = 1;
ds@1952	1499	}
ds@1952	1500	for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
ds@1952	1501	if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
ds@1952	1502	}
ds@1952	1503	if( pSrcIdx==0 ){
ds@1952	1504	return 0; /* pDestIdx has no corresponding index in pSrc */
ds@1952	1505	}
ds@1952	1506	}
ds@1952	1507	#ifndef SQLITE_OMIT_CHECK
ds@1952	1508	if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
ds@1952	1509	return 0; /* Tables have different CHECK constraints. Ticket #2252 */
ds@1952	1510	}
ds@1952	1511	#endif
ds@1952	1512
ds@1952	1513	/* If we get this far, it means either:
ds@1952	1514	**
ds@1952	1515	** * We can always do the transfer if the table contains an
ds@1952	1516	** an integer primary key
ds@1952	1517	**
ds@1952	1518	** * We can conditionally do the transfer if the destination
ds@1952	1519	** table is empty.
ds@1952	1520	*/
ds@1952	1521	#ifdef SQLITE_TEST
ds@1952	1522	sqlite3_xferopt_count++;
ds@1952	1523	#endif
ds@1952	1524	iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
ds@1952	1525	v = sqlite3GetVdbe(pParse);
ds@1952	1526	sqlite3CodeVerifySchema(pParse, iDbSrc);
ds@1952	1527	iSrc = pParse->nTab++;
ds@1952	1528	iDest = pParse->nTab++;
ds@1952	1529	counterMem = autoIncBegin(pParse, iDbDest, pDest);
ds@1952	1530	sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
ds@1952	1531	if( (pDest->iPKey<0 && pDest->pIndex!=0) \|\| destHasUniqueIdx ){
ds@1952	1532	/* If tables do not have an INTEGER PRIMARY KEY and there
ds@1952	1533	** are indices to be copied and the destination is not empty,
ds@1952	1534	** we have to disallow the transfer optimization because the
ds@1952	1535	** the rowids might change which will mess up indexing.
ds@1952	1536	**
ds@1952	1537	** Or if the destination has a UNIQUE index and is not empty,
ds@1952	1538	** we also disallow the transfer optimization because we cannot
ds@1952	1539	** insure that all entries in the union of DEST and SRC will be
ds@1952	1540	** unique.
ds@1952	1541	*/
ds@1952	1542	addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0);
ds@1952	1543	emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
ds@1952	1544	sqlite3VdbeJumpHere(v, addr1);
ds@1952	1545	}else{
ds@1952	1546	emptyDestTest = 0;
ds@1952	1547	}
ds@1952	1548	sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
ds@1952	1549	emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
ds@1952	1550	if( pDest->iPKey>=0 ){
ds@1952	1551	addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
ds@1952	1552	sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
ds@1952	1553	addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
ds@1952	1554	sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
ds@1952	1555	"PRIMARY KEY must be unique", P3_STATIC);
ds@1952	1556	sqlite3VdbeJumpHere(v, addr2);
ds@1952	1557	autoIncStep(pParse, counterMem);
ds@1952	1558	}else if( pDest->pIndex==0 ){
ds@1952	1559	addr1 = sqlite3VdbeAddOp(v, OP_NewRowid, iDest, 0);
ds@1952	1560	}else{
ds@1952	1561	addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
ds@1952	1562	assert( pDest->autoInc==0 );
ds@1952	1563	}
ds@1952	1564	sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0);
ds@1952	1565	sqlite3VdbeOp3(v, OP_Insert, iDest,
ds@1952	1566	OPFLAG_NCHANGE\|OPFLAG_LASTROWID\|OPFLAG_APPEND,
ds@1952	1567	pDest->zName, 0);
ds@1952	1568	sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1);
ds@1952	1569	autoIncEnd(pParse, iDbDest, pDest, counterMem);
ds@1952	1570	for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
ds@1952	1571	for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
ds@1952	1572	if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
ds@1952	1573	}
ds@1952	1574	assert( pSrcIdx );
ds@1952	1575	sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
ds@1952	1576	sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
ds@1952	1577	sqlite3VdbeAddOp(v, OP_Integer, iDbSrc, 0);
ds@1952	1578	pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx);
ds@1952	1579	VdbeComment((v, "# %s", pSrcIdx->zName));
ds@1952	1580	sqlite3VdbeOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum,
ds@1952	1581	(char*)pKey, P3_KEYINFO_HANDOFF);
ds@1952	1582	sqlite3VdbeAddOp(v, OP_Integer, iDbDest, 0);
ds@1952	1583	pKey = sqlite3IndexKeyinfo(pParse, pDestIdx);
ds@1952	1584	VdbeComment((v, "# %s", pDestIdx->zName));
ds@1952	1585	sqlite3VdbeOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum,
ds@1952	1586	(char*)pKey, P3_KEYINFO_HANDOFF);
ds@1952	1587	addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
ds@1952	1588	sqlite3VdbeAddOp(v, OP_RowKey, iSrc, 0);
ds@1952	1589	sqlite3VdbeAddOp(v, OP_IdxInsert, iDest, 1);
ds@1952	1590	sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1+1);
ds@1952	1591	sqlite3VdbeJumpHere(v, addr1);
ds@1952	1592	}
ds@1952	1593	sqlite3VdbeJumpHere(v, emptySrcTest);
ds@1952	1594	sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
ds@1952	1595	sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
ds@1952	1596	if( emptyDestTest ){
ds@1952	1597	sqlite3VdbeAddOp(v, OP_Halt, SQLITE_OK, 0);
ds@1952	1598	sqlite3VdbeJumpHere(v, emptyDestTest);
ds@1952	1599	sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
ds@1952	1600	return 0;
ds@1952	1601	}else{
ds@1952	1602	return 1;
ds@1952	1603	}
ds@1952	1604	}
ds@1952	1605	#endif /* SQLITE_OMIT_XFER_OPT */

author	Asher Baker <asherkin@gmail.com>
	Thu Apr 26 16:58:18 2012 +0100 (2012-04-26)
changeset 3488	bee90c8ef6c3
parent 1952	b7aa2b626807
permissions	-rw-r--r--