PageRenderTime 125ms CodeModel.GetById 108ms app.highlight 13ms RepoModel.GetById 0ms app.codeStats 0ms

/historical/hive_mysql.py

https://bitbucket.org/lindenlab/apiary/
Python | 246 lines | 216 code | 6 blank | 24 comment | 2 complexity | 65f144b8c0fabe5c03665b160a32549a MD5 | raw file
  1#
  2# $LicenseInfo:firstyear=2010&license=mit$
  3# 
  4# Copyright (c) 2010, Linden Research, Inc.
  5# 
  6# Permission is hereby granted, free of charge, to any person obtaining a copy
  7# of this software and associated documentation files (the "Software"), to deal
  8# in the Software without restriction, including without limitation the rights
  9# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 10# copies of the Software, and to permit persons to whom the Software is
 11# furnished to do so, subject to the following conditions:
 12# 
 13# The above copyright notice and this permission notice shall be included in
 14# all copies or substantial portions of the Software.
 15# 
 16# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 17# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 18# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 19# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 20# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 21# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 22# THE SOFTWARE.
 23# $/LicenseInfo$
 24#
 25
 26from optparse import OptionParser
 27
 28import MySQLdb
 29import re
 30import time
 31
 32import hive
 33import sqllog
 34import timestamp
 35
 36def now(future=0.0):
 37    return timestamp.TimeStamp(time.time() + future)
 38
 39def wait_until(ts):
 40    tn = now()
 41    if ts > tn:
 42        delta = float(ts - tn)
 43        if delta > 65.0:
 44            raise Exception("trying to wait longer than 65s. now = %s, until = %s"
 45                % (str(tn), str(ts)))
 46        time.sleep(delta)
 47
 48mysql_host = 'localhost'
 49mysql_port = 3306
 50mysql_user = 'guest'
 51mysql_passwd = ''
 52mysql_db = 'test'
 53
 54class MySQLWorker(hive.Worker):
 55    def __init__(self, options, arguments):
 56        hive.Worker.__init__(self, options, arguments)
 57        self._asap = options.asap
 58        self._error = False
 59        self._errormsg = ''
 60        self._connect_options = {}
 61        self._connect_options['host'] = options.mysql_host
 62        self._connect_options['port'] = options.mysql_port
 63        self._connect_options['user'] = options.mysql_user
 64        self._connect_options['passwd'] = options.mysql_passwd
 65        self._connect_options['db'] = options.mysql_db
 66        self._connection = None
 67    
 68    def start(self):
 69#        self.log("starting")
 70        self._error = False
 71        self._errormsg = ''
 72        try:
 73            if self._connection is None:
 74#                self.log("making MySQL connection")
 75                connection = MySQLdb.connect(**self._connect_options)
 76                self._connection = connection
 77            pass
 78        except Exception, e: # more restrictive error catching?
 79            self._error = True
 80            self._errormsg = "500 " + str(e)
 81        
 82    def event(self, data):
 83#        self.log("event")
 84        if self._error:
 85            return
 86        try:
 87            (tstr, sql) = data.split("\t", 1)
 88            if not self._asap:
 89                self.log("waiting")
 90                wait_until(timestamp.TimeStamp(tstr))
 91            sql = sql.strip()
 92            if sql:
 93                self.log("executing SQL: " + sql)
 94                self.execute_sql(sql)  
 95#                self.log("finished SQL")
 96        except Exception, e: # more restrictive error catching?
 97            self._error = True
 98            self._errormsg = "501 " + str(e)
 99        
100    def end(self):
101#        try:
102#            self.log("committing")
103#            self._connection.commit() # unclear if this should be here
104#        except Exception, e:
105#            pass
106        try:
107            if True:
108#                self.log("closing")
109                self._connection.close()
110                self._connection = None
111        except Exception, e:
112            if not self._error:
113                self._error = True
114                self._errormsg = "502 " + str(e)
115#        self.log("ended")
116        if self._error:
117            return self._errormsg
118        return '200 OK'
119
120    def execute_sql(self, sql):
121        """Execute an SQL statement.
122        
123        Subclasses may override this to alter the SQL before being executed.
124        If so, they should call this inherited version to actually execute
125        the statement. It is acceptable for a sub-class to call this version
126        multiple times.
127        
128        Exceptions are caught in the outer calling function (event())
129        
130        """
131        cursor = self._connection.cursor()
132        cursor.execute(sql)
133        try:
134            cursor.fetchall()
135        except:
136            pass # not all SQL has data to fetch
137        cursor.close()
138        
139
140class MySQLCentral(hive.Central):
141    def __init__(self, options, arguments):
142        hive.Central.__init__(self, options, arguments)
143        self._events = sqllog.input_events(arguments)
144            # this builds a sequence of events from the log streams in the
145            # arguments, which come here from the command line
146        self._connections = {}
147        self._tally = {}
148        self._time_offset = None
149        self._tally_time = time.time() + 15.0
150        
151    def tally(self, msg):
152        if "Duplicate entry" in msg:
153            msg = '501 (1062, "Duplicate entry for key")'
154        if "You have an error in your SQL syntax" in msg:
155            msg = '501 (1064, "You have an error in your SQL syntax")'
156        self._tally[msg] = self._tally.get(msg, 0) + 1
157        if time.time() > self._tally_time:
158            self.print_tally()
159
160    
161    def print_tally(self):
162        keys = self._tally.keys()
163        keys.sort()
164        print
165        print "       count - message"
166        print "------------   -------------------------------------------"
167        for k in keys:
168            print ("%12d - %s" % (self._tally[k], k))
169        self._tally_time = time.time() + 15.0
170
171        
172    def next(self):
173        try:
174            while True:
175                e = self._events.next() # e is a sqllog.Event object
176                if e.state != sqllog.Event.Response:
177                    break
178        except StopIteration:
179            return False
180        
181        if self._time_offset is None:
182            self._time_offset = now(1.0) - e.time
183        t = self._time_offset + e.time
184        
185        id = e.id
186        if e.state == sqllog.Event.End:
187            if id in self._connections:
188                self.tally("102 End connection")
189                del self._connections[id]
190                self.end(id)
191            else:
192                self.tally("103 Duplicate end")
193        else:
194            if id not in self._connections:
195                self.tally("100 Start connection")
196                s = self._connections[id] = True
197                self.start(id)
198            self.tally("101 Event")
199            self.event(id, str(t) + "\t" + e.body)
200
201        return True
202    
203    def result(self, seq, d):
204        self.tally(d)
205    
206        
207    def main(self):
208        t = - time.time()
209        c = - time.clock()
210        hive.Central.main(self)
211        c += time.clock()
212        t += time.time()
213
214        # This never actually happens because hive.Central.main(self) never completes.  
215        # Figure out how to force it to say it exited neatly?
216        print ("Timing: %f process clock, %f wall clock" % (c, t))
217        self.print_tally()
218
219
220class MySQLHive(hive.Hive):
221    def __init__(self, central_cls=MySQLCentral, worker_cls=MySQLWorker):
222        hive.Hive.__init__(self, central_cls, worker_cls)
223    
224    def add_options(self, parser):
225        hive.Hive.add_options(self, parser)
226        parser.add_option('--asap',
227                            action='store_true', default=False,
228                            help='run SQL connections as fast as possible (default: off)')
229        parser.add_option('--mysql-host',
230                            default=mysql_host, metavar='HOST',
231                            help='MySQL server to connect to (default: %default)')
232        parser.add_option('--mysql-port',
233                            default=mysql_port, metavar='PORT',
234                            help='MySQL port to connect on (default: %default)')
235        parser.add_option('--mysql-user',
236                            default=mysql_user, metavar='USER',
237                            help='MySQL user to connect as (default: %default)')
238        parser.add_option('--mysql-passwd',
239                            default=mysql_passwd, metavar='PW',
240                            help='MySQL password to connect with (default: %default)')
241        parser.add_option('--mysql-db',
242                            default=mysql_db, metavar='DB',
243                            help='MySQL database to connect to (default: %default)')
244
245if __name__ == '__main__':
246    MySQLHive().main()