Why I use SQLite and FMDB without Core Data

In my conscience, I can't tell you not to use core Data. It's good, and it's getting better, and it's been understood by many other cocoa developers that it's important to have new people join your group or need someone to take over your project.
More importantly, it is not worth the time and effort to write your own system to replace it. Really, use core data.

This reprint of the Foreigner's thoughts

Why don't I use core Data

Mike Ash writes:

As far as I'm concerned, I'm not a fan. I found the API to be clumsy, and the framework itself was extremely slow for a lot of data.

A practical example: 10,000 articles

Imagine an RSS reader, where a user can right-click on a feed and select Mark all as read.

Under the engine, there is a article entity with the Read property. Mark all entries as read, the program needs to load all the articles of this feed (possibly through a one-to-many relationship), and then set the Read property to Yes.

In most cases it doesn't matter. But imagine that there are 200 articles in that feed, and to avoid blocking the main thread, you might consider doing the work in a background thread (especially if your app is an iphone application). When you start using core Data Multi-threading, things start to turn bad.

This may not be enough, at least not worth switching to core Data.

But then sync.

I have used two different RSS synchronization interfaces to get a list of read article IDs. One of them returns nearly 10,000 IDs.

You are not going to load 10,000 articles in the main thread, and then set read to No. You don't even want to download 10,000 articles in the background line bunch, even if you're careful with managing the memory, there's too much work (if you do this frequently, think about the impact on battery life).

What you really want to do is let the database set read to Yes for each article in the ID list.

SQLite can do this, using only one call. Suppose there is an index on the UniqueID, which will be quick. And you can do it in the background thread as easily as you do in the main threads.

Another example: Quick Start

I want to reduce the start time of my other program, not just the start time, but all the time before the data is displayed.

It's a Twitter-like app (though it's not), it shows the timeline of the message. Displaying the timeline means getting the message and loading the relevant user. It is fast, but when it starts, it fills the UI and populates the data.

About iphone Apps (or all apps) My theory is that startup time is important, more important than most other developers think. The app's startup is slow and doesn't look like it's going to start, because people subconsciously remember it and produce ideas that prevent it from starting. Reducing the start-up time reduces friction, making it more likely that users will continue to use your app and recommend it to others. This is part of the success you make your app.

Because I don't use core Data, I have a simple, conservative solution handy. I saved timeline (message and character objects) through nscoding to a plist file. When it starts, it reads the file, creates the message and the character object, and displays the timeline as soon as the UI appears.

This significantly reduces the delay.

It is not possible to use the message and the character object as an instance object of Nsmanagedobject. (Suppose I have an IDs object encoded and stored, but that means read plist and then touches the database.) This way I completely avoided the database).

I removed the code after the newer, faster machine came out. Looking back, I wish I could keep it.

How do I think about this?

When considering whether to use core data, I consider these things:

Will there be an incredible amount of data?

For an RSS reader or Twitter app, the answer is obvious: yes. Some people focus on hundreds of people. One person may have subscribed to thousands of feeds.

Even if your app doesn't get data from the network, it's still possible for users to automatically add data. If you use a Mac that supports AppleScript, some people will write scripts to load very much data. The same is true if you are using the Web API to add data.

Will there be a Web API that contains a database-like terminal (compared to a Class object terminal)?

An RSS synchronization API is able to return a list of uniquelids for a read article. A sync API for a note-taking app may return archived and deleted notes Uniquelids.

Can a user handle a large number of objects through operations?

At the bottom, you need to consider the same issues as before. When someone deletes all the downloaded 5,000 pasta recipes, how good can your recipe app be to complete this feature (on iphone?). ）？

When I decided to use core data (I've already published an app that uses core data), I'll watch out for how I use it. To get good performance, I found that I used it as a strange interface for a SQL database, and then I knew that I should discard core Data and use SQLite directly.

How do I use SQLite

I use Sqlite,fmdb from flying meat software via Fmdb Wrapper, provided by Gus Mueller.

Basic operations

Before I was on the iphone, Core data used SQLite before. This is the point of how it works:

• All database access-read and write-occurs in a sequential queue, in a background thread. Touching the database in the main thread is never allowed. Use a continuous queue to ensure that everything happens sequentially.
• I use blocks extensively to make asynchronous programs easier.
• The model object exists only in the main thread (but with two important exceptions) and the change triggers a background save.
• Model Objects list the properties that they store in the database. It may be in the code or in the Plist file.
• Some model objects are unique and some are not. Depends on the needs of the application (most of the cases are unique).
• For relational data, I avoid even table queries whenever possible.
• Some object types are fully read into memory when they are started, and other object types may only need to create and maintain one of their uniqueids. Nsmutableset, so I don't have to touch the database, I know what it is.
• Calls to the Web API occur in a background thread, and they use separate model objects.

I will describe it in detail through the code of my current application.

Database updates

In my recent application, there is a single database controller-vsdatabasecontroller, which uses Fmdb to talk to SQLite.

Fmdb distinguish between updates and queries. To update the database, the app calls:

-[VSDatabaseController runDatabaseBlockInTransaction: (VSDatabaseUpdateBlock) databaseBlock]
VSDatabaseUpdateBlock is simple:

typedef void (^ VSDatabaseUpdateBlock) (FMDatabase * database);
runDatabaseBlockInTransaction is also very simple:

-(void) runDatabaseBlockInTransaction: (VSDatabaseUpdateBlock) databaseBlock {
    dispatch_async (self.serialDispatchQueue, ^ {
        @autoreleasepool {
            [self beginTransaction];
            databaseBlock (self.database);
            [self endTransaction];
        }
    });
}
(Note that I use my own continuous dispatch queue. Gus suggests taking a look at FMDatabaseQueue, which is also a continuous dispatch queue. I haven't been able to look at it because it is newer than everything else in FMDB.)

The beginTransaction and endTransaction calls are nestable (in my database controller). They call-[FMDatabase beginTransaction] and-[FMDatabase commit] when appropriate. (Using transactions is the key to making SQLite faster.) Hint: I store the current transaction in-[NSThread threadDictionary]. It's good at getting data for each thread, and I almost never use anything else.

Here is a simple example of calling update database:

-(void) emptyTagsLookupTableForNote: (VSNote *) note {
    NSString * uniqueID = note.uniqueID;
    [self runDatabaseBlockInTransaction: ^ (FMDatabase * database) {
        [database executeUpdate:
            @ "delete from tagsNotesLookup where noteUniqueID =?;", uniqueID];
    }];
}
This illustrates something. First of all SQL is not scary. Even if you have never seen it before, you know what this line of code does.

Like all other public interfaces of VSDatabaseController, emptyTagsLookupTableForNote should be called in the main thread. Model objects can only be referenced in the main thread, so use uniqueID instead of VSNote objects in the block.

Note that in this case, I updated a lookup table. Notes and tags are a many-to-many relationship. One way to do this is to map note uniqueIDs and tag uniqueIDs with a database table. These tables are not difficult to maintain, but I do try to avoid their use if possible.

Note the? In the update string. -[FMDatabase executeUpdate:] is a variadic function. SQLite supports the use of the placeholder?, So you don't need to put true values into strings. There is a security issue here: it helps the daemons against SQL inserts. It also saves you trouble if you need to avoid certain values.

Finally, in the tagsNotesLookup table, there is an index of noteUniquelID (the index is another key to SQLite performance). This line of code is called every time it starts:

[self.database executeUpdate:
    @ "CREATE INDEX if not exists noteUniqueIDIndex on tagsNotesLookup (noteUniqueID);"];
Database acquisition
To get the object, the app calls:

-[VSDatabaseController runFetchForClass: (Class) databaseObjectClass
                             fetchBlock: (VSDatabaseFetchBlock) fetchBlock
                      fetchResultsBlock: (VSDatabaseFetchResultsBlock) fetchResultsBlock];
These two lines of code do most of the work:

FMResultSet * resultSet = fetchBlock (self.database);
NSArray * fetchedObjects = [self databaseObjectsWithResultSet: resultSet
                                                       class: databaseObjectClass];
Use FMDB to search the database and return an FMResultSet. With resultSet you can loop through the sentences and create model objects.

I recommend writing generic code to convert database rows to objects. One method I use is to use a plist to map column names to object attributes. It also contains types, so you know if you need to call-[FMResultSet dateForColumn:],-[FMResultSet stringForColumn:] or whatever.

I did some simple things in my latest application. The database row exactly corresponds to the name of the model object property. All attributes are strings, except for those whose names end with "Date". It's simple, but you can see that a clear correspondence is needed.

Unique object
Create model objects and get data from the database in the same background thread. Once acquired, the program will transfer them to the main thread.

Usually I have uniqued objects. The same database row result always corresponds to the same object.

To be unique, I created an object cache, an NSMapTable, in the init function: _objectCache = [NSMapTable weakToWeakObjectsMapTable]. Let me explain:

For example, when you do a database fetch and transfer the object to a view controller, you want these objects to disappear after the view controller has finished using the objects, or a different view controller is displayed.

If your object cache is an NSMutableDictionary, you will need to do some extra work to empty the objects in the cache. It is painful to determine if its corresponding object has a reference elsewhere. NSMapTable is a weak reference and will handle this automatically.

So: we make objects unique in the main thread. If an object already exists in the object cache, we use that existing object. (The main thread wins because it may have new changes.) If not in the object cache, it will be added.

Keep objects in memory
Many times, it makes sense to keep the entire object type in memory. My latest app has a VSTag object. Although there may be hundreds or thousands of notes, the number of tags is small, basically less than 10. A tag has only six attributes: three BOOLs, two small NSstrings, and one NSDate.

At startup, the app fetches all tags and saves them in two dictionaries. One is the uniqueID of the tag, and the other is the lowercase tag name.

This simplifies a lot of things, not just the tag auto-completion system, which can be operated entirely in memory, and does not require database acquisition.

But many times, it is impractical to keep all the data in memory. For example, we don't keep all notes in memory.

But there are many times when you can't keep objects in memory, you want to keep all uniqueIDs in memory. You would get one like this:

FMResultSet * resultSet = [self.database executeQuery: @ "select uniqueID from some_table"];
The resultSet contains only uniqueIDs, which you can store into an NSMutableSet.

I find this sometimes useful for web APIs. Imagine an API call that returns a list of uniqueIDs of a created note after a certain time. If I already have an NSMutableSet containing all the uniqueIDs of the notes, I can quickly check (via-[NSMutableSet minusSet]) whether there are missing notes and then call another API to download those missing notes. These need not touch the database at all.

However, things like this should be handled with care. Can the app provide enough memory? Does it really simplify programming and improve performance?

Using SQLite and FMDB instead of Core Data gives you a lot of room for flexibility and smart solutions. Remember that sometimes being smart is good, and sometimes being smart is a big mistake.

Web APIs
My API calls are in a background process (often using an NSOperationQueue so I can cancel the operation). Model objects are only on the main thread, but I also pass model objects to my API calls.

This is it: a database object has a detachedCopy method, which can copy the database object. This duplicate object is not a reference to the object cache used to uniqueize it. The only place to refer to that object is the API call. When the API call ends, the copied object disappears.

This is a good system because it means that I can use model objects in API calls. The method looks like this:

-(void) uploadNote: (VSNote *) note {
    VSNoteAPICall * apiCall = [[VSNoteAPICall alloc] initWithNote: [note detachedCopy]];
    [self enqueueAPICall: apiCall];
}
VSNoteAPICall takes the value from the copied VSNote and creates an HTTP request instead of a dictionary or other representation of the note.

Handling Web API return values
I did something similar for web return values. I will create a model object for the returned JSON or XML, and this model object is also separated. It is not stored in the model cache for uniqueness.

Some things are uncertain here. Sometimes it is necessary to use that model object to make local modifications in two places: in the memory cache and the database.

The database is usually the easy part. For example: My app already has a method to save note objects. It uses a SQL insert or replace string. I just call the note object generated from the web API return value and the database is updated.

But maybe there is an in-memory version of that object, fortunately we can easily find it:

VSNote * cachedNote = [self.mapTable objectForKey: downloadedNote.uniqueID];
If cachedNote exists, I will let it get the value from downloadedNote instead of replacing it (which may violate uniqueness). This can share the code of the detachedCopy method.

Once cachedNote is updated, the observer will notify the note via KVO, or I will send an NSNotification, or both.

Web API calls also return some other values. I mentioned that an RSS reader might get a large list of read entries. In this case, I created an NSSet with that list, updated the read attribute of each cached article in memory, and then called-[FMDatabase executeUpdate:].

The key to making it work fast is that NSMapTable lookups are fast. If the object you are looking for is in an NSArray, we should reconsider.

Database migration
Core Data's database migration is cool when it works.

But inevitably, it is a layer in code and database. The more directly you use SQLite, the more directly you can update your database.

You can do this safely and easily.

For example, add a table:

[self.database executeUpdate: @ "CREATE TABLE if not exists tags"
    "(uniqueID TEXT UNIQUE, name TEXT, deleted INTEGER, deletedModificationDate DATE); "];
Or add an index:

[self.database executeUpdate: @ "CREATE INDEX if not exists"
    "archivedSortDateIndex on notes (archived, sortDate);"];
Or add a column:

[self.database executeUpdate: @ "ALTER TABLE tags ADD deletedDate DATE"];
The application should set up the database with the above code in the first place of the code. Future changes just need to add executeUpdate calls — I let them execute in order. Because my database is designed by me, there will be no problems (I have never encountered performance problems, it is fast).

Of course big changes require more code. If your data is obtained via the web, sometimes you can start with a new database model and re-download the data you need.

Performance tips
SQLite can be very, very fast, and it can also be very slow. It all depends on how you use it.

Transaction
Wrap updates in transactions. -[FMDatabase beginTransaction] is called before the update, and-[FMDatabase commit] is called after the update.

If you have to denormalize
Denormalization is unpleasant. This method is to add redundant data for faster retrieval, but it means that you need to maintain redundant data.

I'm crazy to avoid it until it makes a serious performance difference. Then I will do as little as possible.

Using the index
The creation statement of the tags table in my application looks like this:

CREATE TABLE if not exists tags
  (uniqueID TEXT UNIQUE, name TEXT, deleted INTEGER, deletedModificationDate DATE);
The uniqueID column is automatically indexed because it is defined as unique. But if I want to query the table by name, I might create an index on name like this:

CREATE INDEX if not exists tagNameIndex on tags (name);
You can create indexes on multiple columns at once, like this:

CREATE INDEX if not exists archivedSortDateIndex on notes (archived, sortDate);
But note that too many indexes will slow down your inserts. You only need a sufficient number and those that are right.

Using the command line application
When my app is running in the simulator, I will print the path to the database. I can open the database from the command line of sqlite3. (Learn more about this application with the man sqlite3 command).

Command to open the database: sqlite3 "path to database".

After opening, you can see the schema: type .schema.

You can update and query, this is a good way to check if the SQL is correct before using your app.

The coolest part of this is the SQLite Explain Query Plan command, you will want to make sure your statements execute as fast as possible.

Real example
My app displays a list of all tags without archived notes. This query is re-executed every time a note or label changes, so it needs to be fast.

I can use SQL join to query, but it is very slow (joins are very slow).

So I gave up sqlite3 and started trying other methods. I looked at my schema again and realized that I could denormalize it. The archive status of a note can be stored in the notes table, or it can be stored in the tagsNotesLookup table.

I can then execute a query:

select distinct tagUniqueID from tagsNotesLookup where archived = 0;
I already have an index on tagUniqueID. So I use explain query plan to tell me what happens when I execute this query.

sqlite> explain query plan select distinct tagUniqueID from tagsNotesLookup where archived = 0;
0 | 0 | 0 | SCAN TABLE tagsNotesLookup USING INDEX tagUniqueIDIndex (~ 100000 rows)
It uses an index, but SCAN TABLE doesn't sound very good, it is better to have a SEARCH TABLE and cover an index.
I indexed on tagUniqueID and archive:

CREATE INDEX archivedTagUniqueID on tagsNotesLookup (archived, tagUniqueID);
Execute explain query plan again:

sqlite> explain query plan select distinct tagUniqueID from tagsNotesLookup where archived = 0;
0 | 0 | 0 | SEARCH TABLE tagsNotesLookup USING COVERING INDEX archivedTagUniqueID (archived =?) (~ 10 rows)
much better.

More performance tips
FMDB adds the ability to cache statements somewhere, so when creating or opening a database, I always call [self.database setShouldCacheStatements: YES]. This means that you don't need to compile every statement again for each call.

I have never found good guidelines for using vacuum, if the database is not regularly compressed, it will become slower and slower. My application runs a vacuum, but only once a week (it stores the last vacuum time in NSUserDefaults, and then checks to see if it has passed a week at the beginning).
If auto_vacuum is better, see the pragma statements supported by SQLite list.

Other cool stuff
Gus Mueller got me involved with custom SQLite methods. I didn't really use these things. Since he pointed out, I can safely say that I can find its usefulness. Because it's cool.

In Gus's post click preview, a query looks like this:

select displayName, key from items where UTTypeConformsTo (uti,?) order by 2;
SQLite has no idea about UITypes. But you can add core methods, see-[FMDatabase makeFunctionNamed: maximumArguments: withBlock:].

You can execute a large query instead, and then evaluate each object. But that requires more work. It is best to filter at the SQL level, not after converting table rows to objects.

At last
You really should use Core Data, I'm not kidding.

I've been using SQLite and FMDB for a while, and I'm excited about the benefits and getting extraordinary performance.
But remember that the machine is getting faster. Others who look at your code expect to see Core Data he already knows, and others don't plan to look at your database code.
So please take this entire article as a madman shouting about the crazy world of the details he has built for himself and lock yourself in.

Please enjoy the amazing Core Data article (a bit sad shaking your head).

Next, after checking the custom SQLite method features pointed out by Gus, I will study SQLite's full-text search extension. There is always more to learn.

Why I use SQLite and FMDB instead of Core Data