def new_for_user(cls, user_id):
str_user_id = str(user_id)
shard_id = MySQL.get_shard_id_for_string(str_user_id)
id = MySQL.next_id(shard_id)
secret = RandomToken.build(16)
return cls(id, str_user_id, secret, user_id, True)
def new_for_user(cls, user_id):
str_user_id = str(user_id)
shard_id = MySQL.get_shard_id_for_string(str_user_id)
id = MySQL.next_id(shard_id)
secret = RandomToken.build(16)
return cls(id, str_user_id, secret, user_id, True)
def run():
id = MySQL.next_id()
shard = MySQL.get(id)
result = shard.query("SELECT 2")
pprint(result)
result2 = shard.multi_query("SELECT 1 ",[(),(),(),()]);
pprint(result2)
def save(self, session):
if session.is_dirty:
session_dict = self._session_to_row(session)
session_query = (
"INSERT INTO sessions VALUES("
"%(id)s, %(user_id)s, %(user_agent_id)s, %(auth_id)s, %(created_ts)s,"
" %(modified_ts)s, %(log_out_ts)s, NULL, NULL, %(flags)s, %(token)s"
") ON DUPLICATE KEY UPDATE"
" user_agent_id=VALUES(user_agent_id), modified_ts=VALUES(modified_ts),"
" log_out_ts=VALUES(log_out_ts), flags=VALUES(flags)")
MySQL.get(session.id).query(session_query, session_dict)
session.update_stored_state()
def save(self, session):
if session.is_dirty:
session_dict = self._session_to_row(session)
session_query = (
"INSERT INTO sessions VALUES("
"%(id)s, %(user_id)s, %(user_agent_id)s, %(auth_id)s, %(created_ts)s,"
" %(modified_ts)s, %(log_out_ts)s, NULL, NULL, %(flags)s, %(token)s"
") ON DUPLICATE KEY UPDATE"
" user_agent_id=VALUES(user_agent_id), modified_ts=VALUES(modified_ts),"
" log_out_ts=VALUES(log_out_ts), flags=VALUES(flags)"
)
MySQL.get(session.id).query(session_query, session_dict)
session.update_stored_state()
def new_session(self, auth, user_agent_string, log_out_ts=None, session_flags=None):
user = auth.user
new_session_id = MySQL.next_id(Id(user.id).get_shard_id())
try:
user_agent = self._get_user_agent_by_string(user_agent_string)
except UserAgentNotFoundException:
shard_id = MySQL.get_shard_id_for_string(user_agent_string)
id = MySQL.next_id(shard_id)
user_agent = UserAgent(id, user_agent_string)
self._save_user_agent(user_agent)
return Session(new_session_id, user, user_agent, auth, log_out_ts=log_out_ts, flags=session_flags)
def _get_user_agent_by_string(self, user_agent_string):
shard_id = MySQL.get_shard_id_for_string(user_agent_string)
hash_ = UserAgent.generate_hash(user_agent_string)
rows = MySQL.get_by_shard_id(shard_id).query("SELECT * FROM user_agents WHERE user_agent_hash = %s", (hash_,))
if not len(rows):
raise UserAgentNotFoundException()
row = rows[0]
if row['id'] not in self._user_agents:
self._user_agents[row['id']] = UserAgent(row['id'], row['user_agent_string'].decode("utf-8"))
return self._user_agents[row['id']]
def _get(self, table_name, column_list, value_list, shard_by = None, order_by = None, count = None, offset = None):
sql = (
"SELECT * FROM " + table_name + " WHERE "
+ " AND ".join(
column_name+"=%s" for column_name in column_list
))
if order_by:
if offset:
sql += " AND " + order_by + " > " + str(offset)
sql += " ORDER BY " + order_by
if count:
sql += " LIMIT " + str(count)
else:
sql += " " + SQLUtils.get_limit_string(count, offset)
if shard_by is None:
shard_by = value_list[0]
logging.getLogger().debug(sql)
try:
ret = MySQL.get(shard_by).query(sql, value_list)
except BadIdError:
raise RowNotFoundException()
logging.getLogger().debug("RESULTS: " + str(len(ret)))
return ret
def _save(self, table_name, col_to_value, cols_to_update, shard_by = None):
#override
#checks for dirty keys on the model and updates the database
sql = (
"INSERT INTO " + table_name + "( "
+ ( ", ".join(col_name for col_name in col_to_value.keys()) )
+ " ) VALUES( "
+ ( ", ".join("%(" + col_name + ")s" for col_name in col_to_value.keys()) )
+ " )"
)
if len(cols_to_update):
sql = sql + (
" ON DUPLICATE KEY UPDATE "
+ (", ".join(
col +"=VALUES("+ col +")" for col in cols_to_update
))
)
logging.getLogger().debug("SAVE: " + sql)
if shard_by is None:
shard_by = col_to_value['id']
ret = MySQL.get(shard_by).query(sql, col_to_value)
logging.getLogger().debug("DAO._save RESULT " + str(ret))
return ret
def get_auth_for_user(self, user_id, auth_classes=None):
where_clause = And([("user_id", "=", "%s")])
vals = [user_id]
qb = SQLQueryBuilder.select("auth_lookup", backtick=False)
if auth_classes is not None:
if isinstance(auth_classes, type):
auth_classes = [auth_classes]
esses = []
for auth_class in auth_classes:
auth_type = self._class_to_type_id(auth_class)
vals.append(auth_type)
esses.append("%s")
where_clause.append(
("provider_type", "IN", "(" + ",".join(esses) + ")"))
qb = qb.where(where_clause)
rows = MySQL.get(user_id).query(qb.build(), vals)
rows = self._filter_deleted(rows)
return [self._row_to_model(row) for row in rows]
def _get_user_agent_by_string(self, user_agent_string):
shard_id = MySQL.get_shard_id_for_string(user_agent_string)
hash_ = UserAgent.generate_hash(user_agent_string)
rows = MySQL.get_by_shard_id(shard_id).query(
"SELECT * FROM user_agents WHERE user_agent_hash = %s", (hash_, ))
if not len(rows):
raise UserAgentNotFoundException()
row = rows[0]
if row['id'] not in self._user_agents:
self._user_agents[row['id']] = UserAgent(
row['id'], row['user_agent_string'].decode("utf-8"))
return self._user_agents[row['id']]
def multi_shard_insert(self, table_name, shard_by_col_name, dicts_to_insert, cols_to_update = None):
count = 0
#construct mapping of inserted objects to shard that they go to
shard_to_dicts = {}
for d in dicts_to_insert:
try:
primary_id = d[shard_by_col_name]
shard_id = Id(primary_id).get_shard_id()
if shard_id not in shard_to_dicts:
shard_to_dicts[shard_id] = []
shard_to_dicts[shard_id].append(d)
except Exception as e:
#skip objects that don't have the shard_by_col, or in wrong format
if not self._catch_errors:
raise e
for shard_id, dict_list in shard_to_dicts.items():
#make sure we only have a separate call for each column set
dict_lists_by_cols = {}
for d in dict_list:
cols = list(d.keys())
cols.sort()
col_str = ",".join(cols)
if not col_str in dict_lists_by_cols:
dict_lists_by_cols[col_str] = []
dict_lists_by_cols[col_str].append(d)
for col_str, dict_list in dict_lists_by_cols.items():
#get vals array
vals = [ ["%s" for k in d.keys()] for d in dict_list ]
#create parameter placeholders
params = [ v for d in dict_list for v in d.values()]
cols = dict_list[0].keys()
qb = SQLQueryBuilder.insert(table_name).columns(cols).values(vals)
if cols_to_update:
update_list = [ ("`" + c + "`", "VALUES(`" + c+ "`)") for c in cols_to_update ]
qb.on_duplicate_key_update(update_list)
#do insert
shard = MySQL.get_by_shard_id(shard_id, self._pool.get_id())
try:
count = count + shard.query(qb.build(), params, self._use_multi)
except Exception as e:
if not self._catch_errors:
raise e
count = 0
return count
def get_last_active_session_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s AND (log_out_ts=NULL OR log_out_ts>NOW()) ORDER BY modified_ts DESC LIMIT 1"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id))
if not len(rows):
raise SessionNotFoundException()
return self._row_to_session(rows[0], user)
def get_last_active_session_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s AND (log_out_ts=NULL OR log_out_ts>NOW()) ORDER BY modified_ts DESC LIMIT 1"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id))
if not len(rows):
raise SessionNotFoundException()
return self._row_to_session(rows[0], user)
def get_all_sessions_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id,))
ret = []
for row in rows:
ret.append(self._row_to_session(row, user))
return ret
def get_active_sessions_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s AND (log_out_ts=NULL OR log_out_ts>NOW())"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id,))
ret = []
for row in rows:
ret.append(self._row_to_session(row, user))
return ret
def get_auth_by_provider_id(self, auth_class, provider_id):
type_id = self._class_to_type_id(auth_class)
shard_id = MySQL.get_shard_id_for_string(provider_id)
rows = MySQL.get_by_shard_id(shard_id).query("SELECT * FROM auth WHERE provider_id=%s AND provider_type=%s",
(provider_id, type_id))
if not len(rows):
raise NoAuthFoundException(provider_id)
row = rows[0]
try:
auth = self._row_to_model(row)
except RowDeletedException:
raise NoAuthFoundException(provider_id)
auth.update_stored_state()
return auth
def _get_user_agent_by_id(self, user_agent_id):
if user_agent_id not in self._user_agents:
rows = MySQL.get(user_agent_id).query("SELECT * FROM user_agents WHERE id = %s", (user_agent_id,))
if not len(rows):
raise UserAgentNotFoundException()
row = rows[0]
self._user_agents[user_agent_id] = UserAgent(row['id'], row['user_agent_string'].decode("utf-8"))
return self._user_agents[user_agent_id]
def get_active_sessions_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s AND (log_out_ts=NULL OR log_out_ts>NOW())"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id, ))
ret = []
for row in rows:
ret.append(self._row_to_session(row, user))
return ret
def get_all_sessions_for_user(self, user):
query = "SELECT * FROM sessions WHERE user_id=%s"
shard = MySQL.get(user.id)
rows = shard.query(query, (user.id, ))
ret = []
for row in rows:
ret.append(self._row_to_session(row, user))
return ret
def get_auth_by_provider_id(self, auth_class, provider_id):
type_id = self._class_to_type_id(auth_class)
shard_id = MySQL.get_shard_id_for_string(provider_id)
rows = MySQL.get_by_shard_id(shard_id).query(
"SELECT * FROM auth WHERE provider_id=%s AND provider_type=%s",
(provider_id, type_id))
if not len(rows):
raise NoAuthFoundException(provider_id)
row = rows[0]
try:
auth = self._row_to_model(row)
except RowDeletedException:
raise NoAuthFoundException(provider_id)
auth.update_stored_state()
return auth
def _delete(self, table_name, column_list, value_list, shard_by=None):
sql = ("UPDATE " + table_name + " set deleted=1 WHERE " +
" AND ".join(column_name + "=%s"
for column_name in column_list))
if not shard_by:
shard_by = value_list[0]
success = MySQL.get(shard_by).query(sql, value_list)
return success
def _get_user_agent_by_id(self, user_agent_id):
if user_agent_id not in self._user_agents:
rows = MySQL.get(user_agent_id).query(
"SELECT * FROM user_agents WHERE id = %s", (user_agent_id, ))
if not len(rows):
raise UserAgentNotFoundException()
row = rows[0]
self._user_agents[user_agent_id] = UserAgent(
row['id'], row['user_agent_string'].decode("utf-8"))
return self._user_agents[user_agent_id]
def get_session(self, session_id):
shard = MySQL.get(session_id)
rows = shard.query("SELECT * FROM sessions WHERE id=%s", (session_id,))
if not len(rows):
raise SessionNotFoundException()
try:
return self._row_to_session(rows[0])
except (RowNotFoundException,RowDeletedException,framework.models.auth.NoAuthFoundException):
raise SessionNotFoundException()
def _delete(self, table_name, column_list, value_list, shard_by = None):
sql = ("UPDATE " + table_name + " set deleted=1 WHERE "
+ " AND ".join(
column_name+"=%s" for column_name in column_list
))
if not shard_by:
shard_by = value_list[0]
success = MySQL.get(shard_by).query(sql, value_list)
return success
def new_session(self,
auth,
user_agent_string,
log_out_ts=None,
session_flags=None):
user = auth.user
new_session_id = MySQL.next_id(Id(user.id).get_shard_id())
try:
user_agent = self._get_user_agent_by_string(user_agent_string)
except UserAgentNotFoundException:
shard_id = MySQL.get_shard_id_for_string(user_agent_string)
id = MySQL.next_id(shard_id)
user_agent = UserAgent(id, user_agent_string)
self._save_user_agent(user_agent)
return Session(new_session_id,
user,
user_agent,
auth,
log_out_ts=log_out_ts,
flags=session_flags)
def get_session(self, session_id):
shard = MySQL.get(session_id)
rows = shard.query("SELECT * FROM sessions WHERE id=%s",
(session_id, ))
if not len(rows):
raise SessionNotFoundException()
try:
return self._row_to_session(rows[0])
except (RowNotFoundException, RowDeletedException,
framework.models.auth.NoAuthFoundException):
raise SessionNotFoundException()
def get_auth_by_id(self, auth_id, user=None):
rows = MySQL.get(auth_id).query("SELECT * FROM auth WHERE id=%s", (auth_id,))
if not len(rows):
raise NoAuthFoundException("_id" + str(auth_id))
row = rows[0]
if user is None:
user_dao = app.models.user.UserDAO()
user = user_dao.get(row['user_id'])
elif user.id != row['user_id']:
raise UserAuthMismatchError()
auth = self._row_to_model(row)
auth.update_stored_state()
return auth
def get_auth_by_id(self, auth_id, user=None):
rows = MySQL.get(auth_id).query("SELECT * FROM auth WHERE id=%s",
(auth_id, ))
if not len(rows):
raise NoAuthFoundException("_id" + str(auth_id))
row = rows[0]
if user is None:
user_dao = app.models.user.UserDAO()
user = user_dao.get(row['user_id'])
elif user.id != row['user_id']:
raise UserAuthMismatchError()
auth = self._row_to_model(row)
auth.update_stored_state()
return auth
def _get_list(self,
table_name,
id,
id_name,
sort_by="sort_index",
count=None,
offset=None,
has_permissions=None,
missing_permissions=None,
sort=SORT_HI_TO_LO):
where_clause = And([(id_name, "=", "%s"), ("deleted", "=", "0")],
backtick=False)
if has_permissions:
where_clause.append(
("`permission`|" + str(has_permissions), "=", "`permission`"))
if missing_permissions:
where_clause.append(("`permission`&" + str(missing_permissions),
"=", "`permission`"))
if offset and sort_by:
if SORT_HI_TO_LO:
comp = "<"
else:
comp = ">"
where_clause.append((sort_by, comp, offset))
query_builder = (SQLQueryBuilder.select(table_name).where(
where_clause).limit(count))
if sort_by:
query_builder.order_by(sort_by, sort)
try:
ret = MySQL.get(id).query(query_builder.build(), [id])
except BadIdError:
raise framework.models.data_access_object.RowNotFoundException()
return ret
def build(cls, length, shard_id = None, pool_id = None):
if length < cls.MIN_LENGTH:
raise Exception("Random token needs to be at least " + str(cls.MIN_LENGTH) + " chars long")
"""
Returns a random alphanumeric string with
"""
r = ''.join(random.SystemRandom().choice( (string.ascii_letters + string.digits) ) for _ in range(length))
if shard_id is not None:
if pool_id is None:
pool_id = MySQLPool.MAIN
num_shards = MySQL.get_pool(pool_id).get_num_shards()
shard_id = shard_id % num_shards
r = cls._apply_shard_info(r, shard_id, pool_id)
elif pool_id is not None:
r = cls._apply_shard_info(r, None, pool_id)
return r
def _get_list(self, table_name, id, id_name, sort_by="sort_index", count=None, offset=None, has_permissions=None,
missing_permissions=None, sort=SORT_HI_TO_LO):
where_clause = And([
(id_name, "=", "%s"),
("deleted", "=", "0")
],backtick=False)
if has_permissions:
where_clause.append(
("`permission`|" + str(has_permissions), "=", "`permission`")
)
if missing_permissions:
where_clause.append(
("`permission`&" + str(missing_permissions), "=", "`permission`")
)
if offset and sort_by:
if SORT_HI_TO_LO:
comp = "<"
else:
comp = ">"
where_clause.append((sort_by, comp, offset))
query_builder = (SQLQueryBuilder
.select(table_name)
.where(where_clause)
.limit(count))
if sort_by:
query_builder.order_by(sort_by, sort)
try:
ret = MySQL.get(id).query(query_builder.build(), [id])
except BadIdError:
raise framework.models.data_access_object.RowNotFoundException()
return ret
def build(cls, length, shard_id=None, pool_id=None):
if length < cls.MIN_LENGTH:
raise Exception("Random token needs to be at least " +
str(cls.MIN_LENGTH) + " chars long")
"""
Returns a random alphanumeric string with
"""
r = ''.join(random.SystemRandom().choice((string.ascii_letters +
string.digits))
for _ in range(length))
if shard_id is not None:
if pool_id is None:
pool_id = MySQLPool.MAIN
num_shards = MySQL.get_pool(pool_id).get_num_shards()
shard_id = shard_id % num_shards
r = cls._apply_shard_info(r, shard_id, pool_id)
elif pool_id is not None:
r = cls._apply_shard_info(r, None, pool_id)
return r
def _get(self,
table_name,
column_list,
value_list,
shard_by=None,
order_by=None,
count=None,
offset=None):
sql = ("SELECT * FROM " + table_name + " WHERE " +
" AND ".join(column_name + "=%s"
for column_name in column_list))
if order_by:
if offset:
sql += " AND " + order_by + " > " + str(offset)
sql += " ORDER BY " + order_by
if count:
sql += " LIMIT " + str(count)
else:
sql += " " + SQLUtils.get_limit_string(count, offset)
if shard_by is None:
shard_by = value_list[0]
logging.getLogger().debug(sql)
try:
ret = MySQL.get(shard_by).query(sql, value_list)
except BadIdError:
raise RowNotFoundException()
logging.getLogger().debug("RESULTS: " + str(len(ret)))
return ret
def _save(self, table_name, col_to_value, cols_to_update, shard_by=None):
#override
#checks for dirty keys on the model and updates the database
sql = ("INSERT INTO " + table_name + "( " +
(", ".join(col_name for col_name in col_to_value.keys())) +
" ) VALUES( " +
(", ".join("%(" + col_name + ")s"
for col_name in col_to_value.keys())) + " )")
if len(cols_to_update):
sql = sql + (" ON DUPLICATE KEY UPDATE " +
(", ".join(col + "=VALUES(" + col + ")"
for col in cols_to_update)))
logging.getLogger().debug("SAVE: " + sql)
if shard_by is None:
shard_by = col_to_value['id']
ret = MySQL.get(shard_by).query(sql, col_to_value)
logging.getLogger().debug("DAO._save RESULT " + str(ret))
return ret
def next_id(self, id_like=None):
if id_like:
return MySQL.next_id_like(id_like, self._pool())
return MySQL.next_id(pool_id=self._pool())
import sys
from framework.storage.mysql import MySQL
from framework.storage.mysql_pool import MySQLPool
from multiprocessing import Pool
from pprint import pprint
sql_file_name = sys.argv[1]
try:
pool_id = sys.argv[2]
except IndexError:
pool_id = MySQLPool.MAIN
pool = MySQL.get_pool(pool_id)
try:
num_threads = sys.argv[3]
except IndexError:
num_threads = None
def get_query_string(file_name):
with open('sql/' + file_name) as data_file:
return data_file.read()
def get_query_runner(pool, query_str):
# bind pool and query string to a function
# that can make a query per shard id
def run_one_query(shard_id):
shard = pool.get_shard(shard_id)
query_res = shard.query(query_str, None, True)
def _save_user_agent(self, user_agent):
if user_agent.is_dirty:
ua_dict = user_agent.to_dict()
ua_query = "INSERT IGNORE INTO user_agents VALUES(%(id)s, %(hash)s, %(string)s, NOW())"
MySQL.get(user_agent.id).query(ua_query, ua_dict)
user_agent.update_stored_state()
def next_id(self, id_like = None):
if id_like:
return MySQL.next_id_like(id_like, self._pool())
return MySQL.next_id(pool_id=self._pool())
def _generate_id_from_provider_id(self, provider_id):
shard_id = MySQL.get_shard_id_for_string(provider_id)
return MySQL.next_id(shard_id)
def _generate_id_from_provider_id(self, provider_id):
shard_id = MySQL.get_shard_id_for_string(provider_id)
return MySQL.next_id(shard_id)
def _save_user_agent(self, user_agent):
if user_agent.is_dirty:
ua_dict = user_agent.to_dict()
ua_query = "INSERT IGNORE INTO user_agents VALUES(%(id)s, %(hash)s, %(string)s, NOW())"
MySQL.get(user_agent.id).query(ua_query, ua_dict)
user_agent.update_stored_state()
def new_user(self, *args, **kwargs):
return Entity(MySQL.next_id())
