OrganicPanda/test.py

## test.py
import cherrypy
import sqlalchemy
from sqlalchemy import Table, Column, ForeignKey, MetaData, Integer, String
from sqlalchemy.orm import scoped_session, sessionmaker, mapper, relationship
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm.util import object_mapper

# The base class from which all entities will extend
class BaseEntity(object):
	def __repr__(self):
		# Print all properties
		return '[%s]: %s' % (self.__class__.__name__, ','.join(['%s = %s' % (prop.key, getattr(self, prop.key)) for prop in object_mapper(self).iterate_properties if isinstance(prop, ColumnProperty)]))

# Thread-safe session that gets bound on every request (see before_request_body)
Session = scoped_session(sessionmaker(autoflush = True, autocommit = False))

# Global dict of databases. Stores engine, meta, tables and extended model classes
# This will be read-only after reflection and model creation (below)
databases = {
	'one' : {
		'engine' : sqlalchemy.create_engine('mysql://test:test@localhost/test_one?charset=utf8', encoding = 'utf-8'),
		'metadata' : MetaData(),
		'model' : {},
		'table' : {}
	},
	'two' : {
		'engine' : sqlalchemy.create_engine('mysql://test:test@localhost/test_two?charset=utf8', encoding = 'utf-8'),
		'metadata' : MetaData(),
		'model' : {},
		'table' : {}
	}
}

# Use reflection on all databases
databases['one']['metadata'].reflect(bind = databases['one']['engine'])
databases['two']['metadata'].reflect(bind = databases['two']['engine'])

# Extend BaseEntity for use in DB 1 (BaseEntity & ChildEntity)
databases['one']['model']['BaseEntity'] = type('BaseEntity', (BaseEntity, ), {})
databases['one']['table']['base_entity'] = Table('base_entity', databases['one']['metadata'])
databases['one']['model']['ChildEntity'] = type('ChildEntity', (databases['one']['model']['BaseEntity'], ), {})
databases['one']['table']['child_entity'] = Table('child_entity', databases['one']['metadata'])

# Extend BaseEntity for use in DB 2 (BaseEntity & OtherChildEntity)
databases['two']['model']['BaseEntity'] = type('BaseEntity', (BaseEntity, ), {})
databases['two']['table']['base_entity'] = Table('base_entity', databases['two']['metadata'])
databases['two']['model']['OtherChildEntity'] = type('OtherChildEntity', (databases['two']['model']['BaseEntity'], ), {})
databases['two']['table']['other_child_entity'] = Table('other_child_entity', databases['two']['metadata'])

# Map BaseEntity & ChildEntity in DB 1
mapper(databases['one']['model']['BaseEntity'], databases['one']['table']['base_entity'])
mapper(databases['one']['model']['ChildEntity'], databases['one']['table']['child_entity'])

# Map BaseEntity & OtherChildEntity in DB 2
mapper(databases['two']['model']['BaseEntity'], databases['two']['table']['base_entity'])
mapper(databases['two']['model']['OtherChildEntity'], databases['two']['table']['other_child_entity'])

# Before each request (but after the session tool)
def before_request_body():
	if cherrypy.session.get('logged_in', None) is True:
		# Configure the DB session for this thread to point to the correct DB
		Session.configure(bind = databases[cherrypy.session.get('database')]['engine'])

# After each request
def on_end_request():
	# Un-configure the session
	Session.remove()

# Provide easy access to the correct database
def get_database():
	return databases[cherrypy.session.get('database')]

# Test class to see if this setup can reproduce our error
class Test:

	# Simple template that uses JS AJAX requests to:
	# - Log in to a database
	# - Recurringly send requests that require a single DB hit
	template = """<html><head><title>Test</title>
	<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
	<script type="text/javascript">
		$(document).ready(function() {
			var testFunction = function() {
				$.ajax({
					type: 'POST',
					url: '/get_%s',
					success: function(data) { console.log('Success! ', data) },
					error: function(jqXHR, textStatus, errorThrown) { console.error('Error! ', jqXHR, textStatus, errorThrown); }
				});
				window.setTimeout(testFunction, Math.floor(Math.random() * 200));
			}
			$.ajax({ type: 'GET', url: '/login_db_%s',
				success: function() { window.setTimeout(testFunction, Math.floor(Math.random() * 200)); },
			});
		});
	</script></head><body>Test. Check your browser console.</body></html>"""

	# Start point of the test. Go to this URL in one browser
	@cherrypy.expose
	def test_db_one(self):
		return self.template % ('child_entity', 'one')

	# Start point of the test. Go to this URL in a different browser
	# (To avoid session conflicts)
	@cherrypy.expose
	def test_db_two(self):
		return self.template % ('other_child_entity', 'two')

	# The first AJAX calll will simple register a database in the users session
	@cherrypy.expose
	def login_db_one(self):
		cherrypy.session['database'] = 'one'
		cherrypy.session['logged_in'] = True

	# The first AJAX calll will simple register a database in the users session
	@cherrypy.expose
	def login_db_two(self):
		cherrypy.session['database'] = 'two'
		cherrypy.session['logged_in'] = True

	# Simple DB query to test the setup for DB 1
	@cherrypy.expose
	def get_child_entity(self):
		return "Hello ", str(Session().query(get_database()['model']['ChildEntity']).first())

	# Simple DB query to test the setup for DB 2
	@cherrypy.expose
	def get_other_child_entity(self):
		return "Hello ", str(Session().query(get_database()['model']['OtherChildEntity']).first())

# Execute this code once to start the CherryPy server
if __name__ == '__main__':

	# Configure our test app
	root = Test()
	application = cherrypy.tree.mount(root, '/')
	cherrypy.config.update({ 'global': {
		'tools.dbsession_open.on' : True,
		'tools.dbsession_close.on' : True,
		'tools.sessions.on' : True,
		'tools.sessions.storage_type' : "ram",
		'tools.sessions.timeout' : 60
	}})

	# Add the hooks to cherrypy
	# Run after the session tool (priority = 100)
	cherrypy.tools.dbsession_open = cherrypy.Tool('before_request_body', before_request_body, priority = 100)
	cherrypy.tools.dbsession_close = cherrypy.Tool('on_end_request', on_end_request)

	# Start the web server
	cherrypy.engine.start()
	cherrypy.engine.block()
	import cherrypy
	import sqlalchemy
	from sqlalchemy import Table, Column, ForeignKey, MetaData, Integer, String
	from sqlalchemy.orm import scoped_session, sessionmaker, mapper, relationship
	from sqlalchemy.orm.properties import ColumnProperty
	from sqlalchemy.orm.util import object_mapper

	# The base class from which all entities will extend
	class BaseEntity(object):
	def __repr__(self):
	# Print all properties
	return '[%s]: %s' % (self.__class__.__name__, ','.join(['%s = %s' % (prop.key, getattr(self, prop.key)) for prop in object_mapper(self).iterate_properties if isinstance(prop, ColumnProperty)]))

	# Thread-safe session that gets bound on every request (see before_request_body)
	Session = scoped_session(sessionmaker(autoflush = True, autocommit = False))

	# Global dict of databases. Stores engine, meta, tables and extended model classes
	# This will be read-only after reflection and model creation (below)
	databases = {
	'one' : {
	'engine' : sqlalchemy.create_engine('mysql://test:test@localhost/test_one?charset=utf8', encoding = 'utf-8'),
	'metadata' : MetaData(),
	'model' : {},
	'table' : {}
	},
	'two' : {
	'engine' : sqlalchemy.create_engine('mysql://test:test@localhost/test_two?charset=utf8', encoding = 'utf-8'),
	'metadata' : MetaData(),
	'model' : {},
	'table' : {}
	}
	}

	# Use reflection on all databases
	databases['one']['metadata'].reflect(bind = databases['one']['engine'])
	databases['two']['metadata'].reflect(bind = databases['two']['engine'])

	# Extend BaseEntity for use in DB 1 (BaseEntity & ChildEntity)
	databases['one']['model']['BaseEntity'] = type('BaseEntity', (BaseEntity, ), {})
	databases['one']['table']['base_entity'] = Table('base_entity', databases['one']['metadata'])
	databases['one']['model']['ChildEntity'] = type('ChildEntity', (databases['one']['model']['BaseEntity'], ), {})
	databases['one']['table']['child_entity'] = Table('child_entity', databases['one']['metadata'])

	# Extend BaseEntity for use in DB 2 (BaseEntity & OtherChildEntity)
	databases['two']['model']['BaseEntity'] = type('BaseEntity', (BaseEntity, ), {})
	databases['two']['table']['base_entity'] = Table('base_entity', databases['two']['metadata'])
	databases['two']['model']['OtherChildEntity'] = type('OtherChildEntity', (databases['two']['model']['BaseEntity'], ), {})
	databases['two']['table']['other_child_entity'] = Table('other_child_entity', databases['two']['metadata'])

	# Map BaseEntity & ChildEntity in DB 1
	mapper(databases['one']['model']['BaseEntity'], databases['one']['table']['base_entity'])
	mapper(databases['one']['model']['ChildEntity'], databases['one']['table']['child_entity'])

	# Map BaseEntity & OtherChildEntity in DB 2
	mapper(databases['two']['model']['BaseEntity'], databases['two']['table']['base_entity'])
	mapper(databases['two']['model']['OtherChildEntity'], databases['two']['table']['other_child_entity'])

	# Before each request (but after the session tool)
	def before_request_body():
	if cherrypy.session.get('logged_in', None) is True:
	# Configure the DB session for this thread to point to the correct DB
	Session.configure(bind = databases[cherrypy.session.get('database')]['engine'])

	# After each request
	def on_end_request():
	# Un-configure the session
	Session.remove()

	# Provide easy access to the correct database
	def get_database():
	return databases[cherrypy.session.get('database')]

	# Test class to see if this setup can reproduce our error
	class Test:

	# Simple template that uses JS AJAX requests to:
	# - Log in to a database
	# - Recurringly send requests that require a single DB hit
	template = """<html><head><title>Test</title>
	<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
	<script type="text/javascript">
	$(document).ready(function() {
	var testFunction = function() {
	$.ajax({
	type: 'POST',
	url: '/get_%s',
	success: function(data) { console.log('Success! ', data) },
	error: function(jqXHR, textStatus, errorThrown) { console.error('Error! ', jqXHR, textStatus, errorThrown); }
	});
	window.setTimeout(testFunction, Math.floor(Math.random() * 200));
	}
	$.ajax({ type: 'GET', url: '/login_db_%s',
	success: function() { window.setTimeout(testFunction, Math.floor(Math.random() * 200)); },
	});
	});
	</script></head><body>Test. Check your browser console.</body></html>"""

	# Start point of the test. Go to this URL in one browser
	@cherrypy.expose
	def test_db_one(self):
	return self.template % ('child_entity', 'one')

	# Start point of the test. Go to this URL in a different browser
	# (To avoid session conflicts)
	@cherrypy.expose
	def test_db_two(self):
	return self.template % ('other_child_entity', 'two')

	# The first AJAX calll will simple register a database in the users session
	@cherrypy.expose
	def login_db_one(self):
	cherrypy.session['database'] = 'one'
	cherrypy.session['logged_in'] = True

	# The first AJAX calll will simple register a database in the users session
	@cherrypy.expose
	def login_db_two(self):
	cherrypy.session['database'] = 'two'
	cherrypy.session['logged_in'] = True

	# Simple DB query to test the setup for DB 1
	@cherrypy.expose
	def get_child_entity(self):
	return "Hello ", str(Session().query(get_database()['model']['ChildEntity']).first())

	# Simple DB query to test the setup for DB 2
	@cherrypy.expose
	def get_other_child_entity(self):
	return "Hello ", str(Session().query(get_database()['model']['OtherChildEntity']).first())

	# Execute this code once to start the CherryPy server
	if __name__ == '__main__':

	# Configure our test app
	root = Test()
	application = cherrypy.tree.mount(root, '/')
	cherrypy.config.update({ 'global': {
	'tools.dbsession_open.on' : True,
	'tools.dbsession_close.on' : True,
	'tools.sessions.on' : True,
	'tools.sessions.storage_type' : "ram",
	'tools.sessions.timeout' : 60
	}})

	# Add the hooks to cherrypy
	# Run after the session tool (priority = 100)
	cherrypy.tools.dbsession_open = cherrypy.Tool('before_request_body', before_request_body, priority = 100)
	cherrypy.tools.dbsession_close = cherrypy.Tool('on_end_request', on_end_request)

	# Start the web server
	cherrypy.engine.start()
	cherrypy.engine.block()