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