Skip to content

Commit a08e4de

Browse files
author
jef
committedMar 28, 2008
spit plugin fix
- quote values and identifiers properly - remove now unnecessary reserved word dialog - also consider primary key field and geometry field in double field check - allow rename of primary key field - remove disabled connect button (spit automatically connects on OK) - keep the window open window if there was an error - fixes #865 and #930 git-svn-id: http://svn.osgeo.org/qgis/trunk@8293 c8812cc2-4d05-0410-92ff-de0c093fc19c
·
ltr-3_40Before-merge-new_vector_api
1 parent a0c1772 commit a08e4de

11 files changed

+457
-792
lines changed
 

‎src/plugins/spit/CMakeLists.txt

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,6 @@ SET (SPIT_SRCS
77
qgsconnectiondialog.cpp
88
qgspgutil.cpp
99
qgsshapefile.cpp
10-
qgseditreservedwordsdialog.cpp
1110
)
1211

1312
SET (SPIT_PLUGIN_SRCS
@@ -23,14 +22,12 @@ SET (SPIT_EXE_SRCS
2322
SET (SPIT_UIS
2423
qgsconnectiondialogbase.ui
2524
qgsspitbase.ui
26-
qgseditreservedwordsbase.ui
2725
../../ui/qgsmessageviewer.ui
2826
)
2927

3028
SET (SPIT_EXE_MOC_HDRS
3129
qgsspit.h
3230
qgsshapefile.h
33-
qgseditreservedwordsdialog.h
3431
qgsconnectiondialog.h
3532
)
3633

‎src/plugins/spit/qgseditreservedwordsbase.ui

Lines changed: 0 additions & 135 deletions
This file was deleted.

‎src/plugins/spit/qgseditreservedwordsdialog.cpp

Lines changed: 0 additions & 116 deletions
This file was deleted.

‎src/plugins/spit/qgseditreservedwordsdialog.h

Lines changed: 0 additions & 48 deletions
This file was deleted.

‎src/plugins/spit/qgspgutil.cpp

Lines changed: 15 additions & 109 deletions
Original file line numberDiff line numberDiff line change
@@ -24,22 +24,15 @@ QgsPgUtil * QgsPgUtil::instance()
2424
}
2525
return mInstance;
2626
}
27+
2728
QgsPgUtil::QgsPgUtil()
2829
{
29-
// load the reserved word map
30-
initReservedWords();
3130
}
31+
3232
QgsPgUtil::~QgsPgUtil()
3333
{
3434
}
35-
bool QgsPgUtil::isReserved(QString word)
36-
{
37-
// uppercase the word before testing it since all our reserved words are
38-
// stored in uppercase
39-
40-
QStringList::iterator it = mReservedWords.find(word.upper());
41-
return (it != mReservedWords.end());
42-
}
35+
4336
void QgsPgUtil::setConnection(PGconn *con)
4437
{
4538
mPgConnection = con;
@@ -48,106 +41,19 @@ PGconn *QgsPgUtil::connection()
4841
{
4942
return mPgConnection;
5043
}
51-
const QStringList & QgsPgUtil::reservedWords()
44+
45+
QString QgsPgUtil::quotedIdentifier( QString ident )
5246
{
53-
return mReservedWords;
47+
ident.replace('"', "\"\"");
48+
return ident.prepend("\"").append("\"");
5449
}
55-
void QgsPgUtil::initReservedWords()
50+
51+
QString QgsPgUtil::quotedValue( QString value )
5652
{
57-
// create the reserved word list by loading
58-
// the words into a QStringList. We code them here
59-
// for now rather than deal with the complexities
60-
// of finding and loading from a text file
61-
// in the install path
62-
mReservedWords << "ALL"
63-
<< "ANALYSE"
64-
<< "ANALYZE"
65-
<< "AND"
66-
<< "ANY"
67-
<< "ARRAY"
68-
<< "AS"
69-
<< "ASC"
70-
<< "AUTHORIZATION"
71-
<< "BETWEEN"
72-
<< "BINARY"
73-
<< "BOTH"
74-
<< "CASE"
75-
<< "CAST"
76-
<< "CHECK"
77-
<< "COLLATE"
78-
<< "COLUMN"
79-
<< "CONSTRAINT"
80-
<< "CREATE"
81-
<< "CROSS"
82-
<< "CURRENT_DATE"
83-
<< "CURRENT_TIME"
84-
<< "CURRENT_TIMESTAMP"
85-
<< "CURRENT_USER"
86-
<< "DEFAULT"
87-
<< "DEFERRABLE"
88-
<< "DESC"
89-
<< "DISTINCT"
90-
<< "DO"
91-
<< "ELSE"
92-
<< "END"
93-
<< "EXCEPT"
94-
<< "FALSE"
95-
<< "FOR"
96-
<< "FOREIGN"
97-
<< "FREEZE"
98-
<< "FROM"
99-
<< "FULL"
100-
<< "GRANT"
101-
<< "GROUP"
102-
<< "HAVING"
103-
<< "ILIKE"
104-
<< "IN"
105-
<< "INITIALLY"
106-
<< "INNER"
107-
<< "INTERSECT"
108-
<< "INTO"
109-
<< "IS"
110-
<< "ISNULL"
111-
<< "JOIN"
112-
<< "LEADING"
113-
<< "LEFT"
114-
<< "LIKE"
115-
<< "LIMIT"
116-
<< "LOCALTIME"
117-
<< "LOCALTIMESTAMP"
118-
<< "NAMES"
119-
<< "NATURAL"
120-
<< "NEW"
121-
<< "NOT"
122-
<< "NOTNULL"
123-
<< "NULL"
124-
<< "OFF"
125-
<< "OFFSET"
126-
<< "OLD"
127-
<< "ON"
128-
<< "ONLY"
129-
<< "OR"
130-
<< "ORDER"
131-
<< "OUTER"
132-
<< "OVERLAPS"
133-
<< "PLACING"
134-
<< "PRIMARY"
135-
<< "REFERENCES"
136-
<< "RIGHT"
137-
<< "SELECT"
138-
<< "SESSION_USER"
139-
<< "SIMILAR"
140-
<< "SOME"
141-
<< "TABLE"
142-
<< "THEN"
143-
<< "TO"
144-
<< "TRAILING"
145-
<< "TRUE"
146-
<< "UNION"
147-
<< "UNIQUE"
148-
<< "USER"
149-
<< "USING"
150-
<< "VERBOSE"
151-
<< "WHEN"
152-
<< "WHERE";
53+
if( value.isNull() )
54+
return "NULL";
55+
56+
// FIXME: use PQescapeStringConn
57+
value.replace("'", "''");
58+
return value.prepend("'").append("'");
15359
}

‎src/plugins/spit/qgspgutil.h

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -33,12 +33,7 @@ class QgsPgUtil
3333
//! Instance function to return a pointer to the one and
3434
//only QgsPgUtil object (QgsPgUtil is a Singleton)
3535
static QgsPgUtil* instance();
36-
/*! Checks to see if word is a PG reserved word.
37-
* The comparison is case-insensitive.
38-
* @param word Word to check
39-
* @return True if word is a PG reserved word
40-
*/
41-
bool isReserved(QString word);
36+
4237
/*!
4338
* Set the connection to be used in database operations
4439
* @param con Pointer to an active PostgreSQL connection
@@ -49,10 +44,15 @@ class QgsPgUtil
4944
* @return Pointer to the PostgreSQL connection object
5045
*/
5146
PGconn *connection();
52-
/*!
53-
* Get the reserved word list
47+
48+
/** Double quote a PostgreSQL identifier for placement in a SQL string.
49+
*/
50+
static QString quotedIdentifier( QString ident );
51+
52+
/** Quote a value for placement in a SQL string.
5453
*/
55-
const QStringList & reservedWords();
54+
static QString quotedValue( QString value );
55+
5656
protected:
5757
//! Protected constructor
5858
QgsPgUtil();

‎src/plugins/spit/qgsshapefile.cpp

Lines changed: 54 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,8 @@
3636
#include "qgis.h"
3737
#include "qgslogger.h"
3838

39+
#include "qgspgutil.h"
40+
3941
// for htonl
4042
#ifdef WIN32
4143
#include <winsock.h>
@@ -60,7 +62,7 @@ QgsShapeFile::QgsShapeFile(QString name, QString encoding){
6062
// init the geometry types
6163
geometries << "NULL" << "POINT" << "LINESTRING" << "POLYGON" << "MULTPOINT"
6264
<< "MULTILINESTRING" << "MULTIPOLYGON" << "GEOMETRYCOLLECTION";
63-
65+
6466
codec = QTextCodec::codecForName(encoding.toLocal8Bit().data());
6567
if (!codec)
6668
codec = QTextCodec::codecForLocale();
@@ -219,7 +221,7 @@ QString QgsShapeFile::getFeatureClass(){
219221
int numFields = OGR_F_GetFieldCount(feat);
220222
for(int n=0; n<numFields; n++)
221223
{
222-
QString s = codec->toUnicode(OGR_Fld_GetNameRef(OGR_F_GetFieldDefnRef(feat,n)));
224+
QString s = codec->toUnicode(OGR_Fld_GetNameRef(OGR_F_GetFieldDefnRef(feat,n)));
223225
column_names.push_back(s);
224226
}
225227

@@ -263,41 +265,30 @@ void QgsShapeFile::setColumnNames(QStringList columns)
263265
}
264266
}
265267

266-
bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
268+
bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString primary_key, QString geom_col,
267269
QString srid, PGconn * conn, QProgressDialog& pro, bool &fin,
268270
QString& errorText)
269271
{
270272
connect(&pro, SIGNAL(canceled()), this, SLOT(cancelImport()));
271273
import_canceled = false;
272274
bool result = true;
273-
// Mangle the table name to make it PG compliant by replacing spaces with
274-
// underscores
275-
table_name = table_name.replace(" ","_");
276-
277-
QString query = "CREATE TABLE "+schema+"."+table_name+"(gid int4 PRIMARY KEY, ";
278275

279-
for(uint n=0; n<column_names.size() && result; n++){
280-
if(!column_names[n][0].isLetter())
281-
result = false;
276+
QString query = QString("CREATE TABLE %1.%2(%3 int4 PRIMARY KEY")
277+
.arg( QgsPgUtil::quotedIdentifier(schema) )
278+
.arg( QgsPgUtil::quotedIdentifier(table_name) )
279+
.arg( QgsPgUtil::quotedIdentifier(primary_key) );
282280

283-
char * esc_str = new char[column_names[n].length()*2+1];
284-
285-
PQescapeString(esc_str, (const char *)column_names[n].lower(), column_names[n].length());
286-
QgsDebugMsg("Escaped " + column_names[n] + " to " + QString(esc_str));
287-
query += esc_str;
288-
query += " " + column_types[n];
289-
290-
if(n<column_names.size()-1)
291-
{
292-
query += ", ";
293-
}
294-
delete[] esc_str;
281+
for(uint n=0; n<column_names.size() && result; n++)
282+
{
283+
query += QString(",%1 %2")
284+
.arg( QgsPgUtil::quotedIdentifier(column_names[n]) )
285+
.arg( column_types[n] );
295286
}
296287
query += " )";
297288

298289
QgsDebugMsg("Query string is: " + query);
299290

300-
PGresult *res = PQexec(conn, (const char *)query);
291+
PGresult *res = PQexec(conn, query.toUtf8() );
301292

302293
if(PQresultStatus(res)!=PGRES_COMMAND_OK){
303294
// flag error and send query and error message to stdout on debug
@@ -312,10 +303,14 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
312303
PQclear(res);
313304
}
314305

315-
query = "SELECT AddGeometryColumn(\'" + schema + "\', \'" + table_name + "\', \'"+
316-
geom_col + "\', " + srid + ", \'" + geom_type + "\', 2)";
306+
query = QString("SELECT AddGeometryColumn(%1,%2,%3,%4,%5,2)")
307+
.arg( QgsPgUtil::quotedValue( schema ) )
308+
.arg( QgsPgUtil::quotedValue( table_name ) )
309+
.arg( QgsPgUtil::quotedValue( geom_col ) )
310+
.arg( srid )
311+
.arg( QgsPgUtil::quotedValue( geom_type ) );
317312

318-
res = PQexec(conn, (const char *)query);
313+
res = PQexec(conn, query.toUtf8() );
319314

320315
if(PQresultStatus(res)!=PGRES_TUPLES_OK){
321316
errorText += tr("The database gave an error while executing this SQL:") + "\n";
@@ -332,15 +327,16 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
332327

333328
if(isMulti)
334329
{
335-
query = QString("select constraint_name from information_schema.table_constraints where table_schema='%1' and table_name='%2' and constraint_name in ('$2','enforce_geotype_the_geom')")
336-
.arg( schema ).arg( table_name );
330+
query = QString("select constraint_name from information_schema.table_constraints where table_schema=%1 and table_name=%2 and constraint_name in ('$2','enforce_geotype_the_geom')")
331+
.arg( QgsPgUtil::quotedValue(schema) )
332+
.arg( QgsPgUtil::quotedValue(table_name) );
337333

338334
QStringList constraints;
339-
res = PQexec( conn, query );
335+
res = PQexec(conn, query.toUtf8() );
340336
if( PQresultStatus( res ) == PGRES_TUPLES_OK )
341337
{
342338
for(int i=0; i<PQntuples(res); i++)
343-
constraints.append( PQgetvalue(res, i, 0) );
339+
constraints.append( PQgetvalue(res, i, 0) );
344340
}
345341
PQclear(res);
346342

@@ -350,9 +346,11 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
350346
// convert the geometries to the same type or allow
351347
// multiple types in the check constraint. For now, we
352348
// just drop the constraint...
353-
query = "alter table " + table_name + " drop constraint \"" + constraints[0] + "\"";
349+
query = QString("alter table %1 drop constraint %2")
350+
.arg( QgsPgUtil::quotedIdentifier(table_name) )
351+
.arg( QgsPgUtil::quotedIdentifier(constraints[0]) );
354352

355-
res = PQexec(conn, (const char*)query);
353+
res = PQexec(conn, query.toUtf8() );
356354
if(PQresultStatus(res)!=PGRES_COMMAND_OK) {
357355
errorText += tr("The database gave an error while executing this SQL:") + "\n";
358356
errorText += query + '\n';
@@ -368,8 +366,10 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
368366
}
369367

370368
//adding the data into the table
371-
for(int m=0;m<features && result; m++){
372-
if(import_canceled){
369+
for(int m=0; m<features && result; m++)
370+
{
371+
if(import_canceled)
372+
{
373373
fin = true;
374374
break;
375375
}
@@ -378,8 +378,10 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
378378
if(feat){
379379
OGRGeometryH geom = OGR_F_GetGeometryRef(feat);
380380
if(geom){
381-
query = "INSERT INTO \"" + schema + "\".\"" + table_name + "\"" +
382-
QString(" VALUES( %1, ").arg(m);
381+
query = QString("INSERT INTO %1.%2 VALUES(%3")
382+
.arg( QgsPgUtil::quotedIdentifier(schema) )
383+
.arg( QgsPgUtil::quotedIdentifier(table_name) )
384+
.arg( m );
383385

384386
int num = OGR_G_WkbSize(geom);
385387
char * geo_temp = new char[num*3];
@@ -390,38 +392,28 @@ bool QgsShapeFile::insertLayer(QString dbname, QString schema, QString geom_col,
390392
OGR_G_ExportToWkt(geom,&geo_temp);
391393
QString geometry(geo_temp);
392394

393-
QString quotes;
394-
for(uint n=0; n<column_types.size(); n++){
395-
bool numericType(false);
396-
if(column_types[n] == "int" || column_types[n] == "float")
397-
{
398-
quotes = " ";
399-
numericType = true;
400-
}
395+
for(uint n=0; n<column_types.size(); n++) {
396+
QString val;
397+
398+
// FIXME: OGR_F_GetFieldAsString returns junk when called with a 8.255 float field
399+
if( column_types[n] == "float" )
400+
val = QString::number( OGR_F_GetFieldAsDouble(feat,n) );
401401
else
402-
quotes = "\'";
403-
query += quotes;
402+
val = codec->toUnicode(OGR_F_GetFieldAsString(feat,n) );
404403

405-
// escape the string value and cope with blank data
406-
QString val = codec->toUnicode(OGR_F_GetFieldAsString(feat,n));
407-
if (val.isEmpty() && numericType)
408-
{
404+
if( val.isEmpty() )
409405
val = "NULL";
410-
}
411-
val.replace("'", "''");
412-
//char * esc_str = new char[val.length()*2+1];
413-
//PQescapeString(esc_str, (const char *)val.lower().utf8(), val.length());
414-
415-
// add escaped value to the query
416-
query += val; //esc_str;
417-
query += QString(quotes + ", ");
406+
else
407+
val = QgsPgUtil::quotedValue( val );
418408

419-
//delete[] esc_str;
409+
query += "," + val;
420410
}
421-
query += QString("GeometryFromText(\'")+geometry+QString("\', ")+srid+QString("))");
411+
query += QString(",GeometryFromText(%1,%2))")
412+
.arg( QgsPgUtil::quotedValue( geometry ) )
413+
.arg( srid );
422414

423415
if(result)
424-
res = PQexec(conn, (const char *)query.utf8());
416+
res = PQexec(conn, query.utf8() );
425417

426418
if(PQresultStatus(res)!=PGRES_COMMAND_OK){
427419
// flag error and send query and error message to stdout on debug

‎src/plugins/spit/qgsshapefile.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ class QgsShapeFile : public QObject
4545
~QgsShapeFile();
4646
int getFeatureCount();
4747
QString getFeatureClass();
48-
bool insertLayer(QString dbname, QString schema, QString geom_col,
48+
bool insertLayer(QString dbname, QString schema, QString primary_key, QString geom_col,
4949
QString srid, PGconn * conn, QProgressDialog& pro,
5050
bool &fin, QString& errorText);
5151

‎src/plugins/spit/qgsspit.cpp

Lines changed: 181 additions & 235 deletions
Large diffs are not rendered by default.

‎src/plugins/spit/qgsspit.h

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -72,8 +72,6 @@ class QgsSpit : public QDialog, private Ui::QgsSpitBase
7272
void updateSchema();
7373
//! Import shapefiles into PostgreSQL
7474
void import();
75-
//! Edit the column names for a shapefile in the queue
76-
void editColumns( int, int, int, const QPoint & );
7775
//! Edit import properties of a shapefile in the queue
7876
void editShapefile( int, int, int, const QPoint & );
7977

‎src/plugins/spit/qgsspitbase.ui

Lines changed: 197 additions & 72 deletions
Large diffs are not rendered by default.

0 commit comments

Comments
 (0)
Please sign in to comment.