Commits

Mike Steder  committed 2138e10

Initial import

  • Participants

Comments (0)

Files changed (15)

+# use glob syntax.
+syntax: glob
+*.elc
+*.pyc
+*~
+*.log
+*.pid
+
+# switch to regexp syntax.
+syntax: regexp

File benchmarks/connect.py

+"""
+connection benchmark
+
+This simple script just attempts to connect to the database
+many times serially.
+
+This script simply requires a running server and database.
+
+TODO: modify this or create a script that uses threads
+      to open multiple connections at once to see how the
+      driver handles that.
+
+"""
+import sys
+import timeit
+
+
+from txmysql import settings
+
+
+def pymysql():
+    import pymysql as db
+    startTime = timeit.default_timer()
+    for x in xrange(settings.ITERATIONS):
+        con = db.connect(host=settings.HOSTNAME,
+                         user=settings.USERNAME,
+                         db=settings.DATABASE,
+                         passwd="")
+        con.close()
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "Pymysql connected %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    consPerSecond = settings.ITERATIONS / elapsedTime
+    print "Pymysql connected at a rate of %s/s"%(consPerSecond,)
+    avgConTime = elapsedTime / settings.ITERATIONS
+    print "Pymysql average connection time %s"%(avgConTime,)
+
+
+def mysqldb():
+    import MySQLdb as db
+    startTime = timeit.default_timer()
+    for x in xrange(settings.ITERATIONS):
+        con = db.connect(host=settings.HOSTNAME,
+                         user=settings.USERNAME,
+                         db=settings.DATABASE,
+                         use_unicode=settings.USE_UNICODE)
+        con.close()
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "MySQLdb connected %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    consPerSecond = settings.ITERATIONS / elapsedTime
+    print "MySQLdb connected at a rate of %s/s"%(consPerSecond,)
+    avgConTime = elapsedTime / settings.ITERATIONS
+    print "MySQLdb average connection time %s"%(avgConTime,)
+
+
+def oursql():
+    import oursql as db
+    startTime = timeit.default_timer()
+    for x in xrange(settings.ITERATIONS):
+        con = db.connect(host=settings.HOSTNAME,
+                         user=settings.USERNAME,
+                         db=settings.DATABASE,
+                         use_unicode=settings.USE_UNICODE)
+        con.close()
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "Oursql connected %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    consPerSecond = settings.ITERATIONS / elapsedTime
+    print "Oursql connected at a rate of %s/s"%(consPerSecond,)
+    avgConTime = elapsedTime / settings.ITERATIONS
+    print "Oursql average connection time %s"%(avgConTime,)
+
+
+def pymongo():
+    import pymongo
+    startTime = timeit.default_timer()
+    for x in xrange(settings.ITERATIONS):
+        con = pymongo.Connection(settings.HOSTNAME)
+        db = con[settings.DATABASE]
+        con.disconnect()
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "Pymongo connected %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    consPerSecond = settings.ITERATIONS / elapsedTime
+    print "Pymongo connected at a rate of %s/s"%(consPerSecond,)
+    avgConTime = elapsedTime / settings.ITERATIONS
+    print "Pymongo average connection time %s"%(avgConTime,)
+
+
+if __name__ == "__main__":
+    if "mysqldb" in sys.argv:
+        mysqldb()
+    if "oursql" in sys.argv:
+        oursql()
+    if "pymongo" in sys.argv:
+        pymongo()
+    if "pymysql" in sys.argv:
+        pymysql()

File benchmarks/insert.py

+"""
+This script does a bunch of really stupid inserts into
+the following table:
+
+create table counter (
+  id int not null auto_increment primary key, 
+  count int
+);
+
+"""
+
+import sys
+import timeit
+
+
+from txmysql import settings
+
+
+TRANSACTION = False
+EXECUTEMANY = True
+
+
+def pymysql():
+    import pymysql as db
+    startTime = timeit.default_timer()
+    con = db.connect(host=settings.HOSTNAME,
+                     user=settings.USERNAME,
+                     db=settings.DATABASE,
+                     passwd="")
+    with con:
+        cur = con.cursor()
+        if not EXECUTEMANY:
+            for x in xrange(settings.ITERATIONS):
+                if TRANSACTION:
+                    cur.execute("begin;")
+                cur.execute(
+                    "insert into counter set count = %s;", (x,)
+                    )
+                if TRANSACTION:
+                    cur.execute("commit;")
+        else:
+            values = [(x,) for x in xrange(settings.ITERATIONS)]
+            cur.executemany("insert into counter set count = %s;", values)
+                    
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "PyMySQL inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    insertsPerSecond = settings.ITERATIONS / elapsedTime
+    print "PyMySQL inserted at a rate of %s/s"%(insertsPerSecond,)
+    avgInsertTime = elapsedTime / settings.ITERATIONS
+    print "PyMySQL average insert time %s"%(avgInsertTime,)
+
+
+def mysqldb():
+    import MySQLdb as db
+    startTime = timeit.default_timer()
+    con = db.connect(host=settings.HOSTNAME,
+                     user=settings.USERNAME,
+                     db=settings.DATABASE,
+                     use_unicode=settings.USE_UNICODE)
+    with con:
+        cur = con.cursor()
+        if not EXECUTEMANY:
+            for x in xrange(settings.ITERATIONS):
+                if TRANSACTION:
+                    cur.execute("begin;")
+                cur.execute(
+                    "insert into counter set count = %s;", (x,)
+                    )
+                if TRANSACTION:
+                    cur.execute("commit;")
+        else:
+            values = [(x,) for x in xrange(settings.ITERATIONS)]
+            cur.executemany("insert into counter set count = %s;", values)
+                    
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "MySQLdb inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    insertsPerSecond = settings.ITERATIONS / elapsedTime
+    print "MySQLdb inserted at a rate of %s/s"%(insertsPerSecond,)
+    avgInsertTime = elapsedTime / settings.ITERATIONS
+    print "MySQLdb average insert time %s"%(avgInsertTime,)
+
+
+def oursql():
+    import oursql as db
+    startTime = timeit.default_timer()
+    con = db.connect(host=settings.HOSTNAME,
+                     user=settings.USERNAME,
+                     db=settings.DATABASE,
+                     use_unicode=settings.USE_UNICODE)
+    with con:
+        cur = con.cursor()
+        if not EXECUTEMANY:
+            for x in xrange(settings.ITERATIONS):
+                if TRANSACTION:
+                    cur.execute("begin;")
+                cur.execute(
+                    "insert into counter set count = ?;", (x,)
+                    )
+                if TRANSACTION:
+                    cur.execute("commit;")
+        else:
+            values = [(x,) for x in xrange(settings.ITERATIONS)]
+            cur.executemany("insert into counter set count = ?;", values)
+
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "Oursql inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    insertsPerSecond = settings.ITERATIONS / elapsedTime
+    print "Oursql inserted at a rate of %s/s"%(insertsPerSecond,)
+    avgInsertTime = elapsedTime / settings.ITERATIONS
+    print "Oursql average insert time %s"%(avgInsertTime,)
+
+
+def pymongo():
+    import pymongo
+    startTime = timeit.default_timer()
+    con = pymongo.Connection(settings.HOSTNAME)
+    db = con[settings.DATABASE]
+
+    collection = db.counter
+    if not EXECUTEMANY:
+        for x in xrange(settings.ITERATIONS):
+            counter = {"count": x}
+            collection.insert(counter)
+    else:
+        counters = [{"count":x} for x in xrange(settings.ITERATIONS)]
+        collection.insert(counters)
+        
+    con.disconnect()
+    endTime = timeit.default_timer()
+    elapsedTime = endTime - startTime
+    print "Pymongo inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
+    consPerSecond = settings.ITERATIONS / elapsedTime
+    print "Pymongo inserted at a rate of %s/s"%(consPerSecond,)
+    avgConTime = elapsedTime / settings.ITERATIONS
+    print "Pymongo average insert time %s"%(avgConTime,)
+
+
+
+if __name__=="__main__":
+    if "mysqldb" in sys.argv:
+        mysqldb()
+    if "oursql" in sys.argv:
+        oursql()
+    if "pymongo" in sys.argv:
+        pymongo()
+    if "pymysql" in sys.argv:
+        pymysql()

File twisted/plugins/txmysql_plugin.py

+from zope import interface
+
+from twisted import plugin
+from twisted.application import service
+from twisted.python import usage
+
+from txmysql import server, settings
+
+
+class Options(usage.Options):
+    optParameters = (
+        ('port', 'p', None, 'Port on which to listen.'),
+    )
+
+
+class TxmysqlServiceMaker(object):
+    interface.implements(plugin.IPlugin, service.IServiceMaker)
+    description = "Txmysql"
+    options = Options
+    tapname = 'txmysql'
+
+    def makeService(self, options):
+        """
+        Return an instance of txmysql.server.TxmysqlServer
+        """
+        port = settings.PORT
+
+        if options['port'] is not None:
+            port = int(options['port'])
+
+        return server.TxmysqlServer(port)
+
+
+serviceMaker = TxmysqlServiceMaker()

File txmysql/__init__.py

+# __init__.py 

File txmysql/protocol.py

+""" protocol.py
+
+------------------------------------------------------------------
+The MySQL Protocol consists of the following structure 'atoms':
+------------------------------------------------------------------
+==================================================================
+Elements (Data values sent and received)
+==================================================================
+
++++++++++++++++++++++++++++++++++++++++++++
+Null-Terminated String: "\0" 0x00 
++++++++++++++++++++++++++++++++++++++++++++
+
+++++++++++++++++++++++++++++++
+Length Coded Binary
+++++++++++++++++++++++++++++++
+
+Value of      # Of Bytse   Description
+First Byte    Following    
+-----------   ----------   -----------
+0-250         0            = value of first byte
+251           0            column value = NULL (only appropriate in
+                                                column data packet)
+252           2            = value of following 16-bit word
+253           3            = value of following 24-bit word
+254           8            = value of following 64-bit word
+
+All numbers are stored with least significant bit first and are unsigned.
+
++++++++++++++++++++++++++++++++++++++++++
+Length Coded String
++++++++++++++++++++++++++++++++++++++++++
+
+A length coded string is sent as a length coded binary followed by
+the data for that string.
+
+=====================================================
+The Packet Header
+=====================================================
+
+Bytes      Name
+-----      ----
+ 3         Packet Length
+ 1         Packet Number
+
+ Where packet length is simply the length in bytes.
+
+ Maxium packet length is 16MB. (2**(3(bytes)*8(bits)))
+
+ Where packet number is an ordering for the packets.  Each query
+ will start with packet 0 and increment until the result set
+ is received.
+
+Every packet will have one of these first.
+
+-----------------------------------------------------------------------
+A typical session:
+-----------------------------------------------------------------------
+
+ 1. The Handshake (Client connects):
+   A. Server to Client: Sends Handshake Initialization Packet
+   B. Client to Server: Client Authentication Packet
+   C. Server to Client: OK Packet or Error Packet
+ 2. Commands (every action the client wants the server to do):
+   A. Client Sends to Server: Command Packet
+   B. Server Sends to Client: OK Packet, Error Packet, or Result Set Packet
+
+==================================================
+Handshake Initialization Packet
+==================================================
+Bytes                        Name
+ -----                        ----
+ 1                            protocol_version
+ n (Null-Terminated String)   server_version
+ 4                            thread_id
+ 8                            scramble_buff
+ 1                            (filler) always 0x00
+ 2                            server_capabilities
+ 1                            server_language
+ 2                            server_status
+ 13                           (filler) always 0x00 ...
+ 13                           rest of scramble_buff (4.1)
+
+ protocol_version:    The server takes this from PROTOCOL_VERSION
+ in /include/mysql_version.h. Example value = 10.
+
+ server_version:      The server takes this from MYSQL_SERVER_VERSION
+ in /include/mysql_version.h. Example value = "4.1.1-alpha".
+
+ thread_number:       ID of the server thread for this connection.
+ 
+ scramble_buff:       The password mechanism uses this. The second part are the
+ last 13 bytes.
+ (See "Password functions" section elsewhere in this document.)
+ 
+ server_capabilities: CLIENT_XXX options. The possible flag values at time of
+ writing (taken from  include/mysql_com.h):
+  (SEE SERVER CAPABILITIES BELOW) 
+ server_language:     current server character set number
+
+ server_status:       SERVER_STATUS_xxx flags: e.g. SERVER_STATUS_AUTOCOMMIT
+"""
+# SERVER CAPABILITIES:
+CLIENT_LONG_PASSWORD      = 1 # /* new more secure passwords */
+CLIENT_FOUND_ROWS         = 2 # /* Found instead of affected rows */
+CLIENT_LONG_FLAG          = 4 # /* Get all column flags */
+CLIENT_CONNECT_WITH_DB    = 8 # /* One can specify db on connect */
+CLIENT_NO_SCHEMA          = 16 # /* Don't allow database.table.column */
+CLIENT_COMPRESS           = 32 # /* Can use compression protocol */
+CLIENT_ODBC               = 64 # /* Odbc client */
+CLIENT_LOCAL_FILES        = 128 # /* Can use LOAD DATA LOCAL */
+CLIENT_IGNORE_SPACE       = 256 # /* Ignore spaces before '(' */
+CLIENT_PROTOCOL_          = 41512 # /* New 4.1 protocol */
+CLIENT_INTERACTIVE        = 1024 # /* This is an interactive client */
+CLIENT_SSL                = 2048 # /* Switch to SSL after handshake */
+CLIENT_IGNORE_SIGPIPE     = 4096     # /* IGNORE sigpipes */
+CLIENT_TRANSACTIONS       = 8192 # /* Client knows about transactions */
+CLIENT_RESERVED           = 16384    # /* Old flag for 4.1 protocol  */
+CLIENT_SECURE_CONNECTION  = 32768   # /* New 4.1 authentication */
+CLIENT_MULTI_STATEMENTS   = 65536    # /* Enable/disable multi-stmt support */
+CLIENT_MULTI_RESULTS      = 131072   # /* Enable/disable multi-results */
+
+"""
+============================================================
+Client Authentication Packet
+============================================================
+
+From client to server during initial handshake
+
+Version 4.1
+ Bytes                       Name
+ -----                       -----
+ 4                           client_flags
+ 4                           max_packet_size
+ 1                           charset_number
+ 23                          (filler) always 0x00
+ n (Null-terminated string)  user
+ n (length coded binary)     scramble_buff (1+x bytes)
+ n (Null-terminated string)  databasename (optional)
+
+ client_flags:            CLIENT_xxx options. The list of possible flag
+ values is in the description of the Handshake
+ Initialisation Packet, for server_capabilities.
+ For some of the bits, the server passed "what
+ it's capable of". The client leaves some of the
+ bits on, adds others, and passes back to the server.
+ One important flag is: whether compression is desired.
+ Another interesting one is CLIENT_CONNECT_WITH_DB,
+ which shows the presence of the optional databasename.
+ 
+ max_packet_size:         the maximum number of bytes in a packet for the client
+ 
+ charset_number:          in the same domain as the server_language field that
+ the server passes in the Handshake Initialization packet.
+ 
+ user:                    identification
+ 
+ scramble_buff:           the password, after encrypting using the scramble_buff
+ contents passed by the server (see "Password functions"
+ section elsewhere in this document)
+ if length is zero, no password was given
+ 
+ databasename:            name of schema to use initially
+
+================================================================
+OK Packet
+================================================================
+
+Version 4.1+
+
+ Bytes                     Name
+ -----                     ----
+ 1 (Length Coded Binary)   field_count, always = 0
+ 1-9 (Length Coded Binary) affected_rows
+ 1-9 (Length Coded Binary) insert_id
+ 2                         server_status
+ 2                         warning_count
+ n (until end of packet)   message
+
+ field_count:     always = 0
+
+ affected_rows:   = number of rows affected by INSERT/UPDATE/DELETE
+
+ insert_id:       If the statement generated any AUTO_INCREMENT number,
+ it is returned here. Otherwise this field contains 0.
+ Note: when using for example a multiple row INSERT the
+ insert_id will be from the first row inserted, not from
+ last.
+ 
+ server_status:   = The client can use this to check if the
+ command was inside a transaction.
+ 
+ warning_count:   number of warnings
+ 
+ message:         For example, after a multi-line INSERT, message might be
+ "Records: 3 Duplicates: 0 Warnings: 0"
+ 
+ The message field is optional.
+ Alternative terms: OK Packet is also known as "okay packet" or "ok packet" or "OK-Packet". field_count is also known as "number of rows" or "marker for ok packet". message is also known as "Messagetext". OK Packets (and result set packets) are also called "Result packets".
+
+================================================================
+Error Packet
+================================================================
+"""
+
+
+class MysqlProxyProtocol(object):
+    """For proxying traffic to a real mysql server.
+
+    Allows transforms and modifications of result sets
+    """
+
+
+class MysqlProxyFactory(object):
+    protocol = MysqlProxyProtocol
+
+    def __init__(self, hostname, port):
+        self.hostname = hostname
+        self.port = port
+
+
+class MysqlProtocol(object):
+    def __init__(self):
+        self.connected = False
+
+    def connect(self):
+        pass
+
+    def _cbConnected(self, result):
+        pass
+
+    def _ebConnectionFailed(self, error):
+        pass
+
+    
+class MysqlProtocolFactory(object):
+    protocol = MysqlProtocol
+
+    def __init__(self, username,
+                 password,
+                 database=None,
+                 character_set="utf-8",
+                 flags=None,
+                 max_packet_size=None):
+        self.username = username
+        self.password = password
+        self.database = database
+        self.characterSet = character_set
+        self.flags = flags
+        self.maxPacketSize = max_packet_size

File txmysql/resource.py

+""" resource.py
+"""

File txmysql/server.py

+#-*- test-case-name: txmysql.test.test_server -*-
+"""
+Defines the Txmysql Server
+
+"""
+from twisted.application import internet, service
+from twisted.internet import defer, reactor
+from twisted.python import log
+from twisted.web import server
+
+from txmysql.web import root
+
+
+class TxmysqlServer(service.MultiService):
+    def __init__(self, port):
+        service.MultiService.__init__(self)
+        webServerFactory = server.Site(root.Root())
+        webServer = internet.TCPServer(port, webServerFactory)
+        webServer.setName("Txmysql")
+        webServer.setServiceParent(self)
+
+    def _cbStarted(self, result):
+        service.MultiService.startService(self)        
+        return result
+
+    def _ebError(self, failure):
+        log.err("failure starting service:", failure)
+        return failure
+
+    def startupHook(self, deferred):
+        """Include additional startup steps here
+
+        """
+        deferred.callback(True)
+
+    def startService(self):
+        d = defer.Deferred()
+        d.addCallback(self._cbStarted)
+        d.addErrback(self._ebError)
+        reactor.callWhenRunning(self.startupHook, d)
+        return d
+
+    def stopService(self):
+        log.msg("Shutting down service:")
+        return service.MultiService.stopService(self)

File txmysql/settings.py

+"""Settings for Txmysql
+
+"""
+
+from twisted.python import filepath
+
+
+TXMYSQL_ROOT = filepath.FilePath(__file__).parent().parent()
+    
+HOSTNAME = "localhost"
+PORT = 7777
+
+# BENCHMARKS Settings:
+USERNAME = "test"
+DATABASE = "test"
+ITERATIONS = 1000
+USE_UNICODE = True
+

File txmysql/static/root.html

+<html>
+  <head>
+    <title>Txmysql Test Page</title>
+  </head>
+  <body>
+    <h1>Txmysql Test Page</h1>
+  </body>
+</html>

File txmysql/test/__init__.py

+# __init__.py

File txmysql/test/test_server.py

+from twisted.python import failure, log
+from twisted.trial import unittest
+
+from txmysql import server
+
+class TestTxmysqlServer(unittest.TestCase):
+    def setUp(self):
+        portNumber = 6666
+        self.server = server.TxmysqlServer(portNumber)
+        
+    def test_serverName(self):
+        self.assertEqual(None, self.server.name
+                         )
+
+class TestTxmysqlServerStart(unittest.TestCase):
+    def setUp(self):
+        portNumber = 6666
+        self.server = server.TxmysqlServer(portNumber)
+        def _fakeServiceCallback(result):
+            return result
+        self.server._cbStarted = _fakeServiceCallback
+
+    def tearDown(self):
+        d = self.server.stopService()
+        return d
+
+    def _startSuccess(self, result):
+        log.msg("test_server._startSuccess: %s"%(result,))
+        self.assertEqual(True, result, "Expect server start to return true result")
+        return result
+
+    def _startShouldNotFail(self, failure):
+        print failure
+        self.fail("start should succeed!")
+        return failure
+
+    def _startShouldNotSucceed(self, result):
+        self.fail("Start shouldn't succeed!")
+
+    def _startShouldFail(self, failure):
+        self.assertTrue(failure is not None)
+
+    def test_startService(self):
+        d = self.server.startService()
+        d.addCallbacks(callback=self._startSuccess,
+                       errback=self._startShouldNotFail)
+        return d
+
+    def test_startServiceFails(self):
+        def fakeStart(d):
+            d.errback(failure.Failure(Exception("Some exception occured while starting the service")))
+        self.server.startupHook = fakeStart
+        d = self.server.startService()
+        d.addCallbacks(callback=self._startShouldNotSucceed,
+                       errback=self._startShouldFail)
+        return d
+        
+

File txmysql/test/test_web.py

+from twisted.trial import unittest
+from twisted.web import server
+from twisted.web.test import test_web
+
+from txmysql.web import root
+
+class TestRootResource(unittest.TestCase):
+    def setUp(self):
+        self.r = root.Root()
+
+    def test_create(self):
+        r = root.Root()
+        self.assertTrue(r is not None)
+
+    def test_renderGet(self):
+        """should get the login page"""
+        result = self.r.render_GET(test_web.DummyRequest(['']))
+        self.assertTrue(result == server.NOT_DONE_YET)
+        return self.r.d
+        

File txmysql/web/__init__.py

+# __init__.py

File txmysql/web/root.py

+#-*- test-case-name: txmysql.test.test_web -*-
+import os
+
+from twisted.python import log
+from twisted.internet import defer, reactor
+from twisted.web import http, resource, server, static
+
+from txmysql import settings
+
+
+class Root(resource.Resource):
+    def __init__(self):
+        resource.Resource.__init__(self)
+        self.putChild("static", static.File("txmysql/static"))
+
+    def _failed(self, reason):
+        log.err(reason)
+        return http.Response(
+            code=500,
+            headers=None,
+            stream=reason.getErrorMessage()
+        )
+
+    def _got(self, result, request):
+        request.setResponseCode(http.OK)
+        request.write(result)
+        request.finish()
+
+    def getStaticFile(self, deferred):
+        filepath = os.path.join(settings.TXMYSQL_ROOT.path, "txmysql/static/root.html")
+        template = open(filepath, "r")
+        contents = template.read()
+        deferred.callback(contents)
+
+    def render_GET(self, request):
+        self.d = defer.Deferred()
+        self.d.addCallback(self._got, request)
+        self.d.addErrback(self._failed)
+        reactor.callLater(0.1, self.getStaticFile, self.d)
+        return server.NOT_DONE_YET
+
+    def getChild(self, path, request):
+        if path == "":
+            return self
+        return resource.Resource.getChild(self, path, request)