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