DBStats: New db update routines

Replace updatefix.py with the Updater class.  This streamlines some
of the common tasks and groups database updates by release.  Upgrades
from pre 1.1.x are no longer supported.

7 files changed, 608 insertions, 0 deletions

diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_0_x.py b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_0_x.py
new file mode 100644
index 000000000..54ba07554
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_0_x.py
@@ -0,0 +1,11 @@
+"""
+1_0_x.py
+
+This file should contain updates relevant to the 1.0.x branches ONLY.
+The updates() method must be defined and it should return an Updater object
+"""
+from Bcfg2.Server.Reports.Updater import UnsupportedUpdate
+
+def updates():
+    return UnsupportedUpdate("1.0", 10)
+
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_1_x.py b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_1_x.py
new file mode 100644
index 000000000..26194cb67
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_1_x.py
@@ -0,0 +1,59 @@
+"""
+1_1_x.py
+
+This file should contain updates relevant to the 1.1.x branches ONLY.
+The updates() method must be defined and it should return an Updater object
+"""
+from Bcfg2.Server.Reports.Updater import Updater
+from Bcfg2.Server.Reports.Updater.Routines import updatercallable
+
+from django.db import connection
+import sys
+import Bcfg2.Server.Reports.settings
+from Bcfg2.Server.Reports.reports.models import \
+                TYPE_BAD, TYPE_MODIFIED, TYPE_EXTRA
+
+@updatercallable
+def _interactions_constraint_or_idx():
+    """sqlite doesn't support alter tables.. or constraints"""
+    cursor = connection.cursor()
+    try:
+        cursor.execute('alter table reports_interaction add constraint reports_interaction_20100601 unique (client_id,timestamp)')
+    except:
+        cursor.execute('create unique index reports_interaction_20100601 on reports_interaction (client_id,timestamp)')
+
+
+@updatercallable
+def _populate_interaction_entry_counts():
+    '''Populate up the type totals for the interaction table'''
+    cursor = connection.cursor()
+    count_field = {TYPE_BAD: 'bad_entries',
+                   TYPE_MODIFIED: 'modified_entries',
+                   TYPE_EXTRA: 'extra_entries'}
+
+    for type in list(count_field.keys()):
+        cursor.execute("select count(type), interaction_id " +
+                "from reports_entries_interactions where type = %s group by interaction_id" % type)
+        updates = []
+        for row in cursor.fetchall():
+            updates.append(row)
+        try:
+            cursor.executemany("update reports_interaction set " + count_field[type] + "=%s where id = %s", updates)
+        except Exception:
+            e = sys.exc_info()[1]
+            print(e)
+    cursor.close()
+
+
+def updates():
+    fixes = Updater("1.1")
+    fixes.override_base_version(12) # Do not do this in new code
+
+    fixes.add('alter table reports_interaction add column bad_entries integer not null default -1;')
+    fixes.add('alter table reports_interaction add column modified_entries integer not null default -1;')
+    fixes.add('alter table reports_interaction add column extra_entries integer not null default -1;')
+    fixes.add(_populate_interaction_entry_counts())
+    fixes.add(_interactions_constraint_or_idx())
+    fixes.add('alter table reports_reason add is_binary bool NOT NULL default False;')
+    return fixes
+
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_2_x.py b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_2_x.py
new file mode 100644
index 000000000..22bd937c2
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_2_x.py
@@ -0,0 +1,15 @@
+"""
+1_2_x.py
+
+This file should contain updates relevant to the 1.2.x branches ONLY.
+The updates() method must be defined and it should return an Updater object
+"""
+from Bcfg2.Server.Reports.Updater import Updater
+from Bcfg2.Server.Reports.Updater.Routines import updatercallable
+
+def updates():
+    fixes = Updater("1.2")
+    fixes.override_base_version(18) # Do not do this in new code
+    fixes.add('alter table reports_reason add is_sensitive bool NOT NULL default False;')
+    return fixes
+
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_3_0.py b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_3_0.py
new file mode 100644
index 000000000..b09b06302
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Changes/1_3_0.py
@@ -0,0 +1,26 @@
+"""
+1_3_0.py
+
+This file should contain updates relevant to the 1.3.x branches ONLY.
+The updates() method must be defined and it should return an Updater object
+"""
+from Bcfg2.Server.Reports.Updater import Updater, UpdaterError
+from Bcfg2.Server.Reports.Updater.Routines import AddColumns, \
+        RemoveColumns, RebuildTable
+
+from Bcfg2.Server.Reports.reports.models import Reason, Interaction
+
+
+def updates():
+    fixes = Updater("1.3")
+    fixes.add(RemoveColumns(Interaction, 'client_version'))
+    fixes.add(AddColumns(Reason))
+    fixes.add(RebuildTable(Reason, [
+               'owner', 'current_owner',
+               'group', 'current_group',
+               'perms', 'current_perms',
+               'status', 'current_status',
+               'to', 'current_to']))
+
+    return fixes
+
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Changes/__init__.py b/src/lib/Bcfg2/Server/Reports/Updater/Changes/__init__.py
new file mode 100644
index 000000000..e69de29bb
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Changes/__init__.py
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/Routines.py b/src/lib/Bcfg2/Server/Reports/Updater/Routines.py
new file mode 100644
index 000000000..1d41848e4
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/Routines.py
@@ -0,0 +1,258 @@
+import logging
+from django.db.models.fields import NOT_PROVIDED
+from django.db import connection, DatabaseError, backend, models
+from django.core.management.color import no_style
+from django.core.management.sql import sql_create
+import django.core.management
+
+import Bcfg2.Server.Reports.settings
+
+logger = logging.getLogger(__name__)
+
+def _quote(value):
+    """
+    Quote a string to use as a table name or column
+    """
+    return backend.DatabaseOperations().quote_name(value)
+
+
+def _rebuild_sqlite_table(model):
+    """Sqlite doesn't support most alter table statments.  This streamlines the
+       rebuild process"""
+    try:
+        cursor = connection.cursor()
+        table_name = model._meta.db_table
+
+        # Build create staement from django
+        model._meta.db_table = "%s_temp" % table_name
+        sql, references = connection.creation.sql_create_model(model, no_style())
+        columns = ",".join([_quote(f.column) \
+                               for f in model._meta.fields])
+
+        # Create a temp table
+        [cursor.execute(s) for s in sql]
+
+        # Fill the table
+        tbl_name = _quote(table_name)
+        tmp_tbl_name = _quote(model._meta.db_table)
+        # Reset this
+        model._meta.db_table = table_name
+        cursor.execute("insert into %s(%s) select %s from %s;" % (
+            tmp_tbl_name,
+            columns,
+            columns,
+            tbl_name))
+        cursor.execute("drop table %s" % tbl_name)
+
+        # Call syncdb to create the table again
+        django.core.management.call_command("syncdb", interactive=False, verbosity=0)
+        # syncdb closes our cursor
+        cursor = connection.cursor()
+        # Repopulate
+        cursor.execute('insert into %s(%s) select %s from %s;' % (tbl_name,
+                                                                  columns,
+                                                                  columns,
+                                                                  tmp_tbl_name))
+        cursor.execute('DROP TABLE %s;' % tmp_tbl_name)
+    except DatabaseError:
+        logger.error("Failed to rebuild sqlite table %s" % table_name, exc_info=1)
+        raise UpdaterError
+
+
+class UpdaterRoutineException(Exception):
+    pass
+
+
+class UpdaterRoutine(object):
+    """Base for routines."""
+    def __init__(self):
+        pass
+
+    def __str__(self):
+        return __name__
+
+    def run(self):
+        """Called to execute the action"""
+        raise UpdaterRoutineException
+
+
+
+class AddColumns(UpdaterRoutine):
+    """
+    Routine to add new columns to an existing model
+    """
+    def __init__(self, model):
+        self.model = model
+        self.model_name = model.__name__
+
+    def __str__(self):
+        return "Add new columns for model %s" % self.model_name
+
+    def run(self):
+        try:
+            cursor = connection.cursor()
+        except DatabaseError:
+            logger.error("Failed to connect to the db")
+            raise UpdaterRoutineException
+
+        try:
+            desc = {}
+            for d in connection.introspection.get_table_description(cursor, 
+                    self.model._meta.db_table):
+                desc[d[0]] = d
+        except DatabaseError:
+            logger.error("Failed to get table description", exc_info=1)
+            raise UpdaterRoutineException
+
+        for field in self.model._meta.fields:
+            if field.column in desc:
+                continue
+            logger.debug("Column %s does not exist yet" % field.column)
+            if field.default == NOT_PROVIDED:
+                logger.error("Cannot add a column with out a default value")
+                raise UpdaterRoutineException
+
+            sql = "ALTER TABLE %s ADD %s %s NOT NULL DEFAULT " % (
+                    _quote(self.model._meta.db_table),
+                    _quote(field.column), field.db_type(), )
+            db_engine = Bcfg2.Server.Reports.settings.DATABASES['default']['ENGINE']
+            if db_engine == 'django.db.backends.sqlite3':
+                sql += _quote(field.default)
+                sql_values = ()
+            else:
+                sql += '%s'
+                sql_values = (field.default, )
+            try:
+                cursor.execute(sql, sql_values)
+                logger.debug("Added column %s to %s" % 
+                        (field.column, self.model._meta.db_table))
+            except DatabaseError:
+                logger.error("Unable to add column %s" % field.column)
+                raise UpdaterRoutineException
+
+
+class RebuildTable(UpdaterRoutine):
+    """
+    Rebuild the table for an existing model.  Use this if field types have changed.
+    """
+    def __init__(self, model, columns):
+        self.model = model
+        self.model_name = model.__name__
+
+        if type(columns) == str:
+            self.columns = [columns]
+        elif type(columns) in (tuple, list):
+            self.columns = columns
+        else:
+            logger.error("Columns must be a str, tuple, or list")
+            raise UpdaterRoutineException
+
+
+    def __str__(self):
+        return "Rebuild columns for model %s" % self.model_name
+
+    def run(self):
+        try:
+            cursor = connection.cursor()
+        except DatabaseError:
+            logger.error("Failed to connect to the db")
+            raise UpdaterRoutineException
+
+        db_engine = Bcfg2.Server.Reports.settings.DATABASES['default']['ENGINE']
+        if db_engine == 'django.db.backends.sqlite3':
+            """ Sqlite is a special case.  Altering columns is not supported. """
+            _rebuild_sqlite_table(self.model)
+            return
+
+        if db_engine == 'django.db.backends.mysql':
+            modify_cmd = 'MODIFY '
+        else:
+            modify_cmd = 'ALTER COLUMN '
+
+        col_strings = []
+        for column in self.columns:
+            col_strings.append("%s %s %s" % ( \
+                modify_cmd,
+                _quote(column),
+                self.model._meta.get_field(column).db_type()
+            ))
+
+        try:
+            cursor.execute('ALTER TABLE %s %s' %
+                (_quote(self.model._meta.db_table), ", ".join(col_strings)))
+        except DatabaseError:
+            logger.debug("Failed modify table %s" % self.model._meta.db_table)
+            raise UpdaterRoutineException
+
+
+
+class RemoveColumns(RebuildTable):
+    """
+    Routine to remove columns from an existing model
+    """
+    def __init__(self, model, columns):
+        super(RemoveColumns, self).__init__(model, columns)
+
+
+    def __str__(self):
+        return "Remove columns from model %s" % self.model_name
+
+    def run(self):
+        try:
+            cursor = connection.cursor()
+        except DatabaseError:
+            logger.error("Failed to connect to the db")
+            raise UpdaterRoutineException
+
+        try:
+            columns = [d[0] for d in connection.introspection.get_table_description(cursor, 
+                    self.model._meta.db_table)]
+        except DatabaseError:
+            logger.error("Failed to get table description", exc_info=1)
+            raise UpdaterRoutineException
+
+        for column in self.columns:
+            if column not in columns:
+                logger.warning("Cannot drop column %s: does not exist" % column)
+                continue
+
+            logger.debug("Dropping column %s" % column)
+
+            db_engine = Bcfg2.Server.Reports.settings.DATABASES['default']['ENGINE']
+            if db_engine == 'django.db.backends.sqlite3':
+                _rebuild_sqlite_table(self.model)
+            else:
+                sql = "alter table %s drop column %s" % \
+                    (_quote(self.model._meta.db_table), _quote(column), )
+                try:
+                    cursor.execute(sql)
+                except DatabaseError:
+                    logger.debug("Failed to drop column %s from %s" % 
+                        (column, self.model._meta.db_table))
+                    raise UpdaterRoutineException
+
+
+class UpdaterCallable(UpdaterRoutine):
+    """Helper for routines.  Basically delays execution"""
+    def __init__(self, fn):
+        self.fn = fn
+        self.args = []
+        self.kwargs = {}
+
+    def __call__(self, *args, **kwargs):
+        self.args = args
+        self.kwargs = kwargs
+        return self
+
+    def __str__(self):
+        return self.fn.__name__
+
+    def run(self):
+        self.fn(*self.args, **self.kwargs)
+
+def updatercallable(fn):
+    """Decorator for UpdaterCallable.  Use for any function passed
+       into the fixes list"""
+    return UpdaterCallable(fn)
+
+
diff --git a/src/lib/Bcfg2/Server/Reports/Updater/__init__.py b/src/lib/Bcfg2/Server/Reports/Updater/__init__.py
new file mode 100644
index 000000000..3038e9691
--- /dev/null
+++ b/src/lib/Bcfg2/Server/Reports/Updater/__init__.py
@@ -0,0 +1,239 @@
+from django.db import connection, DatabaseError
+import django.core.management
+import logging
+import pkgutil
+import re
+import sys
+import traceback
+
+from Bcfg2.Server.Reports.reports.models import InternalDatabaseVersion
+from Bcfg2.Server.Reports.Updater.Routines import UpdaterRoutineException, \
+                UpdaterRoutine
+from Bcfg2.Server.Reports.Updater import Changes
+
+logger = logging.getLogger(__name__)
+
+class UpdaterError(Exception):
+    pass
+
+
+class SchemaTooOldError(UpdaterError):
+    pass
+
+
+def _walk_packages(path):
+    """Python 2.4 lacks this routine"""
+    import glob
+    submodules = []
+    for path in __path__:
+        for submodule in glob.glob("%s/*.py" % path):
+            mod = '.'.join(submodule.split("/")[-1].split('.')[:-1])
+            if mod != '__init__':
+                submodules.append((None, mod, False))
+    return submodules
+
+
+def _release_to_version(release):
+    """
+    Build a release base for a version
+
+    Expects a string of the form 00.00
+
+    returns an integer of the form MMmm00
+    """
+    regex = re.compile("^(\d+)\.(\d+)$")
+    m = regex.match(release)
+    if not m:
+        logger.error("Invalid release string: %s" % release)
+        raise TypeError
+    return int("%02d%02d00" % (int(m.group(1)), int(m.group(2))))
+
+
+class Updater(object):
+    """Database updater to standardize updates"""
+
+    def __init__(self, release):
+        self._cursor = None
+        self._release = release
+        try:
+            self._base_version = _release_to_version(release)
+        except:
+            raise UpdaterError
+
+        self._fixes = []
+        self._version = -1
+
+
+    def __cmp__(self, other):
+        return self._base_version - other._base_version
+
+    @property
+    def release(self):
+        return self._release
+
+    @property
+    def version(self):
+        if self._version < 0:
+            try:
+                iv = InternalDatabaseVersion.objects.latest()
+                self._version = iv.version
+            except InternalDatabaseVersion.DoesNotExist:
+                raise UpdaterError
+        return self._version
+
+    @property
+    def cursor(self):
+        if not self._cursor:
+            self._cursor = connection.cursor()
+        return self._cursor
+
+    @property
+    def target_version(self):
+        if(len(self._fixes) == 0):
+            return self._base_version
+        else:
+            return self._base_version + len(self._fixes) - 1
+
+
+    def add(self, update):
+        if type(update) == str or isinstance(update, UpdaterRoutine):
+            self._fixes.append(update)
+        else:
+            raise TypeError
+
+
+    def override_base_version(self, version):
+        """Override our starting point for old releases.  New code should
+           not use this method"""
+        self._base_version = int(version)
+
+
+    @staticmethod
+    def get_current_version():
+        """Queries the db for the latest version.  Returns 0 for a fresh install"""
+
+        if "call_command" in dir(django.core.management):
+            django.core.management.call_command("syncdb", interactive=False, verbosity=0)
+        else:
+            logger.warning("Unable to call syndb routine")
+            raise UpdaterError
+
+        try:
+            iv = InternalDatabaseVersion.objects.latest()
+            version = iv.version
+        except InternalDatabaseVersion.DoesNotExist:
+            version = 0
+
+        return version
+
+
+    def syncdb(self):
+        """Function to do the syncronisation for the models"""
+
+        self._version = Updater.get_current_version()
+        self._cursor = None
+
+
+    def increment(self):
+        """Increment schema version in the database"""
+        if self._version < self._base_version:
+            self._version = self._base_version
+        else:
+            self._version += 1
+        InternalDatabaseVersion.objects.create(version=self._version)
+
+    def apply(self):
+        """Apply pending schema changes"""
+
+        if self.version >= self.target_version:
+            logger.debug("No updates for release %s" % self._release)
+            return
+
+        logger.debug("Applying updates for release %s" % self._release)
+
+        if self.version < self._base_version:
+            start = 0
+        else:
+            start = self.version - self._base_version + 1
+
+        try:
+            for fix in self._fixes[start:]:
+                if type(fix) == str:
+                    self.cursor.execute(fix)
+                elif isinstance(fix, UpdaterRoutine):
+                    fix.run()
+                else:
+                    logger.error("Invalid schema change at %s" % \
+                        self._version + 1)
+                self.increment()
+                logger.debug("Applied schema change number %s: %s" % \
+                    (self.version, fix))
+            logger.info("Applied schema changes for release %s" % self._release)
+        except:
+            logger.error("Failed to perform db update %s (%s): %s" % \
+                (self._version + 1, fix, traceback.format_exc().splitlines()[-1]))
+            raise UpdaterError
+
+
+class UnsupportedUpdate(Updater):
+    """Handle an unsupported update"""
+
+    def __init__(self, release, version):
+        super(UnsupportedUpdate, self).__init__(release)
+        self._base_version = version
+
+    def apply(self):
+        """Raise an exception if we're too old"""
+
+        if self.version < self.target_version:
+            logger.error("Upgrade from release %s unsupported" % self._release)
+            raise SchemaTooOldError
+
+
+def update_database():
+    """method to search where we are in the revision
+    of the database models and update them"""
+    try:
+        logger.debug("Verifying database schema")
+
+        updaters = []
+        if hasattr(pkgutil, 'walk_packages'):
+            submodules = pkgutil.walk_packages(path=Changes.__path__)
+        else:
+            #python 2.4
+            submodules = _walk_packages(Changes.__path__)
+        for loader, submodule, ispkg in submodules:
+            if ispkg:
+                continue
+            try:
+                updates = getattr(
+                    __import__("%s.%s" % (Changes.__name__, submodule), 
+                        globals(), locals(), ['*']),
+                    "updates")
+                updaters.append(updates())
+            except ImportError:
+                logger.error("Failed to import %s" % submodule)
+            except AttributeError:
+                logger.warning("Module %s does not have an updates function" % submodule)
+            except:
+                logger.error("Failed to build updater for %s" % submodule, exc_info=1)
+                raise UpdaterError
+
+        current_version = Updater.get_current_version()
+        logger.debug("Database version at %s" % current_version)
+
+        if current_version > 0:
+            [u.apply() for u in sorted(updaters)]
+            logger.debug("Database version at %s" % Updater.get_current_version())
+        else:
+            target = updaters[-1].target_version
+            InternalDatabaseVersion.objects.create(version=target)
+            logger.info("A new database was created")
+
+    except UpdaterError:
+        raise
+    except:
+        logger.error("Error while updating the database")
+        for x in traceback.format_exc().splitlines():
+            logger.error(x)
+        raise UpdaterError