1 module dpq2.conv.to_d_types; 2 3 @safe: 4 5 import dpq2.value; 6 import dpq2.oids: OidType, isNativeInteger, isNativeFloat; 7 import dpq2.connection: Connection; 8 import dpq2.query: QueryParams; 9 import dpq2.result: msg_NOT_BINARY; 10 import dpq2.conv.from_d_types; 11 import dpq2.conv.numeric: rawValueToNumeric; 12 import dpq2.conv.time: binaryValueAs, TimeStamp, TimeStampUTC; 13 14 import vibe.data.json: Json, parseJsonString; 15 import vibe.data.bson: Bson; 16 import std.traits; 17 import std.uuid; 18 import std.datetime; 19 import std.traits: isScalarType; 20 import std.bitmanip: bigEndianToNative; 21 import std.conv: to; 22 23 // Supported PostgreSQL binary types 24 alias PGboolean = bool; /// boolean 25 alias PGsmallint = short; /// smallint 26 alias PGinteger = int; /// integer 27 alias PGbigint = long; /// bigint 28 alias PGreal = float; /// real 29 alias PGdouble_precision = double; /// double precision 30 alias PGtext = string; /// text 31 alias PGnumeric = string; /// numeric represented as string 32 alias PGbytea = const ubyte[]; /// bytea 33 alias PGuuid = UUID; /// UUID 34 alias PGdate = Date; /// Date (no time of day) 35 alias PGtime_without_time_zone = TimeOfDay; /// Time of day (no date) 36 alias PGtimestamp = TimeStamp; /// Both date and time without time zone 37 alias PGtimestamptz = TimeStampUTC; /// Both date and time stored in UTC time zone 38 alias PGjson = Json; /// json or jsonb 39 40 package void throwTypeComplaint(OidType receivedType, string expectedType, string file, size_t line) pure 41 { 42 throw new ValueConvException( 43 ConvExceptionType.NOT_IMPLEMENTED, 44 "Format of the column ("~to!string(receivedType)~") doesn't match to D native "~expectedType, 45 file, line 46 ); 47 } 48 49 private alias VF = ValueFormat; 50 private alias AE = ValueConvException; 51 private alias ET = ConvExceptionType; 52 53 /// Returns cell value as native string type from text or binary formatted field 54 string as(T)(in Value v) pure @trusted 55 if(is(T == string)) 56 { 57 if(v.format == VF.BINARY) 58 { 59 if(!( 60 v.oidType == OidType.Text || 61 v.oidType == OidType.FixedString || 62 v.oidType == OidType.Numeric || 63 v.oidType == OidType.Json 64 )) 65 throwTypeComplaint(v.oidType, "Text, FixedString, Numeric or Json", __FILE__, __LINE__); 66 67 if(v.oidType == OidType.Numeric) 68 return rawValueToNumeric(v.data); 69 } 70 71 return valueAsString(v); 72 } 73 74 /// Returns value as D type value from binary formatted field 75 T as(T)(in Value v) 76 if(!is(T == string) && !is(T == Bson)) 77 { 78 if(!(v.format == VF.BINARY)) 79 throw new AE(ET.NOT_BINARY, 80 msg_NOT_BINARY, __FILE__, __LINE__); 81 82 return binaryValueAs!T(v); 83 } 84 85 package: 86 87 string valueAsString(in Value v) pure 88 { 89 return (cast(const(char[])) v.data).to!string; 90 } 91 92 /// Returns value as bytes from binary formatted field 93 T binaryValueAs(T)(in Value v) 94 if( is( T == const(ubyte[]) ) ) 95 { 96 if(!(v.oidType == OidType.ByteArray)) 97 throwTypeComplaint(v.oidType, "ubyte[] or string", __FILE__, __LINE__); 98 99 return v.data; 100 } 101 102 /// Returns cell value as native integer or decimal values 103 /// 104 /// Postgres type "numeric" is oversized and not supported by now 105 T binaryValueAs(T)(in Value v) 106 if( isNumeric!(T) ) 107 { 108 static if(isIntegral!(T)) 109 if(!isNativeInteger(v.oidType)) 110 throwTypeComplaint(v.oidType, "integral types", __FILE__, __LINE__); 111 112 static if(isFloatingPoint!(T)) 113 if(!isNativeFloat(v.oidType)) 114 throwTypeComplaint(v.oidType, "floating point types", __FILE__, __LINE__); 115 116 if(!(v.data.length == T.sizeof)) 117 throw new AE(ET.SIZE_MISMATCH, 118 to!string(v.oidType)~" length ("~to!string(v.data.length)~") isn't equal to native D type "~ 119 to!string(typeid(T))~" size ("~to!string(T.sizeof)~")", 120 __FILE__, __LINE__); 121 122 ubyte[T.sizeof] s = v.data[0..T.sizeof]; 123 return bigEndianToNative!(T)(s); 124 } 125 126 /// Returns UUID as native UUID value 127 UUID binaryValueAs(T)(in Value v) 128 if( is( T == UUID ) ) 129 { 130 if(!(v.oidType == OidType.UUID)) 131 throwTypeComplaint(v.oidType, "UUID", __FILE__, __LINE__); 132 133 if(!(v.data.length == 16)) 134 throw new AE(ET.SIZE_MISMATCH, 135 "Value length isn't equal to Postgres UUID size", __FILE__, __LINE__); 136 137 UUID r; 138 r.data = v.data; 139 return r; 140 } 141 142 /// Returns boolean as native bool value 143 bool binaryValueAs(T : bool)(in Value v) 144 { 145 if(!(v.oidType == OidType.Bool)) 146 throwTypeComplaint(v.oidType, "bool", __FILE__, __LINE__); 147 148 if(!(v.data.length == 1)) 149 throw new AE(ET.SIZE_MISMATCH, 150 "Value length isn't equal to Postgres boolean size", __FILE__, __LINE__); 151 152 return v.data[0] != 0; 153 } 154 155 /// Returns Vibe.d's Json 156 Json binaryValueAs(T)(in Value v) @trusted 157 if( is( T == Json ) ) 158 { 159 import dpq2.conv.jsonb: jsonbValueToJson; 160 161 Json res; 162 163 switch(v.oidType) 164 { 165 case OidType.Json: 166 // represent value as text and parse it into Json 167 string t = v.valueAsString; 168 res = parseJsonString(t); 169 break; 170 171 case OidType.Jsonb: 172 res = v.jsonbValueToJson; 173 break; 174 175 default: 176 throwTypeComplaint(v.oidType, "json or jsonb", __FILE__, __LINE__); 177 } 178 179 return res; 180 } 181 182 public void _integration_test( string connParam ) @system 183 { 184 import std.algorithm : endsWith; 185 import std.array : replace; 186 import std.format : format; 187 import std.math : abs; 188 189 auto conn = new Connection(connParam); 190 191 // to return times in other than UTC time zone but fixed time zone so make the test reproducible in databases with other TZ 192 conn.exec("SET TIMEZONE TO +02"); 193 194 QueryParams params; 195 params.resultFormat = ValueFormat.BINARY; 196 197 { 198 void testIt(T)(T nativeValue, string pgType, string pgValue) 199 { 200 import std.algorithm : strip; 201 import std..string : representation; 202 203 // test string to native conversion 204 params.sqlCommand = format("SELECT %s::%s as d_type_test_value", pgValue, pgType); 205 params.args = null; 206 auto answer = conn.execParams(params); 207 immutable Value v = answer[0][0]; 208 auto result = v.as!T; 209 210 assert(result == nativeValue, 211 format("Received unexpected value\nreceived pgType=%s\nexpected nativeType=%s\nsent pgValue=%s\nexpected nativeValue=%s\nresult=%s", 212 v.oidType, typeid(T), pgValue, nativeValue, result) 213 ); 214 215 //TODO: Implement toValue for all tested types and remove the condition 216 static if (!is(T == UUID) && !is(T == const(ubyte[])) && !is(T == Json) && !is(T == TimeStamp)) 217 { 218 // test binary to text conversion 219 params.sqlCommand = "SELECT $1::text"; 220 params.args = [nativeValue.toValue]; 221 auto answer2 = conn.execParams(params); 222 auto v2 = answer2[0][0]; 223 auto textResult = v2.as!string.strip(' '); 224 pgValue = pgValue.strip('\''); 225 226 assert(textResult == pgValue, 227 format("Received unexpected value\nreceived pgType=%s\nsent nativeType=%s\nsent nativeValue=%s\nexpected pgValue=%s\nresult=%s\nexpectedRepresentation=%s\nreceivedRepresentation=%s", 228 v.oidType, typeid(T), nativeValue, pgValue, textResult, pgValue.representation, textResult.representation) 229 ); 230 } 231 else pragma(msg, T, " Is not tested in integration tests!"); 232 } 233 234 alias C = testIt; // "C" means "case" 235 236 C!PGboolean(true, "boolean", "true"); 237 C!PGboolean(false, "boolean", "false"); 238 C!PGsmallint(-32_761, "smallint", "-32761"); 239 C!PGinteger(-2_147_483_646, "integer", "-2147483646"); 240 C!PGbigint(-9_223_372_036_854_775_806, "bigint", "-9223372036854775806"); 241 C!PGreal(-12.3456f, "real", "-12.3456"); 242 C!PGdouble_precision(-1234.56789012345, "double precision", "-1234.56789012345"); 243 C!PGtext("first line\nsecond line", "text", "'first line\nsecond line'"); 244 C!PGtext("12345 ", "char(6)", "'12345'"); 245 C!PGbytea([0x44, 0x20, 0x72, 0x75, 0x6c, 0x65, 0x73, 0x00, 0x21], 246 "bytea", r"E'\\x44 20 72 75 6c 65 73 00 21'"); // "D rules\x00!" (ASCII) 247 C!PGuuid(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640"), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'"); 248 249 // numeric testing 250 C!PGnumeric("NaN", "numeric", "'NaN'"); 251 252 const string[] numericTests = [ 253 "42", 254 "-42", 255 "0", 256 "0.0146328", 257 "0.0007", 258 "0.007", 259 "0.07", 260 "0.7", 261 "7", 262 "70", 263 "700", 264 "7000", 265 "70000", 266 267 "7.0", 268 "70.0", 269 "700.0", 270 "7000.0", 271 "70000.000", 272 273 "2354877787627192443", 274 "2354877787627192443.0", 275 "2354877787627192443.00000", 276 "-2354877787627192443.00000" 277 ]; 278 279 foreach(i, s; numericTests) 280 C!PGnumeric(s, "numeric", s); 281 282 // date and time testing 283 C!PGdate(Date(2016, 01, 8), "date", "'2016-01-08'"); 284 C!PGtime_without_time_zone(TimeOfDay(12, 34, 56), "time without time zone", "'12:34:56'"); 285 C!PGtimestamp(PGtimestamp(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12)), "timestamp without time zone", "'1997-12-17 07:37:16.000012'"); 286 C!PGtimestamp(PGtimestamp.max, "timestamp without time zone", "'infinity'"); 287 C!PGtimestamp(PGtimestamp.min, "timestamp without time zone", "'-infinity'"); 288 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'"); 289 290 // SysTime testing 291 auto testTZ = new immutable SimpleTimeZone(2.dur!"hours"); // custom TZ 292 C!SysTime(SysTime(DateTime(1997, 12, 17, 7, 37, 16), dur!"usecs"(12), testTZ), "timestamptz", "'1997-12-17 07:37:16.000012+02'"); 293 294 // json 295 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string")]), "json", `'{"float_value": 123.456,"text_str": "text string"}'`); 296 297 // json as string 298 C!string(`{"float_value": 123.456}`, "json", `'{"float_value": 123.456}'`); 299 300 // jsonb 301 C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string"), "abc": Json(["key": Json("value")])]), "jsonb", 302 `'{"float_value": 123.456, "text_str": "text string", "abc": {"key": "value"}}'`); 303 } 304 }