Added new dumpdatabase action to dump database without ids - helps compare databases

1 files changed, 69 insertions, 0 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 12dff83..230f089 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -1736,3 +1736,72 @@ function list_files()
 	table.sort(retval, function(a,b) return a.filename < b.filename end)
 	return cfe({ type="structure", value=retval, label="List of Provisioning Script files" })
 end
+
+function get_database()
+	local devices = cfe({ type="boolean", value=false, label="Include Device Data" })
+	return cfe({ type="group", value={devices=devices}, label="Provisioning Database Dump" })
+end
+
+function dump_database(db)
+	local res, err = pcall(function()
+		local connected = databaseconnect()
+		local lines = {}
+		local sql = "SELECT name, label, seq FROM provisioning_class_groups ORDER BY name, label"
+		local tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO provisioning_class_groups VALUES(default, '"..escape(t.name).."', '"..escape(t.label).."', '"..escape(t.seq).."')"
+		end
+		sql = "SELECT g.name AS group, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.name, c.label"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='"..escape(t.group).."'), '"..escape(t.label).."', '"..escape(t.seq).."')"
+		end
+		sql = "SELECT name, label, seq FROM provisioning_groups ORDER BY name, label"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO provisioning_groups VALUES(default, '"..escape(t.name).."'), '"..escape(t.label).."', '"..escape(t.seq).."')"
+		end
+		sql = "SELECT name, type, label, descr, value, seq, regexp, validate FROM provisioning_params ORDER BY name"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO provisioning_params VALUES(default, '"..escape(t.name).."', '"..escape(t.type).."', '"..escape(t.label).."', '"..escape(t.descr).."', '"..escape(t.value).."', '"..escape(t.seq).."', '"..escape(t.regexp).."', '"..escape(t.validate).."')"
+		end
+		sql = "SELECT p.name AS param, o.label, o.value, o.seq FROM provisioning_options o JOIN provisioning_params p USING(param_id) ORDER BY p.name, o.seq, o.label"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..escape(t.label).."', '"..escape(t.value).."', '"..escape(t.seq).."')"
+		end
+		sql = "SELECT c.label AS class, g.label AS group FROM provisioning_classes c JOIN classes_to_param_groups USING(class_id) JOIN provisioning_groups g USING(group_id) ORDER BY c.label, g.label"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='"..escape(t.class).."'), (SELECT group_id FROM provisioning_groups WHERE label='"..escape(t.group).."'))"
+		end
+		sql = "SELECT p.name AS param, g.label AS group, t.value, t.editable FROM provisioning_params p JOIN param_groups_to_params t USING(param_id) JOIN provisioning_groups g USING(group_id) ORDER BY p.name, g.label"
+		tmp = getselectresponse(sql)
+		for i,t in ipairs(tmp) do
+			lines[#lines+1] = "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='"..escape(t.group).."'), (SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..escape(t.value).."', "..escape(t.editable)..")"
+		end
+		if db.value.devices and db.value.devices.value then
+			sql = "SELECT device_id FROM devices_to_classes GROUP BY device_id ORDER BY device_id ASC"
+			devices = getselectresponse(sql)
+			for i,d in ipairs(devices) do
+				sql = "SELECT label FROM devices_to_classes JOIN provisioning_classes USING(class_id) WHERE device_id="..escape(d.device_id)
+				tmp = getselectresponse(sql)
+				for j,t in ipairs(tmp) do
+					lines[#lines+1] = "INSERT INTO devices_to_classes VALUES("..i..", (SELECT class_id FROM provisioning_classes WHERE label='"..escape(t.label).."'))"
+				end
+				sql = "SELECT group_name, p.name AS param, v.value FROM provisioning_values v JOIN provisioning_params p USING(param_id) WHERE device_id="..escape(d.device_id)
+				tmp = getselectresponse(sql)
+				for j,t in ipairs(tmp) do
+					lines[#lines+1] = "INSERT INTO provisioning_values VALUES("..i..", '"..escape(t.group_name).."', (SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..t.value.."')"
+				end
+			end
+		end
+		db.value.data = cfe({ type="longtext", value=table.concat(lines, "\n") or "", label="Database Values" })
+		if connected then databasedisconnect() end
+	end)
+	if not res and err then
+		db.errtxt = err
+	end
+	return db
+end