path: root/dbgenerator/database.py

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

"""database module"""

import os, sys
from lrucache import LRUCache

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(['alpha', 'amd64', 'arm', 'hppa', 'ia64', 'm68k', 'mips', 'ppc', 'ppc64', 's390', 'sh', 'sparc', 'sparc-fbsd', 'x86', 'x86-fbsd'])
    # If you change the database structure below, you should increment this number
    schema_version = 59
    
    # 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)

    def __init__(self):
        raise NotImplementedError("don't use base class")

    tables = {}

    tables['categories'] = """CREATE TABLE categories (
                                c INTEGER PRIMARY KEY __AI__,
                                category VARCHAR(64) UNIQUE
                            )"""
    tables['packages'] = """CREATE TABLE packages (
                                cp INTEGER PRIMARY KEY __AI__,
                                c INTEGER,
                                pn VARCHAR(64),
                                UNIQUE (c, pn)
                            )"""
    # All of these get longer than 256 chars sometimes
    tables['metadata'] = """CREATE TABLE metadata (
                                cp INTEGER,
                                license TEXT,
                                homepage TEXT,
                                description TEXT,
                                changelog TEXT,
                                PRIMARY KEY (cp)
                            )"""
    tables['versions'] = """CREATE TABLE versions (
                                cpv INTEGER PRIMARY KEY __AI__,
                                cp INTEGER,
                                pv VARCHAR(32),
                                mtime INT,
                                UNIQUE (cp, pv)
                            )"""
    tables['keywords'] = """CREATE TABLE keywords (
                                cpv INTEGER,
                                a INTEGER,
                                mode CHAR(2),
                                PRIMARY KEY (cpv, a)
                            )"""
    tables['arches'] = """CREATE TABLE arches (
                                a INTEGER PRIMARY KEY __AI__,
                                arch VARCHAR(16),
                                UNIQUE(arch)
                            )"""
    tables['schema_info'] = """CREATE TABLE schema_info (
                                version INTEGER PRIMARY KEY
                            )"""

    def create_structure(self):
        """create database structure"""

        for k in self.tables.keys():
            self.cursor.execute(self.tables[k].replace('__AI__',self.syntax_autoincrement))

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

        self.cursor.execute('INSERT INTO schema_info (version) VALUES (?)'.replace('?',self.syntax_placeholder), (self.schema_version,))
        self.db.commit()

    def drop_structure(self):
        """kill old tables"""
      
        for table in ['categories', 'packages', 'metadata', 'versions', 'keywords', 'arches', 'schema_info']:
            self.cursor.execute('DROP TABLE IF EXISTS %s' % (table))
        self.db.commit()

    def add_keywords(self, category, pn, pv, keyword_dict):
        cpv = self.find_cpv(category, pn, pv)
        #print "c=%s pn=%s pv=%s cpv=%s kw=%s" % (category, pn, pv, repr(cpv), keyword_dict)
        self.cursor.execute('DELETE FROM keywords WHERE cpv = ?'.replace('?',self.syntax_placeholder), (cpv,))
        sql = 'INSERT INTO keywords (cpv, a, mode) VALUES (?, ?, ?)'.replace('?',self.syntax_placeholder)
        for arch in keyword_dict.keys():
            a = self.find_or_create_arch(arch)
            self.cursor.execute(sql, (cpv, a, keyword_dict[arch]))
        self.commit()

    def add_metadata(self, category, pn, description, homepage, license, changelog):
        cp = self.find_or_create_cp(category, pn)
        self.cursor.execute('DELETE FROM metadata WHERE cp = ?'.replace('?',self.syntax_placeholder), (cp,))
        self.cursor.execute('INSERT INTO metadata (cp, homepage, description, license, changelog) VALUES (?, ?, ?, ?, ?)'.replace('?',self.syntax_placeholder),
                            (cp, homepage, description, str(license), changelog))
        self.commit()

    def add_version(self, category, pn, pv, mtime):
        cp = self.find_or_create_cp(category, pn)
        self.cursor.execute('DELETE FROM versions WHERE cp = ? AND pv = ?'.replace('?',self.syntax_placeholder), (cp, pv))
        self.cursor.execute('INSERT INTO versions (cp, pv, mtime) VALUES (?, ?, ?)'.replace('?',self.syntax_placeholder), (cp, pv, mtime))
        self.commit()

    def commit(self):
        return

    def find_cpv_mtime(self, category, pn, pv):
        self.cursor.execute('SELECT mtime FROM versions JOIN packages USING (cp) JOIN categories USING (c) WHERE category = ? AND pn = ? AND pv = ?'.replace('?',self.syntax_placeholder), (category, pn, pv))
        entries = self.cursor.fetchall()
        try:
            return entries[0][0]
        except IndexError:
            return -1
    
    def find_cpv(self, category, pn, pv):
        cachekey = category+pn+pv
        if cachekey in self.cache_cpv:
            return self.cache_cpv[cachekey]
        self.cursor.execute('SELECT cpv FROM versions JOIN packages USING (cp) JOIN categories USING (c) WHERE categories.category = ? AND packages.pn = ? AND versions.pv = ?'.replace('?',self.syntax_placeholder), (category, pn, pv))
        entries = self.cursor.fetchall()
        try:
             cpv = entries[0][0]
             self.cache_cpv[cachekey] = cpv
             return cpv
        except IndexError:
            return -1

    def find_or_create_category(self, category):
        cachekey = category
        if cachekey in self.cache_category:
            return self.cache_category[cachekey]
        self.cursor.execute('SELECT c FROM categories WHERE category = ?'.replace('?',self.syntax_placeholder), (category,))
        entries = self.cursor.fetchall()
        try:
            c = entries[0][0]
            self.cache_category[cachekey] = c
            return c
        except IndexError:
            self.cursor.execute('INSERT INTO categories (category) VALUES (?)'.replace('?',self.syntax_placeholder), (category,))
            self.commit()
            c = self.cursor.lastrowid
            self.cache_category[cachekey] = c
            return c
    
    def find_or_create_arch(self, arch):
        cachekey = arch
        if cachekey in self.cache_arch:
            return self.cache_arch[cachekey]
        self.cursor.execute('SELECT a FROM arches WHERE arch = ?'.replace('?',self.syntax_placeholder), (arch,))
        entries = self.cursor.fetchall()
        try:
            a = entries[0][0]
            self.cache_arch[cachekey] = a
            return a
        except IndexError:
            self.cursor.execute('INSERT INTO arches (arch) VALUES (?)'.replace('?',self.syntax_placeholder), (arch,))
            self.commit()
            a = self.cursor.lastrowid
            self.cache_arch[cachekey] = a
            return a

    def find_or_create_cp(self, category, pn):
        cachekey = category+pn
        if cachekey in self.cache_cp:
            return self.cache_cp[cachekey]
        c = self.find_or_create_category(category)
        self.cursor.execute('SELECT cp FROM packages WHERE c = ? AND pn = ?'.replace('?',self.syntax_placeholder), (c, pn))
        entries = self.cursor.fetchall()
        try:
            cp = entries[0][0]
            self.cache_cp[cachekey] = cp
            return cp
        except IndexError:
            self.cursor.execute('INSERT INTO packages (c,pn) VALUES (?,?)'.replace('?',self.syntax_placeholder), (c,pn))
            self.commit()
            cp = self.cursor.lastrowid
            self.cache_cp[cachekey] = cp
            return cp
     
    def schema_is_current(self):
        try:
            self.cursor.execute('SELECT version FROM schema_info')
            entries = self.cursor.fetchall()
            current_schema = entries[0][0]
            return current_schema == self.schema_version
        except IndexError:
            return False
        except self.db.OperationalError:
            return False
        except self.db.ProgrammingError:
            return False
        
class SQLitePackageDB(SQLPackageDatabase):
    """override for sqlite backend"""

    def __init__(self, config={}):
        self.syntax_placeholder = '?'
        self.syntax_autoincrement = 'AUTOINCREMENT'
        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)

        initdb = True
        if os.path.exists(config['database']):
            initdb = False
        #self.db = sqlite.connect(database=config['database'])
        self.db = sqlite.connect(**config)
        print self.db
        self.db.isolation_level = 'DEFERRED'
        self.cursor = self.db.cursor()
        if not self.schema_is_current():
            print 'Schema is outdated, flushing!'
            initdb = True
        if initdb:
            self.drop_structure()
            self.create_structure()

    def commit(self):
        self.db.commit()

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

    def __init__(self, config={}):
        self.syntax_placeholder = "%s"
        self.syntax_autoincrement = 'AUTO_INCREMENT'

        try:
            import MySQLdb
        except ImportError:
            print "Please install a recent version of MySQLdb for Python"
            sys.exit(1)
        #self.db = MySQLdb.connect(host=config['host'], user=config['user'], passwd=config['passwd'], db=config['db'], charset=config['charset'])
        self.db = MySQLdb.connect(**config)
        self.cursor = self.db.cursor()
        initdb = False
        if not self.schema_is_current():
            print 'Schema is outdated, flushing!'
            initdb = True
        if initdb:
            self.drop_structure()
            self.create_structure()
    
    def commit(self):
        self.db.commit()