//========= 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 ON // (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 vecPRecordInfoDesiredMissing; CUtlVector vecPRecordInfoActualDifferent; CUtlVector 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 vecPColumnInfoDesiredMissing; CUtlVector vecPColumnInfoActualDifferent; CUtlVector 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 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 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 vecColumns; CUtlVector 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