swift - 在 Swift 中使用 FMDB 从多个表中进行选择

Question

我正在寻找一种方法，使用 Swift 中的 FMDB 从 7 个不同的表中打印出四个特定的列。我试过用点符号来选择它们，但这似乎不起作用。

数据库看起来像这样:

• 艺术家:[ArtistId]、[姓名]
• 轨道:[TrackId]、[AlbumId]、[名称]
• 专辑:[AlbumId]、[姓名]、[ArtistId]
• 类型:[GenreId]、[名称]
• 发票:[CustomerId]、[InvoiceId]
• 发票行:[InvoiceId]
• 客户:[CustomerId]、[FirstName]、[LastName]、[City]

.

import Foundation

let database = FMDatabase(path: " /path to database.sqlite/ ")

database.open()

let rs = try database.executeQuery("SELECT Artist.Name, Track.Name, Album.Title, Genre.Name 
                                    FROM Invoice, Customer, InvoiceLine, Track, Album, Artist, Genre
                                    WHERE Customer.FirstName = 'Michelle'
                                    AND Customer.LastName = 'Brooks' 
                                    AND Customer.City = 'New York'
                                    AND Invoice.CustomerId=Customer.CustomerId 
                                    AND InvoiceLine.InvoiceId = Invoice.InvoiceId 
                                    AND Track.TrackId = InvoiceLine.TrackId 
                                    AND Album.AlbumId = Track.AlbumId 
                                    AND Artist.ArtistId = Album.ArtistId
                                    AND Genre.GenreId = Track.GenreId ", values: nil)

while rs.next() {
    let artistName = rs.stringForColumn("Artist.Name")
    let trackName = rs.stringForColumn("Track.Name")
    let albumTitle = rs.stringForColumn("Album.Title")
    let genre = rs.stringForColumn("Genre.Name")

print (artistName + " " + trackName + " " + albumTitle + " " + genre)
}

Answer 1

问题在于 stringForColumn(它使用 sqlite_column_name)函数使用基础表的列名，而忽略了表名。更糟糕的是，如果您有两个同名的列，它无法知道您想从哪个表中获取结果。

因此，有两种解决方法:

1. 您可以为列指定唯一的名称:

   let rs = try database.executeQuery("SELECT Artist.Name AS ArtistName, Track.Name AS TrackName, Album.Title, Genre.Name AS Genre.Name ..."
   

   然后

   let artistName = rs.stringForColumn("ArtistName")
   let trackName = rs.stringForColumn("TrackName")
   let albumTitle = rs.stringForColumn("Title")
   let genre = rs.stringForColumn("GenreName")
   

   或

2. 使用列号:

   let artistName = rs.stringForColumnIndex(0)
   let trackName = rs.stringForColumnIndex(1)
   let albumTitle = rs.stringForColumnIndex(2)
   let genre = rs.stringForColumnIndex(3)