c语言与数据库的创建使用

data.h头文件

struct ONLINE_USER
{
	int fd;	   			//-1
	int flage; 			// registed or not
	char name[32];
	char passwd[32];
};



int db_add_user(char name[], char passwd[]);



int db_user_if_reg(char *name);


int db_user_pwd_corrct(char *name, char *passwd);


int database_init();


void database_close();

data.c实现文件

#include 
#include 
#include 
#include 
#include 



#include 
#include 
#include 
#include 
#include 
#include "data.h"

#define DATABASE_NAME "user.db"
#define TABLE_USER "user"

pthread_mutex_t db_mutex;
sqlite3 *db;      //数据库



int db_add_user(char name[], char passwd[])
{
	char *errmsg;
	char sqlstr[1024] = {0};

	if ((name == NULL) || (passwd == NULL))
	{
		printf("Invalid name or password entered.rn");
		return -1;
	}
	if ((strlen(name) > 32) || (strlen(passwd) > 32))
	{
		printf("The entered name or password is too long.rn");
		return -1;
	}

	sprintf(sqlstr, "insert into %s values('%s', '%s',-1, 1,0)",
			TABLE_USER, name, passwd);

	if (sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != 0)
	{
		printf("Database query error: %srn", sqlite3_errmsg(db));
	}
	else
	{
		//printf("User added successfully.rn");
	}
	//printf("rn");
	return 1;
}



int db_user_if_reg(char *name)
{
	int state = -1;
	char **result, *errmsg;
	int nrow, ncolumn, i, j, index;
	char sqlstr[1024] = {0};

	sprintf(sqlstr, "select regist from %s where name='%s'",
			TABLE_USER, name);

	if (sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != 0)
	{
		printf("Database query error: %srn", errmsg);
		sqlite3_free(errmsg);
	}
	index = ncolumn;
	
	if (nrow > 0)
	{
		//在数据库中找到指定名字的用户(数据)，至少有一条数据
		state = 1;
	}
	else
	{
		//在数据库中没有找到指定名字的用户(数据)
		state = -1;
	}
	sqlite3_free_table(result);
	return state;
}


int db_user_pwd_corrct(char *name, char *passwd)
{
	int state = -1;
	char **result, *errmsg;
	int nrow, ncolumn, i, j, index;
	char sqlstr[1024] = {0};

	sprintf(sqlstr, "select * from %s where name='%s' and passwd='%s'",
			TABLE_USER, name, passwd);

	
	if (sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != 0)
	{
		printf("Database query error: %srn", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	index = ncolumn;
	
	if (nrow > 0)
	{
		//在数据库中找到指定名字和密码的用户(数据)
		state = 1;
	}
	else
	{
		//在数据库中没有找到指定名字和密码的用户(数据)		
		state = -1;
	}
	sqlite3_free_table(result);
	return state;
}


int database_init()
{
	int n;
	int ret;
	char *errmsg;
	char sql[] =
		"CREATE TABLE IF NOT EXISTS user(name INT PRIMARY KEY 	NOT NULL,passwd	TEXT	NOT NULL,fd	INT NOT NULL,regist	INT	 NOT NULL,keygen INT);";

	pthread_mutex_init(&db_mutex, NULL);

	if (sqlite3_open(DATABASE_NAME, &db) < 0)
	{
		printf("Failed to open sqlite3: %srn", sqlite3_errmsg(db));
		return -1;
	}

	
	ret = sqlite3_exec(db, sql, NULL, 0, &errmsg);

	if (ret != SQLITE_OK)
	{
		fprintf(stderr, "Database query error: %srn", errmsg);
		sqlite3_free(errmsg);
	}
	else
	{
		fprintf(stdout, "The database initialized successfully.rn");
	}
	return 0;
}


void database_close()
{
	sqlite3_close(db);
}