libs.utils.dbutil

  1"""
  2libs/utils/dbutil.py
  3"""
  4
  5import sqlite3
  6from importlib.resources import files
  7from typing import TYPE_CHECKING, Union
  8
  9if TYPE_CHECKING:
 10    from pathlib import Path
 11
 12
 13def connection(database_path: Union["Path", str]) -> sqlite3.Connection:
 14    """DB接続共通処理
 15
 16    Args:
 17        database_path (Union[Path, str]): データベースファイル
 18
 19    Returns:
 20        sqlite3.Connection: オブジェクト
 21    """
 22
 23    conn = sqlite3.connect(
 24        database=f"file:{database_path}",
 25        # detect_types=sqlite3.PARSE_DECLTYPES,
 26        uri=True
 27    )
 28    conn.row_factory = sqlite3.Row
 29
 30    return conn
 31
 32
 33def query(keyword: str) -> str:
 34    """SQLクエリを返す
 35
 36    Args:
 37        keyword (str): SQL選択キーワード
 38
 39    Raises:
 40        ValueError: 未定義のキーワード
 41
 42    Returns:
 43        str: SQL文
 44    """
 45
 46    sql_tables: dict[str, str] = {
 47        # テーブル作成
 48        "CREATE_TABLE_MEMBER": "table/member.sql",
 49        "CREATE_TABLE_ALIAS": "table/alias.sql",
 50        "CREATE_TABLE_TEAM": "table/team.sql",
 51        "CREATE_TABLE_RESULT": "table/result.sql",
 52        "CREATE_TABLE_REMARKS": "table/remarks.sql",
 53        "CREATE_TABLE_WORDS": "table/words.sql",
 54
 55        # VIEW作成
 56        "CREATE_VIEW_INDIVIDUAL_RESULTS": "view/individual_results.sql",
 57        "CREATE_VIEW_GAME_RESULTS": "view/game_results.sql",
 58        "CREATE_VIEW_GAME_INFO": "view/game_info.sql",
 59        "CREATE_VIEW_REGULATIONS": "view/regulations.sql",
 60
 61        # 情報取得
 62        "GAME_INFO": "game.info.sql",
 63        "MEMBER_INFO": "member.info.sql",
 64        "TEAM_INFO": "team.info.sql",
 65        "REMARKS_INFO": "remarks.info.sql",
 66        "SUMMARY_GAMEDATA": "summary/gamedata.sql",
 67        "SUMMARY_DETAILS": "summary/details.sql",
 68        "SUMMARY_DETAILS2": "summary/details2.sql",
 69        "SUMMARY_RESULTS": "summary/results.sql",
 70        "SUMMARY_TOTAL": "summary/total.sql",
 71        "SUMMARY_VERSUS_MATRIX": "summary/versus_matrix.sql",
 72        "RANKING_AGGREGATE": "ranking/aggregate.sql",
 73        "RANKING_RESULTS": "ranking/results.sql",
 74        "RANKING_RECORD_COUNT": "ranking/record_count.sql",
 75        "RANKING_RATINGS": "ranking/ratings.sql",
 76        "REPORT_PERSONAL_DATA": "report/personal_data.sql",
 77        "REPORT_COUNT_DATA": "report/count_data.sql",
 78        "REPORT_MONTHLY": "report/monthly.sql",
 79        "REPORT_RESULTS_LIST": "report/results_list.sql",
 80        "REPORT_WINNER": "report/winner.sql",
 81        "REPORT_MATRIX_TABLE": "report/matrix_table.sql",
 82        "REPORT_COUNT_MOVING": "report/count_moving.sql",
 83
 84        #
 85        "RESULT_INSERT": "general/result_insert.sql",
 86        "RESULT_UPDATE": "general/result_update.sql",
 87        "RESULT_DELETE": "general/result_delete.sql",
 88
 89        #
 90        "REMARKS_SELECT": "general/remarks_select.sql",
 91        "REMARKS_INSERT": "general/remarks_insert.sql",
 92        "REMARKS_DELETE_ALL": "general/remarks_delete_all.sql",
 93        "REMARKS_DELETE_ONE": "general/remarks_delete_one.sql",
 94        "REMARKS_DELETE_COMPAR": "general/remarks_delete_compar.sql",
 95
 96        #
 97        "SELECT_ALL_RESULTS": "general/select_all_results.sql",
 98        "SELECT_GAME_RESULTS": "general/select_game_results.sql",
 99    }
100
101    if (query_path := sql_tables.get(keyword)):
102        with open(str(files("files.queries").joinpath(query_path)), "r", encoding="utf-8") as queryfile:
103            return str(queryfile.read()).strip()
104    else:
105        raise ValueError(f"Unknown keyword: {keyword}")
106
107
108def table_info(conn: sqlite3.Connection, table_name: str) -> dict:
109    """テーブルのスキーマを取得して辞書で返す
110
111    Args:
112        conn (sqlite3.Connection): オブジェクト
113        table_name (str): テーブル名
114
115    Returns:
116        dict: スキーマ
117    """
118
119    rows = conn.execute(f"pragma table_info('{table_name}');")
120    schema = {row["name"]: dict(row) for row in rows.fetchall()}
121
122    return schema