Introduction
The Booxter for Mac application allows you to collect information about books in your home library. This post describes the creation of a simple ExtractBooks utility that is extracting some data about books stored in the SQLite database used by the Booxter and loads this data into the MongoDB database. The purpose of this little project is to build expertise in creating a server-side logic using Swift programming language, and this is the first step. It should help later in creating generation templates used by the Data Model Widgets to generate source code in Swift for the different databases like MYSQL, SQLite or PostgreSQL.
Exporting data from Booxter
Booxter helps you track your collections of books, music, movies, and comics on your Mac. You will find more about Booxter here. You can download a trial version and play with it if you like.
Here is a snapshot of how the Booxter main screen looks.
The Booxter creates a library at the location of your choice.
You can use Show Package Contents to explore inner structure of the library.
You will find that data.sql is local SQLite database keeping data about books, music, and videos. You will notice that images subdirectory contains some image files. Those are small snapshots of the book’s covers.
You can open the data.sql file using DB Browser for SQLite or similar product. Here is a list showing the structure of the database used by the application.
We are going to use only a fraction of the data describing books. Here is a sample of the ZBXITEM table keeping most of the details about each book.
Two other tables: ZBXAUTHOR and ZBXIMAGE have additional details we are going to export from the database. The ZBXIMAGE table does not keep images itself, but rather a name of the file stored in the images subdirectory.
It makes sense to mention that path to the data.sql file looks something like:
file:///Users/User/Desktop/BooxterTest.bxbooklib/data.sql
Creating development environment
The first steps will create a development environment for the project. It will allow assembling of all products required to develop and build utility successfully. Installing SQLite and MongoDB is out of the scope of this post.
Step 1. Create a folder for the project
$ mkdir ExtractBooks $ cd ExtractBooks $ swift package init --type executable
Step 2. Create package file inside a new directory
You need to create Package.swift file in the newly created directory.
import PackageDescription
let package = Package(
name: "ExtractBooks",
dependencies: [
.package(url: "https://github.com/IBM-Swift/Swift-Kuery-SQLite.git", from: "1.1.0"),
.package(url: "https://github.com/OpenKitten/MongoKitten.git", from: "4.0.0")
],
targets: [
.target(
name: "ExtractBooks",
dependencies: ["MongoKitten", "SwiftKuerySQLite"]),
]
)
You can notice that our project depends on two other packages: Swift-Kuery-SQLite and MongoKitten. We can create Xcode project now. Rest of the development will be done using Xcode development tool for Mac running macOS.
$ swift package generate-xcodeproj
Structure of the utility
This utility is activated from the command line. It takes some parameters describing the location of the SQLite and MongoDB databases. Here are sample parameters.
-u "file:///Users/User/Desktop/BooxterTest.bxbooklib/data.sql" "mongodb://localhost/library" "books"
Here is the content of the main.swift file’.
//
// main.swift
//
// Created by Marek Stankiewicz on 16/06/2018.
//
import Foundation
let mainLogic = MainLogic()
if CommandLine.argc < 1 {
mainLogic.consoleDialog.printUsage()
} else {
let returnStatus = mainLogic.startProcessing()
if returnStatus.rc == 0 {
mainLogic.consoleDialog.writeMessage(returnStatus.message, to: OutputType.standard)
} else {
mainLogic.consoleDialog.writeMessage(returnStatus.message, to: OutputType.error)
}
exit(Int32(returnStatus.rc))
}
Here is the MainLogic.swift file with the functionality responsible for processing the input parameters.
//
// MainLogic.swift
//
// Created by Marek Stankiewicz on 16/06/2018.
//
import Foundation
import CoreData
enum OptionType: String {
case upload = "-u"
case help = "-h"
case unknown
init(value: String) {
switch value {
case "-u": self = .upload
case "-h": self = .help
default: self = .unknown
}
}
}
enum ProcessResponse {
case successfull
case noSQLiteConnect(String)
case noMongoDBConnect(String)
case problemCreatingMongoDBDoument(String)
case problemProcessingMongoDB(String)
case problemOpeningImageFile(String)
}
class MainLogic {
let consoleDialog = ConsoleDialog()
func startProcessing() -> (rc: Int, message: String) {
let arguments = CommandLine.argc
if arguments < 1 {
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "No option parameters provided.")
}
let option = getOption(CommandLine.arguments[1])
switch option {
case .upload:
let params = unpackCmdParams(array : CommandLine.arguments)
if params == nil {
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "Wrong number of parameters for -u function.")
}
if !validateURL(url: (params?.sqLite)!) {
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "URL to SQLite database malformed.")
}
if !validateURL(url: (params?.mongodburl)!) {
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "URL to MongoDB database malformed.")
}
let uploadAction = UploadAction(filename: (params?.sqLite)!)
let processResponse = uploadAction.upload(fromSQLite: (params?.sqLite)!, toMongoDB: (params?.mongodburl)!, collectionName: (params?.collectionName)!)
return createRetrunMessage(processResponse: processResponse)
case .help:
consoleDialog.printUsage()
case .unknown:
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "Unknown option.")
}
consoleDialog.printUsage()
return (rc: ReturnCode.command_line_usage_error.rawValue, message: "Unknown option.")
}
func validateURL(url : String) -> Bool {
let nsurl = NSURL(string: url)
return nsurl == nil ? false : true
}
func unpackCmdParams(array : [String]) -> (sqLite : String, mongodburl : String, collectionName : String)? {
if array.count != 5 {
return nil
}
return (array[2], array[3], array[4])
}
func getOption(_ option: String) -> OptionType {
return OptionType(value: option)
}
func createRetrunMessage(processResponse: ProcessResponse) -> (rc: Int, message: String) {
switch processResponse {
case .successfull:
return (0,"Processing completed successfully")
default:
return (1, self.getDescription(processResponse: processResponse))
}
}
func getDescription(processResponse: ProcessResponse) -> String {
switch processResponse {
case .successfull:
return "successful"
case let .noSQLiteConnect(message):
return message
case let .noMongoDBConnect(message):
return message
case let .problemOpeningImageFile(message):
return message
case let .problemCreatingMongoDBDoument(message):
return message
case let .problemProcessingMongoDB(message):
return message
}
}
}
The process of extracting from SQLite and loading to the MongoDB is in the UploadAction.swift file.
//
// UploadAction.swift
// BooksUpload
//
// Created by Marek Stankiewicz on 25/06/2018.
//
import Foundation
import SwiftKuery
import SwiftKuerySQLite
import MongoKitten
class ZBXITEM: Table {
let tableName = "ZBXITEM"
let zpkitem = Column("Z_PK", Int32.self)
let zisbn = Column("ZISBN")
let ztitle = Column("ZTITLE")
let zlocation = Column("ZLOCATION")
let zproductdescription = Column("ZPRODUCTDESCRIPTION")
let zpublisher = Column("ZPUBLISHER")
let zpages = Column("ZPAGES", Int32.self)
let zlanguage = Column("ZLANGUAGE")
let zdatepublished = Column("ZDATEPUBLISHED", Timestamp.self, notNull: false)
}
class ZBXIMAGE: Table {
let tableName = "ZBXIMAGE"
let zpkimage = Column("Z_PK")
let zfilename = Column("ZFILENAME")
}
class ZBXAUTHOR: Table {
let tableName = "ZBXAUTHOR"
let zpkauthor = Column("Z_PK", Int32.self)
let zname = Column("ZNAME")
}
class UploadAction {
var processResponse : ProcessResponse = ProcessResponse.successfull
var connection : SQLiteConnection
init(filename: String){
self.processResponse = ProcessResponse.successfull
self.connection = SQLiteConnection(filename: filename)
}
func upload(fromSQLite : String, toMongoDB: String, collectionName: String) -> ProcessResponse {
connection.connect() { error in
if error == nil {
let url = URL(string: fromSQLite)
let imagedir = url?.deletingLastPathComponent().absoluteString
process(toMongoDB: toMongoDB,collectionName: collectionName, imagedir: imagedir! + "images/")
}
else if let error = error {
processResponse = ProcessResponse.noSQLiteConnect("Error opening database: \(error.description)")
}
}
return processResponse
}
func extractTimestamp(number: Any) -> String {
let formatter = DateFormatter()
formatter.locale = Locale(identifier: "en_US_POSIX")
formatter.dateFormat = "yyyy-MM-dd'.'HH.mm.ss.00000"
formatter.timeZone = TimeZone(secondsFromGMT: 0)
if number is Int32 {
return formatter.string(from: Date(timeIntervalSince1970: Double(number as! Int32)))
} else if number is Double {
return formatter.string(from: Date(timeIntervalSince1970: number as! Double))
}
return ""
}
func process(toMongoDB: String, collectionName: String, imagedir: String) {
do {
let library = try MongoKitten.Database(toMongoDB)
let books = library[collectionName]
let zbxitems = ZBXITEM()
let query = Select(zbxitems.zpkitem,
zbxitems.zisbn,
zbxitems.ztitle,
zbxitems.zlocation,
zbxitems.zproductdescription,
zbxitems.zpublisher,
zbxitems.zpages,
zbxitems.zlanguage,
zbxitems.zdatepublished, from: zbxitems)
connection.execute(query: query) { queryResult in
if let rows = queryResult.asRows {
for row in rows {
var book: Document = Document()
// Primary key
let zpkitem = row["Z_PK"].unsafelyUnwrapped as! Int32
book.append(zpkitem, forKey: "_id")
// ISBN Number
let zisbn = row["ZISBN"]
if zisbn.unsafelyUnwrapped != nil {
book.append(zisbn! as! Primitive, forKey: "isbn")
}
// Title
let ztitle = row["ZTITLE"]
if ztitle.unsafelyUnwrapped != nil {
book.append(ztitle! as! Primitive, forKey: "title")
}
// Location
let zlocation = row["ZLOCATION"]
if zlocation.unsafelyUnwrapped != nil {
// book.append(zlocation! as! Primitive, forKey: "location")
}
// Product Description
let zproductdescription = row["ZPRODUCTDESCRIPTION"]
if zproductdescription.unsafelyUnwrapped != nil {
book.append(zproductdescription! as! Primitive, forKey: "description")
}
// Publisher
let zpublisher = row["ZPUBLISHER"]
if zpublisher.unsafelyUnwrapped != nil {
book.append(zpublisher! as! Primitive, forKey: "publisher")
}
// Pages
let zpages = row["ZPAGES"]
if zpages.unsafelyUnwrapped != nil {
book.append(zpages as! Primitive, forKey: "pages")
}
// Language
let zlanguage = row["ZLANGUAGE"]
if zlanguage.unsafelyUnwrapped != nil {
book.append(zlanguage! as! Primitive, forKey: "language")
}
// Date Published
let zdatepublished = row["ZDATEPUBLISHED"]
if zdatepublished.unsafelyUnwrapped != nil {
book.append(self.extractTimestamp(number: zdatepublished.unsafelyUnwrapped ?? "unknown"), forKey: "datepublished")
}
var authors = [String]()
let zbxauthor = ZBXAUTHOR()
let queryAuthor = Select(zbxauthor.zpkauthor,
zbxauthor.zname, from: zbxauthor).where(zbxauthor.zpkauthor == Parameter())
self.connection.execute(query: queryAuthor, parameters: [Int(zpkitem)]) { queryResult in
if let rows = queryResult.asRows {
for row in rows {
// Author Name
let zname = row["ZNAME"]
if zname.unsafelyUnwrapped != nil {
authors.append(zname as! String)
}
}
}
}
book.append(authors, forKey: "authors")
let zbximage = ZBXIMAGE()
let queryImage = Select(zbximage.zpkimage,
zbximage.zfilename, from: zbximage).where(zbximage.zpkimage == Parameter())
self.connection.execute(query: queryImage, parameters: [Int(zpkitem)]) { queryResult in
if let rows = queryResult.asRows {
for row in rows {
// Author Name
let zfilename = row["ZFILENAME"]
if zfilename.unsafelyUnwrapped != nil {
let path = imagedir + (zfilename.unsafelyUnwrapped as! String)
// File Name
let url = URL(string: path)
do {
let file: FileHandle? = try FileHandle(forReadingFrom: url!)
if file != nil {
let data = file?.readDataToEndOfFile()
file?.closeFile()
book.append(Binary(data: data!, withSubtype: Binary.Subtype.generic), forKey: "image")
}
} catch {
self.processResponse = ProcessResponse.problemOpeningImageFile("Problem opening image file: \(String(describing: url))")
}
}
}
}
}
do {
try books.append(book)
} catch {
self.processResponse = ProcessResponse.problemCreatingMongoDBDoument("Problem appending a new document for item: \(Int(zpkitem))")
}
}
}
}
} catch {
self.processResponse = ProcessResponse.problemProcessingMongoDB("Problem processing MongoDB")
}
return
}
}
The entire project is in the GitHub here.
Sample data from the database created using the MongoDB follows:
Summary
This is a just the first example of using Swift. More will follow. Next step will be a server logic written using Kitura framework.