TalkAction /sql command

[Mock]

Author: Mock
It require luasql lib you can get it here:
http://otland.net/f163/lua-modules-otserv-socket-luasql-74836/

Add this tag on talkactions.xml:

<talkaction log="yes" access="5" words="/sql" event="script" value="sql.lua"/>

And the script:

function connect_db()
	dofile("config.lua")
	local env,con
	if sqlType == "mysql" then
		env = assert(luasql.mysql())
		con = assert(env:connect(sql_db or sqlDatabase, sql_user or sqlUser, sql_pass or sqlPass, sql_host or sqlHost, sql_port or sqlPort))
	elseif sqlType == 'sqlite' then
		env = assert(luasql.sqlite3())
		con = assert(env:connect(sql_db or sqliteDatabase or sqlFile))
	else
		error('You cant execute on XML mode.')
	end
	return	con,env
end
function returnS(t)
	local s = ''
	for i,b in pairs(t) do
		s = s..'\n'..tostring(i)..' = '..tostring(b)
	end
	return s
end
function onSay(cid, words, param)
	local ta = os.clock()
	local con,env = connect_db()
	local b,err = con:execute(param)
	if not b and err then
		con:close()
		env:close()
		doShowTextDialog(cid,2638,err)
		return true
	end
	if type(b) == 'number' then
		con:close()
		env:close()
		doShowTextDialog(cid,2639,'Done in '..os.clock()-ta..'\n'..(b+1)..'rows affected\n------------\n')
		return true
	end
	local c = b:fetch({}, "a")
	if not c then
		con:close()
		env:close()
		doShowTextDialog(cid,2639,'Done in '..os.clock()-ta..'\n0 results returned\n------------\n')
		return true
	end
	local results = 0
	local s = ''
	repeat
		results = results+1
		s = s..returnS(c)..'\n------'..results..'-----\n'
		c = b:fetch({}, "a")
	until not c
	con:close()
	env:close()
	doShowTextDialog(cid,2639,'Done in '..os.clock()-ta..'\n'..results..' results returned\n------------\n'..s)
	return true
end

Now you can run it:
/sql select * from player where id=1
/sql update players set name='hacked' where account=123456
/sql delete players
/sql delete from players where lastlogin = 0
etc.
It show count of rows affected and show wath return :thumbup:

 

[hodleo]

mock, que isso faz?

Mock, does that make?

/sql SELECT health FROM players WHERE name = 'Beon'

delete empty accounts without chars in db? then
/sql DELETE * FROM "accounts" WHERE NOT EXISTS (SELECT "id" FROM "players" WHERE "accounts.id" = "players.account_id")

<.< well at least thats what I think this does

 

[Mock]

it:
/sql select * from player where id=1
/sql update players set name='hacked' where account=123456
/sql delete players
/sql delete from players where lastlogin = 0

 

[hodleo]

it's a risky command, you users ought to allow it only to god/owner

 

[MrLipzZz]

Tenso o.o cara faz cada coisa HUEAHEHAUHAE
pra mim não vai ser muito util não :/
Não saco muito dos comandos pra SQL ~~
Mas pra quem saca é um avanço, adeus phpmydmin !

 

[95seba]

The most usefull when you don't have access to the database from current computer.
Rep+

 

[Elexonic]

nice i go test

 

[Renusek]

Very nice! I'm just thinking about impossible things, that Mock will script in future... Thanks!

 

[JoSePh15_]

nice... omg

 

[GarQet]

[21:0:11.695] [Error - TalkAction Interface]
[21:0:11.695] data/talkactions/scripts/sql.luanSay
[21:0:11.695] Description:
[21:0:11.695] data/talkactions/scripts/sql.lua:5: attempt to index global 'luasql' (a nil value)
[21:0:11.695] stack traceback:
[21:0:11.695] data/talkactions/scripts/sql.lua:5: in function 'connect_db'
[21:0:11.695] data/talkactions/scripts/sql.lua:24: in function <data/talkactions/scripts/sql.lua:22>

 

[Icy]

[21:0:11.695] [Error - TalkAction Interface]
[21:0:11.695] data/talkactions/scripts/sql.luanSay
[21:0:11.695] Description:
[21:0:11.695] data/talkactions/scripts/sql.lua:5: attempt to index global 'luasql' (a nil value)
[21:0:11.695] stack traceback:
[21:0:11.695] data/talkactions/scripts/sql.lua:5: in function 'connect_db'
[21:0:11.695] data/talkactions/scripts/sql.lua:24: in function <data/talkactions/scripts/sql.lua:22>

Author: Mock
It require luasql lib you can get it here:
http://otland.net/f163/lua-modules-otserv-socket-luasql-74836/

3chars