diff -r 000000000000 -r 8e1675826e46 normalize_json.lua
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/normalize_json.lua	Mon Mar 13 16:39:07 2023 +0000
@@ -0,0 +1,129 @@
+local json = require "cjson";
+
+local data_base_path = "data/";
+
+local function read_json_file(fn)
+	local data = assert(io.open(data_base_path .. fn)):read("*a");
+	return assert(json.decode(data));
+end
+
+local software_list = read_json_file("software_list_doap.json");
+local software_table = {};
+local known_software = {};
+
+local software_categories_table = {};
+local software_platforms_table = {};
+
+local software_counter = -1;
+for software_name, entry in pairs(software_list) do
+	software_counter = software_counter + 1;
+	known_software[entry.name_slug] = software_counter;
+	local new_entry = {
+		id = software_counter;
+		short_name = entry.name_slug;
+		name = software_name;
+	};
+	table.insert(software_table, new_entry);
+	for _, platform in ipairs(entry.platforms) do
+		table.insert(software_platforms_table, {
+			software_id = software_counter;
+			platform = platform;
+		});
+	end
+	for _, category in ipairs(entry.categories) do
+		table.insert(software_categories_table, {
+			software_id = software_counter;
+			category = category;
+		});
+	end
+end
+
+
+local xep_list = read_json_file("xeplist.json");
+local xep_table, xeps_by_url = {}, {};
+local implementations_table = {
+	--[[{
+		xep = <XEP id>
+		software = <software id>
+		version =
+		status =
+	}]]
+};
+
+table.sort(xep_list, function (a, b)
+	if a.number ~= json.null and b.number ~= json.null then
+		return a.number < b.number;
+	end
+	return a.last_updated < b.last_updated;
+end);
+
+local xep_counter = -1;
+for _, entry in ipairs(xep_list) do
+	xep_counter = xep_counter + 1;
+	if entry.status == "Proto" then
+		entry.status = "ProtoXEP";
+	end
+	local xep = {
+		id = xep_counter;
+		number = entry.number;
+		title = entry.title;
+		status = entry.status;
+		last_updated = entry.last_updated;
+		type = entry.type;
+		version = entry.version;
+		approver = entry.approver;
+		accepted = entry.accepted;
+		short_name = entry.short_name;
+		url = entry.url;
+	};
+
+	if entry.url ~= json.null then
+		xeps_by_url[entry.url] = xep_counter;
+	end
+
+	for _, implementation in ipairs(entry.implementations) do
+		local software_id = known_software[implementation.package_name_slug];
+		if software_id then
+			table.insert(implementations_table, {
+				software_id = software_id;
+				xep_id = xep_counter;
+				xep_version = implementation.implemented_version;
+				status = implementation.status;
+			});
+		end
+	end
+	table.insert(xep_table, xep);
+end
+
+local function write_json(fn, data)
+	local f = assert(io.open(fn, "w+"));
+	f:write(json.encode(data));
+	f:close();
+end
+
+local compliance_table = {};
+
+for category, level_data in pairs(read_json_file("compliance_suite.json")) do
+	for level, software_data in pairs(level_data) do
+		for software_type, spec_list in pairs(software_data) do
+			for _, spec_url in ipairs(spec_list) do
+				local xep_id = xeps_by_url[spec_url];
+				if xep_id then
+					table.insert(compliance_table, {
+						xep_id = xep_id;
+						category = category;
+						level = level;
+						software_type = software_type;
+					});
+				end
+			end
+		end
+	end
+end
+
+write_json("db_xeps.json", xep_table);
+write_json("db_software.json", software_table);
+write_json("db_software_platforms.json", software_platforms_table);
+write_json("db_software_categories.json", software_categories_table);
+write_json("db_implementations.json", implementations_table);
+write_json("db_compliance.json", compliance_table);