1 /// Structures for data received: rows and result sets (ie, a range of rows). 2 module mysql.result; 3 4 import std.conv; 5 import std.exception; 6 import std.range; 7 import std.string; 8 import std.variant; 9 10 import mysql.connection; 11 import mysql.exceptions; 12 import mysql.protocol.comms; 13 import mysql.protocol.extra_types; 14 import mysql.protocol.packets; 15 16 /++ 17 A struct to represent a single row of a result set. 18 19 Type_Mappings: $(TYPE_MAPPINGS) 20 +/ 21 /+ 22 The row struct is used for both 'traditional' and 'prepared' result sets. 23 It consists of parallel arrays of Variant and bool, with the bool array 24 indicating which of the result set columns are NULL. 25 26 I have been agitating for some kind of null indicator that can be set for a 27 Variant without destroying its inherent type information. If this were the 28 case, then the bool array could disappear. 29 +/ 30 struct Row 31 { 32 import mysql.connection; 33 34 package: 35 Variant[] _values; // Temporarily "package" instead of "private" 36 private: 37 bool[] _nulls; 38 39 public: 40 41 /++ 42 A constructor to extract the column data from a row data packet. 43 44 If the data for the row exceeds the server's maximum packet size, then several packets will be 45 sent for the row that taken together constitute a logical row data packet. The logic of the data 46 recovery for a Row attempts to minimize the quantity of data that is bufferred. Users can assist 47 in this by specifying chunked data transfer in cases where results sets can include long 48 column values. 49 50 Type_Mappings: $(TYPE_MAPPINGS) 51 +/ 52 this(Connection con, ref ubyte[] packet, ResultSetHeaders rh, bool binary) 53 { 54 ctorRow(con, packet, rh, binary, _values, _nulls); 55 } 56 57 /++ 58 Simplify retrieval of a column value by index. 59 60 To check for null, use Variant's `type` property: 61 `row[index].type == typeid(typeof(null))` 62 63 Type_Mappings: $(TYPE_MAPPINGS) 64 65 Params: i = the zero based index of the column whose value is required. 66 Returns: A Variant holding the column value. 67 +/ 68 inout(Variant) opIndex(size_t i) inout 69 { 70 enforce!MYX(_nulls.length > 0, format("Cannot get column index %d. There are no columns", i)); 71 enforce!MYX(i < _nulls.length, format("Cannot get column index %d. The last available index is %d", i, _nulls.length-1)); 72 return _values[i]; 73 } 74 75 /++ 76 Check if a column in the result row was NULL 77 78 Params: i = The zero based column index. 79 +/ 80 bool isNull(size_t i) const pure nothrow { return _nulls[i]; } 81 82 /++ 83 Get the number of elements (columns) in this row. 84 +/ 85 @property size_t length() const pure nothrow { return _values.length; } 86 87 ///ditto 88 alias opDollar = length; 89 90 /++ 91 Move the content of the row into a compatible struct 92 93 This method takes no account of NULL column values. If a column was NULL, 94 the corresponding Variant value would be unchanged in those cases. 95 96 The method will throw if the type of the Variant is not implicitly 97 convertible to the corresponding struct member. 98 99 Type_Mappings: $(TYPE_MAPPINGS) 100 101 Params: 102 S = A struct type. 103 s = A ref instance of the type 104 +/ 105 void toStruct(S)(ref S s) if (is(S == struct)) 106 { 107 foreach (i, dummy; s.tupleof) 108 { 109 static if(__traits(hasMember, s.tupleof[i], "nullify") && 110 is(typeof(s.tupleof[i].nullify())) && is(typeof(s.tupleof[i].get))) 111 { 112 if(!_nulls[i]) 113 { 114 enforce!MYX(_values[i].convertsTo!(typeof(s.tupleof[i].get))(), 115 "At col "~to!string(i)~" the value is not implicitly convertible to the structure type"); 116 s.tupleof[i] = _values[i].get!(typeof(s.tupleof[i].get)); 117 } 118 else 119 s.tupleof[i].nullify(); 120 } 121 else 122 { 123 if(!_nulls[i]) 124 { 125 enforce!MYX(_values[i].convertsTo!(typeof(s.tupleof[i]))(), 126 "At col "~to!string(i)~" the value is not implicitly convertible to the structure type"); 127 s.tupleof[i] = _values[i].get!(typeof(s.tupleof[i])); 128 } 129 else 130 s.tupleof[i] = typeof(s.tupleof[i]).init; 131 } 132 } 133 } 134 135 void show() 136 { 137 import std.stdio; 138 139 foreach(Variant v; _values) 140 writef("%s, ", v.toString()); 141 writeln(""); 142 } 143 } 144 145 /++ 146 An $(LINK2 http://dlang.org/phobos/std_range_primitives.html#isInputRange, input range) 147 of Row. 148 149 This is returned by the `mysql.commands.query` functions. 150 151 The rows are downloaded one-at-a-time, as you iterate the range. This allows 152 for low memory usage, and quick access to the results as they are downloaded. 153 This is especially ideal in case your query results in a large number of rows. 154 155 However, because of that, this `ResultRange` cannot offer random access or 156 a `length` member. If you need random access, then just like any other range, 157 you can simply convert this range to an array via 158 $(LINK2 https://dlang.org/phobos/std_array.html#array, `std.array.array()`). 159 160 A `ResultRange` becomes invalidated (and thus cannot be used) when the server 161 is sent another command on the same connection. When an invalidated `ResultRange` 162 is used, a `mysql.exceptions.MYXInvalidatedRange` is thrown. If you need to 163 send the server another command, but still access these results afterwords, 164 you can save the results for later by converting this range to an array via 165 $(LINK2 https://dlang.org/phobos/std_array.html#array, `std.array.array()`). 166 167 Type_Mappings: $(TYPE_MAPPINGS) 168 169 Example: 170 --- 171 ResultRange oneAtATime = myConnection.query("SELECT * from myTable"); 172 Row[] allAtOnce = myConnection.query("SELECT * from myTable").array; 173 --- 174 +/ 175 struct ResultRange 176 { 177 private: 178 Connection _con; 179 ResultSetHeaders _rsh; 180 Row _row; // current row 181 string[] _colNames; 182 size_t[string] _colNameIndicies; 183 ulong _numRowsFetched; 184 ulong _commandID; // So we can keep track of when this is invalidated 185 186 void ensureValid() const pure 187 { 188 enforce!MYXInvalidatedRange(isValid, 189 "This ResultRange has been invalidated and can no longer be used."); 190 } 191 192 package: 193 this (Connection con, ResultSetHeaders rsh, string[] colNames) 194 { 195 _con = con; 196 _rsh = rsh; 197 _colNames = colNames; 198 _commandID = con.lastCommandID; 199 popFront(); 200 } 201 202 public: 203 /++ 204 Check whether the range can still be used, or has been invalidated. 205 206 A `ResultRange` becomes invalidated (and thus cannot be used) when the server 207 is sent another command on the same connection. When an invalidated `ResultRange` 208 is used, a `mysql.exceptions.MYXInvalidatedRange` is thrown. If you need to 209 send the server another command, but still access these results afterwords, 210 you can save the results for later by converting this range to an array via 211 $(LINK2 https://dlang.org/phobos/std_array.html#array, `std.array.array()`). 212 +/ 213 @property bool isValid() const pure nothrow 214 { 215 return _con !is null && _commandID == _con.lastCommandID; 216 } 217 218 /// Check whether there are any rows left 219 @property bool empty() const pure nothrow 220 { 221 if(!isValid) 222 return true; 223 224 return !_con._rowsPending; 225 } 226 227 /++ 228 Gets the current row 229 +/ 230 @property inout(Row) front() pure inout 231 { 232 ensureValid(); 233 enforce!MYX(!empty, "Attempted 'front' on exhausted result sequence."); 234 return _row; 235 } 236 237 /++ 238 Progresses to the next row of the result set - that will then be 'front' 239 +/ 240 void popFront() 241 { 242 ensureValid(); 243 enforce!MYX(!empty, "Attempted 'popFront' when no more rows available"); 244 _row = _con.getNextRow(); 245 _numRowsFetched++; 246 } 247 248 /++ 249 Get the current row as an associative array by column name 250 251 Type_Mappings: $(TYPE_MAPPINGS) 252 +/ 253 Variant[string] asAA() 254 { 255 ensureValid(); 256 enforce!MYX(!empty, "Attempted 'front' on exhausted result sequence."); 257 Variant[string] aa; 258 foreach (size_t i, string s; _colNames) 259 aa[s] = _row._values[i]; 260 return aa; 261 } 262 263 /// Get the names of all the columns 264 @property const(string)[] colNames() const pure nothrow { return _colNames; } 265 266 /// An AA to lookup a column's index by name 267 @property const(size_t[string]) colNameIndicies() pure nothrow 268 { 269 if(_colNameIndicies is null) 270 { 271 foreach(index, name; _colNames) 272 _colNameIndicies[name] = index; 273 } 274 275 return _colNameIndicies; 276 } 277 278 /// Explicitly clean up the MySQL resources and cancel pending results 279 void close() 280 out{ assert(!isValid); } 281 body 282 { 283 if(isValid) 284 _con.purgeResult(); 285 } 286 287 /++ 288 Get the number of rows retrieved so far. 289 290 Note that this is not neccessarlly the same as the length of the range. 291 +/ 292 @property ulong rowCount() const pure nothrow { return _numRowsFetched; } 293 }