SQLite 入门笔记（一）

前言
近期遇到一个项目要在海思开发板上使用一个数据库管理图片。由此接触了 SQLite。在此记录下学习过程中的一些问题，以防日后忘记。本文主要针对零基础入门者，老鸟可跳过。

1. 什么是 SQLite？
以下引用来自百度百科

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，SQLite第一个Alpha版本诞生于2000年5月， 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

2. 什么是SQL？
以下引用来自百度百科

结构化查询语言(Structured Query Language)简称SQL， 是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

总结：SQLite 是一款轻型数据库，目前通用 sqlite3; SQL 是一种与数据库沟通的语言，它需要在数据库应用程序上运行，例如sqlite3。支持 SQL 的应用程序有很多，由于我是做嵌入式的所以这里就使用 sqlite3.

3. 新手如何学习 SQLite
确切来讲我们使用的是 sqlite3 ,正如第1部分概念中介绍的那样，它可以运行在windos/linux/unix 下，所以我们要选择一个系统进行安装。这里我是在 linux 下安装。虽然我的最终目的是在开发板上使用数据库，但是这里我先讲怎么在 ubuntu 上使用数据库，先在 ubuntu 上把 sqlite3 玩会了再移植到开发板。这样对新手来说比较容易接受。如何移植到开发板参见我下一篇博客
《SQLite 入门笔记（二）》。
安装方法：
(1) 安装 sqlite3
sudo apt-get install sqlite3
(2) 安装 sqlite3 编译需要的库
sudo apt-get install libsqlite3-dev
注：其实只安装 sqlite3 就可以在命令行下进行 SQL 语句的执行，安装libsqlite3-dev 是为了支持编译 SQL 源码程序。
一些具体的命令语法我这里就不讲了，我就是按照下面的的教程做的，跟着做一遍就会得差不多了
https://www.runoob.com/sqlite/sqlite-tutorial.html

4. 使用说明
sqlite3 安装完成后，我们就可以使用了。
a. 新建/打开 数据库
在终端直接输入 sqlite3 test.db
即可进入 sqlite 的命令行，如下图。这里 sqlite3 是应用程序名，test.db 是数据库名,可以为pathname,即带路径的文件名。这句命令的意思是 新建/打开一个名为 test.db 的数据库，数据库一般以 .db 为后缀。如果该文件不存在则新建，否则直接打开。教程里没有明确说明我在这里补充一下。

b. 编译 c 语言源码程序。
例如我想要编译自己写的 sql test.c 文件，需要这样做：
gcc test.c -o test -lsqlite3 // here -lsqlite3 means link the dynamic library of sqlite3.

5. 一些源码记录
这里的代码参考了以下博客
https://blog.csdn.net/sinat_39061823/article/details/77448555
(1) 增、删、改、查 以及 取出图片数据的 c 语言实现方法

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
#include <fcntl.h>
#include <sys/stat.h>
#include <unistd.h>

#ifndef NULL
#define NULL ((void *)0)
#endif

int handle_insert(sqlite3 *db);
int handle_delete(sqlite3 *db);
int handle_update(sqlite3 *db);
int handle_select(sqlite3 *db);
int handle_export_data(sqlite3 *db);
void handle_abnormal(void);

/* struct to store data from user */
typedef struct pic_info
{
    int     iID;
    char    arrcGender[10];
    char    arrcName[20];
    int     iAge;
    void    *pData;
} st_pic_info_t;


 
//sqlite3的回调函数      
// sqlite 每查到一条记录，就调用一次这个回调
// data是 sqlite3_exec 里传入的 void * 参数,即第4个参数; 一般不使用。
 static int LoadMyInfo(void *data, int column_cnt, char **column_value, char **column_name)
{
   int i;
   //fprintf(stderr, "%s: ", (const char*)data);
   printf("-------------------------------\n");
   for(i = 0; i < column_cnt; i++)
   {
      printf("%s = %s\n", column_name[i], column_value[i]);
   }
   printf("\n");
   return 0;
}

int main(int argc, char **argv)
{
    sqlite3 *db;
    char *pcErrMsg = NULL;
    int  iRet;
    char *pcCreateSql;
    int iKey;

    /* Open database */
    iRet = sqlite3_open("img.db", &db);
    if(iRet != SQLITE_OK)
    {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
    }
    else
    {
      fprintf(stdout, "Opened database successfully\n");
    }

    /* Create SQL statement */
    pcCreateSql = "CREATE TABLE IF NOT EXISTS img_tb(" \
          "ID INT PRIMARY KEY     NOT NULL," \
          "GENDER         TEXT    NOT NULL," \
          "NAME           TEXT," \
          "AGE            INT," \
          "DATA           BLOB    NOT NULL);";

    /* Execute SQL statement */
    iRet = sqlite3_exec(db, pcCreateSql, LoadMyInfo, NULL, &pcErrMsg);
    if( iRet != SQLITE_OK )
    {
        fprintf(stderr, "SQL error: %s\n", pcErrMsg);
        sqlite3_free(pcErrMsg);
    }
    else
    {
        fprintf(stdout, "Table created successfully\n");
    }
    

    printf("please enter one of the keys to do what you want('q' to quit).\n\
    1 :insert a data into the database;\n\
    2 :delete a data from the database;\n\
    3 :update the data in the database;\n\
    4 :select the data from the database;\n\
    5 :export the data from the database.\n");
    printf("what you will enter is:");
    
    while(scanf("%d", &iKey) == 1)
    {
        switch(iKey)
        {
            case 1: handle_insert(db); break;
            case 2: handle_delete(db); break;
            case 3: handle_update(db); break;
            case 4: handle_select(db); break;
            case 5: handle_export_data(db); break;
            default: handle_abnormal(); break;
                           
        }
    printf("please enter one of the keys to do what you want('q' to quit).\n\
    1 :insert a data into the database;\n\
    2 :delete a data from the database;\n\
    3 :update the data in the database;\n\
    4 :select the data from the database;\n\
    5 :export the data from the database.\n");
    printf("what you will enter is:");
        
    }
    
    sqlite3_close(db);    
    return 0;
}


int handle_insert(sqlite3 *db)
{   
    int iRet = -1;    // return value
    char arrcPicPath[50] = {0};    // buf to keep the pathname of a pic file
    st_pic_info_t stPicInfo;
    
    printf("please enter the pathname of the pic file:");    
    scanf("%s", arrcPicPath);
    
     //打开图片文件，读取文件大小信息
	int iFd = open(arrcPicPath, O_RDONLY);
	if(iFd < 0)
	{
		perror("open error:");
		return 2;
	}
    printf("open pic success.\n");
	//读取文件信息
	struct stat st;
	fstat(iFd, &st);
	int iImgSize = st.st_size;
	stPicInfo.pData = malloc(iImgSize);
 
	if(!stPicInfo.pData)
	{
		perror("malloc error:");
		return 2;
	}
	
	read(iFd, stPicInfo.pData, iImgSize);
	close(iFd);//关闭文件指针
    printf("read pic success.\n");
    
    printf("please enter the ID of the pic file:");    
    scanf("%d", &(stPicInfo.iID));    
    printf("please enter the gender of the pic file:");    
    scanf("%s", stPicInfo.arrcGender);
    printf("please enter the name of the pic file:");    
    scanf("%s", stPicInfo.arrcName);
    printf("please enter the age of the pic file:");    
    scanf("%d", &(stPicInfo.iAge));    
        
	//数据库操作
 	//进行插入数据操作
 	// here "?" is a plaseholder, which means the place it stands needs to be filled by a value later.
	char *pcInsertSql = "INSERT INTO img_tb (ID, GENDER, NAME, AGE, DATA) VALUES (?,?,?,?,?);";
	sqlite3_stmt *stmt;
	iRet = sqlite3_prepare_v2(db, pcInsertSql, strlen(pcInsertSql), &stmt, NULL);
	if(iRet != SQLITE_OK)
	{
		fprintf(stderr,"prepare db error:%s\n",sqlite3_errmsg(db));
		sqlite3_close(db);
	}
    printf("sqlite3_prepare_v2 success.\n");
    
    sqlite3_bind_int(stmt, 1, stPicInfo.iID);   //bind ID 
	sqlite3_bind_text(stmt, 2, stPicInfo.arrcGender, strlen(stPicInfo.arrcGender), NULL);   //bind gender
	sqlite3_bind_text(stmt, 3, stPicInfo.arrcName, strlen(stPicInfo.arrcName), NULL);   //bind name
    sqlite3_bind_int(stmt, 4, stPicInfo.iAge);   //bind age
	sqlite3_bind_blob(stmt, 5, stPicInfo.pData, iImgSize, NULL);    // bind data 
	
    printf("sqlite3_bind_blob success.\n");       
	//执行数据库操作
	sqlite3_step(stmt);
    printf("sqlite3_step success.\n");           

	//销毁语句
	sqlite3_finalize(stmt);
	//sqlite3_close(db);
	free(stPicInfo.pData);
    
    sleep(2);
    return 0;
}

int handle_delete(sqlite3 *db)
{
    int iRet = -1;  // return value
    char arrcDeleteSql[100]= {0};   // buffer to store the sql statement from user
    char *pcErrMsg = NULL;
    
    puts("Please enter the delete sql statement:");
    getchar();   // drop the enter key "\n" in stdin stream;           
    fgets(arrcDeleteSql, 100, stdin);
    
    printf("What you've entered is: %s\n", arrcDeleteSql);
    iRet = sqlite3_exec(db, arrcDeleteSql, LoadMyInfo, NULL, &pcErrMsg);
    if(iRet != SQLITE_OK)
    {
       fprintf(stderr, "SQL error: %s\n", pcErrMsg);
       sqlite3_free(pcErrMsg);
       return -1;
    }
    else
    {
        fprintf(stdout, "Delete done successfully\n");        
    }
    return 0;
}

int handle_update(sqlite3 *db)
{
    int iRet = -1;  // return value
    char arrcUpdateSql[100]= {0};   // buffer to store the sql statement from user
    char *pcErrMsg = NULL;
    
    puts("Please enter the update sql statement:");
    getchar();   // drop the enter key "\n" in stdin stream;           
    fgets(arrcUpdateSql, 100, stdin);
    
    printf("What you've entered is: %s\n", arrcUpdateSql);
    iRet = sqlite3_exec(db, arrcUpdateSql, LoadMyInfo, NULL, &pcErrMsg);
    if(iRet != SQLITE_OK)
    {
       fprintf(stderr, "SQL error: %s\n", pcErrMsg);
       sqlite3_free(pcErrMsg);
       return -1;
    }
    else
    {
        fprintf(stdout, "Update done successfully\n");        
    }
    return 0;
}

int handle_select(sqlite3 *db)
{   
    int iRet = -1;  // return value
    char arrcSelectSql[100]= {0};   // buffer to store the sql statement from user
    char *pcErrMsg = NULL;
    
    puts("Please enter the select sql statement:");
    getchar();   // drop the enter key "\n" in stdin stream;           
    fgets(arrcSelectSql, 100, stdin);
    
    printf("What you've entered is: %s\n", arrcSelectSql);
    iRet = sqlite3_exec(db, arrcSelectSql, LoadMyInfo, NULL, &pcErrMsg);
    if(iRet != SQLITE_OK)
    {
       fprintf(stderr, "SQL error: %s\n", pcErrMsg);
       sqlite3_free(pcErrMsg);
       return -1;
    }
    else
    {
        fprintf(stdout, "Select done successfully\n");        
    }
    return 0;
}

int handle_export_data(sqlite3 *db)
{   
    int iRet = -1;
    char arrcSelectSql[100]= {0};   // buffer to store the sql statement from user
    sqlite3_stmt *stmt;	//读取文件信息
	    
    puts("Please enter the select sql statement:");
    getchar();   // drop the enter key "\n" in stdin stream;           
    fgets(arrcSelectSql, 100, stdin);
    
    // select
    iRet = sqlite3_prepare_v2(db, arrcSelectSql, -1, &stmt, NULL);
	if(iRet != SQLITE_OK)
	{
		fprintf(stderr,"prepare db error:%s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 2;
	}
    iRet = sqlite3_step(stmt);   //execute
    /* definition of variables */
    int         iID;
    const char  *pGender;
    const char  *pName;
    int         iAge;
    const void  *pData;
    
    int iImgSize;
    while(iRet == SQLITE_ROW)//如果执行成功就写入到文件描述符
	{   
        iID = sqlite3_column_int(stmt, 0);
        pGender = sqlite3_column_text(stmt, 1);   
		pName = sqlite3_column_text(stmt, 2);
        iAge = sqlite3_column_int(stmt, 3);
		pData = sqlite3_column_blob(stmt, 4);
		iImgSize = sqlite3_column_bytes(stmt, 4);
		char arrcPicName[32] = {0}; // arrary to store the picture name
        snprintf(arrcPicName, sizeof(arrcPicName), "%s01.jpg", pName);
		printf("ID        GENDER    NAME      AGE       IMG_SIZE\n");
        printf("%-10d%-10s%-10s%-10d%-10d\n", iID, pGender, pName, iAge, iImgSize);
        
		int iFd = open(arrcPicName, O_WRONLY | O_CREAT, 0666);
		if(iFd < 0)
		{
			perror("open error:");
			return -1;
		}
		write(iFd, pData, iImgSize);
		close(iFd);
		iRet = sqlite3_step(stmt);//执行
 
	}
	sqlite3_finalize(stmt);
    
    return 0;
}

void handle_abnormal(void)
{
    puts("Invalid input! Please enter a number among(1, 2, 3, 4, 5)!");
}