Extended getinfo for PtokaX with SQLite and LuaSQL-SQLite3 library
 

Extended getinfo for PtokaX with SQLite and LuaSQL-SQLite3 library

Started by PPK, 22 November, 2015, 12:55:16

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

PPK

This is script is extending getinfo commands. PtokaX contains inbuild !getinfo <nick> and !getipinfo <ip>. This script is adding !getdescriptioninfo, !gettaginfo and !getemailinfo.
This script require PtokaX 0.5.0.3 build 492 or higher. Script is compatible with Lua 5.1, 5.2 and 5.3. This script require LuaIconv and LuaSQL-SQLite3 libs to work.

Simply download http://www.ptokax.org/Scripts/getinfo-sqlite-luasql-ext.lua to PtokaX_dir\scripts directory.

Usage of this script is simple.
With command !help you can list all available commands:
QuoteAvailable commands:
   !getdescriptioninfo <description> - return user(s) with given description or description that match SQL Wildcards.
   !gettaginfo <tag> - return user(s) with given tag or tag that match SQL Wildcards.
   !getemailinfo <email> - return user(s) with given email or email that match SQL Wildcards.

In case of requests, bug reports or questions post them below ;)

Complete script:
Code: Lua
--[[
		PtokaX extended getinfo for luasql-sqlite script version 0.91. Script to get user information based on description, tag or email from SQLite database.

		Copyright (c) 2015 Petr Kozelka, PPK at PtokaX dot org

		This script is licensed under 
			Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International Public License.
			See http://creativecommons.org/licenses/by-nc-nd/4.0/ for license details.
]]--

local iconv = require "iconv"
local luasql = require "luasql.sqlite3"
local sqlite = luasql.sqlite3()

local utf8test = nil
local ansitoutf8 = nil

local uDBConn = nil

function OnStartup()
	-- test if we running on supported PtokaX version
	if Core.BuildNumber == nil or Core.BuildNumber < 492 then
		error("This script require PtokaX 0.5.0.3 build 492 or higher!")
	end

	-- test if PtokaX is compiled with SQLite support
	if SetMan.tBooleans.EnableDatabase == nil then
		error("This script require PtokaX with SQLite support!")
	end

	os.setlocale("")

	utf8test = iconv.new("utf8", "utf8")
	ansitoutf8 = iconv.new("utf8", SetMan.GetString(SetMan.tStrings.Encoding))

	uDBConn = sqlite:connect(Core.GetPtokaXPath().."cfg/users.sqlite")

	if uDB ~= nil then
		uDBConn:execute("PRAGMA synchronous = NORMAL;PRAGMA journal_mode = WAL;")
	end
end

function OnExit()
	if uDBConn ~= nil then
		uDBConn:close()
	end

	if sqlite ~= nil then
		sqlite:close()
	end
end

function ChatArrival(tUser, sData)
	-- check if user is allowed to use getinfo
    if uDBConn == nil or tUser.iProfile == -1 or ProfMan.GetProfilePermissions(tUser.iProfile).bGetInfo == false then
        return false
    end

	-- check if received command is starting with chat command prefix
   if string.find(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), string.sub(sData, tUser.sNick:len()+4, tUser.sNick:len()+4), 1, true) == nil then
      return false
   end

	-- get command withou '<nick> ' and ending pipe
	local sCmd = string.sub(sData, tUser.sNick:len()+5, -2)
	local sParam = nil

	-- try to find space -> command with param
	local nFound = string.find(sCmd, " ", 1, true)
	if nFound ~= nil then
		sParam = string.sub(sCmd, nFound+1)
		sCmd = string.sub(sCmd, 1, nFound-1):lower()
	else
		if sCmd:lower() == "help" then -- list available commands for this script
			local sCommandPrefix = string.sub(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), 1, 1)
			Core.SendToUser(tUser, string.format("<%s> Available commands:\n\t%sgetdescriptioninfo <description> - return user(s) with given description or description that match SQL Wildcards.\n\t%sgettaginfo <tag> - return user(s) with given tag or tag that match SQL Wildcards.\n\t%sgetemailinfo <email> - return user(s) with given email or email that match SQL Wildcards.|",
				Core.GetHubSecAlias(), sCommandPrefix, sCommandPrefix, sCommandPrefix))
			return false
		else
			-- we don't handle any other commands without parameter
			return false
		end
	end

	if sCmd == "getdescriptioninfo" then
		local sEscapedUtf = AnsiToUtfAndEscape(sParam)
		if sEscapedUtf == nil then
         Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetdescriptioninfo <description>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
			return true
		end

		DBExecute(string.format("SELECT nick, %s, ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(description) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", "strftime('%s', last_updated)", sEscapedUtf), tUser, sParam)
		return true
	elseif sCmd == "gettaginfo" then
		local sEscapedUtf = AnsiToUtfAndEscape(sParam)
		if sEscapedUtf == nil then
         Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgettaginfo <tag>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
			return true
		end

		DBExecute(string.format("SELECT nick, %s, ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(tag) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", "strftime('%s', last_updated)", sEscapedUtf), tUser, sParam)

		return true
	elseif sCmd == "getemailinfo" then
		local sEscapedUtf = AnsiToUtfAndEscape(sParam)
		if sEscapedUtf == nil then
         Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetemailinfo <email>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
			return true
		end

		DBExecute(string.format("SELECT nick, %s, ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(email) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", "strftime('%s', last_updated)", sEscapedUtf), tUser, sParam)

		return true
	end
end

function AnsiToUtfAndEscape(sData)
	local sUtfData = nil

	nstr, err = utf8test:iconv(sData)
	if err ~= nil then
		nstr, err = ansitoutf8:iconv(sData)
		if err == nil then
			sUtfData = nstr
		end
	else
		sUtfData = sData
	end

	if sUtfData == nil then
		return nil
	end

	local sEscapedData = uDBConn:escape(sUtfData)
	if sEscapedData == nil then
		return nil
	end

	return sEscapedData
end

function DBExecute(sCommand, tUser, sParam)
	local uRet = assert(uDBConn:execute(sCommand))

	local tResult = uRet:fetch({}, "n")

	if tResult == nil then
		Core.SendToUser(tUser, string.format("<%s> *** Error: %s not found.|", Core.GetHubSecAlias(), sParam))

		uRet:close()

		return
	end

	local tNextResult = uRet:fetch({}, "n")

	if tNextResult == nil then
		local sDBResult = string.format("<%s> \nNick: %s", Core.GetHubSecAlias(), tResult[1])
	
		tReg = RegMan.GetReg(tResult[1])
		if tReg ~= nil then
			sDBResult = sDBResult..string.format("\nProfile: %s", ProfMan.GetProfile(tReg.iProfile).sProfileName)
		end
	
		tUser = Core.GetUser(tResult[1]) 
		if tUser ~= nil then
			sDBResult = sDBResult..string.format("\nStatus: Online from %s", os.date("%c", Core.GetUserValue(tUser, 25)))
		else
			sDBResult = sDBResult..string.format("\nStatus: Offline from %s", os.date("%c", tResult[2]))
		end
	
		sDBResult = sDBResult..string.format("\nIP: %s\nShare size: %s", tResult[3], tResult[4])
	
		if tResult[5]:len() ~= 0 then
			sDBResult = sDBResult..string.format("\nDescription: %s", tResult[5])
		end
	
		if tResult[6]:len() ~= 0 then
			sDBResult = sDBResult..string.format("\nTag: %s", tResult[6])
		end
	
		if tResult[7]:len() ~= 0 then
			sDBResult = sDBResult..string.format("\nConnection: %s", tResult[7])
		end
	
		if tResult[8]:len() ~= 0 then
			sDBResult = sDBResult..string.format("\nEmail: %s", tResult[8])
		end
	
		sDBResult = sDBResult..string.format("\nCountry: %s|", IP2Country.GetCountryCode(tResult[3]))

		Core.SendToUser(tUser, sDBResult)

		uRet:close()

		return
	end

	local sDBResult = string.format("<%s> \n", Core.GetHubSecAlias())

	sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", tResult[1], tResult[3])

	while tNextResult ~= nil do
		sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", tNextResult[1], tNextResult[3])
		tNextResult = uRet:fetch({}, "n")
	end

	Core.SendToUser(tUser, sDBResult)

	uRet:close()
end
"Most of you are familiar with the virtues of a programmer. There are three, of course: laziness, impatience, and hubris." - Larry Wall

SMF spam blocked by CleanTalk