borisdj
commented
2 years ago PR Merged and publish 6.3.2 Also I have improved CountUnique query to include PK besides explicit Unique constrains only, and added in Test Bulk call for Composite PKs. Thx for Contrib.
The following code in sqlquerybuilderpostressql.cs
public static string CountUniqueConstrain(TableInfo tableInfo) { List list = tableInfo.PrimaryKeysPropertyColumnNameDict.Values.ToList();
DefaultInterpolatedStringHandler interpolatedStringHandler = new DefaultInterpolatedStringHandler(220, 1);
interpolatedStringHandler.AppendLiteral("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ");
interpolatedStringHandler.AppendLiteral("INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ");
interpolatedStringHandler.AppendLiteral("ON cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME ");
interpolatedStringHandler.AppendLiteral("WHERE tc.CONSTRAINT_TYPE = 'UNIQUE' ");
interpolatedStringHandler.AppendLiteral("AND tc.TABLE_NAME = '");
interpolatedStringHandler.AppendFormatted(tableInfo.TableName);
interpolatedStringHandler.AppendLiteral("' ");
string str1 = interpolatedStringHandler.ToStringAndClear();
foreach (string str2 in list)
str1 = str1 + "AND cu.COLUMN_NAME = '" + str2 + "' ";
return str1;
}
generates the following sql issue is that there will be 2 rows for it and when the filter is AND between the 2 columns it never produces any result. Suggested solution is already under, please look at this fast as i realy need this to work :)
select count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'UNIQUE' AND tc.TABLE_NAME = 'AvailableInstruments' AND cu.COLUMN_NAME = 'MsLookupId' AND cu.COLUMN_NAME = 'PerformanceId'Suggested solution is to loop the column names and produce an inner join for each column this while produce result when all columns are present in an constraint the sql code under here is tested on the current solution and works
Suggested Solution `select count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu1 ON cu1.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and cu1.column_name ='MsLookupId' INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu2 ON cu2.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and cu2.column_name ='PerformanceId' WHERE tc.CONSTRAINT_TYPE = 'UNIQUE' and tc.TABLE_NAME = 'AvailableInstruments'
`