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
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";
+
+
+ /// <summary>
+ /// We're only using this to version the table!
+ /// </summary>
+
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);
}
}
+ /// <summary>
+ /// Given a list of tables, return the version of the tables, as seen in the database
+ /// </summary>
+ /// <param name="tableList"></param>
+ public void GetTableVersion(Dictionary<string, string> 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<string, string> tableList = new Dictionary<string, string>();
+
+ tableList["land"] = null;
+ dbconn.Open();
+ GetTableVersion(tableList,dbconn);
+
+ UpgradeLandTable(tableList["land"], dbconn);
+ //database.Close();
+
+ }
+
+ /// <summary>
+ /// Execute a SQL statement stored in a resource, as a string
+ /// </summary>
+ /// <param name="name"></param>
+ public void ExecuteResourceSql(string name, MySqlConnection dbcon)
+ {
+ MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
+ cmd.ExecuteNonQuery();
+ }
+
+ /// <summary>
+ /// Extract a named string resource from the embedded resources
+ /// </summary>
+ /// <param name="name">name of embedded resource</param>
+ /// <returns>string contained within the embedded resource</returns>
+ 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