PtokaX forum

Lua 5.3/5.2/5.1 Scripts (for PtokaX 0.4.0.0 and newer) => Finished Scripts => Topic started by: PPK on 21 June, 2015, 23:42:41

Title: Extended getinfo for PtokaX with PostgreSQL
Post by: PPK on 21 June, 2015, 23:42:41
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-Postgres libs to work.

Simply download http://www.PtokaX.org/Scripts/getinfo-postgres-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) Select
--[[
PtokaX extended getinfo for PostgreSQL script version 0.91. Script to get user information based on description, tag or email from PostgreSQL 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.postgres"
local postgres = luasql.postgres()

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 PostgreSQL support
if SetMan.tStrings.PostgresDBName == nil then
error("This script require PtokaX with PostgreSQL support!")
end

os.setlocale("")

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

uDBConn = postgres:connect(SetMan.GetString(SetMan.tStrings.PostgresDBName), SetMan.GetString(SetMan.tStrings.PostgresUser), SetMan.GetString(SetMan.tStrings.PostgresPass), SetMan.GetString(SetMan.tStrings.PostgresHost), SetMan.GetString(SetMan.tStrings.PostgresPort))
end

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

if postgres ~= nil then
postgres: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, EXTRACT(EPOCH FROM last_updated), ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(description) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", 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, EXTRACT(EPOCH FROM last_updated), ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(tag) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", 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, EXTRACT(EPOCH FROM last_updated), ip_address, share, description, tag, connection, email FROM userinfo WHERE LOWER(email) LIKE LOWER('%s') ORDER BY last_updated DESC LIMIT 50;", 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 iNumRows = uRet:numrows();

if iNumRows ~= 0 then
if iNumRows == 1 then
local tResult = uRet:fetch({}, "n")

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)
else
local sDBResult = string.format("<%s> \n", Core.GetHubSecAlias())

for i = 1, iNumRows do
local tResult = uRet:fetch({}, "n")

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

Core.SendToUser(tUser, sDBResult)
end

uRet:close()
else
Core.SendToUser(tUser, string.format("<%s> *** Error: %s not found.|", Core.GetHubSecAlias(), sParam))
end
end
Title: Re: Extended getinfo for PtokaX with PostgreSQL
Post by: PPK on 22 November, 2015, 11:44:25
Script updated. Fixed blocking of hub commands without parameter (thx Alexey for report).