bool BlockTableExists(OWConnection* connection, std::string tableName)
{
std::ostringstream oss;
char szTable[OWNAME]= "";
oss << "select table_name from user_tables where table_name like upper('%%"<< tableName <<"%%') ";
OWStatement* statement = 0;
statement = connection->CreateStatement(oss.str().c_str());
statement->Define(szTable);
try {
statement->Execute();
} catch (std::runtime_error const& ) {
// Assume for now that an error returned here is OCI_NODATA, which means
// the table doesn't exist. If this really isn't the case, we're going
// to get more legit message further down the line.
return false;
// delete statement;
// std::ostringstream oss;
// oss << "Failed select if block table "<< tableName << " exists. Do you have rights to select?"
// << std::endl << e.what() << std::endl;
// throw std::runtime_error(oss.str());
}
return true;
}
OWStatement* RunSQL(OWConnection* connection, std::ostringstream& command)
{
OWStatement* statement = 0;
statement = connection->CreateStatement(command.str().c_str());
try {
statement->Execute();
} catch (std::runtime_error const& e) {
delete statement;
std::ostringstream oss;
oss << "Failed to run SQL:" << command.str() << std::endl << e.what() << std::endl;
throw std::runtime_error(oss.str());
}
return statement;
}
bool IsGeographic(OWConnection* connection, long srid)
{
std::ostringstream oss;
char* kind = (char* ) malloc (OWNAME * sizeof(char));
oss << "SELECT COORD_REF_SYS_KIND from MDSYS.SDO_COORD_REF_SYSTEM WHERE SRID = :1";
OWStatement* statement = 0;
statement = connection->CreateStatement(oss.str().c_str());
long* p_srid = (long*) malloc( 1 * sizeof(long));
p_srid[0] = srid;
statement->Bind(p_srid);
statement->Define(kind);
try {
statement->Execute();
} catch (std::runtime_error const& e) {
delete statement;
std::ostringstream oss;
oss << "Failed to fetch geographicness of srid " << srid << std::endl << e.what() << std::endl;
throw std::runtime_error(oss.str());
}
if (compare_no_case(kind, "GEOGRAPHIC2D",12) == 0) {
delete statement;
free(kind);
free(p_srid);
return true;
}
if (compare_no_case(kind, "GEOGRAPHIC3D",12) == 0) {
delete statement;
free(kind);
free(p_srid);
return true;
}
free(kind);
free(p_srid);
return false;
}
bool EnableTracing(OWConnection* connection)
{
std::ostringstream oss;
// http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php
oss << "ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'";
OWStatement* statement = 0;
statement = connection->CreateStatement(oss.str().c_str());
try {
statement->Execute();
} catch (std::runtime_error const& e) {
delete statement;
std::ostringstream oss;
oss << "Failed to Enable tracing " << std::endl << e.what() << std::endl;
throw std::runtime_error(oss.str());
}
return true;
}
const GDALRasterAttributeTable *GeoRasterRasterBand::GetDefaultRAT()
{
if( poDefaultRAT )
{
return poDefaultRAT->Clone();
}
else
{
poDefaultRAT = new GDALRasterAttributeTable();
}
GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;
// ----------------------------------------------------------
// Get the name of the VAT Table
// ----------------------------------------------------------
char* pszVATName = poGDS->poGeoRaster->GetVAT( nBand );
if( pszVATName == NULL )
{
return NULL;
}
OCIParam* phDesc = NULL;
phDesc = poGDS->poGeoRaster->poConnection->GetDescription( pszVATName );
if( phDesc == NULL )
{
return NULL;
}
// ----------------------------------------------------------
// Create the RAT and the SELECT statemet based on fields description
// ----------------------------------------------------------
int iCol = 0;
char szField[OWNAME];
int hType = 0;
int nSize = 0;
int nPrecision = 0;
signed short nScale = 0;
char szColumnList[OWTEXT];
szColumnList[0] = '\0';
while( poGDS->poGeoRaster->poConnection->GetNextField(
phDesc, iCol, szField, &hType, &nSize, &nPrecision, &nScale ) )
{
switch( hType )
{
case SQLT_FLT:
poDefaultRAT->CreateColumn( szField, GFT_Real, GFU_Generic );
break;
case SQLT_NUM:
if( nPrecision == 0 )
{
poDefaultRAT->CreateColumn( szField, GFT_Integer,
GFU_Generic );
}
else
{
poDefaultRAT->CreateColumn( szField, GFT_Real,
GFU_Generic );
}
break;
case SQLT_CHR:
case SQLT_AFC:
case SQLT_DAT:
case SQLT_DATE:
case SQLT_TIMESTAMP:
case SQLT_TIMESTAMP_TZ:
case SQLT_TIMESTAMP_LTZ:
case SQLT_TIME:
case SQLT_TIME_TZ:
poDefaultRAT->CreateColumn( szField, GFT_String,
GFU_Generic );
break;
default:
CPLDebug("GEORASTER", "VAT (%s) Column (%s) type (%d) not supported"
"as GDAL RAT", pszVATName, szField, hType );
continue;
}
strcpy( szColumnList, CPLSPrintf( "%s substr(%s,1,%d),",
szColumnList, szField, MIN(nSize,OWNAME) ) );
iCol++;
}
szColumnList[strlen(szColumnList) - 1] = '\0'; // remove the last comma
// ----------------------------------------------------------
// Read VAT and load RAT
// ----------------------------------------------------------
OWStatement* poStmt = NULL;
poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf (
"SELECT %s FROM %s", szColumnList, pszVATName ) );
char** papszValue = (char**) CPLMalloc( sizeof(char**) * iCol );
int i = 0;
for( i = 0; i < iCol; i++ )
{
papszValue[i] = (char*) CPLMalloc( sizeof(char*) * OWNAME );
poStmt->Define( papszValue[i] );
}
if( ! poStmt->Execute() )
{
CPLError( CE_Failure, CPLE_AppDefined, "Error reading VAT %s",
pszVATName );
return NULL;
}
int iRow = 0;
while( poStmt->Fetch() )
{
for( i = 0; i < iCol; i++ )
{
poDefaultRAT->SetValue( iRow, i, papszValue[i] );
}
iRow++;
}
for( i = 0; i < iCol; i++ )
{
CPLFree( papszValue[i] );
}
CPLFree( papszValue );
delete poStmt;
CPLFree( pszVATName );
return poDefaultRAT;
}
CPLErr GeoRasterRasterBand::SetDefaultRAT( const GDALRasterAttributeTable *poRAT )
{
GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;
if( ! poRAT )
{
return CE_Failure;
}
if( poDefaultRAT )
{
delete poDefaultRAT;
}
poDefaultRAT = poRAT->Clone();
// ----------------------------------------------------------
// Check if RAT is just colortable and/or histogram
// ----------------------------------------------------------
CPLString sColName = "";
int iCol = 0;
int nColCount = poRAT->GetColumnCount();
for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
{
sColName = poRAT->GetNameOfCol( iCol );
if( EQUAL( sColName, "histogram" ) ||
EQUAL( sColName, "red" ) ||
EQUAL( sColName, "green" ) ||
EQUAL( sColName, "blue" ) ||
EQUAL( sColName, "opacity" ) )
{
nColCount--;
}
}
if( nColCount < 2 )
{
return CE_None;
}
// ----------------------------------------------------------
// Format Table description
// ----------------------------------------------------------
char szName[OWTEXT];
char szDescription[OWTEXT];
strcpy( szDescription, "( ID NUMBER" );
for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
{
strcpy( szName, poRAT->GetNameOfCol( iCol ) );
strcpy( szDescription, CPLSPrintf( "%s, %s",
szDescription, szName ) );
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
{
strcpy( szDescription, CPLSPrintf( "%s NUMBER",
szDescription ) );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
strcpy( szDescription, CPLSPrintf( "%s FLOAT",
szDescription ) );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
strcpy( szDescription, CPLSPrintf( "%s VARCHAR2(%d)",
szDescription, MAXLEN_VATSTR) );
}
}
strcpy( szDescription, CPLSPrintf( "%s )", szDescription ) );
// ----------------------------------------------------------
// Create VAT named based on RDT and RID and Layer (nBand)
// ----------------------------------------------------------
if( ! pszVATName )
{
pszVATName = CPLStrdup( CPLSPrintf(
"RAT_%s_%d_%d",
poGeoRaster->sDataTable.c_str(),
poGeoRaster->nRasterId,
nBand ) );
}
// ----------------------------------------------------------
// Create VAT table
// ----------------------------------------------------------
OWStatement* poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf(
"DECLARE\n"
" TAB VARCHAR2(68) := UPPER(:1);\n"
" CNT NUMBER := 0;\n"
"BEGIN\n"
" EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES\n"
" WHERE TABLE_NAME = :1' INTO CNT USING TAB;\n"
"\n"
" IF NOT CNT = 0 THEN\n"
" EXECUTE IMMEDIATE 'DROP TABLE '||TAB||' PURGE';\n"
" END IF;\n"
"\n"
" EXECUTE IMMEDIATE 'CREATE TABLE '||TAB||' %s';\n"
"END;", szDescription ) );
poStmt->Bind( pszVATName );
if( ! poStmt->Execute() )
{
delete poStmt;
CPLError( CE_Failure, CPLE_AppDefined, "Create VAT Table Error!" );
return CE_Failure;
}
delete poStmt;
// ----------------------------------------------------------
// Insert Data to VAT
// ----------------------------------------------------------
int iEntry = 0;
int nEntryCount = poRAT->GetRowCount();
int nColunsCount = poRAT->GetColumnCount();
int nVATStrSize = MAXLEN_VATSTR * poGeoRaster->poConnection->GetCharSize();
// ---------------------------
// Allocate array of buffers
// ---------------------------
void** papWriteFields = (void**) VSIMalloc2(sizeof(void*), nColunsCount + 1);
papWriteFields[0] =
(void*) VSIMalloc3(sizeof(int), sizeof(int), nEntryCount ); // ID field
for(iCol = 0; iCol < nColunsCount; iCol++)
{
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
papWriteFields[iCol + 1] =
(void*) VSIMalloc3(sizeof(char), nVATStrSize, nEntryCount );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
{
papWriteFields[iCol + 1] =
(void*) VSIMalloc3(sizeof(int), sizeof(int), nEntryCount );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
papWriteFields[iCol + 1] =
(void*) VSIMalloc3(sizeof(double), sizeof(double), nEntryCount );
}
}
// ---------------------------
// Load data to buffers
// ---------------------------
for( iEntry = 0; iEntry < nEntryCount; iEntry++ )
{
((int *)(papWriteFields[0]))[iEntry] = iEntry; // ID field
for(iCol = 0; iCol < nColunsCount; iCol++)
{
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
int nOffset = iEntry * nVATStrSize;
char* pszTarget = ((char*)papWriteFields[iCol + 1]) + nOffset;
const char *pszStrValue = poRAT->GetValueAsString(iEntry, iCol);
int nLen = strlen( pszStrValue );
nLen = nLen > ( nVATStrSize - 1 ) ? nVATStrSize : ( nVATStrSize - 1 );
strncpy( pszTarget, pszStrValue, nLen );
pszTarget[nLen] = '\0';
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
{
((int *)(papWriteFields[iCol + 1]))[iEntry] =
poRAT->GetValueAsInt(iEntry, iCol);
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
((double *)(papWriteFields[iCol]))[iEntry + 1] =
poRAT->GetValueAsDouble(iEntry, iCol);
}
}
}
// ---------------------------
// Prepare insert statement
// ---------------------------
CPLString osInsert = CPLSPrintf( "INSERT INTO %s VALUES (", pszVATName );
for( iCol = 0; iCol < ( nColunsCount + 1); iCol++ )
{
if( iCol > 0 )
{
osInsert.append(", ");
}
osInsert.append( CPLSPrintf(":%d", iCol + 1) );
}
osInsert.append(")");
poStmt = poGeoRaster->poConnection->CreateStatement( osInsert.c_str() );
// ---------------------------
// Bind buffers to columns
// ---------------------------
poStmt->Bind((int*) papWriteFields[0]); // ID field
for(iCol = 0; iCol < nColunsCount; iCol++)
{
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
poStmt->Bind( (char*) papWriteFields[iCol + 1], nVATStrSize );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
{
poStmt->Bind( (int*) papWriteFields[iCol + 1]);
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
poStmt->Bind( (double*) papWriteFields[iCol + 1]);
}
}
if( poStmt->Execute( iEntry ) )
{
poGDS->poGeoRaster->SetVAT( nBand, pszVATName );
}
else
{
CPLError( CE_Failure, CPLE_AppDefined, "Insert VAT Error!" );
}
// ---------------------------
// Clean up
// ---------------------------
for(iCol = 0; iCol < ( nColunsCount + 1); iCol++)
{
CPLFree( papWriteFields[iCol] );
}
CPLFree( papWriteFields );
delete poStmt;
return CE_None;
}
CPLErr GeoRasterRasterBand::SetDefaultRAT( const GDALRasterAttributeTable *poRAT )
{
GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;
if( ! poRAT )
{
return CE_Failure;
}
if( poDefaultRAT )
{
delete poDefaultRAT;
}
poDefaultRAT = poRAT->Clone();
// ----------------------------------------------------------
// Format Table description
// ----------------------------------------------------------
char szName[OWTEXT];
char szDescription[OWTEXT];
int iCol = 0;
strcpy( szDescription, "( ID NUMBER" );
for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
{
strcpy( szName, poRAT->GetNameOfCol( iCol ) );
if( EQUAL( szName, "histogram" ) )
{
return CE_None;
}
strcpy( szDescription, CPLSPrintf( "%s, %s",
szDescription, szName ) );
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
{
strcpy( szDescription, CPLSPrintf( "%s NUMBER",
szDescription ) );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
strcpy( szDescription, CPLSPrintf( "%s FLOAT",
szDescription ) );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
strcpy( szDescription, CPLSPrintf( "%s VARCHAR2(128)",
szDescription ) );
}
}
strcpy( szDescription, CPLSPrintf( "%s )", szDescription ) );
// ----------------------------------------------------------
// Create VAT named based on RDT and RID and Layer (nBand)
// ----------------------------------------------------------
if( ! pszVATName )
{
pszVATName = CPLStrdup( CPLSPrintf(
"RAT_%s_%d_%d",
poGeoRaster->pszDataTable,
poGeoRaster->nRasterId,
nBand ) );
}
// ----------------------------------------------------------
// Create VAT table
// ----------------------------------------------------------
OWStatement* poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf(
"DECLARE\n"
" TAB VARCHAR2(68) := UPPER(:1);\n"
" CNT NUMBER := 0;\n"
"BEGIN\n"
" EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES\n"
" WHERE TABLE_NAME = :1' INTO CNT USING TAB;\n"
"\n"
" IF NOT CNT = 0 THEN\n"
" EXECUTE IMMEDIATE 'DROP TABLE '||TAB||' PURGE';\n"
" END IF;\n"
"\n"
" EXECUTE IMMEDIATE 'CREATE TABLE '||TAB||' %s';\n"
"END;", szDescription ) );
poStmt->Bind( pszVATName );
if( ! poStmt->Execute() )
{
delete poStmt;
CPLError( CE_Failure, CPLE_AppDefined, "Create VAT Table Error!" );
return CE_Failure;
}
delete poStmt;
// ----------------------------------------------------------
// Insert Data to VAT
// ----------------------------------------------------------
int iEntry = 0;
int nEntryCount = poRAT->GetRowCount();
int nColunsCount = poRAT->GetColumnCount();
char szInsert[OWTEXT];
CPLString osInserts =
"DECLARE\n"
" GR1 SDO_GEORASTER := NULL;\n"
"BEGIN\n";
for( iEntry = 0; iEntry < nEntryCount; iEntry++ )
{
szInsert[0] = '\0';
strcat( szInsert, CPLSPrintf ( " INSERT INTO %s VALUES (%d",
pszVATName, iEntry ) );
for( iCol = 0; iCol < nColunsCount; iCol++ )
{
if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
{
strcat( szInsert, CPLSPrintf ( ", '%s'",
poRAT->GetValueAsString( iEntry, iCol ) ) );
}
if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer ||
poRAT->GetTypeOfCol( iCol ) == GFT_Real )
{
strcat( szInsert, CPLSPrintf ( ", %s",
poRAT->GetValueAsString( iEntry, iCol ) ) );
}
}
strcat( szInsert, ");\n" );
osInserts += szInsert;
}
osInserts += CPLSPrintf(
" SELECT %s INTO GR1 FROM %s T WHERE\n"
" T.%s.RasterDataTable = '%s' AND\n"
" T.%s.RasterId = %d FOR UPDATE;\n"
" SDO_GEOR.setVAT(GR1, %d, '%s');\n"
" UPDATE %s T SET %s = GR1 WHERE\n"
" T.%s.RasterDataTable = '%s' AND\n"
" T.%s.RasterId = %d;\n"
"END;",
poGeoRaster->pszColumn, poGeoRaster->pszTable,
poGeoRaster->pszColumn, poGeoRaster->pszDataTable,
poGeoRaster->pszColumn, poGeoRaster->nRasterId,
nBand, pszVATName,
poGeoRaster->pszTable, poGeoRaster->pszColumn,
poGeoRaster->pszColumn, poGeoRaster->pszDataTable,
poGeoRaster->pszColumn, poGeoRaster->nRasterId );
poStmt = poGeoRaster->poConnection->CreateStatement( osInserts.c_str() );
if( ! poStmt->Execute() )
{
CPLError( CE_Failure, CPLE_AppDefined, "Insert/registering VAT Error!" );
return CE_Failure;
}
delete poStmt;
poGDS->poGeoRaster->SetVAT( nBand, pszVATName );
return CE_None;
}
