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 + + /// + /// Ensure that the user related tables exists and are at the latest version + /// + private void TestTables() + { + Dictionary tableList = new Dictionary(); + + tableList["regions"] = null; + database.GetTableVersion(tableList); + + UpgradeRegionsTable(tableList["regions"]); + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + 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 + /// /// Shuts down the grid interface /// 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 + + /// + /// Ensure that the user related tables exists and are at the latest version + /// + private void TestTables() + { + Dictionary tableList = new Dictionary(); + + tableList["agents"] = null; + tableList["users"] = null; + database.GetTableVersion(tableList); + + UpgradeAgentsTable(tableList["agents"]); + UpgradeUsersTable(tableList["users"]); + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeAgentsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateAgentsTable.sql"); + return; + } + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + 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 + /// /// Searches the database for a specified user profile /// 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