From 2d2595ee625ab56a1c0ae9418f12258ecd123d89 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Fri, 16 Aug 2024 12:25:00 -0500 Subject: [PATCH] Make SQL entry page work --- setup.py | 2 + src/pgwui_sql/templates/sql.mak | 157 ++++++++++++++++++++++++++++---- src/pgwui_sql/views/sql.py | 139 +++++++++++++++++++++++++++- 3 files changed, 281 insertions(+), 17 deletions(-) diff --git a/setup.py b/setup.py index a1dc19d..4c8fb69 100644 --- a/setup.py +++ b/setup.py @@ -61,6 +61,8 @@ def filter_readme(): # dependencies (run-time) # install_requires = [ + 'attrs', + 'wtforms', 'markupsafe', 'pgwui_common==' + version, 'psycopg', diff --git a/src/pgwui_sql/templates/sql.mak b/src/pgwui_sql/templates/sql.mak index de5c6c0..713e537 100644 --- a/src/pgwui_sql/templates/sql.mak +++ b/src/pgwui_sql/templates/sql.mak @@ -30,10 +30,10 @@ <%! from pgwui_common.path import asset_abspath - auth_base_mak = asset_abspath('pgwui_common:templates/auth_base.mak') + db_base_mak = asset_abspath('pgwui_common:templates/db_base.mak') %> -<%inherit file="${auth_base_mak}" /> +<%inherit file="${db_base_mak}" /> <%block name="title">${pgwui['pgwui_sql']['menu_label']} <%block name="meta_keywords"> @@ -55,24 +55,149 @@ <%block name="page_heading"> Execute SQL -<%/block> + -<%def name="example_row(tab_index)"> +<%def name="sql_row(tab_index)"> + + <%self.lib:td_label for_id="sql_id">SQL + - <%self.lib:td_label for_id="example_id">Example - <%self.lib:td_input tab_index="${tab_index}"> - + <%self.lib:td_input tab_index="${tab_index}" colspan="2"> + +<%def name="hidden_vars()"> + <%parent:hidden_vars> + + + + +<%def name="submit(tab_index)"> + + <% tab_index.inc() %> + + +<%def name="table_rows(tab_index)"> + <%parent:table_rows tab_index="${tab_index}" args="tab_index"> + ## A blank table row for spacing + + <% sql_row(tab_index) %> + + + +<%def name="render_heading(headings)"> + + + % for heading in headings: + ${heading} + % endfor + + + + +<%def name="render_row(data)"> + + % for item in data: + ${item} + % endfor + + + +<%def name="result_table(rows=[], status=[])"> + ## Passing the result rows and processing them here avoids duplicating + ## the results in RAM. + + ${caller.body()} + + % for row in rows: + ${self.render_row(row.data)} + % endfor + +
+

+ % for status_row in status: + % if not loop.first: +
+ % endif + ${status_row.data} + % endfor +

+ + +<%def name="sql_error()"> +

${caller.body()}

+ + +<%def name="render_results()"> + <% + if not result_rows: + return STOP_RENDERING + heading = None + command_result = [] + status_result = [] + %> + % for result_row in result_rows: + <% type = result_row.type %> + % if type == 'data': + <% command_result.append(result_row) %> + % elif type == 'status': + <% status_result.append(result_row) %> + % else: + % if heading: + <%self:result_table + rows="${command_result}" status="${status_result}"> + ${self.render_heading(heading.data)} + + + <% + command_result = [] + status_result = [] + %> + % endif + % if type == 'heading': + <% heading = result_row %> + % elif type == 'error': + <%self:sql_error> + ${result_row.data} + + % endif + % endif + % endfor + + % if heading: + <%self:result_table + rows="${command_result}" status="${status_result}"> + ${self.render_heading(heading.data)} + + % endif + + +<%def name="result_form(tab_index)"> +
+
+ <%self:hidden_vars /> +
+ +

+ + <% tab_index.inc() %> +

+
+ + <% tab_index = self.attr.TabIndex() %> -<%self:main_form tab_index="${tab_index}" args="tab_index"> - ${example_row(tab_index)} - +% if result_rows: + ${render_results()} + ${result_form(tab_index)} +% else: + ${self.main_form(tab_index)} +% endif diff --git a/src/pgwui_sql/views/sql.py b/src/pgwui_sql/views/sql.py index 4d74d21..8a64811 100644 --- a/src/pgwui_sql/views/sql.py +++ b/src/pgwui_sql/views/sql.py @@ -18,6 +18,8 @@ # from pyramid.view import view_config +from wtforms.fields import TextAreaField +import attrs import logging import pgwui_core.core @@ -33,10 +35,143 @@ sql_ex.ExampleOnOffAskError('42') log = logging.getLogger(__name__) -class SQLHandler(pgwui_core.core.UploadHandler): +@attrs.define(slots=False) +class SQLInitialPost(pgwui_core.forms.UserInitialPost): + sql = attrs.field(default='') + + +class SQLWTForm(pgwui_core.forms.AuthWTForm): + '''The wtform used to connect to the db to execute SQL.''' + # We don't actually use the labels, wanting the template to + # look (and render) like html, but I'll define them anyway + # just to keep my hand in. + sql = TextAreaField('SQL:') + + +@attrs.define(slots=False) +class SQLForm(pgwui_core.forms.UploadFormBaseMixin, + pgwui_core.forms.AuthLoadedForm): + ''' + Acts like a dict, but with extra methods. + + Attributes: + uh The UploadHandler instance using the form + ''' + def read(self): + ''' + Read form data from the client + ''' + + # Read parent's data + super().read() + + # Read our own data + self['sql'] = self._form.sql.data + + def write(self, result, errors): + ''' + Produces the dict pyramid will use to render the form. + ''' + response = super().write(result, errors) + response['sql'] = self['sql'] + return response + + +@attrs.define() +class SQLResult(): pass +@attrs.define() +class ExecuteSQL(pgwui_core.core.DataLineProcessor): + ''' + Attributes: + request A pyramid request instance + uf A GCUploadForm instance + session A pyramid session instance + ue + uh UploadHandler instance + cur + ''' + def eat(self, udl): + ''' + Execute a series of SQL statements. + The result goes into the upload handler (uh.sql_results), + interleaving errors with output. + + udl An UploadDataLine instance, contains all the sql statements + ''' + cur = self.cur + cur.execute(self.uf.data) + + nextset = True + while nextset is True: + while (row := cur.fetchone()) is not None: + sql_result = SQLResult() + sql_result.build(cur, row) + self.uh.sql_results.append(sql_result) + nextset = cur.nextset() + + +@attrs.define(slots=False) +class SQLHandler(pgwui_core.core.SessionDBHandler): + ''' + Attributes: + request A pyramid request instance + uf A SQLForm instance + session A pyramid session instance + ue + cur + ''' + sql_results = attrs.field(factory=list) + + def make_form(self): + ''' + Make the upload form needed by this handler. + ''' + return SQLForm().build(self, ip=SQLInitialPost(), fc=SQLWTForm) + + def deliver_sql(self): + return self.uf['sql'] + + def get_data(self): + '''Return thunks that delivers data, but we only need one thunk + because processing is done by the DataLineProcessor (SQLExecute + ''' + return (self.deliver_sql(),) + + def write(self, result, errors): + ''' + Setup dict to render resulting html form + + Returns: + Dict pyramid will use to render the resulting form + Reserved keys: + errors A list of UploadError exceptions. + report_success Boolean. Whether the copy succeeded. + ''' + response = super().write(result, errors) + + response['report_success'] = (not response['errors'] + and self.uf['action'] != '') + + return response + + def factory(self, ue): + '''Make a db loader function from an UploadEngine. + + Input: + + Side Effects: + Assigns: self.ue, self.cur + And, lots of changes to the db + ''' + + super().factory(ue) + + return ExecuteSQL(ue, self) + + @view_config(route_name='pgwui_sql', renderer='pgwui_sql:templates/sql.mak') @auth_base_view @@ -49,6 +184,8 @@ def sql_view(request): response.setdefault('pgwui', dict()) response['pgwui']['pgwui_sql'] = settings['pgwui']['pgwui_sql'] + response['result_rows'] = uh.sql_results + if response['report_success']: if pgwui_core.utils.is_checked(response['csv_checked']): upload_fmt = 'CSV' -- 2.34.1