1 #include "Sql.h"
2
3 namespace Upp {
4
Of(const char * of) const5 SqlId SqlId::Of(const char *of) const
6 {
7 return String().Cat() << of << (char)SQLC_OF << ToString();
8 }
9
Of(SqlId id) const10 SqlId SqlId::Of(SqlId id) const
11 {
12 return id.IsNull() ? ToString() : String().Cat() << id.ToString() << (char)SQLC_OF << ToString();
13 }
14
PutOf0(String & s,const SqlId & b) const15 void SqlId::PutOf0(String& s, const SqlId& b) const
16 {
17 s << ToString() << (char)SQLC_OF << ~b;
18 }
19
PutOf(String & s,const SqlId & b) const20 void SqlId::PutOf(String& s, const SqlId& b) const
21 {
22 s << (char)SQLC_COMMA;
23 PutOf0(s, b);
24 }
25
operator [](const SqlId & id) const26 SqlId SqlId::operator[](const SqlId& id) const
27 {
28 return Of(id);
29 }
30
As(const char * as) const31 SqlId SqlId::As(const char *as) const
32 {
33 return id.IsNull() ? ToString() : String().Cat() << ToString() << (char)SQLC_AS << as;
34 }
35
operator ()(SqlId p) const36 SqlId SqlId::operator()(SqlId p) const
37 {
38 String x;
39 PutOf0(x, p);
40 return x;
41 }
42
operator [](int i) const43 SqlId SqlId::operator [] (int i) const
44 {
45 return ToString() + FormatInt(i);
46 }
47
operator &(const SqlId & s) const48 SqlId SqlId::operator&(const SqlId& s) const
49 {
50 return ToString() + "$" + s.ToString();
51 }
52
operator ()(const S_info & table) const53 SqlId SqlId::operator()(const S_info& table) const
54 {
55 String x;
56 for(int i = 0; i < table.GetCount(); i++)
57 if(i)
58 PutOf(x, table.GetId(i));
59 else
60 PutOf0(x, table.GetId(i));
61 return x;
62 }
63
operator ()() const64 String SqlS::operator()() const
65 {
66 return String().Cat() << '(' << text << ')';
67 }
68
operator ()(int at) const69 String SqlS::operator()(int at) const
70 {
71 return at > priority ? operator()() : text;
72 }
73
operator ()(int at,byte cond) const74 String SqlS::operator()(int at, byte cond) const
75 {
76 if(at <= priority)
77 return text;
78 StringBuffer out;
79 out << SqlCode(cond, "(")() << text << SqlCode(cond, ")")();
80 return String(out);
81 }
82
Init(const SqlS & a,const char * o,int olen,const SqlS & b,int pr,int prb)83 void SqlS::Init(const SqlS& a, const char *o, int olen, const SqlS& b, int pr, int prb)
84 {
85 StringBuffer s;
86 if(a.priority < pr) {
87 s.Cat('(');
88 s.Cat(~a);
89 s.Cat(')');
90 }
91 else
92 s.Cat(~a);
93 s.Cat(o, olen);
94 if(b.priority < prb) {
95 s.Cat('(');
96 s.Cat(~b);
97 s.Cat(')');
98 }
99 else
100 s.Cat(~b);
101 text = s;
102 priority = pr;
103 }
104
As(const char * as) const105 SqlVal SqlVal::As(const char *as) const {
106 SqlVal v;
107 v.SetHigh(String().Cat() << text << (char)SQLC_AS << '\t' << as << '\t');
108 return v;
109 }
110
As(const SqlId & id) const111 SqlVal SqlVal::As(const SqlId& id) const {
112 return As(~id.ToString());
113 }
114
SqlVal(const String & x)115 SqlVal::SqlVal(const String& x) {
116 if(UPP::IsNull(x))
117 SetNull();
118 else
119 SetHigh(SqlFormat(x));
120 }
121
SqlVal(const char * s)122 SqlVal::SqlVal(const char *s) {
123 if(s && *s)
124 SetHigh(SqlFormat(s));
125 else
126 SetNull();
127 }
128
SqlVal(int x)129 SqlVal::SqlVal(int x) {
130 if(UPP::IsNull(x))
131 SetNull();
132 else
133 SetHigh(SqlFormat(x));
134 }
135
SqlVal(int64 x)136 SqlVal::SqlVal(int64 x) {
137 if(UPP::IsNull(x))
138 SetNull();
139 else
140 SetHigh(SqlFormat(x));
141 }
142
SqlVal(double x)143 SqlVal::SqlVal(double x) {
144 if(UPP::IsNull(x))
145 SetNull();
146 else
147 SetHigh(SqlFormat(x));
148 }
149
SqlVal(Date x)150 SqlVal::SqlVal(Date x) {
151 if(UPP::IsNull(x))
152 SetNull();
153 else
154 SetHigh(SqlFormat(x));
155 }
156
SqlVal(Time x)157 SqlVal::SqlVal(Time x) {
158 if(UPP::IsNull(x))
159 SetNull();
160 else
161 SetHigh(SqlFormat(x));
162 }
163
SqlVal(const Value & x)164 SqlVal::SqlVal(const Value& x) {
165 if(UPP::IsNull(x))
166 SetNull();
167 else
168 SetHigh(SqlFormat(x));
169 }
170
SqlVal(const Nuller &)171 SqlVal::SqlVal(const Nuller&) {
172 SetNull();
173 }
174
SqlVal(const SqlId & id)175 SqlVal::SqlVal(const SqlId& id) {
176 SetHigh(id.Quoted());
177 }
178
SqlVal(const SqlId & (* id)())179 SqlVal::SqlVal(const SqlId& (*id)())
180 {
181 SetHigh((*id)().Quoted());
182 }
183
SqlVal(const SqlCase & x)184 SqlVal::SqlVal(const SqlCase& x) {
185 SetHigh(~x);
186 }
187
operator -(const SqlVal & a)188 SqlVal operator-(const SqlVal& a) {
189 return SqlVal('-' + a(SqlS::UNARY), SqlS::UNARY);
190 }
191
operator +(const SqlVal & a,const SqlVal & b)192 SqlVal operator+(const SqlVal& a, const SqlVal& b) {
193 return SqlVal(a, " + ", b, SqlS::ADD);
194 }
195
operator -(const SqlVal & a,const SqlVal & b)196 SqlVal operator-(const SqlVal& a, const SqlVal& b) {
197 return SqlVal(a," - ", b, SqlS::ADD, SqlS::ADD + 1);
198 }
199
operator *(const SqlVal & a,const SqlVal & b)200 SqlVal operator*(const SqlVal& a, const SqlVal& b) {
201 return SqlVal(a, " * ", b, SqlS::MUL);
202 }
203
operator /(const SqlVal & a,const SqlVal & b)204 SqlVal operator/(const SqlVal& a, const SqlVal& b) {
205 return SqlVal(a, " / ", b, SqlS::MUL, SqlS::MUL + 1);
206 }
207
operator %(const SqlVal & a,const SqlVal & b)208 SqlVal operator%(const SqlVal& a, const SqlVal& b) {
209 return SqlFunc("mod", a, b);
210 }
211
operator |(const SqlVal & a,const SqlVal & b)212 SqlVal operator|(const SqlVal& a, const SqlVal& b) {
213 return SqlVal(a, SqlCode(ORACLE|PGSQL|SQLITE3, "||")(" + "), b, SqlS::MUL);
214 }
215
operator +=(SqlVal & a,const SqlVal & b)216 SqlVal& operator+=(SqlVal& a, const SqlVal& b) { return a = a + b; }
operator -=(SqlVal & a,const SqlVal & b)217 SqlVal& operator-=(SqlVal& a, const SqlVal& b) { return a = a - b; }
operator *=(SqlVal & a,const SqlVal & b)218 SqlVal& operator*=(SqlVal& a, const SqlVal& b) { return a = a * b; }
operator /=(SqlVal & a,const SqlVal & b)219 SqlVal& operator/=(SqlVal& a, const SqlVal& b) { return a = a / b; }
operator %=(SqlVal & a,const SqlVal & b)220 SqlVal& operator%=(SqlVal& a, const SqlVal& b) { return a = a % b; }
operator |=(SqlVal & a,const SqlVal & b)221 SqlVal& operator|=(SqlVal& a, const SqlVal& b) { return a = a | b; }
222
SqlFunc(const char * name,const SqlVal & a)223 SqlVal SqlFunc(const char *name, const SqlVal& a) {
224 return SqlVal(String().Cat() << name << '(' << ~a << ')', SqlS::FN);
225 }
226
SqlFunc(const char * n,const SqlVal & a,const SqlVal & b)227 SqlVal SqlFunc(const char *n, const SqlVal& a, const SqlVal& b) {
228 return SqlVal(String(n).Cat() << '(' + ~a << ", " << ~b << ')', SqlS::FN);
229 }
230
SqlFunc(const char * n,const SqlVal & a,const SqlVal & b,const SqlVal & c)231 SqlVal SqlFunc(const char *n, const SqlVal& a, const SqlVal& b, const SqlVal& c) {
232 return SqlVal(String(n).Cat() << '(' << ~a << ", " << ~b << ", " << ~c << ')', SqlS::FN);
233 }
234
SqlFunc(const char * n,const SqlVal & a,const SqlVal & b,const SqlVal & c,const SqlVal & d)235 SqlVal SqlFunc(const char *n, const SqlVal& a, const SqlVal& b, const SqlVal& c, const SqlVal& d) {
236 return SqlVal(String(n).Cat() << '(' << ~a << ", " << ~b << ", " << ~c << ", " << ~d << ')', SqlS::FN);
237 }
238
SqlFunc(const char * name,const SqlSet & set)239 SqlVal SqlFunc(const char *name, const SqlSet& set) {
240 return SqlVal(name + set(), SqlS::FN);
241 }
242
SqlBoolFunc(const char * name,const SqlBool & a)243 SqlBool SqlBoolFunc(const char *name, const SqlBool& a) {
244 return SqlBool(String().Cat() << name << '(' << ~a << ')', SqlS::FN);
245 }
246
SqlBoolFunc(const char * n,const SqlBool & a,const SqlBool & b)247 SqlBool SqlBoolFunc(const char *n, const SqlBool& a, const SqlBool& b) {
248 return SqlBool(String(n).Cat() << '(' + ~a << ", " << ~b << ')', SqlS::FN);
249 }
250
SqlBoolFunc(const char * n,const SqlBool & a,const SqlBool & b,const SqlBool & c)251 SqlBool SqlBoolFunc(const char *n, const SqlBool& a, const SqlBool& b, const SqlBool& c) {
252 return SqlBool(String(n).Cat() << '(' << ~a << ", " << ~b << ", " << ~c << ')', SqlS::FN);
253 }
254
SqlBoolFunc(const char * n,const SqlBool & a,const SqlBool & b,const SqlBool & c,const SqlBool & d)255 SqlBool SqlBoolFunc(const char *n, const SqlBool& a, const SqlBool& b, const SqlBool& c, const SqlBool& d) {
256 return SqlBool(String(n).Cat() << '(' << ~a << ", " << ~b << ", " << ~c << ", " << ~d << ')', SqlS::FN);
257 }
258
Decode(const SqlVal & exp,const SqlSet & variants)259 SqlVal Decode(const SqlVal& exp, const SqlSet& variants) {
260 ASSERT(!variants.IsEmpty());
261 Vector<SqlVal> v = variants.Split();
262 ASSERT(v.GetCount() > 1);
263 SqlCase cs(exp == v[0], v[1]);
264 int i;
265 for(i = 2; i + 1 < v.GetCount(); i += 2)
266 cs(exp == v[i], v[i + 1]);
267 if(i < v.GetCount())
268 cs(v[i]);
269 else
270 cs(Null);
271 return SqlVal(SqlCode(ORACLE, "decode(" + ~exp + ", " + ~variants + ')')('(' + ~cs + ')'),
272 SqlS::FN);
273 }
274
Distinct(const SqlVal & exp)275 SqlVal Distinct(const SqlVal& exp) {
276 return SqlVal("distinct " + exp(SqlS::ADD), SqlS::UNARY);
277 }
278
Distinct(const SqlSet & columns)279 SqlSet Distinct(const SqlSet& columns) {
280 return SqlSet("distinct " + ~columns, SqlSet::SET);
281 }
282
All(const SqlVal & exp)283 SqlVal All(const SqlVal& exp) {
284 return SqlVal("all " + exp(SqlS::ADD), SqlS::UNARY);
285 }
286
All(const SqlSet & columns)287 SqlSet All(const SqlSet& columns) {
288 return SqlSet("all " + ~columns, SqlSet::SET);
289 }
290
Count(const SqlVal & exp)291 SqlVal Count(const SqlVal& exp)
292 {
293 return SqlFunc("count", exp);
294 }
295
Count(const SqlSet & exp)296 SqlVal Count(const SqlSet& exp)
297 {
298 return SqlFunc("count", exp);
299 }
300
SqlAll()301 SqlId SqlAll()
302 {
303 return SqlId("*");
304 }
305
SqlCountRows()306 SqlVal SqlCountRows()
307 {
308 return Count(SqlAll());
309 }
310
Descending(const SqlVal & exp)311 SqlVal Descending(const SqlVal& exp) {
312 return SqlVal(exp(SqlS::ADD) + " desc", SqlS::UNARY);
313 }
314
SqlMax(const SqlVal & exp)315 SqlVal SqlMax(const SqlVal& exp) {
316 return SqlFunc("max", exp);
317 }
318
SqlMin(const SqlVal & exp)319 SqlVal SqlMin(const SqlVal& exp) {
320 return SqlFunc("min", exp);
321 }
322
SqlSum(const SqlVal & exp)323 SqlVal SqlSum(const SqlVal& exp) {
324 return SqlFunc("sum", exp);
325 }
326
Avg(const SqlVal & a)327 SqlVal Avg(const SqlVal& a) {
328 return SqlFunc("avg", a);
329 }
330
Abs(const SqlVal & a)331 SqlVal Abs(const SqlVal& a) {
332 return SqlFunc("abs", a);
333 }
334
Stddev(const SqlVal & a)335 SqlVal Stddev(const SqlVal& a) {
336 return SqlFunc("stddev", a);
337 }
338
Variance(const SqlVal & a)339 SqlVal Variance(const SqlVal& a) {
340 return SqlFunc("variance", a);
341 }
342
Greatest(const SqlVal & a,const SqlVal & b)343 SqlVal Greatest(const SqlVal& a, const SqlVal& b) {
344 return SqlVal(SqlCode(MSSQL, '(' + ~Case(a < b, b)(a) + ')')
345 (~SqlFunc(SqlCode(SQLITE3, "max")("greatest"), a, b)),
346 SqlS::FN);
347 }
348
Least(const SqlVal & a,const SqlVal & b)349 SqlVal Least(const SqlVal& a, const SqlVal& b) {
350 return SqlVal(SqlCode(MSSQL, '(' + ~Case(a > b, b)(a) + ')')
351 (~SqlFunc(SqlCode(SQLITE3, "min")("least"), a, b)),
352 SqlS::FN);
353 }
354
ConvertCharset(const SqlVal & exp,const SqlVal & charset)355 SqlVal ConvertCharset(const SqlVal& exp, const SqlVal& charset) { //TODO Dialect!
356 if(exp.IsEmpty()) return exp;
357 return exp.IsEmpty() ? exp : SqlFunc("convert", exp, charset);
358 }
359
ConvertAscii(const SqlVal & exp)360 SqlVal ConvertAscii(const SqlVal& exp) {
361 return SqlVal(SqlCode(MSSQL, String().Cat() << "((" << ~exp << ") collate SQL_Latin1_General_Cp1251_CS_AS)")
362 (~ConvertCharset(exp, "US7ASCII")), SqlS::FN); // This is Oracle really, TODO: Add other dialects
363 }
364
Upper(const SqlVal & exp)365 SqlVal Upper(const SqlVal& exp) {
366 return exp.IsEmpty() ? exp : SqlFunc("upper", exp);
367 }
368
Lower(const SqlVal & exp)369 SqlVal Lower(const SqlVal& exp) {
370 return exp.IsEmpty() ? exp : SqlFunc("lower", exp);
371 }
372
Length(const SqlVal & exp)373 SqlVal Length(const SqlVal& exp) {
374 return exp.IsEmpty() ? exp : SqlFunc(SqlCode(MSSQL, "len")("length"), exp);
375 }
376
UpperAscii(const SqlVal & exp)377 SqlVal UpperAscii(const SqlVal& exp) {
378 return exp.IsEmpty() ? exp : Upper(ConvertAscii(exp));
379 }
380
Substr(const SqlVal & a,const SqlVal & b)381 SqlVal Substr(const SqlVal& a, const SqlVal& b) {
382 return SqlFunc("SUBSTR", a, b);
383 }
384
Substr(const SqlVal & a,const SqlVal & b,const SqlVal & c)385 SqlVal Substr(const SqlVal& a, const SqlVal& b, const SqlVal& c)
386 {
387 return SqlFunc(SqlCode(MSSQL, "substring")("substr"), a, b, c);
388 }
389
Instr(const SqlVal & a,const SqlVal & b)390 SqlVal Instr(const SqlVal& a, const SqlVal& b) {
391 return SqlFunc("INSTR", a, b);
392 }
393
Wild(const char * s)394 SqlVal Wild(const char* s) {
395 String result;
396 for(char c; (c = *s++) != 0;)
397 if(c == '*')
398 result << '%';
399 else if(c == '?')
400 result << '_';
401 else if(c == '.' && *s == 0)
402 return result;
403 else
404 result << c;
405 result << '%';
406 return result;
407 }
408
SqlDate(const SqlVal & year,const SqlVal & month,const SqlVal & day)409 SqlVal SqlDate(const SqlVal& year, const SqlVal& month, const SqlVal& day) {
410 return SqlVal(SqlCode(ORACLE, ~SqlFunc("to_date", year|"."|month|"."|day, "SYYYY.MM.DD")) // Similiar to common, but keep proved version to be sure...
411 (MSSQL, ~SqlFunc("datefromparts", year, month, day))
412 ("to_date(to_char(" + ~day + ", '00') || to_char(" + ~month +
413 ", '00') || to_char(" + ~year + ",'9999'), 'DDMMYYYY')"),
414 SqlS::FN);
415 }
416
AddMonths(const SqlVal & date,const SqlVal & months)417 SqlVal AddMonths(const SqlVal& date, const SqlVal& months) {//TODO Dialect!
418 return SqlFunc("add_months", date, months);
419 }
420
LastDay(const SqlVal & date)421 SqlVal LastDay(const SqlVal& date) {//TODO Dialect!
422 return SqlFunc("last_day", date);
423 }
424
MonthsBetween(const SqlVal & date1,const SqlVal & date2)425 SqlVal MonthsBetween(const SqlVal& date1, const SqlVal& date2) {//TODO Dialect!
426 return SqlFunc("months_between", date1, date2);
427 }
428
NextDay(const SqlVal & date)429 SqlVal NextDay(const SqlVal& date) {//TODO Dialect!
430 return SqlFunc("next_day", date);
431 }
432
SqlCurrentDate()433 SqlVal SqlCurrentDate() {
434 return SqlVal(SqlCode(SQLITE3, "date('now')")
435 ("current_date"), SqlVal::HIGH);
436 }
437
SqlCurrentTime()438 SqlVal SqlCurrentTime() {
439 return SqlVal(SqlCode(SQLITE3, "datetime('now')")
440 ("current_timestamp"), SqlVal::HIGH);
441 }
442
Cast(const char * type,const SqlId & a)443 SqlVal Cast(const char* type, const SqlId& a) {
444 return SqlFunc(type, a);
445 }
446
SqlNvl(const SqlVal & a,const SqlVal & b)447 SqlVal SqlNvl(const SqlVal& a, const SqlVal& b) {
448 return SqlFunc(SqlCode
449 (PGSQL, "coalesce")
450 (MY_SQL|SQLITE3, "ifnull")
451 (MSSQL, "isnull")
452 ("nvl"),
453 a, b);
454 }
455
SqlNvl(const SqlVal & a)456 SqlVal SqlNvl(const SqlVal& a) {
457 return Nvl(a, SqlVal(0));
458 }
459
Coalesce(const SqlVal & exp1,const SqlVal & exp2)460 SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2) {
461 return SqlFunc("coalesce", exp1, exp2);
462 }
463
Coalesce(const SqlVal & exp1,const SqlVal & exp2,const SqlVal & exp3)464 SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2, const SqlVal& exp3) {
465 return SqlFunc("coalesce", exp1, exp2, exp3);
466 }
467
Coalesce(const SqlVal & exp1,const SqlVal & exp2,const SqlVal & exp3,const SqlVal & exp4)468 SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2, const SqlVal& exp3, const SqlVal& exp4) {
469 return SqlFunc("coalesce", exp1, exp2, exp3, exp4);
470 }
471
Coalesce(const SqlSet & exps)472 SqlVal Coalesce(const SqlSet& exps) {
473 return SqlFunc("coalesce", exps);
474 }
475
Prior(const SqlId & a)476 SqlVal Prior(const SqlId& a) {
477 return SqlVal("prior " + a.Quoted(), SqlS::UNARY);
478 }
479
NextVal(const SqlId & a)480 SqlVal NextVal(const SqlId& a) {
481 return SqlVal(SqlCode
482 (PGSQL, "nextval('" + a.ToString() + "')")
483 (MSSQL, "next value for " + a.Quoted())
484 (a.Quoted() + ".NEXTVAL")
485 , SqlS::HIGH);
486 }
487
CurrVal(const SqlId & a)488 SqlVal CurrVal(const SqlId& a) {
489 return SqlVal(SqlCode
490 (PGSQL, "currval('" + a.ToString() + "')")
491 (a.Quoted() + ".CURRVAL")
492 , SqlS::HIGH);
493 }
494
SqlTxt(const char * s)495 SqlVal SqlTxt(const char *s)
496 {
497 SqlVal v;
498 v.SetHigh(s);
499 return v;
500 }
501
SqlRowNum()502 SqlVal SqlRowNum()
503 {
504 return SqlTxt("ROWNUM");
505 }
506
SqlArg()507 SqlVal SqlArg() {
508 return SqlTxt("?");
509 }
510
OuterJoin(const SqlId & col)511 SqlVal OuterJoin(const SqlId& col)
512 {
513 return SqlId(col.Quoted() + "(+)");
514 }
515
SqlBinary(const char * s,int l)516 SqlVal SqlBinary(const char *s, int l)
517 {
518 SqlVal x;
519 x.SetHigh(SqlFormatBinary(s, l));
520 return x;
521 }
522
SqlBinary(const String & data)523 SqlVal SqlBinary(const String& data)
524 {
525 return SqlBinary(~data, data.GetCount());
526 }
527
528 }
529