[prev in list] [next in list] [prev in thread] [next in thread]
List: kde-commits
Subject: playground/office/alkimia/alkquotes/backend
From: Brian Cappello <cappellokde () gmail ! com>
Date: 2010-07-06 1:53:02
Message-ID: 20100706015302.7DBD1AC8E6 () svn ! kde ! org
[Download RAW message or body]
SVN commit 1146441 by bcappello:
first working attempt at bind variables
M +9 -4 backend.cpp
M +52 -48 statsmanager.cpp
--- trunk/playground/office/alkimia/alkquotes/backend/backend.cpp #1146440:1146441
@@ -122,7 +122,9 @@
{
// see if data already exists
QSqlQuery query;
- query.exec( "SELECT name FROM sqlite_master WHERE name='symbolinfo'" );
+ query.prepare( "SELECT name FROM sqlite_master WHERE name=:table" );
+ query.bindValue(":table", "symbolinfo" );
+ query.exec();
query.first();
if( query.isValid() )
{
@@ -182,16 +184,19 @@
{
qDebug() << "Backend::db_listTable: " + tableName;
+ QString selectColumns = "id";
+ for( int i = 0; i < columns.size(); ++i )
+ selectColumns += ',' + columns.at(i);
+
QSqlQuery query;
- query.exec( "SELECT * FROM " + tableName );
+ query.exec( "SELECT " + selectColumns + " FROM " + tableName );
while( query.next() )
{
QString tmp;
tmp += "id=" + query.value(0).toString();
- for( int i = 1; i <= columns.size(); i++ )
+ for( int i = 1; i <= columns.size(); ++i )
tmp += ", " + columns.at(i-1) + '=' + query.value(i).toString();
- //tmp.remove(0,1);
qDebug() << tmp;
}
}
--- trunk/playground/office/alkimia/alkquotes/backend/statsmanager.cpp \
#1146440:1146441 @@ -78,7 +78,7 @@
if( !pluginsDir.cd("plugins") )
{
- qDebug() << "couldn't find plugin dir";
+ qDebug() << "StatsManager: couldn't find plugin dir";
return;
}
@@ -88,7 +88,7 @@
if( KeysInterface *interface = qobject_cast<KeysInterface *>(loader.instance()) \
) {
interfaces.append(interface);
- qDebug() << "successfully loaded test plugin";
+ qDebug() << "StatsManager: successfully loaded test plugin";
}
}
}
@@ -152,7 +152,9 @@
selectColumns.chop(1); // delete trailing comma
QSqlQuery query;
- query.exec( "SELECT " + selectColumns + " FROM symbolinfo WHERE symbol='" + \
symbolCAPS + '\'' ); + query.prepare("SELECT " + selectColumns + " FROM symbolinfo \
WHERE symbol=:symbol"); + query.bindValue(":symbol", symbolCAPS);
+ query.exec();
query.first();
if( query.isValid() )
{
@@ -181,7 +183,9 @@
QString symbolCAPS = symbol.toUpper();
QSqlQuery query;
- query.exec("SELECT symbol FROM symbolinfo WHERE symbol='" + symbolCAPS + '\'');
+ query.prepare("SELECT symbol FROM symbolinfo WHERE symbol=:symbol");
+ query.bindValue(":symbol",symbolCAPS);
+ query.exec();
query.first();
if( query.isValid() )
{
@@ -191,12 +195,6 @@
tmpKeys->append(keys.at(i));
}
- int timeIndex = tmpKeys->indexOf("time");
- if( timeIndex != -1 )
- {
- tmpKeys->removeAt( timeIndex );
- }
-
QUrl url = generateUrl( symbolCAPS, tmpKeys );
// make sure this exact url isn't already downloading
@@ -210,6 +208,7 @@
}
else
qDebug() << "This update for " + symbolCAPS + " is already downloading!";
+
}
}
@@ -229,14 +228,27 @@
/// create the list of yahoo's variables for the desired keys
QString parameters;
- /// if keys list contains the "name" key, move it to the end because it can \
contain commas and this will
- /// screw up the indexing of the results list
+ /// make sure the keys list conforms to internal restrictions
+ // if keys list contains the "name" key, move it to the end because it can contain \
commas and this will + // screw up the indexing of the results list
int nameIndex = keys->indexOf( "name" );
if( nameIndex != -1 )
{
keys->removeAt( nameIndex );
keys->append("name");
}
+ // remove any requests to download the symbol; it screws up bind variables
+ int symbolIndex = keys->indexOf( "symbol" );
+ if( symbolIndex != -1 )
+ {
+ keys->removeAt( symbolIndex );
+ }
+ // remove any requests to download the time; this key can't be directly downloaded
+ int timeIndex = keys->indexOf("time");
+ if( timeIndex != -1 )
+ {
+ keys->removeAt( timeIndex );
+ }
keys->prepend("error");
@@ -362,13 +374,13 @@
/// QUESTION/HELP: is there a better way to do this than a bunch of if-else \
statements? for( int i = 0; i < keys->size(); ++i )
{
- // check if the downloaded value represents a string (the value will be enclosed \
in double quotes) + // STRING or NUMBER? (strings will be enclosed in double \
quotes) if( results.at(i).endsWith('"') )
{
/// strip the quotes from the ends
results[i] = results.at(i).section('"',1,1);
- /// correctly format the date
+ /// DATE
if( keys->at(i) == "date" )
{
// results.at( indexOf(date) ) looks like 'MM/DD/YYYY' but we want 'YYYY-MM-DD' [or \
maybe DD-MM-YYYY?] @@ -379,7 +391,7 @@
results[i] = tmp.join("-");
}
- /// correctly format the close
+ /// CLOSE
else if( keys->at(i) == "close" )
{
// results.at( indexOf(close) ) looks like 'TIME - <b>CLOSE</b>'
@@ -435,7 +447,9 @@
qDebug() << "entering StatsManager::db_updateSymbol: " + symbol;
QSqlQuery query(db);
- query.exec("SELECT symbol FROM symbolinfo WHERE symbol='" + symbol + '\'');
+ query.prepare("SELECT symbol FROM symbolinfo WHERE symbol=:symbol");
+ query.bindValue(":symbol", symbol);
+ query.exec();
query.first();
if( !query.isValid() )
{
@@ -445,28 +459,32 @@
qDebug() << "StatsManager::db_Symbol: data for symbol " + symbol + " already \
exists. Only updating relevant fields.";
// even if this symbol was new, we still need to update its entry in the database \
with the downloaded values
- qDebug() << "StatsManager::db_updateSymbol: updating info for: " + symbol;
QString queryUpdate = "UPDATE symbolinfo SET";
int commaPos = queryUpdate.size();
// populate queryUpdate with the specific column-value pairs to update for this \
symbol for( int i = 0; i < keys->size(); ++i )
- queryUpdate += ", " + keys->at(i) + "='" + results->at(i) + '\'';
+ queryUpdate += ", " + keys->at(i) + "=:" + keys->at(i);
// strip the leading comma added by i==0 above
queryUpdate.remove(commaPos,1);
- /// WARNING: removing the following line will apply these column-value pairs to \
EVERY symbol in the db!
- queryUpdate += " WHERE symbol='" + symbol + '\'';
+ queryUpdate += " WHERE symbol=:symbol";
- qDebug() << "db_updateSymbol query: " + queryUpdate;
+ //qDebug() << "db_updateSymbol query: " + queryUpdate;
- query.exec(queryUpdate);
+ query.prepare(queryUpdate);
+ for( int i = 0; i < keys->size(); ++i )
+ query.bindValue(':' + keys->at(i), results->at(i) );
+ if( !keys->contains("symbol") )
+ query.bindValue(":symbol", symbol );
+
+ query.exec();
if( !query.lastError().isValid() )
return true;
else
- qDebug() << "StatsManager::db_updateSymbol failed: " + query.lastError().text();
+ qWarning() << "StatsManager::db_updateSymbol failed: " + \
query.lastError().text();
return false;
}
@@ -478,36 +496,23 @@
bool StatsManager::db_insertSymbol( const QString &symbol )
{
QSqlQuery query(db);
- query.exec("SELECT symbol FROM symbolinfo WHERE symbol='" + symbol + '\'');
+ query.prepare("SELECT symbol FROM symbolinfo WHERE symbol=:symbol");
+ query.bindValue(":symbol",symbol);
+ query.exec();
query.first();
if( !query.isValid() )
{
- qDebug() << "StatsManager::db_insertSymbol: creating a new entry for: " + \
symbol;
- QString queryString = "INSERT INTO symbolinfo VALUES (";
+ //qDebug() << "StatsManager::db_insertSymbol: creating a new entry for: " + \
symbol;
- queryString += "NULL"; // set the primary key as NULL so that SQLite will \
automatically asign a unique key + query.prepare( "INSERT INTO symbolinfo (id, \
symbol) VALUES(NULL, :symbol)" ); + query.bindValue( ":symbol", symbol );
- // we want to initialize every column with a value, so by default we use the \
column names as default values.
- QStringList tmpColumns = db_columns;
- // except for the "symbol" column, where we want to use the actual inserted \
symbol as the value
- tmpColumns.removeFirst(); // "symbol" should be the first column declared in \
db_columns: see backend.cpp's loadDatabase() to make sure
- tmpColumns.prepend(symbol);
-
- // loop through tmpColumns, and update queryString with a new column-value pair \
for each index
- for( int i = 0; i < tmpColumns.size(); ++i )
- queryString += ", '" + tmpColumns.at(i) + '\'';
-
- // add the trailing backet to VALUES ( ... ) to finish off the INSERT query
- queryString += ')';
-
- qDebug() << "StatsManager::db_insertSymbol: " + queryString;
- query.exec( queryString );
-
// check to make sure the INSERT query worked
+ query.exec();
if( !query.lastError().isValid() )
return true;
else
- qDebug() << "StatsManager::db_insertSymbol failed: " + \
query.lastError().text(); + qWarning() << "StatsManager::db_insertSymbol failed: \
" + query.lastError().text(); }
return false;
}
@@ -517,12 +522,11 @@
*/
void StatsManager::db_listTable( const QString &tableName )
{
- qDebug() << "Statsmanager::db_listTable: " + tableName;
+ qDebug() << "StatsManager::db_listTable: " + tableName;
- QString selectColumns = "id,";
+ QString selectColumns = "id";
for( int i = 0; i < db_columns.size(); ++i )
- selectColumns += db_columns.at(i) + ',';
- selectColumns.chop(1); // delete trailing comma
+ selectColumns += ',' + db_columns.at(i);
QSqlQuery query;
query.exec( "SELECT " + selectColumns + " FROM " + tableName );
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic