mysql

#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;
}
阅读更多
个人分类: lua
上一篇shell监控进程是否关闭 (
下一篇lua 重新加载模块
想对作者说点什么? 我来说一句

mysql android

2017年12月07日 29.53MB 下载

信息安全注入

2018年04月24日 3KB 下载

mysql 性能优化

2011年04月11日 3.05MB 下载

MySql MySql使用方法 Mysql例子

2009年02月18日 644KB 下载

MySql使用手册(详细)

2009年06月27日 454KB 下载

高性能MySQL(第2版)中文版.pdf1

2010年10月20日 13.35MB 下载

mysql常见异常 mysql常见异常

2011年07月08日 21KB 下载

MYSQL技术文档下载

2010年06月27日 4.39MB 下载

mysql客户端程序

2010年05月14日 10.16MB 下载

没有更多推荐了,返回首页

关闭
关闭