From a0ee8cbf3d51b5dc919b9cc930f798e0931e7b32 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Wed, 28 Apr 2021 22:55:11 -0500 Subject: [PATCH] Add old code and make it work --- README.rst | 76 +++- setup.py | 5 +- src/pgwui_copy/VERSION | 2 +- src/pgwui_copy/check_settings.py | 24 +- src/pgwui_copy/exceptions.py | 143 ++++++- src/pgwui_copy/pgwui_copy.py | 33 +- src/pgwui_copy/templates/copy.mak | 171 ++++++-- src/pgwui_copy/utils.py | 30 ++ src/pgwui_copy/views/copy.py | 649 ++++++++++++++++++++++++++++-- tests/test_check_settings.py | 39 +- tests/test_pgwui_copy.py | 64 ++- tests/test_utils.py | 55 +++ tests/views/test_copy.py | 78 ++-- 13 files changed, 1171 insertions(+), 198 deletions(-) create mode 100644 src/pgwui_copy/utils.py create mode 100644 tests/test_utils.py diff --git a/README.rst b/README.rst index 4112bfb..eab421b 100644 --- a/README.rst +++ b/README.rst @@ -1,4 +1,4 @@ -.. Copyright (C) 2018 The Meme Factory, Inc. http://www.karlpinc.com/ +.. Copyright (C) 2018, 2021 The Meme Factory, Inc. http://www.karlpinc.com/ This file is part of PGWUI_Copy. @@ -27,19 +27,83 @@ PGWUI_Copy Short Documentation ------------------- -PGWUI_Copy stands for `PostgreSQL`_ Web User Interface . +PGWUI_Copy stands for `PostgreSQL`_ Web User Interface, Copy a Schema +Between Databases. +A generic WSGI web form for copying schemas between `PostgreSQL`_ +databases. The form presented requests database names, login +credentials, and the name of the schema to be copied. -Usage ------ +Usage and Cautions +------------------ + +PGWUI_Copy is included when PGWUI is installed. + +There is an option to have the schema copy operation delete an already +existing schema, in the target database, having the same name as the +copied schema. Care must be taken when this is done. When the schema +in the target database is dropped all dependent objects, such as the +tables in the dropped schema, are dropped as well. But dependent +objects need not be located in the target schema. Anything outside of +the schema which is dependent upon anything in the schema is dropped +as well. All of the objects deleted may not be within the target +schema. + +At present, the schema copy operation may not be suitable for use when +copying into a production database. The core assumption is that the +target database is read-mostly and will benefit from ``VACUUM FULL`` +defragementation. PGWUI_Copy's schema copy operation is intended to +maximize the performance of the target database and copied schema on a +system with locally attached spinning disks. The intention is to put +the copied data onto contiguous disk sectors, under the assumption +that sectors newly allocated by the OS are likely to be contiguous. +The following steps are taken to accomplish this: + +* The existing schema, if it exists in the target database, is dropped. + +* If an existing schema is dropped, a ``VACUUM FULL`` operation is done + on the target database. + +* The schema is copied from the source database to the target database,. + +* A ``VACUUM ANALYZE`` is done on the target database. + +This series of steps has the following implications: + +* The ``VACUUM FULL`` of the target database returns the space occupied + by deleted rows to the operating system. + + * The ``VACUUM FULL`` operation not only utilizes system resources but + also requires an exclusive lock on each table as it is examined. + This can negatively impact performance. + + * ``INSERT`` and ``UPDATE`` operations in the target database must + obtain space from the operating system. This results in reduced + performance in comparison with re-using space occupied by deleted + or updated rows, which can negatively impact performance in high + volume production environments. + + * The deletion of the old schema from the target database and + the creation of the new do not happen within a single transaction. + Should the restoration of the schema into the target database fail + the result will be a database without the target schema. + +* The ``VACUUM ANALYZE`` on the target database analyzes the entire + database, not just the copied schema. + +* The database credentials supplied to PGWUI_Copy must be sufficient + to perform all operations. + +It is recommended but not required to install PGWUI_Logout when +installing PGWUI_Copy. URL Configuration ----------------- The default URL for PGWUI_Copy looks like -*https://www.example.com/upload*. +*https://www.example.com/copy*. See the PGWUI_Server documentation for how to configure a different URL. @@ -75,6 +139,4 @@ provided by `The Dian Fossey Gorilla Fund `_. -.. _PGWUI_Core: http://pgwui_core.readthedocs.io/ .. _PostgreSQL: https://www.postgresql.org/ -.. _Pyramid: https://trypyramid.com/ diff --git a/setup.py b/setup.py index cda8b7a..30cd98f 100644 --- a/setup.py +++ b/setup.py @@ -133,6 +133,8 @@ setup( 'user interface', 'web', 'web front end', + 'schema', + 'copy', ], # You can just specify the packages manually here if your project is @@ -143,8 +145,9 @@ setup( # Run-time dependencies. install_requires=[ 'markupsafe', - 'pgwui_common', + 'pgwui_common==' + version, 'pyramid', + 'attrs', ], # List additional groups of dependencies here (e.g. development diff --git a/src/pgwui_copy/VERSION b/src/pgwui_copy/VERSION index a918a2a..ac39a10 100644 --- a/src/pgwui_copy/VERSION +++ b/src/pgwui_copy/VERSION @@ -1 +1 @@ -0.6.0 +0.9.0 diff --git a/src/pgwui_copy/check_settings.py b/src/pgwui_copy/check_settings.py index b2216f5..033215b 100644 --- a/src/pgwui_copy/check_settings.py +++ b/src/pgwui_copy/check_settings.py @@ -1,4 +1,4 @@ -# Copyright (C) 2020 The Meme Factory, Inc. http://www.karlpinc.com/ +# Copyright (C) 2020, 2021 The Meme Factory, Inc. http://www.karlpinc.com/ # This file is part of PGWUI_Copy. # @@ -20,29 +20,20 @@ # Karl O. Pinc from pgwui_common import checkset -from . import exceptions as ex PGWUI_COMPONENT = 'pgwui_copy' COPY_SETTINGS = ['menu_label', - # An example setting needing custom validation - 'example_on_off_ask', - ] + 'sensitive_dbs', + 'default_source_db', + 'default_target_db', + 'default_schema', + 'bin', + ] REQUIRED_SETTINGS = [] BOOLEAN_SETTINGS = [] -# Example validation code -def validate_example_on_off_ask(errors, settings): - '''Make sure the values are those allowed - ''' - value = settings.get('literal_column_headings') - if value is None: - return - if value not in ('on', 'off', 'ask'): - errors.append(ex.ExampleOnOffAskError(value)) - - def check_settings(component_config): '''Check that all pgwui_copy specific settings are good. This includes: @@ -58,6 +49,5 @@ def check_settings(component_config): PGWUI_COMPONENT, REQUIRED_SETTINGS, component_config)) errors.extend(checkset.boolean_settings( PGWUI_COMPONENT, BOOLEAN_SETTINGS, component_config)) - validate_example_on_off_ask(errors, component_config) return errors diff --git a/src/pgwui_copy/exceptions.py b/src/pgwui_copy/exceptions.py index eefe450..fcd0fd1 100644 --- a/src/pgwui_copy/exceptions.py +++ b/src/pgwui_copy/exceptions.py @@ -1,4 +1,4 @@ -# Copyright (C) 2020 The Meme Factory, Inc. http://www.karlpinc.com/ +# Copyright (C) 2020, 2021 The Meme Factory, Inc. http://www.karlpinc.com/ # This file is part of PGWUI_Copy. # @@ -19,25 +19,144 @@ # Karl O. Pinc -from pgwui_common import exceptions as common_ex +import markupsafe + +from pgwui_copy import utils from pgwui_core import exceptions as core_ex +# Errors without data files and line numbers +from pgwui_common.exceptions import SetupError as Error -# PGWUI setting related exceptions +# Setup errors +class NoSchemaError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) -class Error(common_ex.Error): - pass +class LiveDBTargetError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class NonAdminUserError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class NoFromDBError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class NoToDBError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class SameDBError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class InvalidSchemaError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class CopyToDefaultError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class SchemaExistsError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) -class ExampleOnOffAskError(Error): - def __init__(self, value): - super().__init__( - 'The "pgwui:PGWUI_Copy:example_on_off_ask" PGWUI setting ' - ' must be "on", "off", "ask", or not present') +class InconsistentDBError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) -# Exceptions that require detail -class ExampleDetailedError(core_ex.PGWUIError): +class SchemaDroppedError(InconsistentDBError): def __init__(self, e, descr='', detail=''): super().__init__(e, descr, detail) + + +class SchemaCopiedError(InconsistentDBError): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +class CopyCommandError(Error): + def __init__(self, e, descr='', detail=''): + super().__init__(e, descr, detail) + + +# Errors copying using pg_dump/pg_restore. + +class CopyOSError(CopyCommandError): + '''The copy of the schema failed with an OSError.''' + def __init__(self, e, ex, descr='', detail=''): + detail = ('

The operating system reports: {0}

' + .format(markupsafe.escape(ex.strerror))) + super().__init__(e, descr, detail) + + +class PGDumpOSError(CopyOSError): + def __init__(self, e, ex, descr=''): + super().__init__(e, ex, descr) + + +class PGRestoreOSError(CopyOSError): + def __init__(self, e, ex, descr=''): + super().__init__(e, ex, descr) + + +class CopyProcessError(CopyCommandError): + '''The process which copies the schema failed.''' + def __init__(self, e, retcode, stderr=''): + descr_ish = ( + '

The process reports: {0}

' + .format(markupsafe.escape(utils.translate_retcode(retcode)))) + detail = f'

The error messages are:

\n{stderr}
' + super().__init__(e, detail=f'{descr_ish}{detail}') + + +class PGDumpError(CopyProcessError): + def __init__(self, e, retcode, stderr=''): + super().__init__(e, retcode, stderr) + + +class PGRestoreError(CopyProcessError): + def __init__(self, e, retcode, stderr=''): + super().__init__(e, retcode, stderr) + + +# Database statement execution errors + +class DropSchemaError(core_ex.DBError): + def __init__(self, pgexc, schema, db): + ''' + pgexc The psycopg2 exception object + ''' + super().__init__( + pgexc, 'drop the ({0}) schema in the ({1}) db'.format(schema, db)) + + +class VacuumFullError(core_ex.DBError): + def __init__(self, pgexc, db): + ''' + pgexc The psycopg2 exception object + ''' + super().__init__( + pgexc, 'VACUUM FULL the ({0}) db'.format(db)) + + +class VacuumAnalyzeError(core_ex.DBError): + def __init__(self, pgexc, db): + ''' + pgexc The psycopg2 exception object + ''' + super().__init__( + pgexc, 'VACUUM ANALYZE the ({0}) db'.format(db)) diff --git a/src/pgwui_copy/pgwui_copy.py b/src/pgwui_copy/pgwui_copy.py index cf5a516..0239c39 100644 --- a/src/pgwui_copy/pgwui_copy.py +++ b/src/pgwui_copy/pgwui_copy.py @@ -1,4 +1,5 @@ -# Copyright (C) 2018, 2020 The Meme Factory, Inc. http://www.karlpinc.com/ +# Copyright (C) 2018, 2020, 2021 The Meme Factory, Inc. +# http://www.karlpinc.com/ # This file is part of PGWUI_Copy # @@ -23,22 +24,44 @@ ''' PGWUI_COMPONENT = 'pgwui_copy' DEFAULT_COPY_ROUTE = '/copy' -DEFAULT_COPY_MENU_LABEL = 'copy -- Description of PGWUI_Copy' +DEFAULT_COPY_MENU_LABEL = 'copy -- Copy a Schema Between Databases' +DEFAULT_BIN = '/usr/bin' -def init_menu(config): +def build_sensitive_dbs(pgwui, copy_settings): + if 'sensitive_dbs' in copy_settings: + s_dbs = copy_settings['sensitive_dbs'] + if isinstance(s_dbs, list): + return s_dbs + return [s_dbs] + return [pgwui.get('default_db', '')] + + +def establish_settings(config): '''Add default menu information into settings when they are not present ''' settings = config.get_settings() pgwui = settings.setdefault('pgwui', dict()) pgwui.setdefault(PGWUI_COMPONENT, dict()) - pgwui[PGWUI_COMPONENT].setdefault( + copy_settings = pgwui[PGWUI_COMPONENT] + + copy_settings.setdefault( 'menu_label', DEFAULT_COPY_MENU_LABEL) + copy_settings.setdefault( + 'default_source_db', pgwui.get('default_db', '')) + copy_settings['sensitive_dbs'] = \ + [db.lower() for db in build_sensitive_dbs(pgwui, copy_settings)] + copy_settings.setdefault( + 'default_target_db', '') + copy_settings.setdefault( + 'default_schema', '') + copy_settings.setdefault( + 'bin', DEFAULT_BIN) def includeme(config): '''Pyramid configuration for PGWUI_Copy ''' - init_menu(config) + establish_settings(config) config.add_route(PGWUI_COMPONENT, DEFAULT_COPY_ROUTE) config.scan() diff --git a/src/pgwui_copy/templates/copy.mak b/src/pgwui_copy/templates/copy.mak index cb3faf3..9bab992 100644 --- a/src/pgwui_copy/templates/copy.mak +++ b/src/pgwui_copy/templates/copy.mak @@ -1,5 +1,5 @@ <%doc> - Copyright (C) 2015, 2018, 2020, 2021 The Meme Factory, Inc. + Copyright (C) 2014, 2015, 2018, 2021 The Meme Factory, Inc. http://www.karlpinc.com/ This file is part of PGWUI_Copy. @@ -18,28 +18,34 @@ License along with this program. If not, see . - Template for generic copy page. + Template for copying a schema between databases Karl O. Pinc This template uses the following variables in it's context: + crfs_token + user + db_changed (used by base.mk) - ... + schema + from_db + to_db + overwrite + force - <%! from pgwui_common.path import asset_abspath - auth_base_mak = asset_abspath('pgwui_common:templates/auth_base.mak') + errors_base_mak = asset_abspath('pgwui_common:templates/errors_base.mak') %> -<%inherit file="${auth_base_mak}" /> +<%inherit file="${errors_base_mak}" /> <%block name="title">${pgwui['pgwui_copy']['menu_label']} <%block name="meta_keywords"> + content="PGWUI copy schema PostgreSQL" /> <%block name="meta_description"> @@ -47,33 +53,138 @@ content="PostgreSQL Web User Interface, Copy a Schema Between Databases" /> + <%block name="action_success"> -

Successfully did copy, - from a file containing ${lines} - lines! -

+

The ${schema} schema has been copied + from the ${from_db} db to the ${to_db} + db!

-

Descriptive Heading

+

Copy A Schema Between Databases

-<%def name="example_row(tab_index)"> - - - - - - + +
+
+ + +
+ + + + + + + + + + + + ## A blank table row for spacing + + + + + + + + + + + + + + + + + + + - - + + +
+ + + +
+ + + +
+ + + +
+ + + +
+ + + +
+ + + +
+
+ +
-<% form_elements = [example_row] - append_elements(form_elements) %> +

+ +

+ +
-${parent.upload_form(form_elements)} +

+* CAUTION: Overwriting an existing schema will +cause deletion of all data and objects outside of the destination +schema which relate to any overwritten content. +

diff --git a/src/pgwui_copy/utils.py b/src/pgwui_copy/utils.py new file mode 100644 index 0000000..6508376 --- /dev/null +++ b/src/pgwui_copy/utils.py @@ -0,0 +1,30 @@ +# Copyright (C) 2015, 2018, 2020, 2021 The Meme Factory, Inc. +# http://www.karlpinc.com/ + +# This file is part of PGWUI_Copy. +# +# This program is free software: you can redistribute it and/or +# modify it under the terms of the GNU Affero General Public License +# as published by the Free Software Foundation, either version 3 of +# the License, or (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public +# License along with this program. If not, see +# . +# +# Utility functions + +import os + + +def translate_retcode(retcode): + '''Translate a subprocess.returncode into a descriptive string.''' + if retcode < 0: + return 'Terminated by signal {0}'.format(-retcode) + else: + return os.strerror(retcode) diff --git a/src/pgwui_copy/views/copy.py b/src/pgwui_copy/views/copy.py index bd43560..42b82af 100644 --- a/src/pgwui_copy/views/copy.py +++ b/src/pgwui_copy/views/copy.py @@ -18,44 +18,643 @@ # . # -from pyramid.view import view_config +import attr + import logging +import subprocess +import tempfile -from pgwui_common.view import auth_base_view +import markupsafe -from pgwui_copy import exceptions as copy_ex +from wtforms import ( + BooleanField, + StringField, +) + +from pyramid.view import view_config + +from pgwui_core.core import ( + UploadEngine, + NoTransactionEngine, + UserInitialPost, + UserWTForm, + CredsLoadedForm, + SessionDBHandler, + LogSQLCommand, + SQLData, + NoOpProcessor, + ExecuteSQL, + format_exception, +) + +from pgwui_core.exceptions import AuthFailError +from pgwui_core.constants import ( + CHECKED, + UNCHECKED, +) + +from pgwui_common.view import errors_base_view + +from pgwui_copy import utils + +from pgwui_copy import exceptions as copy_ex log = logging.getLogger(__name__) +@attr.s +class CopySchemaInitialPost(UserInitialPost): + '''Get values from settings when there's not user-supplied values + in POST + ''' + schema = attr.ib(default=None) + from_db = attr.ib(default=None) + to_db = attr.ib(default=None) + force = attr.ib(default=False) + + def build(self, settings={}): + super().build(settings) + copy_settings = settings['pgwui']['pgwui_copy'] + self.schema = copy_settings['default_schema'] + self.from_db = copy_settings['default_source_db'] + self.to_db = copy_settings['default_target_db'] + + return self + + +class CopySchemaWTForm(UserWTForm): + '''The wtform used to connect to the "gombemi" db to authenticate .''' + # 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. + schema = StringField('Schema To Copy:') + from_db = StringField('From Database:') + to_db = StringField('To Database:') + overwrite = BooleanField('Overwrite Destination Schema:') + force = BooleanField('Allow Copy To Sensitive DBs:') + + +@attr.s +class CopySchemaForm(CredsLoadedForm): + ''' + 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['schema'] = self._form.schema.data.lower().strip() + self['from_db'] = self._form.from_db.data.lower().strip() + self['to_db'] = self._form.to_db.data.lower().strip() + self['overwrite'] = self._form.overwrite.data + self['force'] = self._form.force.data + + def write(self, result, errors): + ''' + Produces the dict pyramid will use to render the form. + ''' + response = super().write(result, errors) + + response['schema'] = self['schema'] + response['from_db'] = self['from_db'] + response['to_db'] = self['to_db'] + + if self['force']: + force_checked = CHECKED + else: + force_checked = UNCHECKED + response['force'] = force_checked + + if self['overwrite']: + overwrite_checked = CHECKED + else: + overwrite_checked = UNCHECKED + response['overwrite'] = overwrite_checked + + return response + + +# Utility functions + +def schema_exists(cur, schema): + '''Does the schema exist?''' + cur.execute('SELECT 1 FROM pg_namespace' + ' WHERE nspname = %s', + (schema,)) + return cur.fetchone() is not None + + +@attr.s +class CopySchemaHandler(SessionDBHandler): + ''' + Abstract class to copy a schema from one db to another. + ''' + def make_form(self): + return CopySchemaForm().build( + self, fc=CopySchemaWTForm, + ip=CopySchemaInitialPost()) + + 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. + db_changed Boolean. Whether the db was changed. + ''' + response = super().write(result, errors) + + response['db_changed'] = (not response['errors'] + and self.uf['action'] != '') + + return response + + def factory(self, ue): + '''Make a db loader function from an UploadEngine. + + Input: + + Side Effects: + Yes, lots. + ''' + return ExecuteSQL(ue, self) + + def render(self, errors, result): + '''Instead of rendering, just our results so we can + decide what to do next. + + Input: + errors List of Error instances + result Db connection result dict + ''' + response = super().render(errors, result) + return (response, errors) + + def to_db_detail(self): + '''Return a string for the log detailing an SQL execution attempt.''' + return ('In database {db}: By user ({user})' + .format(db=self.uf['to_db'], user=self.uf['user'])) + + def log_sql_success(self, msg): + '''Write a success message to the log when executing sql.''' + log.warning('{0}: {1}' + .format(msg, self.to_db_detail())) + + def log_sql_failure(self, msg, ex): + '''Write a failture message to the log when executing sql.''' + log.info('{0}: {1}{2}' + .format(msg, self.to_db_detail(), format_exception(ex))) + + +@attr.s +class FromSchemaHandler(CopySchemaHandler): + '''Handler to check the source schema and do initial validation. + + Attributes: + cur + ''' + settings = attr.ib(default=None) + + def init(self): + super().init() + self.settings = self.request.registry.settings + + def factory(self, ue): + '''Make a db loader function from an UploadEngine. + Having the UploadEngine we can test the db content. + + Input: + + Side Effects: + Yes, lots. + ''' + self.cur = ue.cur + + uf = self.uf + cur = self.cur + + # Is the logged in user a superuser? + user = uf['user'] + cur.execute('SELECT 1 FROM pg_roles' + ' WHERE rolname = %s AND rolsuper', + (user,)) + if cur.fetchone() is None: + ex = copy_ex.NonAdminUserError( + 'Must be a superuser', + ('The supplied user ({0}) is not an administrator' + .format(user))) + self.log_sql_failure('User not a superuser', ex) + raise ex + + schema = uf['schema'] + if not schema_exists(cur, schema): + ex = copy_ex.InvalidSchemaError( + 'Source schema does not exist', + ('The supplied source schema ({0}) does not exist' + .format(schema))) + self.log_sql_failure('Source schema does not exist', ex) + raise ex + + return super().factory(ue) + + def get_data(self): + '''We do all the work validating input so need no data.''' + self.data = () + + def val_input(self): + ''' + Validate input needed beyond that required to connect to the db. + + Returns: + A list of Error instances + ''' + uf = self.uf + errors = [] + + schema = uf['schema'] + + # Do we have the essential basic data? + if schema == '': + ex = copy_ex.NoSchemaError('No schema supplied') + self.log_sql_failure('No schema supplied', ex) + errors.append(ex) + + sensitive_dbs = self.settings['pgwui']['pgwui_copy']['sensitive_dbs'] + if uf['to_db'] == '': + ex = copy_ex.NoToDBError('No to db supplied') + self.log_sql_failure('No to db supplied', ex) + errors.append(ex) + elif uf['to_db'].lower() in sensitive_dbs and not uf['force']: + ex = copy_ex.LiveDBTargetError( + 'Cannot write into a sensitive database', + 'The "Allow ..." box must be checked to copy into ' + 'the "{0}" database.' + .format(uf['to_db'])) + self.log_sql_failure( + 'Aborted: The "Allow..." box must be checked to copy into ' + f'the "{uf["to_db"]}" database', ex) + errors.append(ex) + + if uf['from_db'] == '': + ex = copy_ex.NoFromDBError('No from db supplied') + self.log_sql_failure('No from db supplied', ex) + errors.append(ex) + elif uf['from_db'] == uf['to_db']: + ex = copy_ex.SameDBError( + 'The from db is the same as the to db', + 'Both the from db and the to db are ({0}).' + .format(markupsafe.escape(uf['from_db']))) + self.log_sql_failure('From db and to db are the same', ex) + errors.append(ex) + return errors + + +@attr.s +class DropSchemaHandler(CopySchemaHandler): + ''' + Handler to drop the target schema from the target db. + + (Extra) Attributes: + drop_attempted True when the handler will attempt to drop the + schema. + ''' + drop_attempted = attr.ib(default=False) + + def factory(self, ue): + '''Make a db loader function from an UploadEngine. + Having the UploadEngine we can test the db content. + + Input: + + Side Effects: + Yes, lots. + ''' + cur = ue.cur + schema = self.uf['schema'] + if schema_exists(cur, schema): + if self.uf['overwrite']: + self.drop_attempted = True + return super().factory(ue) + else: + log.info('Abort:' + ' Schema ({0}) already exists in the ({1}) db' + .format(schema, self.uf['to_db'])) + raise copy_ex.SchemaExistsError( + 'The target schema exists', + 'The schema ({0}) exists in the {1} db.' + .format(markupsafe.escape(schema), self.uf['to_db'])) + else: + log.info('Schema ({0}) does not exist in the ({1}) db' + .format(schema, self.uf['to_db'])) + return NoOpProcessor(ue, self) + + def log_dropschema_success(self): + '''Write a success message to the log when dropping the schema.''' + self.log_sql_success('Success dropping schema ({0})' + .format(self.uf['schema'])) + + def log_dropschema_failure(self, ex): + '''Write a failure message to the log when dropping the schema.''' + self.log_sql_failure('Failure dropping schema ({0})' + .format(self.uf['schema']), + ex) + + def log_vacuumfull_success(self): + '''Write a success message to the log when vacuum full succeeds.''' + self.log_sql_success('Successful VACUUM FULL') + + def log_vacuumfull_failure(self, ex): + '''Write a failure message to the log when vacuum full fails.''' + self.log_sql_failure('Failed VACUUM FULL', ex) + + def get_data(self): + ''' + Build and stash the SQL to be executed. + + Returns: + List of SQCommand instances + ''' + uf = self.uf + sql = [] + + schema = uf['schema'] + to_db = uf['to_db'] + + # Drop the schema + sql.append(LogSQLCommand( + 'DROP SCHEMA {0} CASCADE'.format(schema), + (), + lambda ex: copy_ex.DropSchemaError(ex, schema, to_db), + log_success=self.log_dropschema_success, + log_failure=self.log_dropschema_failure)) + + # Vacuum the target db + sql.append(LogSQLCommand( + 'VACUUM FULL', + (), + lambda ex: copy_ex.VacuumFullError(ex, to_db), + log_success=self.log_vacuumfull_success, + log_failure=self.log_vacuumfull_failure)) + + self.data = SQLData(sql) + + +class VacuumHandler(CopySchemaHandler): + ''' + Handler to add a user schema to the test db. + ''' + def log_vacuumanalyze_success(self): + self.log_sql_success('Successful VACUUM ANALYZE') + + def log_vacuumanalyze_failure(self, ex): + self.log_sql_failure('Failed VACUUM ANALYZE', ex) + + def get_data(self): + ''' + Build and stash the SQL to be executed. + + Returns: + List of SQCommand instances + ''' + + uf = self.uf + sql = [] + + to_db = uf['to_db'] + + # Vacuum the target db + sql.append(LogSQLCommand( + 'VACUUM ANALYZE', + (), + lambda ex: copy_ex.VacuumAnalyzeError(ex, to_db), + log_success=self.log_vacuumanalyze_success, + log_failure=self.log_vacuumanalyze_failure)) + + self.data = SQLData(sql) + + +class CheckFromSchemaEngine(UploadEngine): + '''Customize credential failure error message.''' + def authfailerror_factory(self): + orig = super().authfailerror_factory() + return AuthFailError( + ('Unable to login to the ({0}) db' + .format(self.uh.uf['from_db'])), + orig.descr) + + def read_db(self): + '''The handler has two db's, save the right one.''' + self.db = self.uh.uf['from_db'] + + +class ToDBEngine(NoTransactionEngine): + '''Use the to_db to login.''' + def read_db(self): + self.db = self.uh.uf['to_db'] + + +def copy_schema(uf, settings): + '''Copy the desired schema using pg_dump/pg_restore. + + Input: + uf The upload form, holding some of the info we need + to do the copy. + settings The configuration settings, holding some of the info we need + to do the copy. + + Returns: + Exception, or None. + ''' + host = settings['pg_host'] + port = settings['pg_port'] + bin = settings['pgwui_copy']['bin'] + user = uf['user'] + password = uf['password'] + schema = uf['schema'] + from_db = uf['from_db'] + to_db = uf['to_db'] + + # Create the service file. + sfile = tempfile.NamedTemporaryFile(mode='w', + prefix='copy_schema_service_file') + servicefile = sfile.name + # No point in writing the dbname in [to_db] since (with 9.2 anyway) + # pg_restore does not use it. + sfile.writelines( + '[from_db]\n' + 'host={host}\n' + 'port={port}\n' + 'dbname={from_db}\n' + 'user={user}\n' + 'password={password}\n' + '\n' + '[to_db]\n' + 'host={host}\n' + 'port={port}\n' + 'user={user}\n' + 'password={password}\n' + .format(from_db=from_db, + user=user, + password=password, + host=host, + port=port)) + sfile.flush() + + # Run the necessary commands. + log_info = ('User ({0}): From db ({1}): To db ({2}): Schema ({3})' + .format(user, from_db, to_db, schema)) + try: + pg_dump = subprocess.Popen( + ('{0}/pg_dump'.format(bin), + '--format', 'c', '--compress', '0', + '--no-password', + '--schema', schema, + '--blobs'), + bufsize=-1, close_fds=True, + env={'PGSERVICEFILE': servicefile, + 'PGSERVICE': 'from_db'}, + stdout=subprocess.PIPE, + stderr=subprocess.PIPE) + except OSError as ex: + log.warn('Failed to execute pg_dump command: {0}: OS error {1}' + .format(log_info, ex.strerror)) + raise copy_ex.PGDumpOSError('Failed to execute pg_dump command', ex) + + try: + pg_restore = subprocess.Popen( + ('{0}/pg_restore'.format(bin), + '--single-transaction', + '--no-password', + '--dbname', to_db), + bufsize=-1, close_fds=True, + env={'PGSERVICEFILE': servicefile, + 'PGSERVICE': 'to_db'}, + stdin=pg_dump.stdout, + stderr=subprocess.PIPE) + except OSError as ex: + log.warn('Failed to execute pg_restore command: {0}: OS error {1}' + .format(log_info, ex.strerror)) + raise copy_ex.PGRestoreOSError( + 'Failed to execute pg_restore command', ex) + + restore_errors = markupsafe.escape( + pg_restore.communicate()[1].decode(errors='backslashreplace')) + dump_errors = markupsafe.escape( + pg_dump.communicate()[1].decode(errors='backslashreplace')) + + dump_retcode = pg_dump.returncode + if dump_retcode: + log.warn(f'pg_dump process failed: {log_info}:' + f' {utils.translate_retcode(dump_retcode)}:' + f' {dump_errors}') + raise copy_ex.PGDumpError( + ('Failure of pg_dump process to dump into ' + f'the "{from_db}" database'), + dump_retcode, dump_errors) + + restore_retcode = pg_restore.returncode + if restore_retcode: + log.warn(f'pg_restore process failed: {log_info}:' + f' {utils.translate_retcode(restore_retcode)}:' + f' {restore_errors}') + raise copy_ex.PGRestoreError( + ('Failure of pg_restore process to restore into ' + f'the "{to_db}" database'), + restore_retcode, restore_errors) + + log.warn('Successfully copied schema: {0}'.format(log_info)) + + # Get rid of the service file. + sfile.close() + + +def add_pgwui(response, settings): + response['pgwui'] = settings['pgwui'] + return response + + @view_config(route_name='pgwui_copy', renderer='pgwui_copy:templates/copy.mak') -@auth_base_view -def copy_view(request): +@errors_base_view +def copy_schema_view(request): - response = UploadEngine(YOURUploadHandler(request)).run() + # Log that we started doing something. + # We don't have a form yet, so get data right out of POST. + post = request.POST + form_submitted = 'action' in post and post['action'] != '' + if form_submitted: + # The form is submitted. + log.debug('Processing form submission') + # Check that the source schema exists and can be copied. + # This is a normal engine and checks for csrf. + from_handler = FromSchemaHandler(request) + from_handler.init() + response, errors = CheckFromSchemaEngine(from_handler).run() settings = request.registry.settings - response.setdefault('pgwui', dict()) - response['pgwui']['pgwui_copy'] = settings['pgwui']['pgwui_copy'] + if errors: + log.debug('Failed feasiblity test') + return add_pgwui(response, settings) + # Drop schema from the target db. + drop_handler = DropSchemaHandler(request) + drop_handler.init() + response, errors = ToDBEngine(drop_handler).run() if response['db_changed']: - if is_checked(response['csv_checked']): - upload_fmt = 'CSV' + # Copy schema to target db. + try: + copy_schema(from_handler.uf, settings['pgwui']) + except copy_ex.CopyCommandError as ex: + copyerrors = [ex] + if drop_handler.drop_attempted: + copyerrors.append(copy_ex.SchemaDroppedError( + 'Target schema dropped but copy failed', + 'The ({schema}) schema was dropped from the {db} db' + ' but the copy failed so now it no longer' + ' exists in the {db} db.' + .format( + schema=markupsafe.escape( + from_handler.uf['schema']), + db=markupsafe.escape(from_handler.uf['to_db'])))) + response['errors'].extend(copyerrors) + response['db_changed'] = False else: - upload_fmt = 'TAB' - log.info('Successful copy: DB {db}: Table ({table}):' - ' File ({filename}): Lines {lines}:' - ' Format {format}: Upload Null {null}: Null Rep ({null_rep}):' - ' Trim {trim}: By user {user}' - .format(filename=response['filename'], - lines=response['lines'], - format=upload_fmt, - null=is_checked(response['upload_null']), - null_rep=escape_eol(response['null_rep']), - trim=is_checked(response['trim_upload']), - db=response['db'], - table=response['table'], - user=response['user'])) - return response + schema = markupsafe.escape(from_handler.uf['schema']) + to_db = markupsafe.escape(from_handler.uf['to_db']) + from_db = markupsafe.escape(from_handler.uf['from_db']) + user = markupsafe.escape(from_handler.uf['user']) + log.info(f'Successfully copied schema:' + f' Schema ({schema}):' + f' From DB ({from_db}):' + f' To DB ({to_db}):' + f' By user {user}') + + # Vacuum analyze in the target db. + vacuum_handler = VacuumHandler(request) + vacuum_handler.init() + vacuumout, vacuumerrors = ToDBEngine(vacuum_handler).run() + # Merge results of modifying twice + if vacuumerrors: + vacuumerrors.append(copy_ex.SchemaCopiedError( + 'VACUUM ANALYZE failed', + f'VACUUM ANALYZE failed in the {to_db} db,' + ' performance may suffer')) + response['errors'].extend(vacuumerrors) + del vacuumout['errors'] + else: + log.info(f'Vacuum analyzed {to_db}') + response.update(vacuumout) + if form_submitted: + log.debug('Finished processing request') + return add_pgwui(response, settings) diff --git a/tests/test_check_settings.py b/tests/test_check_settings.py index 046827c..6ac2260 100644 --- a/tests/test_check_settings.py +++ b/tests/test_check_settings.py @@ -25,7 +25,6 @@ import pgwui_copy.check_settings as check_settings from pgwui_common import checkset from pgwui_develop import testing -from pgwui_copy import exceptions as ex # Activiate the PGWUI pytest plugin pytest_plugins = ("pgwui",) @@ -53,44 +52,12 @@ mock_boolean_settings = testing.make_mock_fixture( checkset, 'boolean_settings') - -# validate_example_on_off_ask() - -@pytest.mark.parametrize( - ('settings', 'error_class'), [ - ({}, None), - ({'example_on_off_ask': 'on'}, None), - ({'example_on_off_ask': 'off'}, None), - ({'example_on_off_ask': 'ask'}, None), - ({'example_on_off_ask': 'bad'}, - ex.ExampleOnOffAskError)]) -@pytest.mark.unittest -def test_validate_example_on_off_ask(settings, error_class): - '''No error is delivered when there's no setting''' - errors = [] - check_settings.validate_example_on_off_ask(errors, settings) - - if error_class: - assert len(errors) == 1 - assert isinstance( - errors[0], error_class) - else: - assert errors == [] - - -literal_err = 'literal column headings error' -mock_validate_example_on_off_ask = testing.make_mock_fixture( - check_settings, 'validate_example_on_off_ask', - wraps=lambda errors, *args: errors.append(literal_err)) - - # check_settings() @pytest.mark.unittest def test_check_settings(mock_unknown_settings, mock_require_settings, - mock_boolean_settings, - mock_validate_example_on_off_ask): + mock_boolean_settings): '''The setting checking functions are called once, the check_settings() call returns all the errors from each mock. ''' @@ -108,9 +75,7 @@ def test_check_settings(mock_unknown_settings, mock_unknown_settings.assert_called_once mock_require_settings.assert_called_once mock_boolean_settings.assert_called_once - mock_validate_example_on_off_ask.assert_called_once - assert result.sort() == ([literal_err] - + unknown_retval + assert result.sort() == (unknown_retval + require_retval + boolean_retval).sort() diff --git a/tests/test_pgwui_copy.py b/tests/test_pgwui_copy.py index 64c49fe..eaa9208 100644 --- a/tests/test_pgwui_copy.py +++ b/tests/test_pgwui_copy.py @@ -38,40 +38,64 @@ def test_pgwui_copy_is_pgwui_component(pgwui_component_entry_point): assert pgwui_component_entry_point('pgwui_copy') is True -# init_menu() +# build_sensitive_dbs() +@pytest.mark.parametrize( + ('copy_settings', 'expected'), [ + ({}, ['default_db_value']), + ({'sensitive_dbs': 'single db'}, ['single db']), + ({'sensitive_dbs': ['list of', 'dbs']}, ['list of', 'dbs'])]) @pytest.mark.unittest -def test_init_menu_default(): +def test_build_sensitive_dbs(copy_settings, expected): + '''The expected result is returned + ''' + result = pgwui_copy.build_sensitive_dbs({'default_db': 'default_db_value'}, + copy_settings) + assert result == expected + + +mock_build_sensitive_dbs = testing.make_mock_fixture( + pgwui_copy, 'build_sensitive_dbs') + + +# establish_settings() + +@pytest.mark.unittest +def test_establish_settings_default(mock_build_sensitive_dbs): '''The settings get the module's default value when no settings exist ''' + mock_build_sensitive_dbs.return_value = [] with pyramid.testing.testConfig() as config: - pgwui_copy.init_menu(config) + pgwui_copy.establish_settings(config) new_settings = config.get_settings() - assert new_settings['pgwui']['pgwui_copy']['menu_label'] == pgwui_copy.DEFAULT_UPLOAD_MENU_LABEL + assert (new_settings['pgwui']['pgwui_copy']['menu_label'] + == pgwui_copy.DEFAULT_COPY_MENU_LABEL) @pytest.mark.unittest -def test_init_menu_no_default(): +def test_establish_settings_no_default(mock_build_sensitive_dbs): '''The settings keep their value when they exist ''' test_menu_label = 'test label' + mock_build_sensitive_dbs.return_value = [] with pyramid.testing.testConfig() as config: sample_settings = config.get_settings() sample_settings['pgwui'] = dict() sample_settings['pgwui']['pgwui_copy'] = dict() - sample_settings['pgwui']['pgwui_copy']['menu_label'] = test_menu_label + sample_settings['pgwui']['pgwui_copy']['menu_label'] = test_menu_label - pgwui_copy.init_menu(config) + pgwui_copy.establish_settings(config) new_settings = config.get_settings() - assert new_settings['pgwui']['pgwui_copy']['menu_label'] == test_menu_label + assert new_settings['pgwui']['pgwui_copy']['menu_label'] == test_menu_label -mock_init_menu = testing.make_mock_fixture(pgwui_copy, 'init_menu') +mock_establish_settings = testing.make_mock_fixture( + pgwui_copy, 'establish_settings') # includeme() @@ -81,8 +105,25 @@ mock_scan = testing.instance_method_mock_fixture('scan') @pytest.mark.unittest -def test_includeme(mock_init_menu, mock_add_route, mock_scan): - '''init_menu, add_route, and scan are all called +def test_includeme(mock_establish_settings, mock_add_route, mock_scan): + '''establish_settings, add_route, and scan are all called + ''' + with pyramid.testing.testConfig() as config: + mocked_add_route = mock_add_route(config) + mocked_scan = mock_scan(config) + + pgwui_copy.includeme(config) + + mock_establish_settings.assert_called_once() + mocked_add_route.assert_called_once() + mocked_scan.assert_called_once() + + +# Integration tests + +@pytest.mark.integrationtest +def test_integration(mock_add_route, mock_scan): + '''The mocks are called ''' with pyramid.testing.testConfig() as config: mocked_add_route = mock_add_route(config) @@ -90,6 +131,5 @@ def test_includeme(mock_init_menu, mock_add_route, mock_scan): pgwui_copy.includeme(config) - mock_init_menu.assert_called_once() mocked_add_route.assert_called_once() mocked_scan.assert_called_once() diff --git a/tests/test_utils.py b/tests/test_utils.py new file mode 100644 index 0000000..0e60f2f --- /dev/null +++ b/tests/test_utils.py @@ -0,0 +1,55 @@ +# Copyright (C) 2021 The Meme Factory, Inc. http://www.karlpinc.com/ + +# This file is part of PGWUI_Copy. +# +# This program is free software: you can redistribute it and/or +# modify it under the terms of the GNU Affero General Public License +# as published by the Free Software Foundation, either version 3 of +# the License, or (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public +# License along with this program. If not, see +# . +# + +# Karl O. Pinc + +import os +import pytest + +import pgwui_copy.utils as utils +from pgwui_develop import testing + + +# Activiate the PGWUI pytest plugin +pytest_plugins = ("pgwui",) + + +# Mocks +mock_strerror = testing.make_mock_fixture(os, 'strerror') + + +# translate_retcode + +@pytest.mark.unittest +def test_translate_retcode_negative(): + '''A negative retcode returns a string + ''' + result = utils.translate_retcode(-1) + assert isinstance(result, str) + + +@pytest.mark.unittest +def test_transate_retcode_nonnegative(mock_strerror): + '''A non-negative retcode calls strerror + ''' + utils.translate_retcode(1) + mock_strerror.assert_called_once() + + +mock_translate_retcode = testing.make_mock_fixture(utils, 'translate_retcode') diff --git a/tests/views/test_copy.py b/tests/views/test_copy.py index b6e1fb6..83d61e8 100644 --- a/tests/views/test_copy.py +++ b/tests/views/test_copy.py @@ -22,10 +22,6 @@ import pytest from pyramid.testing import DummyRequest -from pyramid.threadlocal import get_current_request, get_current_registry -from pgwui_common.__init__ import includeme as pgwui_common_includeme -from pgwui_core import constants -from pgwui_upload.__init__ import includeme as pgwui_upload_includeme from pgwui_copy.views import copy # Activiate our pytest plugin @@ -33,65 +29,45 @@ pytest_plugins = ("pgwui",) # Constants -CHANGED_RESPONSE = { - 'db': 'somedb', - 'db_changed': True, - 'filename': 'file', - 'lines': 5, - 'null_rep': 'NULL', - 'table': 'sometable', - 'trim_upload': constants.CHECKED, - 'upload_null': constants.CHECKED, - 'user': 'someuser', +TEST_SETTINGS = { + 'pgwui': { + 'host': 'pgwui.example.com', + 'port': '6543', + 'urls': {}, + 'pgwui_copy': { + 'default_schema': 'example_schema', + 'default_source_db': 'example_from_db', + 'default_target_db': 'example_to_db', + 'bin': '/test/bin/path', + } + } } -UNCHANGED_RESPONSE = {'db_changed': False} - # Tests # copy_view() -@pytest.fixture -def return_log_tuples(isolate_upload_view, caplog): - '''Get result and the caplog.record_tuples from the upload_view() call''' - caplog.set_level(logging.DEBUG) - - def run(response): - isolate_upload_view(response) - result = copy.copy_view(get_current_request()) - del result['pgwui'] # Remove variables added by pgwui view decorators - - return (result, caplog.record_tuples) - - return run +# Integration tests +class MockPopen(): + def wait(self): + return True -def test_upload_view_db_not_changed(return_log_tuples): - '''When the db did not change nothing logs''' - response = UNCHANGED_RESPONSE - (result, log_tuples) = return_log_tuples(response) - assert result == response - assert log_tuples == [] +class MockSubprocess(): + def Popen(*args, **kwargs): + return MockPopen() -def test_upload_view_db_changed_csv(return_log_tuples): - '''When the db did change from CSV input something logs''' - response = CHANGED_RESPONSE - response['csv_checked'] = constants.CHECKED - (result, log_tuples) = return_log_tuples(response) +@pytest.mark.integrationtest +def test_copy_schema_view(monkeypatch): - assert result == response - assert ([tup[:2] for tup in log_tuples] - == [('pgwui_upload.views.upload', logging.INFO)]) + monkeypatch.setattr(copy, 'subprocess', MockSubprocess) + request = DummyRequest() + request.registry.settings = TEST_SETTINGS -def test_upload_view_db_changed_no_csv(return_log_tuples): - '''When the db did change from not-CSV input something logs''' - response = CHANGED_RESPONSE - response['csv_checked'] = constants.UNCHECKED - (result, log_tuples) = return_log_tuples(response) + response = copy.copy_schema_view(request) - assert result == response - assert ([tup[:2] for tup in log_tuples] - == [('pgwui_upload.views.upload', logging.INFO)]) + assert isinstance(response, dict) + assert 'pgwui' in response -- 2.34.1