1 module dpq2.conv.native_tests; 2 3 import dpq2; 4 import dpq2.conv.arrays : isArrayType; 5 import dpq2.conv.geometric: Line; 6 import std.bitmanip : BitArray; 7 import std.datetime; 8 import std.typecons: Nullable; 9 import std.uuid: UUID; 10 import vibe.data.bson: Bson, deserializeBson; 11 import vibe.data.json: Json, parseJsonString; 12 13 version (integration_tests) 14 private bool compareArraysWithCareAboutNullables(A, B)(A _a, B _b) 15 { 16 static assert(is(A == B)); 17 18 import std.algorithm.comparison : equal; 19 import std.traits: isInstanceOf; 20 21 return equal!( 22 (a, b) 23 { 24 static if(isInstanceOf!(Nullable, A)) 25 { 26 if(a.isNull != b.isNull) 27 return false; 28 29 if(a.isNull) 30 return true; 31 } 32 33 return a == b; 34 } 35 )(_a, _b); 36 } 37 38 version (integration_tests) 39 public void _integration_test( string connParam ) @system 40 { 41 import std.format: format; 42 43 auto conn = new Connection(connParam); 44 45 // to return times in other than UTC time zone but fixed time zone so make the test reproducible in databases with other TZ 46 conn.exec("SET TIMEZONE TO +02"); 47 48 conn.exec("SET lc_monetary = 'en_US.UTF-8'"); 49 50 QueryParams params; 51 params.resultFormat = ValueFormat.BINARY; 52 53 { 54 void testIt(T)(T nativeValue, string pgType, string pgValue) 55 { 56 import std.algorithm : strip; 57 import std.string : representation; 58 59 static string formatValue(T val) 60 { 61 import std.algorithm : joiner, map, strip; 62 import std.conv : text, to; 63 import std.range : chain, ElementType; 64 65 // Nullable format deprecation workaround 66 static if (is(T == Nullable!R, R)) 67 return val.isNull ? "null" : val.get.to!string; 68 else static if (isArrayType!T && is(ElementType!T == Nullable!E, E)) 69 return chain("[", val.map!(a => a.isNull ? "null" : a.to!string).joiner(", "), "]").text; 70 else return val.to!string; 71 } 72 73 // test string to native conversion 74 params.sqlCommand = format("SELECT %s::%s as d_type_test_value", pgValue is null ? "NULL" : pgValue, pgType); 75 params.args = null; 76 auto answer = conn.execParams(params); 77 immutable Value v = answer[0][0]; 78 79 auto result = v.as!T; 80 81 static if(isArrayType!T) 82 const bool assertResult = compareArraysWithCareAboutNullables(result, nativeValue); 83 else 84 const bool assertResult = result == nativeValue; 85 86 assert(assertResult, 87 format("PG to native conv: received unexpected value\nreceived pgType=%s\nexpected nativeType=%s\nsent pgValue=%s\nexpected nativeValue=%s\nresult=%s", 88 v.oidType, typeid(T), pgValue, formatValue(nativeValue), formatValue(result)) 89 ); 90 91 { 92 // test binary to text conversion 93 params.sqlCommand = "SELECT $1::text"; 94 params.args = [toValue(nativeValue)]; 95 96 auto answer2 = conn.execParams(params); 97 auto v2 = answer2[0][0]; 98 99 string textResult = v2.isNull 100 ? "NULL" 101 : v2.as!string.strip(' '); 102 103 pgValue = pgValue.strip('\''); 104 105 // Special cases: 106 static if(is(T == PGbytea)) 107 pgValue = `\x442072756c65730021`; // Server formats its reply slightly different from the passed argument 108 109 static if(is(T == Json)) 110 { 111 // Reformatting by same way in the hope that the data will be sorted same in both cases 112 pgValue = pgValue.parseJsonString.toString; 113 textResult = textResult.parseJsonString.toString; 114 } 115 116 assert(textResult == pgValue, 117 format("Native to PG conv: received unexpected value\nreceived pgType=%s\nsent nativeType=%s\nsent nativeValue=%s\nexpected pgValue=%s\nresult=%s\nexpectedRepresentation=%s\nreceivedRepresentation=%s", 118 v.oidType, typeid(T), formatValue(nativeValue), pgValue, textResult, pgValue.representation, textResult.representation) 119 ); 120 } 121 } 122 123 alias C = testIt; // "C" means "case" 124 125 import dpq2.conv.to_d_types: PGTestMoney; 126 127 C!PGboolean(true, "boolean", "true"); 128 C!PGboolean(false, "boolean", "false"); 129 C!(Nullable!PGboolean)(Nullable!PGboolean.init, "boolean", "NULL"); 130 C!(Nullable!PGboolean)(Nullable!PGboolean(true), "boolean", "true"); 131 C!PGsmallint(-32_761, "smallint", "-32761"); 132 C!PGinteger(-2_147_483_646, "integer", "-2147483646"); 133 C!PGbigint(-9_223_372_036_854_775_806, "bigint", "-9223372036854775806"); 134 C!PGTestMoney(PGTestMoney(-123.45), "money", "'-$123.45'"); 135 C!PGreal(-12.3456f, "real", "-12.3456"); 136 C!PGdouble_precision(-1234.56789012345, "double precision", "-1234.56789012345"); 137 C!PGtext("first line\nsecond line", "text", "'first line\nsecond line'"); 138 C!PGtext("12345 ", "char(6)", "'12345'"); 139 C!PGtext("12345", "varchar(6)", "'12345'"); 140 C!(Nullable!PGtext)(Nullable!PGtext.init, "text", "NULL"); 141 C!PGbytea([0x44, 0x20, 0x72, 0x75, 0x6c, 0x65, 0x73, 0x00, 0x21], 142 "bytea", r"E'\\x44 20 72 75 6c 65 73 00 21'"); // "D rules\x00!" (ASCII) 143 C!PGuuid(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640"), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'"); 144 C!(Nullable!PGuuid)(Nullable!UUID(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640")), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'"); 145 C!PGvarbit(BitArray([1, 0, 1, 0, 1, 1, 0, 1, 0, 1, 1, 0, 1, 0, 1]), "varbit", "'101011010110101'"); 146 C!PGvarbit(BitArray([0, 0, 1, 0, 1]), "varbit", "'00101'"); 147 C!PGvarbit(BitArray([1, 0, 1, 0, 0]), "varbit", "'10100'"); 148 149 // numeric testing 150 C!PGnumeric("NaN", "numeric", "'NaN'"); 151 152 const string[] numericTests = [ 153 "42", 154 "-42", 155 "0", 156 "0.0146328", 157 "0.0007", 158 "0.007", 159 "0.07", 160 "0.7", 161 "7", 162 "70", 163 "700", 164 "7000", 165 "70000", 166 167 "7.0", 168 "70.0", 169 "700.0", 170 "7000.0", 171 "70000.000", 172 173 "2354877787627192443", 174 "2354877787627192443.0", 175 "2354877787627192443.00000", 176 "-2354877787627192443.00000" 177 ]; 178 179 foreach(i, s; numericTests) 180 C!PGnumeric(s, "numeric", s); 181 182 // date and time testing 183 C!PGdate(Date(2016, 01, 8), "date", "'2016-01-08'"); 184 { 185 import std.exception : assertThrown; 186 187 assertThrown!ValueConvException( 188 C!PGdate(Date(0001, 01, 8), "date", "'5874897-12-31'") 189 ); 190 } 191 C!PGtime_without_time_zone(TimeOfDay(12, 34, 56), "time without time zone", "'12:34:56'"); 192 C!PGtimestamp(PGtimestamp(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12)), "timestamp without time zone", "'1997-12-17 07:37:16.000012'"); 193 C!PGtimestamptz(PGtimestamptz(DateTime(1997, 12, 17, 5, 37, 16), dur!"usecs"(12)), "timestamp with time zone", "'1997-12-17 07:37:16.000012+02'"); 194 C!PGtimestamp(PGtimestamp.earlier, "timestamp", "'-infinity'"); 195 C!PGtimestamp(PGtimestamp.later, "timestamp", "'infinity'"); 196 C!PGtimestamp(PGtimestamp.min, "timestamp", `'4713-01-01 00:00:00 BC'`); 197 C!PGtimestamp(PGtimestamp.max, "timestamp", `'294276-12-31 23:59:59.999999'`); 198 199 // SysTime testing 200 auto testTZ = new immutable SimpleTimeZone(2.dur!"hours"); // custom TZ 201 C!SysTime(SysTime(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12), testTZ), "timestamptz", "'1997-12-17 07:37:16.000012+02'"); 202 C!(Nullable!SysTime)(Nullable!SysTime(SysTime(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12), testTZ)), "timestamptz", "'1997-12-17 07:37:16.000012+02'"); 203 204 // json 205 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string")]), "json", `'{"float_value": 123.456,"text_str": "text string"}'`); 206 C!(Nullable!PGjson)(Nullable!Json(Json(["foo": Json("bar")])), "json", `'{"foo":"bar"}'`); 207 208 // json as string 209 C!string(`{"float_value": 123.456}`, "json", `'{"float_value": 123.456}'`); 210 211 // jsonb 212 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string"), "abc": Json(["key": Json("value")])]), "jsonb", 213 `'{"float_value": 123.456, "text_str": "text string", "abc": {"key": "value"}}'`); 214 215 // Geometric 216 import dpq2.conv.geometric: GeometricInstancesForIntegrationTest; 217 mixin GeometricInstancesForIntegrationTest; 218 219 C!Point(Point(1,2), "point", "'(1,2)'"); 220 C!PGline(Line(1,2,3), "line", "'{1,2,3}'"); 221 C!LineSegment(LineSegment(Point(1,2), Point(3,4)), "lseg", "'[(1,2),(3,4)]'"); 222 C!Box(Box(Point(1,2),Point(3,4)), "box", "'(3,4),(1,2)'"); // PG handles box ordered as upper right first and lower left next 223 C!TestPath(TestPath(true, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'((1,1),(2,2),(3,3))'"); 224 C!TestPath(TestPath(false, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'[(1,1),(2,2),(3,3)]'"); 225 C!Polygon(([Point(1,1), Point(2,2), Point(3,3)]), "polygon", "'((1,1),(2,2),(3,3))'"); 226 C!TestCircle(TestCircle(Point(1,2), 10), "circle", "'<(1,2),10>'"); 227 C!(Nullable!Point)(Nullable!Point(Point(1,2)), "point", "'(1,2)'"); 228 229 //Arrays 230 C!(int[][])([[1,2],[3,4]], "int[]", "'{{1,2},{3,4}}'"); 231 C!(int[])([], "int[]", "'{}'"); // empty array test 232 C!((Nullable!string)[])([Nullable!string("foo"), Nullable!string.init], "text[]", "'{foo,NULL}'"); 233 C!(string[])(["foo","bar", "baz"], "text[]", "'{foo,bar,baz}'"); 234 C!(PGjson[])([Json(["foo": Json(42)])], "json[]", `'{"{\"foo\":42}"}'`); 235 C!(PGuuid[])([UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640")], "uuid[]", "'{8b9ab33a-96e9-499b-9c36-aad1fe86d640}'"); 236 C!(Nullable!(int[]))(Nullable!(int[]).init, "int[]", "NULL"); 237 C!(Nullable!(int[]))(Nullable!(int[])([1,2,3]), "int[]", "'{1,2,3}'"); 238 } 239 }