* Patch from justincc to fix Inconsistent automatic mysql table creation - see bug 169

6 files changed, 183 insertions, 0 deletions

diff --git a/OpenSim/Framework/Data.MySQL/MySQLGridData.cs b/OpenSim/Framework/Data.MySQL/MySQLGridData.cs
index bb0e8b3..ac3d849 100644
--- a/OpenSim/Framework/Data.MySQL/MySQLGridData.cs
+++ b/OpenSim/Framework/Data.MySQL/MySQLGridData.cs
@@ -62,8 +62,42 @@ namespace OpenSim.Framework.Data.MySQL
             database =
                 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
                                  settingPort);
+            
+            TestTables();
         }
 
+        #region Test and initialization code
+
+        /// <summary>
+        /// Ensure that the user related tables exists and are at the latest version
+        /// </summary>
+        private void TestTables()
+        {
+            Dictionary<string, string> tableList = new Dictionary<string, string>();
+
+            tableList["regions"] = null;
+            database.GetTableVersion(tableList);
+
+            UpgradeRegionsTable(tableList["regions"]);
+        }
+        
+        /// <summary>
+        /// Create or upgrade the table if necessary
+        /// </summary>
+        /// <param name="oldVersion">A null indicates that the table does not
+        /// currently exist</param>
+        private void UpgradeRegionsTable(string oldVersion)
+        {
+            // null as the version, indicates that the table didn't exist
+            if (oldVersion == null)
+            {
+                database.ExecuteResourceSql("CreateRegionsTable.sql");
+                return;
+            }
+        }           
+        
+        #endregion
+        
         /// <summary>
         /// Shuts down the grid interface
         /// </summary>
diff --git a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs
index 9c97a6a..c21f899 100644
--- a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs
+++ b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs
@@ -61,8 +61,59 @@ namespace OpenSim.Framework.Data.MySQL
             database =
                 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
                                  settingPort);
+            
+            TestTables();
         }
 
+        #region Test and initialization code
+
+        /// <summary>
+        /// Ensure that the user related tables exists and are at the latest version
+        /// </summary>
+        private void TestTables()
+        {
+            Dictionary<string, string> tableList = new Dictionary<string, string>();
+
+            tableList["agents"] = null;
+            tableList["users"] = null;
+            database.GetTableVersion(tableList);
+
+            UpgradeAgentsTable(tableList["agents"]);
+            UpgradeUsersTable(tableList["users"]);
+        }
+        
+        /// <summary>
+        /// Create or upgrade the table if necessary
+        /// </summary>
+        /// <param name="oldVersion">A null indicates that the table does not
+        /// currently exist</param>
+        private void UpgradeAgentsTable(string oldVersion)
+        {
+            // null as the version, indicates that the table didn't exist
+            if (oldVersion == null)
+            {
+                database.ExecuteResourceSql("CreateAgentsTable.sql");
+                return;
+            }
+        }           
+        
+        /// <summary>
+        /// Create or upgrade the table if necessary
+        /// </summary>
+        /// <param name="oldVersion">A null indicates that the table does not
+        /// currently exist</param>
+        private void UpgradeUsersTable(string oldVersion)
+        {
+            // null as the version, indicates that the table didn't exist
+            if (oldVersion == null)
+            {
+                database.ExecuteResourceSql("CreateUsersTable.sql");
+                return;
+            }
+        }   
+        
+        #endregion
+        
         /// <summary>
         /// Searches the database for a specified user profile
         /// </summary>
diff --git a/OpenSim/Framework/Data.MySQL/Resources/CreateAgentsTable.sql b/OpenSim/Framework/Data.MySQL/Resources/CreateAgentsTable.sql
new file mode 100644
index 0000000..3ef7bc9
--- /dev/null
+++ b/OpenSim/Framework/Data.MySQL/Resources/CreateAgentsTable.sql
@@ -0,0 +1,24 @@
+SET FOREIGN_KEY_CHECKS=0;
+-- ----------------------------
+-- Table structure for agents
+-- ----------------------------
+CREATE TABLE `agents` (
+  `UUID` varchar(36) NOT NULL,
+  `sessionID` varchar(36) NOT NULL,
+  `secureSessionID` varchar(36) NOT NULL,
+  `agentIP` varchar(16) NOT NULL,
+  `agentPort` int(11) NOT NULL,
+  `agentOnline` tinyint(4) NOT NULL,
+  `loginTime` int(11) NOT NULL,
+  `logoutTime` int(11) NOT NULL,
+  `currentRegion` varchar(36) NOT NULL,
+  `currentHandle` bigint(20) unsigned NOT NULL,
+  `currentPos` varchar(64) NOT NULL,
+  PRIMARY KEY  (`UUID`),
+  UNIQUE KEY `session` (`sessionID`),
+  UNIQUE KEY `ssession` (`secureSessionID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1';
+
+-- ----------------------------
+-- Records 
+-- ----------------------------
diff --git a/OpenSim/Framework/Data.MySQL/Resources/CreateLogsTable.sql b/OpenSim/Framework/Data.MySQL/Resources/CreateLogsTable.sql
new file mode 100644
index 0000000..64b3a80
--- /dev/null
+++ b/OpenSim/Framework/Data.MySQL/Resources/CreateLogsTable.sql
@@ -0,0 +1,10 @@
+CREATE TABLE `logs` (
+  `logID` int(10) unsigned NOT NULL auto_increment,
+  `target` varchar(36) default NULL,
+  `server` varchar(64) default NULL,
+  `method` varchar(64) default NULL,
+  `arguments` varchar(255) default NULL,
+  `priority` int(11) default NULL,
+  `message` text,
+  PRIMARY KEY  (`logID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1';
diff --git a/OpenSim/Framework/Data.MySQL/Resources/CreateRegionsTable.sql b/OpenSim/Framework/Data.MySQL/Resources/CreateRegionsTable.sql
new file mode 100644
index 0000000..07b0d9b
--- /dev/null
+++ b/OpenSim/Framework/Data.MySQL/Resources/CreateRegionsTable.sql
@@ -0,0 +1,30 @@
+CREATE TABLE `regions` (
+  `uuid` varchar(36) NOT NULL,
+  `regionHandle` bigint(20) unsigned NOT NULL,
+  `regionName` varchar(32) default NULL,
+  `regionRecvKey` varchar(128) default NULL,
+  `regionSendKey` varchar(128) default NULL,
+  `regionSecret` varchar(128) default NULL,
+  `regionDataURI` varchar(255) default NULL,
+  `serverIP` varchar(64) default NULL,
+  `serverPort` int(10) unsigned default NULL,
+  `serverURI` varchar(255) default NULL,
+  `locX` int(10) unsigned default NULL,
+  `locY` int(10) unsigned default NULL,
+  `locZ` int(10) unsigned default NULL,
+  `eastOverrideHandle` bigint(20) unsigned default NULL,
+  `westOverrideHandle` bigint(20) unsigned default NULL,
+  `southOverrideHandle` bigint(20) unsigned default NULL,
+  `northOverrideHandle` bigint(20) unsigned default NULL,
+  `regionAssetURI` varchar(255) default NULL,
+  `regionAssetRecvKey` varchar(128) default NULL,
+  `regionAssetSendKey` varchar(128) default NULL,
+  `regionUserURI` varchar(255) default NULL,
+  `regionUserRecvKey` varchar(128) default NULL,
+  `regionUserSendKey` varchar(128) default NULL, `regionMapTexture` varchar(36) default NULL,
+  `serverHttpPort` int(10) default NULL, `serverRemotingPort` int(10) default NULL,
+  PRIMARY KEY  (`uuid`),
+  KEY `regionName` (`regionName`),
+  KEY `regionHandle` (`regionHandle`),
+  KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 1';
diff --git a/OpenSim/Framework/Data.MySQL/Resources/CreateUsersTable.sql b/OpenSim/Framework/Data.MySQL/Resources/CreateUsersTable.sql
new file mode 100644
index 0000000..faac541
--- /dev/null
+++ b/OpenSim/Framework/Data.MySQL/Resources/CreateUsersTable.sql
@@ -0,0 +1,34 @@
+SET FOREIGN_KEY_CHECKS=0;
+-- ----------------------------
+-- Table structure for users
+-- ----------------------------
+CREATE TABLE `users` (
+  `UUID` varchar(36) NOT NULL default '',
+  `username` varchar(32) NOT NULL,
+  `lastname` varchar(32) NOT NULL,
+  `passwordHash` varchar(32) NOT NULL,
+  `passwordSalt` varchar(32) NOT NULL,
+  `homeRegion` bigint(20) unsigned default NULL,
+  `homeLocationX` float default NULL,
+  `homeLocationY` float default NULL,
+  `homeLocationZ` float default NULL,
+  `homeLookAtX` float default NULL,
+  `homeLookAtY` float default NULL,
+  `homeLookAtZ` float default NULL,
+  `created` int(11) NOT NULL,
+  `lastLogin` int(11) NOT NULL,
+  `userInventoryURI` varchar(255) default NULL,
+  `userAssetURI` varchar(255) default NULL,
+  `profileCanDoMask` int(10) unsigned default NULL,
+  `profileWantDoMask` int(10) unsigned default NULL,
+  `profileAboutText` text,
+  `profileFirstText` text,
+  `profileImage` varchar(36) default NULL,
+  `profileFirstImage` varchar(36) default NULL,
+  PRIMARY KEY  (`UUID`),
+  UNIQUE KEY `usernames` (`username`,`lastname`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1';
+
+-- ----------------------------
+-- Records 
+-- ----------------------------