安装使用数据库系统Sqlite3
安装数据库系统Sqlite3:
apt-get install sqlite sqlite3
检查数据库安装结果:
sqlite3 test.db
.database
.exit
提供下面的命令我们应该可以看到文件test.db。
ls
安装Sqlite3编译需要的工具包:
apt-get install libsqlite3-dev
cat rule_database.c
#include
#include
#include "sqlite3.h"
#define _DEBUG_
int
main (void)
{
sqlite3 *db = NULL;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open ("antidoscfg.db", &db);
if (rc)
{
fprintf (stderr, "Can't open database: %sn", sqlite3_errmsg (db));
sqlite3_close (db);
exit (EXIT_FAILURE);
}
else
printf ("You have opened a sqlite3 database
named antidoscfg.db successfully!n");
char *sql = " CREATE TABLE dos_table(
ID INTEGER PRIMARY KEY,
name VARCHAR(12),
interface VARCHAR(12),
s_ip VARCHAR(12),
s_mask VARCHAR(20),
d_ip VARCHAR(20),
d_mask VARCHAR(20),
sip_status VARCHAR(20),
sip_id INTEGER,
dip_status VARCHAR(20),
dip_id INTEGER,
schedule VARCHAR(10),
service VARCHAR(10),
profile VARCHAR(10)
);";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database insert---antidoscfg add********************/
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database update---antidoscfg modify********************/
sql = "UPDATE "dos_table" set name='o2',interface='eth2' where id=2";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database delete---antidoscfg del********************/
sql = "delete from "dos_table" where id=1";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj1',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy1',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj2',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy2',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database select top ---antidoscfg get_count N********************/
int nrow = 0, ncolumn = 0, i = 0;
char **azResult; //二维数组存放结果
sql = "SELECT * FROM dos_table order by id limit 3";
sqlite3_get_table (db, sql, &azResult, &nrow, &ncolumn, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
printf ("row:%d column=%d n", nrow, ncolumn);
printf ("nThe result of querying is : n");
for (i = 0; i
printf ("azResult[%d] = %sn", i, azResult[i]);
//释放掉 azResult 的内存空间
sqlite3_free_table (azResult);
sqlite3_close (db); //关闭数据库
return 0;
}
cat rule_database.c
#include
#include
#include "sqlite3.h"
#define _DEBUG_
int
main (void)
{
sqlite3 *db = NULL;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open ("antidoscfg.db", &db);
if (rc)
{
fprintf (stderr, "Can't open database: %sn", sqlite3_errmsg (db));
sqlite3_close (db);
exit (EXIT_FAILURE);
}
else
printf ("You have opened a sqlite3 database
named antidoscfg.db successfully!n");
char *sql = " CREATE TABLE dos_table(
ID INTEGER PRIMARY KEY,
name VARCHAR(12),
interface VARCHAR(12),
s_ip VARCHAR(12),
s_mask VARCHAR(20),
d_ip VARCHAR(20),
d_mask VARCHAR(20),
sip_status VARCHAR(20),
sip_id INTEGER,
dip_status VARCHAR(20),
dip_id INTEGER,
schedule VARCHAR(10),
service VARCHAR(10),
profile VARCHAR(10)
);";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database insert---antidoscfg add********************/
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database update---antidoscfg modify********************/
sql = "UPDATE "dos_table" set name='o2',interface='eth2' where id=2";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database delete---antidoscfg del********************/
sql = "delete from "dos_table" where id=1";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj1',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy1',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'zj2',
'eth0',
'192.168.1.100',
'255.255.255.0',
'192.168.1.101',
'255.255.255.0',
'sip status good!','1',
'dip_status_good','2',
'mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
sql = "INSERT INTO "dos_table" VALUES(NULL,
'jimmy2',
'eth1',
'192.168.1.200',
'255.255.255.0',
'192.168.1.201',
'255.255.255.0',
'sip2 status good!',
'3',
'dip2 status good!','4','mmmm',
'nnnn',
'profilename');";
sqlite3_exec (db, sql, 0, 0, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
/**************database select top ---antidoscfg get_count N********************/
int nrow = 0, ncolumn = 0, i = 0;
char **azResult; //二维数组存放结果
sql = "SELECT * FROM dos_table order by id limit 3";
sqlite3_get_table (db, sql, &azResult, &nrow, &ncolumn, &zErrMsg);
#ifdef _DEBUG_
printf ("zErrMsg = %s n", zErrMsg);
#endif
printf ("row:%d column=%d n", nrow, ncolumn);
printf ("nThe result of querying is : n");
for (i = 0; i
printf ("azResult[%d] = %sn", i, azResult[i]);
//释放掉 azResult 的内存空间
sqlite3_free_table (azResult);
sqlite3_close (db); //关闭数据库
return 0;
}
cat Makefile
OBJS = rule_database.o
TARGET = rule_database
CFLAG = -g -Wall -O2 -lsqlite3
all: $(TARGET)
$(TARGET): $(OBJS)
$(CC) $(CFLAG) -o $(TARGET) $(OBJS)
%.o:%.c
$(CC) $(CFLAG) -o $@ -c $?
clean:
rm -rf $(OBJS) $(TARGET)
自己make下就可以了.这是我自己项目中的一部分大概涵盖全了.select delete insert update