Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
TSQLiteServer.cxx
Go to the documentation of this file.
1// @(#)root/sqlite:$Id$
2// Author: o.freyermuth <o.f@cern.ch>, 01/06/2013
3
4/*************************************************************************
5 * Copyright (C) 1995-2013, Rene Brun and Fons Rademakers. *
6 * All rights reserved. *
7 * *
8 * For the licensing terms see $ROOTSYS/LICENSE. *
9 * For the list of contributors see $ROOTSYS/README/CREDITS. *
10 *************************************************************************/
11
12#include "TSQLiteServer.h"
13#include "TSQLiteResult.h"
14#include "TSQLiteStatement.h"
15#include "TSQLColumnInfo.h"
16#include "TList.h"
17#include "TSQLTableInfo.h"
18#include "TSQLRow.h"
19
20#include <sqlite3.h>
21
23
24////////////////////////////////////////////////////////////////////////////////
25/// Open a connection to an SQLite DB server. The db arguments should be
26/// of the form "sqlite://<database>", e.g.:
27/// "sqlite://test.sqlite" or "sqlite://:memory:" for a temporary database
28/// in memory.
29/// Note that for SQLite versions >= 3.7.7 the full string behind
30/// "sqlite://" is handed to sqlite3_open_v2() with SQLITE_OPEN_URI activated,
31/// so all URI accepted by it can be used.
32
33TSQLiteServer::TSQLiteServer(const char *db, const char* /*uid*/, const char* /*pw*/)
34{
35 fSQLite = nullptr;
36 fSrvInfo = "SQLite ";
37 fSrvInfo += sqlite3_libversion();
38
39 if (strncmp(db, "sqlite://", 9)) {
40 TString givenProtocol(db, 9); // this TString-constructor allocs len+1 and does \0 termination already.
41 Error("TSQLiteServer", "protocol in db argument should be sqlite it is %s",
42 givenProtocol.Data());
43 MakeZombie();
44 return;
45 }
46
47 const char *dbase = db + 9;
48
49#ifndef SQLITE_OPEN_URI
50#define SQLITE_OPEN_URI 0x00000000
51#endif
52#if SQLITE_VERSION_NUMBER >= 3005000
53 Int_t error = sqlite3_open_v2(dbase, &fSQLite, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);
54#else
55 Int_t error = sqlite3_open(dbase, &fSQLite);
56#endif
57
58 if (error == 0) {
59 // Set members of the abstract interface
60 fType = "SQLite";
61 fHost = "";
62 fDB = dbase;
63 // fPort != -1 means we are 'connected'
64 fPort = 0;
65 } else {
66 Error("TSQLiteServer", "opening of %s failed with error: %d %s", dbase, sqlite3_errcode(fSQLite), sqlite3_errmsg(fSQLite));
67 sqlite3_close(fSQLite);
68 MakeZombie();
69 }
70
71}
72
73////////////////////////////////////////////////////////////////////////////////
74/// Close SQLite DB.
75
77{
78 if (IsConnected()) {
79 sqlite3_close(fSQLite);
80 }
81}
82
83////////////////////////////////////////////////////////////////////////////////
84/// Close connection to SQLite DB.
85
87{
88 if (!fSQLite) {
89 return;
90 }
91
92 if (IsConnected()) {
93 sqlite3_close(fSQLite);
94 // Mark as disconnected:
95 fPort = -1;
96 fSQLite = nullptr;
97 }
98}
99
100////////////////////////////////////////////////////////////////////////////////
101/// submit "BEGIN TRANSACTION" query to database
102/// return kTRUE, if successful
103
105{
106 return Exec("BEGIN TRANSACTION");
107}
108
109////////////////////////////////////////////////////////////////////////////////
110/// returns kTRUE when transaction is running
111
113{
114 if (!fSQLite)
115 return kFALSE;
116
117 return sqlite3_get_autocommit(fSQLite) == 0;
118}
119
120
121////////////////////////////////////////////////////////////////////////////////
122/// submit "COMMIT TRANSACTION" query to database
123/// return kTRUE, if successful
124
126{
127 return Exec("COMMIT TRANSACTION");
128}
129
130////////////////////////////////////////////////////////////////////////////////
131/// submit "ROLLBACK TRANSACTION" query to database
132/// return kTRUE, if successful
133
135{
136 return Exec("ROLLBACK TRANSACTION");
137}
138
139////////////////////////////////////////////////////////////////////////////////
140/// Execute SQL command. Result object must be deleted by the user.
141/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
142/// The result object must be deleted by the user.
143
145{
146 if (!IsConnected()) {
147 Error("Query", "not connected");
148 return nullptr;
149 }
150
151 sqlite3_stmt *preparedStmt = nullptr;
152 const char *tail = nullptr;
153
154 // -1 as we read until we encounter a \0.
155#if SQLITE_VERSION_NUMBER >= 3005000
156 int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
157#else
158 int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
159#endif
160 if (retVal != SQLITE_OK) {
161 Error("Query", "SQL Error: %d %s", retVal, sqlite3_errmsg(fSQLite));
162 return nullptr;
163 }
164 if (tail && tail[0] != '\0')
165 Warning("Query", "Don't use multiple queries, '%s' query was ignored", tail);
166
167 return new TSQLiteResult(preparedStmt);
168}
169
170////////////////////////////////////////////////////////////////////////////////
171/// Execute SQL command which does not produce any result sets.
172/// Returns kTRUE if successful.
173
175{
176 if (!IsConnected()) {
177 Error("Exec", "not connected");
178 return kFALSE;
179 }
180
181 char *sqlite_err_msg;
182 int ret = sqlite3_exec(fSQLite, sql, NULL, NULL, &sqlite_err_msg);
183 if (ret != SQLITE_OK) {
184 Error("Exec", "SQL Error: %d %s", ret, sqlite_err_msg);
185 sqlite3_free(sqlite_err_msg);
186 return kFALSE;
187 }
188 return kTRUE;
189}
190
191
192////////////////////////////////////////////////////////////////////////////////
193/// Select a database. Always returns non-zero for SQLite,
194/// as only one DB exists per file.
195
197{
198 Error("SelectDataBase", "SelectDataBase command makes no sense for SQLite!");
199 return -1;
200}
201
202////////////////////////////////////////////////////////////////////////////////
203/// List all available databases. Always returns 0 for SQLite,
204/// as only one DB exists per file.
205
207{
208 Error("GetDataBases", "GetDataBases command makes no sense for SQLite!");
209 return nullptr;
210}
211
212////////////////////////////////////////////////////////////////////////////////
213/// List all tables in the specified database. Wild is for wildcarding
214/// "t%" list all tables starting with "t".
215/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
216/// The result object must be deleted by the user.
217
218TSQLResult *TSQLiteServer::GetTables(const char* /*dbname*/, const char *wild)
219{
220 if (!IsConnected()) {
221 Error("GetTables", "not connected");
222 return nullptr;
223 }
224
225 TString sql = "SELECT name FROM sqlite_master where type='table'";
226 if (wild)
227 sql += Form(" AND name LIKE '%s'", wild);
228
229 return Query(sql);
230}
231
232////////////////////////////////////////////////////////////////////////////////
233/// List all columns in specified table (database argument is ignored).
234/// Wild is for wildcarding "t%" list all columns starting with "t".
235/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
236/// The result object must be deleted by the user.
237/// For SQLite, this fails with wildcard, as the column names are not queryable!
238/// If no wildcard is used, the result of PRAGMA table_info(table) is returned,
239/// which contains the names in field 1.
240
241TSQLResult *TSQLiteServer::GetColumns(const char* /*dbname*/, const char* table,
242 const char* wild)
243{
244 if (!IsConnected()) {
245 Error("GetColumns", "not connected");
246 return nullptr;
247 }
248
249 if (wild) {
250 Error("GetColumns", "Not implementable for SQLite as a query with wildcard, use GetFieldNames() after SELECT instead!");
251 return nullptr;
252 } else {
253 TString sql = Form("PRAGMA table_info('%s')", table);
254 return Query(sql);
255 }
256}
257
258////////////////////////////////////////////////////////////////////////////////
259/// Produces SQL table info.
260/// Object must be deleted by user.
261
263{
264 if (!IsConnected()) {
265 Error("GetTableInfo", "not connected");
266 return nullptr;
267 }
268
269 if (!tablename || (*tablename==0)) return nullptr;
270
271 TSQLResult *columnRes = GetColumns("", tablename);
272
273 if (columnRes == nullptr) {
274 Error("GetTableInfo", "could not query columns");
275 return nullptr;
276 }
277
278 TList* lst = nullptr;
279
280 TSQLRow *columnRow;
281
282 while ((columnRow = columnRes->Next()) != nullptr) {
283 if (!lst) {
284 lst = new TList();
285 }
286
287 // Field 3 is 'notnull', i.e. if it is 0, column is nullable
288 Bool_t isNullable = (strcmp(columnRow->GetField(3), "0") == 0);
289
290 lst->Add(new TSQLColumnInfo(columnRow->GetField(1), // column name
291 columnRow->GetField(2), // column type name
292 isNullable, // isNullable defined above
293 -1, // SQLite is totally free about types
294 -1, // SQLite imposes no declarable size-limits
295 -1, // Field length only available querying the field
296 -1, // no data scale in SQLite
297 -1)); // SQLite does not enforce any sign(s)
298 delete columnRow;
299 }
300 delete columnRes;
301
302 // lst == NULL is ok as TSQLTableInfo accepts and handles this
303 TSQLTableInfo* info = new TSQLTableInfo(tablename,
304 lst);
305
306 return info;
307}
308
309////////////////////////////////////////////////////////////////////////////////
310/// Create a database. Always returns non-zero for SQLite,
311/// as it has only one DB per file.
312
314{
315 Error("CreateDataBase", "CreateDataBase command makes no sense for SQLite!");
316 return -1;
317}
318
319////////////////////////////////////////////////////////////////////////////////
320/// Drop (i.e. delete) a database. Always returns non-zero for SQLite,
321/// as it has only one DB per file.
322
323Int_t TSQLiteServer::DropDataBase(const char* /*dbname*/)
324{
325 Error("DropDataBase", "DropDataBase command makes no sense for SQLite!");
326 return -1;
327}
328
329////////////////////////////////////////////////////////////////////////////////
330/// Reload permission tables. Returns 0 if successful, non-zero
331/// otherwise. User must have reload permissions.
332
334{
335 if (!IsConnected()) {
336 Error("Reload", "not connected");
337 return -1;
338 }
339
340 Error("Reload", "not implemented");
341 return 0;
342}
343
344////////////////////////////////////////////////////////////////////////////////
345/// Shutdown the database server. Returns 0 if successful, non-zero
346/// otherwise. Makes no sense for SQLite, always returns -1.
347
349{
350 if (!IsConnected()) {
351 Error("Shutdown", "not connected");
352 return -1;
353 }
354
355 Error("Shutdown", "not implemented");
356 return -1;
357}
358
359////////////////////////////////////////////////////////////////////////////////
360/// We assume prepared statements work for all SQLite-versions.
361/// As we actually use the recommended sqlite3_prepare(),
362/// or, if possible, sqlite3_prepare_v2(),
363/// this already introduces the "compile time check".
364
366{
367 return kTRUE;
368}
369
370////////////////////////////////////////////////////////////////////////////////
371/// Produce TSQLiteStatement.
372
374{
375 if (!sql || !*sql) {
376 SetError(-1, "no query string specified", "Statement");
377 return nullptr;
378 }
379
380 if (!IsConnected()) {
381 Error("Statement", "not connected");
382 return nullptr;
383 }
384
385 sqlite3_stmt *preparedStmt = nullptr;
386 const char *tail = nullptr;
387
388 // -1 as we read until we encounter a \0.
389#if SQLITE_VERSION_NUMBER >= 3005000
390 int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
391#else
392 int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
393#endif
394 if (retVal != SQLITE_OK) {
395 Error("Statement", "SQL Error: %d %s", retVal, sqlite3_errmsg(fSQLite));
396 return nullptr;
397 }
398 if (tail && tail[0] != '\0')
399 Warning("Statement", "Don't use multiple statements, '%s' statement was ignored", tail);
400
401 SQLite3_Stmt_t *stmt = new SQLite3_Stmt_t;
402 stmt->fConn = fSQLite;
403 stmt->fRes = preparedStmt;
404
405 return new TSQLiteStatement(stmt);
406}
407
408////////////////////////////////////////////////////////////////////////////////
409/// Return server info, must be deleted by user.
410
412{
413 if (!IsConnected()) {
414 Error("ServerInfo", "not connected");
415 return nullptr;
416 }
417
418 return fSrvInfo.Data();
419}
constexpr Bool_t kFALSE
Definition RtypesCore.h:101
constexpr Bool_t kTRUE
Definition RtypesCore.h:100
const char Option_t
Definition RtypesCore.h:66
#define ClassImp(name)
Definition Rtypes.h:377
#define SQLITE_OPEN_URI
char * Form(const char *fmt,...)
Formats a string in a circular formatting buffer.
Definition TString.cxx:2489
A doubly linked list.
Definition TList.h:38
void Add(TObject *obj) override
Definition TList.h:83
virtual void Warning(const char *method, const char *msgfmt,...) const
Issue warning message.
Definition TObject.cxx:973
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition TObject.cxx:987
void MakeZombie()
Definition TObject.h:53
virtual TSQLRow * Next()=0
virtual const char * GetField(Int_t field)=0
TString fHost
Definition TSQLServer.h:45
void SetError(Int_t code, const char *msg, const char *method=nullptr)
set new values for error fields if method is specified, displays error message
Int_t fPort
Definition TSQLServer.h:47
TString fDB
Definition TSQLServer.h:46
virtual Bool_t IsConnected() const
Definition TSQLServer.h:93
TString fType
Definition TSQLServer.h:44
Int_t SelectDataBase(const char *dbname) final
Select a database.
TSQLStatement * Statement(const char *sql, Int_t=100) final
Produce TSQLiteStatement.
Bool_t HasStatement() const final
We assume prepared statements work for all SQLite-versions.
Int_t DropDataBase(const char *dbname) final
Drop (i.e.
TSQLResult * GetTables(const char *dbname, const char *wild=nullptr) final
List all tables in the specified database.
Bool_t Commit() final
submit "COMMIT TRANSACTION" query to database return kTRUE, if successful
TSQLResult * Query(const char *sql) final
Execute SQL command.
Bool_t HasTransactionInFlight() final
returns kTRUE when transaction is running
const char * ServerInfo() final
Return server info, must be deleted by user.
Bool_t Rollback() final
submit "ROLLBACK TRANSACTION" query to database return kTRUE, if successful
TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=nullptr) final
List all columns in specified table (database argument is ignored).
Int_t Shutdown() final
Shutdown the database server.
Bool_t Exec(const char *sql) final
Execute SQL command which does not produce any result sets.
Int_t Reload() final
Reload permission tables.
sqlite3 * fSQLite
TSQLResult * GetDataBases(const char *wild=nullptr) final
List all available databases.
Int_t CreateDataBase(const char *dbname) final
Create a database.
~TSQLiteServer()
Close SQLite DB.
void Close(Option_t *opt="") final
Close connection to SQLite DB.
TSQLiteServer(const char *db, const char *uid=nullptr, const char *pw=nullptr)
Open a connection to an SQLite DB server.
Bool_t StartTransaction() final
submit "BEGIN TRANSACTION" query to database return kTRUE, if successful
TSQLTableInfo * GetTableInfo(const char *tablename) final
Produces SQL table info.
Basic string class.
Definition TString.h:139
const char * Data() const
Definition TString.h:376
sqlite3_stmt * fRes