SQLite.swift
SQLite.swiftは「A type-safe, Swift-language layer over SQLite3」として開発された、Swift専用のSQLiteデータベースアクセスライブラリです。SQLite3の薄いラッパーとして機能し、Swiftの型安全性とエクスプレッシブな構文を活用したデータベース操作を提供します。iOS、macOS、watchOS、tvOSのすべてのAppleプラットフォームに対応し、Core DataやRealm等の重厚なフレームワークに対する軽量な代替手段として、モバイルアプリ開発やマックアプリケーション開発において広く採用されています。
GitHub概要
stephencelis/SQLite.swift
A type-safe, Swift-language layer over SQLite3.
スター9,973
ウォッチ217
フォーク1,607
作成日:2014年10月4日
言語:Swift
ライセンス:MIT License
トピックス
sqliteswift
スター履歴
データ取得日時: 2025/7/19 10:28
ライブラリ
SQLite.swift
概要
SQLite.swiftは「A type-safe, Swift-language layer over SQLite3」として開発された、Swift専用のSQLiteデータベースアクセスライブラリです。SQLite3の薄いラッパーとして機能し、Swiftの型安全性とエクスプレッシブな構文を活用したデータベース操作を提供します。iOS、macOS、watchOS、tvOSのすべてのAppleプラットフォームに対応し、Core DataやRealm等の重厚なフレームワークに対する軽量な代替手段として、モバイルアプリ開発やマックアプリケーション開発において広く採用されています。
詳細
SQLite.swift 2025年版は、Swift 5.9以降の最新機能（async/await、Property Wrappers、Result Builder等）を活用し、モダンなSwift開発パラダイムに最適化されています。型安全なクエリビルダー、プリペアドステートメント、トランザクション管理、マイグレーション機能を包括的にサポートし、企業レベルのiOSアプリ開発に必要な信頼性とパフォーマンスを提供します。SwiftUIとの自然な統合、Combine対応、Swift Package Manager完全対応により、現代のiOS開発ワークフローにシームレスに統合可能です。
主な特徴
- 型安全性: Swiftの型システムを活用した完全型安全なクエリ構築
- 軽量設計: SQLite3の薄いラッパーで最小限のオーバーヘッド
- 全プラットフォーム対応: iOS、macOS、watchOS、tvOSサポート
- モダンSwift: async/await、Property Wrappers等の最新機能対応
- クエリビルダー: 直感的で読みやすいクエリ構築API
- マイグレーション: データベーススキーマ変更の自動管理
メリット・デメリット
メリット
- SQLite3の高速性を保持しつつSwiftの型安全性を享受
- Core DataやRealmに比べて軽量で学習コストが低い
- SQLに近い記述でデータベース操作の透明性が高い
- 全Appleプラットフォームでの一貫した開発体験
- Swift Package Managerによる簡単な導入とバージョン管理
- プリペアドステートメントによる高いセキュリティ
デメリット
- Swift/Appleプラットフォーム専用でクロスプラットフォーム対応なし
- Core Dataのような高度なオブジェクトマッピング機能は提供されない
- 複雑なリレーションシップ管理は手動実装が必要
- 大規模データセットでのパフォーマンス最適化は開発者次第
- バックグラウンド処理やマルチスレッド対応は注意が必要
- NoSQL的な柔軟性はSQLiteの制約に依存
参考ページ
書き方の例
セットアップ
// Package.swift
// swift-tools-version: 5.9
import PackageDescription
let package = Package(
name: "SQLiteSwiftExample",
platforms: [
.iOS(.v15),
.macOS(.v12),
.watchOS(.v8),
.tvOS(.v15)
],
dependencies: [
.package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.15.3")
],
targets: [
.target(
name: "SQLiteSwiftExample",
dependencies: [
.product(name: "SQLite", package: "SQLite.swift")
]
),
.testTarget(
name: "SQLiteSwiftExampleTests",
dependencies: ["SQLiteSwiftExample"]
)
]
)
// iOS Xcodeプロジェクトの場合
// File -> Add Package Dependencies -> https://github.com/stephencelis/SQLite.swift.git
import SQLite
import Foundation
基本的な使い方
import SQLite
import Foundation
// データベース接続とテーブル定義
class UserDatabase {
private var db: Connection?
// テーブル定義
private let users = Table("users")
private let id = Expression<Int64>("id")
private let name = Expression<String>("name")
private let email = Expression<String>("email")
private let age = Expression<Int>("age")
private let isActive = Expression<Bool>("is_active")
private let createdAt = Expression<Date>("created_at")
private let profile = Expression<String?>("profile") // JSON文字列として保存
// 投稿テーブル
private let posts = Table("posts")
private let postId = Expression<Int64>("post_id")
private let userId = Expression<Int64>("user_id")
private let title = Expression<String>("title")
private let content = Expression<String>("content")
private let published = Expression<Bool>("published")
private let publishedAt = Expression<Date?>("published_at")
private let viewCount = Expression<Int>("view_count")
init() {
do {
let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
let dbPath = "\(path)/userdb.sqlite3"
db = try Connection(dbPath)
print("Database connected at: \(dbPath)")
createTables()
} catch {
print("Database connection failed: \(error)")
}
}
// テーブル作成
private func createTables() {
do {
// ユーザーテーブル作成
try db?.run(users.create(ifNotExists: true) { table in
table.column(id, primaryKey: .autoincrement)
table.column(name)
table.column(email, unique: true)
table.column(age)
table.column(isActive, defaultValue: true)
table.column(createdAt, defaultValue: Date())
table.column(profile)
})
// 投稿テーブル作成
try db?.run(posts.create(ifNotExists: true) { table in
table.column(postId, primaryKey: .autoincrement)
table.column(userId)
table.column(title)
table.column(content)
table.column(published, defaultValue: false)
table.column(publishedAt)
table.column(viewCount, defaultValue: 0)
table.foreignKey(userId, references: users, id, delete: .cascade)
})
// インデックス作成
try db?.run(users.createIndex(email, ifNotExists: true))
try db?.run(posts.createIndex(userId, ifNotExists: true))
try db?.run(posts.createIndex(published, ifNotExists: true))
print("Tables created successfully")
} catch {
print("Create table error: \(error)")
}
}
}
// ユーザーモデル
struct User {
let id: Int64?
let name: String
let email: String
let age: Int
let isActive: Bool
let createdAt: Date
let profile: UserProfile?
init(id: Int64? = nil, name: String, email: String, age: Int, isActive: Bool = true, createdAt: Date = Date(), profile: UserProfile? = nil) {
self.id = id
self.name = name
self.email = email
self.age = age
self.isActive = isActive
self.createdAt = createdAt
self.profile = profile
}
}
// ユーザープロフィール（JSON）
struct UserProfile: Codable {
let bio: String?
let website: String?
let location: String?
let skills: [String]
init(bio: String? = nil, website: String? = nil, location: String? = nil, skills: [String] = []) {
self.bio = bio
self.website = website
self.location = location
self.skills = skills
}
}
// 投稿モデル
struct Post {
let id: Int64?
let userId: Int64
let title: String
let content: String
let published: Bool
let publishedAt: Date?
let viewCount: Int
init(id: Int64? = nil, userId: Int64, title: String, content: String, published: Bool = false, publishedAt: Date? = nil, viewCount: Int = 0) {
self.id = id
self.userId = userId
self.title = title
self.content = content
self.published = published
self.publishedAt = publishedAt
self.viewCount = viewCount
}
}
// ユーザーサービス拡張
extension UserDatabase {
// ユーザー作成
func createUser(_ user: User) throws -> Int64 {
guard let db = db else { throw DatabaseError.connectionFailed }
let profileJSON = try user.profile?.toJSONString()
let insert = users.insert(
name <- user.name,
email <- user.email,
age <- user.age,
isActive <- user.isActive,
createdAt <- user.createdAt,
profile <- profileJSON
)
let rowid = try db.run(insert)
print("Created user with ID: \(rowid)")
return rowid
}
// 全ユーザー取得
func getAllUsers() throws -> [User] {
guard let db = db else { throw DatabaseError.connectionFailed }
var result: [User] = []
for user in try db.prepare(users.order(createdAt.desc)) {
let userProfile = try? UserProfile.fromJSONString(user[profile])
result.append(User(
id: user[id],
name: user[name],
email: user[email],
age: user[age],
isActive: user[isActive],
createdAt: user[createdAt],
profile: userProfile
))
}
return result
}
// ID指定でユーザー取得
func getUserById(_ userId: Int64) throws -> User? {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = users.filter(id == userId)
guard let user = try db.pluck(query) else { return nil }
let userProfile = try? UserProfile.fromJSONString(user[profile])
return User(
id: user[id],
name: user[name],
email: user[email],
age: user[age],
isActive: user[isActive],
createdAt: user[createdAt],
profile: userProfile
)
}
// メールアドレスでユーザー検索
func getUserByEmail(_ userEmail: String) throws -> User? {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = users.filter(email == userEmail)
guard let user = try db.pluck(query) else { return nil }
let userProfile = try? UserProfile.fromJSONString(user[profile])
return User(
id: user[id],
name: user[name],
email: user[email],
age: user[age],
isActive: user[isActive],
createdAt: user[createdAt],
profile: userProfile
)
}
// ユーザー更新
func updateUser(_ userId: Int64, name: String, email: String, age: Int, profile: UserProfile?) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let userToUpdate = users.filter(id == userId)
let profileJSON = try profile?.toJSONString()
let update = userToUpdate.update(
self.name <- name,
self.email <- email,
self.age <- age,
self.profile <- profileJSON
)
let changes = try db.run(update)
return changes > 0
}
// ユーザーのアクティブ状態変更
func setUserActive(_ userId: Int64, active: Bool) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let userToUpdate = users.filter(id == userId)
let update = userToUpdate.update(isActive <- active)
let changes = try db.run(update)
return changes > 0
}
// ユーザー削除
func deleteUser(_ userId: Int64) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let userToDelete = users.filter(id == userId)
let changes = try db.run(userToDelete.delete())
return changes > 0
}
// 名前で検索
func searchUsersByName(_ searchTerm: String) throws -> [User] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = users.filter(name.like("%\(searchTerm)%")).order(name)
var result: [User] = []
for user in try db.prepare(query) {
let userProfile = try? UserProfile.fromJSONString(user[profile])
result.append(User(
id: user[id],
name: user[name],
email: user[email],
age: user[age],
isActive: user[isActive],
createdAt: user[createdAt],
profile: userProfile
))
}
return result
}
// 年齢範囲で検索
func getUsersByAgeRange(min: Int, max: Int) throws -> [User] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = users.filter(age >= min && age <= max).order(age, name)
var result: [User] = []
for user in try db.prepare(query) {
let userProfile = try? UserProfile.fromJSONString(user[profile])
result.append(User(
id: user[id],
name: user[name],
email: user[email],
age: user[age],
isActive: user[isActive],
createdAt: user[createdAt],
profile: userProfile
))
}
return result
}
// アクティブユーザー数カウント
func getActiveUserCount() throws -> Int {
guard let db = db else { throw DatabaseError.connectionFailed }
return try db.scalar(users.filter(isActive == true).count)
}
// 総ユーザー数
func getTotalUserCount() throws -> Int {
guard let db = db else { throw DatabaseError.connectionFailed }
return try db.scalar(users.count)
}
}
// 使用例
func demonstrateBasicOperations() {
let database = UserDatabase()
do {
// ユーザー作成
let profile1 = UserProfile(bio: "iOS Developer", website: "https://example.com", location: "Tokyo", skills: ["Swift", "iOS", "SwiftUI"])
let user1 = User(name: "Alice Johnson", email: "[email protected]", age: 28, profile: profile1)
let userId1 = try database.createUser(user1)
let profile2 = UserProfile(bio: "Backend Developer", location: "Osaka", skills: ["Swift", "Vapor", "PostgreSQL"])
let user2 = User(name: "Bob Smith", email: "[email protected]", age: 32, profile: profile2)
let userId2 = try database.createUser(user2)
let user3 = User(name: "Charlie Brown", email: "[email protected]", age: 25)
let userId3 = try database.createUser(user3)
print("Created users: \(userId1), \(userId2), \(userId3)")
// 全ユーザー取得
let allUsers = try database.getAllUsers()
print("\n=== All Users ===")
for user in allUsers {
print("ID: \(user.id ?? 0), Name: \(user.name), Email: \(user.email), Age: \(user.age)")
if let profile = user.profile {
print(" Bio: \(profile.bio ?? "N/A"), Skills: \(profile.skills.joined(separator: ", "))")
}
}
// 特定ユーザー取得
if let retrievedUser = try database.getUserById(userId1) {
print("\n=== Retrieved User ===")
print("Retrieved: \(retrievedUser.name) (\(retrievedUser.email))")
}
// ユーザー更新
let updatedProfile = UserProfile(bio: "Senior iOS Developer", website: "https://alice-dev.com", location: "Tokyo", skills: ["Swift", "iOS", "SwiftUI", "Combine"])
let updated = try database.updateUser(userId1, name: "Alice Johnson Smith", email: "[email protected]", age: 29, profile: updatedProfile)
if updated {
print("\n=== User Updated ===")
if let updatedUser = try database.getUserById(userId1) {
print("Updated: \(updatedUser.name) (\(updatedUser.email))")
}
}
// 名前検索
let searchResults = try database.searchUsersByName("Alice")
print("\n=== Search Results ===")
for user in searchResults {
print("Found: \(user.name) (\(user.email))")
}
// 年齢範囲検索
let ageRangeUsers = try database.getUsersByAgeRange(min: 25, max: 30)
print("\n=== Users aged 25-30 ===")
for user in ageRangeUsers {
print("\(user.name): \(user.age) years old")
}
// 統計情報
let totalUsers = try database.getTotalUserCount()
let activeUsers = try database.getActiveUserCount()
print("\n=== Statistics ===")
print("Total users: \(totalUsers)")
print("Active users: \(activeUsers)")
} catch {
print("Error: \(error)")
}
}
投稿機能とJOIN操作
// 投稿サービス拡張
extension UserDatabase {
// 投稿作成
func createPost(_ post: Post) throws -> Int64 {
guard let db = db else { throw DatabaseError.connectionFailed }
let insert = posts.insert(
userId <- post.userId,
title <- post.title,
content <- post.content,
published <- post.published,
publishedAt <- post.publishedAt,
viewCount <- post.viewCount
)
let rowid = try db.run(insert)
print("Created post with ID: \(rowid)")
return rowid
}
// 投稿取得
func getPostById(_ postIdValue: Int64) throws -> Post? {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts.filter(postId == postIdValue)
guard let post = try db.pluck(query) else { return nil }
return Post(
id: post[postId],
userId: post[userId],
title: post[title],
content: post[content],
published: post[published],
publishedAt: post[publishedAt],
viewCount: post[viewCount]
)
}
// ユーザーの投稿一覧取得
func getPostsByUser(_ userIdValue: Int64) throws -> [Post] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts.filter(userId == userIdValue).order(postId.desc)
var result: [Post] = []
for post in try db.prepare(query) {
result.append(Post(
id: post[postId],
userId: post[userId],
title: post[title],
content: post[content],
published: post[published],
publishedAt: post[publishedAt],
viewCount: post[viewCount]
))
}
return result
}
// 公開済み投稿取得
func getPublishedPosts(limit: Int = 20, offset: Int = 0) throws -> [Post] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts
.filter(published == true)
.order(publishedAt.desc)
.limit(limit, offset: offset)
var result: [Post] = []
for post in try db.prepare(query) {
result.append(Post(
id: post[postId],
userId: post[userId],
title: post[title],
content: post[content],
published: post[published],
publishedAt: post[publishedAt],
viewCount: post[viewCount]
))
}
return result
}
// 投稿とユーザー情報結合取得
func getPostsWithAuthors(limit: Int = 20) throws -> [(Post, User)] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts
.join(users, on: users[id] == posts[userId])
.order(posts[postId].desc)
.limit(limit)
var result: [(Post, User)] = []
for row in try db.prepare(query) {
let post = Post(
id: row[posts[postId]],
userId: row[posts[userId]],
title: row[posts[title]],
content: row[posts[content]],
published: row[posts[published]],
publishedAt: row[posts[publishedAt]],
viewCount: row[posts[viewCount]]
)
let userProfile = try? UserProfile.fromJSONString(row[users[profile]])
let user = User(
id: row[users[id]],
name: row[users[name]],
email: row[users[email]],
age: row[users[age]],
isActive: row[users[isActive]],
createdAt: row[users[createdAt]],
profile: userProfile
)
result.append((post, user))
}
return result
}
// 投稿公開
func publishPost(_ postIdValue: Int64) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let postToUpdate = posts.filter(postId == postIdValue)
let update = postToUpdate.update(
published <- true,
publishedAt <- Date()
)
let changes = try db.run(update)
return changes > 0
}
// ビューカウント増加
func incrementViewCount(_ postIdValue: Int64) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let postToUpdate = posts.filter(postId == postIdValue)
let update = postToUpdate.update(viewCount <- viewCount + 1)
let changes = try db.run(update)
return changes > 0
}
// 投稿削除
func deletePost(_ postIdValue: Int64) throws -> Bool {
guard let db = db else { throw DatabaseError.connectionFailed }
let postToDelete = posts.filter(postId == postIdValue)
let changes = try db.run(postToDelete.delete())
return changes > 0
}
// タイトルで投稿検索
func searchPostsByTitle(_ searchTerm: String) throws -> [Post] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts.filter(title.like("%\(searchTerm)%")).order(postId.desc)
var result: [Post] = []
for post in try db.prepare(query) {
result.append(Post(
id: post[postId],
userId: post[userId],
title: post[title],
content: post[content],
published: post[published],
publishedAt: post[publishedAt],
viewCount: post[viewCount]
))
}
return result
}
// 人気投稿取得（ビューカウント順）
func getPopularPosts(limit: Int = 10) throws -> [Post] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = posts
.filter(published == true)
.order(viewCount.desc)
.limit(limit)
var result: [Post] = []
for post in try db.prepare(query) {
result.append(Post(
id: post[postId],
userId: post[userId],
title: post[title],
content: post[content],
published: post[published],
publishedAt: post[publishedAt],
viewCount: post[viewCount]
))
}
return result
}
// ユーザー別投稿数
func getUserPostCounts() throws -> [(User, Int)] {
guard let db = db else { throw DatabaseError.connectionFailed }
let query = """
SELECT u.*, COUNT(p.post_id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
ORDER BY post_count DESC
"""
var result: [(User, Int)] = []
for row in try db.prepare(query) {
let userProfile = try? UserProfile.fromJSONString(row[6] as? String)
let user = User(
id: row[0] as! Int64,
name: row[1] as! String,
email: row[2] as! String,
age: row[3] as! Int,
isActive: row[4] as! Bool,
createdAt: row[5] as! Date,
profile: userProfile
)
let postCount = row[7] as! Int
result.append((user, postCount))
}
return result
}
// 投稿統計情報
func getPostStatistics() throws -> (total: Int, published: Int, avgViewCount: Double) {
guard let db = db else { throw DatabaseError.connectionFailed }
let totalPosts = try db.scalar(posts.count)
let publishedPosts = try db.scalar(posts.filter(published == true).count)
let avgViews = try db.scalar(posts.select(viewCount.average)) ?? 0.0
return (total: totalPosts, published: publishedPosts, avgViewCount: avgViews)
}
}
// 使用例
func demonstratePostOperations() {
let database = UserDatabase()
do {
// ユーザー作成
let user = User(name: "John Doe", email: "[email protected]", age: 30)
let userId = try database.createUser(user)
// 投稿作成
let post1 = Post(userId: userId, title: "My First Post", content: "This is the content of my first post.")
let post2 = Post(userId: userId, title: "Another Post", content: "This is another interesting post.")
let post3 = Post(userId: userId, title: "Published Post", content: "This post is published.", published: true, publishedAt: Date())
let postId1 = try database.createPost(post1)
let postId2 = try database.createPost(post2)
let postId3 = try database.createPost(post3)
print("Created posts: \(postId1), \(postId2), \(postId3)")
// 投稿公開
_ = try database.publishPost(postId1)
print("Published post: \(postId1)")
// ビューカウント増加
_ = try database.incrementViewCount(postId1)
_ = try database.incrementViewCount(postId3)
_ = try database.incrementViewCount(postId3)
// 公開済み投稿取得
let publishedPosts = try database.getPublishedPosts()
print("\n=== Published Posts ===")
for post in publishedPosts {
print("Post: \(post.title) (Views: \(post.viewCount))")
}
// 投稿とユーザー情報結合
let postsWithAuthors = try database.getPostsWithAuthors()
print("\n=== Posts with Authors ===")
for (post, author) in postsWithAuthors {
print("Post: \(post.title) by \(author.name)")
}
// 人気投稿
let popularPosts = try database.getPopularPosts(limit: 5)
print("\n=== Popular Posts ===")
for post in popularPosts {
print("Popular: \(post.title) (\(post.viewCount) views)")
}
// 統計情報
let stats = try database.getPostStatistics()
print("\n=== Post Statistics ===")
print("Total posts: \(stats.total)")
print("Published posts: \(stats.published)")
print("Average view count: \(String(format: "%.1f", stats.avgViewCount))")
// ユーザー別投稿数
let userPostCounts = try database.getUserPostCounts()
print("\n=== User Post Counts ===")
for (user, count) in userPostCounts {
print("\(user.name): \(count) posts")
}
} catch {
print("Error: \(error)")
}
}
トランザクションと高度な機能
// トランザクション機能とバッチ処理
extension UserDatabase {
// トランザクション：ユーザーと初期投稿を同時作成
func createUserWithPosts(user: User, initialPosts: [Post]) throws -> (userId: Int64, postIds: [Int64]) {
guard let db = db else { throw DatabaseError.connectionFailed }
var userId: Int64 = 0
var postIds: [Int64] = []
try db.transaction {
// ユーザー作成
let profileJSON = try user.profile?.toJSONString()
let userInsert = users.insert(
name <- user.name,
email <- user.email,
age <- user.age,
isActive <- user.isActive,
createdAt <- user.createdAt,
profile <- profileJSON
)
userId = try db.run(userInsert)
// 初期投稿作成
for postData in initialPosts {
let postInsert = posts.insert(
self.userId <- userId,
title <- postData.title,
content <- postData.content,
published <- postData.published,
publishedAt <- postData.publishedAt,
viewCount <- postData.viewCount
)
let postId = try db.run(postInsert)
postIds.append(postId)
}
}
return (userId: userId, postIds: postIds)
}
// トランザクション：ユーザー間で投稿を移譲
func transferPosts(fromUserId: Int64, toUserId: Int64) throws -> Int {
guard let db = db else { throw DatabaseError.connectionFailed }
var transferCount = 0
try db.transaction {
// 両方のユーザーの存在確認
guard try db.pluck(users.filter(id == fromUserId)) != nil else {
throw DatabaseError.userNotFound(fromUserId)
}
guard try db.pluck(users.filter(id == toUserId)) != nil else {
throw DatabaseError.userNotFound(toUserId)
}
// 投稿を移譲
let postsToTransfer = posts.filter(userId == fromUserId)
transferCount = try db.run(postsToTransfer.update(userId <- toUserId))
}
return transferCount
}
// バッチ処理：複数ユーザーの年齢を一括更新
func batchUpdateUserAges(userAgeUpdates: [(userId: Int64, newAge: Int)]) throws -> Int {
guard let db = db else { throw DatabaseError.connectionFailed }
var totalUpdated = 0
try db.transaction {
for update in userAgeUpdates {
let userToUpdate = users.filter(id == update.userId)
let changes = try db.run(userToUpdate.update(age <- update.newAge))
totalUpdated += changes
}
}
return totalUpdated
}
// 条件付き削除：非アクティブユーザーとその投稿を削除
func cleanupInactiveUsers() throws -> (deletedUsers: Int, deletedPosts: Int) {
guard let db = db else { throw DatabaseError.connectionFailed }
var deletedUsers = 0
var deletedPosts = 0
try db.transaction {
// 非アクティブユーザーを取得
let inactiveUsers = users.filter(isActive == false)
// 非アクティブユーザーのIDを取得
var inactiveUserIds: [Int64] = []
for user in try db.prepare(inactiveUsers) {
inactiveUserIds.append(user[id])
}
// 関連する投稿を削除
for userIdValue in inactiveUserIds {
let userPosts = posts.filter(userId == userIdValue)
deletedPosts += try db.run(userPosts.delete())
}
// ユーザーを削除
deletedUsers = try db.run(inactiveUsers.delete())
}
return (deletedUsers: deletedUsers, deletedPosts: deletedPosts)
}
// データベースバックアップ（シンプル版）
func exportUserData() throws -> String {
guard let db = db else { throw DatabaseError.connectionFailed }
var exportData: [String: Any] = [:]
// ユーザーデータエクスポート
let allUsers = try getAllUsers()
let usersData = allUsers.map { user in
[
"id": user.id ?? 0,
"name": user.name,
"email": user.email,
"age": user.age,
"isActive": user.isActive,
"createdAt": ISO8601DateFormatter().string(from: user.createdAt),
"profile": user.profile?.toDictionary() ?? [:]
]
}
exportData["users"] = usersData
// 投稿データエクスポート
let allPosts = try db.prepare(posts)
let postsData = try allPosts.map { post in
[
"id": post[postId],
"userId": post[userId],
"title": post[title],
"content": post[content],
"published": post[published],
"publishedAt": post[publishedAt] != nil ? ISO8601DateFormatter().string(from: post[publishedAt]!) : nil,
"viewCount": post[viewCount]
]
}
exportData["posts"] = postsData
let jsonData = try JSONSerialization.data(withJSONObject: exportData, options: .prettyPrinted)
return String(data: jsonData, encoding: .utf8) ?? ""
}
// データベース統計
func getDatabaseStatistics() throws -> DatabaseStatistics {
guard let db = db else { throw DatabaseError.connectionFailed }
let totalUsers = try db.scalar(users.count)
let activeUsers = try db.scalar(users.filter(isActive == true).count)
let totalPosts = try db.scalar(posts.count)
let publishedPosts = try db.scalar(posts.filter(published == true).count)
let avgAge = try db.scalar(users.select(age.average)) ?? 0.0
let avgViewCount = try db.scalar(posts.select(viewCount.average)) ?? 0.0
return DatabaseStatistics(
totalUsers: totalUsers,
activeUsers: activeUsers,
totalPosts: totalPosts,
publishedPosts: publishedPosts,
averageUserAge: avgAge,
averagePostViewCount: avgViewCount
)
}
}
// 統計情報構造体
struct DatabaseStatistics {
let totalUsers: Int
let activeUsers: Int
let totalPosts: Int
let publishedPosts: Int
let averageUserAge: Double
let averagePostViewCount: Double
}
// 使用例
func demonstrateAdvancedFeatures() {
let database = UserDatabase()
do {
// ユーザーと初期投稿を同時作成
let profile = UserProfile(bio: "Content Creator", skills: ["Writing", "Photography"])
let user = User(name: "Content Creator", email: "[email protected]", age: 25, profile: profile)
let initialPosts = [
Post(userId: 0, title: "Welcome Post", content: "Welcome to my blog!"),
Post(userId: 0, title: "About Me", content: "Let me introduce myself..."),
Post(userId: 0, title: "My First Tutorial", content: "This is my first technical post.", published: true, publishedAt: Date())
]
let (userId, postIds) = try database.createUserWithPosts(user: user, initialPosts: initialPosts)
print("Created user \(userId) with posts: \(postIds)")
// 別のユーザー作成
let targetUser = User(name: "Another User", email: "[email protected]", age: 30)
let targetUserId = try database.createUser(targetUser)
// 投稿移譲
let transferCount = try database.transferPosts(fromUserId: userId, toUserId: targetUserId)
print("Transferred \(transferCount) posts from user \(userId) to user \(targetUserId)")
// バッチ年齢更新
let ageUpdates = [
(userId: userId, newAge: 26),
(userId: targetUserId, newAge: 31)
]
let updatedCount = try database.batchUpdateUserAges(userAgeUpdates: ageUpdates)
print("Updated ages for \(updatedCount) users")
// ユーザーを非アクティブに設定
_ = try database.setUserActive(userId, active: false)
// 非アクティブユーザーのクリーンアップ
let (deletedUsers, deletedPosts) = try database.cleanupInactiveUsers()
print("Cleanup: deleted \(deletedUsers) users and \(deletedPosts) posts")
// 統計情報
let stats = try database.getDatabaseStatistics()
print("\n=== Database Statistics ===")
print("Total users: \(stats.totalUsers)")
print("Active users: \(stats.activeUsers)")
print("Total posts: \(stats.totalPosts)")
print("Published posts: \(stats.publishedPosts)")
print("Average user age: \(String(format: "%.1f", stats.averageUserAge))")
print("Average post view count: \(String(format: "%.1f", stats.averagePostViewCount))")
// データエクスポート
let exportedData = try database.exportUserData()
print("\n=== Exported Data (first 200 chars) ===")
print(String(exportedData.prefix(200)) + "...")
} catch {
print("Error: \(error)")
}
}
エラーハンドリングとUtility
// カスタムエラー型
enum DatabaseError: Error, LocalizedError {
case connectionFailed
case userNotFound(Int64)
case postNotFound(Int64)
case invalidData(String)
case transactionFailed(String)
var errorDescription: String? {
switch self {
case .connectionFailed:
return "データベース接続に失敗しました"
case .userNotFound(let id):
return "ユーザーが見つかりません: ID \(id)"
case .postNotFound(let id):
return "投稿が見つかりません: ID \(id)"
case .invalidData(let message):
return "無効なデータ: \(message)"
case .transactionFailed(let message):
return "トランザクション失敗: \(message)"
}
}
}
// UserProfile用のUtility拡張
extension UserProfile {
func toJSONString() throws -> String {
let encoder = JSONEncoder()
let data = try encoder.encode(self)
return String(data: data, encoding: .utf8) ?? ""
}
static func fromJSONString(_ jsonString: String?) throws -> UserProfile? {
guard let jsonString = jsonString,
let data = jsonString.data(using: .utf8) else {
return nil
}
let decoder = JSONDecoder()
return try decoder.decode(UserProfile.self, from: data)
}
func toDictionary() -> [String: Any] {
return [
"bio": bio ?? "",
"website": website ?? "",
"location": location ?? "",
"skills": skills
]
}
}
// マイグレーション機能
extension UserDatabase {
// バージョン管理テーブル
private var migrationTable: Table { Table("migrations") }
private var versionColumn: Expression<Int> { Expression<Int>("version") }
private var appliedAtColumn: Expression<Date> { Expression<Date>("applied_at") }
// マイグレーション実行
func runMigrations() throws {
guard let db = db else { throw DatabaseError.connectionFailed }
// マイグレーションテーブル作成
try db.run(migrationTable.create(ifNotExists: true) { table in
table.column(versionColumn, primaryKey: true)
table.column(appliedAtColumn, defaultValue: Date())
})
let currentVersion = getCurrentMigrationVersion()
// 各マイグレーションを順次実行
for version in (currentVersion + 1)...getLatestMigrationVersion() {
try runMigration(version: version)
try recordMigration(version: version)
print("Applied migration version \(version)")
}
}
private func getCurrentMigrationVersion() -> Int {
do {
guard let db = db else { return 0 }
if let latestMigration = try db.pluck(migrationTable.order(versionColumn.desc)) {
return latestMigration[versionColumn]
}
} catch {
// マイグレーションテーブルが存在しない場合
}
return 0
}
private func getLatestMigrationVersion() -> Int {
return 3 // 現在の最新バージョン
}
private func runMigration(version: Int) throws {
guard let db = db else { throw DatabaseError.connectionFailed }
switch version {
case 1:
// ユーザーテーブルにlast_login_atカラム追加
try db.run("ALTER TABLE users ADD COLUMN last_login_at DATETIME")
case 2:
// 投稿テーブルにcategoryカラム追加
try db.run("ALTER TABLE posts ADD COLUMN category TEXT DEFAULT 'general'")
case 3:
// ユーザーテーブルにavatar_urlカラム追加
try db.run("ALTER TABLE users ADD COLUMN avatar_url TEXT")
default:
throw DatabaseError.invalidData("Unknown migration version: \(version)")
}
}
private func recordMigration(version: Int) throws {
guard let db = db else { throw DatabaseError.connectionFailed }
let insert = migrationTable.insert(
versionColumn <- version,
appliedAtColumn <- Date()
)
try db.run(insert)
}
}
// 非同期対応（iOS 15以降）
@available(iOS 15.0, *)
extension UserDatabase {
func createUserAsync(_ user: User) async throws -> Int64 {
return try await withUnsafeThrowingContinuation { continuation in
do {
let userId = try createUser(user)
continuation.resume(returning: userId)
} catch {
continuation.resume(throwing: error)
}
}
}
func getAllUsersAsync() async throws -> [User] {
return try await withUnsafeThrowingContinuation { continuation in
do {
let users = try getAllUsers()
continuation.resume(returning: users)
} catch {
continuation.resume(throwing: error)
}
}
}
}
// メイン実行例
func main() {
print("=== SQLite.swift Demo ===\n")
demonstrateBasicOperations()
print("\n" + String(repeating: "=", count: 50) + "\n")
demonstratePostOperations()
print("\n" + String(repeating: "=", count: 50) + "\n")
demonstrateAdvancedFeatures()
}
// 実行
main()