当前位置:网站首页>Basic usage of SQLite (C language)

Basic usage of SQLite (C language)

2020-12-06 18:02:27 It's raining orange

One 、 What is? SQLite

  SQLite It's a software library , It's self-sufficient 、 serverless 、 Zero configuration 、 transactional SQL Database engine .SQLite It's the most widely deployed in the world SQL Database engine .SQLite Source code is not restricted by copyright .

Two 、SQL sentence

characteristic : Case insensitive , Add... After each sentence ";" ending .

keyword : select、insert、update、delete、from、creat、where、desc、order、by、group、table、alter、view、index etc. , You cannot use keywords to name tables and fields in a database .

Addition and deletion of tables

  • new table
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);
  • Delete table
DROP TABLE database_name.table_name;

Add, delete, modify and check the records

  • increase
 Specified column :
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

 All columns :
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
  • Delete
DELETE FROM table_name
WHERE [condition];
 If you need to delete all the records in the table :DELETE FROM table_name
  • Change
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
  • check
 Specified column :
SELECT column1, column2, columnN FROM table_name;
 All columns :
SELECT * FROM table_name;

more SQLite Of SQL Please refer to :https://www.runoob.com/sqlite/sqlite-tutorial.html

3、 ... and 、SQLite The basic usage of

  1、 download SQLite Source code , It mainly includes :shell.c、sqlite3.c、sqlite3.h and sqlite3ext.h.

SQLite Source code download address :https://www.sqlite.org/index.html

  2、 Include header file :

#include "sqlite3.h"

  3、 initialization SQLite:

int sqlite3_initialize(void)

  4、 Connect to database :

/*  Open the database according to the file path , If it doesn't exist , A new database will be created . */
int sqlite3_open(const char *zFilename, sqlite3 **ppDb)

  5、 perform SQL sentence :

int sqlite3_exec(
  sqlite3 *db,                /* The database on which the SQL executes */
  const char *zSql,           /* The SQL to be executed */
  sqlite3_callback xCallback, /* Invoke this callback routine */
  void *pArg,                 /* First argument to xCallback() */
  char **pzErrMsg             /* Write error messages here */
)

  6、 Use sqlite3_prepare Query datasets (sqlite3_stmt*), Examples are as follows :

char sql = "SELECT * FROM Book;";
sqlite3_stmt* stmt = NULL; /*  Data sets  */
/* -1 On behalf of the system will automatically calculate SQL The length of the statement  */
sqlite3_prepare(br_sqlite3->db, sql, -1, &stmt, NULL)
/*  Every time you tune sqlite3_step() function ,stmt It will point to the next record  */
while(sqlite3_step(stmt) == SQLITE_ROW) {
  sqlite3_column_int(stmt, 0);  /*  For the first 0 Column int value  */
  sqlite3_column_text(stmt, 1); /*  For the first 1 Column text value  */
  ...
 }
sqlite3_finalize(stmt);  /*  Release the dataset  */

  7、 Close the database :

int sqlite3_close(sqlite3 *db)

  8、 close SQLite:

int sqlite3_shutdown(void)

Four 、SQLite stay C Application in language

   Function description : be based on SQLite Database to achieve the library management system in book_repository_sqlite3_t class , This class contains the creation of SQLite3 Book warehouse 、 Get all the books 、 Additions and deletions 、 Empty books and destroy SQLite3 The function of the book warehouse .

Be careful :book_repository_sqlite3_t yes book_repository_t Subclasses of ,book_repository_t See the article for the base class code :Repository Pattern and its application in C Application in language .

1、book_repository_sqlite3_t The header file (book_repository_sqlite3.h)

#ifndef _BOOK_REPOSITORY_SQLLITE3_H
#define _BOOK_REPOSITORY_SQLLITE3_H

#include "book_repository.h"
#include "../../3rd/sqlite3/sqlite3.h"

BEGIN_C_DECLS

/**
 * @class book_repository_sqlite3_t
 * @parent book_repository_t
 * 
 * sqlite3 Book warehouse .
 * 
 */
typedef struct _book_repository_sqlite3_t {
  book_repository_t br;

  /**
   * @property {char*} app_name
   * @annotation ["readable"]
   * sqlite3 Database files (book.db) The catalog of .
   */
  char* app_name;
  /**
   * @property {sqlite3*} db
   * @annotation ["readable"]
   * sqlite3 Database instance .
   */
  sqlite3* db;
} book_repository_sqlite3_t;

/**
 * @method book_repository_sqlite3_create
 *  establish sqlite3 Book warehouse .
 * 
 * @param {char*} app_name  sqlite3 Database files (book.db) The catalog of ..
 *
 * @return {book_repository_t*} book repository object .
 */
book_repository_t* book_repository_sqlite3_create(char* app_name);

END_C_DECLS

#endif /*_BOOK_REPOSITORY_SQLLITE3_H*/

2、book_repository_sqlite3_t The source file (book_repository_sqlite3.c)

(1) establish sqlite3 Book warehouse

/*  Prepare database file path  */
static ret_t prepare_database_file(char db_filename[MAX_PATH + 1], const char* app_name,
                                   const char* db_name) {
  char home[MAX_PATH + 1];
  char path[MAX_PATH + 1];

  fs_get_user_storage_path(os_fs(), home);
  path_build(path, MAX_PATH, home, app_name, NULL);

  if (!path_exist(path)) {
    fs_create_dir(os_fs(), path);
  }
  path_build(db_filename, MAX_PATH, path, db_name, NULL);

  return RET_OK;
}

/*  Initialize database ( establish book surface ) */
static ret_t database_init(char db_filename[MAX_PATH + 1], book_repository_sqlite3_t* br_sqlite3) {
  char* err_msg = NULL;

  sqlite3_initialize();
  ret_t ret_file = prepare_database_file(db_filename, br_sqlite3->app_name, "book.db");
  return_value_if_fail(ret_file == RET_OK, RET_FAIL);
  int rc = sqlite3_open(db_filename, &(br_sqlite3->db));
  return_value_if_fail(rc == SQLITE_OK, RET_FAIL);

  /* create table */
  const char* sql =
      "CREATE TABLE IF NOT EXISTS Book ("
      "id INT PRIMARY KEY NOT NULL, "
      "title TEXT NOT NULL, "
      "author TEXT NOT NULL, "
      "press TEXT NOT NULL, "
      "year INT NOT NULL, "
      "month INT NOT NULL, "
      "day INT NOT NULL, "
      "number INT NOT NULL);";

  /* exec sql(callback is NULL) */
  rc = sqlite3_exec(br_sqlite3->db, sql, NULL, NULL, &err_msg);
  return_value_if_fail(rc == SQLITE_OK, (sqlite3_free(err_msg), RET_FAIL));

  return RET_OK;
}

/*  establish sqlite3 Book warehouse  */
book_repository_t* book_repository_sqlite3_create(char* app_name) {
  return_value_if_fail(app_name != NULL, NULL);

  book_repository_sqlite3_t* br_sqlite3 = TKMEM_CALLOC(1, sizeof(book_repository_sqlite3_t));
  book_repository_t* br = (book_repository_t*)br_sqlite3;
  return_value_if_fail(br != NULL, NULL);

  br->get_all = book_repository_sqlite3_get_all;
  br->add = book_repository_sqlite3_add;
  br->remove = book_repository_sqlite3_remove;
  br->update = book_repository_sqlite3_update;
  br->find = book_repository_sqlite3_find;
  br->clear = book_repository_sqlite3_clear;
  br->destroy = book_repository_sqlite3_destroy;

  br_sqlite3->app_name = tk_strdup(app_name);
  br_sqlite3->db = NULL;
  char db_filename[MAX_PATH + 1] = {0};

  ret_t ret = database_init(db_filename, br_sqlite3);
  return_value_if_fail(ret == RET_OK, (br->destroy(br), NULL));

  return br;
}

(2) Get all the books

static darray_t* book_repository_sqlite3_get_all(book_repository_t* br, darray_t* darray) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  darray_clear(darray);
  char sql[MAX_SQL] = {0};
  sqlite3_stmt* stmt = NULL; /* sava data */
  tk_snprintf(sql, sizeof(sql), "SELECT * FROM Book;");
  int rc = sqlite3_prepare(br_sqlite3->db, sql, -1, &stmt, NULL);
  return_value_if_fail(rc == SQLITE_OK, darray);

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    if (darray->size < darray->capacity) {
      book_t* book = book_create();
      book_set_id(book, sqlite3_column_int(stmt, 0));
      book_set_title(book, sqlite3_column_text(stmt, 1));
      book_set_author(book, sqlite3_column_text(stmt, 2));
      book_set_press(book, sqlite3_column_text(stmt, 3));
      book_set_year(book, sqlite3_column_int(stmt, 4));
      book_set_month(book, sqlite3_column_int(stmt, 5));
      book_set_day(book, sqlite3_column_int(stmt, 6));
      book->number = sqlite3_column_int(stmt, 7);
      darray_push(darray, book);
    }
  }
  sqlite3_finalize(stmt);
  return darray;
}

(3) Add books

static ret_t book_repository_sqlite3_add(book_repository_t* br, book_t* book) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  book_t* bk = book_repository_sqlite3_find(br, book);
  if (bk != NULL) {
    bk->number++;
    book_repository_sqlite3_update(br, bk, bk);
    book_destroy(bk);
  } else {
    book->number = 1;
    char sql[MAX_SQL] = {0};
    char* err_msg = NULL;
    tk_snprintf(sql, sizeof(sql), "INSERT INTO Book VALUES (%d, '%s', '%s', '%s', %d, %d, %d, %d);",
                book->id, book->title, book->author, book->press, book->year, book->month,
                book->day, book->number);
    int rc = sqlite3_exec(br_sqlite3->db, sql, NULL, NULL, &err_msg);
    return_value_if_fail(rc == SQLITE_OK, (sqlite3_free(err_msg), RET_FAIL));
  }
  return RET_OK;
}

(4) Delete books

static ret_t book_repository_sqlite3_remove(book_repository_t* br, book_t* book, int32_t number) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  book_t* bk = book_repository_sqlite3_find(br, book);
  if (bk != NULL) {
    int32_t book_number = bk->number - number;
    if (book_number > 0) {
      bk->number = book_number;
      book_repository_sqlite3_update(br, bk, bk);
    } else {
      char sql[MAX_SQL] = {0};
      char* err_msg = NULL;
      tk_snprintf(sql, sizeof(sql), "DELETE FROM Book WHERE id = %d;", bk->id);
      int rc = sqlite3_exec(br_sqlite3->db, sql, NULL, NULL, &err_msg);
      return_value_if_fail(rc == SQLITE_OK, (sqlite3_free(err_msg), RET_FAIL));
    }
    book_destroy(bk);
  } else {
    return RET_FAIL;
  }
  return RET_OK;
}

(5) Revise the book

static ret_t book_repository_sqlite3_update(book_repository_t* br, book_t* new_book,
                                            book_t* old_book) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  book_t* bk = book_repository_sqlite3_find(br, old_book);
  if (bk == NULL) {
    return RET_FAIL;
  }
  char sql[MAX_SQL] = {0};
  char* err_msg = NULL;
  tk_snprintf(sql, sizeof(sql),
              "UPDATE Book SET id = %d, title = '%s', author = '%s', press = '%s', year = %d, "
              "month = %d, day = %d, number = %d WHERE id = %d;",
              new_book->id, new_book->title, new_book->author, new_book->press, new_book->year,
              new_book->month, new_book->day, new_book->number, old_book->id);
  int rc = sqlite3_exec(br_sqlite3->db, sql, NULL, NULL, &err_msg);
  return_value_if_fail(rc == SQLITE_OK, (sqlite3_free(err_msg), RET_FAIL));
  book_destroy(bk);
  return RET_OK;
}

(6) Search for books

static book_t* book_repository_sqlite3_find(book_repository_t* br, book_t* book) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  char sql[MAX_SQL] = {0};
  sqlite3_stmt* stmt = NULL; /* sava data */

  tk_snprintf(sql, sizeof(sql), "SELECT * FROM Book WHERE id = %d;", book->id);
  int rc = sqlite3_prepare(br_sqlite3->db, sql, -1, &stmt, NULL);
  return_value_if_fail(rc == SQLITE_OK, NULL);

  if (sqlite3_step(stmt) == SQLITE_ROW) {
    book_t* ret_book = book_create();
    book_init(ret_book, sqlite3_column_int(stmt, 0), sqlite3_column_text(stmt, 1),
              sqlite3_column_text(stmt, 2), sqlite3_column_text(stmt, 3),
              sqlite3_column_int(stmt, 4), sqlite3_column_int(stmt, 5),
              sqlite3_column_int(stmt, 6));
    ret_book->number = sqlite3_column_int(stmt, 7);
    sqlite3_finalize(stmt);
    return ret_book;
  }

(7) Empty books

static ret_t book_repository_sqlite3_clear(book_repository_t* br) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)br;
  char* err_msg = NULL;
  const char* sql = "DELETE FROM Book;";
  int rc = sqlite3_exec(br_sqlite3->db, sql, NULL, NULL, &err_msg);
  return_value_if_fail(rc == SQLITE_OK, (sqlite3_free(err_msg), RET_FAIL));
  return RET_OK;
}

(8) The destruction sqlite3 Book warehouse

static ret_t book_repository_sqlite3_destroy(book_repository_t* br) {
  book_repository_sqlite3_t* br_sqlite3 = (book_repository_sqlite3_t*)(br);
  TKMEM_FREE(br_sqlite3->app_name);
  sqlite3_close(br_sqlite3->db);
  sqlite3_shutdown();
  TKMEM_FREE(br);
  return RET_OK;
}

版权声明
本文为[It's raining orange]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/12/20201206180031097n.html