OGRErr OGRCARTODBTableLayer::CreateField( OGRFieldDefn *poFieldIn, CPL_UNUSED int bApproxOK ) { GetLayerDefn(); if (!poDS->IsReadWrite()) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return OGRERR_FAILURE; } OGRFieldDefn oField(poFieldIn); if( bLaunderColumnNames ) { char* pszName = OGRPGCommonLaunderName(oField.GetNameRef()); oField.SetName(pszName); CPLFree(pszName); } /* -------------------------------------------------------------------- */ /* Create the new field. */ /* -------------------------------------------------------------------- */ if( !bDeferedCreation ) { CPLString osSQL; osSQL.Printf( "ALTER TABLE %s ADD COLUMN %s %s", OGRCARTODBEscapeIdentifier(osName).c_str(), OGRCARTODBEscapeIdentifier(oField.GetNameRef()).c_str(), OGRPGCommonLayerGetType(oField, FALSE, TRUE).c_str() ); if( !oField.IsNullable() ) osSQL += " NOT NULL"; if( oField.GetDefault() != NULL && !oField.IsDefaultDriverSpecific() ) { osSQL += " DEFAULT "; osSQL += OGRPGCommonLayerGetPGDefault(&oField); } json_object* poObj = poDS->RunSQL(osSQL); if( poObj == NULL ) return OGRERR_FAILURE; json_object_put(poObj); } poFeatureDefn->AddFieldDefn( &oField ); return OGRERR_NONE; }
OGRFeature* OGRCARTODBTableLayer::GetFeature( GIntBig nFeatureId ) { if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE ) return NULL; FlushDeferedInsert(); GetLayerDefn(); if( osFIDColName.size() == 0 ) return OGRCARTODBLayer::GetFeature(nFeatureId); CPLString osSQL = osSELECTWithoutWHERE; osSQL += " WHERE "; osSQL += OGRCARTODBEscapeIdentifier(osFIDColName).c_str(); osSQL += " = "; osSQL += CPLSPrintf(CPL_FRMT_GIB, nFeatureId); json_object* poObj = poDS->RunSQL(osSQL); json_object* poRowObj = OGRCARTODBGetSingleRow(poObj); if( poRowObj == NULL ) { if( poObj != NULL ) json_object_put(poObj); return OGRCARTODBLayer::GetFeature(nFeatureId); } OGRFeature* poFeature = BuildFeature(poRowObj); json_object_put(poObj); return poFeature; }
void OGRCARTODBTableLayer::SetDeferedCreation (OGRwkbGeometryType eGType, OGRSpatialReference* poSRS, int bGeomNullable, int bCartoDBify) { bDeferedCreation = TRUE; nNextFID = 1; CPLAssert(poFeatureDefn == NULL); this->bCartoDBify = bCartoDBify; poFeatureDefn = new OGRFeatureDefn(osName); poFeatureDefn->Reference(); poFeatureDefn->SetGeomType(wkbNone); if( eGType == wkbPolygon ) eGType = wkbMultiPolygon; else if( eGType == wkbPolygon25D ) eGType = wkbMultiPolygon25D; if( eGType != wkbNone ) { OGRCartoDBGeomFieldDefn *poFieldDefn = new OGRCartoDBGeomFieldDefn("the_geom", eGType); poFieldDefn->SetNullable(bGeomNullable); poFeatureDefn->AddGeomFieldDefn(poFieldDefn, FALSE); if( poSRS != NULL ) { poFieldDefn->nSRID = poDS->FetchSRSId( poSRS ); poFeatureDefn->GetGeomFieldDefn( poFeatureDefn->GetGeomFieldCount() - 1)->SetSpatialRef(poSRS); } } osFIDColName = "cartodb_id"; osBaseSQL.Printf("SELECT * FROM %s", OGRCARTODBEscapeIdentifier(osName).c_str()); }
CPLString OGRCARTODBResultLayer::GetSRS_SQL(const char* pszGeomCol) { CPLString osSQL; CPLString osLimitedSQL; size_t nPos = osBaseSQL.ifind(" LIMIT "); if( nPos != std::string::npos ) { osLimitedSQL = osBaseSQL; size_t nSize = osLimitedSQL.size(); for(size_t i = nPos + strlen(" LIMIT "); i < nSize; i++) { if( osLimitedSQL[i] == ' ' && osLimitedSQL[i-1] == '0') { osLimitedSQL[i-1] = '1'; break; } osLimitedSQL[i] = '0'; } } else osLimitedSQL.Printf("%s LIMIT 1", osBaseSQL.c_str()); /* Assuming that the SRID of the first non-NULL geometry applies */ /* to geometries of all rows. */ osSQL.Printf("SELECT srid, srtext FROM spatial_ref_sys WHERE srid IN " "(SELECT ST_SRID(%s) FROM (%s) ogr_subselect)", OGRCARTODBEscapeIdentifier(pszGeomCol).c_str(), osLimitedSQL.c_str()); return osSQL; }
OGRErr OGRCARTODBTableLayer::DeleteFeature( GIntBig nFID ) { if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE ) return OGRERR_FAILURE; FlushDeferedInsert(); GetLayerDefn(); if (!poDS->IsReadWrite()) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return OGRERR_FAILURE; } if( osFIDColName.size() == 0 ) return OGRERR_FAILURE; CPLString osSQL; osSQL.Printf("DELETE FROM %s WHERE %s = " CPL_FRMT_GIB, OGRCARTODBEscapeIdentifier(osName).c_str(), OGRCARTODBEscapeIdentifier(osFIDColName).c_str(), nFID); OGRErr eRet = OGRERR_FAILURE; json_object* poObj = poDS->RunSQL(osSQL); if( poObj != NULL ) { json_object* poTotalRows = json_object_object_get(poObj, "total_rows"); if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int ) { int nTotalRows = json_object_get_int(poTotalRows); if( nTotalRows > 0 ) { eRet = OGRERR_NONE; } else eRet = OGRERR_NON_EXISTING_FEATURE; } json_object_put(poObj); } return eRet; }
json_object* OGRCARTODBTableLayer::FetchNewFeatures(GIntBig iNext) { if( osFIDColName.size() > 0 ) { CPLString osSQL; osSQL.Printf("%s WHERE %s%s >= " CPL_FRMT_GIB " ORDER BY %s ASC LIMIT %d", osSELECTWithoutWHERE.c_str(), ( osWHERE.size() ) ? CPLSPrintf("%s AND ", osWHERE.c_str()) : "", OGRCARTODBEscapeIdentifier(osFIDColName).c_str(), iNext, OGRCARTODBEscapeIdentifier(osFIDColName).c_str(), GetFeaturesToFetch()); return poDS->RunSQL(osSQL); } else return OGRCARTODBLayer::FetchNewFeatures(iNext); }
OGRErr OGRCARTODBDataSource::DeleteLayer(int iLayer) { if (!bReadWrite) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return OGRERR_FAILURE; } if( iLayer < 0 || iLayer >= nLayers ) { CPLError( CE_Failure, CPLE_AppDefined, "Layer %d not in legal range of 0 to %d.", iLayer, nLayers-1 ); return OGRERR_FAILURE; } /* -------------------------------------------------------------------- */ /* Blow away our OGR structures related to the layer. This is */ /* pretty dangerous if anything has a reference to this layer! */ /* -------------------------------------------------------------------- */ CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName(); CPLDebug( "CARTODB", "DeleteLayer(%s)", osLayerName.c_str() ); int bDeferedCreation = papoLayers[iLayer]->GetDeferedCreation(); papoLayers[iLayer]->CancelDeferedCreation(); delete papoLayers[iLayer]; memmove( papoLayers + iLayer, papoLayers + iLayer + 1, sizeof(void *) * (nLayers - iLayer - 1) ); nLayers--; if (osLayerName.size() == 0) return OGRERR_NONE; if( !bDeferedCreation ) { CPLString osSQL; osSQL.Printf("DROP TABLE %s", OGRCARTODBEscapeIdentifier(osLayerName).c_str()); json_object* poObj = RunSQL(osSQL); if( poObj == NULL ) return OGRERR_FAILURE; json_object_put(poObj); } return OGRERR_NONE; }
void OGRCARTODBTableLayer::BuildWhere() { osWHERE = ""; if( m_poFilterGeom != NULL && m_iGeomFieldFilter >= 0 && m_iGeomFieldFilter < poFeatureDefn->GetGeomFieldCount() ) { OGREnvelope sEnvelope; m_poFilterGeom->getEnvelope( &sEnvelope ); CPLString osGeomColumn(poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter)->GetNameRef()); char szBox3D_1[128]; char szBox3D_2[128]; char* pszComma; CPLsnprintf(szBox3D_1, sizeof(szBox3D_1), "%.18g %.18g", sEnvelope.MinX, sEnvelope.MinY); while((pszComma = strchr(szBox3D_1, ',')) != NULL) *pszComma = '.'; CPLsnprintf(szBox3D_2, sizeof(szBox3D_2), "%.18g %.18g", sEnvelope.MaxX, sEnvelope.MaxY); while((pszComma = strchr(szBox3D_2, ',')) != NULL) *pszComma = '.'; osWHERE.Printf("(%s && 'BOX3D(%s, %s)'::box3d)", OGRCARTODBEscapeIdentifier(osGeomColumn).c_str(), szBox3D_1, szBox3D_2 ); } if( strlen(osQuery) > 0 ) { if( osWHERE.size() > 0 ) osWHERE += " AND "; osWHERE += osQuery; } if( osFIDColName.size() == 0 ) { osBaseSQL = osSELECTWithoutWHERE; if( osWHERE.size() ) { osBaseSQL += " WHERE "; osBaseSQL += osWHERE; } } }
GIntBig OGRCARTODBTableLayer::GetFeatureCount(int bForce) { if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE ) return 0; FlushDeferedInsert(); GetLayerDefn(); CPLString osSQL(CPLSPrintf("SELECT COUNT(*) FROM %s", OGRCARTODBEscapeIdentifier(osName).c_str())); if( osWHERE.size() ) { osSQL += " WHERE "; osSQL += osWHERE; } json_object* poObj = poDS->RunSQL(osSQL); json_object* poRowObj = OGRCARTODBGetSingleRow(poObj); if( poRowObj == NULL ) { if( poObj != NULL ) json_object_put(poObj); return OGRCARTODBLayer::GetFeatureCount(bForce); } json_object* poCount = json_object_object_get(poRowObj, "count"); if( poCount == NULL || json_object_get_type(poCount) != json_type_int ) { json_object_put(poObj); return OGRCARTODBLayer::GetFeatureCount(bForce); } GIntBig nRet = (GIntBig)json_object_get_int64(poCount); json_object_put(poObj); return nRet; }
OGRLayer *OGRCARTODBDataSource::ICreateLayer( const char *pszName, OGRSpatialReference *poSpatialRef, OGRwkbGeometryType eGType, char ** papszOptions ) { if (!bReadWrite) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return NULL; } /* -------------------------------------------------------------------- */ /* Do we already have this layer? If so, should we blow it */ /* away? */ /* -------------------------------------------------------------------- */ int iLayer; for( iLayer = 0; iLayer < nLayers; iLayer++ ) { if( EQUAL(pszName,papoLayers[iLayer]->GetName()) ) { if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") ) { DeleteLayer( iLayer ); } else { CPLError( CE_Failure, CPLE_AppDefined, "Layer %s already exists, CreateLayer failed.\n" "Use the layer creation option OVERWRITE=YES to " "replace it.", pszName ); return NULL; } } } int nSRID = 0; if( poSpatialRef != NULL ) nSRID = FetchSRSId( poSpatialRef ); CPLString osGeomType; if( eGType != wkbNone ) { osGeomType = OGRToOGCGeomType(eGType); if( eGType & wkb25DBit ) osGeomType += "Z"; } CPLString osSQL; osSQL.Printf("CREATE TABLE %s ( %s SERIAL, ", OGRCARTODBEscapeIdentifier(pszName).c_str(), "cartodb_id"); if( osGeomType.size() > 0 ) { osSQL += CPLSPrintf("%s GEOMETRY(%s, %d), %s GEOMETRY(%s, %d),", "the_geom", osGeomType.c_str(), nSRID, "the_geom_webmercator", osGeomType.c_str(), 3857); } osSQL += CPLSPrintf("PRIMARY KEY (%s) )", "cartodb_id"); osSQL += ";"; osSQL += CPLSPrintf("DROP SEQUENCE IF EXISTS %s", OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str()); osSQL += ";"; osSQL += CPLSPrintf("CREATE SEQUENCE %s START 1", OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str()); osSQL += ";"; osSQL += CPLSPrintf("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval('%s')", pszName, "cartodb_id", OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str()); json_object* poObj = RunSQL(osSQL); if( poObj == NULL ) return NULL; json_object_put(poObj); OGRCARTODBTableLayer* poLayer = new OGRCARTODBTableLayer(this, pszName); papoLayers = (OGRLayer**) CPLRealloc( papoLayers, (nLayers + 1) * sizeof(OGRLayer*)); papoLayers[nLayers ++] = poLayer; return poLayer; }
OGRErr OGRCARTODBTableLayer::ISetFeature( OGRFeature *poFeature ) { int i; if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE ) return OGRERR_FAILURE; FlushDeferedInsert(); GetLayerDefn(); if (!poDS->IsReadWrite()) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return OGRERR_FAILURE; } if (poFeature->GetFID() == OGRNullFID) { CPLError( CE_Failure, CPLE_AppDefined, "FID required on features given to SetFeature()." ); return OGRERR_FAILURE; } CPLString osSQL; osSQL.Printf("UPDATE %s SET ", OGRCARTODBEscapeIdentifier(osName).c_str()); int bMustComma = FALSE; for(i = 0; i < poFeatureDefn->GetFieldCount(); i++) { if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef()); osSQL += " = "; if( !poFeature->IsFieldSet(i) ) { osSQL += "NULL"; } else { OGRFieldType eType = poFeatureDefn->GetFieldDefn(i)->GetType(); if( eType == OFTString || eType == OFTDateTime || eType == OFTDate || eType == OFTTime ) { osSQL += "'"; osSQL += OGRCARTODBEscapeLiteral(poFeature->GetFieldAsString(i)); osSQL += "'"; } else if( (eType == OFTInteger || eType == OFTInteger64) && poFeatureDefn->GetFieldDefn(i)->GetSubType() == OFSTBoolean ) { osSQL += poFeature->GetFieldAsInteger(i) ? "'t'" : "'f'"; } else osSQL += poFeature->GetFieldAsString(i); } } for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++) { if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef()); osSQL += " = "; OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i); if( poGeom == NULL ) { osSQL += "NULL"; } else { OGRCartoDBGeomFieldDefn* poGeomFieldDefn = (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(i); int nSRID = poGeomFieldDefn->nSRID; if( nSRID == 0 ) nSRID = 4326; char* pszEWKB = OGRGeometryToHexEWKB(poGeom, nSRID, FALSE); osSQL += "'"; osSQL += pszEWKB; osSQL += "'"; CPLFree(pszEWKB); } } osSQL += CPLSPrintf(" WHERE %s = " CPL_FRMT_GIB, OGRCARTODBEscapeIdentifier(osFIDColName).c_str(), poFeature->GetFID()); OGRErr eRet = OGRERR_FAILURE; json_object* poObj = poDS->RunSQL(osSQL); if( poObj != NULL ) { json_object* poTotalRows = json_object_object_get(poObj, "total_rows"); if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int ) { int nTotalRows = json_object_get_int(poTotalRows); if( nTotalRows > 0 ) { eRet = OGRERR_NONE; } else eRet = OGRERR_NON_EXISTING_FEATURE; } json_object_put(poObj); } return eRet; }
OGRErr OGRCARTODBTableLayer::ICreateFeature( OGRFeature *poFeature ) { int i; if( bDeferedCreation ) { if( RunDeferedCreationIfNecessary() != OGRERR_NONE ) return OGRERR_FAILURE; } GetLayerDefn(); int bHasUserFieldMatchingFID = FALSE; if( osFIDColName.size() ) bHasUserFieldMatchingFID = poFeatureDefn->GetFieldIndex(osFIDColName) >= 0; if (!poDS->IsReadWrite()) { CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode"); return OGRERR_FAILURE; } CPLString osSQL; int bHasJustGotNextFID = FALSE; if( !bHasUserFieldMatchingFID && bInDeferedInsert && nNextFID < 0 && osFIDColName.size() ) { osSQL.Printf("SELECT nextval('%s') AS nextid", OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", osName.c_str(), osFIDColName.c_str())).c_str()); json_object* poObj = poDS->RunSQL(osSQL); json_object* poRowObj = OGRCARTODBGetSingleRow(poObj); if( poRowObj != NULL ) { json_object* poID = json_object_object_get(poRowObj, "nextid"); if( poID != NULL && json_object_get_type(poID) == json_type_int ) { nNextFID = json_object_get_int64(poID); bHasJustGotNextFID = TRUE; } } if( poObj != NULL ) json_object_put(poObj); } osSQL.Printf("INSERT INTO %s ", OGRCARTODBEscapeIdentifier(osName).c_str()); int bMustComma = FALSE; for(i = 0; i < poFeatureDefn->GetFieldCount(); i++) { if( !poFeature->IsFieldSet(i) ) continue; if( bMustComma ) osSQL += ", "; else { osSQL += "("; bMustComma = TRUE; } osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef()); } for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++) { if( poFeature->GetGeomFieldRef(i) == NULL ) continue; if( bMustComma ) osSQL += ", "; else { osSQL += "("; bMustComma = TRUE; } osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef()); } if( !bHasUserFieldMatchingFID && osFIDColName.size() && (poFeature->GetFID() != OGRNullFID || nNextFID >= 0) ) { if( bMustComma ) osSQL += ", "; else { osSQL += "("; bMustComma = TRUE; } osSQL += OGRCARTODBEscapeIdentifier(osFIDColName); } if( !bMustComma ) osSQL += " DEFAULT VALUES"; else { osSQL += ") VALUES ("; bMustComma = FALSE; for(i = 0; i < poFeatureDefn->GetFieldCount(); i++) { if( !poFeature->IsFieldSet(i) ) continue; if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; OGRFieldType eType = poFeatureDefn->GetFieldDefn(i)->GetType(); if( eType == OFTString || eType == OFTDateTime || eType == OFTDate || eType == OFTTime ) { osSQL += "'"; osSQL += OGRCARTODBEscapeLiteral(poFeature->GetFieldAsString(i)); osSQL += "'"; } else if( (eType == OFTInteger || eType == OFTInteger64) && poFeatureDefn->GetFieldDefn(i)->GetSubType() == OFSTBoolean ) { osSQL += poFeature->GetFieldAsInteger(i) ? "'t'" : "'f'"; } else osSQL += poFeature->GetFieldAsString(i); } for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++) { OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i); if( poGeom == NULL ) continue; if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; OGRCartoDBGeomFieldDefn* poGeomFieldDefn = (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(i); int nSRID = poGeomFieldDefn->nSRID; if( nSRID == 0 ) nSRID = 4326; char* pszEWKB; if( wkbFlatten(poGeom->getGeometryType()) == wkbPolygon && wkbFlatten(GetGeomType()) == wkbMultiPolygon ) { OGRMultiPolygon* poNewGeom = new OGRMultiPolygon(); poNewGeom->addGeometry(poGeom); pszEWKB = OGRGeometryToHexEWKB(poNewGeom, nSRID, FALSE); delete poNewGeom; } else pszEWKB = OGRGeometryToHexEWKB(poGeom, nSRID, FALSE); osSQL += "'"; osSQL += pszEWKB; osSQL += "'"; CPLFree(pszEWKB); } if( !bHasUserFieldMatchingFID ) { if( osFIDColName.size() && nNextFID >= 0 ) { if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; if( bHasJustGotNextFID ) { osSQL += CPLSPrintf(CPL_FRMT_GIB, nNextFID); } else { osSQL += CPLSPrintf("nextval('%s')", OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", osName.c_str(), osFIDColName.c_str())).c_str()); } poFeature->SetFID(nNextFID); nNextFID ++; } else if( osFIDColName.size() && poFeature->GetFID() != OGRNullFID ) { if( bMustComma ) osSQL += ", "; else bMustComma = TRUE; osSQL += CPLSPrintf(CPL_FRMT_GIB, poFeature->GetFID()); } } osSQL += ")"; } if( bInDeferedInsert ) { OGRErr eRet = OGRERR_NONE; if( osDeferedInsertSQL.size() != 0 && (int)osDeferedInsertSQL.size() + (int)osSQL.size() > nMaxChunkSize ) { osDeferedInsertSQL = "BEGIN;" + osDeferedInsertSQL + "COMMIT;"; json_object* poObj = poDS->RunSQL(osDeferedInsertSQL); if( poObj != NULL ) json_object_put(poObj); else { bInDeferedInsert = FALSE; eRet = OGRERR_FAILURE; } osDeferedInsertSQL = ""; } osDeferedInsertSQL += osSQL; osDeferedInsertSQL += ";"; if( (int)osDeferedInsertSQL.size() > nMaxChunkSize ) { osDeferedInsertSQL = "BEGIN;" + osDeferedInsertSQL + "COMMIT;"; json_object* poObj = poDS->RunSQL(osDeferedInsertSQL); if( poObj != NULL ) json_object_put(poObj); else { bInDeferedInsert = FALSE; eRet = OGRERR_FAILURE; } osDeferedInsertSQL = ""; } return eRet; } if( osFIDColName.size() ) { osSQL += " RETURNING "; osSQL += OGRCARTODBEscapeIdentifier(osFIDColName); json_object* poObj = poDS->RunSQL(osSQL); json_object* poRowObj = OGRCARTODBGetSingleRow(poObj); if( poRowObj == NULL ) { if( poObj != NULL ) json_object_put(poObj); return OGRERR_FAILURE; } json_object* poID = json_object_object_get(poRowObj, osFIDColName); if( poID != NULL && json_object_get_type(poID) == json_type_int ) { poFeature->SetFID(json_object_get_int64(poID)); } if( poObj != NULL ) json_object_put(poObj); return OGRERR_NONE; } else { OGRErr eRet = OGRERR_FAILURE; json_object* poObj = poDS->RunSQL(osSQL); if( poObj != NULL ) { json_object* poTotalRows = json_object_object_get(poObj, "total_rows"); if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int ) { int nTotalRows = json_object_get_int(poTotalRows); if( nTotalRows == 1 ) { eRet = OGRERR_NONE; } } json_object_put(poObj); } return eRet; } }
OGRErr OGRCARTODBTableLayer::RunDeferedCreationIfNecessary() { if( !bDeferedCreation ) return OGRERR_NONE; bDeferedCreation = FALSE; CPLString osSQL; osSQL.Printf("CREATE TABLE %s ( %s SERIAL,", OGRCARTODBEscapeIdentifier(osName).c_str(), osFIDColName.c_str()); int nSRID = 0; OGRwkbGeometryType eGType = GetGeomType(); if( eGType != wkbNone ) { CPLString osGeomType = OGRToOGCGeomType(eGType); if( wkbHasZ(eGType) ) osGeomType += "Z"; OGRCartoDBGeomFieldDefn *poFieldDefn = (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(0); nSRID = poFieldDefn->nSRID; osSQL += CPLSPrintf("%s GEOMETRY(%s, %d)%s, %s GEOMETRY(%s, %d),", "the_geom", osGeomType.c_str(), nSRID, (!poFieldDefn->IsNullable()) ? " NOT NULL" : "", "the_geom_webmercator", osGeomType.c_str(), 3857); } for( int i = 0; i < poFeatureDefn->GetFieldCount(); i++ ) { OGRFieldDefn* poFieldDefn = poFeatureDefn->GetFieldDefn(i); if( strcmp(poFieldDefn->GetNameRef(), osFIDColName) != 0 ) { osSQL += OGRCARTODBEscapeIdentifier(poFieldDefn->GetNameRef()); osSQL += " "; osSQL += OGRPGCommonLayerGetType(*poFieldDefn, FALSE, TRUE); if( !poFieldDefn->IsNullable() ) osSQL += " NOT NULL"; if( poFieldDefn->GetDefault() != NULL && !poFieldDefn->IsDefaultDriverSpecific() ) { osSQL += " DEFAULT "; osSQL += poFieldDefn->GetDefault(); } osSQL += ","; } } osSQL += CPLSPrintf("PRIMARY KEY (%s) )", osFIDColName.c_str()); CPLString osSeqName(OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq", osName.c_str(), osFIDColName.c_str()))); osSQL += ";"; osSQL += CPLSPrintf("DROP SEQUENCE IF EXISTS %s CASCADE", osSeqName.c_str()); osSQL += ";"; osSQL += CPLSPrintf("CREATE SEQUENCE %s START 1", osSeqName.c_str()); osSQL += ";"; osSQL += CPLSPrintf("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval('%s')", OGRCARTODBEscapeIdentifier(osName).c_str(), osFIDColName.c_str(), osSeqName.c_str()); json_object* poObj = poDS->RunSQL(osSQL); if( poObj == NULL ) return OGRERR_FAILURE; json_object_put(poObj); if( bCartoDBify ) { if( nSRID != 4326 ) { if( eGType != wkbNone ) { CPLError(CE_Warning, CPLE_AppDefined, "Cannot register table in dashboard with " "cdb_cartodbfytable() since its SRS is not EPSG:4326"); } } else { if( poDS->GetCurrentSchema() == "public" ) osSQL.Printf("SELECT cdb_cartodbfytable('%s')", OGRCARTODBEscapeLiteral(osName).c_str()); else osSQL.Printf("SELECT cdb_cartodbfytable('%s', '%s')", OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str(), OGRCARTODBEscapeLiteral(osName).c_str()); poObj = poDS->RunSQL(osSQL); if( poObj == NULL ) return OGRERR_FAILURE; json_object_put(poObj); } } return OGRERR_NONE; }
OGRFeatureDefn * OGRCARTODBTableLayer::GetLayerDefnInternal(CPL_UNUSED json_object* poObjIn) { if( poFeatureDefn != NULL ) return poFeatureDefn; CPLString osCommand; if( poDS->IsAuthenticatedConnection() ) { // Get everything ! osCommand.Printf( "SELECT a.attname, t.typname, a.attlen, " "format_type(a.atttypid,a.atttypmod), " "a.attnum, " "a.attnotnull, " "i.indisprimary, " "pg_get_expr(def.adbin, c.oid) AS defaultexpr, " "postgis_typmod_dims(a.atttypmod) dim, " "postgis_typmod_srid(a.atttypmod) srid, " "postgis_typmod_type(a.atttypmod)::text geomtyp, " "srtext " "FROM pg_class c " "JOIN pg_attribute a ON a.attnum > 0 AND " "a.attrelid = c.oid AND c.relname = '%s' " "JOIN pg_type t ON a.atttypid = t.oid " "JOIN pg_namespace n ON c.relnamespace=n.oid AND n.nspname= '%s' " "LEFT JOIN pg_index i ON c.oid = i.indrelid AND " "i.indisprimary = 't' AND a.attnum = ANY(i.indkey) " "LEFT JOIN pg_attrdef def ON def.adrelid = c.oid AND " "def.adnum = a.attnum " "LEFT JOIN spatial_ref_sys srs ON srs.srid = postgis_typmod_srid(a.atttypmod) " "ORDER BY a.attnum", OGRCARTODBEscapeLiteral(osName).c_str(), OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str()); } else if( poDS->HasOGRMetadataFunction() != FALSE ) { osCommand.Printf( "SELECT * FROM ogr_table_metadata('%s', '%s')", OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str(), OGRCARTODBEscapeLiteral(osName).c_str() ); } if( osCommand.size() ) { if( !poDS->IsAuthenticatedConnection() && poDS->HasOGRMetadataFunction() < 0 ) CPLPushErrorHandler(CPLQuietErrorHandler); OGRLayer* poLyr = poDS->ExecuteSQLInternal(osCommand); if( !poDS->IsAuthenticatedConnection() && poDS->HasOGRMetadataFunction() < 0 ) { CPLPopErrorHandler(); if( poLyr == NULL ) { CPLDebug("CARTODB", "ogr_table_metadata(text, text) not available"); CPLErrorReset(); } else if( poLyr->GetLayerDefn()->GetFieldCount() != 12 ) { CPLDebug("CARTODB", "ogr_table_metadata(text, text) has unexpected column count"); poDS->ReleaseResultSet(poLyr); poLyr = NULL; } poDS->SetOGRMetadataFunction(poLyr != NULL); } if( poLyr ) { OGRFeature* poFeat; while( (poFeat = poLyr->GetNextFeature()) != NULL ) { if( poFeatureDefn == NULL ) { // We could do that outside of the while() loop, but // by doing that here, we are somewhat robust to // ogr_table_metadata() returning suddenly an empty result set // for example if CDB_UserTables() no longer works poFeatureDefn = new OGRFeatureDefn(osName); poFeatureDefn->Reference(); poFeatureDefn->SetGeomType(wkbNone); } const char* pszAttname = poFeat->GetFieldAsString("attname"); const char* pszType = poFeat->GetFieldAsString("typname"); int nWidth = poFeat->GetFieldAsInteger("attlen"); const char* pszFormatType = poFeat->GetFieldAsString("format_type"); int bNotNull = poFeat->GetFieldAsInteger("attnotnull"); int bIsPrimary = poFeat->GetFieldAsInteger("indisprimary"); int iDefaultExpr = poLyr->GetLayerDefn()->GetFieldIndex("defaultexpr"); const char* pszDefault = (iDefaultExpr >= 0 && poFeat->IsFieldSet(iDefaultExpr)) ? poFeat->GetFieldAsString(iDefaultExpr) : NULL; if( bIsPrimary && (EQUAL(pszType, "int2") || EQUAL(pszType, "int4") || EQUAL(pszType, "int8") || EQUAL(pszType, "serial") || EQUAL(pszType, "bigserial")) ) { osFIDColName = pszAttname; } else if( strcmp(pszAttname, "created_at") == 0 || strcmp(pszAttname, "updated_at") == 0 || strcmp(pszAttname, "the_geom_webmercator") == 0) { /* ignored */ } else { if( EQUAL(pszType,"geometry") ) { int nDim = poFeat->GetFieldAsInteger("dim"); int nSRID = poFeat->GetFieldAsInteger("srid"); const char* pszGeomType = poFeat->GetFieldAsString("geomtyp"); const char* pszSRText = (poFeat->IsFieldSet( poLyr->GetLayerDefn()->GetFieldIndex("srtext"))) ? poFeat->GetFieldAsString("srtext") : NULL; OGRwkbGeometryType eType = OGRFromOGCGeomType(pszGeomType); if( nDim == 3 ) eType = wkbSetZ(eType); OGRCartoDBGeomFieldDefn *poFieldDefn = new OGRCartoDBGeomFieldDefn(pszAttname, eType); if( bNotNull ) poFieldDefn->SetNullable(FALSE); OGRSpatialReference* poSRS = NULL; if( pszSRText != NULL ) { poSRS = new OGRSpatialReference(); char* pszTmp = (char* )pszSRText; if( poSRS->importFromWkt(&pszTmp) != OGRERR_NONE ) { delete poSRS; poSRS = NULL; } if( poSRS != NULL ) { poFieldDefn->SetSpatialRef(poSRS); poSRS->Release(); } } poFieldDefn->nSRID = nSRID; poFeatureDefn->AddGeomFieldDefn(poFieldDefn, FALSE); } else { OGRFieldDefn oField(pszAttname, OFTString); if( bNotNull ) oField.SetNullable(FALSE); OGRPGCommonLayerSetType(oField, pszType, pszFormatType, nWidth); if( pszDefault ) OGRPGCommonLayerNormalizeDefault(&oField, pszDefault); poFeatureDefn->AddFieldDefn( &oField ); } } delete poFeat; } poDS->ReleaseResultSet(poLyr); } } if( poFeatureDefn == NULL ) { osBaseSQL.Printf("SELECT * FROM %s", OGRCARTODBEscapeIdentifier(osName).c_str()); EstablishLayerDefn(osName, NULL); osBaseSQL = ""; } if( osFIDColName.size() > 0 ) { osBaseSQL = "SELECT "; osBaseSQL += OGRCARTODBEscapeIdentifier(osFIDColName); } for(int i=0; i<poFeatureDefn->GetGeomFieldCount(); i++) { if( osBaseSQL.size() == 0 ) osBaseSQL = "SELECT "; else osBaseSQL += ", "; osBaseSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef()); } for(int i=0; i<poFeatureDefn->GetFieldCount(); i++) { if( osBaseSQL.size() == 0 ) osBaseSQL = "SELECT "; else osBaseSQL += ", "; osBaseSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef()); } if( osBaseSQL.size() == 0 ) osBaseSQL = "SELECT *"; osBaseSQL += " FROM "; osBaseSQL += OGRCARTODBEscapeIdentifier(osName); osSELECTWithoutWHERE = osBaseSQL; return poFeatureDefn; }
OGRErr OGRCARTODBTableLayer::GetExtent( int iGeomField, OGREnvelope *psExtent, int bForce ) { CPLString osSQL; if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE ) return OGRERR_FAILURE; FlushDeferedInsert(); if( iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() || GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone ) { if( iGeomField != 0 ) { CPLError(CE_Failure, CPLE_AppDefined, "Invalid geometry field index : %d", iGeomField); } return OGRERR_FAILURE; } OGRGeomFieldDefn* poGeomFieldDefn = poFeatureDefn->GetGeomFieldDefn(iGeomField); /* Do not take the spatial filter into account */ osSQL.Printf( "SELECT ST_Extent(%s) FROM %s", OGRCARTODBEscapeIdentifier(poGeomFieldDefn->GetNameRef()).c_str(), OGRCARTODBEscapeIdentifier(osName).c_str()); json_object* poObj = poDS->RunSQL(osSQL); json_object* poRowObj = OGRCARTODBGetSingleRow(poObj); if( poRowObj != NULL ) { json_object* poExtent = json_object_object_get(poRowObj, "st_extent"); if( poExtent != NULL && json_object_get_type(poExtent) == json_type_string ) { const char* pszBox = json_object_get_string(poExtent); const char * ptr, *ptrEndParenthesis; char szVals[64*6+6]; ptr = strchr(pszBox, '('); if (ptr) ptr ++; if (ptr == NULL || (ptrEndParenthesis = strchr(ptr, ')')) == NULL || ptrEndParenthesis - ptr > (int)(sizeof(szVals) - 1)) { CPLError( CE_Failure, CPLE_IllegalArg, "Bad extent representation: '%s'", pszBox); json_object_put(poObj); return OGRERR_FAILURE; } strncpy(szVals,ptr,ptrEndParenthesis - ptr); szVals[ptrEndParenthesis - ptr] = '\0'; char ** papszTokens = CSLTokenizeString2(szVals," ,",CSLT_HONOURSTRINGS); int nTokenCnt = 4; if ( CSLCount(papszTokens) != nTokenCnt ) { CPLError( CE_Failure, CPLE_IllegalArg, "Bad extent representation: '%s'", pszBox); CSLDestroy(papszTokens); json_object_put(poObj); return OGRERR_FAILURE; } // Take X,Y coords // For PostGis ver >= 1.0.0 -> Tokens: X1 Y1 X2 Y2 (nTokenCnt = 4) // For PostGIS ver < 1.0.0 -> Tokens: X1 Y1 Z1 X2 Y2 Z2 (nTokenCnt = 6) // => X2 index calculated as nTokenCnt/2 // Y2 index caluclated as nTokenCnt/2+1 psExtent->MinX = CPLAtof( papszTokens[0] ); psExtent->MinY = CPLAtof( papszTokens[1] ); psExtent->MaxX = CPLAtof( papszTokens[nTokenCnt/2] ); psExtent->MaxY = CPLAtof( papszTokens[nTokenCnt/2+1] ); CSLDestroy(papszTokens); json_object_put(poObj); return OGRERR_NONE; } } if( poObj != NULL ) json_object_put(poObj); if( iGeomField == 0 ) return OGRLayer::GetExtent( psExtent, bForce ); else return OGRLayer::GetExtent( iGeomField, psExtent, bForce ); }