SQL changes so works with both postgresql and sqlite

1 files changed, 52 insertions, 17 deletions

diff --git a/vmail-model.lua b/vmail-model.lua
index 9264c3d..51261cf 100644
--- a/vmail-model.lua
+++ b/vmail-model.lua
@@ -50,16 +50,48 @@ else
 end
 
 vmaildb.table_creation_scripts = {
-	voicemail_users = {
-		"CREATE TABLE voicemail_users (uid INTEGER PRIMARY KEY, username VARCHAR(255) UNIQUE)",
-		"CREATE INDEX users_username_idx ON voicemail_users (username)",
-	},
 	voicemail_values = {
-		"CREATE TABLE voicemail_values (uid INTEGER, nid INTEGER, value VARCHAR(255))",
-		"CREATE INDEX values_uid_nid_idx on voicemail_values (uid, nid)",
+		"CREATE TABLE voicemail_values (uid INTEGER, nid INTEGER, value VARCHAR(255), PRIMARY KEY(uid, nid))",
+	},
+	voicemail_folders = {
+		"CREATE TABLE voicemail_folders (in_folder VARCHAR(255) PRIMARY KEY, label VARCHAR(255))",
+		"INSERT INTO voicemail_folders VALUES('inbox', 'Inbox')",
 	},
-	voicemail_params = {
-		"CREATE TABLE voicemail_params (nid INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER)",
+	-- The voicemail_prefs table is created by Freeswitch mod_voicemail.c, but we duplicate here in case not already created
+	voicemail_prefs = {"CREATE TABLE voicemail_prefs (username VARCHAR(255), domain VARCHAR(255), name_path VARCHAR(255), greeting_path VARCHAR(255), password VARCHAR(255))",
+		"create index voicemail_prefs_idx1 on voicemail_prefs(username)",
+		"create index voicemail_prefs_idx2 on voicemail_prefs(domain)",
+	},
+}
+
+if dbengine == db.engine.postgresql then
+	vmaildb.table_creation_scripts.voicemail_users = {
+		"CREATE TABLE voicemail_users (uid SERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE)",
+		"CREATE INDEX users_username_idx ON voicemail_users (username)",
+	}
+	vmaildb.table_creation_scripts.voicemail_params = {
+		"CREATE TABLE voicemail_params (nid SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr TEXT, value VARCHAR(255), seq INTEGER)",
+		"CREATE INDEX params_name_idx ON voicemail_params (name)",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'username', 'text', 'Extension', '', '', '1')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'firstname', 'text', 'User First Name', '', '', '2')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'lastname', 'text', 'User Last Name', '', '', '3')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-password', 'password', 'Voicemail Password', 'Passwords must be all numbers and at least three digits', '', '4')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-password-confirm', 'password', 'Enter again to confirm', '', '', '5')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-mailto', 'text', 'Email Address', 'Email a notification, including audio file if enabled', '', '6')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-email-all-messages', 'boolean', 'Email Enable', '', 'false', '7')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-attach-file', 'boolean', 'Attach voicemail to email', 'Option to attach audio file to emaie', 'false', '8')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-keep-local-after-email', 'boolean', 'Keep voicemail', 'When disabled, the message will be deleted from the voicemailbox after any notification email is sent (whether or not email is enabled). This allows receiving voicemail via email alone, rather than having the voicemail available from the Web interface or by telephone. CAUTION: \"Email Address\" must be valid and \"Email Enable\" and \"Attach voicemail to email\" must be enabled, OTHERWISE YOUR MESSAGES WILL BE LOST FOREVER.', 'true', '9')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-notify-mailto', 'text', 'Pager Email Address', 'Email a short notification', '', '10')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'vm-notify-email-all-messages', 'boolean', 'Pager Email Enable', '', 'false', '11')",
+		"INSERT INTO voicemail_params VALUES(DEFAULT, 'callmenumber', 'text', 'Call Me Number', '', '', '12')",
+	}
+else
+	vmaildb.table_creation_scripts.voicemail_users = {
+		"CREATE TABLE voicemail_users (uid INTEGER PRIMARY KEY, username VARCHAR(255) UNIQUE)",
+		"CREATE INDEX users_username_idx ON voicemail_users (username)",
+	}
+	vmaildb.table_creation_scripts.voicemail_params = {
+		"CREATE TABLE voicemail_params (nid INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr TEXT, value VARCHAR(255), seq INTEGER)",
 		"CREATE INDEX params_name_idx ON voicemail_params (name)",
 		"INSERT INTO voicemail_params VALUES(null, 'username', 'text', 'Extension', '', '', '1')",
 		"INSERT INTO voicemail_params VALUES(null, 'firstname', 'text', 'User First Name', '', '', '2')",
@@ -73,13 +105,8 @@ vmaildb.table_creation_scripts = {
 		"INSERT INTO voicemail_params VALUES(null, 'vm-notify-mailto', 'text', 'Pager Email Address', 'Email a short notification', '', '10')",
 		"INSERT INTO voicemail_params VALUES(null, 'vm-notify-email-all-messages', 'boolean', 'Pager Email Enable', '', 'false', '11')",
 		"INSERT INTO voicemail_params VALUES(null, 'callmenumber', 'text', 'Call Me Number', '', '', '12')",
-	},
-	voicemail_folders = {
-		"CREATE TABLE voicemail_folders (in_folder VARCHAR(255) PRIMARY KEY, label VARCHAR(255))",
-		"INSERT INTO voicemail_folders VALUES('inbox', 'Inbox')",
-	},
-	voicemail_prefs = {"CREATE TABLE voicemail_prefs (username VARCHAR(255), domain VARCHAR(255), name_path VARCHAR(255), greeting_path VARCHAR(255), password VARCHAR(255))"},
-}
+	}
+end
 
 -- ################################################################################
 -- LOCAL FUNCTIONS
@@ -792,7 +819,11 @@ mymodule.set_bunchsettings = function (self, bunchdata)
 					entry.value.lastname.value=lastname
 					entry.value["vm-password"].value=password
 					if validateentry(entry) then
-						sql = "INSERT INTO voicemail_users VALUES(default, '"..vmaildb.escape(username).."')"
+						if dbengine == db.engine.postgresql then
+							sql = "INSERT INTO voicemail_users VALUES(default, '"..vmaildb.escape(username).."')"
+						else
+							sql = "INSERT INTO voicemail_users VALUES(null, '"..vmaildb.escape(username).."')"
+						end
 						vmaildb.runsqlcommand(sql)
 						sql = "SELECT uid FROM voicemail_users where username ='"..vmaildb.escape(username).."'";
 						uid = vmaildb.getselectresponse(sql)
@@ -864,7 +895,11 @@ mymodule.update_usersettings = function(self, usersettings, action, create)
 				errtxt = "User does not exist"
 			else
 				if create then
-					sql = "INSERT INTO voicemail_users VALUES(default, '"..vmaildb.escape(usersettings.value.username.value).."')"
+					if dbengine == db.engine.postgresql then
+						sql = "INSERT INTO voicemail_users VALUES(default, '"..vmaildb.escape(usersettings.value.username.value).."')"
+					else
+						sql = "INSERT INTO voicemail_users VALUES(null, '"..vmaildb.escape(usersettings.value.username.value).."')"
+					end
 					vmaildb.runsqlcommand(sql)
 				end
 				success,errtxt = setuserparams(usersettings.value)