IOS app uses SQLite's definition of the handle and basic database operations _ios

handle
to manipulate a database you'll have to have a handle to the database (and you've got this difficult word, but there's really no better word to replace it). You don't really have to care about the word, you just have to figure out what he is. Just like the shoes why the shoes, carefully think about it is difficult to understand, but clear his function is OK, is not it?
Handle in many places we have seen, the most common is the file handle, we have to manipulate a file, we need to get a file handle. What is a handle? In fact it is very simple, the handle is a description of the east, he is defined as a structure, this structure may contain the specific information to describe the Dongdong, such as location, size, type and so on. We have this descriptive information that we can go to find this stuff and manipulate it.
A word: A handle is a descriptive structure of an object.
Let's take a look at how the SQLite handle is defined (don't be scared, just skip the code):

struct sqlite3 {
 sqlite3_vfs *pVfs;      /* OS Interface */
 int nDb;           /* Number of backends currently in use */
 Db *aDb;           /* All backends */
 int flags;          /* Miscellaneous flags. See below */
 unsigned int openFlags;    /* Flags passed to sqlite3_vfs.xOpen() */
 int errCode;         /* Most recent error code (SQLITE_*) */
 int errMask;         /* & result codes with this before returning */
 u8 autoCommit;        /* The auto-commit flag. */
 u8 temp_store;        /* 1: file 2: memory 0: default */
 u8 mallocFailed;       /* True if we have seen a malloc failure */
 u8 dfltLockMode;       /* Default locking-mode for attached dbs */
 signed char nextAutovac;   /* Autovac setting after VACUUM if >=0 */
 u8 suppressErr;        /* Do not issue error messages if true */
 u8 vtabOnConflict;      /* Value to return for s3_vtab_on_conflict() */
 int nextPagesize;       /* Pagesize after VACUUM if >0 */
 int nTable;          /* Number of tables in the database */
 CollSeq *pDfltColl;      /* The default collating sequence (BINARY) */
 i64 lastRowid;        /* ROWID of most recent insert (see above) */
 u32 magic;          /* Magic number for detect library misuse */
 int nChange;         /* Value returned by sqlite3_changes() */
 int nTotalChange;       /* Value returned by sqlite3_total_changes() */
 sqlite3_mutex *mutex;     /* Connection mutex */
 int aLimit[SQLITE_N_LIMIT];  /* Limits */
 struct sqlite3InitInfo {   /* Information used during initialization */
  int iDb;          /* When back is being initialized */
  int newTnum;        /* Rootpage of table being initialized */
  u8 busy;          /* TRUE if currently initializing */
  u8 orphanTrigger;      /* Last statement is orphaned TEMP trigger */
 } init;
 int nExtension;        /* Number of loaded extensions */
 void **aExtension;      /* Array of shared library handles */
 struct Vdbe *pVdbe;      /* List of active virtual machines */
 int activeVdbeCnt;      /* Number of VDBEs currently executing */
 int writeVdbeCnt;       /* Number of active VDBEs that are writing */
 int vdbeExecCnt;       /* Number of nested calls to VdbeExec() */
 void (*xTrace)(void*,const char*);    /* Trace function */
 void *pTraceArg;             /* Argument to the trace function */
 void (*xProfile)(void*,const char*,u64); /* Profiling function */
 void *pProfileArg;            /* Argument to profile function */
 void *pCommitArg;         /* Argument to xCommitCallback() */  
 int (*xCommitCallback)(void*);  /* Invoked at every commit. */
 void *pRollbackArg;        /* Argument to xRollbackCallback() */  
 void (*xRollbackCallback)(void*); /* Invoked at every commit. */
 void *pUpdateArg;
 void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
#ifndef SQLITE_OMIT_WAL
 int (*xWalCallback)(void *, sqlite3 *, const char *, int);
 void *pWalArg;
#endif
 void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
 void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
 void *pCollNeededArg;
 sqlite3_value *pErr;     /* Most recent error message */
 char *zErrMsg;        /* Most recent error message (UTF-8 encoded) */
 char *zErrMsg16;       /* Most recent error message (UTF-16 encoded) */
 union {
  volatile int isInterrupted; /* True if sqlite3_interrupt has been called */
  double notUsed1;      /* Spacer */
 } u1;
 Lookaside lookaside;     /* Lookaside malloc configuration */
#ifndef SQLITE_OMIT_AUTHORIZATION
 int (*xAuth)(void*,int,const char*,const char*,const char*,const char*);
                /* Access authorization function */
 void *pAuthArg;        /* 1st argument to the access auth function */
#endif
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
 int (*xProgress)(void *);   /* The progress callback */
 void *pProgressArg;      /* Argument to the progress callback */
 int nProgressOps;       /* Number of opcodes for progress callback */
#endif
#ifndef SQLITE_OMIT_VIRTUALTABLE
 Hash aModule;         /* populated by sqlite3_create_module() */
 VtabCtx *pVtabCtx;      /* Context for active vtab connect/create */
 VTable **aVTrans;       /* Virtual tables with open transactions */
 int nVTrans;         /* Allocated size of aVTrans */
 VTable *pDisconnect;  /* Disconnect these in next sqlite3_prepare() */
#endif
 FuncDefHash aFunc;      /* Hash table of connection functions */
 Hash aCollSeq;        /* All collating sequences */
 BusyHandler busyHandler;   /* Busy callback */
 int busyTimeout;       /* Busy handler timeout, in msec */
 Db aDbStatic[2];       /* Static space for the 2 default backends */
 Savepoint *pSavepoint;    /* List of active savepoints */
 int nSavepoint;        /* Number of non-transaction savepoints */
 int nStatement;        /* Number of nested statement-transactions */
 u8 isTransactionSavepoint;  /* True if the outermost savepoint is a TS */
 i64 nDeferredCons;      /* Net deferred constraints this transaction. */
 int *pnBytesFreed;      /* If not NULL, increment this in DbFree() */
  
#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
 /* The following variables are all protected by the STATIC_MASTER
 ** mutex, not by sqlite3.mutex. They are used by code in notify.c.
 **
 ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
 ** unlock so that it can proceed.
 **
 ** When X.pBlockingConnection==Y, that means that something that X tried
 ** tried to do recently failed with an SQLITE_LOCKED error due to locks
 ** held by Y.
 */
 sqlite3 *pBlockingConnection; /* Connection that caused SQLITE_LOCKED */
 sqlite3 *pUnlockConnection;      /* Connection to watch for unlock */
 void *pUnlockArg;           /* Argument to xUnlockNotify */
 void (*xUnlockNotify)(void **, int); /* Unlock notify callback */
 sqlite3 *pNextBlocked;    /* Next in list of all blocked connections */
#endif
};
 
typedef struct sqlite3 sqlite3;//

is not scared, it does not matter, this code is I posted out to scare you, I did not seriously study the code, and do not want to study, unless the day someone pay a high price to me to study, I now only know how to use the good.
This sqlite3 structure is used to describe the database files in our disk. With this descriptor we can do a variety of operations on this database, the details of the operation we do not need to understand, we need to know how to invoke the API is good, of course, sometimes to understand a little insider, I'll talk about it later.
I used that long word to add a large section of scary code for one purpose: Don't be afraid of the handle.
All right, let's get to the point, SQLite. You have to manipulate the database first we have to create a handle, and then all of the following operations on the database will use this handle.

sqlite3* pdb; 

It is so simple, such a ssqlite handle we have created, we can not be separated from the operation of the database.

Open, close, create a database
I created a handle, but how do I know which database file the handle is pointing to on disk? We just created a pointer to a sqlite3 type of struct. The data inside is empty or default. What we're going to do next is to apply the memory to the structure and populate the structure. Are you feeling scared again? This structure is so large, we go to fill the SE years. It doesn't have to be scary, it doesn't need to be explicitly populated, we just have to tell it some basic information, and then call the API to get the API to populate it for us.
Now all we need to do is tell the structure a message, the path to the database file. And then call the Sqlite3_open function and it's OK.

Sqlite_api int Sqlite3_open (//SQLITE_API is a macro that is used to annotate the API without ignoring its 
 const char *zfilename,  
 sqlite3 **ppdb  
); 

The first parameter is the path, the const char * type, the second parameter is a pointer to the database handle, the note is a two-level pointer, the one-level pointer we declare, so we'll add an address character, and look at my example below:

int ret = Sqlite3_open (GetFilePath (), &pdb); 

Don't be surprised, actually open the database is to get some information about the database, and then convenient for us to manipulate it, is not it? With this function we bind the database to the handle PDB, and we manipulate the database through the PDB. The first parameter in this function is the path to the database file (including the file name), I usually write the path as a function, so follow the coding principle (the principle used here is a function as far as possible to do only one thing), I suggest you, the benefits of your own can slowly realize. The function that I get the path as follows you can refer to:

Const char* GetFilePath () {return 
  [[NSString stringwithformat:@ "%@/documents/db", Nshomedirectory ()] utf8string] ; 
      I'm sorry there's something about iOS, but there's no way to do that unless I write an absolute path, but that's not going to work 
      , but it doesn't matter, you'll have to change your path somewhere else, so just change it when you transplant. 
 

Another point is the return value, which we use to determine whether the execution was successful. SQLite defines a series of macros that are used as return values:

#define SQLITE_OK      0  /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR    1  /* SQL error or missing database */
#define SQLITE_INTERNAL   2  /* Internal logic error in SQLite */
#define SQLITE_PERM     3  /* Access permission denied */
#define SQLITE_ABORT    4  /* Callback routine requested an abort */
#define SQLITE_BUSY     5  /* The database file is locked */
#define SQLITE_LOCKED    6  /* A table in the database is locked */
#define SQLITE_NOMEM    7  /* A malloc() failed */
#define SQLITE_READONLY   8  /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT  9  /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR    10  /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT   11  /* The database disk image is malformed */
#define SQLITE_NOTFOUND  12  /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL    13  /* Insertion failed because database is full */
#define SQLITE_CANTOPEN  14  /* Unable to open the database file */
#define SQLITE_PROTOCOL  15  /* Database lock protocol error */
#define SQLITE_EMPTY    16  /* Database is empty */
#define SQLITE_SCHEMA   17  /* The database schema changed */
#define SQLITE_TOOBIG   18  /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT 19  /* Abort due to constraint violation */
#define SQLITE_MISMATCH  20  /* Data type mismatch */
#define SQLITE_MISUSE   21  /* Library used incorrectly */
#define SQLITE_NOLFS    22  /* Uses OS features not supported on host */
#define SQLITE_AUTH    23  /* Authorization denied */
#define SQLITE_FORMAT   24  /* Auxiliary database format error */
#define SQLITE_RANGE    25  /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB   26  /* File opened that is not a database file */
#define SQLITE_ROW     100 /* sqlite3_step() has another row ready */
#define SQLITE_DONE    101 /* sqlite3_step() has finished executing */
/* end-of-error-codes */

With these we can more easily debug our code.
Okay, to get to the point, the above function is to open the database in the presence of the database, does not exist in the case of the creation of a database, the name of the database can be arbitrarily random, as long as the ASCII character is good, because the SQLite database is an ASCII file (so its security is not very good , but there's nothing wrong with being a local database.
After the database opened we can do a series of additions and deletions to check the operation of the operation, we will close the database, is not it? How else would you release the resources?
Closing is also simple:

Sqlite_api int Sqlite3_close (sqlite3 *db); 

I don't need to explain this, just look at my example:

Sqlite3_close (PDB);//Here we can not consider the return value.