1 /// Generates SQL query with appropriate variable types 2 module dpq2.query_gen; 3 4 import dpq2.args: QueryParams; 5 import dpq2.connection: Connection; 6 import std.conv: to; 7 import std.traits: isInstanceOf; 8 import std.array: appender; 9 import dpq2.conv.from_d_types: toValue; 10 11 private enum ArgLikeIn 12 { 13 INSERT, // looks like "FieldName" and passes value as appropriate variable 14 UPDATE, // looks like "FieldName" = $3 and passes value into appropriate dollar variable 15 } 16 17 private struct Arg(ArgLikeIn _argLikeIn, T) 18 { 19 enum argLikeIn = _argLikeIn; 20 21 string name; 22 T value; 23 } 24 25 private struct DollarArg(T) 26 { 27 T value; 28 } 29 30 /// INSERT-like argument 31 auto i(T)(string statementArgName, T value) 32 { 33 return Arg!(ArgLikeIn.INSERT, T)(statementArgName, value); 34 } 35 36 /// UPDATE-like argument 37 auto u(T)(string statementArgName, T value) 38 { 39 return Arg!(ArgLikeIn.UPDATE, T)(statementArgName, value); 40 } 41 42 /// Argument representing dollar, usable in SELECT statements 43 auto d(T)(T value) 44 { 45 return DollarArg!T(value); 46 } 47 48 private struct CTStatement(SQL_CMD...) 49 { 50 QueryParams qp; 51 alias qp this; 52 53 this(Connection conn, SQL_CMD sqlCmd) 54 { 55 qp = parseSqlCmd!SQL_CMD(conn, sqlCmd); 56 } 57 } 58 59 private string dollarsString(size_t num) 60 { 61 string ret; 62 63 foreach(i; 1 .. num+1) 64 { 65 ret ~= `$`; 66 ret ~= i.to!string; 67 68 if(i < num) 69 ret ~= `,`; 70 } 71 72 return ret; 73 } 74 75 private template isStatementArg(T) 76 { 77 enum isStatementArg = 78 isInstanceOf!(Arg, T) || 79 isInstanceOf!(DollarArg, T); 80 } 81 82 private bool symbolNeedsDelimit(dchar c) 83 { 84 import std.ascii: isAlphaNum; 85 86 return c == '$' || c.isAlphaNum; 87 } 88 89 private void concatWithDelimiter(A, T)(ref A appender, T val) 90 { 91 92 if( 93 val.length && 94 appender.data.length && 95 val[0].symbolNeedsDelimit && 96 appender.data[$-1].symbolNeedsDelimit 97 ) 98 appender ~= ' '; 99 100 appender ~= val; 101 } 102 103 private string escapeName(string s, Connection conn) 104 { 105 if(conn !is null) 106 return conn.escapeIdentifier(s); 107 else 108 return '"'~s~'"'; 109 } 110 111 private QueryParams parseSqlCmd(SQL_CMD...)(Connection conn, SQL_CMD sqlCmd) 112 { 113 QueryParams qp; 114 auto resultSql = appender!string; 115 116 foreach(i, V; sqlCmd) 117 { 118 // argument variable is found? 119 static if(isStatementArg!(typeof(V))) 120 { 121 // previous argument already was processed? 122 static if(i > 0 && isStatementArg!(typeof(sqlCmd[i-1]))) 123 { 124 resultSql ~= `,`; 125 } 126 127 static if(isInstanceOf!(DollarArg, typeof(V))) 128 { 129 resultSql.concatWithDelimiter(`$`); 130 resultSql ~= (qp.args.length + 1).to!string; 131 } 132 else static if(V.argLikeIn == ArgLikeIn.UPDATE) 133 { 134 resultSql ~= V.name.escapeName(conn); 135 resultSql ~= `=$`; 136 resultSql ~= (qp.args.length + 1).to!string; 137 } 138 else static if(V.argLikeIn == ArgLikeIn.INSERT) 139 { 140 resultSql ~= V.name.escapeName(conn); 141 } 142 else 143 static assert(false); 144 145 qp.args ~= V.value.toValue; 146 } 147 else 148 { 149 // Usable as INSERT VALUES ($1, $2, ...) argument 150 static if(is(typeof(V) == Dollars)) 151 { 152 resultSql ~= dollarsString(qp.args.length); 153 } 154 else 155 { 156 // ordinary part of SQL statement 157 resultSql.concatWithDelimiter(V); 158 } 159 } 160 } 161 162 qp.sqlCommand = resultSql[]; 163 164 return qp; 165 } 166 167 struct Dollars {} 168 169 /// 170 auto wrapStatement(C : Connection, T...)(C conn, T statement) 171 { 172 return CTStatement!T(conn, statement); 173 } 174 175 /// 176 auto wrapStatement(T...)(T statement) 177 if(!is(T[0] == Connection)) 178 { 179 return CTStatement!T(null, statement); 180 } 181 182 unittest 183 { 184 auto stmnt = wrapStatement(`abc=`, d(123)); 185 186 assert(stmnt.qp.sqlCommand == `abc=$1`); 187 assert(stmnt.qp.args.length == 1); 188 assert(stmnt.qp.args[0] == 123.toValue); 189 } 190 191 unittest 192 { 193 auto stmnt = wrapStatement( 194 `SELECT`, d!string("abc"), d!int(123) 195 ); 196 197 assert(stmnt.qp.args.length == 2); 198 assert(stmnt.qp.args[0] == "abc".toValue); 199 assert(stmnt.qp.args[1] == 123.toValue); 200 } 201 202 unittest 203 { 204 auto stmnt = wrapStatement( 205 `UPDATE table1`, 206 `SET`, 207 u(`boolean_field`, true), 208 u(`integer_field`, 123), 209 u(`text_field`, `abc`), 210 ); 211 212 assert(stmnt.qp.sqlCommand.length > 10); 213 assert(stmnt.qp.args.length == 3); 214 assert(stmnt.qp.args[0] == true.toValue); 215 assert(stmnt.qp.args[1] == 123.toValue); 216 assert(stmnt.qp.args[2] == `abc`.toValue); 217 } 218 219 unittest 220 { 221 int integer = 123; 222 int another_integer = 456; 223 string text = "abc"; 224 225 auto stmnt = wrapStatement( 226 `INSERT INTO table1 (`, 227 i(`integer_field`, integer), 228 i(`text_field`, text), 229 `) WHERE`, 230 u(`integer_field`, another_integer), 231 `VALUES(`, Dollars(),`)` 232 ); 233 234 assert(stmnt.qp.sqlCommand.length > 10); 235 assert(stmnt.qp.args[0] == 123.toValue); 236 assert(stmnt.qp.args[1] == `abc`.toValue); 237 assert(stmnt.qp.args[2] == 456.toValue); 238 } 239 240 version(integration_tests) 241 void _integration_test(string connParam) 242 { 243 import dpq2.connection: createTestConn; 244 245 auto conn = createTestConn(connParam); 246 auto stmnt = wrapStatement(conn, i("Some Integer", 123)); 247 248 assert(stmnt.qp.sqlCommand == `"Some Integer"`); 249 assert(stmnt.qp.args.length == 1); 250 assert(stmnt.qp.args[0] == 123.toValue); 251 }