QGIS API Documentation  3.26.3-Buenos Aires (65e4edfdad)
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 
31 void QgsSqlite3Closer::operator()( sqlite3 *database ) const
32 {
33  sqlite3_close_v2( database );
34 }
35 
36 void QgsSqlite3StatementFinalizer::operator()( sqlite3_stmt *statement ) const
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  const 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  const 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  const 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  const 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  const 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  }
203  return uniqueFieldsResults;
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 }
sqlite3_database_unique_ptr::open
int open(const QString &path)
Opens the database at the specified file path.
Definition: qgssqliteutils.cpp:78
sqlite3_database_unique_ptr::prepare
sqlite3_statement_unique_ptr prepare(const QString &sql, int &resultCode) const
Prepares a sql statement, returning the result.
Definition: qgssqliteutils.cpp:99
sqlite3
struct sqlite3 sqlite3
Definition: qgscoordinatereferencesystem.h:61
QgsSqliteUtils::systemTables
static QStringList systemTables()
Returns a string list of SQLite (and spatialite) system tables.
Definition: qgssqliteutils.cpp:293
sqlite3_statement_unique_ptr::columnAsBlob
QByteArray columnAsBlob(int column) const
Returns the column value from the current statement row as raw byte array.
Definition: qgssqliteutils.cpp:66
QgsSqliteUtils::quotedString
static QString quotedString(const QString &value)
Returns a quoted string value, surround by ' characters and with special characters correctly escaped...
Definition: qgssqliteutils.cpp:249
QgsSqlite3Closer::operator()
void operator()(sqlite3 *database) const
Closes an sqlite database.
Definition: qgssqliteutils.cpp:31
sqlite3_statement_unique_ptr::columnAsInt64
qlonglong columnAsInt64(int column) const
Gets column value from the current statement row as a long long integer (64 bits).
Definition: qgssqliteutils.cpp:73
sqlite3_database_unique_ptr::errorMessage
QString errorMessage() const
Returns the most recent error message encountered by the database.
Definition: qgssqliteutils.cpp:94
qgs_sqlite3_mprintf
QString qgs_sqlite3_mprintf(const char *format,...)
Wraps sqlite3_mprintf() by automatically freeing the memory.
Definition: qgssqliteutils.cpp:312
sqlite3_statement_unique_ptr::step
int step()
Steps to the next record in the statement, returning the sqlite3 result code.
Definition: qgssqliteutils.cpp:41
QgsSqliteUtils::quotedValue
static QString quotedValue(const QVariant &value)
Returns a properly quoted and escaped version of value for use in SQL strings.
Definition: qgssqliteutils.cpp:266
QgsSqliteUtils::nextSequenceValue
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...
Definition: qgssqliteutils.cpp:206
sqlite3_statement_unique_ptr::columnCount
int columnCount() const
Gets the number of columns that this statement returns.
Definition: qgssqliteutils.cpp:56
QgsSqliteUtils::uniqueFields
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,...
Definition: qgssqliteutils.cpp:124
sqlite3_database_unique_ptr::open_v2
int open_v2(const QString &path, int flags, const char *zVfs)
Opens the database at the specified file path.
Definition: qgssqliteutils.cpp:86
sqlite3_statement_unique_ptr::columnAsText
QString columnAsText(int column) const
Returns the column value from the current statement row as a string.
Definition: qgssqliteutils.cpp:61
sqlite3_database_unique_ptr::exec
int exec(const QString &sql, QString &errorMessage) const
Executes the sql command in the database.
Definition: qgssqliteutils.cpp:109
sqlite3_statement_unique_ptr::columnName
QString columnName(int column) const
Returns the name of column.
Definition: qgssqliteutils.cpp:46
qgssqliteutils.h
QgsSqlite3StatementFinalizer::operator()
void operator()(sqlite3_stmt *statement) const
Finalizes an sqlite3 statement.
Definition: qgssqliteutils.cpp:36
sqlite3_statement_unique_ptr::columnAsDouble
double columnAsDouble(int column) const
Gets column value from the current statement row as a double.
Definition: qgssqliteutils.cpp:51
sqlite3_database_unique_ptr
Unique pointer for sqlite3 databases, which automatically closes the database when the pointer goes o...
Definition: qgssqliteutils.h:118
QgsSqliteUtils::quotedIdentifier
static QString quotedIdentifier(const QString &identifier)
Returns a properly quoted version of identifier.
Definition: qgssqliteutils.cpp:259
sqlite3_statement_unique_ptr
Unique pointer for sqlite3 prepared statements, which automatically finalizes the statement when the ...
Definition: qgssqliteutils.h:69