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.

diff --git a/python/pakfire/constants.py b/python/pakfire/constants.py
index 5dc897157cc9e590edd357d1468e873cf26b733a..2ff3ddb739ef6f6e84002f778761f594cce7900c 100644 (file)
--- 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 e216459c7c8eb01f4e42d1afc4d87348d7247741..1358ea052799f403521c2dfd6c3d07228e576d9b 100644 (file)
--- 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 d8751e9b5f21748e1cbdb5b325833bcecd34dbf3..d22bdb5f6e9eb8074492906fb75976d5973c104d 100644 (file)
--- 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))