import repository from arizona
[raven.git] / apps / gacks / gackscalendar_mysql.py
1 from gackscalendar import *
2
3 import time
4 import MySQLdb
5 import MySQLdb.cursors
6
7 """ database notes:
8
9       * backup the database
10             mysqldump --add-drop-table -h localhost -u gacks -p gacks > gacks.sql
11 """
12
13 class GacksMySQLCalendar(GacksCalendar):
14     def __init__(self, tableName=None):
15         if tableName is None:
16              tableName = "gacks"
17
18         GacksCalendar.__init__(self)
19         self.tablename = tableName
20         self.dbname = "gacks"
21         self.address = "localhost"
22         self.user = "gacks"
23         self.passwordfile = "/root/mysql-gacks-pass.txt"
24         self.rootpasswordfile = "/root/mysql-root-pass.txt"
25         self.init_database()
26         self.open()
27
28     def get_password(self):
29         return open(self.passwordfile,"r").readline().strip()
30
31     def get_root_password(self):
32         return open(self.rootpasswordfile,"r").readline().strip()
33
34     def reset(self):
35         self.execute("DROP TABLE IF EXISTS `" + self.tablename + "`;")
36         self.create()
37
38     def open(self):
39         self.connect()
40         self.create()
41
42     def lock(self):
43         self.execute("SELECT GET_LOCK('cal_lock', 300);")  # 5 minute duration
44
45     def unlock(self):
46         self.cursor.execute("SELECT RELEASE_LOCK('cal_lock');")
47
48     def exists(self):
49         tableList = self.cnx.get_tables()\r
50         if 'public.' + self.tablename in tableList:\r
51             return True\r
52         if 'public."' + self.tablename + '"' in tableList:\r
53             return True\r
54         return False\r
55 \r
56     def connect(self, db=None, user=None, passwd=None, address=None):\r
57         if not db:\r
58             db = self.dbname\r
59         if not user:\r
60             user = self.user\r
61         if not passwd:\r
62             passwd = self.get_password()\r
63         if not address:\r
64             address = self.address\r
65         self.conn = MySQLdb.connect(host=address, user=user, passwd=passwd, db=db, cursorclass=MySQLdb.cursors.DictCursor)\r
66         self.cursor = self.conn.cursor()\r
67 \r
68     def init_database(self):\r
69         self.connect(db="information_schema")\r
70         self.execute("SELECT * FROM SCHEMATA WHERE SCHEMA_NAME = %s", (self.dbname))
71         row = self.cursor.fetchone();
72         if row == None:
73             self.connect(db="information_schema", user="root", passwd=self.get_root_password())
74             self.execute("CREATE DATABASE IF NOT EXISTS `" + self.dbname + "`;")
75             self.execute("GRANT ALL PRIVILEGES ON `" + self.dbname +"` . * TO %s@'%%';", (self.user))
76             self.execute("GRANT ALL PRIVILEGES ON `" + self.dbname +"` . * TO %s@'localhost';", (self.user))\r
77 \r
78     def create(self):\r
79         querystr = "CREATE TABLE IF NOT EXISTS `" + self.tablename + "`" + """\r
80                        (`cellkey`       VARCHAR(255) NOT NULL,
81                         `id`            VARCHAR(64) NOT NULL,
82                         `unitStart`     INT NOT NULL,
83                         `unitStop`      INT NOT NULL,
84                         `timeStart`     INT NOT NULL,
85                         `timeStop`      INT NOT NULL,
86                         `allocatorHRNs` TEXT,
87                         `consumerHRN`   TEXT);"""
88         self.execute(querystr);
89
90     def execute(self, query, args=None):
91         if args is not None:
92              query = query % self.conn.literal(args)
93         self.cursor.execute(query)
94
95     def execute_dict(self, query, args=None):
96         self.execute(query, args)
97         return self.cursor.fetchall()
98
99     def garbage_collect(self, timeStop=None):
100         if not timeStop:
101             timeStop = time.time()
102         self.execute("DELETE FROM `" + self.tablename +"` WHERE timeStop<%s", (timeStop))
103
104     def find_record(self, item):
105         list = self.execute_dict("SELECT * FROM `" + self.tablename + "` WHERE cellkey=%s;", (item.get_cell_key()) )
106
107         if not list:
108             return None
109
110         if len(list) > 1:
111             raise GacksMultipleRecordCollision(item_to_delete.as_string())
112
113         return list[0]
114
115     def remove_record(self, item):
116         self.execute("DELETE FROM `" + self.tablename + "` WHERE cellkey = %s;", (item.get_cell_key()))
117
118     def insert_record(self, item):
119         conflicts = self.query_overlap(item.id, item.unitStart, item.unitStop, item.timeStart, item.timeStop)
120         if conflicts:
121             raise GacksConflictingInsert(item.as_string())
122
123         self.execute("INSERT INTO `" + self.tablename + "` "+ \
124                        "(cellkey, id, unitStart, unitStop, timeStart, timeStop, allocatorHRNs, consumerHRN) "+ \
125                        "VALUES(%s, %s, %s, %s, %s, %s, %s, %s);",
126
127                        (item.get_cell_key(),
128                         item.id,
129                         int(item.unitStart),
130                         int(item.unitStop),
131                         int(item.timeStart),
132                         int(item.timeStop),
133                         str(",".join(item.allocatorHRNs)),
134                         item.consumerHRN))
135
136     def is_empty(self):
137         if len(self.list_all_dict()) == 0:
138             return True
139         else:
140             return False
141
142     def list_all_dict(self):
143         dict_list = self.execute_dict("SELECT * FROM `" + self.tablename + "`;")
144         return dict_list
145
146     def dict_to_item(self, dict):
147         dict = dict.copy()
148         if not ("allocatorHRNs" in dict):
149             print dict
150         dict["allocatorHRNs"] = dict["allocatorHRNs"].split(",")
151         return GacksRecord(dict = dict)
152
153     def list_all(self):
154         list = []
155         dict_list = self.list_all_dict()
156         for dict in dict_list:
157             list.append(self.dict_to_item(dict))
158         return list
159
160     def query_overlap(self, id=None, unitStart=0, unitStop=INFINITY, timeStart=0, timeStop=INFINITY, hasAllocator=None, isLastAllocator=None):
161         all = self.list_all()
162         list = []
163         for item in all:
164             if self.test_id(id, item.id) and \
165                self.test_lesser(unitStart, item.unitStop) and \
166                self.test_greater(unitStop, item.unitStart) and \
167                self.test_lesser(timeStart, item.timeStop) and \
168                self.test_greater(timeStop, item.timeStart) and \
169                self.test_allocator(hasAllocator, item.allocatorHRNs) and \
170                self.test_last_allocator(isLastAllocator, item.allocatorHRNs):
171                  list.append(item)
172         return list
173
174     def query_exact(self, id=None, unitStart=None, unitStop=None, timeStart=None, timeStop=None, hasAllocator=None, isLastAllocator=None):
175         all = self.list_all()
176         list = []
177         for item in all:
178             if self.test_id(id, item.id) and \
179                self.test_equal(unitStart, item.unitStart) and \
180                self.test_equal(unitStop, item.unitStop) and \
181                self.test_equal(timeStart, item.timeStart) and \
182                self.test_equal(timeStop, item.timeStop) and \
183                self.test_allocator(hasAllocator, item.allocatorHRNs) and \
184                self.test_last_allocator(isLastAllocator, item.allocatorHRNs):
185                  list.append(item)
186         return list
187
188
189