add: geoip with local base (only for postgresql)

3 files changed, 181 insertions, 1 deletions

diff --git a/.gitignore b/.gitignore
index 3f60b79..3e427b8 100644
--- a/.gitignore
+++ b/.gitignore
@@ -11,6 +11,7 @@ data/log/*.log
 data/custom_censor.txt
 data/paste/*.txt
 data/paste/*.html
+data/paste/*.ico
 data/paste/*.jpg
 data/paste/*.JPG
 data/paste/*.gif
@@ -32,4 +33,4 @@ settings/_*
 tmp/isida.pid
 tmp/starttime
 tmp/ver*
-tmp/answers.txt
\ No newline at end of file
+tmp/answers.txt
diff --git a/dev-tools/geoip_to_postgresql/geo2db.py b/dev-tools/geoip_to_postgresql/geo2db.py
new file mode 100644
index 0000000..53a85a6
--- /dev/null
+++ b/dev-tools/geoip_to_postgresql/geo2db.py
@@ -0,0 +1,132 @@
+#!/usr/bin/python
+# -*- coding: utf-8 -*-
+
+# --------------------------------------------------------------------------- #
+#                                                                             #
+#    GEOIP to PostgreSQL                                                      #
+#    Copyright (C) diSabler <dsy@dsy.name>                                    #
+#                                                                             #
+#    This program is free software: you can redistribute it and/or modify     #
+#    it under the terms of the GNU General Public License as published by     #
+#    the Free Software Foundation, either version 3 of the License, or        #
+#    (at your option) any later version.                                      #
+#                                                                             #
+#    This program is distributed in the hope that it will be useful,          #
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of           #
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            #
+#    GNU General Public License for more details.                             #
+#                                                                             #
+#    You should have received a copy of the GNU General Public License        #
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.    #
+#                                                                             #
+# --------------------------------------------------------------------------- #
+# REQUIRED: wget, unzip                                                       #
+# --------------------------------------------------------------------------- #
+
+import csv,os
+
+URL = 'http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip'
+DUMP = 'geoip.dump'
+
+_SQL1 = '''DROP TABLE geoip_blocks;
+CREATE TABLE geoip_blocks (
+	startIpNum bigint,
+	endIpNum bigint,
+	locId integer
+);
+INSERT INTO geoip_blocks VALUES'''
+
+_SQL2 = '''(0,0,0);
+CREATE INDEX geoip_blocks_1 ON geoip_blocks (startIpNum,endIpNum,locId);
+CREATE INDEX geoip_blocks_2 ON geoip_blocks (locId,startIpNum,endIpNum);
+DELETE FROM geoip_blocks WHERE locId=0;'''
+
+_SQL3 = '''DROP TABLE geoip_location;
+CREATE TABLE geoip_location (
+	locId integer,
+	country text,
+	region text,
+	city text,
+	postalCode text,
+	latitude real,
+	longitude real,
+	metroCode text,
+	areaCode text
+);
+INSERT INTO geoip_location VALUES'''
+
+_SQL4 = '''(0,'','','','',0,0,'','');
+CREATE INDEX geoip_location_1 ON geoip_location (locId);
+DELETE FROM geoip_location WHERE locId=0;'''
+
+print 'Download GEOIP file'
+
+ZIPFILE = URL.split('/')[-1]
+
+try: os.remove(ZIPFILE)
+except: pass
+os.system('wget %s' % URL)
+os.system('unzip %s' % ZIPFILE)
+try: os.remove(ZIPFILE)
+except: pass
+
+FOLDER = [t for t in os.listdir('.') if t.startswith(ZIPFILE.split('-',1)[0]) and os.path.isdir(t)][0]
+BLOCKS_CSV = '%s/GeoLiteCity-Blocks.csv' % FOLDER
+LOCATION_CSV = '%s/GeoLiteCity-Location.csv' % FOLDER
+
+print 'Remove old dump file: %s' % DUMP
+
+try: os.remove(DUMP)
+except: pass
+
+DMP = open(DUMP, 'a')
+# ------------- Blocks -------------
+DMP.write(_SQL1)
+print 'Parse: %s' % BLOCKS_CSV
+
+csvfile = open(BLOCKS_CSV, 'rb')
+csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
+CNT = 0
+for row in csvreader:
+	if len(row) > 1 and row[0].isdigit():
+		ROW = '(%s),\n' % ','.join(row)
+		DMP.write(ROW)
+		CNT += 1
+	if not CNT % 10000:
+		os.system('printf "."')
+		DMP.write('(0,0,0);\nINSERT INTO geoip_blocks VALUES')
+
+print '\nTotal blocks: %s' % CNT
+		
+DMP.write(_SQL2)
+# ------------- Location -------------
+DMP.write(_SQL3)
+
+print 'Parse: %s' % LOCATION_CSV
+
+csvfile = open(LOCATION_CSV, 'rb')
+csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
+CNT = 0
+for row in csvreader:
+	if len(row) > 1 and row[0].isdigit():
+		ROW = '(%s),\n' % ','.join(["'%s'" % t.replace("'","`") for t in row])
+		try: ROW = ROW.decode('latin-1').encode('utf8')
+		except:
+			print 'Error!', ROW
+			ROW = ''.join([['?',l][l<='~'] for l in ROW])
+		DMP.write(ROW)
+		CNT += 1
+	if not CNT % 10000:
+		os.system('printf "."')
+		DMP.write('(0,\'\',\'\',\'\',\'\',0,0,\'\',\'\');\nINSERT INTO geoip_location VALUES')
+
+print '\nTotal locations: %s' % CNT
+
+DMP.write(_SQL4)
+DMP.close()
+
+print 'Import dump file to PostgreSQL'
+os.system('psql -U isidabot isidabot -f %s' % DUMP)
+
+print 'Done!'
+# The end is near!
diff --git a/plugins/geoip.py b/plugins/geoip.py
new file mode 100644
index 0000000..a8555df
--- /dev/null
+++ b/plugins/geoip.py
@@ -0,0 +1,47 @@
+#!/usr/bin/python
+# -*- coding: utf-8 -*-
+
+# --------------------------------------------------------------------------- #
+#                                                                             #
+#    Plugin for iSida Jabber Bot                                              #
+#    Copyright (C) diSabler <dsy@dsy.name>                                    #
+#                                                                             #
+#    This program is free software: you can redistribute it and/or modify     #
+#    it under the terms of the GNU General Public License as published by     #
+#    the Free Software Foundation, either version 3 of the License, or        #
+#    (at your option) any later version.                                      #
+#                                                                             #
+#    This program is distributed in the hope that it will be useful,          #
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of           #
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            #
+#    GNU General Public License for more details.                             #
+#                                                                             #
+#    You should have received a copy of the GNU General Public License        #
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.    #
+#                                                                             #
+# --------------------------------------------------------------------------- #
+
+def geoip(type, jid, nick, text):
+	text = text.lower().strip()
+	is_ip = None
+	if text.count('.') == 3:
+		is_ip = True
+		for ii in text:
+			if not nmbrs.count(ii):
+				is_ip = None
+				break
+	if is_ip: IP = text
+	else:
+		try: IP = socket.gethostbyname_ex(text.encode('idna'))[2][0]
+		except: IP = None
+	if IP:
+		DEC_IP = sum([int(t[1])*256**t[0] for t in enumerate(IP.split('.')[::-1],0)])
+		INFO = cur_execute_fetchone('select country,region,city,postalCode,latitude,longitude,metroCode,areaCode from geoip_location where locid=(select locId from geoip_blocks where startIpNum<=%s and endIpNum>=%s limit 1);',(DEC_IP,DEC_IP))
+		if INFO: msg = L('IP: %s\nAddress: %s, %s, %s, %s\nLat/lon: %s/%s | Metro/Area: %s/%s','%s/%s'%(jid,nick)) % tuple([t,'?'][t == ''] for t in tuple((IP,) + INFO))
+		else: msg = L('GEO info not found!','%s/%s'%(jid,nick))
+	else: msg = L('I can\'t resolve it','%s/%s'%(jid,nick))
+	send_msg(type, jid, nick, msg)
+
+global execute
+
+if base_type == 'pgsql': execute = [(4, 'geo', geoip, 2, 'GEOIP information about IP or domain')]