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