Author Topic: Extended getinfo for PtokaX with SQLite and LuaSQL-SQLite3 library  (Read 1487 times)

0 Members and 1 Guest are viewing this topic.

Offline PPK

  • Administrator
  • Emperor
  • *****
  • Posts: 1 475
  • Karma: +209/-22
  • PtokaX developer
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:
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 luasql-sqlite script version 0.91. Script to get user information based on description, tag or email from SQLite 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.sqlite3"
  13. local sqlite = luasql.sqlite3()
  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 SQLite support
  27.         if SetMan.tBooleans.EnableDatabase == nil then
  28.                 error("This script require PtokaX with SQLite 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 = sqlite:connect(Core.GetPtokaXPath().."cfg/users.sqlite")
  37.  
  38.         if uDB ~= nil then
  39.                 uDBConn:execute("PRAGMA synchronous = NORMAL;PRAGMA journal_mode = WAL;")
  40.         end
  41. end
  42.  
  43. function OnExit()
  44.         if uDBConn ~= nil then
  45.                 uDBConn:close()
  46.         end
  47.  
  48.         if sqlite ~= nil then
  49.                 sqlite:close()
  50.         end
  51. end
  52.  
  53. function ChatArrival(tUser, sData)
  54.         -- check if user is allowed to use getinfo
  55.     if uDBConn == nil or tUser.iProfile == -1 or ProfMan.GetProfilePermissions(tUser.iProfile).bGetInfo == false then
  56.         return false
  57.     end
  58.  
  59.         -- check if received command is starting with chat command prefix
  60.    if string.find(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), string.sub(sData, tUser.sNick:len()+4, tUser.sNick:len()+4), 1, true) == nil then
  61.       return false
  62.    end
  63.  
  64.         -- get command withou '<nick> ' and ending pipe
  65.         local sCmd = string.sub(sData, tUser.sNick:len()+5, -2)
  66.         local sParam = nil
  67.  
  68.         -- try to find space -> command with param
  69.         local nFound = string.find(sCmd, " ", 1, true)
  70.         if nFound ~= nil then
  71.                 sParam = string.sub(sCmd, nFound+1)
  72.                 sCmd = string.sub(sCmd, 1, nFound-1):lower()
  73.         else
  74.                 if sCmd:lower() == "help" then -- list available commands for this script
  75.                         local sCommandPrefix = string.sub(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), 1, 1)
  76.                         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.|",
  77.                                 Core.GetHubSecAlias(), sCommandPrefix, sCommandPrefix, sCommandPrefix))
  78.                         return false
  79.                 else
  80.                         -- we don't handle any other commands without parameter
  81.                         return false
  82.                 end
  83.         end
  84.  
  85.         if sCmd == "getdescriptioninfo" then
  86.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  87.                 if sEscapedUtf == nil then
  88.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetdescriptioninfo <description>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  89.                         return true
  90.                 end
  91.  
  92.                 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)
  93.                 return true
  94.         elseif sCmd == "gettaginfo" then
  95.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  96.                 if sEscapedUtf == nil then
  97.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgettaginfo <tag>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  98.                         return true
  99.                 end
  100.  
  101.                 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)
  102.  
  103.                 return true
  104.         elseif sCmd == "getemailinfo" then
  105.                 local sEscapedUtf = AnsiToUtfAndEscape(sParam)
  106.                 if sEscapedUtf == nil then
  107.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetemailinfo <email>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  108.                         return true
  109.                 end
  110.  
  111.                 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)
  112.  
  113.                 return true
  114.         end
  115. end
  116.  
  117. function AnsiToUtfAndEscape(sData)
  118.         local sUtfData = nil
  119.  
  120.         nstr, err = utf8test:iconv(sData)
  121.         if err ~= nil then
  122.                 nstr, err = ansitoutf8:iconv(sData)
  123.                 if err == nil then
  124.                         sUtfData = nstr
  125.                 end
  126.         else
  127.                 sUtfData = sData
  128.         end
  129.  
  130.         if sUtfData == nil then
  131.                 return nil
  132.         end
  133.  
  134.         local sEscapedData = uDBConn:escape(sUtfData)
  135.         if sEscapedData == nil then
  136.                 return nil
  137.         end
  138.  
  139.         return sEscapedData
  140. end
  141.  
  142. function DBExecute(sCommand, tUser, sParam)
  143.         local uRet = assert(uDBConn:execute(sCommand))
  144.  
  145.         local tResult = uRet:fetch({}, "n")
  146.  
  147.         if tResult == nil then
  148.                 Core.SendToUser(tUser, string.format("<%s> *** Error: %s not found.|", Core.GetHubSecAlias(), sParam))
  149.  
  150.                 uRet:close()
  151.  
  152.                 return
  153.         end
  154.  
  155.         local tNextResult = uRet:fetch({}, "n")
  156.  
  157.         if tNextResult == nil then
  158.                 local sDBResult = string.format("<%s> \nNick: %s", Core.GetHubSecAlias(), tResult[1])
  159.        
  160.                 tReg = RegMan.GetReg(tResult[1])
  161.                 if tReg ~= nil then
  162.                         sDBResult = sDBResult..string.format("\nProfile: %s", ProfMan.GetProfile(tReg.iProfile).sProfileName)
  163.                 end
  164.        
  165.                 tUser = Core.GetUser(tResult[1])
  166.                 if tUser ~= nil then
  167.                         sDBResult = sDBResult..string.format("\nStatus: Online from %s", os.date("%c", Core.GetUserValue(tUser, 25)))
  168.                 else
  169.                         sDBResult = sDBResult..string.format("\nStatus: Offline from %s", os.date("%c", tResult[2]))
  170.                 end
  171.        
  172.                 sDBResult = sDBResult..string.format("\nIP: %s\nShare size: %s", tResult[3], tResult[4])
  173.        
  174.                 if tResult[5]:len() ~= 0 then
  175.                         sDBResult = sDBResult..string.format("\nDescription: %s", tResult[5])
  176.                 end
  177.        
  178.                 if tResult[6]:len() ~= 0 then
  179.                         sDBResult = sDBResult..string.format("\nTag: %s", tResult[6])
  180.                 end
  181.        
  182.                 if tResult[7]:len() ~= 0 then
  183.                         sDBResult = sDBResult..string.format("\nConnection: %s", tResult[7])
  184.                 end
  185.        
  186.                 if tResult[8]:len() ~= 0 then
  187.                         sDBResult = sDBResult..string.format("\nEmail: %s", tResult[8])
  188.                 end
  189.        
  190.                 sDBResult = sDBResult..string.format("\nCountry: %s|", IP2Country.GetCountryCode(tResult[3]))
  191.  
  192.                 Core.SendToUser(tUser, sDBResult)
  193.  
  194.                 uRet:close()
  195.  
  196.                 return
  197.         end
  198.  
  199.         local sDBResult = string.format("<%s> \n", Core.GetHubSecAlias())
  200.  
  201.         sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", tResult[1], tResult[3])
  202.  
  203.         while tNextResult ~= nil do
  204.                 sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", tNextResult[1], tNextResult[3])
  205.                 tNextResult = uRet:fetch({}, "n")
  206.         end
  207.  
  208.         Core.SendToUser(tUser, sDBResult)
  209.  
  210.         uRet:close()
  211. end
  212.  
"Most of you are familiar with the virtues of a programmer. There are three, of course: laziness, impatience, and hubris." - Larry Wall

PtokaX forum