单例、QML、model、数据库

#include "dbmanagement.h" #include <QDebug> #include <QFile> #include <QSqlQuery> #include <QSqlError> DbManagement::DbManagement(QObject *parent) : QObject(parent) { #ifndef QT_DEBUG m_strDbPath = "/home/root/app/ui/test.db"; #else m_strDbPath = "test.db"; #endif initCreateSql(); init(); } bool DbManagement::init() { if (m_db.isOpen()) { return true; } if (!QFile::exists(m_strDbPath)) { qDebug() << "DB is not exists, will be created."; } m_db = QSqlDatabase::addDatabase("QSQLITE"); m_db.setDatabaseName(m_strDbPath); if (!m_db.open()) { qDebug() << "Failed to open db file: " << m_db.databaseName(); qDebug() << "db ??? "; return false; } checkTable(); return true; } void DbManagement::initCreateSql() { // 用户信息 m_mapTableSql.insert("user_info", "CREATE TABLE [user_info]([user_id] INT PRIMARY KEY NOT NULL UNIQUE, [user_name] TEXT NOT NULL, [password] TEXT NOT NULL, " "[create_time] DATETIME, [modify_time] DATETIME, [isDeleted] BOOL DEFAULT False, [isKeepPassword] BOOL DEFAULT False);"); } void DbManagement::checkTable() { QStringList tables = m_db.tables(); for (QMap<QString, QString>::const_iterator it = m_mapTableSql.begin(); it != m_mapTableSql.end(); ++it) { if (!tables.contains(it.key())) { createTable(it.key()); } } if (!tables.contains("user_info")) { initTableContents(); } // if (!tables.contains("chanal_info")) // { // initTableContentsChanel(); // } // if (!tables.contains("config_info")) // { // initTableContentsConfig(); // } } /*! * \brief DbManagement::initTableContents 新增Admin初始用户 */ void DbManagement::initTableContents() { QSqlQuery query; query.prepare("INSERT INTO user_info (user_id, user_name, password, create_time, modify_time, isDeleted, isKeepPassword) " "VALUES(:id, :name, :password, :create, :modify, :deleted, :keepPw)"); query.bindValue(":id", 1); query.bindValue(":name", "Admin"); query.bindValue(":password", "82218051"); query.bindValue(":create", QDateTime::currentDateTime()); query.bindValue(":modify", QDateTime::currentDateTime()); query.bindValue(":deleted", true); query.bindValue(":keepPw", false); if (!query.exec()) { qDebug() << tr("Failed to initial user info"); qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); } } void DbManagement::createTable(const QString &strTableName) { QSqlQuery query; if (!query.exec(m_mapTableSql.value(strTableName))) { qDebug() << "Failed to create table: " << strTableName; qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); } } bool DbManagement::saveUser(CommonDefines::UserInfo &userInfo) { QSqlQuery query; query.prepare("REPLACE INTO user_info (user_id, user_name, password, create_time, modify_time, isDeleted, isKeepPassword) " "VALUES(:id, :name, :password, :create, :modify, :deleted, :keepPw)"); query.bindValue(":id", userInfo.iUserId); query.bindValue(":name", userInfo.strUserName); query.bindValue(":password", userInfo.strPassword); query.bindValue(":create", userInfo.tmCreateTime); query.bindValue(":modify", userInfo.tmModifyTime); query.bindValue(":deleted", userInfo.bIsDeleted); query.bindValue(":keepPw", userInfo.bIsKeepPassword); if (!query.exec()) { qDebug() << tr("Failed to save user info [%1][%2]").arg(userInfo.iUserId).arg(userInfo.strUserName); qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); return false; } return true; } bool DbManagement::queryUserInfoList(QList<CommonDefines::UserInfo> &userInfoList) { QSqlQuery query; query.prepare("SELECT * FROM user_info WHERE isDeleted = false ORDER BY modify_time DESC"); if (!query.exec()) { qDebug() << "Failed to query user info list"; qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); return false; } while (query.next()) { CommonDefines::UserInfo userInfo; userInfo.iUserId = query.value("user_id").toInt(); userInfo.strUserName = query.value("user_name").toString(); userInfo.strPassword = query.value("password").toString(); userInfo.tmCreateTime = query.value("create_time").toDateTime(); userInfo.tmModifyTime = query.value("modify_time").toDateTime(); userInfo.bIsDeleted = query.value("isDeleted").toBool(); userInfo.bIsKeepPassword = query.value("isKeepPassword").toBool(); userInfoList.append(userInfo); qDebug() << "++++++++++++++++++++++++++" << userInfo.strUserName; } return true; } bool DbManagement::getUserCount(int &iCount) { QSqlQuery query; query.prepare("SELECT count(*) FROM user_info"); if (!query.exec()) { qDebug() << "Failed to query user count"; qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); return false; } while (query.next()) { iCount = query.value("count(*)").toInt(); } return true; } bool DbManagement::queryAdminPw(QString &strPassword) { QSqlQuery query; query.prepare("SELECT * FROM user_info WHERE user_name = :name"); query.bindValue(":name", "Admin"); if (!query.exec()) { qDebug() << "Failed to query Admin"; qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); return false; } if (query.next()) { strPassword = query.value("password").toString(); } return true; } bool DbManagement::updateAdminPw(QString strPassword) { QSqlQuery query; query.prepare("UPDATE user_info SET password = :pw WHERE user_name = :name"); query.bindValue(":pw", strPassword); query.bindValue(":name", "Admin"); if (!query.exec()) { qDebug() << "Failed to modify Admin password"; qDebug() << query.lastQuery(); qDebug() << query.lastError().text(); return false; } return true; }