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.