/**************************************************************************************
Function: init_database()
Description: 初始化数据库
Input:
Output:
Return: 0:成功，-1打开数据库失败 ，-2建数据库表失败
Others:
***************************************************************************************/
int init_database()
{
int result;
//sqlite3 * db;
int ret = 0;
//创建一表，表名叫video_conf
//const char *create_table_sql="create table if not exists video_conf(id int primary key,ipaddr varchar(60),rtspport varchar(20), httpport varchar(20),name varchar(50),password varchar(50),aliases nvarchar(100),indexid varchar(10),roomid varchar(10),ipc_status varchar(10)),DomainName nvarchar(100),SerialNumber nvarchar(100)";
const char *create_table_sql="create table if not exists video_conf(id int primary key,ipaddr varchar(60),rtspport varchar(20),\
httpport varchar(20),name varchar(50),password varchar(50),aliases nvarchar(100),indexid varchar(10),roomid varchar(10),ipc_status varchar(10),\
DomainName nvarchar(100),SerialNumber nvarchar(100))";
char* errmsg=NULL;
//result=sqlite3_open("video_conf.db",&db);
result=sqlite3_open(ipc_conf_database,&db);
if(result !=SQLITE_OK)
{
//数据库打开失败
//printf("open sqlite db faile\r\n");
printf("<p>addipc open sqlite db faile</p>");
ret = -1;
}
//建表
result = sqlite3_exec(db,create_table_sql, NULL, NULL, &errmsg);
if(result != SQLITE_OK )
{
//printf("error:%d,reasion:%s\n",result,errmsg);
printf("<p>error%d,reasion:%s</p>",result,errmsg);
ret = -2;
}
sqlite3_free(errmsg);
return ret;
}
int sqlite_db_open(sqlite_db_t * sql, char * sql_db_patch)
{
int exist = 0;
int fd;
int result;
char * errmsg;
while ((fd = open(sql_db_patch, O_RDWR | O_CREAT | O_EXCL)) < 0) {
if (errno == EINTR)
continue;
if (errno == EEXIST) {
exist = 1; //文件已经存在
break;
}
log_error(LOG_ERROR, "open");
return -1;
}
if (fd > 0) {
exist = 0; //文件不存在
unlink(sql_db_patch);
close(fd);
}
if (sqlite3_open(sql_db_patch, &sql->sqlite_db) != SQLITE_OK) { //数据库打开错误
log_error(LOG_ERROR, "sqlite3_open");
unlink(sql_db_patch);
if (sqlite3_open(sql_db_patch, &sql->sqlite_db) != SQLITE_OK) {
log_error(LOG_ERROR, "sqlite3 fatal error");
return -1;
}
exist = 0;
}
if (exist)
return 0;
result = sqlite3_exec(sql->sqlite_db, "create table USER(user_name nvarchar(32) primary key,\
user_password nvarchar(32) NOT NULL, user_email nvarchar(32), \
user_phone nvarchar(20));",
NULL, NULL, &errmsg ); //用户表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table AREA( area_id integer primary key,\
area_name nvarchar(32) NOT NULL);",
NULL, NULL, &errmsg ); //区域表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table OWEN( owener_id integer primary key autoincrement,\
user_name nvarchar(32) not NULL, area_id integer not NULL);",
NULL, NULL, &errmsg ); //用户拥有房间表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table TEM( tem_id integer primary key autoincrement, area_id integer,\
timestamp nvarchar(32), tem_data float, tem_gate float, ext_addr nvarchar(8));",
NULL, NULL, &errmsg ); //温度数据表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table APP( app_id integer primary key autoincrement, \
area_id integer NOT NULL, app_name nvarchar(32) NOT NULL,\
red_id integer);",
NULL, NULL, &errmsg ); //家电
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table IR_CODE( ir_code_id integer primary key,\
ir_name integer not null);",
NULL, NULL, &errmsg ); //红外编码表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table IR_ZIGBEE( ir_zig_id integer primary key autoincrement,\
red_code_id integer,\
ext_addr nvarchar[30] not NULL,\
version integer not NULL);",
NULL, NULL, &errmsg);
if (result != SQLITE_OK) { //红外和 zigbee的映射表
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d error msg = %s\n", __LINE__,errmsg );
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table RED_KEY( red_key_id integer primary key,\
red_code_id integer NOT NULL, red_path nvarchar[30] not NULL);",
NULL, NULL, &errmsg ); //红外键码表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
/* 创建一个红外编码表存储键值 */
result = sqlite3_exec(sql->sqlite_db, "create table IR_KEY(\
ir_key_type integer not null, \
ir_key_word integer not null,\
ir_key_code integer NOT NULL, \
ir_key_data BLOB not NULL,\
ir_key_version integer not null,\
primary key(ir_key_type, ir_key_word, ir_key_code));",
NULL, NULL, &errmsg ); //红外键码表
if (result != SQLITE_OK) {
log_error(LOG_ERROR, errmsg);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table CTRL_BLIND( ctrl_blind_id integer primary key autoincrement,\
area_id integer NOT NULL, user_name nvarchar[32] not NULL,\
action_time nvarchar[50] NOT NULL, action_direction integer NOT NULL,\
ext_addr nvarchar[30] NOT NULL\
);",
NULL, NULL, &errmsg ); //窗帘表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
result = sqlite3_exec(sql->sqlite_db, "create table MODULE( ext_addr nvarchar[20] primary key,\
moudle_name integer NOT NULL, area_id integer NOT NULL);",
NULL, NULL, &errmsg ); //模块表
if(result != SQLITE_OK ) {
log_error(LOG_ERROR, errmsg);
printf("\nsqlite3_exec line = %d\n", __LINE__);
goto err_quit;
}
/* 血压表 */
result = sqlite3_exec(sql->sqlite_db, "create table BLOOD_PRESS( time integer primary key,\
ext_addr nvarchar[20] not null,\
press_high float NOT NULL, press_low float NOT NULL, \
rate float NOT NULL, tem float NOT NULL);",
NULL, NULL, &errmsg );
return 0;
err_quit:
unlink(sql_db_patch);
return -1;
}
