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 );
}
