Author Topic: Extended getinfo for PtokaX with PostgreSQL  (Read 1572 times)

0 Members and 1 Guest are viewing this topic.

Offline PPK

  • Administrator
  • Emperor
  • *****
  • Posts: 1 478
  • Karma: +209/-22
  • PtokaX developer
Extended getinfo for PtokaX with PostgreSQL
« 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:
Quote
Available 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
  1. --[[
  2.                 PtokaX extended getinfo for PostgreSQL script version 0.91. Script to get user information based on description, tag or email from PostgreSQL database.
  3.  
  4.                 Copyright (c) 2015 Petr Kozelka, PPK at PtokaX dot org
  5.  
  6.                 This script is licensed under
  7.                         Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International Public License.
  8.                         See http://creativecommons.org/licenses/by-nc-nd/4.0/ for license details.
  9. ]]--
  10.  
  11. local iconv = require "iconv"
  12. local luasql = require "luasql.postgres"
  13. local postgres = luasql.postgres()
  14.  
  15. local utf8test = nil
  16. local ansitoutf8 = nil
  17.  
  18. local uDBConn = nil
  19.  
  20. function OnStartup()
  21.         -- test if we running on supported PtokaX version
  22.         if Core.BuildNumber == nil or Core.BuildNumber < 492 then
  23.                 error("This script require PtokaX 0.5.0.3 build 492 or higher!")
  24.         end
  25.  
  26.         -- test if PtokaX is compiled with PostgreSQL support
  27.         if SetMan.tStrings.PostgresDBName == nil then
  28.                 error("This script require PtokaX with PostgreSQL support!")
  29.         end
  30.  
  31.         os.setlocale("")
  32.  
  33.         utf8test = iconv.new("utf8", "utf8")
  34.         ansitoutf8 = iconv.new("utf8", SetMan.GetString(SetMan.tStrings.Encoding))
  35.  
  36.         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))
  37. end
  38.  
  39. function OnExit()
  40.         if uDBConn ~= nil then
  41.                 uDBConn:close()
  42.         end
  43.  
  44.         if postgres ~= nil then
  45.                 postgres:close()
  46.         end
  47. end
  48.  
  49. function ChatArrival(tUser, sData)
  50.         -- check if user is allowed to use getinfo
  51.     if uDBConn == nil or tUser.iProfile == -1 or ProfMan.GetProfilePermissions(tUser.iProfile).bGetInfo == false then
  52.         return false
  53.     end
  54.  
  55.         -- check if received command is starting with chat command prefix
  56.    if string.find(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), string.sub(sData, tUser.sNick:len()+4, tUser.sNick:len()+4), 1, true) == nil then
  57.       return false
  58.    end
  59.  
  60.         -- get command withou '<nick> ' and ending pipe
  61.         local sCmd = string.sub(sData, tUser.sNick:len()+5, -2)
  62.         local sParam = nil
  63.  
  64.         -- try to find space -> command with param
  65.         local nFound = string.find(sCmd, " ", 1, true)
  66.         if nFound ~= nil then
  67.                 sParam = string.sub(sCmd, nFound+1)
  68.                 sCmd = string.sub(sCmd, 1, nFound-1):lower()
  69.         else
  70.                 if sCmd:lower() == "help" then -- list available commands for this script
  71.                         local sCommandPrefix = string.sub(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), 1, 1)
  72.                         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.|",
  73.                                 Core.GetHubSecAlias(), sCommandPrefix, sCommandPrefix, sCommandPrefix))
  74.                         return false
  75.                 else
  76.                         -- we don't handle any other commands without parameter
  77.                         return false
  78.                 end
  79.         end
  80.  
  81.         if sCmd == "getdescriptioninfo" then
  82.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  83.                 if sEscapedUtf == nil then
  84.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetdescriptioninfo <description>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  85.                         return true
  86.                 end
  87.  
  88.                 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)
  89.                 return true
  90.         elseif sCmd == "gettaginfo" then
  91.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  92.                 if sEscapedUtf == nil then
  93.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgettaginfo <tag>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  94.                         return true
  95.                 end
  96.  
  97.                 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)
  98.  
  99.                 return true
  100.         elseif sCmd == "getemailinfo" then
  101.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  102.                 if sEscapedUtf == nil then
  103.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetemailinfo <email>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  104.                         return true
  105.                 end
  106.  
  107.                 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)
  108.  
  109.                 return true
  110.         end
  111. end
  112.  
  113. function AnsiToUtfAndEscape(sData)
  114.         local sUtfData = nil
  115.  
  116.         nstr, err = utf8test:iconv(sData)
  117.         if err ~= nil then
  118.                 nstr, err = ansitoutf8:iconv(sData)
  119.                 if err == nil then
  120.                         sUtfData = nstr
  121.                 end
  122.         else
  123.                 sUtfData = sData
  124.         end
  125.  
  126.         if sUtfData == nil then
  127.                 return nil
  128.         end
  129.  
  130.         local sEscapedData = uDBConn:escape(sUtfData)
  131.         if sEscapedData == nil then
  132.                 return nil
  133.         end
  134.  
  135.         return sEscapedData
  136. end
  137.  
  138. function DBExecute(sCommand, tUser, sParam)
  139.         local uRet = assert(uDBConn:execute(sCommand))
  140.         local iNumRows = uRet:numrows();
  141.  
  142.         if iNumRows ~= 0 then
  143.                 if iNumRows == 1 then
  144.                         local tResult = uRet:fetch({}, "n")
  145.  
  146.                         local sDBResult = string.format("<%s> \nNick: %s", Core.GetHubSecAlias(), tResult[1])
  147.        
  148.                         tReg = RegMan.GetReg(tResult[1])
  149.                         if tReg ~= nil then
  150.                                 sDBResult = sDBResult..string.format("\nProfile: %s", ProfMan.GetProfile(tReg.iProfile).sProfileName)
  151.                         end
  152.        
  153.                         tUser = Core.GetUser(tResult[1])
  154.                         if tUser ~= nil then
  155.                                 sDBResult = sDBResult..string.format("\nStatus: Online from %s", os.date("%c", Core.GetUserValue(tUser, 25)))
  156.                         else
  157.                                 sDBResult = sDBResult..string.format("\nStatus: Offline from %s", os.date("%c", tResult[2]))
  158.                         end
  159.        
  160.                         sDBResult = sDBResult..string.format("\nIP: %s\nShare size: %s", tResult[3], tResult[4])
  161.        
  162.                         if tResult[5]:len() ~= 0 then
  163.                                 sDBResult = sDBResult..string.format("\nDescription: %s", tResult[5])
  164.                         end
  165.        
  166.                         if tResult[6]:len() ~= 0 then
  167.                                 sDBResult = sDBResult..string.format("\nTag: %s", tResult[6])
  168.                         end
  169.        
  170.                         if tResult[7]:len() ~= 0 then
  171.                                 sDBResult = sDBResult..string.format("\nConnection: %s", tResult[7])
  172.                         end
  173.        
  174.                         if tResult[8]:len() ~= 0 then
  175.                                 sDBResult = sDBResult..string.format("\nEmail: %s", tResult[8])
  176.                         end
  177.        
  178.                         sDBResult = sDBResult..string.format("\nCountry: %s|", IP2Country.GetCountryCode(tResult[3]))
  179.  
  180.                         Core.SendToUser(tUser, sDBResult)
  181.                 else
  182.                         local sDBResult = string.format("<%s> \n", Core.GetHubSecAlias())
  183.  
  184.                         for i = 1, iNumRows do
  185.                                 local tResult = uRet:fetch({}, "n")
  186.        
  187.                                 sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", tResult[1], tResult[3])
  188.                         end
  189.  
  190.                         Core.SendToUser(tUser, sDBResult)
  191.                 end
  192.  
  193.                 uRet:close()
  194.         else
  195.                 Core.SendToUser(tUser, string.format("<%s> *** Error: %s not found.|", Core.GetHubSecAlias(), sParam))
  196.         end
  197. end
  198.  
« Last Edit: 22 November, 2015, 11:43:52 by PPK »
"Most of you are familiar with the virtues of a programmer. There are three, of course: laziness, impatience, and hubris." - Larry Wall

PtokaX forum

Extended getinfo for PtokaX with PostgreSQL
« on: 21 June, 2015, 23:42:41 »

Offline PPK

  • Administrator
  • Emperor
  • *****
  • Posts: 1 478
  • Karma: +209/-22
  • PtokaX developer
Re: Extended getinfo for PtokaX with PostgreSQL
« Reply #1 on: 22 November, 2015, 11:44:25 »
Script updated. Fixed blocking of hub commands without parameter (thx Alexey for report).
"Most of you are familiar with the virtues of a programmer. There are three, of course: laziness, impatience, and hubris." - Larry Wall

PtokaX forum

Re: Extended getinfo for PtokaX with PostgreSQL
« Reply #1 on: 22 November, 2015, 11:44:25 »