Tuesday, September 29, 2015

Create a Data Access Layer with SQLite.swift and Swift 2

In an earlier post I showed how to crate a data access layer with SQLite.swift and Swift 1.2.  In this post I will be updating the previous post to use Swift 2 and SQLite.swift for Swift 2.  In both of these posts I describe the data access layer.  In my new book Protocol-Oriented programming I take this example a step further by not only showing the data access layer but also showing how to integrate it with your application using the bridge design pattern.

SQLite is an open source, lightweight and cross platform relational database however it does require good knowledge of SQL to use.  For me that is not much of a problem however it is always better if we can avoid embedding SQL statements in our source code.  This is where frameworks like SQLite.swift come in.  SQLite.swift is a type-safe, Swift language layer over SQLite3 that allows us to access an SQLite database in a pure Swift type interface. 
It is important, when designing an application, to design a good data access layer between our application and its backend data storage.  While some may argue that is what Core Data is for, I am just not a big fan of it especially if we are planning on porting our applications to other platforms.  In this blog post, I will show how we can use the SQLite.swift framework to design a good data access layer for our applications written in Swift 2.
We can download sqlite.swift for Swift 2 from it’s github repository.  There are good instructions on how to install it either though CocoaPods or manually in our applications.  Once we have sqlite.swift setup within our application we will be ready to design our data access layer.
In this blog post, we will mainly be discussing how to design the data access layer.  We will back up our design with a working example but the focus of the post will be on the design and the code is there to reinforce that design.  You can find the code for this post in this github repository.

Data Access Layer Introduction

Our data access layer will consist of three layers.  The bottom most, connection, layer will consist of one class named SQLiteDataStore which will contain the connection handle for our SQLite database.  This class will implement the singleton pattern so all access to the database will go through one connection handle.
The next layer will be a data helper layer that will contain one class for each of our tables.  These data helper classes will contain methods to create, insert, delete and query a specific table.  We will want to create a protocol for the data helper classes to ensure that they contain a minimum set of functionality like creating the table.
The final layer is the model layer.  This layer will contain tuples that model our database tables and will be used to write or retrieve data from the data store.
In our example, we will have two tables.  These are the Teams and the Players table.  From our description above, we can probably guess that this means we will need two data helper classes (TeamDataHelper and PlayerDataHelper) and two tuples (Team and Player).  The data access layer design will look like this:




Connection Layer

Lets begin by defining our errors that we can receive from our data access layer.  We will be using Swift 2’s new error handling feature so our errors are defined in an enum like this:
enum DataAccessError: ErrorType {
    case Datastore_Connection_Error
    case Insert_Error
    case Delete_Error
    case Search_Error
    case Nil_In_Data
}
We will see where we use these errors as we go though our code.
Now lets look at the code.  Next we will look at the SQLiteDataStore class that will contain the connection handle for our database.  The SQLiteDataStore class will contain the following code:

import Foundation
import SQLite

class SQLiteDataStore {
    static let sharedInstance = SQLiteDataStore()
    let BBDB: Connection?
   
    private init() {
       
        var path = "BaseballDB.sqlite"
       
        if let dirs: [NSString] =          NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory,
            NSSearchPathDomainMask.AllDomainsMask, true) as [NSString] {
               
             let dir = dirs[0]
             path = dir.stringByAppendingPathComponent("BaseballDB.sqlite");
        }
       
        do {
            BBDB = try Connection(path)
        } catch _ {
            BBDB = nil
        }
    }
   
    func createTables() throws{
        do {
            try TeamDataHelper.createTable()
            try PlayerDataHelper.createTable()
        } catch {
            throw DataAccessError.Datastore_Connection_Error
        }
    }
}


The SQLiteDataStore class implements the singleton pattern so there is only one instance of the class for the lifecycle of our application.  We implement this pattern by creating a private initializer which is accessed from a static constant named sharedInstance.  Inside the initializer, we set the path to the file that will contain our database and then create the BBDB Database connection with that path.
The SQLiteDataStore class contains a second method named createTables.  The createTables method calls the createTable methods from our data helper classes.  This method will let us create all of the tables from a single method.

Model Layer

Now lets look at the model layer.  The first thing we need to do in the model layer is to create an enumeration that will contain the position that the players could play.  This Positions enum looks like this.

enum Positions: String {
    case Pitcher = "Pitcher"
    case Catcher = "Catcher"
    case FirstBase = "First Base"
    case SecondBase = "Second Base"
    case ThirdBase = "Third Base"
    case Shortstop = "Shortstop"
    case LeftField = "Left Field"
    case CenterField = "Center Field"
    case RightField = "Right field"
    case DesignatedHitter = "Designated Hitter"
}
Next we need to define the types that we use to model our data.  These types will be used to transfer the data from our data access layer to the rest of our code.  Since these types should be used exclusively to transfer the data, using value types are definitely preferred.  The reason why we should avoid using these types in our business logic layer is they may change if we change the table structure of our database and we do not want these changes to effect the code in our business logic layer.  With this in mind we will use tuples to model our data as shown in the following code:

typealias Team = (
    teamId: Int64?,
    city: String?,
    nickName: String?,
    abbreviation: String?
)

typealias Player = (
    playerId: Int64?,
    firstName: String?,
    lastName: String?,
    number: Int?,
    teamId: Int64?,
    position: Positions?
)

Keep in mind that we should avoid using these types in our business logic layer so we have the ability to change them without having to change our business logic code.  We should have an adapter that will convert the data from these types into data structures used by the business logic layer.

Data Helper Layer

Now lets look at the data helper layer.  This layer will be the bridge to our connection layer and will contain one data helper class per table in our database.  We will begin by creating a data helper protocol that will define the set of methods that each data helper class needs to implement.  The DataHelperProtocol protocol looks like this:

protocol DataHelperProtocol {
    typealias T
    static func createTable() throws -> Void
    static func insert(item: T) throws -> Int64
    static func delete(item: T) throws -> Void
    static func findAll() throws -> [T]?
}

Within this protocol the four methods that we are defining are:    
createTable:  Creates the table
insert:  insets a row into the table
delete:  deletes a row from the table
findAll:  returns all rows in the table

Notice that we only define one method to query our data stores.  We do this because the methods to query each individual table could be different therefore the method(s) needed to query the tables could be different.  We would need to evaluate the query methods needed for each table on a table-by-table basis.
Now lets look at the TeamDataHelper class that will confirm to the DataHelperProtocol.  This class will be used to read and write information to the Teams table of the SQLite data store.

import Foundation
import SQLite

class TeamDataHelper: DataHelperProtocol {
    static let TABLE_NAME = "Teams"
   
    static let table = Table(TABLE_NAME)
    static let teamId = Expression<Int64>("teamid")
    static let city = Expression<String>("city")
    static let nickName = Expression<String>("nickname")
    static let abbreviation = Expression<String>("abbreviation")
   
   
    typealias T = Team
   
    static func createTable() throws {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        do {
            let _ = try DB.run( table.create(ifNotExists: true) {t in
                t.column(teamId, primaryKey: true)
                t.column(city)
                t.column(nickName)
                t.column(abbreviation)
                })
           
        } catch _ {
            // Error throw if table already exists
        }
       
    }
   
    static func insert(item: T) throws -> Int64 {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        if (item.city != nil && item.nickName != nil && item.abbreviation != nil) {
            let insert = table.insert(city <- item.city!, nickName <- item.nickName!, abbreviation <- item.abbreviation!)
            do {
                let rowId = try DB.run(insert)
                guard rowId > 0 else {
                    throw DataAccessError.Insert_Error
                }
                return rowId
            } catch _ {
                throw DataAccessError.Insert_Error
            }
        }
        throw DataAccessError.Nil_In_Data
       
    }
   
    static func delete (item: T) throws -> Void {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        if let id = item.teamId {
            let query = table.filter(teamId == id)
            do {
                let tmp = try DB.run(query.delete())
                guard tmp == 1 else {
                    throw DataAccessError.Delete_Error
                }
            } catch _ {
                throw DataAccessError.Delete_Error
            }
        }
    }
   
    static func find(id: Int64) throws -> T? {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        let query = table.filter(teamId == id)
        let items = DB.prepare(query)
        for item in  items {
            return Team(teamId: item[teamId] , city: item[city], nickName: item[nickName], abbreviation: item[abbreviation])
        }
       
        return nil
       
    }
   
    static func findAll() throws -> [T]? {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        var retArray = [T]()
        let items = DB.prepare(table)
        for item in items {
            retArray.append(Team(teamId: item[teamId], city: item[city], nickName: item[nickName], abbreviation: item[abbreviation]))
        }
       
        return retArray
       
    }
}

We start the TeamDataHelper class by setting the TABLE_NAME property.  This property defines the name for the table within our database.  Next we define the table property which is the SQLite table that contains our data.  We use the table property to access the Teams table with in our database.  The next four properties (teamId, city, nickname and abbreviation) define the name and type of each row in the table. 
Finally we implement each of the four methods defined in the DataHelperProtocol protocol plus one extra method which will search the table by its unique identifier.
The PlayerDataHelper class is similar to the TeamDataHelper class except it is used to read and write to the Players table of the SQLite data store.  The PlayerDataHelper class looks like this:

import Foundation
import SQLite

class PlayerDataHelper: DataHelperProtocol {
    static let TABLE_NAME = "Players"
   
    static let playerId = Expression<Int64>("playerid")
    static let firstName = Expression<String>("firstName")
    static let lastName = Expression<String>("lastName")
    static let number = Expression<Int>("number")
    static let teamId = Expression<Int64>("teamid")
    static let position = Expression<String>("position")
   
   
    static let table = Table(TABLE_NAME)
   
    typealias T = Player
   
    static func createTable() throws {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        do {
            _ = try DB.run( table.create(ifNotExists: true) {t in
               
                t.column(playerId, primaryKey: true)
                t.column(firstName)
                t.column(lastName)
                t.column(number)
                t.column(teamId)
                t.column(position)
               
                })
        } catch _ {
            // Error thrown when table exists
        }
    }
   
    static func insert(item: T) throws -> Int64 {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        if (item.firstName != nil && item.lastName != nil && item.teamId != nil && item.position != nil) {
            let insert = table.insert(firstName <- item.firstName!, number <- item.number!, lastName <- item.lastName!, teamId <- item.teamId!, position <- item.position!.rawValue)
            do {
                let rowId = try DB.run(insert)
                guard rowId >= 0 else {
                    throw DataAccessError.Insert_Error
                }
                return rowId
            } catch _ {
                throw DataAccessError.Insert_Error
            }
        }
        throw DataAccessError.Nil_In_Data
    }
   
    static func delete (item: T) throws -> Void {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        if let id = item.playerId {
            let query = table.filter(playerId == id)
            do {
                let tmp = try DB.run(query.delete())
                guard tmp == 1 else {
                    throw DataAccessError.Delete_Error
                }
            } catch _ {
                throw DataAccessError.Delete_Error
            }
        }
       
    }
   
    static func find(id: Int64) throws -> T? {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        let query = table.filter(playerId == id)
        let items = DB.prepare(query)
        for item in  items {
            return Player(playerId: item[playerId], firstName: item[firstName], lastName: item[lastName], number: item[number], teamId: item[teamId], position: Positions(rawValue: item[position]))
        }
       
        return nil
       
    }
   
    static func findAll() throws -> [T]? {
        guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
            throw DataAccessError.Datastore_Connection_Error
        }
        var retArray = [T]()
        let items = DB.prepare(table)
        for item in items {
            retArray.append(Player(playerId: item[playerId], firstName: item[firstName], lastName: item[lastName], number: item[number], teamId: item[teamId], position: Positions(rawValue: item[position])))
        }
       
        return retArray
    }
}

Now lets look at how we would use this data access layer to read and write information from our data store. 

Using the Data Access Layer

To insert a row into a table, all we need to do is to create an instance of the appropriate model type (Player or Team) and then pass it to the appropriate data helper class (PlayerDataHelper or TeamDataHelper).  As an example, here is how we would create four rows in the Teams table:

        do {
       let bosId = try TeamDataHelper.insert(
           Team(
               teamId: 0,
               city: "Boston",
               nickName: "Red Sox",
              abbreviation: "BOS"))
       print(bosId)
   } catch _{}
     
       
   do {
       let torId = try TeamDataHelper.insert(
           Team(
               teamId: 0,
               city: "Toronto",
               nickName: "Blue Jays",
               abbreviation: "TOR"))
       print(torId)
   } catch _ {}

Here is how we would create three rows in the Players table:

let ortizId = try PlayerDataHelper.insert(
    Player(
        playerId: 0,
        firstName: "David",
        lastName: "Ortiz",
        number: 34,
        teamId: bosId,
        position: Positions.DesignatedHitter
 ))
 print(ortizId)
      
 let bogeyId = try PlayerDataHelper.insert(
     Player(
         playerId: 0,
         firstName: "Xander",
         lastName: "Bogarts",
         number: 2,
         teamId: bosId,
         position: Positions.Shortstop
  ))
  print(bogeyId)

To query the database we would call the appropriate method with the data helper class.  The following code would return a list of all teams in the Teams table and prints out the city and nickname for each team.

do {
   if let teams = try TeamDataHelper.findAll() {
      for team in teams {
          print("\(team.city!) \(team.nickName!)")
       }
    }
} catch _ {}

Conclusion
In this blog post we showed how we could use sqlite.swift and good development practices to create a data access layer that simplifies access to the data and hides the complexity of the underlying data store.  The example shown gives us a good abstraction layer that separates the backend data store from our main application. 
The one thing that our example is missing is a way to query the tables by different elements of the table.  For example, we do not have a way to query all players by team or position.  There are a number of ways that we could implement these queries depending on the complexity needed.  We could create a method for each type of query needed if there are not too many or we could create a single method that will query by each element set in the model object.  How you implement these queries is up to you and really depends on the complexity needed in your application.






41 comments:

  1. Hi Jon,

    I have a generic question not related to this blog post but I don't know where to put it so I am asking it here.

    I purchased your book, mastering swift, and wanted to know if there's any plans on updating the source code to version 2 of swift

    ReplyDelete
  2. We are actually updating the whole book and adding two additional chapters (Protocol Extensions and Error Handling). Here is a link: https://www.packtpub.com/application-development/mastering-swift-2

    ReplyDelete
  3. shouldn't there be a discount if I already purchased Mastering Swift V1

    ReplyDelete
    Replies
    1. I know you get a discount if you purchase different formats but to be honest I have no idea about updates to book. I would post that question on Packt's site. Is there a particular example that you are looking for but I would be more than happy to post some updates as I can but I have also started another book on Swift so my time is really tight right now.

      Delete
  4. Thanks man. it is great tutorial !

    ReplyDelete
  5. What if I wanted to create a foreign key constraint in one of my tables, I can't seem to get around errors with the reference. Errors i get are "Cannot invoke column with an argument list of type (Expression, references: Tablename.Type)" or "Cannot invoke column with an argument list of type (Expression, references: Expression)"

    ReplyDelete
    Replies
    1. You would have to post the code for me to see what you are doing.

      Delete
    2. In the create table function under PlayerDataHelper class, if I change the teamId column (line 146) to

      "t.column(teamId, references: Team, teamId)"

      I get an error "cannot invoke 'column' with an argument list of type '(Expression, references: Team.Type, Expression)'". I don't understand this error.
      After further thought I guess a better question would be is it necessary to enforce foreign keys as long as that column is being assigned the correct teamId?

      Delete
  6. i have had a similar trouble while setting up. usually that was when the data helper layer didn't have the same type-definition as the actual data that you want to use. i find it easiest to put most data into the database as string and just use swifts inbuilt functions for transformation. However, i have a problem when i want to filter and access multiple rows out of a database. i can load one row just fine, but when i try to create a filter and return an array the whole thing fails. Could you supply in you post an example of code to access multiple lines. I built something like this in the data helper

    static func findCustomer(vName:String, vVorname:String) throws -> (retArray: [T]! , count: Int){
    guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
    throw DataAccessError.Datastore_Connection_Error
    }
    var retArray = [T]()
    let query = table.filter(name.lowercaseString == vName && firstname.lowercaseString == vVorname)
    let items = try DB.prepare(query)
    for item in items {
    retArray.append(Kunde(kundeID: item[kundeID], name: item[name], firstname: item[firstname], street: item[street], street2: item[street2], postcode: item[postcode], city: item[city], phone1: item[phone1], phone2: item[phone2], email1: item[email1], email2: item[email2], skype: item[skype], timestamp: item[timestamp]))
    }

    let count = DB.scalar(table.select(name.count))
    return (retArray, count)
    }

    This code complies but does not return any data. i would guess it is because i am doing something wrong with the search string, but i can't find out what it is.

    also i'm trying to do this in an uiviewcontroller to load the data into a form.

    @IBAction func btnLoad(sender: UIButton) {
    if fldName.text == "" || fldVorname.text == "" {
    let Alert = UIAlertController(title: "Suche nicht möglich", message: "Bitte geben sie Nachname und Vorname für die Suche ein.", preferredStyle: UIAlertControllerStyle.Alert)
    Alert.addAction(UIAlertAction(title: "OK", style: .Default, handler: nil))
    self.presentViewController(Alert, animated: true, completion: nil)
    return
    }
    do {
    let vAnzahl = try KundeDataHelper.findCustomer(fldName.text!, vVorname: fldVorname.text!).count
    if vAnzahl == 0 {
    //no clients found


    } else if vAnzahl == 1 {
    // one client found


    } else {
    // multiple clients found
    let kArray = try KundeDataHelper.findCustomer(fldName.text!, vVorname: fldVorname.text!).retArray

    for Kunde in kArray{
    // showing a form or a picker where the user will see Name, Firstname, Place and he can choose which client he finally wants to load. probably with a subviewcontroller or something like that.
    print(Kunde)
    }
    }
    } catch {}
    }

    I would be really grateful if you could help me with that code.

    ReplyDelete
    Replies
    1. I would double check your data. I added the following method to my TeamDataHelper type and it correctly returned multiple rows when I added the same team multiple times:

      static func find(citySearch: String, nameSearch: String) throws -> [T]? {
      guard let DB = SQLiteDataStore.sharedInstance.BBDB else {
      throw DataAccessError.Datastore_Connection_Error
      }
      var retArray = [T]()
      let query = table.filter(city == citySearch && nickName == nameSearch)
      let items = try DB.prepare(query)
      for item in items {
      retArray.append(Team(teamId: item[teamId], city: item[city], nickName: item[nickName], abbreviation: item[abbreviation]))
      }
      try TeamDataHelper.findAll()
      return retArray

      }

      Delete
  7. Hi, very good tutorial. Thank You!
    I'm also having a trouble to assign a foreign key.
    I want to set Player.teamId as a foreign key of Team.teamid.
    There is an example https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#table-constraints
    but somehow I can't apply it to your tutorial. Could you please make an example how to do that?
    Thank you.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  9. You refer to the business logic layer. Is that the model layer, data helper layer, connection layer, or something else?

    ReplyDelete
    Replies
    1. The business logic layer is something else. It is the layer that you display and/or interact with the data. Within your data access layer your types tend to model how the data is stored and over time this changes especially if you are normalizing your data properly. As the types in your data access layer change to account for the changes to the data store you will want to minimize the changes needed to the business logic therefore you do not want to tightly couple business logic layer with your data access layer. The Bridge design pattern can help separate the two layers so the types in the data access layer can change independently of the business logic layer.

      Delete
  10. Thank you for your last reply about the business logic layer. That made sense.

    I notice in the various layers that nothing is done in a background thread. Does the SQLite.swift framework take care of that itself or is that something I should add to the business logic layer?

    ReplyDelete
    Replies
    1. SQLite does not take care of the threading for you, it is something that you need to do for yourself. I do not know your application or your application architecture but in general I would recommend putting the threading at the data access layer. You could then possibly use the delegation pattern to return results back. This may or may not work for you depending on your application. Think about how things like the UITableViewDelegate work.

      Delete
    2. Let me see if I have this right. In the general situation, I would wrap the potentially time consuming database operations in a dispatch_async background task. This would be done for each of the methods in the DataHelper class layer.

      Example: http://stackoverflow.com/a/25070476

      When the background database task finishes, I would call a delegate method (that I create myself) to update the UI or whatever else I need to do.

      Is this correct?

      Delete
    3. There is no threading solution that is right for all situations however that is where I would start thinking about putting the threads. That solution may not work for you, as an example if you had say 50 tables it would be a pretty large effort to create delegates for each and every table. In that case maybe you want it in the business logic layer. The key is to have the ability to change how the data is stored and accessed (in the data access layer) without having to change your business logic layer.
      I do not know anything about your data, how it is used, the amount of data or anything else about your application so I cannot really advise you how to use threads in you application, which is the reason my example does not include it.
      If you have four or five very small tables, you may not need to access the data on a separate thread. If you have a 100 tables maybe you only want to use threads for four of five of the tables or maybe you need it for everything. I know some people say to always use threads but every situation is different and there are times you do not want the user to do anything until the data is displayed (think of a cover screen with an activity indicator). Where and how you use threads is very dependent on your application and your data.

      Delete
    4. Thank you very much for your replies and this whole tutorial. There really isn't much else out there about using SQLite.swift and the documentation is a little sparse.

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Is there any plans on adding an update method to the DataHelper protocol? Would be nice to have a full/complete example on CRUD operations.

    ReplyDelete
    Replies
    1. I am going to be updating this post for Swift 3 and I will put it in my list to include an update method. Thank you for the suggestion.

      Delete
  14. Jon,

    Great tutorial, very clear and helpful!!
    Just a quick question:
    Why did you use typealias in the Model Layer? Could you have used structs instead?
    And for the business logic, would you use structs or classes?

    Thanks.

    ReplyDelete
    Replies
    1. I use tuples in the model layer because I want the type to be a data storage type with no business logic. If you need you add logic (methods) to those types you could use a struct if you like but why would you want to add logic at that layer?
      In the business logic layer I would recommend using structs however that is a personal preference. The key is to make all of the types either reference or value types for consistency.

      Delete
    2. Thanks, that makes sense.
      I'll go buy your book!!

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Hi Jon, I'm implementing the bridge pattern you've mentioned in the introduction of the tutorial and came up with something like this:

    The tuple (Dto in this case stands for "Data Transfer Object"):
    typealias ProcedureDto = (
    procedureId: Int64?,
    name: String?,
    description: String?,
    estimatedTime: Double?
    )

    And the struct to be used in the business logic:

    struct Procedure {
    init (procedureDto: ProcedureDto) {
    self.procedureDto = procedureDto
    }

    var procedureDto:ProcedureDto

    var procedureId: Int64 {
    get {
    return self.procedureDto.procedureId!
    }
    }

    var name: String {
    get {
    return self.procedureDto.name!
    }
    set {
    procedureDto.name = newValue
    }
    }

    var description: String {
    get {
    return self.procedureDto.description!
    }
    set {
    procedureDto.description = newValue
    }
    }

    var estimatedTime: Double {
    get {
    return self.procedureDto.estimatedTime!
    }
    set {
    procedureDto.estimatedTime = newValue
    }
    }
    }

    Does it make sense to you? Would you do something different than that?

    Thanks in advance.

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Hey great tutorial! No fat, all of it was informational.

    Is there a reason `teamId != nil` is not being checked in TeamDataHelper insert()?

    Also, on each of the Teams you add at the end ("Boston" and "Toronto") both teamId's are 0. Is that intentional? Does SQLite.swift autoincrement or is that just a typo?



    ReplyDelete
    Replies
    1. You are correct, SQLite will auto increment. In the definition for the table the teamId is set like this:
      -- t.column(teamId, primaryKey: true)
      With the primaryKey set to true this id will auto increment which is why, in the insert() method of the TeamDataHelper we ignore the teamId. This is the line that sets the values for the insert:
      -- let insert = table.insert(city <- item.city!, nickName <- item.nickName!, abbreviation <- item.abbreviation!)
      And you will notice we do not set the teamId which is why we do not bother to check to see if it is nil or not.

      Delete
  19. Great tutorial. Do you need to have a couple initializers set up for the find and findAll functions?

    I keep getting an error saying it cannot invoke initializer for type Player with the argument list of types...

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. i would like to add paging so that when the user scroll to bottom more data will be loaded from the local database. i can only use the insert, delete, find and findAll queries. how can i add more queries so that i can retrieve data with page count (i.e get only 10 rows)
    how can i use : users.limit(10, offset: 10) -> SELECT * FROM "users" LIMIT 10 OFFSET 10

    ReplyDelete
    Replies
    1. Sorry I have been unable to answer, but between work/family/other commitments I am having trouble finding time. I have not has the opportunity to need to write a query like that with SQLite.swift but I did look through the documentation and found this example:

      let query = users.select(email) // SELECT "email" FROM "users"
      .filter(name != nil) // WHERE "name" IS NOT NULL
      .order(email.desc, name) // ORDER BY "email" DESC, "name"
      .limit(5, offset: 1) // LIMIT 5 OFFSET 1

      this is from the documentation here: https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#sqliteswift-documentation

      I have not tried it but it sure looks like it is what you need. Hope that helps you.

      Delete
    2. No worries, i have already checked this documentation and added the limit query but still not working. it is working just like findAll so i'm stuck. Any way thank you for your time.

      Delete
  23. This comment has been removed by the author.

    ReplyDelete
  24. Great article. I'd like to check how you're going to do migration? Take your case as an example, I want to add a new field "logo" for the team. How are you going to do? Thanks.

    ReplyDelete