Author Topic: Extended getinfo for PtokaX with SQLite and LuaSQLite3 library  (Read 1800 times)

0 Members and 1 Guest are viewing this topic.

Offline PPK

  • Administrator
  • Emperor
  • *****
  • Posts: 1 478
  • 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 LuaSQLite3 libs to work.

Simply download http://www.PtokaX.org/Scripts/getinfo-sqlite-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 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 sqlite3 = require "lsqlite3"
  13.  
  14. local utf8test = nil
  15. local ansitoutf8 = nil
  16.  
  17. local uDB = nil
  18.  
  19. local sDBResult = nil
  20. local sFirstNick = nil
  21. local sFirstIP = nil
  22.  
  23. function OnStartup()
  24.         -- test if we running on supported PtokaX version
  25.         if Core.BuildNumber == nil or Core.BuildNumber < 492 then
  26.                 error("This script require PtokaX 0.5.0.3 build 492 or higher!")
  27.         end
  28.  
  29.         -- test if PtokaX is compiled with SQLite support
  30.         if SetMan.tBooleans.EnableDatabase == nil then
  31.                 error("This script require PtokaX with SQLite support!")
  32.         end
  33.  
  34.         os.setlocale("")
  35.  
  36.         utf8test = iconv.new("utf8", "utf8")
  37.         ansitoutf8 = iconv.new("utf8", SetMan.GetString(SetMan.tStrings.Encoding))
  38.  
  39.         uDB = sqlite3.open(Core.GetPtokaXPath().."cfg/users.sqlite")
  40.  
  41.         if uDB ~= nil then
  42.                 uDB:exec("PRAGMA synchronous = NORMAL;PRAGMA journal_mode = WAL;")
  43.         end
  44. end
  45.  
  46. function OnExit()
  47.         if uDB ~= nil then
  48.                 uDB:close()
  49.         end
  50. end
  51.  
  52. function ChatArrival(tUser, sData)
  53.         -- check if user is allowed to use getinfo
  54.     if uDB == nil or tUser.iProfile == -1 or ProfMan.GetProfilePermissions(tUser.iProfile).bGetInfo == false then
  55.         return false
  56.     end
  57.  
  58.         -- check if received command is starting with chat command prefix
  59.    if string.find(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), string.sub(sData, tUser.sNick:len()+4, tUser.sNick:len()+4), 1, true) == nil then
  60.       return false
  61.    end
  62.  
  63.         -- get command without '<nick> ' and ending pipe
  64.         local sCmd = string.sub(sData, tUser.sNick:len()+5, -2)
  65.         local sParam = nil
  66.  
  67.         -- try to find space -> command with param
  68.         local nFound = string.find(sCmd, " ", 1, true)
  69.         if nFound ~= nil then
  70.                 sParam = string.sub(sCmd, nFound+1)
  71.                 sCmd = string.sub(sCmd, 1, nFound-1):lower()
  72.         else
  73.                 if sCmd:lower() == "help" then -- list available commands for this script
  74.                         local sCommandPrefix = string.sub(SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes), 1, 1)
  75.                         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.|",
  76.                                 Core.GetHubSecAlias(), sCommandPrefix, sCommandPrefix, sCommandPrefix))
  77.                         return false
  78.                 else
  79.                         -- we don't handle any commands without parameter
  80.                         return false
  81.                 end
  82.         end
  83.  
  84.         if sCmd == "getdescriptioninfo" then
  85.                 sEscapedUtf = AnsiToUtfAndEscape(sParam)
  86.                 if sEscapedUtf == nil then
  87.          Core.SendToUser(tUser, string.format("<%s> *** Syntax error in command %sgetdescriptioninfo <description>.|", Core.GetHubSecAlias(), SetMan.GetString(SetMan.tStrings.ChatCommandsPrefixes):sub(1, 1)))
  88.                         return true
  89.                 end
  90.  
  91.                 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)
  92.  
  93.                 return true
  94.         elseif sCmd == "gettaginfo" then
  95.                 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.                 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.         sEscapedData = uDB: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.         uDB:exec(sCommand, DBCallBack)
  144.  
  145.         if sDBResult ~= nil then
  146.                 Core.SendToUser(tUser, sDBResult)
  147.                 sDBResult = nil
  148.                 sFirstNick = nil
  149.                 sFirstIP = nil
  150.         else
  151.                 Core.SendToUser(tUser, string.format("<%s> *** Error: %s not found.|", Core.GetHubSecAlias(), sParam))
  152.         end
  153. end
  154.  
  155. function DBCallBack(uData, iCount, sData, sNames)
  156.         if iCount ~= 8 then
  157.                 return 1
  158.         end
  159.  
  160.         if sData[1]:len() == 0 or sData[2]:len() == 0 or sData[3]:len() == 0 then
  161.                 return 1
  162.         end
  163.  
  164.         if sDBResult == nil then
  165.                 sFirstNick = sData[1]
  166.                 sFirstIP = sData[3]
  167.  
  168.                 sDBResult = string.format("<%s> \nNick: %s", Core.GetHubSecAlias(), sData[1])
  169.  
  170.                 tReg = RegMan.GetReg(sData[1])
  171.                 if tReg ~= nil then
  172.                         sDBResult = sDBResult..string.format("\nProfile: %s", ProfMan.GetProfile(tReg.iProfile).sProfileName)
  173.                 end
  174.  
  175.                 tUser = Core.GetUser(sData[1])
  176.                 if tUser ~= nil then
  177.                         sDBResult = sDBResult..string.format("\nStatus: Online from %s", os.date("%c", Core.GetUserValue(tUser, 25)))
  178.                 else
  179.                         sDBResult = sDBResult..string.format("\nStatus: Offline from %s", os.date("%c", sData[2]))
  180.                 end
  181.  
  182.                 sDBResult = sDBResult..string.format("\nIP: %s\nShare size: %s", sData[3], sData[4])
  183.  
  184.                 if sData[5]:len() ~= 0 then
  185.                         sDBResult = sDBResult..string.format("\nDescription: %s", sData[5])
  186.                 end
  187.  
  188.                 if sData[6]:len() ~= 0 then
  189.                         sDBResult = sDBResult..string.format("\nTag: %s", sData[6])
  190.                 end
  191.  
  192.                 if sData[7]:len() ~= 0 then
  193.                         sDBResult = sDBResult..string.format("\nConnection: %s", sData[7])
  194.                 end
  195.  
  196.                 if sData[8]:len() ~= 0 then
  197.                         sDBResult = sDBResult..string.format("\nEmail: %s", sData[8])
  198.                 end
  199.  
  200.                 sDBResult = sDBResult..string.format("\nCountry: %s", IP2Country.GetCountryCode(sData[3]))
  201.         else
  202.                 if sFirstNick ~= nil then
  203.                         sDBResult = string.format("<%s> \nNick: %s\t\tIP: %s", Core.GetHubSecAlias(), sFirstNick, sFirstIP)
  204.                         sFirstNick = nil
  205.                         sFirstIP = nil
  206.                 end
  207.  
  208.                 sDBResult = sDBResult..string.format("\nNick: %s\t\tIP: %s", sData[1], sData[3])
  209.         end
  210.  
  211.         return 0
  212. end
  213.  
« Last Edit: 22 November, 2015, 12:52:13 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


Offline PPK

  • Administrator
  • Emperor
  • *****
  • Posts: 1 478
  • Karma: +209/-22
  • PtokaX developer
Re: Extended getinfo for PtokaX with SQLite
« Reply #1 on: 22 November, 2015, 11:44:49 »
Script updated. Added help. 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 SQLite
« Reply #1 on: 22 November, 2015, 11:44:49 »