path: root/dbgenerator/database.py

# written 2007 by Markus Ullmann <jokey@gentoo.org>
# License: GPL-2

"""database module"""

import os, sys
from dbgenerator.lrucache import LRUCache
from etc.const_data import ConstData
import re

# We use short variable names!
# pylint: disable-msg=C0103

class DBStateError(Exception):
    """If the DB is in a state that is not expected, we raise this."""
    def __init__(self, value):
        Exception.__init__(self)
        self.value = value
    def __str__(self):
        return repr(self.value)

class SQLPackageDatabase(object):
    """we have to store our stuff somewhere

    subclass and redefine init to provide
    at least self.cursor"""

    # This should match /usr/portage/profiles/arch.list
    arches = frozenset(ConstData.arches['all'])
    # If you change the database structure below, you should
    # increment this number
    schema_version = 73

    # These are used to cache the various relations and
    # avoid more SELECT queries just to find the relations.
    # Relies on the fact that related
    # category/package/version items are visited in close
    # proximity.
    cache_arch = LRUCache(len(arches))
    cache_category = LRUCache(3)
    cache_cp = LRUCache(3)
    cache_cpv = LRUCache(3)

    # These are set by subclasses
    db = None
    cursor = None
    syntax_placeholder = None
    syntax_autoincrement = None

    sql = {}

    tables = ['categories', 'packages', 'metadata',
              'versions', 'verbumps', 'keywords',
              'arches', 'schema_info']

    sql['CREATE_categories'] = """
    CREATE TABLE categories (
        c INTEGER PRIMARY KEY __AI__,
        category VARCHAR(64) UNIQUE
    )"""
    sql['CREATE_packages'] = """
    CREATE TABLE packages (
        cp INTEGER PRIMARY KEY __AI__,
        c INTEGER,
        pn VARCHAR(64),
        UNIQUE (c, pn),
        INDEX (c)
    )"""
    # All of these get longer than 256 chars sometimes
    sql['CREATE_metadata'] = """
    CREATE TABLE metadata (
        cp INTEGER,
        license TEXT,
        homepage TEXT,
        description TEXT,
        changelog TEXT,
        changelog_mtime INT,
        changelog_sha1 CHAR(40),
        manifest_mtime INT,
        manifest_sha1 CHAR(40),
        PRIMARY KEY (cp),
        INDEX (changelog_mtime),
        INDEX (manifest_mtime)
    )"""
    sql['CREATE_versions'] = """
    CREATE TABLE versions (
        cpv INTEGER PRIMARY KEY __AI__,
        cp INTEGER,
        pv VARCHAR(32),
        sha1 CHAR(40),
        mtime INT,
        UNIQUE (cp, pv),
        INDEX (cp),
        INDEX (mtime)
    )"""
    # newpkg == 1 if this is a new package as well as a version bump
    sql['CREATE_verbumps'] = """
    CREATE TABLE verbumps (
        cpv INTEGER PRIMARY KEY,
        mtime INTEGER,
        newpkg TINYINT,
        INDEX (mtime),
        INDEX (newpkg)
    )"""
    sql['CREATE_keywords'] = """
    CREATE TABLE keywords (
        cpv INTEGER,
        a INTEGER,
        mode CHAR(2),
        PRIMARY KEY (cpv, a),
        INDEX (a),
        INDEX (mode)
    )"""
    sql['CREATE_arches'] = """
    CREATE TABLE arches (
        a INTEGER PRIMARY KEY __AI__,
        arch VARCHAR(16),
        UNIQUE(arch)
    )"""
    sql['CREATE_schema_info'] = """
    CREATE TABLE schema_info (
        version INTEGER PRIMARY KEY
    )"""

    sql['INSERT_schema_info'] = """
    INSERT INTO schema_info
    (version)
    VALUES
    (?)
    """
    def create_structure(self):
        """Create all tables in the tables list"""

        for k in self.tables:
            tablesql = self.sql['CREATE_%s' % k]
            self.cursor.execute(tablesql)

        for arch in self.arches:
            self.find_or_create_arch(arch)

        sql = self.sql['INSERT_schema_info']
        self.cursor.execute(sql, (self.schema_version, ))
        self.db.commit()

    # This is the only non-prepared query
    # Because MySQL barfs if you quote the table name here.
    sql['DROP_TABLE'] = 'DROP TABLE IF EXISTS'
    def drop_structure(self):
        """Drop all tables in the tables list"""

        for table in self.tables:
            sql = '%s %s' % (self.sql['DROP_TABLE'], table)
            self.cursor.execute(sql)
        self.db.commit()
    
    sql['DELETE_packages'] = """
    DELETE FROM packages
    WHERE cp = ?
    """
    def del_packages(self, cpi):
        sql = self.sql['DELETE_packages']
        self.cursor.execute(sql, (cpi, ))
    
    sql['DELETE_versions'] = """
    DELETE FROM versions
    WHERE cpv = ?
    """
    def del_versions(self, cpvi):
        sql = self.sql['DELETE_versions']
        self.cursor.execute(sql, (cpvi, ))
    
    sql['DELETE_verbumps'] = """
    DELETE FROM verbumps
    WHERE cpv = ?
    """
    def del_verbumps(self, cpvi):
        sql = self.sql['DELETE_verbumps']
        self.cursor.execute(sql, (cpvi, ))

    sql['DELETE_keywords'] = """
    DELETE FROM keywords
    WHERE cpv = ?
    """
    def del_keywords(self, cpvi):
        sql = self.sql['DELETE_keywords']
        self.cursor.execute(sql, (cpvi, ))
    
    sql['INSERT_keywords'] = """
    INSERT INTO keywords
    (cpv, a, mode)
    VALUES
    (?, ?, ?)
    """
    def add_keywords(self, category, pn, pv, keyword_dict, force_update):
        """Replace keywords for the CPV with the new set"""
        cpv = self.find_cpv(category, pn, pv)
        if cpv is None:
            msg = 'CPV for %s/%s-%s does not exist' \
                    % (category, pn, pv)
            raise DBStateError(msg)
        else:
            # discard mtime and sha1
            (cpv, dummy, dummy) = cpv
        self.del_keywords(cpv)
        sql2 = self.sql['INSERT_keywords']
        for arch in keyword_dict.keys():
            if arch is "*":
                continue
            a = self.find_arch(arch)
            if a is None:
                msg = 'arch:%s for %s/%s-%s does not exist' \
                        % (arch, category, pn, pv)
                raise DBStateError(msg)
            self.cursor.execute(sql2, (cpv, a, keyword_dict[arch]))
        self.db.commit()

    sql['DELETE_metadata'] = """
    DELETE FROM metadata
    WHERE cp = ?
    """
    def del_metadata(self, cpi):
        sql = self.sql['DELETE_metadata']
        self.cursor.execute(sql, (cpi, ))

    sql['INSERT_metadata'] = """
    INSERT INTO metadata
    (cp, homepage, description, license, 
    changelog, changelog_mtime, changelog_sha1,
    manifest_mtime, manifest_sha1
    )
    VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    def add_metadata(self, category, pn,
            description, homepage, pkglicense, 
            changelog, 
            changelog_mtime, changelog_sha1,
            manifest_mtime, manifest_sha1
            ):
        """Replace the metadata for the CP with new metadata"""
        cp = self.find_cp(category, pn)
        if cp is None:
            msg = 'CP for %s/%s does not exist' \
                    % (category, pn)
            raise DBStateError(msg)
        self.del_metadata(cp)
        sql = self.sql['INSERT_metadata']
        params = (cp, homepage, description, str(pkglicense), 
                    changelog, changelog_mtime, changelog_sha1,
                    manifest_mtime, manifest_sha1)
        self.cursor.execute(sql, params)
        self.db.commit()

    sql['SELECT_get_changelog'] = """
    SELECT changelog, changelog_mtime, changelog_sha1
    FROM metadata
    WHERE cp = ?
    """
    def get_changelog(self, cat, pn):
        cp = self.find_cp(cat, pn)
        result = (None, -1, None)
        if cp is None:
            return result
        sql = self.sql['SELECT_get_changelog']
        self.cursor.execute(sql, (cp, ))
        row = self.cursor.fetchone()
        if row:
            result = (unicode(row[0]), int(row[1]), str(row[2]))
        return result
    
    sql['SELECT_get_manifest'] = """
    SELECT manifest_mtime, manifest_sha1
    FROM metadata
    WHERE cp = ?
    """
    def get_manifest(self, cat, pn):
        cp = self.find_cp(cat, pn)
        result = (-1, None)
        if cp is None:
            return result
        sql = self.sql['SELECT_get_manifest']
        self.cursor.execute(sql, (cp, ))
        row = self.cursor.fetchone()
        if row:
            result = (int(row[0]), str(row[1]))
        return result

    sql['INSERT_versions'] = """
    INSERT INTO versions
    (cp, pv, mtime, sha1)
    VALUES (?, ?, ?, ?)
    """
    sql['INSERT_verbumps'] = """
    INSERT INTO verbumps
    (cpv, mtime, newpkg)
    VALUES (?, ?, ?)
    """
    sql['UPDATE_versions'] = """
    UPDATE versions
    SET mtime = ?, sha1 = ?
    WHERE cpv = ?
    """
    def add_version(self, category, pn, pv, mtime, sha1, force_update):
        """Create or update an entry in the versions list for a new CPV"""
        cpv = self.find_cpv(category, pn, pv)
        (cpi, is_cp_created) = self.find_or_create_cp(category, pn)
        if cpv is None:
            sql = self.sql['INSERT_versions']
            self.cursor.execute(sql, (cpi, pv, mtime, sha1))
            cpvi = self.cursor.lastrowid
            # Pick up if this a new package or not
            # False => 0, True => 1
            newpkg = int(is_cp_created)
            # Since this version was not in the database
            # We know it is either a versionbump, or the database is empty
            # This means at the start, the verbump page will be identical to
            # the all page, but that cannot be avoided
            sql = self.sql['INSERT_verbumps']
            self.cursor.execute(sql, (cpvi, mtime, newpkg))
            ret = (cpi, cpvi, mtime, sha1)
        else:
            (cpvi, oldmtime, oldsha1) = cpv
            if (oldmtime != mtime and oldsha1 != sha1) or force_update:
                sql = self.sql['UPDATE_versions']
                self.cursor.execute(sql, (mtime, sha1, cpvi, ))
                ret = (cpi, cpvi, mtime, sha1)
            else:
                ret = (cpi, cpvi, mtime, sha1)
            #    msg = 'CPV for %s/%s-%s @ %d/%s tried to add duplicate data' % \
            #          (category, pn, pv, mtime, sha1)
            #    raise DBStateError(msg)
        self.db.commit()
        return ret

    def find_cpv_mtime_sha1(self, category, pn, pv):
        """Get the mtime and SHA1 for a given CPV from the database"""
        i = self.find_cpv(category, pn, pv)
        timestamp = -1
        sha1 = None
        if i is not None:
            (timestamp, sha1) = (int(i[1]), str(i[2]))
        return (timestamp, sha1)

    sql['SELECT_find_cpv'] = """
    SELECT cpv, mtime, versions.sha1
    FROM versions JOIN packages USING (cp) JOIN categories USING (c)
    WHERE categories.category = ? AND packages.pn = ? AND versions.pv = ?
    """
    def find_cpv(self, category, pn, pv):
        """Find a CPV entry in the database"""
        cachekey = category+pn+pv
        if cachekey in self.cache_cpv:
            return self.cache_cpv[cachekey]
        sql = self.sql['SELECT_find_cpv']
        self.cursor.execute(sql, (category, pn, pv))
        entries = self.cursor.fetchall()
        if entries is None:
            return None
        try:
            vals = entries[0]
            (cpv, mtime, sha1) = vals
            cacheval = (cpv, mtime, sha1)
            self.cache_cpv[cachekey] = cacheval
            return cacheval
        except IndexError:
            return None

    sql['SELECT_child_cpv'] = """
    SELECT cpv
    FROM versions
    WHERE cp=?
    """
    def child_cpv(self, cpi):
        """Return all CPV ids from the database where cp is a given value"""
        sql = self.sql['SELECT_child_cpv']
        self.cursor.execute(sql, (cpi, ))
        entries = self.cursor.fetchall()
        a = []
        if entries is not None:
            for i in entries:
                a.append(i[0])
        return a

    sql['SELECT_all_cpv'] = """
    SELECT cpv
    FROM versions
    """
    def all_cpv(self):
        """Return all CPV ids from the database"""
        sql = self.sql['SELECT_all_cpv']
        self.cursor.execute(sql)
        entries = self.cursor.fetchall()
        a = []
        if entries is not None:
            for i in entries:
                a.append(i[0])
        return a
    
    sql['SELECT_all_cp'] = """
    SELECT cp
    FROM packages
    """
    def all_cp(self):
        """Return all CP ids from the database"""
        sql = self.sql['SELECT_all_cp']
        self.cursor.execute(sql)
        entries = self.cursor.fetchall()
        a = []
        if entries is not None:
            for i in entries:
                a.append(i[0])
        return a

    sql['SELECT_find_category'] = """
    SELECT c
    FROM categories
    WHERE category = ?
    """
    def find_category(self, category):
        """Find a category in the database"""
        cachekey = category
        if cachekey in self.cache_category:
            return self.cache_category[cachekey]
        sql = self.sql['SELECT_find_category']
        self.cursor.execute(sql, (category, ))
        entries = self.cursor.fetchall()
        if entries is None:
            return None
        try:
            c = entries[0][0]
            self.cache_category[cachekey] = c
            return c
        except IndexError:
            return None

    sql['INSERT_categories'] = """
    INSERT INTO categories
    (category)
    VALUES
    (?)
    """
    def find_or_create_category(self, category):
        """Find a category in the database, or create it"""
        c = self.find_category(category)
        created = False
        if c is None:
            cachekey = category
            sql = self.sql['INSERT_categories']
            self.cursor.execute(sql, (category, ))
            self.db.commit()
            c = self.cursor.lastrowid
            self.cache_category[cachekey] = c
            created = True
        return (c, created)

    sql['SELECT_find_arch'] = """
    SELECT a
    FROM arches
    WHERE arch = ?
    """
    def find_arch(self, arch):
        """Find an arch in the database"""
        cachekey = arch
        if cachekey in self.cache_arch:
            return self.cache_arch[cachekey]
        sql = self.sql['SELECT_find_arch']
        self.cursor.execute(sql, (arch, ))
        entries = self.cursor.fetchall()
        if entries is None:
            return None
        try:
            a = entries[0][0]
            self.cache_arch[cachekey] = a
            return a
        except IndexError:
            return None

    sql['INSERT_arches'] = """
    INSERT INTO arches
    (arch)
    VALUES
    (?)
    """
    def find_or_create_arch(self, arch):
        """Find an arch in the database, or create it"""
        a = self.find_arch(arch)
        created = False
        if a is None:
            cachekey = arch
            sql = self.sql['INSERT_arches']
            self.cursor.execute(sql, (arch, ))
            self.db.commit()
            a = self.cursor.lastrowid
            self.cache_arch[cachekey] = a
            created = True
        return (a, created)

    sql['SELECT_find_cp'] = """
    SELECT cp
    FROM packages
    WHERE c = ? AND pn = ?
    """
    def find_cp(self, category, pn):
        """Find a CP in the database"""
        cachekey = category+pn
        if cachekey in self.cache_cp:
            return self.cache_cp[cachekey]
        c = self.find_category(category)
        if c is None:
            return None
        sql = self.sql['SELECT_find_cp']
        self.cursor.execute(sql, (c, pn))
        entries = self.cursor.fetchall()
        if entries is None:
            return None
        try:
            cp = entries[0][0]
            self.cache_cp[cachekey] = cp
            return cp
        except IndexError:
            return None

    sql['INSERT_packages'] = """
    INSERT INTO packages
    (c,pn)
    VALUES
    (?,?)
    """
    def find_or_create_cp(self, category, pn):
        """Find a CP in the database, or create it"""
        cp = self.find_cp(category, pn)
        created = False
        if cp is None:
            cachekey = category+pn
            # ignore is_category_created
            (c, dummy) = self.find_or_create_category(category)
            sql = self.sql['INSERT_packages']
            self.cursor.execute(sql, (c, pn))
            self.db.commit()
            cp = self.cursor.lastrowid
            self.cache_cp[cachekey] = cp
            created = True
        return (cp, created)

    sql['SELECT_schema_is_current'] = """
    SELECT version
    FROM schema_info
    """
    def schema_is_current(self):
        """Check if the database schema version matches the
            version expected by the sourcecode"""
        result = False
        detected_version = None
        try:
            sql = self.sql['SELECT_schema_is_current']
            self.cursor.execute(sql)
            entries = self.cursor.fetchall()
            if entries is not None:
                current_schema = entries[0][0]
                result = (current_schema == self.schema_version)
                detected_version = current_schema
        except IndexError:
            pass
        except self.db.OperationalError:
            pass
        except self.db.ProgrammingError:
            pass
        return (result, detected_version)

    def _preparesql(self):
        """Prepare all SQL statements for the relevant DB backend"""
        reps = []
        reps.append(('__AI__', self.syntax_autoincrement))
        reps.append(('?', self.syntax_placeholder))
        spacematch = re.compile(r'(\s+|\n)')
        for k in self.sql.keys():
            s = self.sql[k]
            for o, n in reps:
                s = s.replace(o, n)
            s = spacematch.sub(' ', s)
            self.sql[k] = s

    def commit(self):
        """Run the commit function for the database store"""
        self.db.commit()


class SQLitePackageDB(SQLPackageDatabase):
    """override for sqlite backend"""

    syntax_placeholder = '?'
    syntax_autoincrement = 'AUTOINCREMENT'

    def __init__(self, config=None):
        # Do not complain about correct usage of ** magic
        # pylint: disable-msg=W0142
        SQLPackageDatabase.__init__(self)

        if config is None or 'database' not in config:
            print "No configuration available!"
            sys.exit(1)

        try:
            import sqlite3 as sqlite
        except ImportError:
            try:
                import pysqlite2.dbapi2 as sqlite
            except ImportError:
                print "Please install PySQLite or use Python 2.5 with sqlite"
                sys.exit(1)

        self.initdb = True
        if os.path.exists(config['database']):
            self.initdb = False
        self.db = sqlite.connect(**config)
        self.db.isolation_level = 'DEFERRED'
        self.cursor = self.db.cursor()
        self._preparesql()

        schema_check = self.schema_is_current()
        if not schema_check[0]:
            print 'Schema is outdated, flushing!'
            self.initdb = True
        if self.initdb:
            self.drop_structure()
            self.create_structure()
    
    def _preparesql(self):
        """SQLite does not like the INDEX statements that MySQL loves"""
        SQLPackageDatabase._preparesql(self)
        re_no_index = re.compile(r',\s*INDEX\s*\([^)]+\)')
        for k in self.tables:
            tablesql = self.sql['CREATE_%s' % k]
            tablesql = re_no_index.sub('', tablesql)
            self.sql['CREATE_%s' % k] = tablesql

class MySQLPackageDB(SQLPackageDatabase):
    """override for MySQL backend"""

    syntax_placeholder = "%s"
    syntax_autoincrement = 'AUTO_INCREMENT'

    def __init__(self, config=None):
        # Do not complain about correct usage of ** magic
        # pylint: disable-msg=W0142
        SQLPackageDatabase.__init__(self)

        if config is None or 'db' not in config:
            print "No configuration available!"
            sys.exit(1)

        try:
            import MySQLdb
        except ImportError:
            print "Please install a recent version of MySQLdb for Python"
            sys.exit(1)

        self.db = MySQLdb.connect(**config)
        self.cursor = self.db.cursor()
        self.initdb = False
        self._preparesql()

        if not self.schema_is_current():
            print 'Schema is outdated, flushing!'
            self.initdb = True
        if self.initdb:
            self.drop_structure()
            self.create_structure()

# vim:ts=4 et ft=python: