iOS
Caching Data Advanced

Database caching: Advanced

This page shows you how to build a local cache using a database. This has several advantages like storing raw data in a file and enabling paricular queries on stored channels and messages. The content is provided on the assumption that you use SQLite database as a local storage, you can easily follow these steps with any other database like Realm.


Serialize and deserialize SendBird objects

In order to enable you to store SendBird objects such as messages, channels, and users in a local storage, we provide serialization and deserialization methods through our SDK.

By using the serialize method to convert a SendBird object to binary data like the following, you can save the binary data natively in your persistent database.

Objective-C
Swift
Light Color Skin
Copy
// In SBDBaseMessage.h
- (nullable NSData *)serialize;
+ (nullable instancetype)buildFromSerializedData:(NSData * _Nonnull)data;
Light Color Skin
Copy
// In SBDBaseMessage.h
func serialize() -> Data?
class func build(fromSerializedData data: Data) -> Self?

Save and load messages with serialization and deserialization

Your database should have a table to save and load messages, which has columns corresponding to the properties of a message object.

Design a message table

A basic table to store messages contains the following columns:

message_idchannel_urlmessage_tspayload

22312323345

sendbird_channel_2348023

1432039402493

Serialized data

23623445361

sendbird_channel_2348023

1432039403243

Serialized data

Caching procedures

  1. By using one of the getNextMessagesByTimestamp:limit: ... :, getPreviousMessagesByTimestamp:limit: ... :, or getPreviousAndNextMessagesByTimestamp:prevLimit:nextLimit: ... : methods, you can fetch new messages in a channel. With the returned messages, serialize and insert each message of them into your database. However, we recommend storing the message ID, timestamp, and channel URL in separate columns using message.messageId, message.createdAt, and message.channelUrl. This allows you to query the data on a row-by-row basis later on.
  2. Before loading messages within a channel, sort rows in the chronological order by message_ts. Then, deserialize each message and display them in your UI.
  3. When loading previous messages that are not currently stored in the local database, obtain the timestamp of the earliest stored message. Then, query for messages created before that value.
  4. Likewise, when loading new messages, query for messages with a later timestamp than the most recent message.

Note: Because messages in a channel can be updated or deleted, to keep your local database synced with data in SendBird server, your client app should check changes to the messages regularly and apply them to the database. You can retrieve change logs of messages in a channel with the getMessageChangeLogsWithToken:completionHandler: or getMessageChangeLogsByTimestamp:completionHandler: method, with which you can manage your local database updates.

Objective-C
Swift
Light Color Skin
Copy
// Example 1: When joining a channel

sqlite3 *contactDB;
char *dbpath = "<DATABASE_PATH>";
char *query;
sqlite3_stmt *stmt;

// Loading messages from your local database.
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
    if (order) {
        query = "SELECT * FROM (SELECT * FROM MESSAGE WHERE COLUMN_NAME_CHANNEL_URL = ? AND COLUMN_NAME_TIMESTAMP < ? ORDER BY COLUMN_NAME_TIMESTAMP DESC LIMIT ?) ORDER BY COLUMN_NAME_TIMESTAMP ASC";
    }
    else {
        query = "SELECT * FROM (SELECT * FROM MESSAGE WHERE COLUMN_NAME_CHANNEL_URL = ? AND COLUMN_NAME_TIMESTAMP < ? ORDER BY COLUMN_NAME_TIMESTAMP DESC LIMIT ?) ORDER BY COLUMN_NAME_TIMESTAMP DESC";
    }

    if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [@"<CHANNEL_URL>" UTF8String], -1, SQLITE_TRANSIENT); // COLUMN_NAME_CHANNEL_URL
        sqlite3_bind_int64(stmt, 2, timestamp); // COLUMN_NAME_TIMESTAMP
        sqlite3_bind_int64(stmt, 3, limit);     // COLUMN_NAME_TIMESTAMP

        // Create a list of `SBDBaseMessage` objects by deserializing each.
        NSMutableArray<SBDBaseMessage *> *prevMessageList = [[NSMutableArray alloc] init];

        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const char *payload = sqlite3_column_blob(stmt, 4);

            int size = sqlite3_column_bytes(stmt, 4);
            data = [[NSData alloc] initWithBytes:payload length:size];

            SBDBaseMessage *message = [SBDBaseMessage buildFromSerializedData:data];
            [prevMessageList addObject:message];
        }
        sqlite3_finalize(stmt);

        // Pass messages to data source for displaying them in a UITableView, UICollectionView, etc.
        [self.messageList addObjects:prevMessageList];

        // Get new messages from SendBird server.
        long latestStoredTs = prevMessageList[0].createdAt; // Get the timestamp of the last stored message.

        [self.channel getNextMessagesByTimestamp:latestStoredTs limit:30 reverse:NO completionHandler:^(NSArray<SBDBaseMessage *> * _Nullable messages, SBDError * _Nullable error) {
            if (error != nil) {
                // Error.
                return;
            }

            // New messages successfully fetched.
            [self.messageList addObjects:messages];

            // Insert each new message into your local database.
            const char *query = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)";

            for (SBDBaseMessage *message in messages) {
                // Store each new message in the local database.
                if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                    sqlite3_bind_int64(stmt, 1, message.messageId); // message_id
                    sqlite3_bind_text(stmt, 2, [message.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
                    sqlite3_bind_int64(stmt, 3, message.createdAt); // message_ts

                    NSData *blob = [message serialize];
                    sqlite3_bind_blob(stmt, 4, [blob bytes], [blob length], SQLITE_TRANSIENT);
                }

                if (sqlite3_step(stmt) != SQLITE_DONE) {
                    // Error.
                }

                sqlite3_finalize(stmt);
            }
        }];
    }

    sqlite3_finalize(stmt);
}

sqlite3_close(contactDB);
Light Color Skin
Copy
// Example 1: When joining a channel

var dbpath: String = "<DATABASE_PATH>"
var contactDB: OpaquePointer? = nil
var statement: OpaquePointer? = nil

// Loading messages from your local database.
if sqlite3_open(dbpath, &contactDB) == SQLITE_OK {
    let query: String = "SELECT * FROM (SELECT * FROM MESSAGE WHERE COLUMN_NAME_CHANNEL_URL = ? AND COLUMN_NAME_TIMESTAMP < ? ORDER BY COLUMN_NAME_TIMESTAMP DESC LIMIT ?) ORDER BY COLUMN_NAME_TIMESTAMP ASC"

    if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
        sqlite3_bind_text(statement, 1, "<CHANNEL_URL>".UTF8String, -1, SQLITE_TRANSIENT) // COLUMN_NAME_CHANNEL_URL
        sqlite3_bind_int64(statement, 2, timestamp) // COLUMN_NAME_TIMESTAMP
        sqlite3_bind_int64(statement, 3, limit)     // COLUMN_NAME_TIMESTAMP

        // Create a list of 'SBDBaseMessage' objects by deserializing each.
        var messageList: [SBDBaseMessage] = []
        while (sqlite3_step(statement) == SQLITE_ROW) {
            guard let payload = sqlite3_column_blob(statement, 4) else { return }
            let size = sqlite3_column_bytes(statement, 4)
            let data: Data = Data.init(bytes: payload, count: Int(size))
            let message: SBDBaseMessage? = SBDBaseMessage.build(fromSerializedData: data)
            if let theMessage: SBDBaseMessage = message {
                messageList.append(theMessage)
            }
        }

        sqlite3_finalize(statement)

        // Pass messages to data source for displaying them in a UITableView, UICollectionView, etc.
        self.messages.append(messageList)

        // Get new messages from SendBird server
        let latestStoredTimestamp: Int64  = messageList.first?.createdAt ?? LONG_LONG_MAX // Get the timestamp of the last stored message.

        channel.getPreviousMessages(byTimestamp: latestStoredTimestamp, limit: 30, reverse: false) { (messages, error) in
            guard let _: Error = error else {
                // Error.
                return
            }

            // New messages successfully fetched.
            self.messages.append(messages)

            // Insert each new message into your local database.
            let query: String = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)"

            for message in messages ?? [SBDBaseMessage] {
                // Store each new message in the local database.
                if (sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK) {
                    sqlite3_bind_int64(statement, 1, message.messageId) // message_id
                    sqlite3_bind_text(statement, 2, message.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url
                    sqlite3_bind_int64(statement, 3, message.createdAt) // message_ts

                    let blob: Data? = message.serialize()
                    sqlite3_bind_blob(statement, 4, blob?.bytes, blob?.length, SQLITE_TRANSIENT)
                }

                if (sqlite3_step(statement) != SQLITE_DONE) {
                    // Error.
                }

                sqlite3_finalize(statement)
            }
        }
    }

    sqlite3_finalize(statement)
}

sqlite3_close(contactDB)
Objective-C
Swift
Light Color Skin
Copy
// Example 2: When receiving a new message

- (void)channel:(SBDBaseChannel * _Nonnull)sender didReceiveMessage:(SBDBaseMessage * _Nonnull)message {
    if (sender == self.channel) {
        // Pass the message to your data source for UITableView or UICollectionView.
        [self.messageList addObject:message];

        // Store the message in your local database.
        // It is a good idea to have a helper class or method for database transactions.
        sqlite3 *contactDB;
        char *dbpath = "<DATABASE_PATH>";
        char *query;
        sqlite3_stmt *stmt;

        if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
            // Insert each new message into your local database.
            const char *query = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)";

            // Store each new message in the local database.
            if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                sqlite3_bind_int64(stmt, 1, message.messageId); // message_id
                sqlite3_bind_text(stmt, 2, [message.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
                sqlite3_bind_int64(stmt, 3, message.createdAt); // message_ts

                NSData *blob = [message serialize];
                sqlite3_bind_blob(stmt, 4, [blob bytes], [blob length], SQLITE_TRANSIENT);
            }

            if (sqlite3_step(stmt) != SQLITE_DONE) {
                // Error.
            }

            sqlite3_finalize(stmt);
        }

        sqlite3_close(contactDB);
    }
}
Light Color Skin
Copy
// Example 2: When receiving a new message

func channel(_ sender: SBDBaseChannel, didReceive message: SBDBaseMessage) {
    guard let channel: SBDGroupChannel = sender else {
        return
    }

    // Pass the message to your data source for UITableView or UICollectionView.
    self.messages.append(message)

    // Store the message in your local database.
    // It is a good idea to have a helper class or method for database transactions.
    var dbpath: String = "<DATABASE_PATH>"
    var contactDB: OpaquePointer? = nil
    var statement: OpaquePointer? = nil

    if sqlite3_open(dbpath, &contactDB) == SQLITE_OK {
        // Insert each new message into your local database.
        let query: String = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)"

        // Store each new message in the local database.
        if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_int64(statement, 1, message.messageId) // message_id
            sqlite3_bind_text(statement, 2, message.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url
            sqlite3_bind_int64(statement, 3, message.createdAt) // message_ts

            let blob: Data? = message.serialize()
            sqlite3_bind_blob(statement, 4, blob?.bytes, blob?.length, SQLITE_TRANSIENT)
        }
    
        if (sqlite3_step(statement) != SQLITE_DONE) {
            // Error.
        }

        sqlite3_finalize(statement)
    }

    sqlite3_close(contactDB)
}
Objective-C
Swift
Light Color Skin
Copy
// Example 3: When sending a message

[self.channel sendUserMessage:messageBody completionHandler:^(SBDUserMessage * _Nullable userMessage, SBDError * _Nullable error) {
    if (error != nil) {
        // Error.
        return;
    }

    // Pass the message to your data source for UITableView or UICollectionView.
    [self.messageList addObject:message];

    // Store the message in your local database.
    // It is a good idea to have a helper class or method for database transactions.
    sqlite3 *contactDB;
    char *dbpath = "<DATABASE_PATH>";
    char *query;
    sqlite3_stmt *stmt;

    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
        // Insert each new message into your local database.
        const char *query = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)";

        // Store each new message in the local database.
        if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
            sqlite3_bind_int64(stmt, 1, message.messageId); // message_id
            sqlite3_bind_text(stmt, 2, [message.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
            sqlite3_bind_int64(stmt, 3, message.createdAt); // message_ts

            NSData *blob = [message serialize];
            sqlite3_bind_blob(stmt, 4, [blob bytes], [blob length], SQLITE_TRANSIENT);
        }

        if (sqlite3_step(stmt) != SQLITE_DONE) {
            // Error.
        }
        
        sqlite3_finalize(stmt);
    }

    sqlite3_close(contactDB);
}];
Light Color Skin
Copy
// Example 3: When sending a message

channel.sendUserMessage(text, completionHandler: { (userMessage, error) in
    guard let message: SBDUserMessage = userMessage, error == nil else {
        return
    }

    // Pass the message to your data source for UITableView or UICollectionView.
    self.messages.append(message)

    // Store the message in your local database.
    // It is a good idea to have a helper class or method for database transactions.
    var dbpath: String = "<DATABASE_PATH>"
    var contactDB: OpaquePointer? = nil
    var statement: OpaquePointer? = nil

    if sqlite3_open(dbpath, &contactDB) == SQLITE_OK {
        // Insert each new message into your local database.
        let query: String = "INSERT INTO MESSAGE (message_id, channel_url, message_ts, payload) VALUES (?, ?, ?, ?)"

        // Store each new message in the local database.
        if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_int64(statement, 1, message.messageId) // message_id
            sqlite3_bind_text(statement, 2, message.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url
            sqlite3_bind_int64(statement, 3, message.createdAt) // message_ts

            let blob: Data? = message.serialize()
            sqlite3_bind_blob(statement, 4, blob?.bytes, blob?.length, SQLITE_TRANSIENT)
        }

        if (sqlite3_step(statement) != SQLITE_DONE) {
            // Error.
        }

        sqlite3_finalize(statement)
    }
})

Caveats

Currently, it is difficult to sync deleted or edited messages. We are working to provide this feature in both our SDKs and APIs, and hope to release it soon.


Save and load channels with serialization and deserialization

Your database should have a table to save and load channels, which has columns corresponding to the properties of a channel object.

Note: The following examples are based on a group channel. To cache an open channel, slightly improvise from the directions below (such as changing last_message_ts to channel_created_at).

Design a channel table

A basic table to store channels contains the following columns:

channel_urllast_message_tspayload

sendbird_channel_2348023

1432039402729

Serialized data

sendbird_channel_2348023

1432039403448

Serialized data

Caching procedures

  1. After fetching new channels by using a SBDOpenChannelListQuery or SBDGroupChannelListQuery, serialize and insert each channel into your database. We recommend storing the channel URL and timestamp of the last message in separate columns by using channel.channelUrl and channel.lastMessage.createdAt. This allows you to query the data on a row-by-row basis later on.
  2. Before loading a list of channels, sort rows in the chronological order by last_message_ts. Then, deserialize each channel and display them in your UI.
  3. Unlike messages, channels are relatively few in number and go through frequent property changes, such as cover URL and name changes, or their deletions. We recommend updating a cache by completely replacing with new data when possible.
  4. When real-time changes are made to a channel list, update your cache.

Note: In group channels, information associated with them can be updated or the current user might leave them anytime. To keep your local database synced with SendBird server, your client app should check information about changes to the channels regularly and apply the changes to the database. You can retrieve change logs of the current user's group channels by using the getMyGrouhannelChangeLogsByToken:customTypes:completionHandler: or getMyGroupChannelChangeLogsByTimestamp:customTypes:completionHandler: method, with which you can manage your local database updates.

Objective-C
Swift
Light Color Skin
Copy
// Example 1: When displaying a list of channels

sqlite3 *contactDB;
char *dbpath = "<DATABASE_PATH>";
char *query;
sqlite3_stmt *stmt;

// Loading channels from your local database.
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
    if (order) {
        query = "SELECT * FROM CHANNEL ORDER BY COLUMN_NAME_LAST_MESSAGE_TIMESTAMP ASC";
    }
    else {
        query = "SELECT * FROM CHANNEL ORDER BY COLUMN_NAME_LAST_MESSAGE_TIMESTAMP DESC";
    }

    if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
        // Create a list of `SBDBaseChannel` objects by deserializing each.
        NSMutableArray<SBDBaseChannel *> *prevChannelList = [[NSMutableArray alloc] init];

        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const char *payload = sqlite3_column_blob(stmt, 3);

            int size = sqlite3_column_bytes(stmt, 3);
            data = [[NSData alloc] initWithBytes:payload length:size];

            SBDBaseChannel *channel = [SBDBaseChannel buildFromSerializedData:data];
            [prevChannelList addObject:channel];
        }
        sqlite3_finalize(stmt);

        sqlite3_close(contactDB);

        // Pass messages to data source for displaying them in a UITableView, UICollectionView, etc.
        [self.channelList addObjects:prevChannelList];

        // Get new channels from SendBird server.
        SBDGroupChannelListQuery *query = [SBDGroupChannel createMyGroupChannelListQuery];
        [query loadNextPageWithCompletionHandler:^(NSArray<SBDGroupChannel *> * _Nullable channels, SBDError * _Nullable error) {
            if (error != nil) {
                // Error.
                return;
            }

            // Replace the current (cached) data set.
            [self.channelList removeAllObjects];
            [self.channels addObjects:channels];

            sqlite3 *contactDB;
            char *dbpath = "<DATABASE_PATH>";
            char *query;
            sqlite3_stmt *stmt;

            // Clear the current cache.

            if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
                // Insert each new channel into your local database.
                const char *query = "INSERT INTO CHANNEL (channel_url, last_message_ts, payload) VALUES (?, ?, ?)";
                for (SBDGroupChannel *channel in channels) {
                    // Store each new channel in the local database.
                    if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                        sqlite3_bind_text(stmt, 1, [channel.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
                        sqlite3_bind_int64(stmt, 2, channel.lastMessage.createdAt); // last_message_ts

                        NSData *blob = [channel serialize];
                        sqlite3_bind_blob(stmt, 3, [blob bytes], [blob length], SQLITE_TRANSIENT);
                    }
                    
                    if (sqlite3_step(stmt) != SQLITE_DONE) {
                        // Error.
                    }
    
                    sqlite3_finalize(stmt);
                }
            }

            sqlite3_close(contactDB);
        }];
    }
}
Light Color Skin
Copy
// Example 1: When displaying a list of channels

var dbpath: String = "<DATABASE_PATH>"
var contactDB: OpaquePointer? = nil
var statement: OpaquePointer? = nil

// Loading channels from your local database.
if sqlite3_open(dbpath, &contactDB) == SQLITE_OK {
    let query: String = "SELECT * FROM CHANNEL ORDER BY COLUMN_NAME_LAST_MESSAGE_TIMESTAMP ASC"

    if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
        // Create a list of `SBDBaseChannel` objects by deserializing each.
        var channels: [SBDGroupChannel] = []
        while (sqlite3_step(statement) == SQLITE_ROW) {
            guard let payload = sqlite3_column_blob(statement, {iCol}) else { return }
            let size = sqlite3_column_bytes(statement, {iCol})
            let data: Data = Data.init(bytes: payload, count: Int(size))
            let channel: SBDGroupChannel? = SBDGroupChannel.build(fromSerializedData: data)
            if let theChannel: SBDGroupChannel = channel {
                channels.append(theChannel)
            }
        }
        sqlite3_finalize(statement)

        sqlite3_close(contactDB)

        // Pass messages to data source for displaying them in a UITableView, UICollectionView, etc.
        self.channels.append(channels)

        // Get new channels from SendBird server.
        let query: SBDGroupChannelListQuery? = SBDGroupChannel.createMyGroupChannelListQuery()
        query?.loadNextPage(completionHandler: { (channels, error) in
            guard error == nil else {
                // Error.
                return
            }

            // Replace the current (cached) data set.
            self.channels.removeAll()
            self.channels.append(channels)

            // Save channels.
            SaveChannelsToDatabase(channels)
        })
    }
    
    sqlite3_finalize(statement)
}

sqlite3_close(contactDB)
Objective-C
Swift
Light Color Skin
Copy
// Example 2: On real-time events such as adding or updating a channel

- (void)channelWasChanged:(SBDBaseChannel * _Nonnull)sender {
    if ([sender isKindOfClass:[SBDGroupChannel class]]) {
        sqlite3 *contactDB;
        char *dbpath = "<DATABASE_PATH>";
        char *query;
        sqlite3_stmt *stmt;

        if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
            query = "SELECT * FROM CHANNEL WHERE channel_url = ?";
            if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                sqlite3_bind_text(stmt, 1, [sender.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
            }

            if (sqlite3_step(stmt) == SQLITE_ROW) {
                // If the channel is not currently cached, add it.
                sqlite3_finalize(stmt);

                const char *query = "INSERT INTO CHANNEL (channel_url, last_message_ts, payload) VALUES (?, ?, ?)";
                for (SBDGroupChannel *channel in channels) {
                    // Store each new channel in the local database.
                    if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                        sqlite3_bind_text(stmt, 1, [sender.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url
                        sqlite3_bind_int64(stmt, 2, ((SBDGroupChannel *)sender).lastMessage.createdAt); // last_message_ts

                        NSData *blob = [sender serialize];
                        sqlite3_bind_blob(stmt, 3, [blob bytes], [blob length], SQLITE_TRANSIENT);
                    }
                    
                    if (sqlite3_step(stmt) != SQLITE_DONE) {
                        // Error.
                    }

                    sqlite3_finalize(stmt);
                }
            } else {
                // If the channel is in the current cache, update it.
                sqlite3_finalize(stmt);
                query = "UPDATE CHANNEL SET last_message_ts = ?, payload = ? WHERE channel_url = ?";
                if (sqlite3_prepare_v2(contactDB, query, -1, &stmt, nil) == SQLITE_OK) {
                    sqlite3_bind_int64(stmt, 1, ((SBDGroupChannel *)sender).lastMessage.createdAt); // last_message_ts

                    NSData *blob = [sender serialize];
                    sqlite3_bind_blob(stmt, 2, [blob bytes], [blob length], SQLITE_TRANSIENT);
                    sqlite3_bind_text(stmt, 3, [sender.channelUrl UTF8String], -1, SQLITE_TRANSIENT); // channel_url

                    if (sqlite3_step(stmt) != SQLITE_DONE) {
                        // Error.
                    }

                    sqlite3_finalize(stmt);
                }
            }
        }

        sqlite3_close(contactDB);
    }
}
Light Color Skin
Copy
// Example 2: On real-time events such as adding or updating a channel

func channelWasChanged(_ sender: SBDBaseChannel) {
    guard let channel: SBDGroupChannel = sender as? SBDGroupChannel else {
        return
    }

    var dbpath: String = "<DATABASE_PATH>"
    var contactDB: OpaquePointer? = nil
    var statement: OpaquePointer? = nil

    if sqlite3_open(dbpath, &contactDB) == SQLITE_OK {
        let query: String = "SELECT * FROM CHANNEL WHERE channel_url = ?"

        if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_text(statement, 1, channel.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url
        }

        if sqlite3_step(statement) == SQLITE_ROW {
            // If the channel is not currently cached, add it.
            sqlite3_finalize(statement)

            let query: String = "INSERT INTO CHANNEL (channel_url, last_message_ts, payload) VALUES (?, ?, ?)"

            // Store new channel in the local database.
            if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
                sqlite3_bind_text(statement, 1, channel.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url
                sqlite3_bind_int64(statement, 2, channel.lastMessage?.createdAt ?? 0) // last_message_ts

                let blob: Data? = channel.serialize()
                sqlite3_bind_blob(statement, 3, blob?.bytes, blob?.length, SQLITE_TRANSIENT)
            }

            if (sqlite3_step(statement) != SQLITE_DONE) {
                // Error.
            }
    
            sqlite3_finalize(statement)
        } else {
            // If the channel is in the current cache, update it.
            sqlite3_finalize(statement)

            let query: String = "UPDATE CHANNEL SET last_message_ts = ?, payload = ? WHERE channel_url = ?"
            if sqlite3_prepare_v2(contactDB, query, -1, &statement, nil) == SQLITE_OK {
                sqlite3_bind_int64(statement, 1, channel.lastMessage?.createdAt ?? 0) // last_message_ts

                let blob: Data? = channel.serialize()
                sqlite3_bind_blob(statement, 2, blob?.bytes, blob?.length, SQLITE_TRANSIENT)
                sqlite3_bind_text(statement, 3, channel.channelUrl.UTF8String, -1, SQLITE_TRANSIENT) // channel_url

                if (sqlite3_step(statement) != SQLITE_DONE) {
                    // Error.
                }

                sqlite3_finalize(statement)
            }
        }
    }

    sqlite3_close(contactDB)
}

Note: A similar process can be applied to the channelWasDeleted:channelType:, channel:userDidJoin:, and channel:userDidLeave: method.