1 module dpq2.types.to_d_types;
2 
3 import dpq2;
4 
5 import dpq2.types.numeric: rawValueToNumeric;
6 import dpq2.types.time: binaryValueAs, TimeStampWithoutTZ;
7 
8 import vibe.data.json: Json, parseJsonString;
9 import std.traits;
10 import std.uuid;
11 import std.datetime;
12 
13 // Supported PostgreSQL binary types
14 alias PGboolean =       bool; /// boolean
15 alias PGsmallint =      short; /// smallint
16 alias PGinteger =       int; /// integer
17 alias PGbigint =        long; /// bigint
18 alias PGreal =          float; /// real
19 alias PGdouble_precision = double; /// double precision
20 alias PGtext =          string; /// text
21 alias PGnumeric =       string; /// numeric represented as string
22 alias PGbytea =         const ubyte[]; /// bytea
23 alias PGuuid =          UUID; /// UUID
24 alias PGdate =          Date; /// Date (no time of day)
25 alias PGtime_without_time_zone = TimeOfDay; /// Time of day (no date)
26 alias PGtimestamp_without_time_zone = TimeStampWithoutTZ; /// Both date and time (no time zone)
27 alias PGjson =          Json; /// json or jsonb
28 
29 package void throwTypeComplaint(OidType receivedType, string expectedType, string file, size_t line)
30 {
31     throw new AnswerException(
32             ExceptionType.NOT_IMPLEMENTED,
33             "Format of the column ("~to!string(receivedType)~") doesn't match to D native "~expectedType,
34             file, line
35         );
36 }
37 
38 private alias VF = ValueFormat;
39 private alias AE = AnswerException;
40 private alias ET = ExceptionType;
41 
42 /// Returns cell value as native string type from text or binary formatted field
43 @property string as(T)(in Value v)
44 if(is(T == string))
45 {
46     if(v.format == VF.BINARY)
47     {
48         if(!(v.oidType == OidType.Text || v.oidType == OidType.Numeric))
49             throwTypeComplaint(v.oidType, "string or numeric", __FILE__, __LINE__);
50 
51         if(v.oidType == OidType.Numeric)
52             return rawValueToNumeric(v.value);
53     }
54 
55     return valueAsString(v);
56 }
57 
58 /// Returns value as D type value from binary formatted field
59 @property T as(T)(in Value v)
60 if(!is(T == string))
61 {
62     if(!(v.format == VF.BINARY))
63         throw new AE(ET.NOT_BINARY,
64             msg_NOT_BINARY, __FILE__, __LINE__);
65 
66     return binaryValueAs!T(v);
67 }
68 
69 package:
70 
71 @property string valueAsString(in Value v)
72 {
73     return to!string( cast(const(char[])) v.value );
74 }
75 
76 /// Returns value as bytes from binary formatted field
77 @property T binaryValueAs(T)(in Value v)
78 if( is( T == const(ubyte[]) ) )
79 {
80     if(!(v.oidType == OidType.ByteArray))
81         throwTypeComplaint(v.oidType, "ubyte[] or string", __FILE__, __LINE__);
82 
83     return v.value;
84 }
85 
86 /// Returns cell value as native integer or decimal values
87 ///
88 /// Postgres type "numeric" is oversized and not supported by now
89 @property T binaryValueAs(T)(in Value v)
90 if( isNumeric!(T) )
91 {
92     static if(isIntegral!(T))
93         if(!isNativeInteger(v.oidType))
94             throwTypeComplaint(v.oidType, "integral types", __FILE__, __LINE__);
95 
96     static if(isFloatingPoint!(T))
97         if(!isNativeFloat(v.oidType))
98             throwTypeComplaint(v.oidType, "floating point types", __FILE__, __LINE__);
99 
100     if(!(v.value.length == T.sizeof))
101         throw new AE(ET.SIZE_MISMATCH,
102             to!string(v.oidType)~" length ("~to!string(v.value.length)~") isn't equal to native D type "~
103                 to!string(typeid(T))~" size ("~to!string(T.sizeof)~")",
104             __FILE__, __LINE__);
105 
106     ubyte[T.sizeof] s = v.value[0..T.sizeof];
107     return bigEndianToNative!(T)(s);
108 }
109 
110 /// Returns UUID as native UUID value
111 @property UUID binaryValueAs(T)(in Value v)
112 if( is( T == UUID ) )
113 {
114     if(!(v.oidType == OidType.UUID))
115         throwTypeComplaint(v.oidType, "UUID", __FILE__, __LINE__);
116 
117     if(!(v.value.length == 16))
118         throw new AE(ET.SIZE_MISMATCH,
119             "Value length isn't equal to Postgres UUID size", __FILE__, __LINE__);
120 
121     UUID r;
122     r.data = v.value;
123     return r;
124 }
125 
126 /// Returns boolean as native bool value
127 @property bool binaryValueAs(T)(in Value v)
128 if( is( T == bool ) )
129 {
130     if(!(v.oidType == OidType.Bool))
131         throwTypeComplaint(v.oidType, "bool", __FILE__, __LINE__);
132 
133     if(!(v.value.length == 1))
134         throw new AE(ET.SIZE_MISMATCH,
135             "Value length isn't equal to Postgres boolean size", __FILE__, __LINE__);
136 
137     return v.value[0] != 0;
138 }
139 
140 /// Returns Vibe.d's Json
141 @property Json binaryValueAs(T)(in Value v) // FIXME ref is need
142 if( is( T == Json ) )
143 {
144     Json res;
145 
146     switch(v.oidType)
147     {
148         case OidType.Json:
149             // represent value as text and parse it into Json
150             auto t = Value(cast(ubyte[]) v.value, OidType.Text);
151             res = parseJsonString(t.as!PGtext);
152             break;
153 
154         case OidType.Jsonb:
155             assert(false, "Is not implemented");
156             //break;
157 
158         default:
159             throwTypeComplaint(v.oidType, "json or jsonb", __FILE__, __LINE__);
160     }
161 
162     return res;
163 }
164 
165 public void _integration_test( string connParam )
166 {
167     auto conn = new Connection;
168 	conn.connString = connParam;
169     conn.connect();
170 
171     QueryParams params;
172     params.resultFormat = ValueFormat.BINARY;
173 
174     {
175         void testIt(T)(T nativeValue, string pgType, string pgValue)
176         {
177             params.sqlCommand = "SELECT "~pgValue~"::"~pgType~" as d_type_test_value";
178             auto answer = conn.exec(params);
179             immutable Value v = answer[0][0].get;
180             auto result = v.as!T;
181 
182             assert(result == nativeValue, "Received unexpected value\nreceived pgType="~to!string(v.oidType)~"\nexpected nativeType="~to!string(typeid(T))~
183                 "\nsent pgValue="~pgValue~"\nexpected nativeValue="~to!string(nativeValue)~"\nresult="~to!string(result));
184         }
185 
186         alias C = testIt; // "C" means "case"
187 
188         C!PGboolean(true, "boolean", "true");
189         C!PGboolean(false, "boolean", "false");
190         C!PGsmallint(-32_761, "smallint", "-32761");
191         C!PGinteger(-2_147_483_646, "integer", "-2147483646");
192         C!PGbigint(-9_223_372_036_854_775_806, "bigint", "-9223372036854775806");
193         C!PGreal(-12.3456f, "real", "-12.3456");
194         C!PGdouble_precision(-1234.56789012345, "double precision", "-1234.56789012345");
195         C!PGtext("first line\nsecond line", "text", "'first line\nsecond line'");
196         C!PGbytea([0x44, 0x20, 0x72, 0x75, 0x6c, 0x65, 0x73, 0x00, 0x21],
197             "bytea", r"E'\\x44 20 72 75 6c 65 73 00 21'"); // "D rules\x00!" (ASCII)
198         C!PGuuid(UUID("8b9ab33a-96e9-499b-9c36-aad1fe86d640"), "uuid", "'8b9ab33a-96e9-499b-9c36-aad1fe86d640'");
199 
200         // numeric testing
201         C!PGnumeric("NaN", "numeric", "'NaN'");
202 
203         const string[] numericTests = [
204             "42",
205             "-42",
206             "0",
207             "0.0146328",
208             "0.0007",
209             "0.007",
210             "0.07",
211             "0.7",
212             "7",
213             "70",
214             "700",
215             "7000",
216             "70000",
217 
218             "7.0",
219             "70.0",
220             "700.0",
221             "7000.0",
222             "70000.000",
223 
224             "2354877787627192443",
225             "2354877787627192443.0",
226             "2354877787627192443.00000",
227             "-2354877787627192443.00000"
228         ];
229 
230         foreach(i, s; numericTests)
231             C!PGnumeric(s, "numeric", s);
232 
233         // date and time testing
234         C!PGdate(Date(2016, 01, 8), "date", "'January 8, 2016'");
235         C!PGtime_without_time_zone(TimeOfDay(12, 34, 56), "time without time zone", "'12:34:56'");
236         C!PGtimestamp_without_time_zone(TimeStampWithoutTZ(DateTime(1997, 12, 17, 7, 37, 16), FracSec.from!"usecs"(12)), "timestamp without time zone", "'1997-12-17 07:37:16.000012'");
237         C!PGtimestamp_without_time_zone(TimeStampWithoutTZ.max, "timestamp without time zone", "'infinity'");
238         C!PGtimestamp_without_time_zone(TimeStampWithoutTZ.min, "timestamp without time zone", "'-infinity'");
239 
240         // json
241         C!PGjson(Json(["float_value": Json(123.456), "text_str": Json("text string")]), "json", "'{\"float_value\": 123.456,\"text_str\": \"text string\"}'");
242 
243         //C!PGjson(Json(["integer": Json(123), "float": Json(123.456), "text_string": Json("This is a text string")]), "jsonb",
244             //"'{\"integer\": 123, \"float\": 123.456,\"text_string\": \"This is a text string\"}'");
245     }
246 }