summaryrefslogtreecommitdiff
path: root/gcsdk/sqlaccess/sqlutil.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'gcsdk/sqlaccess/sqlutil.cpp')
-rw-r--r--gcsdk/sqlaccess/sqlutil.cpp918
1 files changed, 918 insertions, 0 deletions
diff --git a/gcsdk/sqlaccess/sqlutil.cpp b/gcsdk/sqlaccess/sqlutil.cpp
new file mode 100644
index 0000000..6744d21
--- /dev/null
+++ b/gcsdk/sqlaccess/sqlutil.cpp
@@ -0,0 +1,918 @@
+//========= Copyright Valve Corporation, All rights reserved. ============//
+//
+// Purpose:
+//
+// $NoKeywords: $
+//=============================================================================
+
+
+#include "stdafx.h"
+
+// memdbgon must be the last include file in a .cpp file!!!
+#include "tier0/memdbgon.h"
+
+namespace GCSDK
+{
+const char *GetInsertArgString()
+{
+ static char s_str[1024];
+ static bool s_bInit = false;
+
+ if ( !s_bInit )
+ {
+ for ( int i = 0; i < 1023; i++ )
+ {
+ s_str[i] = i % 2 == 0 ? '?' : ',';
+ }
+
+ s_str[1023] = NULL;
+ s_bInit = true;
+ }
+
+ return s_str;
+}
+
+uint32 GetInsertArgStringChars( uint32 nNumParams )
+{
+ AssertMsg( nNumParams <= GetInsertArgStringMaxParams(), "Error: Requested more characters than are provided by the GetInsertArgString" );
+ if( nNumParams == 0 )
+ return 0;
+
+ return nNumParams * 2 - 1;
+}
+
+uint32 GetInsertArgStringMaxParams()
+{
+ return 512;
+}
+
+//-----------------------------------------------------------------------------
+// Purpose: Converts array of field data to text for SQL IN clause
+// Input: columnInfo - schema of column being converted
+// pubData - pointer to array of data to convert
+// cubData - size of array of data
+// rgchResult - pointer to output buffer
+// cubResultLen - size of output buffer
+// bForPreparedStatement - Should we prepare the text for a prepared statement or directly place the values?
+//-----------------------------------------------------------------------------
+void ConvertFieldArrayToInText( const CColumnInfo &columnInfo, byte *pubData, int cubData, char *rgchResult, int cubResultLen, bool bForPreparedStatement )
+{
+ int32 cubLength = columnInfo.GetFixedSize();
+ Assert( cubData % cubLength == 0 );
+ int32 nArrayCount = cubData / cubLength;
+
+ int32 len = 0;
+ rgchResult[len++] = '(';
+ for( int i = 0; i < nArrayCount; ++i )
+ {
+ if ( bForPreparedStatement )
+ {
+ if ( i < nArrayCount - 1 )
+ {
+ rgchResult[len++] = '?';
+ rgchResult[len++] = ',';
+ }
+ else
+ {
+ rgchResult[len++] = '?';
+ rgchResult[len++] = ')';
+ }
+ }
+ else
+ {
+ switch ( columnInfo.GetType() )
+ {
+ case k_EGCSQLType_int8:
+ if ( i < nArrayCount - 1 )
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d,", *( (byte *) pubData ) );
+ else
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d)", *( (byte *) pubData ) );
+ break;
+ case k_EGCSQLType_int16:
+ if ( i < nArrayCount - 1 )
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d,", *( (short *) pubData ) );
+ else
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d)", *( (short *) pubData ) );
+ break;
+ case k_EGCSQLType_int32:
+ if ( i < nArrayCount - 1 )
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d,", *( (int *) pubData ) );
+ else
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%d)", *( (int *) pubData ) );
+ break;
+ case k_EGCSQLType_int64:
+ if ( i < nArrayCount - 1 )
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%lld,", *( (int64 *) pubData ) );
+ else
+ len += Q_snprintf( rgchResult + len, cubResultLen - len, "%lld)", *( (int64 *) pubData ) );
+ break;
+ default:
+ AssertMsg( false, "Unsupported data type for non prepares statement with IN clause\n" );
+ rgchResult[0] = 0;
+ return;
+ }
+ }
+
+ if( len >= cubResultLen - 1 )
+ {
+ AssertMsg( false, "Generation of IN clause foverflowed\n" );
+ rgchResult[0] = 0;
+ return;
+ }
+ pubData += cubLength;
+ }
+
+ rgchResult[len] = 0;
+ return;
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Converts field data to text equivalent for SQL statement
+// Input: eFieldType - The type of the field to convert to text
+// pubRecord - pointer to record data to convert
+// cubRecord - size of record data
+// rgchField - pointer to output buffer
+// cchField - size of output buffer
+//-----------------------------------------------------------------------------
+void ConvertFieldToText( EGCSQLType eFieldType, uint8 *pubRecord, int cubRecord, char *rgchField, int cchField, bool bQuoteString )
+{
+ char rgchTmp[k_cMedBuff];
+
+ switch ( eFieldType )
+ {
+ case k_EGCSQLType_int8:
+ Q_snprintf( rgchField, cchField, "%d", *( (byte *) pubRecord ) );
+ break;
+ case k_EGCSQLType_int16:
+ Q_snprintf( rgchField, cchField, "%d", *( (short *) pubRecord ) );
+ break;
+ case k_EGCSQLType_int32:
+ Q_snprintf( rgchField, cchField, "%d", *( (int *) pubRecord ) );
+ break;
+ case k_EGCSQLType_int64:
+ Q_snprintf( rgchField, cchField, "%lld", *( (int64 *) pubRecord ) );
+ break;
+ case k_EGCSQLType_float:
+ Q_snprintf( rgchField, cchField, "%f", *((float*) pubRecord) );
+ break;
+ case k_EGCSQLType_double:
+ Q_snprintf( rgchField, cchField, "%f", *((double*) pubRecord) );
+ break;
+ case k_EGCSQLType_String:
+ if ( pubRecord && *pubRecord )
+ {
+ Assert( cubRecord + 1 < Q_ARRAYSIZE( rgchTmp ) );
+
+ Q_memcpy( rgchTmp, (char *) pubRecord, cubRecord );
+ rgchTmp[cubRecord] = 0;
+
+ if ( bQuoteString )
+ {
+ EscapeStringValue( rgchTmp, Q_ARRAYSIZE( rgchTmp ) );
+ Q_snprintf( rgchField, cchField, "'%s'", rgchTmp );
+ }
+ else
+ {
+ Q_strncpy( rgchField, rgchTmp, cchField );
+ }
+ }
+ else
+ {
+ if ( bQuoteString )
+ {
+ Q_strncpy( rgchField, "''", cchField );
+ }
+ else
+ {
+ Q_strncpy( rgchField, "", cchField );
+ }
+ }
+ break;
+ case k_EGCSQLType_Blob:
+ case k_EGCSQLType_Image:
+ Q_strncpy( rgchField, "0x", cchField );
+ Q_binarytohex( pubRecord, cubRecord, rgchField + 2, cchField - 2 );
+ break;
+ default:
+ Assert( false );
+ break;
+ }
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Returns the text SQL type for a given field
+// Input: field - field to determine type for
+// pchBuf - pointer to output buffer
+// cchBuf - size of output buffer
+// Output: returns pchBuf for convenience of one-line usage
+//-----------------------------------------------------------------------------
+char *SQLTypeFromField( const CColumnInfo &colInfo, char *pchBuf, int cchBuf )
+{
+ EGCSQLType eType = colInfo.GetType();
+ *pchBuf = 0;
+ switch ( eType )
+ {
+ case k_EGCSQLType_int8:
+ Q_strncpy( pchBuf, "TINYINT", cchBuf );
+ break;
+ case k_EGCSQLType_int16:
+ Q_strncpy( pchBuf, "SMALLINT", cchBuf );
+ break;
+ case k_EGCSQLType_int32:
+ Q_strncpy( pchBuf, "INT", cchBuf );
+ break;
+ case k_EGCSQLType_int64:
+ Q_strncpy( pchBuf, "BIGINT", cchBuf );
+ break;
+ case k_EGCSQLType_float:
+ Q_strncpy( pchBuf, "REAL", cchBuf );
+ break;
+ case k_EGCSQLType_double:
+ Q_strncpy( pchBuf, "FLOAT", cchBuf );
+ break;
+ case k_EGCSQLType_String:
+ Q_snprintf( pchBuf, cchBuf, "VARCHAR(%d)", colInfo.GetMaxSize() );
+ break;
+ case k_EGCSQLType_Blob:
+ Q_snprintf( pchBuf, cchBuf, "VARBINARY(%d)", colInfo.GetMaxSize() );
+ break;
+ case k_EGCSQLType_Image:
+ Q_strncpy( pchBuf, "IMAGE", cchBuf );
+ break;
+ default:
+ Assert( false );
+ break;
+ }
+
+ return pchBuf;
+}
+
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Escapes any single quotes to a string value to double single quotes
+// Input: rgchField - text to escape
+// cchField - size of text buffer
+// Notes: The text will be escaped and expanded in place in the buffer.
+// In the worst case, the text may expand by 2x. (If the field is all
+// single quotes.) So, you must pass in a buffer which is at least
+// twice as long as the text length so we can guarantee to be able to
+// escape the string.
+//-----------------------------------------------------------------------------
+void EscapeStringValue( char *rgchField, int cchField )
+{
+ // TODO - what else do we need to escape? %() ...
+ char *pubCur = rgchField;
+ int nLen = 0;
+ int cSingleQuotes = 0;
+
+ // This function gets called on every text field we write but most text fields
+ // don't need to be escaped, so try to be as fast as possible in the normal case.
+
+ // first, walk through the string and count the string length and number of single quotes
+ while ( *pubCur )
+ {
+ if ( '\'' == *pubCur )
+ cSingleQuotes++;
+ nLen ++;
+ pubCur++;
+ }
+
+ // if no single quotes, nothing to do
+ if ( !cSingleQuotes )
+ return;
+
+ // caller must pass in a buffer that's long enough for expansion
+ Assert( nLen + cSingleQuotes + 1 <= cchField );
+ if ( !( nLen + cSingleQuotes + 1 <= cchField ) )
+ return;
+
+ // We know exactly how many characters the string will expand by (the # of single quotes). Walk backward
+ // and copy the characters into the right places. This touches each character only once.
+ pubCur = rgchField + nLen + cSingleQuotes;
+ *pubCur = 0;
+ pubCur--;
+ while ( pubCur > rgchField && cSingleQuotes > 0 )
+ {
+ // read pointer is offset from write pointer by # of remaining single quotes
+ char *pubRead = pubCur - cSingleQuotes;
+ Assert( pubRead >= rgchField );
+ // copy each character
+ *pubCur = *pubRead;
+ if ( '\'' == *pubRead )
+ {
+ // if the character is a single quote, back up one more and insert another single quote to escape it
+ pubCur --;
+ *pubCur = '\'';
+ // decrement # of single quotes remaining
+ cSingleQuotes --;
+ Assert( cSingleQuotes >= 0 );
+ }
+ pubCur--;
+ }
+}
+//-----------------------------------------------------------------------------
+// Purpose: Adds constraint information to a SQL command to add or remove constraint
+// Input: pchTableName - name of table
+// pchColumnName - name of column
+// nColFlagConstraint - flag with which constraint to
+// bForAdd - whether constraint is being added or removed
+// pchCmd - buffer to append SQL command to
+// cchCmd - size of buffer
+//-----------------------------------------------------------------------------
+void AppendConstraint( const char *pchTableName, const char *pchColumnName, int nColFlagConstraint, bool bForAdd,
+ bool bClustered, CFmtStrMax & sCmd, int nFillFactor )
+{
+ Assert( pchTableName && pchTableName[0] );
+ Assert( pchColumnName && pchColumnName[0] );
+
+ switch ( nColFlagConstraint )
+ {
+ case k_nColFlagPrimaryKey:
+ sCmd.AppendFormat( " CONSTRAINT %s_%s_PrimaryKey", pchTableName, pchColumnName);
+ if ( bForAdd )
+ {
+ sCmd += " PRIMARY KEY ";
+ if ( bClustered )
+ {
+ sCmd.AppendFormat( " CLUSTERED WITH (FILLFACTOR = %d) ", nFillFactor );
+ }
+ else
+ {
+ sCmd += "NONCLUSTERED";
+ }
+ }
+ break;
+ case k_nColFlagUnique:
+ /* do nothing - the uniqueness will be handled by creation of an index */
+ break;
+ case k_nColFlagAutoIncrement:
+ sCmd += " IDENTITY";
+ break;
+ default:
+ AssertMsg( false, "CSQLThread::AppendContraint: invalid constraint type" );
+ break;
+ }
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Adds constraint information to a SQL command to add or remove constraint
+// Input: pRecordInfo - record info describing table
+// pColumnInfo - record info describing column
+// bForAdd - whether constraint is being added or removed
+// pchCmd - buffer to append SQL command to
+// cchCmd - size of buffer
+//-----------------------------------------------------------------------------
+void AppendConstraints( const CRecordInfo *pRecordInfo, const CColumnInfo *pColumnInfo, bool bForAdd, CFmtStrMax & sCmd )
+{
+ Assert( pRecordInfo != NULL );
+ Assert( pColumnInfo != NULL );
+
+ if ( pColumnInfo->BIsPrimaryKey() )
+ {
+ // any column in a PK can't be NULL.
+ if ( bForAdd )
+ {
+ sCmd += " NOT NULL";
+ }
+
+ // only add primary key constraint here if it is a single-column PK
+ if ( pRecordInfo->GetPrimaryKeyType() == k_EPrimaryKeyTypeSingle )
+ {
+ // get the fields on the primary key
+ const CUtlVector< FieldSet_t > &refFields = pRecordInfo->GetIndexFields( );
+ int nFillFactor = refFields.Element( pRecordInfo->GetPKIndex() ).GetFillFactor();
+ AppendConstraint( pRecordInfo->GetName(), pColumnInfo->GetName(), k_nColFlagPrimaryKey, bForAdd, pColumnInfo->BIsClustered(), sCmd, nFillFactor );
+ }
+ }
+ else if ( pColumnInfo->BIsUnique() )
+ {
+ AppendConstraint( pRecordInfo->GetName(), pColumnInfo->GetName(), k_nColFlagUnique, bForAdd, pColumnInfo->BIsClustered(), sCmd, 0 );
+ }
+
+ if ( pColumnInfo->BIsAutoIncrement() )
+ {
+ AppendConstraint( pRecordInfo->GetName(), pColumnInfo->GetName(), k_nColFlagAutoIncrement, bForAdd, pColumnInfo->BIsClustered(), sCmd, 0 );
+ }
+}
+
+//-----------------------------------------------------------------------------
+// Purpose: Generates the "CONSTRAINT ..." text for the table primary key
+//-----------------------------------------------------------------------------
+void BuildTablePKConstraintText( TSQLCmdStr *psStatement, CRecordInfo *pRecordInfo )
+{
+ const FieldSet_t& vecFields = pRecordInfo->GetPKFields( );
+
+ psStatement->sprintf( "CONSTRAINT %s_PrimaryKey PRIMARY KEY %s ( ",
+ pRecordInfo->GetName(),
+ vecFields.IsClustered() ? "CLUSTERED" : "NONCLUSTERED" );
+
+ for ( int nField = 0; nField < vecFields.GetCount(); nField++ )
+ {
+ // what field is the next column in our index?
+ int nThisField = vecFields.GetField( nField );
+ const CColumnInfo& columnInfo = pRecordInfo->GetColumnInfo(nThisField);
+
+ if (nField != 0)
+ {
+ *psStatement += ", ";
+ }
+ *psStatement += columnInfo.GetName();
+ }
+
+ // close our list
+ *psStatement += ") ";
+
+ if ( vecFields.GetFillFactor() != 0 )
+ {
+ // non-default fill factor, so specify it
+ psStatement->AppendFormat( " WITH FILLFACTOR = %d ",
+ vecFields.GetFillFactor() );
+ }
+}
+
+//-----------------------------------------------------------------------------
+// Purpose: Adds constraint information to a SQL command to add or remove table-level constraints
+// Input: pRecordInfo - record info describing table
+// pchCmd - buffer to append SQL command to
+// cchCmd - size of buffer
+//-----------------------------------------------------------------------------
+
+void AppendTableConstraints( CRecordInfo *pRecordInfo, CFmtStrMax & sCmd )
+{
+ // the only supported table constraint is for PKs or FKs
+ if ( pRecordInfo->GetPrimaryKeyType() == k_EPrimaryKeyTypeMulti )
+ {
+ TSQLCmdStr tmp;
+ BuildTablePKConstraintText( &tmp, pRecordInfo );
+ sCmd += ", ";
+ sCmd += tmp;
+ }
+
+ // Look for FKs required on this table
+ // the only supported table constraint is for PKs or FKs
+ int cFKs = pRecordInfo->GetFKCount();
+ for( int i=0; i < cFKs; ++i )
+ {
+ FKData_t &fkData = pRecordInfo->GetFKData( i );
+
+ CFmtStr sColumns, sParentColumns;
+ FOR_EACH_VEC( fkData.m_VecColumnRelations, nCol )
+ {
+ FKColumnRelation_t &colRelation = fkData.m_VecColumnRelations[nCol];
+ if ( nCol > 0)
+ {
+ sColumns += ",";
+ sParentColumns += ",";
+ }
+ sColumns += colRelation.m_rgchCol;
+ sParentColumns += colRelation.m_rgchParentCol;
+ }
+
+ TSQLCmdStr sTmp;
+ sTmp.sprintf( ", CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) ON DELETE %s ON UPDATE %s",
+ fkData.m_rgchName, sColumns.Access(), fkData.m_rgchParentTableName, sParentColumns.Access(),
+ PchNameFromEForeignKeyAction( fkData.m_eOnDeleteAction ), PchNameFromEForeignKeyAction( fkData.m_eOnUpdateAction ) );
+
+ // add to the command
+ sCmd += sTmp;
+ }
+}
+
+
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL INSERT statement
+// Input: psStatement - The string to put the statement into
+// pRecordInfo - record info describing table inserting into
+//-----------------------------------------------------------------------------
+void BuildInsertStatementText( TSQLCmdStr *psStatement, const CRecordInfo *pRecordInfo )
+{
+ psStatement->sprintf("INSERT INTO %s.%s (", GSchemaFull().GetDefaultSchemaNameForCatalog( pRecordInfo->GetESchemaCatalog() ), pRecordInfo->GetName() );
+
+ // build a string of the field names
+ int cColumns = pRecordInfo->GetNumColumns();
+ int nInsertable = 0;
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ nInsertable++;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ }
+
+ psStatement->AppendFormat( ") VALUES (%.*s)", GetInsertArgStringChars( nInsertable ), GetInsertArgString() );
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL INSERT statement
+// IMPORTANT NOTE - This Insert statement will use the Microsoft SQL Server
+// specific clause 'OUTPUT Inserted.ColumnName'
+// The result of that will be that the SQL statement will return to us
+// the columns that could not be specified by the Insert.
+// At the time of writing, that is primarily AutoIncrement columns,
+// however in theory we should be able to recover any computed column
+// from SQL server, with the caveats specified at :
+// http://msdn.microsoft.com/en-us/library/ms177564.aspx
+//
+// Input: psStatement - The output statement string
+// pRecordInfo - record info describing table inserting into
+//-----------------------------------------------------------------------------
+
+void BuildInsertAndReadStatementText( TSQLCmdStr *psStatement, CUtlVector<int> *pvecOutputFields, const CRecordInfo *pRecordInfo )
+{
+ psStatement->sprintf("INSERT INTO %s.%s (", GSchemaFull().GetDefaultSchemaNameForCatalog( pRecordInfo->GetESchemaCatalog() ), pRecordInfo->GetName() );
+
+ // build a string of the field names
+ int nInsertable = 0;
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ nInsertable++;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ }
+
+ bAddedBefore = false ;
+ int nOutputColumn = 0;
+ for( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn ) ;
+
+ //
+ // If we can't Insert it - we want SQL Server to tell us what value was stored
+ // in the column !!
+ //
+ if( !columnInfo.BIsInsertable() )
+ {
+ if( bAddedBefore )
+ psStatement->Append( ", INSERTED." );
+ else
+ psStatement->Append( ") OUTPUT INSERTED." );
+ bAddedBefore = true ;
+ psStatement->Append( columnInfo.GetName() );
+ pvecOutputFields->AddToTail( iColumn );
+ nOutputColumn++;
+ }
+ }
+
+ // add field values to SQL statement
+ psStatement->AppendFormat( " VALUES (%.*s)", GetInsertArgStringChars( nInsertable ), GetInsertArgString() );
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL MERGE statement update or insert using in-flight values table
+// Input: psStatement - The string to put the statement into
+// pRecordInfo - record info describing table inserting into
+//-----------------------------------------------------------------------------
+void BuildMergeStatementTextOnPKWhenMatchedUpdateWhenNotMatchedInsert( TSQLCmdStr *psStatement, const CRecordInfo *pRecordInfo )
+{
+ psStatement->sprintf( "MERGE INTO %s.%s WITH( HOLDLOCK, ROWLOCK ) T USING ( VALUES (%.*s) ) AS S(",
+ GSchemaFull().GetDefaultSchemaNameForCatalog( pRecordInfo->GetESchemaCatalog() ), pRecordInfo->GetName(),
+ GetInsertArgStringChars( pRecordInfo->GetNumColumns() ), GetInsertArgString() );
+
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( iColumn )
+ psStatement->Append( ',' );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( ") ON " );
+
+ // build a string of the PK columns
+ const FieldSet_t &fsPK = pRecordInfo->GetIndexFields()[pRecordInfo->GetPKIndex()];
+ {
+ int cColumns = fsPK.GetCount();
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( fsPK.GetField( iColumn ) );
+ if ( iColumn )
+ psStatement->Append( " AND " );
+ psStatement->Append( "T." );
+ psStatement->Append( columnInfo.GetName() );
+ psStatement->Append( "=S." );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( " WHEN MATCHED THEN UPDATE SET " );
+
+ // build the update string
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ bool bThisColumnIsPartOfPK = false;
+ for ( int ipkCheck = 0; ipkCheck < fsPK.GetCount(); ++ipkCheck )
+ {
+ if ( iColumn == fsPK.GetField( ipkCheck ) )
+ {
+ bThisColumnIsPartOfPK = true;
+ break;
+ }
+ }
+ if ( bThisColumnIsPartOfPK )
+ continue;
+
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ psStatement->Append( "=S." );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( " WHEN NOT MATCHED BY TARGET THEN INSERT (" );
+
+ // build a string of the field names
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( ") VALUES (" );
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( "S." );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+ psStatement->Append( ");" );
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL MERGE statement using CTE_MergeParams as supplied table holding rows
+// Input: psStatement - The string to put the statement into
+// pRecordInfo - record info describing table inserting into
+//-----------------------------------------------------------------------------
+void BuildMergeStatementTextOnPKWhenNotMatchedInsert( TSQLCmdStr *psStatement, const CRecordInfo *pRecordInfo )
+{
+ psStatement->sprintf( "MERGE INTO %s.%s WITH( HOLDLOCK, ROWLOCK ) T USING ( VALUES (%.*s) ) AS S(",
+ GSchemaFull().GetDefaultSchemaNameForCatalog( pRecordInfo->GetESchemaCatalog() ), pRecordInfo->GetName(),
+ GetInsertArgStringChars( pRecordInfo->GetNumColumns() ), GetInsertArgString() );
+
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( iColumn )
+ psStatement->Append( ',' );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( ") ON " );
+
+ // build a string of the PK columns
+ const FieldSet_t &fsPK = pRecordInfo->GetIndexFields()[pRecordInfo->GetPKIndex()];
+ {
+ int cColumns = fsPK.GetCount();
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( fsPK.GetField( iColumn ) );
+ if ( iColumn )
+ psStatement->Append( " AND " );
+ psStatement->Append( "T." );
+ psStatement->Append( columnInfo.GetName() );
+ psStatement->Append( "=S." );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( " WHEN NOT MATCHED BY TARGET THEN INSERT (" );
+
+ // build a string of the field names
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+
+ psStatement->Append( ") VALUES (" );
+ {
+ int cColumns = pRecordInfo->GetNumColumns();
+ bool bAddedBefore = false;
+ for ( int iColumn = 0; iColumn < cColumns; iColumn++ )
+ {
+ const CColumnInfo &columnInfo = pRecordInfo->GetColumnInfo( iColumn );
+ if ( !columnInfo.BIsInsertable() )
+ continue;
+
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( "S." );
+ psStatement->Append( columnInfo.GetName() );
+ }
+ }
+ psStatement->Append( ");" );
+}
+
+void BuildSelectStatementText( TSQLCmdStr *psStatement, const CColumnSet & selectSet, const char *pchTopClause )
+{
+ *psStatement = "SELECT ";
+
+ if( pchTopClause )
+ {
+ psStatement->Append( pchTopClause );
+ psStatement->Append( ' ' );
+ }
+
+ // build a string of the field names
+ bool bAddedBefore = false;
+ FOR_EACH_COLUMN_IN_SET( selectSet, nColumnIndex )
+ {
+ const CColumnInfo &columnInfo = selectSet.GetColumnInfo( nColumnIndex );
+ if ( bAddedBefore )
+ psStatement->Append( ',' );
+ bAddedBefore = true;
+ psStatement->Append( columnInfo.GetName() );
+ }
+
+ psStatement->Append( " FROM ");
+ psStatement->Append( GSchemaFull().GetDefaultSchemaNameForCatalog( selectSet.GetRecordInfo()->GetESchemaCatalog() ) );
+ psStatement->Append( '.' );
+ psStatement->Append( selectSet.GetRecordInfo()->GetName() );
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL UPDATE statement
+// Input: pRecordInfo - record info describing table inserting into
+// bForPreparedStatement - if true, inserts values as '?' for later
+// binding. If false, values are inserted in text.
+// pchStatement - pointer to buffer to build statement in
+// cchStatement - size of buffer
+// pSQLRecord - pointer to record with data to update
+// iColumnMatch - column to use for WHERE condition
+// pvMatch - data value to use for WHERE condition
+// cubMatch - size of pvMatch data
+// rgiColumnUpdate - array of column #'s to update
+// ciColumnUpdate - count of column #'s to update
+//-----------------------------------------------------------------------------
+void BuildUpdateStatementText( TSQLCmdStr *psStatement, const CColumnSet & updateColumns )
+{
+ // build the UPDATE statement
+ psStatement->sprintf( "UPDATE %s.%s SET ", GSchemaFull().GetDefaultSchemaNameForCatalog( updateColumns.GetRecordInfo()->GetESchemaCatalog() ), updateColumns.GetRecordInfo()->GetName() );
+
+ // add each field we're updating to the UPDATE statement
+ FOR_EACH_COLUMN_IN_SET( updateColumns, nColumnIndex )
+ {
+ const CColumnInfo &columnInfo = updateColumns.GetColumnInfo( nColumnIndex );
+
+ if( nColumnIndex > 0 )
+ psStatement->Append( ',' );
+ psStatement->Append( columnInfo.GetName() );
+ psStatement->Append( "=?" );
+ }
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a SQL UPDATE statement
+//-----------------------------------------------------------------------------
+void BuildDeleteStatementText( TSQLCmdStr *psStatement, const CRecordInfo *pRecordInfo )
+{
+ psStatement->sprintf( "DELETE FROM %s.%s", GSchemaFull().GetDefaultSchemaNameForCatalog( pRecordInfo->GetESchemaCatalog() ), pRecordInfo->GetName() );
+}
+
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds a where clause for the provided fields
+//-----------------------------------------------------------------------------
+void AppendWhereClauseText( TSQLCmdStr *psClause, const CColumnSet & columnSet )
+{
+ // add each field we're updating to the UPDATE statement
+ FOR_EACH_COLUMN_IN_SET( columnSet, nColumnIndex )
+ {
+ const CColumnInfo &columnInfo = columnSet.GetColumnInfo( nColumnIndex );
+
+ if( nColumnIndex > 0 )
+ psClause->Append( " AND ");
+ psClause->Append( columnInfo.GetName() );
+ psClause->Append( "=?" );
+ }
+}
+
+//-----------------------------------------------------------------------------
+// Purpose: Builds an OUTPUT [fields] INTO [table] for the provided fields/data
+//-----------------------------------------------------------------------------
+void BuildOutputClauseText( TSQLCmdStr *psClause, const CColumnSet & columnSet )
+{
+ *psClause = " OUTPUT ";
+
+ FOR_EACH_COLUMN_IN_SET( columnSet, nColumnIndex )
+ {
+ const CColumnInfo &columnInfo = columnSet.GetColumnInfo( nColumnIndex );
+
+ if( nColumnIndex > 0 )
+ psClause->Append( ", ");
+
+ psClause->Append( " ? AS " );
+ psClause->Append( columnInfo.GetName() );
+ }
+
+ psClause->Append( " INTO " );
+ psClause->Append( columnSet.GetRecordInfo()->GetName() );
+}
+
+////-----------------------------------------------------------------------------
+//// Purpose: our own special "upsert" into a column with a uniqueness constraint
+////-----------------------------------------------------------------------------
+//EResult UpdateOrInsertUnique( CSQLAccess &sqlAccess, int iTable, int iField, CRecordBase *pRecordBase, int iIndexID )
+//{
+// // attempt an update - if it fails due to duplicate primary key, they can't use this
+// // url (it's taken) - if it succeeds but affects 0 rows, they didn't have a vanity url
+// // and we need to do an insert (which could again fail due to primary key constraints)
+// int cRecordsUpdated = 0;
+// bool bRet = sqlAccess.BYieldingUpdateFieldFromRecordWithIndex( iTable, &cRecordsUpdated, iField, pRecordBase, iIndexID );
+// if ( !bRet )
+// {
+// // ODBC is the suck - give me Spring JDBC templates, please.
+// if ( sqlAccess.GetLastError()->IsDuplicateInsertAttempt() )
+// {
+// return k_EResultDuplicateName;
+// }
+// return k_EResultFail;
+// }
+// else if ( 0 == cRecordsUpdated )
+// {
+// // the user didn't have an entry, so insert one.
+// bRet = sqlAccess.BYieldingInsertRecord( iTable, pRecordBase );
+// if ( !bRet )
+// {
+// // ODBC is the suck - give me Spring JDBC templates, please.
+// if ( sqlAccess.GetLastError()->IsDuplicateInsertAttempt() )
+// {
+// return k_EResultDuplicateName;
+// }
+// return k_EResultFail;
+// }
+// }
+// return k_EResultOK;
+//}
+//
+
+} // namespace GCSDK
generated by cgit v1.2.3 (git 2.25.1) at 2026-06-06 21:44:25 +0000