SqliteConn.cpp 15.8 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/*
 * The CERN Tape Archive (CTA) project
 * Copyright (C) 2015  CERN
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

19
#include "common/exception/Exception.hpp"
20
#include "common/make_unique.hpp"
21
#include "common/threading/MutexLocker.hpp"
22
23
#include "common/utils/utils.hpp"
#include "common/utils/Regex.hpp"
24
#include "rdbms/Conn.hpp"
25
26
27
#include "rdbms/wrapper/Sqlite.hpp"
#include "rdbms/wrapper/SqliteConn.hpp"
#include "rdbms/wrapper/SqliteStmt.hpp"
Steven Murray's avatar
Steven Murray committed
28

29
#include <iostream>
Steven Murray's avatar
Steven Murray committed
30
31
#include <stdexcept>
#include <string>
32
#include <string.h>
33

34
namespace cta {
35
namespace rdbms {
36
namespace wrapper {
37

38
39
40
//------------------------------------------------------------------------------
// constructor
//------------------------------------------------------------------------------
41
SqliteConn::SqliteConn(const std::string &filename) {
42
  try {
43
44
45
46
    m_sqliteConn = nullptr;
    if(sqlite3_open_v2(filename.c_str(), &m_sqliteConn, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_URI, nullptr)) {
      std::string msg = sqlite3_errmsg(m_sqliteConn);
      sqlite3_close(m_sqliteConn);
47
      throw exception::Exception(msg);
48
    }
49

50
51
52
    // Enable extended result codes
    sqlite3_extended_result_codes(m_sqliteConn, 1);

53
54
    // Give SQLite upto 120 seconds to avoid a busy error
    sqlite3_busy_timeout(m_sqliteConn, 120000);
55

56
    {
57
      char *errMsg = nullptr;
58
      if(SQLITE_OK != sqlite3_exec(m_sqliteConn, "PRAGMA foreign_keys = ON;", nullptr, nullptr, &errMsg)) {
59
60
        exception::Exception ex;
        ex.getMessage() << "Failed to to set PRAGMA foreign_keys = ON";
61
        if(nullptr != errMsg) {
62
63
64
          ex.getMessage() << ": " << errMsg;
          sqlite3_free(errMsg);
        }
65
        sqlite3_close(m_sqliteConn);
66
67
68
        throw ex;
      }
    }
69
70
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
71
72
73
74
75
76
  }
}

//------------------------------------------------------------------------------
// destructor
//------------------------------------------------------------------------------
77
SqliteConn::~SqliteConn() {
78
79
80
81
82
  try {
    close(); // Idempotent close() method
  } catch(...) {
    // Destructor should not throw any exceptions
  }
83
84
85
86
87
}

//------------------------------------------------------------------------------
// close
//------------------------------------------------------------------------------
88
void SqliteConn::close() {
89
  threading::MutexLocker locker(m_mutex);
90

91
  if(nullptr != m_sqliteConn) {
92
93
94
95
96
97
    const int closeRc = sqlite3_close(m_sqliteConn);
    if(SQLITE_OK != closeRc) {
      exception::Exception ex;
      ex.getMessage() << "Failed to close SQLite connection: " << Sqlite::rcToStr(closeRc);
      throw ex;
    }
98
    m_sqliteConn = nullptr;
99
  }
100
101
}

102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
//------------------------------------------------------------------------------
// setAutocommitMode
//------------------------------------------------------------------------------
void SqliteConn::setAutocommitMode(const AutocommitMode autocommitMode) {
  if(AutocommitMode::AUTOCOMMIT_OFF == autocommitMode) {
    throw rdbms::Conn::AutocommitModeNotSupported("Failed to set autocommit mode to AUTOCOMMIT_OFF: SqliteConn only"
      " supports AUTOCOMMIT_ON");
  }
}

//------------------------------------------------------------------------------
// getAutocommitMode
//------------------------------------------------------------------------------
AutocommitMode SqliteConn::getAutocommitMode() const noexcept{
  return AutocommitMode::AUTOCOMMIT_ON;
}

119
120
121
//------------------------------------------------------------------------------
// executeNonQuery
//------------------------------------------------------------------------------
122
void SqliteConn::executeNonQuery(const std::string &sql) {
123
124
  try {
    auto stmt = createStmt(sql);
125
    stmt->executeNonQuery();
126
127
128
129
130
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
}

131
//------------------------------------------------------------------------------
132
133
// createStmt
//------------------------------------------------------------------------------
134
std::unique_ptr<StmtWrapper> SqliteConn::createStmt(const std::string &sql) {
135
  try {
136
    threading::MutexLocker locker(m_mutex);
137

138
    if(nullptr == m_sqliteConn) {
139
140
141
      throw exception::Exception("Connection is closed");
    }

142
    return cta::make_unique<SqliteStmt>(*this, sql);
143
  } catch(exception::Exception &ex) {
144
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
145
  }
146
147
}

148
149
150
151
//------------------------------------------------------------------------------
// commit
//------------------------------------------------------------------------------
void SqliteConn::commit() {
152
  try {
153
    threading::MutexLocker locker(m_mutex);
154

155
    if(nullptr == m_sqliteConn) {
156
157
158
      throw exception::Exception("Connection is closed");
    }

159
160
161
162
163
    char *errMsg = nullptr;
    if(SQLITE_OK != sqlite3_exec(m_sqliteConn, "COMMIT", nullptr, nullptr, &errMsg)) {
      if(nullptr == errMsg) {
        throw exception::Exception("sqlite3_exec failed");
      } else if(strcmp("cannot commit - no transaction is active", errMsg)) {
164
        exception::Exception ex;
165
166
        ex.getMessage() << "sqlite3_exec failed: " << errMsg;
        sqlite3_free(errMsg);
167
        throw ex;
168
169
      } else {
        sqlite3_free(errMsg);
170
171
      }
    }
172
173
174
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
175
176
177
}

//------------------------------------------------------------------------------
178
// rollback
179
180
//------------------------------------------------------------------------------
void SqliteConn::rollback() {
181
  try {
182
    threading::MutexLocker locker(m_mutex);
183

184
    if(nullptr == m_sqliteConn) {
185
186
187
      throw exception::Exception("Connection is closed");
    }

188
189
190
191
192
193
194
    char *errMsg = nullptr;
    if(SQLITE_OK != sqlite3_exec(m_sqliteConn, "ROLLBACK", nullptr, nullptr, &errMsg)) {
      exception::Exception ex;
      ex.getMessage() << "sqlite3_exec failed";
      if(nullptr != errMsg) {
        ex.getMessage() << ": " << errMsg;
        sqlite3_free(errMsg);
195
      }
196
      throw ex;
197
    }
198
199
200
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
201
202
}

203
//------------------------------------------------------------------------------
204
// printSchema
205
//------------------------------------------------------------------------------
206
207
208
209
210
211
void SqliteConn::printSchema(std::ostream &os) {
  try {
    const char *const sql =
      "SELECT "
        "NAME AS NAME, "
        "TYPE AS TYPE "
212
      "FROM "
213
        "SQLITE_MASTER "
214
      "ORDER BY "
215
216
        "TYPE, "
        "NAME;";
217
    auto stmt = createStmt(sql);
218
    auto rset = stmt->executeQuery();
219
220
    os << "NAME, TYPE" << std::endl;
    os << "==========" << std::endl;
221
222
223
224
    while (rset->next()) {
      const auto name = rset->columnOptionalString("NAME");
      const auto type = rset->columnOptionalString("TYPE");
      os << (name ? name.value() : "NULL") << ", " << (type ? type.value() : "NULL") << std::endl;
225
226
227
    }
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
228
229
  }
}
230

231
232
233
234
//------------------------------------------------------------------------------
// getColumns
//------------------------------------------------------------------------------
std::map<std::string, std::string> SqliteConn::getColumns(const std::string &tableName) {
235
236
  try {
    std::map<std::string, std::string> columnNamesAndTypes;
237
    const char *const sql =
238
239
240
      "SELECT "
        "SQL AS SQL "
      "FROM "
241
242
243
244
245
        "("
          "SELECT TBL_NAME, TYPE, SQL FROM SQLITE_MASTER "
            "UNION ALL "
          "SELECT TBL_NAME, TYPE, SQL FROM SQLITE_TEMP_MASTER"
        ") "
246
      "WHERE "
247
        "TBL_NAME = :TABLE_NAME "
248
249
250
251
252
253
254
      "AND "
      "TYPE = 'table';";
    const std::string columnTypes = 
    "NUMERIC|"
    "INTEGER|"
    "CHAR|"
    "VARCHAR|"
255
    "VARCHAR2|"
256
    "BLOB|"
257
258
259
260
261
    "BIGINT|"
    "SMALLINT|"
    "INT|"
    "TINYINT|"
    "VARBINARY|"
262
    "BYTEA|"
263
    "RAW";
264
265
    
    auto stmt = createStmt(sql);
266
    stmt->bindString(":TABLE_NAME", tableName);
267
268
269
270
271
272
273
274
275
276
277
    auto rset = stmt->executeQuery();
    if (rset->next()) {
      auto tableSql = rset->columnOptionalString("SQL").value();     
      tableSql += std::string(","); // hack for parsing          
      std::string::size_type searchPosComma = 0;
      std::string::size_type findResultComma = std::string::npos;
      while(std::string::npos != (findResultComma = tableSql.find(',', searchPosComma))) {
        const std::string::size_type stmtLenComma = findResultComma - searchPosComma;
        const std::string sqlStmtComma = utils::trimString(tableSql.substr(searchPosComma, stmtLenComma));
        searchPosComma = findResultComma + 1;
        if(0 < sqlStmtComma.size()) { // Ignore empty statements
278
          const std::string columnSQL = "([a-zA-Z_0-9]+) +(" + columnTypes + ")";
279
280
281
282
283
284
285
286
287
288
289
290
          cta::utils::Regex columnSqlRegex(columnSQL.c_str());
          auto columnSql = columnSqlRegex.exec(sqlStmtComma);
          if (3 == columnSql.size()) {
            columnNamesAndTypes.insert(std::make_pair(columnSql[1], columnSql[2]));
          }
        }
      }     
    }
    return columnNamesAndTypes;
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  } 
291
292
}

Steven Murray's avatar
Steven Murray committed
293
294
295
296
297
298
299
300
301
//------------------------------------------------------------------------------
// getTableNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getTableNames() {
  try {
    const char *const sql =
      "SELECT "
        "NAME AS NAME "
      "FROM "
302
303
304
305
306
        "("
          "SELECT NAME, TYPE FROM SQLITE_MASTER "
            "UNION ALL "
          "SELECT NAME, TYPE FROM SQLITE_TEMP_MASTER"
        ") "
Steven Murray's avatar
Steven Murray committed
307
308
309
310
      "WHERE "
        "TYPE = 'table' "
      "ORDER BY "
        "NAME;";
311
    auto stmt = createStmt(sql);
312
    auto rset = stmt->executeQuery();
Steven Murray's avatar
Steven Murray committed
313
    std::list<std::string> names;
314
    while (rset->next()) {
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
      auto name = rset->columnOptionalString("NAME");
      if(name) {
        names.push_back(name.value());
      }
    }
    return names;
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
}

//------------------------------------------------------------------------------
// getIndexNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getIndexNames() {
  try {
    const char *const sql =
      "SELECT "
        "NAME AS NAME "
      "FROM "
335
336
337
338
339
      "("
        "SELECT NAME, TYPE FROM SQLITE_MASTER "
          "UNION ALL "
        "SELECT NAME, TYPE FROM SQLITE_TEMP_MASTER"
      ") "
340
341
342
343
344
345
346
347
      "WHERE "
        "TYPE = 'index' "
      "ORDER BY "
        "NAME;";
    auto stmt = createStmt(sql);
    auto rset = stmt->executeQuery();
    std::list<std::string> names;
    while (rset->next()) {
348
349
350
351
      auto name = rset->columnOptionalString("NAME");
      if(name) {
        names.push_back(name.value());
      }
Steven Murray's avatar
Steven Murray committed
352
353
354
355
356
357
358
    }
    return names;
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
}

359
360
361
362
//------------------------------------------------------------------------------
// isOpen
//------------------------------------------------------------------------------
bool SqliteConn::isOpen() const {
363
  return nullptr != m_sqliteConn;
364
365
}

366
367
368
369
370
371
372
373
374
375
376
//------------------------------------------------------------------------------
// getSequenceNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getSequenceNames() {
  try {
    return std::list<std::string>();
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
}

377
378
379
380
381
382
383
//------------------------------------------------------------------------------
// getTriggerNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getTriggerNames() {
  return std::list<std::string>();
}

384
385
386
//------------------------------------------------------------------------------
// getParallelTableNames
//------------------------------------------------------------------------------
387
388
389
390
std::list<std::string> SqliteConn::getParallelTableNames(){
  return std::list<std::string>();
}

391
392
393
394
395
396
397
398
399
400
//------------------------------------------------------------------------------
// getConstraintNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getConstraintNames(const std::string &tableName){
  try {
    std::list<std::string> constraintNames;
    const char *const sql = 
    "SELECT "
      "SQL AS SQL "
    "FROM "
401
402
403
404
405
      "("
        "SELECT SQL, TYPE, NAME FROM SQLITE_MASTER "
          "UNION ALL "
        "SELECT SQL, TYPE, NAME FROM SQLITE_TEMP_MASTER"
      ") "
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
    "WHERE TYPE = 'table' "
      "AND NAME = :TABLE_NAME ";
    auto stmt = createStmt(sql);
    stmt->bindString(":TABLE_NAME", tableName);
    auto rset = stmt->executeQuery();
    if (rset->next()) {
      auto tableSql = rset->columnOptionalString("SQL").value();     
      tableSql += std::string(","); // hack for parsing          
      std::string::size_type searchPosComma = 0;
      std::string::size_type findResultComma = std::string::npos;
      while(std::string::npos != (findResultComma = tableSql.find(',', searchPosComma))) {
        const std::string::size_type stmtLenComma = findResultComma - searchPosComma;
        const std::string sqlStmtComma = utils::trimString(tableSql.substr(searchPosComma, stmtLenComma));
        searchPosComma = findResultComma + 1;
        if(0 < sqlStmtComma.size()) { // Ignore empty statements
          const std::string constraintSQL = "CONSTRAINT ([a-zA-Z_0-9]+)";
          cta::utils::Regex constraintSQLRegex(constraintSQL.c_str());
          auto constraintSql = constraintSQLRegex.exec(sqlStmtComma);
          if (2 == constraintSql.size()) {
            constraintNames.emplace_back(constraintSql[1]);
          }
        }
      }     
    }
    return constraintNames;
  } catch(exception::Exception &ex) {
    throw exception::Exception(std::string(__FUNCTION__) + " failed: " + ex.getMessage().str());
  }
434
435
436
437
438
439
440
441
      }


//------------------------------------------------------------------------------
// getStoredProcedureNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getStoredProcedureNames() {
  return std::list<std::string>();
442
443
}

444
445
446
447
448
449
450
451
452
453
454
455
456
457
//------------------------------------------------------------------------------
// getSynonymNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getSynonymNames() {
  return std::list<std::string>();
}

//------------------------------------------------------------------------------
// getTypeNames
//------------------------------------------------------------------------------
std::list<std::string> SqliteConn::getTypeNames() {
  return std::list<std::string>();
}

458

459
} // namespace wrapper
460
} // namespace rdbms
461
} // namespace cta