QGIS API Documentation  3.8.0-Zanzibar (11aff65)
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 
25 {
26  sqlite3_close_v2( database );
27 }
28 
29 void QgsSqlite3StatementFinalizer::operator()( sqlite3_stmt *statement )
30 {
31  sqlite3_finalize( statement );
32 }
33 
35 {
36  return sqlite3_step( get() );
37 }
38 
39 QString sqlite3_statement_unique_ptr::columnName( int column ) const
40 {
41  return QString::fromUtf8( static_cast<const char *>( sqlite3_column_name( get(), column ) ) );
42 }
43 
45 {
46  return sqlite3_column_double( get(), column );
47 }
48 
50 {
51  return sqlite3_column_count( get() );
52 }
53 
54 QString sqlite3_statement_unique_ptr::columnAsText( int column ) const
55 {
56  return QString::fromUtf8( reinterpret_cast<const char *>( sqlite3_column_text( get(), column ) ) );
57 }
58 
59 qlonglong sqlite3_statement_unique_ptr::columnAsInt64( int column ) const
60 {
61  return sqlite3_column_int64( get(), column );
62 }
63 
64 int sqlite3_database_unique_ptr::open( const QString &path )
65 {
66  sqlite3 *database = nullptr;
67  int result = sqlite3_open( path.toUtf8(), &database );
68  reset( database );
69  return result;
70 }
71 
72 int sqlite3_database_unique_ptr::open_v2( const QString &path, int flags, const char *zVfs )
73 {
74  sqlite3 *database = nullptr;
75  int result = sqlite3_open_v2( path.toUtf8(), &database, flags, zVfs );
76  reset( database );
77  return result;
78 }
79 
81 {
82  return QString( sqlite3_errmsg( get() ) );
83 }
84 
85 sqlite3_statement_unique_ptr sqlite3_database_unique_ptr::prepare( const QString &sql, int &resultCode ) const
86 {
87  sqlite3_stmt *preparedStatement = nullptr;
88  const char *tail = nullptr;
89  resultCode = sqlite3_prepare( get(), sql.toUtf8(), sql.toUtf8().length(), &preparedStatement, &tail );
91  s.reset( preparedStatement );
92  return s;
93 }
94 
95 int sqlite3_database_unique_ptr::exec( const QString &sql, QString &errorMessage ) const
96 {
97  char *errMsg;
98 
99  int ret = sqlite3_exec( get(), sql.toUtf8(), nullptr, nullptr, &errMsg );
100 
101  if ( errMsg )
102  {
103  errorMessage = QString::fromUtf8( errMsg );
104  sqlite3_free( errMsg );
105  }
106 
107  return ret;
108 }
109 
110 QString QgsSqliteUtils::quotedString( const QString &value )
111 {
112  if ( value.isNull() )
113  return QStringLiteral( "NULL" );
114 
115  QString v = value;
116  v.replace( '\'', QLatin1String( "''" ) );
117  return v.prepend( '\'' ).append( '\'' );
118 }
119 
120 QString QgsSqliteUtils::quotedIdentifier( const QString &identifier )
121 {
122  QString id( identifier );
123  id.replace( '\"', QLatin1String( "\"\"" ) );
124  return id.prepend( '\"' ).append( '\"' );
125 }
126 
127 QString QgsSqliteUtils::quotedValue( const QVariant &value )
128 {
129  if ( value.isNull() )
130  return QStringLiteral( "NULL" );
131 
132  switch ( value.type() )
133  {
134  case QVariant::Int:
135  case QVariant::LongLong:
136  case QVariant::Double:
137  return value.toString();
138 
139  case QVariant::Bool:
140  //SQLite has no boolean literals
141  return value.toBool() ? QStringLiteral( "1" ) : QStringLiteral( "0" );
142 
143  default:
144  case QVariant::String:
145  QString v = value.toString();
146  // https://www.sqlite.org/lang_expr.html :
147  // """A string constant is formed by enclosing the string in single quotes (').
148  // A single quote within the string can be encoded by putting two single quotes
149  // in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. """
150  return v.replace( '\'', QLatin1String( "''" ) ).prepend( '\'' ).append( '\'' );
151  }
152 }
153 
155 {
156  return QStringList() << QStringLiteral( "SpatialIndex" ) << QStringLiteral( "geom_cols_ref_sys" ) << QStringLiteral( "geometry_columns" )
157  << QStringLiteral( "geometry_columns_auth" ) << QStringLiteral( "views_geometry_columns" ) << QStringLiteral( "virts_geometry_columns" )
158  << QStringLiteral( "spatial_ref_sys" ) << QStringLiteral( "spatial_ref_sys_all" ) << QStringLiteral( "spatial_ref_sys_aux" )
159  << QStringLiteral( "sqlite_sequence" ) << QStringLiteral( "tableprefix_metadata" ) << QStringLiteral( "tableprefix_rasters" )
160  << QStringLiteral( "layer_params" ) << QStringLiteral( "layer_statistics" ) << QStringLiteral( "layer_sub_classes" )
161  << QStringLiteral( "layer_table_layout" ) << QStringLiteral( "pattern_bitmaps" ) << QStringLiteral( "symbol_bitmaps" )
162  << QStringLiteral( "project_defs" ) << QStringLiteral( "raster_pyramids" ) << QStringLiteral( "sqlite_stat1" ) << QStringLiteral( "sqlite_stat2" )
163  << QStringLiteral( "spatialite_history" ) << QStringLiteral( "geometry_columns_field_infos" ) << QStringLiteral( "geometry_columns_statistics" )
164  << QStringLiteral( "geometry_columns_time" ) << QStringLiteral( "sql_statements_log" ) << QStringLiteral( "vector_layers" )
165  << QStringLiteral( "vector_layers_auth" ) << QStringLiteral( "vector_layers_field_infos" ) << QStringLiteral( "vector_layers_statistics" )
166  << QStringLiteral( "views_geometry_columns_auth" ) << QStringLiteral( "views_geometry_columns_field_infos" )
167  << QStringLiteral( "views_geometry_columns_statistics" ) << QStringLiteral( "virts_geometry_columns_auth" )
168  << QStringLiteral( "virts_geometry_columns_field_infos" ) << QStringLiteral( "virts_geometry_columns_statistics" )
169  << QStringLiteral( "virts_layer_statistics" ) << QStringLiteral( "views_layer_statistics" )
170  << QStringLiteral( "ElementaryGeometries" );
171 }
172 
173 QString QgsSqlite3Mprintf( const char *format, ... )
174 {
175  va_list ap;
176  va_start( ap, format );
177  char *c_str = sqlite3_vmprintf( format, ap );
178  va_end( ap );
179  QString res( QString::fromUtf8( c_str ) );
180  sqlite3_free( c_str );
181  return res;
182 }
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.
QString errorMessage() const
Returns the most recent error message encountered by the database.
int step()
Steps to the next record in the statement, returning the sqlite3 result code.
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
int open_v2(const QString &path, int flags, const char *zVfs)
Opens the database at the specified file path.
void operator()(sqlite3 *database)
Closes an sqlite database.
QString QgsSqlite3Mprintf(const char *format,...)
Wraps sqlite3_mprintf() by automatically freeing the memory.
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...