Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
do a case-insensitive check looking for SL tables into geometry_columns
  • Loading branch information
brushtyler committed Jul 15, 2012
1 parent 5131eb5 commit 2278f40
Show file tree
Hide file tree
Showing 3 changed files with 24 additions and 19 deletions.
24 changes: 12 additions & 12 deletions python/plugins/db_manager/db_plugins/spatialite/connector.py
Expand Up @@ -208,7 +208,7 @@ def getVectorTables(self, schema=None):

# get geometry info from geometry_columns if exists
sql = u"""SELECT m.name, m.type = 'view', g.f_table_name, g.f_geometry_column, g.type, g.coord_dimension, g.srid
FROM sqlite_master AS m JOIN geometry_columns AS g ON lower(m.name) = lower(g.f_table_name)
FROM sqlite_master AS m JOIN geometry_columns AS g ON upper(m.name) = upper(g.f_table_name)
WHERE m.type in ('table', 'view')
ORDER BY m.name, g.f_geometry_column"""

Expand Down Expand Up @@ -243,9 +243,9 @@ def getRasterTables(self, schema=None):

# get geometry info from geometry_columns if exists
sql = u"""SELECT r.table_name||'_rasters', m.type = 'view', r.table_name, r.geometry_column, g.srid
FROM sqlite_master AS m JOIN geometry_columns AS g ON lower(m.name) = lower(g.f_table_name)
JOIN layer_params AS r ON REPLACE(m.name, '_metadata', '') = r.table_name
WHERE m.type in ('table', 'view') AND m.name = r.table_name||'_metadata'
FROM sqlite_master AS m JOIN geometry_columns AS g ON upper(m.name) = upper(g.f_table_name)
JOIN layer_params AS r ON upper(REPLACE(m.name, '_metadata', '')) = upper(r.table_name)
WHERE m.type in ('table', 'view') AND upper(m.name) = upper(r.table_name||'_metadata')
ORDER BY r.table_name"""

self._execute(c, sql)
Expand Down Expand Up @@ -298,7 +298,7 @@ def getTableConstraints(self, table):
def getTableTriggers(self, table):
c = self._get_cursor()
schema, tablename = self.getSchemaTableName(table)
sql = u"SELECT name, sql FROM sqlite_master WHERE lower(tbl_name) = lower(%s) AND type = 'trigger'" % (self.quoteString(tablename))
sql = u"SELECT name, sql FROM sqlite_master WHERE tbl_name = %s AND type = 'trigger'" % (self.quoteString(tablename))
self._execute(c, sql)
return c.fetchall()

Expand Down Expand Up @@ -339,7 +339,7 @@ def getSpatialRefInfo(self, srid):
def isVectorTable(self, table):
if self.has_geometry_columns:
schema, tablename = self.getSchemaTableName(table)
sql = u"SELECT count(*) FROM geometry_columns WHERE f_table_name = %s" % self.quoteString(tablename)
sql = u"SELECT count(*) FROM geometry_columns WHERE upper(f_table_name) = upper(%s)" % self.quoteString(tablename)
c = self._execute(None, sql)
ret = c.fetchone()
return res != None and ret[0] > 0
Expand All @@ -353,8 +353,8 @@ def isRasterTable(self, table):

sql = u"""SELECT count(*)
FROM layer_params AS r JOIN geometry_columns AS g
ON r.table_name||'_metadata' = g.f_table_name
WHERE r.table_name = REPLACE(%s, '_rasters', '')""" % self.quoteString(tablename)
ON upper(r.table_name||'_metadata') = upper(g.f_table_name)
WHERE upper(r.table_name) = upper(REPLACE(%s, '_rasters', ''))""" % self.quoteString(tablename)
c = self._execute(None, sql)
ret = c.fetchone()
return res != None and ret[0] > 0
Expand Down Expand Up @@ -388,7 +388,7 @@ def deleteTable(self, table):
sql = u"DROP TABLE %s" % self.quoteId(table)
self._execute(c, sql)
schema, tablename = self.getSchemaTableName(table)
sql = u"DELETE FROM geometry_columns WHERE lower(f_table_name) = lower(%s)" % self.quoteString(tablename)
sql = u"DELETE FROM geometry_columns WHERE upper(f_table_name) = upper(%s)" % self.quoteString(tablename)
self._execute(c, sql)
self._commit()

Expand Down Expand Up @@ -417,7 +417,7 @@ def renameTable(self, table, new_table):

# update geometry_columns
if self.has_geometry_columns:
sql = u"UPDATE geometry_columns SET f_table_name=%s WHERE f_table_name=%s" % (self.quoteString(new_table), self.quoteString(tablename))
sql = u"UPDATE geometry_columns SET f_table_name = %s WHERE upper(f_table_name) = upper(%s)" % (self.quoteString(new_table), self.quoteString(tablename))
self._execute(c, sql)

self._commit()
Expand Down Expand Up @@ -480,7 +480,7 @@ def setColumnNull(self, table, column, is_null):
def isGeometryColumn(self, table, column):
c = self._get_cursor()
schema, tablename = self.getSchemaTableName(table)
sql = u"SELECT count(*) > 0 FROM geometry_columns WHERE lower(f_table_name)=lower(%s) AND lower(f_geometry_column)=lower(%s)" % (self.quoteString(tablename), self.quoteString(column))
sql = u"SELECT count(*) > 0 FROM geometry_columns WHERE upper(f_table_name) = upper(%s) AND upper(f_geometry_column) = upper(%s)" % (self.quoteString(tablename), self.quoteString(column))
self._execute(c, sql)
return c.fetchone()[0] == 't'

Expand Down Expand Up @@ -547,7 +547,7 @@ def hasSpatialIndex(self, table, geom_column='geometry'):
return False
c = self._get_cursor()
schema, tablename = self.getSchemaTableName(table)
sql = u"SELECT spatial_index_enabled FROM geometry_columns WHERE f_table_name = %s AND f_geometry_column = %s" % (self.quoteString(tablename), self.quoteString(geom_column))
sql = u"SELECT spatial_index_enabled FROM geometry_columns WHERE upper(f_table_name) = upper(%s) AND upper(f_geometry_column) = upper(%s)" % (self.quoteString(tablename), self.quoteString(geom_column))
self._execute(c, sql)
row = c.fetchone()
return row != None and row[0] == 1
Expand Down
3 changes: 3 additions & 0 deletions python/plugins/db_manager/db_plugins/spatialite/plugin.py
Expand Up @@ -165,6 +165,9 @@ class SLVectorTable(SLTable, VectorTable):
def __init__(self, row, db, schema=None):
SLTable.__init__(self, row[:-5], db, schema)
VectorTable.__init__(self, db, schema)
# SpatiaLite does case-insensitive checks for table names, but the
# SL provider didn't do the same in QGis < 1.9, so self.geomTableName
# stores the table name like stored in the geometry_columns table
self.geomTableName, self.geomColumn, self.geomType, self.geomDim, self.srid = row[-5:]

def uri(self):
Expand Down
16 changes: 9 additions & 7 deletions src/providers/spatialite/qgsspatialiteprovider.cpp
Expand Up @@ -202,7 +202,7 @@ QgsSpatiaLiteProvider::createEmptyLayer(
if ( ret != SQLITE_OK )
throw SLException( errMsg );

sql = QString( "DELETE FROM geometry_columns WHERE f_table_name = %1" )
sql = QString( "DELETE FROM geometry_columns WHERE upper(f_table_name) = upper(%1)" )
.arg( quotedValue( tableName ) );

ret = sqlite3_exec( sqliteHandle, sql.toUtf8().constData(), NULL, NULL, &errMsg );
Expand Down Expand Up @@ -381,7 +381,9 @@ QgsSpatiaLiteProvider::createEmptyLayer(

flist.append( fld );
if ( oldToNewAttrIdxMap )
oldToNewAttrIdxMap->insert( fldIt.key(), offset++ );
oldToNewAttrIdxMap->insert( fldIt.key(), offset );

offset++;
}

if ( !provider->addAttributes( flist ) )
Expand Down Expand Up @@ -4292,7 +4294,7 @@ bool QgsSpatiaLiteProvider::checkLayerType()
sql = QString( "SELECT read_only FROM geometry_columns "
"LEFT JOIN geometry_columns_auth "
"USING (f_table_name, f_geometry_column) "
"WHERE f_table_name=%1 and f_geometry_column=%2" )
"WHERE upper(f_table_name) = upper(%1) and upper(f_geometry_column) = upper(%2)" )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( mGeometryColumn ) );

Expand All @@ -4302,7 +4304,7 @@ bool QgsSpatiaLiteProvider::checkLayerType()
if ( errMsg && strcmp( errMsg, "no such table: geometry_columns_auth" ) == 0 )
{
sqlite3_free( errMsg );
sql = QString( "SELECT 0 FROM geometry_columns WHERE f_table_name=%1 and f_geometry_column=%2" )
sql = QString( "SELECT 0 FROM geometry_columns WHERE upper(f_table_name) = upper(%1) and upper(f_geometry_column) = upper(%2)" )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( mGeometryColumn ) );
ret = sqlite3_get_table( sqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
Expand Down Expand Up @@ -4413,7 +4415,7 @@ bool QgsSpatiaLiteProvider::getTableGeometryDetails()
mIndexGeometry = mGeometryColumn;

QString sql = QString( "SELECT type, srid, spatial_index_enabled, coord_dimension FROM geometry_columns"
" WHERE f_table_name=%1 and f_geometry_column=%2" ).arg( quotedValue( mTableName ) ).
" WHERE upper(f_table_name) = upper(%1) and upper(f_geometry_column) = upper(%2)" ).arg( quotedValue( mTableName ) ).
arg( quotedValue( mGeometryColumn ) );

ret = sqlite3_get_table( sqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
Expand Down Expand Up @@ -4511,7 +4513,7 @@ bool QgsSpatiaLiteProvider::getViewGeometryDetails()
QString sql = QString( "SELECT type, srid, spatial_index_enabled, f_table_name, f_geometry_column "
" FROM views_geometry_columns"
" JOIN geometry_columns USING (f_table_name, f_geometry_column)"
" WHERE view_name=%1 and view_geometry=%2" ).arg( quotedValue( mTableName ) ).
" WHERE upper(view_name) = upper(%1) and upper(view_geometry) = upper(%2)" ).arg( quotedValue( mTableName ) ).
arg( quotedValue( mGeometryColumn ) );

ret = sqlite3_get_table( sqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
Expand Down Expand Up @@ -5039,7 +5041,7 @@ QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QS
}

// remove table from geometry columns
sql = QString( "DELETE FROM geometry_columns WHERE f_table_name = %1" )
sql = QString( "DELETE FROM geometry_columns WHERE upper(f_table_name) = upper(%1)" )
.arg( QgsSpatiaLiteProvider::quotedValue( tableName ) );
ret = sqlite3_exec( sqlite_handle, sql.toUtf8().constData(), NULL, NULL, NULL );
if ( ret != SQLITE_OK )
Expand Down

0 comments on commit 2278f40

Please sign in to comment.