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 _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_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_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_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_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 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 _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 _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 _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(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_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()