镜缘浮影 小人本住在 苏州的城外 家里有屋又有田 生活乐无边

sqlite数据存储

2017-02-12
wilmosfang
c
 
原文地址 http://soft.dog/2017/02/12/c-sqlite3/

前言

数据的 输入输出加工存储 是计算系统所有关心的范畴

当数据量小,数据结构简单的时候,可以直接使用扁平的文件结构来存储

但是当数据量大,结构复杂的时候,操作普通扁平文件的效率就变得低下了(时间开销大,更容易出错,维护成本高)

于是有必要将数据的存储这项事务独立分离出来,进行模块化和针对性的解决,数据库就应运而生了

目前的数据库主要分 关系型非关系型 两大类,网上资料多如牛毛,我就不进行赘述了

SQLite 是一个开源的进程内库,实现了自给自足、无服务端、零配置、事务性的 SQL 数据库引擎

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine

因为轻量的特性,它被大量地使用在了各类嵌入式系统中

Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

这里分享一下使用C语言操作 SQLite 的相关基础

Tip: 当前的最新版本为 SQLite Release 3.16.2 On 2017-01-06,手动维护的相关基础可以参看之前的 《SQLite 基础》,C 语言的API介绍可以参看 C-language Interface Specification for SQLite,相关的函数列表可以参看 List Of Functions


概要


代码示例

要求

  • 1.表结构:SID NAME SCORE
  • 2.用sqlite实现增、删、改、查
  • 3.用文字界面手动输入
1:insert 2:delete 3:update 4:query 0:exit

1:input 0:back

2: 1 delete   0:back
   delete ID:

代码示例

sqlite.c

#include <stdio.h> //printf,scanf,sprintf,fprintf,stderr 相关的函数声明在此文件中
#include <sqlite3.h> //sqlite3_open,sqlite3_errmsg,sqlite3_close,sqlite3_exec 相关的函数声明在此文件中

#define NAMESIZE 20 
#define SQLSIZE 1024


int callback(void *data,int argc,char **argv,char **colname) //定义一个回调函数,用于对SQL 语句的执行结果进行逐条处理,主要作用是进行显示
{
  int i=0;
  for(i=0;i<argc;i++)printf("%10s",colname[i]); //先打印每个列名
  printf("\n");
  for(i=0;i<argc;i++)printf("%10s",argv[i]); //再打印返回集中的每一项
  printf("\n");
  return 0;
}



int showall(sqlite3 *db)  //查询所有记录的操作
{
  int res=-1,option=-1;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.showall"); //子项提示
  scanf("%d",&option); //获取选择
  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option)
  {
    sprintf(sql,"select * from students;"); //构建查询所有信息的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询所有信息的SQL语句
    {
      fprintf(stderr,"Can't show all from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Select successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}


int query(sqlite3 *db) //查询操作
{
  
  int res=-1,option=-1,sid=0;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.query"); //子项提示
  scanf("%d",&option);
  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option)
  {
    printf("Please input SID of the student:\n%-10s\n","SID");
    scanf("%d",&sid);
    sprintf(sql,"select * from students where sid=%d;",sid); //构建进行查询的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询语句
    {
      fprintf(stderr,"Can't select from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Select successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}

int update(sqlite3 *db) //更新操作
{
  int res=-1,option=-1,sid=0;
  char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
  float score=0;

  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.update"); //子项提示
  scanf("%d",&option);

  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option) 
  {
    printf("Please input SID,NAME,SCORE of the student to be update:\n%-10s%-10s%-10s\n","SID","NAME","SCORE"); //提示输入更新的相关信息
    scanf("%d%s%f",&sid,name,&score); 
    sprintf(sql,"update students set name='%s',score=%5.2f where sid=%d;",name,score,sid); //构建进行更新的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行更新语句
    {
      fprintf(stderr,"Can't update table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Update successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}

int delete(sqlite3 *db) //删除操作
{
  int res=-1,option=-1,sid=0;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.delete");
  scanf("%d",&option); //子项提示
  if(0 == option)  //0项退出
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option) 
  {
    printf("Please input SID of the student:\n%-10s\n","SID");
    scanf("%d",&sid);
    sprintf(sql,"delete from students where sid=%d;",sid); //构建删除的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行删除语句
    {
      fprintf(stderr,"Can't delete from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Delete successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
  
}

int insert( sqlite3 *db) //插入操作
{
  int res=-1,option=-1,sid=0;
  char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
  float score=0;

  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.insert"); //子项提示
  scanf("%d",&option); //获取输入

  if(0 == option)  //0选项进行退出
  {
    printf("back to main menu...\n");
    return res; 
  }
  else if(1 == option)  //1选项进行插入
  {
    printf("Please input SID,NAME,SCORE of the student:\n%-10s%-10s%-10s\n","SID","NAME","SCORE"); //提示输入
    scanf("%d%s%f",&sid,name,&score); //获取输入
    sprintf(sql,"insert into students(sid,name,score) values(%d,'%s',%5.2f);",sid,name,score); //构建插入的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行插入操作
    {
      fprintf(stderr,"Can't insert into table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Insert successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}




int main()
{
  sqlite3 *db=NULL; 
  char *errmsg=NULL,*sql=NULL;
  char *dbname="test.db";
  int res=-1,option=-1; //定义变量,进行初始化

  if(0 != sqlite3_open(dbname,&db)) //打开数据库,如果没有就创建
  {
    fprintf(stderr,"Can't open database %s :%s\n",dbname,sqlite3_errmsg(db)); //打开失败则返回错误信息
    sqlite3_close(db);
    return res;
  }
  else printf("Open database %s successfully...\n",dbname);

  sql="create table students (sid int primary key,name varchar(20),score real )"; //构建SQL语句,在数据库中创建表

  if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行SQL语句
  {
    fprintf(stderr,"Can't create table students:%s\n",sqlite3_errmsg(db)); 
  }
  else printf("Create table students successfully...\n");


  while(1)
  {

    printf("Please take follow action:\n%-10s%-10s%-10s%-10s%-10s%-10s\n","0.exit","1.insert","2.delete","3.update","4.query","5.showall"); //显示主菜单
    scanf("%d",&option); //获取选项
    if(0 == option)  //如果选择退出,就进行退出
    {
      printf("ready to exit...\n");
      break;
    }
    else if(1 == option) insert(db); 
    else if(2 == option) delete(db);
    else if(3 == option) update(db);
    else if(4 == option) query(db);
    else if(5 == option) showall(db); //进入对应的处理过程
    else printf("error option %d\nplease reinput...\n",option); //如果不在其列,就提示重新输入
  }


  if(0 != sqlite3_close(db)) //关闭数据库
  {
    fprintf(stderr,"Can't close database %s :%s\n",dbname,sqlite3_errmsg(db));
    return res;
  }
  else printf("Close database %s successfully...\n",dbname);
  res=0;
  return res;
}

编译执行

emacs@ubuntu:~/c$ alias gtc
alias gtc='gcc -Wall -g -o'
emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c -lsqlite3
emacs@ubuntu:~/c$ 

Note: 在编译过程中一定要加上 -lsqlite3 参数,否则不加载相关的库,会有依赖关系的报错

emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c 
/tmp/ccjGMlsR.o: In function `showall':
/home/emacs/c/sqlite.c:35: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:37: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `query':
/home/emacs/c/sqlite.c:67: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:69: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `update':
/home/emacs/c/sqlite.c:99: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:101: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `delete':
/home/emacs/c/sqlite.c:129: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:131: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `insert':
/home/emacs/c/sqlite.c:162: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:164: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `main':
/home/emacs/c/sqlite.c:185: undefined reference to `sqlite3_open'
/home/emacs/c/sqlite.c:187: undefined reference to `sqlite3_errmsg'
/home/emacs/c/sqlite.c:188: undefined reference to `sqlite3_close'
/home/emacs/c/sqlite.c:195: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:197: undefined reference to `sqlite3_errmsg'
/home/emacs/c/sqlite.c:221: undefined reference to `sqlite3_close'
/home/emacs/c/sqlite.c:223: undefined reference to `sqlite3_errmsg'
collect2: ld returned 1 exit status
emacs@ubuntu:~/c$

进行执行

emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c -lsqlite3
emacs@ubuntu:~/c$ ./sqlite.x 
Open database test.db successfully...
Can't create table students:table students already exists
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
5
Please take follow action:
0.back    1.showall 
1
ready to process ...
[ select * from students; ]
       sid      name     score
        99        io      2334
       sid      name     score
        98       iii        91
       sid      name     score
        34        ui        89
       sid      name     score
        12        ii      89.3
       sid      name     score
        89        jj      89.2
Select successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
1
Please take follow action:
0.back    1.insert  
1
Please input SID,NAME,SCORE of the student:
SID       NAME      SCORE     
77 wilmos 100
ready to process ...
[ insert into students(sid,name,score) values(77,'wilmos',100.00); ]
Insert successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
4
Please take follow action:
0.back    1.query   
1
Please input SID of the student:
SID       
77
ready to process ...
[ select * from students where sid=77; ]
       sid      name     score
        77    wilmos       100
Select successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
3
Please take follow action:
0.back    1.update  
1
Please input SID,NAME,SCORE of the student to be update:
SID       NAME      SCORE     
77 fang 99
ready to process ...
[ update students set name='fang',score=99.00 where sid=77; ]
Update successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
4
Please take follow action:
0.back    1.query   
1
Please input SID of the student:
SID       
77
ready to process ...
[ select * from students where sid=77; ]
       sid      name     score
        77      fang        99
Select successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
5
Please take follow action:
0.back    1.showall 
1
ready to process ...
[ select * from students; ]
       sid      name     score
        99        io      2334
       sid      name     score
        98       iii        91
       sid      name     score
        34        ui        89
       sid      name     score
        12        ii      89.3
       sid      name     score
        89        jj      89.2
       sid      name     score
        77      fang        99
Select successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
2
Please take follow action:
0.back    1.delete  
1
Please input SID of the student:
SID       
89
ready to process ...
[ delete from students where sid=89; ]
Delete successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
5
Please take follow action:
0.back    1.showall 
1
ready to process ...
[ select * from students; ]
       sid      name     score
        99        io      2334
       sid      name     score
        98       iii        91
       sid      name     score
        34        ui        89
       sid      name     score
        12        ii      89.3
       sid      name     score
        77      fang        99
Select successfully...
Please take follow action:
0.exit    1.insert  2.delete  3.update  4.query   5.showall 
0
ready to exit...
Close database test.db successfully...
emacs@ubuntu:~/c$

编译执行过程中没有报错,从结果来看,符合预期


sqlite3_open

sqlite3.h 中有关于 sqlite3_open 的声明

/*
** Open the sqlite database file "filename".  The "filename" is UTF-8
** encoded for sqlite3_open() and UTF-16 encoded in the native byte order
** for sqlite3_open16().  An sqlite3* handle is returned in *ppDb, even
** if an error occurs. If the database is opened (or created) successfully,
** then SQLITE_OK is returned. Otherwise an error code is returned. The
** sqlite3_errmsg() or sqlite3_errmsg16()  routines can be used to obtain
** an English language description of the error.
**
** If the database file does not exist, then a new database is created.
** The encoding for the database is UTF-8 if sqlite3_open() is called and
** UTF-16 if sqlite3_open16 is used.
**
** Whether or not an error occurs when it is opened, resources associated
** with the sqlite3* handle should be released by passing it to
** sqlite3_close() when it is no longer required.
*/
int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

这个函数用来打开指定的数据库

sqlite3_open()sqlite3_open_v2() 使用 UTF-8 来解析文件名

sqlite3_open16() 使用 UTF-16 来解析文件名

返回值 : 成功 SQLITE_OK ,失败会返回错误代码,错误代码可以使用 sqlite3_errmsg()sqlite3_errmsg16() 来获取解析

Note: ppDb 是指针的指针


SQLITE_OK

sqlite3.h 中有关于返回码的定义

/*
** Return values for sqlite3_exec() and sqlite3_step()
*/
#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* An 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   /* (Internal Only) Table or record not found */
#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   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint 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 */

更全面和详细的解释可以参看 Result and Error Codes


sqlite3_errmsg

sqlite3.h 中有关于 sqlite3_errmsg 的声明

/*
** Return a pointer to a UTF-8 encoded string describing in english the
** error condition for the most recent sqlite3_* API call. The returned
** string is always terminated by an 0x00 byte.
**
** The string "not an error" is returned when the most recent API call was
** successful.
*/
const char *sqlite3_errmsg(sqlite3*);

这个函数返回一串关于此错误的英文描述(事实上是一个字符指针,指向一个字符串)

参数为一个数据库指针


sqlite3_close

sqlite3.h 中有关于 sqlite3_close 的声明

/*
** A function to close the database.
**
** Call this function with a pointer to a structure that was previously
** returned from sqlite3_open() and the corresponding database will by closed.
**
** All SQL statements prepared using sqlite3_prepare() or
** sqlite3_prepare16() must be deallocated using sqlite3_finalize() before
** this routine is called. Otherwise, SQLITE_BUSY is returned and the
** database connection remains open.
*/
int sqlite3_close(sqlite3 *);

这个函数用于关闭一个数据库

参数为一个数据库指针


sqlite3_exec

sqlite3.h 中有关于 sqlite3_exec 的声明

/*
** A function to executes one or more statements of SQL.
**
** If one or more of the SQL statements are queries, then
** the callback function specified by the 3rd parameter is
** invoked once for each row of the query result.  This callback
** should normally return 0.  If the callback returns a non-zero
** value then the query is aborted, all subsequent SQL statements
** are skipped and the sqlite3_exec() function returns the SQLITE_ABORT.
**
** The 4th parameter is an arbitrary pointer that is passed
** to the callback function as its first parameter.
**
** The 2nd parameter to the callback function is the number of
** columns in the query result.  The 3rd parameter to the callback
** is an array of strings holding the values for each column.
** The 4th parameter to the callback is an array of strings holding
** the names of each column.
**
** The callback function may be NULL, even for queries.  A NULL
** callback is not an error.  It just means that no callback
** will be invoked.
**
** If an error occurs while parsing or evaluating the SQL (but
** not while executing the callback) then an appropriate error
** message is written into memory obtained from malloc() and
** *errmsg is made to point to that message.  The calling function
** is responsible for freeing the memory that holds the error
** message.   Use sqlite3_free() for this.  If errmsg==NULL,
** then no error message is ever written.
**
** The return value is is SQLITE_OK if there are no errors and
** some other return code if there is an error.  The particular
** return value depends on the type of error. 
**
** If the query could not be executed because a database file is
** locked or busy, then this function returns SQLITE_BUSY.  (This
** behavior can be modified somewhat using the sqlite3_busy_handler()
** and sqlite3_busy_timeout() functions below.)
*/
int sqlite3_exec(
  sqlite3*,                     /* An open database */
  const char *sql,              /* SQL to be executed */
  sqlite3_callback,             /* Callback function */
  void *,                       /* 1st argument to callback function */
  char **errmsg                 /* Error msg written here */
);

这个函数用来执行 SQL 语句

sqlite3 指定一个已经打开的数据库

sql 指定一个将要被执行的SQL语句

sqlite3_callback 回调函数

void * 回调函数的第一个参数

errmsg 错误信息的指针


sqlite3_callback

sqlite3.h 中有关于 sqlite3_callback 的声明

/*
** The type for a callback function.
*/
typedef int (*sqlite3_callback)(void*,int,char**, char**);

回调函数是作为 sqlite3_exec 第三个参数而存在的,它的作用在于处理返回的每条结果

一般而言,回调函数的返回结果都是0,如果结果是非0,那么查询过程会中断,所有接下来的查询会中断并且跳过,sqlite3_exec() 的返回结果会是 SQLITE_ABORT

第一个参数 void*sqlite3_exec() 传过来的第四个参数,作为自己的第一个参数

第二个参数 int 查询结果中当条记录的字段数

第三个参数 char** 字段名形成的字符串指针数组

第四个参数 char** 结果集形成的字符串指针数组


sprintf

stdio.h 中有关于 sprintf 的声明

/* Write formatted output to S.  */
extern int sprintf (char *__restrict __s,
                    __const char *__restrict __format, ...) __THROW;

在构建 SQL 语句的过程中会经常使用到 sprintf ,它和 printf 的用法相似,但是将结果写到一个字符数组中,而不是直接打印到了终端上,这样便于后期的处理


总结

以下函数可以对sqlite数据库进行创建与控制,是存储数据的基础操作

  • sqlite3_open
  • sqlite3_errmsg
  • sqlite3_close
  • sqlite3_exec
  • sqlite3_callback

通过各方面资料弄懂其参数的意义和返回值的类型,是熟练掌握的基础

原文地址 http://soft.dog/2017/02/12/c-sqlite3/

评论