DatabaseTree.cpp

// Author: Kang Lin <kl222@126.com>
 
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QJsonArray>
#include <QJsonObject>
#include <QLoggingCategory>
 
#include "DatabaseTree.h"
 
static Q_LOGGING_CATEGORY(log, "DB.Tree")
TreeItem::TreeItem(TYPE type) {
    m_Data.m_Type = type;
    m_Data.m_id = 0;
    m_Data.m_nKey = 0;
    m_Data.m_ParentId = 0;
    m_Data.m_SortOrder = 0;
}
 
TreeItem::TreeItem(const TreeItem &item)
{
    m_Data = item.m_Data;
}
 
TreeItem& TreeItem::operator =(const TreeItem& item)
{
    m_Data = item.m_Data;
    return *this;
}
 
bool TreeItem::IsNode() const
{
    return Node == GetType();
}
 
bool TreeItem::IsLeaf() const
{
    return Leaf == GetType();
}
 
TreeItem::TYPE TreeItem::GetType() const
{
    return m_Data.m_Type;
}
 
void TreeItem::SetType(TYPE type)
{
    m_Data.m_Type = type;
}
 
int TreeItem::GetId() const
{
    return m_Data.m_id;
}
 
void TreeItem::SetId(int newId)
{
    m_Data.m_id = newId;
}
 
QString TreeItem::GetName() const
{
    return m_Data.m_szName;
}
 
void TreeItem::SetName(const QString &newName)
{
    m_Data.m_szName = newName;
}
 
int TreeItem::GetParentId() const
{
    return m_Data.m_ParentId;
}
 
void TreeItem::SetParentId(int newParentId)
{
    m_Data.m_ParentId = newParentId;
}
 
int TreeItem::GetSortOrder() const
{
    return m_Data.m_SortOrder;
}
 
void TreeItem::SetSortOrder(int newSortOrder)
{
    m_Data.m_SortOrder = newSortOrder;
}
 
int TreeItem::GetKey() const
{
    return m_Data.m_nKey;
}
 
void TreeItem::SetKey(int newNKey)
{
    m_Data.m_nKey = newNKey;
}
 
QDateTime TreeItem::GetCreateTime() const
{
    return m_Data.m_CreateTime;
}
 
void TreeItem::SetCreateTime(const QDateTime &newCreateTime)
{
    m_Data.m_CreateTime = newCreateTime;
}
 
QDateTime TreeItem::GetModifyTime() const
{
    return m_Data.m_ModifyTime;
}
 
void TreeItem::SetModifyTime(const QDateTime &newModifyTime)
{
    m_Data.m_ModifyTime = newModifyTime;
}
 
QDateTime TreeItem::GetLastVisitTime() const
{
    return m_Data.m_LastVisitTime;
}
 
void TreeItem::SetLastVisitTime(const QDateTime &newLastVisitTime)
{
    m_Data.m_LastVisitTime = newLastVisitTime;
}
 
CDatabaseNode::CDatabaseNode(const QString &szPrefix, QObject *parent)
    : CDatabase(parent)
{
    m_szTableName = "folders";
    if(!szPrefix.isEmpty())
        m_szTableName = szPrefix + "_" + m_szTableName;
    m_szConnectName = "connect_" + m_szTableName;
}
 
bool CDatabaseNode::OnInitializeSqliteDatabase()
{
    QSqlQuery query(GetDatabase());
 
    // 启用外键约束
    query.exec("PRAGMA foreign_keys = ON");
    QString szSql =
        "CREATE TABLE IF NOT EXISTS "
        + m_szTableName +
        " ("
        "    id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "    name TEXT NOT NULL,"
        "    parent_id INTEGER DEFAULT 0,"
        "    sort_order INTEGER DEFAULT 0,"
        "    created_time DATETIME DEFAULT CURRENT_TIMESTAMP"
        ")";
    // 创建文件夹表
    bool bRet = query.exec(szSql);
    if (!bRet) {
        SetError("Failed to create folders sqlite table: "
                 + query.lastError().text()
                 + "; Sql: " + szSql);
        qCritical(log) << GetError();
        return false;
    }
 
    // 创建索引
    szSql = "CREATE INDEX IF NOT EXISTS idx_" + m_szTableName + "_parent ON " + m_szTableName + "(parent_id)";
    bRet = query.exec(szSql);
    if(!bRet)
        qDebug(log) << "Failed to create index idx_" + m_szTableName + "_parent:" << query.lastError().text()
                    << "; Sql: " + szSql;
 
    return true;
}
 
bool CDatabaseNode::OnInitializeMySqlDatabase()
{
    QSqlQuery query(GetDatabase());
 
    // 启用外键约束
    query.exec("PRAGMA foreign_keys = ON");
 
    // 创建文件夹表
    QString szSql =
        "CREATE TABLE IF NOT EXISTS "
        + m_szTableName +
        " ("
        "    id INTEGER PRIMARY KEY AUTO_INCREMENT,"
        "    name TEXT NOT NULL,"
        "    parent_id INTEGER DEFAULT 0,"
        "    sort_order INTEGER DEFAULT 0,"
        "    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "    INDEX idx_parent (parent_id),"
        "    INDEX idx_parent_sort (parent_id, sort_order)"
        ")";
    bool bRet = query.exec(szSql);
    if (!bRet) {
        qCritical(log) << "Failed to create folders mysql table:"
                       << m_szTableName << query.lastError().text()
                       << "Sql:" << szSql;
        return false;
    }
 
    return true;
}
 
int CDatabaseNode::AddNode(const QString &name, int parentId)
{
    bool bRet = false;
    QSqlQuery query(GetDatabase());
 
    // Check if it already exists
    query.prepare("SELECT id FROM `" + m_szTableName + "` "
                  " WHERE `parent_id` = :parent_id "
                  " AND `name`=:name"
                  );
    query.bindValue(":parent_id", parentId);
    query.bindValue(":name", name);
    bRet = query.exec();
    if(!bRet) {
        SetError("Failed to add folders: " + query.lastError().text()
                       + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return 0;
    }
    if(query.next()) {
        return query.value(0).toInt();
    }
 
    // 获取最大排序值
    query.prepare("SELECT MAX(sort_order) FROM `" + m_szTableName + "` "
                  " WHERE `parent_id` = :parent_id");
    query.bindValue(":parent_id", parentId);
    bRet = query.exec();
    if(!bRet) {
        SetError("Failed to get max sort_order: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return 0;
    }
    int maxOrder = 0;
    if (query.next()) {
        maxOrder = query.value(0).toInt() + 1;
    }
 
    query.prepare(
        "INSERT INTO `" + m_szTableName + "` "
        " (`name`, `parent_id`, `sort_order`) "
        " VALUES (:name, :parent_id, :sort_order) "
        );
    query.bindValue(":name", name);
    query.bindValue(":parent_id", parentId);
    query.bindValue(":sort_order", maxOrder);
 
    int id = 0;
    bRet = query.exec();
    if (bRet) {
        id = query.lastInsertId().toInt();
        if(id > 0) {
            emit sigAddNode(id, parentId);
            emit sigChanged();
        }
    } else {
        SetError("Failed to add folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
 
    return id;
}
 
bool CDatabaseNode::RenameNode(int id, const QString &newName)
{
    QSqlQuery query(GetDatabase());
    query.prepare("UPDATE `" + m_szTableName + "` "
                  " SET `name` = :name WHERE `id` = :id");
    query.bindValue(":id", id);
    query.bindValue(":name", newName);
    bool bRet = query.exec();
    if (bRet)
        emit sigChanged();
    else {
        SetError("Failed to rename folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
    return bRet;
}
 
bool CDatabaseNode::DeleteNode(
    int id, std::function<bool (int)> cbDeleteLeaf, bool checkReturn)
{
    bool bRet = false;
    // 删除子目录
    auto folders = GetSubNodes(id);
    foreach(auto f, folders) {
        bRet = DeleteNode(f.GetId(), cbDeleteLeaf, checkReturn);
        if(checkReturn && !bRet)
            return false;
    }
 
    // 删除其下面的所有条目
    if(cbDeleteLeaf) {
        bRet = cbDeleteLeaf(id);
        if(checkReturn && !bRet) {
            SetError("Failed to call cbDeleteLeaf");
            qCritical(log) << GetError();
            return false;
        }
    }
 
    bRet = OnDeleteLeafs(id);
    if(checkReturn && !bRet) {
        SetError("Failed to call OnDeleteLeafs");
        qCritical(log) << GetError();
        return false;
    }
 
    // 删除文件夹
    QSqlQuery query(GetDatabase());
    query.prepare("DELETE FROM `" + m_szTableName + "` WHERE `id` = :id");
    query.bindValue(":id", id);
    bRet = query.exec();
    if (bRet)
        emit sigChanged();
    else {
        SetError("Failed to delete folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
 
    return bRet;
}
 
bool CDatabaseNode::OnDeleteLeafs(int id)
{
    Q_UNUSED(id);
    return true;
}
 
bool CDatabaseNode::MoveNode(int id, int newParentId)
{
    if(id == newParentId) {
        SetError("Failed to move node. The same node: " + id);
        qWarning(log) << GetError();
        return false;
    }
    // Check the newParentId is not the child of id
    bool bRet = InSubNode(id, newParentId);
    if(bRet) {
        SetError("The id is the parent of newParentId. "
                 + QString::number(id) + " is the parent of "
                 + QString::number(newParentId));
        qWarning(log) << GetError();
        return false;
    }
    QSqlQuery query(GetDatabase());
    query.prepare("UPDATE `" + m_szTableName + "` "
                  " SET `parent_id` = :parent_id WHERE `id` = :id");
    query.bindValue(":id", id);
    query.bindValue(":parent_id", newParentId);
    bRet = query.exec();
    if (bRet)
        emit sigChanged();
    else {
        SetError("Failed to move folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
    return bRet;
}
 
TreeItem CDatabaseNode::GetNode(int id)
{
    TreeItem folder(TreeItem::Node);
    if(0 >= id) return folder;
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `id`, `name`, `parent_id`, `sort_order`, `created_time` "
        "FROM `" + m_szTableName + "` "
        "WHERE `id`=:id"
        );
    query.bindValue(":id", id);
    if (query.exec()) {
        if(query.next()) {
            folder.SetId(query.value(0).toInt());
            folder.SetName(query.value(1).toString());
            folder.SetParentId(query.value(2).toInt());
            folder.SetSortOrder(query.value(3).toInt());
            folder.SetCreateTime(query.value(4).toDateTime());
        }
    } else {
        SetError("Failed to get folder: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery() +  QString::number(id));
        qCritical(log) << GetError();
    }
    return folder;
}
 
QList<TreeItem> CDatabaseNode::GetAllNodes()
{
    QList<TreeItem> folders;
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `id`, `name`, `parent_id`, `sort_order`, `created_time` "
        " FROM `" + m_szTableName + "` "
        " ORDER BY `parent_id`, `sort_order`, `name`"
        );
    if (query.exec()) {
        while (query.next()) {
            TreeItem folder(TreeItem::Node);
            folder.SetId(query.value(0).toInt());
            folder.SetName(query.value(1).toString());
            folder.SetParentId(query.value(2).toInt());
            folder.SetSortOrder(query.value(3).toInt());
            folder.SetCreateTime(query.value(4).toDateTime());
            folders.append(folder);
        }
    } else {
        SetError("Failed to get all folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
 
    return folders;
}
 
QList<TreeItem> CDatabaseNode::GetSubNodes(int parentId)
{
    QList<TreeItem> folders;
 
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `id`, `name`, `parent_id`, `sort_order`, `created_time` "
        "FROM `" + m_szTableName + "` "
        "WHERE `parent_id` = :parent_id "
        "ORDER BY `sort_order`, `name`"
        );
    query.bindValue(":parent_id", parentId);
    if (query.exec()) {
        while (query.next()) {
            TreeItem folder(TreeItem::Node);
            folder.SetId(query.value(0).toInt());
            folder.SetName(query.value(1).toString());
            folder.SetParentId(query.value(2).toInt());
            folder.SetSortOrder(query.value(3).toInt());
            folder.SetCreateTime(query.value(4).toDateTime());
            folders.append(folder);
        }
    } else {
        SetError("Failed to get sub folders: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery()
                 + "; parentId: " + QString::number(parentId));
        qCritical(log) << GetError();
    }
 
    return folders;
}
 
bool CDatabaseNode::InSubNode(int parentId, int id)
{
    bool bRet = false;
    auto items = GetSubNodes(parentId);
    foreach(auto it, items) {
        if(it.GetId() == id)
            return true;
        bRet = InSubNode(it.GetId(), id);
        if(bRet) return true;
    }
    return bRet;
}
 
int CDatabaseNode::GetCount(int parentId)
{
    QSqlQuery query(GetDatabase());
    if(0 == parentId) {
        query.prepare("SELECT COUNT(*) FROM `" + m_szTableName + "`");
    } else {
        query.prepare("SELECT COUNT(*) FROM `" + m_szTableName + "` WHERE `parent_id`=:id");
        query.bindValue(":id", parentId);
    }
    if (query.exec()) {
        if(query.next())
            return query.value(0).toInt();
    } else {
        SetError("Failed to get count: " + query.lastError().text()
                 + "; parentId: " + QString::number(parentId));
        qCritical(log) << GetError();
    }
    return 0;
}
 
bool CDatabaseNode::ExportToJson(QJsonObject& obj)
{
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `id`, `name`, `parent_id`, `sort_order`, `created_time` "
        "FROM `" + m_szTableName + "` "
        );
    bool success = query.exec();
    if (!success) {
        SetError("Failed to export folder to json: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return false;
    }
    QJsonArray folder;
    while(query.next()) {
        QJsonObject f;
        f.insert("id", query.value(0).toInt());
        f.insert("name", query.value(1).toString());
        f.insert("parent_id", query.value(2).toInt());
        f.insert("sort_order", query.value(3).toInt());
        f.insert("created_time", query.value(4).toDateTime().toString());
        folder.append(f);
    }
    if(!folder.isEmpty())
        obj.insert("folder", folder);
    return true;
}
 
bool CDatabaseNode::ImportFromJson(const QJsonObject& obj)
{
    return true;
}
 
CDatabaseTree::CDatabaseTree(const QString &szPrefix, QObject *parent)
    : CDatabase(parent)
    , m_FolderDB(szPrefix)
{
    m_szTableName = "tree";
    if(!szPrefix.isEmpty())
        m_szTableName = szPrefix + "_" + m_szTableName;
    m_szConnectName = "connect_" + m_szTableName;
}
 
bool CDatabaseTree::OnInitializeDatabase()
{
    bool bRet = false;
    bRet = CDatabase::OnInitializeDatabase();
 
    if(!bRet) return false;
    bRet = m_FolderDB.SetDatabase(GetDatabase(), m_pPara);
    if(!bRet) return false;
    bRet = connect(&m_FolderDB, &CDatabaseNode::sigAddNode,
                   this, &CDatabaseTree::sigAddNode);
    Q_ASSERT(bRet);
 
    return bRet;
}
 
bool CDatabaseTree::OnInitializeSqliteDatabase()
{
    QSqlQuery query(GetDatabase());
    
    // 启用外键约束
    query.exec("PRAGMA foreign_keys = ON");
    
    QString szSql =
        "CREATE TABLE IF NOT EXISTS `" + m_szTableName + "` (" +
        "    `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        "    `parent_id` INTEGER DEFAULT 0,"
        "    `name` TEXT,"
        "    `key` INTEGER DEFAULT 0,"
        "    `created_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "    `modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "    `last_visit_time` DATETIME"
        ")";
    bool bRet = query.exec(szSql);
    if (!bRet) {
        SetError("Failed to create tree sqlite table: " + query.lastError().text()
                 + "; Sql: " + szSql);
        qCritical(log) << GetError();
        return false;
    }
 
    // 创建索引
    query.exec("CREATE INDEX IF NOT EXISTS `idx_" + m_szTableName + "_key` ON " + m_szTableName + "(`key`)");
    query.exec("CREATE INDEX IF NOT EXISTS `idx_" + m_szTableName + "_parent_id` ON " + m_szTableName + "(`parent_id`)");
 
    return true;
}
 
bool CDatabaseTree::OnInitializeMySqlDatabase()
{
    QSqlQuery query(GetDatabase());
    
    // 启用外键约束
    query.exec("PRAGMA foreign_keys = ON");
    
    QString szSql =
        "CREATE TABLE IF NOT EXISTS `" + m_szTableName + "` (" +
        "    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,"
        "    `parent_id` INTEGER DEFAULT 0,"
        "    `name` TEXT,"
        "    `key` INTEGER DEFAULT 0,"
        "    `created_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "    `modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "    `last_visit_time` DATETIME,"
        "    INDEX `idx_key` (`key`),"
        "    INDEX `idx_parent_id` (`parent_id`)"
        ")";
    bool bRet = query.exec(szSql);
    if (!bRet) {
        SetError("Failed to create tree mysql table: " + query.lastError().text()
                 + "; Sql: " + szSql);
        qCritical(log) << GetError();
        return false;
    }
 
    return true;
}
 
int CDatabaseTree::Add(const TreeItem &item)
{
    bool ok = false;
    // Check if it is a leaf
    if(item.IsNode()) {
        qCritical(log) << "The item is not leaf";
        return 0;
    }
 
    QSqlQuery query(GetDatabase());
    
    // Check if it already exists
    query.prepare(
        "SELECT `id` FROM `" + m_szTableName + "` "
        " WHERE `key`=:key AND `parent_id`=:parent_id"
        );
    query.bindValue(":key", item.GetKey());
    query.bindValue(":parent_id", item.GetParentId());
    ok = query.exec();
    if(!ok) {
        SetError("Failed to add tree: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return 0;
    }
    if(query.next()) {
        return query.value(0).toInt();
    }
 
    // Insert
    query.prepare(
        "INSERT INTO `" + m_szTableName + "` (`name`, `key`, "
        "`created_time`, `modified_time`, `last_visit_time`, `parent_id`) "
        "VALUES (:name, :key, "
        ":created_time, :modified_time, :last_visit_time, :parent_id)"
        );
    query.bindValue(":name", item.GetName());
    query.bindValue(":key", item.GetKey());
    QDateTime time = QDateTime::currentDateTime();
    query.bindValue(":created_time", time);
    query.bindValue(":modified_time", time);
    query.bindValue(":last_visit_time", time);
    query.bindValue(":parent_id", item.GetParentId());
 
    bool bRet = query.exec();
    if (!bRet) {
        SetError("Failed to add tree item: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return 0;
    }
 
    int id = query.lastInsertId().toInt();
    if(0 < id)
        emit sigAdd(id, item.GetParentId());
    return id;
}
 
bool CDatabaseTree::Update(const TreeItem &item)
{
    // Check if it is a leaf
    if(item.IsNode()) {
        qCritical(log) << "The item is not leaf";
        return false;
    }
 
    QSqlQuery query(GetDatabase());
    query.prepare(
        "UPDATE `" + m_szTableName + "` SET "
        "`name` = :name, "
        "`key` = :key, "
        "`created_time` = :created_time, "
        "`modified_time` = :modified_time, "
        "`last_visit_time` = :last_visit_time, "
        "`parent_id` = :parent_id "
        "WHERE `id` = :id"
        );
    query.bindValue(":name", item.GetName());
    query.bindValue(":key", item.GetKey());
    query.bindValue(":created_time", item.GetCreateTime());
    query.bindValue(":modified_time", QDateTime::currentDateTime());
    query.bindValue(":last_visit_time", item.GetLastVisitTime());
    query.bindValue(":parent_id", item.GetParentId());
    query.bindValue(":id", item.GetId());
    qDebug(log) << "Sql:" << query.executedQuery();
    qDebug(log) << "Bound value:" << query.boundValues();
    bool bRet = query.exec();
    if (!bRet) {
        SetError("Failed to update tree item: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
    return bRet;
}
 
bool CDatabaseTree::Delete(int id, bool delKey)
{
    // 如果是最后的一个，则从 key 相关表中删除 key
    if(delKey) {
        auto leaf = GetLeaf(id);
        if(leaf.GetKey() > 0) {
            auto leaves = GetLeavesByKey(leaf.GetKey());
            if(leaves.count() == 1) {
                bool ok = OnDeleteKey(leaf.GetKey());
                if(!ok)
                    return ok;
            }
        }
    }
    QSqlQuery query(GetDatabase());
    query.prepare("DELETE FROM `" + m_szTableName + "` WHERE `id` = :id");
    query.bindValue(":id", id);
    bool bRet = query.exec();
    if (!bRet) {
        SetError("Failed to delete item: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery()
                 + "; table: " + m_szTableName
                 + "; id: " + QString::number(id));
        qCritical(log) << GetError();
    }
    return bRet;
}
 
bool CDatabaseTree::Delete(QList<int> items, bool delKey)
{
    if(items.isEmpty()) return false;
 
    if(delKey) {
        foreach(auto id, items) {
            // 如果是最后的一个，则从 key 相关表中删除 key
            auto leaf = GetLeaf(id);
            if(leaf.GetKey() > 0) {
                auto leaves = GetLeavesByKey(leaf.GetKey());
                if(leaves.count() == 1) {
                    bool ok = OnDeleteKey(leaf.GetKey());
                    if(!ok)
                        return ok;
                }
            }
        }
    }
 
    QSqlQuery query(GetDatabase());
    QString szSql = "DELETE FROM `" + m_szTableName + "` WHERE ";
    int i = 0;
    foreach(auto id, items) {
        if(0 == i) {
            i++;
            szSql += " `id` = " + QString::number(id);
        } else {
            szSql += " OR `id` = " + QString::number(id);
        }
    }
    bool success = query.exec(szSql);
    if (!success) {
        SetError("Failed to delete item: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
 
    return success;
}
 
bool CDatabaseTree::DeleteChild(int parentId, bool delKey)
{
    if(delKey) {
        auto leaves = GetLeaves(parentId);
        foreach(auto leaf, leaves) {
            if(!Delete(leaf.GetId(), delKey))
                return false;
        }
    }
 
    QSqlQuery query(GetDatabase());
    query.prepare("DELETE FROM `" + m_szTableName + "` WHERE `parent_id` = :parent_id");
    query.bindValue(":parent_id", parentId);
    bool success = query.exec();
    if (!success) {
        SetError("Failed to delete child item:" + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
 
    return success;
}
 
bool CDatabaseTree::Move(int id, int newParent)
{
    QSqlQuery query(GetDatabase());
    query.prepare(
        "UPDATE `" + m_szTableName + "` SET "
        "`parent_id` = :parent_id WHERE `id` = :id");
    query.bindValue(":parent_id", newParent);
    query.bindValue(":id", id);
    bool bRet = query.exec();
    if (!bRet) {
        SetError("Failed to add tree item: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
    return bRet;
}
 
TreeItem CDatabaseTree::GetLeaf(int id)
{
    TreeItem item(TreeItem::Leaf);
 
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `name`, `key`, `created_time`, `modified_time`, `last_visit_time`, `parent_id` "
        " FROM `" + m_szTableName + "` "
        " WHERE `id` = :id");
    query.bindValue(":id", id);
    bool bRet = query.exec();
    if (!bRet) {
        QString szErr
            = "Failed to get leaf: " + query.lastError().text()
              + "; Sql: " + query.executedQuery()
              + "; id: " + QString::number(id);
        SetError(szErr);
        qCritical(log) << GetError();
        return item;
    }
 
    if(query.next()) {
        item.SetId(id);
        item.SetName(query.value(0).toString());
        item.SetKey(query.value(1).toInt());
        item.SetCreateTime(query.value(2).toDateTime());
        item.SetModifyTime(query.value(3).toDateTime());
        item.SetLastVisitTime(query.value(4).toDateTime());
        item.SetParentId(query.value(5).toInt());
    }
 
    return item;
}
 
QList<TreeItem> CDatabaseTree::GetLeaves(int nodeId)
{
    QList<TreeItem> items;
    QSqlQuery query(GetDatabase());
    QString szSql;
    szSql = "SELECT `id`, `name`, `key`, "
            "`created_time`, `modified_time`, `last_visit_time`, `parent_id` "
            "FROM `" + m_szTableName + "`";
    if(0 <= nodeId)
        szSql += " WHERE `parent_id` = :parent_id";
    query.prepare(szSql);
    query.bindValue(":parent_id", nodeId);
    bool bRet = query.exec();
    if (!bRet) {
        SetError("Failed to get leaves: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery()
                 + "; parentId: " + QString::number(nodeId));
        qCritical(log) << GetError();
        return items;
    }
    while(query.next()) {
        TreeItem item(TreeItem::Leaf);
        item.SetId(query.value(0).toInt());
        item.SetName(query.value(1).toString());
        item.SetKey(query.value(2).toInt());
        item.SetCreateTime(query.value(3).toDateTime());
        item.SetModifyTime(query.value(4).toDateTime());
        item.SetLastVisitTime(query.value(5).toDateTime());
        item.SetParentId(query.value(6).toInt());
 
        items.append(item);
    }
    return items;
}
 
QList<TreeItem> CDatabaseTree::GetLeavesByKey(int key)
{
    QList<TreeItem> items;
    QSqlQuery query(GetDatabase());
    QString szSql;
    szSql = "SELECT `id`, `name`, "
            " `created_time`, `modified_time`, `last_visit_time`, `parent_id` "
            " FROM `" + m_szTableName + "` "
            " WHERE `key` = :key";
    query.prepare(szSql);
    query.bindValue(":key", key);
    bool success = query.exec();
    if (!success) {
        SetError("Failed to get leaves by key: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery()
                 + "; key: " + QString::number(key));
        return items;
    }
    while(query.next()) {
        TreeItem item(TreeItem::Leaf);
        item.SetId(query.value(0).toInt());
        item.SetName(query.value(1).toString());
        item.SetKey(key);
        item.SetCreateTime(query.value(2).toDateTime());
        item.SetModifyTime(query.value(3).toDateTime());
        item.SetLastVisitTime(query.value(4).toDateTime());
        item.SetParentId(query.value(5).toInt());
 
        items.append(item);
    }
    return items;
}
 
QList<TreeItem> CDatabaseTree::GetLeavesByKey(QList<int> key)
{
    QList<TreeItem> items;
    if(key.isEmpty()) return items;
 
    QSqlQuery query(GetDatabase());
    QString szSql;
    szSql = "SELECT `id`, `name`, `key`, "
            " `created_time`, `modified_time`, `last_visit_time`, `parent_id` "
            " FROM `" + m_szTableName + "` "
            " WHERE ";
    int i = 0;
    foreach(auto KeyId, key) {
        if(0 == i++) {
            szSql += " `key` = " + QString::number(KeyId);
            continue;
        }
            szSql += " OR `key` = " + QString::number(KeyId);
    }
    bool success = query.exec(szSql);
    if (!success) {
        SetError("Failed to get leaves by key: " + query.lastError().text()
                 + "; Sql: " + szSql);
        qCritical(log) << GetError();
        return items;
    }
    while(query.next()) {
        TreeItem item(TreeItem::Leaf);
        item.SetId(query.value(0).toInt());
        item.SetName(query.value(1).toString());
        item.SetKey(query.value(2).toInt());
        item.SetCreateTime(query.value(3).toDateTime());
        item.SetModifyTime(query.value(4).toDateTime());
        item.SetLastVisitTime(query.value(5).toDateTime());
        item.SetParentId(query.value(6).toInt());
 
        items.append(item);
    }
    return items;
}
 
int CDatabaseTree::GetLeafCount(int parentId)
{
    QSqlQuery query(GetDatabase());
    if(0 == parentId) {
        query.prepare("SELECT COUNT(*) FROM `" + m_szTableName + "`");
    } else {
        query.prepare("SELECT COUNT(*) FROM `" + m_szTableName + "` WHERE `parent_id`=:id");
        query.bindValue(":id", parentId);
    }
    if (query.exec()) {
        if(query.next())
            return query.value(0).toInt();
    } else {
        SetError("Failed to get leaf count: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
    }
    return 0;
}
 
int CDatabaseTree::AddNode(const QString &name, int parentId)
{
    return m_FolderDB.AddNode(name, parentId);
}
 
bool CDatabaseTree::RenameNode(int id, const QString &newName)
{
    return m_FolderDB.RenameNode(id, newName);
}
 
bool CDatabaseTree::DeleteNode(int id, bool delKey)
{
    return m_FolderDB.DeleteNode(
        id,
        [&, delKey](int parentId)->bool {
            return DeleteChild(parentId, delKey);
        },
        true);
}
 
bool CDatabaseTree::MoveNode(int id, int newParentId)
{
    return m_FolderDB.MoveNode(id, newParentId);
}
 
TreeItem CDatabaseTree::GetNode(int id)
{
    return m_FolderDB.GetNode(id);
}
 
QList<TreeItem> CDatabaseTree::GetAllNodes()
{
    return m_FolderDB.GetAllNodes();
}
 
QList<TreeItem> CDatabaseTree::GetSubNodes(int parentId)
{
    return m_FolderDB.GetSubNodes(parentId);
}
 
int CDatabaseTree::GetNodeCount(int nParentId)
{
    return m_FolderDB.GetCount(nParentId);
}
 
int CDatabaseTree::GetCount(int parentId)
{
    return GetNodeCount(parentId) + GetLeafCount(parentId);
}
 
bool CDatabaseTree::OnDeleteKey(int key)
{
    return 0;
}
 
bool CDatabaseTree::ExportToJson(QJsonObject& obj)
{
    bool bRet = true;
 
    QSqlQuery query(GetDatabase());
    query.prepare(
        "SELECT `id`, `parent_id`, `name`, `key`, "
        " `created_time`, `modified_time`, `last_visit_time` "
        " FROM `" + m_szTableName + "`");
    bRet = query.exec();
    if (!bRet) {
        SetError("Failed to export tree to json: " + query.lastError().text()
                 + "; Sql: " + query.executedQuery());
        qCritical(log) << GetError();
        return false;
    }
 
    QJsonArray tree;
    while(query.next()) {
        QJsonObject t;
        t.insert("id", query.value(0).toInt());
        t.insert("parent_id", query.value(1).toInt());
        t.insert("name", query.value(2).toString());
        t.insert("key", query.value(3).toInt());
        t.insert("created_time", query.value(4).toDateTime().toString());
        t.insert("modified_time", query.value(5).toDateTime().toString());
        t.insert("last_visit_time", query.value(6).toDateTime().toString());
        tree.append(t);
    }
    if(!tree.isEmpty())
        obj.insert("tree", tree);
 
    bRet = m_FolderDB.ExportToJson(obj);
    return bRet;
}
 
bool CDatabaseTree::ImportFromJson(const QJsonObject& obj)
{
    bool bRet = true;
    bRet = m_FolderDB.ImportFromJson(obj);
    return bRet;
}