From 83f727bb7c1da0f49ccf727bea8384ea4ebdd7d1 Mon Sep 17 00:00:00 2001
From: Teravus Ovares
Date: Wed, 12 Dec 2007 01:23:15 +0000
Subject: * Patch from justincc to fix Inconsistent automatic mysql table
creation - see bug 169
---
OpenSim/Framework/Data.MySQL/MySQLGridData.cs | 34 +++++++++++++++
OpenSim/Framework/Data.MySQL/MySQLUserData.cs | 51 ++++++++++++++++++++++
.../Data.MySQL/Resources/CreateAgentsTable.sql | 24 ++++++++++
.../Data.MySQL/Resources/CreateLogsTable.sql | 10 +++++
.../Data.MySQL/Resources/CreateRegionsTable.sql | 30 +++++++++++++
.../Data.MySQL/Resources/CreateUsersTable.sql | 34 +++++++++++++++
6 files changed, 183 insertions(+)
create mode 100644 OpenSim/Framework/Data.MySQL/Resources/CreateAgentsTable.sql
create mode 100644 OpenSim/Framework/Data.MySQL/Resources/CreateLogsTable.sql
create mode 100644 OpenSim/Framework/Data.MySQL/Resources/CreateRegionsTable.sql
create mode 100644 OpenSim/Framework/Data.MySQL/Resources/CreateUsersTable.sql
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
+-- ----------------------------
--
cgit v1.1