From 87da3448de427881678ff4de670dd77ce3fbcd5a Mon Sep 17 00:00:00 2001 From: Michael Tremer Date: Wed, 31 Jul 2013 02:15:38 +0200 Subject: [PATCH] Cleanup database and add indexes. Trying to speed up the database of installed packages, by adding indexes and moving big data from the packages table into an extra dependencies table. --- python/pakfire/constants.py | 4 +- python/pakfire/packages/installed.py | 38 ++++---- python/pakfire/repository/database.py | 121 +++++++++++++++++++------- 3 files changed, 112 insertions(+), 51 deletions(-) diff --git a/python/pakfire/constants.py b/python/pakfire/constants.py index 5dc89715..2ff3ddb7 100644 --- a/python/pakfire/constants.py +++ b/python/pakfire/constants.py @@ -75,8 +75,8 @@ PACKAGE_FORMATS_SUPPORTED = [0, 1, 2, 3, 4, 5] PACKAGE_EXTENSION = "pfm" MAKEFILE_EXTENSION = "nm" -DATABASE_FORMAT = 6 -DATABASE_FORMATS_SUPPORTED = [0, 1, 2, 3, 4, 5, 6] +DATABASE_FORMAT = 7 +DATABASE_FORMATS_SUPPORTED = [0, 1, 2, 3, 4, 5, 6, 7] PACKAGE_FILENAME_FMT = "%(name)s-%(version)s-%(release)s.%(arch)s.%(ext)s" diff --git a/python/pakfire/packages/installed.py b/python/pakfire/packages/installed.py index e216459c..1358ea05 100644 --- a/python/pakfire/packages/installed.py +++ b/python/pakfire/packages/installed.py @@ -150,39 +150,45 @@ class DatabasePackage(Package): return inst_size + @property + def dependencies(self): + if not hasattr(self, "__dependencies"): + self.__dependencies = {} + + c = self.db.cursor() + c.execute("SELECT type, dependency FROM dependencies WHERE pkg = ?", (self.id,)) + + for type, dependency in c.fetchall(): + try: + self.__dependencies[type].append(dependency) + except KeyError: + self.__dependencies[type] = [dependency,] + + return self.__dependencies + @property def provides(self): - return self.metadata.get("provides", "").splitlines() + return self.dependencies.get("provides", []) @property def requires(self): - return self.metadata.get("requires", "").splitlines() + return self.dependencies.get("requires", []) @property def conflicts(self): - return self.metadata.get("conflicts", "").splitlines() + return self.dependencies.get("conflicts", []) @property def obsoletes(self): - return self.metadata.get("obsoletes", "").splitlines() + return self.dependencies.get("obsoletes", []) @property def recommends(self): - recommends = self.metadata.get("recommends", None) - - if recommends: - return recommends.splitlines() - - return [] + return self.dependencies.get("recommends", []) @property def suggests(self): - suggests = self.metadata.get("suggests", None) - - if suggests: - return suggests.splitlines() - - return [] + return self.dependencies.get("suggests", []) @property def hash1(self): diff --git a/python/pakfire/repository/database.py b/python/pakfire/repository/database.py index d8751e9b..d22bdb5f 100644 --- a/python/pakfire/repository/database.py +++ b/python/pakfire/repository/database.py @@ -34,14 +34,6 @@ from pakfire.constants import * from pakfire.errors import * from pakfire.i18n import _ -class Cursor(sqlite3.Cursor): - def execute(self, *args, **kwargs): - # For debugging of SQL queries. - #print args, kwargs - - return sqlite3.Cursor.execute(self, *args, **kwargs) - - class Database(object): def __init__(self, pakfire, filename): self.pakfire = pakfire @@ -100,7 +92,7 @@ class Database(object): def cursor(self): self.open() - return self._db.cursor(Cursor) + return self._db.cursor() def executescript(self, *args, **kwargs): self.open() @@ -179,6 +171,7 @@ class DatabaseLocal(Database): mtime INTEGER, capabilities TEXT ); + CREATE INDEX files_pkg_index ON files(pkg); CREATE TABLE packages( id INTEGER PRIMARY KEY, @@ -192,12 +185,6 @@ class DatabaseLocal(Database): size INTEGER, inst_size INTEGER, hash1 TEXT, - provides TEXT, - requires TEXT, - conflicts TEXT, - obsoletes TEXT, - recommends TEXT, - suggests TEXT, license TEXT, summary TEXT, description TEXT, @@ -211,6 +198,7 @@ class DatabaseLocal(Database): reason TEXT, repository TEXT ); + CREATE INDEX packages_name_index ON packages(name); CREATE TABLE scriptlets( id INTEGER PRIMARY KEY, @@ -218,13 +206,14 @@ class DatabaseLocal(Database): action TEXT, scriptlet TEXT ); + CREATE INDEX scriptlets_pkg_index ON scriptlets(pkg); - CREATE TABLE triggers( - id INTEGER PRIMARY KEY, - pkg INTEGER, - dependency TEXT, - scriptlet TEXT + CREATE TABLE dependencies( + pkg INTEGER, + type TEXT, + dependency TEXT ); + CREATE INDEX dependencies_pkg_index ON dependencies(pkg); """ % DATABASE_FORMAT) # XXX add some indexes here self.commit() @@ -269,6 +258,72 @@ class DatabaseLocal(Database): if self.format < 6: c.execute("ALTER TABLE packages ADD COLUMN inst_size INTEGER AFTER size") + if self.format < 7: + c.executescript(""" + CREATE TABLE dependencies(pkg INTEGER, type TEXT, dependency TEXT); + CREATE INDEX dependencies_pkg_index ON dependencies(pkg); + """) + + c.execute("SELECT id, provides, requires, conflicts, obsoletes, recommends, suggests FROM packages") + pkgs = c.fetchall() + + for pkg in pkgs: + (pkg_id, provides, requires, conflicts, obsoletes, recommends, suggests) = pkg + + dependencies = ( + ("provides", provides), + ("requires", requires), + ("conflicts", conflicts), + ("obsoletes", obsoletes), + ("recommends", recommends), + ("suggests", suggests), + ) + + for type, deps in dependencies: + c.executemany("INSERT INTO dependencies(pkg, type, dependency) VALUES(?, ?, ?)", + ((pkg_id, type, d) for d in deps)) + + c.executescript(""" + CREATE TABLE packages_( + id INTEGER PRIMARY KEY, + name TEXT, + epoch INTEGER, + version TEXT, + release TEXT, + arch TEXT, + groups TEXT, + filename TEXT, + size INTEGER, + inst_size INTEGER, + hash1 TEXT, + license TEXT, + summary TEXT, + description TEXT, + uuid TEXT, + vendor TEXT, + build_id TEXT, + build_host TEXT, + build_date TEXT, + build_time INTEGER, + installed INT, + reason TEXT, + repository TEXT + ); + + INSERT INTO packages_ SELECT id, name, epoch, version, release, arch, groups, filename, + size, inst_size, hash1, license, summary, description, uuid, vendor, build_id, + build_host, build_date, build_time, installed, reason, repository FROM packages; + + DROP TABLE packages; + ALTER TABLE packages_ RENAME TO packages; + + DROP TABLE triggers; + + CREATE INDEX files_pkg_index ON files(pkg); + CREATE INDEX scriptlets_pkg_index ON scriptlets(pkg); + CREATE INDEX packages_name_index ON packages(name); + """) + # In the end, we can easily update the version of the database. c.execute("UPDATE settings SET val = ? WHERE key = 'version'", (DATABASE_FORMAT,)) self.__format = DATABASE_FORMAT @@ -294,12 +349,6 @@ class DatabaseLocal(Database): size, inst_size, hash1, - provides, - requires, - conflicts, - obsoletes, - recommends, - suggests, license, summary, description, @@ -312,7 +361,7 @@ class DatabaseLocal(Database): installed, repository, reason - ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", + ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", ( pkg.name, pkg.epoch, @@ -324,12 +373,6 @@ class DatabaseLocal(Database): pkg.size, pkg.inst_size, pkg.hash1, - "\n".join(pkg.provides), - "\n".join(pkg.requires), - "\n".join(pkg.conflicts), - "\n".join(pkg.obsoletes), - "\n".join(pkg.recommends), - "\n".join(pkg.suggests), pkg.license, pkg.summary, pkg.description, @@ -347,6 +390,18 @@ class DatabaseLocal(Database): pkg_id = c.lastrowid + # Add all dependencies. + dependencies = ( + ("provides", pkg.provides), + ("requires", pkg.requires), + ("conflicts", pkg.conflicts), + ("obsoletes", pkg.obsoletes), + ("recommends", pkg.recommends), + ("suggests", pkg.suggests), + ) + for type, deps in dependencies: + c.executemany("INSERT INTO dependencies(pkg, type, dependency) VALUES(?, ?, ?)", ((pkg_id, type, d) for d in deps)) + c.executemany("INSERT INTO files(`name`, `pkg`, `size`, `config`, `datafile`, `type`, `hash1`, `mode`, `user`, `group`, `mtime`, `capabilities`)" " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", ((f.name, pkg_id, f.size, f.is_config(), f.is_datafile(), f.type, f.hash1, f.mode, f.user, f.group, f.mtime, f.capabilities or "") for f in pkg.filelist)) -- 2.39.2