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
