import repository from arizona
[raven.git] / apps / gacks / gacksqueue_mysql.py
1 from gacksqueue 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 GacksMySQLQueue(GacksQueue):
14     def __init__(self, tableName="asap"):
15         GacksQueue.__init__(self)
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.open()
23
24     def get_password(self):
25         return open(self.passwordfile,"r").readline().strip()
26
27     def get_root_password(self):
28         return open(self.rootpasswordfile,"r").readline().strip()
29
30     def reset(self):
31         self.execute("DROP TABLE IF EXISTS `" + self.tablename + "`;")
32         self.create()
33
34     def open(self):
35         self.connect()
36         self.create()
37
38     def lock(self):
39         self.execute("SELECT GET_LOCK('queue_lock', 300);")  # 5 minute duration
40
41     def unlock(self):
42         self.cursor.execute("SELECT RELEASE_LOCK('queue_lock');")
43
44     def connect(self, db=None, user=None, passwd=None, address=None):
45         if not db:\r
46             db = self.dbname\r
47         if not user:\r
48             user = self.user\r
49         if not passwd:\r
50             passwd = self.get_password()\r
51         if not address:\r
52             address = self.address\r
53         self.conn = MySQLdb.connect(host=address, user=user, passwd=passwd, db=db, cursorclass=MySQLdb.cursors.DictCursor)\r
54         self.cursor = self.conn.cursor()\r
55 \r
56     def create(self):\r
57         querystr = "CREATE TABLE IF NOT EXISTS `" + self.tablename + "`" + """\r
58                        (`jobid`         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
59                         `allocatorHRN`  TEXT,
60                         `consumerHRN`   TEXT,
61                         `duration`      INT,
62                         `resources`     TEXT);"""
63         self.execute(querystr);
64
65     def execute(self, query, args=None):
66         if args is not None:
67              query = query % self.conn.literal(args)
68         # print "Query:", query
69         self.cursor.execute(query)
70
71     def execute_dict(self, query, args=None):
72         self.execute(query, args)
73         return self.cursor.fetchall()
74
75     def query(self, id=None, allocatorHRN=None, consumerHRN=None):
76         querystr = "SELECT * FROM `" + self.tablename +"`"
77         conds = []
78         if (id != None):
79            conds.append("jobid='" + str(id) + "'")
80         if (allocatorHRN != None):
81            conds.append("allocatorHRN='" + allocatorHRN + "'")
82         if (consumerHRN != None):
83            conds.append("consumerHRN='" + consumerHRN + "'")
84
85         if (len(conds) > 0):
86             querystr = querystr + " WHERE " + " AND ".join(conds)
87
88         dicts = self.execute_dict(querystr)
89         jobs = []
90         for dict in dicts:
91             jobs.append( AsapJob(dict = dict) )
92
93         return jobs
94
95     def delete(self, id=None, allocatorHRN=None, consumerHRN=None):
96         querystr = "DELETE FROM `" + self.tablename + "`"
97         conds = []
98         if (id != None):
99            conds.append("jobid='" + str(id) + "'")
100         if (allocatorHRN != None):
101            conds.append("allocatorHRN='" + allocatorHRN + "'")
102         if (consumerHRN != None):
103            conds.append("consumerHRN='" + consumerHRN + "'")
104
105         if (len(conds) > 0):
106             querystr = querystr + " WHERE " + " AND ".join(conds)
107
108         self.execute(querystr)
109
110     def add(self, job):
111         self.execute("INSERT INTO `" + self.tablename + "` (allocatorHRN, consumerHRN, duration, resources) VALUES('%s','%s','%d','%s');" %
112                      (job.get_allocator(), job.get_consumer(), job.get_duration(), job.get_resources_string()))
113