Select Count(Col_Name) in Sqlite (Swift) Not Working

Select count(col_name) in sqlite (Swift) not working

The column numbers when returning values (e.g. the result of sqlite3_column_xxx) are zero-based. You’re attempting to retrieve column 1, when you meant column 0.

By the way, if you want to detect invalid index, you can remove that nil coalescing operator. E.g., so, instead of:

if try stmt.step() == .row {
    return Int(from: stmt,index:  1) ?? -1
}

You could instead check to see if not only if step succeeded, but also that Int(from:index:) returned a non-nil value:

if try stmt.step() == .row {
    if let count = Int(from: stmt, index: 1) {
        return count
    } else {
        // do whatever you want when it fails, e.g.

        fatalError("no value returned; invalid column index?")
    }
}

Or perhaps more succinctly:

if try stmt.step() == .row, let count = Int(from: stmt, index: 1) {
    return count
} else {
    fatalError("no value returned; invalid column index?")
}

That would lead you to recognize that you need the 0-based index:

if try stmt.step() == .row, let count = Int(from: stmt, index: 0) {
    return count
} else {
    fatalError("no value returned; invalid column index?")
}

Accessing an SQLite Database in Swift

While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would:

Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do:
```
import SQLite3
```

Create/open database.

let fileURL = try! FileManager.default
    .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
    .appendingPathComponent("test.sqlite")

// open database

var db: OpaquePointer?
guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
    print("error opening database")
    sqlite3_close(db)
    db = nil
    return
}

Note, I know it seems weird to close the database upon failure to open, but the sqlite3_open documentation makes it explicit that we must do so to avoid leaking memory:

Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

Use sqlite3_exec to perform SQL (e.g. create table).

if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error creating table: \(errmsg)")
}

Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

var statement: OpaquePointer?

if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error preparing insert: \(errmsg)")
}

if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure binding foo: \(errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure inserting foo: \(errmsg)")
}

Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

Reset SQL to insert another value. In this example, I'll insert a NULL value:

if sqlite3_reset(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error resetting prepared statement: \(errmsg)")
}

if sqlite3_bind_null(statement, 1) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure binding null: \(errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("failure inserting null: \(errmsg)")
}

Finalize prepared statement to recover memory associated with that prepared statement:

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error finalizing prepared statement: \(errmsg)")
}

statement = nil

Prepare new statement for selecting values from table and loop through retrieving the values:

if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error preparing select: \(errmsg)")
}

while sqlite3_step(statement) == SQLITE_ROW {
    let id = sqlite3_column_int64(statement, 0)
    print("id = \(id); ", terminator: "")

    if let cString = sqlite3_column_text(statement, 1) {
        let name = String(cString: cString)
        print("name = \(name)")
    } else {
        print("name not found")
    }
}

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error finalizing prepared statement: \(errmsg)")
}

statement = nil

Close database:

if sqlite3_close(db) != SQLITE_OK {
    print("error closing database")
}

db = nil

For Swift 2 and older versions of Xcode, see previous revisions of this answer.

Value of type 'Tags' has no member 'lastUsed'

The problem is that the declarations of name and lastUsed have an invisible character (U+200B = "ZERO WIDTH SPACE") as the last character of the identifier.

Here is a short example demonstrating what seems to be a paradox. The first print statement does not compile, but the last one does:

struct Tags {
    let name = "name"
}

print(Tags().name) // (1) Error: Value of type 'Foo' has no member 'name'
print(Tags().name) // (2) No error

Unfortunately, Xcode does not display this character, even if “Editor->Invisibles” is switched on. Opening the file in vi shows the issue:

struct Tags {
    let name<200b> = "name"
}

print(Tags().name) // (1) Error: Value of type 'Foo' has no member 'name'
print(Tags().name<200b>) // (2) No error

Note that invisible characters are allowed in identifier names, this has been discussed in the Swift forum.

The first print statement was created with Xcode's code completion and Xcode omits the trailing invisible character. I would consider that a bug.

The second print statement was created by carefully copying the name identifier including the trailing invisible character.

Summary: Swift identifiers can contain invisible characters, but those do not work well with Xcode's code completion and cause only confusion. Rewriting all occurrences of those identifiers fixes the issue.

SQLite Order By Date1530019888000

you can do it like this

SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1

Complex sorting for search in realm, union of multiple RealmResults

Thank you guys, you both helped me alot to solve the problem. @Mateusz Herych @EpicPandaForce

Here is the custom adapter:

public abstract class RealmMultiAdapter<T extends RealmObject> extends BaseAdapter {

    private final RealmChangeListener<T> realmChangeListener = new RealmChangeListener<T>() {
        @Override
        public void onChange(RealmResults<T> t) {
            notifyDataSetChanged();
        }
    };

    protected LayoutInflater inflater;
    protected List<RealmResults<T>> realmResults;
    protected Context context;

    public RealmMultiAdapter(Context context, List<RealmResults<T>> realmResults, boolean automaticUpdate) {
        if (context == null) {
            throw new IllegalArgumentException("Context cannot be null");
        }
        this.context = context;
        this.realmResults = realmResults;
        this.inflater = LayoutInflater.from(context);
        for(RealmResults<T> results : realmResults) {
            results.addChangeListener(realmChangeListener);
        }
    }

    /**
     * Returns how many items are in the data set.
     *
     * @return count of items.
     */
    @Override
    public int getCount() {
        if (realmResults == null) {
            return 0;
        }
        int count = 0;
        for(RealmResults<T> realmResult : realmResults) {
            count += realmResult.size();
        }
        return count;
    }

    /**
     * Returns the item associated with the specified position.
     *
     * @param i index of item whose data we want.
     * @return the item at the specified position.
     */
    @Override
    public T getItem(int i) {
        if (realmResults == null || realmResults.size()==0) {
            return null;
        }
        int count = 0;
        for(RealmResults<T> realmResult : realmResults) {
            if(i<realmResult.size()+count) {
                return realmResult.get(i-count);
            }
            count += realmResult.size();
        }
        return null;
    }

    /**
     * Returns the current ID for an item. Note that item IDs are not stable so you cannot rely on the item ID being the
     * same after {@link #notifyDataSetChanged()} or {@link #updateRealmResults(List<RealmResults<T>>)} has been called.
     *
     * @param i index of item in the adapter.
     * @return current item ID.
     */
    @Override
    public long getItemId(int i) {
        // TODO: find better solution once we have unique IDs
        return i;
    }

    /**
     * Updates the RealmResults associated to the Adapter. Useful when the query has been changed.
     * If the query does not change you might consider using the automaticUpdate feature.
     *
     * @param queryResults the new RealmResults coming from the new query.
     */
    public void updateRealmResults(List<RealmResults<T>> queryResults) {
        for(RealmResults<T> results : realmResults) {
            if(results.isValid()) {             
                results.removeChangeListener(realmChangeListener);
            }
        }
        this.realmResults = queryResults;
        for(RealmResults<T> results : realmResults) {
            results.addChangeListener(realmChangeListener);
        }
        notifyDataSetChanged();
    }
}

Basically I replaced the single RealmResult with a list of RealmResults and modified the getItem() and getCount() method.

    // before
    protected RealmResults<T> realmResults;
    // after
    protected List<RealmResults<T>> realmResults;

And this is how I update the search

    List<RealmResults<Command>> results = new ArrayList<>();
    results.add(mRealm.where(Command.class).equalTo("name", query).findAll());
    results.add(mRealm.where(Command.class).beginsWith("name", query).notEqualTo("name", query).findAll());
    results.add(mRealm.where(Command.class).contains("name", query).not().beginsWith("name", query).notEqualTo("name", query).findAll());

    mAdapter.updateRealmResults(results);

Oracle ROWNUM performance

First of all, as mentioned in Alex's comment, I'm not sure that your second version is 100% guaranteed to give you the right rows -- since the "middle" block of the query does not have an explicit order by, Oracle is under no obligation to pass the rows up to the outer query block in any specific order. However, there doesn't seem to be any particular reason that it would change the order that the rows are passed up from the innermost block, so in practice it will probably work.

And this is why Oracle chooses a different plan for the second query -- it is logically not able to apply the STOPKEY operation to the innermost query block.

I think in the first case, the optimizer is assuming that id values are well-distributed and, for any given value, there are likely to be some very recent transactions. Since it can see that it only needs to find the 5 most recent matches, it calculates that it appears to be more efficient to scan the rows in descending order of paydate using an index, lookup the corresponding id and other data from the table, and stop when it's found the first 5 matches. I suspect that you would see very different performance for this query depending on the specific id value that you use -- if the id has a lot of recent activity, the rows should be found very quickly, but if it does not, the index scan may have to do a lot more work.

In the second case, I believe it's not able to apply the STOPKEY optimization to the innermost block due to the extra layer of nesting. In that case, the index full scan would become much less attractive, since it would always need to scan the entire index. Therefore it chooses to do an index lookup on id (I'm assuming) followed by an actual sort on the date. If the given id value matches a small subset of rows, this is likely to be more efficient -- but if you give an id that has lots of rows spread throughout the entire table, I would expect it to become slower, since it will have to access and sort many rows.

So, I would guess that your tests have used id value(s) that have relatively few rows which are not very recent. If this would be a typical use case, then the second query is probably better for you (again, with the caveat that I'm not sure it is technically guaranteed to produce the correct result set). But if typical values would be more likely to have many matching rows and/or more likely to have 5 very recent rows, then the first query and plan might be better.

Select Count(Col_Name) in Sqlite (Swift) Not Working