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