EzDev.org

SQLite.swift

A type-safe, Swift-language layer over SQLite3.


filtering Tables and put it into an array , some new in Xcode 7?

I've got Xcode 7 but since there are many errors in my old Project, I stuck with one Problem:

xcode 6:

var myData: Array<Row> = []
var my_mData: Array<Row> = []

var m_mitglieder : Query!
var m_firmen : Query!
var stmt_m : Query!
var stmt_f : Query!
var Daten:[String] = []

let m_vorname = Expression<String>("Vorname")
.....

in ViewDidLoad

m_mitglieder = dbFirma["Mitglieder]
stmt = m_mitglieder.select(m_vorname,m_nb......).filter(mnr0 == m_mnr)
myData = Array(stmt)

if Int(myData[0].get(m_nb)) > 0 {
  Daten.append(myData[0].get(m_nb))
}
...

xcode 7:

var myData: Array<Row> = []
var my_mData: Array<Row> = []

let m_mitglieder = Table("Mitglieder")
let m_firmen = Table("firmen")
//var stmt_m : Query!
//var stmt_f : Query!

let m_vorname = Expression<String>("Vorname")
.....

in ViewDidLoad

m_mitglieder = dbFirma["Mitglieder]
let stmt = m_mitglieder.select(m_vorname,m_nb......).filter(mnr0 == m_mnr)
let myData = Array(stmt)  <<<<<ERROR Missing Argument lable 'arrayLiteral:' in call

if Int(myData[0].get(m_nb)) > 0 {
  Daten.append(myData[0].get(m_nb))
}
...

I get the error message : Missing Argument lable 'arrayLiteral:' in call

But when I try to fix it to

myData = Array(arrayLiteral: stmt)    

then I get errors as

Daten.append(myData[0].get(m_nb))  <<<ERROR: Value of type
'ScaleQuerystring>' has no member 'get'

How can I fix this Problem?


Source: (StackOverflow)

Use NSArray in SQLite Statement

I use SQLite.swift and want to replace the question marks in a statement. This can be done for single entries like this:

let stmt = db.prepare("INSERT INTO users (email) VALUES (?)")
for email in ["betty@icloud.com", "cathy@icloud.com"] {
    stmt.run(email)
}

I did not see how I can do the following to use a array like:

var values:[String] = ["test1","test2", "test3"]

in a Statement like:

let stmt = db.prepare("SELECT * from users where email in (?)")

The following does not work:

stmt.run(values)

How do I use an NSArray as an argument for a statement?


Source: (StackOverflow)

Sqlite.swift create dynamic complex queries

I have 1 table with multiple columns. On the app, we are looking forward to add 4 dynamic filters like (cat, size, color,shape).

We know we can create a filter to sqllite like so:

user = user.select(name) 
        .filter((color == "Blue") && (size = "Big") && (cat="a") && (shape="round")) 
        .order(name.asc, name) // ORDER BY "email" DESC, "name"
        .limit(5, offset: 0)

But what happens if a filter, let's say that for color we want to search for all colors. Then,

.filter((color == "?????") && (size = "Big") && (cat="a") && (shape="round"))

Any ideas on how to create dynamic filters for this case?


Source: (StackOverflow)

Test Driven Development in Swift

I'm playing around a bit in Swift and after installing some external frameworks I decided that before I go any further with this project I'll write some tests.

I first imported my model class into my test class :

import XCTest
import Company

class CompaniesTest: XCTestCase {
...

However I got an error : "No such Module Company"

I then went to that model and added it to the target Mobile CRMTest:

enter image description here

But then in my model Class I got the same Error : "No such Module SQLite"

import Foundation
import SQLite
import SwiftyJSON

class Company  {

for the SQLite.swift framework I'm using (installed using CocoaPods)


Source: (StackOverflow)

NSDate Compare in tests

I'm writing some tests to see if a person object is correctly saved into a local database (SQLite, using strings for the dates).

in my tests I have :

let testPerson = Person(firstName:"John", lastName:"Doe")

After saving to the database I test if createdAt != nil

That is ok! Then :

let testPerson2 = Person.LoadByID(testPerson.id)

which is a static function loading a person object from the database into testPerson2

all fields are passing in the tests (so they're exactly the same for testPerson and testPerson2 except the createdAt and updateAt dates

If I do a println of those dates, they're EXACTLY the same :

testPerson.createdAt : Optional(2015-08-14 12:03:01 +0000)
testPerson2.createdAt :Optional(2015-08-14 12:03:01 +0000)

But testing (via 2 different methods):

XCTAssert(testPerson2.createdAt == testPerson.createdAt, "createdAt should be the same")

or

XCTAssertTrue(testPerson2.createdAt!.isEqualToDate(testPerson2.createdAt!), "createdAt should be the same")

fail

Doing those tests with 2x testPerson or testPerson2, succeeds

I also tried the following code:

if testPerson.createdAt!.compare(testPerson.createdAt!) == NSComparisonResult.OrderedDescending
        {
            println("date1 after date2");
        } else if testPerson.createdAt!.compare(testPerson.createdAt!) ==     NSComparisonResult.OrderedAscending
        {
            println("date1 before date2");
        } else
        {
            println("dates are equal");
        }

failing if I use testPerson and testPerson2, succeeding with testPerson/testPerson and testPerson2/testPerson2

Why ? Println and looking into the database itself look ok.


Source: (StackOverflow)

SQLite.swift: Use XCGLogger

I'm using SQLite.swift library in my iOS project.

Now I'm logging SQLite sentences using db.trace(println) as suggested in the documentation, but I want to use the XCGLogger library with something like log.info().

Somebody knows how to do it?


Source: (StackOverflow)

How to pass Row of sqlite.swift to another controller using NSNotification

I am using sqlite.swift in my project.

let inputdata = row as Row

NSNotificationCenter.defaultCenter().postNotificationName("navigateToProductDetail",object: inputdata)

I cant pass "inputdata"

inputdata would be AnyObject , in my case its Row

so it throwing error, Help me to solve this or tell me alternate way to pass this row object to another controller

enter image description here


Source: (StackOverflow)

How to delete table or update column with SQLite.swift ?

I need to re-create my table in my database. Is there anyway to update table's columns or delete table with SQLite.swift ?


Source: (StackOverflow)

How to execute a complex sql statement and get the results in an array?

I would like to execute a fairly complex SQL statement using SQLite.swift and get the result preferably in an array to use as a data source for a tableview. The statement looks like this:

SELECT defindex, AVG(price) FROM prices WHERE quality = 5 AND price_index != 0 GROUP BY defindex ORDER BY AVG(price) DESC

I was studying the SQLite.swift documentation to ind out how to do it properly, but I couldn't find a way. I could call prepare on the database and iterate through the Statement object, but that wouldn't be optimal performance wise.

Any help would be appreciated.


Source: (StackOverflow)

SQLite Swift db.prepare Optional() in statement values

In the SQLite Swift documentation there is reference to getting statement results directly. I have a lot of SQL queries prepared and I don't really want to refactor them. I would sooner use them as they are using db.prepare, as per below.

Statements with results may be iterated over.

let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    print("id: \(row[0]), email: \(row[1])")
    // id: Optional(1), email: Optional("alice@mac.com")
}

The return values always have the "Optional()" around them. Is there a way we can just get the raw row values back without this?


Source: (StackOverflow)

How to use 'SQLite' (the wrapper on github) in a Command Line Tool?

I'm successfully using the great Swift wrapper for sqlite from https://github.com/stephencelis/SQLite.swift in a Cocoa application.

But when I try to use the wrapper in a Command Line Tool project and follow the same detailed installation steps I get the following error:

Check dependencies

Unable to run command 'PBXCp SQLite.framework' - this target might include its own product.

I checked the dependencies, but couldn't figure out how to solve this.


Source: (StackOverflow)

Sqlite.swift live search

I want to do a live search on the DB.

Lets say I want to search by companies and I have the following info on a column named companies.

  • Facebook
  • FastCompany
  • Facebook
  • Google
  • Microsoft

I have a textfield that has calls a function on editchanged.

   @IBAction func searching(sender: AnyObject) {
        tempstring = "%"+searchBar.text+"%"
        println(tempstring)

         user = user.select(name)
            .filter(like(tempstring, name))
            .limit(30, offset: 0)
        collectionView?.reloadData()
}

It kind of works, if I start typing "fa" It will show (Facebook, Facebook and FastCompany)

If I continue typing "fac" it will show (Facebook, Facebook)

But when I delete the last character "c" from the searchbox (leaving it in "fa" again) then the query displays nothing.

Any ideas on how I can solve this.


Source: (StackOverflow)

add json-based data into a sqlite database using swiftyJSON and sqlite.swift

Please bear with me this is my first swift project, I'm not yet up to speed with the syntax/language.

I retrieve data from a web service using AlamoFire and parse it using SwiftyJson. Then I want to insert it into a local SQLite database using SQLite.swift.

So far AlomoFire, SwiftyJson and SQLite.swift are installed and working in my project, things went very smooth to my surprise ;)

Now my question. The data I got is in JSON, in an Array so I want to do this:

let companiesTable = db["companies"] // get the table from our SQLite Database
db.transaction(.Deferred) { txn in
    for (index,company) in data {
        if companiesTable.insert(name <- company["name"]!), shortname <- company["short_name"]).statement.failed {
            return .Rollback
        }
    }
    return .Commit
}

My problem is in the insert. I have to force unwrap using a !, which is ok for name (required column in the database), but not ok for shortname or a dozen other columns (not mentioned in the example above for simplicity) which may be empty/null. Of course only columns with a value should be inserted.

Second question. I found the stuff about transactions here on stackoverflow, is the transaction automatically Committed or Roll-backed when doing the 'return'


Source: (StackOverflow)

How to Delete Rows with SQLite.SWIFT?

I use SQLite.SWIFT and want to delete rows with specific id from my table.

The documentation here said that I can use:

let delete = delete.update(email <- "alice@me.com")
if let changes = delete.changes where changes > 0 {
    println("deleted alice")
} else if delete.statement.failed {
    println("delete failed: \(delete.statement.reason)")
}

I could not find a global delete function. My table is

let users = db["users"]

How do I perform the delete.update function?


Source: (StackOverflow)

Can I cast Int64 directly into Int?

I've been using SQLite.swift lately to build my app database. And I'm defining all my INTEGER columns with a Int64 type, like the documentation explains.

But every once in a while I need that Int64 to be just Int. So my question is, if I do this:

//Create a table with Int instead of Int64
let test_id = Expression<Int>("test_id")
let tests = db["tests"]

db.create(table: tests, ifNotExists: true){ t in
    t.column(test_id)
}


class func insertTest(t: Int) -> Int{
    //insert.rowid returns an Int64 type
    let insert = tests.insert(test_id <- t)
    if let rowid = insert.rowid{
        //directly cast Int64 into Int
        return Int(rowid)
    }
    return 0
}

Will it be correct?

Of course I tested it. And it does works, but I was reading this question in Stackoverflow

And it seems that I could have a problem with 32 bits devices...

If this is wrong, how can I cast Int64 into Int?


Source: (StackOverflow)