aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs384
1 files changed, 0 insertions, 384 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs b/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
deleted file mode 100644
index 4145d95..0000000
--- a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
+++ /dev/null
@@ -1,384 +0,0 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using System.Data.SqlClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37using System.Text;
38
39namespace OpenSim.Data.MSSQL
40{
41 public class MSSQLGenericTableHandler<T> where T : class, new()
42 {
43// private static readonly ILog m_log =
44// LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 protected string m_ConnectionString;
47 protected MSSQLManager m_database; //used for parameter type translation
48 protected Dictionary<string, FieldInfo> m_Fields =
49 new Dictionary<string, FieldInfo>();
50
51 protected List<string> m_ColumnNames = null;
52 protected string m_Realm;
53 protected FieldInfo m_DataField = null;
54
55 public MSSQLGenericTableHandler(string connectionString,
56 string realm, string storeName)
57 {
58 m_Realm = realm;
59
60 m_ConnectionString = connectionString;
61
62 if (storeName != String.Empty)
63 {
64 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
65 {
66 conn.Open();
67 Migration m = new Migration(conn, GetType().Assembly, storeName);
68 m.Update();
69 }
70
71 }
72 m_database = new MSSQLManager(m_ConnectionString);
73
74 Type t = typeof(T);
75 FieldInfo[] fields = t.GetFields(BindingFlags.Public |
76 BindingFlags.Instance |
77 BindingFlags.DeclaredOnly);
78
79 if (fields.Length == 0)
80 return;
81
82 foreach (FieldInfo f in fields)
83 {
84 if (f.Name != "Data")
85 m_Fields[f.Name] = f;
86 else
87 m_DataField = f;
88 }
89
90 }
91
92 private void CheckColumnNames(SqlDataReader reader)
93 {
94 if (m_ColumnNames != null)
95 return;
96
97 m_ColumnNames = new List<string>();
98
99 DataTable schemaTable = reader.GetSchemaTable();
100 foreach (DataRow row in schemaTable.Rows)
101 {
102 if (row["ColumnName"] != null &&
103 (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
104 m_ColumnNames.Add(row["ColumnName"].ToString());
105
106 }
107 }
108
109 private List<string> GetConstraints()
110 {
111 List<string> constraints = new List<string>();
112 string query = string.Format(@"SELECT
113 COL_NAME(ic.object_id,ic.column_id) AS column_name
114 FROM sys.indexes AS i
115 INNER JOIN sys.index_columns AS ic
116 ON i.object_id = ic.object_id AND i.index_id = ic.index_id
117 WHERE i.is_primary_key = 1
118 AND i.object_id = OBJECT_ID('{0}');", m_Realm);
119 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
120 using (SqlCommand cmd = new SqlCommand(query, conn))
121 {
122 conn.Open();
123 using (SqlDataReader rdr = cmd.ExecuteReader())
124 {
125 while (rdr.Read())
126 {
127 // query produces 0 to many rows of single column, so always add the first item in each row
128 constraints.Add((string)rdr[0]);
129 }
130 }
131 return constraints;
132 }
133 }
134
135 public virtual T[] Get(string field, string key)
136 {
137 return Get(new string[] { field }, new string[] { key });
138 }
139
140 public virtual T[] Get(string[] fields, string[] keys)
141 {
142 if (fields.Length != keys.Length)
143 return new T[0];
144
145 List<string> terms = new List<string>();
146
147 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
148 using (SqlCommand cmd = new SqlCommand())
149 {
150
151 for (int i = 0; i < fields.Length; i++)
152 {
153 cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i]));
154 terms.Add("[" + fields[i] + "] = @" + fields[i]);
155 }
156
157 string where = String.Join(" AND ", terms.ToArray());
158
159 string query = String.Format("SELECT * FROM {0} WHERE {1}",
160 m_Realm, where);
161
162 cmd.Connection = conn;
163 cmd.CommandText = query;
164 conn.Open();
165 return DoQuery(cmd);
166 }
167 }
168
169 protected T[] DoQuery(SqlCommand cmd)
170 {
171 List<T> result = new List<T>();
172 using (SqlDataReader reader = cmd.ExecuteReader())
173 {
174 if (reader == null)
175 return new T[0];
176
177 CheckColumnNames(reader);
178
179 while (reader.Read())
180 {
181 T row = new T();
182
183 foreach (string name in m_Fields.Keys)
184 {
185 if (m_Fields[name].GetValue(row) is bool)
186 {
187 int v = Convert.ToInt32(reader[name]);
188 m_Fields[name].SetValue(row, v != 0 ? true : false);
189 }
190 else if (m_Fields[name].GetValue(row) is UUID)
191 {
192 UUID uuid = UUID.Zero;
193
194 UUID.TryParse(reader[name].ToString(), out uuid);
195 m_Fields[name].SetValue(row, uuid);
196 }
197 else if (m_Fields[name].GetValue(row) is int)
198 {
199 int v = Convert.ToInt32(reader[name]);
200 m_Fields[name].SetValue(row, v);
201 }
202 else
203 {
204 m_Fields[name].SetValue(row, reader[name]);
205 }
206 }
207
208 if (m_DataField != null)
209 {
210 Dictionary<string, string> data =
211 new Dictionary<string, string>();
212
213 foreach (string col in m_ColumnNames)
214 {
215 data[col] = reader[col].ToString();
216 if (data[col] == null)
217 data[col] = String.Empty;
218 }
219
220 m_DataField.SetValue(row, data);
221 }
222
223 result.Add(row);
224 }
225 return result.ToArray();
226 }
227 }
228
229 public virtual T[] Get(string where)
230 {
231 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
232 using (SqlCommand cmd = new SqlCommand())
233 {
234
235 string query = String.Format("SELECT * FROM {0} WHERE {1}",
236 m_Realm, where);
237 cmd.Connection = conn;
238 cmd.CommandText = query;
239
240 //m_log.WarnFormat("[MSSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where);
241
242 conn.Open();
243 return DoQuery(cmd);
244 }
245 }
246
247 public virtual bool Store(T row)
248 {
249 List<string> constraintFields = GetConstraints();
250 List<KeyValuePair<string, string>> constraints = new List<KeyValuePair<string, string>>();
251
252 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
253 using (SqlCommand cmd = new SqlCommand())
254 {
255
256 StringBuilder query = new StringBuilder();
257 List<String> names = new List<String>();
258 List<String> values = new List<String>();
259
260 foreach (FieldInfo fi in m_Fields.Values)
261 {
262 names.Add(fi.Name);
263 values.Add("@" + fi.Name);
264 // Temporarily return more information about what field is unexpectedly null for
265 // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the
266 // InventoryTransferModule or we may be required to substitute a DBNull here.
267 if (fi.GetValue(row) == null)
268 throw new NullReferenceException(
269 string.Format(
270 "[MSSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null",
271 fi.Name, row));
272
273 if (constraintFields.Count > 0 && constraintFields.Contains(fi.Name))
274 {
275 constraints.Add(new KeyValuePair<string, string>(fi.Name, fi.GetValue(row).ToString()));
276 }
277 cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row).ToString()));
278 }
279
280 if (m_DataField != null)
281 {
282 Dictionary<string, string> data =
283 (Dictionary<string, string>)m_DataField.GetValue(row);
284
285 foreach (KeyValuePair<string, string> kvp in data)
286 {
287 if (constraintFields.Count > 0 && constraintFields.Contains(kvp.Key))
288 {
289 constraints.Add(new KeyValuePair<string, string>(kvp.Key, kvp.Key));
290 }
291 names.Add(kvp.Key);
292 values.Add("@" + kvp.Key);
293 cmd.Parameters.Add(m_database.CreateParameter("@" + kvp.Key, kvp.Value));
294 }
295
296 }
297
298 query.AppendFormat("UPDATE {0} SET ", m_Realm);
299 int i = 0;
300 for (i = 0; i < names.Count - 1; i++)
301 {
302 query.AppendFormat("[{0}] = {1}, ", names[i], values[i]);
303 }
304 query.AppendFormat("[{0}] = {1} ", names[i], values[i]);
305 if (constraints.Count > 0)
306 {
307 List<string> terms = new List<string>();
308 for (int j = 0; j < constraints.Count; j++)
309 {
310 terms.Add(" [" + constraints[j].Key + "] = @" + constraints[j].Key);
311 }
312 string where = String.Join(" AND ", terms.ToArray());
313 query.AppendFormat(" WHERE {0} ", where);
314
315 }
316 cmd.Connection = conn;
317 cmd.CommandText = query.ToString();
318
319 conn.Open();
320 if (cmd.ExecuteNonQuery() > 0)
321 {
322 //m_log.WarnFormat("[MSSQLGenericTable]: Updating {0}", m_Realm);
323 return true;
324 }
325 else
326 {
327 // assume record has not yet been inserted
328
329 query = new StringBuilder();
330 query.AppendFormat("INSERT INTO {0} ([", m_Realm);
331 query.Append(String.Join("],[", names.ToArray()));
332 query.Append("]) values (" + String.Join(",", values.ToArray()) + ")");
333 cmd.Connection = conn;
334 cmd.CommandText = query.ToString();
335 //m_log.WarnFormat("[MSSQLGenericTable]: Inserting into {0}", m_Realm);
336 if (conn.State != ConnectionState.Open)
337 conn.Open();
338 if (cmd.ExecuteNonQuery() > 0)
339 return true;
340 }
341
342 return false;
343 }
344 }
345
346 public virtual bool Delete(string field, string key)
347 {
348 return Delete(new string[] { field }, new string[] { key });
349 }
350
351 public virtual bool Delete(string[] fields, string[] keys)
352 {
353 if (fields.Length != keys.Length)
354 return false;
355
356 List<string> terms = new List<string>();
357
358 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
359 using (SqlCommand cmd = new SqlCommand())
360 {
361 for (int i = 0; i < fields.Length; i++)
362 {
363 cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i]));
364 terms.Add("[" + fields[i] + "] = @" + fields[i]);
365 }
366
367 string where = String.Join(" AND ", terms.ToArray());
368
369 string query = String.Format("DELETE FROM {0} WHERE {1}", m_Realm, where);
370
371 cmd.Connection = conn;
372 cmd.CommandText = query;
373 conn.Open();
374
375 if (cmd.ExecuteNonQuery() > 0)
376 {
377 //m_log.Warn("[MSSQLGenericTable]: " + deleteCommand);
378 return true;
379 }
380 return false;
381 }
382 }
383 }
384}
generated by cgit v1.1 at 2024-12-14 10:30:10 +0000