CPLString OGRCARTODBTableLayer::GetSRS_SQL(const char* pszGeomCol)
{
CPLString osSQL;
osSQL.Printf("SELECT srid, srtext FROM spatial_ref_sys WHERE srid IN "
"(SELECT Find_SRID('%s', '%s', '%s'))",
OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str(),
OGRCARTODBEscapeLiteral(osName).c_str(),
OGRCARTODBEscapeLiteral(pszGeomCol).c_str());
return osSQL;
}
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;
}
int OGRCARTODBDataSource::Open( const char * pszFilename,
char** papszOpenOptions,
int bUpdateIn )
{
bReadWrite = bUpdateIn;
bBatchInsert = CSLTestBoolean(CSLFetchNameValueDef(papszOpenOptions, "BATCH_INSERT", "YES"));
pszName = CPLStrdup( pszFilename );
if( CSLFetchNameValue(papszOpenOptions, "ACCOUNT") )
pszAccount = CPLStrdup(CSLFetchNameValue(papszOpenOptions, "ACCOUNT"));
else
{
pszAccount = CPLStrdup(pszFilename + strlen("CARTODB:"));
char* pchSpace = strchr(pszAccount, ' ');
if( pchSpace )
*pchSpace = '\0';
if( pszAccount[0] == 0 )
{
CPLError(CE_Failure, CPLE_AppDefined, "Missing account name");
return FALSE;
}
}
osAPIKey = CSLFetchNameValueDef(papszOpenOptions, "API_KEY",
CPLGetConfigOption("CARTODB_API_KEY", ""));
CPLString osTables = OGRCARTODBGetOptionValue(pszFilename, "tables");
/*if( osTables.size() == 0 && osAPIKey.size() == 0 )
{
CPLError(CE_Failure, CPLE_AppDefined,
"When not specifying tables option, CARTODB_API_KEY must be defined");
return FALSE;
}*/
bUseHTTPS = CSLTestBoolean(CPLGetConfigOption("CARTODB_HTTPS", "YES"));
OGRLayer* poSchemaLayer = ExecuteSQLInternal("SELECT current_schema()");
if( poSchemaLayer )
{
OGRFeature* poFeat = poSchemaLayer->GetNextFeature();
if( poFeat )
{
if( poFeat->GetFieldCount() == 1 )
{
osCurrentSchema = poFeat->GetFieldAsString(0);
}
delete poFeat;
}
ReleaseResultSet(poSchemaLayer);
}
if( osCurrentSchema.size() == 0 )
return FALSE;
if( osAPIKey.size() && bUpdateIn )
{
ExecuteSQLInternal(
"DROP FUNCTION IF EXISTS ogr_table_metadata(TEXT,TEXT); "
"CREATE OR REPLACE FUNCTION ogr_table_metadata(schema_name TEXT, table_name TEXT) RETURNS TABLE "
"(attname TEXT, typname TEXT, attlen INT, format_type TEXT, "
"attnum INT, attnotnull BOOLEAN, indisprimary BOOLEAN, "
"defaultexpr TEXT, dim INT, srid INT, geomtyp TEXT, srtext TEXT) AS $$ "
"SELECT a.attname::text, t.typname::text, a.attlen::int, "
"format_type(a.atttypid,a.atttypmod)::text, "
"a.attnum::int, "
"a.attnotnull::boolean, "
"i.indisprimary::boolean, "
"pg_get_expr(def.adbin, c.oid)::text AS defaultexpr, "
"(CASE WHEN t.typname = 'geometry' THEN postgis_typmod_dims(a.atttypmod) ELSE NULL END)::int dim, "
"(CASE WHEN t.typname = 'geometry' THEN postgis_typmod_srid(a.atttypmod) ELSE NULL END)::int srid, "
"(CASE WHEN t.typname = 'geometry' THEN postgis_typmod_type(a.atttypmod) ELSE NULL END)::text geomtyp, "
"srtext "
"FROM pg_class c "
"JOIN pg_attribute a ON a.attnum > 0 AND "
"a.attrelid = c.oid AND c.relname = $2 "
"AND c.relname IN (SELECT CDB_UserTables())"
"JOIN pg_type t ON a.atttypid = t.oid "
"JOIN pg_namespace n ON c.relnamespace=n.oid AND n.nspname = $1 "
"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 "
"$$ LANGUAGE SQL");
}
if (osTables.size() != 0)
{
char** papszTables = CSLTokenizeString2(osTables, ",", 0);
for(int i=0;papszTables && papszTables[i];i++)
{
papoLayers = (OGRCARTODBTableLayer**) CPLRealloc(
papoLayers, (nLayers + 1) * sizeof(OGRCARTODBTableLayer*));
papoLayers[nLayers ++] = new OGRCARTODBTableLayer(this, papszTables[i]);
}
CSLDestroy(papszTables);
return TRUE;
}
OGRLayer* poTableListLayer = ExecuteSQLInternal("SELECT CDB_UserTables()");
if( poTableListLayer )
{
OGRFeature* poFeat;
while( (poFeat = poTableListLayer->GetNextFeature()) != NULL )
{
if( poFeat->GetFieldCount() == 1 )
{
papoLayers = (OGRCARTODBTableLayer**) CPLRealloc(
papoLayers, (nLayers + 1) * sizeof(OGRCARTODBTableLayer*));
papoLayers[nLayers ++] = new OGRCARTODBTableLayer(
this, poFeat->GetFieldAsString(0));
}
delete poFeat;
}
ReleaseResultSet(poTableListLayer);
}
else if( osCurrentSchema == "public" )
return FALSE;
/* There's currently a bug with CDB_UserTables() on multi-user accounts */
if( nLayers == 0 && osCurrentSchema != "public" )
{
CPLString osSQL;
osSQL.Printf("SELECT c.relname FROM pg_class c, pg_namespace n "
"WHERE c.relkind in ('r', 'v') AND c.relname !~ '^pg_' AND c.relnamespace=n.oid AND n.nspname = '%s'",
OGRCARTODBEscapeLiteral(osCurrentSchema).c_str());
poTableListLayer = ExecuteSQLInternal(osSQL);
if( poTableListLayer )
{
OGRFeature* poFeat;
while( (poFeat = poTableListLayer->GetNextFeature()) != NULL )
{
if( poFeat->GetFieldCount() == 1 )
{
papoLayers = (OGRCARTODBTableLayer**) CPLRealloc(
papoLayers, (nLayers + 1) * sizeof(OGRCARTODBTableLayer*));
papoLayers[nLayers ++] = new OGRCARTODBTableLayer(
this, poFeat->GetFieldAsString(0));
}
delete poFeat;
}
ReleaseResultSet(poTableListLayer);
}
else
return FALSE;
}
return TRUE;
}
