Getting Table Data with Loading to Database

A request can e executed synchronously and asynchronously:

To get table data with loading to database by means of synchronous and asynchronous requests, create the HHFWTableStreamRetry application, which uses framework methods:

Method name Brief description
initWithCredentials (_: host: environment: project: device:) The method initializes the framework.
auth (_: password:) The method authenticates the user by login and password.
resources (_: handler:) The method gets schemes of available resources and automatically creates corresponding tables in the database.
setDownloadPath(_:) The method sets the path to the folder in the mobile application directory, which will store resource files in the current downloading state.
useDownload(_:) The method determines whether resource downloading is resumed.

NOTE. The application code contains the tableStream/tableStreamAsync method, for which the TableCallParams auxiliary class is determined with the retryCount and retryIntervalSec properties.
tableStream (_: withCache: transactionID: tableCallParams: handler:) The method executes synchronous request and loads table data to local database.
tableStreamAsync (_: withCache: transactionID: tableCallParams: handler:) The method executes asynchronous request and loads table data to local database.
getTablesName (_: resourceName: requestParams: tableName:) The method returns table name in local database.
query (_: query:) The method sends SQL query to local database.

Auxiliary methods:

Method name Brief description
getURL (forFile:) The method returns URL for the file with the specified name in the Documents folder.
Type of returned data: URL
getURL (forFolder:) The method returns URL for the folder with the specified name in the Documents folder.
Type of returned data: URL
getRecords (from:) The method returns value of the "records" key from JSON result of SQL query to local database.
Type of returned data: [[String: Any]]?
getTableNames (from:) The method returns the array of values for "name" keys from results of the getRecords method.
Type of returned data: [String]
parseServerJSONResponse (json:) The method returns the result of converting JSON string into a dictionary.
Type of returned data: [String: Any]?
getResponseStatus (from:) The method returns value for the "status" key from JSON result of the query.
Type of returned data: String?
getTableNamesInDatabase (_: forResource: originalTableName:) The method returns the array of table names that satisfy the specified parameters: database location in application directory, resource name and searched table name.
Type of returned data: [String]

The HHFWTableStreamRetry application includes one screen, the UITextView text view, and UIButton buttons:

NOTE. When the application is initialized, authentication is executed, the buttons are not available. If the authentication is executed successfully, the buttons are available, and the text box displays the text "Authentication and resources load success".

To execute the example:

  1. Click the Perform TableStream button.

Clicking the button synchronously or asynchronously requests table data for the specified resource. Query execution result is automatically written to local database.

  1. Click the Perform SELECT button.

Clicking the button executes the SQL query SELECT * FROM table_name to local database.

Application code with synchronous request execution:

import UIKit
 
class ViewController: UIViewController {
 
    // Determine output variables
    @IBOutlet weak var resultTextView: UITextView!
    @IBOutlet weak var tableStreamButton: UIButton!
    @IBOutlet weak var queryButton: UIButton!
 
    // Redetermine method
    override func viewDidLoad() {
        super.viewDidLoad()
        self.deactivateButtons()
        self.initializeFramework()
        self.auth {
                let databaseName = "database.sqlite"
                self.setResumingDownload(true)
                self.loadResourcesScheme(databaseName) {
                    self.activateButtons()
                    self.resultTextView.text = "Authentication and load resources success"
                }
            }
        }
    }
 
    // Set method that will be executed on button click
    @IBAction func tableStreamPressed(_ sender: UIButton) {
        let databaseName = "database.sqlite"
        self.loadTable(databaseName) { (jsonResult) in
            self.resultTextView.text = String(format: "%@", jsonResult)
        }
    }
 
    @IBAction func selectFromTablePressed(_ sender: Any?) {
        let databaseName = "database.sqlite"
        self.performSelect(databaseName, completion: { (jsonResult) in
            self.resultTextView.text = jsonResult
        })
    }
 
    // Initialize framework
    private func initializeFramework() {
        let apiVersion: String = "v1"
        let host: String = "http://testmasterfmp.fsight.cloud/"
        let environment: String = "DocumentationExampleEnv"
        let project: String = "DocumentationExampleProj"
        let device: String = (UIDevice.current.identifierForVendor?.uuidString)!
        HHFWController.sharedInstance().initWithCredentials(
            apiVersion,
            host: host,
            environment: environment,
            project: project,
            device: device
        )
    }
 
    // Execute authentication
    private func auth(completion: @escaping ()->()) {
        let username: String = "test"
        let password: String = "test123"
        HHFWController.sharedInstance().auth(username, password: password){ (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Auth success")
                completion()
            } else {
                print("Auth error")
            }
        }
    }
 
    // Load resources scheme
    private func loadResourcesScheme(_ databaseName: String, completion: @escaping ()->()) {
        HHFWController.sharedInstance().resources(databaseName) { (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Load resources success")
                completion()
            } else {
                print("Load resources error")
            }
        }
    }
 
    private func setResumingDownload(_ value: Bool) {
        let path = self.getURL(forFolder: "DownloadPath").path
        HHFWController.sharedInstance().setDownloadPath(path)
        HHFWController.sharedInstance().useDownload(value)
    }
 
    // Execute the TableStream query
    private func loadTable(_ databaseName: String, completion: @escaping (NSDictionary)->()) {
        let resourceName = "test_resource"
        let requestCallParams = RequestCallParams(defaultDb: ())
        let fullDatabaseURL = self.getURL(for: databaseName)
        requestCallParams?.dataBasePath = fullDatabaseURL.path
        requestCallParams?.retryCount = 10
        requestCallParams?.retryIntervalSec = 1
        HHFWController.sharedInstance().tableStream(resourceName, withCache: true, transactionID: nil, tableCallParams: requestCallParams) { (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Load table success")
                completion(jsonDict)
            } else {
                print("Load table error")
            }
        }
    }
 
    // Execute the SELECT * FROM table request
    private func performSelect(_ databaseName: String, completion: (String?) -> ()) {
        let resourceName = "test_resource"
        let tableName = "output_table"
        if let databaseTableName = self.getTableNamesInDatabase(databaseName, forResource: resourceName, originalTableName: tableName).first {
            let query = "SELECT * FROM `\(databaseTableName)`"
            let jsonResult = HHFWController.sharedInstance().query("/Documents/\(databaseName)", query: query)
            completion(jsonResult)
        }
    }
 
    // Get table names in local database
    private func getTableNamesInDatabase(_ databaseName: String, forResource resourceName: String, originalTableName tableName: String) -> [String] {
        let tableNames = HHFWController.sharedInstance().getTablesName("/Documents/\(databaseName)", resourceName: resourceName, requestParams: "", tableName: tableName)
        return self.getTableNames(from: tableNames)
    }
 
    // Set methods for displaying application user interface elements
    private func deactivateButtons() {
        self.tableStreamButton.isEnabled = false
        self.queryButton.isEnabled = false
    }
 
    private func activateButtons() {
        self.tableStreamButton.isEnabled = true
        self.queryButton.isEnabled = true
    }
 
    // Call auxiliary classes
    private func getURL(for fileName: String) -> URL {
        let paths: [URL] = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
        let fileURL: URL = paths[0].appendingPathComponent(fileName)
        return fileURL
    }
 
    private func getURL(forFolder folderName: String) -> URL {
        let paths: [URL] = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
        let folderURL: URL = paths[0].appendingPathComponent(folderName, isDirectory: true)
        return folderURL
    }
 
    private func getRecords(from json: String?) -> [[String: Any]]? {
        var resultRecords: [[String: Any]]? = nil
        if let jsonString = json {
            let jsonDict = parseServerJSONResponse(json: jsonString)
            if self.getResponseStatus(from: jsonDict) == "ok" {
                if let result = jsonDict?["result"] as? [String: Any],
                    let database = result["database"] as? [String:Any],
                    let records = database["records"] as? [[String: Any]] {
                    resultRecords = records
                }
            }
        }
        return resultRecords
    }
 
    private func getTableNames(from json: String?) -> [String] {
        var resultTableNames: [String] = []
        if let records = getRecords(from: json) {
            for record in records {
                if let tableName = record["name"] as? String {
                    resultTableNames.append(tableName)
                }
            }
        }
        return resultTableNames
    }
 
    private func parseServerJSONResponse(json: String) -> [String: Any]? {
        guard let responseData = json.data(using: .utf8) else { return nil }
        return try? JSONSerialization.jsonObject(with: responseData, options: []) as! [String: Any]
    }
 
    func getResponseStatus(from json: [String: Any]?) -> String? {
        return json?["status"] as? String
    }
}

Application code with asynchronous request execution:

import UIKit
 
class ViewController: UIViewController {
 
    // Determine output variables
    @IBOutlet weak var resultTextView: UITextView!
    @IBOutlet weak var tableStreamButton: UIButton!
    @IBOutlet weak var queryButton: UIButton!
 
    // Redetermine method
    override func viewDidLoad() {
        super.viewDidLoad()
        self.deactivateButtons()
        self.initializeFramework()
        self.auth {
                let databaseName = "database.sqlite"
                self.setResumingDownload(true)
                self.loadResourcesScheme(databaseName) {
                    self.activateButtons()
                    self.resultTextView.text = "Authentication and load resources success"
                }
            }
        }
    }
 
    // Set method that will be executed on button click
    @IBAction func tableStreamPressed(_ sender: UIButton) {
        let databaseName = "database.sqlite"
        self.loadTable(databaseName) { (jsonResult) in
            self.resultTextView.text = String(format: "%@", jsonResult)
        }
    }
 
    @IBAction func selectFromTablePressed(_ sender: Any?) {
        let databaseName = "database.sqlite"
        self.performSelect(databaseName, completion: { (jsonResult) in
            self.resultTextView.text = jsonResult
        })
    }
 
    // Initialize framework
    private func initializeFramework() {
        let apiVersion: String = "v1"
        let host: String = "http://testmasterfmp.fsight.cloud/"
        let environment: String = "DocumentationExampleEnv"
        let project: String = "DocumentationExampleProj"
        let device: String = (UIDevice.current.identifierForVendor?.uuidString)!
        HHFWController.sharedInstance().initWithCredentials(
            apiVersion,
            host: host,
            environment: environment,
            project: project,
            device: device
        )
    }
 
    // Execute authentication
    private func auth(completion: @escaping ()->()) {
        let username: String = "test"
        let password: String = "test123"
        HHFWController.sharedInstance().auth(username, password: password){ (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Auth success")
                completion()
            } else {
                print("Auth error")
            }
        }
    }
 
    // Load resources scheme
    private func loadResourcesScheme(_ databaseName: String, completion: @escaping ()->()) {
        HHFWController.sharedInstance().resources(databaseName) { (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Load resources success")
                completion()
            } else {
                print("Load resources error")
            }
        }
    }
 
    private func setResumingDownload(_ value: Bool) {
        let path = self.getURL(forFolder: "DownloadPath").path
        HHFWController.sharedInstance().setDownloadPath(path)
        HHFWController.sharedInstance().useDownload(value)
    }
 
    // Execute the TableStreamAsync request
    private func loadTable(_ databaseName: String, completion: @escaping (NSDictionary)->()) {
        let resourceName = "test_resource"
        let requestCallParams = RequestCallParams(defaultDb: ())
        let fullDatabaseURL = self.getURL(for: databaseName)
        requestCallParams?.dataBasePath = fullDatabaseURL.path
        requestCallParams?.retryCount = 10
        requestCallParams?.retryIntervalSec = 1
        HHFWController.sharedInstance().tableStreamAsync(resourceName, withCache: true, transactionID: nil, tableCallParams: requestCallParams) { (jsonResult) in
            if let jsonDict = jsonResult as? NSDictionary,
                let status = jsonDict["status"] as? String, status == "ok" {
                print("Load table success")
                completion(jsonDict)
            } else {
                print("Load table error")
            }
        }
    }
 
    // Execute the SELECT * FROM table request
    private func performSelect(_ databaseName: String, completion: (String?) -> ()) {
        let resourceName = "test_resource"
        let tableName = "output_table"
        if let databaseTableName = self.getTableNamesInDatabase(databaseName, forResource: resourceName, originalTableName: tableName).first {
            let query = "SELECT * FROM `\(databaseTableName)`"
            let jsonResult = HHFWController.sharedInstance().query("/Documents/\(databaseName)", query: query)
            completion(jsonResult)
        }
    }
 
    // Get table names in local database
    private func getTableNamesInDatabase(_ databaseName: String, forResource resourceName: String, originalTableName tableName: String) -> [String] {
        let tableNames = HHFWController.sharedInstance().getTablesName("/Documents/\(databaseName)", resourceName: resourceName, requestParams: "", tableName: tableName)
        return self.getTableNames(from: tableNames)
    }
 
    // Set methods for displaying application user interface elements
    private func deactivateButtons() {
        self.tableStreamButton.isEnabled = false
        self.queryButton.isEnabled = false
    }
 
    private func activateButtons() {
        self.tableStreamButton.isEnabled = true
        self.queryButton.isEnabled = true
    }
 
    // Call auxiliary classes
    private func getURL(for fileName: String) -> URL {
        let paths: [URL] = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
        let fileURL: URL = paths[0].appendingPathComponent(fileName)
        return fileURL
    }
 
    private func getURL(forFolder folderName: String) -> URL {
        let paths: [URL] = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
        let folderURL: URL = paths[0].appendingPathComponent(folderName, isDirectory: true)
        return folderURL
    }
 
    private func getRecords(from json: String?) -> [[String: Any]]? {
        var resultRecords: [[String: Any]]? = nil
        if let jsonString = json {
            let jsonDict = parseServerJSONResponse(json: jsonString)
            if self.getResponseStatus(from: jsonDict) == "ok" {
                if let result = jsonDict?["result"] as? [String: Any],
                    let database = result["database"] as? [String:Any],
                    let records = database["records"] as? [[String: Any]] {
                    resultRecords = records
                }
            }
        }
        return resultRecords
    }
 
    private func getTableNames(from json: String?) -> [String] {
        var resultTableNames: [String] = []
        if let records = getRecords(from: json) {
            for record in records {
                if let tableName = record["name"] as? String {
                    resultTableNames.append(tableName)
                }
            }
        }
        return resultTableNames
    }
 
    private func parseServerJSONResponse(json: String) -> [String: Any]? {
        guard let responseData = json.data(using: .utf8) else { return nil }
        return try? JSONSerialization.jsonObject(with: responseData, options: []) as! [String: Any]
    }
 
    func getResponseStatus(from json: [String: Any]?) -> String? {
        return json?["status"] as? String
    }
}

See also:

Examples of iOS Framework Use | Examples of Working with Resources