1 /++ 2 Use a DB via plain SQL statements. 3 4 Commands that are expected to return a result set - queries - have distinctive 5 methods that are enforced. That is it will be an error to call such a method 6 with an SQL command that does not produce a result set. So for commands like 7 SELECT, use the `query` functions. For other commands, like 8 INSERT/UPDATE/CREATE/etc, use `exec`. 9 +/ 10 11 module mysql.commands; 12 13 import std.conv; 14 import std.exception; 15 import std.range; 16 import std.typecons; 17 import std.variant; 18 19 import mysql.connection; 20 import mysql.exceptions; 21 import mysql.prepared; 22 import mysql.protocol.constants; 23 import mysql.protocol.extra_types; 24 import mysql.protocol.packets; 25 import mysql.result; 26 27 /++ 28 A struct to represent specializations of prepared statement parameters. 29 30 If you are executing a query that will include result columns that are large objects, 31 it may be expedient to deal with the data as it is received rather than first buffering 32 it to some sort of byte array. These two variables allow for this. If both are provided 33 then the corresponding column will be fed to the stipulated delegate in chunks of 34 `chunkSize`, with the possible exception of the last chunk, which may be smaller. 35 The bool argument `finished` will be set to true when the last chunk is set. 36 37 Be aware when specifying types for column specializations that for some reason the 38 field descriptions returned for a resultset have all of the types TINYTEXT, MEDIUMTEXT, 39 TEXT, LONGTEXT, TINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOB lumped as type 0xfc 40 contrary to what it says in the protocol documentation. 41 +/ 42 struct ColumnSpecialization 43 { 44 size_t cIndex; // parameter number 0 - number of params-1 45 ushort type; 46 uint chunkSize; 47 void delegate(const(ubyte)[] chunk, bool finished) chunkDelegate; 48 } 49 ///ditto 50 alias CSN = ColumnSpecialization; 51 52 package struct ExecQueryImplInfo 53 { 54 bool isPrepared; 55 56 // For non-prepared statements: 57 string sql; 58 59 // For prepared statements: 60 uint hStmt; 61 PreparedStmtHeaders psh; 62 Variant[] inParams; 63 ParameterSpecialization[] psa; 64 } 65 66 /++ 67 Internal implementation for the exec and query functions. 68 69 Execute a one-off SQL command. 70 71 Any result set can be accessed via Connection.getNextRow(), but you should really be 72 using the query function for such queries. 73 74 Params: ra = An out parameter to receive the number of rows affected. 75 Returns: true if there was a (possibly empty) result set. 76 +/ 77 //TODO: All low-level commms should be moved into the mysql.protocol package. 78 package bool execQueryImpl(Connection conn, ExecQueryImplInfo info, out ulong ra) 79 { 80 scope(failure) conn.kill(); 81 82 // Send data 83 if(info.isPrepared) 84 ProtocolPrepared.sendCommand(conn, info.hStmt, info.psh, info.inParams, info.psa); 85 else 86 { 87 conn.sendCmd(CommandType.QUERY, info.sql); 88 conn._fieldCount = 0; 89 } 90 91 // Handle response 92 ubyte[] packet = conn.getPacket(); 93 bool rv; 94 if (packet.front == ResultPacketMarker.ok || packet.front == ResultPacketMarker.error) 95 { 96 conn.resetPacket(); 97 auto okp = OKErrorPacket(packet); 98 enforcePacketOK(okp); 99 ra = okp.affected; 100 conn._serverStatus = okp.serverStatus; 101 conn._insertID = okp.insertID; 102 rv = false; 103 } 104 else 105 { 106 // There was presumably a result set 107 assert(packet.front >= 1 && packet.front <= 250); // Result set packet header should have this value 108 conn._headersPending = conn._rowsPending = true; 109 conn._binaryPending = info.isPrepared; 110 auto lcb = packet.consumeIfComplete!LCB(); 111 assert(!lcb.isNull); 112 assert(!lcb.isIncomplete); 113 conn._fieldCount = cast(ushort)lcb.value; 114 assert(conn._fieldCount == lcb.value); 115 rv = true; 116 ra = 0; 117 } 118 return rv; 119 } 120 121 ///ditto 122 package bool execQueryImpl(Connection conn, ExecQueryImplInfo info) 123 { 124 ulong rowsAffected; 125 return execQueryImpl(conn, info, rowsAffected); 126 } 127 128 /++ 129 Execute an SQL command or prepared statement, such as INSERT/UPDATE/CREATE/etc. 130 131 This method is intended for commands such as which do not produce a result set 132 (otherwise, use one of the `query` functions instead.) If the SQL command does 133 produces a result set (such as SELECT), `mysql.exceptions.MYXResultRecieved` 134 will be thrown. 135 136 Only use the `string sql` overload when you are not going to be using the same 137 command repeatedly and you are CERTAIN all the data you're sending is properly 138 escaped. Otherwise, consider using overload that takes a `Prepared`. 139 140 Type_Mappings: $(TYPE_MAPPINGS) 141 142 Params: 143 conn = An open `mysql.connection.Connection` to the database. 144 sql = The SQL command to be run. 145 prepared = The prepared statement to be run. 146 147 Returns: The number of rows affected. 148 +/ 149 ulong exec(Connection conn, string sql) 150 { 151 return execImpl(conn, ExecQueryImplInfo(false, sql)); 152 } 153 154 ///ditto 155 ulong exec(Connection conn, ref Prepared prepared) 156 { 157 auto preparedInfo = conn.registerIfNeeded(prepared.sql); 158 auto ra = execImpl(conn, prepared.getExecQueryImplInfo(preparedInfo.statementId)); 159 prepared._lastInsertID = conn.lastInsertID; 160 return ra; 161 } 162 163 ///ditto 164 ulong exec(Connection conn, ref BackwardCompatPrepared prepared) 165 { 166 auto p = prepared.prepared; 167 auto result = exec(conn, p); 168 prepared._prepared = p; 169 return result; 170 } 171 172 /// Common implementation for `exec` overloads 173 package ulong execImpl(Connection conn, ExecQueryImplInfo info) 174 { 175 ulong rowsAffected; 176 bool receivedResultSet = execQueryImpl(conn, info, rowsAffected); 177 if(receivedResultSet) 178 { 179 conn.purgeResult(); 180 throw new MYXResultRecieved(); 181 } 182 183 return rowsAffected; 184 } 185 186 /++ 187 Execute an SQL SELECT command or prepared statement. 188 189 This returns an input range of `mysql.result.Row`, so if you need random access 190 to the `mysql.result.Row` elements, simply call 191 $(LINK2 https://dlang.org/phobos/std_array.html#array, `std.array.array()`) 192 on the result. 193 194 If the SQL command does not produce a result set (such as INSERT/CREATE/etc), 195 then `mysql.exceptions.MYXNoResultRecieved` will be thrown. Use 196 `exec` instead for such commands. 197 198 Only use the `string sql` overload when you are not going to be using the same 199 command repeatedly and you are CERTAIN all the data you're sending is properly 200 escaped. Otherwise, consider using overload that takes a `Prepared`. 201 202 If there are long data items among the expected result columns you can use 203 the `csa` param to specify that they are to be subject to chunked transfer via a 204 delegate. 205 206 Type_Mappings: $(TYPE_MAPPINGS) 207 208 Params: 209 conn = An open `mysql.connection.Connection` to the database. 210 sql = The SQL command to be run. 211 prepared = The prepared statement to be run. 212 csa = An optional array of `ColumnSpecialization` structs. 213 214 Returns: A (possibly empty) `mysql.result.ResultRange`. 215 216 Example: 217 --- 218 ResultRange oneAtATime = myConnection.query("SELECT * from myTable"); 219 Row[] allAtOnce = myConnection.query("SELECT * from myTable").array; 220 --- 221 +/ 222 ResultRange query(Connection conn, string sql, ColumnSpecialization[] csa = null) 223 { 224 return queryImpl(csa, conn, ExecQueryImplInfo(false, sql)); 225 } 226 227 ///ditto 228 ResultRange query(Connection conn, ref Prepared prepared, ColumnSpecialization[] csa = null) 229 { 230 auto preparedInfo = conn.registerIfNeeded(prepared.sql); 231 auto result = queryImpl(csa, conn, prepared.getExecQueryImplInfo(preparedInfo.statementId)); 232 prepared._lastInsertID = conn.lastInsertID; // Conceivably, this might be needed when multi-statements are enabled. 233 return result; 234 } 235 236 ///ditto 237 ResultRange query(Connection conn, ref BackwardCompatPrepared prepared, ColumnSpecialization[] csa = null) 238 { 239 auto p = prepared.prepared; 240 auto result = query(conn, p, csa); 241 prepared._prepared = p; 242 return result; 243 } 244 245 /// Common implementation for `query` overloads 246 package ResultRange queryImpl(ColumnSpecialization[] csa, 247 Connection conn, ExecQueryImplInfo info) 248 { 249 ulong ra; 250 enforceEx!MYXNoResultRecieved(execQueryImpl(conn, info, ra)); 251 252 conn._rsh = ResultSetHeaders(conn, conn._fieldCount); 253 if (csa !is null) 254 conn._rsh.addSpecializations(csa); 255 256 conn._headersPending = false; 257 return ResultRange(conn, conn._rsh, conn._rsh.fieldNames); 258 } 259 260 /++ 261 Execute an SQL SELECT command or prepared statement where you only want the 262 first `mysql.result.Row`, if any. 263 264 If the SQL command does not produce a result set (such as INSERT/CREATE/etc), 265 then `mysql.exceptions.MYXNoResultRecieved` will be thrown. Use 266 `exec` instead for such commands. 267 268 Only use the `string sql` overload when you are not going to be using the same 269 command repeatedly and you are CERTAIN all the data you're sending is properly 270 escaped. Otherwise, consider using overload that takes a `Prepared`. 271 272 If there are long data items among the expected result columns you can use 273 the `csa` param to specify that they are to be subject to chunked transfer via a 274 delegate. 275 276 Type_Mappings: $(TYPE_MAPPINGS) 277 278 Params: 279 conn = An open `mysql.connection.Connection` to the database. 280 sql = The SQL command to be run. 281 prepared = The prepared statement to be run. 282 csa = An optional array of `ColumnSpecialization` structs. 283 284 Returns: `Nullable!(mysql.result.Row)`: This will be null (check via `Nullable.isNull`) if the 285 query resulted in an empty result set. 286 +/ 287 Nullable!Row queryRow(Connection conn, string sql, ColumnSpecialization[] csa = null) 288 { 289 return queryRowImpl(csa, conn, ExecQueryImplInfo(false, sql)); 290 } 291 292 ///ditto 293 Nullable!Row queryRow(Connection conn, ref Prepared prepared, ColumnSpecialization[] csa = null) 294 { 295 auto preparedInfo = conn.registerIfNeeded(prepared.sql); 296 auto result = queryRowImpl(csa, conn, prepared.getExecQueryImplInfo(preparedInfo.statementId)); 297 prepared._lastInsertID = conn.lastInsertID; // Conceivably, this might be needed when multi-statements are enabled. 298 return result; 299 } 300 301 ///ditto 302 Nullable!Row queryRow(Connection conn, ref BackwardCompatPrepared prepared, ColumnSpecialization[] csa = null) 303 { 304 auto p = prepared.prepared; 305 auto result = queryRow(conn, p, csa); 306 prepared._prepared = p; 307 return result; 308 } 309 310 /// Common implementation for `querySet` overloads. 311 package Nullable!Row queryRowImpl(ColumnSpecialization[] csa, Connection conn, 312 ExecQueryImplInfo info) 313 { 314 auto results = queryImpl(csa, conn, info); 315 if(results.empty) 316 return Nullable!Row(); 317 else 318 { 319 auto row = results.front; 320 results.close(); 321 return Nullable!Row(row); 322 } 323 } 324 325 /++ 326 Execute an SQL SELECT command or prepared statement where you only want the 327 first `mysql.result.Row`, and place result values into a set of D variables. 328 329 This method will throw if any column type is incompatible with the corresponding D variable. 330 331 Unlike the other query functions, queryRowTuple will throw 332 `mysql.exceptions.MYX` if the result set is empty 333 (and thus the reference variables passed in cannot be filled). 334 335 If the SQL command does not produce a result set (such as INSERT/CREATE/etc), 336 then `mysql.exceptions.MYXNoResultRecieved` will be thrown. Use 337 `exec` instead for such commands. 338 339 Only use the `string sql` overload when you are not going to be using the same 340 command repeatedly and you are CERTAIN all the data you're sending is properly 341 escaped. Otherwise, consider using overload that takes a `Prepared`. 342 343 Type_Mappings: $(TYPE_MAPPINGS) 344 345 Params: 346 conn = An open `mysql.connection.Connection` to the database. 347 sql = The SQL command to be run. 348 prepared = The prepared statement to be run. 349 args = The variables, taken by reference, to receive the values. 350 +/ 351 void queryRowTuple(T...)(Connection conn, string sql, ref T args) 352 { 353 return queryRowTupleImpl(conn, ExecQueryImplInfo(false, sql), args); 354 } 355 356 ///ditto 357 void queryRowTuple(T...)(Connection conn, ref Prepared prepared, ref T args) 358 { 359 auto preparedInfo = conn.registerIfNeeded(prepared.sql); 360 queryRowTupleImpl(conn, prepared.getExecQueryImplInfo(preparedInfo.statementId), args); 361 prepared._lastInsertID = conn.lastInsertID; // Conceivably, this might be needed when multi-statements are enabled. 362 } 363 364 ///ditto 365 void queryRowTuple(T...)(Connection conn, ref BackwardCompatPrepared prepared, ref T args) 366 { 367 auto p = prepared.prepared; 368 queryRowTuple(conn, p, args); 369 prepared._prepared = p; 370 } 371 372 /// Common implementation for `queryRowTuple` overloads. 373 package void queryRowTupleImpl(T...)(Connection conn, ExecQueryImplInfo info, ref T args) 374 { 375 ulong ra; 376 enforceEx!MYXNoResultRecieved(execQueryImpl(conn, info, ra)); 377 378 Row rr = conn.getNextRow(); 379 /+if (!rr._valid) // The result set was empty - not a crime. 380 return;+/ 381 enforceEx!MYX(rr._values.length == args.length, "Result column count does not match the target tuple."); 382 foreach (size_t i, dummy; args) 383 { 384 enforceEx!MYX(typeid(args[i]).toString() == rr._values[i].type.toString(), 385 "Tuple "~to!string(i)~" type and column type are not compatible."); 386 args[i] = rr._values[i].get!(typeof(args[i])); 387 } 388 // If there were more rows, flush them away 389 // Question: Should I check in purgeResult and throw if there were - it's very inefficient to 390 // allow sloppy SQL that does not ensure just one row! 391 conn.purgeResult(); 392 } 393 394 // Test what happends when queryRowTuple receives no rows 395 debug(MYSQLN_TESTS) 396 unittest 397 { 398 import mysql.prepared; 399 import mysql.test.common : scopedCn, createCn; 400 mixin(scopedCn); 401 402 cn.exec("DROP TABLE IF EXISTS `queryRowTuple`"); 403 cn.exec("CREATE TABLE `queryRowTuple` ( 404 `val` INTEGER 405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); 406 407 immutable selectSQL = "SELECT * FROM `queryRowTuple`"; 408 int queryTupleResult; 409 assertThrown!MYX(cn.queryRowTuple(selectSQL, queryTupleResult)); 410 } 411 412 /++ 413 Execute an SQL SELECT command or prepared statement and return a single value: 414 the first column of the first row received. 415 416 If the query did not produce any rows, or the rows it produced have zero columns, 417 this will return `Nullable!Variant()`, ie, null. Test for this with `result.isNull`. 418 419 If the query DID produce a result, but the value actually received is NULL, 420 then `result.isNull` will be FALSE, and `result.get` will produce a Variant 421 which CONTAINS null. Check for this with `result.get.type == typeid(typeof(null))`. 422 423 If the SQL command does not produce a result set (such as INSERT/CREATE/etc), 424 then `mysql.exceptions.MYXNoResultRecieved` will be thrown. Use 425 `exec` instead for such commands. 426 427 Only use the `string sql` overload when you are not going to be using the same 428 command repeatedly and you are CERTAIN all the data you're sending is properly 429 escaped. Otherwise, consider using overload that takes a `Prepared`. 430 431 If there are long data items among the expected result columns you can use 432 the `csa` param to specify that they are to be subject to chunked transfer via a 433 delegate. 434 435 Type_Mappings: $(TYPE_MAPPINGS) 436 437 Params: 438 conn = An open `mysql.connection.Connection` to the database. 439 sql = The SQL command to be run. 440 prepared = The prepared statement to be run. 441 csa = An optional array of `ColumnSpecialization` structs. 442 443 Returns: `Nullable!Variant`: This will be null (check via `Nullable.isNull`) if the 444 query resulted in an empty result set. 445 +/ 446 Nullable!Variant queryValue(Connection conn, string sql, ColumnSpecialization[] csa = null) 447 { 448 return queryValueImpl(csa, conn, ExecQueryImplInfo(false, sql)); 449 } 450 451 ///ditto 452 Nullable!Variant queryValue(Connection conn, ref Prepared prepared, ColumnSpecialization[] csa = null) 453 { 454 auto preparedInfo = conn.registerIfNeeded(prepared.sql); 455 auto result = queryValueImpl(csa, conn, prepared.getExecQueryImplInfo(preparedInfo.statementId)); 456 prepared._lastInsertID = conn.lastInsertID; // Conceivably, this might be needed when multi-statements are enabled. 457 return result; 458 } 459 460 ///ditto 461 Nullable!Variant queryValue(Connection conn, ref BackwardCompatPrepared prepared, ColumnSpecialization[] csa = null) 462 { 463 auto p = prepared.prepared; 464 auto result = queryValue(conn, p, csa); 465 prepared._prepared = p; 466 return result; 467 } 468 469 /// Common implementation for `queryValue` overloads. 470 package Nullable!Variant queryValueImpl(ColumnSpecialization[] csa, Connection conn, 471 ExecQueryImplInfo info) 472 { 473 auto results = queryImpl(csa, conn, info); 474 if(results.empty) 475 return Nullable!Variant(); 476 else 477 { 478 auto row = results.front; 479 results.close(); 480 481 if(row.length == 0) 482 return Nullable!Variant(); 483 else 484 return Nullable!Variant(row[0]); 485 } 486 }