void tst_QSqlDriver::primaryIndex()
{
QFETCH_GLOBAL(QString, dbName);
QSqlDatabase db = QSqlDatabase::database(dbName);
CHECK_DATABASE(db);
QString tablename = qTableName("relTEST1");
//check that we can get primary index using unquoted mixed case table name
QSqlIndex index = db.driver()->primaryIndex(tablename);
QCOMPARE(index.count(), 1);
if( db.driverName().startsWith("QIBASE") || db.driverName().startsWith("QOCI") || db.driverName().startsWith("QDB2"))
QCOMPARE(index.fieldName(0), QString::fromLatin1("ID"));
else
QCOMPARE(index.fieldName(0), QString::fromLatin1("id"));
//check that we can get the primary index using a quoted tablename
if( db.driverName().startsWith("QIBASE") || db.driverName().startsWith("QOCI") || db.driverName().startsWith("QDB2"))
tablename = tablename.toUpper();
else if (db.driverName().startsWith("QPSQL"))
tablename = tablename.toLower();
if(!db.driverName().startsWith("QODBC") && !db.databaseName().contains("PostgreSql")) {
index = db.driver()->primaryIndex(db.driver()->escapeIdentifier(tablename, QSqlDriver::TableName));
QCOMPARE(index.count(), 1);
}
if( db.driverName().startsWith("QIBASE") || db.driverName().startsWith("QOCI") || db.driverName().startsWith("QDB2"))
QCOMPARE(index.fieldName(0), QString::fromLatin1("ID"));
else
QCOMPARE(index.fieldName(0), QString::fromLatin1("id"));
//check that we can not get the primary index using a quoted but incorrect table name casing
if( db.driverName().startsWith("QIBASE") || db.driverName().startsWith("QOCI") || db.driverName().startsWith("QDB2"))
tablename = tablename.toLower();
else if (db.driverName().startsWith("QPSQL"))
tablename = tablename.toUpper();
index = db.driver()->primaryIndex(db.driver()->escapeIdentifier(tablename, QSqlDriver::TableName));
if (tst_Databases::isMySQL(db)
|| db.driverName().startsWith("QSQLITE")
|| db.driverName().startsWith("QTDS")
|| tst_Databases::isSqlServer(db)
|| tst_Databases::isMSAccess(db))
QCOMPARE(index.count(), 1); //mysql will always find the table name regardless of casing
else
QCOMPARE(index.count(), 0);
}
MyInfoScheme::MyInfoScheme(const QString &connectname, QObject *parent) :
QObject(parent)
{
connectionname = connectname;
QSqlDatabase db = QSqlDatabase::database(connectname);
if(!db.open())
{
sqlerror = db.lastError();
error = true;
return;
}
dbname = db.databaseName();
query = new QSqlQuery(db);
error = false;
}
QDebug operator<<(QDebug dbg, const QSqlDatabase &d)
{
QDebugStateSaver saver(dbg);
dbg.nospace();
dbg.noquote();
if (!d.isValid()) {
dbg << "QSqlDatabase(invalid)";
return dbg;
}
dbg << "QSqlDatabase(driver=\"" << d.driverName() << "\", database=\""
<< d.databaseName() << "\", host=\"" << d.hostName() << "\", port=" << d.port()
<< ", user=\"" << d.userName() << "\", open=" << d.isOpen() << ')';
return dbg;
}
void CoreDbHelper::onCreate(QSqlDatabase &database) {
database.transaction();
QSqlQuery query(database);
if ( !query.exec(CREATE_TABLE_BOOKMARK) ) {
QWARNING_EXP("CoreDbHelper::onCreate()", query.lastError().text());
}
if ( !query.exec(CREATE_TABLE_HISTORY) ) {
QWARNING_EXP("CoreDbHelper::onCreate()", query.lastError().text());
}
if ( !database.commit() ) {
QDEBUG_EXP("CoreDbHelper::onCreate()", "DB: " << database.databaseName() << " - " << query.lastError().text());
QDEBUG_EXP("CoreDbHelper::onCreate()", "Last Query: " << query.lastQuery());
QWARNING_EXP("CoreDbHelper::onCreate()", "Couldn't create tables");
} else {
QDEBUG_EXP("CoreDbHelper::onCreate()", "Done.");
}
}
/**
* Ajoute une base au pool de connexions.
*
* @param driver
* Voir doc Qt
* @param host
* Hôte distant ou local
* @param user
* Nom d'utilisateur
* @param pswd
* Mot de passe
* @param dbnm
* Base de données
* @param save
* Déclenche l'enregistrement de la liste ou non
*/
int DbManagerPrivate::addDatabase(QString driver, QString host, QString user,
QString pswd, QString dbnm, bool save)
{
QSqlDatabase db = QSqlDatabase::addDatabase(driver, genConnectionName());
db.setHostName(host);
db.setUserName(user);
db.setPassword(pswd);
db.setDatabaseName(dbnm);
foreach(QSqlDatabase *d, dbList)
{
// on contrôle les éventuels doublons
if (d->hostName() == db.hostName() &&
d->userName() == db.userName() &&
d->password() == db.password() &&
d->databaseName() == db.databaseName())
{
LogDialog::instance()->append(
QObject::tr("Attempting to add an existing connection."));
return indexOf(d);
}
}
QSqlDatabase *newDb = new QSqlDatabase(db);
dbList.append(newDb);
dbMap[newDb] = new QStandardItem(dbTitle(newDb));
dbMap[newDb]->setEditable(false);
dbMap[newDb]->setData(DbManager::DbItem, Qt::UserRole);
dbMap[newDb]->setIcon(IconManager::get("connect_no"));
dbMap[newDb]->setToolTip(dbToolTip(newDb));
// if(!isRunning())
// start();
m_model->appendRow(dbMap[newDb]);
if(save)
{
saveList();
LogDialog::instance()->append(QObject::tr("Database %1 on %2 added")
.arg(dbnm)
.arg(host));
}
return dbList.size() - 1;
}
ImporterSqlConnection::~ImporterSqlConnection()
{
if( isTransaction() )
{
QSqlDatabase db = connection();
if( db.isOpen() )
{
warning() << __PRETTY_FUNCTION__ << "Rolling back unfinished transaction for"
<< "database" << db.databaseName() << "(" << db.hostName() << ":"
<< db.port() << ")";
db.rollback();
}
}
QSqlDatabase::removeDatabase( m_connectionName );
}
/*!
\internal
Constructs a connection for the calling thread. The resulting class must
never be accessed from any other thread than the one for which it was
constructed. The \a masterName must reference an existing database
connection which is already open.
*/
QxtSqlThreadManager::QxtSqlThreadManager(const QString &masterName)
{
// Build a name for the new connection
this->name = QString("$qxt$tc_%1$%2")
.arg((unsigned long)(void*)QThread::currentThreadId(), 0, 36)
.arg(masterName);
// Clone the primary thread's connection
Q_ASSERT(QSqlDatabase::contains(masterName));
Q_ASSERT(QSqlDatabase::database(masterName).isOpen() == true);
QSqlDatabase conn = QSqlDatabase::cloneDatabase(
QSqlDatabase::database(masterName), this->name);
// Open the connection (should not fail but ...)
if(!conn.open())
qWarning() << Q_FUNC_INFO
<< "Failed to open connection to database" << conn.databaseName()
<< ", error: " << conn.lastError().text();
qDebug() << "Constructed database connection" << this->name;
}
LibraryDB::LibraryDB(Proxy *pr) :QObject(0)
{
proxy = pr;
QMutexLocker locker(&lock);
db = QSqlDatabase::addDatabase("QSQLITE", "LibraryDB");
db.setDatabaseName(QDir::homePath()+"/.cuberok/library.db");
if(QFile::exists(db.databaseName())) {
if(!db.open()) {
qDebug("Can not open library database");
proxy->error("Can not open library database");
open = false;
} else {
open = true;
QSqlQuery q1("select value from Version", db);
int ver = 0;
if(q1.next()) ver = q1.value(0).toString().toInt();
if(ver < DB_VERSION) {
open = updateDatabase(ver);
if(!open) db.close();
} else if(ver > DB_VERSION) {
open = false;
db.close();
qDebug("Wrong database version (%d)", ver);
proxy->error("Wrong database version -" + QString::number(ver));
}
}
} else {
if(!QDir().mkpath(QDir::homePath()+"/.cuberok") || !db.open()) {
qDebug("Can not create database");
proxy->error("Can not create database");
open = false;
} else {
QSqlQuery q0("create table Version (value integer)", db);
QSqlQuery q1("insert into Version (value) values ("+QString::number(DB_VERSION)+")", db);
QSqlQuery q2("create table Playlist (ID integer primary key autoincrement, value varchar(200), refs integer, rating integer, art varchar(250), list varchar(250))", db);
FillFromCollection();
open = true;
}
}
if(open) proxy->message("LibraryDB ready");
}
bool Hourglass::setupDatabase()
{
bool ok = true;
QSettings settings;
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
if (settings.contains("databasePath")) {
database.setDatabaseName(settings.value("databasePath").toString());
}
else {
#ifdef Q_OS_LINUX
QDir path(QDir::home().path());
if ((path.exists(".hourglass") || path.mkdir(".hourglass")) && path.cd(".hourglass")) {
QString databasePath = path.absoluteFilePath("hourglass.db");
database.setDatabaseName(databasePath);
settings.setValue("databasePath", databasePath);
}
#else
// Non-Linux
database.setDatabaseName("hourglass.db");
#endif
}
if (database.databaseName().isEmpty() || !database.open()) {
// Try to use in-memory database
database.setDatabaseName(":memory:");
if (database.open()) {
QMessageBox::warning(NULL, "In memory database", "Couldn't create a local database; using an in-memory database instead. Your activities will not be saved.");
}
else {
QMessageBox::critical(NULL, "Database", "Couldn't create a local database or use an in-memory database. Closing...");
ok = false;
}
}
if (ok) {
migrateDatabase(database);
}
return ok;
}
QString TSocket::convertString(const QString &str)
{
//get_currency_properties(1)
QSqlDatabase db = QSqlDatabase::database();
if( !db.isValid() )
return str;
QString string = db.databaseName();
QString a = "CALL ";
if( !string.isNull() && !string.isEmpty() )
{
a += string;
a += ".";
}
a += "p_";
a += str;
a += ";";
return a;
}
void DatabaseConnectionDialog::validDatabaseAndAccept() {
try {
QSqlDatabase database = QSqlDatabase::addDatabase("QMYSQL");
database.setHostName(currentProperties->getHostAddress());
if(database.hostName().isEmpty())
throw QString("hostName cannot be empty !");
database.setDatabaseName(currentProperties->getDatabaseName());
if(database.databaseName().isEmpty())
throw QString("databaseName cannot be empty !");
database.setUserName(currentProperties->getUserName());
if(database.userName().isEmpty())
throw QString("userName cannot be empty !");
database.setPassword(currentProperties->getPassword());
if(!database.open())
throw QString("unable to reach the database...");
else
qDebug() << "database properties OK. Ready to be sent !";
database.close();
emit this->propertiesValidated(currentProperties);
qDebug() << "properties sent !";
//this->accept();
this->close();
}
catch(const QString& exception) {
QMessageBox msgBox;
msgBox.setText(exception);
msgBox.setInformativeText("Check the connection information");
msgBox.setStandardButtons(QMessageBox::Ok);
int ret = msgBox.exec();
switch(ret) {
case QMessageBox::Ok:
ui->passwordEdit->setText("");
currentProperties->setPassword("");
break;
default:
;
}
}
}
void MainWindow::on_Update_clicked()
{
QSqlDatabase* updatedB = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE","updatedB"));
updatedB->setDatabaseName(QString(QDir::homePath() + "/.sqlitedb/symbol_list.sqlite"));
if(!updatedB->open())
{
qDebug() << "Error opening" << updatedB->databaseName();
}
if(updatedB->isValid() && updatedB->isOpen())
{
QSqlQueryModel *model = new QSqlQueryModel(this);
QSqlQuery *query = new QSqlQuery(*updatedB);
query->prepare("SELECT * FROM symbol_list");
query->exec();
model->setQuery(*query);
ui->integratedStocks->setModel(model);
// ui->integratedStocks->sortByColumn(0);
delete query;
}
}
Database::Database() :QObject(0), subset(false), ssAlbum(0), fillingProgress(0), filling(false)
{
QMutexLocker locker(&lock);
db = QSqlDatabase::addDatabase("QSQLITE", "CollectionDB");
db.setDatabaseName(QDir::homePath()+"/.cuberok/collection.db");
// db.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE=1");
if(QFile::exists(db.databaseName())) {
if(!db.open()) {
qDebug("Can not open database");
proxy->error("Can not open database");
open = false;
} else {
open = true;
QSqlQuery q1("select value from Version", db);
int ver = -1;
if(q1.next()) ver = q1.value(0).toString().toInt();
if(ver == -1) {
open = createDatabase();
} else if(ver < DB_VERSION) {
open = updateDatabase(ver);
if(!open) db.close();
} else if(ver > DB_VERSION) {
open = false;
db.close();
qDebug("Wrong database version (%d)", ver);
proxy->error("Wrong database version -" + QString::number(ver));
}
}
} else {
if(!QDir().mkpath(QDir::homePath()+"/.cuberok") || !db.open()) {
qDebug("Can not create database");
proxy->error("Can not create database");
open = false;
} else {
open = createDatabase();
}
}
if(open) proxy->message("Database ready");
}
TractionTypeView::TractionTypeView(Configuration *cfg, QObject *parent) :
QObject(parent)
{
m_parent = parent;
config = Configuration::instance();
//sql->setConfig(config);
sql = SQL::instance();
mainWindow* myParent = qobject_cast<mainWindow*>(m_parent);
tableView = myParent->ui->tblTractionTypes;
tableView->verticalHeader()->resize(2,20);
QSqlDatabase db = QSqlDatabase::database();
qDebug()<<db.databaseName();
connect(tableView->verticalHeader(), SIGNAL(sectionCountChanged(int,int)), this, SLOT(Resize(int,int)));
tableView->setAlternatingRowColors(true);
model = new MyTractionTypesTableModel(this, db);
model->setTable("TractionTypes");
model->setEditStrategy(QSqlTableModel::OnFieldChange);
model->query().setForwardOnly(false);
model->select();
QString name = model->record(0).value("description").toString();
tableView->setModel(model);
menu = new QMenu();
addAct = new QAction(tr("Add Traction Type"),this);
addAct->setToolTip(tr("Add a new traction type"));
connect(addAct, SIGNAL(triggered()), this, SLOT(newRecord()));
delAct = new QAction(tr("Delete Traction Type"),this);
delAct->setToolTip(tr("Delete an existing traction type"));
connect(delAct, SIGNAL(triggered()), this, SLOT(delRecord()));
tableView->setContextMenuPolicy(Qt::CustomContextMenu);
connect(tableView, SIGNAL(customContextMenuRequested( const QPoint& )), this, SLOT(tablev_customContextMenu( const QPoint& )));
tableView->show();
}
void LibraryDB::FillFromCollection()
{
QSqlDatabase cdb;
cdb = QSqlDatabase::addDatabase("QSQLITE");
cdb.setDatabaseName(QDir::homePath()+"/.cuberok/collection.db");
if(QFile::exists(db.databaseName())) {
if(cdb.open()) {
QSqlQuery q1("select value from Version", cdb);
int ver = 0;
if(q1.next()) ver = q1.value(0).toString().toInt();
if(ver <= 7) {
QSqlQuery q("", cdb);
q.prepare("select value, art from Playlist order by value ASC");
q.exec();
while(q.next()) {
QString list = q.value(0).toString();
AddPlaylist(list);
ArtForPlaylist(list, q.value(1).toString());
}
}
cdb.close();
}
}
}
void Options::loadOptions()
{
QSqlDatabase db = QSqlDatabase::database("nutrition_db");
QSettings settings("Nerdland", "Nutrition Tracker");
settings.beginGroup("db");
ui.chkSaveConnection->setChecked(settings.contains("server"));
ui.txtDBServer->setText(settings.value("server", db.hostName()).toString());
ui.txtDBDatabase->setText(settings.value("database", db.databaseName()).toString());
ui.txtDBUsername->setText(settings.value("username", db.userName()).toString());
ui.txtDBPassword->setText(unfrobPassword(settings.value("password", frobPassword(db.password())).toByteArray()));
settings.endGroup();
settings.beginGroup("user");
ui.chkSaveAccount->setChecked(settings.contains("username"));
ui.txtUserUsername->setText(User::getLoggedInUser()->getUsername());
ui.txtUserRealName->setText(User::getLoggedInUser()->getRealName());
ui.txtUserPassword->setText(unfrobPassword(settings.value("password").toByteArray()));
settings.endGroup();
settings.beginGroup("preferences");
ui.chkDefaultToIncludeRefuse->setChecked(settings.value("includerefuse", true).toBool());
settings.endGroup();
}
bool BazaDanych::polacz(){
// nawiazanie polaczenia z baza
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("baza.db");
if(!db.open()) {
qDebug() << "Nie udalo sie poalczyc z baza danych" << db.databaseName() << db.lastError().text();
return false;
}
// sprawdzamy czy tabele w bazie danych istnieją
if(!db.tables().count()) {
db.exec("CREATE TABLE pociski ("
" pocisk_id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
" nazwa varchar(255), "
" zasieg integer, "
" predkosc integer, "
" silaRazenie integer, "
" promienRazenie integer, "
" animacjaSzerokosc smallint, "
" animacjaWysokosc smallint, "
" animacjaCzas integer "
");");
db.exec("CREATE TABLE plansze ("
" mapa_id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
" tryb smallint, "
" plik varchar(255) "
");");
db.exec("CREATE TABLE pojazdy ("
" pojazd_id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
" nazwa varchar(255), "
" przesuniecieOsKorpus float(6,2), "
" przesuniencieOsWieza float(6,2), "
" predkoscMaksymalna float(6,2), "
" predkoscWiezy float(6,2), "
" wytrzymalosc integer "
");");
}
// baza ustawien
QSqlDatabase dbUstawienia = QSqlDatabase::addDatabase("QSQLITE", "dbUstawienia");
QDir(QDesktopServices::storageLocation(QDesktopServices::DataLocation)).mkpath(".");
dbUstawienia.setDatabaseName(QDesktopServices::storageLocation(QDesktopServices::DataLocation) + "/ustawienia.db");
if(!dbUstawienia.open()) {
qDebug() << "Nie udalo sie poalczyc z baza danych" << dbUstawienia.databaseName() << dbUstawienia.lastError().text();
return false;
}
if(!dbUstawienia.tables().count()) {
dbUstawienia.exec("CREATE TABLE profile ("
" profil_id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
" nazwa varchar(80) UNIQUE "
");");
dbUstawienia.exec("CREATE TABLE rekordy ("
" rekord_id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
" profil_id integer, "
" wynik float(4,2), "
" mapa_id integer, "
" FOREIGN KEY (profil_id) REFERENCES profile(profil_id) "
");");
dbUstawienia.exec("CREATE TABLE ustawienia ("
" nazwa text PRIMARY KEY NOT NULL UNIQUE, "
" wartosc text "
");");
}
return true;
}
Database::Database() {
QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QSQLITE"));
QSettings qs;
QStringList datapaths;
int i;
datapaths << g.qdBasePath.absolutePath();
datapaths << QDesktopServices::storageLocation(QDesktopServices::DataLocation);
#if defined(Q_OS_UNIX) && ! defined(Q_OS_MAC)
datapaths << QDir::homePath() + QLatin1String("/.config/Mumble");
#endif
datapaths << QDir::homePath();
datapaths << QDir::currentPath();
datapaths << qApp->applicationDirPath();
datapaths << qs.value(QLatin1String("InstPath")).toString();
bool found = false;
for (i = 0; (i < datapaths.size()) && ! found; i++) {
if (!datapaths[i].isEmpty()) {
QFile f(datapaths[i] + QLatin1String("/mumble.sqlite"));
if (f.exists()) {
db.setDatabaseName(f.fileName());
found = db.open();
}
QFile f2(datapaths[i] + QLatin1String("/.mumble.sqlite"));
if (f2.exists()) {
db.setDatabaseName(f2.fileName());
found = db.open();
}
}
}
if (! found) {
for (i = 0; (i < datapaths.size()) && ! found; i++) {
if (!datapaths[i].isEmpty()) {
QDir::root().mkpath(datapaths[i]);
#ifdef Q_OS_WIN
QFile f(datapaths[i] + QLatin1String("/mumble.sqlite"));
#else
QFile f(datapaths[i] + QLatin1String("/.mumble.sqlite"));
#endif
db.setDatabaseName(f.fileName());
found = db.open();
}
}
}
if (! found) {
QMessageBox::critical(NULL, QLatin1String("Mumble"), tr("Mumble failed to initialize a database in any\nof the possible locations."), QMessageBox::Ok | QMessageBox::Default, QMessageBox::NoButton);
qFatal("Database: Failed initialization");
}
QFileInfo fi(db.databaseName());
if (! fi.isWritable()) {
QMessageBox::critical(NULL, QLatin1String("Mumble"), tr("The database '%1' is read-only. Mumble cannot store server settings (i.e. SSL certificates) until you fix this problem.").arg(fi.filePath()), QMessageBox::Ok | QMessageBox::Default, QMessageBox::NoButton);
qWarning("Database: Database is read-only");
}
QSqlQuery query;
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `servers` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `hostname` TEXT, `port` INTEGER DEFAULT 64738, `username` TEXT, `password` TEXT)"));
query.exec(QLatin1String("ALTER TABLE `servers` ADD COLUMN `url` TEXT"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `comments` (`who` TEXT, `comment` BLOB, `seen` DATE)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `comments_comment` ON `comments`(`who`, `comment`)"));
query.exec(QLatin1String("CREATE INDEX IF NOT EXISTS `comments_seen` ON `comments`(`seen`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `blobs` (`hash` TEXT, `data` BLOB, `seen` DATE)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `blobs_hash` ON `blobs`(`hash`)"));
query.exec(QLatin1String("CREATE INDEX IF NOT EXISTS `blobs_seen` ON `blobs`(`seen`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `tokens` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB, `token` TEXT)"));
query.exec(QLatin1String("CREATE INDEX IF NOT EXISTS `tokens_host_port` ON `tokens`(`digest`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `shortcut` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB, `shortcut` BLOB, `target` BLOB, `suppress` INTEGER)"));
query.exec(QLatin1String("CREATE INDEX IF NOT EXISTS `shortcut_host_port` ON `shortcut`(`digest`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `udp` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `udp_host_port` ON `udp`(`digest`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `cert` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hostname` TEXT, `port` INTEGER, `digest` TEXT)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `cert_host_port` ON `cert`(`hostname`,`port`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `friends` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `hash` TEXT)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `friends_name` ON `friends`(`name`)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `friends_hash` ON `friends`(`hash`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `muted` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` TEXT)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `muted_hash` ON `muted`(`hash`)"));
query.exec(QLatin1String("CREATE TABLE IF NOT EXISTS `pingcache` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hostname` TEXT, `port` INTEGER, `ping` INTEGER)"));
query.exec(QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `pingcache_host_port` ON `pingcache`(`hostname`,`port`)"));
query.exec(QLatin1String("DELETE FROM `comments` WHERE `seen` < datetime('now', '-1 years')"));
query.exec(QLatin1String("DELETE FROM `blobs` WHERE `seen` < datetime('now', '-1 months')"));
query.exec(QLatin1String("VACUUM"));
query.exec(QLatin1String("PRAGMA synchronous = OFF"));
query.exec(QLatin1String("PRAGMA journal_mode = TRUNCATE"));
query.exec(QLatin1String("SELECT sqlite_version()"));
while (query.next())
qWarning() << "Database SQLite:" << query.value(0).toString();
}
//Fillup the QStringList of tables in insertion order
int getTableOrder(QSqlDatabase db)
{
QSqlQuery qtables(db);
QString sql;
//Get all the tables that are parents
sql = "SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
sql = sql + " REFERENCED_TABLE_NAME is not null";
if (qtables.exec(sql))
{
while (qtables.next())
{
tables.append(qtables.value(0).toString());
}
}
else
return 1;
//Get only neccesary references to workout the order in parent tables that are also parents
sql = "SELECT table_name,REFERENCED_TABLE_NAME FROM";
sql = sql + " information_schema.KEY_COLUMN_USAGE WHERE";
sql = sql + " table_schema = '" + db.databaseName() + "' AND";
sql = sql + " REFERENCED_TABLE_NAME is not null AND";
sql = sql + " table_name IN (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
sql = sql + " REFERENCED_TABLE_NAME is not null)";
sql = sql + " group by table_name,REFERENCED_TABLE_NAME";
references = QSqlQuery(db);
references.exec(sql);
//Because a parent table can also be a child we need to organize the list so childs are after parents
int pos;
int parentIndex;
QString table;
for (pos = 0; pos <= tables.count()-1; pos++)
{
//Get the maximum index if the parent of this table
parentIndex = getMaxParentIndex(tables[pos]);
if (pos < parentIndex) //If the position of this table is before the max parent index
{
table = tables[pos]; //Get the table name
tables.removeAt(pos); //Remove the table from the list
tables.insert(parentIndex+1,table); //Insert it back in the position after the last parent
pos = 0; //Back to beginning of the process
}
}
//Append the tables that have no childs
sql = "SELECT DISTINCT information_schema.tables.table_name FROM information_schema.tables WHERE";
sql = sql + " information_schema.tables.table_schema = '" + db.databaseName() + "' AND";
sql = sql + " table_type = 'BASE TABLE' AND";
sql = sql + " information_schema.tables.table_name NOT IN";
sql = sql + " (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
sql = sql + " REFERENCED_TABLE_NAME is not null)";
if (qtables.exec(sql))
{
while (qtables.next())
{
tables.append(qtables.value(0).toString());
}
}
return 0;
}
int loadMETATables(QSqlDatabase mydb, bool deletePrevious, bool includeViews)
{
QSqlQuery query(mydb);
QStringList sqls;
int pos;
bool error;
error = false;
if (deletePrevious)
{
sqls << "DELETE FROM dict_lkpiso639";
sqls << "DELETE FROM dict_dctiso639";
sqls << "DELETE FROM dict_iso639";
sqls << "DELETE FROM audit_log";
sqls << "DELETE FROM dict_childinfo";
sqls << "DELETE FROM dict_relinfo";
sqls << "DELETE FROM dict_clminfo";
sqls << "DELETE FROM user_log";
sqls << "DELETE FROM auth_user_user_permissions";
sqls << "DELETE FROM auth_user_groups";
sqls << "DELETE FROM auth_user";
sqls << "DELETE FROM auth_group_permissions";
sqls << "DELETE FROM auth_permission";
sqls << "DELETE FROM dict_tblinfo";
sqls << "DELETE FROM dict_grpinfo";
sqls << "DELETE FROM auth_group";
for (pos = 0; pos <= sqls.count()-1;pos++)
{
query.exec(sqls[pos]);
}
}
QString sqlStr;
QSqlQuery query2(mydb);
QSqlQuery query3(mydb);
QSqlQuery query4(mydb);
//Start of procees
if (!includeViews)
sqlStr = "select table_name from information_schema.tables where table_schema = '" + mydb.databaseName() + "' and table_type = 'BASE TABLE'";
else
sqlStr = "select table_name from information_schema.tables where table_schema = '" + mydb.databaseName() + "'";
query.exec(sqlStr);
QString tablename;
QString fieldname;
QString fieldtype;
bool mainDesc;
bool key;
int poscol;
int tbllkp;
while (query.next())
{
tablename = query.value(0).toString().trimmed();
if (!isIgnoreTable(tablename))
{
if (tablename.toLower().left(3) == "lkp")
tbllkp = 1;
else
tbllkp = 0;
sqlStr = "INSERT INTO dict_tblinfo (tbl_cod,tbl_des,tbl_lkp,tbl_pos) VALUES (";
sqlStr = sqlStr + "'" + tablename.toLower() + "',";
sqlStr = sqlStr + "'Description of " + tablename + "',";
sqlStr = sqlStr + "" + QString::number(tbllkp) + "," + QString::number(tables.indexOf(tablename)) + ")";
if (!query2.exec(sqlStr))
{
log("Error inserting in Table Information");
log(query2.lastError().databaseText());
return 1;
}
sqlStr = "desc " + tablename;
query3.exec(sqlStr);
poscol = 0;
mainDesc = false;
while (query3.next())
{
poscol++;
fieldname = query3.value(0).toString().trimmed();
fieldtype = query3.value(1).toString().trimmed();
if (query3.value(3).toString().trimmed() == "PRI")
key = true;
else
key = false;
sqlStr = "INSERT INTO dict_clminfo (tbl_cod,clm_cod,clm_pos,clm_des,clm_key,clm_typ,clm_maindesc) VALUES (";
sqlStr = sqlStr + "'" + tablename.toLower() + "',";
sqlStr = sqlStr + "'" + fieldname + "',";
sqlStr = sqlStr + QString::number(poscol) + ",";
sqlStr = sqlStr + "'Description of " + fieldname + "',";
if (key)
sqlStr = sqlStr + "1,";
else
sqlStr = sqlStr + "0,";
sqlStr = sqlStr + "'" + fieldtype + "',";
if (mainDesc == false)
{
if (fieldname.contains("_des") || fieldname.contains("_name"))
{
mainDesc = true;
sqlStr = sqlStr + "1)";
}
else
{
sqlStr = sqlStr + "0)";
}
}
else
sqlStr = sqlStr + "0)";
if (!query4.exec(sqlStr))
{
log("Error inserting in Column Information");
log(query4.lastError().databaseText());
return 1;
}
}
}
}
if (!includeViews)
sqlStr = "select table_name from information_schema.tables where table_schema = '" + mydb.databaseName() + "' and table_type = 'BASE TABLE'";
else
sqlStr = "select table_name from information_schema.tables where table_schema = '" + mydb.databaseName() + "'";
query.exec(sqlStr);
QString errorMsg;
while (query.next())
{
tablename = query.value(0).toString().trimmed();
if (!isIgnoreTable(tablename))
{
sqlStr = "select table_name,column_name,REFERENCED_TABLE_NAME,";
sqlStr = sqlStr + "REFERENCED_COLUMN_NAME,CONSTRAINT_NAME from ";
sqlStr = sqlStr + "information_schema.KEY_COLUMN_USAGE where ";
sqlStr = sqlStr + "table_schema = '" + mydb.databaseName() + "' and ";
sqlStr = sqlStr + "table_name = '" + tablename + "' and ";
sqlStr = sqlStr + "REFERENCED_TABLE_NAME is not null";
//qDebug() << sqlStr;
query3.exec(sqlStr);
while (query3.next())
{
sqlStr = "INSERT INTO dict_relinfo (tbl_cod,clm_cod,rtbl_cod,rclm_cod,cnt_name,error_msg,error_notes) VALUES (";
sqlStr = sqlStr + "'" + query3.value(0).toString().trimmed().toLower() + "',";
sqlStr = sqlStr + "'" + query3.value(1).toString().trimmed() + "',";
sqlStr = sqlStr + "'" + query3.value(2).toString().trimmed().toLower() + "',";
sqlStr = sqlStr + "'" + query3.value(3).toString().trimmed() + "',";
sqlStr = sqlStr + "'" + query3.value(4).toString().trimmed() + "',";
errorMsg = "The value in column \"" + query3.value(1).toString().trimmed() + "\" is not included in the list of possible values in table \"" + query3.value(2).toString().trimmed() + "\"";
errorMsg = errorMsg + ". Correct the value in table \"" + query3.value(0).toString().trimmed() + "\" or add the value to table \"" + query3.value(2).toString().trimmed() + "\"";
sqlStr = sqlStr + "'" + errorMsg + "','')";
if (!query4.exec(sqlStr))
{
log("Error inserting in the Relation Table");
log(query4.lastError().databaseText());
}
}
}
}
if (!error)
log("META's tables sucessfully loaded");
return 0;
}
QString ibanBicData::iban2Bic(const QString& iban)
{
Q_ASSERT(iban.length() < 1 || iban.at(0).isLetterOrNumber());
Q_ASSERT(iban.length() < 2 || iban.at(1).isLetterOrNumber());
Q_ASSERT(iban == payeeIdentifiers::ibanBic::ibanToElectronic(iban));
if (iban.length() <= 4) // This iban is to short to extract a BIC
return QString("");
// Get bank identifier
const QString bankCode = extractBankIdentifier(iban);
if (bankCode.isEmpty())
return bankCode; // keep .isEmpty() or .isNull()
// Get countryCode
const QString countryCode = iban.left(2);
// Get services which support iban2bic and have a database entry
KService::List services = KServiceTypeTrader::self()->query("KMyMoney/IbanBicData",
QString("(\'%1' ~in [X-KMyMoney-CountryCodes] or '*' in [X-KMyMoney-CountryCodes]) and true == [X-KMyMoney-IBAN-2-BIC-supported] and exist [X-KMyMoney-Bankdata-Database]").arg(countryCode)
);
if (services.isEmpty())
return QString();
QSqlDatabase db = createDatabaseConnection(services.first()->property(QLatin1String("X-KMyMoney-Bankdata-Database"), QVariant::String).toString());
if (!db.isOpen()) // This is an error
return QString();
QSqlQuery query = QSqlQuery(db);
query.prepare("SELECT bic FROM institutions WHERE bankcode=? and country=?");
query.bindValue(0, bankCode);
query.bindValue(1, countryCode);
if (!query.exec()) {
qWarning() << QString("Could not execute query on \"%1\" to receive BIC. Error: %2").arg(db.databaseName()).arg(query.lastError().text());
return QString();
}
if (query.next()) {
return query.value(0).toString();
}
return QString("");
}
ibanBicData::bicAllocationStatus ibanBicData::isBicAllocated(const QString& bic)
{
// Get countryCode
const QString countryCode = bic.mid(4, 2);
if (countryCode.length() != 2)
return bicNotAllocated;
// Get services which have a database entry
KService::List services = KServiceTypeTrader::self()->query("KMyMoney/IbanBicData",
QString("(\'%1' ~in [X-KMyMoney-CountryCodes] or '*' in [X-KMyMoney-CountryCodes]) and exist [X-KMyMoney-Bankdata-Database]").arg(countryCode)
);
if (services.isEmpty())
return bicAllocationUncertain;
QSqlDatabase db = createDatabaseConnection(services.first()->property(QLatin1String("X-KMyMoney-Bankdata-Database"), QVariant::String).toString());
if (!db.isOpen()) // This is an error
return bicAllocationUncertain;
QSqlQuery query(db);
query.prepare("SELECT ? IN (SELECT bic FROM institutions)");
query.bindValue(0, bic);
if (!query.exec() || !query.next()) {
qWarning() << QString("Could not execute query on \"%1\" to check if bic exists. Error: %2").arg(db.databaseName()).arg(query.lastError().text());
return bicAllocationUncertain;
}
if (query.value(0).toBool()) // Bic found
return bicAllocated;
// Bic not found, test if database is complete
if (services.first()->property(QLatin1String("X-KMyMoney-Bankdata-IsComplete"), QVariant::Bool).toBool())
return bicNotAllocated;
return bicAllocationUncertain;
}
QString ibanBicData::bankNameByBic(QString bic)
{
if (bic.length() == 8)
bic += QLatin1String("XXX");
else if (bic.length() != 11)
return QString();
const QString countryCode = bic.mid(4, 2);
// Get services which have a database entry
KService::List services = KServiceTypeTrader::self()->query("KMyMoney/IbanBicData",
QString("(\'%1' ~in [X-KMyMoney-CountryCodes] or '*' in [X-KMyMoney-CountryCodes]) and exist [X-KMyMoney-Bankdata-Database]").arg(countryCode)
);
if (services.isEmpty())
return QString();
QSqlDatabase db = createDatabaseConnection(services.first()->property("X-KMyMoney-Bankdata-Database", QVariant::String).toString());
if (!db.isOpen()) // This is an error
return QString();
QSqlQuery query = QSqlQuery(db);
query.prepare("SELECT name FROM institutions WHERE bic=?");
query.bindValue(0, bic);
if (!query.exec()) {
qWarning() << QString("Could not execute query on \"%1\" to receive bank name. Error: %2").arg(db.databaseName()).arg(query.lastError().text());
return QString();
}
if (query.next()) {
return query.value(0).toString();
}
return QString("");
}
int main(int argc, char *argv[])
{
QCoreApplication application(argc, argv);
application.addLibraryPath(".");
QTextStream out(stdout, QIODevice::WriteOnly);
if (application.argc() > 1)
{
QString databaseURL;
QString username;
QString passwd;
QString arguments;
QString xml_file = QString::null;
int report_grade = 0;
for (int counter = 1; counter < application.argc(); counter++)
{
QString arguments(application.argv()[counter]);
if (arguments.startsWith("-databaseURL=", Qt::CaseInsensitive))
databaseURL = arguments.right(arguments.length() - 13);
else if (arguments.startsWith("-username="******"-passwd=", Qt::CaseInsensitive))
passwd = arguments.right(arguments.length() - 8);
else if (arguments.startsWith("-f=", Qt::CaseInsensitive))
xml_file = arguments.right(arguments.length() - 3);
else if (arguments.startsWith("-grade=", Qt::CaseInsensitive))
report_grade = (arguments.right(arguments.length() - 7)).toInt();
else if(!arguments.startsWith("-"))
xml_file = arguments;
}
QString report_name = QString::null;
QString report_desc = QString::null;
QString report_src = QString::null;
if(xml_file != "") {
QFile file(xml_file);
if(file.open( QIODevice::ReadOnly )) {
QDomDocument doc;
QString errMsg;
int errLine, errCol;
if(doc.setContent(&file, &errMsg, &errLine, &errCol)) {
QDomElement root = doc.documentElement();
if(root.tagName() == "report") {
for(QDomNode n = root.firstChild();
!n.isNull(); n = n.nextSibling() ) {
if(n.nodeName() == "name") {
report_name = n.firstChild().nodeValue();
} else if(n.nodeName() == "description") {
report_desc = n.firstChild().nodeValue();
}
}
report_src = doc.toString();
if(report_name == "") {
out << "The document " << xml_file << " does not have a report name defined." << endl;
}
} else {
out << "XML Document " << xml_file << " does not have root node of report." << endl;
}
} else {
out << "Error parsing file " << xml_file << ": " << errMsg << " on line " << errLine << " column " << errCol << endl;
}
} else {
out << "Could not open the specified file: " << xml_file << endl;
}
} else {
out << "You must specify an XML file to load by using the -f= parameter." << endl;
}
if(report_name == "" || report_src == "") {
// an error message already should have been displayed to the user
exit(-1);
}
if ( (databaseURL != "") &&
(username != "") &&
(passwd != "") ) {
QSqlDatabase db;
// Open the Database Driver
db = databaseFromURL( databaseURL );
if (!db.isValid())
{
out << "Could not load the specified database driver." << endl;
exit(-1);
}
// Try to connect to the Database
db.setUserName(username);
db.setPassword(passwd);
if (!db.open())
{
out << "Host=" << db.hostName() << ", Database=" << db.databaseName() << ", port=" << db.port() << endl;
out << "Could not log into database. System Error: "
<< db.lastError().text() << endl;
exit(-1);
}
QSqlQuery().exec(getSqlFromTag("fmt05", db.driverName())); // MANU
// first we need to determine if there is already a report in the database of the same
// name and if so then we will perform an update instead of an insert
QSqlQuery qry;
qry.prepare(getSqlFromTag("fmt09", db.driverName())); // MANU
qry.bindValue(":report_name", report_name);
qry.bindValue(":report_grade", report_grade);
qry.exec();
QSqlQuery query;
if(qry.first()) {
// update
query.prepare(getSqlFromTag("fmt10", db.driverName())); // MANU
query.bindValue(":report_desc", report_desc);
query.bindValue(":report_src", report_src);
query.bindValue(":report_id", qry.value(0));
query.bindValue(":report_name", report_name);
} else {
// insert
query.prepare(getSqlFromTag("fmt11", db.driverName())); // MANU
query.bindValue(":report_name", report_name);
query.bindValue(":report_desc", report_desc);
query.bindValue(":report_src", report_src);
query.bindValue(":report_grade", report_grade);
}
if(!query.exec()) {
QSqlError err = query.lastError();
out << "Error: " << err.driverText() << endl
<< "\t" << err.databaseText() << endl;
exit(-1);
}
}
else if (databaseURL == "")
out << "You must specify a Database URL by using the -databaseURL= parameter." << endl;
else if (username == "")
out << "You must specify a Database Username by using the -username= parameter." << endl;
else if (passwd == "")
out << "You must specify a Database Password by using the -passwd= parameter." << endl;
}
else
out << "Usage: import -databaseURL='$' -username='******' -passwd='$' -grade=# -f='$'" << endl;
return 0;
}
/**
* @brief DbManager::addDatabase
* @param driver
* @param host
* @param user
* @param pswd
* @param dbnm
* @param alias
* @param wrapperName
* @param save
* @return the index of the added db OR if the db already exists, its index
*/
int DbManager::addDatabase(QString driver, QString host, QString user,
QString pswd, QString dbnm, QString alias,
QString wrapperName, bool save) {
QSqlDatabase db = QSqlDatabase::addDatabase(driver, genConnectionName());
db.setHostName(host);
db.setUserName(user);
db.setPassword(pswd);
db.setDatabaseName(dbnm);
for (int i=0; i<m_connections.length(); i++) {
Connection* c = m_connections[i];
// checking for doubles
if (c->db()->hostName() == db.hostName() &&
c->db()->userName() == db.userName() &&
c->db()->password() == db.password() &&
c->db()->databaseName() == db.databaseName()) {
return i;
}
}
QSqlDatabase* newDb = new QSqlDatabase(db);
Connection* connection = new Connection(newDb, alias, this);
connect(connection, SIGNAL(changed()), this, SLOT(refreshModelItem()));
m_connections.append(connection);
QString title;
if (alias.isEmpty()) {
title = dbTitle(newDb);
} else {
title = alias;
}
dbMap[newDb] = new QStandardItem(title);
dbMap[newDb]->setEditable(false);
dbMap[newDb]->setData(DbManager::DbItem, Qt::UserRole);
dbMap[newDb]->setIcon(IconManager::get("database_connect"));
dbMap[newDb]->setToolTip(dbToolTip(newDb));
QList<QStandardItem*> uiItemList;
// name
uiItemList << dbMap[newDb];
// icon (= DBMS icon)
uiItemList << new QStandardItem(driverIcon[driver], "");
uiItemList[1]->setSelectable(false);
m_model->appendRow(uiItemList);
SqlWrapper *wrapper = NULL;
if (wrapperName.length() > 0) {
wrapper = PluginManager::wrapper(wrapperName);
} else if (!driver.startsWith("QODBC")) {
wrapper = PluginManager::availableWrapper(driver);
}
if (wrapper) {
dbWrappers[newDb] = wrapper->newInstance(newDb);
}
if (save) {
saveList();
}
return m_connections.size() - 1;
}
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
QTextStream out(stdout);
out << "Hello world!" << endl;
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("basic.sqlite");
bool ok = db.open();
out << "What is the database name? " << db.databaseName() << endl;
out << "Did I connect to " << db.databaseName() << "? " << ok << endl;
out << "Is the connection valid? " << db.isValid() << endl;
out << "Is the connection open? " << db.isOpen() << endl;
out << "What is the driver name? " << db.driverName() << endl;
out << "What is the host name? " << db.hostName() << endl;
out << "What is the connection name? " << db.connectionName() << endl;
out << "What are the connection options? " << db.connectOptions() << endl;
bool hasQuerySize = db.driver()->hasFeature(QSqlDriver::QuerySize);
out << "Does sqlite support querying the result set size? " << hasQuerySize << endl;
out << "Tables:";
QStringList tables = db.tables();
foreach (QString table, tables) {
out << " " << table;
}
out << endl;
QSqlQuery query;
query.setForwardOnly(true);
query.exec("SELECT * from advances");
if (hasQuerySize) {
out << "Querying advances (" << query.size() << " results):" << endl;
} else {
out << "Querying advances:" << endl;
}
QStringList columns;
QSqlRecord record = query.record();
out << "Number of columns: " << record.count() << endl;
for (int i = 0; i < record.count(); i++) {
out << "column " << i << ": " << record.fieldName(i) << endl;
columns.append(record.fieldName(i));
}
QList<QObject*> advances;
while(query.next()) {
Civilization::Advance *advance = new Civilization::Advance();
out << "advance " << advances.size() << ":";
for (int i = 0; i < columns.size(); i++) {
out << " " << columns[i] << ": " << query.value(i).toString();
QVariant v(query.value(i).toString());
advance->setProperty(columns[i].toLatin1().data(), v);
}
out << endl;
advances.append(advance);
}
out << advances.size() << " results." << endl;
db.close();
QQmlApplicationEngine engine;
engine.rootContext()->setContextProperty("advanceListModel", QVariant::fromValue(advances));
engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
return app.exec();
}
int main(int argc, char *argv[])
{
QCoreApplication application(argc, argv);
application.addLibraryPath(".");
QTextStream out(stdout, QIODevice::WriteOnly);
if (argc > 1)
{
QString databaseURL;
QString username;
QString passwd;
QString arguments;
QString dbServer = QString::null;
for (int counter = 1; counter < argc; counter++)
{
QString arguments(argv[counter]);
if (arguments.startsWith("-databaseURL=", Qt::CaseInsensitive))
databaseURL = arguments.right(arguments.length() - 13);
else if (arguments.startsWith("-username="******"-passwd=", Qt::CaseInsensitive))
passwd = arguments.right(arguments.length() - 8);
else if (arguments.startsWith("-dbengine=", Qt::CaseInsensitive))
dbServer = arguments.right(arguments.length() - 10);
}
if ( (databaseURL != "") &&
(username != "") &&
(passwd != "") )
{
QSqlDatabase db;
QString protocol = QString::null;
QString server = QString::null;
QString database = QString::null;
QString port = QString::null;
// Note: parseDatabaseURL returns a default port of 5432 (Default Postgresql port)
// is this a bug or a feature ?
parseDatabaseURL(databaseURL, protocol, server, database, port);
// treat odbc connections as a special case
if ( protocol == "odbc")
{
if ( dbServer == QString::null )
{
out << " database URL = " << databaseURL << endl;
out << "Protocol=" << protocol << ", Host=" << server << ", Database=" << database << ", port=" << port << endl;
out << "\"--dbengine=\" parameter required when url protocol is odbc" << endl;
exit(EXIT_ERROR_MISSING_DB_ENGINE);
}
if (! isValidProtocol(dbServer, false) )
{
out << "Unrecognised database server: [--dbengine=" << dbServer << "]" << endl;
out << "Protocol=" << protocol << ", Host=" << server << ", Database=" << database << ", port=" << port << endl;
exit(EXIT_ERROR_DB_ENGINE);
}
}
// Open the Database Driver
db = databaseFromURL( databaseURL );
if (!db.isValid())
{
out << " database URL = " << databaseURL << endl;
out << "Protocol=" << protocol << ", Host=" << server << ", Database=" << database << ", port=" << port << endl;
out << "Could not load the specified database driver." << endl;
exit(EXIT_ERROR_DB_DRIVER);
}
// Try to connect to the Database
db.setUserName(username);
db.setPassword(passwd);
if (!db.open())
{
out << "Protocol=" << protocol << ", Host=" << db.hostName() <<
", Database=" << db.databaseName() << ", port=" << db.port() << endl;
out << "Could not log into database. System Error: "
<< db.lastError().text() << endl;
exit(EXIT_ERROR_DB_LOGIN);
}
if ( ! buildTable(db, out, dbServer) )
exit(EXIT_ERROR_DB_TABLE_BUILD);
}
else if (databaseURL == "")
{
out << "You must specify a Database URL by using the -databaseURL= parameter." << endl;
exit(EXIT_ERROR_MISSING_URL);
}
else if (username == "")
{
out << "You must specify a Database Username by using the -username= parameter." << endl;
exit(EXIT_ERROR_MISSING_USERNAME);
}
else if (passwd == "")
{
out << "You must specify a Database Password by using the -passwd= parameter." << endl;
exit(EXIT_ERROR_MISSING_PASSWORD);
}
}
else
{
out << "Usage: bldtbls -databaseURL='$' -username='******' -passwd='$' [-dbengine='$']" << endl;
exit(EXIT_ERROR_BAD_ARGS);
}
return EXIT_SUCCESS;
}
Database::Database() {
QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QSQLITE"));
QSettings qs;
QStringList datapaths;
int i;
datapaths << g.qdBasePath.absolutePath();
#if QT_VERSION >= 0x050000
datapaths << QStandardPaths::writableLocation(QStandardPaths::DataLocation);
#else
datapaths << QDesktopServices::storageLocation(QDesktopServices::DataLocation);
#endif
#if defined(Q_OS_UNIX) && ! defined(Q_OS_MAC)
datapaths << QDir::homePath() + QLatin1String("/.config/Mumble");
#endif
datapaths << QDir::homePath();
datapaths << QDir::currentPath();
datapaths << qApp->applicationDirPath();
datapaths << qs.value(QLatin1String("InstPath")).toString();
bool found = false;
for (i = 0; (i < datapaths.size()) && ! found; i++) {
if (!datapaths[i].isEmpty()) {
QFile f(datapaths[i] + QLatin1String("/mumble.sqlite"));
if (f.exists()) {
db.setDatabaseName(f.fileName());
found = db.open();
}
//TODO: If the above succeeds, but we also have a .mumble.sqlite, we open another DB!?
QFile f2(datapaths[i] + QLatin1String("/.mumble.sqlite"));
if (f2.exists()) {
db.setDatabaseName(f2.fileName());
found = db.open();
}
}
}
if (! found) {
for (i = 0; (i < datapaths.size()) && ! found; i++) {
if (!datapaths[i].isEmpty()) {
QDir::root().mkpath(datapaths[i]);
#ifdef Q_OS_WIN
QFile f(datapaths[i] + QLatin1String("/mumble.sqlite"));
#else
QFile f(datapaths[i] + QLatin1String("/.mumble.sqlite"));
#endif
db.setDatabaseName(f.fileName());
found = db.open();
}
}
}
if (! found) {
QMessageBox::critical(NULL, QLatin1String("Mumble"), tr("Mumble failed to initialize a database in any\nof the possible locations."), QMessageBox::Ok | QMessageBox::Default, QMessageBox::NoButton);
qFatal("Database: Failed initialization");
}
QFileInfo fi(db.databaseName());
if (! fi.isWritable()) {
QMessageBox::critical(NULL, QLatin1String("Mumble"), tr("The database '%1' is read-only. Mumble cannot store server settings (i.e. SSL certificates) until you fix this problem.").arg(Qt::escape(fi.filePath())), QMessageBox::Ok | QMessageBox::Default, QMessageBox::NoButton);
qWarning("Database: Database is read-only");
}
{
QFile f(db.databaseName());
f.setPermissions(f.permissions() & ~(QFile::ReadGroup | QFile::WriteGroup | QFile::ExeGroup | QFile::ReadOther | QFile::WriteOther | QFile::ExeOther));
}
QSqlQuery query;
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `servers` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `hostname` TEXT, `port` INTEGER DEFAULT " MUMTEXT(DEFAULT_MUMBLE_PORT) ", `username` TEXT, `password` TEXT)"));
query.exec(QLatin1String("ALTER TABLE `servers` ADD COLUMN `url` TEXT")); // Upgrade path, failing this query is not noteworthy
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `comments` (`who` TEXT, `comment` BLOB, `seen` DATE)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `comments_comment` ON `comments`(`who`, `comment`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE INDEX IF NOT EXISTS `comments_seen` ON `comments`(`seen`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `blobs` (`hash` TEXT, `data` BLOB, `seen` DATE)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `blobs_hash` ON `blobs`(`hash`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE INDEX IF NOT EXISTS `blobs_seen` ON `blobs`(`seen`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `tokens` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB, `token` TEXT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE INDEX IF NOT EXISTS `tokens_host_port` ON `tokens`(`digest`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `shortcut` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB, `shortcut` BLOB, `target` BLOB, `suppress` INTEGER)"));
execQueryAndLogFailure(query, QLatin1String("CREATE INDEX IF NOT EXISTS `shortcut_host_port` ON `shortcut`(`digest`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `udp` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `digest` BLOB)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `udp_host_port` ON `udp`(`digest`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `cert` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hostname` TEXT, `port` INTEGER, `digest` TEXT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `cert_host_port` ON `cert`(`hostname`,`port`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `friends` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `hash` TEXT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `friends_name` ON `friends`(`name`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `friends_hash` ON `friends`(`hash`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `ignored` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` TEXT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `ignored_hash` ON `ignored`(`hash`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `muted` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` TEXT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `muted_hash` ON `muted`(`hash`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `volume` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` TEXT, `volume` FLOAT)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `volume_hash` ON `volume`(`hash`)"));
//Note: A previous snapshot version created a table called 'hidden'
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `filtered_channels` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `server_cert_digest` TEXT NOT NULL, `channel_id` INTEGER NOT NULL)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `filtered_channels_entry` ON `filtered_channels`(`server_cert_digest`, `channel_id`)"));
execQueryAndLogFailure(query, QLatin1String("CREATE TABLE IF NOT EXISTS `pingcache` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hostname` TEXT, `port` INTEGER, `ping` INTEGER)"));
execQueryAndLogFailure(query, QLatin1String("CREATE UNIQUE INDEX IF NOT EXISTS `pingcache_host_port` ON `pingcache`(`hostname`,`port`)"));
execQueryAndLogFailure(query, QLatin1String("DELETE FROM `comments` WHERE `seen` < datetime('now', '-1 years')"));
execQueryAndLogFailure(query, QLatin1String("DELETE FROM `blobs` WHERE `seen` < datetime('now', '-1 months')"));
execQueryAndLogFailure(query, QLatin1String("VACUUM"));
execQueryAndLogFailure(query, QLatin1String("PRAGMA synchronous = OFF"));
#ifdef Q_OS_WIN
// Windows can not handle TRUNCATE with multiple connections to the DB. Thus less performant DELETE.
execQueryAndLogFailure(query, QLatin1String("PRAGMA journal_mode = DELETE"));
#else
execQueryAndLogFailure(query, QLatin1String("PRAGMA journal_mode = TRUNCATE"));
#endif
execQueryAndLogFailure(query, QLatin1String("SELECT sqlite_version()"));
while (query.next())
qWarning() << "Database SQLite:" << query.value(0).toString();
}
void CDatabaseConnectionExtWidget::processConnecting()
{
ui->lDbConnectionStatus->setText(tr("Подключение..."));
QApplication::processEvents(QEventLoop::ExcludeUserInputEvents);
bool connectingToServer = ui->rbnDbServer->isChecked(); //Флаг, показывающий подключаемся ли мы к серверу или к файлу.
QSqlDatabase db = QSqlDatabase::addDatabase(connectingToServer ? "QMYSQL" : "QSQLITE", mConnectionName);
if(db.isValid() == false)
{
QMessageBox::critical(this, tr("Ошибка"), tr("Не найден драйвер базы данных."));
return;
}
if(connectingToServer)
{
db.setHostName(ui->cbxDbHost->currentText());
db.setDatabaseName(ui->leDbName->text());
db.setUserName(ui->leDbLogin->text());
db.setPassword(ui->leDbPassword->text());
if(db.hostName().isEmpty())
{
ui->lDbConnectionStatus->setText(tr("Укажите хост подключения"));
return;
}
}
else //connecting to file
{
db.setDatabaseName(ui->cbxDbFileName->currentText());
if(db.databaseName().isEmpty())
{
ui->lDbConnectionStatus->setText(tr("Файл не выбран"));
return;
}
}
if(db.open()) //connection is successfully
{
mConnected = true;
ui->lDbConnectionStatus->setText(tr("Подключение произведено"));
QApplication::processEvents(QEventLoop::ExcludeUserInputEvents);
//кеширование:
if(mCache)
{
if(connectingToServer)
{
mCache->setValue(INI_CONNECTION_TYPE, "server");
mCache->setValue(INI_LASTSERVER, db.hostName());
if(ui->chbxDbRemember->isChecked()) //Галочка 'Запомнить'
{
mCache->beginGroup(db.hostName());
mCache->setValue(INI_DBNAME, db.databaseName());
mCache->setValue(INI_LOGIN, db.userName());
mCache->setValue(INI_PASSWORD, Global::crypt(db.password()));
mCache->endGroup();
}
}
else //connecting to file
{
mCache->setValue(INI_CONNECTION_TYPE, "file");
mCache->setValue(INI_LASTFILE, ui->cbxDbFileName->currentText());
QStringList files = mCache->value(INI_FILES).toStringList();
if(files.contains(ui->cbxDbFileName->currentText()) == false)
{
files.append(ui->cbxDbFileName->currentText());
mCache->setValue(INI_FILES, files);
}
}
mCache->sync();
}
if(!connectingToServer)
emit connectedToFile(ui->cbxDbFileName->currentText());
else if(db.databaseName().isEmpty())
emit connectedToHost();
else
emit connectedToDatabase(db.databaseName());
}
else
{
ui->lDbConnectionStatus->setText(tr("Ошибка подключения"));
QApplication::processEvents(QEventLoop::ExcludeUserInputEvents);
qDebug("CDataBaseConnectionWidget::on_pbnDbConnect_clicked: error: %d", db.lastError().number());
QString errorText = tr("Не удалось подключиться к базе данных.\n");
switch(db.lastError().number())
{
case 1045:
errorText += tr("Ошибка аутентификации. Проверьте правильность логина и пароля.\n");
break;
case 1044:
errorText += tr("Неверно указано имя базы данных.\n");
break;
case 2005:
errorText += tr("Не удалось найти указанный хост.\n");
break;
case -1:
errorText += tr("Указанный файл не является базой данных.");
break;
}
if(db.lastError().number() >= 0)
errorText += '\n' + db.lastError().text();
QMessageBox::warning(this, tr("Ошибка подключения"), errorText);
ui->lDbConnectionStatus->clear();
}
}
bool Database::updateDatabase(int fromver)
{
switch (fromver) {
case 0: {
QSqlQuery q0("create table Version (value integer)", db);
//QSqlQuery q1("insert into Version (value) values ("+QString::number(DB_VERSION)+")");
QSqlQuery q3("alter table Artist add column art varchar(250)", db);
QSqlQuery q4("alter table Album add column art varchar(250)", db);
QSqlQuery q5("alter table Genre add column art varchar(250)", db);
QSqlQuery q6("alter table Song drop column Mark", db);
QSqlQuery q2("drop table Mark", db);
qDebug("Update database from version 0");
}
case 1: {
QSqlQuery q0("create table Playlist (ID integer primary key autoincrement, value varchar(200), refs integer, rating integer, art varchar(250), list varchar(250))", db);
qDebug("Update database from version 1");
}
case 2: {
QSqlQuery q0("alter table Album add column artist integer", db);
qDebug("Update database from version 2");
QSqlQuery q1("select distinct Artist, Album from Song", db);
while(q1.next()) {
int ar = q1.value(0).toString().toInt();
QSqlQuery q2("select artist from Album where ID = "+q1.value(1).toString(), db);
if(q2.next()) {
int ar2 = q2.value(0).toString().toInt();
if(!ar2) { // update
QSqlQuery q3("update Album set artist = "+q1.value(0).toString()+" where ID = "+q1.value(1).toString(), db);
} else if(ar2 != ar) { // copy
QSqlQuery q4("select value, art, refs, rating from Album where ID = "+q1.value(1).toString()+" and artist = "+q2.value(0).toString(), db);
if(q4.next()) {
int old_ref = q4.value(2).toString().toInt();
int old_rat = q4.value(3).toString().toInt();
QSqlQuery q5("insert into Album (value,art,artist) values ('"+q4.value(0).toString()+"','"+q4.value(1).toString()+"',"+q1.value(0).toString(), db);
QSqlQuery q6("select ID from Album where value = '"+q4.value(0).toString()+"' and art = '"+q4.value(1).toString()+"' and artist = "+q1.value(0).toString(), db);
if(q6.next()) {
int id = q6.value(0).toString().toInt();
int ref=0, rat=0;
QSqlQuery q7("select ID, Rating from Song where Artist = "+q1.value(0).toString()+" and Album = "+q1.value(1).toString(), db);
while(q7.next()) {
ref ++;
rat += q7.value(1).toString().toInt();
QSqlQuery q8("update Song set Album = "+QString::number(id)+" where ID = "+q7.value(0).toString(), db);
}
QSqlQuery q8("update Album set refs = "+QString::number(ref)+", rating = "+QString::number(rat)+" where ID = "+QString::number(id), db);
QSqlQuery q9("update Album set refs = "+QString::number(old_ref-ref)+", rating = "+QString::number(old_rat-rat)+" where ID = "+q1.value(1).toString(), db);
}
}
}
}
}
}
case 3: {
QSqlQuery q0("alter table Artist add column mbid varchar(50)", db);
QSqlQuery q1("alter table Album add column mbid varchar(50)", db);
qDebug("Update database from version 3");
}
case 4: {
QSqlQuery q0("create table Info(Mbid varchar(50) primary key, text varchar(10000))", db);
qDebug("Update database from version 4");
}
case 5: {
QSqlQuery q0("", db);
q0.prepare("create table SQLPlaylist (ID integer primary key autoincrement, value varchar(200), art varchar(250), data varchar(250))");
q0.exec();
CreateDefaultSqlPlaylists();
qDebug("Update database from version 5");
}
case 6: {
QSqlQuery q0("alter table Song add column Type varchar(30)", db);
qDebug("Update database from version 6");
}
case 7: {
QSqlDatabase ldb;
ldb = QSqlDatabase::addDatabase("QSQLITE", "tempLibraryDB");
ldb.setDatabaseName(QDir::homePath()+"/.cuberok/library.db");
if(!QFile::exists(ldb.databaseName())) {
if(!QDir().mkpath(QDir::homePath()+"/.cuberok") || !ldb.open()) {
qDebug("Can not create library database");
} else {
QSqlQuery q0("create table Version (value integer)", ldb);
QSqlQuery q1("insert into Version (value) values (0)", ldb);
QSqlQuery q2("create table Playlist (ID integer primary key autoincrement, value varchar(200), refs integer, rating integer, art varchar(250), list varchar(250))", ldb);
QSqlQuery q("select value, art from Playlist order by value ASC", db);
q.exec();
int records = 0;
while(q.next()) {
QSqlQuery q3("", ldb);
q3.prepare("insert into Playlist (value, art) values (:val, :art)");
q3.bindValue(":val", q.value(0).toString());
q3.bindValue(":art", q.value(1).toString());
q3.exec();
records ++;
}
ldb.close();
qDebug("%d records has been moved", records);
}
}
QSqlQuery q2("drop table Playlist", db);
QSqlDatabase::removeDatabase("tempLibraryDB");
qDebug("Update database from version 7");
}
case 8: {
QSqlQuery q0("alter table Song add column Date varchar(50)", db);
CreateDefaultSqlPlaylists2();
qDebug("Update database from version 8");
}
}
proxy->message("Database update from version "+QString::number(fromver));
QSqlQuery q1("delete from Version", db);
QSqlQuery q2("insert into Version (value) values ("+QString::number(DB_VERSION)+")", db);
return true;
}