From b3f44fbb94287d58c635cad7d75e5223ef35aedf Mon Sep 17 00:00:00 2001 From: Teravus Ovares Date: Wed, 30 Apr 2008 20:08:15 +0000 Subject: * Refactored the land table to be versionable in mySQL. * Added AuthbuyerID so that if someone sets a land for sale to someone and then restarts the simulator, when the simulator comes back up, the setting persists. --- OpenSim/Data/MySQL/MySQLDataStore.cs | 139 +++++++++++++++++++-- OpenSim/Data/MySQL/Resources/CreateLandTable.sql | 39 ++++++ .../MySQL/Resources/UpgradeLandTableToVersion2.sql | 3 + 3 files changed, 170 insertions(+), 11 deletions(-) create mode 100644 OpenSim/Data/MySQL/Resources/CreateLandTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeLandTableToVersion2.sql (limited to 'OpenSim') diff --git a/OpenSim/Data/MySQL/MySQLDataStore.cs b/OpenSim/Data/MySQL/MySQLDataStore.cs index 7714fdc..d438064 100644 --- a/OpenSim/Data/MySQL/MySQLDataStore.cs +++ b/OpenSim/Data/MySQL/MySQLDataStore.cs @@ -50,6 +50,12 @@ namespace OpenSim.Data.MySQL private const string m_terrainSelect = "select * from terrain limit 1"; private const string m_landSelect = "select * from land"; private const string m_landAccessListSelect = "select * from landaccesslist"; + + + /// + /// We're only using this to version the table! + /// + private DataSet m_dataSet; private MySqlDataAdapter m_primDataAdapter; @@ -85,6 +91,8 @@ namespace OpenSim.Data.MySQL m_log.Info("[DATASTORE]: MySql - connecting: " + connectionstring); m_connection = new MySqlConnection(connectionstring); + TestTablesVersionable(m_connection); + MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection); m_primDataAdapter = new MySqlDataAdapter(primSelectCmd); @@ -103,6 +111,7 @@ namespace OpenSim.Data.MySQL MySqlCommand landAccessListSelectCmd = new MySqlCommand(m_landAccessListSelect, m_connection); m_landAccessListDataAdapter = new MySqlDataAdapter(landAccessListSelectCmd); + TestTables(m_connection); lock (m_dataSet) @@ -141,7 +150,104 @@ namespace OpenSim.Data.MySQL m_landAccessListDataAdapter.Fill(m_landAccessListTable); } } + /// + /// Given a list of tables, return the version of the tables, as seen in the database + /// + /// + public void GetTableVersion(Dictionary tableList, MySqlConnection dbcon) + { + lock (dbcon) + { + MySqlCommand tablesCmd = + new MySqlCommand( + "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", + dbcon); + tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); + using (MySqlDataReader tables = tablesCmd.ExecuteReader()) + { + while (tables.Read()) + { + try + { + string tableName = (string)tables["TABLE_NAME"]; + string comment = (string)tables["TABLE_COMMENT"]; + if (tableList.ContainsKey(tableName)) + { + tableList[tableName] = comment; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + tables.Close(); + } + } + } + private void TestTablesVersionable(MySqlConnection dbconn) + { + Dictionary tableList = new Dictionary(); + + tableList["land"] = null; + dbconn.Open(); + GetTableVersion(tableList,dbconn); + + UpgradeLandTable(tableList["land"], dbconn); + //database.Close(); + + } + + /// + /// Execute a SQL statement stored in a resource, as a string + /// + /// + public void ExecuteResourceSql(string name, MySqlConnection dbcon) + { + MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); + cmd.ExecuteNonQuery(); + } + + /// + /// Extract a named string resource from the embedded resources + /// + /// name of embedded resource + /// string contained within the embedded resource + private string getResourceString(string name) + { + Assembly assem = GetType().Assembly; + string[] names = assem.GetManifestResourceNames(); + + foreach (string s in names) + { + if (s.EndsWith(name)) + { + using (Stream resource = assem.GetManifestResourceStream(s)) + { + using (StreamReader resourceReader = new StreamReader(resource)) + { + string resourceString = resourceReader.ReadToEnd(); + return resourceString; + } + } + } + } + throw new Exception(string.Format("Resource '{0}' was not found", name)); + } + private void UpgradeLandTable(string oldVersion, MySqlConnection dbconn) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + ExecuteResourceSql("CreateLandTable.sql",dbconn); + oldVersion = "Rev. 2; InnoDB free: 0 kB"; + } + if (!oldVersion.Contains("Rev.")) + { + ExecuteResourceSql("UpgradeLandTableToVersion2.sql", dbconn); + } + } public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID) { lock (m_dataSet) @@ -673,6 +779,7 @@ namespace OpenSim.Data.MySQL createCol(land, "UserLookAtX", typeof (Double)); createCol(land, "UserLookAtY", typeof (Double)); createCol(land, "UserLookAtZ", typeof (Double)); + createCol(land, "AuthBuyerID", typeof (String)); land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]}; @@ -925,7 +1032,14 @@ namespace OpenSim.Data.MySQL newData.musicURL = (String) row["MusicURL"]; newData.passHours = Convert.ToSingle(row["PassHours"]); newData.passPrice = Convert.ToInt32(row["PassPrice"]); - newData.snapshotID = (String) row["SnapshotUUID"]; + LLUUID authedbuyer = LLUUID.Zero; + LLUUID snapshotID = LLUUID.Zero; + + Helpers.TryParse((string)row["AuthBuyerID"], out authedbuyer); + Helpers.TryParse((string)row["SnapshotUUID"], out snapshotID); + + newData.authBuyerID = authedbuyer; + newData.snapshotID = snapshotID; newData.userLocation = new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]), @@ -1096,6 +1210,7 @@ namespace OpenSim.Data.MySQL row["UserLookAtX"] = land.userLookAt.X; row["UserLookAtY"] = land.userLookAt.Y; row["UserLookAtZ"] = land.userLookAt.Z; + row["AuthBuyerID"] = land.authBuyerID; } private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID) @@ -1479,14 +1594,16 @@ namespace OpenSim.Data.MySQL string createShapes = defineTable(createShapeTable()); string createItems = defineTable(createItemsTable()); string createTerrain = defineTable(createTerrainTable()); - string createLand = defineTable(createLandTable()); + + // Land table is created from the Versionable Test Table routine now. + //string createLand = defineTable(createLandTable()); string createLandAccessList = defineTable(createLandAccessListTable()); MySqlCommand pcmd = new MySqlCommand(createPrims, conn); MySqlCommand scmd = new MySqlCommand(createShapes, conn); MySqlCommand icmd = new MySqlCommand(createItems, conn); MySqlCommand tcmd = new MySqlCommand(createTerrain, conn); - MySqlCommand lcmd = new MySqlCommand(createLand, conn); + //MySqlCommand lcmd = new MySqlCommand(createLand, conn); MySqlCommand lalcmd = new MySqlCommand(createLandAccessList, conn); if (conn.State != ConnectionState.Open) @@ -1539,14 +1656,14 @@ namespace OpenSim.Data.MySQL m_log.WarnFormat("[MySql]: Terrain Table Already Exists: {0}", e); } - try - { - lcmd.ExecuteNonQuery(); - } - catch (MySqlException e) - { - m_log.WarnFormat("[MySql]: Land Table Already Exists: {0}", e); - } + //try + //{ + //lcmd.ExecuteNonQuery(); + //} + //catch (MySqlException e) + //{ + //m_log.WarnFormat("[MySql]: Land Table Already Exists: {0}", e); + //} try { diff --git a/OpenSim/Data/MySQL/Resources/CreateLandTable.sql b/OpenSim/Data/MySQL/Resources/CreateLandTable.sql new file mode 100644 index 0000000..8a1b626 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateLandTable.sql @@ -0,0 +1,39 @@ +CREATE TABLE `land` + ( + `UUID` varchar (255) NOT NULL, + `RegionUUID` varchar (255) DEFAULT NULL , + `LocalLandID` int (11) DEFAULT NULL , + `Bitmap` longblob, + `Name` varchar (255) DEFAULT NULL , + `Description` varchar (255) DEFAULT NULL , + `OwnerUUID` varchar (255) DEFAULT NULL , + `IsGroupOwned` int (11) DEFAULT NULL , + `Area` int (11) DEFAULT NULL , + `AuctionID` int (11) DEFAULT NULL , + `Category` int (11) DEFAULT NULL , + `ClaimDate` int (11) DEFAULT NULL , + `ClaimPrice` int (11) DEFAULT NULL , + `GroupUUID` varchar (255) DEFAULT NULL , + `SalePrice` int (11) DEFAULT NULL , + `LandStatus` int (11) DEFAULT NULL , + `LandFlags` int (11) DEFAULT NULL , + `LandingType` int (11) DEFAULT NULL , + `MediaAutoScale` int (11) DEFAULT NULL , + `MediaTextureUUID` varchar (255) DEFAULT NULL , + `MediaURL` varchar (255) DEFAULT NULL , + `MusicURL` varchar (255) DEFAULT NULL , + `PassHours` float DEFAULT NULL , + `PassPrice` int (11) DEFAULT NULL , + `SnapshotUUID` varchar (255) DEFAULT NULL , + `UserLocationX` float DEFAULT NULL , + `UserLocationY` float DEFAULT NULL , + `UserLocationZ` float DEFAULT NULL , + `UserLookAtX` float DEFAULT NULL , + `UserLookAtY` float DEFAULT NULL , + `UserLookAtZ` float DEFAULT NULL , + `AuthbuyerID` varchar(36) default '00000000-0000-0000-0000-000000000000' not null, + + PRIMARY KEY (`UUID`) + ) + ENGINE=INNODB + DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/UpgradeLandTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeLandTableToVersion2.sql new file mode 100644 index 0000000..eb024be --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeLandTableToVersion2.sql @@ -0,0 +1,3 @@ +ALTER TABLE `land` + ADD COLUMN `AuthbuyerID` varchar(36) default '00000000-0000-0000-0000-000000000000' not null, +COMMENT='Rev. 2'; \ No newline at end of file -- cgit v1.1