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