aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLAssetData.cs
diff options
context:
space:
mode:
authorMelanie Thielker2008-09-14 13:23:02 +0000
committerMelanie Thielker2008-09-14 13:23:02 +0000
commit281955949910eb257b5f7e42e54535ba7812418e (patch)
tree8c62c0006f2567983a686e811513c8c009061aac /OpenSim/Data/MSSQL/MSSQLAssetData.cs
parent* Converted a number of methods within the login processes from private to pr... (diff)
downloadopensim-SC_OLD-281955949910eb257b5f7e42e54535ba7812418e.zip
opensim-SC_OLD-281955949910eb257b5f7e42e54535ba7812418e.tar.gz
opensim-SC_OLD-281955949910eb257b5f7e42e54535ba7812418e.tar.bz2
opensim-SC_OLD-281955949910eb257b5f7e42e54535ba7812418e.tar.xz
Mantis #2124
Thank you, RuudL, for a patch that brings MSSQL up to the same implementation level as MySQL.
Diffstat (limited to 'OpenSim/Data/MSSQL/MSSQLAssetData.cs')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAssetData.cs245
1 files changed, 147 insertions, 98 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
index ed1c91b..4d5ca1c 100644
--- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -41,44 +41,83 @@ namespace OpenSim.Data.MSSQL
41 /// </summary> 41 /// </summary>
42 internal class MSSQLAssetData : AssetDataBase 42 internal class MSSQLAssetData : AssetDataBase
43 { 43 {
44 private const string _migrationStore = "AssetStore";
45
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 47
48 /// <summary>
49 /// Database manager
50 /// </summary>
46 private MSSQLManager database; 51 private MSSQLManager database;
47 52
48 #region IAssetProviderPlugin Members 53 #region IPlugin Members
54
55 override public void Dispose() { }
49 56
50 /// <summary> 57 /// <summary>
51 /// Migration method 58 /// <para>Initialises asset interface</para>
52 /// <list type="bullet">
53 /// <item>Execute "CreateAssetsTable.sql" if tableName == null</item>
54 /// </list>
55 /// </summary> 59 /// </summary>
56 /// <param name="tableName">Name of table</param> 60 override public void Initialise()
57 private void UpgradeAssetsTable(string tableName)
58 { 61 {
59 // null as the version, indicates that the table didn't exist 62 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
60 if (tableName == null) 63 throw new PluginNotInitialisedException(Name);
64 }
65
66 /// <summary>
67 /// Initialises asset interface
68 /// </summary>
69 /// <para>
70 /// a string instead of file, if someone writes the support
71 /// </para>
72 /// <param name="connectionString">connect string</param>
73 override public void Initialise(string connectionString)
74 {
75 if (string.IsNullOrEmpty(connectionString))
61 { 76 {
62 m_log.Info("[ASSET DB]: Creating new database tables"); 77 database = new MSSQLManager(connectionString);
63 database.ExecuteResourceSql("CreateAssetsTable.sql"); 78 }
64 return; 79 else
80 {
81
82 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
83 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
84 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
85 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
86 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
87 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
88
89 database =
90 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
91 settingPassword);
65 } 92 }
66 93
94 //TODO can be removed at some time!!
95 TestTables();
96
97 //New migration to check for DB changes
98 database.CheckMigration(_migrationStore);
67 } 99 }
68 100
69 /// <summary> 101 /// <summary>
70 /// Ensure that the assets related tables exists and are at the latest version 102 /// Database provider version.
71 /// </summary> 103 /// </summary>
72 private void TestTables() 104 override public string Version
73 { 105 {
74 Dictionary<string, string> tableList = new Dictionary<string, string>(); 106 get { return database.getVersion(); }
75 107 }
76 tableList["assets"] = null;
77 database.GetTableVersion(tableList);
78 108
79 UpgradeAssetsTable(tableList["assets"]); 109 /// <summary>
110 /// The name of this DB provider.
111 /// </summary>
112 override public string Name
113 {
114 get { return "MSSQL Asset storage engine"; }
80 } 115 }
81 116
117 #endregion
118
119 #region IAssetProviderPlugin Members
120
82 /// <summary> 121 /// <summary>
83 /// Fetch Asset from database 122 /// Fetch Asset from database
84 /// </summary> 123 /// </summary>
@@ -86,13 +125,26 @@ namespace OpenSim.Data.MSSQL
86 /// <returns></returns> 125 /// <returns></returns>
87 override public AssetBase FetchAsset(UUID assetID) 126 override public AssetBase FetchAsset(UUID assetID)
88 { 127 {
89 Dictionary<string, string> param = new Dictionary<string, string>(); 128 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM assets WHERE id = @id"))
90 param["id"] = assetID.ToString();
91
92 using (IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param))
93 using (IDataReader reader = result.ExecuteReader())
94 { 129 {
95 return database.getAssetRow(reader); 130 command.Parameters.Add(database.CreateParameter("id", assetID));
131 using (IDataReader reader = command.ExecuteReader())
132 {
133 if (reader.Read())
134 {
135 AssetBase asset = new AssetBase();
136 // Region Main
137 asset.FullID = new UUID((string)reader["id"]);
138 asset.Name = (string)reader["name"];
139 asset.Description = (string)reader["description"];
140 asset.Type = Convert.ToSByte(reader["assetType"]);
141 asset.Local = Convert.ToBoolean(reader["local"]);
142 asset.Temporary = Convert.ToBoolean(reader["temporary"]);
143 asset.Data = (byte[])reader["data"];
144 return asset;
145 }
146 return null; // throw new Exception("No rows to return");
147 }
96 } 148 }
97 } 149 }
98 150
@@ -102,34 +154,27 @@ namespace OpenSim.Data.MSSQL
102 /// <param name="asset">the asset</param> 154 /// <param name="asset">the asset</param>
103 override public void CreateAsset(AssetBase asset) 155 override public void CreateAsset(AssetBase asset)
104 { 156 {
105 if (ExistsAsset((UUID) asset.FullID)) 157 if (ExistsAsset(asset.FullID))
106 { 158 {
107 return; 159 return;
108 } 160 }
109 161
110 162 using (AutoClosingSqlCommand command = database.Query(
111 using (AutoClosingSqlCommand cmd =
112 database.Query(
113 "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" + 163 "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" +
114 " VALUES " + 164 " VALUES " +
115 "(@id, @name, @description, @assetType, @local, @temporary, @data)")) 165 "(@id, @name, @description, @assetType, @local, @temporary, @data)"))
116 { 166 {
117
118 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar); 167 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar);
119 //p.Value = asset.FullID.ToString(); 168 //p.Value = asset.FullID.ToString();
120 cmd.Parameters.AddWithValue("id", asset.FullID.ToString()); 169 command.Parameters.Add(database.CreateParameter("id", asset.FullID));
121 cmd.Parameters.AddWithValue("name", asset.Name); 170 command.Parameters.Add(database.CreateParameter("name", asset.Name));
122 cmd.Parameters.AddWithValue("description", asset.Description); 171 command.Parameters.Add(database.CreateParameter("description", asset.Description));
123 SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt); 172 command.Parameters.Add(database.CreateParameter("assetType", asset.Type));
124 e.Value = asset.Type; 173 command.Parameters.Add(database.CreateParameter("local", asset.Local));
125 SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt); 174 command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary));
126 g.Value = asset.Local; 175 command.Parameters.Add(database.CreateParameter("data", asset.Data));
127 SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt); 176
128 h.Value = asset.Temporary; 177 command.ExecuteNonQuery();
129 SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image);
130 i.Value = asset.Data;
131
132 cmd.ExecuteNonQuery();
133 } 178 }
134 } 179 }
135 180
@@ -139,7 +184,7 @@ namespace OpenSim.Data.MSSQL
139 /// <param name="asset">the asset</param> 184 /// <param name="asset">the asset</param>
140 override public void UpdateAsset(AssetBase asset) 185 override public void UpdateAsset(AssetBase asset)
141 { 186 {
142 using (IDbCommand command = database.Query("UPDATE assets set id = @id, " + 187 using (AutoClosingSqlCommand command = database.Query("UPDATE assets set id = @id, " +
143 "name = @name, " + 188 "name = @name, " +
144 "description = @description," + 189 "description = @description," +
145 "assetType = @assetType," + 190 "assetType = @assetType," +
@@ -148,22 +193,14 @@ namespace OpenSim.Data.MSSQL
148 "data = @data where " + 193 "data = @data where " +
149 "id = @keyId;")) 194 "id = @keyId;"))
150 { 195 {
151 SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToString()); 196 command.Parameters.Add(database.CreateParameter("id", asset.FullID));
152 SqlParameter param2 = new SqlParameter("@name", asset.Name); 197 command.Parameters.Add(database.CreateParameter("name", asset.Name));
153 SqlParameter param3 = new SqlParameter("@description", asset.Description); 198 command.Parameters.Add(database.CreateParameter("description", asset.Description));
154 SqlParameter param4 = new SqlParameter("@assetType", asset.Type); 199 command.Parameters.Add(database.CreateParameter("assetType", asset.Type));
155 SqlParameter param6 = new SqlParameter("@local", asset.Local); 200 command.Parameters.Add(database.CreateParameter("local", asset.Local));
156 SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary); 201 command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary));
157 SqlParameter param8 = new SqlParameter("@data", asset.Data); 202 command.Parameters.Add(database.CreateParameter("data", asset.Data));
158 SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToString()); 203 command.Parameters.Add(database.CreateParameter("@keyId", asset.FullID));
159 command.Parameters.Add(param1);
160 command.Parameters.Add(param2);
161 command.Parameters.Add(param3);
162 command.Parameters.Add(param4);
163 command.Parameters.Add(param6);
164 command.Parameters.Add(param7);
165 command.Parameters.Add(param8);
166 command.Parameters.Add(param9);
167 204
168 try 205 try
169 { 206 {
@@ -192,57 +229,69 @@ namespace OpenSim.Data.MSSQL
192 229
193 #endregion 230 #endregion
194 231
195 #region IPlugin Members 232 #region Private Methods
196
197 override public void Dispose() { }
198 233
199 /// <summary> 234 /// <summary>
200 /// <para>Initialises asset interface</para> 235 /// Migration method
201 /// <para> 236 /// <list type="bullet">
202 /// TODO: this would allow you to pass in connnect info as 237 /// <item>Execute "CreateAssetsTable.sql" if tableName == null</item>
203 /// a string instead of file, if someone writes the support 238 /// </list>
204 /// </para>
205 /// </summary> 239 /// </summary>
206 /// <param name="connect">connect string</param> 240 /// <param name="tableName">Name of table</param>
207 override public void Initialise(string connect) 241 private void UpgradeAssetsTable(string tableName)
208 { 242 {
209 Initialise(); 243 // null as the version, indicates that the table didn't exist
244 if (tableName == null)
245 {
246 m_log.Info("[ASSET DB]: Creating new database tables");
247 database.ExecuteResourceSql("CreateAssetsTable.sql");
248 return;
249 }
210 } 250 }
211 251
212 /// <summary> 252 /// <summary>
213 /// Initialises asset interface 253 /// Ensure that the assets related tables exists and are at the latest version
214 /// </summary> 254 /// </summary>
215 /// <remarks>it use mssql_connection.ini</remarks> 255 private void TestTables()
216 override public void Initialise()
217 { 256 {
218 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); 257 Dictionary<string, string> tableList = new Dictionary<string, string>();
219 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
220 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
221 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
222 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
223 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
224 258
225 database = 259 tableList["assets"] = null;
226 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 260 database.GetTableVersion(tableList);
227 settingPassword);
228 261
229 TestTables(); 262 UpgradeAssetsTable(tableList["assets"]);
230 }
231 263
232 /// <summary> 264 //Special for Migrations
233 /// Database provider version. 265 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'"))
234 /// </summary> 266 {
235 override public string Version 267 try
236 { 268 {
237 get { return database.getVersion(); } 269 bool insert = true;
238 } 270 using (SqlDataReader reader = cmd.ExecuteReader())
271 {
272 if (reader.Read()) insert = false;
273 }
274 if (insert)
275 {
276 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
277 cmd.ExecuteNonQuery();
278 }
279 }
280 catch
281 {
282 //No migrations table
283 //HACK create one and add data
284 cmd.CommandText = "create table migrations(name varchar(100), version int)";
285 cmd.ExecuteNonQuery();
286
287 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
288 cmd.ExecuteNonQuery();
289
290 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
291 cmd.ExecuteNonQuery();
292 }
293 }
239 294
240 /// <summary>
241 /// The name of this DB provider.
242 /// </summary>
243 override public string Name
244 {
245 get { return "MSSQL Asset storage engine"; }
246 } 295 }
247 296
248 #endregion 297 #endregion