summary
sqlite3 is a small database with less code and less memory. It is written in C, so it is naturally suitable for embedded systems, especially embedded linux. It is quite supported. sqlite3 can run directly through the shell, but this is only limited to testing. In actual project programming, we still use the C/C + + interface function provided by sqlite3, That is, the API interface.
sqlite3 migration
Download C source code from sqlite official website: https://www.sqlite.org/download.html
Unzip the package: tar -zxvf sqlite-autoconf-3360000.tar.gz
Run the configuration file:. / configure -- host = arm linux -- prefix = / work / SRC_ packages/sqlite-autoconf-3071401/dist-sqlite3
– host: Specifies the cross compilation tool, which is consistent with the compiler for compiling Linux
– prefix: Specifies the installation directory where the files generated after compilation are placed. They must be absolute paths
Compile: execute make and then make install
After compilation, the dist SQLite3 folder is generated in the source directory
Copy / dist-sqlite3/bin/sqlite3 to the / bin directory of the development board, and execute chmod 777 sqlite3.
Copy all files of / dist SQLite3 / lib / to the / lib directory of the development board.
Enter "sqlite3" at the terminal to enter sqlite3.
sqlite3 common commands
Enter SQLite3 command terminal: #sqlite3 test.db
Get point command list: SQLite >. Help
Exit sqlite3 operation: SQLite >. Exit or SQLite >. Quit
Open or create the database: SQLite >. Open test.db
View database file information: SQLite >. Database
Create table: CREATE TABLE statement;
View all tables: SQLite >. Tables
View the complete information of all tables: SQLite >. Schema
Drop table: DROP TABLE statement
Add a new data row to a table: INSERT INTO statement
Getting data from database tables: SELECT statement
List the configuration of the current display format: SQLite >. Show
Open header for setting display format: SQLite >. Header on
Setting the display format: SQLite >. Width 2
Example:
# sqlite3 SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open test.db sqlite> .databases main: /backup0/test.db r/w sqlite> create table demo (id int,name char,age int); sqlite> .tables demo sqlite> .schema CREATE TABLE demo (id int,name char,age int); sqlite> insert into demo values(100,'WangWu',23); sqlite> insert into demo values(101,'Tommm',25); sqlite> .header on sqlite> .mode column sqlite> select * from demo; id name age --- ------ --- 100 WangWu 23 101 Tommm 25 sqlite> .exit #
sqlite3 API usage
Open database
int sqlite3_ Open (file name, SQLite3 * *);
close database
int sqlite3_close(sqlite3 *);
Execute sql statement
int sqlite3_exec(sqlite3*,const char sql,sqlite3_callback,void,char **errmsg );
query data base
int sqlite3_get_table(sqlite3*,const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);
sqlite3 programming example
Implementation idea:
- Open the database and execute sqlite3_open；
- Create database tables and use sqlite3_exec executes the create table statement;
- Execute insert, delete, query and exit operations according to the input instructions;
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#include <strings.h>
#define DATABASE "fru.db"
typedef struct{
char time[20];
char name[16];
float cost;//cost
float price;//price
char in_out[5];//Incoming in, outgoing out
float number;//Trading volume
float money;//Transaction amount (cost or selling price * transaction volume)
float total;//Total inventory
}fruit_t;
void operate_table_cmd_menu()
{
puts("/******************************************/");
puts("*operate table cmd menu:");
printf("*1:insert 2:delete 3:query 4:update 5:quite \n");
puts("/******************************************/");
printf("Please input cmd:");
}
const char *get_local_time(char *time_str);
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit);
int query_callback(void *para, int f_num, char **f_value, char **f_name);
int do_insert_sqlite3(sqlite3 * db);
int do_delete_sqlite3(sqlite3 * db);
int do_query_sqlite3(sqlite3 * db);
void get_columns_name(sqlite3 * db);//Get field name
int do_update_sqlite3(sqlite3 * db);
int main(int argc, const char *argv[])
{
sqlite3 * db;
char *errmsg;
int cmd;
char sql[128]={};
//Open sqlite3 database
if(sqlite3_open(DATABASE,&db) != SQLITE_OK)
{
printf("%s\n",sqlite3_errmsg(db));
exit(-1);
}
else
{
printf("open %s success.\n",DATABASE);
}
//Create a table in the database, set the ID as the primary key value and set it as a self increasing field (ensure the uniqueness of the field)
sprintf(sql,"create table fru(time char,name char,cost Integer,price Integer,in_out char,number Integer,money Integer,total Integer);");
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}
else
{
printf("create or open table success.\n");
}
while(1)
{
operate_table_cmd_menu();
while(scanf("%d",&cmd) == 0)
{
getchar();
operate_table_cmd_menu();
}
getchar();
switch(cmd)
{
case 1:
do_insert_sqlite3(db);
break;
case 2:
do_delete_sqlite3(db);
break;
case 3:
do_query_sqlite3(db);
break;
case 4:
do_update_sqlite3(db);
break;
case 5:
sqlite3_close(db);
exit(0);
default:
printf("Error cmd.\n");
}
}
return 0;
}
const char *get_local_time(char *time_str)
{
time_t tim_t;
struct tm loc_t;
time(&tim_t);
loc_t = *localtime(&tim_t);
sprintf(time_str,"%04d-%d-%02d %02d:%02d:%02d",loc_t.tm_year+1900,loc_t.tm_mon+1,loc_t.tm_mday,loc_t.tm_hour,loc_t.tm_min,loc_t.tm_sec);
return time_str;
}
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit)
{
char **resultp;
int nrow;
int ncolumn;
char sql[128];
int index_base;
char *errmsg;
sprintf(sql,"select * from fru where name='%s';",fruit->name);
sqlite3_get_table(db,sql,&resultp,&nrow,&ncolumn,&errmsg);
if(nrow == 0)
{
return NULL;
}
else
{
index_base = (nrow)*(ncolumn);
fruit->cost = atof(resultp[index_base+2]);
fruit->price = atof(resultp[index_base+3]);
fruit->total = atof(resultp[index_base+7]);
}
return fruit;
}
int do_insert_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char type[10];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//This fruit has been recorded in the system
{
puts("*************************************");
printf("%s cost:%.2f price:%.2f\n",fruit.name,fruit.cost,fruit.price);
puts("*************************************");
printf("if you change it[y|n]:");
scanf("%s",type);
getchar();
if(strncasecmp(type,"y",strlen("y")) == 0)
{
char value[20];
printf("change cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
scanf("%s",value);//Enter the cost to modify the cost, and enter n to discard the modification
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.cost = atof(value);
printf("change price:%.2f of %s [price|n]:",fruit.price,fruit.name);
scanf("%s",value);//Enter the unit price to modify the unit price, and enter n to discard the modification
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.price = atof(value);
}
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.total +fruit.number;
}
else//There are no fruit types in the system
{
printf("Please input the cost of %s [cost]:",fruit.name);
while(scanf("%f",&fruit.cost) == 0)
{
getchar();
printf("Please input the cost of %s [cost]:",fruit.name);
}
getchar();
printf("Please input the price of %s [price]:",fruit.name);
while(scanf("%f",&fruit.price) == 0)
{
getchar();
printf("Please input the price of %s [price]:",fruit.name);
}
getchar();
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.number;
}
sprintf(fruit.in_out,"in");
fruit.money = fruit.cost * fruit.number;
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//The value of the auto increment field is passed to NULL
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("insert:\n ");
sprintf(sql,"select * from fru where time='%s';",fruit.time);
get_columns_name(db);//Get field name
sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
printf("done\n");
return 0;
}
}
int query_callback(void *para, int f_num, char **f_value, char **f_name)
{
int i;
for(i=0;i<f_num;i++)
{
if(i == 0)
printf("\t%-19s ",f_value[i]);
else
printf("%-10s ",f_value[i]);
}
puts("");
return 0;
}
void get_columns_name(sqlite3 * db)
{
char **resultp;
int nrow;
int ncolumn;
char *errmsg;
int i;
sqlite3_get_table(db,"select * from fru",&resultp,&nrow,&ncolumn,&errmsg);
printf("\t%-19s ",resultp[0]);
for(i=1;i<ncolumn;i++)
{
printf("%-10s ",resultp[i]);
}
puts("");
}
int do_delete_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[20];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//There is this fruit in the system
{
printf("input the out number of %s:",fruit.name);
scanf("%s",value);
getchar();
if(atof(value) > fruit.total)//The remaining quantity is not enough
{
printf("The total number:%.4f of %s is not enough, please stock it.\n",fruit.total,fruit.name);
return -1;
}
else
{
fruit.number = atof(value);
fruit.money = fruit.price * fruit.number;
fruit.total = fruit.total - fruit.number;
}
}
else//There is no such fruit in the system
{
printf("We don't have this fruit:%s. Please stock it.",fruit.name);
return -1;
}
sprintf(fruit.in_out,"out");
printf("are you sure delete number:%.4f of %s[y|n]:",fruit.number,fruit.name);
scanf("%s",value);
getchar();
if(strncasecmp(value,"y",strlen("y")) == 0)
{
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//The value of the auto increment field is passed to NULL
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("delete done\n ");
return 0;
}
}
else
{
printf("delete cancelled.\n");
return 0;
}
}
int do_query_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[96];
bzero(&fruit,sizeof(fruit));
puts("*************************************");
printf("*Examples of query types:\n");
puts("*[all | name='apple' | in_out='in']");
//puts("*[all total | apple total | banana total]");
puts("*[time<'2018-12-5 17:08:08' | total>90 and total<200]");
puts("*[name='apple' and in_out='in']");
puts("*************************************");
printf("Pease input query type:");
scanf("%s",value);
getchar();
if(strncasecmp("all",value,strlen(value)) == 0)
sprintf(sql,"select * from fru;");
else
sprintf(sql,"select * from fru where %s;",value);//The value of the auto increment field is passed to NULL
get_columns_name(db);//Get field name
if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("query done\n ");
return 0;
}
}
int do_update_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
int type;
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//This fruit has been recorded in the system
{
puts("*************************************");
printf("%s cost:%.2f price:%.2f\n",fruit.name,fruit.cost,fruit.price);
puts("*************************************");
printf("if you update it[1:yes 2:cancel]:");
scanf("%d",&type);
getchar();
char value[20];
if(type == 1)
{
printf("update cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
scanf("%s",value);//Enter the cost to modify the cost, and enter n to discard the modification
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.cost = atof(value);
printf("update price:%.2f of %s [price|n]:",fruit.price,fruit.name);
scanf("%s",value);//Enter the unit price to modify the unit price, and enter n to discard the modification
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.price = atof(value);
}
else
{
printf("update cancelled\n");
return 0;
}
sprintf(fruit.in_out,"update");
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//The value of the auto increment field is passed to NULL
if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("update:\n");
get_columns_name(db);//Get field name
sprintf(sql,"select * from fru where time='%s';",fruit.time);
sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
printf("update done\n ");
return 0;
}
}
else//There are no fruit types in the system
{
printf("We don't have this fruit:%s. Please stock it.",fruit.name);
}
return 0;
}
sqlite3 test verification
Cross compilation:
arm-linux-gcc -I /home/ubuntu/sqlite3/install/include/ -L /home/ubuntu/sqlite3/install/lib/ -o sql testSql.c -lsqlite3 –ldl
-I specifies the path of sqlite3.h,
-L specifies the lib library path of sqlite3.
Copy the generated executable file to the development board and run the test:
Links, sqlite3 rookie tutorial: https://www.runoob.com/sqlite/sqlite-create-table.html
Link, sqlite3 C code example: https://blog.csdn.net/m0_37542524/article/details/84842154