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 2
    return Arg!(ArgLikeIn.INSERT, T)(statementArgName, value);
34
}
35

36
/// UPDATE-like argument
37
auto u(T)(string statementArgName, T value)
38
{
39 2
    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 2
    return DollarArg!T(value);
46
}
47

48
private struct CTStatement(SQL_CMD...)
49
{
50
    QueryParams qp;
51
    alias qp this;
52

53 2
    this(Connection conn, SQL_CMD sqlCmd)
54
    {
55 2
        qp = parseSqlCmd!SQL_CMD(conn, sqlCmd);
56
    }
57
}
58

59
private string dollarsString(size_t num)
60
{
61 2
    string ret;
62

63 2
    foreach(i; 1 .. num+1)
64
    {
65 2
          ret ~= `$`;
66 2
          ret ~= i.to!string;
67

68 2
          if(i < num)
69 2
                ret ~= `,`;
70
    }
71

72 2
    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 2
    return c == '$' || c.isAlphaNum;
87
}
88

89
private void concatWithDelimiter(A, T)(ref A appender, T val)
90
{
91

92 2
    if(
93
        val.length &&
94 2
        appender.data.length &&
95 2
        val[0].symbolNeedsDelimit &&
96 2
        appender.data[$-1].symbolNeedsDelimit
97
    )
98 2
        appender ~= ' ';
99

100 2
    appender ~= val;
101
}
102

103
private string escapeName(string s, Connection conn)
104
{
105 2
    if(conn !is null)
106 0
        return conn.escapeIdentifier(s);
107
    else
108 2
        return '"'~s~'"';
109
}
110

111
private QueryParams parseSqlCmd(SQL_CMD...)(Connection conn, SQL_CMD sqlCmd)
112
{
113 2
    QueryParams qp;
114 2
    auto resultSql = appender!string;
115

116 2
    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 2
                resultSql ~= `,`;
125
            }
126

127
            static if(isInstanceOf!(DollarArg, typeof(V)))
128
            {
129 2
                resultSql.concatWithDelimiter(`$`);
130 2
                resultSql ~= (qp.args.length + 1).to!string;
131
            }
132
            else static if(V.argLikeIn == ArgLikeIn.UPDATE)
133
            {
134 2
                resultSql ~= V.name.escapeName(conn);
135 2
                resultSql ~= `=$`;
136 2
                resultSql ~= (qp.args.length + 1).to!string;
137
            }
138
            else static if(V.argLikeIn == ArgLikeIn.INSERT)
139
            {
140 2
                resultSql ~= V.name.escapeName(conn);
141
            }
142
            else
143
                static assert(false);
144

145 2
            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 2
                resultSql ~= dollarsString(qp.args.length);
153
            }
154
            else
155
            {
156
                // ordinary part of SQL statement
157 2
                resultSql.concatWithDelimiter(V);
158
            }
159
        }
160
    }
161

162 2
    qp.sqlCommand = resultSql[];
163

164 2
    return qp;
165
}
166

167
struct Dollars {}
168

169
///
170
auto wrapStatement(C : Connection, T...)(C conn, T statement)
171
{
172 0
    return CTStatement!T(conn, statement);
173
}
174

175
///
176
auto wrapStatement(T...)(T statement)
177
if(!is(T[0] == Connection))
178
{
179 2
    return CTStatement!T(null, statement);
180
}
181

182
unittest
183
{
184 2
    auto stmnt = wrapStatement(`abc=`, d(123));
185

186 2
    assert(stmnt.qp.sqlCommand == `abc=$1`);
187 2
    assert(stmnt.qp.args.length == 1);
188 2
    assert(stmnt.qp.args[0] == 123.toValue);
189
}
190

191
unittest
192
{
193 2
    auto stmnt = wrapStatement(
194
        `SELECT`, d!string("abc"), d!int(123)
195
    );
196

197 2
    assert(stmnt.qp.args.length == 2);
198 2
    assert(stmnt.qp.args[0] == "abc".toValue);
199 2
    assert(stmnt.qp.args[1] == 123.toValue);
200
}
201

202
unittest
203
{
204 2
    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 2
    assert(stmnt.qp.sqlCommand.length > 10);
213 2
    assert(stmnt.qp.args.length == 3);
214 2
    assert(stmnt.qp.args[0] == true.toValue);
215 2
    assert(stmnt.qp.args[1] == 123.toValue);
216 2
    assert(stmnt.qp.args[2] == `abc`.toValue);
217
}
218

219
unittest
220
{
221 2
    int integer = 123;
222 2
    int another_integer = 456;
223 2
    string text = "abc";
224

225 2
    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 2
    assert(stmnt.qp.sqlCommand.length > 10);
235 2
    assert(stmnt.qp.args[0] == 123.toValue);
236 2
    assert(stmnt.qp.args[1] == `abc`.toValue);
237 2
    assert(stmnt.qp.args[2] == 456.toValue);
238
}
239

240
version(integration_tests)
241
void _integration_test(string connParam)
242
{
243 0
    auto conn = new Connection(connParam);
244 0
    auto stmnt = wrapStatement(conn, i("Some Integer", 123));
245

246 0
    assert(stmnt.qp.sqlCommand == `"Some Integer"`);
247 0
    assert(stmnt.qp.args.length == 1);
248 0
    assert(stmnt.qp.args[0] == 123.toValue);
249
}

Read our documentation on viewing source code .

Loading