PGSQL migrations for IM_Store, UserProfiles, removal of casting exception for UserProfiles and adding double precision to PGSQL Manager

PGSQL migrations for IM_Store, UserProfiles, removal of casting
exception for UserProfiles and adding double precision to PGSQL Manager.

Fixes the Offline IM to Email toggle and prepares for fixing Offline
IM.

Signed-off-by: Oren Hurvitz <orenh@kitely.com>

4 files changed, 33 insertions, 1 deletions

diff --git a/OpenSim/Data/PGSQL/PGSQLManager.cs b/OpenSim/Data/PGSQL/PGSQLManager.cs
index 97f40b2..46f835a 100644
--- a/OpenSim/Data/PGSQL/PGSQLManager.cs
+++ b/OpenSim/Data/PGSQL/PGSQLManager.cs
@@ -249,6 +249,10 @@ namespace OpenSim.Data.PGSQL
             {
                 return (DateTime)value;
             }
+            if (PGFieldType == "double precision")
+            {
+                return (Double)value;
+            }
             return CreateParameterValue(value);
         }
 
diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
index 70c67dd..f166976 100644
--- a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
@@ -877,7 +877,7 @@ namespace OpenSim.Data.PGSQL
         {
             string query = string.Empty;
 
-            query += "SELECT imviaemail,visible,email FROM ";
+            query += "SELECT imviaemail::VARCHAR,visible::VARCHAR,email FROM ";
             query += "usersettings WHERE ";
             query += "useruuid = :Id";
 
diff --git a/OpenSim/Data/PGSQL/Resources/IM_Store.migrations b/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
index 70dc011..eb97824 100644
--- a/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
+++ b/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
@@ -24,3 +24,22 @@ DELETE FROM `migrations` WHERE name='diva_im_Store';
 */
 
 COMMIT;
+
+:VERSION 3         # -------------------------- 
+
+BEGIN;
+
+-- dropping the table here as there most likely is only one record in the table at the time of migration
+
+DROP TABLE IF EXISTS "public"."im_offline";
+CREATE TABLE "public"."im_offline" (
+        "ID" serial,
+        "PrincipalID" uuid NOT NULL,
+        "Message" text NOT NULL COLLATE "default",
+        "TMStamp" timestamp(6) NOT NULL DEFAULT clock_timestamp(),
+        "FromID" uuid NOT NULL
+)
+WITH (OIDS=FALSE);
+ALTER TABLE "public"."im_offline" ADD PRIMARY KEY ("ID","PrincipalID","FromID") NOT DEFERRABLE INITIALLY IMMEDIATE;
+
+COMMIT;
\ No newline at end of file
diff --git a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
index 1544b48..a6bd8ca 100644
--- a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
+++ b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
@@ -143,4 +143,13 @@ ALTER TABLE usersettings ALTER COLUMN visible SET DATA TYPE boolean USING CASE W
 ALTER TABLE usersettings ADD COLUMN email varchar(254) NOT NULL;
 ALTER TABLE usersettings ADD PRIMARY KEY (useruuid);
 
+COMMIT;
+
+
+:VERSION 5         # -------------------------------
+
+BEGIN;
+
+ALTER TABLE usersettings ALTER COLUMN imviaemail SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
+
 COMMIT;
\ No newline at end of file