Swift4 iOS11中如何附加两个SQLite数据库实现跨库查询

Hey there! Let's get that cross-database query working using SQLite's ATTACH command. I’ll walk you through adjusting your existing code to properly attach the second database and run cross-library queries without hitches.

Key Fixes & Modified Code #

The main issues you might have hit are incorrect file path formatting for the ATTACH command, missing parameter binding (which avoids SQL injection and path-related bugs), and incomplete error handling. Here’s a revised version of your function that supports dual databases:

func readTwoDBs() -> String? {
    // Get valid file paths for both databases
    guard let file1URL = try? createSQLFilePath(fileName: "file1", fileExtension: "db"),
          let file2URL = try? createSQLFilePath(fileName: "file2", fileExtension: "db") else {
        print("Failed to retrieve database file paths")
        return nil
    }
    
    // SQLite needs raw file system paths, not the "file://" prefixed absoluteString
    let file1Path = file1URL.path
    let file2Path = file2URL.path
    
    var db: OpaquePointer? = nil
    
    // Open the primary database (file1.db) first
    if sqlite3_open(file1Path, &db) != SQLITE_OK {
        print("Error opening primary database")
        sqlite3_close(db)
        return nil
    }
    
    // Ensure we close the database when done, even if errors occur
    defer {
        sqlite3_close(db)
    }
    
    // Attach the second database with an alias (we'll use "file2" for easy reference)
    let attachQuery = "ATTACH DATABASE ? AS file2"
    var attachStatement: OpaquePointer? = nil
    
    if sqlite3_prepare_v2(db, attachQuery, -1, &attachStatement, nil) == SQLITE_OK {
        // Bind the file path to avoid issues with special characters
        sqlite3_bind_text(attachStatement, 1, file2Path, -1, SQLITE_TRANSIENT)
        
        if sqlite3_step(attachStatement) != SQLITE_DONE {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("Error attaching second database: \(errmsg)")
            sqlite3_finalize(attachStatement)
            return nil
        }
        sqlite3_finalize(attachStatement)
    } else {
        let errmsg = String(cString: sqlite3_errmsg(db))
        print("Error preparing attach command: \(errmsg)")
        return nil
    }
    
    // Example cross-database query (adjust tables/joins to match your schema)
    // Use the alias "file2" to reference tables from the second database
    let crossQuery = """
        SELECT DISTINCT n.locations 
        FROM names n 
        -- Join with a table from the attached database (replace with your actual table)
        LEFT JOIN file2.related_names rn ON n.id = rn.name_id
        WHERE n.name = ?
    """
    
    var queryStatement: OpaquePointer? = nil
    
    if sqlite3_prepare_v2(db, crossQuery, -1, &queryStatement, nil) == SQLITE_OK {
        // Bind the name parameter (safer than string interpolation to avoid SQL injection)
        sqlite3_bind_text(queryStatement, 1, self.myName, -1, SQLITE_TRANSIENT)
        
        while sqlite3_step(queryStatement) == SQLITE_ROW {
            if let nameCString = sqlite3_column_text(queryStatement, 0) {
                let nameString = String(cString: nameCString)
                sqlite3_finalize(queryStatement)
                return nameString
            } else {
                print("No matching location found")
                sqlite3_finalize(queryStatement)
                return nil
            }
        }
        sqlite3_finalize(queryStatement)
        print("Query returned no results")
        return nil
    } else {
        let errmsg = String(cString: sqlite3_errmsg(db))
        print("Error preparing cross-database query: \(errmsg)")
        return nil
    }
}

Critical Notes to Avoid Headaches #

• Path Format: Use fileURL.path instead of absoluteString—SQLite doesn’t recognize the file:// prefix in the ATTACH command.
• Parameter Binding: I replaced your string interpolation with sqlite3_bind_text—this prevents SQL injection and fixes bugs if myName contains quotes or special characters.
• Database Alias: The AS file2 in the ATTACH command lets you reference tables from the second database using file2.table_name in your queries.
• Auto-Detach: When you close the primary database, the attached database is automatically detached—no need to run a separate DETACH command.
• Error Handling: Added checks at every step (opening, attaching, querying) to help you debug exactly where things go wrong.

内容的提问来源于stack exchange，提问作者iKK