vpackager / src / vpackager / dbutils.py

#!/usr/bin/env python

#    This file is part of vpackager.
#
#    vpackager is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License v2 as published by
#    the Free Software Foundation.
#
#    vpackager 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 General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with vpackager.  If not, see <http://www.gnu.org/licenses/>.

import sqlite3
import os
import preferences

__author__ = 'M0E-lnx'
__author_email__ = 'moc.liamg@xnl.E0M'[::-1]
__version__ = '0.1'

dbpath = os.path.join(preferences.VPConf().GetSetting('database_home'),
                      'vpackager.db')
#dbpath = '/tmp/vpackager.db'

class dbase(object):
    """ Main database class. All operations to the job queue or history are
    done via this class. The database must be closed on exit"""
    def __init__(self, path = dbpath):
        self._dbpath = path
        self.history_observers = []
        self.queue_observers = []
        self.db = None
        self.cursor = None

    def add_observer(self, obj, table='queue'):
        """Addn an observer to be notified of any changes to the dbase"""
        if table == 'queue':
            self.queue_observers.append(obj)
        else:
            self.history_observers.append(obj)
        return

    def notify(self, data, group='all'):
        """Notify all observer of the changes that just happened"""
        if group == 'all':
            for watcher in self.queue_observers:
                watcher(data)
            for watcher in self.history_observers:
                watcher(data)
        elif group == 'queue':
            for watcher in self.queue_observers:
                watcher(data)
        elif group == 'history':
            for watcher in self.history_observers:
                watcher(data)

    def _open(self):
        """ Internal method that will be ussed to wrap the connection
        to single instances and then will be immediately closed.
        Returns a tuple o f(dbase, cursor)"""
        if self.db:
            try:
                self.db.commit()
                self.db.close()
            except:
                pass

        self.db = sqlite3.connect(self._dbpath)
        return (self.db, self.db.cursor())

    def close(self):
        """ Completely close the database to save all data """
        try:
            self.cursor.close()
            self.db.commit()
            self.db.close()
        except:
            pass

    def verify_tables(self):
        """Verify that the essential tables for the vpackages environemtn
        actually exist"""
        db, cursor = self._open()
        cursor.execute('''select name from sqlite_master where
                type="table" and name != "sqlite_sequence"''')
        res = cursor.fetchall()
        #print ''.join(res)
        return res[0][0] not in ('queue', 'history')

    def _cleanup_history(self):
        """ Make sure all packages that did not finish correctly are marked
        as failed"""
        db, cursor = self._open()
        cursor.execute('''UPDATE history SET result="Fail" WHERE result="Running"''')
        db.commit()
        self.close()
        self.notify(self.GetHistory(), group='history')

    def CreateTables(self):
        """Create the essential tables for the vpackager environment. Should
        only need to be ran once"""
        db,cursor = self._open()
        cursor.execute('''
        create table queue (id integer primary key autoincrement,
        app text,
        ver text,
        srcURI text,
        descURI text,
        release text,
        type text,
        install text,
        config_options text,
        doinstURI text,
        patches text)''')
        db.commit()

        cursor.execute('''
        create table history(id integer primary key,
        app text, ver text, srcURI text, descURI text,
        release text, type text, config_options text,
        doinstURI text, patches text, buildstart timestamp,
        buildend timestamp, stdout text, result text)''')
        db.commit()

        return self.close()

    def NewJob(self, app, ver, srcURI, descURI, release, _type,
            install, config_options, doinstURI, patches):
        """ Add a new job to the job queue"""
        t = (app, ver, srcURI, descURI, release, _type, install,
             config_options, doinstURI,patches)
        db, cursor = self._open()
        cursor.execute('''
        insert into queue (app, ver, srcURI, descURI, release, type,
        install,config_options, doinstURI,
        patches) values (?,?,?,?,?,?,?,?,?,?)''', t)
        db.commit()
        self.notify(data = self.GetQueue(), group='queue')
        return self.close()

    def UpdateJob(self, _id, app, ver, srcURI, descURI, release,
                  _type, install, config_options, doinstURI, patches):
        """ Update job information on an existing job"""
        db, cursor = self._open()
        t = (app, ver, srcURI, descURI, release, _type, install,
             config_options, doinstURI, patches, _id)
        try:
            cursor.execute('''
                UPDATE queue set app=?, ver=?, srcURI=?, descURI=?,
                release=?, type=?, install=? , config_options=?,
                doinstURI=?, patches=? WHERE id=?''', t)
            db.commit()
            self.notify(self.GetQueue(), group='queue')
        except:
            return True

    def DeleteJob(self, job_id):
        """ Removes job_id from the job queue"""
        db, cursor = self._open()
        t = (job_id,)
        cursor.execute('''
        delete from queue where id=?''',t)
        db.commit()
        self.notify(data = self.GetQueue(), group='queue')
        return self.close()

    def LogJobStart(self, job_id):
        """ Moves a job from the queue to the history and adds the timestamp to
        the corresponding to the time the job started"""
        db, cursor = self._open()
        t = (job_id,)
        cursor.execute('''
        select id, app, ver, srcURI, descURI, release, type, config_options,
        doinstURI from queue where id=?''',t)
        res = cursor.next()
        cursor.execute('''
        insert into history (id, app, ver, srcURI, descURI, release, type,
        config_options, doinstURI, buildstart, result)
        values (?,?,?,?,?,?,?,?,?,datetime(CURRENT_TIMESTAMP, 'localtime'), 'Running')''', res)
        cursor.execute('delete from queue where id=?', t)
        db.commit()
        # Notify the observers of the changes.
        self.notify(data = self.GetHistory(), group='history')
        self.notify(data = self.GetQueue(), group='queue')
        return self.close()

    def LogJobEnd(self, job_id, stdoutpath, result):
        """ Update the job history to log timestamp when the job ended and the
        result of the build"""
        db, cursor = self._open()
        t = (stdoutpath, result, job_id)
        cursor.execute('''
            UPDATE history SET buildend=datetime(CURRENT_TIMESTAMP, 'localtime'),
            stdout=?, result=? WHERE id=?''', t)
        db.commit()
        self.notify(data = self.GetHistory(), group='history')
        self.notify(data = self.GetQueue(), group='queue')
        return self.close()

    def GetJobProp(self, job_id, prop):
        """ Returns job_id's property (prop) """
        db, cursor = self._open()
        cursor.execute('''select % from queue where id=%s'''% (prop,
            job_id))
        try:
            ret = cursor.next()
        except StopIteration:
            ret = None
        if ret:
            return ret[0]
        self.close()
        return ret

    def GetTaskProp(self, job_id, prop):
        """ Return job_id's (prop) property on a performed task. This
        information comes from the build history"""
        db, cursor = self._open()
        cursor.execute('''select %s from history where id=%s'''% (prop,
            job_id))
        try:
            ret = cursor.next()
        except StopIteration:
            ret = None

        if ret:
            return ret[0]
        self.close()
        return ret

    def GetTaskDetails(self, job_id):
        """ Returns all information from the job history about job_id in the
        form of a dictionary"""
        db, cursor = self._open()
        retval = {}
        t = (job_id,)
        cursor.execute('''select * from history where id=?''',t)
        try:
            ret = cursor.next()
        except StopIteration:
            ret = None
        if ret:
            retval['id'] = ret[0]
            retval['app'] = ret[1]
            retval['ver'] = ret[2]
            retval['srcURI'] = ret[3]
            retval['descURI'] = ret[4]
            retval['release'] = ret[5]
            retval['type'] = ret[6]
            retval['config'] = ret[7]
            retval['doinstURI'] = ret[8]
            retval['patches'] = ret[9]
            retval['buildstart'] = ret[10]
            retval['buildend'] = ret[11]
            retval['stdout'] = ret[12]
            retval['result'] = ret[13]
        else:
            retval = {}
        self.close()

        return retval

    def GetJobDetails(self, job_id):
        """ Returns all the information from the job queue about job_id in the
        form of a dictionary"""
        db,cursor = self._open()
        retval = {}
        t = (job_id,)
        cursor.execute('select * from queue where id=?',t)
        try:
            ret = cursor.next()
        except StopIteration:
            ret = None

        if ret:
            retval['id'] = ret[0]
            retval['app'] = ret[1]
            retval['ver'] = ret[2]
            retval['srcURI'] = ret[3]
            retval['descURI'] = ret[4]
            retval['release'] = ret[5]
            retval['type'] = ret[6]
            retval['install'] = ret[7]
            retval['config'] = ret[8]
            retval['doinstURI'] = ret[9]
            retval['patches'] = ret[10]
        else:
            retval = None
        self.close()

        return retval

    def GetNextJob(self):
        """ Find the next job in line to be built.
        Returns the jobid for the next job"""
        db, cursor = self._open()
        cursor.execute('select id from queue')
        retval = None
        try:
            ret = cursor.next()
            retval = ret[0]
        except StopIteration:
            ret = None
        self.close()
        return retval

    def RefreshQueue(self):
        """ Forces the GUI to refresh the queue list"""
        return self.notify(self.GetQueue(), group='queue')

    def GetQueue(self):
        """ Returns a list of all pending jobs """
        db, cursor = self._open()
        cursor.execute('''select id, app, ver, release from queue''')
        ret = cursor.fetchall()
        self.close()
        return ret

    def RefreshHistory(self):
        """ Forces a push to refresh the GUI representation of the history"""
        return self.notify(self.GetHistory(), group='history')

    def GetHistory(self):
        """ Returns alist of all the processed jobs in the job history"""
        db, cursor = self._open()
        cursor.execute('''
        select id, app, ver, release, buildstart, buildend, result from history''')

        ret = cursor.fetchall()
        self.close()
        return ret

    def FilterHistory(self, filter):
        """ Filters the history to show only the items whose result matches
        the filter"""
        db, cursor = self._open()
        if filter is "all":
            self.close()
            return self.notify(self.GetHistory(), group='history')
        elif filter in ('success, Success, successful'):
            sql = '''select id, app, ver, release, buildstart, buildend,
            result from history where result="Success"'''
        elif filter in ('failed', 'Failed'):
            sql = ''' select id, app, ver, release, buildstart, buildend,
            result from history where result="Fail"'''
        else:
            self.close()
            return self.notify(self.GetHistory(), group='history')
        cursor.execute(sql)
        ret = cursor.fetchall()
        self.close()
        return self.notify(ret, group='history')

    def SearchHistoryByApp(self, appname=''):
        """ Search the job history for app named appname"""
        db, cursor = self._open()
        sql = '''SELECT id, app, ver, release, buildstart, buildend, result
        FROM history WHERE app LIKE "''' + appname + '%"'
        cursor.execute(sql)
        ret = cursor.fetchall()
        self.close()
        return self.notify(ret, group='history')

    def WipeHistory(self):
        """ Deletes the entire job history. Only use after confirming with the
        user"""
        db, cursor = self._open()
        cursor.execute('''delete from history where id > 0''')
        self.close()
        self.notify(data=self.GetHistory(), group='history')
        return
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.