OGRFeature *OGRSQLiteViewLayer::GetFeature( long nFeatureId )
{
if (HasLayerDefnError())
return NULL;
/* -------------------------------------------------------------------- */
/* If we don't have an explicit FID column, just read through */
/* the result set iteratively to find our target. */
/* -------------------------------------------------------------------- */
if( pszFIDColumn == NULL )
return OGRSQLiteLayer::GetFeature( nFeatureId );
/* -------------------------------------------------------------------- */
/* Setup explicit query statement to fetch the record we want. */
/* -------------------------------------------------------------------- */
CPLString osSQL;
int rc;
ClearStatement();
iNextShapeId = nFeatureId;
osSQL.Printf( "SELECT \"%s\", * FROM '%s' WHERE \"%s\" = %d",
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
pszEscapedTableName,
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
(int) nFeatureId );
CPLDebug( "OGR_SQLITE", "exec(%s)", osSQL.c_str() );
rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
&hStmt, NULL );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In GetFeature(): sqlite3_prepare(%s):\n %s",
osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
return NULL;
}
/* -------------------------------------------------------------------- */
/* Get the feature if possible. */
/* -------------------------------------------------------------------- */
OGRFeature *poFeature = NULL;
poFeature = GetNextRawFeature();
ResetReading();
return poFeature;
}
OGRErr OGRSQLiteViewLayer::ResetStatement()
{
int rc;
CPLString osSQL;
ClearStatement();
iNextShapeId = 0;
osSQL.Printf( "SELECT \"%s\", * FROM '%s' %s",
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
pszEscapedTableName,
osWHERE.c_str() );
rc = sqlite3_prepare( poDS->GetDB(), osSQL, osSQL.size(),
&hStmt, NULL );
if( rc == SQLITE_OK )
{
return OGRERR_NONE;
}
else
{
CPLError( CE_Failure, CPLE_AppDefined,
"In ResetStatement(): sqlite3_prepare(%s):\n %s",
osSQL.c_str(), sqlite3_errmsg(poDS->GetDB()) );
hStmt = NULL;
return OGRERR_FAILURE;
}
}
static
void OGR2SQLITE_ogr_datasource_load_layers(sqlite3_context* pContext,
int argc, sqlite3_value** argv)
{
sqlite3* hDB = (sqlite3*) sqlite3_user_data(pContext);
if( (argc < 1 || argc > 3) || sqlite3_value_type (argv[0]) != SQLITE_TEXT )
{
sqlite3_result_int (pContext, 0);
return;
}
const char* pszDataSource = (const char*) sqlite3_value_text(argv[0]);
int bUpdate = FALSE;
if( argc >= 2 )
{
if( sqlite3_value_type(argv[1]) != SQLITE_INTEGER )
{
sqlite3_result_int (pContext, 0);
return;
}
bUpdate = sqlite3_value_int(argv[1]);
}
const char* pszPrefix = NULL;
if( argc >= 3 )
{
if( sqlite3_value_type(argv[2]) != SQLITE_TEXT )
{
sqlite3_result_int (pContext, 0);
return;
}
pszPrefix = (const char*) sqlite3_value_text(argv[2]);
}
OGRDataSource* poDS = (OGRDataSource*)OGROpenShared(pszDataSource, bUpdate, NULL);
if( poDS == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined, "Cannot open %s", pszDataSource);
sqlite3_result_int (pContext, 0);
return;
}
CPLString osEscapedDataSource = OGRSQLiteEscape(pszDataSource);
for(int i=0; i<poDS->GetLayerCount(); i++)
{
const char* pszLayerName = poDS->GetLayer(i)->GetName();
CPLString osEscapedLayerName = OGRSQLiteEscape(pszLayerName);
CPLString osTableName;
if( pszPrefix != NULL )
{
osTableName = pszPrefix;
osTableName += "_";
osTableName += OGRSQLiteEscapeName(pszLayerName);
}
else
{
osTableName = OGRSQLiteEscapeName(pszLayerName);
}
char* pszErrMsg = NULL;
if( sqlite3_exec(hDB, CPLSPrintf(
"CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR('%s', %d, '%s')",
osTableName.c_str(),
osEscapedDataSource.c_str(),
bUpdate,
osEscapedLayerName.c_str()),
NULL, NULL, &pszErrMsg) != SQLITE_OK )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot create table \"%s\" : %s",
osTableName.c_str(), pszErrMsg);
sqlite3_free(pszErrMsg);
}
}
poDS->Release();
sqlite3_result_int (pContext, 1);
}
OGRLayer * OGRSQLiteExecuteSQL( GDALDataset* poDS,
const char *pszStatement,
OGRGeometry *poSpatialFilter,
CPL_UNUSED const char *pszDialect )
{
char* pszTmpDBName = (char*) CPLMalloc(256);
snprintf(pszTmpDBName, 256, "/vsimem/ogr2sqlite/temp_%p.db", pszTmpDBName);
OGRSQLiteDataSource* poSQLiteDS = NULL;
int nRet;
int bSpatialiteDB = FALSE;
CPLString osOldVal;
const char* pszOldVal = CPLGetConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", NULL);
if( pszOldVal != NULL )
{
osOldVal = pszOldVal;
pszOldVal = osOldVal.c_str();
}
/* -------------------------------------------------------------------- */
/* Create in-memory sqlite/spatialite DB */
/* -------------------------------------------------------------------- */
#ifdef HAVE_SPATIALITE
/* -------------------------------------------------------------------- */
/* Creating an empty SpatiaLite DB (with spatial_ref_sys populated */
/* has a significant cost. So at the first attempt, let's make */
/* one and cache it for later use. */
/* -------------------------------------------------------------------- */
#if 1
static size_t nEmptyDBSize = 0;
static GByte* pabyEmptyDB = NULL;
{
static CPLMutex* hMutex = NULL;
CPLMutexHolder oMutexHolder(&hMutex);
static int bTried = FALSE;
if( !bTried &&
CPLTestBool(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) )
{
bTried = TRUE;
char* pszCachedFilename = (char*) CPLMalloc(256);
snprintf(pszCachedFilename, 256, "/vsimem/ogr2sqlite/reference_%p.db",
pszCachedFilename);
char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
OGRSQLiteDataSource* poCachedDS = new OGRSQLiteDataSource();
nRet = poCachedDS->Create( pszCachedFilename, papszOptions );
CSLDestroy(papszOptions);
papszOptions = NULL;
delete poCachedDS;
if( nRet )
{
/* Note: the reference file keeps the ownership of the data, so that */
/* it gets released with VSICleanupFileManager() */
vsi_l_offset nEmptyDBSizeLarge = 0;
pabyEmptyDB = VSIGetMemFileBuffer( pszCachedFilename, &nEmptyDBSizeLarge, FALSE );
nEmptyDBSize = static_cast<size_t>(nEmptyDBSizeLarge);
}
CPLFree( pszCachedFilename );
}
}
/* The following configuration option is useful mostly for debugging/testing */
if( pabyEmptyDB != NULL && CPLTestBool(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) )
{
GByte* pabyEmptyDBClone = (GByte*)VSI_MALLOC_VERBOSE(nEmptyDBSize);
if( pabyEmptyDBClone == NULL )
{
CPLFree(pszTmpDBName);
return NULL;
}
memcpy(pabyEmptyDBClone, pabyEmptyDB, nEmptyDBSize);
VSIFCloseL(VSIFileFromMemBuffer( pszTmpDBName, pabyEmptyDBClone, nEmptyDBSize, TRUE ));
poSQLiteDS = new OGRSQLiteDataSource();
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Open( pszTmpDBName, TRUE, NULL );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
if( !nRet )
{
/* should not happen really ! */
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
bSpatialiteDB = TRUE;
}
#else
/* No caching version */
poSQLiteDS = new OGRSQLiteDataSource();
char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Create( pszTmpDBName, papszOptions );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
CSLDestroy(papszOptions);
papszOptions = NULL;
if( nRet )
{
bSpatialiteDB = TRUE;
}
#endif
else
{
delete poSQLiteDS;
poSQLiteDS = NULL;
#else // HAVE_SPATIALITE
if( true )
{
#endif // HAVE_SPATIALITE
poSQLiteDS = new OGRSQLiteDataSource();
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Create( pszTmpDBName, NULL );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
if( !nRet )
{
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
}
/* -------------------------------------------------------------------- */
/* Attach the Virtual Table OGR2SQLITE module to it. */
/* -------------------------------------------------------------------- */
OGR2SQLITEModule* poModule = OGR2SQLITE_Setup(poDS, poSQLiteDS);
sqlite3* hDB = poSQLiteDS->GetDB();
/* -------------------------------------------------------------------- */
/* Analysze the statement to determine which tables will be used. */
/* -------------------------------------------------------------------- */
std::set<LayerDesc> oSetLayers;
std::set<CPLString> oSetSpatialIndex;
CPLString osModifiedSQL;
OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
oSetSpatialIndex, osModifiedSQL);
std::set<LayerDesc>::iterator oIter = oSetLayers.begin();
if( strcmp(pszStatement, osModifiedSQL.c_str()) != 0 )
CPLDebug("OGR", "Modified SQL: %s", osModifiedSQL.c_str());
pszStatement = osModifiedSQL.c_str(); /* do not use it anymore */
int bFoundOGRStyle = ( osModifiedSQL.ifind("OGR_STYLE") != std::string::npos );
/* -------------------------------------------------------------------- */
/* For each of those tables, create a Virtual Table. */
/* -------------------------------------------------------------------- */
OGRLayer* poSingleSrcLayer = NULL;
for(; oIter != oSetLayers.end(); ++oIter)
{
const LayerDesc& oLayerDesc = *oIter;
/*CPLDebug("OGR", "Layer desc : %s, %s, %s, %s",
oLayerDesc.osOriginalStr.c_str(),
oLayerDesc.osSubstitutedName.c_str(),
oLayerDesc.osDSName.c_str(),
oLayerDesc.osLayerName.c_str());*/
CPLString osSQL;
OGRLayer* poLayer = NULL;
CPLString osTableName;
int nExtraDS;
if( oLayerDesc.osDSName.size() == 0 )
{
poLayer = poDS->GetLayerByName(oLayerDesc.osLayerName);
/* Might be a false positive (unlikely) */
if( poLayer == NULL )
continue;
osTableName = oLayerDesc.osLayerName;
nExtraDS = -1;
}
else
{
OGRDataSource* poOtherDS = (OGRDataSource* )
OGROpen(oLayerDesc.osDSName, FALSE, NULL);
if( poOtherDS == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot open datasource '%s'",
oLayerDesc.osDSName.c_str() );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
poLayer = poOtherDS->GetLayerByName(oLayerDesc.osLayerName);
if( poLayer == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot find layer '%s' in '%s'",
oLayerDesc.osLayerName.c_str(),
oLayerDesc.osDSName.c_str() );
delete poOtherDS;
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
osTableName = oLayerDesc.osSubstitutedName;
nExtraDS = OGR2SQLITE_AddExtraDS(poModule, poOtherDS);
}
if( oSetLayers.size() == 1 )
poSingleSrcLayer = poLayer;
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d,%d)",
OGRSQLiteEscapeName(osTableName).c_str(),
nExtraDS,
OGRSQLiteEscape(oLayerDesc.osLayerName).c_str(),
bFoundOGRStyle,
TRUE/*bExposeOGRNativeData*/);
char* pszErrMsg = NULL;
int rc = sqlite3_exec( hDB, osSQL.c_str(),
NULL, NULL, &pszErrMsg );
if( rc != SQLITE_OK )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot create virtual table for layer '%s' : %s",
osTableName.c_str(), pszErrMsg);
sqlite3_free(pszErrMsg);
continue;
}
for(int i=0; i<poLayer->GetLayerDefn()->GetGeomFieldCount(); i++)
{
OGR2SQLITEDealWithSpatialColumn(poLayer, i, oLayerDesc,
osTableName, poSQLiteDS, hDB,
bSpatialiteDB, oSetLayers,
oSetSpatialIndex);
}
}
/* -------------------------------------------------------------------- */
/* Reload, so that virtual tables are recognized */
/* -------------------------------------------------------------------- */
poSQLiteDS->ReloadLayers();
/* -------------------------------------------------------------------- */
/* Prepare the statement. */
/* -------------------------------------------------------------------- */
/* This will speed-up layer creation */
/* ORDER BY are costly to evaluate and are not necessary to establish */
/* the layer definition. */
int bUseStatementForGetNextFeature = TRUE;
int bEmptyLayer = FALSE;
sqlite3_stmt *hSQLStmt = NULL;
int rc = sqlite3_prepare( hDB,
pszStatement, -1,
&hSQLStmt, NULL );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In ExecuteSQL(): sqlite3_prepare(%s):\n %s",
pszStatement, sqlite3_errmsg(hDB) );
if( hSQLStmt != NULL )
{
sqlite3_finalize( hSQLStmt );
}
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
/* -------------------------------------------------------------------- */
/* Do we get a resultset? */
/* -------------------------------------------------------------------- */
rc = sqlite3_step( hSQLStmt );
if( rc != SQLITE_ROW )
{
if ( rc != SQLITE_DONE )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In ExecuteSQL(): sqlite3_step(%s):\n %s",
pszStatement, sqlite3_errmsg(hDB) );
sqlite3_finalize( hSQLStmt );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
if( !STARTS_WITH_CI(pszStatement, "SELECT ") )
{
sqlite3_finalize( hSQLStmt );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
bUseStatementForGetNextFeature = FALSE;
bEmptyLayer = TRUE;
}
/* -------------------------------------------------------------------- */
/* Create layer. */
/* -------------------------------------------------------------------- */
OGRSQLiteSelectLayer *poLayer = NULL;
poLayer = new OGRSQLiteExecuteSQLLayer( pszTmpDBName,
poSQLiteDS, pszStatement, hSQLStmt,
bUseStatementForGetNextFeature, bEmptyLayer );
if( poSpatialFilter != NULL )
poLayer->SetSpatialFilter( 0, poSpatialFilter );
if( poSingleSrcLayer != NULL )
poLayer->SetMetadata( poSingleSrcLayer->GetMetadata( "NATIVE_DATA" ),
"NATIVE_DATA" );
return poLayer;
}
/************************************************************************/
/* OGRSQLiteGetReferencedLayers() */
/************************************************************************/
std::set<LayerDesc> OGRSQLiteGetReferencedLayers(const char* pszStatement)
{
/* -------------------------------------------------------------------- */
/* Analysze the statement to determine which tables will be used. */
/* -------------------------------------------------------------------- */
std::set<LayerDesc> oSetLayers;
std::set<CPLString> oSetSpatialIndex;
CPLString osModifiedSQL;
OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
oSetSpatialIndex, osModifiedSQL);
return oSetLayers;
}
static
int OGR2SQLITEDealWithSpatialColumn(OGRLayer* poLayer,
int iGeomCol,
const LayerDesc& oLayerDesc,
const CPLString& osTableName,
OGRSQLiteDataSource* poSQLiteDS,
sqlite3* hDB,
int bSpatialiteDB,
const std::set<LayerDesc>&
WHEN_SPATIALITE(oSetLayers),
const std::set<CPLString>&
WHEN_SPATIALITE(oSetSpatialIndex)
)
{
OGRGeomFieldDefn* poGeomField =
poLayer->GetLayerDefn()->GetGeomFieldDefn(iGeomCol);
CPLString osGeomColRaw;
if( iGeomCol == 0 )
osGeomColRaw = OGR2SQLITE_GetNameForGeometryColumn(poLayer);
else
osGeomColRaw = poGeomField->GetNameRef();
const char* pszGeomColRaw = osGeomColRaw.c_str();
CPLString osGeomColEscaped(OGRSQLiteEscape(pszGeomColRaw));
const char* pszGeomColEscaped = osGeomColEscaped.c_str();
CPLString osLayerNameEscaped(OGRSQLiteEscape(osTableName));
const char* pszLayerNameEscaped = osLayerNameEscaped.c_str();
CPLString osIdxNameRaw(CPLSPrintf("idx_%s_%s",
oLayerDesc.osLayerName.c_str(), pszGeomColRaw));
CPLString osIdxNameEscaped(OGRSQLiteEscapeName(osIdxNameRaw));
/* Make sure that the SRS is injected in spatial_ref_sys */
OGRSpatialReference* poSRS = poGeomField->GetSpatialRef();
if( iGeomCol == 0 && poSRS == NULL )
poSRS = poLayer->GetSpatialRef();
int nSRSId = poSQLiteDS->GetUndefinedSRID();
if( poSRS != NULL )
nSRSId = poSQLiteDS->FetchSRSId(poSRS);
CPLString osSQL;
#ifdef HAVE_SPATIALITE
bool bCreateSpatialIndex = false;
#endif
if( !bSpatialiteDB )
{
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, geometry_format, geometry_type, "
"coord_dimension, srid) "
"VALUES ('%s','%s','SpatiaLite',%d,%d,%d)",
pszLayerNameEscaped,
pszGeomColEscaped,
(int) wkbFlatten(poLayer->GetGeomType()),
wkbHasZ( poLayer->GetGeomType() ) ? 3 : 2,
nSRSId);
}
#ifdef HAVE_SPATIALITE
else
{
/* We detect the need for creating a spatial index by 2 means : */
/* 1) if there's an explicit reference to a 'idx_layername_geometrycolumn' */
/* table in the SQL --> old/traditional way of requesting spatial indices */
/* with spatialite. */
std::set<LayerDesc>::const_iterator oIter2 = oSetLayers.begin();
for(; oIter2 != oSetLayers.end(); ++oIter2)
{
const LayerDesc& oLayerDescIter = *oIter2;
if( EQUAL(oLayerDescIter.osLayerName, osIdxNameRaw) )
{
bCreateSpatialIndex = true;
break;
}
}
/* 2) or if there's a SELECT FROM SpatialIndex WHERE f_table_name = 'layername' */
if( !bCreateSpatialIndex )
{
std::set<CPLString>::const_iterator oIter3 = oSetSpatialIndex.begin();
for(; oIter3 != oSetSpatialIndex.end(); ++oIter3)
{
const CPLString& osNameIter = *oIter3;
if( EQUAL(osNameIter, oLayerDesc.osLayerName) )
{
bCreateSpatialIndex = true;
break;
}
}
}
if( poSQLiteDS->HasSpatialite4Layout() )
{
int nGeomType = poLayer->GetGeomType();
int nCoordDimension = 2;
if( wkbHasZ((OGRwkbGeometryType)nGeomType) )
{
nGeomType += 1000;
nCoordDimension = 3;
}
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, geometry_type, coord_dimension, "
"srid, spatial_index_enabled) "
"VALUES (Lower('%s'),Lower('%s'),%d ,%d ,%d, %d)",
pszLayerNameEscaped,
pszGeomColEscaped, nGeomType,
nCoordDimension,
nSRSId, static_cast<int>(bCreateSpatialIndex) );
}
else
{
const char *pszGeometryType = OGRToOGCGeomType(poLayer->GetGeomType());
if (pszGeometryType[0] == '\0')
pszGeometryType = "GEOMETRY";
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, type, coord_dimension, "
"srid, spatial_index_enabled) "
"VALUES ('%s','%s','%s','%s',%d, %d)",
pszLayerNameEscaped,
pszGeomColEscaped, pszGeometryType,
wkbHasZ( poLayer->GetGeomType() ) ? "XYZ" : "XY",
nSRSId, static_cast<int>(bCreateSpatialIndex) );
}
}
#endif // HAVE_SPATIALITE
char* pszErrMsg = NULL;
int rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, &pszErrMsg );
if( pszErrMsg != NULL )
{
CPLDebug("SQLITE", "%s -> %s", osSQL.c_str(), pszErrMsg);
sqlite3_free(pszErrMsg);
}
#ifdef HAVE_SPATIALITE
/* -------------------------------------------------------------------- */
/* Should we create a spatial index ?. */
/* -------------------------------------------------------------------- */
if( !bSpatialiteDB || !bCreateSpatialIndex )
return rc == SQLITE_OK;
CPLDebug("SQLITE", "Create spatial index %s", osIdxNameRaw.c_str());
/* ENABLE_VIRTUAL_OGR_SPATIAL_INDEX is not defined */
#ifdef ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING "
"VirtualOGRSpatialIndex(%d, '%s', pkid, xmin, xmax, ymin, ymax)",
osIdxNameEscaped.c_str(),
nExtraDS,
OGRSQLiteEscape(oLayerDesc.osLayerName).c_str());
rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
if( rc != SQLITE_OK )
{
CPLDebug( "SQLITE",
"Error occurred during spatial index creation : %s",
sqlite3_errmsg(hDB));
}
#else // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, NULL );
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" "
"USING rtree(pkid, xmin, xmax, ymin, ymax)",
osIdxNameEscaped.c_str());
if( rc == SQLITE_OK )
rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
sqlite3_stmt *hStmt = NULL;
if( rc == SQLITE_OK )
{
const char* pszInsertInto = CPLSPrintf(
"INSERT INTO \"%s\" (pkid, xmin, xmax, ymin, ymax) "
"VALUES (?,?,?,?,?)", osIdxNameEscaped.c_str());
rc = sqlite3_prepare(hDB, pszInsertInto, -1, &hStmt, NULL);
}
OGRFeature* poFeature;
OGREnvelope sEnvelope;
OGR2SQLITE_IgnoreAllFieldsExceptGeometry(poLayer);
poLayer->ResetReading();
while( rc == SQLITE_OK &&
(poFeature = poLayer->GetNextFeature()) != NULL )
{
OGRGeometry* poGeom = poFeature->GetGeometryRef();
if( poGeom != NULL && !poGeom->IsEmpty() )
{
poGeom->getEnvelope(&sEnvelope);
sqlite3_bind_int64(hStmt, 1,
(sqlite3_int64) poFeature->GetFID() );
sqlite3_bind_double(hStmt, 2, sEnvelope.MinX);
sqlite3_bind_double(hStmt, 3, sEnvelope.MaxX);
sqlite3_bind_double(hStmt, 4, sEnvelope.MinY);
sqlite3_bind_double(hStmt, 5, sEnvelope.MaxY);
rc = sqlite3_step(hStmt);
if( rc == SQLITE_OK || rc == SQLITE_DONE )
rc = sqlite3_reset(hStmt);
}
delete poFeature;
}
poLayer->SetIgnoredFields(NULL);
sqlite3_finalize(hStmt);
if( rc == SQLITE_OK )
rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, NULL );
else
{
CPLDebug( "SQLITE",
"Error occurred during spatial index creation : %s",
sqlite3_errmsg(hDB));
rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, NULL );
}
#endif // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
#endif // HAVE_SPATIALITE
return rc == SQLITE_OK;
}
CPLString OGRSQLiteViewLayer::GetSpatialWhere(OGRGeometry* poFilterGeom)
{
CPLString osSpatialWHERE;
if (HasLayerDefnError())
return "";
if( poFilterGeom != NULL && bHasSpatialIndex )
{
OGREnvelope sEnvelope;
poFilterGeom->getEnvelope( &sEnvelope );
/* We first check that the spatial index table exists */
if (!bHasCheckedSpatialIndexTable)
{
bHasCheckedSpatialIndexTable = TRUE;
char **papszResult;
int nRowCount, nColCount;
char *pszErrMsg = NULL;
CPLString osSQL;
osSQL.Printf("SELECT name FROM sqlite_master "
"WHERE name='idx_%s_%s'",
pszEscapedUnderlyingTableName,
OGRSQLiteEscape(osUnderlyingGeometryColumn).c_str());
int rc = sqlite3_get_table( poDS->GetDB(), osSQL.c_str(),
&papszResult, &nRowCount,
&nColCount, &pszErrMsg );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined, "Error: %s",
pszErrMsg );
sqlite3_free( pszErrMsg );
bHasSpatialIndex = FALSE;
}
else
{
if (nRowCount != 1)
{
bHasSpatialIndex = FALSE;
}
sqlite3_free_table(papszResult);
}
}
if (bHasSpatialIndex)
{
osSpatialWHERE.Printf("\"%s\" IN ( SELECT pkid FROM 'idx_%s_%s' WHERE "
"xmax > %.12f AND xmin < %.12f AND ymax > %.12f AND ymin < %.12f)",
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
pszEscapedUnderlyingTableName,
OGRSQLiteEscape(osUnderlyingGeometryColumn).c_str(),
sEnvelope.MinX - 1e-11, sEnvelope.MaxX + 1e-11,
sEnvelope.MinY - 1e-11, sEnvelope.MaxY + 1e-11);
}
else
{
CPLDebug("SQLITE", "Count not find idx_%s_%s layer. Disabling spatial index",
pszEscapedUnderlyingTableName, osUnderlyingGeometryColumn.c_str());
}
}
if( poFilterGeom != NULL && bSpatialiteLoaded && !bHasSpatialIndex )
{
OGREnvelope sEnvelope;
poFilterGeom->getEnvelope( &sEnvelope );
/* A bit inefficient but still faster than OGR filtering */
osSpatialWHERE.Printf("MBRIntersects(\"%s\", BuildMBR(%.12f, %.12f, %.12f, %.12f))",
OGRSQLiteEscapeName(osGeomColumn).c_str(),
sEnvelope.MinX - 1e-11, sEnvelope.MinY - 1e-11,
sEnvelope.MaxX + 1e-11, sEnvelope.MaxY + 1e-11);
}
return osSpatialWHERE;
}
CPLErr OGRSQLiteViewLayer::EstablishFeatureDefn()
{
int rc;
sqlite3 *hDB = poDS->GetDB();
sqlite3_stmt *hColStmt = NULL;
const char *pszSQL;
OGRSQLiteLayer* poUnderlyingLayer = GetUnderlyingLayer();
if (poUnderlyingLayer == NULL)
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot find underlying layer %s for view %s",
osUnderlyingTableName.c_str(), pszViewName);
return CE_Failure;
}
if ( !poUnderlyingLayer->IsTableLayer() )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Underlying layer %s for view %s is not a regular table",
osUnderlyingTableName.c_str(), pszViewName);
return CE_Failure;
}
const char* pszRealUnderlyingGeometryColumn = poUnderlyingLayer->GetGeometryColumn();
if ( pszRealUnderlyingGeometryColumn == NULL ||
!EQUAL(pszRealUnderlyingGeometryColumn, osUnderlyingGeometryColumn.c_str()) )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Underlying layer %s for view %s has not expected geometry column name (%s instead of %s)",
osUnderlyingTableName.c_str(), pszViewName,
pszRealUnderlyingGeometryColumn ? pszRealUnderlyingGeometryColumn : "(null)",
osUnderlyingGeometryColumn.c_str());
return CE_Failure;
}
poSRS = poUnderlyingLayer->GetSpatialRef();
if (poSRS)
poSRS->Reference();
this->bHasSpatialIndex = poUnderlyingLayer->HasSpatialIndex();
/* -------------------------------------------------------------------- */
/* Get the column definitions for this table. */
/* -------------------------------------------------------------------- */
hColStmt = NULL;
pszSQL = CPLSPrintf( "SELECT \"%s\", * FROM '%s' LIMIT 1",
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
pszEscapedTableName );
rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Unable to query table %s for column definitions : %s.",
pszViewName, sqlite3_errmsg(hDB) );
return CE_Failure;
}
rc = sqlite3_step( hColStmt );
if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In Initialize(): sqlite3_step(%s):\n %s",
pszSQL, sqlite3_errmsg(hDB) );
sqlite3_finalize( hColStmt );
return CE_Failure;
}
/* -------------------------------------------------------------------- */
/* Collect the rest of the fields. */
/* -------------------------------------------------------------------- */
std::set<CPLString> aosEmpty;
BuildFeatureDefn( pszViewName, hColStmt, aosEmpty );
sqlite3_finalize( hColStmt );
/* -------------------------------------------------------------------- */
/* Set the geometry type if we know it. */
/* -------------------------------------------------------------------- */
poFeatureDefn->SetGeomType( poUnderlyingLayer->GetGeomType() );
return CE_None;
}
CPLString OGRSQLiteViewLayer::GetSpatialWhere(int iGeomCol,
OGRGeometry* poFilterGeom)
{
if (HasLayerDefnError() || poFeatureDefn == NULL ||
iGeomCol < 0 || iGeomCol >= poFeatureDefn->GetGeomFieldCount())
return "";
if( poFilterGeom != NULL && bHasSpatialIndex )
{
OGREnvelope sEnvelope;
poFilterGeom->getEnvelope( &sEnvelope );
/* We first check that the spatial index table exists */
if (!bHasCheckedSpatialIndexTable)
{
bHasCheckedSpatialIndexTable = TRUE;
char **papszResult;
int nRowCount, nColCount;
char *pszErrMsg = NULL;
CPLString osSQL;
osSQL.Printf("SELECT name FROM sqlite_master "
"WHERE name='idx_%s_%s'",
pszEscapedUnderlyingTableName,
OGRSQLiteEscape(osUnderlyingGeometryColumn).c_str());
int rc = sqlite3_get_table( poDS->GetDB(), osSQL.c_str(),
&papszResult, &nRowCount,
&nColCount, &pszErrMsg );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined, "Error: %s",
pszErrMsg );
sqlite3_free( pszErrMsg );
bHasSpatialIndex = FALSE;
}
else
{
if (nRowCount != 1)
{
bHasSpatialIndex = FALSE;
}
sqlite3_free_table(papszResult);
}
}
if (bHasSpatialIndex)
{
return FormatSpatialFilterFromRTree(poFilterGeom,
CPLSPrintf("\"%s\"", OGRSQLiteEscapeName(pszFIDColumn).c_str()),
pszEscapedUnderlyingTableName,
OGRSQLiteEscape(osUnderlyingGeometryColumn).c_str());
}
else
{
CPLDebug("SQLITE", "Count not find idx_%s_%s layer. Disabling spatial index",
pszEscapedUnderlyingTableName, osUnderlyingGeometryColumn.c_str());
}
}
if( poFilterGeom != NULL && poDS->IsSpatialiteLoaded() )
{
return FormatSpatialFilterFromMBR(poFilterGeom,
OGRSQLiteEscapeName(poFeatureDefn->GetGeomFieldDefn(iGeomCol)->GetNameRef()).c_str());
}
return "";
}
CPLErr OGRSQLiteViewLayer::EstablishFeatureDefn()
{
int rc;
sqlite3 *hDB = poDS->GetDB();
sqlite3_stmt *hColStmt = NULL;
const char *pszSQL;
OGRSQLiteLayer* poUnderlyingLayer = GetUnderlyingLayer();
if (poUnderlyingLayer == NULL)
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot find underlying layer %s for view %s",
osUnderlyingTableName.c_str(), pszViewName);
return CE_Failure;
}
if ( !poUnderlyingLayer->IsTableLayer() )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Underlying layer %s for view %s is not a regular table",
osUnderlyingTableName.c_str(), pszViewName);
return CE_Failure;
}
int nUnderlyingLayerGeomFieldIndex =
poUnderlyingLayer->GetLayerDefn()->GetGeomFieldIndex(osUnderlyingGeometryColumn);
if ( nUnderlyingLayerGeomFieldIndex < 0 )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Underlying layer %s for view %s has not expected geometry column name %s",
osUnderlyingTableName.c_str(), pszViewName,
osUnderlyingGeometryColumn.c_str());
return CE_Failure;
}
this->bHasSpatialIndex = poUnderlyingLayer->HasSpatialIndex(nUnderlyingLayerGeomFieldIndex);
/* -------------------------------------------------------------------- */
/* Get the column definitions for this table. */
/* -------------------------------------------------------------------- */
hColStmt = NULL;
pszSQL = CPLSPrintf( "SELECT \"%s\", * FROM '%s' LIMIT 1",
OGRSQLiteEscapeName(pszFIDColumn).c_str(),
pszEscapedTableName );
rc = sqlite3_prepare( hDB, pszSQL, strlen(pszSQL), &hColStmt, NULL );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Unable to query table %s for column definitions : %s.",
pszViewName, sqlite3_errmsg(hDB) );
return CE_Failure;
}
rc = sqlite3_step( hColStmt );
if ( rc != SQLITE_DONE && rc != SQLITE_ROW )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In Initialize(): sqlite3_step(%s):\n %s",
pszSQL, sqlite3_errmsg(hDB) );
sqlite3_finalize( hColStmt );
return CE_Failure;
}
/* -------------------------------------------------------------------- */
/* Collect the rest of the fields. */
/* -------------------------------------------------------------------- */
std::set<CPLString> aosGeomCols;
std::set<CPLString> aosIgnoredCols;
aosGeomCols.insert(osGeomColumn);
BuildFeatureDefn( pszViewName, hColStmt, aosGeomCols, aosIgnoredCols );
sqlite3_finalize( hColStmt );
/* -------------------------------------------------------------------- */
/* Set the properties of the geometry column. */
/* -------------------------------------------------------------------- */
if( poFeatureDefn->GetGeomFieldCount() != 0 )
{
OGRSQLiteGeomFieldDefn* poSrcGeomFieldDefn =
poUnderlyingLayer->myGetLayerDefn()->myGetGeomFieldDefn(nUnderlyingLayerGeomFieldIndex);
OGRSQLiteGeomFieldDefn* poGeomFieldDefn =
poFeatureDefn->myGetGeomFieldDefn(0);
poGeomFieldDefn->SetType(poSrcGeomFieldDefn->GetType());
poGeomFieldDefn->SetSpatialRef(poSrcGeomFieldDefn->GetSpatialRef());
poGeomFieldDefn->nSRSId = poSrcGeomFieldDefn->nSRSId;
if( eGeomFormat != OSGF_None )
poGeomFieldDefn->eGeomFormat = eGeomFormat;
}
return CE_None;
}
OGRLayer * OGRSQLiteExecuteSQL( OGRDataSource* poDS,
const char *pszStatement,
OGRGeometry *poSpatialFilter,
const char *pszDialect )
{
char* pszTmpDBName = (char*) CPLMalloc(256);
sprintf(pszTmpDBName, "/vsimem/ogr2sqlite/temp_%p.db", pszTmpDBName);
OGRSQLiteDataSource* poSQLiteDS = NULL;
int nRet;
int bSpatialiteDB = FALSE;
CPLString osOldVal;
const char* pszOldVal = CPLGetConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", NULL);
if( pszOldVal != NULL )
{
osOldVal = pszOldVal;
pszOldVal = osOldVal.c_str();
}
/* -------------------------------------------------------------------- */
/* Create in-memory sqlite/spatialite DB */
/* -------------------------------------------------------------------- */
#ifdef HAVE_SPATIALITE
/* -------------------------------------------------------------------- */
/* Creating an empty spatialite DB (with spatial_ref_sys populated */
/* has a non-neglectable cost. So at the first attempt, let's make */
/* one and cache it for later use. */
/* -------------------------------------------------------------------- */
#if 1
static vsi_l_offset nEmptyDBSize = 0;
static GByte* pabyEmptyDB = NULL;
{
static void* hMutex = NULL;
CPLMutexHolder oMutexHolder(&hMutex);
static int bTried = FALSE;
if( !bTried &&
CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) )
{
bTried = TRUE;
char* pszCachedFilename = (char*) CPLMalloc(256);
sprintf(pszCachedFilename, "/vsimem/ogr2sqlite/reference_%p.db",pszCachedFilename);
char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
OGRSQLiteDataSource* poCachedDS = new OGRSQLiteDataSource();
nRet = poCachedDS->Create( pszCachedFilename, papszOptions );
CSLDestroy(papszOptions);
papszOptions = NULL;
delete poCachedDS;
if( nRet )
/* Note: the reference file keeps the ownership of the data, so that */
/* it gets released with VSICleanupFileManager() */
pabyEmptyDB = VSIGetMemFileBuffer( pszCachedFilename, &nEmptyDBSize, FALSE );
CPLFree( pszCachedFilename );
}
}
/* The following configuration option is usefull mostly for debugging/testing */
if( pabyEmptyDB != NULL && CSLTestBoolean(CPLGetConfigOption("OGR_SQLITE_DIALECT_USE_SPATIALITE", "YES")) )
{
GByte* pabyEmptyDBClone = (GByte*)VSIMalloc(nEmptyDBSize);
if( pabyEmptyDBClone == NULL )
{
CPLFree(pszTmpDBName);
return NULL;
}
memcpy(pabyEmptyDBClone, pabyEmptyDB, nEmptyDBSize);
VSIFCloseL(VSIFileFromMemBuffer( pszTmpDBName, pabyEmptyDBClone, nEmptyDBSize, TRUE ));
poSQLiteDS = new OGRSQLiteDataSource();
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Open( pszTmpDBName, TRUE );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
if( !nRet )
{
/* should not happen really ! */
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
bSpatialiteDB = TRUE;
}
#else
/* No caching version */
poSQLiteDS = new OGRSQLiteDataSource();
char** papszOptions = CSLAddString(NULL, "SPATIALITE=YES");
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Create( pszTmpDBName, papszOptions );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
CSLDestroy(papszOptions);
papszOptions = NULL;
if( nRet )
{
bSpatialiteDB = TRUE;
}
#endif
else
{
delete poSQLiteDS;
poSQLiteDS = NULL;
#else // HAVE_SPATIALITE
if( TRUE )
{
#endif // HAVE_SPATIALITE
poSQLiteDS = new OGRSQLiteDataSource();
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", "NO");
nRet = poSQLiteDS->Create( pszTmpDBName, NULL );
CPLSetThreadLocalConfigOption("OGR_SQLITE_STATIC_VIRTUAL_OGR", pszOldVal);
if( !nRet )
{
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
}
/* -------------------------------------------------------------------- */
/* Attach the Virtual Table OGR2SQLITE module to it. */
/* -------------------------------------------------------------------- */
OGR2SQLITEModule* poModule = OGR2SQLITE_Setup(poDS, poSQLiteDS);
sqlite3* hDB = poSQLiteDS->GetDB();
/* -------------------------------------------------------------------- */
/* Analysze the statement to determine which tables will be used. */
/* -------------------------------------------------------------------- */
std::set<LayerDesc> oSetLayers;
std::set<CPLString> oSetSpatialIndex;
CPLString osModifiedSQL;
OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
oSetSpatialIndex, osModifiedSQL);
std::set<LayerDesc>::iterator oIter = oSetLayers.begin();
if( strcmp(pszStatement, osModifiedSQL.c_str()) != 0 )
CPLDebug("OGR", "Modified SQL: %s", osModifiedSQL.c_str());
pszStatement = osModifiedSQL.c_str(); /* do not use it anymore */
int bFoundOGRStyle = ( osModifiedSQL.ifind("OGR_STYLE") != std::string::npos );
/* -------------------------------------------------------------------- */
/* For each of those tables, create a Virtual Table. */
/* -------------------------------------------------------------------- */
for(; oIter != oSetLayers.end(); ++oIter)
{
const LayerDesc& oLayerDesc = *oIter;
/*CPLDebug("OGR", "Layer desc : %s, %s, %s, %s",
oLayerDesc.osOriginalStr.c_str(),
oLayerDesc.osSubstitutedName.c_str(),
oLayerDesc.osDSName.c_str(),
oLayerDesc.osLayerName.c_str());*/
CPLString osSQL;
OGRLayer* poLayer = NULL;
CPLString osTableName;
int nExtraDS;
if( oLayerDesc.osDSName.size() == 0 )
{
poLayer = poDS->GetLayerByName(oLayerDesc.osLayerName);
/* Might be a false positive (unlikely) */
if( poLayer == NULL )
continue;
osTableName = oLayerDesc.osLayerName;
nExtraDS = -1;
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d)",
OGRSQLiteEscapeName(osTableName).c_str(),
nExtraDS,
OGRSQLiteEscape(osTableName).c_str(),
bFoundOGRStyle);
}
else
{
OGRDataSource* poOtherDS = (OGRDataSource* )
OGROpen(oLayerDesc.osDSName, FALSE, NULL);
if( poOtherDS == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot open datasource '%s'",
oLayerDesc.osDSName.c_str() );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
poLayer = poOtherDS->GetLayerByName(oLayerDesc.osLayerName);
if( poLayer == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot find layer '%s' in '%s'",
oLayerDesc.osLayerName.c_str(),
oLayerDesc.osDSName.c_str() );
delete poOtherDS;
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
osTableName = oLayerDesc.osSubstitutedName;
nExtraDS = OGR2SQLITE_AddExtraDS(poModule, poOtherDS);
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING VirtualOGR(%d,'%s',%d)",
OGRSQLiteEscapeName(osTableName).c_str(),
nExtraDS,
OGRSQLiteEscape(oLayerDesc.osLayerName).c_str(),
bFoundOGRStyle);
}
char* pszErrMsg = NULL;
int rc = sqlite3_exec( hDB, osSQL.c_str(),
NULL, NULL, &pszErrMsg );
if( rc != SQLITE_OK )
{
CPLError(CE_Failure, CPLE_AppDefined,
"Cannot create virtual table for layer '%s' : %s",
osTableName.c_str(), pszErrMsg);
sqlite3_free(pszErrMsg);
continue;
}
if( poLayer->GetGeomType() == wkbNone )
continue;
CPLString osGeomColRaw(OGR2SQLITE_GetNameForGeometryColumn(poLayer));
const char* pszGeomColRaw = osGeomColRaw.c_str();
CPLString osGeomColEscaped(OGRSQLiteEscape(pszGeomColRaw));
const char* pszGeomColEscaped = osGeomColEscaped.c_str();
CPLString osLayerNameEscaped(OGRSQLiteEscape(osTableName));
const char* pszLayerNameEscaped = osLayerNameEscaped.c_str();
CPLString osIdxNameRaw(CPLSPrintf("idx_%s_%s",
oLayerDesc.osLayerName.c_str(), pszGeomColRaw));
CPLString osIdxNameEscaped(OGRSQLiteEscapeName(osIdxNameRaw));
/* Make sure that the SRS is injected in spatial_ref_sys */
OGRSpatialReference* poSRS = poLayer->GetSpatialRef();
int nSRSId = poSQLiteDS->GetUndefinedSRID();
if( poSRS != NULL )
nSRSId = poSQLiteDS->FetchSRSId(poSRS);
int bCreateSpatialIndex = FALSE;
if( !bSpatialiteDB )
{
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, geometry_format, geometry_type, "
"coord_dimension, srid) "
"VALUES ('%s','%s','SpatiaLite',%d,%d,%d)",
pszLayerNameEscaped,
pszGeomColEscaped,
(int) wkbFlatten(poLayer->GetGeomType()),
( poLayer->GetGeomType() & wkb25DBit ) ? 3 : 2,
nSRSId);
}
#ifdef HAVE_SPATIALITE
else
{
/* We detect the need for creating a spatial index by 2 means : */
/* 1) if there's an explicit reference to a 'idx_layername_geometrycolumn' */
/* table in the SQL --> old/traditionnal way of requesting spatial indices */
/* with spatialite. */
std::set<LayerDesc>::iterator oIter2 = oSetLayers.begin();
for(; oIter2 != oSetLayers.end(); ++oIter2)
{
const LayerDesc& oLayerDescIter = *oIter2;
if( EQUAL(oLayerDescIter.osLayerName, osIdxNameRaw) )
{
bCreateSpatialIndex = TRUE;
break;
}
}
/* 2) or if there's a SELECT FROM SpatialIndex WHERE f_table_name = 'layername' */
if( !bCreateSpatialIndex )
{
std::set<CPLString>::iterator oIter3 = oSetSpatialIndex.begin();
for(; oIter3 != oSetSpatialIndex.end(); ++oIter3)
{
const CPLString& osNameIter = *oIter3;
if( EQUAL(osNameIter, oLayerDesc.osLayerName) )
{
bCreateSpatialIndex = TRUE;
break;
}
}
}
if( poSQLiteDS->HasSpatialite4Layout() )
{
int nGeomType = poLayer->GetGeomType();
int nCoordDimension = 2;
if( nGeomType & wkb25DBit )
{
nGeomType += 1000;
nCoordDimension = 3;
}
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, geometry_type, coord_dimension, "
"srid, spatial_index_enabled) "
"VALUES ('%s',Lower('%s'),%d ,%d ,%d, %d)",
pszLayerNameEscaped,
pszGeomColEscaped, nGeomType,
nCoordDimension,
nSRSId, bCreateSpatialIndex );
}
else
{
const char *pszGeometryType = OGRToOGCGeomType(poLayer->GetGeomType());
if (pszGeometryType[0] == '\0')
pszGeometryType = "GEOMETRY";
osSQL.Printf("INSERT INTO geometry_columns (f_table_name, "
"f_geometry_column, type, coord_dimension, "
"srid, spatial_index_enabled) "
"VALUES ('%s','%s','%s','%s',%d, %d)",
pszLayerNameEscaped,
pszGeomColEscaped, pszGeometryType,
( poLayer->GetGeomType() & wkb25DBit ) ? "XYZ" : "XY",
nSRSId, bCreateSpatialIndex );
}
}
#endif // HAVE_SPATIALITE
sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
#ifdef HAVE_SPATIALITE
/* -------------------------------------------------------------------- */
/* Should we create a spatial index ?. */
/* -------------------------------------------------------------------- */
if( !bSpatialiteDB || !bCreateSpatialIndex )
continue;
CPLDebug("SQLITE", "Create spatial index %s", osIdxNameRaw.c_str());
/* ENABLE_VIRTUAL_OGR_SPATIAL_INDEX is not defined */
#ifdef ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" USING "
"VirtualOGRSpatialIndex(%d, '%s', pkid, xmin, xmax, ymin, ymax)",
osIdxNameEscaped.c_str(),
nExtraDS,
OGRSQLiteEscape(oLayerDesc.osLayerName).c_str());
rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
if( rc != SQLITE_OK )
{
CPLDebug("SQLITE",
"Error occured during spatial index creation : %s",
sqlite3_errmsg(hDB));
}
#else // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
rc = sqlite3_exec( hDB, "BEGIN", NULL, NULL, NULL );
osSQL.Printf("CREATE VIRTUAL TABLE \"%s\" "
"USING rtree(pkid, xmin, xmax, ymin, ymax)",
osIdxNameEscaped.c_str());
if( rc == SQLITE_OK )
rc = sqlite3_exec( hDB, osSQL.c_str(), NULL, NULL, NULL );
sqlite3_stmt *hStmt = NULL;
if( rc == SQLITE_OK )
{
const char* pszInsertInto = CPLSPrintf(
"INSERT INTO \"%s\" (pkid, xmin, xmax, ymin, ymax) "
"VALUES (?,?,?,?,?)", osIdxNameEscaped.c_str());
rc = sqlite3_prepare(hDB, pszInsertInto, -1, &hStmt, NULL);
}
OGRFeature* poFeature;
OGREnvelope sEnvelope;
OGR2SQLITE_IgnoreAllFieldsExceptGeometry(poLayer);
poLayer->ResetReading();
while( rc == SQLITE_OK &&
(poFeature = poLayer->GetNextFeature()) != NULL )
{
OGRGeometry* poGeom = poFeature->GetGeometryRef();
if( poGeom != NULL && !poGeom->IsEmpty() )
{
poGeom->getEnvelope(&sEnvelope);
sqlite3_bind_int64(hStmt, 1,
(sqlite3_int64) poFeature->GetFID() );
sqlite3_bind_double(hStmt, 2, sEnvelope.MinX);
sqlite3_bind_double(hStmt, 3, sEnvelope.MaxX);
sqlite3_bind_double(hStmt, 4, sEnvelope.MinY);
sqlite3_bind_double(hStmt, 5, sEnvelope.MaxY);
rc = sqlite3_step(hStmt);
if( rc == SQLITE_OK || rc == SQLITE_DONE )
rc = sqlite3_reset(hStmt);
}
delete poFeature;
}
poLayer->SetIgnoredFields(NULL);
sqlite3_finalize(hStmt);
if( rc == SQLITE_OK )
rc = sqlite3_exec( hDB, "COMMIT", NULL, NULL, NULL );
else
{
CPLDebug("SQLITE",
"Error occured during spatial index creation : %s",
sqlite3_errmsg(hDB));
rc = sqlite3_exec( hDB, "ROLLBACK", NULL, NULL, NULL );
}
#endif // ENABLE_VIRTUAL_OGR_SPATIAL_INDEX
#endif // HAVE_SPATIALITE
}
/* -------------------------------------------------------------------- */
/* Reload, so that virtual tables are recognized */
/* -------------------------------------------------------------------- */
poSQLiteDS->ReloadLayers();
/* -------------------------------------------------------------------- */
/* Prepare the statement. */
/* -------------------------------------------------------------------- */
/* This will speed-up layer creation */
/* ORDER BY are costly to evaluate and are not necessary to establish */
/* the layer definition. */
int bUseStatementForGetNextFeature = TRUE;
int bEmptyLayer = FALSE;
sqlite3_stmt *hSQLStmt = NULL;
int rc = sqlite3_prepare( hDB,
pszStatement, strlen(pszStatement),
&hSQLStmt, NULL );
if( rc != SQLITE_OK )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In ExecuteSQL(): sqlite3_prepare(%s):\n %s",
pszStatement, sqlite3_errmsg(hDB) );
if( hSQLStmt != NULL )
{
sqlite3_finalize( hSQLStmt );
}
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
/* -------------------------------------------------------------------- */
/* Do we get a resultset? */
/* -------------------------------------------------------------------- */
rc = sqlite3_step( hSQLStmt );
if( rc != SQLITE_ROW )
{
if ( rc != SQLITE_DONE )
{
CPLError( CE_Failure, CPLE_AppDefined,
"In ExecuteSQL(): sqlite3_step(%s):\n %s",
pszStatement, sqlite3_errmsg(hDB) );
sqlite3_finalize( hSQLStmt );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
if( !EQUALN(pszStatement, "SELECT ", 7) )
{
sqlite3_finalize( hSQLStmt );
delete poSQLiteDS;
VSIUnlink(pszTmpDBName);
CPLFree(pszTmpDBName);
return NULL;
}
bUseStatementForGetNextFeature = FALSE;
bEmptyLayer = TRUE;
}
/* -------------------------------------------------------------------- */
/* Create layer. */
/* -------------------------------------------------------------------- */
OGRSQLiteSelectLayer *poLayer = NULL;
poLayer = new OGRSQLiteExecuteSQLLayer( pszTmpDBName,
poSQLiteDS, pszStatement, hSQLStmt,
bUseStatementForGetNextFeature, bEmptyLayer );
if( poSpatialFilter != NULL )
poLayer->SetSpatialFilter( poSpatialFilter );
return poLayer;
}
/************************************************************************/
/* OGRSQLiteGetReferencedLayers() */
/************************************************************************/
std::set<LayerDesc> OGRSQLiteGetReferencedLayers(const char* pszStatement)
{
/* -------------------------------------------------------------------- */
/* Analysze the statement to determine which tables will be used. */
/* -------------------------------------------------------------------- */
std::set<LayerDesc> oSetLayers;
std::set<CPLString> oSetSpatialIndex;
CPLString osModifiedSQL;
OGR2SQLITEGetPotentialLayerNames(pszStatement, oSetLayers,
oSetSpatialIndex, osModifiedSQL);
return oSetLayers;
}
