monetdb.monetdb source code

1 module monetdb.monetdb;
2 
3 import std.string;
4 import std.exception;
5 import std.conv;
6 import std.datetime;
7 import std.range;
8 import std.variant;
9 
10 import monetdb.binding;
11 
12 alias Null = typeof(null);
13 
14 //TODO: support const type as well
15 alias Record = Algebraic!(int, short, long, double, char, bool, string, Date, DateTime, Null);
16 
17 class MonetDbException : Exception
18 {
19     this(string msg, string file = __FILE__, size_t line = __LINE__)
20     {
21         super(msg, file, line);
22     }
23 }
24 
25 protected Record dize(T)(const T typeName, const T value) if (is(T : string))
26 {
27     Record res;
28     if (value == "")
29     {
30         res = null;
31         return res;
32     }
33     if (typeName == "int")
34     {
35         res = value.to!int;
36     }
37     else if (typeName == "tinyint")
38     {
39         res = value.to!short;
40     }
41     else if (typeName == "bigint")
42     {
43         res = value.to!long;
44     }
45     else if (typeName == "double")
46     {
47         res = value.to!double;
48     }
49     else if (typeName == "date")
50     {
51         res = Date.fromISOExtString(value.to!string);
52     }
53     else if (typeName == "timestamp")
54     {
55         auto toExtISOFormat = (const string d) => d[0 .. 10] ~ "T" ~ d[11 .. 19];
56         res = DateTime.fromISOExtString(toExtISOFormat(value.to!string));
57     }
58     else if (typeName == "varchar" || typeName == "char")
59     {
60         res = value.to!string;
61     }
62     else
63     {
64         enforce!MonetDbException(false, "Could not d-ize type: " ~ typeName);
65     }
66     return res;
67 }
68 
69 protected MapiDate monetizeDate(const Date date)
70 {
71     return MapiDate(date.year, date.month, date.day);
72 }
73 
74 protected MapiDateTime monetizeDateTime(const DateTime date)
75 {
76     return MapiDateTime(date.year, date.month, date.day, date.hour, date.minute, date.second, 0);
77 }
78 
79 Record[] recordArray(T...)(T params)
80 {
81     Record[] records;
82     foreach (i, p; params)
83     {
84         records ~= Record(p);
85     }
86     return records;
87 }
88 
89 class MonetDb
90 {
91     private Mapi _mapi;
92 
93     this(string host, int port, string username, string password, string lang, string dbname) @trusted
94     {
95         _mapi = mapi_connect(toStringz(host), port, toStringz(username),
96                 toStringz(password), toStringz(lang), toStringz(dbname));
97     }
98 
99     ~this()
100     {
101         mapi_destroy(_mapi);
102     }
103 
104     private string errorMessage(MapiHdl handler)
105     {
106         auto msg = mapi_error_str(_mapi);
107         if (msg)
108         {
109             return to!string(msg);
110         }
111         return to!string(mapi_result_error(handler));
112     }
113 
114     private int getMapiType(Record p)
115     {
116         return p.visit!((int a) => MAPI_INT, (short a) => MAPI_SHORT,
117                 (long a) => MAPI_LONG, (char a) => MAPI_CHAR, (string a) => MAPI_VARCHAR,
118                 (double a) => MAPI_DOUBLE, (Date a) => MAPI_DATE,
119                 (DateTime a) => MAPI_DATETIME, (bool a) => MAPI_USHORT, (Null a) => -1)();
120     }
121 
122     private MapiHdl buildHandler(string command, Record[] params)
123     {
124         MapiHdl result;
125         if (params is null)
126         {
127             result = mapi_query(_mapi, toStringz(command));
128         }
129         else
130         {
131             result = mapi_prepare(_mapi, toStringz(command));
132             foreach (i, p; params)
133             {
134                 auto mapiType = getMapiType(p);
135                 enforce!MonetDbException(mapiType >= 0,
136                         "Parameter having a null value are not supported!");
137                 if (p.type == typeid(Date))
138                 {
139                     auto d = p.get!Date.monetizeDate;
140                     mapi_param_type(result, i.to!int, mapiType, mapiType, &d);
141                 }
142                 else if (p.type == typeid(DateTime))
143                 {
144                     auto d = p.get!DateTime.monetizeDateTime;
145                     mapi_param_type(result, i.to!int, mapiType, mapiType, &d);
146                 }
147                 else if (p.type == typeid(string))
148                 {
149                     auto s = cast(char*) p.get!string.toStringz;
150                     mapi_param_type(result, i.to!int, mapiType, mapiType, s);
151                 }
152                 else if (p.type == typeid(int))
153                 {
154                     auto v = p.get!int;
155                     mapi_param_type(result, i.to!int, mapiType, mapiType, &v);
156                 }
157                 else if (p.type == typeid(long))
158                 {
159                     auto v = p.get!long;
160                     mapi_param_type(result, i.to!int, mapiType, mapiType, &v);
161                 }
162                 else if (p.type == typeid(double))
163                 {
164                     auto v = p.get!double;
165                     mapi_param_type(result, i.to!int, mapiType, mapiType, &v);
166                 }
167                 else if (p.type == typeid(char))
168                 {
169                     auto v = p.get!char;
170                     mapi_param_type(result, i.to!int, mapiType, mapiType, &v);
171                 }
172                 else if (p.type == typeid(bool))
173                 {
174                     auto v = p.get!bool;
175                     mapi_param_type(result, i.to!int, mapiType, mapiType, &v);
176                 }
177                 else
178                 {
179                     assert(false);
180                 }
181             }
182             mapi_execute(result);
183         }
184         enforce!MonetDbException(mapi_error(_mapi) == MOK, errorMessage(result));
185 
186         return result;
187     }
188 
189     void exec(string command, Record[] params = null)
190     {
191         auto result = buildHandler(command, params);
192         mapi_close_handle(result);
193     }
194 
195     auto query(string command, Record[] params = null)
196     {
197         auto result = buildHandler(command, params);
198 
199         static struct QueryResult
200         {
201             private MapiHdl handler_;
202             private bool hasRow_;
203             private int count_;
204 
205             this(MapiHdl handler)
206             {
207                 handler_ = handler;
208                 fetchRow();
209             }
210 
211             auto empty()
212             {
213                 return !hasRow_;
214             }
215 
216             private void fetchRow()
217             {
218                 hasRow_ = mapi_fetch_row(handler_) != 0;
219                 count_ = mapi_get_field_count(handler_);
220             }
221 
222             auto front()
223             {
224                 assert(!empty);
225 
226                 Record[string] records;
227                 foreach (i; iota(count_))
228                 {
229                     auto name = mapi_get_name(handler_, i).to!string;
230                     auto typeName = mapi_get_type(handler_, i).to!string;
231                     auto value = mapi_fetch_field(handler_, i).to!string;
232                     records[name] = dize(typeName, value);
233                 }
234                 return records;
235             }
236 
237             void popFront()
238             {
239                 assert(!empty);
240                 fetchRow();
241                 if (!hasRow_)
242                     mapi_close_handle(handler_);
243             }
244         }
245 
246         return QueryResult(result);
247     }
248 
249     void close()
250     {
251         mapi_disconnect(_mapi);
252     }
253 }
254 
255 unittest
256 {
257     auto conn = new MonetDb("localhost", 50_000, "monetdb", "monetdb", "sql", "16megabytes");
258     scope (exit)
259         conn.close();
260 
261     conn.exec("DROP TABLE IF EXISTS FOO;");
262     conn.exec(`CREATE TABLE IF NOT EXISTS FOO (
263             ID INTEGER NOT NULL,
264             VALUE VARCHAR(5),
265             RATIO DOUBLE,
266             CREATION DATE,
267             SYSDATE TIMESTAMP DEFAULT NOW,
268             FLAG BOOLEAN DEFAULT FALSE,
269             UNIT CHAR DEFAULT 'U'
270         );`);
271     conn.exec("INSERT INTO FOO (ID, VALUE, RATIO, CREATION) VALUES (1, 'foo', .5, '2018-01-01');");
272     Record[] p1 = recordArray(2, "bar", .63, Date(2018, 1, 1));
273     conn.exec("INSERT INTO FOO (ID, VALUE, RATIO, CREATION) VALUES (?, ?, ?, ?);", p1);
274     Record[] p2 = recordArray(3, Date(2019, 3, 31));
275     conn.exec("INSERT INTO FOO (ID, CREATION) VALUES (?, ?)", p2);
276 
277     auto r = conn.query("SELECT ID, VALUE, CREATION, SYSDATE, RATIO FROM FOO ORDER BY ID");
278     auto row = r.front;
279     assert("id" in row);
280     assert("value" in row);
281     assert("creation" in row);
282     assert(row["id"].get!int == 1);
283     assert(row["value"].get!string == "foo");
284     assert(row["creation"].get!Date == Date(2018, 1, 1));
285     r.popFront();
286     auto row2 = r.front;
287     assert(row2["id"].get!int == 2);
288     assert(row2["value"].get!string == "bar");
289     assert(row2["creation"].get!Date == Date(2018, 1, 1));
290     r.popFront();
291     auto row3 = r.front;
292     assert(row3["value"].get!Null is null);
293     assert(row3["ratio"].get!Null is null);
294 
295     Record[] params = recordArray(1, "foo");
296     auto rp = conn.query(
297             "SELECT ID, VALUE, RATIO, CREATION FROM FOO WHERE ID = ? AND VALUE = ?", params);
298     assert(rp.front["id"].get!int == 1);
299     assert(rp.front["value"].get!string == "foo");
300     assert(rp.front["ratio"].get!double == .5);
301     assert(rp.front["creation"].get!Date == Date(2018, 1, 1));
302 
303     auto rl = conn.query("SELECT COUNT(*) AS V, ID FROM FOO WHERE FLAG = ? AND SYSDATE > ? AND UNIT = ? GROUP BY ID HAVING COUNT(*) = ?;",
304             recordArray(false, DateTime(1970, 1, 1, 0, 0, 0), 'U', 1L));
305     assert(rl.front["v"].get!long == 1L);
306 
307     conn.exec("DROP TABLE FOO;");
308 }
309 
310 unittest
311 {
312     auto conn = new MonetDb("localhost", 50_000, "monetdb", "monetdb", "sql", "16megabytes");
313     scope (exit)
314         conn.close();
315 
316     conn.exec(
317             "CREATE TABLE IF NOT EXISTS FOO (ID INT); INSERT INTO FOO (ID) VALUES (1), (2); DROP TABLE FOO;");
318 }