Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

使用sqlite数据库存储本地文件系统例如C盘的树状结构元数据,C++实现,支持增删改查,添加文件时分割目录,记录每层文件夹层级,自身ID,父文件夹ID等,从数据库删除文件夹时同…

  1. User

    使用sqlite数据库存储本地文件系统例如C盘的树状结构元数据,C++实现,支持增删改查,添加文件时分割目录,记录每层文件夹层级,自身ID,父文件夹ID等,从数据库删除文件夹时同时递归删除此文件夹下所有关联的子文件及文件夹

  2. Assistant

    以下为SQLite数据库的C++实现示例:

    1. 安装SQLite3

    下载SQLite3官方源代码,使用以下命令进行编译和安装:

    $ tar xzf sqlite-autoconf-*.tar.gz
    $ cd sqlite-autoconf-*
    $ ./configure --prefix=/usr/local
    $ make
    $ sudo make install
    
    1. 创建数据库表结构

    使用以下SQL语句创建文件系统的元数据表:

    CREATE TABLE filesystem (
        id INTEGER PRIMARY KEY,
        parent_id INTEGER,
        name TEXT NOT NULL,
        level INTEGER NOT NULL
    );
    
    1. 添加文件或目录

    在添加文件或目录时,需要先分割目录,并逐层添加到数据库中。

    示例代码:

    sqlite3* db;
    sqlite3_open("filesystem.db", &db);
    
    std::string path = "/path/to/file.txt";
    
    // 分割目录
    std::vector<std::string> dirs;
    boost::split(dirs, path, boost::is_any_of("/"));
    
    // 添加目录
    int parent_id = 0;
    for (size_t i = 0; i < dirs.size() - 1; i++) {
        std::string name = dirs[i];
        
        // 查询是否有同名目录
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << parent_id << " AND name = " << name;
        sqlite3_stmt* stmt;
        sqlite3_prepare_v2(db, ss.str().c_str(), -1, &stmt, nullptr);
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            // 没有同名目录,插入新的目录
            ss.str("");
            ss << "INSERT INTO filesystem (parent_id, name, level) VALUES(" << parent_id << ", " << name << ", " << i << ")";
            sqlite3_exec_v2(db, ss.str.c_str(), nullptr, nullptr);
        
            parent_id = sqlite3_last_insert_rowid(db);
        } else {
            // 已有同名目录,使用该目录ID
            parent_id = sqlite3_column_int(stmt, 0);
        }
    }
    
    // 添加文件
    std::string name = dirs.back();
    int level = dirs.size() - 1;
    std::stringstream ss;
    ss << "INSERT INTO filesystem (parent_id, name, level) VALUES(" << parent_id << ", " << name << ", " << level << ")";
    sqlite3_exec_v2(db, ss.str().c_str(), nullptr, nullptr);
    
    1. 删除文件或目录

    在删除文件或目录时,需要递归删除此文件夹下所有关联的子文件及文件夹,并从数据库中删除。

    示例代码:

    void deleteFilesystem(int id) {
        // 获取子文件和子目录
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << id;
        sqlite3_stmt* stmt;
        sqlite3_prepare_v2(db, ss.str().c_str(), -1, &stmt, nullptr);
        while (true) {
            int rc = sqlite3_step(stmt);
            if (rc == SQLITE_DONE) {
                break;
            }
    
            int child_id = sqlite3_column_int(stmt, 0);
            deleteFilesystem(child_id);
        }
    
        // 删除此目录
        ss.str("");
        ss << "DELETE FROM filesystem WHERE id = " << id;
        sqlite3_exec_v2(db, ss.str().c_str(), nullptr, nullptr);
    }
    
  3. User

    deleteFilesystem入参使用文件全路径,不使用id

  4. Assistant

    如果要使用文件全路径作为 deleteFilesystem 函数的入参,则需要在该函数内部将文件路径转换为对应的ID,然后再递归删除这个ID对应的目录及其子目录及文件。下面是一个示例代码:

    void deleteFilesystem(const std::string& path) {
        // 查询文件路径对应的ID
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE name = \"" << boost::filesystem::basename(path) 
           << "\" AND level = " << boost::algorithm::count_if(path, boost::is_any_of("/"));
        sqlite3_stmt* stmt;
        sqlite3_prepare_v2(db, ss.str().c_str(), -1, &stmt, nullptr);
    
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            // 文件或目录不存在,返回
            return;
        }
    
        int id = sqlite3_column_int(stmt, 0);
        deleteFilesystem(id);
    }
    

    需要注意的是,在使用文件路径作为入参时,需要确保该路径存在且是相对于存储文件系统的根目录的相对路径。否则需要对路径进行转换。

  5. User

    完整代码

  6. Assistant

    以下是一个完整的SQLite数据库存储本地文件系统的C++实现,支持基本的增删查功能。

    #include <boost/algorithm/string.hpp>
    #include <boost/filesystem.hpp>
    #include <iostream>
    #include <memory>
    #include <sstream>
    #include <sqlite3.h>
    #include <string>
    #include <vector>
    
    class Sqlite3 {
    
    public:
        Sqlite3(const std::string& filename) {
            sqlite3_open(filename.c_str(), &db_);
        }
    
        ~Sqlite3() {
            sqlite3_close(db_);
        }
    
        bool exec(const std::string& sql) {
            char* err_message;
            int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &err_message);
            if (rc != SQLITE_OK) {
                std::cerr << "Failed to execute SQL statement: " << err_message << std::endl;
                sqlite3_free(err_message);
                return false;
            }
            return true;
        }
    
        sqlite3_stmt* prepare(const std::string& sql) {
            sqlite3_stmt* stmt;
            sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, nullptr);
            return stmt;
        }
    
        void finalize(sqlite3_stmt* stmt) {
            sqlite3_finalize(stmt);
        }
    
        int getLastInsertRowId() {
            return sqlite3_last_insert_rowid(db_);
        }
    
    private:
        sqlite3* db_;
    };
    
    void createFilesystemTable(Sqlite3& db) {
        std::string sql = "CREATE TABLE filesystem (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT NOT NULL, level INTEGER NOT NULL)";
        db.exec(sql);
    }
    
    void insertFilesystem(Sqlite3& db, int parentId, const std::string& name, int level) {
        std::stringstream ss;
        ss << "INSERT INTO filesystem (parent_id, name, level) VALUES (" << parentId << ", '" << name << "', " << level << ")";
        db.exec(ss.str());
    }
    
    int queryFilesystem(Sqlite3& db, int parentId, const std::string& name, int level) {
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << parentId << " AND name = '" << name << "' AND level = " << level;
        sqlite3_stmt* stmt = db.prepare(ss.str());
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            return -1;
        }
        int id = sqlite3_column_int(stmt, 0);
        db.finalize(stmt);
        return id;
    }
    
    void deleteFilesystem(Sqlite3& db, int id) {
        // 获取子文件和子目录
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << id;
        sqlite3_stmt* stmt = db.prepare(ss.str());
        while (true) {
            int rc = sqlite3_step(stmt);
            if (rc == SQLITE_DONE) {
                break;
            }
    
            int child_id = sqlite3_column_int(stmt, 0);
            deleteFilesystem(db, child_id);
        }
        db.finalize(stmt);
    
        // 删除此目录
        ss.str("");
        ss << "DELETE FROM filesystem WHERE id = " << id;
        db.exec(ss.str());
    }
    
    int getFilesystemId(Sqlite3& db, const std::string& path) {
        // 查询文件路径对应的ID
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE name = \"" << boost::filesystem::basename(path) 
           << "\" AND level = " << boost::algorithm::count_if(path, boost::is_any_of("/"));
        sqlite3_stmt* stmt = db.prepare(ss.str());
    
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            // 文件或目录不存在,返回
            return -1;
        }
    
        int id = sqlite3_column_int(stmt, 0);
        db.finalize(stmt);
        return id;
    }
    
    void insertFileOrDir(Sqlite3& db, const std::string& path) {
        // 分割目录
        std::vector<std::string> dirs;
        boost::split(dirs, path, boost::is_any_of("/"));
    
        // 添加目录和文件
        int parentId = 0;
        for (size_t i = 0; i < dirs.size() - 1; i++) {
            std::string name = dirs[i];
    
            // 查询是否有同名目录
            int id = queryFilesystem(db, parentId, name, i);
            if (id < 0) {
                // 没有同名目录,插入新的目录
                insertFilesystem(db, parentId, name, i);
                parentId = db.getLastInsertRowId();
            } else {
                // 已有同名目录,使用该目录ID
                parentId = id;
            }
        }
    
        // 添加文件
        std::string name = dirs.back();
        int level = dirs.size() - 1;
        insertFilesystem(db, parentId, name, level);
    }
    
    void deleteFileOrDir(Sqlite3& db, const std::string& path) {
        int id = getFilesystemId(db, path);
        if (id >= 0) {
            deleteFilesystem(db, id);
        }
    }
    
    void printFilesystem(Sqlite3& db, int parentId, int level) {
        std::stringstream ss;
        ss << "SELECT id, name FROM filesystem WHERE parent_id = " << parentId << " AND level = " << level;
        sqlite3_stmt* stmt = db.prepare(ss.str());
    
        while (true) {
            int rc = sqlite3_step(stmt);
            if (rc == SQLITE_DONE) {
                break;
            }
    
            int id = sqlite3_column_int(stmt, 0);
            const unsigned char* name = sqlite3_column_text(stmt, 1);
            std::cout << std::string(level * 2, ' ') << name << " (" << id << ")" << std::endl;
    
            printFilesystem(db, id, level + 1);
        }
        db.finalize(stmt);
    }
    
    int main() {
        Sqlite3 db("filesystem.db");
        createFilesystemTable(db);
    
        insertFileOrDir(db, "dir1/dir2/file1.txt");
        insertFileOrDir(db, "dir1/dir3/file2.txt");
        insertFileOrDir(db, "dir1/file3.txt");
        insertFileOrDir(db, "file4.txt");
    
        printFilesystem(db, 0, 0);
        /*
          dir1 (1)
            dir2 (2)
              file1.txt (3)
            file3.txt (4)
            dir3 (5)
              file2.txt (6)
          file4.txt (7)
        */
    
        deleteFileOrDir(db, "dir1/dir2/file1.txt");
    
        printFilesystem(db, 0, 0);
        /*
          dir1 (1)
            file3.txt (4)
            dir3 (5)
              file2.txt (6)
          file4.txt (7)
        */
    
        return 0;
    }
    

    此示例使用Boost库实现了文件路径的分割,并使用了SQLite3的原生API进行数据库操作。应用程序在每个文件或目录的SQLite数据库中存储了一个元数据条目,包括其ID、父目录ID、名称及其在树状结构中的级别。可以通过递归查询和删除这些元数据来管理文件系统的层次结构。

  7. User

    首先优化下数据库表添加id,size,is_dir等字段,再实现windows下扫描C盘所有文件添加到上面的数据库

  8. Assistant

    好的,针对您提出的需求,我们可以先修改数据库表结构,增加一些额外的字段表示文件ID,文件大小和文件类型。

    修改后的数据库表结构如下:

    CREATE TABLE filesystem (
        id INTEGER PRIMARY KEY,
        parent_id INTEGER,
        name TEXT NOT NULL,
        level INTEGER NOT NULL,
        size INTEGER,
        is_dir BOOL
    );
    

    接下来我们可以编写Windows下的程序来扫描C盘所有文件,然后将其添加到数据库中。以下是一个基于Boost库的示例代码:

    #include <boost/algorithm/string.hpp>
    #include <boost/filesystem.hpp>
    #include <iostream>
    #include <memory>
    #include <sstream>
    #include <sqlite3.h>
    #include <string>
    #include <vector>
    
    class Sqlite3 {
    
    public:
        Sqlite3(const std::string& filename) {
            sqlite3_open(filename.c_str(), &db_);
        }
    
        ~Sqlite3() {
            sqlite3_close(db_);
        }
    
        bool exec(const std::string& sql) {
            char* err_message;
            int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &err_message);
            if (rc != SQLITE_OK) {
                std::cerr << "Failed to execute SQL statement: " << err_message << std::endl;
                sqlite3_free(err_message);
                return false;
            }
            return true;
        }
    
        sqlite3_stmt* prepare(const std::string& sql) {
            sqlite3_stmt* stmt;
            sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, nullptr);
            return stmt;
        }
    
        void finalize(sqlite3_stmt* stmt) {
            sqlite3_finalize(stmt);
        }
    
        int getLastInsertRowId() {
            return sqlite3_last_insert_rowid(db_);
        }
    
    private:
        sqlite3* db_;
    };
    
    void createFilesystemTable(Sqlite3& db) {
        std::string sql = "CREATE TABLE filesystem (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT NOT NULL, level INTEGER NOT NULL, size INTEGER, is_dir BOOL)";
        db.exec(sql);
    }
    
    void insertFilesystem(Sqlite3& db, int parentId, const std::string& name, int level, int size, bool is_dir) {
        std::stringstream ss;
        ss << "INSERT INTO filesystem (parent_id, name, level, size, is_dir) VALUES (" << parentId << ", '" << name << "', " << level << ", " << size << ", " << (is_dir ? "1" : "0") << ")";
        db.exec(ss.str());
    }
    
    int queryFilesystem(Sqlite3& db, int parentId, const std::string& name, int level) {
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << parentId << " AND name = '" << name << "' AND level = " << level;
        sqlite3_stmt* stmt = db.prepare(ss.str());
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            return -1;
        }
        int id = sqlite3_column_int(stmt, 0);
        db.finalize(stmt);
        return id;
    }
    
    void deleteFilesystem(Sqlite3& db, int id) {
        // 获取子文件和子目录
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE parent_id = " << id;
        sqlite3_stmt* stmt = db.prepare(ss.str());
        while (true) {
            int rc = sqlite3_step(stmt);
            if (rc == SQLITE_DONE) {
                break;
            }
    
            int child_id = sqlite3_column_int(stmt, 0);
            deleteFilesystem(db, child_id);
        }
        db.finalize(stmt);
    
        // 删除此目录
        ss.str("");
        ss << "DELETE FROM filesystem WHERE id = " << id;
        db.exec(ss.str());
    }
    
    int getFilesystemId(Sqlite3& db, const std::string& path) {
        // 查询文件路径对应的ID
        std::stringstream ss;
        ss << "SELECT id FROM filesystem WHERE name = \"" << boost::filesystem::basename(path) 
           << "\" AND level = " << boost::algorithm::count_if(path, boost::is_any_of("/"));
        sqlite3_stmt* stmt = db.prepare(ss.str());
    
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            // 文件或目录不存在,返回
            return -1;
        }
    
        int id = sqlite3_column_int(stmt, 0);
        db.finalize(stmt);
        return id;
    }
    
    void insertFileOrDir(Sqlite3& db, const boost::filesystem::path& filePath) {
    
        if (boost::filesystem::is_directory(filePath)) {
            // 添加目录
            std::string name = filePath.filename().string();
            std::string path = filePath.string();
            int level = boost::algorithm::count_if(path, boost::is_any_of("\\")) - 2; // 目录的level值需要减去根目录和目录本身的层级
    
            // 查询是否有同名目录
            int parentId = 0;
            for (size_t i = 0; i < level; i++) {
                std::string dirName = boost::split_iterator<std::string::const_iterator>(path.begin() + 3, path.end(), boost::is_any_of("\\"))[i];
                int id = queryFilesystem(db, parentId, dirName, i);
                if (id < 0) {
                    // 没有同名目录,插入新的目录
                    insertFilesystem(db, parentId, dirName, i, 0, true);
                    parentId = db.getLastInsertRowId
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.