iOS App USES the definition of SQLite handles and basic database operations
- 2020-06-12 10:41:38
- OfStack
handle
To manipulate a database you have to have a handle to that database. You don't have to care what the word is, you just have to figure out what it is. Like why shoes are called shoes, it's hard to understand when you think about it, but it's clear what they do, isn't it?
Handles we've seen in a lot of places, and the most common one is a handle to a file, so to manipulate a file, we have to get a handle to a file. What is a handle? Quite simply, a handle is a description of an object, defined as a structure, which may contain specific information about the object to be described, such as location, size, type, and so on. We have this description information and we can go and find this guy and manipulate it.
1. Handle is the descriptive structure of the object.
Let's take a look at how sqlite handles are defined (don't freak out, 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;//
It doesn't matter if it scares you. This code was originally posted by me to scare you. I haven't studied this code seriously, and I don't want to study it, unless someone offers me a high price someday.
This is sqlite3 structure is used to describe our database files in the disk, with this descriptor we can on the database for all kinds of operation, operation of concrete inside we don't have to understand, we just need to know how to call API, of course, sometimes still have to know a little story, after this encounter to speak again.
I add a long, scary piece of code for one purpose: don't be afraid of handles.
Okay, so to get started, in sqlite you're going to manipulate the database we're going to create a handle, and then we're going to use that handle for all of the other operations that we're going to do with the database.
sqlite3* pdb;
As simple as that, we have created one ssqlite handle that we will need for future database operations.
Open, close, and create the database
I created a handle, but how do I know which database file this handle is pointing to on disk? We just created a pointer to a structure of type sqlite3. The data is empty or default. The next thing we need to do is to request memory for the structure and fill the structure. Do you feel scared again? This structure is so huge that we have to fill it ourselves. We don't have to be afraid of anything, we don't need to explicitly fill it, we just need to tell it 1 basic information, and then call API and ask API to fill it for us.
For now, we only need to tell this structure one piece of information, which is the path of the database file. Then call the sqlite3_open function to OK.
SQLITE_API int sqlite3_open(//SQLITE_API It's a macro. It's used for annotations API Ignore it
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 level 2 pointer, the level 1 pointer we declared, so we need to add an addressee & , please see my following example:
int ret = sqlite3_open( getFilePath(), &pdb);//
Don't be surprised, opening the database is about getting some information from the database and manipulating it, right? With this function we bind the database to handle pdb at 1, so we can manipulate the database with pdb. In this function, the first parameter is the path of 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 1 function to do only one thing), I suggest you do the same, you can slowly experience the benefits. The function of my path is as follows. You can refer to 1 below:
const char* getFilePath(){
return [[NSString stringWithFormat:@"%@/Documents/db",NSHomeDirectory() ] UTF8String];
// I'm sorry to use alpha and beta IOS The relevant 1 I can't do that unless I write it as an absolute path, but that's not going to work
// But it doesn't matter, you're going to have to change your path when you migrate, so change it when you migrate 1 Under it
}
One more point is the return value, which we use to determine if the execution was successful. sqlite helped us define a series of macros to be 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 this we can debug our code more easily.
Well, get into the business, this function is open the database in the presence of database, there are no circumstances create the database, the database name can take at random, as long as it is ASCII character is good, because sqlite database is a ASCII file (so it's safe or not, but do not have what problem) as a local database.
After the database is opened, we can do 1 series of adding, deleting, checking and changing operations. After the operation, we will close the database, right? How else to free up resources?
It's also easy to close:
SQLITE_API int sqlite3_close(sqlite3 *db);
I don't have to explain this, but let me give you an example:
sqlite3_close(pdb);// I'm not going to worry about the return value here