//打開(kāi)數(shù)據(jù)庫(kù),如不存在則會(huì)創(chuàng)建數(shù)據(jù)庫(kù)
int ret = sqlite3_open("project_data.db", &db);
if( ret )
{
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    exit(-1);
}
fprintf(stderr, "Opened database successfully\n");

//CREATE TABLE 告訴數(shù)據(jù)庫(kù)系統(tǒng)創(chuàng)建一個(gè)新表的關(guān)鍵字。CREATE TABLE 語(yǔ)句后跟著表的唯一的名稱或標(biāo)識(shí)。
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);
char table_name[200] = {0};
char *err_msg = NULL;
snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
int ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret){
    fprintf(stderr, "create table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "create table successfully\n");

//SQLite 的 DROP TABLE 語(yǔ)句用來(lái)刪除表定義及其所有相關(guān)數(shù)據(jù)、索引、觸發(fā)器、約束和該表的權(quán)限規(guī)范。
//DROP TABLE 語(yǔ)句的基本語(yǔ)法如下。
DROP TABLE database_name.table_name;
char table_name[200] = {0};
char *err_msg = NULL;
snprintf(table_name, sizeof(table_name), "DROP TABLE database_name.table_name;");
int ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret){
    fprintf(stderr, "delete table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "delete table successfully\n");

INSERT INTO 語(yǔ)句有兩種基本語(yǔ)法，如下所示：
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  VALUES (value1, value2, value3,...valueN);//在這里，column1, column2,...columnN 是要插入數(shù)據(jù)的表中的列的名稱
或
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
//確保值的順序與列在表中的順序一致。
char table_value[200] = {0};
int ret = 0;
char *err_msg = NULL;
snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
if(ret)
{
    fprintf(stderr, "insert value to table err:%s\n", err_msg);
    return -1;
}
fprintf(stderr, "insert value to table successfully\n");

//SQLite 的 SELECT 語(yǔ)句用于從 SQLite 數(shù)據(jù)庫(kù)表中獲取數(shù)據(jù)，以結(jié)果表的形式返回?cái)?shù)據(jù)。這些結(jié)果表也被稱為結(jié)果集。
//SQLite 的 SELECT 語(yǔ)句的基本語(yǔ)法如下：
SELECT column1, column2, columnN FROM table_name;//在這里，column1, column2...是表的字段，他們的值即是您要獲取的。
SELECT * FROM table_name;    //獲取所有可用的字段
char *err_msg = NULL;
sprintf(sql, "select * from table_value;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg);   //執(zhí)行 SQL 命令的快捷方式
if(ret)
{
    fprintf(stderr, "Can't select sqlite value: %s\n", sqlite3_errmsg(db));
    return -1;
}

//SQLite 的 DELETE 查詢用于刪除表中已有的記錄?？梢允褂脦в?WHERE 子句的 DELETE 查詢來(lái)刪除選定行，否則所有的記錄都會(huì)被刪除。
//帶有 WHERE 子句的 DELETE 查詢的基本語(yǔ)法如下：
DELETE FROM table_name
WHERE [condition];
//可以使用 AND 或 OR 運(yùn)算符來(lái)結(jié)合 N 個(gè)數(shù)量的條件。
char *err_msg = NULL;
sprintf(sql, "DELETE FROM camera WHERE time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg);   //執(zhí)行 SQL 命令的快捷方式
if(ret)
{
    fprintf(stderr, "Can't DELETE sqlite data: %s\n", sqlite3_errmsg(db));
    return -1;
}

//SQLite 的 UPDATE 查詢用于修改表中已有的記錄?？梢允褂脦в?WHERE 子句的 UPDATE 查詢來(lái)更新選定行，否則所有的行都會(huì)被更新。
//帶有 WHERE 子句的 UPDATE 查詢的基本語(yǔ)法如下：
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
char *err_msg = NULL;
sprintf(sql, "UPDATE camera SET action = 't' WHERE time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg);   //執(zhí)行 SQL 命令的快捷方式
if(ret)
{
    fprintf(stderr, "Can't DELETE sqlite data: %s\n", sqlite3_errmsg(db));
    return -1;
}

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlite3.h>
int database_init();
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current);
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time);
long long get_current_timestamp_ms(void); 
int main(void)
{
    printf("sqlite3 database test!\n");    
    database_init();
    return 0;
}
/**
  * @brief  數(shù)據(jù)庫(kù)初始化
  * @param  NONE
  * @retval 成功返回0， 失敗返回-1
  */
int database_init(void)  
{
    int ret = -1;
    sqlite3 *db;
    char *err_msg = NULL;
    char database_name[128] = {0};
    //獲取當(dāng)前時(shí)間
    struct tm t; 
    time_t now;
    time(&now);
    localtime_s(&t, &now);
    snprintf(database_name, sizeof(database_name),"%02d%02d%02d.db", t.tm_year + 1900, t.tm_mon + 1, t.tm_mday);
    printf("date:%s\n", database_name);
   //打開(kāi)數(shù)據(jù)庫(kù)
    ret = sqlite3_open(database_name, &db);
    if( ret )
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    fprintf(stderr, "Opened database successfully\n");
    char table_name[200] = {0};
    //時(shí)間戳 目標(biāo)位置 實(shí)際位置 實(shí)際速度 實(shí)際電流
    //create table if not exists motor0 (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current, real);
    for(int motor_id = 0; motor_id < 6; motor_id++)
    {
        snprintf(table_name, sizeof(table_name),"create table if not exists motor%d (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current real);", motor_id);
        ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
        if(ret)
        {
            fprintf(stderr, "create table err:%s\n", err_msg);
            return -1;
        }
        fprintf(stderr, "create table successfully\n");
    }
    memset(table_name, 0, sizeof(table_name));
    snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
    ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
    if(ret)
    {
        fprintf(stderr, "create table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "create table successfully\n");
    write_motor_info_to_database(db, 0, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 1, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 2, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 3, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 4, 90.0, 87.2, 5.0, 0.85);
    write_motor_info_to_database(db, 5, 90.0, 87.2, 5.0, 0.85);
    write_camera_info_to_database(db, 't', 100,200,150,160,130,110,1000);
    return 0;
}
/**
  * @brief  寫(xiě)入電機(jī)數(shù)據(jù)到數(shù)據(jù)庫(kù)中
  * @param  db：數(shù)據(jù)庫(kù)文件描述符
  * @param  target_pos：目標(biāo)位置
  * @param  real_pos：實(shí)際位置
  * @param  real_speed：實(shí)際速度
  * @param  real_current：實(shí)際電流
  * @retval 寫(xiě)入成功返回0，失敗-1
  */
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current)
{
    char table_value[200] = {0};
    int ret = 0;
    char *err_msg = NULL;
    //insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
    snprintf(table_value, sizeof(table_value),"insert into motor%d values(%lld, %.2f, %.2f, %.2f, %.2f);", motor_id, get_current_timestamp_ms(), target_pos, real_pos, real_speed, real_current);
    ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
    if(ret)
    {
        fprintf(stderr, "insert value to table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "insert value to table successfully\n");
    return 0;
}
/**
  * @brief  寫(xiě)入相機(jī)數(shù)據(jù)到數(shù)據(jù)庫(kù)中
  * @param  db：數(shù)據(jù)庫(kù)文件描述符
  * @param  action:動(dòng)作
  * @param  x：
  * @param  y：
  * @param  z:
  * @param  vx：
  * @param  vy：
  * @param  vz:
  * @retval 寫(xiě)入成功返回0，失敗-1
  */
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time)
{
    char table_value[200] = {0};
    int ret = 0;
    char *err_msg = NULL;
    //insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
    snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
    ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
    if(ret)
    {
        fprintf(stderr, "insert value to table err:%s\n", err_msg);
        return -1;
    }
    fprintf(stderr, "insert value to table successfully\n");
    return 0;
}
/**
  * @brief  獲取毫秒級(jí)時(shí)間戳
  * @param  NONE
  * @retval 成功返回時(shí)間戳值，失敗返回-1
  */
long long get_current_timestamp_ms(void)
{
#if defined(_WIN32) || defined(_WIN64)
    struct _timeb timebuffer;
    _ftime64_s(&timebuffer);
    return (long long)timebuffer.time * 1000 + timebuffer.millitm;
#elif defined(__unix__) || defined(__unix) || defined(unix)
    struct timeval tv;
    gettimeofday(&tv, NULL);
    return (long long)tv.tv_sec * 1000 + tv.tv_usec / 1000;
#endif
}