1 bool SqlToBool(const String& s);
2 bool SqlToBool(const Value& v);
3 const String& BoolToSql(bool b);
4
5 class SqlSession;
6
7 class SqlExc : public Exc {
8 public:
9 #ifndef NOAPPSQL
10 SqlExc();
11 #endif
12 SqlExc(const SqlSession& session);
13 SqlExc(const Sql& sql);
SqlExc(const String & desc)14 SqlExc(const String& desc) : Exc(desc) {}
SqlExc(const char * desc)15 SqlExc(const char *desc) : Exc(desc) {}
16
17 void SetSessionError(const SqlSession& session);
18 };
19
20 enum { SQLRAW_V = 34 };
21
22 class SqlRaw : public String, public ValueType<SqlRaw, SQLRAW_V> {
23 public:
Value()24 operator Value() const { return RawValue<SqlRaw>(*this); }
SqlRaw(const Value & q)25 SqlRaw(const Value& q)
26 : String(IsNull(q) ? String() :
27 IsString(q) ? String(q) :
28 String(RawValue<SqlRaw>::Extract(q))) {}
SqlRaw(const String & s)29 SqlRaw(const String& s) : String(s) {}
SqlRaw()30 SqlRaw() {}
31 };
32
33 struct SqlColumnInfo : Moveable<SqlColumnInfo> {
34 String name;
35 int type;
36 int width;
37 int precision; //number of total digits in numeric types
38 int scale; //number of digits after comma in numeric types
39 bool nullable; //true - column can hold null values
40 bool binary; //column holds binary data
41 };
42
43 class SqlConnection {
44 protected:
45 friend class Sql;
46 friend class SqlSession;
47
48 virtual void SetParam(int i, const Value& r) = 0;
49 virtual bool Execute() = 0;
50 virtual int GetRowsProcessed() const;
51 virtual Value GetInsertedId() const;
52 virtual bool Fetch() = 0;
53 virtual void GetColumn(int i, Ref r) const = 0;
54 virtual void Cancel() = 0;
55 virtual SqlSession& GetSession() const = 0;
56 virtual String GetUser() const;
57 virtual String ToString() const = 0;
58
59 static void Attach(Sql& sql, SqlConnection *con);
60
61 SqlConnection();
62 virtual ~SqlConnection();
63
64 int starttime;
65 String statement;
66 Vector<SqlColumnInfo> info;
67 int fetchrows;
68 int longsize;
69 bool parse;
70 };
71
72 #define E__ColVal(I) const char *c##I, const Value& v##I
73 #define E__IdVal(I) SqlId c##I, const Value& v##I
74
75 class SqlSource {
76 protected:
77 virtual SqlConnection *CreateConnection() = 0;
~SqlSource()78 virtual ~SqlSource() {}
79 friend class Sql;
SqlSource()80 SqlSource() {}
81
82 private:
83 void operator=(const SqlSource&);
84 SqlSource(const SqlSource&);
85 };
86
87 class Sql {
88 SqlConnection *cn;
89 Vector<Value> param;
90
91 friend class SqlSession;
92 friend class SqlConnection;
93 friend Sql& AppCursor();
94 friend Sql& AppCursorR();
95
96 Value Select0(const String& what);
97
98 void SetSession(SqlSource& src);
99 void Attach(SqlConnection *connection);
100 void Detach();
101
102 protected:
103 Sql(SqlConnection *connection);
104
105 public:
106 String Compile(const SqlStatement& s);
107
108 void Clear();
109
110 void SetParam(int i, const Value& val);
111 void SetStatement(const String& s);
SetStatement(const SqlStatement & s)112 void SetStatement(const SqlStatement& s) { SetStatement(Compile(s)); }
113
114 bool Execute();
115 void ExecuteX(); // Deprecated
Run()116 bool Run() { return Execute(); }
RunX()117 void RunX() { ExecuteX(); } // Deprecated
118
119 bool Execute(const String& s);
120 void ExecuteX(const String& s); // Deprecated
121
Execute(const SqlStatement & s)122 bool Execute(const SqlStatement& s) { return Execute(Compile(s)); }
ExecuteX(const SqlStatement & s)123 void ExecuteX(const SqlStatement& s) { ExecuteX(Compile(s)); } // Deprecated
124
125
126 //$-
127 #define E__Run(I) bool Run(__List##I(E__Value));
128 __Expand(E__Run)
129 //$+ bool Run(const Value& v1 [, const Value& v2 ...]);
130
131 //$-
132 #define E__RunX(I) void RunX(__List##I(E__Value)); // Deprecated
133 __Expand(E__RunX)
134 //$+
135
136 //$-
137 #define E__Execute(I) bool Execute(const String& s, __List##I(E__Value));
138 __Expand(E__Execute)
139 //$+ bool Execute(const String& s, const Value& v1 [, const Value& v2 ...]);
140
141 //$-
142 #define E__ExecuteX(I) void ExecuteX(const String& s, __List##I(E__Value)); // Deprecated
143 __Expand(E__ExecuteX)
144 //$+
145
146
147 bool Fetch();
148
149 //$-
150 #define E__Fetch(I) bool Fetch(__List##I(E__Ref));
151 __Expand(E__Fetch)
152 //$+ bool Fetch(Ref v1 [, Ref v2 ...]);
153
154 bool Fetch(Vector<Value>& row);
155 bool Fetch(ValueMap& row);
156 bool Fetch(Fields fields);
157
GetRowsProcessed()158 int GetRowsProcessed() const { return cn->GetRowsProcessed(); }
159
160 int GetColumns() const;
161 int GetColumnCount() const;
162
163 void GetColumn(int i, Ref r) const;
164 void GetColumn(SqlId colid, Ref r) const;
165 Value operator[](int i) const;
166 Value operator[](SqlId colid) const;
GetColumnInfo(int i)167 const SqlColumnInfo& GetColumnInfo(int i) const { return cn->info[i]; }
168 Vector<Value> GetRow() const;
169 operator Vector<Value>() const { return GetRow(); }
170 ValueMap GetRowMap() const;
171 ValueMap operator~() const { return GetRowMap(); }
172 void Get(Fields fields);
173
SetFetchRows(int nrows)174 void SetFetchRows(int nrows) { cn->fetchrows = nrows; } // deprecated
SetLongSize(int lsz)175 void SetLongSize(int lsz) { cn->longsize = lsz; } // deprecated
176
Cancel()177 void Cancel() { cn->Cancel(); }
178
179 Value Select(const String& what); // Deprecated
180
181 //$-
182 #define E__Select(I) Value Select(const String& what, __List##I(E__Value)); // Deprecated
183 __Expand(E__Select)
184
185 #define E__Insert(I) bool Insert(const char *tb, const char *c0, const Value& v0, __List##I(E__ColVal)); // Deprecated
186 __Expand(E__Insert)
187
188 #define E__InsertId(I) bool Insert(SqlId tb, SqlId c0, const Value& v0, __List##I(E__IdVal)); // Deprecated
189 __Expand(E__InsertId)
190
191 #define E__Update(I) bool Update(const char *tb, const char *k, const Value& kv, __List##I(E__ColVal)); // Deprecated
192 __Expand(E__Update)
193
194 #define E__UpdateId(I) bool Update(SqlId tb, SqlId k, const Value& kv, __List##I(E__IdVal)); // Deprecated
195 __Expand(E__UpdateId)
196 //$+
197
198 bool Insert(Fields nf);
199 bool Insert(Fields nf, const char *table);
200 bool Insert(Fields nf, SqlId table);
201
202 bool InsertNoKey(Fields nf, const char *table);
203 bool InsertNoKey(Fields nf);
204 bool InsertNoKey(Fields nf, SqlId table);
205
206 bool InsertNoNulls(Fields nf, const char *table);
207 bool InsertNoNulls(Fields nf);
208 bool InsertNoNulls(Fields nf, SqlId table);
209
210 bool Update(Fields nf);
211 bool Update(Fields nf, const char *table);
212 bool Update(Fields nf, SqlId table);
213
214 bool Delete(const char *table, const char *key, const Value& keyval);
215 bool Delete(SqlId table, SqlId key, const Value& keyval);
216
ToString()217 String ToString() const { return cn->ToString(); }
218
219 bool operator*(const SqlStatement& q) { return Execute(q); }
220 Sql& operator&(const SqlStatement& q) { ExecuteX(q); return *this; } // Deprecated
221 Value operator%(const SqlStatement& q);
222 ValueMap operator^(const SqlStatement& q);
223 ValueArray operator/(const SqlStatement& q);
224
GetSession()225 SqlSession& GetSession() const { return cn->GetSession(); }
226 int GetDialect() const;
227
GetInsertedId()228 Value GetInsertedId() const { return cn->GetInsertedId(); }
229
GetUser()230 String GetUser() const { return cn->GetUser(); } // Deprecated
231
232 enum ERRORCLASS {
233 ERROR_UNSPECIFIED,
234 CONNECTION_BROKEN,
235 };
236
237 // following block deprecated, use SqlSession for error handling
238 void SetError(String error, String stmt, int code = 0, const char *scode = NULL, ERRORCLASS clss = ERROR_UNSPECIFIED);
239 String GetLastError() const;
240 String GetErrorStatement() const;
241 int GetErrorCode() const;
242 String GetErrorCodeString() const;
243 ERRORCLASS GetErrorClass() const;
244 void ClearError();
245
246 void Begin(); // deprecated: use SqlSession::Begin instead
247 void Commit(); // deprecated: use SqlSession::Commit instead
248 void Rollback(); // deprecated: use SqlSession::Rollback instead
249 int GetTransactionLevel(); // deprecated: only single level of transactions generally supported
250
251 String Savepoint(); // deprecated
252 void RollbackTo(const String& savepoint); // deprecated
253
254 bool IsOpen();
255
256 bool WasError() const; // deprecated, use SqlSession::WasError
257
258 Sql(SqlSource& src);
259 #ifndef NOAPPSQL
260 Sql();
261 Sql(const char *stmt);
262 Sql(const SqlStatement& s);
263 #endif
264 Sql(const char *stmt, SqlSource& session);
265 Sql(const SqlStatement& s, SqlSource& session);
266 ~Sql();
267
268 void operator=(SqlSession& s); // this only works with SQL and SQLR...
269 #ifdef _MULTITHREADED
270 static void PerThread(bool b = true); // Activates thread local SQL/SQLR
271 #endif
272
273 private:
274 void operator=(const Sql&);
275 Sql(const Sql&);
276 };
277
278 struct Sql0 : Sql {
Sql0Sql0279 Sql0() : Sql((SqlConnection *)NULL) {}
280 };
281
282 #ifndef NOAPPSQL
283 struct SqlR : Sql {
284 SqlR();
285 SqlR(const char *stmt);
286 SqlR(const SqlStatement& s);
287 };
288 #endif
289
290 struct StatementExecutor { // Deprecated, use SqlPerformScript
291 virtual bool Execute(const String& stmt) = 0;
~StatementExecutorStatementExecutor292 virtual ~StatementExecutor() {}
293 };
294
295 // Deprecated, use SqlPerformScript
296 typedef bool (*RunScript)(const String& text, StatementExecutor& executor, Gate<int, int> progress_canceled);
297
298 class AppSql;
299 class AppSqlR;
300
301 class SqlSession : public SqlSource {
302 public:
303 enum {
304 START_FETCHING,
305 END_FETCHING,
306 END_FETCHING_MANY,
307 START_EXECUTING,
308 END_EXECUTING,
309 EXECUTING_ERROR,
310 CONNECTION_ERROR,
311 BEFORE_EXECUTING,
312 AFTER_EXECUTING
313 };
314
315 protected:
316 virtual SqlConnection *CreateConnection();
317
318 friend class Sql;
319
320 Stream *trace, *error_log;
321 bool tracetime;
322 bool trace_compression;
323 int traceslow;
324 int dialect;
325 int exectime;
326
327 String statement;
328
329 String lasterror;
330 String errorstatement;
331 int errorcode_number;
332 String errorcode_string;
333 Sql::ERRORCLASS errorclass;
334 bool (*error_handler)(String error, String stmt, int code, const char *scode, Sql::ERRORCLASS clss);
335 bool throwonerror;
336
337 int status;
338
339 One<Sql> sql;
340 One<Sql> sqlr;
341
342 void SessionClose();
343
344 static void Attach(Sql& sql, SqlConnection *con);
345
346 protected:
Dialect(int q)347 SqlSession& Dialect(int q) { dialect = q; return *this; }
348
349 public:
350 virtual void Begin();
351 virtual void Commit();
352 virtual void Rollback();
353 virtual int GetTransactionLevel() const;
354
355 virtual String Savepoint(); // Deprecated
356 virtual void RollbackTo(const String& savepoint); // Deprecated
357
358 virtual bool IsOpen() const;
359
360 virtual RunScript GetRunScript() const; // Deprecated
361
362 virtual Vector<String> EnumUsers();
363 virtual Vector<String> EnumDatabases();
364 virtual Vector<String> EnumTables(String database);
365 virtual Vector<String> EnumViews(String database);
366 virtual Vector<String> EnumSequences(String database);
367 virtual Vector<SqlColumnInfo> EnumColumns(String database, String table);
368 virtual Vector<String> EnumPrimaryKey(String database, String table);
369 virtual String EnumRowID(String database, String table); // deprecated
370 virtual Vector<String> EnumReservedWords(); // deprecated
371
GetDialect()372 int GetDialect() const { ASSERT(dialect != 255); return dialect; }
373
374 void SetTrace(Stream& s = VppLog()) { trace = &s; }
GetTrace()375 Stream *GetTrace() const { return trace; }
KillTrace()376 void KillTrace() { trace = NULL; }
IsTraceCompression()377 bool IsTraceCompression() const { return trace_compression; }
SetTraceCompression(bool b)378 void SetTraceCompression(bool b) { trace_compression = b; }
379
380 void LogErrors(Stream& s = VppLog()) { error_log = &s; }
LogErrors(bool b)381 void LogErrors(bool b) { error_log = b ? &VppLog() : NULL; }
382
383 void TraceTime(bool b = true) { tracetime = b; }
IsTraceTime()384 bool IsTraceTime() const { return tracetime; }
385
386 SqlSession& TraceSlow(int ms = 5000) { traceslow = ms; return *this; }
387
388 SqlSession& ThrowOnError(bool b = true) { throwonerror = b; return *this; }
IsThrowOnError()389 bool IsThrowOnError() const { return throwonerror; }
390
WasError()391 bool WasError() const { return !GetLastError().IsEmpty(); }
392
393 void SetError(String error, String stmt, int code = 0, const char * scode = NULL, Sql::ERRORCLASS clss = Sql::ERROR_UNSPECIFIED);
GetLastError()394 String GetLastError() const { return lasterror; }
GetErrorStatement()395 String GetErrorStatement() const { return errorstatement; }
GetErrorCode()396 int GetErrorCode() const { return errorcode_number; }
GetErrorCodeString()397 String GetErrorCodeString() const { return errorcode_string; }
GetErrorClass()398 Sql::ERRORCLASS GetErrorClass() const { return errorclass; }
399 void ClearError();
400 void InstallErrorHandler(bool (*handler)(String error, String stmt, int code, const char *scode, Sql::ERRORCLASS clss));
401
GetStatement()402 String GetStatement() const { return statement; } // deprecated
SetStatement(const String & s)403 void SetStatement(const String& s) { statement = s; } // deprecated
404
SetTime(int t)405 void SetTime(int t) { exectime = t; } // deprecated
GetTime()406 int GetTime() const { return exectime; } // deprecated
407
GetUser()408 String GetUser() { return Sql(*this).GetUser(); } // deprecated
409
410 Sql& GetSessionSql(); // "private" - only to make SQL work
411 Sql& GetSessionSqlR(); // "private" - only to make SQLR work
412
413 operator bool() const { return IsOpen(); }
414
GetStatus()415 int GetStatus() { return status; }
SetStatus(int s)416 void SetStatus(int s) { status = s; WhenDatabaseActivity(*this); }
417 bool operator == (int s) const { return status == s; }
418 bool operator != (int s) const { return status != s; }
419
420 Callback1<const SqlSession&> WhenDatabaseActivity;
421
422 #ifdef _MULTITHREADED
423 static void PerThread(bool b = true); // Activates thread local SQL/SQLR
424 #endif
425
426 SqlSession();
427 virtual ~SqlSession();
428 };
429
430
431 #ifndef NOAPPSQL
432
433 Sql& AppCursor();
434 Sql& AppCursorR();
435
436 //$-
437 #define SQL AppCursor()
438 #define SQLR AppCursorR()
439 //$+
440 // Assist++ cheat:
441 //$ Sql SQL;
442
443 #endif
444
445 class OciConnection;
446
447 bool SqlPerformScript(SqlSession& session, Stream& script,
448 Gate<int, int> progress_canceled = Null, bool stoponerror = false);
449 bool SqlPerformScript(Stream& script,
450 Gate<int, int> progress_canceled = Null, bool stoponerror = false);
451 bool SqlPerformScript(SqlSession& session, const String& script,
452 Gate<int, int> progress_canceled = Null, bool stoponerror = false);
453 bool SqlPerformScript(const String& script,
454 Gate<int, int> progress_canceled = Null, bool stoponerror = false);
455
456 class SqlMassInsert {
457 struct Row : Moveable<Row> {
458 uint64 nulls;
459 Vector <Value> value;
460 SqlBool remove;
461
462 rval_default(Row);
RowRow463 Row() {}
464 };
465
466 Sql& sql;
467 SqlId table;
468 Vector<String> column;
469 Vector<Row> cache;
470 int pos;
471 bool error;
472 bool use_transaction;
473
474 void NewRow();
475
476 public:
477 SqlMassInsert& operator()(SqlId col, const Value& val);
478 SqlMassInsert& operator()(const ValueMap& data);
479 SqlMassInsert& EndRow(SqlBool remove = SqlBool());
480 void Flush();
IsError()481 bool IsError() const { return error; }
482 SqlMassInsert& UseTransaction(bool b = true) { use_transaction = b; return *this; }
NoUseTransaction()483 SqlMassInsert& NoUseTransaction() { return UseTransaction(false); }
484
SqlMassInsert(Sql & sql,SqlId table)485 SqlMassInsert(Sql& sql, SqlId table) : sql(sql), table(table) { pos = 0; error = false; use_transaction = true; }
486 #ifndef NOAPPSQL
SqlMassInsert(SqlId table)487 SqlMassInsert(SqlId table) : sql(SQL), table(table) { pos = 0; error = false; use_transaction = true; }
488 #endif
489 ~SqlMassInsert();
490 };
491
492 #ifndef NOAPPSQL
493
494 template <class T>
495 void SqlLoadTable(T& t, SqlId table, SqlId key = SqlId("ID"))
496 {
497 Sql sql;
498 sql * Select(SqlAll()).From(table);
499 while(sql.Fetch())
500 sql.Get(t.Add(sql[key]));
501 }
502
503 template <class T>
504 void SqlLoadColumn(T& t, SqlId table, SqlId column, SqlId key = SqlId("ID"))
505 {
506 Sql sql;
507 sql * Select(key, column).From(table);
508 while(sql.Fetch())
509 t.Add(sql[key], sql[column]);
510 }
511
512 template <class T>
513 void SqlLoadTable(T& t, SqlSelect select, SqlId key = SqlId("ID"))
514 {
515 Sql sql;
516 sql * select;
517 while(sql.Fetch())
518 sql.Get(t.Add(sql[key]));
519 }
520
521 template <class T>
SqlLoadColumn(T & t,SqlSelect select)522 void SqlLoadColumn(T& t, SqlSelect select)
523 {
524 Sql sql;
525 sql * select;
526 while(sql.Fetch())
527 t.Add(sql[0], sql[1]);
528 }
529
530 void operator*=(ValueMap& map, SqlSelect select);
531
532 template<class K, class V>
533 void operator*=(VectorMap<K, V>& map, SqlSelect select)
534 {
535 map.Clear();
536 Sql sql;
537 sql * select;
538 while(sql.Fetch())
539 map.Add(sql[0], sql[1]);
540 }
541
542 #endif
543
544 // Deprecated, use SqlPerformScript instead
545 struct StdStatementExecutor : StatementExecutor {
StdStatementExecutorStdStatementExecutor546 StdStatementExecutor(SqlSession& session) : cursor(session) {}
547 virtual bool Execute(const String& stmt);
548 Sql cursor;
549 };
550
551 #ifndef NOAPPSQL
552 StatementExecutor& SQLStatementExecutor();
553 #endif
554
555 #ifdef BackwardCompatibility
556 typedef Sql QSql;
557 typedef SqlSession QSession;
558 #endif
559