def delete_user(user_id=undefined, ): User.delete_user(user_id) users = DBSession.execute(select([ t_users.c.id, ])).fetchall() return users
def create_device(subject_id=undefined, device_id=undefined, device_os=undefined, push_id=undefined, app_version=undefined, gen_data=default_device_data): if push_id is undefined: push_id = gen_data["push_id"] if device_os is undefined: device_os = gen_data["device_os"] if app_version is undefined: app_version = gen_data["app_version"] if device_id is undefined: device_id = gen_data["device_id"] SubjectDevice.add_or_update_device(device_id=device_id, subject_id=subject_id, device_os=device_os, push_id=push_id, app_version=app_version) device = DBSession.execute(t_subject_device.select().where( and_(t_subject_device.c.device_id == device_id, t_subject_device.c.subject_id == subject_id))).fetchone() return device
def subject_remove_from_parent(request, *args, **kw): context = request.context parent_row = context.subject_row if not request.has_perm(perm_global_manage_subjects): raise APIError(403, "forbidden") q = t_subjects_subjects.select().where(and_( t_subjects_subjects.c.subject_id == request.validated_params.body["subject_id"], t_subjects_subjects.c.part_of_id == parent_row["id"], )) r = DBSession.execute(q).fetchone() if r: q = t_subjects_subjects.delete().where(and_( t_subjects_subjects.c.subject_id == request.validated_params.body["subject_id"], t_subjects_subjects.c.part_of_id == parent_row["id"], )) update_connection().execute(q) return r_status.output({ "status": "ok" })
def forward(cls, subjecttype_id, from_date, to_date, whole_time_required): q = select([t_subjects.c.id, ]).where(t_subjects.c.subjecttype_id == subjecttype_id) if whole_time_required: q = q.where(and_( t_subjects.c.created_at <= from_date #or_( # t_subjects.c.deleted_at == None, # t_subjects.c.deleted_at >= to_date #) )) else: q = q.where(or_( and_( t_subjects.c.created_at <= from_date, #or_( # t_subjects.c.deleted_at >= from_date, # t_subjects.c.deleted_at == None, #) ), and_( t_subjects.c.created_at >= from_date, t_subjects.c.created_at <= to_date, ) )) return [x.id for x in DBSession.execute(q).fetchall()]
def create_goal_properties(goal_id): goal_property = GoalProperty() goal_property.name = "participate" goal_property.is_variable = True DBSession.add(goal_property) DBSession.flush() translation_variable = TranslationVariable() translation_variable.name = "invite_users_goal_name" DBSession.add(translation_variable) DBSession.flush() goals_goal_property = GoalGoalProperty() goals_goal_property.goal_id = goal_id goals_goal_property.property_id = goal_property.id goals_goal_property.value = "7" goals_goal_property.value_translation_id = translation_variable.id goals_goal_property.from_level = 2 DBSession.add(goals_goal_property) DBSession.flush() goals_goal_property_result = DBSession.execute( t_goals_goalproperties.select().where( t_goals_goalproperties.c.goal_id == goal_id)).fetchone() return goals_goal_property_result
def create_user( user_id = undefined, lat = undefined, lng = undefined, country = undefined, region = undefined, city = undefined, timezone = undefined, language = undefined, friends = [], groups = [], additional_public_data = undefined, gen_data = default_gen_data ): if additional_public_data is undefined: additional_public_data = { 'first_name' : 'Stefan', 'last_name' : 'Rogers' } if user_id is undefined: user_id = (DBSession.execute("SELECT max(id) as c FROM users").scalar() or 0) + 1 if lat is undefined: lat = randrange_float(gen_data["area"]["min_lat"],gen_data["area"]["max_lat"]) if lng is undefined: lng = randrange_float(gen_data["area"]["min_lng"], gen_data["area"]["max_lng"]) if country is undefined: country = gen_data["country"] if timezone is undefined: timezone = gen_data["timezone"] if region is undefined: region = gen_data["region"] if city is undefined: city = gen_data["city"] if language is undefined: language = gen_data["language"] User.set_infos( user_id = user_id, lat = lat, lng = lng, timezone = timezone, country = country, region = region, city = city, language = language, groups = groups, friends = friends, additional_public_data = additional_public_data ) return User.get_user(user_id)
def main(argv=sys.argv): if len(argv) < 2: usage(argv) config_uri = argv[1] options = parse_vars(argv[2:]) setup_logging(config_uri) settings = get_appsettings(config_uri, options=options) from gengine.base.settings import set_settings set_settings(settings) durl = os.environ.get("DATABASE_URL") # heroku if durl: settings['sqlalchemy.url'] = durl murl = os.environ.get("MEMCACHED_URL") if murl: settings['urlcache_url'] = murl engine = engine_from_config(settings, 'sqlalchemy.') config = Configurator(settings=settings) pyramid_dogpile_cache.includeme(config) from gengine.metadata import ( init_session, init_declarative_base, init_db ) init_session() init_declarative_base() init_db(engine) init_caches() from gengine.metadata import ( DBSession ) sess = DBSession() init_session(override_session=sess, replace=True) import gengine.app.model as m with transaction.manager: mark_changed(sess, transaction.manager, True) messages = sess.execute(m.t_user_messages.select().where(m.t_user_messages.c.has_been_pushed == False)) for msg in messages: m.UserMessage.deliver(msg) sess.flush() sess.commit()
def gcm_feedback(response): # Successfully handled registration_ids if 'success' in response: for reg_id, success_id in response['success'].items(): log.debug( 'Successfully sent notification for reg_id {0}'.format(reg_id)) # Handling errors if 'errors' in response: for error, reg_ids in response['errors'].items(): # Check for errors and act accordingly if error in ['NotRegistered', 'InvalidRegistration']: # Remove reg_ids from database for reg_id in reg_ids: q = t_subject_device.delete().where( t_subject_device.c.push_id == reg_id) DBSession.execute(q) # Repace reg_id with canonical_id in your database if 'canonical' in response: for reg_id, canonical_id in response['canonical'].items(): if not isinstance(reg_id, str): reg_id = reg_id.decode("utf8") log.debug( "Replacing reg_id: {0} with canonical_id: {1} in db".format( reg_id, canonical_id)) q = t_subject_device.update().values({ "push_id": canonical_id }).where(t_subject_device.c.push_id == reg_id) DBSession.execute(q) DBSession.flush()
def __getitem__(self, subject_id): try: from gengine.metadata import DBSession row = DBSession.execute(t_subjects.select().where( t_subjects.c.id == int(subject_id))).fetchone() if row: return SubjectResource(request=self.request, t_name=subject_id, t_parent=self, subject_id=subject_id, subject_row=row) except ValueError: pass except: log.exception("Error creating SubjectResource") raise KeyError()
def variables_search_list(request, *args, **kw): context = request.context q = t_variables.select().order_by(t_variables.c.name.asc()) types = DBSession.execute(q).fetchall() if not request.has_perm(perm_global_list_variables): raise APIError(403, "forbidden") ret = { "variables": [{ "id": st["id"], "name": st["name"], "increase_permission": st["increase_permission"], } for st in types] } return r_variablelist.output(ret)
def subjecttype_search_list(request, *args, **kw): context = request.context if not request.has_perm(perm_global_search_subjecttypes): raise APIError(403, "forbidden") q = t_subjecttypes.select().order_by(t_subjecttypes.c.name.asc()) types = DBSession.execute(q).fetchall() ret = { "subjecttypes": [{ "id": st["id"], "name": st["name"], } for st in types] } return r_subjecttypelist.output(ret)
def update_device( user_id=undefined, device_id=undefined, device_os=undefined, push_id=undefined, app_version=undefined, ): UserDevice.add_or_update_device(device_id=device_id, user_id=user_id, device_os=device_os, push_id=push_id, app_version=app_version) device = DBSession.execute(t_user_device.select().where( and_(t_user_device.c.device_id == device_id, t_user_device.c.user_id == user_id))).fetchone() return device
def set_messages_read(request): try: doc = request.json_body except: raise APIError(400, "invalid_json", "no valid json body") subject_id = int(request.matchdict["subject_id"]) if asbool(get_settings().get("enable_user_authentication", False)): may_read_messages = request.has_perm( perm_global_read_messages ) or request.has_perm(perm_own_read_messages) and str( request.subject.id) == str(subject_id) if not may_read_messages: raise APIError(403, "forbidden", "You may not read the messages of this subject.") if not exists_by_expr(t_subjects, t_subjects.c.id == subject_id): raise APIError(404, "set_messages_read.subject_not_found", "There is no subject with this id.") message_id = doc.get("message_id") q = select([t_subject_messages.c.id, t_subject_messages.c.created_at], from_obj=t_subject_messages).where( and_(t_subject_messages.c.id == message_id, t_subject_messages.c.subject_id == subject_id)) msg = DBSession.execute(q).fetchone() if not msg: raise APIError(404, "set_messages_read.message_not_found", "There is no message with this id.") uS = update_connection() uS.execute(t_subject_messages.update().values({ "is_read": True }).where( and_(t_subject_messages.c.subject_id == subject_id, t_subject_messages.c.created_at <= msg["created_at"]))) return {"status": "ok"}
def get_messages(request): try: subject_id = int(request.matchdict["subject_id"]) except: subject_id = None try: offset = int(request.GET.get("offset", 0)) except: offset = 0 limit = 100 if asbool(get_settings().get("enable_user_authentication", False)): may_read_messages = request.has_perm( perm_global_read_messages ) or request.has_perm(perm_own_read_messages) and str( request.subject.id) == str(subject_id) if not may_read_messages: raise APIError(403, "forbidden", "You may not read the messages of this subject.") if not exists_by_expr(t_subjects, t_subjects.c.id == subject_id): raise APIError(404, "get_messages.subject_not_found", "There is no subject with this id.") q = t_subject_messages.select().where( t_subject_messages.c.subject_id == subject_id).order_by( t_subject_messages.c.created_at.desc()).limit(limit).offset(offset) rows = DBSession.execute(q).fetchall() return { "messages": [{ "id": message["id"], "text": SubjectMessage.get_text(message), "is_read": message["is_read"], "created_at": message["created_at"] } for message in rows] }
def forward(cls, subject_id, from_date, to_date, whole_time_required): subjects = [subject_id, ] q = select([t_subjectrelations.c.to_id, ], t_subjectrelations.c.from_id == subject_id) if from_date and to_date: if whole_time_required: q = q.where(and_( t_subjectrelations.c.created_at <= from_date, or_( t_subjectrelations.c.deleted_at == None, t_subjectrelations.c.deleted_at >= to_date ) )) else: q = q.where(or_( and_( t_subjectrelations.c.created_at <= from_date, or_( t_subjectrelations.c.deleted_at >= from_date, t_subjectrelations.c.deleted_at == None, ) ), and_( t_subjectrelations.c.created_at >= from_date, t_subjectrelations.c.created_at <= to_date, ) )) else: q = q.where( t_subjectrelations.c.deleted_at == None, ) subjects += [x["to_id"] for x in DBSession.execute(q).fetchall()] return subjects
def main(argv=sys.argv): if len(argv) < 2: usage(argv) config_uri = argv[1] options = parse_vars(argv[2:]) setup_logging(config_uri) settings = get_appsettings(config_uri, options=options) import gengine gengine.main({}, **settings) from gengine.metadata import (DBSession) sess = DBSession() import gengine.app.model as m import crontab from gengine.app.registries import get_task_registry enginetasks = get_task_registry().registrations with transaction.manager: mark_changed(sess, transaction.manager, True) tasks = sess.execute(m.t_tasks.select()).fetchall() for task in tasks: cron = task["cron"] if not cron: cron = enginetasks.get(task["task_name"]).get( "default_cron", None) if cron: now = dt_now().replace(second=0) item = crontab.CronItem(line=cron) s = item.schedule(date_from=now) prev = s.get_next().replace(second=0) next = s.get_next().replace(second=0) execs = sess.execute(m.t_taskexecutions.select().where( and_( m.t_taskexecutions.c.task_id == task["id"], m.t_taskexecutions.c.canceled_at == None, m.t_taskexecutions.c.finished_at == None, )).order_by( m.t_taskexecutions.c.planned_at.desc())).fetchall() found = False for exec in execs: if exec["planned_at"] >= next: # The next execution is already planned found = True if exec["planned_at"] <= prev and prev < dt_ago( minutes=10) and not exec["locked_at"]: # The execution is more than 10 minutes in the past and not yet locked (worker not running / overloaded) if next - datetime.timedelta(minutes=10) < dt_now(): # The next execution is planned in less than 10 minutes, cancel the other one sess.execute(m.t_taskexecutions.update().values({ 'canceled_at': dt_now() }).where({'id': exec["id"]})) if exec["locked_at"] and exec["locked_at"] < dt_ago( hours=24): # this task is running for more than 24 hours. probably crashed.... set it to canceled sess.execute(m.t_taskexecutions.update().values({ 'canceled_at': dt_now() }).where({'id': exec["id"]})) if not found: # Plan next execution sess.execute(m.t_taskexecutions.insert().values({ 'task_id': task["id"], 'planned_at': next })) sess.flush() sess.commit()
def subjects_search_list(request, *args, **kw): context = request.context if not request.has_perm(perm_global_search_subjects): raise APIError(403, "forbidden") exclude_leaves = request.validated_params.body.get("exclude_leaves", None) parent_subjecttype_id = request.validated_params.body.get("parent_subjecttype_id", None) parent_subject_id = request.validated_params.body.get("parent_subject_id", None) stj = t_subjecttypes.outerjoin(t_subjecttypes_subjecttypes, t_subjecttypes_subjecttypes.c.subjecttype_id == t_subjecttypes.c.id) q = select([t_subjecttypes.c.id], from_obj=stj) if parent_subjecttype_id is not None: q = q.where( t_subjecttypes_subjecttypes.c.part_of_id == parent_subjecttype_id ) if exclude_leaves is not None: et = t_subjecttypes_subjecttypes.alias() eq = select([et.c.subjecttype_id]).where(et.c.part_of_id == t_subjecttypes.c.id) q = q.where(exists(eq)) subjecttype_ids = [x["id"] for x in DBSession.execute(q).fetchall()] cols = [ t_subjects.c.id, t_subjects.c.subjecttype_id, t_subjects.c.name, t_subjects.c.lat, t_subjects.c.lng, t_subjects.c.language_id, t_subjects.c.timezone, t_subjects.c.created_at, ] j = t_subjects if parent_subject_id is not None: sq = text(""" WITH RECURSIVE nodes_cte(subject_id, subjecttype_id, name, part_of_id, depth, path) AS ( SELECT g1.id, g1.subjecttype_id, g1.name, NULL::bigint as part_of_id, 1::INT as depth, g1.id::TEXT as path FROM subjects as g1 LEFT JOIN subjects_subjects ss ON ss.subject_id=g1.id WHERE ss.part_of_id = :subject_id UNION ALL SELECT c.subject_id, p.subjecttype_id, p.name, c.part_of_id, p.depth + 1 AS depth, (p.path || '->' || g2.id ::TEXT) FROM nodes_cte AS p, subjects_subjects AS c JOIN subjects AS g2 ON g2.id=c.subject_id WHERE c.part_of_id = p.subject_id ) SELECT * FROM nodes_cte """).bindparams(subject_id=parent_subject_id)\ .columns(subject_id=Integer, subjecttype_id=Integer,name=String,part_of_id=Integer,depth=Integer,path=String)\ .alias() j = j.outerjoin(sq, sq.c.subject_id == t_subjects.c.id) cols += [ sq.c.path, sq.c.name.label("inherited_by_name"), sq.c.subjecttype_id.label("inherited_by_subjecttype_id") ] subjects_query = select(cols, from_obj=j).where(t_subjects.c.subjecttype_id.in_(subjecttype_ids)) include_search = request.validated_params.body.get("include_search", None) if include_search: subjects_query = subjects_query.where(or_( t_subjects.c.name.ilike("%" + include_search + "%"), )) limit = request.validated_params.body.get("limit", None) if limit: subjects_query = subjects_query.limit(limit) offset = request.validated_params.body.get("offset", None) if offset: subjects_query = subjects_query.offset(offset) result = DBSession.execute(subjects_query).fetchall() subjects = {} for r in result: if not r["id"] in subjects: path = r["path"] if "path" in r and r["path"] is not None else "" inherited_by_name = r["inherited_by_name"] if "inherited_by_name" in r and r["inherited_by_name"] is not None else "" inherited_by_subjecttype_id = r["inherited_by_subjecttype_id"] if "inherited_by_subjecttype_id" in r and r["inherited_by_subjecttype_id"] is not None else "" subjects[r["id"]] = { 'id': r["id"], 'subjecttype_id': r["subjecttype_id"], 'name': r["name"], 'created_at': r["created_at"], 'path': path, 'inherited_by_subjecttype_id': inherited_by_subjecttype_id, 'inherited_by_name': inherited_by_name, 'in_parent': True if path else False, 'directly_in_parent': len(path)>0 and not "->" in path, 'inherited_by': path.split("->")[0] if len(path)>0 and "->" in path else None } return r_subjectlist.output({ "subjects": list(subjects.values()) })
def admin_tenant(environ, start_response): from gengine.app.admin import adminapp def admin_app(environ, start_response): #return HTTPSProxied(DebuggedApplication(adminapp.wsgi_app, True))(environ, start_response) return HTTPSProxied(adminapp.wsgi_app)(environ, start_response) def request_auth(environ, start_response): resp = Response() resp.status_code = 401 resp.www_authenticate = 'Basic realm="%s"' % ( "Gamification Engine Admin", ) return resp(environ, start_response) if not asbool(get_settings().get("enable_user_authentication", False)): return admin_app(environ, start_response) req = Request(environ) def _get_basicauth_credentials(request): authorization = request.headers.get("authorization", "") try: authmeth, auth = authorization.split(' ', 1) except ValueError: # not enough values to unpack return None if authmeth.lower() == 'basic': try: auth = base64.b64decode(auth.strip()).decode("UTF-8") except binascii.Error: # can't decode return None try: login, password = auth.split(':', 1) except ValueError: # not enough values to unpack return None return {'login': login, 'password': password} return None user = None cred = _get_basicauth_credentials(req) token = req.cookies.get("token", None) if token: tokenObj = DBSession.query(AuthToken).filter( AuthToken.token == token).first() user = None if tokenObj and tokenObj.valid_until < datetime.datetime.utcnow(): tokenObj.extend() if tokenObj: user = tokenObj.user if not user: if cred: user = DBSession.query(AuthUser).filter_by( email=cred["login"]).first() if not user or not user.verify_password(cred["password"]): return request_auth(environ, start_response) if user: j = t_auth_users_roles.join(t_auth_roles).join( t_auth_roles_permissions) q = select( [t_auth_roles_permissions.c.name], from_obj=j).where(t_auth_users_roles.c.auth_user_id == user.id) permissions = [r["name"] for r in DBSession.execute(q).fetchall()] if not perm_global_access_admin_ui in permissions: return request_auth(environ, start_response) else: token_s = user.get_or_create_token().token def start_response_with_headers(status, headers, exc_info=None): cookie = SimpleCookie() cookie['X-Auth-Token'] = token_s cookie['X-Auth-Token']['path'] = get_settings().get( "urlprefix", "").rstrip("/") + "/" headers.append( ('Set-Cookie', cookie['X-Auth-Token'].OutputString()), ) return start_response(status, headers, exc_info) return admin_app(environ, start_response_with_headers)
def send_push_message(subject_id, text="", custom_payload={}, title="Gamification-Engine", android_text=None, ios_text=None): message_count = DBSession.execute( select([func.count("*").label("c")], from_obj=t_subject_messages).where( and_(t_subject_messages.c.subject_id == subject_id, t_subject_messages.c.is_read == False))).scalar() data = dict({"title": title, "badge": message_count}, **custom_payload) settings = get_settings() if not ios_text: ios_text = text if not android_text: android_text = text rows = DBSession.execute( select([t_subject_device.c.push_id, t_subject_device.c.device_os], from_obj=t_subject_device).distinct().where( t_subject_device.c.subject_id == subject_id)).fetchall() for device in rows: if "ios" in device.device_os.lower(): identifier = random.getrandbits(32) if custom_payload: payload = Payload(alert=ios_text, custom=data, badge=message_count, sound="default") else: payload = Payload(alert=ios_text, custom=data, badge=message_count, sound="default") log.debug("Sending Push message to User (ID: %s)", subject_id) if device.push_id.startswith("prod_"): get_prod_apns().gateway_server.send_notification( device.push_id[5:], payload, identifier=identifier) elif device.push_id.startswith("dev_"): get_dev_apns().gateway_server.send_notification( device.push_id[4:], payload, identifier=identifier) if "android" in device.device_os.lower(): log.debug("Sending Push message to User (ID: %s)", subject_id) push_id = lstrip_word(device.push_id, "dev_") push_id = lstrip_word(push_id, "prod_") response = get_gcm().json_request( registration_ids=[ push_id, ], data={ "message": android_text, "data": data, "title": title }, restricted_package_name=os.environ.get( "GCM_PACKAGE", settings.get("gcm.package", "")), priority='high', delay_while_idle=False) if response: gcm_feedback(response)