import repository from arizona
[raven.git] / apps / gacks / gacksaccountmanager.py
1 import fcntl
2 import os
3 import time
4
5 from gacksexcep import *
6 from gackshandle import *
7 from gacksaccount import GacksAccount
8 import MySQLdb
9 import MySQLdb.cursors
10
11 class GacksAccountManager:
12     def __init__(self, tableName=None):
13         if tableName is None:
14              tableName = "gacksaccount"
15
16         self.tablename = tableName
17         self.dbname = "gacks"
18         self.address = "localhost"
19         self.user = "gacks"
20         self.passwordfile = "/root/mysql-gacks-pass.txt"
21         self.rootpasswordfile = "/root/mysql-root-pass.txt"
22         self.init_database()
23         self.open()
24
25     def get_password(self):
26         return open(self.passwordfile,"r").readline().strip()
27
28     def get_root_password(self):
29         return open(self.rootpasswordfile,"r").readline().strip()
30
31     def reset(self):
32         self.execute("DROP TABLE IF EXISTS `" + self.tablename + "`;")
33         self.create()
34
35     def open(self):
36         self.connect()
37         self.create()
38
39     def connect(self, db=None, user=None, passwd=None, address=None):
40         if not db:\r
41             db = self.dbname\r
42         if not user:\r
43             user = self.user\r
44         if not passwd:\r
45             passwd = self.get_password()\r
46         if not address:\r
47             address = self.address\r
48         self.conn = MySQLdb.connect(host=address, user=user, passwd=passwd, db=db, cursorclass=MySQLdb.cursors.DictCursor)\r
49         self.cursor = self.conn.cursor()\r
50
51     def init_database(self):
52         self.connect(db="information_schema")\r
53         self.execute("SELECT * FROM SCHEMATA WHERE SCHEMA_NAME = %s", (self.dbname))
54         row = self.cursor.fetchone();
55         if row == None:
56             self.connect(db="information_schema", user="root", passwd=self.get_root_password())
57             self.execute("CREATE DATABASE IF NOT EXISTS `" + self.dbname + "`;")
58             self.execute("GRANT ALL PRIVILEGES ON `" + self.dbname +"` . * TO %s@'%%';", (self.user))
59             self.execute("GRANT ALL PRIVILEGES ON `" + self.dbname +"` . * TO %s@'localhost';", (self.user))
60
61     def create(self):
62         querystr = "CREATE TABLE IF NOT EXISTS `" + self.tablename + "`" + """\r
63                        (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
64                         `name`          VARCHAR(255) NOT NULL,
65                         `kind`          VARCHAR(32) NOT NULL,
66                         `level`         VARCHAR(32) NOT NULL,
67                         `balance`       FLOAT,
68                         `goodStanding`  INT,
69                         `multiplier`    INT,
70                         `maxBalance`    INT,
71                         `minBalance`    INT,
72                         `outRate`       INT,
73                         `inRate`        INT,
74                         `lastInTime`    TIMESTAMP,
75                         `lastOutTime`   TIMESTAMP,
76                         `lastOutAmount` INT,
77                         INDEX(`name`),
78                         INDEX(`kind`),
79                         UNIQUE(`name`, `kind`));"""
80         self.execute(querystr);
81
82     def execute(self, query, args=None):
83         if args is not None:
84              query = query % self.conn.literal(args)
85         self.cursor.execute(query)
86
87     def execute_dict(self, query, args=None):
88         self.execute(query, args)
89         return self.cursor.fetchall()
90
91     def get_id(self, name, kind):
92         records = self.execute_dict("SELECT `id` FROM `" + self.tablename + "` WHERE name=%s and kind=%s;", (name, kind))
93         if len(records)==0:
94             return None
95         else:
96             return records[0]["id"]
97
98     def get_name(self, id, kind):
99         records = self.execute_dict("SELECT `name` FROM `" + self.tablename + "` WHERE id=%s AND kind=%s;", (str(id), kind))
100         if len(records)==0:
101             return None
102         else:
103             return records[0]["name"]
104
105     def fill_account(self, acct):
106         if (acct.id == None):
107             acct.id = self.get_id(acct.name, acct.kind)
108
109         if (acct.id == None):
110             return False
111
112         records = self.execute_dict("SELECT *,UNIX_TIMESTAMP(lastInTime) FROM `" + self.tablename + "` WHERE id=%s;", (str(acct.id)))
113         if len(records)==0:
114             # this shouldn't happen
115             return False
116         else:
117             acct.balance = float(records[0]["balance"])
118             acct.goodStanding = int(records[0]["goodStanding"])
119             acct.multiplier = int(records[0]["multiplier"])
120             acct.maxBalance = int(records[0]["maxBalance"])
121             acct.minBalance = int(records[0]["minBalance"])
122             acct.level = records[0]["level"]
123             acct.inRate = int(records[0]["inRate"])
124             acct.lastInTime = int(records[0]["UNIX_TIMESTAMP(lastInTime)"])
125
126             acct.lastBalance = acct.balance
127             acct.lastGoodStanding = acct.goodStanding
128             acct.lastMultiplier = acct.multiplier
129             acct.lastMaxBalance = acct.maxBalance
130             acct.lastMinBalance = acct.minBalance
131             acct.lastLevel = acct.level
132             acct.lastInRate = acct.inRate
133             acct.lastLastInTime = acct.lastInTime
134
135         return True
136
137     def write_account(self, acct):
138         # only update things that have changed
139         fieldNames=[]
140         fieldValues=[]
141         if acct.balance != acct.lastBalance:
142             fieldNames.append("balance")
143             fieldValues.append(self.conn.literal(acct.balance))
144         if acct.goodStanding != acct.lastGoodStanding:
145             fieldNames.append("goodStanding")
146             fieldValues.append(self.conn.literal(acct.goodStanding))
147         if acct.multiplier != acct.lastMultiplier:
148             fieldNames.append("multiplier")
149             fieldValues.append(self.conn.literal(acct.multiplier))
150         if acct.maxBalance != acct.lastMaxBalance:
151             fieldNames.append("maxBalance")
152             fieldValues.append(self.conn.literal(acct.maxBalance))
153         if acct.minBalance != acct.lastMinBalance:
154             fieldNames.append("minBalance")
155             fieldValues.append(self.conn.literal(acct.minBalance))
156         if acct.level != acct.lastLevel:
157             fieldNames.append("level")
158             fieldValues.append(self.conn.literal(acct.level))
159         if acct.inRate != acct.lastInRate:
160             fieldNames.append("inRate")
161             fieldValues.append(self.conn.literal(acct.inRate))
162         if acct.lastInTime != acct.lastLastInTime:
163             fieldNames.append("lastInTime")
164             fieldValues.append("FROM_UNIXTIME(" + str(acct.lastInTime) + ")")
165
166         if (acct.id == None):
167             acct.id = self.get_id(acct.name, acct.kind)
168
169         if (acct.id == None):
170             # it doesn't exist in the database, so lets create it
171             fieldNames += ["name", "kind"]
172             fieldValues += [self.conn.literal(acct.name), self.conn.literal(acct.kind)]
173             self.execute_dict("INSERT INTO `" + self.tablename + "` (" + ", ".join(fieldNames) + ") VALUES(" + ", ".join(fieldValues) + ");")
174
175         elif fieldNames != []:
176             # it does exist so lets update it
177             updates = []
178             for (i, fieldName) in enumerate(fieldNames):
179                 updates.append(fieldName + "=" + fieldValues[i])
180             #print updates
181
182             self.execute_dict("UPDATE `" + self.tablename + "` SET " + ", ".join(updates) + " WHERE id=%s;", (str(acct.id)))
183
184         else:
185             # nothing has changed, so nothing to update
186             pass
187
188         acct.lastBalance = acct.balance
189         acct.lastGoodStanding = acct.goodStanding
190         acct.lastMultiplier = acct.multiplier
191         acct.lastMaxBalance = acct.maxBalance
192         acct.lastMinBalance = acct.minBalance
193         acct.lastLevel = acct.level
194         acct.lastInRate = acct.inRate
195         acct.lastLastInTime = acct.lastInTime
196
197     def get_account(self, name, kind, create_if_not_exist=False):
198         acct = GacksAccount(name, kind, manager=self)
199         if not self.fill_account(acct):
200             if not create_if_not_exist:
201                 return None
202             acct = GacksAccount(name, kind, manager=self)
203             acct.created=True
204             acct.commit()
205
206         return acct
207
208     def delete_account(self, name, kind):
209         self.execute("DELETE FROM `" + self.tablename +  "` WHERE name=%s and kind=%s;", (name, kind))
210
211     def new_account(self, name, kind):
212         return GacksAccount(name, kind, manager=self)
213
214     def list_accounts(self):
215         records = self.execute_dict("SELECT * FROM `" + self.tablename + "`")
216         return records
217
218
219