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 std.variant: Variant; 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 import dpq2.connection: createTestConn; 43 44 auto conn = createTestConn(connParam); 45 46 // to return times in other than UTC time zone but fixed time zone so make the test reproducible in databases with other TZ 47 conn.exec("SET TIMEZONE TO +02"); 48 49 // It is found what Linux and Windows have different approach for monetary 50 // types formatting at same locales. This line sets equal approach. 51 conn.exec("SET lc_monetary = 'C'"); 52 53 QueryParams params; 54 params.resultFormat = ValueFormat.BINARY; 55 56 { 57 import dpq2.conv.geometric: GeometricInstancesForIntegrationTest; 58 mixin GeometricInstancesForIntegrationTest; 59 60 void testIt(T)(T nativeValue, in string pgType, string pgValue) 61 { 62 import std.algorithm : strip; 63 import std.string : representation; 64 import std.meta: AliasSeq, anySatisfy; 65 66 static string formatValue(T val) 67 { 68 import std.algorithm : joiner, map; 69 import std.conv : text, to; 70 import std.range : chain, ElementType; 71 72 // Nullable format deprecation workaround 73 static if (is(T == Nullable!R, R)) 74 return val.isNull ? "null" : val.get.to!string; 75 else static if (isArrayType!T && is(ElementType!T == Nullable!E, E)) 76 return chain("[", val.map!(a => a.isNull ? "null" : a.to!string).joiner(", "), "]").text; 77 else return val.to!string; 78 } 79 80 // test string to native conversion 81 params.sqlCommand = format("SELECT %s::%s as d_type_test_value", pgValue is null ? "NULL" : pgValue, pgType); 82 params.args = null; 83 auto answer = conn.execParams(params); 84 immutable Value v = answer[0][0]; 85 86 auto result = v.as!T; 87 88 enum disabledForStdVariant = ( 89 is(T == Nullable!string[]) || // Variant haven't heuristics to understand what array elements can contain NULLs 90 is(T == Nullable!(int[])) || // Same reason, but here is all values are Nullable and thus incompatible for comparison with original values 91 is(T == SysTime) || is(T == Nullable!SysTime) || // Can't be supported by toVariant because TimeStampWithZone converted to PGtimestamptz 92 is(T == LineSegment) || // Impossible to support: LineSegment struct must be provided by user 93 is(T == PGTestMoney) || // ditto 94 is(T == BitArray) || //TODO: Format of the column (VariableBitString) doesn't supported by Value to Variant converter 95 is(T == Nullable!BitArray) || // ditto 96 is(T == Point) || // Impossible to support: LineSegment struct must be provided by user 97 is(T == Nullable!Point) || // ditto 98 is(T == Box) || // ditto 99 is(T == TestPath) || // ditto 100 is(T == Polygon) || // ditto 101 is(T == TestCircle) // ditto 102 ); 103 104 static if(!disabledForStdVariant) 105 { 106 static if (is(T == Nullable!R, R)) 107 auto stdVariantResult = v.as!(Variant, true); 108 else 109 auto stdVariantResult = v.as!(Variant, false); 110 } 111 112 string formatMsg(string varType) 113 { 114 return format( 115 "PG to %s conv: received unexpected value\nreceived pgType=%s\nexpected nativeType=%s\nsent pgValue=%s\nexpected nativeValue=%s\nresult=%s", 116 varType, v.oidType, typeid(T), pgValue, formatValue(nativeValue), formatValue(result) 117 ); 118 } 119 120 static if(isArrayType!T) 121 const bool assertResult = compareArraysWithCareAboutNullables(result, nativeValue); 122 else 123 { 124 const bool assertResult = result == nativeValue; 125 126 //Variant: 127 static if(!disabledForStdVariant) 128 { 129 // Ignores "json as string" test case with Json sent natively as string 130 if(!(is(T == string) && v.oidType == OidType.Json)) 131 { 132 assert(stdVariantResult == nativeValue, formatMsg("std.variant.Variant (type: %s)".format(stdVariantResult.type))); 133 } 134 } 135 } 136 137 assert(assertResult, formatMsg("native")); 138 139 { 140 // test binary to text conversion 141 params.sqlCommand = "SELECT $1::text"; 142 params.args = [toValue(nativeValue)]; 143 144 auto answer2 = conn.execParams(params); 145 auto v2 = answer2[0][0]; 146 147 string textResult = v2.isNull 148 ? "NULL" 149 : v2.as!string.strip(' '); 150 151 pgValue = pgValue.strip('\''); 152 153 // Special cases: 154 static if(is(T == PGbytea)) 155 pgValue = `\x442072756c65730021`; // Server formats its reply slightly different from the passed argument 156 157 static if(is(T == Json)) 158 { 159 // Reformatting by same way in the hope that the data will be sorted same in both cases 160 pgValue = pgValue.parseJsonString.toString; 161 textResult = textResult.parseJsonString.toString; 162 } 163 164 assert(textResult == pgValue, 165 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", 166 v.oidType, typeid(T), formatValue(nativeValue), pgValue, textResult, pgValue.representation, textResult.representation) 167 ); 168 } 169 } 170 171 alias C = testIt; // "C" means "case" 172 173 import dpq2.conv.to_d_types: PGTestMoney; 174 175 C!PGboolean(true, "boolean", "true"); 176 C!PGboolean(false, "boolean", "false"); 177 C!(Nullable!PGboolean)(Nullable!PGboolean.init, "boolean", "NULL"); 178 C!(Nullable!PGboolean)(Nullable!PGboolean(true), "boolean", "true"); 179 C!PGsmallint(-32_761, "smallint", "-32761"); 180 C!PGinteger(-2_147_483_646, "integer", "-2147483646"); 181 C!PGbigint(-9_223_372_036_854_775_806, "bigint", "-9223372036854775806"); 182 C!PGTestMoney(PGTestMoney(-123.45), "money", "'-$123.45'"); 183 C!PGreal(-12.3456f, "real", "-12.3456"); 184 C!PGdouble_precision(-1234.56789012345, "double precision", "-1234.56789012345"); 185 C!PGtext("first line\nsecond line", "text", "'first line\nsecond line'"); 186 C!PGtext("12345 ", "char(6)", "'12345'"); 187 C!PGtext("12345", "varchar(6)", "'12345'"); 188 C!(Nullable!PGtext)(Nullable!PGtext.init, "text", "NULL"); 189 C!PGbytea([0x44, 0x20, 0x72, 0x75, 0x6c, 0x65, 0x73, 0x00, 0x21], 190 "bytea", r"E'\\x44 20 72 75 6c 65 73 00 21'"); // "D rules\x00!" (ASCII) 191 C!PGuuid(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640"), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'"); 192 C!(Nullable!PGuuid)(Nullable!UUID(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640")), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'"); 193 C!PGvarbit(BitArray([1, 0, 1, 0, 1, 1, 0, 1, 0, 1, 1, 0, 1, 0, 1]), "varbit", "'101011010110101'"); 194 C!PGvarbit(BitArray([0, 0, 1, 0, 1]), "varbit", "'00101'"); 195 C!PGvarbit(BitArray([1, 0, 1, 0, 0]), "varbit", "'10100'"); 196 C!(Nullable!PGvarbit)(Nullable!PGvarbit.init, "varbit", "NULL"); 197 198 // numeric testing 199 C!PGnumeric("NaN", "numeric", "'NaN'"); 200 201 const string[] numericTests = [ 202 "42", 203 "-42", 204 "0", 205 "0.0146328", 206 "0.0007", 207 "0.007", 208 "0.07", 209 "0.7", 210 "7", 211 "70", 212 "700", 213 "7000", 214 "70000", 215 216 "7.0", 217 "70.0", 218 "700.0", 219 "7000.0", 220 "70000.000", 221 222 "2354877787627192443", 223 "2354877787627192443.0", 224 "2354877787627192443.00000", 225 "-2354877787627192443.00000" 226 ]; 227 228 foreach(i, s; numericTests) 229 C!PGnumeric(s, "numeric", s); 230 231 // date and time testing 232 C!PGdate(Date(2016, 01, 8), "date", "'2016-01-08'"); 233 { 234 import std.exception : assertThrown; 235 236 assertThrown!ValueConvException( 237 C!PGdate(Date(0001, 01, 8), "date", "'5874897-12-31'") 238 ); 239 } 240 C!PGtime_without_time_zone(TimeOfDay(12, 34, 56), "time without time zone", "'12:34:56'"); 241 C!PGtime_with_time_zone(PGtime_with_time_zone(TimeOfDay(12, 34, 56), 3600 * 5), "time with time zone", "'12:34:56-05'"); 242 C!PGinterval(PGinterval(-123), "interval", "'-00:00:00.000123'"); 243 C!PGinterval(PGinterval(7200_000_000 + 123), "interval", "'02:00:00.000123'"); 244 C!PGinterval(PGinterval(0, 2, 13), "interval", "'1 year 1 mon 2 days'"); 245 C!PGinterval(PGinterval(0, 0, -1), "interval", "'-1 mons'"); 246 C!PGinterval(PGinterval(0, -2, 1), "interval", "'1 mon -2 days'"); 247 C!PGinterval(PGinterval(-123, -2, -1), "interval", "'-1 mons -2 days -00:00:00.000123'"); 248 C!PGinterval(PGinterval(-(7200_000_000 + 123), 2, 177999999 * 12 + 3), "interval", "'177999999 years 3 mons 2 days -02:00:00.000123'"); 249 C!PGtimestamp(PGtimestamp(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12)), "timestamp without time zone", "'1997-12-17 07:37:16.000012'"); 250 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'"); 251 C!PGtimestamp(PGtimestamp.earlier, "timestamp", "'-infinity'"); 252 C!PGtimestamp(PGtimestamp.later, "timestamp", "'infinity'"); 253 C!PGtimestamp(PGtimestamp.min, "timestamp", `'4713-01-01 00:00:00 BC'`); 254 C!PGtimestamp(PGtimestamp.max, "timestamp", `'294276-12-31 23:59:59.999999'`); 255 256 // SysTime testing 257 auto testTZ = new immutable SimpleTimeZone(2.dur!"hours"); // custom TZ 258 C!SysTime(SysTime(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12), testTZ), "timestamptz", "'1997-12-17 07:37:16.000012+02'"); 259 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'"); 260 261 // json 262 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string")]), "json", `'{"float_value": 123.456,"text_str": "text string"}'`); 263 C!(Nullable!PGjson)(Nullable!Json(Json(["foo": Json("bar")])), "json", `'{"foo":"bar"}'`); 264 265 // json as string 266 C!string(`{"float_value": 123.456}`, "json", `'{"float_value": 123.456}'`); 267 268 // jsonb 269 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string"), "abc": Json(["key": Json("value")])]), "jsonb", 270 `'{"float_value": 123.456, "text_str": "text string", "abc": {"key": "value"}}'`); 271 272 // Geometric 273 C!Point(Point(1,2), "point", "'(1,2)'"); 274 C!PGline(Line(1,2,3), "line", "'{1,2,3}'"); 275 C!LineSegment(LineSegment(Point(1,2), Point(3,4)), "lseg", "'[(1,2),(3,4)]'"); 276 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 277 C!TestPath(TestPath(true, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'((1,1),(2,2),(3,3))'"); 278 C!TestPath(TestPath(false, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'[(1,1),(2,2),(3,3)]'"); 279 C!Polygon(([Point(1,1), Point(2,2), Point(3,3)]), "polygon", "'((1,1),(2,2),(3,3))'"); 280 C!TestCircle(TestCircle(Point(1,2), 10), "circle", "'<(1,2),10>'"); 281 C!(Nullable!Point)(Nullable!Point(Point(1,2)), "point", "'(1,2)'"); 282 283 //Arrays 284 C!(int[][])([[1,2],[3,4]], "int[]", "'{{1,2},{3,4}}'"); 285 C!(int[])([], "int[]", "'{}'"); // empty array test 286 C!((Nullable!string)[])([Nullable!string("foo"), Nullable!string.init], "text[]", "'{foo,NULL}'"); 287 C!(string[])(["foo","bar", "baz"], "text[]", "'{foo,bar,baz}'"); 288 C!(PGjson[])([Json(["foo": Json(42)])], "json[]", `'{"{\"foo\":42}"}'`); 289 C!(PGuuid[])([UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640")], "uuid[]", "'{8b9ab33a-96e9-499b-9c36-aad1fe86d640}'"); 290 C!(PGline[])([Line(1,2,3), Line(4,5,6)], "line[]", `'{"{1,2,3}","{4,5,6}"}'`); 291 C!(Nullable!(int[]))(Nullable!(int[]).init, "int[]", "NULL"); 292 C!(Nullable!(int[]))(Nullable!(int[])([1,2,3]), "int[]", "'{1,2,3}'"); 293 } 294 295 // test round-trip compound types 296 { 297 conn.exec("CREATE TYPE test_type AS (x int, y int)"); 298 scope(exit) conn.exec("DROP TYPE test_type"); 299 300 params.sqlCommand = "SELECT 'test_type'::regtype::oid"; 301 OidType oid = cast(OidType)conn.execParams(params)[0][0].as!Oid; 302 303 Value input = Value(toRecordValue([17.toValue, Nullable!int.init.toValue]).data, oid); 304 305 params.sqlCommand = "SELECT $1::text"; 306 params.args = [input]; 307 Value v = conn.execParams(params)[0][0]; 308 assert(v.as!string == `(17,)`, v.as!string); 309 params.sqlCommand = "SELECT $1"; 310 v = conn.execParams(params)[0][0]; 311 assert(v.oidType == oid && v.data == input.data); 312 } 313 }