Connect to a WaveDB table: allow the user to explore all rows and edit the data

wave_to_sql_table.py

# Connect to a WaveDB table: allow the user to explore all rows and edit the data
# To run this app you need to run `./wavedb` first
# The UI and SQL calls in this demo are hardcoded to a specific UI and would need to be updated as the data changes
# There was no attempt to "automatically" create the UI based on a generic SQL table
# This example does not include sorting, filtering, or downloading the dataset
from h2o_wave import main, app, Q, ui, connect


@app('/')
async def serve(q: Q):

    if not q.app.initialized:
        await initialize_app(q)

    if not q.client.initialized:
        await initialize_client(q)

    if q.args.table is not None and len(q.args.table) > 0:
        # Someone has clicked on a row in our dataset
        await edit_issue(q)

    if q.events.table:
        # Someone has interacted with our table object
        if q.events.table.page_change:
            q.client.table_offset = q.events.table.page_change.get('offset', 0)

        # Update the UI with the new data
        await format_rows_wave_table(q)

    if q.args.issue_id_button is not None:
        # Someone wants to update an issue with new information
        await save_issue_edits(q)

    await q.page.save()


async def initialize_app(q: Q):

    # Setup our instance, database, table, and data
    db = await create_database()
    await create_table(db)
    await populate_table(db)

    # Variables to use by all users in our app
    q.app.db = db
    q.app.view_table_limit = 10
    q.app.initialized = True


async def initialize_client(q: Q):

    # Variables to be tracked per browser tab
    q.client.view_table_offset = 0

    # Setup UI for each browser tab
    q.page['meta'] = ui.meta_card(box='', layouts=[ui.layout(
        breakpoint="xs",
        max_width="1200px",
        zones=[ui.zone("header"), ui.zone("body")]
    )])
    q.page["header"] = ui.header_card(
        box="header",
        title="Interacting with Tables",
        subtitle="Editable Table in a SQLite Database",
        image="https://cloud.h2o.ai/logo.svg",
    )
    q.page['table_card'] = ui.form_card(box="body", items=[
        ui.table(
            name='table',
            height="600px",
            columns=[
                ui.table_column(name='issue_id', label='Issue ID', link=True),
                ui.table_column(name='description', label='Description'),
                ui.table_column(name='status', label='Status'),
            ],
            rows=[],
            pagination=ui.table_pagination(
                total_rows=await get_count_table(db=q.app.db),
                rows_per_page=q.app.view_table_limit
            ),
            events=["page_change"]
        )
    ])
    await format_rows_wave_table(q)

    q.client.initialized = True


async def edit_issue(q):

    issue = await query_table(db=q.app.db, issue_id=q.args.table[0])
    issue = issue[0]

    q.page["meta"].side_panel = ui.side_panel(
        title=f"Edit Issue {issue[0]}",
        items=[
            ui.textbox(name="edit_description", label="Description", value=issue[1]),
            ui.choice_group(
                name="edit_status",
                label="Status",
                choices=[ui.choice("Open", "Open"), ui.choice("Closed", "Closed")],
                value=issue[2]
            ),
            ui.button(name="issue_id_button", label="Save", value=str(issue[0]), primary=True)
        ]
    )


async def save_issue_edits(q):
    q.page["meta"].side_panel = None

    await update_table(
        db=q.app.db,
        issue_id=q.args.issue_id_button,
        description=q.args.edit_description,
        status=q.args.edit_status
    )
    await format_rows_wave_table(q)


async def format_rows_wave_table(q: Q):
    rows = await query_table(db=q.app.db, limit=q.app.view_table_limit, offset=q.client.table_offset)
    q.page['table_card'].items[0].table.rows = [ui.table_row(name=str(r[0]), cells=[str(v) for v in r]) for r in rows]


async def create_database():
    # Create a connection to WaveDB SQLite instance
    sqlite_connection = connect()

    # Create a database if it doesn't already exist
    db = sqlite_connection["my_app_database"]

    return db


async def create_table(db):
    # In most cases we would would want to delete all historic data,
    # but for this testing example it's nice to start fresh
    _, err = await db.exec_many(
        "DROP TABLE IF EXISTS issues;",
        """
        CREATE TABLE issues (
            issue_id INTEGER PRIMARY KEY,
            description TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'Open'
        );
        """
    )
    if err:
        raise RuntimeError(f'Failed creating the table: {err}')


async def populate_table(db):

    insert_statements = []
    for i in range(100):
        insert_statements.append((
            "INSERT INTO issues (description, status) VALUES (?, ?)",
            f"Dummy description {i+1}",
            "Closed" if i % 2 == 0 else "Open"
        ))

    _, err = await db.exec_many(*insert_statements)
    if err:
        raise RuntimeError(f'Failed inserting new data: {err}')


async def get_count_table(db):

    rows, err = await db.exec("SELECT COUNT(*) FROM issues;")

    if err:
        raise RuntimeError(f'Failed querying the table: {err}')

    count = rows[0][0]
    return count


async def query_table(db, issue_id=None, limit=None, offset=None):
    where_clause = f"WHERE issue_id={issue_id}" if issue_id is not None else ""
    limit_clause = f"LIMIT {limit}" if limit is not None else ""
    offset_clause = f"OFFSET {offset}" if offset is not None else ""

    rows, err = await db.exec(
        f"""
        SELECT issue_id, description, status
        FROM issues
        {where_clause}
        {limit_clause}
        {offset_clause}
        """
    )

    if err:
        raise RuntimeError(f'Failed querying the table: {err}')

    return rows


async def update_table(db, issue_id, description, status):
    _, err = await db.exec(
        f"""
        UPDATE issues 
        SET description='{description}',
            status='{status}'
        WHERE issue_id={issue_id}
        """
    )
    if err:
        raise RuntimeError(f'Failed updating the table: {err}')