void SharedCookieJarQt::deleteAllCookies()
{
if (!m_database.isOpen())
return;
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("DELETE * FROM cookies"));
sqlQuery.exec();
setAllCookies(QList<QNetworkCookie>());
}
void startStop(sqlite3 * db, fC callback, int id){
values * val = selectFromTable(db, "select count(*) as count from TASK where TIMEID = %d AND STOP IS NULL;", id);
int flag = atoi(val->result[1]);
updateStatus(db, callback, id, !flag);
/*
* int flag:
* flag == FALSE - INSERT
* flag == TRUE - UPDATE
*/
foreignKey(db, 1);
if(!flag) sqlQuery(db, callback, "INSERT INTO TASK (TIMEID, START) VALUES (%d, datetime('now'));", id);
else sqlQuery(db, callback, "UPDATE TASK SET STOP = datetime('now') WHERE TIMEID = %d AND STOP IS NULL;", id);
foreignKey(db, 0);
freeStructValues(val);
}
void SharedCookieJarQt::ensureDatabaseTable()
{
if (!m_database.open()) {
qWarning("Can't open cookie database");
return;
}
m_database.exec(QLatin1String("PRAGMA synchronous=OFF"));
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("CREATE TABLE IF NOT EXISTS cookies (cookieId VARCHAR PRIMARY KEY, cookie BLOB);"));
sqlQuery.exec();
}
void updateStatus(sqlite3 * db, fC callback, int id, int status){
/*
* int status:
* status == 1 - active
* status == 0 - no active
*/
char * query = status ? "UPDATE TIME SET STATUS = 1 WHERE ID = %d;"
: "UPDATE TIME SET STATUS = 0 WHERE ID = %d;";
sqlQuery(db, callback, query, id);
}
void SharedCookieJarQt::loadCookies()
{
if (!m_database.isOpen())
return;
QList<QNetworkCookie> cookies;
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("SELECT cookie FROM cookies"));
sqlQuery.exec();
while (sqlQuery.next())
cookies.append(QNetworkCookie::parseCookies(sqlQuery.value(0).toByteArray()));
setAllCookies(cookies);
}
void CollectionSuggester::suggest(const QString &query) {
QString q = query.simplified();
if (q.isEmpty()) return;
QList<Suggestion*> suggestions;
QStringList strings;
QString likeQuery;
if (q.length() < 3) likeQuery = q + "%";
else likeQuery = "%" + q + "%";
QSqlDatabase db = Database::instance().getConnection();
QSqlQuery sqlQuery(db);
sqlQuery.prepare("select name from artists where name like ? and trackCount>1 order by trackCount desc limit 5");
sqlQuery.bindValue(0, likeQuery);
bool success = sqlQuery.exec();
if (!success) qDebug() << sqlQuery.lastQuery() << sqlQuery.lastError().text() << sqlQuery.lastError().number();
while (sqlQuery.next()) {
QString value = sqlQuery.value(0).toString();
suggestions << new Suggestion(value, "artist");
strings << value;
}
QString likeDate;
if (q.length() == 3) likeDate = q + "%";
else if (q.length() == 4) likeDate = q;
sqlQuery.prepare("select title from albums where (title like ? or year like ?) and trackCount>0 order by year desc, trackCount desc limit 5");
sqlQuery.bindValue(0, likeQuery);
sqlQuery.bindValue(1, likeDate);
success = sqlQuery.exec();
if (!success) qDebug() << sqlQuery.lastQuery() << sqlQuery.lastError().text() << sqlQuery.lastError().number();
while (sqlQuery.next()) {
QString value = sqlQuery.value(0).toString();
suggestions << new Suggestion(value, "album");
strings << value;
}
sqlQuery.prepare("select title from tracks where title like ? order by track, path limit 10");
sqlQuery.bindValue(0, likeQuery);
success = sqlQuery.exec();
if (!success) qDebug() << sqlQuery.lastQuery() << sqlQuery.lastError().text() << sqlQuery.lastError().number();
while (sqlQuery.next()) {
QString value = sqlQuery.value(0).toString();
if (strings.contains(value)) continue;
suggestions << new Suggestion(value, "track");
strings << value;
}
emit ready(suggestions);
}
bool SharedCookieJarQt::deleteCookie(const QNetworkCookie& cookie)
{
if (!QNetworkCookieJar::deleteCookie(cookie))
return false;
if (!m_database.isOpen())
return false;
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("DELETE FROM cookies WHERE cookieId=:cookieIdvalue"));
sqlQuery.bindValue(QLatin1String(":cookieIdvalue"), cookie.domain().append(QLatin1String(cookie.name())));
sqlQuery.exec();
return true;
}
//execute a query in database and send the result by a signal
void ManageBDD::executeSQLQuery(const QString& query, QPointer<QObject> sender, int id, const QString &dbName, QPointer<QObject> data)
{
QList<QSqlRecord> results;
if (dbName != this->previousDbName)
{
this->dataBase->close();
this->openDatabase(dbName);
this->previousDbName = dbName;
QSqlQuery sqlQuery(*this->dataBase);
sqlQuery.exec("PRAGMA synchronous=OFF;");
}
if (this->dataBase->isOpen())
{
// qDebug() << "Received query : {" << query << "}";
QSqlQuery sqlQuery(*this->dataBase);
sqlQuery.prepare(query);
if (!sqlQuery.exec())
qDebug() << "YOU SHOULD CHECK YOUR SQL QUERY FOR REQUEST id : " << id << " request : " << query ;
results.push_back(sqlQuery.record());
while (sqlQuery.next())
results.push_back(sqlQuery.record());
}
emit resultFromSQLQuery(results, sender, id, data);
}
bool PushDAO::createPushTable()
{
const QString query("CREATE TABLE IF NOT EXISTS push (seqnum INTEGER PRIMARY KEY AUTOINCREMENT, pushdate TEXT, type TEXT, pushtime TEXT, extension TEXT, content BLOB, unread INTEGER);");
// Execute the query.
QSqlQuery sqlQuery(query, SQLConnection());
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
return false;
}
return true;
}
bool PushDAO::removeAll()
{
const QString query("DROP TABLE push;");
// Execute the query.
QSqlQuery sqlQuery(query, SQLConnection());
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
return false;
}
return true;
}
void BookmarkModel::insert(const QString& name, const QString& url)
{
if (url.isEmpty() || contains(url))
return;
QModelIndex index = QModelIndex();
beginInsertRows(index, rowCount(index), rowCount(index));
QSqlQuery sqlQuery(database());
static QString insertStatement = QLatin1String("INSERT INTO bookmarks (name, url, dateAdded) VALUES (?, ?, ?)");
sqlQuery.prepare(insertStatement);
sqlQuery.addBindValue(name);
sqlQuery.addBindValue(url);
sqlQuery.addBindValue(QDateTime::currentDateTime().toTime_t());
sqlQuery.exec();
select();
endInsertRows();
submitAll();
}
teamSolutionList dbcontroller::fetchSolutionsForTeam(int teamId) {
std::stringstream cacheKey;
std::stringstream trigger;
teamSolutionList solutions;
cacheKey.str("");
trigger.str("");
cacheKey << "challenges_" << teamId;
trigger << "teamsolved_" << teamId;
std::shared_ptr<QSqlQuery> sqlQuery(new QSqlQuery(this->db));
bool ok = doQuery(sqlQuery, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT solutions.idchallenge as idchallenge,bonus,points,is_flash from solutions inner join challenges on (challenges.idchallenge=solutions.idchallenge) where idteam=:id")) return false;
stmt->bindValue("id", teamId);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch solutions for team, aborting";
return {};
}
if (sqlQuery->size() == 0) {
//LOL empty store, but let's not bother db until it's necessary
cur_ctx->cache().store_data(
cacheKey.str(), solutions,
srv->settings().get<int>("cache.timeout.teamsolutions")
);
return solutions;
}
while (sqlQuery->next()) {
teamSolution solution;
solution.bonus = sqlQuery->record().value("bonus").toInt();
solution.points = sqlQuery->record().value("points").toInt();
solution.id = sqlQuery->record().value("idchallenge").toInt();
solution.flashChallenge = sqlQuery->record().value("is_flash").toInt();
solutions.challenges.push_back(solution);
}
cur_ctx->cache().store_data(
cacheKey.str(), solutions, makeStringSet(trigger.str()),
srv->settings().get<int>("cache.timeout.teamsolutions")
);
return solutions;
}
void BookmarkModel::remove(const QString& url)
{
if (!contains(url))
return;
QSqlQuery sqlQuery(database());
sqlQuery.prepare(QString("SELECT id FROM bookmarks WHERE url = '%1'").arg(url));
sqlQuery.exec();
sqlQuery.first();
int indexToDelete = -1;
for (int row = 0; row < rowCount(); ++row) {
if (index(row, 0).data(Qt::DisplayRole).toInt() == sqlQuery.value(0).toInt()) {
indexToDelete = row;
break;
}
}
removeRow(indexToDelete);
}
//查看中间库的数据，需要将中间库链接
void MainWindow::on_EM_options_check_PsBtn_clicked()
{
QString strTemp ;
QSqlRecord sqlRec;
ui->EM_options_check_TxtBrowser->setText("");
strTemp = ui->EM_options_check_LnEdit->text();
QSqlQuery sqlQuery(strTemp);
sqlRec = sqlQuery.record();
while(sqlQuery.next())
{
for(int i=0;i<sqlRec.count();i++)
{
strTemp=QString::number(i)+" : " +sqlQuery.value(i).toString();
ui->EM_options_check_TxtBrowser->append(strTemp);
}
}
}
teamAlertList dbcontroller::fetchAlertsForTeam(int teamId) {
std::stringstream cacheKey;
teamAlertList alerts;
cacheKey.str("");
cacheKey << "alerts_" << teamId;
std::shared_ptr<QSqlQuery> sqlQuery(new QSqlQuery(this->db));
bool ok = doQuery(sqlQuery, [&](std::shared_ptr<QSqlQuery> stmt) {
if (!stmt->prepare("SELECT text,UNIX_TIMESTAMP(timestamp) as timestamp,points from alerts where recipient=:id")) return false;
stmt->bindValue("id", teamId);
return true;
});
if (!ok) {
BOOSTER_ERROR("scoreboard") << "Could not fetch alert for team, aborting";
return {};
}
if (sqlQuery->size() == 0) {
//LOL empty store, but let's not bother db until it's necessary
cur_ctx->cache().store_data(
cacheKey.str(), alerts,
srv->settings().get<int>("cache.timeout.teamalerts")
);
// Returning empty alerts for team
return alerts;
}
while (sqlQuery->next()) {
teamAlert alert;
alert.message = sqlQuery->record().value("text").toString().toStdString();
alert.timestamp = sqlQuery->record().value("timestamp").toInt();
alert.points = sqlQuery->record().value("points").toInt();
alerts.alerts.push_back(alert);
}
cur_ctx->cache().store_data(
cacheKey.str(), alerts,
srv->settings().get<int>("cache.timeout.teamalerts")
);
return alerts;
}
// ---------------------------------------------------------------------------
// Open the Table to a specific song view
// ---------------------------------------------------------------------------
//
void CMPXHarvesterDatabaseTable::OpenItemL( const TDesC& aSongName )
{
MPX_DEBUG1("CMPXHarvesterDatabaseTable::OpenItemL <---");
// Close old view just in case
iView.Close();
HBufC* buffer = HBufC::NewLC(KStringLength); // 255 file path + 45 for single quotes
// magic, this would break if we have > 45 single quotes in a file name.
TPtr ptr = buffer->Des();
// Open all files query
// select * from files where path='path' AND filename='aSongName'
//
TParsePtrC parse( aSongName );
TBuf<KDbMaxTableCreationSQLLength> query;
query += KSelectAll;
query += KHarvesterMainTable;
query += KWhere;
query += KHarFileName;
query += KEquals;
query += KItemBracket;
ptr.Copy( KNullDesC );
FindAndReplaceSingleQuote( parse.NameAndExt(), ptr );
query += ptr;
query += KItemBracket;
query += KAnd;
query += KHarPathName;
query += KEquals;
query += KItemBracket;
ptr.Copy( KNullDesC );
FindAndReplaceSingleQuote( parse.DriveAndPath(), ptr );
query += ptr;
query += KItemBracket;
// Open the view
TDbQuery sqlQuery( query, EDbCompareFolded );
User::LeaveIfError( iView.Prepare( iDb, sqlQuery ) );
User::LeaveIfError( iView.Evaluate() );
iState = EFile;
CleanupStack::PopAndDestroy( buffer );
MPX_DEBUG1("CMPXHarvesterDatabaseTable::OpenItemL --->");
}
//--------------------------------------------------------------------------------
bool QmvSqlQuery::updateRelationSet()
{
int status = getRelation()->open( sqlQuery(), controller );
if ( controller )
controller->reset();
if ( status < 0 )
{
query_errormessage = getRelation()->lastError();
query_status = false;
} else {
query_errormessage = "";
query_status = true;
emit relationChanged( getRelation() );
}
emit queryStatus( status, getRelation() );
return query_status;
}
void loginDialog::login()
{
QString username = ui->usename_lineEdit->text();
QString password = ui->password_lineEdit->text();
if(username == "" || password == "")
{
ui->status_label->setText(emptyFieldsLabelText);
ui->status_label->setHidden(false);
}
else
{
QString dbPassword = "";
QString getPasswordQuery = QString("SELECT password FROM Staff "
"WHERE username='******'").arg(username);
QSqlQueryModel model;
model.setQuery(getPasswordQuery, *db);
dbPassword = model.record(0).value("password").toString();
if (password != dbPassword)
{
ui->status_label->setText(wrongUsernameOrPasswordText);
ui->status_label->setHidden(false);
}
else
{
parameters->loggedInUser = username;
QSqlQuery sqlQuery( *parameters->dbConnection->getDbPtr() );
QString isAdminQuery = QString("SELECT * FROM Staff WHERE username='******'").arg(username);
sqlQuery.exec(isAdminQuery);
if( sqlQuery.next() && 1 == sqlQuery.record().value("ifAdmin").toInt() )
parameters->isAdmin = true;
else
parameters->isAdmin = false;
done(1);
}
}
}
QVariantList PushDAO::pushes()
{
QVariantList data;
const QString query("SELECT seqnum, pushdate, type, pushtime, extension, content, unread FROM push ORDER BY seqnum desc;");
QSqlQuery sqlQuery(query, SQLConnection());
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
} else {
while (sqlQuery.next()){
data.append(retrievePush(sqlQuery).toMap());
}
}
return data;
}
bool PushDAO::remove(int pushSeqNum)
{
QSqlQuery sqlQuery(SQLConnection());
const QString query("DELETE FROM push WHERE seqnum = :seqNum;");
sqlQuery.prepare(query);
sqlQuery.bindValue(":seqNum", pushSeqNum);
sqlQuery.exec();
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
return false;
}
return true;
}
void SharedCookieJarQt::deleteCookiesForHostname(const String& hostname)
{
if (!m_database.isOpen())
return;
QList<QNetworkCookie> cookies = allCookies();
QList<QNetworkCookie>::Iterator it = cookies.begin();
QList<QNetworkCookie>::Iterator end = cookies.end();
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("DELETE FROM cookies WHERE cookieId=:cookieIdvalue"));
while (it != end) {
if (it->domain() == QString(hostname)) {
sqlQuery.bindValue(QLatin1String(":cookieIdvalue"), it->domain().append(QLatin1String(it->name())));
sqlQuery.exec();
it = cookies.erase(it);
} else
it++;
}
setAllCookies(cookies);
}
bool PushDAO::markAllAsRead()
{
QSqlQuery sqlQuery(SQLConnection());
const QString query("UPDATE push SET unread = :unread;");
sqlQuery.prepare(query);
sqlQuery.bindValue(":unread", false);
sqlQuery.exec();
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
return false;
}
return true;
}
bool SharedCookieJarQt::setCookiesFromUrl(const QList<QNetworkCookie>& cookieList, const QUrl& url)
{
if (!QNetworkCookieJar::setCookiesFromUrl(cookieList, url))
return false;
if (!m_database.isOpen())
return false;
QSqlQuery sqlQuery(m_database);
sqlQuery.prepare(QLatin1String("INSERT OR REPLACE INTO cookies (cookieId, cookie) VALUES (:cookieIdvalue, :cookievalue)"));
QVariantList cookiesIds;
QVariantList cookiesValues;
foreach (const QNetworkCookie &cookie, cookiesForUrl(url)) {
if (cookie.isSessionCookie())
continue;
cookiesIds.append(cookie.domain().append(QLatin1String(cookie.name())));
cookiesValues.append(cookie.toRawForm());
}
sqlQuery.bindValue(QLatin1String(":cookieIdvalue"), cookiesIds);
sqlQuery.bindValue(QLatin1String(":cookievalue"), cookiesValues);
sqlQuery.execBatch();
return true;
}
bool QuranDbHelper::queryDatabase(const QString query) {
if (!mDb.isOpen()){
if (!initDatabase()) {
qDebug() << "fail to open database " << AyatDataModel::mQuranDatabase;
} else {
qDebug() << "succes open database " << AyatDataModel::mQuranDatabase;
}
}
// Execute the query.
QSqlQuery sqlQuery(query, mDb);
QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "SQL reported an error for query: " << query << " error: "
<< mDb.lastError().text();
return false;
}
return true;
}
Push PushDAO::push(int pushSeqNum)
{
Push push;
QSqlQuery sqlQuery(SQLConnection());
const QString query("SELECT seqnum, pushdate, type, pushtime, extension, content, unread FROM push WHERE seqnum = :seqNum;");
sqlQuery.prepare(query);
sqlQuery.bindValue(":seqNum", pushSeqNum);
sqlQuery.exec();
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
} else {
if (sqlQuery.next()){
push = retrievePush(sqlQuery);
}
}
return push;
}
void DBConnection_POSTGRESQL::getTables(char *buffer, int *bufsize)
{
int rowseplen = 1;
char rowsep[] = "\n";
if (!buffer) {
*bufsize = 2500;
return;
}
char tsql[] = "SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%'";
DBCursor *newcursor = sqlQuery(tsql , NULL, 0, 0);
if (newcursor)
{
char *result = buffer;
char *resultptr = result;
unsigned int colsize = 0;
if (!newcursor->getEOF())
{
while (True) {
unsigned int colsize;
char *coldata = newcursor->getFieldDataBinary(1,colsize);
colsize = strlen(coldata);
if (((resultptr-result) + (int)colsize + rowseplen + 16 ) > *bufsize)
break;
memcpy(resultptr,coldata,colsize);
resultptr+=colsize;
newcursor->next();
if (newcursor->getEOF()) break;
memcpy(resultptr,rowsep,rowseplen);
resultptr+=rowseplen;
}
}
deleteCursor(newcursor->GetID());
*resultptr++ = '\0';
*bufsize = resultptr-result;
}
newcursor = NULL;
}
void commaRecon(char *fileName)
/* Do comma based reconstruction. */
{
char **words;
long start, end;
struct sqlConnection *conn = sqlConnect(database);
struct sqlResult *sr;
char **row;
FILE *f = mustOpen(fileName, "w");
start = clock1000();
words = loadWords();
end = clock1000();
printf("Time to load words: %4.3f\n", 0.001*(end-start));
start = clock1000();
sr = sqlQuery(conn, "SELECT * from commaLine");
while ((row = sqlNextRow(sr)) != NULL)
{
int wordCount = sqlUnsigned(row[1]);
int i;
char *s = row[2],*e;
int wordIx;
for (i=0; i<wordCount; ++i)
{
e = strchr(s,',');
*e++ = 0;
wordIx = sqlUnsigned(s);
s = e;
fileOutput(f,words[wordIx]);
}
}
end = clock1000();
printf("Time to comma reconstruct file: %4.3f\n", 0.001*(end-start));
sqlDisconnect(&conn);
}
QVariantList PushDAO::pushes()
{
QVariantList data;
const QString query("SELECT seqnum, pushdate, type, pushtime, extension, content, unread FROM push ORDER BY seqnum desc;");
QSqlQuery sqlQuery(query, SQLConnection());
const QSqlError err = sqlQuery.lastError();
if (err.isValid()) {
qWarning() << "Error executing SQL statement: " << query << ". ERROR: " << err.text();
} else {
const QSqlRecord record = sqlQuery.record();
while (sqlQuery.next()){
Push push(sqlQuery.value(0).toInt(),QByteArray::fromBase64(sqlQuery.value(5).toByteArray()),
sqlQuery.value(2).toString(),sqlQuery.value(4).toString(), sqlQuery.value(1).toString(),
sqlQuery.value(3).toString(), sqlQuery.value(6).toBool());
data.append(push.toMap());
}
}
return data;
}
int SqlDatabase::getScalar(const char* szSQL, int errorValue) {
SqlStatement q = sqlQuery(szSQL);
if (!q.hasRow() || q.currentRow().numFields() < 1)
return errorValue;
return q.currentRow().getIntField(0);
}
void DBConnection_ORACLE::getTables(char *p_buffer, int *p_buffer_size)
{
int t_rowdelimiter_length;
t_rowdelimiter_length = 1;
char t_rowdelimiter[1];
*t_rowdelimiter = '\n';
char t_query[] = "select tname from tab";
DBCursor *t_cursor;
t_cursor = sqlQuery(t_query, NULL, 0, 0);
if (t_cursor == NULL)
return;
char *t_result;
t_result = p_buffer;
char *t_result_ptr;
t_result_ptr = t_result;
if (p_buffer == NULL)
{
unsigned int t_total_size;
t_total_size = 0;
while (1)
{
unsigned int t_column_size;
t_column_size = 0;
char *t_column_data;
t_column_data = t_cursor -> getFieldDataBinary(1, t_column_size);
t_column_size = strlen(t_column_data);
t_total_size += t_column_size + t_rowdelimiter_length;
t_cursor -> next();
if (t_cursor -> getEOF())
break;
}
t_cursor -> getConnection() -> deleteCursor(t_cursor -> GetID());
*p_buffer_size = t_total_size;
return;
}
else
{
while (1)
{
unsigned int t_column_size;
t_column_size = 0;
char *t_column_data;
t_column_data = t_cursor -> getFieldDataBinary(1, t_column_size);
t_column_size = strlen(t_column_data);
if (((t_result_ptr - t_result) + (int)t_column_size + t_rowdelimiter_length + 1) >= *p_buffer_size)
break;
memcpy(t_result_ptr, t_column_data, t_column_size);
t_result_ptr += t_column_size;
t_cursor -> next();
if (t_cursor -> getEOF())
break;
memcpy(t_result_ptr, t_rowdelimiter, t_rowdelimiter_length);
t_result_ptr += t_rowdelimiter_length;
}
*t_result_ptr = '\0';
}
t_cursor -> getConnection() -> deleteCursor(t_cursor -> GetID());
t_cursor = NULL;
}
