diff options
Diffstat (limited to 'dbgenerator/database.py')
| -rw-r--r-- | dbgenerator/database.py | 112 |
1 files changed, 61 insertions, 51 deletions
diff --git a/dbgenerator/database.py b/dbgenerator/database.py index 5ee2e5b..ad6b275 100644 --- a/dbgenerator/database.py +++ b/dbgenerator/database.py @@ -15,7 +15,7 @@ class SQLPackageDatabase(object): # 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 = 57 + schema_version = 59 # These are used to cache the various relations # and avoid more SELECT queries just to find the relations @@ -29,59 +29,59 @@ class SQLPackageDatabase(object): def __init__(self): raise NotImplementedError("don't use base class") - def create_structure(self): - """create database structure""" + tables = {} - self.cursor.execute("""CREATE TABLE categories ( - c INTEGER PRIMARY KEY AUTOINCREMENT, + tables['categories'] = """CREATE TABLE categories ( + c INTEGER PRIMARY KEY __AI__, category VARCHAR(64) UNIQUE - )""") - - self.cursor.execute("""CREATE TABLE packages ( - cp INTEGER PRIMARY KEY AUTOINCREMENT, + )""" + tables['packages'] = """CREATE TABLE packages ( + cp INTEGER PRIMARY KEY __AI__, c INTEGER, pn VARCHAR(64), UNIQUE (c, pn) - )""") - - self.cursor.execute("""CREATE TABLE metadata ( + )""" + # All of these get longer than 256 chars sometimes + tables['metadata'] = """CREATE TABLE metadata ( cp INTEGER, - license VARCHAR(255), - homepage VARCHAR(255), + license TEXT, + homepage TEXT, description TEXT, changelog TEXT, PRIMARY KEY (cp) - )""") - - self.cursor.execute("""CREATE TABLE versions ( - cpv INTEGER PRIMARY KEY AUTOINCREMENT, + )""" + tables['versions'] = """CREATE TABLE versions ( + cpv INTEGER PRIMARY KEY __AI__, cp INTEGER, pv VARCHAR(32), mtime INT, UNIQUE (cp, pv) - )""") - - self.cursor.execute("""CREATE TABLE keywords ( + )""" + tables['keywords'] = """CREATE TABLE keywords ( cpv INTEGER, a INTEGER, mode CHAR(2), PRIMARY KEY (cpv, a) - )""") - - self.cursor.execute("""CREATE TABLE arches ( - a INTEGER PRIMARY KEY AUTOINCREMENT, + )""" + tables['arches'] = """CREATE TABLE arches ( + a INTEGER PRIMARY KEY __AI__, arch VARCHAR(16), UNIQUE(arch) - )""") - - self.cursor.execute("""CREATE TABLE schema_info ( + )""" + 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.placeholder), (self.schema_version,)) + self.cursor.execute('INSERT INTO schema_info (version) VALUES (?)'.replace('?',self.syntax_placeholder), (self.schema_version,)) self.db.commit() def drop_structure(self): @@ -94,8 +94,8 @@ class SQLPackageDatabase(object): 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.placeholder), (cpv,)) - sql = 'INSERT INTO keywords (cpv, a, mode) VALUES (?, ?, ?)'.replace('?',self.placeholder) + 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])) @@ -103,22 +103,22 @@ class SQLPackageDatabase(object): 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.placeholder), (cp,)) - self.cursor.execute('INSERT INTO metadata (cp, homepage, description, license, changelog) VALUES (?, ?, ?, ?, ?)'.replace('?',self.placeholder), + 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.placeholder), (cp, pv)) - self.cursor.execute('INSERT INTO versions (cp, pv, mtime) VALUES (?, ?, ?)'.replace('?',self.placeholder), (cp, pv, mtime)) + 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.placeholder), (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] @@ -129,7 +129,7 @@ class SQLPackageDatabase(object): 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.placeholder), (category, pn, pv)) + 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] @@ -142,14 +142,14 @@ class SQLPackageDatabase(object): cachekey = category if cachekey in self.cache_category: return self.cache_category[cachekey] - self.cursor.execute('SELECT c FROM categories WHERE category = ?'.replace('?',self.placeholder), (category,)) + 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.placeholder), (category,)) + self.cursor.execute('INSERT INTO categories (category) VALUES (?)'.replace('?',self.syntax_placeholder), (category,)) self.commit() c = self.cursor.lastrowid self.cache_category[cachekey] = c @@ -159,14 +159,14 @@ class SQLPackageDatabase(object): cachekey = arch if cachekey in self.cache_arch: return self.cache_arch[cachekey] - self.cursor.execute('SELECT a FROM arches WHERE arch = ?'.replace('?',self.placeholder), (arch,)) + 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.placeholder), (arch,)) + self.cursor.execute('INSERT INTO arches (arch) VALUES (?)'.replace('?',self.syntax_placeholder), (arch,)) self.commit() a = self.cursor.lastrowid self.cache_arch[cachekey] = a @@ -177,14 +177,14 @@ class SQLPackageDatabase(object): 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.placeholder), (c, pn)) + 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.placeholder), (c,pn)) + 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 @@ -200,11 +200,15 @@ class SQLPackageDatabase(object): return False except self.db.OperationalError: return False + except self.db.ProgrammingError: + return False class SQLitePackageDB(SQLPackageDatabase): """override for sqlite backend""" - def __init__(self, filename): + def __init__(self, config={}): + self.syntax_placeholder = '?' + self.syntax_autoincrement = 'AUTOINCREMENT' try: import sqlite3 as sqlite except ImportError: @@ -213,11 +217,13 @@ class SQLitePackageDB(SQLPackageDatabase): except ImportError: print "Please install PySQLite or use Python 2.5 with sqlite" sys.exit(1) - self.placeholder = '?' + initdb = True - if os.path.exists(filename): + if os.path.exists(config['database']): initdb = False - self.db = sqlite.connect(filename) + #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(): @@ -233,15 +239,19 @@ class SQLitePackageDB(SQLPackageDatabase): class MySQLPackageDB(SQLPackageDatabase): """override for MySQL backend""" - def __init__(self, host, username, password, database): - self.placeholder = "%s" + 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=host, user=username, passwd=password, db=database, charset='utf8') + #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 |