[people/ms/pakfire.git] / src / libpakfire / db.c

/*#############################################################################
#                                                                             #
# Pakfire - The IPFire package management system                              #
# Copyright (C) 2021 Pakfire development team                                 #
#                                                                             #
# This program is free software: you can redistribute it and/or modify        #
# it under the terms of the GNU General Public License as published by        #
# the Free Software Foundation, either version 3 of the License, or           #
# (at your option) any later version.                                         #
#                                                                             #
# This program is distributed in the hope that it will be useful,             #
# but WITHOUT ANY WARRANTY; without even the implied warranty of              #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               #
# GNU General Public License for more details.                                #
#                                                                             #
# You should have received a copy of the GNU General Public License           #
# along with this program.  If not, see <http://www.gnu.org/licenses/>.       #
#                                                                             #
#############################################################################*/

#include <errno.h>
#include <linux/limits.h>
#include <stdlib.h>
#include <time.h>

#include <openssl/sha.h>
#include <solv/solver.h>
#include <sqlite3.h>

#include <pakfire/archive.h>
#include <pakfire/db.h>
#include <pakfire/dependencies.h>
#include <pakfire/digest.h>
#include <pakfire/file.h>
#include <pakfire/filelist.h>
#include <pakfire/logging.h>
#include <pakfire/package.h>
#include <pakfire/pakfire.h>
#include <pakfire/repo.h>
#include <pakfire/string.h>
#include <pakfire/util.h>

#define DATABASE_PATH PAKFIRE_PRIVATE_DIR "/packages.db"

#define CURRENT_SCHEMA 8
#define SCHEMA_MIN_SUP 7

struct pakfire_db {
	struct pakfire* pakfire;
	int nrefs;

	char path[PATH_MAX];
	int mode;

	sqlite3* handle;
	int schema;
	time_t last_modified_at;
};

static void logging_callback(void* data, int r, const char* msg) {
	struct pakfire* pakfire = (struct pakfire*)data;

	ERROR(pakfire, "Database Error: %s: %s\n",
		sqlite3_errstr(r), msg);
}

static sqlite3_value* pakfire_db_get(struct pakfire_db* db, const char* key) {
	sqlite3_stmt* stmt = NULL;
	sqlite3_value* val = NULL;
	int r;

	const char* sql = "SELECT val FROM settings WHERE key = ?";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r != SQLITE_OK) {
		//ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
		//	sql, sqlite3_errmsg(db->handle));
		return NULL;
	}

	// Bind key
	r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Execute the statement
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	// We should have read a row
	if (r != SQLITE_ROW)
		goto ERROR;

	// Read value
	val = sqlite3_column_value(stmt, 0);
	if (!val) {
		ERROR(db->pakfire, "Could not read value\n");
		goto ERROR;
	}

	// Copy value onto the heap
	val = sqlite3_value_dup(val);

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	return val;
}

static char* pakfire_db_get_string(struct pakfire_db* db, const char* key) {
	char* s = NULL;

	// Fetch the value from the database
	sqlite3_value* value = pakfire_db_get(db, key);
	if (!value)
		return NULL;

	// Extract the value as string
	const char* p = (const char*)sqlite3_value_text(value);
	if (!p)
		goto ERROR;

	// Copy string to heap
	s = strdup(p);

ERROR:
	if (value)
		sqlite3_value_free(value);

	return s;
}

static int pakfire_db_set_string(struct pakfire_db* db, const char* key, const char* val) {
	sqlite3_stmt* stmt = NULL;
	int r;

	DEBUG(db->pakfire, "Setting %s to '%s'\n", key, val);

	const char* sql = "INSERT INTO settings(key, val) VALUES(?, ?) \
		ON CONFLICT (key) DO UPDATE SET val = excluded.val";

	// Prepare statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		return 1;
	}

	// Bind key
	r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind val
	r = sqlite3_bind_text(stmt, 2, val, strlen(val), NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not bind val: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Execute the statement
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	// Set return code
	r = (r == SQLITE_OK);

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

static int pakfire_db_set_int(struct pakfire_db* db, const char* key, int val) {
	sqlite3_stmt* stmt = NULL;
	int r;

	DEBUG(db->pakfire, "Setting %s to '%d'\n", key, val);

	const char* sql = "INSERT INTO settings(key, val) VALUES(?, ?) \
		ON CONFLICT (key) DO UPDATE SET val = excluded.val";

	// Prepare statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		return 1;
	}

	// Bind key
	r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind val
	r = sqlite3_bind_int64(stmt, 2, val);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not bind val: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Execute the statement
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	// Set return code
	r = (r == SQLITE_OK);

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

static time_t pakfire_read_modification_time(struct pakfire_db* db) {
	time_t t = 0;

	// Fetch the value from the database
	sqlite3_value* value = pakfire_db_get(db, "last_modified_at");
	if (value) {
		t = sqlite3_value_int64(value);
		sqlite3_value_free(value);
	} else {
		DEBUG(db->pakfire, "Could not find last modification timestamp\n");
	}

	return t;
}

static int pakfire_update_modification_time(struct pakfire_db* db) {
	// Get the current time in UTC
	time_t t = time(NULL);

	// Store it in the database
	int r = pakfire_db_set_int(db, "last_modified_at", t);
	if (r)
		return r;

	// Update the last modification timestamp
	db->last_modified_at = t;

	return r;
}

static int pakfire_db_execute(struct pakfire_db* db, const char* stmt) {
	int r;

	DEBUG(db->pakfire, "Executing database query: %s\n", stmt);

	do {
		r = sqlite3_exec(db->handle, stmt, NULL, NULL, NULL);
	} while (r == SQLITE_BUSY);

	// Log any errors
	if (r) {
		ERROR(db->pakfire, "Database query failed: %s\n", sqlite3_errmsg(db->handle));
	}

	return r;
}

static int pakfire_db_begin_transaction(struct pakfire_db* db) {
	return pakfire_db_execute(db, "BEGIN TRANSACTION");
}

static int pakfire_db_commit(struct pakfire_db* db) {
	/*
		If the database was opened in read-write mode, we will store the
		timestamp of the latest modification to compare whether the database
		has been changed mid-transaction.
	*/
	if (db->mode == PAKFIRE_DB_READWRITE) {
		int r = pakfire_update_modification_time(db);
		if (r)
			return r;
	}

	return pakfire_db_execute(db, "COMMIT");
}

static int pakfire_db_rollback(struct pakfire_db* db) {
	return pakfire_db_execute(db, "ROLLBACK");
}

/*
	This function performs any fast optimization and tries to truncate the WAL log file
	to keep the database as compact as possible on disk.
*/
static void pakfire_db_optimize(struct pakfire_db* db) {
	pakfire_db_execute(db, "PRAGMA optimize");
	pakfire_db_execute(db, "PRAGMA wal_checkpoint = TRUNCATE");
}

static void pakfire_db_free(struct pakfire_db* db) {
	if (db->handle) {
		// Optimize the database before it is being closed
		pakfire_db_optimize(db);

		// Close database handle
		int r = sqlite3_close(db->handle);
		if (r != SQLITE_OK) {
			ERROR(db->pakfire, "Could not close database handle: %s\n",
				sqlite3_errmsg(db->handle));
		}
	}

	pakfire_unref(db->pakfire);

	free(db);
}

static int pakfire_db_get_schema(struct pakfire_db* db) {
	sqlite3_value* value = pakfire_db_get(db, "schema");
	if (!value)
		return -1;

	int schema = sqlite3_value_int64(value);
	sqlite3_value_free(value);

	DEBUG(db->pakfire, "Database has schema version %d\n", schema);

	return schema;
}

static int pakfire_db_create_schema(struct pakfire_db* db) {
	int r;

	// Create settings table
	r = pakfire_db_execute(db, "CREATE TABLE IF NOT EXISTS settings(key TEXT, val TEXT)");
	if (r)
		return 1;

	// settings: Add a unique index on key
	r = pakfire_db_execute(db, "CREATE UNIQUE INDEX IF NOT EXISTS settings_key ON settings(key)");
	if (r)
		return 1;

	// Create packages table
	r = pakfire_db_execute(db,
		"CREATE TABLE IF NOT EXISTS packages("
			"id             INTEGER PRIMARY KEY, "
			"name           TEXT, "
			"evr            TEXT, "
			"arch           TEXT, "
			"groups         TEXT, "
			"filename       TEXT, "
			"size           INTEGER, "
			"inst_size      INTEGER, "
			"digest         TEXT, "
			"license        TEXT, "
			"summary        TEXT, "
			"description    TEXT, "
			"uuid           TEXT, "
			"vendor         TEXT, "
			"build_host     TEXT, "
			"build_time     INTEGER, "
			"installed      INTEGER, "
			"userinstalled  INTEGER, "
			"repository     TEXT, "
			"source_name    TEXT, "
			"source_evr     TEXT, "
			"source_arch    TEXT, "
			"distribution   TEXT"
		")");
	if (r)
		return 1;

	// packages: Create index to find package by name
	r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS packages_name ON packages(name)");
	if (r)
		return 1;

	// packages: Create unique index over UUID
	r = pakfire_db_execute(db, "CREATE UNIQUE INDEX IF NOT EXISTS packages_uuid ON packages(uuid)");
	if (r)
		return 1;

	// Create dependencies table
	r = pakfire_db_execute(db,
		"CREATE TABLE IF NOT EXISTS dependencies("
			"pkg            INTEGER, "
			"type           TEXT, "
			"dependency     TEXT, "
			"FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
		")");
	if (r)
		return r;

	// dependencies: Add index over packages
	r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS dependencies_pkg_index ON dependencies(pkg)");
	if (r)
		return r;

	// Create files table
	r = pakfire_db_execute(db,
		"CREATE TABLE IF NOT EXISTS files("
			"id                INTEGER PRIMARY KEY, "
			"path              TEXT, "
			"pkg               INTEGER, "
			"size              INTEGER, "
			"config            INTEGER, "
			"datafile          INTEGER, "
			"mode              INTEGER, "
			"uname             TEXT, "
			"gname             TEXT, "
			"ctime             INTEGER, "
			"mtime             INTEGER, "
			"capabilities      TEXT, "
			"digest_sha2_512   BLOB, "
			"digest_sha2_256   BLOB, "
			"digest_blake2b512 BLOB, "
			"digest_blake2s256 BLOB, "
			"digest_sha3_512   BLOB, "
			"digest_sha3_256   BLOB, "
			"FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
		")");
	if (r)
		return 1;

	// files: Add index over packages
	r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS files_pkg_index ON files(pkg)");
	if (r)
		return 1;

	// files: Add index over path
	r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS files_path_index ON files(path)");
	if (r)
		return 1;

	// Create scriptlets table
	r = pakfire_db_execute(db,
		"CREATE TABLE IF NOT EXISTS scriptlets("
			"id             INTEGER PRIMARY KEY, "
			"pkg            INTEGER, "
			"type           TEXT, "
			"scriptlet      TEXT, "
			"FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
		")");
	if (r)
		return 1;

	// scriptlets: Add index over packages
	r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS scriptlets_pkg_index ON scriptlets(pkg)");
	if (r)
		return 1;

	const char* arch = pakfire_get_arch(db->pakfire);

	// Set architecture
	r = pakfire_db_set_string(db, "arch", arch);
	if (r) {
		ERROR(db->pakfire, "Could not set architecture\n");
		return r;
	}

	return 0;
}

static int pakfire_db_migrate_to_schema_8(struct pakfire_db* db) {
	// packages: Drop build_id column

	// Add foreign keys
	// TODO sqlite doesn't support adding foreign keys to existing tables and so we would
	// need to recreate the whole table and rename it afterwards. Annoying.

	return 0;
}

static int pakfire_db_migrate_schema(struct pakfire_db* db) {
	int r;

	while (db->schema < CURRENT_SCHEMA) {
		// Begin a new transaction
		r = pakfire_db_begin_transaction(db);
		if (r)
			goto ROLLBACK;

		switch (db->schema) {
			// No schema exists
			case -1:
				r = pakfire_db_create_schema(db);
				if (r)
					goto ROLLBACK;

				db->schema = CURRENT_SCHEMA;
				break;

			case 7:
				r = pakfire_db_migrate_to_schema_8(db);
				if (r)
					goto ROLLBACK;

				db->schema++;
				break;

			default:
				ERROR(db->pakfire, "Cannot migrate database from schema %d\n", db->schema);
				goto ROLLBACK;
		}

		// Update the schema version
		r = pakfire_db_set_int(db, "schema", CURRENT_SCHEMA);
		if (r)
			goto ROLLBACK;

		// All done, commit!
		r = pakfire_db_commit(db);
		if (r)
			goto ROLLBACK;
	}

	return 0;

ROLLBACK:
	pakfire_db_rollback(db);

	return 1;
}

static int pakfire_db_check_arch(struct pakfire_db* db) {
	int r = 1;

	// Fetch database architecture
	char* db_arch = pakfire_db_get_string(db, "arch");
	if (!db_arch) {
		ERROR(db->pakfire, "Database is of an unknown architecture\n");
		goto ERROR;
	}

	// Fetch the running architecture
	const char* arch = pakfire_get_arch(db->pakfire);
	if (!arch)
		goto ERROR;

	// They should match
	if (strcmp(db_arch, arch) == 0)
		r = 0;

ERROR:
	if (db_arch)
		free(db_arch);

	return r;
}

static int pakfire_db_setup(struct pakfire_db* db) {
	int r;

	// Setup logging
	sqlite3_config(SQLITE_CONFIG_LOG, logging_callback, db->pakfire);

	// Enable foreign keys
	pakfire_db_execute(db, "PRAGMA foreign_keys = ON");

	// Make LIKE case-sensitive
	pakfire_db_execute(db, "PRAGMA case_sensitive_like = ON");

	// Fetch the current schema
	db->schema = pakfire_db_get_schema(db);

	// Check if the schema is recent enough
	if (db->schema > 0 && db->schema < SCHEMA_MIN_SUP) {
		ERROR(db->pakfire, "Database schema %d is not supported by this version of Pakfire\n",
			db->schema);
		return 1;
	}

	// Read modification timestamp
	db->last_modified_at = pakfire_read_modification_time(db);

	DEBUG(db->pakfire, "The database was last modified at %ld\n", db->last_modified_at);

	// Done when not in read-write mode
	if (db->mode != PAKFIRE_DB_READWRITE)
		return 0;

	// Disable secure delete
	pakfire_db_execute(db, "PRAGMA secure_delete = OFF");

	// Set database journal to WAL
	r = pakfire_db_execute(db, "PRAGMA journal_mode = WAL");
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not set journal mode to WAL: %s\n",
			sqlite3_errmsg(db->handle));
		return 1;
	}

	// Disable autocheckpoint
	r = sqlite3_wal_autocheckpoint(db->handle, 0);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not disable autocheckpoint: %s\n",
			sqlite3_errmsg(db->handle));
		return 1;
	}

	// Create or migrate schema
	r = pakfire_db_migrate_schema(db);
	if (r)
		return r;

	return 0;
}

int pakfire_db_open(struct pakfire_db** db, struct pakfire* pakfire, int flags) {
	int r = 1;

	struct pakfire_db* o = calloc(1, sizeof(*o));
	if (!o)
		return -ENOMEM;

	o->pakfire = pakfire_ref(pakfire);
	o->nrefs = 1;

	int sqlite3_flags = 0;

	// Store mode & forward it to sqlite3
	if (flags & PAKFIRE_DB_READWRITE) {
		o->mode = PAKFIRE_DB_READWRITE;
		sqlite3_flags |= SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
	} else {
		o->mode = PAKFIRE_DB_READONLY;
		sqlite3_flags |= SQLITE_OPEN_READONLY;
	}

	// Make the filename
	r = pakfire_path(o->pakfire, o->path, "%s", DATABASE_PATH);
	if (r)
		goto END;

	// Try to open the sqlite3 database file
	r = sqlite3_open_v2(o->path, &o->handle, sqlite3_flags, NULL);
	if (r != SQLITE_OK) {
		ERROR(pakfire, "Could not open database %s: %s\n",
			o->path, sqlite3_errmsg(o->handle));

		r = 1;
		goto END;
	}

	// Setup the database
	r = pakfire_db_setup(o);
	if (r)
		goto END;

	// Check for compatible architecture
	r = pakfire_db_check_arch(o);
	if (r)
		goto END;

	*db = o;
	r = 0;

END:
	if (r)
		pakfire_db_free(o);

	return r;
}

struct pakfire_db* pakfire_db_ref(struct pakfire_db* db) {
	db->nrefs++;

	return db;
}

struct pakfire_db* pakfire_db_unref(struct pakfire_db* db) {
	if (--db->nrefs > 0)
		return db;

	pakfire_db_free(db);

	return NULL;
}

static unsigned long pakfire_db_integrity_check(struct pakfire_db* db) {
	sqlite3_stmt* stmt = NULL;
	int r;

	r = sqlite3_prepare_v2(db->handle, "PRAGMA integrity_check", -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare integrity check: %s\n",
			sqlite3_errmsg(db->handle));
		return 1;
	}

	// Count any errors
	unsigned long errors = 0;

	while (1) {
		do {
			r = sqlite3_step(stmt);
		} while (r == SQLITE_BUSY);

		if (r == SQLITE_ROW) {
			const char* error = (const char*)sqlite3_column_text(stmt, 0);

			// If the message is "ok", the database has passed the check
			if (strcmp(error, "ok") == 0)
				continue;

			// Increment error counter
			errors++;

			// Log the message
			ERROR(db->pakfire, "%s\n", error);

		// Break on anything else
		} else
			break;
	}

	sqlite3_finalize(stmt);

	if (errors)
		ERROR(db->pakfire, "Database integrity check failed\n");
	else
		INFO(db->pakfire, "Database integrity check passed\n");

	return errors;
}

static unsigned long pakfire_db_foreign_key_check(struct pakfire_db* db) {
	sqlite3_stmt* stmt = NULL;
	int r;

	r = sqlite3_prepare_v2(db->handle, "PRAGMA foreign_key_check", -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare foreign key check: %s\n",
			sqlite3_errmsg(db->handle));
		return 1;
	}

	// Count any errors
	unsigned long errors = 0;

	while (1) {
		do {
			r = sqlite3_step(stmt);
		} while (r == SQLITE_BUSY);

		if (r == SQLITE_ROW) {
			const unsigned char* table = sqlite3_column_text(stmt, 0);
			unsigned long rowid = sqlite3_column_int64(stmt, 1);
			const unsigned char* foreign_table = sqlite3_column_text(stmt, 2);
			unsigned long foreign_rowid = sqlite3_column_int64(stmt, 3);

			// Increment error counter
			errors++;

			// Log the message
			ERROR(db->pakfire, "Foreign key violation found in %s, row %lu: "
				"%lu does not exist in table %s\n", table, rowid, foreign_rowid, foreign_table);

		// Break on anything else
		} else
			break;
	}

	sqlite3_finalize(stmt);

	if (errors)
		ERROR(db->pakfire, "Foreign key check failed\n");
	else
		INFO(db->pakfire, "Foreign key check passed\n");

	return errors;
}

/*
	This function performs an integrity check of the database
*/
int pakfire_db_check(struct pakfire_db* db) {
	int r;

	// Perform integrity check
	r = pakfire_db_integrity_check(db);
	if (r)
		return 1;

	// Perform foreign key check
	r = pakfire_db_foreign_key_check(db);
	if (r)
		return 1;

	return 0;
}

// Returns the number of packages installed
ssize_t pakfire_db_packages(struct pakfire_db* db) {
	sqlite3_stmt* stmt = NULL;
	ssize_t packages = -1;

	int r = sqlite3_prepare_v2(db->handle, "SELECT COUNT(*) FROM packages", -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s\n",
			sqlite3_errmsg(db->handle));
		return -1;
	}

	// Execute query
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	if (r == SQLITE_ROW) {
		packages = sqlite3_column_int64(stmt, 0);
	}

	sqlite3_finalize(stmt);

	return packages;
}

static void pakfire_db_add_userinstalled(struct pakfire* pakfire, const char* name) {
	Pool* pool = pakfire_get_solv_pool(pakfire);

	// Convert name to ID
	Id id = pool_str2id(pool, name, 1);

	// Append it to pooljobs
	queue_push2(&pool->pooljobs, SOLVER_USERINSTALLED|SOLVER_SOLVABLE_NAME, id);
}

static const struct pakfire_digest {
	enum pakfire_digest_types type;
	const char* prefix;
} pakfire_digests[] = {
	{ PAKFIRE_DIGEST_SHA2_512, "sha512:" },
	{ PAKFIRE_DIGEST_SHA2_256, "sha256:" },
	{ 0, NULL },
};

static char* pakfire_db_pack_digest(enum pakfire_digest_types type, const char* hexdigest) {
	char* s = NULL;
	int r;

	for (const struct pakfire_digest* digest = pakfire_digests; digest->type; digest++) {
		if (digest->type == type) {
			r = asprintf(&s, "%s%s", digest->prefix, hexdigest);
			if (r < 0)
				return NULL;

			return s;
		}
	}

	return NULL;
}

static const char* pakfire_db_unpack_digest(const char* hexdigest, enum pakfire_digest_types* type) {
	*type = 0;

	// Don't do anything for empty input
	if (!hexdigest || !*hexdigest)
		return NULL;

	for (const struct pakfire_digest* digest = pakfire_digests; digest->type; digest++) {
		if (pakfire_string_startswith(hexdigest, digest->prefix)) {
			*type = digest->type;

			// Return the beginning of the hexdigest
			return hexdigest + strlen(digest->prefix);
		}
	}

	// No match
	return NULL;
}

static int pakfire_db_add_dependencies(struct pakfire_db* db, unsigned long id, struct pakfire_package* pkg) {
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	const char* sql = "INSERT INTO dependencies(pkg, type, dependency) VALUES(?, ?, ?)";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto END;
	}

	const struct __relation {
		const char* type;
		char** (*func)(struct pakfire_package*);
	} relations[] = {
		{ "provides", pakfire_package_get_provides },
		{ "prerequires", pakfire_package_get_prerequires },
		{ "requires", pakfire_package_get_requires },
		{ "conflicts", pakfire_package_get_conflicts },
		{ "obsoletes", pakfire_package_get_obsoletes },
		{ "recommends", pakfire_package_get_recommends },
		{ "suggests", pakfire_package_get_suggests },
		{ "supplements", pakfire_package_get_supplements },
		{ "enhances", pakfire_package_get_enhances },
		{ NULL, NULL },
	};

	for (const struct __relation* relation = relations; relation->type; relation++) {
		char** list = relation->func(pkg);
		if (!list)
			continue;

		for (char** dep = list; *dep; dep++) {
			// Bind package ID
			r = sqlite3_bind_int64(stmt, 1, id);
			if (r) {
				ERROR(db->pakfire, "Could not bind id: %s\n",
					sqlite3_errmsg(db->handle));
				goto END;
			}

			// Bind type
			r = sqlite3_bind_text(stmt, 2, relation->type, -1, NULL);
			if (r) {
				ERROR(db->pakfire, "Could not bind type: %s\n",
					sqlite3_errmsg(db->handle));
				goto END;
			}

			// Bind dependency
			r = sqlite3_bind_text(stmt, 3, *dep, -1, NULL);
			if (r) {
				ERROR(db->pakfire, "Could not bind dependency: %s\n",
					sqlite3_errmsg(db->handle));
				goto END;
			}

			// Execute query
			do {
				r = sqlite3_step(stmt);
			} while (r == SQLITE_BUSY);

			free(*dep);

			// Reset bound values
			sqlite3_reset(stmt);
		}

		free(list);
	}

	// All okay
	r = 0;

END:
	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

static int pakfire_db_bind_digest(struct pakfire_db* db, sqlite3_stmt* stmt, const int field,
		struct pakfire_file* file, const enum pakfire_digest_types type) {
	const unsigned char* digest = NULL;
	size_t length = 0;

	// Fetch the digest
	digest = pakfire_file_get_digest(file, type, &length);

	// If this digest isn't set, just bind NULL
	if (!digest)
		return sqlite3_bind_null(stmt, field);

	// Otherwise bind the data blob
	return sqlite3_bind_blob(stmt, field, digest, length, NULL);
}

static int pakfire_db_add_files(struct pakfire_db* db, unsigned long id, struct pakfire_archive* archive) {
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	// Get the filelist from the archive
	struct pakfire_filelist* filelist = pakfire_archive_get_filelist(archive);
	if (!filelist) {
		ERROR(db->pakfire, "Could not fetch filelist from archive\n");
		return 1;
	}

	// Nothing to do if the list is empty
	if (pakfire_filelist_is_empty(filelist)) {
		r = 0;
		goto END;
	}

	const char* sql =
		"INSERT INTO "
		"	files("
				"pkg, "
				"path, "
				"size, "
				"config, "
				"datafile, "
				"mode, "
				"user, "
				"'group', "
				"ctime, "
				"mtime, "
				"capabilities, "
				"digest_sha2_512, "
				"digest_sha2_256, "
				"digest_blake2b512, "
				"digest_blake2s256, "
				"digest_sha3_512, "
				"digest_sha3_256"
			") "
			"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto END;
	}

	for (unsigned int i = 0; i < pakfire_filelist_size(filelist); i++) {
		struct pakfire_file* file = pakfire_filelist_get(filelist, i);

		// Bind package ID
		r = sqlite3_bind_int64(stmt, 1, id);
		if (r) {
			ERROR(db->pakfire, "Could not bind id: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind name
		const char* path = pakfire_file_get_path(file);

		r = sqlite3_bind_text(stmt, 2, path, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind path: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind size
		size_t size = pakfire_file_get_size(file);

		r = sqlite3_bind_int64(stmt, 3, size);
		if (r) {
			ERROR(db->pakfire, "Could not bind size: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind config - XXX TODO
		r = sqlite3_bind_null(stmt, 4);
		if (r) {
			ERROR(db->pakfire, "Could not bind config: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind datafile - XXX TODO
		r = sqlite3_bind_null(stmt, 5);
		if (r) {
			ERROR(db->pakfire, "Could not bind datafile: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind mode
		mode_t mode = pakfire_file_get_mode(file);

		r = sqlite3_bind_int64(stmt, 6, mode);
		if (r) {
			ERROR(db->pakfire, "Could not bind mode: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind uname
		const char* uname = pakfire_file_get_uname(file);

		r = sqlite3_bind_text(stmt, 7, uname, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind uname: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind gname
		const char* gname = pakfire_file_get_gname(file);

		r = sqlite3_bind_text(stmt, 8, gname, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind gname: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind ctime
		time_t ctime = pakfire_file_get_ctime(file);

		r = sqlite3_bind_int64(stmt, 9, ctime);
		if (r) {
			ERROR(db->pakfire, "Could not bind ctime: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind mtime
		time_t mtime = pakfire_file_get_mtime(file);

		r = sqlite3_bind_int64(stmt, 10, mtime);
		if (r) {
			ERROR(db->pakfire, "Could not bind mtime: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind capabilities - XXX TODO
		r = sqlite3_bind_null(stmt, 11);
		if (r) {
			ERROR(db->pakfire, "Could not bind capabilities: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// SHA2-512 Digest
		r = pakfire_db_bind_digest(db, stmt, 12, file, PAKFIRE_DIGEST_SHA2_512);
		if (r) {
			ERROR(db->pakfire, "Could not bind SHA2-512 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// SHA2-256 Digest
		r = pakfire_db_bind_digest(db, stmt, 13, file, PAKFIRE_DIGEST_SHA2_256);
		if (r) {
			ERROR(db->pakfire, "Could not bind SHA2-256 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// BLAKE2b512 Digest
		r = pakfire_db_bind_digest(db, stmt, 14, file, PAKFIRE_DIGEST_BLAKE2B512);
		if (r) {
			ERROR(db->pakfire, "Could not bind BLAKE2b512 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// BLAKE2s256 Digest
		r = pakfire_db_bind_digest(db, stmt, 15, file, PAKFIRE_DIGEST_BLAKE2S256);
		if (r) {
			ERROR(db->pakfire, "Could not bind BLAKE2s256 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// SHA3-512 Digest
		r = pakfire_db_bind_digest(db, stmt, 16, file, PAKFIRE_DIGEST_SHA3_512);
		if (r) {
			ERROR(db->pakfire, "Could not bind SHA3-512 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// SHA3-256 Digest
		r = pakfire_db_bind_digest(db, stmt, 17, file, PAKFIRE_DIGEST_SHA3_256);
		if (r) {
			ERROR(db->pakfire, "Could not bind SHA3-256 digest: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Execute query
		do {
			r = sqlite3_step(stmt);
		} while (r == SQLITE_BUSY);

		// Check for errors
		if (r != SQLITE_DONE) {
			ERROR(db->pakfire, "Could not add file to database: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Move on to next file
		pakfire_file_unref(file);

		// Reset bound values
		sqlite3_reset(stmt);
	}

	// All okay
	r = 0;

END:
	if (stmt)
		sqlite3_finalize(stmt);
	pakfire_filelist_unref(filelist);

	return r;
}

static int pakfire_db_add_scriptlets(struct pakfire_db* db, unsigned long id, struct pakfire_archive* archive) {
	sqlite3_stmt* stmt = NULL;
	size_t size;
	int r = 1;

	const char* sql = "INSERT INTO scriptlets(pkg, type, scriptlet) VALUES(?, ?, ?)";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto END;
	}

	for (const char** type = pakfire_scriptlet_types; *type; type++) {
		// Fetch the scriptlet
		struct pakfire_scriptlet* scriptlet = pakfire_archive_get_scriptlet(archive, *type);
		if (!scriptlet)
			continue;

		// Bind package ID
		r = sqlite3_bind_int64(stmt, 1, id);
		if (r) {
			ERROR(db->pakfire, "Could not bind id: %s\n", sqlite3_errmsg(db->handle));
			pakfire_scriptlet_unref(scriptlet);
			goto END;
		}

		// Bind handle
		r = sqlite3_bind_text(stmt, 2, *type, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind type: %s\n", sqlite3_errmsg(db->handle));
			pakfire_scriptlet_unref(scriptlet);
			goto END;
		}

		const char* data = pakfire_scriptlet_get_data(scriptlet, &size);

		// Bind scriptlet
		r = sqlite3_bind_text(stmt, 3, data, size, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind scriptlet: %s\n", sqlite3_errmsg(db->handle));
			pakfire_scriptlet_unref(scriptlet);
			goto END;
		}

		// Execute query
		do {
			r = sqlite3_step(stmt);
		} while (r == SQLITE_BUSY);

		// Check for errors
		if (r != SQLITE_DONE) {
			ERROR(db->pakfire, "Could not add scriptlet to database: %s\n",
				sqlite3_errmsg(db->handle));
			pakfire_scriptlet_unref(scriptlet);
			goto END;
		}

		pakfire_scriptlet_unref(scriptlet);

		// Reset bound values
		sqlite3_reset(stmt);
	}

	// All okay
	r = 0;

END:
	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

int pakfire_db_add_package(struct pakfire_db* db,
		struct pakfire_package* pkg, struct pakfire_archive* archive, int userinstalled) {
	sqlite3_stmt* stmt = NULL;
	char* digest = NULL;
	int r;

	// Begin a new transaction
	r = pakfire_db_begin_transaction(db);
	if (r)
		goto ERROR;

	const char* sql = "INSERT INTO "
		"packages("
			"name, "
			"evr, "
			"arch, "
			"groups, "
			"filename, "
			"size, "
			"inst_size, "
			"digest, "
			"license, "
			"summary, "
			"description, "
			"uuid, "
			"vendor, "
			"build_host, "
			"build_time, "
			"installed, "
			"repository, "
			"userinstalled, "
			"source_name, "
			"source_evr, "
			"source_arch, "
			"distribution"
		") VALUES("
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"CURRENT_TIMESTAMP, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?, "
			"?"
		")";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r != SQLITE_OK) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind name
	const char* name = pakfire_package_get_name(pkg);

	r = sqlite3_bind_text(stmt, 1, name, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind name: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind evr
	const char* evr = pakfire_package_get_evr(pkg);

	r = sqlite3_bind_text(stmt, 2, evr, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind evr: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind arch
	const char* arch = pakfire_package_get_arch(pkg);

	r = sqlite3_bind_text(stmt, 3, arch, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind arch: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind groups
	char* groups = pakfire_package_get_groups(pkg);
	if (groups) {
		r = sqlite3_bind_text(stmt, 4, groups, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind groups: %s\n", sqlite3_errmsg(db->handle));
			free(groups);
			goto ERROR;
		}

		free(groups);

	// No groups
	} else {
		r = sqlite3_bind_null(stmt, 4);
		if (r)
			goto ERROR;
	}

	// Bind filename
	const char* filename = pakfire_package_get_filename(pkg);

	r = sqlite3_bind_text(stmt, 5, filename, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind filename: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind size
	unsigned long long size = pakfire_package_get_downloadsize(pkg);

	r = sqlite3_bind_int64(stmt, 6, size);
	if (r) {
		ERROR(db->pakfire, "Could not bind size: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind installed size
	unsigned long long inst_size = pakfire_package_get_installsize(pkg);

	r = sqlite3_bind_int64(stmt, 7, inst_size);
	if (r) {
		ERROR(db->pakfire, "Could not bind inst_size: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	enum pakfire_digest_types digest_type = 0;

	const char* hexdigest = pakfire_package_get_hexdigest(pkg, &digest_type);
	if (hexdigest) {
		digest = pakfire_db_pack_digest(digest_type, hexdigest);
		if (!digest)
			goto ERROR;

		r = sqlite3_bind_text(stmt, 8, digest, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind digest: %s\n", sqlite3_errmsg(db->handle));
			goto ERROR;
		}
	}

	// Bind license
	const char* license = pakfire_package_get_license(pkg);

	r = sqlite3_bind_text(stmt, 9, license, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind license: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind summary
	const char* summary = pakfire_package_get_summary(pkg);

	r = sqlite3_bind_text(stmt, 10, summary, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind summary: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind description
	const char* description = pakfire_package_get_description(pkg);

	r = sqlite3_bind_text(stmt, 11, description, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind description: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind uuid
	const char* uuid = pakfire_package_get_uuid(pkg);

	r = sqlite3_bind_text(stmt, 12, uuid, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind vendor
	const char* vendor = pakfire_package_get_vendor(pkg);

	r = sqlite3_bind_text(stmt, 13, vendor, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind vendor: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind build_host
	const char* build_host = pakfire_package_get_build_host(pkg);

	r = sqlite3_bind_text(stmt, 14, build_host, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind build_host: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind build_time
	time_t build_time = pakfire_package_get_build_time(pkg);

	r = sqlite3_bind_int64(stmt, 15, build_time);
	if (r) {
		ERROR(db->pakfire, "Could not bind build_time: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind repository name
	struct pakfire_repo* repo = pakfire_package_get_repo(pkg);
	if (repo) {
		const char* repo_name = pakfire_repo_get_name(repo);
		pakfire_repo_unref(repo);

		r = sqlite3_bind_text(stmt, 16, repo_name, -1, NULL);
		if (r)
			goto ERROR;

	// No repository?
	} else {
		r = sqlite3_bind_null(stmt, 16);
		if (r)
			goto ERROR;
	}

	// installed by the user?
	r = sqlite3_bind_int(stmt, 17, userinstalled);
	if (r) {
		ERROR(db->pakfire, "Could not bind userinstalled: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Source package name
	const char* source_name = pakfire_package_get_source_name(pkg);
	if (source_name) {
		r = sqlite3_bind_text(stmt, 18, source_name, -1, NULL);
		if (r)
			goto ERROR;
	} else {
		r = sqlite3_bind_null(stmt, 18);
		if (r)
			goto ERROR;
	}

	// Source EVR
	const char* source_evr = pakfire_package_get_source_evr(pkg);
	if (source_evr) {
		r = sqlite3_bind_text(stmt, 19, source_evr, -1, NULL);
		if (r)
			goto ERROR;
	} else {
		r = sqlite3_bind_null(stmt, 19);
		if (r)
			goto ERROR;
	}

	// Source arch
	const char* source_arch = pakfire_package_get_source_arch(pkg);
	if (source_arch) {
		r = sqlite3_bind_text(stmt, 20, source_arch, -1, NULL);
		if (r)
			goto ERROR;
	} else {
		r = sqlite3_bind_null(stmt, 20);
		if (r)
			goto ERROR;
	}

	// Distribution
	const char* distribution = pakfire_package_get_distribution(pkg);
	if (distribution) {
		r = sqlite3_bind_text(stmt, 21, distribution, -1, NULL);
		if (r)
			goto ERROR;
	} else {
		r = sqlite3_bind_null(stmt, 21);
		if (r)
			goto ERROR;
	}

	// Run query
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	if (r != SQLITE_DONE) {
		ERROR(db->pakfire, "Could not add package to database: %s\n",
			sqlite3_errmsg(db->handle));
		r = 1;
		goto ERROR;
	}

	// Save package ID
	unsigned long packages_id = sqlite3_last_insert_rowid(db->handle);

	// Add dependencies
	r = pakfire_db_add_dependencies(db, packages_id, pkg);
	if (r)
		goto ERROR;

	// Add files
	r = pakfire_db_add_files(db, packages_id, archive);
	if (r)
		goto ERROR;

	// Add scriptlets
	r = pakfire_db_add_scriptlets(db, packages_id, archive);
	if (r)
		goto ERROR;

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);
	if (digest)
		free(digest);

	// Commit or rollback
	if (r)
		pakfire_db_rollback(db);
	else
		r = pakfire_db_commit(db);

	return r;
}

int pakfire_db_remove_package(struct pakfire_db* db, struct pakfire_package* pkg) {
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	// Begin a new transaction
	r = pakfire_db_begin_transaction(db);
	if (r)
		goto ERROR;

	// Fetch the package's UUID
	const char* uuid = pakfire_package_get_uuid(pkg);
	if (!uuid) {
		ERROR(db->pakfire, "Package has no UUID\n");
		r = 1;
		goto ERROR;
	}

	r = sqlite3_prepare_v2(db->handle,
		"DELETE FROM packages WHERE uuid = ?", -1, &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s\n",
			sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind UUID
	r = sqlite3_bind_text(stmt, 1, uuid, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Execute query
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	// Check if we have been successful
	if (r != SQLITE_DONE) {
		ERROR(db->pakfire, "Could not delete package %s\n", uuid);
		r = 1;
		goto ERROR;
	}

	// All done
	r = 0;

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	// Commit or rollback
	if (r)
		pakfire_db_rollback(db);
	else
		r = pakfire_db_commit(db);

	return r;
}

struct pakfire_scriptlet* pakfire_db_get_scriptlet(struct pakfire_db* db,
		struct pakfire_package* pkg, const char* type) {
	struct pakfire_scriptlet* scriptlet = NULL;
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	// Fetch the package's UUID
	const char* uuid = pakfire_package_get_uuid(pkg);
	if (!uuid) {
		ERROR(db->pakfire, "Package has no UUID\n");
		goto ERROR;
	}

	const char* sql = "SELECT scriptlets.scriptlet FROM packages \
		JOIN scriptlets ON packages.id = scriptlets.pkg \
		WHERE packages.uuid = ? AND scriptlets.type = ?";

	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind UUID
	r = sqlite3_bind_text(stmt, 1, uuid, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	r = sqlite3_bind_text(stmt, 2, type, -1, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not bind type: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	DEBUG(db->pakfire, "Searching for scriptlet for package %s of type %s\n", uuid, type);

	// Execute query
	do {
		r = sqlite3_step(stmt);
	} while (r == SQLITE_BUSY);

	// We have some payload
	if (r == SQLITE_ROW) {
		const void* data = sqlite3_column_blob(stmt, 1);
		ssize_t size = sqlite3_column_bytes(stmt, 1);

		// Create a scriptlet object
		r = pakfire_scriptlet_create(&scriptlet, db->pakfire, type, data, size);
		if (r)
			goto ERROR;
	}

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	return scriptlet;
}

static int pakfire_db_load_package(struct pakfire_db* db, struct pakfire_repo* repo, sqlite3_stmt* stmt) {
	struct pakfire_package* pkg = NULL;
	int r = 1;

	// Name
	const char* name = (const char*)sqlite3_column_text(stmt, 0);
	if (!name) {
		ERROR(db->pakfire, "Could not read name: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// EVR
	const char* evr = (const char*)sqlite3_column_text(stmt, 1);
	if (!evr) {
		ERROR(db->pakfire, "Could not read evr: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Arch
	const char* arch = (const char*)sqlite3_column_text(stmt, 2);
	if (!arch) {
		ERROR(db->pakfire, "Could not read arch: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Create package
	pkg = pakfire_package_create(db->pakfire, repo, name, evr, arch);
	if (!pkg) {
		ERROR(db->pakfire, "Could not create package\n");
		goto ERROR;
	}

	// ID
	uint64_t id = sqlite3_column_int64(stmt, 3);
	if (id)
		pakfire_package_set_dbid(pkg, id);

	// Groups
	const char* groups = (const char*)sqlite3_column_text(stmt, 4);
	if (groups) {
		pakfire_package_set_groups(pkg, groups);
	}

	// Filename
	const char* filename = (const char*)sqlite3_column_text(stmt, 5);
	if (filename) {
		pakfire_package_set_filename(pkg, filename);
	}

	// Size
	size_t size = sqlite3_column_int64(stmt, 6);
	if (size) {
		pakfire_package_set_downloadsize(pkg, size);
	}

	// Installed size
	size = sqlite3_column_int64(stmt, 7);
	if (size) {
		pakfire_package_set_installsize(pkg, size);
	}

	// Digest
	const char* digest = (const char*)sqlite3_column_text(stmt, 8);
	if (digest) {
		enum pakfire_digest_types digest_type = 0;

		// Unpack digest
		const char* hexdigest = pakfire_db_unpack_digest(digest, &digest_type);
		if (hexdigest)
			pakfire_package_set_hexdigest(pkg, digest_type, hexdigest);
	}

	// License
	const char* license = (const char*)sqlite3_column_text(stmt, 9);
	if (license) {
		pakfire_package_set_license(pkg, license);
	}

	// Summary
	const char* summary = (const char*)sqlite3_column_text(stmt, 10);
	if (summary) {
		pakfire_package_set_summary(pkg, summary);
	}

	// Description
	const char* description = (const char*)sqlite3_column_text(stmt, 11);
	if (description) {
		pakfire_package_set_description(pkg, description);
	}

	// UUID
	const char* uuid = (const char*)sqlite3_column_text(stmt, 12);
	if (uuid) {
		pakfire_package_set_uuid(pkg, uuid);
	}

	// Vendor
	const char* vendor = (const char*)sqlite3_column_text(stmt, 13);
	if (vendor) {
		pakfire_package_set_vendor(pkg, vendor);
	}

	// Build Host
	const char* build_host = (const char*)sqlite3_column_text(stmt, 14);
	if (build_host) {
		pakfire_package_set_build_host(pkg, build_host);
	}

	// Build Time
	time_t build_time = sqlite3_column_int64(stmt, 15);
	if (build_time) {
		pakfire_package_set_build_time(pkg, build_time);
	}

	// Install Time
	time_t install_time = sqlite3_column_int64(stmt, 16);
	if (install_time) {
		pakfire_package_set_install_time(pkg, install_time);
	}

	// installed by user?
	int userinstalled = sqlite3_column_int(stmt, 17);
	if (userinstalled)
		pakfire_db_add_userinstalled(db->pakfire, name);

	// Files
	const char* files = (const char*)sqlite3_column_text(stmt, 18);
	if (files) {
		r = pakfire_package_set_filelist_from_string(pkg, files);
		if (r)
			goto ERROR;
	}

	// Dependencies

	const struct dependency {
		unsigned int field;
		void (*func)(struct pakfire_package* pkg, const char* dep);
	} dependencies[] = {
		{ 19, pakfire_package_add_provides },
		{ 20, pakfire_package_add_prerequires },
		{ 21, pakfire_package_add_requires },
		{ 22, pakfire_package_add_conflicts },
		{ 23, pakfire_package_add_obsoletes },
		{ 24, pakfire_package_add_recommends },
		{ 25, pakfire_package_add_suggests },
		{ 26, pakfire_package_add_supplements },
		{ 27, pakfire_package_add_enhances },
		{ 0, NULL },
	};

	for (const struct dependency* deps = dependencies; deps->field; deps++) {
		const char* relations = (const char*)sqlite3_column_text(stmt, deps->field);
		if (relations) {
			pakfire_str2deps(db->pakfire, pkg, deps->func, relations);
		}
	}

	// Source package
	const char* source_name = (const char*)sqlite3_column_text(stmt, 28);
	if (source_name)
		pakfire_package_set_source_name(pkg, source_name);

	// Source EVR
	const char* source_evr = (const char*)sqlite3_column_text(stmt, 29);
	if (source_evr)
		pakfire_package_set_source_evr(pkg, source_evr);

	// Source arch
	const char* source_arch = (const char*)sqlite3_column_text(stmt, 30);
	if (source_arch)
		pakfire_package_set_source_arch(pkg, source_arch);

	// Distribution
	const char* distribution = (const char*)sqlite3_column_text(stmt, 31);
	if (distribution)
		pakfire_package_set_distribution(pkg, distribution);

	// Success
	r = 0;

ERROR:
	if (pkg)
		pakfire_package_unref(pkg);

	return r;
}

int pakfire_db_load(struct pakfire_db* db, struct pakfire_repo* repo) {
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	DEBUG(db->pakfire, "Loading package database...\n");

	// Drop contents of the repository
	pakfire_repo_clear(repo);

	// Save starting time
	clock_t t_start = clock();
	clock_t t_end;

	const char* sql =
		"SELECT "
			"name, "
			"evr, "
			"arch, "
			"id, "
			"groups, "
			"filename, "
			"size, "
			"inst_size, "
			"digest, "
			"license, "
			"summary, "
			"description, "
			"uuid, "
			"vendor, "
			"build_host, "
			"build_time, "
			"strftime('%s', installed) AS installed, "
			"userinstalled, "
			"("
				"SELECT group_concat(path, '\n') FROM files WHERE files.pkg = packages.id"
			") AS files, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'provides'"
			") AS provides, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'prerequires'"
			") AS prerequires, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'requires'"
			") AS requires, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'conflicts'"
			") AS conflicts, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'obsoletes'"
			") AS obsoletes, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'recommends'"
			") AS recommends, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'suggests'"
			") AS suggests, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'supplements'"
			") AS supplements, "
			"("
				"SELECT group_concat(dependency, '\n') FROM dependencies d "
					"WHERE d.pkg = packages.id AND d.type = 'enhances'"
			") AS enhances, "
			"source_name, "
			"source_evr, "
			"source_arch, "
			"distribution "
		"FROM "
			"packages"
		";";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	for (;;) {
		// Execute query
		r = sqlite3_step(stmt);

		switch (r) {
			// Retry if the database was busy
			case SQLITE_BUSY:
				continue;

			// Read a row
			case SQLITE_ROW:
				r = pakfire_db_load_package(db, repo, stmt);
				if (r)
					goto ERROR;
				break;

			// All rows have been processed
			case SQLITE_DONE:
				goto END;

			// Go to error in any other cases
			default:
				goto ERROR;
		}
	}

END:
	// Save time when we finished
	t_end = clock();

	DEBUG(db->pakfire, "Loading package database completed in %.4fms\n",
		(double)(t_end - t_start) * 1000 / CLOCKS_PER_SEC);

	// Mark repository as changed
	pakfire_repo_has_changed(repo);

	// All done
	r = 0;

ERROR:
	if (r) {
		ERROR(db->pakfire, "Failed reading package database: %d\n", r);
		pakfire_repo_clear(repo);
	}

	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

static int pakfire_db_load_file_digest(struct pakfire_db* db, struct pakfire_file* file,
		sqlite3_stmt* stmt, const enum pakfire_digest_types type, const int field) {
	// Fetch digest
	const unsigned char* digest = sqlite3_column_blob(stmt, field);

	// Nothing further to do if field is NULL
	if (!digest)
		return 0;

	// Length of the stored value
	const size_t length = sqlite3_column_bytes(stmt, field);

	// Store digest
	return pakfire_file_set_digest(file, type, digest, length);
}

static int pakfire_db_load_file(struct pakfire_db* db, struct pakfire_filelist* filelist,
		sqlite3_stmt* stmt) {
	struct pakfire_file* file = NULL;
	int r;

	// Create a new file object
	r = pakfire_file_create(&file, db->pakfire);
	if (r)
		goto ERROR;

	// Path
	const char* path = (const char*)sqlite3_column_text(stmt, 0);

	// Abort if no path is set
	if (!path) {
		ERROR(db->pakfire, "File has no path\n");
		r = 1;
		goto ERROR;
	}

	// Set path
	r = pakfire_file_set_path(file, path);
	if (r) {
		ERROR(db->pakfire, "%s: Could not set path '%s': %m\n", path, path);
		goto ERROR;
	}

	// Size
	size_t size = sqlite3_column_int64(stmt, 1);
	if (size)
		pakfire_file_set_size(file, size);

	// Mode
	mode_t mode = sqlite3_column_int(stmt, 2);
	if (mode)
		pakfire_file_set_mode(file, mode);

	// uname
	const char* uname = (const char*)sqlite3_column_text(stmt, 3);

	// Abort if no user is set
	if (!uname) {
		ERROR(db->pakfire, "%s: No user\n", path);
		r = 1;
		goto ERROR;
	}

	// Set uname
	r = pakfire_file_set_uname(file, uname);
	if (r) {
		ERROR(db->pakfire, "%s: Could not set user '%s': %m\n", path, uname);
		goto ERROR;
	}

	// gname
	const char* gname = (const char*)sqlite3_column_text(stmt, 4);

	// Abort if no group is set
	if (!gname) {
		ERROR(db->pakfire, "%s: No group\n", path);
		r = 1;
		goto ERROR;
	}

	// Set gname
	r = pakfire_file_set_gname(file, gname);
	if (r) {
		ERROR(db->pakfire, "%s: Could not set group '%s': %m\n", path, gname);
		goto ERROR;
	}

	// ctime
	time_t ctime = sqlite3_column_int64(stmt, 5);
	if (ctime)
		pakfire_file_set_ctime(file, ctime);

	// mtime
	time_t mtime = sqlite3_column_int64(stmt, 6);
	if (mtime)
		pakfire_file_set_mtime(file, mtime);

	// SHA2-512 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_SHA2_512, 7);
	if (r)
		goto ERROR;

	// SHA2-256 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_SHA2_256, 8);
	if (r)
		goto ERROR;

	// BLAKE2b512 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_BLAKE2B512, 9);
	if (r)
		goto ERROR;

	// BLAKE2s256 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_BLAKE2S256, 10);
	if (r)
		goto ERROR;

	// SHA3-512 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_SHA3_512, 11);
	if (r)
		goto ERROR;

	// SHA3-256 Digest
	r = pakfire_db_load_file_digest(db, file, stmt, PAKFIRE_DIGEST_SHA3_256, 12);
	if (r)
		goto ERROR;

	// Append the file to the filelist
	r = pakfire_filelist_append(filelist, file);
	if (r)
		goto ERROR;

ERROR:
	if (file)
		pakfire_file_unref(file);

	return r;
}

int pakfire_db_filelist(struct pakfire_db* db, struct pakfire_filelist** filelist) {
	struct pakfire_filelist* list = NULL;
	sqlite3_stmt* stmt = NULL;
	int r;

	const char* sql =
		"SELECT "
			"path, "
			"size, "
			"mode, "
			"uname, "
			"gname, "
			"ctime, "
			"mtime, "
			"digest_sha2_512, "
			"digest_sha2_256, "
			"digest_blake2b512, "
			"digest_blake2s256, "
			"digest_sha3_512, "
			"digest_sha3_256 "
		"FROM files "
		"ORDER BY path"
		";";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Create a new filelist
	r = pakfire_filelist_create(&list, db->pakfire);
	if (r)
		goto ERROR;

	// Iterate over all rows
	for (;;) {
		// Execute query
		r = sqlite3_step(stmt);

		switch (r) {
			// Retry if the database was busy
			case SQLITE_BUSY:
				continue;

			// Read a row
			case SQLITE_ROW:
				r = pakfire_db_load_file(db, list, stmt);
				if (r)
					goto ERROR;
				break;

			// All rows have been processed
			case SQLITE_DONE:
				r = 0;
				goto END;

			// Go to error in any other cases
			default:
				goto ERROR;
		}
	}

END:
	// Return the filelist
	*filelist = pakfire_filelist_ref(list);

ERROR:
	if (r)
		ERROR(db->pakfire, "Could not fetch filelist: %m\n");

	if (stmt)
		sqlite3_finalize(stmt);
	if (list)
		pakfire_filelist_unref(list);

	return r;
}

int pakfire_db_package_filelist(struct pakfire_db* db, struct pakfire_filelist** filelist,
		struct pakfire_package* pkg) {
	struct pakfire_filelist* fl = NULL;
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	// Fetch the package ID
	uint64_t id = pakfire_package_get_dbid(pkg);
	if (!id) {
		ERROR(db->pakfire, "Package did not have an ID\n");
		return 1;
	}

	// Create a new filelist
	r = pakfire_filelist_create(&fl, db->pakfire);
	if (r) {
		ERROR(db->pakfire, "Could not create filelist: %m\n");
		goto ERROR;
	}

	const char* sql =
		"SELECT "
			"path, "
			"size, "
			"mode, "
			"uname, "
			"gname, "
			"ctime, "
			"mtime, "
			"digest_sha2_512, "
			"digest_sha2_256, "
			"digest_blake2b512, "
			"digest_blake2s256, "
			"digest_sha3_512, "
			"digest_sha3_256 "
		"FROM files "

		// Select all files that belong to this package
		"WHERE "
			"pkg = ? "

		// Filter out any files that are also in a different package (i.e. an update
		// that has already been installed and this is the cleanup of the obsolete pkg)
		"AND "
			"NOT EXISTS ("
				"SELECT "
					"1 "
				"FROM files AS duplicates "
				"WHERE "
					"files.path = duplicates.path "
				"AND "
					"files.pkg != duplicates.pkg"
			") "

		// Return the longest paths first
		"ORDER BY "
			"length(path) DESC"
		";";

	// Prepare the statement
	r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
	if (r) {
		ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
			sql, sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	// Bind package ID
	r = sqlite3_bind_int64(stmt, 1, id);
	if (r) {
		ERROR(db->pakfire, "Could not bind package ID: %s\n", sqlite3_errmsg(db->handle));
		goto ERROR;
	}

	for (;;) {
		// Execute query
		r = sqlite3_step(stmt);

		switch (r) {
			// Retry if the database was busy
			case SQLITE_BUSY:
				continue;

			// Read a row
			case SQLITE_ROW:
				r = pakfire_db_load_file(db, fl, stmt);
				if (r)
					goto ERROR;
				break;

			// All rows have been processed
			case SQLITE_DONE:
				goto END;

			// Go to error in any other cases
			default:
				goto ERROR;
		}
	}

END:
	*filelist = pakfire_filelist_ref(fl);
	r = 0;

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);
	if (fl)
		pakfire_filelist_unref(fl);

	return r;
}