db: Read userinstalled packages when loading database

[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 <solv/solver.h>
#include <sqlite3.h>

#include <pakfire/archive.h>
#include <pakfire/db.h>
#include <pakfire/file.h>
#include <pakfire/logging.h>
#include <pakfire/package.h>
#include <pakfire/pakfire.h>
#include <pakfire/repo.h>
#include <pakfire/types.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 {
	Pakfire pakfire;
	int nrefs;

	char path[PATH_MAX];
	int mode;

	sqlite3* handle;
	int schema;
};

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

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

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) {
	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 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 int pakfire_db_set_int(struct pakfire_db* db, const char* key, int val) {
	sqlite3_stmt* stmt = NULL;
	int r;

	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 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, "
			"hash1          TEXT, "
			"license        TEXT, "
			"summary        TEXT, "
			"description    TEXT, "
			"uuid           TEXT, "
			"vendor         TEXT, "
			"build_host     TEXT, "
			"build_time     INTEGER, "
			"installed      INTEGER, "
			"userinstalled  INTEGER, "
			"repository     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, "
			"type           INTEGER, "
			"config         INTEGER, "
			"datafile       INTEGER, "
			"mode           INTEGER, "
			"user           TEXT, "
			"'group'        TEXT, "
			"hash1          TEXT, "
			"ctime          INTEGER, "
			"mtime          INTEGER, "
			"capabilities   TEXT, "
			"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;

	// 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;

	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_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;
	}

	// 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, 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_make_path(o->pakfire, o->path, DATABASE_PATH);
	if (r < 0)
		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;

	*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(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 int pakfire_db_add_dependencies(struct pakfire_db* db, unsigned long id, PakfirePackage 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)(PakfirePackage);
	} 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_add_files(struct pakfire_db* db, unsigned long id, PakfireArchive archive) {
	sqlite3_stmt* stmt = NULL;
	int r = 1;

	// Get the filelist from the archive
	PakfireFilelist 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, type, config, datafile, mode, "
		"user, 'group', hash1, ctime, mtime, capabilities) 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 (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 type - XXX this is char which isn't very helpful
		//char type = pakfire_file_get_type(file);

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

		// Bind config - XXX TODO
		r = sqlite3_bind_null(stmt, 5);
		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, 6);
		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, 7, mode);
		if (r) {
			ERROR(db->pakfire, "Could not bind mode: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

		// Bind user
		const char* user = pakfire_file_get_user(file);

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

		// Bind group
		const char* group = pakfire_file_get_group(file);

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

		// Bind hash1
		const char* chksum = pakfire_file_get_chksum(file);

		r = sqlite3_bind_text(stmt, 10, chksum, -1, NULL);
		if (r) {
			ERROR(db->pakfire, "Could not bind hash1: %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, 11, 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, 12, 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, 13);
		if (r) {
			ERROR(db->pakfire, "Could not bind capabilities: %s\n", sqlite3_errmsg(db->handle));
			pakfire_file_unref(file);
			goto END;
		}

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

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

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

		// 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, PakfireArchive 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,
		PakfirePackage pkg, PakfireArchive archive, int userinstalled) {
	sqlite3_stmt* stmt = NULL;
	int r;

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

	const char* sql = "INSERT INTO packages(name, evr, arch, groups, filename, size, "
		"inst_size, hash1, license, summary, description, uuid, vendor, build_host, "
		"build_time, installed, repository, userinstalled) "
		"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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
		}

		free(groups);

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

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// Bind hash1
	const char* hash1 = pakfire_package_get_checksum(pkg);

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

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// 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 ROLLBACK;
	}

	// Bind repository name
	PakfireRepo 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 ROLLBACK;

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

	// 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 ROLLBACK;
	}

	// 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));
		goto ROLLBACK;
	}

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

	// This is done
	r = sqlite3_finalize(stmt);
	if (r == SQLITE_OK)
		stmt = NULL;

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

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

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

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

	return 0;

ROLLBACK:
	if (stmt)
		sqlite3_finalize(stmt);

	pakfire_db_rollback(db);

	return 1;
}

int pakfire_db_remove_package(struct pakfire_db* db, PakfirePackage pkg) {
	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;
	}

	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));
		return 1;
	}

	// 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;
	}

	r = 0;

ERROR:
	if (stmt)
		sqlite3_finalize(stmt);

	return r;
}

struct pakfire_scriptlet* pakfire_db_get_scriptlet(struct pakfire_db* db,
		PakfirePackage 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 scriptlet.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, PakfireRepo repo, sqlite3_stmt* stmt) {
	PakfirePackage 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;
	}

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

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

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

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

	// Hash 1
	const char* hash1 = (const char*)sqlite3_column_text(stmt, 7);
	if (hash1) {
		pakfire_package_set_checksum(pkg, hash1);
	}

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

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

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

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

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

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

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

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

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

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

	// Dependencies

	const struct dependency {
		unsigned int field;
		void (*func)(PakfirePackage pkg, const char* dep);
	} dependencies[] = {
		{ 18, pakfire_package_add_provides },
		{ 19, pakfire_package_add_prerequires },
		{ 20, pakfire_package_add_requires },
		{ 21, pakfire_package_add_conflicts },
		{ 22, pakfire_package_add_obsoletes },
		{ 23, pakfire_package_add_recommends },
		{ 24, pakfire_package_add_suggests },
		{ 25, pakfire_package_add_supplements },
		{ 26, 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_parse_deps(db->pakfire, pkg, deps->func, relations);
		}
	}

	// Success
	r = 0;

ERROR:
	if (pkg)
		pakfire_package_unref(pkg);

	return r;
}

int pakfire_db_load(struct pakfire_db* db, PakfireRepo 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, groups, filename, size, inst_size, hash1, 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 "
		"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);

	// Internalize repository
	pakfire_repo_internalize(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;
}