void CAcqHive::RebuildTable(const std::string& tab_name, std::vector<std::string>& vec_field, const std::string& tab_location) throw(base::Exception)
{
if ( tab_name.empty() )
{
throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] Can not rebuild! 库表名为空! [FILE:%s, LINE:%d]", __FILE__, __LINE__);
}
if ( vec_field.empty() )
{
throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] 没有库表 [%s] 的字段信息! [FILE:%s, LINE:%d]", tab_name.c_str(), __FILE__, __LINE__);
}
std::string sql_drop = "DROP TABLE IF EXISTS " + tab_name;
std::string sql_create = "CREATE TABLE " + tab_name + " ( ";
const size_t VEC_SIZE = vec_field.size();
for ( size_t i = 0; i < VEC_SIZE; ++i )
{
std::string& str_field = vec_field[i];
if ( str_field.empty() )
{
throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] 缺少库表 [%s] 的第 [%lu] 个字段的信息! [FILE:%s, LINE:%d]", tab_name.c_str(), (i+1), __FILE__, __LINE__);
}
if ( i != 0 )
{
sql_create += ", " + str_field + " string";
}
else
{
sql_create += str_field + " string";
}
}
sql_create += " ) row format delimited fields terminated by '|' stored as textfile";
// 测试环境：不指定建表的 location
if ( !tab_location.empty() )
{
// 指定建表的 location
sql_create += " location '" + tab_location + tab_name + "'";
}
try
{
m_pLog->Output("[HIVE] Try to drop table: %s", tab_name.c_str());
ExecuteSQL(sql_drop);
m_pLog->Output("[HIVE] Drop table OK.");
m_pLog->Output("[HIVE] Create table: %s", tab_name.c_str());
ExecuteSQL(sql_create);
m_pLog->Output("[HIVE] Create table ---- done!");
}
catch ( const base::Exception& ex )
{
throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] Rebuild table \"%s\" failed! %s [FILE:%s, LINE:%d]", tab_name.c_str(), ex.What().c_str(), __FILE__, __LINE__);
}
}
/*!
\brief Load feature properties from DB
\param poFeature pointer to OGR feature
\return OGRERR_NONE on success or OGRERR_FAILURE on failure
*/
OGRErr VFKFeatureSQLite::LoadProperties(OGRFeature *poFeature)
{
CPLString osSQL;
osSQL.Printf("SELECT * FROM %s WHERE rowid = %d",
m_poDataBlock->GetName(), m_iRowId);
if (ExecuteSQL(osSQL.c_str()) != OGRERR_NONE)
return OGRERR_FAILURE;
for (int iField = 0; iField < m_poDataBlock->GetPropertyCount(); iField++) {
if (sqlite3_column_type(m_hStmt, iField) == SQLITE_NULL) /* skip null values */
continue;
OGRFieldType fType = poFeature->GetDefnRef()->GetFieldDefn(iField)->GetType();
if (fType == OFTInteger)
poFeature->SetField(iField,
sqlite3_column_int(m_hStmt, iField));
else if (fType == OFTReal)
poFeature->SetField(iField,
sqlite3_column_double(m_hStmt, iField));
else
poFeature->SetField(iField,
(const char *) sqlite3_column_text(m_hStmt, iField));
}
FinalizeSQL();
return OGRERR_NONE;
}
void svStorageEnginePostgreSQL::GetHostKey(
const string &dev, const string &org, svHostKey &key)
{
string sql(sql_query_hostkey);
string value(dev);
EscapeString(value);
if (PrepareSQL(sql, "%d", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: device");
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ExecuteSQL(sql);
if (PQresultStatus(pg_res) != PGRES_TUPLES_OK) {
throw svExStorageQuery(string("PQexec: ") +
PQresultErrorMessage(pg_res));
}
if (PQntuples(pg_res) == 0)
throw svExStorageRecordNotFound(name + ": Device not found: " + dev);
key.SetAge(time(NULL));
key.SetKey(PQgetvalue(pg_res, 0, 0));
PQclear(pg_res);
pg_res = NULL;
}
void svStorageEngineMySQL::GetHostKey(
const string &dev, const string &org, svHostKey &key)
{
string sql(sql_query_hostkey);
string value(dev);
EscapeString(value);
if (PrepareSQL(sql, "%d", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: device");
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ExecuteSQL(sql);
if (mysql_num_rows(mysql_res) == 0)
throw svExStorageRecordNotFound(name + ": Device not found: " + dev);
MYSQL_ROW row;
if (!(row = mysql_fetch_row(mysql_res))) {
throw svExStorageQuery(name +
string(": mysql_fetch_row: ") + mysql_error(mysql_conn));
}
key.SetAge(time(NULL));
key.SetKey(row[0]);
mysql_free_result(mysql_res);
mysql_res = NULL;
}
void svStorageEngineMySQL::InsertPoolClient(const string &node,
const string &pool_name, const string &dev,
const string &org, svPoolClientState state)
{
if (sql_insert_pool_client.size() == 0)
throw svExStorageQuery("No SQL query set: insert-pool-client");
string sql(sql_insert_pool_client);
string value(dev);
EscapeString(value);
if (PrepareSQL(sql, "%d", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: device");
value = node;
EscapeString(value);
PrepareSQL(sql, "%n", value);
value = pool_name;
EscapeString(value);
PrepareSQL(sql, "%p", value);
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ostringstream os;
map<svPoolClientState, string>::iterator i;
i = pool_client_state.find(state);
if (i == pool_client_state.end()) os << state;
else os << i->second;
PrepareSQL(sql, "%s", os.str());
ExecuteSQL(sql);
}
/*!
\brief Load data block definitions (&B)
Call VFKReader::OpenFile() before this function.
\return number of data blocks or -1 on error
*/
int VFKReaderSQLite::ReadDataBlocks()
{
int nDataBlocks = -1;
CPLString osSQL;
const char *pszName, *pszDefn;
IVFKDataBlock *poNewDataBlock;
sqlite3_stmt *hStmt;
osSQL.Printf("SELECT table_name, table_defn FROM %s", VFK_DB_TABLE);
hStmt = PrepareStatement(osSQL.c_str());
while(ExecuteSQL(hStmt) == OGRERR_NONE) {
pszName = (const char*) sqlite3_column_text(hStmt, 0);
pszDefn = (const char*) sqlite3_column_text(hStmt, 1);
poNewDataBlock = (IVFKDataBlock *) CreateDataBlock(pszName);
poNewDataBlock->SetGeometryType();
poNewDataBlock->SetProperties(pszDefn);
VFKReader::AddDataBlock(poNewDataBlock, NULL);
}
if (m_nDataBlockCount == 0) {
CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "BEGIN", NULL, NULL, NULL));
/* CREATE TABLE ... */
nDataBlocks = VFKReader::ReadDataBlocks();
CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "COMMIT", NULL, NULL, NULL));
StoreInfo2DB();
}
return nDataBlocks;
}
void svStorageEngineMySQL::UpdatePoolClient(const string &node,
const string &pool_name, const string &dev,
const string &org, svPoolClientState state)
{
if (sql_update_pool_client.size() == 0)
throw svExStorageQuery("No SQL query set: update-pool-client");
string sql(sql_update_pool_client);
string value(dev);
EscapeString(value);
if (PrepareSQL(sql, "%d", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: device");
value = node;
EscapeString(value);
PrepareSQL(sql, "%n", value);
value = pool_name;
EscapeString(value);
PrepareSQL(sql, "%p", value);
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ostringstream os;
map<svPoolClientState, string>::iterator i;
i = pool_client_state.find(state);
if (i == pool_client_state.end()) os << state;
else os << i->second;
PrepareSQL(sql, "%s", os.str());
ExecuteSQL(sql);
my_ulonglong affected_rows = mysql_affected_rows(mysql_conn);
if (affected_rows == 0)
throw svExStorageRecordNotFound("Pool client not found");
}
/*!
\brief Create DB table from VFKDataBlock (SQLITE only)
\param poDataBlock pointer to VFKDataBlock instance
*/
void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn)
{
CPLString osCommand, osColumn;
VFKPropertyDefn *poPropertyDefn;
sqlite3_stmt *hStmt;
/* register table in 'vfk_blocks' */
osCommand.Printf("SELECT COUNT(*) FROM vfk_blocks WHERE "
"table_name = '%s'",
poDataBlock->GetName());
hStmt = PrepareStatement(osCommand.c_str());
if (ExecuteSQL(hStmt) == OGRERR_NONE &&
sqlite3_column_int(hStmt, 0) == 0) {
osCommand.Printf("CREATE TABLE '%s' (", poDataBlock->GetName());
for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
poPropertyDefn = poDataBlock->GetProperty(i);
if (i > 0)
osCommand += ",";
osColumn.Printf("%s %s", poPropertyDefn->GetName(),
poPropertyDefn->GetTypeSQL().c_str());
osCommand += osColumn;
}
osColumn.Printf(",%s integer", FID_COLUMN);
osCommand += osColumn;
if (poDataBlock->GetGeometryType() != wkbNone) {
osColumn.Printf(",%s blob", GEOM_COLUMN);
osCommand += osColumn;
}
osCommand += ")";
ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */
osCommand.Printf("INSERT INTO 'vfk_blocks' (file_name, table_name, "
"num_records, num_geometries, table_defn) VALUES "
"('%s', '%s', -1, 0, '%s')",
m_pszFilename, poDataBlock->GetName(), pszDefn);
ExecuteSQL(osCommand.c_str());
sqlite3_finalize(hStmt);
}
return VFKReader::AddDataBlock(poDataBlock, NULL);
}
/*!
\brief Create index
If creating unique index fails, then non-unique index is created instead.
\param name index name
\param table table name
\param column column(s) name
\param unique TRUE to create unique index
*/
void VFKReaderSQLite::CreateIndex(const char *name, const char *table, const char *column,
bool unique)
{
CPLString osSQL;
if (unique) {
osSQL.Printf("CREATE UNIQUE INDEX %s ON %s (%s)",
name, table, column);
if (ExecuteSQL(osSQL.c_str()) == OGRERR_NONE) {
return;
}
}
osSQL.Printf("CREATE INDEX %s ON %s (%s)",
name, table, column);
ExecuteSQL(osSQL.c_str());
}
/*!
\brief VFKReaderSQLite constructor
*/
VFKReaderSQLite::VFKReaderSQLite(const char *pszFilename) : VFKReader(pszFilename)
{
const char *pszDbNameConf;
CPLString pszDbName;
CPLString osCommand;
VSIStatBufL sStatBuf;
bool bNewDb;
/* open tmp SQLite DB (re-use DB file if already exists) */
pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", NULL);
if (pszDbNameConf) {
pszDbName = pszDbNameConf;
}
else {
pszDbName.Printf("%s.db", m_pszFilename);
}
if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES")))
m_bSpatial = TRUE; /* build geometry from DB */
else
m_bSpatial = FALSE; /* store also geometry in DB */
bNewDb = TRUE;
if (VSIStatL(pszDbName, &sStatBuf ) == 0) {
if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO"))) {
bNewDb = TRUE; /* overwrite existing DB */
VSIUnlink(pszDbName);
}
else {
bNewDb = FALSE; /* re-use exising DB */
}
}
else {
CPLError(CE_Warning, CPLE_AppDefined,
"SQLite DB not found. Reading VFK data may take some time...");
}
CPLDebug("OGR-VFK", "New DB: %s Spatial: %s",
bNewDb ? "yes" : "no", m_bSpatial ? "yes" : "no");
if (SQLITE_OK != sqlite3_open(pszDbName, &m_poDB)) {
CPLError(CE_Failure, CPLE_AppDefined,
"Creating SQLite DB failed");
}
else {
char* pszErrMsg = NULL;
sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg);
sqlite3_free(pszErrMsg);
}
if (bNewDb) {
/* new DB, create support metadata tables */
osCommand = "CREATE TABLE 'vfk_blocks' "
"(file_name text, table_name text, num_records integer, "
"num_geometries integer, table_defn text)";
ExecuteSQL(osCommand.c_str());
}
}
BOOL Sqlite3DB::ExecuteSQL( LPCWSTR unicodeSql, int* pAffectedRows /*= NULL*/ )
{
std::string utf8Sql;
if (! Unicode2Utf8(unicodeSql, utf8Sql))
{
return FALSE;
}
return ExecuteSQL(utf8Sql.c_str(), pAffectedRows);
}
bool Database::RollbackTransaction()
{
if (!IsOpen())
{
m_iLastError=SQLITE_ERROR;
return false;
}
m_iLastError = ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return m_iLastError==SQLITE_OK;
}
bool Database::CommitTransaction()
{
if (!IsOpen())
{
m_iLastError=SQLITE_ERROR;
return false;
}
m_iLastError = ExecuteSQL(_T("COMMIT TRANSACTION"));
return m_iLastError==SQLITE_OK;
}
bool Database::BeginTransaction()
{
if (!IsOpen())
{
m_iLastError=SQLITE_ERROR;
return false;
}
m_iLastError = ExecuteSQL(_T("BEGIN TRANSACTION"));
return m_iLastError==SQLITE_OK;
}
//*========================================================================================
//*函数: bool CSmartJZSRCTable::SvaeToDB(CString strSQL)
//*功能: 保存到数据库
//*参数: 略
//*返回: 成功与否
//*说明: 虚基类程序
//*========================================================================================
bool CSmartJZSRCTable::SaveToDB(CString strSQL1, CString strSQL2)
{
long nDatabase = GetDBHandle(DATABASE_NORMAL);
if( nDatabase <= 0 )
{
ReportLog(RET_DBBUSY, "保存扎帐数据", "不能得到数据库句柄!");
return false;
}
// printf("%s\n", strSQL1.GetBuffer(0));
long nRec = ExecuteSQL(nDatabase, strSQL1.GetBuffer(0));
if( nRec != RET_OK )
{
printf("%s\n", strSQL1.GetBuffer(0));
CString strText;
strText = "create table " + m_strTableName ;
strText += "(smt_rowid integer not null primary key,smt_authno number(10,0) not null,";
strText += "smt_data varchar(100) not null,smt_CRC number(2) , smt_datetime date ,";
strText += "smt_dealwith number(2),smt_dealwithdatetime date,smt_adjustcode varchar(4)) ";
ExecuteSQL(nDatabase, strText.GetBuffer(0));
strText = "create sequence " + m_strTableName + "_rowid start with 1 increment by 1";
ExecuteSQL(nDatabase, strText.GetBuffer(0));
if( ExecuteSQL(nDatabase, strSQL1.GetBuffer(0)) != RET_OK )
{
FreeDBHandle(nDatabase);
return false;
}
}
// printf("%s\n", strSQL2.GetBuffer(0));
nRec = ExecuteSQL(nDatabase, strSQL2.GetBuffer(0));
if( nRec != RET_OK )
{
printf("%s\n", strSQL2.GetBuffer(0));
FreeDBHandle(nDatabase);
return false;
}
if( m_bUpdate )
{
nRec = ExecuteSQL(nDatabase, strUpdate.GetBuffer(0));
if( nRec != RET_OK )
{
printf("%s\n", strUpdate.GetBuffer(0));
FreeDBHandle(nDatabase);
return false;
}
}
FreeDBHandle(nDatabase);
return true;
}
/*!
\brief Read FID from DB
*/
OGRErr VFKFeatureSQLite::SetFIDFromDB()
{
CPLString osSQL;
osSQL.Printf("SELECT %s FROM %s WHERE rowid = %d",
FID_COLUMN, m_poDataBlock->GetName(), m_iRowId);
if (ExecuteSQL(osSQL.c_str()) != OGRERR_NONE)
return OGRERR_FAILURE;
m_nFID = sqlite3_column_int(m_hStmt, 0);
FinalizeSQL();
return OGRERR_NONE;
}
void CUpdateDBThread::DoUpdateDB()
{
CString strSQL;
//如果表不存在则创建
// 2011/07/15-8201-gxx: 本地消费表，保存本地用户结账后的信息
if (!TableExists(_T("LocalConsume")))
{
IBA_LOG0(_T("create table LocalConsume"));
strSQL.Empty();
strSQL = _T("CREATE TABLE `LocalConsume` (\
`submitTime` datetime NOT NULL,\
`netBarId` smallint(6) NOT NULL default '0',\
`refNo` int(11) NOT NULL default '0',\
`serialNo` int(11) default NULL,\
`serialNum` varchar(20) default NULL,\
`name` varchar(20) default NULL,\
`idNumber` varchar(50) default NULL,\
`memberId` int(11) default NULL,\
`creditAmount` int(11) default NULL,\
`consumeAmount` int(11) default NULL,\
`returnAmount` int(11) default NULL,\
`checkinTime` datetime default NULL,\
`checkoutTime` datetime default NULL,");
strSQL += _T("\
`termId` varchar(8) default NULL,\
`ClassId` tinyint(4) default '0',\
`PayType` tinyint(4) default '0',\
`PCClass` tinyint(4) default '0',\
`timeConsume` int(11) default NULL,\
`operator` varchar(30) default NULL,\
`dataStatus` tinyint(4) default NULL,\
`checkData` varchar(32) default NULL,\
PRIMARY KEY (`submitTime`,`netBarId`,`refNo`),\
KEY `lc_serialnum` (`serialNum`),\
KEY `lc_memberId` (`memberId`),\
KEY `lc_termId` (`termId`),\
KEY `lc_name` (`name`),\
KEY `lc_idnumber` (`idNumber`)\
) ENGINE=InnoDB DEFAULT CHARSET=gbk");
ExecuteSQL(strSQL);
}
////////////////////////////////////////////////////////////////////////////////
//with NON_TRANSACTIONAL, it makes durable all of the modifications to active
//incarnations whose PIDs are contained in the pids parameter, regardless of the
//transactional context of the calling thread
//
//with TRANSACTIONAL, it behaves as follows:
//$$ if the invoking thread is accociated with a transcation context, it makes
// durable all state modifications made in the current transactional scope for
// incarnations whose PIDs are contained in the pids parameter, flushing them
// to the underying datastore.
//$$ if the invoking thread is not associated with a transactional context, the
// standard exception TRANSACTION_REQUIRED is raised.
//if the session pool implementation is unable to reconcile the changes and make
//them durable, then the PERSISTENT_STORE standard exception is raised
////////////////////////////////////////////////////////////////////////////////
void
SessionPoolImpl::flush_by_pids(const PidList& pids)
{
DEBUG_OUT("CatalogBaseImpl::flush_by_pids() is called");
if( !CanTransact() )
NORMAL_OUT( "CatalogBaseImpl::flush_by_pids() - Database do not support transaction, flush is errorprone!" );
if( access_mode==READ_ONLY )
{
NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - Session pool is read-only!" );
return;
}
std::string strFlush = "";
std::vector<Pid> vPidList;
//there is no function for deleting Pid from PidList, so the PidList should
//be first converted in a list of Pid
for(CORBA::ULong i=0; i<pids.length(); i++)
vPidList.push_back(pids[i]);
for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ )
strFlush += (*homeBaseIter_)->getFlushByPid(vPidList);
if(ExecuteSQL(strFlush.c_str()))
{
SQLRETURN ret;
ret = SQLEndTran(SQL_HANDLE_DBC, hDbc_, SQL_COMMIT);
if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
// the flush is successfull, we set the modified-value of each
// storage object back to FALSE
for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ )
(*homeBaseIter_)->setBatchUnModified();
}
else
{
NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - Transaction is not successful!" );
}
}
else
{
NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - flush_by_pids is not executed!" );
}
}
void svStorageEngineMySQL::PurgePoolClients(
const string &node, const string &org)
{
if (sql_purge_pool_clients.size() == 0)
throw svExStorageQuery("No SQL query set: purge-pool-clients");
string sql(sql_purge_pool_clients);
string value(node);
EscapeString(value);
if (PrepareSQL(sql, "%n", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: node");
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ExecuteSQL(sql);
}
/*!
\brief Store header info to VFK_DB_HEADER
*/
void VFKReaderSQLite::StoreInfo2DB()
{
CPLString osSQL;
const char *value;
char q;
for(std::map<CPLString, CPLString>::iterator i = poInfo.begin();
i != poInfo.end(); ++i) {
value = i->second.c_str();
q = (value[0] == '"') ? ' ' : '"';
osSQL.Printf("INSERT INTO %s VALUES(\"%s\", %c%s%c)",
VFK_DB_HEADER, i->first.c_str(),
q, value, q);
ExecuteSQL(osSQL);
}
}
bool SensorDB::AddData(SensorDataSet dataset)
{
string channel_num="";
string datatype_id="";
string operator_id="";
string device_id="";
string position_id="";
string activity_id="";
string activitybeginframe_id="";
string activityendframe_id="";
string samplerate="";
string createtime="";
for (int i = 0; i < dataset.Count(); i++)
{
string cmd = "INSERT INTO [dbo].[SensorDataTable](DataTypeID,ActivityID,DeviceID,OperatorID,PositionID,SampleRate,CreateTime,TotalChannelNum,ActivityBeginFrameID,ActivityEndFrameID";
for (int j = 1; j <= dataset.GetSensorData(i).GetTotalChannelNum(); j++)
{
cmd += (",channel_"+int2string(j));
}
cmd+=") VALUES (";
channel_num = ",'"+int2string(dataset.GetSensorData(i).GetTotalChannelNum())+"'";
datatype_id = "'"+int2string(dataset.GetSensorData(i).GetDataTypeID())+"'";
operator_id = ",'"+int2string(dataset.GetSensorData(i).GetOperatorID())+"'";
device_id = ",'"+int2string(dataset.GetSensorData(i).GetDeviceID())+"'";
position_id = ",'"+int2string(dataset.GetSensorData(i).GetPositionID())+"'";
activity_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityID())+"'";
activitybeginframe_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityBeginFrameID())+"'";
activityendframe_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityEndFrameID())+"'";
samplerate= ",'"+double2string(dataset.GetSensorData(i).GetSampleRate())+"'";
createtime= ",'"+dataset.GetSensorData(i).GetCreateTime().toString("yyyy-MM-dd hh:mm:ss").toStdString()+"'";
cmd+=(datatype_id+activity_id+device_id+operator_id+position_id+samplerate+createtime+channel_num+activitybeginframe_id+activityendframe_id);
for (int j = 1; j <= dataset.GetSensorData(i).GetTotalChannelNum(); j++)
{
cmd+=(",'"+dataset.GetSensorData(i).GetChannel(j-1).ToString()+"'");
}
cmd+=");";
if(!ExecuteSQL(cmd)){
return false;
}
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
//write to disk any cached modifications of storage object incarnations managed
//by this catalog.
////////////////////////////////////////////////////////////////////////////////
void
CatalogBaseImpl::flush()
{
DEBUG_OUT("CatalogBaseImpl::flush() is called");
if( !CanTransact() )
NORMAL_OUT( "CatalogBaseImpl::flush() - Database do not support transaction, flush is errorprone!" );
if( access_mode==READ_ONLY )
{
NORMAL_ERR( "CatalogBaseImpl::flush() - Session is read-only!" );
return;
}
std::string strFlush = "";
for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ )
strFlush += (*homeBaseIter_)->getFlush();
if(ExecuteSQL(strFlush.c_str()))
{
SQLRETURN ret;
ret = SQLEndTran(SQL_HANDLE_DBC, hDbc_, SQL_COMMIT);
if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
// the flush is successfull, we set the modified-value of each
// storage object back to FALSE
for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ )
(*homeBaseIter_)->setBatchUnModified();
}
else
{
NORMAL_ERR( "CatalogBaseImpl::flush() - Database transaction is not successful!" );
}
}
else
{
NORMAL_ERR( "CatalogBaseImpl::flush() - flush is not executed!" );
}
}
void svStorageEnginePostgreSQL::UpdatePoolClient(const string &node,
const string &pool_name, const string &dev, const string &org,
svPoolClientState state)
{
if (sql_update_pool_client.size() == 0)
throw svExStorageQuery("No SQL query set: update-pool-client");
string sql(sql_update_pool_client);
string value(dev);
EscapeString(value);
if (PrepareSQL(sql, "%d", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: device");
value = node;
EscapeString(value);
PrepareSQL(sql, "%n", value);
value = pool_name;
EscapeString(value);
PrepareSQL(sql, "%p", value);
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ostringstream os;
map<svPoolClientState, string>::iterator i;
i = pool_client_state.find(state);
if (i == pool_client_state.end()) os << state;
else os << i->second;
PrepareSQL(sql, "%s", os.str());
ExecuteSQL(sql);
if (PQresultStatus(pg_res) != PGRES_COMMAND_OK) {
throw svExStorageQuery(string("PQexec: ") +
PQresultErrorMessage(pg_res));
}
uint32_t affected_rows = (uint32_t)atoi(PQcmdTuples(pg_res));
if (affected_rows == 0)
throw svExStorageRecordNotFound("Pool client not found");
}
void CAcqHive::ExecuteAcqSQL(std::vector<std::string>& vec_sql) throw(base::Exception)
{
if ( vec_sql.empty() )
{
throw base::Exception(ACQERR_EXECUTE_ACQSQL, "[HIVE] No sql to be executed! [FILE:%s, LINE:%d]", __FILE__, __LINE__);
}
try
{
const size_t VEC_SIZE = vec_sql.size();
for ( size_t i = 0; i < VEC_SIZE; ++i )
{
std::string& hive_sql = vec_sql[i];
ExecuteSQL(hive_sql);
}
}
catch ( const base::Exception& ex )
{
throw base::Exception(ACQERR_EXECUTE_ACQSQL, "[HIVE] Execute sql failed! %s [FILE:%s, LINE:%d]", ex.What().c_str(), __FILE__, __LINE__);
}
}
void svStorageEnginePostgreSQL::PurgePoolClients(
const string &node, const string &org)
{
if (sql_purge_pool_clients.size() == 0)
throw svExStorageQuery("No SQL query set: purge-pool-clients");
string sql(sql_purge_pool_clients);
string value(node);
EscapeString(value);
if (PrepareSQL(sql, "%n", value) == 0)
throw svExStorageQuery("Invalid SQL, missing match token: node");
value = org;
EscapeString(value);
PrepareSQL(sql, "%o", value);
ExecuteSQL(sql);
if (PQresultStatus(pg_res) != PGRES_COMMAND_OK) {
throw svExStorageQuery(string("PQexec: ") +
PQresultErrorMessage(pg_res));
}
}
int DBSpool::ExecuteSQL(const string& strSQL, RESDataList* res, int &iRelt, unsigned long long* iInsertId)
{
MYSQL_RES* SQLRes = NULL;
short shIdt = 0;
int iRet = ExecuteSQL(strSQL, &SQLRes, shIdt, iRelt, iInsertId);
if (iRet == SQL_TYPE_SELECT){
//MYSQL_FIELD* fields;
MYSQL_ROW row;
int irows, ifields;
string strValue;
irows = mysql_num_rows(SQLRes);
if(irows == 0) {
ReleaseConnection(shIdt);
return iRet;
}
ifields = mysql_num_fields(SQLRes);
if(ifields == 0){
ReleaseConnection(shIdt);
return iRet;
}
//fields = mysql_fetch_fields(SQLRes);
for(int i=0; i<irows; i++){
if((row = mysql_fetch_row(SQLRes)) == NULL){
break;
}
for(int j=0; j<ifields; j++) {
if (row[j]){
strValue = row[j];
}else{
strValue = "";
}
res->push_back(strValue);
}
}
ReleaseConnection(shIdt);
}
return iRet;
}
/*!
\brief Load data records (&D)
Call VFKReader::OpenFile() before this function.
\param poDataBlock limit to selected data block or NULL for all
\return number of data records or -1 on error
*/
int VFKReaderSQLite::ReadDataRecords(IVFKDataBlock *poDataBlock)
{
int nDataRecords;
int iDataBlock;
const char *pszName;
CPLString osSQL;
IVFKDataBlock *poDataBlockCurrent;
sqlite3_stmt *hStmt;
pszName = NULL;
if (poDataBlock) { /* read records only for selected data block */
/* table name */
pszName = poDataBlock->GetName();
/* check for existing records (re-use already inserted data) */
osSQL.Printf("SELECT num_records FROM %s WHERE "
"table_name = '%s'",
VFK_DB_TABLE, pszName);
hStmt = PrepareStatement(osSQL.c_str());
nDataRecords = -1;
if (ExecuteSQL(hStmt) == OGRERR_NONE) {
nDataRecords = sqlite3_column_int(hStmt, 0);
}
sqlite3_finalize(hStmt);
}
else {
/* read all data blocks */
/* check for existing records (re-use already inserted data) */
osSQL.Printf("SELECT COUNT(*) FROM %s WHERE num_records = -1", VFK_DB_TABLE);
hStmt = PrepareStatement(osSQL.c_str());
if (ExecuteSQL(hStmt) == OGRERR_NONE &&
sqlite3_column_int(hStmt, 0) == 0)
nDataRecords = 0; /* -> read from DB */
else
nDataRecords = -1; /* -> read from VFK file */
sqlite3_finalize(hStmt);
}
if (nDataRecords > -1) { /* read records from DB */
/* read from DB */
long iFID;
int iRowId;
VFKFeatureSQLite *poNewFeature = NULL;
poDataBlockCurrent = NULL;
for (iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++) {
poDataBlockCurrent = GetDataBlock(iDataBlock);
if (poDataBlock && poDataBlock != poDataBlockCurrent)
continue;
poDataBlockCurrent->SetFeatureCount(0); /* avoid recursive call */
pszName = poDataBlockCurrent->GetName();
CPLAssert(NULL != pszName);
osSQL.Printf("SELECT %s,_rowid_ FROM %s ",
FID_COLUMN, pszName);
if (EQUAL(pszName, "SBP"))
osSQL += "WHERE PORADOVE_CISLO_BODU = 1 ";
osSQL += "ORDER BY ";
osSQL += FID_COLUMN;
hStmt = PrepareStatement(osSQL.c_str());
nDataRecords = 0;
while (ExecuteSQL(hStmt) == OGRERR_NONE) {
iFID = sqlite3_column_int(hStmt, 0);
iRowId = sqlite3_column_int(hStmt, 1);
poNewFeature = new VFKFeatureSQLite(poDataBlockCurrent, iRowId, iFID);
poDataBlockCurrent->AddFeature(poNewFeature);
nDataRecords++;
}
/* check DB consistency */
osSQL.Printf("SELECT num_features FROM %s WHERE table_name = '%s'",
VFK_DB_TABLE, pszName);
hStmt = PrepareStatement(osSQL.c_str());
if (ExecuteSQL(hStmt) == OGRERR_NONE) {
int nFeatDB;
nFeatDB = sqlite3_column_int(hStmt, 0);
if (nFeatDB > 0 && nFeatDB != poDataBlockCurrent->GetFeatureCount())
CPLError(CE_Failure, CPLE_AppDefined,
"%s: Invalid number of features " CPL_FRMT_GIB " (should be %d)",
pszName, poDataBlockCurrent->GetFeatureCount(), nFeatDB);
}
sqlite3_finalize(hStmt);
}
}
else { /* read from VFK file and insert records into DB */
/* begin transaction */
ExecuteSQL("BEGIN");
/* INSERT ... */
nDataRecords = VFKReader::ReadDataRecords(poDataBlock);
/* update VFK_DB_TABLE table */
poDataBlockCurrent = NULL;
for (iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++) {
poDataBlockCurrent = GetDataBlock(iDataBlock);
if (poDataBlock && poDataBlock != poDataBlockCurrent)
continue;
osSQL.Printf("UPDATE %s SET num_records = %d WHERE "
"table_name = '%s'",
VFK_DB_TABLE, poDataBlockCurrent->GetRecordCount(),
poDataBlockCurrent->GetName());
ExecuteSQL(osSQL);
}
/* commit transaction */
ExecuteSQL("COMMIT");
}
return nDataRecords;
}
/*!
\brief Create DB table from VFKDataBlock (SQLITE only)
\param poDataBlock pointer to VFKDataBlock instance
*/
void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn)
{
const char *pszBlockName;
const char *pszKey;
CPLString osCommand, osColumn;
bool bUnique;
VFKPropertyDefn *poPropertyDefn;
sqlite3_stmt *hStmt;
bUnique = !CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_IGNORE_DUPLICATES", "NO"));
pszBlockName = poDataBlock->GetName();
/* register table in VFK_DB_TABLE */
osCommand.Printf("SELECT COUNT(*) FROM %s WHERE "
"table_name = '%s'",
VFK_DB_TABLE, pszBlockName);
hStmt = PrepareStatement(osCommand.c_str());
if (ExecuteSQL(hStmt) == OGRERR_NONE &&
sqlite3_column_int(hStmt, 0) == 0) {
osCommand.Printf("CREATE TABLE '%s' (", pszBlockName);
for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
poPropertyDefn = poDataBlock->GetProperty(i);
if (i > 0)
osCommand += ",";
osColumn.Printf("%s %s", poPropertyDefn->GetName(),
poPropertyDefn->GetTypeSQL().c_str());
osCommand += osColumn;
}
osColumn.Printf(",%s integer", FID_COLUMN);
osCommand += osColumn;
if (poDataBlock->GetGeometryType() != wkbNone) {
osColumn.Printf(",%s blob", GEOM_COLUMN);
osCommand += osColumn;
}
osCommand += ")";
ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */
/* create indices */
osCommand.Printf("%s_%s", pszBlockName, FID_COLUMN);
CreateIndex(osCommand.c_str(), pszBlockName, FID_COLUMN,
!EQUAL(pszBlockName, "SBP"));
pszKey = ((VFKDataBlockSQLite *) poDataBlock)->GetKey();
if (pszKey) {
osCommand.Printf("%s_%s", pszBlockName, pszKey);
CreateIndex(osCommand.c_str(), pszBlockName, pszKey, bUnique);
}
if (EQUAL(pszBlockName, "SBP")) {
/* create extra indices for SBP */
CreateIndex("SBP_OB", pszBlockName, "OB_ID", FALSE);
CreateIndex("SBP_HP", pszBlockName, "HP_ID", FALSE);
CreateIndex("SBP_DPM", pszBlockName, "DPM_ID", FALSE);
CreateIndex("SBP_OB_HP_DPM", pszBlockName, "OB_ID,HP_ID,DPM_ID", bUnique);
CreateIndex("SBP_OB_POR", pszBlockName, "OB_ID,PORADOVE_CISLO_BODU", FALSE);
CreateIndex("SBP_HP_POR", pszBlockName, "HP_ID,PORADOVE_CISLO_BODU", FALSE);
CreateIndex("SBP_DPM_POR", pszBlockName, "DPM_ID,PORADOVE_CISLO_BODU", FALSE);
}
else if (EQUAL(pszBlockName, "HP")) {
/* create extra indices for HP */
CreateIndex("HP_PAR1", pszBlockName, "PAR_ID_1", FALSE);
CreateIndex("HP_PAR2", pszBlockName, "PAR_ID_2", FALSE);
}
else if (EQUAL(pszBlockName, "OB")) {
/* create extra indices for OP */
CreateIndex("OB_BUD", pszBlockName, "BUD_ID", FALSE);
}
/* update VFK_DB_TABLE meta-table */
osCommand.Printf("INSERT INTO %s (file_name, table_name, "
"num_records, num_features, num_geometries, table_defn) VALUES "
"('%s', '%s', -1, 0, 0, '%s')",
VFK_DB_TABLE, m_pszFilename, pszBlockName, pszDefn);
ExecuteSQL(osCommand.c_str());
sqlite3_finalize(hStmt);
}
return VFKReader::AddDataBlock(poDataBlock, NULL);
}
/*!
\brief VFKReaderSQLite constructor
*/
VFKReaderSQLite::VFKReaderSQLite(const char *pszFilename) : VFKReader(pszFilename)
{
const char *pszDbNameConf;
CPLString osDbName;
CPLString osCommand;
VSIStatBufL sStatBufDb, sStatBufVfk;
/* open tmp SQLite DB (re-use DB file if already exists) */
pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", NULL);
if (pszDbNameConf) {
osDbName = pszDbNameConf;
}
else {
osDbName = CPLResetExtension(m_pszFilename, "db");
}
size_t nLen = osDbName.length();
if( nLen > 2048 )
{
nLen = 2048;
osDbName.resize(nLen);
}
m_pszDBname = new char [nLen+1];
std::strncpy(m_pszDBname, osDbName.c_str(), nLen);
m_pszDBname[nLen] = 0;
CPLDebug("OGR-VFK", "Using internal DB: %s",
m_pszDBname);
if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES")))
m_bSpatial = TRUE; /* build geometry from DB */
else
m_bSpatial = FALSE; /* store also geometry in DB */
m_bNewDb = TRUE;
if (VSIStatL(osDbName, &sStatBufDb) == 0) {
if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO"))) {
m_bNewDb = TRUE; /* overwrite existing DB */
CPLDebug("OGR-VFK", "Internal DB (%s) already exists and will be overwritten",
m_pszDBname);
VSIUnlink(osDbName);
}
else {
if (VSIStatL(pszFilename, &sStatBufVfk) == 0 &&
sStatBufVfk.st_mtime > sStatBufDb.st_mtime) {
CPLDebug("OGR-VFK",
"Found %s but ignoring because it appears\n"
"be older than the associated VFK file.",
osDbName.c_str());
m_bNewDb = TRUE;
VSIUnlink(osDbName);
}
else {
m_bNewDb = FALSE; /* re-use existing DB */
}
}
}
/*
if (m_bNewDb) {
CPLError(CE_Warning, CPLE_AppDefined,
"INFO: No internal SQLite DB found. Reading VFK data may take some time...");
}
*/
CPLDebug("OGR-VFK", "New DB: %s Spatial: %s",
m_bNewDb ? "yes" : "no", m_bSpatial ? "yes" : "no");
if (SQLITE_OK != sqlite3_open(osDbName, &m_poDB)) {
CPLError(CE_Failure, CPLE_AppDefined,
"Creating SQLite DB failed");
}
else {
char* pszErrMsg = NULL;
CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg));
sqlite3_free(pszErrMsg);
}
if (m_bNewDb) {
/* new DB, create support metadata tables */
osCommand.Printf("CREATE TABLE %s (file_name text, table_name text, num_records integer, "
"num_features integer, num_geometries integer, table_defn text)",
VFK_DB_TABLE);
ExecuteSQL(osCommand.c_str());
/* header table */
osCommand.Printf("CREATE TABLE %s (key text, value text)", VFK_DB_HEADER);
ExecuteSQL(osCommand.c_str());
}
}
/*!
\brief Add feature
\param poDataBlock pointer to VFKDataBlock instance
\param poFeature pointer to VFKFeature instance
*/
OGRErr VFKReaderSQLite::AddFeature(IVFKDataBlock *poDataBlock, VFKFeature *poFeature)
{
CPLString osCommand;
CPLString osValue;
const char *pszBlockName;
OGRFieldType ftype;
const VFKProperty *poProperty;
VFKFeatureSQLite *poNewFeature;
pszBlockName = poDataBlock->GetName();
osCommand.Printf("INSERT INTO '%s' VALUES(", pszBlockName);
for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) {
ftype = poDataBlock->GetProperty(i)->GetType();
poProperty = poFeature->GetProperty(i);
if (i > 0)
osCommand += ",";
if (poProperty->IsNull())
osValue.Printf("NULL");
else {
switch (ftype) {
case OFTInteger:
osValue.Printf("%d", poProperty->GetValueI());
break;
case OFTReal:
osValue.Printf("%f", poProperty->GetValueD());
break;
case OFTString:
if (poDataBlock->GetProperty(i)->IsIntBig())
osValue.Printf("%s", poProperty->GetValueS());
else
osValue.Printf("'%s'", poProperty->GetValueS(TRUE));
break;
default:
osValue.Printf("'%s'", poProperty->GetValueS());
break;
}
}
osCommand += osValue;
}
osValue.Printf("," CPL_FRMT_GIB, poFeature->GetFID());
if (poDataBlock->GetGeometryType() != wkbNone) {
osValue += ",NULL";
}
osValue += ")";
osCommand += osValue;
if (ExecuteSQL(osCommand.c_str(), TRUE) != OGRERR_NONE)
return OGRERR_FAILURE;
if (EQUAL(pszBlockName, "SBP")) {
poProperty = poFeature->GetProperty("PORADOVE_CISLO_BODU");
if( poProperty == NULL )
{
CPLError(CE_Failure, CPLE_AppDefined, "Cannot find property PORADOVE_CISLO_BODU");
return OGRERR_FAILURE;
}
if (!EQUAL(poProperty->GetValueS(), "1"))
return OGRERR_NONE;
}
poNewFeature = new VFKFeatureSQLite(poDataBlock, poDataBlock->GetRecordCount(RecordValid) + 1,
poFeature->GetFID());
poDataBlock->AddFeature(poNewFeature);
return OGRERR_NONE;
}
