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 206 attachment: postgresql.0.8.1.5.patch (10.9 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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
diff -urNx '*.py[co]' pyicqt-0.8.1.5.orig/src/config.py pyicqt-0.8.1.5/src/config.py
--- pyicqt-0.8.1.5.orig/src/config.py 2009-03-26 00:54:40.000000000 +0300
+++ pyicqt-0.8.1.5/src/config.py 2009-03-26 00:55:46.000000000 +0300
@@ -46,6 +46,7 @@
admins = []
xdbDriver = "xmlfiles"
xdbDriver_mysql = {}
+xdbDriver_postgresql = {}
xdbDriver_xmlfiles = {}
useXCP = bool(False)
useComponentBinding = bool(False)
diff -urNx '*.py[co]' pyicqt-0.8.1.5.orig/src/xdb/postgresql.py pyicqt-0.8.1.5/src/xdb/postgresql.py
--- pyicqt-0.8.1.5.orig/src/xdb/postgresql.py 1970-01-01 03:00:00.000000000 +0300
+++ pyicqt-0.8.1.5/src/xdb/postgresql.py 2009-03-26 00:55:46.000000000 +0300
@@ -0,0 +1,248 @@
+# 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 pgdb
+import re
+
+class XDB:
+ """
+ Class for storage of data.
+ """
+ def __init__(self, name):
+ """ Creates an XDB object. """
+ self.db=pgdb.connect(
+ host=config.xdbDriver_postgresql["server"],
+ user=config.xdbDriver_postgresql["username"],
+ password=config.xdbDriver_postgresql["password"],
+ database=config.xdbDriver_postgresql["database"]
+ )
+ if not self.db:
+ print "Unable to connect to MySQL database."
+ sys.exit(1)
+
+ def db_ping(self, get_cursor=0):
+ """
+ Function for connection check to reconnect on lost connection
+ There are no ping in pgdb, so we use db.cursor()
+ """
+ try:
+ c=self.db.cursor()
+ except:
+ self.db=pgdb.connect(
+ host=config.xdbDriver_postgresql["server"],
+ user=config.xdbDriver_postgresql["username"],
+ password=config.xdbDriver_postgresql["password"],
+ database=config.xdbDriver_postgresql["database"]
+ )
+ c=self.db.cursor()
+ if get_cursor: return c
+ c.close()
+
+ def getRegistration(self, jabberID):
+ """ Retrieve registration information from the XDB.
+ Returns a username and password. """
+ c=self.db_ping(1)
+ c.execute("SELECT username,password,encryptedpassword FROM register WHERE owner = '%s'" % jabberID)
+ ret = c.fetchone()
+ if ret:
+ (username,password,encpass) = ret
+ if encpass:
+ return (username,encpass)
+ else:
+ return (username,password)
+ else:
+ return None
+
+ def getRegistrationList(self):
+ """ Returns an array of all of the registered jids. """
+ c=self.db_ping(1)
+ 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. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM register WHERE owner = '%s'" % jabberID)
+ if config.xdbDriver_mysql.get("format","") == "encrypted":
+ c.execute("INSERT INTO register(owner,username,encryptedpassword) VALUES('%s','%s','%s')" % (jabberID, username, password))
+ else:
+ c.execute("INSERT INTO register(owner,username,password) VALUES('%s','%s','%s')" % (jabberID, username, password))
+ self.db.commit()
+
+ def removeRegistration(self, jabberID):
+ """ Removes a registration from the XDB. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM register WHERE owner = '%s'" % jabberID)
+ c.execute("DELETE FROM settings WHERE owner = '%s'" % jabberID)
+ c.execute("DELETE FROM lists WHERE owner = '%s'" % jabberID)
+ c.execute("DELETE FROM list_attributes WHERE owner = '%s'" % jabberID)
+ self.db.commit()
+
+ def getSettingList(self, jabberID):
+ """ Gets a list of all settings for a user from the XDB. """
+ c=self.db_ping(1)
+ c.execute("SELECT variable,value FROM settings WHERE 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. """
+ c=self.db_ping(1)
+ c.execute("SELECT value FROM settings WHERE owner = '%s' AND 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. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM settings WHERE owner = '%s' AND variable = '%s'" % (jabberID, variable))
+ c.execute("INSERT INTO settings(owner,variable,value) VALUES('%s','%s','%s')" % (jabberID, variable, value))
+ self.db.commit()
+
+ def getCSetting(self, jabberID, variable):
+ """ Gets a custom user setting from the XDB. """
+ c=self.db_ping(1)
+ c.execute("SELECT value FROM csettings WHERE owner = '%s' AND variable = '%s'" % (jabberID, variable))
+ ret = c.fetchone()
+ if ret:
+ (value) = ret[0]
+ return value
+ else:
+ return None
+
+ def setCSetting(self, jabberID, variable, value):
+ """ Sets a custom user setting in the XDB. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM csettings WHERE owner = '%s' AND variable = '%s'" % (jabberID, variable))
+ c.execute("INSERT INTO csettings(owner,variable,value) VALUES('%s','%s','%s')" % (jabberID, variable, value))
+ self.db.commit()
+
+ def getXstatusText(self, jabberID, number):
+ """ Get a latest title and desc for x-status """
+ c=self.db_ping(1)
+ c.execute("SELECT title, value FROM xstatuses WHERE owner = '%s' AND number = '%s'" % (jabberID, number))
+ ret = c.fetchone()
+ if ret:
+ (title) = ret[0]
+ (value) = ret[1]
+ return (title, value)
+ else:
+ return ('','')
+
+ def setXstatusText(self, jabberID, number, title, desc):
+ """ Set a latest title and desc for x-status """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM xstatuses WHERE owner = '%s' AND number = '%s'" % (jabberID, number))
+ c.execute("INSERT INTO xstatuses(owner,number,title,value) VALUES('%s','%s','%s','%s')" % (jabberID, number, title, desc))
+ self.db.commit()
+
+ def getCSettingList(self, jabberID):
+ """ Gets a list of all custom settings for a user from the XDB. """
+ c=self.db_ping(1)
+ c.execute("SELECT variable,value FROM csettings WHERE owner = '%s'" % (jabberID))
+ results = dict([])
+ ret = c.fetchone()
+ while ret:
+ (variable) = ret[0]
+ (value) = ret[1]
+ results[variable] = value
+ ret = c.fetchone()
+ return results
+
+ 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. """
+ c=self.db_ping(1)
+ attributes = {}
+ c.execute("SELECT attribute,value FROM list_attributes WHERE owner = '%s' AND type = '%s' AND 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. """
+ c=self.db_ping(1)
+ types = []
+ c.execute("SELECT type FROM lists WHERE 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. """
+ c=self.db_ping(1)
+ entities = []
+ c.execute("SELECT jid FROM lists WHERE owner = '%s' AND type = '%s'" % (jabberID, type))
+ ret = c.fetchone()
+ while ret:
+ (jid) = ret[0]
+ entity = []
+ entity.append(jid)
+ attributes = {}
+ ic = self.db_ping(1)
+ ic.execute("SELECT attribute,value FROM list_attributes WHERE owner = '%s' AND type = '%s' AND 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. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM lists WHERE owner = '%s' AND type = '%s' AND jid = '%s'" % (jabberID, type, legacyID))
+ c.execute("DELETE FROM list_attributes WHERE owner = '%s' AND type = '%s' AND jid = '%s'" % (jabberID, type, legacyID))
+ c.execute("INSERT INTO lists(owner,type,jid) VALUES('%s','%s','%s')" % (jabberID, type, legacyID))
+ for p in payload.keys():
+ c.execute("INSERT INTO list_attributes(owner,type,jid,attribute,value) VALUES('%s','%s','%s','%s','%s')" % (jabberID, type, legacyID, p, re.escape(payload[p].replace("'", "\\'"))))
+ self.db.commit()
+
+ 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. """
+ c=self.db_ping(1)
+ c.execute("DELETE FROM lists WHERE owner = '%s' AND type = '%s' AND jid = '%s'" % (jabberID, type, legacyID))
+ c.execute("DELETE FROM list_attributes WHERE owner = '%s' AND type = '%s' AND jid = '%s'" % (jabberID, type, legacyID))
+ self.db.commit()
+
+
+def housekeep():
+ """ Perform cleanup type tasks upon startup. """
+ pass
diff -urNx '*.py[co]' pyicqt-0.8.1.5.orig/tools/db-setup.pgsql pyicqt-0.8.1.5/tools/db-setup.pgsql
--- pyicqt-0.8.1.5.orig/tools/db-setup.pgsql 1970-01-01 03:00:00.000000000 +0300
+++ pyicqt-0.8.1.5/tools/db-setup.pgsql 2009-03-26 00:55:46.000000000 +0300
@@ -0,0 +1,68 @@
+--
+-- This is the required schema for MySQL. Load this into the database
+-- using the mysql interactive terminal:
+--
+-- mysql> \. db-setup.mysql
+--
+-- Then make sure you create a user in MySQL and grant it full access
+-- to the pyicqt database. You will need to enter this information
+-- into your PyICQt config file.
+--
+
+--
+-- registration table
+--
+CREATE TABLE pyicqt.register (
+ owner TEXT NOT NULL,
+ username VARCHAR,
+ password VARCHAR,
+ encryptedpassword VARCHAR
+);
+
+--
+-- settings table
+--
+CREATE TABLE pyicqt.settings (
+ owner TEXT NOT NULL,
+ variable VARCHAR,
+ value VARCHAR
+);
+
+--
+-- lists table
+--
+CREATE TABLE pyicqt.lists (
+ owner TEXT NOT NULL,
+ type VARCHAR NOT NULL,
+ jid VARCHAR
+);
+
+--
+-- list attributes table
+--
+CREATE TABLE pyicqt.list_attributes (
+ owner TEXT NOT NULL,
+ type VARCHAR NOT NULL,
+ jid VARCHAR,
+ attribute VARCHAR,
+ value VARCHAR
+);
+
+--
+-- custom settings table
+--
+CREATE TABLE pyicqt.csettings (
+ owner TEXT NOT NULL,
+ variable VARCHAR,
+ value VARCHAR
+);
+
+--
+-- x-statuses table
+--
+CREATE TABLE pyicqt.xstatuses (
+ owner TEXT NOT NULL,
+ number VARCHAR,
+ title VARCHAR,
+ value VARCHAR
+);
Powered by Google Project Hosting