My favorites | Sign in
Project Home Downloads Wiki Issues Source
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions

Issue 200 attachment: mysql.py (8.1 KB)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
# Copyright 2005-2006 Daniel Henninger <jadestorm@nc.rr.com>
# Licensed for distribution under the GPL version 2, check COPYING for details
#
# MySQL database storage. See db-setup.mysql in the tools directory in
# the root of the distribution, as well as configuration options in your
# transport config file. (see config_example.xml)
#

import config
import os
import MySQLdb
import string
import random
import hashlib

class XDB:
"""
Class for storage of data.
"""
def __init__(self, name):
""" Creates an XDB object. """
self.db=MySQLdb.connect(
host=config.xdbDriver_mysql["server"],
user=config.xdbDriver_mysql["username"],
passwd=config.xdbDriver_mysql["password"],
charset="utf8",
db=config.xdbDriver_mysql["database"]
)
if not self.db:
print "Unable to connect to MySQL database."
os.exit(1)

def db_ping(self):
"""
Wrapper function for MySQLdb.ping() to reconnect on lost connection
"""
try:
self.db.ping()
except:
self.db=MySQLdb.connect(
host=config.xdbDriver_mysql["server"],
user=config.xdbDriver_mysql["username"],
passwd=config.xdbDriver_mysql["password"],
charset="utf8",
db=config.xdbDriver_mysql["database"]
)
self.db.ping()

def getRegistration(self, jabberID):
""" Retrieve registration information from the XDB.
Returns a username and password. """
self.db_ping()
c=self.db.cursor()
c.execute("SELECT salt FROM register WHERE owner = '%s'" % (jabberID))
ret = c.fetchone()
if ret:
salt = ret[0]
self.db_ping()
ic=self.db.cursor()
if salt == '':
ic.execute("SELECT username,password FROM register WHERE owner = '%s'" % jabberID)
else:
ic.execute("SELECT username,AES_DECRYPT(UNHEX(password),'%s') FROM register WHERE owner = '%s'" % (hashlib.sha1(config.xdbDriver_mysql["password"] + salt).hexdigest(), jabberID))

iret = ic.fetchone()
if iret:
(username,password) = iret
return (username,password)
else:
return None

else:
return None

def getRegistrationList(self):
""" Returns an array of all of the registered jids. """
self.db_ping()
c=self.db.cursor()
c.execute("SELECT owner FROM register")
results = []
ret = c.fetchone()
while ret:
(jid) = ret[0]
results.append(jid)
ret = c.fetchone()
return results

def setRegistration(self, jabberID, username, password):
""" Sets up or creates a registration in the XDB.
username and password are for the legacy account. """
self.db_ping()
c=self.db.cursor()
c.execute("DELETE FROM register WHERE owner = '%s'" % jabberID)
if config.xdbDriver_mysql.get("format","") == "encrypted":
""" Creates Random Seed """
randomSeed=random.SystemRandom()
salt = ''
for x in randomSeed.sample(string.letters + string.digits, 6):
salt += x

c.execute("INSERT INTO register(owner,username,password,salt) VALUES('%s','%s',HEX(AES_ENCRYPT('%s','%s')),'%s')" % (jabberID, username, password, hashlib.sha1(config.xdbDriver_mysql["password"] + salt).hexdigest(), salt))
else:
c.execute("INSERT INTO register(owner,username,password,salt) VALUES('%s','%s','%s','')" % (jabberID, username, password))

def removeRegistration(self, jabberID):
""" Removes a registration from the XDB. """
self.db_ping()
c=self.db.cursor()
c.execute("DELETE FROM settings USING settings, register WHERE settings.ownerID = register.id AND register.owner = '%s'" % jabberID)
c.execute("DELETE FROM list_attributes USING list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s'" % jabberID)
c.execute("DELETE FROM register WHERE owner = '%s'" % jabberID)

def getSettingList(self, jabberID):
""" Gets a list of all settings for a user from the XDB. """
self.db_ping()
c=self.db.cursor()
c.execute("SELECT settings.variable,settings.value FROM settings, register WHERE settings.ownerID = register.id AND register.owner = '%s'" % (jabberID))
results = []
ret = c.fetchone()
while ret:
(variable) = ret[0]
(value) = ret[1]
results[variable] = value
ret = c.fetchone()
return results

def getSetting(self, jabberID, variable):
""" Gets a user setting from the XDB. """
self.db_ping()
c=self.db.cursor()
c.execute("SELECT settings.value FROM settings, register WHERE settings.ownerID = register.id AND register.owner = '%s' AND settings.variable = '%s'" % (jabberID, variable))
ret = c.fetchone()
if ret:
(value) = ret[0]
return value
else:
return None

def setSetting(self, jabberID, variable, value):
""" Sets a user setting in the XDB. """
self.db_ping()
c=self.db.cursor()
c.execute("DELETE FROM settings USING settings, register WHERE settings.ownerID = register.id AND register.owner = '%s' AND settings.variable = '%s'" % (jabberID, variable))
c.execute("INSERT INTO settings(ownerID,variable,value) VALUES((SELECT id FROM register WHERE owner = '%s'),'%s','%s')" % (jabberID, variable, value))

def getListEntry(self, type, jabberID, legacyID):
""" Retrieves a legacy ID entry from a list in
the XDB, based off the type and jabberID you provide. """
self.db_ping()
attributes = {}
c=self.db.cursor()
c.execute("SELECT list_attributes.attribute, list_attributes.value FROM list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s' AND list_attributes.type = '%s' AND list_attributes.jid = '%s'" % (jabberID, type, legacyID))
ret = c.fetchone()
while ret:
(attribute,value) = ret[0:1]
attributes[attribute] = value
ret = c.fetchone()
return attributes

def getListTypes(self, jabberID):
""" Returns an array containing a list of all list types
associated with a user. """
self.db_ping()
types = []
c=self.db.cursor()
c.execute("SELECT list_attributes.type FROM list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s'" % (jabberID))
ret = c.fetchone()
while ret:
(type) = ret[0]
types.append(type)
ret = c.fetchone()
return types

def getList(self, type, jabberID):
""" Retrieves an array containing an entire list of a
jabberID's from the XDB, based off the type and jabberID
you provide. """
self.db_ping()
entities = []
c=self.db.cursor()
c.execute("SELECT list_attributes.jid FROM list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s' AND list_attributes.type = '%s'" % (jabberID, type))
ret = c.fetchone()
while ret:
(jid) = ret[0]
entity = []
entity.append(jid)
attributes = {}
self.db_ping()
ic = self.db.cursor()
ic.execute("SELECT list_attributes.attribute, list_attributes.value FROM list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s' AND list_attributes.type = '%s' AND list_attributes.jid = '%s'" % (jabberID, type, jid))
iret = ic.fetchone()
while iret:
(attribute,value) = iret[0:2]
attributes[attribute] = value
iret = ic.fetchone()
entity.append(attributes)
ret = c.fetchone()
return entities

def setListEntry(self, type, jabberID, legacyID, payload = {}):
""" Updates or adds a legacy ID entry to a list in
the XDB, based off the type and jabberID you provide. """
self.db_ping()
c=self.db.cursor()
c.execute("DELETE FROM list_attributes USING list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s' AND list_attributes.type = '%s' AND list_attributes.jid = '%s'" % (jabberID, type, legacyID))
for p in payload.keys():
c.execute("INSERT INTO list_attributes(ownerID,type,jid,attribute,value) VALUES((SELECT id FROM register WHERE owner = '%s'),'%s','%s','%s','%s')" % (jabberID, type, legacyID, p, payload[p].replace("'", "\\'")))

def removeListEntry(self, type, jabberID, legacyID):
""" Removes a legacy ID entry from a list in
the XDB, based off the type and jabberID you provide. """
self.db_ping()
c=self.db.cursor()
c.execute("DELETE FROM list_attributes USING list_attributes, register WHERE list_attributes.ownerID = register.id AND register.owner = '%s' AND list_attributes.type = '%s' AND list_attributes.jid = '%s'" % (jabberID, type, legacyID))


def housekeep():
""" Perform cleanup type tasks upon startup. """
pass
Powered by Google Project Hosting