def test_persist_token(conn: connection):
user = conftest.users[0]
register_user(user, conn)
with conn.cursor() as cur:
cur.execute("select * from withings_token")
tokens = cur.fetchall()
cur.execute("select * from withings_token_gargling")
matched = cur.fetchall()
assert len(tokens) == 1
token = dict(tokens[0])
exp = {
"id": 1002,
"access_token": "access_token2",
"refresh_token": "refresh_token2",
"expires_at": 1573921366,
"enable_steps": False,
"enable_weight": False,
}
assert token == exp
assert len(matched) == 1
match = dict(matched[0])
exp = {"service_user_id": 1002, "gargling_id": 2}
assert match == exp
def get_user_with_permissions_and_role_by_username(
db_connection: connection, username: str) -> Optional[Dict[str, Any]]:
cursor = db_connection.cursor(cursor_factory=DictCursor)
cursor.execute(
"""
SELECT
users.id,
users.email,
users.password,
users.username,
user_roles.role AS role,
ARRAY_AGG(permissions.name) AS permissions
FROM
users
INNER JOIN user_roles
ON users.role_id = user_roles.id
LEFT JOIN
user_roles_to_permissions
ON user_roles.id = user_roles_to_permissions.user_role_id
INNER JOIN
permissions
ON user_roles_to_permissions.permission_id = permissions.id
WHERE
users.username = %s
OR users.email = %s
GROUP BY
users.id, user_roles.role;
""", (
username,
username,
))
user = cursor.fetchone()
cursor.close()
return user
def populate_user_table(db: connection) -> None:
with db.cursor() as cursor:
print(cursor)
for user in users:
sql_command = """INSERT INTO faces (db_id, name)
VALUES (%(db_id)s,
%(name)s);"""
data = {"db_id": user.db_id, "name": user.name}
cursor.execute(sql_command, data)
sql_command = """INSERT INTO user_ids (db_id, slack_id, slack_nick, first_name, bday)
VALUES (%(db_id)s,
%(slack_id)s,
%(slack_nick)s,
%(first_name)s,
%(bday)s);"""
data = {
"db_id": user.db_id,
"slack_id": user.slack_id,
"slack_nick": user.slack_nick,
"first_name": user.name,
"bday": user.bday,
}
cursor.execute(sql_command, data)
def create_tables(connection: PostgresConnection) -> None:
"""Create empty tables in database."""
with connection.cursor() as cursor:
for sql in get_sql_script("create_tables"):
cursor.execute(sql)
connection.commit()
def _execute_with_schema(conn: connection, schema: str, *args, **kwargs):
with conn.cursor() as curs: # type: cursor
if schema:
curs.execute('SET search_path TO %s', [schema])
curs.execute(*args, **kwargs)
def add_user(conn: connection, user: User):
insert_impl(conn, user)
conn.commit()
return user
def create_db(conn: connection):
create_db_impl(conn)
conn.commit()
def drop_db(conn: connection):
with conn.cursor() as cursor:
cursor.execute("DROP TABLE IF EXISTS USERS")
cursor.execute("DROP SEQUENCE IF EXISTS public.users_seq")
def delete(conn: connection, id_: int):
with conn.cursor() as cursor:
cursor.execute("DELETE FROM USERS WHERE id=%s", [id_])
def create_tables(cur: cursor, conn: connection) -> None:
conn, cur = get_connection()
for query in create_table_queries:
cur.execute(query)
conn.commit()
def update_article(
db_connection: connection,
id: int,
article_input: ArticleInput,
) -> Article:
cursor = db_connection.cursor(
cursor_factory=DictCursor
)
cursor.execute(
"""
UPDATE
articles
SET
title = %s,
content = %s
WHERE
articles.id = %s
RETURNING
articles.id,
articles.title,
articles.author_id,
articles.content,
articles.status_id,
articles.created_date;
""",
(
article_input.title,
article_input.content,
id,
),
)
updated_article = cursor.fetchone()
db_connection.commit()
cursor.execute(
"""
SELECT
article_statuses.name
FROM
article_statuses
WHERE
article_statuses.id = %s;
""",
(
updated_article['status_id'],
)
)
article_status = cursor.fetchone()
cursor.close()
return Article(
id=updated_article['id'],
title=updated_article['title'],
content=updated_article['content'],
author_id=updated_article['author_id'],
status=article_status['name'],
created_date=updated_article['created_date'],
)
def drop_tables(cur: cursor, conn: connection) -> None:
for query in drop_table_queries:
cur.execute(query)
conn.commit()
def insert_many(table: str, data: List[dict], conn: connection, **kwargs):
query, data = get_insert_query(table, data, **kwargs)
with conn.cursor() as cur:
_ = cur.executemany(query, data)
return conn.commit()
def update(conn: connection, post_id: int, title: str, content: str):
with conn.cursor() as cursor:
query = "UPDATE posts SET title=%s, content=%s WHERE id=%s"
cursor.execute(query, (title, content, post_id))
def get_article_by_id(
db_connection: connection,
id: int,
are_draft_allowed: bool,
are_draft_allowed_for_current_user_id: bool,
current_user_id: int,
are_published_allowed: bool,
are_published_allowed_for_current_user_id: bool,
are_archived_allowed: bool,
are_archived_allowed_for_current_user_id: bool,
) -> Optional[Article]:
cursor = db_connection.cursor(
cursor_factory=DictCursor
)
cursor.execute(
"""
SELECT
*
FROM (
SELECT
articles.id,
articles.title,
articles.content,
articles.author_id,
article_statuses.name AS status,
articles.created_date
FROM
articles
INNER JOIN
article_statuses
ON articles.status_id = article_statuses.id
WHERE
CASE
-- When DRAFT articles are allowed.
WHEN article_statuses.name = 'DRAFT' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When PUBLISHED articles are allowed.
WHEN article_statuses.name = 'PUBLISHED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When ARCHIVED articles are allowed.
WHEN article_statuses.name = 'ARCHIVED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
ELSE FALSE
END
) AS allowed_articles
WHERE
allowed_articles.id = %s;
""",
(
are_draft_allowed,
are_draft_allowed_for_current_user_id,
current_user_id,
are_published_allowed,
are_published_allowed_for_current_user_id,
current_user_id,
are_archived_allowed,
are_archived_allowed_for_current_user_id,
current_user_id,
id,
),
)
article_row = cursor.fetchone()
cursor.close()
if article_row is None:
return None
else:
return Article(
id=article_row["id"],
title=article_row["title"],
content=article_row["content"],
author_id=article_row["author_id"],
status=article_row["status"],
created_date=article_row["created_date"],
)
def delete_by_name(conn: connection, name: str):
with conn.cursor() as cursor:
cursor.execute("DELETE FROM USERS WHERE name=%s", [name])
def fetch_user(name: str, conn: connection) -> dict:
with conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.execute(SQL_SELECT_USER_BY_NAME, {'name': name})
user = cursor.fetchone()
return user
def query(conn: connection, q: str, vals: tuple = ()) -> Iterator[tuple]:
with conn:
with conn.cursor() as cur:
cur.execute(q, vals)
for record in cur:
yield record
def __init__(self, pg_conn: _connection):
self.cursor = pg_conn.cursor()
self._counter = 0
def db_disconnection(con: connection) -> None:
try:
con.close()
print('PostgreSQL connection is closed')
except (Exception, psycopg2.DatabaseError) as e:
print('Error while disconnecting PostgreSQL:', e)
def drop_db(conn: connection):
drop_db_impl(conn)
conn.commit()
def get_thresholds(connection: extensions.connection) -> Iterator[Threshold]:
sql = '''
select
attr.column_name,
threshold.term_name,
threshold.threshold_uuid,
nam_locn.nam_locn_name,
condition.start_date,
condition.end_date,
condition.is_date_constrained,
condition.start_day_of_year,
condition.end_day_of_year,
property.number_value,
property.string_value
from
pdr.condition
join
pdr.threshold on condition.threshold_uuid = threshold.threshold_uuid
join
pdr.property on condition.condition_uuid = property.condition_uuid
join
pdr.attr on attr.attr_id = property.attr_id
join
pdr.nam_locn on pdr.nam_locn.nam_locn_id = pdr.condition.nam_locn_id
where
property.condition_uuid is not null
order by
nam_locn.nam_locn_name
'''
with closing(connection.cursor()) as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
threshold_name = row[0]
term_name = row[1]
threshold_uuid = row[2]
location_name = row[3]
start_date = row[4]
end_date = row[5]
is_date_constrained = row[6]
start_day_of_year = row[7]
end_day_of_year = row[8]
number_value = row[9]
string_value = row[10]
'''
Change term name for soil temp thresholds from 'soilPRTResistance' to 'temp'.
The Avro schema for calibrated PRT data uses the term name 'temp'
and data file term name must match threshold term name to apply QA/QC.
'''
if term_name == 'soilPRTResistance':
term_name = 'temp'
if start_date is not None:
start_date = date_formatter.to_string(start_date)
if end_date is not None:
end_date = date_formatter.to_string(end_date)
if number_value is not None:
number_value = float(number_value)
context: List[str] = get_threshold_context(connection,
threshold_uuid)
threshold = Threshold(threshold_name=threshold_name,
term_name=term_name,
location_name=location_name,
context=context,
start_date=start_date,
end_date=end_date,
is_date_constrained=is_date_constrained,
start_day_of_year=start_day_of_year,
end_day_of_year=end_day_of_year,
number_value=number_value,
string_value=string_value)
yield threshold
def transfer_by_name_tpc(from_connection: connection, from_name: str,
to_connection: connection, to_name: str,
amount: Union[Decimal, int]):
from_connection.tpc_begin(
from_connection.xid(42, 'transaction ID', 'connection 1'))
to_connection.tpc_begin(
to_connection.xid(42, 'transaction ID', 'connection 2'))
try:
transfer_by_name_tpc_impl(from_connection, from_name, to_connection,
to_name, amount)
from_connection.tpc_prepare()
to_connection.tpc_prepare()
except Error:
from_connection.tpc_rollback()
to_connection.tpc_rollback()
raise
except MoneyAmountError:
from_connection.tpc_rollback()
to_connection.tpc_rollback()
raise
else:
from_connection.tpc_commit()
to_connection.tpc_commit()
def drop_tables(cur: cursor, conn: connection) -> None:
for table, query in drop_table_queries:
print(f"Dropping {table} table")
cur.execute(query)
conn.commit()
def get_articles_count(
db_connection: connection,
are_draft_allowed: bool,
are_draft_allowed_for_current_user_id: bool,
current_user_id: int,
are_published_allowed: bool,
are_published_allowed_for_current_user_id: bool,
are_archived_allowed: bool,
are_archived_allowed_for_current_user_id: bool,
status: Optional[str] = None,
) -> int:
cursor = db_connection.cursor(cursor_factory=DictCursor)
has_status_filter = status is not None
cursor.execute(
"""
SELECT
count(*) AS articles_count
FROM (
SELECT
articles.id,
articles.title,
articles.content,
articles.author_id,
article_statuses.name AS status,
articles.created_date
FROM
articles
INNER JOIN
article_statuses
ON articles.status_id = article_statuses.id
WHERE
CASE
-- When DRAFT articles are allowed.
WHEN article_statuses.name = 'DRAFT' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When PUBLISHED articles are allowed.
WHEN article_statuses.name = 'PUBLISHED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When ARCHIVED articles are allowed.
WHEN article_statuses.name = 'ARCHIVED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
ELSE FALSE
END
) AS allowed_articles
WHERE
CASE
-- Has status filter?
WHEN %s
THEN allowed_articles.status = %s
ELSE TRUE
END
""", (
are_draft_allowed,
are_draft_allowed_for_current_user_id,
current_user_id,
are_published_allowed,
are_published_allowed_for_current_user_id,
current_user_id,
are_archived_allowed,
are_archived_allowed_for_current_user_id,
current_user_id,
has_status_filter,
status,
))
result = cursor.fetchone()
cursor.close()
return result['articles_count']
def create_tables(cur: cursor, conn: connection) -> None:
for table, query in create_table_queries:
print(f"Creating {table} table")
cur.execute(query)
conn.commit()
def _get_schema_cursor(conn: connection, schema: str = None) -> cursor:
curs = conn.cursor()
if schema:
curs.execute('SET search_path TO %s', [schema])
return curs
def get_added_sentences(db: connection) -> Dict[int, str]:
""" Return added sentences associated to its level """
with db.cursor() as cur:
cur.execute('select lvl, jpn from added_sentences')
return {lvl: jpn for lvl, jpn in cur.fetchall()}
def _create_users_table(conn: connection):
with conn.cursor() as cursor:
cursor.execute(SQL_CREATE_USERS_TABLE)
def get_articles(
db_connection: connection,
are_draft_allowed: bool,
are_draft_allowed_for_current_user_id: bool,
current_user_id: int,
are_published_allowed: bool,
are_published_allowed_for_current_user_id: bool,
are_archived_allowed: bool,
are_archived_allowed_for_current_user_id: bool,
page: int,
page_size: int,
status: Optional[str] = None,
) -> List[Article]:
cursor = db_connection.cursor(
cursor_factory=DictCursor
)
start_from = (page - 1) * page_size
has_status_filter = status is not None
cursor.execute("""
SELECT
*
FROM (
SELECT
articles.id,
articles.title,
articles.content,
articles.author_id,
article_statuses.name AS status,
articles.created_date
FROM
articles
INNER JOIN
article_statuses
ON articles.status_id = article_statuses.id
WHERE
CASE
-- When DRAFT articles are allowed.
WHEN article_statuses.name = 'DRAFT' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When PUBLISHED articles are allowed.
WHEN article_statuses.name = 'PUBLISHED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
-- When ARCHIVED articles are allowed.
WHEN article_statuses.name = 'ARCHIVED' AND %s
THEN
CASE
-- Are articles allowed for a specific user id?
WHEN %s
THEN articles.author_id = %s
ELSE TRUE
END
ELSE FALSE
END
) AS allowed_articles
WHERE
CASE
-- Has status filter?
WHEN %s
THEN allowed_articles.status = %s
ELSE TRUE
END
ORDER BY allowed_articles.created_date DESC
LIMIT %s
OFFSET %s;
""", (
are_draft_allowed,
are_draft_allowed_for_current_user_id,
current_user_id,
are_published_allowed,
are_published_allowed_for_current_user_id,
current_user_id,
are_archived_allowed,
are_archived_allowed_for_current_user_id,
current_user_id,
has_status_filter,
status,
page_size,
start_from,
))
article_rows = cursor.fetchall()
cursor.close()
return [
Article(
id=article_row['id'],
title=article_row['title'],
content=article_row['content'],
author_id=article_row['author_id'],
status=article_row['status'],
created_date=article_row['created_date'],
)
for article_row
in article_rows
]
