public void QueryMultipleWithBreaks()
{
var db = new SPTestsDatabase();
var resultCTestFull = db.QueryMultipleFromProcedure("cbreaktest", outParams: new { c1 = new Cursor(), c2 = new Cursor() });
CheckMultiResultSetStructure(resultCTestFull, 10, 11);
var resultCTestToBreak = db.QueryMultipleFromProcedure("cbreaktest", ioParams: new { c1 = new Cursor(), c2 = new Cursor() });
CheckMultiResultSetStructure(resultCTestToBreak, breakTest: true);
}
public void DereferenceOneByNFromProcedure()
{
var db = new SPTestsDatabase();
var resultSetOneByN = db.QueryMultipleFromProcedure("cursorOneByN", outParams: new { xyz = new Cursor() });
CheckMultiResultSetStructure(resultSetOneByN);
}
public void QueryFromMixedCursorOutput()
{
var db = new SPTestsDatabase(ProviderName);
var mixedSets = db.QueryMultipleFromProcedure("mixedresults", outParams: new { prc1 = new Cursor(), prc2 = new Cursor(), num1 = 0, num2 = 0 });
int sets = 0;
int[] counts = new int[2];
foreach (var set in mixedSets)
{
foreach (var item in set)
{
counts[sets]++;
if (sets == 0)
{
Assert.AreEqual(typeof(string), item.ENAME.GetType());
}
else
{
Assert.AreEqual(typeof(string), item.DNAME.GetType());
}
}
sets++;
}
Assert.AreEqual(2, sets);
Assert.AreEqual(14, counts[0]);
Assert.AreEqual(60, counts[1]);
}
public void QueryMultipleFromTwoOutputCursors()
{
var db = new SPTestsDatabase(ProviderName);
// Oracle procedure two cursor output variables
var twoSets = db.QueryMultipleFromProcedure("tworesults", outParams: new { prc1 = new Cursor(), prc2 = new Cursor() });
int sets = 0;
int[] counts = new int[2];
foreach (var set in twoSets)
{
foreach (var item in set)
{
counts[sets]++;
if (sets == 0)
{
Assert.AreEqual(typeof(string), item.ENAME.GetType());
}
else
{
Assert.AreEqual(typeof(string), item.DNAME.GetType());
}
}
sets++;
}
Assert.AreEqual(2, sets);
Assert.AreEqual(14, counts[0]);
Assert.AreEqual(60, counts[1]);
}
public void DereferenceNByOneFromProcedure()
{
var db = new SPTestsDatabase();
// For small result sets, this actually saves one round trip to the database
//db.AutoDereferenceFetchSize = -1;
var resultSetNByOne = db.QueryMultipleFromProcedure("cursorNByOne", outParams: new { c1 = new Cursor(), c2 = new Cursor() });
CheckMultiResultSetStructure(resultSetNByOne);
}
public void HugeCursorTest()
{
var db = new SPTestsDatabase();
//// Huge cursor tests....
var config = db.SingleFromQuery("SELECT current_setting('work_mem') work_mem, current_setting('log_temp_files') log_temp_files");
//// huge data from SELECT *
//var resultLargeSelectTest = db.QueryWithParams("SELECT * FROM large");
//foreach(var item in resultLargeSelectTest)
//{
// int a = 1;
//}
//// huge data from (implicit) FETCH ALL
//// AUTO-DEREFERENCE TWO HUGE, ONLY FETCH FROM ONE
//var resultLargeProcTest = db.QueryFromProcedure("lump2", returnParams: new { abc = new Cursor() });
//foreach (var item in resultLargeProcTest)
//{
// Console.WriteLine(item.id);
// break;
//}
var results = db.QueryMultipleFromProcedure("lump2", returnParams: new { abc = new Cursor() });
db.NpgsqlAutoDereferenceFetchSize = 4000000;
CheckMultiResultSetStructure(results, 10000000, 10000000, true, true);
// one item from cursor
//using (var conn = db.OpenConnection())
//{
// using (var trans = conn.BeginTransaction())
// {
// var result = db.ExecuteAsProcedure("lump2", returnParams: new { abc = new Cursor(), def = new Cursor() }, connection: conn);
// var singleItemTest = db.QueryWithParams($@"FETCH 5000000 FROM ""{result.abc}"";", connection: conn);
// foreach (var item in singleItemTest)
// {
// Console.WriteLine(item.id);
// break;
// }
// NB plain Execute() did NOT take a connection, and changing this MIGHT be an API breaking change??? TEST...!
// (This is the first, and so far only, really unwanted side effect of trying to stay 100% non-breaking.)
// db.Execute($@"CLOSE ""{result.abc}"";", conn);
// trans.Commit();
// }
//}
}
public void LargeCursorX2_AutomaticDereferencing()
{
var db = new SPTestsDatabase();
// Either of these will show big server-side buffers in PostrgeSQL logs (but will still pass)
//db.AutoDereferenceFetchSize = -1; // FETCH ALL
//db.AutoDereferenceFetchSize = 400000;
var results = db.QueryMultipleFromProcedure("lump2", returnParams: new { cursor = new Cursor() });
int rcount = 0;
foreach (var result in results)
{
rcount++;
int count = 0;
foreach (var item in result)
{
count++;
// there is no ORDER BY (it would not be sensible on such a huge data set) - this only sometimes works...
//Assert.AreEqual(count, item.id);
}
Assert.AreEqual(LargeCursorSize, count);
}
Assert.AreEqual(2, rcount);
}