def __init__(self, host, port, user='******', password='', database='information_schema'):
""" Initialize the RandomAggregatorPool with connection
information for an aggregator in a MemSQL Distributed System.
All aggregator connections will share the same user/password/database.
"""
self.logger = logging.getLogger('memsql.random_aggregator_pool')
self._pool = ConnectionPool()
self._refresh_aggregator_list = memoize(30)(self._update_aggregator_list)
self._lock = threading.RLock()
self._primary_aggregator = (host, port)
self._user = user
self._password = password
self._database = database
self._aggregators = []
self._aggregator = None
self._master_aggregator = None
def pool():
from memsql.common.connection_pool import ConnectionPool
return ConnectionPool()
class RandomAggregatorPool(object):
""" A automatic fail-over connection pool.
One layer above the connection pool. It's purpose is to choose a
random aggregator and use it while it is available. If not it fails
over to another aggregator. The class maintains the list of
aggregators by periodically calling `SHOW AGGREGATORS`.
Note: If you point this class at a MemSQL Singlebox instance, it
will still work, but all connections will just be made to the
singlebox node.
"""
def __init__(self, host, port, user='******', password='', database='information_schema'):
""" Initialize the RandomAggregatorPool with connection
information for an aggregator in a MemSQL Distributed System.
All aggregator connections will share the same user/password/database.
"""
self.logger = logging.getLogger('memsql.random_aggregator_pool')
self._pool = ConnectionPool()
self._refresh_aggregator_list = memoize(30)(self._update_aggregator_list)
self._lock = threading.RLock()
self._primary_aggregator = (host, port)
self._user = user
self._password = password
self._database = database
self._aggregators = []
self._aggregator = None
self._master_aggregator = None
def connect(self):
""" Returns an aggregator connection, and periodically updates the aggregator list. """
conn = self._connect()
self._refresh_aggregator_list(conn)
return conn
def connect_master(self):
if self._master_aggregator is None:
with self._pool_connect(self._primary_aggregator) as conn:
self._update_aggregator_list(conn)
conn.expire()
try:
return self._pool_connect(self._master_aggregator)
except PoolConnectionException:
return None
def close(self):
self._pool.close()
def _pool_connect(self, agg):
""" `agg` should be (host, port)
Returns a live connection from the connection pool
"""
return self._pool.connect(agg[0], agg[1], self._user, self._password, self._database)
def _connect(self):
""" Returns an aggregator connection. """
with self._lock:
if self._aggregator:
try:
return self._pool_connect(self._aggregator)
except PoolConnectionException:
self._aggregator = None
if not len(self._aggregators):
with self._pool_connect(self._primary_aggregator) as conn:
self._update_aggregator_list(conn)
conn.expire()
random.shuffle(self._aggregators)
last_exception = None
for aggregator in self._aggregators:
self.logger.debug('Attempting connection with %s:%s' % (aggregator[0], aggregator[1]))
try:
conn = self._pool_connect(aggregator)
# connection successful!
self._aggregator = aggregator
return conn
except PoolConnectionException as e:
# connection error
last_exception = e
else:
# bad news bears... try again later
self._aggregator = None
self._aggregators = []
raise last_exception
def _update_aggregator_list(self, conn):
try:
rows = conn.query('SHOW AGGREGATORS')
except DatabaseError as e:
if e.args[0] == errorcodes.ER_DISTRIBUTED_NOT_AGGREGATOR:
# connected to memsql singlebox
self._aggregators = [self._primary_aggregator]
self._master_aggregator = self._primary_aggregator
else:
raise
else:
with self._lock:
self._aggregators = []
for row in rows:
if row.Host == '127.0.0.1':
# this is the aggregator we are connecting to
row['Host'] = conn.connection_info()[0]
if int(row.Master_Aggregator) == 1:
self._master_aggregator = (row.Host, row.Port)
self._aggregators.append((row.Host, row.Port))
assert len(self._aggregators) > 0, "Failed to retrieve a list of aggregators"
self.logger.debug('Aggregator list is updated to %s. Current aggregator is %s.' % (self._aggregators, self._aggregator))
def __init__(self):
self._pool = ConnectionPool()
self._db_args = None
self._tables = {}
def __init__(self):
self._pool = ConnectionPool()
self._db_args = None
self._tables = {}
class SQLUtility(object):
def __init__(self):
self._pool = ConnectionPool()
self._db_args = None
self._tables = {}
###############################
# Public Interface
def connect(self,
host='127.0.0.1',
port=3306,
user='******',
password='',
database=None):
""" Connect to the database specified """
if database is None:
raise exceptions.RequiresDatabase()
self._db_args = {
'host': host,
'port': port,
'user': user,
'password': password,
'database': database
}
with self._db_conn() as conn:
conn.query('SELECT 1')
return self
def disconnect(self):
self._pool.close()
def setup(self):
""" Initialize the required tables in the database """
with self._db_conn() as conn:
for table_defn in self._tables.values():
conn.execute(table_defn)
return self
def destroy(self):
""" Destroy the SQLStepQueue tables in the database """
with self._db_conn() as conn:
for table_name in self._tables:
conn.execute('DROP TABLE IF EXISTS %s' % table_name)
return self
def ready(self):
""" Returns True if the tables have been setup, False otherwise """
with self._db_conn() as conn:
tables = [
row.t for row in conn.query(
'''
SELECT table_name AS t FROM information_schema.tables
WHERE table_schema=%s
''', self._db_args['database'])
]
return all([table_name in tables for table_name in self._tables])
###############################
# Protected Interface
def _define_table(self, table_name, table_definition):
self._tables[table_name] = table_definition
def _db_conn(self):
if self._db_args is None:
raise exceptions.NotConnected()
return self._pool.connect(**self._db_args)
class SQLUtility(object):
def __init__(self):
self._pool = ConnectionPool()
self._db_args = None
self._tables = {}
###############################
# Public Interface
def connect(self, host="127.0.0.1", port=3306, user="******", password="", database=None):
""" Connect to the database specified """
if database is None:
raise exceptions.RequiresDatabase()
self._db_args = {"host": host, "port": port, "user": user, "password": password, "database": database}
with self._db_conn() as conn:
conn.query("SELECT 1")
return self
def disconnect(self):
self._pool.close()
def setup(self):
""" Initialize the required tables in the database """
with self._db_conn() as conn:
for table_defn in self._tables.values():
conn.execute(table_defn)
return self
def destroy(self):
""" Destroy the SQLStepQueue tables in the database """
with self._db_conn() as conn:
for table_name in self._tables:
conn.execute("DROP TABLE IF EXISTS %s" % table_name)
return self
def ready(self):
""" Returns True if the tables have been setup, False otherwise """
with self._db_conn() as conn:
tables = [
row.t
for row in conn.query(
"""
SELECT table_name AS t FROM information_schema.tables
WHERE table_schema=%s
""",
self._db_args["database"],
)
]
return all([table_name in tables for table_name in self._tables])
###############################
# Protected Interface
def _define_table(self, table_name, table_definition):
self._tables[table_name] = table_definition
def _db_conn(self):
if self._db_args is None:
raise exceptions.NotConnected()
return self._pool.connect(**self._db_args)
def __init__(self):
self.pool = ConnectionPool()
class PooPooApi(object):
def __init__(self):
self.pool = ConnectionPool()
def connect(self):
host = os.getenv("DBHOST", "127.1")
return self.pool.connect(host, 3306, "root", "", "poopoobanana")
def AddUser(self, name):
try:
with self.connect() as conn:
return conn.execute("""insert into users(user_name) values(%s)""", name)
except Exception as e:
raise Exception("user exists (%s)" % str(e))
def Login(self, user, password):
user_id = self.GetUserId(user)
if user_id is None:
return self.AddUser(user)
return user_id
def GetUserId(self, name):
with self.connect() as conn:
rows = conn.query("""select user_id from users where user_name = %s""", name)
if len(rows) == 0:
return None
return int(rows[0]["user_id"])
def AddAd(self, name, cost, ad_data):
try:
with self.connect() as conn:
return conn.execute("""insert into ads(ad_name, ad_cost, ad_data) values (%s,%s,%s)""", name, str(cost), simplejson.dumps(ad_data))
except Exception as e:
raise Exception("ad exists (%s)" % str(e))
def GetAd(self, name):
with self.connect() as conn:
rows = conn.query("""select * from ads where ad_name = %s""", name)
if len(rows) == 0:
return None
result = simplejson.loads(rows[0]["ad_data"])
result["ad_id"] = int(rows[0]["ad_id"])
result["ad_cost"] = int(rows[0]["ad_cost"])
return result
def AddEvent(self, name, user_id, data):
if "points" in data:
points = str(data["points"])
del data["points"]
else:
points = "0"
with self.connect() as conn:
conn.execute("""insert into events(event_name, user_id, event_points, event_data) values (%s, %s, %s, %s)""", name, user_id, points, simplejson.dumps(data))
def GetUserPoints(self, ads_only, user_id=None, limit=None):
q = """select users.user_name, users.user_id, %(total)s ifnull(sum(ads.ad_cost), 0) as points
from users %(events_joins)s
left join ad_trophy_case on users.user_id = ad_trophy_case.user_id
left join ads on ad_trophy_case.ad_id = ads.ad_id """
if not ads_only:
dta = {"total": "ifnull(sum(events.event_points), 0) -",
"events_joins": "left join events on users.user_id = events.user_id"}
else:
dta = {"total": "",
"events_joins": ""}
q = q % dta
if user_id is None:
if ads_only:
q += "where ad_trophy_case.ad_watched = 1 "
q += "group by users.user_id order by points desc "
if limit is not None:
q += "limit %d" % limit
else:
q += "where users.user_id = %d" % user_id
with self.connect() as conn:
rows = conn.query(q)
if user_id is not None:
return rows[0]
return rows
def Purchase(self, ad_id, user_id):
pts = int(self.GetUserPoints(False, user_id)["points"])
with self.connect() as conn:
cost = int(conn.query("select ad_cost from ads where ad_id = %d" % ad_id)[0]["ad_cost"])
if pts < cost:
return False
else:
try:
conn.query("insert into ad_trophy_case(user_id,ad_id,ad_watched) values (%d,%d,0)" % (user_id, ad_id))
except Exception as e:
raise Exception("already purchased (%s)" % str(e))
return True
def Watch(self, ad_id, user_id):
with self.connect() as conn:
conn.execute("update ad_trophy_case set ad_watched = 1 where ad_id = %d and user_id = %d" % (ad_id, user_id))
def Library(self, user_id):
q = """select * from
ad_trophy_case join ads
on ad_trophy_case.ad_id = ads.ad_id
where ad_trophy_case.user_id = %d""" % user_id
with self.connect() as conn:
rows = conn.query(q)
result = []
for r in rows:
result.append(simplejson.loads(r["ad_data"]))
result[-1]["ad_name"] = r["ad_name"]
result[-1]["ad_id"] = int(r["ad_id"])
result[-1]["ad_cost"] = int(r["ad_cost"])
result[-1]["watched"] = r["ad_watched"] == "1"
return result
def __init__(self, node_row):
self.update_from_node(node_row)
self.alias = None
self._pool = ConnectionPool()
class Node(object):
def __init__(self, node_row):
self.update_from_node(node_row)
self.alias = None
self._pool = ConnectionPool()
def update_from_node(self, node):
self.id = node.id
self.host = node.host
self.port = node.port
def update_alias(self, connection_pool, alias):
self.alias = alias
try:
conn = connection_pool.connect_master()
if conn:
conn.execute('''
INSERT INTO node_alias (node_id, alias)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE alias=VALUES(alias)
''', self.id, alias)
finally:
if conn:
conn.close()
def connect(self):
return self._pool.connect(
host=self.host,
port=self.port,
user="******",
password="",
database="information_schema")
def status(self):
with self.connect() as conn:
rows = conn.query('SHOW STATUS EXTENDED')
for row in rows:
name = row.Variable_name
try:
value = self._parse_value(row.Value)
except ValueError:
continue
yield (name, value)
STATUS_CONSTS = re.compile(r"ms|MB|KB", re.I)
def _parse_value(self, value):
if self.STATUS_CONSTS.search(value):
return float(value.split(" ")[0])
return float(value)
def variables(self):
with self.connect() as conn:
rows = conn.query('SHOW VARIABLES')
for row in rows:
yield (row.Variable_name, row.Value)
for name, path in self.directories().iteritems():
yield (name, path)
def directories(self):
with self.connect() as conn:
rows = conn.query("SHOW STATUS EXTENDED LIKE '%_directory'")
return dict([(row.Variable_name, row.Value) for row in rows])
