tf2/tf2_src/gcsdk/sqlaccess/schemaupdate.cpp

//========= Copyright Valve Corporation, All rights reserved. ============//
//
// Purpose: Contains the job that's responsible for updating the database schema
//
//=============================================================================

#include "stdafx.h"
#include "gcsdk/sqlaccess/schemaupdate.h"

// memdbgon must be the last include file in a .cpp file!!!
#include "tier0/memdbgon.h"

namespace GCSDK
{
#ifndef SQL_SUCCESS
#define SQL_SUCCESS                0
#define SQL_SUCCESS_WITH_INFO      1
#define SQL_NO_DATA              100
#define SQL_ERROR                 (-1)
#endif // SQLSUCCESS

// this comes from sql.h. The GC really shouldn't depend on the MSSQL headers
#define SQL_INDEX_CLUSTERED 1

inline bool SQL_OK( SQLRETURN nRet )
{
	return ( ( SQL_SUCCESS == nRet ) || (SQL_SUCCESS_WITH_INFO == nRet ) );
}

#define SQL_FAILED( ret )	( !SQL_OK( ret ) )

#define EXIT_ON_SQL_FAILURE( ret ) \
{ \
	if ( !SQL_OK( ret ) ) \
{ \
	goto Exit;	\
}  \
}

#define EXIT_ON_BOOLSQL_FAILURE( ret ) \
{ \
	if ( !(ret) ) \
{ \
	nRet = SQL_ERROR; \
	goto Exit;	\
}  \
}

#define RETURN_SQL_ERROR_ON_FALSE( ret ) \
	if( !(ret) ) \
	{\
		return SQL_ERROR;\
	}


//-----------------------------------------------------------------------------
// Purpose: Emits a message and appends it to a string
//-----------------------------------------------------------------------------
void EmitAndAppend( CFmtStr1024 & sTarget, const CGCEmitGroup& Group, int iLevel, int iLevelLog, PRINTF_FORMAT_STRING const char *pchMessage, ... )
{
	va_list args;
	va_start( args, pchMessage );

	if( sTarget.Length() < 1024 )
		sTarget.AppendFormatV( pchMessage, args );

	EmitInfoV( Group, iLevel, iLevelLog, pchMessage, args );

	va_end( args );
}

//-----------------------------------------------------------------------------
// builds a command of the form:
//		CREATE [CLUSTERED] [UNIQUE] INDEX <index_name> ON <table_name>
//		(col1, col2, ...)
//		[INCLUDE (icol1, icol2, ...)]
//		[WITH (FILLFACTOR = n)]
//-----------------------------------------------------------------------------
CUtlString GetAddIndexSQL( CRecordInfo *pRecordInfo, const FieldSet_t &refFields )
{
	CFmtStrMax sCmd;
	sCmd.sprintf( "CREATE %s%sINDEX %s ON App%u.%s (",
		refFields.IsClustered() ? "CLUSTERED " : "",
		refFields.IsUnique() ? "UNIQUE " : "",
		refFields.GetIndexName(),
		GGCBase()->GetAppID(),
		pRecordInfo->GetName() );

	// add real columns
	for ( int n = 0; n < refFields.GetCount(); n++ )
	{
		int nField = refFields.GetField( n );
		const CColumnInfo &refInfo = pRecordInfo->GetColumnInfo( nField );

		sCmd.AppendFormat( "%s%s", 
			(n > 0) ? "," : "",
			refInfo.GetName() );
	}
	sCmd += ")";

	// do we have any included columns?
	if ( refFields.GetIncludedCount() > 0 )
	{
		// yes, add those
		sCmd += "\nINCLUDE (";

		for ( int n = 0; n < refFields.GetIncludedCount(); n++ )
		{
			int nField = refFields.GetIncludedField( n );
			const CColumnInfo &refInfo = pRecordInfo->GetColumnInfo( nField );

			sCmd.AppendFormat( "%s%s", 
				(n > 0) ? "," : "",
				refInfo.GetName() );
		}
		sCmd += ")";
	}

	// do we need a fill factor?
	if ( refFields.GetFillFactor() != 0)
	{
		sCmd.AppendFormat("\nWITH (FILLFACTOR = %d)",
			refFields.GetFillFactor() );
	}

	return CUtlString( sCmd.String() );
}

CUtlString GetAlterColumnText( CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfoDesired )
{
	Assert( pRecordInfo );
	Assert( pColumnInfoDesired );

	char rgchTmp[128];
	CUtlString sCmd;
	sCmd.Format( "ALTER TABLE App%u.%s ALTER COLUMN %s %s %s", GGCBase()->GetAppID(), pRecordInfo->GetName(), pColumnInfoDesired->GetName(),
		SQLTypeFromField( *pColumnInfoDesired, rgchTmp, Q_ARRAYSIZE( rgchTmp ) ),
		pColumnInfoDesired->BIsPrimaryKey() ? "NOT NULL" : ""
	);
	return sCmd;
}

//-----------------------------------------------------------------------------
// Purpose: Constructor
//-----------------------------------------------------------------------------
CSchemaUpdate::CSchemaUpdate()
{
	m_mapPRecordInfoDesired.SetLessFunc( CaselessStringLessThan );
	m_eConversionMode = k_EConversionModeInspectOnly;

	m_bConversionNeeded = false;
	m_cTablesDesiredMissing = 0;
	m_cTablesActualDifferent = 0;
	m_cTablesActualUnknown = 0;
	m_cTablesNeedingChange = 0;
	m_cColumnsDesiredMissing = 0;
	m_cColumnsActualDifferent = 0;
	m_cColumnsActualUnknown = 0;
	m_bSkippedAChange = false;
}


//-----------------------------------------------------------------------------
// Purpose: Destructor
//-----------------------------------------------------------------------------
CSchemaUpdate::~CSchemaUpdate()
{
	// release all the record info's we're holding onto
	FOR_EACH_MAP_FAST( m_mapPRecordInfoDesired, iRecordInfo )
	{
		SAFE_RELEASE( m_mapPRecordInfoDesired[iRecordInfo] );
	}
}


//-----------------------------------------------------------------------------
// Purpose: Adds a record info that describes a desired table that should
//			exist in the database
// Input:	pRecordInfo - pointer to record info
//-----------------------------------------------------------------------------
void CSchemaUpdate::AddRecordInfoDesired( CRecordInfo *pRecordInfo )
{
	Assert( pRecordInfo );
	const char *pchName = pRecordInfo->GetName();
	Assert( pchName && pchName[0] );
	Assert( m_mapPRecordInfoDesired.InvalidIndex() == m_mapPRecordInfoDesired.Find( pchName ) );
	// addref the record info since we're hanging onto it
	pRecordInfo->AddRef();
	// insert it in our map, indexed by name
	m_mapPRecordInfoDesired.Insert( pchName, pRecordInfo );
}


//-----------------------------------------------------------------------------
// Purpose: Adds a CFTSCatalogInfo that tells us about a FTS catalog that
//			should exist in the database
//-----------------------------------------------------------------------------
void CSchemaUpdate::AddFTSInfo( const CFTSCatalogInfo &refFTSInfo )
{
	m_listFTSCatalogInfo.AddToTail( refFTSInfo );
}


//-----------------------------------------------------------------------------
// Purpose: Adds a CFTSCatalogInfo that tells us about a FTS catalog that
//			should exist in the database
//-----------------------------------------------------------------------------
void CSchemaUpdate::AddTriggerInfos( const CUtlVector< CTriggerInfo > &refTriggerInfo )
{
	m_vecTriggerInfo = refTriggerInfo;
}


//-----------------------------------------------------------------------------
// Purpose: Validates and updates the database schema
//-----------------------------------------------------------------------------
bool CJobUpdateSchema::BYieldingRunJob( void * )
{
	// update the main schema
	EmitInfo( SPEW_GC, 2, 2, "Updating main schema...\n" );
	if ( !BYieldingUpdateSchema( k_ESchemaCatalogMain ) )
	{
		m_pGC->SetStartupComplete( false );
		return false;
	}

	// Could fail, but we shouldn't stop from starting up if it does
	BYieldingUpdateSchema( k_ESchemaCatalogOGS );

	bool bSuccess = m_pGC->BYieldingFinishStartup();
	m_pGC->SetStartupComplete( bSuccess );
	if ( bSuccess )
	{
		bSuccess = m_pGC->BYieldingPostStartup();
	}
	return true;
}


//-----------------------------------------------------------------------------
// Purpose:
//-----------------------------------------------------------------------------
bool CJobUpdateSchema::BYieldingUpdateSchema( ESchemaCatalog eSchemaCatalog )
{
	if( !YieldingBuildTypeMap( eSchemaCatalog ) )
		return false;

	// make an object to communicate desired database schema & results
	CSchemaUpdate *pSchemaUpdate = new CSchemaUpdate();

	// do safe conversions only
	// TODO - do one round of inspection only first so we can send watchdog alert about what's about
	// to happen, then do conversions.  Also force conversions in dev system.
	pSchemaUpdate->m_eConversionMode = k_EConversionModeConvertSafe;

	// Add all the tables to desired schema
	for ( int iTable = 0; iTable < m_iTableCount; iTable++ )
	{
		// MERGE COMMENT: "schema" cannot be used as a variable name because it is an empty #define resulting in error C2059
		CSchema &gc_schema = GSchemaFull().GetSchema( iTable );

		// is it in the schema we want?
		if ( gc_schema.GetESchemaCatalog() == eSchemaCatalog )
			pSchemaUpdate->AddRecordInfoDesired( gc_schema.GetRecordInfo() );
	}

	// add all the FTS catalogs to the desired schema
	for ( int n = 0; n < GSchemaFull().GetCFTSCatalogs(); n++ )
	{
		const CFTSCatalogInfo &refInfo = GSchemaFull().GetFTSCatalogInfo( n );
		pSchemaUpdate->AddFTSInfo( refInfo );
	}

	pSchemaUpdate->AddTriggerInfos( GSchemaFull().GetTriggerInfos() );

	SQLRETURN nRet = YieldingEnsureDatabaseSchemaCorrect( eSchemaCatalog, pSchemaUpdate );
	if( !SQL_OK( nRet ) )
	{
		AssertMsg( false, "SQL Schema Update failed" );
		return false;
	}

	SAFE_RELEASE( pSchemaUpdate );
	return true;
}


//-----------------------------------------------------------------------------
// Purpose: Examines actual running schema of database, compares to specified desired
//			schema, and changes the actual schema to correspond to desired schema
// Input:	pSQLThread - SQL thread to execute on
//			pSchemaUpdate - pointer to object with desired schema 
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingEnsureDatabaseSchemaCorrect( ESchemaCatalog eSchemaCatalog, CSchemaUpdate *pSchemaUpdate )
{
	Assert( pSchemaUpdate );

	CMapPRecordInfo &mapPRecordInfoDesired = pSchemaUpdate->m_mapPRecordInfoDesired;
	const CUtlVector< CTriggerInfo > &vecTriggerInfoDesired = pSchemaUpdate->m_vecTriggerInfo;
	m_eConversionMode = pSchemaUpdate->m_eConversionMode;

	bool bDoConversion = true;
//	bool bDoConversion = ( ( k_EConversionModeConvertSafe == eConversionMode ) || 
//		( k_EConversionModeConvertIrreversible == eConversionMode ) );	

	CMapPRecordInfo mapPRecordInfoActual;
	mapPRecordInfoActual.SetLessFunc( CaselessStringLessThan );
	CUtlVector<CRecordInfo *> vecPRecordInfoDesiredMissing;
	CUtlVector<CRecordInfo *> vecPRecordInfoActualDifferent;
	CUtlVector<CRecordInfo *> vecPRecordInfoActualUnknown;
	CUtlVector< CTriggerInfo > vecTriggerInfoActual;
	CUtlVector< CTriggerInfo > vecTriggerInfoMissing;
	CUtlVector< CTriggerInfo > vecTriggerInfoDifferent;

	pSchemaUpdate->m_cTablesDesiredMissing = 0;
	pSchemaUpdate->m_cTablesActualDifferent = 0;
	pSchemaUpdate->m_cTablesActualUnknown = 0;
	pSchemaUpdate->m_cTablesNeedingChange = 0;
	pSchemaUpdate->m_cColumnsDesiredMissing = 0;
	pSchemaUpdate->m_cColumnsActualDifferent = 0;
	pSchemaUpdate->m_cColumnsActualUnknown = 0;
	pSchemaUpdate->m_sDetail.Clear();

	CFmtStr1024 &sDetail = pSchemaUpdate->m_sDetail;

	CFastTimer tickCounterOverall;
	tickCounterOverall.Start();

	//
	// Do some up-front bookkeeping to see how many tables need to be created and/or altered
	//

	int nSchemaID;
	SQLRETURN nRet = YieldingGetSchemaID( eSchemaCatalog, &nSchemaID );
	EXIT_ON_SQL_FAILURE( nRet );

	// Determine the actual running schema
	nRet = YieldingGetRecordInfoForAllTables( eSchemaCatalog, nSchemaID, mapPRecordInfoActual );
	EXIT_ON_SQL_FAILURE( nRet );

	nRet = YieldingGetTriggers( eSchemaCatalog, nSchemaID, vecTriggerInfoActual );
	EXIT_ON_SQL_FAILURE( nRet );

	// Look through the list of desired tables, find any that are missing or different from the actual schema
	FOR_EACH_MAP_FAST( mapPRecordInfoDesired, iRecordInfoDesired )
	{
		// is this desired table in the currently connected catalog?
		CRecordInfo *pRecordInfoDesired = mapPRecordInfoDesired[iRecordInfoDesired];
		if ( pRecordInfoDesired->GetESchemaCatalog() == eSchemaCatalog )
		{
			// yes. do something about it
			int iRecordInfoActual = mapPRecordInfoActual.Find( pRecordInfoDesired->GetName() );
			if ( mapPRecordInfoDesired.InvalidIndex() == iRecordInfoActual )
			{
				// This table is desired but does not exist
				vecPRecordInfoDesiredMissing.AddToTail( pRecordInfoDesired );
			}
			else
			{
				// Table with same name exists in desired & actual schemas; is it exactly the same?
				CRecordInfo *pRecordInfoActual = mapPRecordInfoActual[iRecordInfoActual];
				if ( !pRecordInfoDesired->EqualTo( pRecordInfoActual ) )
				{
					// This desired table exists but the actual table is different than desired
					vecPRecordInfoActualDifferent.AddToTail( pRecordInfoActual );
				}
			}
		}
	}

	// Now, look through the list of actual tables and find any that do not exist in the list of desired tables
	FOR_EACH_MAP_FAST( mapPRecordInfoActual, iRecordInfoActual )
	{
		CRecordInfo *pRecordInfoActual = mapPRecordInfoActual[iRecordInfoActual];
		int iRecordInfoDesired = mapPRecordInfoDesired.Find( pRecordInfoActual->GetName() );
		if ( !mapPRecordInfoDesired.IsValidIndex( iRecordInfoDesired ) )
		{
			// This table exists but is not in the list of desired tables
			// maybe it's an old table.

			vecPRecordInfoActualUnknown.AddToTail( pRecordInfoActual );
		}
	}

	// find a list of missing triggers
	FOR_EACH_VEC( vecTriggerInfoDesired, iDesired )
	{
		// not of this catalog? skip it
		if ( vecTriggerInfoDesired[ iDesired ].m_eSchemaCatalog != eSchemaCatalog )
			continue;

		// it is our catalog, so try and match
		bool bMatched = false;
		FOR_EACH_VEC( vecTriggerInfoActual, iActual )
		{
			// is it the same table and trigger name?
			if ( vecTriggerInfoActual[ iActual ] == vecTriggerInfoDesired[ iDesired ] )
			{
				// yes! test the text for differences
				if ( vecTriggerInfoActual[ iActual ].IsDifferent( vecTriggerInfoDesired[ iDesired ] ) )
				{
					vecTriggerInfoDifferent.AddToTail( vecTriggerInfoDesired[ iDesired ] );
				}
				else
				{
					// we have a match!
					vecTriggerInfoActual[ iActual ].m_bMatched = true;
					bMatched = true;
				}
				break;
			}
		}

		if ( !bMatched )
		{
			vecTriggerInfoMissing.AddToTail( vecTriggerInfoDesired[ iDesired ] );
		}
	}

	//
	// Now do the actual conversion
	//
	EmitAndAppend( sDetail, SPEW_GC, 2, 2, "Database conversion: %s\n",
		bDoConversion ? "beginning" : "inspection only" );


	// find tables which need to be created
	EmitAndAppend( sDetail, SPEW_GC, 2, 2, "# of specified tables that do not currently exist in database: %d\n", 
		vecPRecordInfoDesiredMissing.Count() );
	if ( vecPRecordInfoDesiredMissing.Count() > 0 )
		pSchemaUpdate->m_bConversionNeeded = true;

	// Create any tables which need to be created
	for ( int iTable = 0; iTable < vecPRecordInfoDesiredMissing.Count(); iTable++ )
	{
		CRecordInfo *pRecordInfoDesired = vecPRecordInfoDesiredMissing[iTable];
		if ( bDoConversion )
		{
			CFastTimer tickCounter;
			tickCounter.Start();

			// Create the table
			nRet = YieldingCreateTable( eSchemaCatalog, pRecordInfoDesired );
			EXIT_ON_SQL_FAILURE( nRet );
			tickCounter.End();
			int nElapsedMilliseconds = tickCounter.GetDuration().GetMilliseconds();
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tCreated table: %s: %d millisec\n", pRecordInfoDesired->GetName(), nElapsedMilliseconds );
		}
		else
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t%s\n", pRecordInfoDesired->GetName() );
	}

	// find tables which are different
	EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: # of specified tables that differ from schema in database: %d\n",
		vecPRecordInfoActualDifferent.Count() );

#ifdef _DEBUG
	// are some different? if so, list their names only for now.
	// This is in _debug only because it's useful for debugging the below loop,
	// but spewey for everyday life (as long as the below loop is working).
	if ( vecPRecordInfoActualDifferent.Count() > 0 ) 
	{
		FOR_EACH_VEC( vecPRecordInfoActualDifferent, i )
		{
			CRecordInfo *pRecordInfoActual = vecPRecordInfoActualDifferent[i];
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]:   table %s is different\n",
				pRecordInfoActual->GetName() );
		}
	}
#endif

	pSchemaUpdate->m_bSkippedAChange = false;

	// Alter any table which needs to be altered
	for ( int iTable = 0; iTable < vecPRecordInfoActualDifferent.Count(); iTable++ )
	{
		CRecordInfo *pRecordInfoActual = vecPRecordInfoActualDifferent[iTable];
		int iRecordInfoDesired = mapPRecordInfoDesired.Find( pRecordInfoActual->GetName() );
		Assert( mapPRecordInfoDesired.InvalidIndex() != iRecordInfoDesired );
		CRecordInfo *pRecordInfoDesired = mapPRecordInfoDesired[iRecordInfoDesired];

		CUtlVector<const CColumnInfo *> vecPColumnInfoDesiredMissing;
		CUtlVector<const CColumnInfo *> vecPColumnInfoActualDifferent;
		CUtlVector<const CColumnInfo *> vecPColumnInfoActualUnknown;

		// We know something is different between the actual & desired schema for this table, but don't yet know what

		// Compare each column
		for ( int iColumnDesired = 0; iColumnDesired < pRecordInfoDesired->GetNumColumns(); iColumnDesired ++ )
		{
			const CColumnInfo &columnInfoDesired = pRecordInfoDesired->GetColumnInfo( iColumnDesired );
			int iColumnActual = -1;
			bool bRet = pRecordInfoActual->BFindColumnByName( columnInfoDesired.GetName(), &iColumnActual );
			if ( bRet )
			{
				const CColumnInfo &columnInfoActual = pRecordInfoActual->GetColumnInfo( iColumnActual );
				if ( columnInfoActual.GetChecksum() != columnInfoDesired.GetChecksum() )
				{
					// The actual column is different than the desired column
					vecPColumnInfoActualDifferent.AddToTail( &columnInfoActual );
				}
			}
			else
			{
				// The desired column is missing from the actual table
				vecPColumnInfoDesiredMissing.AddToTail( &columnInfoDesired );
			}
		}
		for ( int iColumnActual = 0; iColumnActual < pRecordInfoActual->GetNumColumns(); iColumnActual ++ )
		{
			const CColumnInfo &columnInfoActual = pRecordInfoActual->GetColumnInfo( iColumnActual );
			int iColumnDesired = -1;
			bool bRet = pRecordInfoDesired->BFindColumnByName( columnInfoActual.GetName(), &iColumnDesired );
			if ( !bRet )
			{
				// this column exists in the running schema, but not in the desired schema (e.g. old column)
				vecPColumnInfoActualUnknown.AddToTail( &columnInfoActual );
			}
		}

		if ( ( vecPColumnInfoDesiredMissing.Count() > 0 )  || ( vecPColumnInfoActualDifferent.Count() > 0 ) )
		{
			pSchemaUpdate->m_bConversionNeeded = true;
			pSchemaUpdate->m_cTablesNeedingChange++;
		}

		// Add any desired columns which are missing from the actual schema
		if ( vecPColumnInfoDesiredMissing.Count() > 0 )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\tDesired columns missing in table %s:\n", pRecordInfoActual->GetName() );

			for ( int iColumn = 0; iColumn < vecPColumnInfoDesiredMissing.Count(); iColumn++ )
			{
				const CColumnInfo *pColumnInfoDesired = vecPColumnInfoDesiredMissing[iColumn];
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t\t%s\n", pColumnInfoDesired->GetName() );
				if ( bDoConversion )
				{
					CFastTimer tickCounter;
					tickCounter.Start();
					// Add the column
					nRet = YieldingAlterTableAddColumn( eSchemaCatalog, pRecordInfoActual, pColumnInfoDesired );
					EXIT_ON_SQL_FAILURE( nRet );
					tickCounter.End();
					int nElapsedMilliseconds = tickCounter.GetDuration().GetMilliseconds();
					EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t\t\tCreated column %s: %d millisec\n", pColumnInfoDesired->GetName(), nElapsedMilliseconds );
				}
			}
		}

		// Check for any stray indices that aren't found in the specification?
		bool bIndexMismatch = false;
		for ( int idx = 0 ; idx < pRecordInfoActual->GetIndexFieldCount() ; ++idx )
		{
			const FieldSet_t &fs = pRecordInfoActual->GetIndexFields()[idx];
			if ( pRecordInfoDesired->FindIndex( pRecordInfoActual, fs ) >= 0 )
				continue;
			if ( pRecordInfoDesired->FindIndexByName( fs.GetIndexName() ) >= 0 )
				continue; // we already handled this above
			bIndexMismatch = true;

			if ( idx == pRecordInfoActual->GetPKIndex() )
			{
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tTable %s primary key in database differs from specification.\n",
					pRecordInfoDesired->GetName() );
				nRet = YieldingProcessUnsafeConversion( eSchemaCatalog,
					CFmtStr("ALTER TABLE App%u.%s DROP CONSTRAINT %s", GGCBase()->GetAppID(), pRecordInfoActual->GetName(), fs.GetIndexName() ).String(),
					CFmtStr("%s: remove old primary key.", pRecordInfoDesired->GetName() ).String() );
				EXIT_ON_SQL_FAILURE( nRet );
			}
			else
			{
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tTable %s index %s exists in database but is not in specification.  (Possible performance problem?).\n",
					pRecordInfoDesired->GetName(), fs.GetIndexName() );
				nRet = YieldingProcessUnsafeConversion( eSchemaCatalog,
					CFmtStr("DROP INDEX %s ON App%u.%s", fs.GetIndexName(), GGCBase()->GetAppID(), pRecordInfoActual->GetName() ).String(),
					CFmtStr("%s: cleanup stray index %s.", pRecordInfoDesired->GetName(), fs.GetIndexName() ).String() );
				EXIT_ON_SQL_FAILURE( nRet );
			}
		}

		// Change any columns which are different between desired and actual schema
		if ( vecPColumnInfoActualDifferent.Count() > 0 )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tColumns that differ between specification and database in table %s:\n",
				pRecordInfoActual->GetName() );
			for ( int iColumn = 0; iColumn < vecPColumnInfoActualDifferent.Count(); iColumn++ )
			{
				const CColumnInfo *pColumnInfoActual = vecPColumnInfoActualDifferent[iColumn];
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t%s", pColumnInfoActual->GetName() );
				int iColumnDesired = -1;
				DbgVerify( pRecordInfoDesired->BFindColumnByName( pColumnInfoActual->GetName(), &iColumnDesired ) );
				const CColumnInfo *pColumnInfoDesired = &pRecordInfoDesired->GetColumnInfo( iColumnDesired );

				// if type or size changed, alter the column
				if ( ( pColumnInfoDesired->GetType() != pColumnInfoActual->GetType() )  ||
					// fixed length field, and the sizes differ
					( ! pColumnInfoDesired->BIsVariableLength() && 
					( pColumnInfoDesired->GetFixedSize() != pColumnInfoActual->GetFixedSize() ) ) ||
					// variable length field, and the sizes differ
					// fixed length field, and the sizes differ
					( pColumnInfoDesired->BIsVariableLength() && 
					( pColumnInfoDesired->GetMaxSize() != pColumnInfoActual->GetMaxSize() ) ) )
				{
					if ( k_EConversionModeConvertIrreversible != m_eConversionMode )
					{
						pSchemaUpdate->m_bSkippedAChange = true;
						if ( pColumnInfoDesired->GetType() != pColumnInfoActual->GetType() )
							EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t(data types differ: desired=%s, actual=%s)", PchNameFromEGCSQLType( pColumnInfoDesired->GetType() ), PchNameFromEGCSQLType( pColumnInfoActual->GetType() ) ); 
						if ( pColumnInfoDesired->GetFixedSize() != pColumnInfoActual->GetFixedSize() )
							EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t(column sizes differ: desired=%d, actual=%d)", pColumnInfoDesired->GetFixedSize(), pColumnInfoActual->GetFixedSize()  ); 
						if ( pColumnInfoDesired->GetMaxSize() != pColumnInfoActual->GetMaxSize() )
							EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t(maximum column sizes differ: desired=%d, actual=%d)", pColumnInfoDesired->GetMaxSize(), pColumnInfoActual->GetMaxSize()  ); 
					}
					nRet = YieldingChangeColumnTypeOrLength( eSchemaCatalog, pRecordInfoActual, pColumnInfoDesired );
					EXIT_ON_SQL_FAILURE( nRet );
				}

				// If column constraints/indexes are different, make appropriate adjustments
				// do this second so it has a chance to succeed - otherwise, we may create an index here that
				// prevents us from performing an alter table
				if ( pColumnInfoDesired->GetColFlags() != pColumnInfoActual->GetColFlags() )
				{
					if ( k_EConversionModeConvertIrreversible != m_eConversionMode )
					{
						char szDesiredFlags[k_nKiloByte];
						char szActualFlags[k_nKiloByte];
						pColumnInfoDesired->GetColFlagDescription( szDesiredFlags, Q_ARRAYSIZE( szDesiredFlags ) );
						pColumnInfoActual->GetColFlagDescription( szActualFlags, Q_ARRAYSIZE( szActualFlags ) );

						pSchemaUpdate->m_bSkippedAChange = true;
						EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t(column flags differ: desired=\"%s\", actual=\"%s\")",
							szDesiredFlags, szActualFlags );
					}
					nRet = YieldingChangeColumnProperties( eSchemaCatalog, pRecordInfoActual, pColumnInfoActual, pColumnInfoDesired );
					EXIT_ON_SQL_FAILURE( nRet );
				}

				if ( pSchemaUpdate->m_bSkippedAChange )
					EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t(Not attempting unsafe change).\n" );
			}
		}

		// Scan for any new / changed indices
		for ( int idx = 0 ; idx < pRecordInfoDesired->GetIndexFieldCount() ; ++idx )
		{
			const FieldSet_t &fs = pRecordInfoDesired->GetIndexFields()[idx];
			int iActualIdx = pRecordInfoActual->FindIndex( pRecordInfoDesired, fs );
			if ( iActualIdx >= 0 )
				continue;
			bIndexMismatch = true;

			// The exact index we want doesn't exist.  Is it the primary key?
			CUtlString sCommand;
			CUtlString sComment;
			if ( idx == pRecordInfoDesired->GetPKIndex() )
			{
					EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tTable %s primary key in specification differs from database.\n",
						pRecordInfoDesired->GetName() );
					sComment.Format( "%s: create new primary key constraint", pRecordInfoDesired->GetName() );
					TSQLCmdStr cmd;
					BuildTablePKConstraintText( &cmd, pRecordInfoDesired );
					for ( int i = 0 ; i < fs.GetCount() ; ++i ) // make sure they are non-NULL
					{
						int idxField = fs.GetField(i);
						const CColumnInfo *pColInfo = &pRecordInfoDesired->GetColumnInfo( idxField );
						Assert( pColInfo->BIsPrimaryKey() );
						sCommand += GetAlterColumnText( pRecordInfoDesired, pColInfo );
						sCommand += ";\n";
					}

					CUtlString sCreatePK;
					sCreatePK.Format( "GO\nALTER TABLE App%u.%s ADD %s\n", GGCBase()->GetAppID(), pRecordInfoDesired->GetName(), cmd.String() );
					sCommand += sCreatePK;
			}
			else
			{

				// Another common thing that could happen is that an index is changed.
				// Look for an existing index with the same name as a way to try to
				// detect this common case and provide a more specific message.  (Otherwise,
				// we will report it as a missing index, and an extra unwanted index --- which
				// is correct but a bit more confusing.)

				iActualIdx = pRecordInfoActual->FindIndexByName( fs.GetIndexName() );
				if ( iActualIdx < 0 )
				{
					sComment.Format("%s: add index %s", pRecordInfoDesired->GetName(), fs.GetIndexName() );
					EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tTable %s index %s is specified but not present in database.\n",
						pRecordInfoDesired->GetName(), fs.GetIndexName() );
				}
				else
				{
					EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tTable %s index %s differs between specification and database.\n",
						pRecordInfoDesired->GetName(), fs.GetIndexName() );
					sComment.Format( "%s: fix index %s", pRecordInfoDesired->GetName(), fs.GetIndexName() );
					sCommand.Format( "DROP INDEX %s ON App%u.%s;\n", fs.GetIndexName(), GGCBase()->GetAppID(), pRecordInfoDesired->GetName() );
				}
				sCommand += GetAddIndexSQL( pRecordInfoDesired, fs );
			}
			nRet = YieldingProcessUnsafeConversion( eSchemaCatalog, sCommand, sComment );
			EXIT_ON_SQL_FAILURE( nRet );
		}

		// Just to be safe, let's run the old code, too.
		if ( !bIndexMismatch && !pRecordInfoActual->CompareIndexLists( pRecordInfoDesired ) )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tIndex sets in table %s differ between specification and database [GC]:\n",
				pRecordInfoDesired->GetName() );
			CFmtStr1024 sTemp;

			pRecordInfoDesired->GetIndexFieldList( &sTemp, 3 );
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\tDesired %s", sTemp.Access() );

			pRecordInfoActual->GetIndexFieldList( &sTemp, 3 );
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\tActual %s", sTemp.Access() );
		}

		// what about foreign key constraints?
		if ( ! pRecordInfoActual->CompareFKs( pRecordInfoDesired ) )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tForeign Key constraints in table %s differs between specification and database [GC]:\n",
				pRecordInfoDesired->GetName() );

			CFmtStr1024 sTemp;
			pRecordInfoDesired->GetFKListString( &sTemp, 3 );
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\tDesired %s", sTemp.Access() );

			pRecordInfoActual->GetFKListString( &sTemp, 3 );
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\tActual %s", sTemp.Access() );
		}



		if ( vecPColumnInfoActualUnknown.Count() > 0 )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tColumns in database [GC] table %s that are not in specification (ignored):\n",
				pRecordInfoActual->GetName() );

			// Since this can actually destroy data, let's not ever, ever run it automatically.
			CUtlString sCommand;
			sCommand.Format( "ALTER TABLE App%u.%s DROP COLUMN ", GGCBase()->GetAppID(), pRecordInfoActual->GetName() );

			for ( int iColumn = 0; iColumn < vecPColumnInfoActualUnknown.Count(); iColumn++ )
			{
				const CColumnInfo *pColumnInfo = vecPColumnInfoActualUnknown[iColumn];
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t%s\n", pColumnInfo->GetName() );
				if ( iColumn != 0 )
					sCommand += ", ";
				sCommand += pColumnInfo->GetName();
			}
			AddDataDestroyingConversion( sCommand,
				CFmtStr( "-- Drop extra column(s) in %s\n", pRecordInfoActual->GetName() ) );
		}

		pSchemaUpdate->m_cColumnsDesiredMissing += vecPColumnInfoDesiredMissing.Count();
		pSchemaUpdate->m_cColumnsActualDifferent += vecPColumnInfoActualDifferent.Count();
		pSchemaUpdate->m_cColumnsActualUnknown += vecPColumnInfoActualUnknown.Count();
	}

	EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: # of tables that currently exist in database but were unspecified: %d\n", 
		vecPRecordInfoActualUnknown.Count() );
	for ( int iTable = 0; iTable < vecPRecordInfoActualUnknown.Count(); iTable++ )
	{
		CRecordInfo *pRecordInfo = vecPRecordInfoActualUnknown[iTable];
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t%s\n", pRecordInfo->GetName() );
		AddDataDestroyingConversion(
			CFmtStr( "DROP TABLE App%u.%s\n", GGCBase()->GetAppID(), pRecordInfo->GetName() ),
			CFmtStr( "-- Drop extra table %s\n", pRecordInfo->GetName() ) );
	}

	// then, the triggers
	if ( vecTriggerInfoMissing.Count() > 0 )
	{
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: # of specified triggers that do not currently exist: %d\n",
			vecTriggerInfoMissing.Count() );

		FOR_EACH_VEC( vecTriggerInfoMissing, iMissing )
		{
			CFastTimer tickCounter;
			tickCounter.Start();

			// Create the trigger
			nRet = YieldingCreateTrigger( eSchemaCatalog, vecTriggerInfoMissing[ iMissing] );
			EXIT_ON_SQL_FAILURE( nRet );
			tickCounter.End();
			int nElapsedMilliseconds = tickCounter.GetDuration().GetMilliseconds();
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: Created trigger %s on table %s: %d millisec\n",
				vecTriggerInfoMissing[ iMissing ].m_szTriggerName,
				vecTriggerInfoMissing[ iMissing ].m_szTriggerTableName, nElapsedMilliseconds );
		}
	}

	// different triggers
	FOR_EACH_VEC( vecTriggerInfoDifferent, iDifferent )
	{
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: Trigger %s on table %s differs from the desired trigger\n", vecTriggerInfoMissing[ iDifferent ].m_szTriggerName,
			vecTriggerInfoMissing[ iDifferent ].m_szTriggerTableName);

		// a different trigger text is a forced failure.
		nRet = SQL_ERROR;
	}

	// extra triggers
	FOR_EACH_VEC( vecTriggerInfoActual, iActual )
	{
		// if it was never matched, it isn't in the schema anywhere
		if ( ! vecTriggerInfoActual[ iActual ].m_bMatched )
		{
			SQLRETURN nSQLReturn = YieldingDropTrigger( eSchemaCatalog, vecTriggerInfoActual[ iActual ] );

			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: Trigger %s on table %s is not in the declared schema ... Drop %s",
				vecTriggerInfoActual[ iActual ].m_szTriggerName,
				vecTriggerInfoActual[ iActual ].m_szTriggerTableName,
				SQL_OK( nSQLReturn ) ? "OK" : "FAILED!" ) ;

			if ( !SQL_OK ( nSQLReturn ) )
			{
				// it broke; latch the failure
				nRet = nSQLReturn;
			}
		}
	}

	tickCounterOverall.End();
	EmitAndAppend( sDetail, SPEW_GC, 2, 2, "[GC]: Total time: %d milliseconds\n",
		tickCounterOverall.GetDuration().GetMilliseconds() );

Exit:

	// Spew suggested SQL to clean up stuff
	if ( !m_sRecommendedSQL.IsEmpty() || !m_sDataDestroyingCleanupSQL.IsEmpty() )
	{
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\tThe following SQL might work to fixup schema differences.\n" );
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t** \n" );
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t** DISCLAIMER ** This conversion code is not well tested.  Review the SQL and use at your own risk.\n" );
		EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t** \n" );
		{
			CUtlVectorAutoPurge<char*> vecLines;
			V_SplitString( m_sRecommendedSQL, "\n", vecLines );
			FOR_EACH_VEC( vecLines, i )
			{
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t%s\n", vecLines[i] );
			}
			m_sRecommendedSQL.Clear();
		}
		if ( !m_sDataDestroyingCleanupSQL.IsEmpty() )
		{
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t-- WARNING: The following operations will *destroy* data that is in the database but not in the specification.\n" );
			EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t--          If you have manually created extra tables or columns in your database, it will appear here!\n" );
			CUtlVectorAutoPurge<char*> vecLines;
			V_SplitString( m_sDataDestroyingCleanupSQL, "\n", vecLines );
			FOR_EACH_VEC( vecLines, i )
			{
				EmitAndAppend( sDetail, SPEW_GC, 2, 2, "\t\t%s\n", vecLines[i] );
			}
			m_sDataDestroyingCleanupSQL.Clear();
		}
	}

	pSchemaUpdate->m_cTablesDesiredMissing = vecPRecordInfoDesiredMissing.Count();
	pSchemaUpdate->m_cTablesActualDifferent = vecPRecordInfoActualDifferent.Count();
	pSchemaUpdate->m_cTablesActualUnknown = vecPRecordInfoActualUnknown.Count();

	FOR_EACH_MAP_FAST( mapPRecordInfoActual, iRecordInfoActual )
		SAFE_RELEASE( mapPRecordInfoActual[iRecordInfoActual] );

	return nRet;
}


//-----------------------------------------------------------------------------
// Purpose: Builds a record info for each table in database that describes the
//			columns in that table
// Input:	pSQLConnection - SQL connection to execute on
//			mapPRecordInfo - map to add the table record infos to
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingGetSchemaID( ESchemaCatalog eSchemaCatalog, int *pSchemaID )
{
	CSQLAccess sqlAccess( eSchemaCatalog );
	CFmtStr1024 sDefaultSchema;
	if( !sqlAccess.BYieldingExecuteString( FILE_AND_LINE, "SELECT default_schema_name FROM sys.database_principals WHERE name=CURRENT_USER",
		&sDefaultSchema ) )
		return SQL_ERROR;

	CFmtStr sExpectedDefaultSchema( "App%u", GGCBase()->GetAppID() );
	if ( 0 != Q_stricmp( sDefaultSchema, sExpectedDefaultSchema ) )
	{
		AssertMsg2( false, "SQL connection has the wrong default schema. Expected: %s. Actual %s", sExpectedDefaultSchema.Get(), sDefaultSchema.Get() );
		return SQL_ERROR;
	}

	sqlAccess.AddBindParam( sDefaultSchema );
	if( !sqlAccess.BYieldingExecuteScalarInt( FILE_AND_LINE, "SELECT SCHEMA_ID(?)", pSchemaID ) )
		return SQL_ERROR;

	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: Builds a record info for each table in database that describes the
//			columns in that table
// Input:	pSQLConnection - SQL connection to execute on
//			mapPRecordInfo - map to add the table record infos to
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingGetRecordInfoForAllTables( ESchemaCatalog eSchemaCatalog, int nSchemaID, CMapPRecordInfo &mapPRecordInfo )
{
	CSQLAccess sqlAccess( eSchemaCatalog );

	// create a query that returns all tables in the database
	sqlAccess.AddBindParam( nSchemaID );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, "SELECT object_id, name FROM sys.objects WHERE type_desc = 'USER_TABLE' AND is_ms_shipped = 0 AND schema_id = ?" ) );

	FOR_EACH_SQL_RESULT( sqlAccess, 0, tableIDRecord )
	{
		int nTableID;
		RETURN_SQL_ERROR_ON_FALSE( tableIDRecord.BGetIntValue( 0, &nTableID ) );

		CFmtStr1024 sTableName;
		RETURN_SQL_ERROR_ON_FALSE( tableIDRecord.BGetStringValue( 1, &sTableName) );

		YieldingGetColumnInfoForTable( eSchemaCatalog, mapPRecordInfo, nTableID, sTableName );
	}


	// now, get the column indexes and constraints for each table
	FOR_EACH_MAP_FAST( mapPRecordInfo, iRecordInfo )
	{
		CRecordInfo *pRecordInfo = mapPRecordInfo[iRecordInfo];
		pRecordInfo->SetAllColumnsAdded();

		// determine indexes and constraints
		YieldingGetColumnIndexes( eSchemaCatalog, pRecordInfo );

		// determine FK constraints
		YieldingGetTableFKConstraints( eSchemaCatalog, pRecordInfo );

		// do final calculations then ready to use
		pRecordInfo->PrepareForUse();
	}

	return SQL_SUCCESS;
}

SQLRETURN CJobUpdateSchema::YieldingGetColumnInfoForTable( ESchemaCatalog eSchemaCatalog, CMapPRecordInfo &mapPRecordInfo, int nTableID, const char *pchTableName )
{
	CSQLAccess sqlAccess( eSchemaCatalog );

	sqlAccess.AddBindParam( nTableID );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, "SELECT name, column_id, user_type_id, max_length, is_identity FROM sys.columns WHERE object_id = ?") );

	CRecordInfo *pRecordInfo = CRecordInfo::Alloc();
	pRecordInfo->SetName( pchTableName );
	pRecordInfo->SetTableID( nTableID );
	FOR_EACH_SQL_RESULT( sqlAccess, 0, columnInfo )
	{
		CFmtStr1024 sColumnName;
		int nType;
		int nColumnID;
		int nMaxLength;
		bool bIdentity;
		RETURN_SQL_ERROR_ON_FALSE( columnInfo.BGetStringValue( 0, &sColumnName) );
		RETURN_SQL_ERROR_ON_FALSE( columnInfo.BGetIntValue( 1, &nColumnID ) );
		RETURN_SQL_ERROR_ON_FALSE( columnInfo.BGetIntValue( 2, &nType ) );
		RETURN_SQL_ERROR_ON_FALSE( columnInfo.BGetIntValue( 3, &nMaxLength ) );
		RETURN_SQL_ERROR_ON_FALSE( columnInfo.BGetBoolValue( 4, &bIdentity) );

		int nColFlags = 0;
		if( bIdentity )
			nColFlags |= k_nColFlagAutoIncrement;

		pRecordInfo->AddColumn( sColumnName, nColumnID, GetEGCSQLTypeForMSSQLType( nType ), nMaxLength, nColFlags, nMaxLength );
	}
	mapPRecordInfo.Insert( pRecordInfo->GetName(), pRecordInfo );

	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// purpose: get a list of triggers from the database.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingGetTriggers( ESchemaCatalog eSchemaCatalog, int nSchemaID, CUtlVector< CTriggerInfo > &vecTriggerInfo )
{
	CSQLAccess sqlAccess( eSchemaCatalog );

	// get some description and the text of the triggers on this database.
	// Find the name of the trigger, the name of the table it servers, the type of
	// trigger, and its text. Doesn't bring back any disabled or MS-shipped triggers,
	// and gets only DML triggers and not DDL triggers.
	const char *pchStatement = "SELECT ST.name AS TriggerName, SOT.name AS TableName, ST.is_instead_of_trigger, SC.Text, SC.ColID"
		"  FROM sys.triggers AS ST"
		"  JOIN sys.syscomments AS SC ON SC.id = ST.object_id"
		"  JOIN sys.objects AS SO ON SO.object_id = ST.object_id"
		"  JOIN sys.objects AS SOT on SOT.object_id = ST.parent_id"
		" WHERE ST.type_desc = 'SQL_TRIGGER'"
		"   AND ST.is_ms_shipped = 0 AND ST.is_disabled = 0"
		"   AND ST.parent_class = 1"
		"   AND SO.schema_id = ?"
		" ORDER BY TableName, TriggerName, SC.ColID";
	sqlAccess.AddBindParam( nSchemaID );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, pchStatement ) );

	// should be one results set
	Assert( 1 == sqlAccess.GetResultSetCount() );

	FOR_EACH_SQL_RESULT( sqlAccess, 0, sqlRecord )
	{
		// get the text of the procedure
		const char *pchText;
		DbgVerify( sqlRecord.BGetStringValue( 3, &pchText ) );

		// is this instead of?
		bool bIsInsteadOf;
		DbgVerify( sqlRecord.BGetBoolValue( 2, &bIsInsteadOf ) );

		// get the table name
		const char *pchTableName;
		DbgVerify( sqlRecord.BGetStringValue( 1, &pchTableName ) );

		// and the trigger name
		const char *pchTriggerName;
		DbgVerify( sqlRecord.BGetStringValue( 0, &pchTriggerName ) );

		// finally, grab the collation id
		int16 nColID;
		DbgVerify( sqlRecord.BGetInt16Value( 4, &nColID ) );

		if ( nColID == 1 )
		{
			// the collation ID is one, so we know this is a new one
			CTriggerInfo info;
			info.m_strText = pchText;
			Q_strncpy( info.m_szTriggerName, pchTriggerName, Q_ARRAYSIZE( info.m_szTriggerName ) );
			Q_strncpy( info.m_szTriggerTableName, pchTableName, Q_ARRAYSIZE( info.m_szTriggerTableName ) );
			info.m_eSchemaCatalog = eSchemaCatalog;
			vecTriggerInfo.AddToTail( info );
		}
		else
		{
			// the collation ID is not one, so we're concatenating.
			Assert( vecTriggerInfo.Count() - 1 >= 0 );

			// the name could not have changed
			Assert( 0 == Q_strcmp( vecTriggerInfo[vecTriggerInfo.Count() - 1].m_szTriggerName, pchTriggerName ) );
		}
	}

	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: retrieves the index information for the specified table, then adds this
//			information to the record info. This is a SQL Server-specific implementation
//			which gets data describing index features not available through plain ODBC
//			queries.
// Input:	pSQLConnection - SQL connection to execute on
//			pRecordInfo - CRecordInfo to add the index info into
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingGetColumnIndexes( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo )
{
	// query the system management views for all of the indexes on this table
	static const char *pstrStatement = 
		"SELECT SI.Name AS INDEX_NAME, SC.Name AS COLUMN_NAME, SI.Type AS [TYPE], IS_INCLUDED_COLUMN, SIC.KEY_Ordinal AS [ORDINAL_POSITION], IS_UNIQUE, IS_PRIMARY_KEY, SI.INDEX_ID"
		"  FROM sys.indexes SI "
		"  JOIN sys.index_columns SIC"
		"		ON SIC.Object_id = SI.Object_Id"
		"			AND SIC.Index_ID = SI.Index_id"
		"  JOIN sys.objects SO"
		"		ON SIC.Object_ID = SO.Object_ID"
		"  JOIN sys.columns SC"
		"		ON SC.Object_ID = SO.Object_ID"
		"			AND SC.column_id = SIC.column_id"
		" WHERE SO.Object_ID = ? "
		"ORDER BY SIC.Index_id, SIC.Key_Ordinal ";
	CSQLAccess sqlAccess( eSchemaCatalog );
	sqlAccess.AddBindParam( pRecordInfo->GetTableID() );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, pstrStatement ) );

	// builds a list of columns in a particular index.
	CUtlVector<int> vecColumns;
	CUtlVector<int> vecIncluded;
	int nLastIndexID = -1;
	bool bIsClustered = false;
	bool bIsIndexUnique = false;
	bool bIsPrimaryKey = false;
	CFmtStr1024 sIndexName, sColumnName;

	FOR_EACH_SQL_RESULT( sqlAccess, 0, typeRecord )
	{
		// Starting a new index?
		int nIndexID;
		DbgVerify( typeRecord.BGetIntValue( 7, &nIndexID ) );
		if ( nIndexID != nLastIndexID )
		{
			// first column! is it our first time through?
			if ( vecColumns.Count() > 0 )
			{
				// yes. let's add what we had from the previous index
				// to the fieldset
				FieldSet_t fs( bIsIndexUnique, bIsClustered, vecColumns, sIndexName );
				fs.AddIncludedColumns( vecIncluded );
				int idx = pRecordInfo->AddIndex( fs );
				if ( bIsPrimaryKey )
				{
					Assert( bIsIndexUnique );
					Assert( pRecordInfo->GetPKIndex() < 0 );
					pRecordInfo->SetPKIndex( idx );
				}

				// reset the vector
				vecColumns.RemoveAll();
				vecIncluded.RemoveAll();
				bIsIndexUnique = false;
				bIsClustered = false;
				bIsPrimaryKey = false;
			}
			nLastIndexID = nIndexID;
		}	

		int nTypeID, nOrdinalPosition;
		bool bIsIncluded, bIsColumnUnique;

		DbgVerify( typeRecord.BGetStringValue( 0, &sIndexName ) );
		DbgVerify( typeRecord.BGetStringValue( 1, &sColumnName ) );
		DbgVerify( typeRecord.BGetIntValue( 2, &nTypeID ) );
		DbgVerify( typeRecord.BGetBoolValue( 3, &bIsIncluded ) );
		DbgVerify( typeRecord.BGetIntValue( 4, &nOrdinalPosition ) );
		DbgVerify( typeRecord.BGetBoolValue( 5, &bIsColumnUnique ) );
		DbgVerify( typeRecord.BGetBoolValue( 6, &bIsPrimaryKey ) );

		RETURN_SQL_ERROR_ON_FALSE( sColumnName.Length() > 0 );

		int nColumnIndexed = -1;
		RETURN_SQL_ERROR_ON_FALSE( pRecordInfo->BFindColumnByName( sColumnName, &nColumnIndexed ) );

		CColumnInfo & columnInfo = pRecordInfo->GetColumnInfo( nColumnIndexed );
		int nColFlags = 0;

		if ( bIsIncluded )
		{
			Assert( nOrdinalPosition == 0 );
			// it's included; no flags
			vecIncluded.AddToTail( nColumnIndexed );
		}
		else
		{
			Assert( nOrdinalPosition != 0 );
			if ( bIsPrimaryKey )
			{
				// if we're working a primary key, mark those flags
				nColFlags = k_nColFlagPrimaryKey | k_nColFlagIndexed | k_nColFlagUnique;
				// PKs are always unique
				bIsIndexUnique = true;
			}
			else
			{
				// if we're working a "regular" index, we need to know the uniqueness of the index ...

				nColFlags = k_nColFlagIndexed;
				if ( bIsColumnUnique )
				{
					nColFlags |= k_nColFlagUnique;
					bIsIndexUnique = true;
				}
			}

			// clustering type
			if ( nTypeID == SQL_INDEX_CLUSTERED )
			{
				nColFlags |= k_nColFlagClustered;
				bIsClustered = true;
			}
			columnInfo.SetColFlagBits( nColFlags );

			// add this column to our list for the index set
			vecColumns.AddToTail( nColumnIndexed );
		}
	}

	// anything left over?
	if ( vecColumns.Count() > 0 )
	{
		// yep, add that, too
		FieldSet_t fs( bIsIndexUnique, bIsClustered, vecColumns, sIndexName );
		fs.AddIncludedColumns( vecIncluded );
		int idx = pRecordInfo->AddIndex( fs );
		if ( bIsPrimaryKey )
		{
			Assert( bIsIndexUnique );
			Assert( pRecordInfo->GetPKIndex() < 0 );
			pRecordInfo->SetPKIndex( idx );
		}
	}

	return SQL_SUCCESS;
}

//-----------------------------------------------------------------------------
// Purpose: Finds the schema info on any FK constraints defined for the table
// Input:	pRecordInfo - CRecordInfo to add the index info into (and lookup table name out of)
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingGetTableFKConstraints( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo )
{
	// Used below, declared up here because of goto
	FKData_t fkData;

	CSQLAccess sqlAccess( eSchemaCatalog );
	const char *pchStatement =  "SELECT fk.name AS FKName, current_table.name AS TableName, parent_table.name AS ParentTableName, "
		"table_column.name AS ColName, parent_table_column.name AS ParentColName, "
		"fk.delete_referential_action_desc AS OnDelete, "
		"fk.update_referential_action_desc AS OnUpdate "
		"FROM sys.objects AS current_table "
		"JOIN sys.foreign_keys AS fk ON fk.parent_object_id=current_table.object_id AND fk.is_ms_shipped=0 "
		"JOIN sys.foreign_key_columns AS fk_col ON fk_col.constraint_object_id=fk.object_id "
		"JOIN sys.objects AS parent_table ON parent_table.object_id=fk_col.referenced_object_id "
		"JOIN sys.columns AS table_column ON table_column.object_id=fk_col.parent_object_id AND table_column.column_id=fk_col.parent_column_id "
		"JOIN sys.columns AS parent_table_column ON parent_table_column.object_id=fk_col.referenced_object_id AND parent_table_column.column_id=fk_col.referenced_column_id "
		"WHERE current_table.object_id = ? ORDER BY fk.name";
	sqlAccess.AddBindParam( pRecordInfo->GetTableID() );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, pchStatement ) );

	FOR_EACH_SQL_RESULT( sqlAccess, 0, sqlRecord )
	{
		// get all the data for the FK
		const char *pchFKName;
		DbgVerify( sqlRecord.BGetStringValue( 0, &pchFKName ) );

		const char *pchTableName;
		DbgVerify( sqlRecord.BGetStringValue( 1, &pchTableName ) );

		AssertMsg( Q_stricmp( pchTableName, pRecordInfo->GetName() ) == 0, "FOREIGN KEY schema conversion found FK for table not matching search!\n" );

		const char *pchParentTable;
		DbgVerify( sqlRecord.BGetStringValue( 2, &pchParentTable ) );

		const char *pchColName;
		DbgVerify( sqlRecord.BGetStringValue( 3, &pchColName ) );

		const char *pchParentColName;
		DbgVerify( sqlRecord.BGetStringValue( 4, &pchParentColName ) );

		const char *pchOnDelete;
		DbgVerify( sqlRecord.BGetStringValue( 5, &pchOnDelete ) );

		const char *pchOnUpdate;
		DbgVerify( sqlRecord.BGetStringValue( 6, &pchOnUpdate ) );

		// Is this more data for the FK we are already tracking?  If so just append the column data,
		// otherwise, assuming some data exists, add the key to the record info
		if ( Q_strcmp( fkData.m_rgchName, pchFKName ) == 0 )
		{
			int iColRelation = fkData.m_VecColumnRelations.AddToTail();
			FKColumnRelation_t &colRelation = fkData.m_VecColumnRelations[iColRelation];
			Q_strncpy( colRelation.m_rgchCol, pchColName, Q_ARRAYSIZE( colRelation.m_rgchCol ) );
			Q_strncpy( colRelation.m_rgchParentCol, pchParentColName, Q_ARRAYSIZE( colRelation.m_rgchParentCol ) );
		}
		else 
		{
			if ( Q_strlen( fkData.m_rgchName ) )
			{	
				pRecordInfo->AddFK( fkData  );
			}

			// Do initial setup of the new key
			Q_strncpy( fkData.m_rgchName, pchFKName, Q_ARRAYSIZE( fkData.m_rgchName ) );
			Q_strncpy( fkData.m_rgchParentTableName, pchParentTable, Q_ARRAYSIZE( fkData.m_rgchParentTableName ) );
			fkData.m_eOnDeleteAction = EForeignKeyActionFromName( pchOnDelete ); 
			fkData.m_eOnUpdateAction = EForeignKeyActionFromName( pchOnUpdate );
			int iColRelation = fkData.m_VecColumnRelations.AddToTail();
			FKColumnRelation_t &colRelation = fkData.m_VecColumnRelations[iColRelation];
			Q_strncpy( colRelation.m_rgchCol, pchColName, Q_ARRAYSIZE( colRelation.m_rgchCol ) );
			Q_strncpy( colRelation.m_rgchParentCol, pchParentColName, Q_ARRAYSIZE( colRelation.m_rgchParentCol ) );
		}
	}


	// Add the last key we were building data for
	if ( Q_strlen( fkData.m_rgchName ) )
	{
		pRecordInfo->AddFK( fkData  );
	}

	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: Creates a table in the DB to match the recordinfo
// Input:	pRecordInfo - CRecordInfo to create a table for
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingCreateTable( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo )
{
	CFmtStrMax sCmd;
	SQLRETURN nRet = 0;
	const char *pchName = pRecordInfo->GetName();
	Assert( pchName && pchName[0] );
	CSQLAccess sqlAccess( eSchemaCatalog );

	// build the create table command
	sCmd.sprintf( "CREATE TABLE %s (", pchName );

	// add all the columns
	for ( int iColumn = 0; iColumn < pRecordInfo->GetNumColumns(); iColumn++ )
	{
		char rgchType[k_cSmallBuff];
		const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
		char *pchType = SQLTypeFromField( columnInfo, rgchType, Q_ARRAYSIZE( rgchType ) );
		Assert( pchType[0] );

		// add the name and type of this column
		sCmd.AppendFormat( "%s %s", columnInfo.GetName(), pchType );

		// add any constraints
		AppendConstraints( pRecordInfo, &columnInfo, true, sCmd );

		if ( iColumn < ( pRecordInfo->GetNumColumns() - 1 ) )
			sCmd += ", ";
	}

	AppendTableConstraints( pRecordInfo, sCmd );
	sCmd += ")";


	// create the table
	// metadata operations aren't transactional, so we'll set bAutoTransaction = true
	EXIT_ON_BOOLSQL_FAILURE( sqlAccess.BYieldingExecute( FILE_AND_LINE, sCmd ) );

	// add any indexes
	for ( int n = 0; n < pRecordInfo->GetIndexFields( ).Count(); n++ )
	{
		const FieldSet_t& refSet = pRecordInfo->GetIndexFields( )[ n ];

		// already added the PK index, so skip it
		if ( n == pRecordInfo->GetPKIndex() )
			continue;

		// call YieldingAddIndex to do the work
		nRet = YieldingAddIndex( eSchemaCatalog, pRecordInfo, refSet );
		EXIT_ON_SQL_FAILURE( nRet );
	}

Exit:
	return nRet;
}

//-----------------------------------------------------------------------------
// Purpose: Adds an index of multiple columns to a table.
// Input:	pSQLConnection - connection to use for command
//			pRecordInfo - record info describing table
//			refFields - description of index to add
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingAddIndex( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo, const FieldSet_t &refFields )
{
	CSQLAccess sqlAccess( eSchemaCatalog );
	CUtlString sCmd = GetAddIndexSQL( pRecordInfo, refFields );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, sCmd ) );
	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: Depending on the conversion mode, either really perfom the
//          conversion, or just log the SQL text so a human being can review
//          it and do it later.
//-----------------------------------------------------------------------------

SQLRETURN CJobUpdateSchema::YieldingProcessUnsafeConversion( ESchemaCatalog eSchemaCatalog, const char *pszSQL, const char *pszComment )
{
	if ( k_EConversionModeConvertIrreversible != m_eConversionMode )
	{
		if ( pszComment )
		{
			m_sRecommendedSQL.Append( "-- " );
			m_sRecommendedSQL.Append( pszComment );
			m_sRecommendedSQL.Append( "\n" );
		}
		m_sRecommendedSQL.Append( pszSQL );
		m_sRecommendedSQL.Append("\nGO\n \n"); // that space is a kludge, because we are using V_splitlines, which will ignore consecutive seperators
		return SQL_SUCCESS;
	}
	CSQLAccess sqlAccess( eSchemaCatalog );
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, pszSQL ) );
	return SQL_SUCCESS;
}

//-----------------------------------------------------------------------------
// Purpose: Add a SQL command to cleanup the schema that will actually destroy (supposedly unused) data.
//-----------------------------------------------------------------------------

void CJobUpdateSchema::AddDataDestroyingConversion( const char *pszSQL, const char *pszComment )
{
	if ( pszComment )
	{
		m_sDataDestroyingCleanupSQL.Append( "-- " );
		m_sDataDestroyingCleanupSQL.Append( pszComment );
		m_sDataDestroyingCleanupSQL.Append( "\n" );
	}
	m_sDataDestroyingCleanupSQL.Append( pszSQL );
	m_sDataDestroyingCleanupSQL.Append("\nGO\n \n"); // that space is a kludge, because we are using V_splitlines, which will ignore consecutive seperators
}

//-----------------------------------------------------------------------------
// Purpose: Adds a column to a table
// Input:	pRecordInfo - record info describing table to add a column to
//			pColumnInfo - column info describing column to add
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingAlterTableAddColumn( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfo )
{
	CSQLAccess sqlAccess( eSchemaCatalog );
	CFmtStrMax sCmd;
	char rgchType[k_cSmallBuff];
	const char *pchTableName = pRecordInfo->GetName();
	Assert( pchTableName );
	const char *pchColumnName = pColumnInfo->GetName();
	Assert( pchColumnName );
	DbgVerify( SQLTypeFromField( *pColumnInfo, rgchType, Q_ARRAYSIZE( rgchType ) )[0] );

	// build the alter table command
	sCmd.sprintf( "ALTER TABLE %s ADD %s %s", pchTableName, pchColumnName, rgchType );

	// add any constraints
	AppendConstraints( pRecordInfo, pColumnInfo, true, sCmd );

	// !KLUDGE! This is guaranteed to fail if it has "not null" in it.
	if ( V_strstr( sCmd, "NOT NULL" ) )
	{
		EmitError( SPEW_SQL, "Cannot add column %s to table %s with NOT NULL constraint\n", pchColumnName, pchTableName );
		EmitInfo( SPEW_SQL, SPEW_ALWAYS, LOG_ALWAYS, "Here is the SQL that should be run to add the column:\n" );
		EmitInfo( SPEW_SQL, SPEW_ALWAYS, LOG_ALWAYS, "  %s\n", sCmd.String() );
		return SQL_ERROR;
	}

	// execute the command.
	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, sCmd ) );
	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: Adds a constraint to an existing column
// Input:	hDBC - SQL connection to execute on
//			pRecordInfo - record info describing table
//			pColumnInfo - column info describing column to add contraint for
//			nColFlagConstraint - constraint to add
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingAddConstraint( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfo, int nColFlagConstraint )
{
	Assert( pRecordInfo );
	Assert( pColumnInfo );

	CFmtStrMax sCmd;
	sCmd.sprintf( "ALTER TABLE App%u.%s ADD", GGCBase()->GetAppID(), pRecordInfo->GetName() );

	Assert( !pColumnInfo->BIsClustered() );
	AppendConstraint( pRecordInfo->GetName(), pColumnInfo->GetName(), nColFlagConstraint, true, pColumnInfo->BIsClustered(), sCmd, 0 );

	sCmd.AppendFormat( " (%s)", pColumnInfo->GetName() );

	return YieldingProcessUnsafeConversion( eSchemaCatalog, sCmd );
}


//-----------------------------------------------------------------------------
// Purpose: Changes type or length of existing column
// Input:	hDBC - SQL connection to execute on
//			pRecordInfo - record info describing table
//			pColumnInfoDesired - column info describing new column type or length
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingChangeColumnTypeOrLength( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfoDesired )
{
	CUtlString sCmd = GetAlterColumnText( pRecordInfo, pColumnInfoDesired );
	return YieldingProcessUnsafeConversion( eSchemaCatalog, sCmd );
}


//-----------------------------------------------------------------------------
// Purpose: Changes constraints/indexes on a column
// Input:	hDBC - SQL connection to execute on
//			pRecordInfo - record info describing table
//			pColumnInfoActual - column info describing existing column properties
//			pColumnInfoActual - column info describing desired new column properties
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingChangeColumnProperties( ESchemaCatalog eSchemaCatalog, CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfoActual, 
											 const CColumnInfo *pColumnInfoDesired )
{
	CSQLAccess sqlAccess( eSchemaCatalog );
	Assert( pRecordInfo );
	Assert( pColumnInfoActual );
	Assert( pColumnInfoDesired );

	SQLRETURN nRet = SQL_SUCCESS;

	pColumnInfoActual->ValidateColFlags();
	pColumnInfoDesired->ValidateColFlags();

	Assert( pColumnInfoActual->GetColFlags() != pColumnInfoDesired->GetColFlags() );

	// all the operations we might have to perform; note we have have to drop one
	// thing and add another
	bool bAddExplicitIndex = false, bRemoveExplicitIndex = false;
	bool bAddUniqueConstraint = false, bRemoveUniqueConstraint = false;
	bool bAddPrimaryKeyConstraint = false, bRemovePrimaryKeyConstraint = false;

	// determine which operations need to be performed
	if ( !pColumnInfoActual->BIsPrimaryKey() && pColumnInfoDesired->BIsPrimaryKey() )
	{
		bAddPrimaryKeyConstraint = true;
	}
	else if ( pColumnInfoActual->BIsPrimaryKey() && !pColumnInfoDesired->BIsPrimaryKey() )
	{
		bRemovePrimaryKeyConstraint = true;
	}

	if ( !pColumnInfoActual->BIsExplicitlyUnique() && pColumnInfoDesired->BIsExplicitlyUnique() )
	{
		bAddUniqueConstraint = true;
	}
	else if ( pColumnInfoActual->BIsExplicitlyUnique() && !pColumnInfoDesired->BIsExplicitlyUnique() )
	{
		bRemoveUniqueConstraint = true;
	}

	if ( !pColumnInfoActual->BIsExplicitlyIndexed() && pColumnInfoDesired->BIsExplicitlyIndexed() )
	{
		bAddExplicitIndex = true;
	}
	else if ( pColumnInfoActual->BIsExplicitlyIndexed() && !pColumnInfoDesired->BIsExplicitlyIndexed() )
	{
		bRemoveExplicitIndex = true;
	}

	// sanity check 
	Assert( !( bAddUniqueConstraint && bAddPrimaryKeyConstraint ) );	// primary key constraint adds implicit uniqueness constraint; it's a bug if we decide to do both
	Assert( !( bAddUniqueConstraint && bAddExplicitIndex ) );	// uniqueness constraint adds implicit index; it's a bug if we decide to do both
	Assert( !( bAddPrimaryKeyConstraint && bAddExplicitIndex ) );	// primary key constraint adds implicit index; it's a bug if we decide to do both

	// The index conversion stuff already handles dropping of unexpected indices
	// and primary key constraints.  I'm not even sure that this works or is used anymore.
	if ( bAddUniqueConstraint )
	{
		nRet = YieldingAddConstraint( eSchemaCatalog, pRecordInfo, pColumnInfoActual, k_nColFlagUnique );
		EXIT_ON_SQL_FAILURE( nRet );
	}

Exit:
	return nRet;
}




//-----------------------------------------------------------------------------
// Purpose: Creates specified trigger
// Input:	pSQLConnection - SQL connection to execute on
//			refTriggerInfo - trigger to be created
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingCreateTrigger( ESchemaCatalog eSchemaCatalog, CTriggerInfo &refTriggerInfo )
{
	char rgchCmd[ k_cchSQLStatementTextMax];
	CSQLAccess sqlAccess( eSchemaCatalog );

	// build the create command
	Q_snprintf( rgchCmd, Q_ARRAYSIZE( rgchCmd ),
		"CREATE TRIGGER [%s] ON [%s] "
		"%s "
		"AS BEGIN"
		"%s\n"
		"END",
		refTriggerInfo.m_szTriggerName,
		refTriggerInfo.m_szTriggerTableName,
		refTriggerInfo.GetTriggerTypeString(), 
		refTriggerInfo.m_strText.Get() );

	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, rgchCmd ) );
	return SQL_SUCCESS;
}


//-----------------------------------------------------------------------------
// Purpose: drops specified trigger
// Input:	pSQLConnection - SQL connection to execute on
//			refTriggerInfo - trigger to be dropped
// Output:  SQL return code.
//-----------------------------------------------------------------------------
SQLRETURN CJobUpdateSchema::YieldingDropTrigger( ESchemaCatalog eSchemaCatalog, CTriggerInfo &refTriggerInfo )
{
	char rgchCmd[ k_cchSQLStatementTextMax];
	CSQLAccess sqlAccess( eSchemaCatalog );

	// build the create command
	Q_snprintf( rgchCmd, Q_ARRAYSIZE( rgchCmd ),
		"DROP TRIGGER [%s];",
		refTriggerInfo.m_szTriggerName );

	RETURN_SQL_ERROR_ON_FALSE( sqlAccess.BYieldingExecute( FILE_AND_LINE, rgchCmd ) );
	return SQL_SUCCESS;
}



//-----------------------------------------------------------------------------
// Purpose: Used for SQL/EGCSQLType conversion
//-----------------------------------------------------------------------------
struct SQLTypeMapping_t
{
	const char *m_pchTypeName;
	EGCSQLType m_eType;
};

static SQLTypeMapping_t g_rSQLTypeMapping[] =
{
	{ "tinyint", k_EGCSQLType_int8 },
	{ "smallint", k_EGCSQLType_int16 },
	{ "int", k_EGCSQLType_int32 },
	{ "bigint", k_EGCSQLType_int64 },
	{ "real", k_EGCSQLType_float },
	{ "float", k_EGCSQLType_double },
	{ "text", k_EGCSQLType_String },
	{ "ntext", k_EGCSQLType_String },
	{ "char", k_EGCSQLType_String },
	{ "varchar", k_EGCSQLType_String },
	{ "nchar", k_EGCSQLType_String },
	{ "nvarchar", k_EGCSQLType_String },
	{ "sysname", k_EGCSQLType_String },
	{ "varbinary", k_EGCSQLType_Blob },
	{ "image", k_EGCSQLType_Image },
};
static uint32 g_cSQLTypeMapping = Q_ARRAYSIZE( g_rSQLTypeMapping );


//-----------------------------------------------------------------------------
// Purpose: Returns the EGCSQLType for the specified SQL type name (or Invalid 
//			for unsupported types.)
//-----------------------------------------------------------------------------
EGCSQLType ETypeFromMSSQLDataType( const char *pchType )
{
	for( uint32 unMapping = 0; unMapping < g_cSQLTypeMapping; unMapping++ )
	{
		if( !Q_stricmp( pchType, g_rSQLTypeMapping[ unMapping ].m_pchTypeName ) )
			return g_rSQLTypeMapping[ unMapping ].m_eType;
	}
	return k_EGCSQLTypeInvalid;
}


//-----------------------------------------------------------------------------
// Purpose: Prepares the type map for use in schema upgrades
//-----------------------------------------------------------------------------
bool CJobUpdateSchema::YieldingBuildTypeMap( ESchemaCatalog eSchemaCatalog )
{
	CSQLAccess sqlAccess( eSchemaCatalog );
	if( !sqlAccess.BYieldingExecute( FILE_AND_LINE, "select name, system_type_id from sys.types" ) )
		return false;

	FOR_EACH_SQL_RESULT( sqlAccess, 0, typeRecord )
	{
		CFmtStr1024 sTypeName;
		byte nTypeID;
		DbgVerify( typeRecord.BGetStringValue( 0, &sTypeName ) );
		DbgVerify( typeRecord.BGetByteValue( 1, &nTypeID ) );

		EGCSQLType eType = ETypeFromMSSQLDataType( sTypeName );
		if( eType != k_EGCSQLTypeInvalid )
			m_mapSQLTypeToEType.Insert( nTypeID, eType );
	}
	return true;
}


//-----------------------------------------------------------------------------
// Purpose: Returns the EGCSQLType for the specified type ID out of the database
//-----------------------------------------------------------------------------
EGCSQLType CJobUpdateSchema::GetEGCSQLTypeForMSSQLType( int nType )
{
	int nIndex = m_mapSQLTypeToEType.Find( nType );
	if( m_mapSQLTypeToEType.IsValidIndex( nIndex ) )
		return m_mapSQLTypeToEType[ nIndex ];
	else
		return k_EGCSQLTypeInvalid;
}

} // namespace GCSDK