安装使用数据库系统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: %s\n", 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 < (nrow + 1) * ncolumn; i++)
printf ("azResult[%d] = %s\n", 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: %s\n", 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 < (nrow + 1) * ncolumn; i++)
printf ("azResult[%d] = %s\n", 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
阅读(786) | 评论(0) | 转发(0) |
