wxArrayString wxPostgresDatabase::GetPKColumns(const wxString& table)
{
// Initialize variables
wxArrayString returnArray;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
wxPreparedStatement* pStatement = NULL;
wxDatabaseResultSet* pResult = NULL;
#if wxUSE_DATABASE_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT pg_attribute.attname,format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = ? ::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, table);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(wxT("attname")));
}
}
}
#if wxUSE_DATABASE_EXCEPTIONS
}
catch (wxDatabaseException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return returnArray;
}
bool wxPostgresDatabase::ViewExists(const wxString& view)
{
// Initialize variables
bool bReturn = false;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
wxPreparedStatement* pStatement = NULL;
wxDatabaseResultSet* pResult = NULL;
#if wxUSE_DATABASE_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT COUNT(*) FROM information_schema.tables WHERE table_type='VIEW' AND table_name=?;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, view);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
if (pResult->Next())
{
if(pResult->GetResultInt(1) != 0)
{
bReturn = true;
}
}
}
}
#if wxUSE_DATABASE_EXCEPTIONS
}
catch (wxDatabaseException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return bReturn;
}
wxArrayString wxPostgresDatabase::GetColumns(const wxString& table)
{
// Initialize variables
wxArrayString returnArray;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
wxPreparedStatement* pStatement = NULL;
wxDatabaseResultSet* pResult = NULL;
#if wxUSE_DATABASE_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT column_name FROM information_schema.columns WHERE table_name=? ORDER BY ordinal_position;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, table);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(1));
}
}
}
#if wxUSE_DATABASE_EXCEPTIONS
}
catch (wxDatabaseException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return returnArray;
}
bool TdsDatabaseLayer::ViewExists(const wxString& view)
{
bool bReturn = false;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
PreparedStatement* pStatement = NULL;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString query = _("exec sp_tables ?, NULL, NULL, '''VIEW'''");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, view);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
if (pResult->Next())
{
//wxPrintf(_("View found: '%s'\n"), pResult->GetResultString(_("TABLE_NAME")));
if(view.CmpNoCase(pResult->GetResultString(_("TABLE_NAME"))) == 0)
{
bReturn = true;
}
}
}
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return bReturn;
}
wxArrayString FirebirdDatabaseLayer::GetColumns(const wxString& table)
{
// Initialize variables
wxArrayString returnArray;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
PreparedStatement* pStatement = NULL;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString tableUpperCase = table.Upper();
wxString query = _("SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME=?;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, tableUpperCase);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(1).Trim());
}
}
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return returnArray;
}
bool FirebirdDatabaseLayer::ViewExists(const wxString& view)
{
// Initialize variables
bool bReturn = false;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
PreparedStatement* pStatement = NULL;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString viewUpperCase = view.Upper();
wxString query = _("SELECT COUNT(*) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_BLR IS NOT NULL AND RDB$RELATION_NAME=?;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, viewUpperCase);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
if (pResult->Next())
{
if(pResult->GetResultInt(1) != 0)
{
bReturn = true;
}
}
}
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
return bReturn;
}
void WriteData(DBHandles *d, link *head, std::string symbol, std::string symbolTableName,
boost::unordered_set<dataPoint> *hash)
{
char sql[512];
SQLRETURN r;
std::string errorString;
CreateStatement(d);
sprintf(sql, o.gInsertSQL, symbolTableName.c_str());
//WriteLog("%s\n", sql);
if (!SQL_SUCCEEDED(r = SQLPrepare(d->hstmt, (SQLTCHAR *) (sql), SQL_NTS)))
throw DataException(__FILE__, __LINE__);
link *currentLink = head;
char oneLine[5120];
unsigned long startTime = timeGetTime();
int totalRows = 0;
int numSkipped = 0;
int numWritten = 0;
unsigned long totalTime = 0;
DataFeedData df;
if (o.useTicks == true)
BindTickVariables(d, &df);
else
BindIntVariables(d, &df);
//externally set some df parameters, hacky
strcpy(df.symbol, symbol.c_str());
df.stringLength = SQL_NTS;
//iterate through each chunk in the link list
while (currentLink != NULL)
{
char *begin = currentLink->buffer;
unsigned long blockTime = timeGetTime();
int beginBlockCount = totalRows;
char *end = NULL;
//iterate through each line in the buffer and
//write it to the database
while ((end = strchr(begin, '\n')) != NULL)
{
memset(oneLine, 0, sizeof(oneLine));
strncpy(oneLine, begin, end-begin + 1);
char copyOfOneLine[512];
strcpy(copyOfOneLine, oneLine);
//printf("%s\n", copyOfOneLine);
if (WriteOneLine(d, oneLine, hash, &df) == 1)
numWritten++;
else
numSkipped++;
totalRows++;
begin = end + 1;
if (numWritten % 100 == 0)
{
if (!SQL_SUCCEEDED(r = SQLEndTran(SQL_HANDLE_DBC, d->hdbc, SQL_COMMIT)))
throw DataException(__FILE__, __LINE__);
}
if (totalRows % 50000 == 0)
{
//stats gathering shit
unsigned long currentTime = timeGetTime();
totalTime = currentTime - startTime;
double blockDelta = currentTime - blockTime;
double rowDelta = totalRows - beginBlockCount;
double currentBlockTime = blockDelta == 0 ? 0 : 1000* rowDelta/blockDelta;
WriteLog("%s: writing total rows=%d, total time = %d, rows/s = %.2f, numWritten = %d, "
"numSkipped = %d, timestamp = %s\n",
symbol.c_str(), totalRows, totalTime/1000, currentBlockTime, numWritten, numSkipped, oneLine);
}
}
link *freeLink = currentLink;
currentLink = currentLink->next;
free(freeLink->buffer);
free(freeLink);
}
totalTime = timeGetTime() - startTime;
WriteLog("%s: FINAL: writing total rows=%d, total time = %d, rows/s = %.2f, numWritten = %d, numSkipped = %d\n",
symbol.c_str(), totalRows, totalTime/1000, 1000.0 * totalRows / totalTime, numWritten, numSkipped);
CloseStatement(d);
}
boost::unordered_set<dataPoint> *GetDBHash(DBHandles *d, std::string symbolTableName,
char *date1, char *date2, std::string contractName)
{
//IQFeed will give the data backwards, so we need to order the dates properly
char *firstDate = date2, *secondDate = date1;
std::string errorString;
UDate udate1 = GetUDate(date1, dateGC);
UDate udate2 = GetUDate(date2, dateGC);
if (udate1 < udate2)
{
firstDate = date1;
secondDate = date2;
}
WriteLog("Getting data from %s to %s\n", firstDate, secondDate);
char query[256];
if (o.isOption || o.isFutures)
{
sprintf(query, "SELECT transactiontime, tickId FROM %s WHERE transactiontime >= '%s' "
"AND transactiontime <= '%s' and contract = '%s'",
symbolTableName.c_str(), firstDate, secondDate, contractName.c_str());
}
else
{
sprintf(query, "SELECT transactiontime, tickId FROM %s WHERE transactiontime >= '%s' "
"AND transactiontime <= '%s'",
symbolTableName.c_str(), firstDate, secondDate);
}
CreateStatement(d);
SQLRETURN ret;
if (!SQL_SUCCEEDED(ret = SQLExecDirect(d->hstmt, (SQLCHAR *) query, SQL_NTS)))
throw DataException(__FILE__, __LINE__);
char transactionTime[64];
int tickId;
SQLLEN indicator;
boost::unordered_set<dataPoint> *hash = new boost::unordered_set<dataPoint>();
if (!SQL_SUCCEEDED(ret = SQLBindCol(d->hstmt, 1, SQL_C_CHAR, transactionTime, sizeof(transactionTime), &indicator)))
throw DataException(__FILE__, __LINE__);
if (!SQL_SUCCEEDED(ret = SQLBindCol(d->hstmt, 2, SQL_INTEGER, &tickId, sizeof(tickId), &indicator)))
throw DataException(__FILE__, __LINE__);
int numRows = 0;
while (SQL_SUCCEEDED(ret = SQLFetch(d->hstmt)))
{
dataPoint newData;
newData.date = GetUDate(transactionTime, dateGC);
newData.tickId = tickId;
try
{
hash->insert(newData);
}
catch (const std::bad_alloc &)
{
WriteLog("F**k ran out of memory... Let's forget about getting more data from the database and keep going\n");
break;
}
numRows++;
}
WriteLog("Total of %d rows retrieved\n", numRows);
CloseStatement(d);
return hash;
}
