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!PGtime_with_time_zone(PGtime_with_time_zone(TimeOfDay(12, 34, 56), 3600 * 5), "time with time zone", "'12:34:56-05'"); 193 C!PGinterval(PGinterval(-123), "interval", "'-00:00:00.000123'"); 194 C!PGinterval(PGinterval(7200_000_000 + 123), "interval", "'02:00:00.000123'"); 195 C!PGinterval(PGinterval(0, 2, 13), "interval", "'1 year 1 mon 2 days'"); 196 C!PGinterval(PGinterval(0, 0, -1), "interval", "'-1 mons'"); 197 C!PGinterval(PGinterval(0, -2, 1), "interval", "'1 mon -2 days'"); 198 C!PGinterval(PGinterval(-123, -2, -1), "interval", "'-1 mons -2 days -00:00:00.000123'"); 199 C!PGinterval(PGinterval(-(7200_000_000 + 123), 2, 177999999 * 12 + 3), "interval", "'177999999 years 3 mons 2 days -02:00:00.000123'"); 200 C!PGtimestamp(PGtimestamp(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12)), "timestamp without time zone", "'1997-12-17 07:37:16.000012'"); 201 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'"); 202 C!PGtimestamp(PGtimestamp.earlier, "timestamp", "'-infinity'"); 203 C!PGtimestamp(PGtimestamp.later, "timestamp", "'infinity'"); 204 C!PGtimestamp(PGtimestamp.min, "timestamp", `'4713-01-01 00:00:00 BC'`); 205 C!PGtimestamp(PGtimestamp.max, "timestamp", `'294276-12-31 23:59:59.999999'`); 206 207 // SysTime testing 208 auto testTZ = new immutable SimpleTimeZone(2.dur!"hours"); // custom TZ 209 C!SysTime(SysTime(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12), testTZ), "timestamptz", "'1997-12-17 07:37:16.000012+02'"); 210 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'"); 211 212 // json 213 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string")]), "json", `'{"float_value": 123.456,"text_str": "text string"}'`); 214 C!(Nullable!PGjson)(Nullable!Json(Json(["foo": Json("bar")])), "json", `'{"foo":"bar"}'`); 215 216 // json as string 217 C!string(`{"float_value": 123.456}`, "json", `'{"float_value": 123.456}'`); 218 219 // jsonb 220 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string"), "abc": Json(["key": Json("value")])]), "jsonb", 221 `'{"float_value": 123.456, "text_str": "text string", "abc": {"key": "value"}}'`); 222 223 // Geometric 224 import dpq2.conv.geometric: GeometricInstancesForIntegrationTest; 225 mixin GeometricInstancesForIntegrationTest; 226 227 C!Point(Point(1,2), "point", "'(1,2)'"); 228 C!PGline(Line(1,2,3), "line", "'{1,2,3}'"); 229 C!LineSegment(LineSegment(Point(1,2), Point(3,4)), "lseg", "'[(1,2),(3,4)]'"); 230 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 231 C!TestPath(TestPath(true, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'((1,1),(2,2),(3,3))'"); 232 C!TestPath(TestPath(false, [Point(1,1), Point(2,2), Point(3,3)]), "path", "'[(1,1),(2,2),(3,3)]'"); 233 C!Polygon(([Point(1,1), Point(2,2), Point(3,3)]), "polygon", "'((1,1),(2,2),(3,3))'"); 234 C!TestCircle(TestCircle(Point(1,2), 10), "circle", "'<(1,2),10>'"); 235 C!(Nullable!Point)(Nullable!Point(Point(1,2)), "point", "'(1,2)'"); 236 237 //Arrays 238 C!(int[][])([[1,2],[3,4]], "int[]", "'{{1,2},{3,4}}'"); 239 C!(int[])([], "int[]", "'{}'"); // empty array test 240 C!((Nullable!string)[])([Nullable!string("foo"), Nullable!string.init], "text[]", "'{foo,NULL}'"); 241 C!(string[])(["foo","bar", "baz"], "text[]", "'{foo,bar,baz}'"); 242 C!(PGjson[])([Json(["foo": Json(42)])], "json[]", `'{"{\"foo\":42}"}'`); 243 C!(PGuuid[])([UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640")], "uuid[]", "'{8b9ab33a-96e9-499b-9c36-aad1fe86d640}'"); 244 C!(Nullable!(int[]))(Nullable!(int[]).init, "int[]", "NULL"); 245 C!(Nullable!(int[]))(Nullable!(int[])([1,2,3]), "int[]", "'{1,2,3}'"); 246 } 247 248 // test round-trip compound types 249 { 250 conn.exec("CREATE TYPE test_type AS (x int, y int)"); 251 scope(exit) conn.exec("DROP TYPE test_type"); 252 253 params.sqlCommand = "SELECT 'test_type'::regtype::oid"; 254 OidType oid = cast(OidType)conn.execParams(params)[0][0].as!Oid; 255 256 Value input = Value(toRecordValue([17.toValue, Nullable!int.init.toValue]).data, oid); 257 258 params.sqlCommand = "SELECT $1::text"; 259 params.args = [input]; 260 Value v = conn.execParams(params)[0][0]; 261 assert(v.as!string == `(17,)`, v.as!string); 262 params.sqlCommand = "SELECT $1"; 263 v = conn.execParams(params)[0][0]; 264 assert(v.oidType == oid && v.data == input.data); 265 } 266 }