Why I use SQLite and FMDB without Core Data

Ext: http://segmentfault.com/a/1190000000363392 Editor's note: The article "I" refers to the original author.

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.

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 I use my own sequential dispatch queue.) Gus suggests looking at Fmdatabasequeue, which is also a continuous dispatch queue. I haven't been able to see it yet, because it's newer than anything else fmdb. ）

The calls to BeginTransaction and endtransaction are nested (in my database controller). They will call-[fmdatabase BeginTransaction] and-[fmdatabase commit] at the right time. (using transactions is the key to getting sqlite to go faster.) Tip: I store the current transaction in-[nsthread Threaddictionary]. It's good to get data for each thread, and I almost never use the other one.

Here's a simple example of a call to update a database:

- (void)emptyTagsLookupTableForNote:(VSNote *)note {
    NSString *uniqueID = note.uniqueID;
    [self runDatabaseBlockInTransaction:^(FMDatabase *database) {
        [database executeUpdate:
            @"delete from tagsNotesLookup where noteUniqueID = ?;", uniqueID];
    }];
}

This shows some things. First of all, SQL is not scary. Even if you've 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 In this case, I updated a lookup table. Notes and tags are many-to-many relationships, and one way to behave is to map note uniqueids and tag uniqueids with a database table. These tables are not difficult to maintain, but if possible, I do try to avoid their use.

Note In the update string?. -[fmdatabase executeupdate:] is a mutable parameter function. SQLite supports the use of placeholders, so you don't need to put a positive value into a string. Here's a security question: it helps the daemon against SQL insertions. If you need to avoid certain values, it also saves you trouble.

Finally, in the Tagsnoteslookup table, there is a Noteuniquelid index (index is another key to SQLite performance). This line of code is called at each startup:

[self.database executeUpdate:    @"CREATE INDEX if not exists noteUniqueIDIndex on tagsNotesLookup (noteUniqueID);"];

Database acquisition

To get an object, the app calls:

 
-[VSDatabaseController runFetchForClass:(Class)databaseObjectClass 
                             fetchBlock:(VSDatabaseFetchBlock)fetchBlock 
                      fetchResultsBlock:(VSDatabaseFetchResultsBlock)fetchResultsBlock];

These two lines of code do most of the work:

 
 
MResultSet *resultSet = fetchBlock(self.database);
NSArray *fetchedObjects = [self databaseObjectsWithResultSet:resultSet 
                                                       class:databaseObjectClass];

Returns a fmresultset with the Fmdb lookup database. With resultset you can step through the loop and create the model object.

I recommend writing generic code to convert a database row to an object. One way I use it is to map column names to object properties with a plist. It also contains the type, so you know if you need to call-[fmresultset Dateforcolumn:],-[fmresultset stringforcolumn:] or something else.

I have done some simple things in my latest application. The database row corresponds exactly to the name of the Model object property. All properties are strings except those whose names end with "Date". Very simple, but you can see that a clear correspondence is needed.

Unique objects

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, a nsmaptable, in the init function: _objectcache = [nsmaptable weaktoweakobjectsmaptable]. Let me explain:

For example, when you make a database fetch and transfer the object to a view controller, you want the view controller to finish using these objects, or if a different view controller is displayed, the objects can disappear.

If your object cache is a nsmutabledictionary, you will need to do some extra work to empty the objects in the cache. It is painful to determine whether the object it corresponds to has references elsewhere. Nsmaptable is a weak reference that will automatically handle this problem.

So: We make the object 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 it is not in the object cache, it will be added.

Keep objects in memory

There are many times when it makes sense to keep the entire object type in memory. My latest app has a Vstag object. Although there may be hundreds of notes, the number of tags is small, basically less than 10. A tag has only 6 attributes: 3 bool, two very small nsstring, and a nsdate.

When started, the app gets all tags and saves them in two dictionaries, one primary key is the tag's UniqueID, the other primary key is the lower case of the tag name.

This simplifies a lot of things, not just the tag auto-complete system, this can be completely in-memory operation, do not need database acquisition.

But many times it is impractical to keep all of the data in memory. For example, we don't keep all the 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 will do a fetch like this:

FMResultSet *resultSet = [self.database executeQuery:@"select uniqueID from some_table"];

ResultSet contains only uniqueids, and you can store it in a nsmutableset.

I find that sometimes this is useful for Web APIs. Imagine an API call that returns a uniqueids list of notes that have been created since a certain time. If I already have a nsmutableset that contains all the notes UniqueIDs, I can quickly check (through-[nsmutableset minusset]) If I have missed notes, and then go to another API to download those missing notes. These do not need to 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?

With SQLite and Fmdb instead of core Data, there's plenty of room for flexibility and smart solutions. Remember that sometimes it's good to be smart, and sometimes it's a big mistake to be smart.

Web APIs

My API calls in the background process (often with a nsoperationqueue, so I can cancel the operation). The model object is only in the main thread, but I also pass the model object to my API call.

This is true: a Database object has a Detachedcopy method that can replicate database objects. This replication object is not a reference to the object cache that is unique to the self. The only place to reference that object is the API call, and when the API call ends, the copied object disappears.

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

- (void)uploadNote:(VSNote *)note {
    VSNoteAPICall *apiCall = [[VSNoteAPICall alloc] initWithNote:[note detachedCopy]];
    [self enqueueAPICall:apiCall];
}

Vsnoteapicall gets the value from the copied Vsnote and creates an HTTP request instead of a dictionary or other representation of the note.

Working with Web API return values

I did something similar to the Web return value. I will create a model object for the JSON or XML that is returned, and this model object is also detached. It is not stored in the model cache for uniqueness.

There are some things in here that are uncertain. It is sometimes necessary to make local modifications in two places with that model object: in memory cache and database.

A database is usually an easy part. For example, my app already has a way to save a note object. It uses a SQL insert or replace string. I just need to call the note object generated from the Web API return value, and the database will be updated.

But maybe that object has an in-memory version, and fortunately we are easy to find:

VSNote *cachedNote = [self.mapTable objectForKey:downloadedNote.uniqueID];

If cachednote exists, I'll let it get the value from the downloadednote instead of replacing it (this might violate uniqueness). This can share the code of the Detachedcopy method.

Once Cachednote is updated, the Observer will notify the note through KVO, or I will send a nsnotification, or both.

Web API calls will also return some other values. I mentioned that RSS readers might get a large list of read entries. In this case, I used that list to create a nsset, update the Read property of each cached article in memory, and then call-[fmdatabase executeupdate:].

The key to making it work fast is that the nsmaptable search is fast. If the object you are looking for is in a nsarray, we should reconsider.

Database migration

The database migration of Core data is cool when it works.

But inevitably, it's a layer of code and a database. The more directly you use SQLite, the more direct you are to update the 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 in the first place of the code with the above code. Later changes only add executeupdate to the call-I let them execute sequentially. Because my database was designed by me, there was no problem (I never had a performance problem, it was fast).

Of course, big changes require more code. If your data gets through 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, it can also be very slow. It all depends on how you use it.

Transaction

Wrap the update in a transaction. Call-[fmdatabase BeginTransaction] before the update, call-[fmdatabase commit] after the update.

If you have to counter-normalize (denormalize)

Anti-normalization makes people very uncomfortable. This approach is to add redundant data for faster retrieval, but it means you need to maintain redundant data.

I always go crazy to avoid it until this can have serious performance differences. And I'll do as little as I can.

Working with Indexes

The tags table in my app creates statements like this:

CREATE TABLE if not exists tags (uniqueID TEXT UNIQUE, name TEXT, deleted INTEGER, deletedModificationDate DATE);

The UniqueID column is auto-indexed because it is defined as unique. But if I want to query the table with 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 paying attention to too many indexes can slow down your insertion speed. One needs only a sufficient quantity and is to those.

Apply using the command line

When my app runs in the emulator, I print the path to the database. I can open the database via the Sqlite3 command line. (Use the man sqlite3 command to learn more about this app).

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

When you open it, you can see Schema:type. Schema.

You can update and query, which is a good way to check whether SQL is correct before using your app.

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

A real example

My app displays a list of all tags that don't have an archived note. Whenever a note or tag changes, the query executes again, so it needs to be quick.

I can query with SQL join, but it is slow (joins is slow).

So I gave up sqlite3 and started experimenting with other methods. I looked at my schema again and realized that I could counter-normalize. The archive status of a note can be stored in the Notes table, and it can also be stored in the Tagsnoteslookup tables.

Then I can execute a query:

select distinct tagUniqueID from tagsNotesLookup where archived=0;

I already have an index on the Taguniqueid. So I use explain query plan to tell me what happens when I execute this query.

select distinct tagUniqueID from tagsNotesLookup where archived=0;0|0|0|SCAN TABLE tagsNotesLookup USING INDEX tagUniqueIDIndex (~100000 rows)

It uses an index, but the scan table doesn't sound good, preferably a search table and an index overlay.
I built the index on Taguniqueid and archive:

CREATE INDEX archivedTagUniqueID on tagsNotesLookup(archived, tagUniqueID);

Execute explain query plan again:

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

Somewhere in the Fmdb is the ability to cache statements, so when creating or opening a database, I always call [Self.database Setshouldcachestatements:yes]. This means that for each call you do not need to compile each statement again.

I have never found a good guideline to use vacuum, and if the database is not regularly compressed, it will be slower. My app will run a vacuum, but only once a week (it stores the last vacuum in the nsuserdefaults and then checks for a week at the beginning).
If can auto_vacuum that better, see pragma statements supported by SQLite list.

Other cool stuff.

Gus Mueller let me deal with the content of the custom SQLite method. I did not really use these things, since he pointed out, I can safely say I can find its usefulness. Because it's cool.

In Gus's post, there is a query like this:

select displayName, key from items where UTTypeConformsTo(uti, ?) order by 2;

SQLite is completely unaware of uitypes. But you can add the core method to view-[fmdatabase MakeFunctionNamed:maximumArguments:withBlock:].

You can perform a large query to replace and then evaluate each object. But that requires more work. It is best to filter at the SQL level, not after the table rows have been converted to objects.

At last

You really should use core Data, I'm not kidding.

I have been using SQLite and fmdb for some time, I am very excited about the benefits and get the unusual performance.
But remember that the machine is getting faster, other people looking at your code expect to see core Data that he already knows, and others are not going to look at your database code.
So please take this whole article as a madman's scream, a mad world about the details he built for himself, and lock himself in.

Please enjoy the great core data article (a little sad shaking your head).

Next, after checking out the characteristics of the custom SQLite method indicated by Gus, I will study SQLite's Full-text search extension. There is always more content to learn.

Why I use SQLite and FMDB without Core Data