mysql

原创 2012年03月21日 16:48:15
#include <lua.h>
#include <lauxlib.h>
#include <mysql.h>
#include <string.h>
#include <stdio.h>
 
#define YO2MYSQL_CONN "YO2MySQL"
typedef struct {
	MYSQL *conn;
	char *dbname;
	int num_rows;
	int found_rows;
	int per_page;
} yo2mysql_connection_type;
 
int luaopen_yo2mysql (lua_State *L);
 
// Create a metatable and leave it on top of the stack.
int yo2mysql_register (lua_State *L, const char *name, const luaL_reg *methods) {
    if (!luaL_newmetatable (L, name))
        return 0;
 
    /* define methods */
    luaL_register (L, NULL, methods);
 
    /* define metamethods */
    lua_pushliteral (L, "__gc");
    lua_pushcfunction (L, methods->func);
    lua_rawset (L, -3);
 
    lua_pushliteral (L, "__index");
    lua_pushvalue (L, -2);
    lua_rawset (L, -3);
 
    lua_pushliteral (L, "__metatable");
    lua_pushliteral (L, "you're not allowed to get this metatable");
    lua_rawset (L, -3);
 
    return 1;
}
 
static int yo2mysql_connect (lua_State *L) {
 
	// This function init a connection and connect to a real database
	char * sqlarg[3]; // The first 3 arguments for mysql_real_connect: server, user, passwd
 
	// Get the first 3 arguments
	int i;
	for ( i = 0; i < 3; i++) {
		if (!lua_isstring(L, i+1)) {
			lua_pushnil(L);
			lua_pushstring(L, "Wrong type of argument!");
			return 2;
		}
		else {
			sqlarg[i] = lua_tostring(L, i+1);
		}
	}
 
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)lua_newuserdata(L, sizeof(yo2mysql_connection_type));
	luaL_getmetatable (L, YO2MYSQL_CONN);
	lua_setmetatable (L, -2);
	// Init the connection and connect it to a real database
	yo2mysql_connection->conn = mysql_init(NULL);
	if (!yo2mysql_connection->conn) {
        lua_pushstring(L, "Cannot init connection!");
		return 2;
    }
	if (!mysql_real_connect(yo2mysql_connection->conn, sqlarg[0], sqlarg[1], sqlarg[2], NULL, 0, NULL, 0)) {
		lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
		return 2;
	}
 
	// Push the connection pointer onto the stack
	return 1;
}
 
static int yo2mysql_close (lua_State *L) {
	// This function closes the given connection
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
	mysql_close(yo2mysql_connection->conn);
	yo2mysql_connection->conn = NULL;
	return 0;
}
 
char * yo2mysql_up_add(char *query) {
	// This function convert the query statement to UPPERCASE
	char *new = malloc(strlen(query)+10);
	char *s;
	strcpy(new, query);
 
	for (s = new; *s; s++){
		if ((*s>='a') && (*s<='z')) {
			*s-=32;
		}
	}
 
	// and add LIMIT 1 if it doesn't have the limitation
	char *slimit = " LIMIT";
	if (strstr(new, slimit) == NULL) {
		sprintf(new, "%s LIMIT 1", query);
	}
 
	return new;
}
 
int yo2mysql_calcperpage(char *query) {
	// This function returns the number after the comma after 'LIMIT' to the query statement
	// e.g a query with 'LIMIT 10, 20' will return 20
 
	// First convert the query statement to UPPERCASE
	char *new = malloc(strlen(query)+10);
	char *s;
	strcpy(new, query);
 
	for (s = new; *s; s++){
		if ((*s>='a') && (*s<='z')) {
			*s-=32;
		}
	}
 
	int per_page = 0;
 
	// and add LIMIT 1 if it doesn't have the limitation
	char *slimit = "LIMIT";
	if ((s = strstr(new, slimit)) != NULL) {
		s += 5;
		while (*s == ' ')
			s++;
		while (*s >= '0' && *s <= '9')
			s++;
		if (*s = ',') {
			s++;
			while (*s >= '0' && *s <= '9') {
				per_page = per_page * 10 + *s - '0';
				s++;
			}
		}
	}
 
	free(new);
 
	return per_page;
}
 
static int yo2mysql_query (lua_State *L) {
	// This function returns whether the query on a given connection has results
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	MYSQL_RES *res;
 
	// Get the query statement
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
 
	// Do the query on the given connection
	if (mysql_query(yo2mysql_connection->conn, sqlquery)) {
		lua_pushnil(L);
		lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
		return 2;
	}
 
	// Get the results set
	res = mysql_use_result(yo2mysql_connection->conn);
	unsigned int field_count = mysql_field_count(yo2mysql_connection->conn);
	if (res) {
		lua_pushnumber(L, 1);
	}
	else {
		if (field_count == 0) {
			lua_pushnumber(L, mysql_affected_rows(yo2mysql_connection->conn));
		}
		else {
			lua_pushnil(L);
			lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
			return 2;
		}
	}
	mysql_free_result(res); // Free the results set
 
	return 1;
}
 
static int yo2mysql_getresults (lua_State *L) {
	// This function returns a 2d table for a query on a given connection
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	// Get the query statement
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument!");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
 
	// Do the query on the given connection
	if (mysql_query(yo2mysql_connection->conn, sqlquery)) {
		lua_pushnil(L);
		lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
		return 2;
	}
 
	// Get the results set
	MYSQL_RES *res = mysql_use_result(yo2mysql_connection->conn);
 
	// Get the fields for field name
	unsigned int num_fields = mysql_num_fields(res);
	unsigned int i,row_num = 0;
	MYSQL_FIELD *fields;
	fields = mysql_fetch_fields(res);
 
	// Push the outter table for different rows onto the stack
	lua_newtable(L);
 
	// Get each row
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res)) != NULL) {
		row_num++;
		lua_pushnumber(L, row_num);// And push the row number
		lua_newtable(L); // Push the inner table for a single row onto the stack
		for ( i = 0; i < num_fields; i++) {
			lua_pushstring(L, fields[i].name); // Push the field name as key
			lua_pushstring(L, row[i]); // Push the actual data in string format as value
			lua_rawset(L, -3); // Finish a single key/value record of the inner table
		}
		lua_rawset(L, -3); // Finish a single row of records of the outter table
	}
	yo2mysql_connection->num_rows = row_num;
	yo2mysql_connection->per_page = yo2mysql_calcperpage(sqlquery);
 
	mysql_free_result(res); // Free the results set
 
	if (strstr(sqlquery, " SQL_CALC_FOUND_ROWS ") != NULL) {
		mysql_query(yo2mysql_connection->conn, "SELECT FOUND_ROWS()");
		res = mysql_use_result(yo2mysql_connection->conn);
		row = mysql_fetch_row(res);
		yo2mysql_connection->found_rows = atoi(row[0]);
		mysql_free_result(res);
	}
 
	return 1;
}
 
static int yo2mysql_numrows (lua_State *L) {
	// This function just returns the global variable set in yo2mysql_getresults or yo2mysql_setnumrows
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	lua_pushnumber(L, yo2mysql_connection->num_rows);
 
	return 1;
}
 
static int yo2mysql_perpage (lua_State *L) {
	// This function just returns the global variable set in yo2mysql_getresults or yo2mysql_setperpage
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	lua_pushnumber(L, yo2mysql_connection->per_page);
 
	return 1;
}
 
static int yo2mysql_foundrows (lua_State *L) {
	// This function just returns the global variable set in yo2mysql_getresults or yo2mysql_setfoundrows
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	lua_pushnumber(L, yo2mysql_connection->found_rows);
 
	return 1;
}
 
static int yo2mysql_getrow (lua_State *L) {
	// This function returns a table contains the data in a single row for a query on a given connection
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	// Get the query statement
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument!");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
	// Convert the query statement to UPPERCASE and add ' LIMIT 1' if it doesn't have the limitation
	sqlquery = yo2mysql_up_add(sqlquery);
 
	// Do the query on the given connection
	if (mysql_query(yo2mysql_connection->conn, sqlquery)) {
		if(sqlquery) {free(sqlquery);}
		lua_pushnil(L);
		lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
		return 2;
	}
	// Get the results set
	MYSQL_RES *res = mysql_use_result(yo2mysql_connection->conn);
	if(sqlquery) {free(sqlquery);}
	// Get the fields for field name
	unsigned int num_fields = mysql_num_fields(res);
	unsigned int i,row_num = 1;
	MYSQL_FIELD *fields;
	fields = mysql_fetch_fields(res);
 
	// Get the first (and only) row
	MYSQL_ROW row = mysql_fetch_row(res);
	if (row == NULL) {
		lua_pushnil(L);
		lua_pushstring(L, "No row found");
		return 2;
	}
	else {
		lua_newtable(L); // Push a table onto the stack
		for ( i = 0; i < num_fields; i++) {
			lua_pushstring(L, fields[i].name); // Push the field name as key
			lua_pushstring(L, row[i]); // Push the actual data in string format as value
			lua_rawset(L, -3);// Finish a single key/value record of the table
		}
	}
 
	mysql_free_result(res); // Free the results set
 
	return 1;
}
 
static int yo2mysql_getvar (lua_State *L) {
	// This function returns only the first data for a query on a given connection
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	// Get the query statement
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument!");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
 
	// Convert the query statement to UPPERCASE and add ' LIMIT 1' if it doesn't have the limitation
	sqlquery = yo2mysql_up_add(sqlquery);
 
	// Do the query on the given connection
	if (mysql_query(yo2mysql_connection->conn, sqlquery)) {
		lua_pushnil(L);
		lua_pushstring(L, mysql_error(yo2mysql_connection->conn));
		return 2;
	}
 
	// Get the results set
	MYSQL_RES *res = mysql_use_result(yo2mysql_connection->conn);
 
	// Get the first (and only) row
	MYSQL_ROW row = mysql_fetch_row(res);
	if (row == NULL) {
		if(sqlquery) {free(sqlquery);}
		lua_pushnil(L);
		lua_pushstring(L, "No row found!");
		return 2;
	}
	else {
		lua_pushstring(L, row[0]); // Push the first (and only) data onto the stack
	}
 
	if(sqlquery) {free(sqlquery);}
	mysql_free_result(res); // Free the results set
 
	return 1;
}
 
static int yo2mysql_insertid (lua_State *L) {
	// This function returns the id of the object just been inserted or updated on a given connection
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	lua_pushnumber(L, mysql_insert_id(yo2mysql_connection->conn)); // Push the id onto the stack
 
	return 1;
}
 
static int yo2mysql_setnumrows (lua_State *L) {
	// This function directly changes the value of the global gyo2mysql_num_rows
	if (lua_isnumber(L, 1)) {
		yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
		yo2mysql_connection->num_rows = lua_tonumber(L, 1);
	}
 
	return 0;
}
 
static int yo2mysql_setperpage (lua_State *L) {
	// This function directly changes the value of the global gyo2mysql_per_page
 
	if (lua_isnumber(L, 1)) {
		yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
		yo2mysql_connection->per_page = lua_tonumber(L, 1);
	}
 
	return 0;
}
 
static int yo2mysql_setfoundrows (lua_State *L) {
	// This function directly changes the value of the global gyo2mysql_found_rows
 
	if (lua_isnumber(L, 1)) {
		yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
		yo2mysql_connection->found_rows = lua_tonumber(L, 1);
	}
 
	return 0;
}
 
static int yo2mysql_selectdb (lua_State *L) {
	// This function changes the connection's default database
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument!");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
 
	mysql_select_db(yo2mysql_connection->conn, sqlquery);
 
	return 0;
}
 
static int yo2mysql_setcharset (lua_State *L) {
	// This function changes the connection's default character set
	yo2mysql_connection_type *yo2mysql_connection = (yo2mysql_connection_type *)luaL_checkudata (L, 1, YO2MYSQL_CONN);
	if (!yo2mysql_connection->conn) {
		lua_pushnil(L);
		lua_pushstring(L, "MySQL connection has gone!");
		return 2;
	}
 
	char *sqlquery;
	if (!lua_isstring(L, 2)) {
		lua_pushnil(L);
		lua_pushstring(L, "Wrong type of argument!");
		return 2;
	}
	else {
		sqlquery = lua_tostring(L, 2);
	}
 
	mysql_set_character_set(yo2mysql_connection->conn, sqlquery);
 
	return 0;
}
 
static const luaL_reg yo2mysql_connection_lib[] = {
		{"set_charset", yo2mysql_setcharset},
		{"select_db", yo2mysql_selectdb},
		{"query", yo2mysql_query},
		{"get_results", yo2mysql_getresults},
		{"get_row", yo2mysql_getrow},
		{"get_var", yo2mysql_getvar},
		{"num_rows", yo2mysql_numrows},
		{"found_rows", yo2mysql_foundrows},
		{"per_page", yo2mysql_perpage},
		{"insert_id", yo2mysql_insertid},
		{"set_num_rows", yo2mysql_setnumrows},
		{"set_found_rows", yo2mysql_setfoundrows},
		{"set_per_page", yo2mysql_setperpage},
		{"close", yo2mysql_close},
		{NULL, NULL}
};
 
int luaopen_yo2mysql (lua_State *L) {
	lua_pushcfunction(L, yo2mysql_connect);
	lua_setglobal(L, "open_mysql");
	yo2mysql_register (L, YO2MYSQL_CONN, yo2mysql_connection_lib);
	lua_pop (L, 2);
	return 1;
}
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

基于DRBD实现MySQL高可用

基于DRBD实现MySQL高可用 一.基础知识 DRBD—Distributed Replicated Block Device 是一种基于Linux的软件组件, 它是由内核模块和相关程序而组成的...

mysql-python 64位

  • 2017-07-07 10:46
  • 1.03MB
  • 下载

linux 系统中安装mysql

  • 2016-10-24 20:01
  • 950KB
  • 下载

关于选择MySQL还是MongoDB

MySQL比MongoDB好的优点,在于Complex Transactions(复杂事务),这是什么意思呢?

高性能mysql第三版.part2

  • 2016-10-19 08:42
  • 30.03MB
  • 下载

mysql-5.5.19-win32.msi

  • 2017-06-28 13:28
  • 31.14MB
  • 下载

Linux虚拟机安装以及jdk,tomcat,mysql的安装配置

转载:Linux虚拟机的安装与jdk,tomcat的安装 转载:MySQL的安装在安装MySQL过程中,第九步出现问题:输入mysql -uroot -p提示mysql指令没找到 只有在mysql...

MySQL标准文实例

  • 2017-02-24 16:51
  • 31KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)