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