|
| 1 | +/*************************************************************************** |
| 2 | + qgsoraclestablecache.cpp |
| 3 | + ------------------- |
| 4 | +begin : April 2014 |
| 5 | +copyright : (C) 2014 by Martin Dobias |
| 6 | +email : wonder.sk at gmail dot com |
| 7 | + ***************************************************************************/ |
| 8 | + |
| 9 | +/*************************************************************************** |
| 10 | + * * |
| 11 | + * This program is free software; you can redistribute it and/or modify * |
| 12 | + * it under the terms of the GNU General Public License as published by * |
| 13 | + * the Free Software Foundation; either version 2 of the License, or * |
| 14 | + * (at your option) any later version. * |
| 15 | + * * |
| 16 | + ***************************************************************************/ |
| 17 | + |
| 18 | +#include "qgsoracletablecache.h" |
| 19 | + |
| 20 | +#include <sqlite3.h> |
| 21 | + |
| 22 | +#include "qgsapplication.h" |
| 23 | + |
| 24 | +#include <QDir> |
| 25 | + |
| 26 | + |
| 27 | + |
| 28 | +static bool _executeSqliteStatement( sqlite3* db, const QString& sql ) |
| 29 | +{ |
| 30 | + sqlite3_stmt* stmt; |
| 31 | + if ( sqlite3_prepare_v2( db, sql.toUtf8().data(), -1, &stmt, NULL ) != SQLITE_OK ) |
| 32 | + return false; |
| 33 | + |
| 34 | + return sqlite3_step( stmt ) == SQLITE_DONE; |
| 35 | +} |
| 36 | + |
| 37 | +static bool _removeFromCache( sqlite3* db, const QString& connName ) |
| 38 | +{ |
| 39 | + QString tblName = "oracle_" + connName; |
| 40 | + |
| 41 | + QString sqlDeleteFromMeta = QString( "DELETE FROM meta_oracle WHERE conn = %1" ).arg( QgsOracleConn::quotedValue( connName ) ); |
| 42 | + bool res1 = _executeSqliteStatement( db, sqlDeleteFromMeta ); |
| 43 | + |
| 44 | + QString sqlDropTable = QString( "DROP TABLE IF EXISTS %1" ).arg( QgsOracleConn::quotedIdentifier( tblName ) ); |
| 45 | + bool res2 = _executeSqliteStatement( db, sqlDropTable ); |
| 46 | + |
| 47 | + return res1 && res2; |
| 48 | +} |
| 49 | + |
| 50 | + |
| 51 | +static sqlite3* _openCacheDatabase() |
| 52 | +{ |
| 53 | + sqlite3* database; |
| 54 | + if ( sqlite3_open_v2( QgsOracleTableCache::cacheDatabaseFilename().toUtf8().data(), &database, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0 ) != SQLITE_OK ) |
| 55 | + return 0; |
| 56 | + |
| 57 | + if ( !_executeSqliteStatement( database, "CREATE TABLE IF NOT EXISTS meta_oracle(conn text primary_key, flags int)" ) ) |
| 58 | + { |
| 59 | + sqlite3_close( database ); |
| 60 | + return 0; |
| 61 | + } |
| 62 | + |
| 63 | + return database; |
| 64 | +} |
| 65 | + |
| 66 | + |
| 67 | +static bool _hasCache( sqlite3* db, const QString& connName, int flags = -1 ) // flags == -1 implies any flags |
| 68 | +{ |
| 69 | + QString sqlCacheForConn = QString( "SELECT * FROM meta_oracle WHERE conn = %1" ).arg( QgsOracleConn::quotedValue( connName ) ); |
| 70 | + if ( flags >= 0 ) |
| 71 | + sqlCacheForConn.append( QString( " AND flags = %1" ).arg( flags ) ); |
| 72 | + |
| 73 | + char **results; |
| 74 | + int rows, columns; |
| 75 | + char *errMsg = NULL; |
| 76 | + bool res = sqlite3_get_table( db, sqlCacheForConn.toUtf8(), &results, &rows, &columns, &errMsg ) == SQLITE_OK; |
| 77 | + bool hasCache = ( res && rows == 1 ); |
| 78 | + sqlite3_free_table( results ); |
| 79 | + |
| 80 | + return hasCache; |
| 81 | +} |
| 82 | + |
| 83 | + |
| 84 | +static bool _renameConnectionInCache( sqlite3* db, const QString& oldName, const QString& newName ) |
| 85 | +{ |
| 86 | + if ( !_hasCache( db, oldName ) ) |
| 87 | + return true; |
| 88 | + |
| 89 | + QString sql1 = QString( "ALTER TABLE %1 RENAME TO %2" ).arg( QgsOracleConn::quotedIdentifier( "oracle_" + oldName ) ).arg( QgsOracleConn::quotedIdentifier( "oracle_" + newName ) ); |
| 90 | + bool res1 = _executeSqliteStatement( db, sql1 ); |
| 91 | + |
| 92 | + QString sql2 = QString( "UPDATE meta_oracle SET conn = %1 WHERE conn = %2" ).arg( QgsOracleConn::quotedIdentifier( newName ) ).arg( QgsOracleConn::quotedIdentifier( oldName ) ); |
| 93 | + bool res2 = _executeSqliteStatement( db, sql2 ); |
| 94 | + |
| 95 | + return res1 && res2; |
| 96 | +} |
| 97 | + |
| 98 | + |
| 99 | + |
| 100 | +QString QgsOracleTableCache::cacheDatabaseFilename() |
| 101 | +{ |
| 102 | + return QgsApplication::qgisSettingsDirPath() + QDir::separator() + "data_sources_cache.db"; |
| 103 | +} |
| 104 | + |
| 105 | +bool QgsOracleTableCache::hasCache( const QString& connName, CacheFlags flags ) |
| 106 | +{ |
| 107 | + sqlite3* db = _openCacheDatabase(); |
| 108 | + if ( !db ) |
| 109 | + return false; |
| 110 | + |
| 111 | + bool hasCache = _hasCache( db, connName, ( int ) flags ); |
| 112 | + |
| 113 | + sqlite3_close( db ); |
| 114 | + return hasCache; |
| 115 | +} |
| 116 | + |
| 117 | + |
| 118 | +bool QgsOracleTableCache::saveToCache( const QString& connName, CacheFlags flags, const QVector<QgsOracleLayerProperty>& layers ) |
| 119 | +{ |
| 120 | + sqlite3* db = _openCacheDatabase(); |
| 121 | + if ( !db ) |
| 122 | + return false; |
| 123 | + |
| 124 | + QString tblNameRaw = "oracle_" + connName; |
| 125 | + QString tblName = QgsOracleConn::quotedIdentifier( tblNameRaw ); |
| 126 | + |
| 127 | + // recreate the cache table |
| 128 | + |
| 129 | + if ( !_removeFromCache( db, connName ) ) |
| 130 | + { |
| 131 | + sqlite3_close( db ); |
| 132 | + return false; |
| 133 | + } |
| 134 | + |
| 135 | + QString sqlCreateTable = QString( "CREATE TABLE %1 (ownername text, tablename text, geometrycolname text, isview int, sql text, pkcols text, geomtypes text, geomsrids text)" ).arg( tblName ); |
| 136 | + QString sqlInsertToMeta = QString( "INSERT INTO meta_oracle VALUES (%1, %2)" ).arg( QgsOracleConn::quotedValue( connName ) ).arg(( int ) flags ); |
| 137 | + |
| 138 | + bool res1 = _executeSqliteStatement( db, sqlCreateTable ); |
| 139 | + bool res2 = _executeSqliteStatement( db, sqlInsertToMeta ); |
| 140 | + if ( !res1 || !res2 ) |
| 141 | + { |
| 142 | + sqlite3_close( db ); |
| 143 | + return false; |
| 144 | + } |
| 145 | + |
| 146 | + // insert data |
| 147 | + |
| 148 | + _executeSqliteStatement( db, "BEGIN" ); |
| 149 | + |
| 150 | + QString sqlInsert = QString( "INSERT INTO %1 VALUES(?,?,?,?,?,?,?,?)" ).arg( tblName ); |
| 151 | + sqlite3_stmt* stmtInsert; |
| 152 | + if ( sqlite3_prepare_v2( db, sqlInsert.toUtf8().data(), -1, &stmtInsert, 0 ) != SQLITE_OK ) |
| 153 | + { |
| 154 | + sqlite3_close( db ); |
| 155 | + return false; |
| 156 | + } |
| 157 | + |
| 158 | + bool insertOk = true; |
| 159 | + foreach ( const QgsOracleLayerProperty& item, layers ) |
| 160 | + { |
| 161 | + sqlite3_bind_text( stmtInsert, 1, item.ownerName.toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 162 | + sqlite3_bind_text( stmtInsert, 2, item.tableName.toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 163 | + sqlite3_bind_text( stmtInsert, 3, item.geometryColName.toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 164 | + sqlite3_bind_int( stmtInsert, 4, item.isView ); |
| 165 | + sqlite3_bind_text( stmtInsert, 5, item.sql.toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 166 | + |
| 167 | + sqlite3_bind_text( stmtInsert, 6, item.pkCols.join( "," ).toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 168 | + |
| 169 | + QStringList geomTypes; |
| 170 | + foreach ( QGis::WkbType geomType, item.types ) |
| 171 | + geomTypes. append( QString::number( static_cast<ulong>( geomType ) ) ); |
| 172 | + sqlite3_bind_text( stmtInsert, 7, geomTypes.join( "," ).toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 173 | + |
| 174 | + QStringList geomSrids; |
| 175 | + foreach ( int geomSrid, item.srids ) |
| 176 | + geomSrids.append( QString::number( geomSrid ) ); |
| 177 | + sqlite3_bind_text( stmtInsert, 8, geomSrids.join( "," ).toUtf8().data(), -1, SQLITE_TRANSIENT ); |
| 178 | + |
| 179 | + if ( sqlite3_step( stmtInsert ) != SQLITE_DONE ) |
| 180 | + insertOk = false; |
| 181 | + |
| 182 | + sqlite3_reset( stmtInsert ); |
| 183 | + } |
| 184 | + |
| 185 | + sqlite3_finalize( stmtInsert ); |
| 186 | + |
| 187 | + _executeSqliteStatement( db, "COMMIT" ); |
| 188 | + |
| 189 | + sqlite3_close( db ); |
| 190 | + return insertOk; |
| 191 | +} |
| 192 | + |
| 193 | + |
| 194 | +bool QgsOracleTableCache::loadFromCache( const QString& connName, CacheFlags flags, QVector<QgsOracleLayerProperty>& layers ) |
| 195 | +{ |
| 196 | + sqlite3* db = _openCacheDatabase(); |
| 197 | + if ( !db ) |
| 198 | + return false; |
| 199 | + |
| 200 | + if ( !_hasCache( db, connName, ( int ) flags ) ) |
| 201 | + return false; |
| 202 | + |
| 203 | + sqlite3_stmt* stmt; |
| 204 | + QString sql = QString( "SELECT * FROM %1" ).arg( QgsOracleConn::quotedIdentifier( "oracle_" + connName ) ); |
| 205 | + if ( sqlite3_prepare_v2( db, sql.toUtf8().data(), -1, &stmt, NULL ) != SQLITE_OK ) |
| 206 | + { |
| 207 | + sqlite3_close( db ); |
| 208 | + return false; |
| 209 | + } |
| 210 | + |
| 211 | + while ( sqlite3_step( stmt ) == SQLITE_ROW ) |
| 212 | + { |
| 213 | + QgsOracleLayerProperty layer; |
| 214 | + layer.ownerName = QString::fromUtf8(( const char * ) sqlite3_column_text( stmt, 0 ) ); |
| 215 | + layer.tableName = QString::fromUtf8(( const char * ) sqlite3_column_text( stmt, 1 ) ); |
| 216 | + layer.geometryColName = QString::fromUtf8(( const char * ) sqlite3_column_text( stmt, 2 ) ); |
| 217 | + layer.isView = sqlite3_column_int( stmt, 3 ); |
| 218 | + layer.sql = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 4 ) ); |
| 219 | + |
| 220 | + QString pkCols = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 5 ) ); |
| 221 | + layer.pkCols = pkCols.split( ",", QString::SkipEmptyParts ); |
| 222 | + |
| 223 | + QString geomTypes = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 6 ) ); |
| 224 | + foreach ( QString geomType, geomTypes.split( ",", QString::SkipEmptyParts ) ) |
| 225 | + layer.types.append( static_cast<QGis::WkbType>( geomType.toInt() ) ); |
| 226 | + |
| 227 | + QString geomSrids = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 7 ) ); |
| 228 | + foreach ( QString geomSrid, geomSrids.split( ",", QString::SkipEmptyParts ) ) |
| 229 | + layer.srids.append( geomSrid.toInt() ); |
| 230 | + |
| 231 | + layers.append( layer ); |
| 232 | + } |
| 233 | + |
| 234 | + sqlite3_finalize( stmt ); |
| 235 | + |
| 236 | + sqlite3_close( db ); |
| 237 | + return true; |
| 238 | +} |
| 239 | + |
| 240 | + |
| 241 | +bool QgsOracleTableCache::removeFromCache( const QString& connName ) |
| 242 | +{ |
| 243 | + sqlite3* db = _openCacheDatabase(); |
| 244 | + if ( !db ) |
| 245 | + return false; |
| 246 | + |
| 247 | + bool res = _removeFromCache( db, connName ); |
| 248 | + |
| 249 | + sqlite3_close( db ); |
| 250 | + return res; |
| 251 | +} |
| 252 | + |
| 253 | + |
| 254 | +bool QgsOracleTableCache::renameConnectionInCache( const QString& oldName, const QString& newName ) |
| 255 | +{ |
| 256 | + sqlite3* db = _openCacheDatabase(); |
| 257 | + if ( !db ) |
| 258 | + return false; |
| 259 | + |
| 260 | + bool res = _renameConnectionInCache( db, oldName, newName ); |
| 261 | + |
| 262 | + sqlite3_close( db ); |
| 263 | + return res; |
| 264 | +} |
| 265 | + |
| 266 | + |
| 267 | +#if 0 |
| 268 | +// testing routine - ideally it should be a unit test |
| 269 | +void _testTableCache() |
| 270 | +{ |
| 271 | + QString connName = "local"; |
| 272 | + QVector<QgsOracleLayerProperty> layers; |
| 273 | + |
| 274 | + // fetch |
| 275 | + |
| 276 | + QgsDataSourceURI uri = QgsOracleConn::connUri( connName ); |
| 277 | + QgsOracleConn* c = QgsOracleConn::connectDb( uri.connectionInfo() ); |
| 278 | + if ( !c ) |
| 279 | + return; |
| 280 | + |
| 281 | + c->supportedLayers( layers, true ); |
| 282 | + |
| 283 | + bool useEstimated = true; |
| 284 | + bool onlyExisting = QgsOracleConn::onlyExistingTypes( connName ); |
| 285 | + |
| 286 | + for ( QVector<QgsOracleLayerProperty>::iterator it = layers.begin(), end = layers.end(); it != end; ++it ) |
| 287 | + { |
| 288 | + QgsOracleLayerProperty &layerProperty = *it; |
| 289 | + c->retrieveLayerTypes( layerProperty, useEstimated, onlyExisting ); |
| 290 | + } |
| 291 | + |
| 292 | + c->disconnect(); |
| 293 | + |
| 294 | + // save |
| 295 | + |
| 296 | + QgsOracleTableCache::CacheFlags flags = QgsOracleTableCache::UseEstimatedTableMetadata | QgsOracleTableCache::OnlyExistingGeometryTypes; |
| 297 | + QgsOracleTableCache::saveToCache( connName, flags, layers ); |
| 298 | + |
| 299 | + // load |
| 300 | + |
| 301 | + QVector<QgsOracleLayerProperty> layersLoaded; |
| 302 | + QgsOracleTableCache::loadFromCache( connName, flags, layersLoaded ); |
| 303 | + |
| 304 | + // compare |
| 305 | + |
| 306 | + foreach ( const QgsOracleLayerProperty& item, layers ) |
| 307 | + qDebug( "== %s %s", item.tableName.toAscii().data(), item.geometryColName.toAscii().data() ); |
| 308 | + |
| 309 | + foreach ( const QgsOracleLayerProperty& item, layersLoaded ) |
| 310 | + qDebug( "++ %s %s", item.tableName.toAscii().data(), item.geometryColName.toAscii().data() ); |
| 311 | + |
| 312 | + Q_ASSERT( layers == layersLoaded ); |
| 313 | +} |
| 314 | +#endif |
0 commit comments