#include "StdAfx.h"

#include "c_KgOraUtil.h"

#include "c_OCI_API.h"
#include "c_Ora_API2.h"
#include "c_SdoGeomToAGF2.h"
//#include "c_FdoOra_API2.h"
#include "ut_OCI.h"

#include "c_FdoOra_API3.h"


CPPUNIT_TEST_SUITE_REGISTRATION(ut_OCI);



#define D_CONN_USERNAME L"unittest"
#define D_CONN_PASS     L"unittest"
#define D_CONN_SERVICE  L"//test10gxe/xe"
//#define D_CONN_SERVICE  L"//temp10gr2/ora10gr2"


ut_OCI::ut_OCI(void)
{
}

ut_OCI::~ut_OCI(void)
{
}


c_Oci_Connection* ut_OCI::OCI_CreateConnection()
{
  return c_OCI_API::CreateConnection(D_CONN_USERNAME,D_CONN_PASS,D_CONN_SERVICE);
}


void ut_OCI::OCI_OpenConn()
{
try
{
  c_OCI_API::OciInit();
  c_Oci_Connection* ociconn = OCI_CreateConnection();
  
  c_OCI_API::CloseConnection(ociconn);

}
catch(c_Oci_Exception* exc)
{
  
  FdoStringP fdostr(exc->GetErrorText());
  delete exc;
  
  
  CPPUNIT_FAIL( (const char*)fdostr );
  
}
catch(...)
{
  
  CPPUNIT_FAIL( "General exception" );
}
  
}//end of ut_OCI::OCI_OpenConn


c_SdoGeomToAGF2 sdotoagf;
void process_data(c_Oci_Connection* ociconn,SDO_GEOMETRY_TYPE **array_geomobjptr,SDO_GEOMETRY_ind**array_geomindptr,OCINumber*gid,
                  int  rows_to_process, 
                  int  *rows_processed)
{
  int            row;
  int         double_gid;
  
  SDO_GEOMETRY_TYPE* geomobj = *array_geomobjptr; 
  SDO_GEOMETRY_ind* geomind = *array_geomindptr;

  for (row = 0; row < rows_to_process; row++, (*rows_processed)++)
  {
    /*
    ** Get ID_COLUMN
    */
    if( gid )
      ociconn->OciCheckError( OCINumberToInt(ociconn->m_OciHpError, gid, 
        (uword)sizeof(int),OCI_NUMBER_UNSIGNED,(dvoid *)&double_gid));

    
    if( geomind->_atomic != OCI_IND_NULL )
    {
      //SDO_GEOMETRY_TYPE * geom = global_geom_obj[row];
      
      
      int gtype;

      ociconn->OciCheckError( OCINumberToInt(ociconn->m_OciHpError, &(geomobj->sdo_gtype),
        (uword)sizeof(int), OCI_NUMBER_SIGNED,
        (dvoid *)&gtype));
      
      
      sdotoagf.SetGeometry(geomobj,geomind);
      sdotoagf.ToAGF();
     
      
      //char *buff = c_Ora_API2::SdoGeomToString(geomobj,geomind);      
      //delete []buff;
      
      
      
      //SDO_GEOMETRY_TYPE*sdo2 = ociconn->NewSdoGeometry();
      //int size;
      //size = OCICollSize( c_OCI_API::m_OciHpEnvironment, c_OCI_API::m_OciHpError, sdo2->sdo_ordinates, &size );
                   
      
      // free the spatial object instance
    
      
   
         
      //ociconn->OciCheckError(OCIObjectFree(ociconn->m_OciHpEnvironment, ociconn->m_OciHpError, (dvoid *)geomobj,(ub2)OCI_OBJECTFREE_FORCE));
      //ociconn->OciCheckError(OCIObjectFree(ociconn->m_OciHpEnvironment, ociconn->m_OciHpError, (dvoid *)global_geom_obj[row],(ub2)0));
      
      //*array_geomobjptr = NULL;// global_geom_obj[row] = NULL;
      
      
      
      //OCIObjectFree(ociconn->m_OciHpEnvironment, ociconn->m_OciHpError, (dvoid *)geomind,(ub2)0);
      
      
      //OCIObjectFree(ociconn->m_OciHpEnvironment, ociconn->m_OciHpError, (dvoid *)geomobj,(ub2)0);
      //*array_geomobjptr = NULL;
      
      //delete geomind;
      
      array_geomobjptr++;
      array_geomindptr++;
      geomobj = *array_geomobjptr;
      geomind = *array_geomindptr;
    }
    
  } /* end of for-loop: row */
}

void ut_OCI::OCI_ReadGeoms()
{
  OCINumber         global_gid[D_OCI_ARRAY_SIZE];
  SDO_GEOMETRY_TYPE *global_geom_obj[D_OCI_ARRAY_SIZE];/* spatial object buffer */
  SDO_GEOMETRY_ind  *global_geom_ind[D_OCI_ARRAY_SIZE]; /* Object indicator */
  int rows_fetched=0,rows_processed=0;
  
  try
  {
    c_OCI_API::OciInit();
    //c_Oci_Connection* ociconn = c_OCI_API::CreateConnection(L"unittest",L"unittest",L"//test10gxe/xe");
    

    //std::wstring sql( L"SELECT AUTOGENERATED_SDF_ID,shpgeom FROM parcels" );
    //std::wstring sql( L"SELECT featid,geometry FROM bldg_polygon" );
    std::wstring sql( L"SELECT geometry FROM bldg_polygon" );
    
    clock_t elog_t1=clock();
      
for(int loop=0;loop<1;loop++)      
{
    c_Oci_Connection* ociconn = OCI_CreateConnection();
    
    /* initialize stmthp */
    OCIStmt 		*stmthp;
    ociconn->OciCheckError( OCIHandleAlloc((dvoid *)ociconn->m_OciHpEnvironment, (dvoid **)&stmthp, (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0));
      
    /* parse query */
    ociconn->OciCheckError( OCIStmtPrepare(stmthp, ociconn->m_OciHpError, 
      (text *)sql.c_str(), (ub4)wcslen(sql.c_str())*sizeof(wchar_t), 
      (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

    


    
    
    // define GID and spatial ADT object 
/*    
    OCIDefine *defn1p;
    ociconn->OciCheckError( OCIDefineByPos(stmthp, &defn1p, ociconn->m_OciHpError, (ub4)1, 
      (dvoid *)&gid, 
      (sb4)sizeof(OCINumber), SQLT_VNU,
      (dvoid *)0, (ub2 *)0, (ub2 *)0, 
      (ub4)OCI_DEFAULT));
*/
    OCIDefine *defn2p;
    ociconn->OciCheckError( OCIDefineByPos(stmthp, &defn2p, ociconn->m_OciHpError, (ub4)1, 
      (dvoid *)0, (sb4)0, SQLT_NTY, (dvoid *)0,
      (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT));

    ociconn->OciCheckError( OCIDefineObject(defn2p, ociconn->m_OciHpError, ociconn->m_OciType_SdoGeometry, 
      (dvoid **)global_geom_obj, (ub4 *)0, 
      (dvoid **)global_geom_ind, (ub4 *)0));



    for( int i=0; i < D_OCI_ARRAY_SIZE; i++ )
    {
      global_geom_obj[i]=NULL;
      global_geom_ind[i]=NULL;
    }



    /* execute */
    int status = OCIStmtExecute(ociconn->m_OciHpServiceContext, stmthp, ociconn->m_OciHpError, (ub4)D_OCI_ARRAY_SIZE, (ub4)0, 
      (OCISnapshot *)NULL, (OCISnapshot *)NULL, 
      (ub4)OCI_DEFAULT);

    bool has_more_data;
    if (status == OCI_SUCCESS_WITH_INFO || status == OCI_NO_DATA)
      has_more_data = FALSE;
    else
    {
      has_more_data = TRUE;
      ociconn->OciCheckError( status);
    }

    /* process data */
    rows_fetched=0;rows_processed=0;
    ociconn->OciCheckError( OCIAttrGet((dvoid *)stmthp, (ub4)OCI_HTYPE_STMT,
      (dvoid *)&rows_fetched, (ub4 *)0, 
      (ub4)OCI_ATTR_ROW_COUNT, ociconn->m_OciHpError));
    int rows_to_process = rows_fetched - rows_processed;

    process_data(ociconn,global_geom_obj,global_geom_ind,NULL, rows_to_process, &rows_processed);

    while (has_more_data)
    {
   
      status = OCIStmtFetch2(stmthp, ociconn->m_OciHpError, (ub4)D_OCI_ARRAY_SIZE, 
        (ub2)OCI_FETCH_NEXT, 1,(ub4)OCI_DEFAULT);
      //status = OCIStmtFetch(stmthp, ociconn->m_OciHpError, (ub4)D_OCI_ARRAY_SIZE, 
      //  (ub2)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT);

      if (status != OCI_SUCCESS)
        has_more_data = FALSE;

      /* process data */
      ociconn->OciCheckError( OCIAttrGet((dvoid *)stmthp, (ub4)OCI_HTYPE_STMT,
        (dvoid *)&rows_fetched, (ub4 *)0, 
        (ub4)OCI_ATTR_ROW_COUNT, ociconn->m_OciHpError));
      rows_to_process = rows_fetched - rows_processed;

      process_data(ociconn, global_geom_obj,global_geom_ind,NULL,rows_to_process, &rows_processed);
    }

    //ociconn->OciCheckError( OCICacheFree(ociconn->m_OciHpEnvironment,ociconn->m_OciHpError,ociconn->m_OciHpServiceContext) );
    


    


    /* initialize stmthp */
    
    ociconn->OciCheckError( OCIHandleFree((dvoid *)stmthp, (ub4)OCI_HTYPE_STMT));
    
    c_OCI_API::CloseConnection(ociconn);

}

clock_t elog_t2=clock();
double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    //ociconn->OciCheckError( OCICacheFree ( ociconn->m_OciHpEnvironment,ociconn->m_OciHpError,ociconn->m_OciHpServiceContext )); 
    // free the spatial object instance

/*
    for(int row=0;row<D_ARRAY_SIZE;row++) 
    {
    
      if( global_geom_obj[row] )
      {            
        ociconn->OciCheckError( OCIObjectFree(ociconn->m_OciHpEnvironment, ociconn->m_OciHpError, (dvoid *)global_geom_obj[row], 
        (ub2)OCI_OBJECTFREE_FORCE));

        global_geom_obj[row] = NULL;
      }
    }
*/   

    
    c_OCI_API::OciTerminate();
   
    double secs = msecs;
    secs = secs / 1000.0;
    
    char buff[128];
    sprintf(buff,"Ellapsed %.2lfs rows_fetched=%d rows_processed=%d",secs,rows_fetched,rows_processed);
    CPPUNIT_FAIL( buff );
    
    
    

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
 
}//end of void ut_OCI::OCI_ReadGeoms


void ut_OCI::OCI_ReadGeoms2()
{
  OCINumber         global_gid[D_OCI_ARRAY_SIZE];
  SDO_GEOMETRY_TYPE *global_geom_obj[D_OCI_ARRAY_SIZE];/* spatial object buffer */
  SDO_GEOMETRY_ind  *global_geom_ind[D_OCI_ARRAY_SIZE]; /* Object indicator */
  int rows_processed=0;
  
  try
  {
    c_OCI_API::OciInit();
    //c_Oci_Connection* ociconn = c_OCI_API::CreateConnection(L"unittest",L"unittest",L"//test10gxe/xe");
    

    //std::wstring sql( L"SELECT AUTOGENERATED_SDF_ID,shpgeom FROM parcels" );
    //std::wstring sql( L"SELECT featid,geometry FROM bldg_polygon" );
    //std::wstring sql( L"SELECT geometry,bldg_polygon.*  FROM bldg_polygon " );
    std::wstring sql( L"SELECT geometry,featid  FROM bldg_polygon " );
    c_Oci_Connection* ociconn = OCI_CreateConnection();
    double method1_secs,method2_secs;
    long method1_rows_fetched=0,method2_rows_fetched=0;
    int loop_count=1;

  {    
    clock_t elog_t1=clock();
      
    for(int loop=0;loop<loop_count;loop++)      
    {
        c_Oci_Statement* stm = ociconn->CreateStatement();
        
        stm->Prepare(sql.c_str(),0);
        stm->ExecuteSelectAndDefine(256);
        
        c_SdoGeomToAGF2 sdotoagf;
        while (stm->ReadNext())
        {
          c_SDO_GEOMETRY*geom = stm->GetSdoGeom(1);
          
          sdotoagf.SetGeometry(geom);      
          sdotoagf.ToAGF();
          delete geom;
          
          method1_rows_fetched++;
          rows_processed++;
          
        }

        
        ociconn->TerminateStatement(stm);
        
        

    }

    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    method1_secs=msecs/1000.0;
  }
  
  // Method 2
  {    
    
    c_Oci_Statement* stm = ociconn->CreateStatement();
        
    stm->Prepare(sql.c_str(),0);
    stm->ExecuteSelectAndDefine(256);
    while (stm->ReadNext());
    
    clock_t elog_t1=clock();
      
    for(int loop=0;loop<loop_count;loop++)      
    {
        stm->ExecuteSelect(256);
        
        c_SdoGeomToAGF2 sdotoagf;
        while (stm->ReadNext())
        {
          c_SDO_GEOMETRY*geom = stm->GetSdoGeom(1);
          
          sdotoagf.SetGeometry(geom);      
          sdotoagf.ToAGF();
          delete geom;
          
          method2_rows_fetched++;
          rows_processed++;
          
        }

        
        
    }

    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    method2_secs=msecs/1000.0;
    
    ociconn->TerminateStatement(stm);
  }

    
    c_OCI_API::CloseConnection(ociconn);
    c_OCI_API::OciTerminate();
   
   
    
    char buff[128];
    sprintf(buff,"Ellapsed: Method1: %.2lfs (%ld) Method2: %.2lfs (%ld) ",method1_secs,method1_rows_fetched,method2_secs,method2_rows_fetched);
    CPPUNIT_FAIL( buff );
    
    
    

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
 
}//end of void ut_OCI::OCI_ReadGeoms2

void ut_OCI::OCI_DescribeScema()
{
  try
  {
    c_OCI_API::OciInit();
    c_Oci_Connection* ociconn = OCI_CreateConnection();

    FdoPtr<c_KgOraSchemaDesc> schema = c_FdoOra_API3::DescribeSchema(ociconn,L"UNITTEST",L"UNITTEST",NULL,NULL);

    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
  catch(...)
  {

    CPPUNIT_FAIL( "General exception" );
  }
}//end of ut_OCI::OCI_DescribeScema()

void ut_OCI::OCI_Test_c_Ora_Api()
{
   try
  {
    c_OCI_API::OciInit();
    c_Oci_Connection* ociconn = c_OCI_API::CreateConnection(L"unittest",L"unittest",L"//temp10gr2/ora10gr2");


    long srid = c_Ora_API2::GetSrid(ociconn,L"ELD79 / UTM zone 34N");
    
    std::wstring wkt;
    if( !c_Ora_API2::GetCoordinateSystemWkt(ociconn,srid,wkt) )
    {
      throw new c_Oci_Exception(0,0,L"ut_OCI::OCI_GetSrid Unable to find SRID ");
    }


    FdoInt64 val1 = c_Ora_API2::GetSequenceNextVal(ociconn,L"TEST1_FDOSEQ");
    FdoInt64 val2 = c_Ora_API2::GetSequenceNextVal(ociconn,L"TEST1_FDOSEQ");
    
    if( val2 != (val1+1) )
    {
      throw new c_Oci_Exception(0,0,L"ut_OCI::OCI_GetSrid : Invalid sequence value!");
    }
    
    int mainver,subver;
    c_Ora_API2::GetOracleVersion(ociconn,mainver,subver);
    
    std::vector<std::wstring> pcols;
    c_Ora_API2::GetTablePkeyColumns(ociconn,L"UNITTEST",L"TEST1",pcols);
    
    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
  catch(...)
  {

    CPPUNIT_FAIL( "General exception" );
  }
}




void ut_OCI::OCI_CreateTableData()
{
   try
  {
    c_OCI_API::OciInit();
    c_Oci_Connection* ociconn = OCI_CreateConnection();

    PrepareTable2D(ociconn,L"TABLE1");

    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
  catch(...)
  {

    CPPUNIT_FAIL( "General exception" );
  }
  
}


void ut_OCI::PrepareTable2D(c_Oci_Connection* Connection,const wchar_t*TableName)
{
 
  // create test table with different data types
  c_Oci_Statement*sqlcomm = Connection->CreateStatement();
  wchar_t buff[512];

try
{  
  wsprintf(buff,L"DROP TABLE %s",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}  
catch(c_Oci_Exception*ex )
{
  delete ex;
} 
try
{  
  // drop sequnce
  wsprintf(buff,L"DROP SEQUENCE %s_FDOSEQ",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{ 
  wsprintf(buff,L"CREATE TABLE %s( \
                          fid NUMBER(10,0) \
                          ,geom SDO_GEOMETRY,val_string VARCHAR2(100),val_double NUMBER(20,5),val_date DATE )",TableName); 
  sqlcomm->Prepare(buff);
                       
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // create primary key
  wsprintf(buff,L"ALTER TABLE %s ADD CONSTRAINT %s_pk PRIMARY KEY (fid)",TableName,TableName);
                          
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // create sequnce
  wsprintf(buff,L"CREATE SEQUENCE %s_FDOSEQ",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // delete user_sdo_geom_metadata
  wsprintf(buff,L"DELETE user_SDO_GEOM_METADATA WHERE table_name='%s'",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}
catch(c_Oci_Exception* ex )
{
  delete ex;
}
try
{  
  // insert user_sdo_geom_metadata
  wsprintf(buff,L"INSERT INTO user_SDO_GEOM_METADATA  VALUES ('%s'\
                      , 'GEOM',SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -1000000, 1000000, 0.005),SDO_DIM_ELEMENT('Y', -1000000, 1000000, 0.005))\
                      ,NULL)",TableName);
  sqlcomm->Prepare(buff);

  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}
catch(c_Oci_Exception* ex )
{
  delete ex;
}
try
{  
//-- Create index
  wsprintf(buff,L"CREATE INDEX %s_sid ON %s (\"GEOM\")\
                            INDEXTYPE IS \"MDSYS\".\"SPATIAL_INDEX\" PARAMETERS ('sdo_indx_dims=2')",TableName,TableName);
  sqlcomm->Prepare(buff);
  
  sqlcomm->ExecuteNonQuery();
}
catch(c_Oci_Exception* ex )
{
  FdoStringP fdostr( ex->GetErrorText());
  delete ex;
  CPPUNIT_FAIL( (const char*)fdostr );
} 

  Connection->TerminateStatement(sqlcomm);
 
}

void ut_OCI::PrepareTable3D(c_Oci_Connection* Connection,const wchar_t*TableName)
{
 
  // create test table with different data types
  c_Oci_Statement*sqlcomm = Connection->CreateStatement();
  wchar_t buff[512];

try
{  
  wsprintf(buff,L"DROP TABLE %s",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}  
catch(c_Oci_Exception*ex )
{
  delete ex;
} 
try
{  
  // drop sequnce
  wsprintf(buff,L"DROP SEQUENCE %s_FDOSEQ",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{ 
  wsprintf(buff,L"CREATE TABLE %s( \
                          fid NUMBER(10,0) \
                          ,geom SDO_GEOMETRY,val_string VARCHAR2(100),val_double NUMBER(20,5),val_date DATE )",TableName); 
  sqlcomm->Prepare(buff);
                       
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // create primary key
  wsprintf(buff,L"ALTER TABLE %s ADD CONSTRAINT %s_pk PRIMARY KEY (fid)",TableName,TableName);
                          
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // create sequnce
  wsprintf(buff,L"CREATE SEQUENCE %s_FDOSEQ",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery();
}  
catch(c_Oci_Exception* ex )
{
  delete ex;
} 
try
{  
  // delete user_sdo_geom_metadata
  wsprintf(buff,L"DELETE user_SDO_GEOM_METADATA WHERE table_name='%s'",TableName);
  sqlcomm->Prepare(buff);
  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}
catch(c_Oci_Exception* ex )
{
  delete ex;
}
try
{  
  // insert user_sdo_geom_metadata
  wsprintf(buff,L"INSERT INTO user_SDO_GEOM_METADATA  VALUES ('%s'\
                      , 'GEOM',SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -1000000, 1000000, 0.005),SDO_DIM_ELEMENT('Y', -1000000, 1000000, 0.005),SDO_DIM_ELEMENT('Z', -1000000, 1000000, 0.1))\
                      ,NULL)",TableName);
  sqlcomm->Prepare(buff);

  sqlcomm->ExecuteNonQuery(OCI_COMMIT_ON_SUCCESS);
}
catch(c_Oci_Exception* ex )
{
  delete ex;
}
try
{  
//-- Create index
  wsprintf(buff,L"CREATE INDEX %s_sid ON %s (\"GEOM\")\
                            INDEXTYPE IS \"MDSYS\".\"SPATIAL_INDEX\" PARAMETERS ('sdo_indx_dims=3')",TableName,TableName);
  sqlcomm->Prepare(buff);
  
  sqlcomm->ExecuteNonQuery();
}
catch(c_Oci_Exception* ex )
{
  FdoStringP fdostr( ex->GetErrorText());
  delete ex;
  CPPUNIT_FAIL( (const char*)fdostr );
} 

  Connection->TerminateStatement(sqlcomm);
 
}

void ut_OCI::OCI_TestDefine()
{
  c_Oci_Connection* ociconn=NULL;
  c_Oci_Statement* stm=NULL;
   try
  {
    c_OCI_API::OciInit();
    ociconn = OCI_CreateConnection();

    c_Oci_Statement* stm = ociconn->CreateStatement();

    stm->Prepare(L"select sdo_diminfo,sdo_table_name,NULL as col3 from mdsys.sdo_geom_metadata_table");
    stm->ExecuteSelectAndDefine(10);
    c_SDO_DIM_ARRAY dimarray;
    while( stm->ReadNext() )
    {
      dimarray = stm->GetSdoDimArray(1);;
      /*
      OCIArray* dimarray = stm->GetSdoDimArray(1);
      boolean exists;
      SDO_DIM_ELEMENT *dimelem;
      SDO_DIM_ELEMENT_ind *dimelem_ind;
      OCICollGetElem(ociconn->m_OciHpEnvironment,ociconn->m_OciHpError,dimarray,0,&exists,(void**)&dimelem,(void**)&dimelem_ind);
      
      wchar_t* dimname = (wchar_t*)OCIStringPtr(ociconn->m_OciHpEnvironment, dimelem->SDO_DIMNAME);
      */
      const wchar_t* tab_name = stm->GetString(2);
      
      CPPUNIT_ASSERT_MESSAGE( "Column 3 is not NULL" , stm->IsColumnNull(3) == true );
      
      int a;
      a=5;
    }
    
  
    ociconn->TerminateStatement(stm);
    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    if( ociconn && stm ) ociconn->TerminateStatement(stm);
    if( ociconn ) c_OCI_API::CloseConnection(ociconn);
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }

}

void ut_OCI::OCI_Test_c_FdoOra_API2()
{
  c_Oci_Connection* ociconn=NULL;
  c_Oci_Statement* stm=NULL;
   try
  {
    c_OCI_API::OciInit();
    ociconn = OCI_CreateConnection();

    //FdoPtr<c_KgOraSchemaDesc> fdoschema = c_FdoOra_API2::DescribeSchema(ociconn,L"UNITTEST",NULL);
    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    if( ociconn && stm ) ociconn->TerminateStatement(stm);
    if( ociconn ) c_OCI_API::CloseConnection(ociconn);
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
}

void ut_OCI::OCI_Test_Bind()
{
  c_Oci_Connection* con=NULL;
  c_Oci_Statement* stm=NULL;
  try
  {
    c_OCI_API::OciInit();
    con = OCI_CreateConnection();

    c_Oci_Statement* stm = con->CreateStatement();
    
    
    PrepareTable2D(con,L"TABLE1");
    
    
    
    
    stm->Prepare(L"INSERT INTO TABLE1(fid,geom,val_double,val_string,val_date) VALUES(:1,:2,:3,:4,:5)");
    
    
    int varnum=1;
    
    long val_long=12;
    stm->BindLong(varnum++,&val_long);
    
    
    c_SDO_GEOMETRY*geom = c_SDO_GEOMETRY::Create(con);
    //geom->SetSdoSrid(0);
    //geom->SetSdoGtype(2003);
    stm->BindSdoGeomValue(varnum++,geom);
    
    stm->BindDouble(varnum++,NULL);
    
    const wchar_t* val_string = L"123456789 ";
    stm->BindString(varnum++,NULL);
    
    
    OCIDate date;
    date.OCIDateYYYY = 2008;
    date.OCIDateMM = 11;
    date.OCIDateDD = 14;
    date.OCIDateTime.OCITimeHH = 17;
    date.OCIDateTime.OCITimeMI = 37;
    date.OCIDateTime.OCITimeSS = 30;
    stm->BindDate(varnum++,&date);
    
    stm->ExecuteNonQuery();
    
    
    
    stm->Prepare(L"DELETE USER_SDO_GEOM_METADATA WHERE table_name='TEMPX'");
    stm->ExecuteNonQuery();
    
    
    std::wstring sqlstr;
    
    sqlstr = L"INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'TEMPX','GEOMX',MDSYS.SDO_DIM_ARRAY(:1,:2),:3)";
    
    stm->Prepare(sqlstr.c_str());
    
    c_SDO_DIM_ELEMENT* xdim_sdoelem = c_SDO_DIM_ELEMENT::Create(con);
    xdim_sdoelem->SetDimName(L"X");
    xdim_sdoelem->SetLB(1);
    xdim_sdoelem->SetUB(2);
    xdim_sdoelem->SetTolerance(0.1);
    stm->BindSdoDimElement(1,xdim_sdoelem);
    
    c_SDO_DIM_ELEMENT* ydim_sdoelem = c_SDO_DIM_ELEMENT::Create(con);
    ydim_sdoelem->SetDimName(L"Y");
    ydim_sdoelem->SetLB(3);
    ydim_sdoelem->SetUB(4);
    ydim_sdoelem->SetTolerance(0.2);
    stm->BindSdoDimElement(2,ydim_sdoelem);
    
    //c_SDO_DIM_ELEMENT* zdim_sdoelem = c_SDO_DIM_ELEMENT::CreateNull(con);
    //stm->BindSdoDimElement(3,zdim_sdoelem);
    
    stm->BindLongValue(3,2003);
    
    stm->ExecuteNonQuery();
    
    delete xdim_sdoelem;
    delete ydim_sdoelem;
    
    
    
    con->TerminateStatement(stm);
    c_OCI_API::CloseConnection(con);
    
    c_OCI_API::OciTerminate();

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    
    if( con && stm ) con->TerminateStatement(stm);
    if( con ) c_OCI_API::CloseConnection(con);
    
    c_OCI_API::OciTerminate();
    CPPUNIT_FAIL( (const char*)fdostr );

  }
}


void GenerateRandomArrayInt(int*& Array,int count,int min,int max)
{
  srand( (unsigned)time( NULL ) );
  
  for (int ind = 0; ind < count; ind++ )
  {
    int randnum;
    bool isnew=false;
     
    while( !isnew )
    {
      randnum = ((double) rand() / (double) RAND_MAX) * max + min;
      int ind2=0;
      isnew = true;
      while(ind2<ind)
      {
        if( Array[ind2] == randnum ) { isnew = false; break; }
        ind2++;
      }
    } 
    
    Array[ind] = randnum;  
  }


}

void ut_OCI::OCI_Test_ColumnNameLookup()
{
  std::vector<wchar_t*> m_Names;
  
  m_Names.push_back(L"Name1");
  m_Names.push_back(L"Name2");
  m_Names.push_back(L"Name3");
  m_Names.push_back(L"Name4");
  m_Names.push_back(L"Name5");
  m_Names.push_back(L"Name6");
  m_Names.push_back(L"Name7");
  m_Names.push_back(L"Name8");
  m_Names.push_back(L"Name9");
  m_Names.push_back(L"Name10");
  m_Names.push_back(L"Name11");
  m_Names.push_back(L"Name12");
  m_Names.push_back(L"Name13");
  m_Names.push_back(L"Name14");
  m_Names.push_back(L"Name15");
  m_Names.push_back(L"Name16");
  m_Names.push_back(L"Name17");
  m_Names.push_back(L"Name18");
  m_Names.push_back(L"Name19");
  m_Names.push_back(L"Name20");
  m_Names.push_back(L"Name21");
  m_Names.push_back(L"Name22");
  m_Names.push_back(L"Name23");
  m_Names.push_back(L"Name24");
  m_Names.push_back(L"Name25");
  m_Names.push_back(L"Name26");
  m_Names.push_back(L"Name27");
  m_Names.push_back(L"Name28");
  m_Names.push_back(L"Name29");
  
  int count_names = m_Names.size();
  
  std::map<std::wstring,int> namesmap;
  for(long ind = 0; ind<count_names;ind++ )
  {
    namesmap[m_Names.at(ind)] = ind+1;
  }
  
  double map_secs,vector_secs,vector2_secs;
  {
    clock_t elog_t1=clock();
    
    int oraind;
    for(int ind=0;ind<21000;ind++)
    {
      for( int ind2=0;ind2<count_names;ind2++)
      {
        std::map<std::wstring,int>::iterator iter = namesmap.find(m_Names[ind2]);
        if( iter != namesmap.end() )
        {
          oraind = iter->second;  
        }
      }
    }
    
    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    map_secs = msecs/1000.0;
  }
  long method1_count_strcmp=0,method1_not_found=0; 
  long method2_count_strcmp=0,method2_not_found=0;
  {    
    // create random array of indexes of names i nwhich order they will be searched for
    int *array_ind_serach = new int[count_names];
        
    for(int ind=0;ind<count_names;ind++)
    {
      array_ind_serach[ind] = ind;
    }
    
    GenerateRandomArrayInt(array_ind_serach,count_names,0,count_names-1);
    
    clock_t elog_t1=clock();
    
    int oraind,ind_search;
    for(int ind=0;ind<21000;ind++)
    {
      for( int ind2=0;ind2<count_names;ind2++)
      {
        ind_search = array_ind_serach[ind2];
        
        bool found=false;
        int ind3=0;
        while((ind3 < count_names) && !found)
        {
          method1_count_strcmp++;
          if( wcscmp(m_Names[ind3],m_Names[ind_search]) == 0 )
          {
            found = true;
            break;
          }
          ind3++;
        }
        oraind = ind3+1;
        if( !found ) CPPUNIT_FAIL("Not found");
      }
    }
    
    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    vector_secs = msecs/1000.0;
  }
 {    
    // create random array of indexes of names i nwhich order they will be searched for
    int *array_ind_serach = new int[count_names];        
    for(int ind=0;ind<count_names;ind++)
    {
      array_ind_serach[ind] = ind;
    }
    
    GenerateRandomArrayInt(array_ind_serach,count_names,0,count_names-1);
    
    
    int *array_ind_lookup = new int[count_names];        
    for(int ind=0;ind<count_names;ind++)
    {
      array_ind_lookup[ind] = ind;
    }
    int curr_ind_lookup=0;
    
    clock_t elog_t1=clock();
    
    int oraind,ind_search;
    for(int ind=0;ind<21000;ind++)
    {
      for( int ind2=0;ind2<count_names;ind2++)
      {
        ind_search = array_ind_serach[ind2];
        
        
        int ind3=0;
        // check on next lookup ind
        ind3 = array_ind_lookup[curr_ind_lookup];
        method2_count_strcmp++;
        if( wcscmp(m_Names[ind3],m_Names[ind_search]) == 0 )
        {
          curr_ind_lookup++;
          if( curr_ind_lookup >= count_names ) curr_ind_lookup=0;
          
          oraind = ind3 + 1;
        }
        else
        {
          int next_ind_lookup = curr_ind_lookup + 1;
          if( next_ind_lookup>= count_names ) next_ind_lookup = 0;
          
          bool found=false;
          while( next_ind_lookup!=curr_ind_lookup)
          {
            ind3 = array_ind_lookup[next_ind_lookup];
            method2_count_strcmp++;
            if( wcscmp(m_Names[ind3],m_Names[ind_search]) == 0 )
            {
              int temp = array_ind_lookup[curr_ind_lookup];
              array_ind_lookup[curr_ind_lookup] = array_ind_lookup[next_ind_lookup];
              array_ind_lookup[next_ind_lookup] = temp;
            
              curr_ind_lookup++;
              if( curr_ind_lookup >= count_names ) curr_ind_lookup=0;
              found = true;
              break;
            }
            
            next_ind_lookup++;
            if( next_ind_lookup>= count_names ) next_ind_lookup = 0;
          }
          
          if( !found ) 
          {
            bool found_test=false;
            int ind_test=0;
            while(ind_test<count_names)
            {
              if( array_ind_lookup[ind_test] == ind_search )
              {
                found_test=true;
                break;
              }
              ind_test++;
            }
            if( !found_test )
            {
              CPPUNIT_FAIL("Method2 : bad array 'array_ind_lookup' ");
            }
            CPPUNIT_FAIL("Method2 : Not found");
          }
          
          oraind = ind3+1;
        }
        
        
      }
    }
    
    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    vector2_secs = msecs/1000.0;
  }
  
  char buff[256];
  sprintf(buff,"Ellapsed Map: %.2lfs ; Vector: %.2lf (%ld); Vector2: %.2lf (%ld)",map_secs,vector_secs,method1_count_strcmp,vector2_secs,method2_count_strcmp);
  CPPUNIT_FAIL( buff );
}//end of OCI_Test_ColumnNameLookup

void ut_OCI::OCI_TestSpeed_DescribeScema()
{
  double m1_secs;
  
  try
  {
    c_OCI_API::OciInit();
    c_Oci_Connection* ociconn = OCI_CreateConnection();

    
    
    clock_t elog_t1=clock();
    
    FdoPtr<c_KgOraSchemaDesc> schema = c_FdoOra_API3::DescribeSchema(ociconn,L"UNITTEST",L"UNITTEST",NULL,NULL);

    
    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    m1_secs = msecs/1000.0;
    
    c_OCI_API::CloseConnection(ociconn);
    
    c_OCI_API::OciTerminate();
    
    char buff[256];
    sprintf(buff,"Elapsed : %.2lfs ; ",m1_secs);
    CPPUNIT_FAIL( buff );  

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }
    
}


void ut_OCI::OCI_ReadGeomsSameConn()
{
  int rows_processed=0;
  
  try
  {
    
    //c_Oci_Connection* ociconn = c_OCI_API::CreateConnection(L"unittest",L"unittest",L"//test10gxe/xe");
    

    //std::wstring sql( L"SELECT AUTOGENERATED_SDF_ID,shpgeom FROM parcels" );
    //std::wstring sql( L"SELECT featid,geometry FROM bldg_polygon" );
    //std::wstring sql( L"SELECT geometry,bldg_polygon.*  FROM bldg_polygon " );
    std::wstring sql( L"SELECT geometry,featid  FROM bldg_polygon " );
    
    double method1_secs,method2_secs;
    long method1_rows_fetched=0,method2_rows_fetched=0;
    int loop_count=1;

  {    
    clock_t elog_t1=clock();
      
    for(int loop=0;loop<loop_count;loop++)      
    {
        c_Oci_Statement* stm = m_OciConn->CreateStatement();
        
        stm->Prepare(sql.c_str(),0);
        stm->ExecuteSelectAndDefine(256);
        
        c_SdoGeomToAGF2 sdotoagf;
        while (stm->ReadNext())
        {
          c_SDO_GEOMETRY*geom = stm->GetSdoGeom(1);
          
          sdotoagf.SetGeometry(geom);      
          sdotoagf.ToAGF();
          delete geom;
          
          method1_rows_fetched++;
          rows_processed++;
          
        }

        
        m_OciConn->TerminateStatement(stm);
        
        

    }

    clock_t elog_t2=clock();
    double msecs = (double)((elog_t2-elog_t1)* CLOCKS_PER_SEC / 1000);
    method1_secs=msecs/1000.0;
  }
 
    
    char buff[128];
    sprintf(buff,"Ellapsed: Method1: %.2lfs (%ld)  ",method1_secs,method1_rows_fetched);
    CPPUNIT_FAIL( buff );
    
    
    

  }
  catch(c_Oci_Exception* exc)
  {
    FdoStringP fdostr( exc->GetErrorText());
    delete exc;
    CPPUNIT_FAIL( (const char*)fdostr );

  }

}

void ut_OCI::setUp()
{  
  c_OCI_API::OciInit();
   m_OciConn = OCI_CreateConnection();
}

void ut_OCI::tearDown()
{
  c_OCI_API::CloseConnection(m_OciConn);
  c_OCI_API::OciTerminate();
}
