<commit_before>#include <sys/param.h>
#include <sys/stat.h>
#include <sys/types.h>

#include <assert.h>
#include <err.h>
#include <errno.h>
#include <regex.h>
#include <sha256.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

#include <sqlite3.h>

#include "pkg.h"
#include "pkg_error.h"
#include "pkg_private.h"
#include "pkgdb.h"
#include "pkg_util.h"

#define PKG_DBDIR "/var/db/pkg"

static struct pkgdb_it * pkgdb_it_new(struct pkgdb *, sqlite3_stmt *);
static void pkgdb_regex(sqlite3_context *, int, sqlite3_value **, int);
static void pkgdb_regex_basic(sqlite3_context *, int, sqlite3_value **);
static void pkgdb_regex_extended(sqlite3_context *, int, sqlite3_value **);
static void pkgdb_regex_delete(void *);

static void
pkgdb_regex_delete(void *p)
{
	regex_t *re = (regex_t *)p;

	regfree(re);
	free(re);
}

static void
pkgdb_regex(sqlite3_context *ctx, int argc, sqlite3_value **argv, int reg_type)
{
	const char *regex = NULL;
	const char *str;
	regex_t *re;
	int ret;

	if (argc != 2 || (regex = sqlite3_value_text(argv[0])) == NULL ||
		(str = sqlite3_value_text(argv[1])) == NULL) {
		sqlite3_result_error(ctx, "SQL function regex() called with invalid arguments.\n", -1);
		return;
	}

	re = (regex_t *)sqlite3_get_auxdata(ctx, 0);
	if (re == NULL) {
		re = malloc(sizeof(regex_t));
		if (regcomp(re, regex, reg_type | REG_NOSUB) != 0) {
			sqlite3_result_error(ctx, "Invalid regex\n", -1);
			free(re);
			return;
		}

		sqlite3_set_auxdata(ctx, 0, re, pkgdb_regex_delete);
	}

	ret = regexec(re, str, 0, NULL, 0);
	sqlite3_result_int(ctx, (ret != REG_NOMATCH));
}

static void
pkgdb_regex_basic(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
	pkgdb_regex(ctx, argc, argv, REG_BASIC);
}

static void
pkgdb_regex_extended(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
	pkgdb_regex(ctx, argc, argv, REG_EXTENDED);
}

const char *
pkgdb_get_dir(void)
{
	const char *pkg_dbdir;

	if ((pkg_dbdir = getenv("PKG_DBDIR")) == NULL)
		pkg_dbdir = PKG_DBDIR;

	return pkg_dbdir;
}

/*
 * in the database : 
 * scripts.type can be:
 * - 0: PRE_INSTALL
 * - 1: POST_INSTALL
 * - 2: PRE_DEINSTALL
 * - 3: POST_DEINSTALL
 * - 4: PRE_UPGRADE
 * - 5: POST_UPGRADE
 * - 6: INSTALL
 * - 7: DEINSTALL
 * - 8: UPGRADE
 * 
 * exec.type can be:
 * - 0: exec
 * - 1: unexec
 *
 */

static int
pkgdb_init(sqlite3 *sdb)
{
	char *errmsg;
	const char sql[] = ""
	"CREATE TABLE packages ("
		"origin TEXT PRIMARY KEY,"
		"name TEXT,"
		"version TEXT,"
		"comment TEXT,"
		"desc TEXT,"
		"mtree_id INTEGER REFERENCES mtree(rowid) ON DELETE RESTRICT"
			" ON UPDATE CASCADE,"
		"message TEXT,"
		"arch TEXT,"
		"osversion TEXT,"
		"maintainer TEXT,"
		"www TEXT,"
		"prefix TEXT,"
		"flatsize INTEGER,"
		"automatic INTEGER,"
		"pkg_format_version INTEGER"
	");"
	"CREATE TABLE scripts ("
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"script TEXT,"
		"type INTEGER,"
		"PRIMARY KEY (package_id, type)"
	");"
	"CREATE INDEX scripts_package ON scripts(package_id);"
	"CREATE TABLE exec ("
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"cmd TEXT,"
		"type INTEGER"
	");"
	"CREATE INDEX exec_package ON exec(package_id);"
	"CREATE TABLE options ("
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"option TEXT,"
		"value TEXT,"
		"PRIMARY KEY (package_id,option)"
	");"
	"CREATE INDEX options_package ON options(package_id);"
	"CREATE TABLE deps ("
		"origin TEXT,"
		"name TEXT,"
		"version TEXT,"
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"PRIMARY KEY (package_id,origin)"
	");"
	"CREATE INDEX deps_origin ON deps(origin);"
	"CREATE INDEX deps_package ON deps(package_id);"
	"CREATE TABLE files ("
		"path TEXT PRIMARY KEY,"
		"sha256 TEXT,"
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE"
	");"
	"CREATE INDEX files_package ON files(package_id);"
	"CREATE TABLE conflicts ("
		"name TEXT,"
		"package_id INTEGER REFERENCES packages(rowid) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"PRIMARY KEY (package_id,name)"
	");"
	"CREATE INDEX conflicts_package ON conflicts(package_id);"
	"CREATE TABLE mtree ("
		"sha256 TEXT PRIMARY KEY,"
		"content TEXT"
	");"
	"CREATE TRIGGER clean_mtree AFTER DELETE ON packages BEGIN "
		"DELETE FROM mtree WHERE rowid NOT IN (SELECT DISTINCT mtree_id FROM packages);"
	"END;";

	if (sqlite3_exec(sdb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	return (EPKG_OK);
}

int
pkgdb_open(struct pkgdb **db)
{
	int retcode;
	struct stat st;
	char *errmsg;
	char fpath[MAXPATHLEN];

	snprintf(fpath, sizeof(fpath), "%s/pkg.db", pkgdb_get_dir());

	if ((*db = calloc(1, sizeof(struct pkgdb))) == NULL)
		return (pkg_error_set(EPKG_FATAL, "calloc(): %s", strerror(errno)));

	if ((retcode = stat(fpath, &st)) == -1 && errno != ENOENT)
		return (pkg_error_set(EPKG_FATAL, "can not stat %s: %s", fpath,
							  strerror(errno)));

	if (sqlite3_open(fpath, &(*db)->sqlite) != SQLITE_OK)
		return (ERROR_SQLITE((*db)->sqlite));

	/* If the database is missing we have to initialize it */
	if (retcode == -1)
		if ((retcode = pkgdb_init((*db)->sqlite)) != EPKG_OK)
			return (retcode);

	sqlite3_create_function((*db)->sqlite, "regexp", 2, SQLITE_ANY, NULL,
							pkgdb_regex_basic, NULL, NULL);
	sqlite3_create_function((*db)->sqlite, "eregexp", 2, SQLITE_ANY, NULL,
							pkgdb_regex_extended, NULL, NULL);

	/* 
	 * allow forign key option which will allow to have clean support for
	 * reinstalling
	 */
	if (sqlite3_exec((*db)->sqlite, "PRAGMA foreign_keys = ON;", NULL, NULL,
		&errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	return (EPKG_OK);
}

void
pkgdb_close(struct pkgdb *db)
{
	if (db == NULL)
		return;

	if (db->sqlite != NULL)
		sqlite3_close(db->sqlite);

	free(db);
}

static struct pkgdb_it *
pkgdb_it_new(struct pkgdb *db, sqlite3_stmt *s)
{
	struct pkgdb_it *it;

	if ((it = malloc(sizeof(struct pkgdb_it))) == NULL) {
		pkg_error_set(EPKG_FATAL, "malloc(): %s", strerror(errno));
		sqlite3_finalize(s);
		return (NULL);
	}

	it->db = db;
	it->stmt = s;
	return (it);
}

int
pkgdb_it_next(struct pkgdb_it *it, struct pkg **pkg_p, int flags)
{
	struct pkg *pkg;
	int ret;

	if (it == NULL)
		return (ERROR_BAD_ARG("it"));

	switch (sqlite3_step(it->stmt)) {
	case SQLITE_ROW:
		if (*pkg_p == NULL)
			pkg_new(pkg_p);
		else
			pkg_reset(*pkg_p);
		pkg = *pkg_p;

		pkg->type = PKG_INSTALLED;
		pkg->rowid = sqlite3_column_int(it->stmt, 0);
		pkg_set(pkg, PKG_ORIGIN, sqlite3_column_text(it->stmt, 1));
		pkg_set(pkg, PKG_NAME, sqlite3_column_text(it->stmt, 2));
		pkg_set(pkg, PKG_VERSION, sqlite3_column_text(it->stmt, 3));
		pkg_set(pkg, PKG_COMMENT, sqlite3_column_text(it->stmt, 4));
		pkg_set(pkg, PKG_DESC, sqlite3_column_text(it->stmt, 5));
		pkg_set(pkg, PKG_MTREE, sqlite3_column_text(it->stmt, 6));
		pkg_set(pkg, PKG_MESSAGE, sqlite3_column_text(it->stmt, 7));
		pkg_set(pkg, PKG_ARCH, sqlite3_column_text(it->stmt, 8));
		pkg_set(pkg, PKG_OSVERSION, sqlite3_column_text(it->stmt, 9));
		pkg_set(pkg, PKG_MAINTAINER, sqlite3_column_text(it->stmt, 10));
		pkg_set(pkg, PKG_WWW, sqlite3_column_text(it->stmt, 11));
		pkg_set(pkg, PKG_PREFIX, sqlite3_column_text(it->stmt, 12));
		pkg_setflatsize(pkg, sqlite3_column_int64(it->stmt, 13));

		if (flags & PKG_DEPS)
			if ((ret = pkgdb_pkg_loaddeps(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_RDEPS)
			if ((ret = pkgdb_pkg_loadrdeps(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_CONFLICTS)
			if ((ret = pkgdb_pkg_loadconflicts(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_FILES)
			if ((ret = pkgdb_pkg_loadfiles(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_EXECS)
			if ((ret = pkgdb_pkg_loadexecs(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_SCRIPTS)
			if ((ret = pkgdb_pkg_loadscripts(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_OPTIONS)
			if ((ret = pkgdb_pkg_loadoptions(it->db, pkg)) != EPKG_OK)
				return (ret);

		return (EPKG_OK);
	case SQLITE_DONE:
		return (EPKG_END);
	default:
		return (ERROR_SQLITE(it->db->sqlite));
	}
}

void
pkgdb_it_free(struct pkgdb_it *it)
{
	if (it != NULL) {
		sqlite3_finalize(it->stmt);
		free(it);
	}
}

struct pkgdb_it *
pkgdb_query(struct pkgdb *db, const char *pattern, match_t match)
{
	char sql[BUFSIZ];
	sqlite3_stmt *stmt;
	const char *comp = NULL;
	char *checkorigin = NULL;

	if (match != MATCH_ALL && pattern == NULL) {
		ERROR_BAD_ARG("pattern");
		return (NULL);
	}

	if (pattern != NULL)
		checkorigin = strchr(pattern, '/');

	switch (match) {
	case MATCH_ALL:
		comp = "";
		break;
	case MATCH_EXACT:
		if (checkorigin == NULL)
			comp = " AND name = ?1";
		else
			comp = " AND origin = ?1";
		break;
	case MATCH_GLOB:
		if (checkorigin == NULL)
			comp = " AND name GLOB ?1";
		else
			comp = " AND origin GLOB ?1";
		break;
	case MATCH_REGEX:
		if (checkorigin == NULL)
			comp = " AND name REGEXP ?1";
		else
			comp = " AND origin REGEXP ?1";
		break;
	case MATCH_EREGEX:
		if (checkorigin == NULL)
			comp = " AND EREGEXP(?1, name)";
		else
			comp = " AND EREGEXP(?1, origin)";
		break;
	}

	snprintf(sql, sizeof(sql),
			"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
				"p.message, p.arch, p.osversion, p.maintainer, p.www, "
				"p.prefix, p.flatsize "
			"FROM packages AS p, mtree AS m "
			"WHERE m.rowid = p.mtree_id%s;", comp);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK) {
		ERROR_SQLITE(db->sqlite);
		return (NULL);
	}

	if (match != MATCH_ALL)
		sqlite3_bind_text(stmt, 1, pattern, -1, SQLITE_TRANSIENT);

	return (pkgdb_it_new(db, stmt));
}

struct pkgdb_it *
pkgdb_query_which(struct pkgdb *db, const char *path)
{
	sqlite3_stmt *stmt;
	const char sql[] = ""
		"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
			"p.message, p.arch, p.osversion, p.maintainer, p.www, "
			"p.prefix, p.flatsize "
			"FROM packages AS p, mtree AS m, files AS f "
			"WHERE m.rowid = p.mtree_id " 
				"AND p.rowid = f.package_id "
				"AND f.path = ?1;";

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK) {
		ERROR_SQLITE(db->sqlite);
		return (NULL);
	}

	sqlite3_bind_text(stmt, 1, path, -1, SQLITE_TRANSIENT);

	return (pkgdb_it_new(db, stmt));
}

int
pkgdb_pkg_loaddeps(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkgdb_it it;
	struct pkg *p;
	int ret;
	const char sql[] = ""
	"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
		"p.message, p.arch, p.osversion, p.maintainer, p.www, "
		"p.prefix, p.flatsize "
	"FROM packages AS p, mtree AS m, deps AS d "
	"WHERE m.rowid = p.mtree_id "
		"AND p.origin = d.origin "
		"AND d.package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_DEPS)
		return (EPKG_OK);

	array_init(&pkg->deps, 10);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	it.stmt = stmt;
	it.db = db;

	p = NULL;
	while ((ret = pkgdb_it_next(&it, &p, PKG_BASIC)) == EPKG_OK) {
		array_append(&pkg->deps, p);
		p = NULL;
	}

	sqlite3_finalize(stmt);

	if (ret != EPKG_END) {
		array_reset(&pkg->deps, &pkg_free_void);
		return (ret);
	}

	pkg->flags |= PKG_DEPS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadrdeps(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkgdb_it it;
	struct pkg *p;
	int ret;
	const char sql[] = ""
		"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
			"p.message, p.arch, p.osversion, p.maintainer, p.www, "
			"p.prefix, p.flatsize "
		"FROM packages AS p, mtree AS m, deps AS d "
		"WHERE m.id = p.mtree_id "
			"AND p.rowid = d.package_id "
			"AND d.origin = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_RDEPS)
		return (EPKG_OK);

	array_init(&pkg->rdeps, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_text(stmt, 1, pkg_get(pkg, PKG_ORIGIN), -1, SQLITE_STATIC);

	it.stmt = stmt;
	it.db = db;

	p = NULL;
	while ((ret = pkgdb_it_next(&it, &p, PKG_BASIC)) == EPKG_OK) {
		array_append(&pkg->rdeps, p);
		p = NULL;
	}
	sqlite3_finalize(stmt);

	if (ret != EPKG_END) {
		array_reset(&pkg->rdeps, &pkg_free_void);
		return (ret);
	}

	pkg->flags |= PKG_RDEPS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadconflicts(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_conflict *c;
	int ret;
	const char sql[] = ""
		"SELECT name "
		"FROM conflicts "
		"WHERE package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_CONFLICTS)
		return (EPKG_OK);

	array_init(&pkg->conflicts, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt) == SQLITE_ROW)) {
		pkg_conflict_new(&c);
		sbuf_set(&c->glob, sqlite3_column_text(stmt, 0));
		array_append(&pkg->conflicts, c);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->conflicts, &pkg_conflict_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_CONFLICTS;

	return (EPKG_OK);
}

int
pkgdb_pkg_loadfiles(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_file *f;
	int ret;
	const char sql[] = ""
		"SELECT path, sha256 "
		"FROM files "
		"WHERE package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_FILES)
		return (EPKG_OK);

	array_init(&pkg->files, 10);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_file_new(&f);
		strlcpy(f->path, sqlite3_column_text(stmt, 0), sizeof(f->path));
		strlcpy(f->sha256, sqlite3_column_text(stmt, 1), sizeof(f->sha256));
		array_append(&pkg->files, f);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->files, &free);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_FILES;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadexecs(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_exec *e;
	int ret;
	const char sql[] = ""
		"SELECT cmd, type "
		"FROM exec "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_EXECS)
		return (EPKG_OK);

	array_init(&pkg->exec, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_exec_new(&e);
		sbuf_set(&e->cmd, sqlite3_column_text(stmt, 0));
		e->type = sqlite3_column_int(stmt, 1);
		array_append(&pkg->exec, e);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->exec, &pkg_exec_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_EXECS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadscripts(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_script *s;
	int ret;
	const char sql[] = ""
		"SELECT script, type "
		"FROM scripts "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_SCRIPTS)
		return (EPKG_OK);

	array_init(&pkg->scripts, 6);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_script_new(&s);
		sbuf_set(&s->data, sqlite3_column_text(stmt, 0));
		s->type = sqlite3_column_int(stmt, 1);
		array_append(&pkg->scripts, s);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->scripts, &pkg_script_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_SCRIPTS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadoptions(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_option *o;
	int ret;
	const char sql[] = ""
		"SELECT option, value "
		"FROM options "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_OPTIONS)
		return (EPKG_OK);

	array_init(&pkg->options, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_option_new(&o);
		sbuf_set(&o->opt, sqlite3_column_text(stmt, 0));
		sbuf_set(&o->value, sqlite3_column_text(stmt, 1));
		array_append(&pkg->options, o);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->options, &pkg_option_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_OPTIONS;
	return (EPKG_OK);
}

int
pkgdb_register_pkg(struct pkgdb *db, struct pkg *pkg)
{
	struct pkg **deps;
	struct pkg_file **files;
	struct pkg_conflict **conflicts;
	struct pkg_exec **execs;
	struct pkg_script **scripts;
	struct pkg_option **options;

	sqlite3 *s;
	sqlite3_stmt *stmt_pkg = NULL;
	sqlite3_stmt *stmt_mtree = NULL;
	sqlite3_stmt *stmt_dep = NULL;
	sqlite3_stmt *stmt_conflict = NULL;
	sqlite3_stmt *stmt_file = NULL;
	sqlite3_stmt *stmt_exec = NULL;
	sqlite3_stmt *stmt_script = NULL;
	sqlite3_stmt *stmt_option = NULL;

	int i;
	int ret;
	int retcode = EPKG_OK;
	int64_t package_id;
	int64_t mtree_id;
	char mtree_sha256[65];
	char *errmsg;
	const char *mtree;

	const char sql_begin[] = "BEGIN TRANSACTION;";
	const char sql_mtree[] = ""
		"INSERT OR IGNORE INTO mtree (sha256, content) "
		"VALUES (?1, ?2);";
	const char sql_pkg[] = ""
		"INSERT OR REPLACE INTO packages( "
			"origin, name, version, comment, desc, mtree_id, message, arch, "
			"osversion, maintainer, www, prefix, flatsize) "
		"VALUES( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13);";
	const char sql_dep[] = ""
		"INSERT INTO deps (origin, name, version, package_id) "
		"VALUES (?1, ?2, ?3, ?4);";
	const char sql_conflict[] = ""
		"INSERT INTO conflicts (name, package_id) "
		"VALUES (?1, ?2);";
	const char sql_file[] = ""
		"INSERT INTO files (path, sha256, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_script[] = ""
		"INSERT INTO scripts (script, type, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_exec[] = ""
		"INSERT INTO exec (cmd, type, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_option[] = ""
		"INSERT INTO options (option, value, package_id) "
		"VALUES (?1, ?2, ?3);";

	s = db->sqlite;


	if (sqlite3_exec(s, sql_begin, NULL, NULL, &errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	/*
	 * If this package has a mtree, insert it in the database.
	 * Compute the mtree_id which is the sha256 of the mtree.
	 * If there is no mtree, mtree_id is set to NULL.
	 */
	mtree = pkg_get(pkg, PKG_MTREE);
	if (mtree != NULL) {
		SHA256_Data(mtree, strlen(mtree), mtree_sha256);
		if (sqlite3_prepare(s, sql_mtree, -1, &stmt_mtree, NULL) !=
							SQLITE_OK) {
			retcode = ERROR_SQLITE(s);
			goto cleanup;
		}

		sqlite3_bind_text(stmt_mtree, 1, mtree_sha256, -1, SQLITE_STATIC);
		sqlite3_bind_text(stmt_mtree, 2, mtree, -1, SQLITE_STATIC);

		ret = sqlite3_step(stmt_mtree);
		sqlite3_finalize(stmt_mtree);
		if (ret != SQLITE_DONE) {
			retcode = ERROR_SQLITE(s);
			goto cleanup;
		}

		mtree_id = sqlite3_last_insert_rowid(s);
	} else {
		mtree_id = 0;
	}

	/*
	 * Insert package record
	 */
	if (sqlite3_prepare(s, sql_pkg, -1, &stmt_pkg, NULL) != SQLITE_OK) {
		retcode = ERROR_SQLITE(s);
		goto cleanup;
	}
	sqlite3_bind_text(stmt_pkg, 1, pkg_get(pkg, PKG_ORIGIN), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 2, pkg_get(pkg, PKG_NAME), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 3, pkg_get(pkg, PKG_VERSION), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 4, pkg_get(pkg, PKG_COMMENT), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 5, pkg_get(pkg, PKG_DESC), -1, SQLITE_STATIC);
	sqlite3_bind_int64(stmt_pkg, 6, mtree_id);
	sqlite3_bind_text(stmt_pkg, 7, pkg_get(pkg, PKG_MESSAGE), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 8, pkg_get(pkg, PKG_ARCH), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 9, pkg_get(pkg, PKG_OSVERSION), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 10, pkg_get(pkg, PKG_MAINTAINER), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 11, pkg_get(pkg, PKG_WWW), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 12, pkg_get(pkg, PKG_PREFIX), -1, SQLITE_STATIC);
	sqlite3_bind_int64(stmt_pkg, 13, pkg_flatsize(pkg));

	if (sqlite3_step(stmt_pkg) != SQLITE_DONE) {
		retcode = ERROR_SQLITE(s);
		goto cleanup;
	}

	/*
	 * Get the generated package_id
	 */<commit_msg>Create explicit INTEGER PRIMARY KEY.<commit_after>#include <sys/param.h>
#include <sys/stat.h>
#include <sys/types.h>

#include <assert.h>
#include <err.h>
#include <errno.h>
#include <regex.h>
#include <sha256.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

#include <sqlite3.h>

#include "pkg.h"
#include "pkg_error.h"
#include "pkg_private.h"
#include "pkgdb.h"
#include "pkg_util.h"

#define PKG_DBDIR "/var/db/pkg"

static struct pkgdb_it * pkgdb_it_new(struct pkgdb *, sqlite3_stmt *);
static void pkgdb_regex(sqlite3_context *, int, sqlite3_value **, int);
static void pkgdb_regex_basic(sqlite3_context *, int, sqlite3_value **);
static void pkgdb_regex_extended(sqlite3_context *, int, sqlite3_value **);
static void pkgdb_regex_delete(void *);

static void
pkgdb_regex_delete(void *p)
{
	regex_t *re = (regex_t *)p;

	regfree(re);
	free(re);
}

static void
pkgdb_regex(sqlite3_context *ctx, int argc, sqlite3_value **argv, int reg_type)
{
	const char *regex = NULL;
	const char *str;
	regex_t *re;
	int ret;

	if (argc != 2 || (regex = sqlite3_value_text(argv[0])) == NULL ||
		(str = sqlite3_value_text(argv[1])) == NULL) {
		sqlite3_result_error(ctx, "SQL function regex() called with invalid arguments.\n", -1);
		return;
	}

	re = (regex_t *)sqlite3_get_auxdata(ctx, 0);
	if (re == NULL) {
		re = malloc(sizeof(regex_t));
		if (regcomp(re, regex, reg_type | REG_NOSUB) != 0) {
			sqlite3_result_error(ctx, "Invalid regex\n", -1);
			free(re);
			return;
		}

		sqlite3_set_auxdata(ctx, 0, re, pkgdb_regex_delete);
	}

	ret = regexec(re, str, 0, NULL, 0);
	sqlite3_result_int(ctx, (ret != REG_NOMATCH));
}

static void
pkgdb_regex_basic(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
	pkgdb_regex(ctx, argc, argv, REG_BASIC);
}

static void
pkgdb_regex_extended(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
	pkgdb_regex(ctx, argc, argv, REG_EXTENDED);
}

const char *
pkgdb_get_dir(void)
{
	const char *pkg_dbdir;

	if ((pkg_dbdir = getenv("PKG_DBDIR")) == NULL)
		pkg_dbdir = PKG_DBDIR;

	return pkg_dbdir;
}

/*
 * in the database : 
 * scripts.type can be:
 * - 0: PRE_INSTALL
 * - 1: POST_INSTALL
 * - 2: PRE_DEINSTALL
 * - 3: POST_DEINSTALL
 * - 4: PRE_UPGRADE
 * - 5: POST_UPGRADE
 * - 6: INSTALL
 * - 7: DEINSTALL
 * - 8: UPGRADE
 * 
 * exec.type can be:
 * - 0: exec
 * - 1: unexec
 *
 */

static int
pkgdb_init(sqlite3 *sdb)
{
	char *errmsg;
	const char sql[] = ""
	"CREATE TABLE packages ("
		"id INTEGER PRIMARY KEY,"
		"origin TEXT UNIQUE,"
		"name TEXT,"
		"version TEXT,"
		"comment TEXT,"
		"desc TEXT,"
		"mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT"
			" ON UPDATE CASCADE,"
		"message TEXT,"
		"arch TEXT,"
		"osversion TEXT,"
		"maintainer TEXT,"
		"www TEXT,"
		"prefix TEXT,"
		"flatsize INTEGER,"
		"automatic INTEGER,"
		"pkg_format_version INTEGER"
	");"
	"CREATE TABLE scripts ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"script TEXT,"
		"type INTEGER,"
		"PRIMARY KEY (package_id, type)"
	");"
	"CREATE INDEX scripts_package ON scripts(package_id);"
	"CREATE TABLE exec ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"cmd TEXT,"
		"type INTEGER"
	");"
	"CREATE INDEX exec_package ON exec(package_id);"
	"CREATE TABLE options ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"option TEXT,"
		"value TEXT,"
		"PRIMARY KEY (package_id,option)"
	");"
	"CREATE INDEX options_package ON options(package_id);"
	"CREATE TABLE deps ("
		"origin TEXT,"
		"name TEXT,"
		"version TEXT,"
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"PRIMARY KEY (package_id,origin)"
	");"
	"CREATE INDEX deps_origin ON deps(origin);"
	"CREATE INDEX deps_package ON deps(package_id);"
	"CREATE TABLE files ("
		"path TEXT PRIMARY KEY,"
		"sha256 TEXT,"
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE"
	");"
	"CREATE INDEX files_package ON files(package_id);"
	"CREATE TABLE conflicts ("
		"name TEXT,"
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE,"
		"PRIMARY KEY (package_id,name)"
	");"
	"CREATE INDEX conflicts_package ON conflicts(package_id);"
	"CREATE TABLE mtree ("
		"id INTEGER PRIMARY KEY,"
		"sha256 TEXT UNIQUE,"
		"content TEXT"
	");"
	"CREATE TRIGGER clean_mtree AFTER DELETE ON packages BEGIN "
		"DELETE FROM mtree WHERE rowid NOT IN (SELECT DISTINCT mtree_id FROM packages);"
	"END;";

	if (sqlite3_exec(sdb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	return (EPKG_OK);
}

int
pkgdb_open(struct pkgdb **db)
{
	int retcode;
	struct stat st;
	char *errmsg;
	char fpath[MAXPATHLEN];

	snprintf(fpath, sizeof(fpath), "%s/pkg.db", pkgdb_get_dir());

	if ((*db = calloc(1, sizeof(struct pkgdb))) == NULL)
		return (pkg_error_set(EPKG_FATAL, "calloc(): %s", strerror(errno)));

	if ((retcode = stat(fpath, &st)) == -1 && errno != ENOENT)
		return (pkg_error_set(EPKG_FATAL, "can not stat %s: %s", fpath,
							  strerror(errno)));

	if (sqlite3_open(fpath, &(*db)->sqlite) != SQLITE_OK)
		return (ERROR_SQLITE((*db)->sqlite));

	/* If the database is missing we have to initialize it */
	if (retcode == -1)
		if ((retcode = pkgdb_init((*db)->sqlite)) != EPKG_OK)
			return (retcode);

	sqlite3_create_function((*db)->sqlite, "regexp", 2, SQLITE_ANY, NULL,
							pkgdb_regex_basic, NULL, NULL);
	sqlite3_create_function((*db)->sqlite, "eregexp", 2, SQLITE_ANY, NULL,
							pkgdb_regex_extended, NULL, NULL);

	/* 
	 * allow forign key option which will allow to have clean support for
	 * reinstalling
	 */
	if (sqlite3_exec((*db)->sqlite, "PRAGMA foreign_keys = ON;", NULL, NULL,
		&errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	return (EPKG_OK);
}

void
pkgdb_close(struct pkgdb *db)
{
	if (db == NULL)
		return;

	if (db->sqlite != NULL)
		sqlite3_close(db->sqlite);

	free(db);
}

static struct pkgdb_it *
pkgdb_it_new(struct pkgdb *db, sqlite3_stmt *s)
{
	struct pkgdb_it *it;

	if ((it = malloc(sizeof(struct pkgdb_it))) == NULL) {
		pkg_error_set(EPKG_FATAL, "malloc(): %s", strerror(errno));
		sqlite3_finalize(s);
		return (NULL);
	}

	it->db = db;
	it->stmt = s;
	return (it);
}

int
pkgdb_it_next(struct pkgdb_it *it, struct pkg **pkg_p, int flags)
{
	struct pkg *pkg;
	int ret;

	if (it == NULL)
		return (ERROR_BAD_ARG("it"));

	switch (sqlite3_step(it->stmt)) {
	case SQLITE_ROW:
		if (*pkg_p == NULL)
			pkg_new(pkg_p);
		else
			pkg_reset(*pkg_p);
		pkg = *pkg_p;

		pkg->type = PKG_INSTALLED;
		pkg->rowid = sqlite3_column_int(it->stmt, 0);
		pkg_set(pkg, PKG_ORIGIN, sqlite3_column_text(it->stmt, 1));
		pkg_set(pkg, PKG_NAME, sqlite3_column_text(it->stmt, 2));
		pkg_set(pkg, PKG_VERSION, sqlite3_column_text(it->stmt, 3));
		pkg_set(pkg, PKG_COMMENT, sqlite3_column_text(it->stmt, 4));
		pkg_set(pkg, PKG_DESC, sqlite3_column_text(it->stmt, 5));
		pkg_set(pkg, PKG_MTREE, sqlite3_column_text(it->stmt, 6));
		pkg_set(pkg, PKG_MESSAGE, sqlite3_column_text(it->stmt, 7));
		pkg_set(pkg, PKG_ARCH, sqlite3_column_text(it->stmt, 8));
		pkg_set(pkg, PKG_OSVERSION, sqlite3_column_text(it->stmt, 9));
		pkg_set(pkg, PKG_MAINTAINER, sqlite3_column_text(it->stmt, 10));
		pkg_set(pkg, PKG_WWW, sqlite3_column_text(it->stmt, 11));
		pkg_set(pkg, PKG_PREFIX, sqlite3_column_text(it->stmt, 12));
		pkg_setflatsize(pkg, sqlite3_column_int64(it->stmt, 13));

		if (flags & PKG_DEPS)
			if ((ret = pkgdb_pkg_loaddeps(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_RDEPS)
			if ((ret = pkgdb_pkg_loadrdeps(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_CONFLICTS)
			if ((ret = pkgdb_pkg_loadconflicts(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_FILES)
			if ((ret = pkgdb_pkg_loadfiles(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_EXECS)
			if ((ret = pkgdb_pkg_loadexecs(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_SCRIPTS)
			if ((ret = pkgdb_pkg_loadscripts(it->db, pkg)) != EPKG_OK)
				return (ret);

		if (flags & PKG_OPTIONS)
			if ((ret = pkgdb_pkg_loadoptions(it->db, pkg)) != EPKG_OK)
				return (ret);

		return (EPKG_OK);
	case SQLITE_DONE:
		return (EPKG_END);
	default:
		return (ERROR_SQLITE(it->db->sqlite));
	}
}

void
pkgdb_it_free(struct pkgdb_it *it)
{
	if (it != NULL) {
		sqlite3_finalize(it->stmt);
		free(it);
	}
}

struct pkgdb_it *
pkgdb_query(struct pkgdb *db, const char *pattern, match_t match)
{
	char sql[BUFSIZ];
	sqlite3_stmt *stmt;
	const char *comp = NULL;
	char *checkorigin = NULL;

	if (match != MATCH_ALL && pattern == NULL) {
		ERROR_BAD_ARG("pattern");
		return (NULL);
	}

	if (pattern != NULL)
		checkorigin = strchr(pattern, '/');

	switch (match) {
	case MATCH_ALL:
		comp = "";
		break;
	case MATCH_EXACT:
		if (checkorigin == NULL)
			comp = " AND name = ?1";
		else
			comp = " AND origin = ?1";
		break;
	case MATCH_GLOB:
		if (checkorigin == NULL)
			comp = " AND name GLOB ?1";
		else
			comp = " AND origin GLOB ?1";
		break;
	case MATCH_REGEX:
		if (checkorigin == NULL)
			comp = " AND name REGEXP ?1";
		else
			comp = " AND origin REGEXP ?1";
		break;
	case MATCH_EREGEX:
		if (checkorigin == NULL)
			comp = " AND EREGEXP(?1, name)";
		else
			comp = " AND EREGEXP(?1, origin)";
		break;
	}

	snprintf(sql, sizeof(sql),
			"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
				"p.message, p.arch, p.osversion, p.maintainer, p.www, "
				"p.prefix, p.flatsize "
			"FROM packages AS p, mtree AS m "
			"WHERE m.rowid = p.mtree_id%s;", comp);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK) {
		ERROR_SQLITE(db->sqlite);
		return (NULL);
	}

	if (match != MATCH_ALL)
		sqlite3_bind_text(stmt, 1, pattern, -1, SQLITE_TRANSIENT);

	return (pkgdb_it_new(db, stmt));
}

struct pkgdb_it *
pkgdb_query_which(struct pkgdb *db, const char *path)
{
	sqlite3_stmt *stmt;
	const char sql[] = ""
		"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
			"p.message, p.arch, p.osversion, p.maintainer, p.www, "
			"p.prefix, p.flatsize "
			"FROM packages AS p, mtree AS m, files AS f "
			"WHERE m.rowid = p.mtree_id " 
				"AND p.rowid = f.package_id "
				"AND f.path = ?1;";

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK) {
		ERROR_SQLITE(db->sqlite);
		return (NULL);
	}

	sqlite3_bind_text(stmt, 1, path, -1, SQLITE_TRANSIENT);

	return (pkgdb_it_new(db, stmt));
}

int
pkgdb_pkg_loaddeps(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkgdb_it it;
	struct pkg *p;
	int ret;
	const char sql[] = ""
	"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
		"p.message, p.arch, p.osversion, p.maintainer, p.www, "
		"p.prefix, p.flatsize "
	"FROM packages AS p, mtree AS m, deps AS d "
	"WHERE m.rowid = p.mtree_id "
		"AND p.origin = d.origin "
		"AND d.package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_DEPS)
		return (EPKG_OK);

	array_init(&pkg->deps, 10);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	it.stmt = stmt;
	it.db = db;

	p = NULL;
	while ((ret = pkgdb_it_next(&it, &p, PKG_BASIC)) == EPKG_OK) {
		array_append(&pkg->deps, p);
		p = NULL;
	}

	sqlite3_finalize(stmt);

	if (ret != EPKG_END) {
		array_reset(&pkg->deps, &pkg_free_void);
		return (ret);
	}

	pkg->flags |= PKG_DEPS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadrdeps(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkgdb_it it;
	struct pkg *p;
	int ret;
	const char sql[] = ""
		"SELECT p.rowid, p.origin, p.name, p.version, p.comment, p.desc, m.content, "
			"p.message, p.arch, p.osversion, p.maintainer, p.www, "
			"p.prefix, p.flatsize "
		"FROM packages AS p, mtree AS m, deps AS d "
		"WHERE m.id = p.mtree_id "
			"AND p.rowid = d.package_id "
			"AND d.origin = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_RDEPS)
		return (EPKG_OK);

	array_init(&pkg->rdeps, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_text(stmt, 1, pkg_get(pkg, PKG_ORIGIN), -1, SQLITE_STATIC);

	it.stmt = stmt;
	it.db = db;

	p = NULL;
	while ((ret = pkgdb_it_next(&it, &p, PKG_BASIC)) == EPKG_OK) {
		array_append(&pkg->rdeps, p);
		p = NULL;
	}
	sqlite3_finalize(stmt);

	if (ret != EPKG_END) {
		array_reset(&pkg->rdeps, &pkg_free_void);
		return (ret);
	}

	pkg->flags |= PKG_RDEPS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadconflicts(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_conflict *c;
	int ret;
	const char sql[] = ""
		"SELECT name "
		"FROM conflicts "
		"WHERE package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_CONFLICTS)
		return (EPKG_OK);

	array_init(&pkg->conflicts, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt) == SQLITE_ROW)) {
		pkg_conflict_new(&c);
		sbuf_set(&c->glob, sqlite3_column_text(stmt, 0));
		array_append(&pkg->conflicts, c);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->conflicts, &pkg_conflict_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_CONFLICTS;

	return (EPKG_OK);
}

int
pkgdb_pkg_loadfiles(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_file *f;
	int ret;
	const char sql[] = ""
		"SELECT path, sha256 "
		"FROM files "
		"WHERE package_id = ?1;";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_FILES)
		return (EPKG_OK);

	array_init(&pkg->files, 10);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_file_new(&f);
		strlcpy(f->path, sqlite3_column_text(stmt, 0), sizeof(f->path));
		strlcpy(f->sha256, sqlite3_column_text(stmt, 1), sizeof(f->sha256));
		array_append(&pkg->files, f);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->files, &free);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_FILES;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadexecs(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_exec *e;
	int ret;
	const char sql[] = ""
		"SELECT cmd, type "
		"FROM exec "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_EXECS)
		return (EPKG_OK);

	array_init(&pkg->exec, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_exec_new(&e);
		sbuf_set(&e->cmd, sqlite3_column_text(stmt, 0));
		e->type = sqlite3_column_int(stmt, 1);
		array_append(&pkg->exec, e);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->exec, &pkg_exec_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_EXECS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadscripts(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_script *s;
	int ret;
	const char sql[] = ""
		"SELECT script, type "
		"FROM scripts "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_SCRIPTS)
		return (EPKG_OK);

	array_init(&pkg->scripts, 6);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_script_new(&s);
		sbuf_set(&s->data, sqlite3_column_text(stmt, 0));
		s->type = sqlite3_column_int(stmt, 1);
		array_append(&pkg->scripts, s);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->scripts, &pkg_script_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_SCRIPTS;
	return (EPKG_OK);
}

int
pkgdb_pkg_loadoptions(struct pkgdb *db, struct pkg *pkg)
{
	sqlite3_stmt *stmt;
	struct pkg_option *o;
	int ret;
	const char sql[] = ""
		"SELECT option, value "
		"FROM options "
		"WHERE package_id = ?1";

	if (pkg->type != PKG_INSTALLED)
		return (ERROR_BAD_ARG("pkg"));

	if (pkg->flags & PKG_OPTIONS)
		return (EPKG_OK);

	array_init(&pkg->options, 5);

	if (sqlite3_prepare(db->sqlite, sql, -1, &stmt, NULL) != SQLITE_OK)
		return (ERROR_SQLITE(db->sqlite));

	sqlite3_bind_int64(stmt, 1, pkg->rowid);

	while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
		pkg_option_new(&o);
		sbuf_set(&o->opt, sqlite3_column_text(stmt, 0));
		sbuf_set(&o->value, sqlite3_column_text(stmt, 1));
		array_append(&pkg->options, o);
	}
	sqlite3_finalize(stmt);

	if (ret != SQLITE_DONE) {
		array_reset(&pkg->options, &pkg_option_free_void);
		return (ERROR_SQLITE(db->sqlite));
	}

	pkg->flags |= PKG_OPTIONS;
	return (EPKG_OK);
}

int
pkgdb_register_pkg(struct pkgdb *db, struct pkg *pkg)
{
	struct pkg **deps;
	struct pkg_file **files;
	struct pkg_conflict **conflicts;
	struct pkg_exec **execs;
	struct pkg_script **scripts;
	struct pkg_option **options;

	sqlite3 *s;
	sqlite3_stmt *stmt_pkg = NULL;
	sqlite3_stmt *stmt_mtree = NULL;
	sqlite3_stmt *stmt_dep = NULL;
	sqlite3_stmt *stmt_conflict = NULL;
	sqlite3_stmt *stmt_file = NULL;
	sqlite3_stmt *stmt_exec = NULL;
	sqlite3_stmt *stmt_script = NULL;
	sqlite3_stmt *stmt_option = NULL;

	int i;
	int ret;
	int retcode = EPKG_OK;
	int64_t package_id;
	int64_t mtree_id;
	char mtree_sha256[65];
	char *errmsg;
	const char *mtree;

	const char sql_begin[] = "BEGIN TRANSACTION;";
	const char sql_mtree[] = ""
		"INSERT OR IGNORE INTO mtree (sha256, content) "
		"VALUES (?1, ?2);";
	const char sql_pkg[] = ""
		"INSERT OR REPLACE INTO packages( "
			"origin, name, version, comment, desc, mtree_id, message, arch, "
			"osversion, maintainer, www, prefix, flatsize) "
		"VALUES( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13);";
	const char sql_dep[] = ""
		"INSERT INTO deps (origin, name, version, package_id) "
		"VALUES (?1, ?2, ?3, ?4);";
	const char sql_conflict[] = ""
		"INSERT INTO conflicts (name, package_id) "
		"VALUES (?1, ?2);";
	const char sql_file[] = ""
		"INSERT INTO files (path, sha256, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_script[] = ""
		"INSERT INTO scripts (script, type, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_exec[] = ""
		"INSERT INTO exec (cmd, type, package_id) "
		"VALUES (?1, ?2, ?3);";
	const char sql_option[] = ""
		"INSERT INTO options (option, value, package_id) "
		"VALUES (?1, ?2, ?3);";

	s = db->sqlite;


	if (sqlite3_exec(s, sql_begin, NULL, NULL, &errmsg) != SQLITE_OK) {
		pkg_error_set(EPKG_FATAL, "sqlite: %s", errmsg);
		sqlite3_free(errmsg);
		return (EPKG_FATAL);
	}

	/*
	 * If this package has a mtree, insert it in the database.
	 * Compute the mtree_id which is the sha256 of the mtree.
	 * If there is no mtree, mtree_id is set to NULL.
	 */
	mtree = pkg_get(pkg, PKG_MTREE);
	if (mtree != NULL) {
		SHA256_Data(mtree, strlen(mtree), mtree_sha256);
		if (sqlite3_prepare(s, sql_mtree, -1, &stmt_mtree, NULL) !=
							SQLITE_OK) {
			retcode = ERROR_SQLITE(s);
			goto cleanup;
		}

		sqlite3_bind_text(stmt_mtree, 1, mtree_sha256, -1, SQLITE_STATIC);
		sqlite3_bind_text(stmt_mtree, 2, mtree, -1, SQLITE_STATIC);

		ret = sqlite3_step(stmt_mtree);
		sqlite3_finalize(stmt_mtree);
		if (ret != SQLITE_DONE) {
			retcode = ERROR_SQLITE(s);
			goto cleanup;
		}

		mtree_id = sqlite3_last_insert_rowid(s);
	} else {
		mtree_id = 0;
	}

	/*
	 * Insert package record
	 */
	if (sqlite3_prepare(s, sql_pkg, -1, &stmt_pkg, NULL) != SQLITE_OK) {
		retcode = ERROR_SQLITE(s);
		goto cleanup;
	}
	sqlite3_bind_text(stmt_pkg, 1, pkg_get(pkg, PKG_ORIGIN), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 2, pkg_get(pkg, PKG_NAME), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 3, pkg_get(pkg, PKG_VERSION), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 4, pkg_get(pkg, PKG_COMMENT), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 5, pkg_get(pkg, PKG_DESC), -1, SQLITE_STATIC);
	if (mtree_id > 0)
		sqlite3_bind_int64(stmt_pkg, 6, mtree_id);
	else
		sqlite3_bind_null(stmt_pkg, 6);
	sqlite3_bind_text(stmt_pkg, 7, pkg_get(pkg, PKG_MESSAGE), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 8, pkg_get(pkg, PKG_ARCH), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 9, pkg_get(pkg, PKG_OSVERSION), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 10, pkg_get(pkg, PKG_MAINTAINER), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 11, pkg_get(pkg, PKG_WWW), -1, SQLITE_STATIC);
	sqlite3_bind_text(stmt_pkg, 12, pkg_get(pkg, PKG_PREFIX), -1, SQLITE_STATIC);
	sqlite3_bind_int64(stmt_pkg, 13, pkg_flatsize(pkg));

	if (sqlite3_step(stmt_pkg) != SQLITE_DONE) {
		retcode = ERROR_SQLITE(s);
		goto cleanup;
	}

	/*
	 * Get the generated package_id
	 */
{'fraction_non_alphanumeric': 0.1584894999529146, 'fraction_numerical': 0.01711554760335248, 'mean_word_length': 4.442977960020502, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 268, 'https://': 0, 'lorem ipsum': 0, 'www.': 12, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5166179', 'n_tokens_mistral': 19526, 'n_tokens_neox': 17638, 'n_words': 3992}