<commit_before>#ifndef _DB_UPGRADES
#define _DB_UPGRADES

static struct db_upgrades {
	int version;
	const char *sql;
} db_upgrades[] = {
	{1,
	"CREATE TABLE licenses ("
		"id INTEGER PRIMARY KEY, "
		"license TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_licenses_assoc ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE, "
		"license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT"
			" ON UPDATE RESTRICT, "
		"PRIMARY KEY (package_id, license_id)"
	");"
	"CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages "
	"INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id "
	"INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;"
	"CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO licenses(license) values (NEW.license);"
			"INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES "
				"((SELECT id FROM packages where origin = NEW.origin), "
				"(SELECT id FROM categories WHERE name = NEW.name));"
		"END;"
	},

	{2,
	"ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1);"
	},

	{3,
	"DROP VIEW pkg_licenses;"
	"DROP TRIGGER license_insert;"
	"ALTER TABLE licenses RENAME TO todelete;"
	"CREATE TABLE licenses (id INTERGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);"
	"INSERT INTO licenses(id, name) SELECT id, license FROM todelete;"
	"CREATE VIEW pkg_licenses AS SELECT origin, licenses.name FROM packages "
	"INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id "
	"INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;"
	"CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO licenses(name) values (NEW.name);"
			"INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES "
				"((SELECT id FROM packages where origin = NEW.origin), "
				"(SELECT id FROM licenses WHERE name = NEW.name));"
		"END;"
	"DROP VIEW pkg_mtree;"
	"CREATE VIEW pkg_mtree AS "
		"SELECT origin, name, version, comment, desc, mtree.content AS "
			"mtree, message, arch, osversion, maintainer, www, prefix, "
			"flatsize, automatic, licenselogic, pkg_format_version "
			"FROM packages "
	"INNER JOIN mtree ON packages.mtree_id = mtree.id;"
	"DROP TRIGGER pkg_insert;"
	"CREATE TRIGGER pkg_insert INSTEAD OF INSERT ON pkg_mtree "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO mtree (content) VALUES (NEW.mtree);"
			"INSERT OR REPLACE INTO packages(origin, name, version, comment, desc, mtree_id, "
				"message, arch, osversion, maintainer, www, prefix, flatsize, automatic, licenselogic) "
				"VALUES (NEW.origin, NEW.name, NEW.version, NEW.comment, NEW.desc, "
				"(SELECT id FROM mtree WHERE content = NEW.mtree), "
				"NEW.message, NEW.arch, NEW.osversion, NEW.maintainer, NEW.www, NEW.prefix, "
				"NEW.flatsize, NEW.automatic, NEW.licenselogic);"
		"END;"
	"DROP TABLE todelete;"
	},

	{4,
	"DROP VIEW pkg_mtree;"
	"DROP TRIGGER CLEAN_MTREE;"
	"DROP TRIGGER pkg_insert;"
	"DROP VIEW pkg_dirs;"
	"DROP TRIGGER dir_insert;"
	"ALTER TABLE pkg_dirs_assoc RENAME TO pkg_directories;"
	"DROP VIEW pkg_categories;"
	"DROP TRIGGER category_insert;"
	"ALTER TABLE pkg_categories_assoc RENAME TO pkg_categories;"
	"DROP VIEW pkg_licenses;"
	"DROP TRIGGER licenses_insert;"
	"ALTER TABLE pkg_licenses_assoc RENAME TO pkg_licenses;"
	},

	{5,
	"CREATE TABLE users ("
		"id INTEGER PRIMATY KEY, "
		"name TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_users ("
		"package_id INTEGER REFERECES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE, "
		"user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT"
			" ON UPDATE RESTRICT, "
		"UNIQUE(package_id, user_id)"
	");"
	"CREATE TABLE groups ("
		"id INTEGER PRIMARY KEY, "
		"name TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_groups ("
		"package_id INTEGER REFERECES packages(id) ON DELETE CASCADE"<commit_msg>Also fix the upgrade sql statements!<commit_after>#ifndef _DB_UPGRADES
#define _DB_UPGRADES

static struct db_upgrades {
	int version;
	const char *sql;
} db_upgrades[] = {
	{1,
	"CREATE TABLE licenses ("
		"id INTEGER PRIMARY KEY, "
		"license TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_licenses_assoc ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE, "
		"license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT"
			" ON UPDATE RESTRICT, "
		"PRIMARY KEY (package_id, license_id)"
	");"
	"CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages "
	"INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id "
	"INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;"
	"CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO licenses(license) values (NEW.license);"
			"INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES "
				"((SELECT id FROM packages where origin = NEW.origin), "
				"(SELECT id FROM categories WHERE name = NEW.name));"
		"END;"
	},

	{2,
	"ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1);"
	},

	{3,
	"DROP VIEW pkg_licenses;"
	"DROP TRIGGER license_insert;"
	"ALTER TABLE licenses RENAME TO todelete;"
	"CREATE TABLE licenses (id INTERGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);"
	"INSERT INTO licenses(id, name) SELECT id, license FROM todelete;"
	"CREATE VIEW pkg_licenses AS SELECT origin, licenses.name FROM packages "
	"INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id "
	"INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;"
	"CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO licenses(name) values (NEW.name);"
			"INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES "
				"((SELECT id FROM packages where origin = NEW.origin), "
				"(SELECT id FROM licenses WHERE name = NEW.name));"
		"END;"
	"DROP VIEW pkg_mtree;"
	"CREATE VIEW pkg_mtree AS "
		"SELECT origin, name, version, comment, desc, mtree.content AS "
			"mtree, message, arch, osversion, maintainer, www, prefix, "
			"flatsize, automatic, licenselogic, pkg_format_version "
			"FROM packages "
	"INNER JOIN mtree ON packages.mtree_id = mtree.id;"
	"DROP TRIGGER pkg_insert;"
	"CREATE TRIGGER pkg_insert INSTEAD OF INSERT ON pkg_mtree "
		"FOR EACH ROW BEGIN "
			"INSERT OR IGNORE INTO mtree (content) VALUES (NEW.mtree);"
			"INSERT OR REPLACE INTO packages(origin, name, version, comment, desc, mtree_id, "
				"message, arch, osversion, maintainer, www, prefix, flatsize, automatic, licenselogic) "
				"VALUES (NEW.origin, NEW.name, NEW.version, NEW.comment, NEW.desc, "
				"(SELECT id FROM mtree WHERE content = NEW.mtree), "
				"NEW.message, NEW.arch, NEW.osversion, NEW.maintainer, NEW.www, NEW.prefix, "
				"NEW.flatsize, NEW.automatic, NEW.licenselogic);"
		"END;"
	"DROP TABLE todelete;"
	},

	{4,
	"DROP VIEW pkg_mtree;"
	"DROP TRIGGER CLEAN_MTREE;"
	"DROP TRIGGER pkg_insert;"
	"DROP VIEW pkg_dirs;"
	"DROP TRIGGER dir_insert;"
	"ALTER TABLE pkg_dirs_assoc RENAME TO pkg_directories;"
	"DROP VIEW pkg_categories;"
	"DROP TRIGGER category_insert;"
	"ALTER TABLE pkg_categories_assoc RENAME TO pkg_categories;"
	"DROP VIEW pkg_licenses;"
	"DROP TRIGGER licenses_insert;"
	"ALTER TABLE pkg_licenses_assoc RENAME TO pkg_licenses;"
	},

	{5,
	"CREATE TABLE users ("
		"id INTEGER PRIMARY KEY, "
		"name TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_users ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
			" ON UPDATE CASCADE, "
		"user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT"
			" ON UPDATE RESTRICT, "
		"UNIQUE(package_id, user_id)"
	");"
	"CREATE TABLE groups ("
		"id INTEGER PRIMARY KEY, "
		"name TEXT NOT NULL UNIQUE "
	");"
	"CREATE TABLE pkg_groups ("
		"package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE"
{'fraction_non_alphanumeric': 0.10367470640232353, 'fraction_numerical': 0.0015153428463189798, 'mean_word_length': 4.641025641025641, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3907839', 'n_tokens_mistral': 3131, 'n_tokens_neox': 2750, 'n_words': 854}