metrozines
 Stories from the Metro • MetrObservations • MetroBiographies

FMDB Using Swift

For SQL-savvy developers, using FMDB with Sqlite lets us run rings around CoreData users. So lets bring our skill-set to Apple's latest and greatest: The Swift Programming Language!

With this tutorial, you don't need to wait for a port of FMDB to Swift. You can just use it as is in its Objective-C form!

STEPS:
1) add 'libsqlite3' standard library to your project and copy FMDB files (even though they're Objective-C) to your project.

2) create a file called "FMDB-Bridging-Header.h"

inside "Bridging-Header.h" type the following:
#import "FMDB.h"

3) go to Build Settings -> Swift Compiler - Code Generation
- add to 'Objective-C Bridging Header': FMDB-Bridging-Header.h

or if it was placed inside a folder in your project:

FolderName/FMDB-Bridging-Header.h

4) copy into your project the SQLite database. In this instructional, we'll assume the file is called 'tempdb.sqlite' with just one table inside:
CREATE TABLE test_tb ( test_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, keywordtext TEXT)

5) in your AppDelegate.swift's class AppDelegate add this to your variables:

var dbFilePath: NSString = NSString()


For example:

import UIKit

@UIApplicationMain
class AppDelegate: UIResponder, UIApplicationDelegate {

var window: UIWindow?
var navi: UINavigationController?
var dbFilePath: NSString = NSString()

func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: NSDictionary?) -> Bool {
....


6) add this to method inside AppDelegate.swift's class AppDelegate
// MARK: - FMDB
 
let DATABASE_RESOURCE_NAME = "tempdb"
let DATABASE_RESOURCE_TYPE = "sqlite"
let DATABASE_FILE_NAME = "tempdb.sqlite"
 
func initializeDb() -> Bool {
        let documentFolderPath = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as String
 
        let dbfile = "/" + DATABASE_FILE_NAME;
 
        self.dbFilePath = documentFolderPath.stringByAppendingString(dbfile)
 
        let filemanager = NSFileManager.defaultManager()
        if (!filemanager.fileExistsAtPath(dbFilePath) ) {
 
            let backupDbPath = NSBundle.mainBundle().pathForResource(DATABASE_RESOURCE_NAME, ofType: DATABASE_RESOURCE_TYPE)
 
            if (backupDbPath == nil) {
                return false
            } else {
                var error: NSError?
                let copySuccessful = filemanager.copyItemAtPath(backupDbPath, toPath:dbFilePath, error: &error)
                if !copySuccessful {
                    println("copy failed: \(error?.localizedDescription)")
                    return false
                }
 
            }
 
        }
        return true
 
    }
 

7) call it in AppDelegate.swift's func application


func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: NSDictionary?) -> Bool {

if self.initializeDb() {
NSLog("Successful db copy")
}


8) in the ViewController where you'll use FMDB (in this case, we'll use the data inside a UITableViewController) use FMDB like this:
 
import UIKit
 
class SecondViewController: UIViewController {
 
// MARK: - .H
 
    @IBOutlet var dataTable: UITableView?
    var dataArray:[MultiField] = []
 
// MARK: - .M
 
    required init(coder: NSCoder) {
        fatalError("NSCoding not supported")
    }
 
    override init(nibName nibNameOrNil: String?, bundle nibBundleOrNil: NSBundle?) {
        super.init(nibName: nibNameOrNil, bundle: nibBundleOrNil)
        // Custom initialization
    }
 
    override func viewDidLoad() {
        super.viewDidLoad()
 
        // Do any additional setup after loading the view.
        self.title = "FMDB Using Swift"
 
        let mainDelegate: AppDelegate = UIApplication.sharedApplication().delegate as AppDelegate
 
        // initialize FMDB
        let db: FMDatabase = FMDatabase(path:mainDelegate.dbFilePath)
        if (db.open() == nil) {
            NSLog("error opening db")
        }
 
        // insert data
        let addQuery = "INSERT INTO test_tb (name, keywordtext) VALUES ('excalibur', 'hot')"
        let addSuccessful = db.executeUpdate(addQuery, withArgumentsInArray: nil)
        if !addSuccessful {
            println("insert failed: \(db.lastErrorMessage())")
        }
        // end insert data
 
        // update data
        let updateQuery = "UPDATE test_tb SET keywordtext = 'cool' WHERE name = 'excalibur' "
        let updateSuccessful = db.executeUpdate(updateQuery, withArgumentsInArray: nil)
        if !updateSuccessful {
            println("update failed: \(db.lastErrorMessage())")
        }
        // end update
 
        // get data from db and store into array used by UITableView
        let mainQuery = "SELECT name, keywordtext FROM test_tb"
        let rsMain: FMResultSet? = db.executeQuery(mainQuery, withArgumentsInArray: [])
 
        while (rsMain!.next() == true) {
            let productName = rsMain?.stringForColumn("name")
            let keywords = rsMain?.stringForColumn("keywordtext")
 
            let multiField = MultiField(aField1: productName!, aField2: keywords!)
            self.dataArray.append(multiField)
 
        }
        // end get data
 
        // delete data
        let delQuery = "DELETE FROM test_tb WHERE name = 'excalibur' "
        let deleteSuccessful = db.executeUpdate(delQuery, withArgumentsInArray: nil)
        if !deleteSuccessful {
            println("delete failed: \(db.lastErrorMessage())")
        }
        // end delete data
 
        // example: get num rows
        let rsTemp: FMResultSet? = db.executeQuery("SELECT count(*) AS numrows FROM test_tb", withArgumentsInArray: [])
        rsTemp!.next()
        let numrows = rsTemp?.intForColumn("numrows")
 
        NSLog("numrows: \(numrows)")
        // end get num rows
 
        db.close()
 
    }
 
    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }
 
// MARK: - TableView DataSource
 
    func numberOfSectionsInTableView(tableView: UITableView!) -> Int {
        return 1
    }
 
    func tableView(tableView: UITableView!, numberOfRowsInSection section: Int) -> Int {
 
        return self.dataArray.count
    }
 
    func tableView(tableView: UITableView!, cellForRowAtIndexPath indexPath: NSIndexPath!) -> UITableViewCell!  {
 
        let cell: UITableViewCell = UITableViewCell(style: UITableViewCellStyle.Subtitle, reuseIdentifier: "FMDBTest")
 
        let multiField: MultiField = self.dataArray[indexPath.row]
 
        let num = indexPath.row + 1
 
        cell.textLabel.text = "\(num). \(multiField.field1!)"
        cell.detailTextLabel.text = multiField.field2
 
        return cell
    }
 
// MARK: - UITableViewDelegate
 
    func tableView(tableView: UITableView!, didSelectRowAtIndexPath indexPath: NSIndexPath!) {
        tableView.deselectRowAtIndexPath(indexPath, animated: true)
 
 
    }
 
 
}
 
And here's the code for Multifield.swift which I use simply for storing the contents of the database in an array for easy retrieval in the UITableView:
//
//  MultiField.swift
 
import Foundation
 
class MultiField: NSObject {
    var field1: NSString?
    var field2: NSString?
 
    override init() {
        super.init()
    }
 
    convenience init(aField1:NSString, aField2:NSString) {
        self.init()
        if (self != nil) {
            self.field1 = aField1
            self.field2 = aField2
        }
    }
 
}
FMDB Honcho Rob Ryan was good enough to give some tips to make this tutorial better and we've integrated those tips above. He's also given us the low-down on how to use multithreaded FMDB! Here it is...

Multithreaded FMDB Via FMDatabaseQueue
var queue: FMDatabaseQueue?
 
func testDatabaseQueue() {
    let documentsFolder = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as String
    let databasePath = documentsFolder.stringByAppendingPathComponent("test.sqlite")
 
    queue = FMDatabaseQueue(path: databasePath)
 
    // create table
 
    queue?.inDatabase() {
        db in
 
        var success = db.executeUpdate("create table test (id integer primary key autoincrement, a text)", withArgumentsInArray:nil)
 
        if !success {
            println("table create failure: \(db.lastErrorMessage())")
            return
        }
    }
 
    // let's insert five rows
 
    queue?.inTransaction() {
        db, rollback in
 
        for i in 0 ..< 5 {
            if !db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"]) {
                println("insert \(i) failure: \(db.lastErrorMessage())")
                rollback.initialize(true)
                return
            }
        }
    }
 
    // let's try inserting rows, but deliberately fail half way and make sure it rolls back correctly
 
    queue?.inTransaction() {
        db, rollback in
 
        for i in 5 ..< 10 {
            let success = db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"])
 
            if !success {
                println("insert \(i) failure: \(db.lastErrorMessage())")
                rollback.initialize(true)
                return
            }
 
            if (i == 7) {
                rollback.initialize(true)
            }
        }
    }
 
    // let's prove that only the first five rows are there
 
    queue?.inDatabase() {
        db in
 
        if let rs = db.executeQuery("select * from test", withArgumentsInArray:nil) {
 
            while rs.next() {
                println(rs.resultDictionary())
            }
        } else {
            println("select failure: \(db.lastErrorMessage())")
        }
 
    }
 
    // let's drop that table
 
    queue?.inDatabase() {
        db in
 
        let success = db.executeUpdate("drop table test", withArgumentsInArray:nil)
 
        if !success {
            println("table drop failure: \(db.lastErrorMessage())")
            return
        }
    }
}
From Rob: But this not only illustrates the use of

FMDatabaseQueue

(and the non-intuitive way one sets the

rollback

boolean), but also the use of a class property for the database/queue (because its not advisable to constantly open and close databases, but rather open it up once and use it repeatedly).

Jaime Llanes: Kingmaker
By Vip Malixi

If only one or two players excel coming from a training program, then the results might not have been due to the program but the players' natural talent. But when dozens emerge and they're all exceptional, then it's the coach. So just like tennis's Nick Bollettieri who has bred champions Monica Seles, Andre Agassi, Martina Hingis and Boris Becker, badminton's Jaime Llanes spawns champions like rabbits. The list of players who've passed through Jaime reads like a badminton All-Stars: Alex Cuevas, Ricky Bartolome, Alfredo "Gudo" Mailon, Reynaldo Selga, Wilson Frias, Tim Ang, Kevin Dalisay, the Magnaye brothers, Irene Chui, Arlo Madrid (Toby's 2006 under-14 champion), Descka Calimlim, Abby Garcia.

Jaime Llanes was part of the mythical 1997 Jakarta SEA Games Philippine team that beat Singapore which the Philippines hadn't won against for 45 years. Composed of Jaime and his brother Melvin, Kennevic Asuncion, Naresh Ramnani, Anthony Ave, Arolas Amahit, Ian Piencenaves, and Rhamir Antonio, with coaches Renato Reyes and Nelson Asuncion, they got a third-place finish in badminton for the first time. After the loss, Singapore felt so embarrassed they did a complete overhaul and brought in imported players from China.

How It All Started: The Birth of JLTC (the Jaime Llanes Training Camp)
Malvin Alcala was the PNP (Philippine National Police at Camp Crame) team's former coach. When he left, Jaime was asked if he could take over, even temporarily. He said, "Okay, I'll handle the training. But in return, I insist the players follow my instructions to the letter. I'm a strict, disciplinarian, you see."

Jaime started with five players.

"Actually I didn't want to train people yet then. I was still in the national team and that's where I wanted to concentrate. But I felt sorry for the kids at Crame. They were just lounging around, looking aimless. So I said, 'why not?'

After just eight months, he tested his players mettle with a huge tournament. "The initial players I taught joined the first-ever JVC Tournament (which had one of the strongest fields then)." The result? "Every single one of my players reached the quarterfinals in their categories!"

Bogs Amahit, Jaime's former national team compatriot who was at the tournament said, "Aren't all those guys your kids? How did all of them get (into the quarters)? Why are they all so strong?"

During the JVC, Kennevic Asuncion and Lloyd Escoas were pushed to 14-all by Jaime's charges, Alex Cuevas and Paul Armas before eventually prevailing. The elite pair had a real difficult time handling Cuevas's smash. Jaime said he taught Cuevas rhythm, which he learned during his training in China. "I applied to them the number of repetitions of our China drills. I was very strict."

Jaime even got into Alex Cuevas's diet. "Alex has a tendency to gain weight. So I even talked to his parents and told them that he could only eat one bowl of rice per meal." The result was Alex trimming down and being both muscular and quick.

This early success at JVC spurred Llanes to continue with his training camp. "My wife encouraged me to continue with the PNP program." So after training at Rizal Memorial with the national team, Jaime would go to Camp Crame and train his players at night. Back then, it was all voluntary. He wasn't getting paid for his training gig. He just got by with his national team allowance and earnings from private lessons.

Now he gets a yearly fee from the parents of players he trains. But he also entertains "scholars"--students who he sees have no capacity to pay but has potential. He just tells them, "You don't have to pay, I'll take care of you." These guys and gals won't get a break during training though. In fact, is doubly strict with them because he has higher expectations. "I'm after them becoming so good that they'll get educational scholarships. Like, I've been able to get a lot of kids into De La Salle. Instead of paying tuition, they actually receive allowances. For example, Ricky Cajefe, my former trainee, is already graduating. Ricky goesn't have parents anymore to support him, yet he was able to get a 100% scholarship plus P4,000 a month allowance. He really worked hard during training. He was the fastest at sprints. He was the most agile. He was the fastest to finish at skipping rope. You see, how we do skipping rope exercises is this: the minimum is 1,500 repetitions, double-skip, and you have to finish within 30 minutes. Ricky's able to do it in 21 minutes! Because for every set, he's able to do 500! So he was able to overcome adversity. Now he's graduating from De La Salle!"

"Anyway, from those first five (who joined the JVC), the number of players I handled grew. When word got out that all these new players received training from me, the news spread."

Getting Into the National Team
As a coach and player, Jaime is well-experienced. He's trained in China, Korea, and Malaysia. He also joined dual-meets with many clubs in Taiwan as well as a world junior tournament in Indonesia.

"When we were training in China, they were very meticulous when it came to strokes. Every little mistake brought on a scolding. So that's how I was with my trainees. Once I see that they're doing something wrong, I immediately correct it. I fix their strokes before they take on bad habits."

Unlike other coaches who let things slide, Jaime says that it's not the student's reputation that gets ruined when people see they're not doing something correctly. It's his reputation they're carrying.

"For example, Tim Ang. A lot of people are saying his style of play is very much like mine because I taught him everything I know, including the reverse slice and his fluid movement."

At 31, Jaime, who is in the same batch as Camilla Martin, was with the national team from 1992 and retired in 2002. He talked about how things were then:

"Before you got into the national team, you must first be a member of the junior team. Once you're with the juniors, you next have to beat the No. 10th ranked player in the national team."

"So Melvin and I kept at it. First we beat No. 10, then 9, 8, and so on."

"Back then, there were so many strong players. You had to compete against the other national players to stay on the team. If you couldn't, you went down to the junior team."

Jaime's Training Program
Jaime's training program usually has a conditioning component. This consists of long-distance running on Mondays and Fridays, weights and sprints on Tuesdays and Thursdays. Skipping rope on Wednesdays, then court time (games and court tactics) on Saturdays and Sundays.

His youngest student is six years old. He's divided his training camp into three groups. Batch 1 is where Tim Ang, Kevin Dalisay, the Magnaye brothers came from. Batch 2 followed them. They're the medium players. They're good and competing but need more polish. And my third batch are the tykes: six, seven, eight, and nine years old. "What I do is, every time someone from batch 1 leaves, then a player from a lower batch moves up, so there's constant replenishment of new talents."

He charges P10,000 a year for his training batches and P400 per hour for private students.

Before he accepts students into his batches, he first asks them to try out. Once he sees what kind of strokes they have, that's when he determines what batch to put them in.

One thing he tries to instill in them is self-discipline. Before they can reach the next level, they first have to have internal discipline.

He's never physical in his scolding, but he is very strict when it comes to the exercise-punishments his students have to do to make up for infractions.

Even his private students aren't spared Jaime's whip. Laughingly, he says some of his private students call him sadist. But that's why some of his private students become champions. Their games have really improved, and when people ask who trained them and they give Llanes's name, the usual retort is, "No wonder!"

Jaime gave this anecdote: "One time I had a female student who had a back problem. After one year of training with me, she went back to her doctor and her doctor was surprised that she had gained back muscles that were now supporting her spine! The doctor was shocked. 'How did this happen?' he asked her."

"I gave her back-specific exercises like hanging back arches for two minutes that made her back stronger. Now her back is so built up she's no longer bothered by scoliosis."

Jaime never advertises his training, like posting flyers at gyms. He prefers to get students via word of mouth. He wants students to be the ones who come to him. And come to him they do. For the Battle of the Courts tournament, for example, Valle Verde hired him to improve their chances and the improvements in Valle's players' games were remarkable. They probably lasted longer in 'Battle' due to the training Jaime was able to give them in such a short span of time.

Jaime doesn't mind students coming from other coaches. He also gives credit where credit's due. For example, he'll be the first to say his better players didn't get all their training from him, that they first went through other coaches. That's why he's in good terms with the other coaches--they know he'll give them their due.

Jaime is also constantly researching--finding out new techniques. He says he can't stop looking at the latest trends because he's a coach so his knowledge of badminton needs to be vast.

Players Today Vs Players Before
Jaime sees a difference now between today's players and his batch. "Back then, we were more 'stroke players' instead of power players. We used our heads more. Nowadays, it seems players are concentrating on trying to end the rally as quick as possible with a power game." Based on how we fared in the Philippine Open, where the Indonesians outlasted and "out-consistent-ed" our players, Jaime may have a point. While Filipino players nowadays expect to be perfect for 5 to 7 strokes in a rally, world-class players expect to do 50 or more. Jaime said, back during his time, the emphasis was more in mixing endurance with power and being patient and strategizing, knowing the point won't end too quickly, a player had to be ready for the long haul.

Well Coach Jaime certainly knows what he's talking about. And parents looking for a serious badminton future for their son or daughter need look no further than Jaime Llanes.

 

Guidelines for a Traffic-Free Metropolis

Traffic Enforcers Must Know the Goals
The purpose of traffic enforcement is to reduce traffic and improve traffic safety. The first thing a traffic enforcer must learn is this. If a traffic enforcer's actions leads to more traffic and/or less safety, then the traffic enforcer must change their behavior or be replaced with a traffic enforcer aware of these guidelines.

Intersection Stop-Go Guidelines
1. Stop-Go Timing
Stop, Go, and Left Turn times depend on the intersection situations:

1) traffic flow is good for all paths - in this case, Stop-Go-Left-Turn times must all be equal and not be more than 1 minute. Timing should be from 22 seconds to 1 minute, depending on how many cars pass through. If after 25 seconds most cars have passed, then timing can be kept at 25 seconds. If after 1 minute there are still a great volume of cars, then timing can be kept at 1 minute.

2) traffic flow is good for some paths but bad for others - in this case, for the bad flow, for example, West->East is bad, but East->West and North->South, South->North are good, then timing for bad flow may be slightly longer. For example, West->East = 1 minute, while all the other paths use 30 seconds.

3) traffic flow is blocked at 1 or more paths - if for example, West->East flow is sporadic (only 1 or 2 cars can pass every 22 seconds for example), then this is how timing is done: first, stop all traffic that passes through West->East from the other paths, that this, those turning right into West->East from the other paths when it is the turn of West->East. Next, instead of timing by seconds, count how many cars are able to flow from West->East, that is, how many cars are able to cross the intersection. Count from 7 to 10 cars, depending on the traffic, before making it the turn of the other paths. In other words, for the other paths, use seconds, so East->West, North->South, South->North would all use 30 seconds, while West->East will be allowed to flow until 7 to 10 cars are able to pass through. If the traffic is really bad, then just 7, if 10 cars are able to pass in less then 2 minutes, then allow 10.

IMPORTANT: DO NOT GO PAST THESE GUIDELINES. Letting one path to flow for more than 1 minute or 10 cars will block the flow of the other path to the point that traffic will extend for many cars. This will then block streets and intersections behind and cause a major traffic bottleneck. Stoplights or traffic enforcers who do timings past 1 minute or 10 cars should immediately be relieved and retrained/recalibrated so as to avoid further traffic congestion.

2. Before installing a stoplight or adding a traffic enforcer - Test if putting a stoplight or a manual stop-go traffic enforcer will improve traffic flow and safety before adding either of them to the intersection - for 1 week (Sunday to Monday), for 15 minute intervals: 9am-9:15am, 5:30pm-5:45pm and 7:30pm-7:45pm, count how many cars travel for 15 minutes at all paths. If you do not have 4 people to count all traffic and only 1 person, have the person first count at one intersection for the first 15 minutes, then the opposite flow, for the next 15 minutes, then do this for the other side. You count the car once it has passed the intersection.

Do this measurement first without the stoplight or stop-go traffic enforcer. Next do the measurement with an operational stoplight or stop-go traffic enforcer. Compare the two measurements. If traffic flow is consistently better by at least 3 cars and not made worse on all paths, then it is worth adding a stoplight or stop-go officer.

For example, going North->South = 15 cars for 15 minutes. South->North = 22 cars. West->East = 7 cars. East->West = 8 cars. This measurements were done from 9am-9:15am without a stoplight or stop-go enforcer. With a stoplight/stop-go enforcer the measurements were: North->South = 20 cars. South->North = 22 cars. West->East = 9 cars. East->West = 8 cars. In this case, it is worth adding a stoplight or stop-go enforcer because it did not make traffic flow worse and improved on certain paths.

Note that adding a stoplight or traffic-go enforcer may improve at certain time slots (for example, morning) but make traffic flow worse at other times (for example, evening). If such is the case, then the stoplight or traffic enforcer must not be operating during evening so as not to impede traffic flow and only operate during mornings.

3. Arrest priority should given for those restricting traffic flow - traffic enforcers should emphasize arresting vehicles that restrict traffic flow or endanger traffic safety over other traffic rules. For example, for-hire vehicles that load/unload near the intersection (at least 25 meters whether before or after the intersection) should immediately be arrested and their vehicle impounded to prevent repeat offending because this is the number one cause of traffic impedance. Another example are vehicles turning left who block the middle lane, or vehicles going straight who block the right lane.