Make column names lower-case, change hash column type to bytea and remove old commented lines of code

2 files changed, 80 insertions, 52 deletions

diff --git a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
index a8a9e38..967aeba 100644
--- a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
@@ -140,9 +140,9 @@ namespace OpenSim.Data.PGSQL
                     dbcon.Open();
 
                     using (NpgsqlCommand cmd = new NpgsqlCommand(
-                        @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Local"", ""Temporary"", ""AssetFlags"", ""CreatorID"", ""Data""
+                        @"SELECT name, description, access_time, ""AssetType"", local, temporary, asset_flags, creatorid, data
                             FROM XAssetsMeta 
-                            JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ""ID""=:ID",
+                            JOIN XAssetsData ON XAssetsMeta.hash = XAssetsData.Hash WHERE id=:ID",
                         dbcon))
                     {
                         cmd.Parameters.AddWithValue("ID", assetID.ToString());
@@ -155,21 +155,21 @@ namespace OpenSim.Data.PGSQL
                                 {
                                     asset = new AssetBase(
                                         assetID,
-                                        (string)dbReader["Name"],
+                                        (string)dbReader["name"],
                                         Convert.ToSByte(dbReader["AssetType"]),
-                                        dbReader["CreatorID"].ToString());
+                                        dbReader["creatorid"].ToString());
 
-                                    asset.Data = (byte[])dbReader["Data"];
-                                    asset.Description = (string)dbReader["Description"];
+                                    asset.Data = (byte[])dbReader["data"];
+                                    asset.Description = (string)dbReader["description"];
 
-                                    string local = dbReader["Local"].ToString();
+                                    string local = dbReader["local"].ToString();
                                     if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
                                         asset.Local = true;
                                     else
                                         asset.Local = false;
 
-                                    asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
-                                    asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
+                                    asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
+                                    asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
 
                                     if (m_enableCompression)
                                     {
@@ -186,7 +186,7 @@ namespace OpenSim.Data.PGSQL
                                         }
                                     }
 
-                                    UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
+                                    UpdateAccessTime(asset.Metadata, (int)dbReader["access_time"]);
                                 }
                             }
                         }
@@ -256,48 +256,35 @@ namespace OpenSim.Data.PGSQL
 //                            "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
 //                            asset.ID, asset.Name, hash, compressedData.Length);
 
-                        m_log.DebugFormat("[XASSET DB]: Hash Info {0} {1}", hash, hash.Length);
-
                         try
                         {
                             using (NpgsqlCommand cmd =
                                 new NpgsqlCommand(
-                                    @"insert INTO XAssetsMeta(""ID"", ""Hash"", ""Name"", ""Description"", " + " \"AssetType\" " + @", ""Local"", ""Temporary"", ""CreateTime"", ""AccessTime"", ""AssetFlags"", ""CreatorID"")
+                                    @"insert INTO XAssetsMeta(id, hash, name, description, " + " \"AssetType\" " + @", local, temporary, create_time, access_time, asset_flags, creatorid)
                                        Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
-                                        where not exists( Select ""ID"" from XAssetsMeta where ""ID"" = :ID);
+                                        where not exists( Select id from XAssetsMeta where id = :ID);
 
                                       update XAssetsMeta
-                                          set ""ID"" = :ID, ""Hash"" = :Hash, ""Name"" = :Name, ""Description"" = :Description, " +
-                                              "\"AssetType\" " + @" = :AssetType, ""Local"" = :Local, ""Temporary"" = :Temporary, ""CreateTime"" = :CreateTime, 
-                                              ""AccessTime"" = :AccessTime, ""AssetFlags"" = :AssetFlags, ""CreatorID"" = :CreatorID
-                                        where ""ID"" = :ID;
+                                          set id = :ID, hash = :Hash, name = :Name, description = :Description, " +
+                                              "\"AssetType\" " + @" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime, 
+                                              access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID
+                                        where id = :ID;
                                      ",
                                     dbcon))
                             {
                                 // create unix epoch time
                                 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
                                 cmd.Parameters.Add(m_database.CreateParameter("ID", asset.ID));
-                                // cmd.Parameters.AddWithValue("ID", asset.ID);
                                 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
-                                // cmd.Parameters.AddWithValue("Hash", hash);
                                 cmd.Parameters.Add(m_database.CreateParameter("Name", assetName));
-                                // cmd.Parameters.AddWithValue("Name", assetName);
                                 cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription));
-                                // cmd.Parameters.AddWithValue("Description", assetDescription);
                                 cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type));
-                                // cmd.Parameters.AddWithValue("AssetType", asset.Type);
                                 cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local));
-                                // cmd.Parameters.AddWithValue("Local", asset.Local);
                                 cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary));
-                                // cmd.Parameters.AddWithValue("Temporary", asset.Temporary);
                                 cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now));
-                                // cmd.Parameters.AddWithValue("CreateTime", now);
                                 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now));
-                                // cmd.Parameters.AddWithValue("AccessTime", now);
                                 cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID));
-                                // cmd.Parameters.AddWithValue("CreatorID", asset.Metadata.CreatorID);
                                 cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags));
-                                // cmd.Parameters.AddWithValue("AssetFlags", (int)asset.Flags);
 
                                 cmd.ExecuteNonQuery();
                             }
@@ -318,13 +305,11 @@ namespace OpenSim.Data.PGSQL
                             {
                                 using (NpgsqlCommand cmd =
                                     new NpgsqlCommand(
-                                        @"INSERT INTO XAssetsData(""Hash"", ""Data"") VALUES(:Hash, :Data)",
+                                        @"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)",
                                         dbcon))
                                 {
                                     cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
-                                    // cmd.Parameters.AddWithValue("Hash", hash);
                                     cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data));
-                                    // cmd.Parameters.AddWithValue("Data", asset.Data);
                                     cmd.ExecuteNonQuery();
                                 }
                             }
@@ -367,17 +352,15 @@ namespace OpenSim.Data.PGSQL
                 {
                     dbcon.Open();
                     NpgsqlCommand cmd =
-                        new NpgsqlCommand(@"update XAssetsMeta set ""AccessTime""=:AccessTime where ID=:ID", dbcon);
+                        new NpgsqlCommand(@"update XAssetsMeta set access_time=:AccessTime where id=:ID", dbcon);
 
                     try
                     {
                         using (cmd)
                         {
                             // create unix epoch time
-                            cmd.Parameters.Add(m_database.CreateParameter("ID", assetMetadata.ID));
-                            // cmd.Parameters.AddWithValue("ID", assetMetadata.ID);
-                            cmd.Parameters.Add(m_database.CreateParameter("AccessTime", (int)Utils.DateTimeToUnixTime(now)));
-                            // cmd.Parameters.AddWithValue("AccessTime", (int)Utils.DateTimeToUnixTime(now));
+                            cmd.Parameters.Add(m_database.CreateParameter("id", assetMetadata.ID));
+                            cmd.Parameters.Add(m_database.CreateParameter("access_time", (int)Utils.DateTimeToUnixTime(now)));
                             cmd.ExecuteNonQuery();
                         }
                     }
@@ -405,9 +388,9 @@ namespace OpenSim.Data.PGSQL
 
             bool exists = false;
 
-            using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""Hash"" FROM XAssetsData WHERE ""Hash""=:Hash", dbcon))
+            using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT hash FROM XAssetsData WHERE hash=:Hash", dbcon))
             {
-                cmd.Parameters.AddWithValue("Hash", hash);
+                cmd.Parameters.AddWithValue("hash", hash);
 
                 try
                 {
@@ -444,7 +427,7 @@ namespace OpenSim.Data.PGSQL
             HashSet<UUID> exist = new HashSet<UUID>();
 
             string ids = "'" + string.Join("','", uuids) + "'";
-            string sql = string.Format(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID"" IN ({0})", ids);
+            string sql = string.Format(@"SELECT id FROM XAssetsMeta WHERE id IN ({0})", ids);
 
             using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
             {
@@ -484,9 +467,9 @@ namespace OpenSim.Data.PGSQL
                 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
                 {
                     dbcon.Open();
-                    using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID""=:ID", dbcon))
+                    using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT id FROM XAssetsMeta WHERE id=:ID", dbcon))
                     {
-                        cmd.Parameters.AddWithValue("ID", uuid.ToString());
+                        cmd.Parameters.AddWithValue("id", uuid.ToString());
 
                         try
                         {
@@ -528,7 +511,7 @@ namespace OpenSim.Data.PGSQL
                 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
                 {
                     dbcon.Open();
-                    NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Temporary"", ""ID"", ""AssetFlags"", ""CreatorID""
+                    NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT name, description, access_time, ""AssetType"", temporary, id, asset_flags, creatorid
                                             FROM XAssetsMeta 
                                             LIMIT :start, :count", dbcon);
                     cmd.Parameters.AddWithValue("start", start);
@@ -541,18 +524,18 @@ namespace OpenSim.Data.PGSQL
                             while (dbReader.Read())
                             {
                                 AssetMetadata metadata = new AssetMetadata();
-                                metadata.Name = (string)dbReader["Name"];
-                                metadata.Description = (string)dbReader["Description"];
+                                metadata.Name = (string)dbReader["name"];
+                                metadata.Description = (string)dbReader["description"];
                                 metadata.Type = Convert.ToSByte(dbReader["AssetType"]);
-                                metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
-                                metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
-                                metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
-                                metadata.CreatorID = dbReader["CreatorID"].ToString();
+                                metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]);
+                                metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
+                                metadata.FullID = DBGuid.FromDB(dbReader["id"]);
+                                metadata.CreatorID = dbReader["creatorid"].ToString();
 
                                 // We'll ignore this for now - it appears unused!
 //                                metadata.SHA1 = dbReader["hash"]);
 
-                                UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
+                                UpdateAccessTime(metadata, (int)dbReader["access_time"]);
 
                                 retList.Add(metadata);
                             }
@@ -578,9 +561,9 @@ namespace OpenSim.Data.PGSQL
                 {
                     dbcon.Open();
 
-                    using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where ""ID""=:ID", dbcon))
+                    using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where id=:ID", dbcon))
                     {
-                        cmd.Parameters.AddWithValue("ID", id);
+                        cmd.Parameters.AddWithValue(id, id);
                         cmd.ExecuteNonQuery();
                     }
 
diff --git a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
index 1513cc3..8fb3656 100644
--- a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
+++ b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
@@ -36,4 +36,49 @@ ALTER TABLE xassetsmeta ALTER COLUMN "Temporary" SET DATA TYPE boolean USING CAS
 ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66);
 ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66);
 
+COMMIT;
+
+:VERSION 3
+
+BEGIN;
+
+ALTER TABLE xassetsmeta RENAME COLUMN "ID" TO id;
+ALTER TABLE xassetsmeta RENAME COLUMN "Hash" TO hash;
+ALTER TABLE xassetsmeta RENAME COLUMN "Name" TO name;
+ALTER TABLE xassetsmeta RENAME COLUMN "Description" TO description;
+ALTER TABLE xassetsmeta RENAME COLUMN "Local" to local;
+ALTER TABLE xassetsmeta RENAME COLUMN "Temporary" TO temporary;
+ALTER TABLE xassetsmeta RENAME COLUMN "CreateTime" TO create_time;
+ALTER TABLE xassetsmeta RENAME COLUMN "AccessTime" TO access_time;
+ALTER TABLE xassetsmeta RENAME COLUMN "AssetFlags" TO asset_flags;
+ALTER TABLE xassetsmeta RENAME COLUMN "CreatorID" TO creatorid;
+ALTER TABLE xassetsmeta DROP CONSTRAINT xassetsmeta_pkey;
+ALTER TABLE xassetsmeta ADD PRIMARY KEY (id);
+
+
+ALTER TABLE xassetsdata RENAME COLUMN "Hash" TO hash;
+ALTER TABLE xassetsdata RENAME COLUMN "Data" TO data;
+ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey;
+ALTER TABLE xassetsdata ADD PRIMARY KEY (hash);
+
+COMMIT;
+
+
+:VERSION 4
+
+BEGIN;
+
+;; This is a harsh way of migrating these columns to
+;; a different data type, but it didn't work otherwise
+;; and we have a strict warning when using the module
+;; so here we go ...
+
+ALTER TABLE xassetsmeta DROP COLUMN hash;
+ALTER TABLE xassetsmeta ADD COLUMN hash bytea NOT NULL;
+
+ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey;
+ALTER TABLE xassetsdata DROP COLUMN hash;
+ALTER TABLE xassetsdata ADD COLUMN hash bytea NOT NULL;
+ALTER TABLE xassetsdata ADD PRIMARY KEY (hash);
+
 COMMIT;
\ No newline at end of file