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_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 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 new_user(self, *args, **kwargs): return Entity(MySQL.next_id())