QGIS API Documentation  3.17.0-Master (a035f434f4)
qgssqliteutils.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgssqliteutils.cpp
3  -------------------
4  begin : Nov, 2017
5  copyright : (C) 2017 by Nyall Dawson
6  email : nyall dot dawson 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 "qgssqliteutils.h"
19 
20 #include <sqlite3.h>
21 #include <cstdarg>
22 #include <QVariant>
23 #include <QSet>
24 
25 // Temporary solution until GDAL Unique support is available
26 #include <regex>
27 #include <sstream>
28 #include <algorithm>
29 // end temporary
30 
32 {
33  sqlite3_close_v2( database );
34 }
35 
36 void QgsSqlite3StatementFinalizer::operator()( sqlite3_stmt *statement )
37 {
38  sqlite3_finalize( statement );
39 }
40 
42 {
43  return sqlite3_step( get() );
44 }
45 
46 QString sqlite3_statement_unique_ptr::columnName( int column ) const
47 {
48  return QString::fromUtf8( static_cast<const char *>( sqlite3_column_name( get(), column ) ) );
49 }
50 
52 {
53  return sqlite3_column_double( get(), column );
54 }
55 
57 {
58  return sqlite3_column_count( get() );
59 }
60 
61 QString sqlite3_statement_unique_ptr::columnAsText( int column ) const
62 {
63  return QString::fromUtf8( reinterpret_cast<const char *>( sqlite3_column_text( get(), column ) ) );
64 }
65 
66 QByteArray sqlite3_statement_unique_ptr::columnAsBlob( int column ) const
67 {
68  const void *blob = sqlite3_column_blob( get(), column );
69  int size = sqlite3_column_bytes( get(), column );
70  return QByteArray( reinterpret_cast<const char *>( blob ), size );
71 }
72 
73 qlonglong sqlite3_statement_unique_ptr::columnAsInt64( int column ) const
74 {
75  return sqlite3_column_int64( get(), column );
76 }
77 
78 int sqlite3_database_unique_ptr::open( const QString &path )
79 {
80  sqlite3 *database = nullptr;
81  int result = sqlite3_open( path.toUtf8(), &database );
82  reset( database );
83  return result;
84 }
85 
86 int sqlite3_database_unique_ptr::open_v2( const QString &path, int flags, const char *zVfs )
87 {
88  sqlite3 *database = nullptr;
89  int result = sqlite3_open_v2( path.toUtf8(), &database, flags, zVfs );
90  reset( database );
91  return result;
92 }
93 
95 {
96  return QString( sqlite3_errmsg( get() ) );
97 }
98 
99 sqlite3_statement_unique_ptr sqlite3_database_unique_ptr::prepare( const QString &sql, int &resultCode ) const
100 {
101  sqlite3_stmt *preparedStatement = nullptr;
102  const char *tail = nullptr;
103  resultCode = sqlite3_prepare( get(), sql.toUtf8(), sql.toUtf8().length(), &preparedStatement, &tail );
105  s.reset( preparedStatement );
106  return s;
107 }
108 
109 int sqlite3_database_unique_ptr::exec( const QString &sql, QString &errorMessage ) const
110 {
111  char *errMsg;
112 
113  int ret = sqlite3_exec( get(), sql.toUtf8(), nullptr, nullptr, &errMsg );
114 
115  if ( errMsg )
116  {
117  errorMessage = QString::fromUtf8( errMsg );
118  sqlite3_free( errMsg );
119  }
120 
121  return ret;
122 }
123 
124 QSet<QString> QgsSqliteUtils::uniqueFields( sqlite3 *connection, const QString &tableName, QString &errorMessage )
125 {
126  QSet<QString> uniqueFieldsResults;
127  char *zErrMsg = 0;
128  std::vector<std::string> rows;
129  QByteArray tableNameUtf8 = tableName.toUtf8();
130  QString sql = qgs_sqlite3_mprintf( "select sql from sqlite_master "
131  "where type='table' and name='%q'", tableNameUtf8.constData() );
132  auto cb = [ ](
133  void *data /* Data provided in the 4th argument of sqlite3_exec() */,
134  int /* The number of columns in row */,
135  char **argv /* An array of strings representing fields in the row */,
136  char ** /* An array of strings representing column names */ ) -> int
137  {
138  static_cast<std::vector<std::string>*>( data )->push_back( argv[0] );
139  return 0;
140  };
141 
142  int rc = sqlite3_exec( connection, sql.toUtf8(), cb, ( void * )&rows, &zErrMsg );
143  if ( rc != SQLITE_OK )
144  {
145  errorMessage = zErrMsg;
146  sqlite3_free( zErrMsg );
147  return uniqueFieldsResults;
148  }
149 
150  // Match identifiers with " or ` or no delimiter (and no spaces).
151  std::smatch uniqueFieldMatch;
152  static const std::regex sFieldIdentifierRe { R"raw(\s*(["`]([^"`]+)["`])|(([^\s]+)\s).*)raw" };
153  for ( auto tableDefinition : rows )
154  {
155  tableDefinition = tableDefinition.substr( tableDefinition.find( '(' ), tableDefinition.rfind( ')' ) );
156  std::stringstream tableDefinitionStream { tableDefinition };
157  while ( tableDefinitionStream.good() )
158  {
159  std::string fieldStr;
160  std::getline( tableDefinitionStream, fieldStr, ',' );
161  std::string upperCaseFieldStr { fieldStr };
162  std::transform( upperCaseFieldStr.begin(), upperCaseFieldStr.end(), upperCaseFieldStr.begin(), ::toupper );
163  if ( upperCaseFieldStr.find( "UNIQUE" ) != std::string::npos )
164  {
165  if ( std::regex_search( fieldStr, uniqueFieldMatch, sFieldIdentifierRe ) )
166  {
167  const std::string quoted { uniqueFieldMatch.str( 2 ) };
168  uniqueFieldsResults.insert( QString::fromStdString( quoted.length() ? quoted : uniqueFieldMatch.str( 4 ) ) );
169  }
170  }
171  }
172  }
173  rows.clear();
174 
175  // Search indexes:
176  sql = qgs_sqlite3_mprintf( "SELECT sql FROM sqlite_master WHERE type='index' AND"
177  " tbl_name='%q' AND sql LIKE 'CREATE UNIQUE INDEX%%'", tableNameUtf8.constData() );
178  rc = sqlite3_exec( connection, sql.toUtf8(), cb, ( void * )&rows, &zErrMsg );
179  if ( rc != SQLITE_OK )
180  {
181  errorMessage = zErrMsg;
182  sqlite3_free( zErrMsg );
183  return uniqueFieldsResults;
184  }
185 
186  if ( rows.size() > 0 )
187  {
188  static const std::regex sFieldIndexIdentifierRe { R"raw(\(\s*[`"]?([^",`\)]+)["`]?\s*\))raw" };
189  for ( auto indexDefinition : rows )
190  {
191  std::string upperCaseIndexDefinition { indexDefinition };
192  std::transform( upperCaseIndexDefinition.begin(), upperCaseIndexDefinition.end(), upperCaseIndexDefinition.begin(), ::toupper );
193  if ( upperCaseIndexDefinition.find( "UNIQUE" ) != std::string::npos )
194  {
195  indexDefinition = indexDefinition.substr( indexDefinition.find( '(' ), indexDefinition.rfind( ')' ) );
196  if ( std::regex_search( indexDefinition, uniqueFieldMatch, sFieldIndexIdentifierRe ) )
197  {
198  uniqueFieldsResults.insert( QString::fromStdString( uniqueFieldMatch.str( 1 ) ) );
199  }
200  }
201  }
202  }
204 }
205 
206 long long QgsSqliteUtils::nextSequenceValue( sqlite3 *connection, const QString &tableName, QString errorMessage )
207 {
208  long long result { -1 };
210  dsPtr.reset( connection );
211  const QString quotedTableName { QgsSqliteUtils::quotedValue( tableName ) };
212 
213  int resultCode;
214  sqlite3_statement_unique_ptr stmt { dsPtr.prepare( QStringLiteral( "SELECT seq FROM sqlite_sequence WHERE name = %1" )
215  .arg( quotedTableName ), resultCode )};
216  if ( resultCode == SQLITE_OK )
217  {
218  stmt.step();
219  result = sqlite3_column_int64( stmt.get(), 0 );
220  // Try to create the sequence in case this is an empty layer
221  if ( sqlite3_column_count( stmt.get() ) == 0 )
222  {
223  dsPtr.exec( QStringLiteral( "INSERT INTO sqlite_sequence (name, seq) VALUES (%1, 1)" ).arg( quotedTableName ), errorMessage );
224  if ( errorMessage.isEmpty() )
225  {
226  result = 1;
227  }
228  else
229  {
230  errorMessage = QObject::tr( "Error retrieving default value for %1" ).arg( tableName );
231  }
232  }
233  else // increment
234  {
235  if ( dsPtr.exec( QStringLiteral( "UPDATE sqlite_sequence SET seq = %1 WHERE name = %2" )
236  .arg( QString::number( ++result ), quotedTableName ),
237  errorMessage ) != SQLITE_OK )
238  {
239  errorMessage = QObject::tr( "Error retrieving default value for %1" ).arg( tableName );
240  result = -1;
241  }
242  }
243  }
244 
245  dsPtr.release();
246  return result;
247 }
248 
249 QString QgsSqliteUtils::quotedString( const QString &value )
250 {
251  if ( value.isNull() )
252  return QStringLiteral( "NULL" );
253 
254  QString v = value;
255  v.replace( '\'', QLatin1String( "''" ) );
256  return v.prepend( '\'' ).append( '\'' );
257 }
258 
259 QString QgsSqliteUtils::quotedIdentifier( const QString &identifier )
260 {
261  QString id( identifier );
262  id.replace( '\"', QLatin1String( "\"\"" ) );
263  return id.prepend( '\"' ).append( '\"' );
264 }
265 
266 QString QgsSqliteUtils::quotedValue( const QVariant &value )
267 {
268  if ( value.isNull() )
269  return QStringLiteral( "NULL" );
270 
271  switch ( value.type() )
272  {
273  case QVariant::Int:
274  case QVariant::LongLong:
275  case QVariant::Double:
276  return value.toString();
277 
278  case QVariant::Bool:
279  //SQLite has no boolean literals
280  return value.toBool() ? QStringLiteral( "1" ) : QStringLiteral( "0" );
281 
282  default:
283  case QVariant::String:
284  QString v = value.toString();
285  // https://www.sqlite.org/lang_expr.html :
286  // """A string constant is formed by enclosing the string in single quotes (').
287  // A single quote within the string can be encoded by putting two single quotes
288  // in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. """
289  return v.replace( '\'', QLatin1String( "''" ) ).prepend( '\'' ).append( '\'' );
290  }
291 }
292 
294 {
295  return QStringList() << QStringLiteral( "SpatialIndex" ) << QStringLiteral( "geom_cols_ref_sys" ) << QStringLiteral( "geometry_columns" )
296  << QStringLiteral( "geometry_columns_auth" ) << QStringLiteral( "views_geometry_columns" ) << QStringLiteral( "virts_geometry_columns" )
297  << QStringLiteral( "spatial_ref_sys" ) << QStringLiteral( "spatial_ref_sys_all" ) << QStringLiteral( "spatial_ref_sys_aux" )
298  << QStringLiteral( "sqlite_sequence" ) << QStringLiteral( "tableprefix_metadata" ) << QStringLiteral( "tableprefix_rasters" )
299  << QStringLiteral( "layer_params" ) << QStringLiteral( "layer_statistics" ) << QStringLiteral( "layer_sub_classes" )
300  << QStringLiteral( "layer_table_layout" ) << QStringLiteral( "pattern_bitmaps" ) << QStringLiteral( "symbol_bitmaps" )
301  << QStringLiteral( "project_defs" ) << QStringLiteral( "raster_pyramids" ) << QStringLiteral( "sqlite_stat1" ) << QStringLiteral( "sqlite_stat2" )
302  << QStringLiteral( "spatialite_history" ) << QStringLiteral( "geometry_columns_field_infos" ) << QStringLiteral( "geometry_columns_statistics" )
303  << QStringLiteral( "geometry_columns_time" ) << QStringLiteral( "sql_statements_log" ) << QStringLiteral( "vector_layers" )
304  << QStringLiteral( "vector_layers_auth" ) << QStringLiteral( "vector_layers_field_infos" ) << QStringLiteral( "vector_layers_statistics" )
305  << QStringLiteral( "views_geometry_columns_auth" ) << QStringLiteral( "views_geometry_columns_field_infos" )
306  << QStringLiteral( "views_geometry_columns_statistics" ) << QStringLiteral( "virts_geometry_columns_auth" )
307  << QStringLiteral( "virts_geometry_columns_field_infos" ) << QStringLiteral( "virts_geometry_columns_statistics" )
308  << QStringLiteral( "virts_layer_statistics" ) << QStringLiteral( "views_layer_statistics" )
309  << QStringLiteral( "ElementaryGeometries" );
310 }
311 
312 QString qgs_sqlite3_mprintf( const char *format, ... )
313 {
314  va_list ap;
315  va_start( ap, format );
316  char *c_str = sqlite3_vmprintf( format, ap );
317  va_end( ap );
318  QString res( QString::fromUtf8( c_str ) );
319  sqlite3_free( c_str );
320  return res;
321 }
void operator()(sqlite3_stmt *statement)
Finalizes an sqlite3 statement.
static QString quotedValue(const QVariant &value)
Returns a properly quoted and escaped version of value for use in SQL strings.
Unique pointer for sqlite3 prepared statements, which automatically finalizes the statement when the ...
int exec(const QString &sql, QString &errorMessage) const
Executes the sql command in the database.
static QString quotedIdentifier(const QString &identifier)
Returns a properly quoted version of identifier.
static QStringList systemTables()
Returns a string list of SQLite (and spatialite) system tables.
QByteArray columnAsBlob(int column) const
Returns the column value from the current statement row as raw byte array.
QString errorMessage() const
Returns the most recent error message encountered by the database.
static QSet< QString > uniqueFields(sqlite3 *connection, const QString &tableName, QString &errorMessage)
Returns a list of field names for connection and tableName having a UNIQUE constraint, fields that are part of a UNIQUE constraint that spans over multiple fields are not returned.
int step()
Steps to the next record in the statement, returning the sqlite3 result code.
static long long nextSequenceValue(sqlite3 *connection, const QString &tableName, QString errorMessage)
Increments and returns an SQLITE sequence of the table "sqlite_sequence" for tableName and returns it...
int columnCount() const
Gets the number of columns that this statement returns.
QString columnAsText(int column) const
Returns the column value from the current statement row as a string.
sqlite3_statement_unique_ptr prepare(const QString &sql, int &resultCode) const
Prepares a sql statement, returning the result.
int open(const QString &path)
Opens the database at the specified file path.
struct sqlite3 sqlite3
Unique pointer for sqlite3 databases, which automatically closes the database when the pointer goes o...
int open_v2(const QString &path, int flags, const char *zVfs)
Opens the database at the specified file path.
return uniqueFieldsResults
QString qgs_sqlite3_mprintf(const char *format,...)
Wraps sqlite3_mprintf() by automatically freeing the memory.
void operator()(sqlite3 *database)
Closes an sqlite database.
QString columnName(int column) const
Returns the name of column.
double columnAsDouble(int column) const
Gets column value from the current statement row as a double.
qlonglong columnAsInt64(int column) const
Gets column value from the current statement row as a long long integer (64 bits).
static QString quotedString(const QString &value)
Returns a quoted string value, surround by &#39; characters and with special characters correctly escaped...