Handle
To manipulate a database, you have to have a handle to this database (I encountered this incomprehensible word again, but I really haven't got a better word to replace it). In fact, you don’t need to care about what the word is, you just need to figure out what it is. Just like why shoes are called shoes, it is really hard to understand if you think about it carefully, but it is OK to know its function, right?
Handles have been seen in many places. The most common one is the handle of a file. If we want to manipulate a file, we have to obtain the handle of a file. What is a handle? In fact, it is very simple. The handle is a description of a thing. It is defined as a structure. This structure may contain specific information about the thing to be described, such as position, size, type, etc. With this description information, we can find this thing and manipulate it.
In a word: The handle is the 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 . They are used by code in .
**
** When ==Y, that means that X is waiting for Y to
** unlock so that it can proceed.
**
** When ==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;//
It doesn't matter if you were scared or not. I posted this code to scare you. I haven't studied this code seriously and don't want to study it. Unless someone pays a high price one day, I only know how to use it now.
This sqlite3 structure is used to describe the database file on our disk. With this descriptor, we can perform various operations on the database. We don’t need to understand the specific internal information of the operation. We just need to know how to call the API. Of course, sometimes we still need to understand a little bit of the internal information, and we will talk about it later.
I added a large piece of scary code in such a long sentence for only one purpose: don't be afraid of the handle.
Okay, let's start our topic. If you want to manipulate the database in sqlite, we must first create a handle, and then all operations on the database will use this handle.
sqlite3* pdb;
It's that simple. We have created a ssqlite handle, and our future operations on the database will be inseparable from it.
Open, close, create database
I created a handle, but how do I know which database file this handle points to on disk? We just created a pointer to a sqlite3 type structure. The data inside is empty or default. What we need to do next is to apply memory for this structure and fill it. Do you feel scared again? This structure is so huge that we can fill in the year of the monkey and horse month. There is no need to be afraid of anything, we don’t need to explicitly fill it. We just need to tell it some basic information, and then call the API to let the API fill it for us.
At present, we only need to tell this structure one information, which is the path to the database file. Then call the sqlite3_open function and it will be OK.
SQLITE_API int sqlite3_open(//SQLITE_API is a macro, and it is ignored if you use it to mark the API. const char *zFilename, sqlite3 **ppDb );
The first parameter is the path, const char * type, and the second parameter is the pointer to the database handle. Note that it is a secondary pointer, the first-level pointer we declare, so we also need to add an address character &, please see my example below:
int ret = sqlite3_open( getFilePath(), &pdb);//
Don’t be surprised. In fact, opening the database is to obtain some information about the database, and then facilitate us to manipulate it, right? Through this function, we bind the database with the handle pdb, so we use this pdb to manipulate the database. The first parameter in this function is the path of the database file (including the file name). I usually write the path as a function, which follows the encoding principle (the principle used here is to try to do only one thing for a function). It is recommended that you do the same, and you can slowly understand the benefits. The function I get the path is as follows:
const char* getFilePath(){
return [[NSString stringWithFormat:@"%@/Documents/db",NSHomeDirectory() ] UTF8String];
//Sorry, I'm using something related to IOS here, but there is nothing to do with this, unless I write it as an absolute path, but that won't work // But it doesn't matter. If you transplant it elsewhere, you must change the path, so just change it when transplanting it.}
Another point is the return value, which we use to determine whether the execution is successful. sqlite helps us define a series of macros 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 debug our code more easily.
Okay, let's get to the topic. The above function is to open the database when the database exists, and create the database when it does not exist. The name of the database can be randomly selected, as long as it is ASCII characters, because the sqlite database is originally an ASCII file (so its security is still not very good, but as a local database, there is no problem).
After the database is opened, we can perform a series of operations such as adding, deleting, checking and modifying. After the operation is completed, we have to close the database, right? Otherwise, how to release resources?
Close is also very simple:
SQLITE_API int sqlite3_close(sqlite3 *db);
No need to explain this, just look at my example:
sqlite3_close(pdb);// You can ignore the return value here