示例#1
0
CPLErr OGRSQLiteViewLayer::Initialize( const char *pszViewName,
                                       const char *pszViewGeometry,
                                       const char *pszViewRowid,
                                       const char *pszUnderlyingTableName,
                                       const char *pszUnderlyingGeometryColumn,
                                       int bSpatialiteLoaded)

{
    this->pszViewName = CPLStrdup(pszViewName);

    osGeomColumn = pszViewGeometry;
    eGeomFormat = OSGF_SpatiaLite;

    CPLFree( pszFIDColumn );
    pszFIDColumn = CPLStrdup( pszViewRowid );

    osUnderlyingTableName = pszUnderlyingTableName;
    osUnderlyingGeometryColumn = pszUnderlyingGeometryColumn;

    this->bSpatialiteLoaded = bSpatialiteLoaded;
    //this->bHasM = bHasM;

    pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszViewName));
    pszEscapedUnderlyingTableName = CPLStrdup(OGRSQLiteEscape(pszUnderlyingTableName));

    return CE_None;
}
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);
}
CPLErr OGRSQLiteTableLayer::Initialize( const char *pszTableName, 
                                        const char *pszGeomCol,
                                        OGRwkbGeometryType eGeomType,
                                        const char *pszGeomFormat,
                                        OGRSpatialReference *poSRS,
                                        int nSRSId,
                                        int bHasSpatialIndex,
                                        int bHasM, 
                                        int bSpatialiteReadOnly,
                                        int bSpatialiteLoaded,
                                        int iSpatialiteVersion )

{
    int rc;
    sqlite3 *hDB = poDS->GetDB();

    if( pszGeomCol == NULL )
        osGeomColumn = "";
    else
        osGeomColumn = pszGeomCol;

    if( pszGeomFormat )
    {
        if ( EQUAL(pszGeomFormat, "WKT") )
            eGeomFormat = OSGF_WKT;
        else if ( EQUAL(pszGeomFormat,"WKB") )
            eGeomFormat = OSGF_WKB;
        else if ( EQUAL(pszGeomFormat,"FGF") )
            eGeomFormat = OSGF_FGF;
        else if( EQUAL(pszGeomFormat,"SpatiaLite") )
            eGeomFormat = OSGF_SpatiaLite;
    }

    CPLFree( pszFIDColumn );
    pszFIDColumn = NULL;

    this->poSRS = poSRS;
    this->nSRSId = nSRSId;
    this->bHasSpatialIndex = bHasSpatialIndex;
    this->bHasM = bHasM;
    this->bSpatialiteReadOnly = bSpatialiteReadOnly;
    this->bSpatialiteLoaded = bSpatialiteLoaded;
    this->iSpatialiteVersion = iSpatialiteVersion;

    pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszTableName));

    CPLErr eErr;
    sqlite3_stmt *hColStmt = NULL;
    const char *pszSQL;

    if ( eGeomFormat == OSGF_SpatiaLite &&
         bSpatialiteLoaded == TRUE && 
         iSpatialiteVersion < 24 && poDS->GetUpdate() )
    {
    // we need to test version required by Spatialite TRIGGERs 
        hColStmt = NULL;
        pszSQL = CPLSPrintf( "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND tbl_name = '%s' AND sql LIKE '%%RTreeAlign%%'",
            pszEscapedTableName );

        int nRowTriggerCount, nColTriggerCount;
        char **papszTriggerResult, *pszErrMsg;

        rc = sqlite3_get_table( hDB, pszSQL, &papszTriggerResult,
            &nRowTriggerCount, &nColTriggerCount, &pszErrMsg );
        if( nRowTriggerCount >= 1 )
        {
        // obsolete library version not supporting new triggers
        // enforcing ReadOnly mode
            CPLDebug("SQLITE", "Enforcing ReadOnly mode : obsolete library version not supporting new triggers");
            this->bSpatialiteReadOnly = TRUE;
        }

        sqlite3_free_table( papszTriggerResult );
    }
	
    if( poSRS )
        poSRS->Reference();

/* -------------------------------------------------------------------- */
/*      Get the column definitions for this table.                      */
/* -------------------------------------------------------------------- */
    hColStmt = NULL;
    pszSQL = CPLSPrintf( "SELECT _rowid_, * FROM '%s' LIMIT 1",
                                     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.",
                  pszTableName, 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;
    }

/* -------------------------------------------------------------------- */
/*      What should we use as FID?  If there is a primary key           */
/*      integer field, then this will be used as the _rowid_, and we    */
/*      will pick up the real column name here.  Otherwise, we will     */
/*      just use fid.                                                   */
/*                                                                      */
/*      Note that the select _rowid_ will return the real column        */
/*      name if the rowid corresponds to another primary key            */
/*      column.                                                         */
/* -------------------------------------------------------------------- */
    pszFIDColumn = CPLStrdup(sqlite3_column_name( hColStmt, 0 ));

/* -------------------------------------------------------------------- */
/*      Collect the rest of the fields.                                 */
/* -------------------------------------------------------------------- */
    eErr = BuildFeatureDefn( pszTableName, hColStmt );
    sqlite3_finalize( hColStmt );

    if( eErr != CE_None )
        return eErr;

/* -------------------------------------------------------------------- */
/*      Set the geometry type if we know it.                            */
/* -------------------------------------------------------------------- */
    if( eGeomType != wkbUnknown )
        poFeatureDefn->SetGeomType( eGeomType );

    return CE_None;
}
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;
}
CPLErr OGRSQLiteViewLayer::Initialize( const char *pszViewName,
                                       const char *pszViewGeometry,
                                       const char *pszViewRowid,
                                       const char *pszUnderlyingTableName,
                                       const char *pszUnderlyingGeometryColumn,
                                       int bSpatialiteLoaded)

{
    int rc;
    sqlite3 *hDB = poDS->GetDB();

    OGRSQLiteLayer* poUnderlyingLayer = (OGRSQLiteLayer*) poDS->GetLayerByName(pszUnderlyingTableName);
    if (poUnderlyingLayer == NULL)
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Cannot find underlying layer %s for view %s",
                 pszUnderlyingTableName, pszViewName);
        return CE_Failure;
    }
    if ( !poUnderlyingLayer->IsTableLayer() )
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Underlying layer %s for view %s is not a regular table",
                 pszUnderlyingTableName, pszViewName);
        return CE_Failure;
    }

    const char* pszRealUnderlyingGeometryColumn = poUnderlyingLayer->GetGeometryColumn();
    if ( pszRealUnderlyingGeometryColumn == NULL ||
         !EQUAL(pszRealUnderlyingGeometryColumn, pszUnderlyingGeometryColumn) )
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Underlying layer %s for view %s has not expected geometry column name (%s instead of %s)",
                 pszUnderlyingTableName, pszViewName,
                 pszRealUnderlyingGeometryColumn ? pszRealUnderlyingGeometryColumn : "(null)",
                 pszUnderlyingGeometryColumn);
        return CE_Failure;
    }

    osGeomColumn = pszViewGeometry;
    eGeomFormat = OSGF_SpatiaLite;

    CPLFree( pszFIDColumn );
    pszFIDColumn = CPLStrdup( pszViewRowid );

    osUnderlyingTableName = pszUnderlyingTableName;
    osUnderlyingGeometryColumn = pszUnderlyingGeometryColumn;

    poSRS = poUnderlyingLayer->GetSpatialRef();
    if (poSRS)
        poSRS->Reference();

    this->bHasSpatialIndex = poUnderlyingLayer->HasSpatialIndex();
    this->bSpatialiteLoaded = bSpatialiteLoaded;
    //this->bHasM = bHasM;

    pszEscapedTableName = CPLStrdup(OGRSQLiteEscape(pszViewName));
    pszEscapedUnderlyingTableName = CPLStrdup(OGRSQLiteEscape(pszUnderlyingTableName));

    CPLErr eErr;
    sqlite3_stmt *hColStmt = NULL;
    const char *pszSQL;

/* -------------------------------------------------------------------- */
/*      Get the column definitions for this table.                      */
/* -------------------------------------------------------------------- */
    hColStmt = NULL;
    pszSQL = CPLSPrintf( "SELECT %s, * FROM '%s' LIMIT 1", pszFIDColumn, 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.                                 */
/* -------------------------------------------------------------------- */
    eErr = BuildFeatureDefn( pszViewName, hColStmt );
    sqlite3_finalize( hColStmt );

    if( eErr != CE_None )
        return eErr;

/* -------------------------------------------------------------------- */
/*      Set the geometry type if we know it.                            */
/* -------------------------------------------------------------------- */
    poFeatureDefn->SetGeomType( poUnderlyingLayer->GetGeomType() );

    return CE_None;
}
示例#7
0
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;
}
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 "";
}
示例#9
0
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;
}