1697 lines
64 KiB
C++
1697 lines
64 KiB
C++
//========= 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
|