From 0fc558bf1caff41fc7e4b73cc05167adde65945d Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Mon, 28 Dec 2015 04:53:29 +0000 Subject: Fix dump_database to properly output null values --- provisioning-model.lua | 20 +++++++++++++------- 1 file changed, 13 insertions(+), 7 deletions(-) diff --git a/provisioning-model.lua b/provisioning-model.lua index bb5142f..f1b5dd2 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -2008,33 +2008,39 @@ end function mymodule.dump_database(self, db) local connected + + local function escape_or_null(value) + if not value then return "null" end + return "'"..provdb.escape(value).."'" + end + local res, err = pcall(function() connected = databaseconnect(true) 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, '"..provdb.escape(t.name).."', '"..provdb.escape(t.label).."', '"..provdb.escape(t.seq).."');" + lines[#lines+1] = "INSERT INTO provisioning_class_groups VALUES(default, "..escape_or_null(t.name)..", "..escape_or_null(t.label)..", "..escape_or_null(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='"..provdb.escape(t.group).."'), '"..provdb.escape(t.label).."', '"..provdb.escape(t.seq).."');" + lines[#lines+1] = "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='"..provdb.escape(t.group).."'), "..escape_or_null(t.label)..", "..escape_or_null(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, '"..provdb.escape(t.name).."', '"..provdb.escape(t.label).."', '"..provdb.escape(t.seq).."');" + lines[#lines+1] = "INSERT INTO provisioning_groups VALUES(default, "..escape_or_null(t.name)..", "..escape_or_null(t.label)..", "..escape_or_null(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, '"..provdb.escape(t.name).."', '"..provdb.escape(t.type).."', '"..provdb.escape(t.label).."', '"..provdb.escape(t.descr).."', '"..provdb.escape(t.value).."', '"..provdb.escape(t.seq).."', '"..provdb.escape(t.regexp).."', '"..provdb.escape(t.validate).."');" + lines[#lines+1] = "INSERT INTO provisioning_params VALUES(default, "..escape_or_null(t.name)..", "..escape_or_null(t.type)..", "..escape_or_null(t.label)..", "..escape_or_null(t.descr)..", "..escape_or_null(t.value)..", "..escape_or_null(t.seq)..", "..escape_or_null(t.regexp)..", "..escape_or_null(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='"..provdb.escape(t.param).."'), '"..provdb.escape(t.label).."', '"..provdb.escape(t.value).."', '"..provdb.escape(t.seq).."');" + lines[#lines+1] = "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(t.param).."'), "..escape_or_null(t.label)..", "..escape_or_null(t.value)..", "..escape_or_null(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) @@ -2044,7 +2050,7 @@ function mymodule.dump_database(self, db) 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='"..provdb.escape(t.group).."'), (SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(t.param).."'), '"..provdb.escape(t.value).."', '"..provdb.escape(t.editable).."');" + lines[#lines+1] = "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='"..provdb.escape(t.group).."'), (SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(t.param).."'), "..escape_or_null(t.value)..", "..escape_or_null(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" @@ -2058,7 +2064,7 @@ function mymodule.dump_database(self, db) sql = "SELECT group_name, p.name AS param, v.value FROM provisioning_values v JOIN provisioning_params p USING(param_id) WHERE device_id='"..provdb.escape(d.device_id).."'" tmp = getselectresponse(sql) for j,t in ipairs(tmp) do - lines[#lines+1] = "INSERT INTO provisioning_values VALUES("..provdb.escape(i)..", '"..provdb.escape(t.group_name).."', (SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(t.param).."'), '"..provdb.escape(t.value).."');" + lines[#lines+1] = "INSERT INTO provisioning_values VALUES("..provdb.escape(i)..", "..escape_or_null(t.group_name)..", (SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(t.param).."'), "..escape_or_null(t.value)..");" end end lines[#lines+1] = "SELECT pg_catalog.setval('provisioning_device_seq', "..provdb.escape(#devices)..", true);" -- cgit v1.2.3