def increment(name): with get_connection() as conn: new = conn.increment(name) return { 'name': name, 'count': new, }
def get_printers(): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute("SELECT name, hostname, ip, client, client_props FROM printers") data = cursor.fetchall() cursor.close() return data
def get_all_settings(): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute("SELECT key, val FROM settings") data = cursor.fetchall() cursor.close() return data
def get(name): with get_connection() as conn: value = conn.get(name) return { 'name': name, 'count': value, }
def get_gcodes(order_by=None, limit=None, start_with=None, filter=None): columns = [ "id", "path", "filename", "display", "absolute_path", "uploaded", "size", "analysis", "user_uuid", ] with get_connection() as connection: statement = prepare_list_statement( connection, "gcodes", columns, order_by=order_by, limit=limit, start_with=start_with, filter=filter, ) cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(statement) data = cursor.fetchall() cursor.close() return data
def get_users(order_by=None, limit=None, start_with=None, filter=None): columns = [ "uuid", "username", "role", "providers", "providers_data", "suspended", "created", ] with get_connection() as connection: statement = prepare_list_statement( connection, "users", columns, order_by=order_by, limit=limit, start_with=start_with, filter=filter, pk_column="uuid", ) cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(statement) data = cursor.fetchall() cursor.close() return data
def get_network_client_by_props(hostname, ip, port, path): def _is_or_equal(column, value): if value is None: return sql.SQL("{} is null").format(sql.Identifier(column)) else: return sql.SQL("{} = {}").format(sql.Identifier(column), sql.Literal(value)) with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) basequery = sql.SQL("SELECT {} from network_clients WHERE").format( sql.SQL(",").join([sql.Identifier(f) for f in FIELDS])) query = sql.SQL(" ").join([ basequery, sql.SQL(" AND ").join([ _is_or_equal("hostname", hostname), _is_or_equal("ip", ip), _is_or_equal("port", port), _is_or_equal("path", path), ]), ]) cursor.execute(query) data = cursor.fetchone() cursor.close() return data
def add_printjob(**job): 'creates a printjob returning the id of the newly created job' if 'uuid' in job: raise ValueError("Cannot add a printjob with an already set UUID.") printer = get_printer(job["printer_uuid"]) if not printer: raise ValueError(f"The printer {job['printer_uuid']} does not exist.") if printer["organization_uuid"] != job["organization_uuid"]: raise ValueError(f"The printer does not belong to the organization.") if job.get("gcode_data", None) and job["gcode_data"].get("uuid", None): if job["gcode_uuid"] != job["gcode_data"]["uuid"]: raise ValueError("printjob['gcode_uuid'] does not match printjob['gcode_data']['uuid']") # FIXME: printjob should not contain organization_uuid (it is already in printer_uuid) job['uuid'] = guid.uuid4() with get_connection() as connection: cursor = connection.cursor() cursor.execute( "INSERT INTO printjobs (uuid, gcode_uuid, organization_uuid, printer_uuid, gcode_data, printer_data, user_uuid) values (%s, %s, %s, %s, %s, %s, %s) RETURNING uuid", ( job["uuid"], job["gcode_uuid"], job["organization_uuid"], job["printer_uuid"], psycopg2.extras.Json(job.get("gcode_data", None)), psycopg2.extras.Json(job.get("printer_data", None)), job.get("user_uuid", None), ), ) data = cursor.fetchone() cursor.close() return data[0]
def get_printer_by_network_props(hostname, ip, port): def _is_or_equal(column, value): if value is None: return sql.SQL("{} is null").format(sql.Identifier(column)) else: return sql.SQL("{} = {}").format(sql.Identifier(column), sql.Literal(value)) with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) basequery = sql.SQL( "SELECT uuid, name, hostname, ip, port, client, client_props, printer_props, protocol FROM printers where" ) query = sql.SQL(" ").join([ basequery, sql.SQL(" and ").join([ _is_or_equal("hostname", hostname), _is_or_equal("ip", ip), _is_or_equal("port", port), ]), ]) cursor.execute(query) data = cursor.fetchone() cursor.close() return data
def get_gcodes( org_uuid, order_by=None, limit=None, start_with=None, filter=None, fulltext_search=None, ): with get_connection() as connection: statement = prepare_list_statement( connection, "gcodes", FIELDS, order_by=order_by, limit=limit, start_with=start_with, filter=filter, where=sql.SQL("organization_uuid = {}").format( sql.Literal(org_uuid)), pk_column="uuid", fulltext_search=fulltext_search, ) cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(statement) data = cursor.fetchall() cursor.close() return data
def get_printjobs(order_by=None, limit=None, start_with=None, filter=None): columns = [ "id", "gcode_id", "printer_uuid", "started", "gcode_data", "printer_data", "user_uuid", ] with get_connection() as connection: statement = prepare_list_statement( connection, "printjobs", columns, order_by=order_by, limit=limit, start_with=start_with, filter=filter, ) cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(statement) data = cursor.fetchall() cursor.close() return data
def reset(name): print('in reset!') with get_connection() as conn: conn.reset(name) return { 'name': name, 'count': 0, }
def drop_organization_role(organization_uuid, user_uuid): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "DELETE FROM organization_roles WHERE organization_uuid = %s AND user_uuid = %s", (organization_uuid, user_uuid), ) cursor.close()
def add_token(**kwargs): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "INSERT INTO api_tokens (user_uuid, jti, name) values (%s, %s, %s)", (kwargs["user_uuid"], kwargs["jti"], kwargs["name"]), ) cursor.close()
def add_organization(**kwargs): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "INSERT INTO organizations (uuid, name) VALUES (%s, %s)", (kwargs["uuid"], kwargs["name"]), ) cursor.close()
def upsert_val(key, val): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "INSERT INTO settings (key, val) values (%s, %s) ON CONFLICT ON CONSTRAINT settings_key_uqc DO UPDATE SET val = %s", (key.lower(), val, val), ) cursor.close()
def set_analysis(gcode_uuid, analysis): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "UPDATE gcodes SET analysis = %s where uuid = %s", (psycopg2.extras.Json(analysis), gcode_uuid), ) cursor.close()
def update_gcode_data(gcode_uuid, gcode_data): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "UPDATE printjobs SET gcode_data = %s where gcode_uuid = %s", (psycopg2.extras.Json(gcode_data), gcode_uuid), ) cursor.close()
def revoke_all_tokens(user_uuid, organization_uuid): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "UPDATE api_tokens set revoked=TRUE where user_uuid = %s and organization_uuid = %s", (user_uuid, organization_uuid), ) cursor.close()
def update_organization(**kwargs): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "UPDATE organizations SET name = %s WHERE uuid = %s", (kwargs["name"], kwargs["uuid"]), ) cursor.close()
def add_local_user(**kwargs): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "INSERT INTO local_users (user_uuid, pwd_hash, force_pwd_change) values (%s, %s, %s)", (kwargs["user_uuid"], kwargs["pwd_hash"], kwargs["force_pwd_change"]), ) cursor.close()
def get_usernames_for_uuids(uuids): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "SELECT uuid, username from users where uuid::text = any(%s)", (uuids,) ) data = cursor.fetchall() cursor.close() return data
def set_organization_role(organization_uuid, user_uuid, role): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( """INSERT INTO organization_roles (organization_uuid, user_uuid, role) VALUES (%s, %s, %s) ON CONFLICT (organization_uuid, user_uuid) DO UPDATE SET role = %s""", (organization_uuid, user_uuid, role, role), ) cursor.close()
def update_local_user(**kwargs): with get_connection() as connection: cursor = connection.cursor() cursor.execute( "UPDATE local_users SET pwd_hash = %s, force_pwd_change = %s where user_uuid = %s", (kwargs["pwd_hash"], kwargs["force_pwd_change"], kwargs["user_uuid"]), ) cursor.close()
def get_printer(uuid): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "SELECT uuid, name, hostname, ip, port, client, client_props, printer_props, protocol FROM printers where uuid = %s", (uuid, ), ) data = cursor.fetchone() cursor.close() return data
def get_by_user_uuid(user_uuid): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "SELECT o.uuid, o.name, ox.role from organizations o join organization_roles ox on ox.organization_uuid = o.uuid where ox.user_uuid = %s", (user_uuid, ), ) data = cursor.fetchall() cursor.close() return data
def get_by_uuid(uuid): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "SELECT uuid, username, role, providers, providers_data, suspended, created from users where uuid = %s", (uuid,), ) data = cursor.fetchone() cursor.close() return data
def get_printjob(uuid): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) query = sql.SQL("SELECT {} from printjobs where uuid = {}").format( sql.SQL(",").join([sql.Identifier(f) for f in FIELDS]), sql.Literal(uuid), ) cursor.execute(query) data = cursor.fetchone() cursor.close() return data
def delete_gcode(id): try: if isinstance(id, str): id = int(id, base=10) except ValueError: pass with get_connection() as connection: cursor = connection.cursor() cursor.execute("DELETE FROM gcodes WHERE id = %s", (id,)) cursor.close()
def get_token(jti): with get_connection() as connection: cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( "SELECT user_uuid, jti, created, name, revoked from api_tokens where jti = %s", (jti, ), ) data = cursor.fetchone() cursor.close() return data