new/CommonFrame/DBManager.swift

//
//  DBManager.swift
//  dcwj
//
//  Created by Virgil on 16/8/30.
//  Copyright © 2016年 Virgil. All rights reserved.
//

import UIKit

class DBManager: NSObject {
   static var commonDB: FMDatabase!

    ///打开数据库
    class func openDB() -> Bool {
        if commonDB == nil {
            commonDB = FMDatabase(path: CommonDBManager.getDBPath())
        }
        let res = commonDB.open()
        if !res {
            return false
        }
        return true
    }

    ///关闭数据库
   class func closeDB() {
        if commonDB != nil {
            commonDB.close()
        }
    }

    ///检测表是否存在
  class  func tableExists(tableName: String) -> Bool {
        if !openDB() {
            return false
        }
        let res = commonDB.tableExists(tableName)
        if res {
            closeDB()
            return true
        } else {
            closeDB()
            return false
        }

    }

    ///检测列名是否存在
  class  func columnExists(columnName: String, tableName: String) -> Bool {
        if !openDB() {
            return false
        }
//        let res = commonDB.columnExists(tableName, columnName: columnName)
        let res = commonDB.columnExists(columnName, inTableWithName: tableName)
        if res {
            closeDB()
            return true
        } else {
            closeDB()
            return false
        }
    }

   ///根据sql 获取数据  返回数组  没有时数组个数为0   参数id=? values
   class func getDataForSql(sql: String, values: [String]?) -> NSMutableArray {
        let reData = NSMutableArray()

        if !openDB() {
            return reData
        }

        do {
            let set = try  commonDB.executeQuery(sql, values: values)
            while set.next() {
                reData.add(NSMutableDictionary(dictionary: set.resultDictionary!))
            }
        } catch {

        }
        closeDB()
        return reData
    }

     ///根据sql 获取 单条数据，没有时返回nil      参数id=?
    class func getDictionaryForSql(sql: String, values: [AnyObject]?) -> NSMutableDictionary? {
        let reData = NSMutableDictionary()

        if !openDB() {
            return nil
        }
        do {
            let set = try  commonDB.executeQuery(sql, values: values)
            while set.next() {
                reData.setDictionary(set.resultDictionary!)
                break
            }
        } catch {
            return nil
        }
        closeDB()
        return reData
    }
    /// 获取第一个值  执行失败返回空字符串
    class func getValueForSql(sql: String, values: [AnyObject]?) -> String {
        var reData = ""

        if !openDB() {
            return reData
        }

        do {
            let set = try  commonDB.executeQuery(sql, values: values)
            while set.next() {
                if set.string(forColumnIndex: 0) != nil {
                    reData  = set.string(forColumnIndex: 0)!
                }
                break
            }
        } catch {

        }
        closeDB()
        return reData
    }

     ///获取统计值 执行失败 返回 -1
    class func getCountForSql(sql: String, values: [AnyObject]?) -> Int {
        var reData = -1

        if !openDB() {
            return reData
        }

        do {
            let set = try  commonDB.executeQuery(sql, values: values)
            while set.next() {
                reData  = Int(set.int(forColumnIndex: 0))
                break
            }
        } catch {

        }
        closeDB()
        return reData
    }

    ///更新数据 含新增 修改   ?代替
   class func updateDataForSql(sql: String, values: [String]?) -> Bool {
        if !openDB() {
            return false
        }
        do {
            _ = try  commonDB.executeUpdate(sql, values: values)

        } catch {
            closeDB()
            return false
        }
        closeDB()
        return true

    }

    ///更新数据 多条 含新增 修改 ? values
    class func updateDataForSqls(sqls: [String], values: [[String]?]) -> Bool {
        if !openDB() {
            return false
        }
        do {
            var i = 0
            for sql in sqls {
                _ = try  commonDB.executeUpdate(sql, values: values[i])
                i+=1
            }
        } catch {
            closeDB()
            return false
        }
        closeDB()
        return true

    }

    ///增加表字段
    class func addColunm(tabName: String, colName: String, colType: String, defauleValue: String) -> Bool {
        let sql = "ALTER TABLE '\(tabName)' ADD '\(colName)' \(colType) DEFAULT '\(defauleValue)'"
        return updateDataForSql(sql: sql, values: [])
    }

}