A,

In our work, we often encounter changes in the structure of some tables in a project due to new requirements. For example, the most common is to add new fields. Here IS my summary of how TO deal with this situation. SQLite also has some drawbacks. I hope it can help friends who have the same needs. I’m using FMDB for the following database operation, and the idea of upgrading with native or other tools is the same.

2. Upgrade operations

When the user upgrades the app, when we judge that the version number of the old project (or the separate version number of the database) is less than a certain version number, we will carry out the upgrade operation.

1. Add common fields

If you want to add a normal field and not a primary key (we’ll see what happens later), then you can do it easily. Just execute the new field statement.

Let’s review the new fields in SQL: Alter table mydownload add column ‘IsFree’ varchar(100) default ‘1’ alter table mydownload add column ‘IsFree’ varchar(100) default ‘1’

Here is the code to determine the upgrade and add the operation:

if(! [dbPointer columnExists:@"LoginUserId" inTableWithName:@"mydownload"]) {
    // If the LoginUserId field does not exist, run the add LoginUserId statement. The default value is the current LoginUserId
    NSString *addStr = [NSString stringWithFormat:@"alter table mydownload add column 'LoginUserId' varchar(100) default '%@'", [Global sharedGlobal].loginInfo.userId];
    if ([dbPointer executeUpdate:addStr]){
        DebugLog(@" Added LoginUserId field successfully!");
    } else {
        DebugLog(@" Failed to add LoginUserId field!"); }}Copy the code

⚠️ Note: SQLite is a neuter database. Many database operations are not supported. For example, adding fields in batches is not supported, so if you want to add multiple fields, you have to add them one by one.

2. Add or modify a primary key or delete a field

If you add a primary key. For example, if you want to add a new user ID to the original primary key, you will not be able to execute the new field method because of SQLite’s limitations.

⚠️ Note: SQLite restricts some of the capabilities of ALTER TABLE to adding columns to the end of a TABLE or changing the name of the TABLE. If you want to make more complex changes in the structure of the table, you must recreate the table. You can save existing data to a temporary table, delete the old table, create a new table, and then copy data from the temporary table.

For example, suppose you have a table named “person” with columns named “ID,” “name,” and “age,” and you want to delete column “age” from this table. The following SQL statement steps show how to do this:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE person_backup(id.name);
INSERT INTO person_backup SELECT id.name FROM person;
DROP TABLE person;
CREATE TABLE person(id.name);
INSERT INTO person SELECT id.name FROM person_backup;
DROP TABLE person_backup;
COMMIT;
Copy the code

Concrete example

The original download table myDownload had only two fields, EpisodeId and DownloaderFilePath, with the primary key being EpisodeId. The download does not follow the user. New requirement: Downloads are now required to follow the user, i.e. the download list only shows what the current user has downloaded. This involves adding a new LoginUserId user ID field and using it as a joint primary key with the EpisodeId so the download can be tied to the user ID. Specific code operations:

if (! [dbPointer columnExists:@"LoginUserId" inTableWithName:@" myDownload "]) {// Rename the original table [dbPointer] if there is no LoginUserId field beginTransaction]; BOOL isRollBack = NO; @try { if ([dbPointer executeUpdate:@"ALTER TABLE mydownload RENAME TO temp_mydownload"]) { NSString *executeStr = @"CREATE TABLE mydownload (EpisodeId varchar(100), LoginUserId varchar(100), DownloaderFilePath varchar(100),CONSTRAINT PK_mydownload PRIMARY KEY(EpisodeId,LoginUserId) )"; If ([dbPointer executeUpdate:executeStr]) {NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO mydownload (EpisodeId,LoginUserId,DownloaderFilePath) select EpisodeId,'%@','1','',DownloaderFilePath from temp_mydownload", [Global sharedGlobal].loginInfo.userId]; If ([dbPointer executeUpdate:insertSql]) {[dbPointer executeUpdate:@"drop" table temp_mydownload"]; / / delete old watch [[NSUserDefaults standardUserDefaults] setObject: @ "YES" forKey: @ "hasModifyDownloadDatabase"]. // mark that the download table has been upgraded}; } } } @catch (NSException *exception) { isRollBack = YES; [dbPointer] rollback; } @finally { if (! IsRollBack) {// [dbPointer] commit; }}}Copy the code

The above summary refers to and extracts the following articles. Thank you very much for sharing with the following authors! :

1.Sqlite ALTER TABLE add multiple columns

2,Sqlite does not support changing the primary key or deleting columns after creating a table. If you want to change the primary key, see the following procedure.

3,Add table Fields to FMDB Database Upgrade

Reprint please note the original source, shall not be used for commercial dissemination – fan how much