aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs401
1 files changed, 401 insertions, 0 deletions
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
new file mode 100644
index 0000000..811a355
--- /dev/null
+++ b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
@@ -0,0 +1,401 @@
1using System;
2using System.Collections.Generic;
3using System.Text;
4
5using OpenSim.Framework.Console;
6using OpenSim.Framework.Types;
7using OpenSim.Framework.Utilities;
8using libsecondlife;
9
10using System.Data;
11using System.Data.SqlTypes;
12
13using Mono.Data.SqliteClient;
14
15namespace OpenSim.Framework.Data.SQLite
16{
17
18 public class SQLiteInventoryStore : IInventoryData
19 {
20 private const string invItemsSelect = "select * from inventoryitems";
21 private const string invFoldersSelect = "select * from inventoryfolders";
22
23 private DataSet ds;
24 private SqliteDataAdapter invItemsDa;
25 private SqliteDataAdapter invFoldersDa;
26
27 /// <summary>
28 /// Initialises the interface
29 /// </summary>
30 public void Initialise()
31 {
32 Initialise("inventoryStore.db", "inventoryDatabase");
33 }
34
35 public void Initialise(string dbfile, string dbname)
36 {
37 string connectionString = "URI=file:" + dbfile + ",version=3";
38
39 MainLog.Instance.Verbose("Inventory", "Sqlite - connecting: " + dbfile);
40 SqliteConnection conn = new SqliteConnection(connectionString);
41
42 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
43 invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
44 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
45
46 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
47 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
48
49 ds = new DataSet();
50
51 invItemsDa.Fill(ds, "inventoryitems");
52 invFoldersDa.Fill(ds, "inventoryfolders");
53 ds.AcceptChanges();
54
55 DataTable itemsTable = ds.Tables["inventoryitems"];
56 itemsTable.PrimaryKey = new DataColumn[] { itemsTable.Columns["UUID"] };
57 setupItemsCommands(invItemsDa, conn);
58
59 // shapeDa.FillSchema(ds, SchemaType.Source, "ShapeSchema");
60 DataTable folderTable = ds.Tables["inventoryfolders"];
61 folderTable.PrimaryKey = new DataColumn[] { folderTable.Columns["UUID"] };
62 setupFoldersCommands(invFoldersDa, conn);
63 return;
64 }
65
66 private SqliteParameter createSqliteParameter(string name, DbType type)
67 {
68 SqliteParameter param = new SqliteParameter();
69 param.ParameterName = ":" + name;
70 param.DbType = type;
71 param.SourceColumn = name;
72 param.SourceVersion = DataRowVersion.Current;
73 return param;
74 }
75
76 private Dictionary<string, DbType> createInventoryItemsDataDefs()
77 {
78 Dictionary<string, DbType> data = new Dictionary<string, DbType>();
79 data.Add("UUID", DbType.String); //inventoryID
80 data.Add("assetID", DbType.String);
81 data.Add("assetType", DbType.Int32);
82 data.Add("invType", DbType.Int32);
83 data.Add("parentFolderID", DbType.String);
84 data.Add("avatarID", DbType.String);
85 data.Add("creatorsID", DbType.String);
86
87 data.Add("inventoryName", DbType.String);
88 data.Add("inventoryDescription", DbType.String);
89 // permissions
90 data.Add("inventoryNextPermissions", DbType.Int32);
91 data.Add("inventoryCurrentPermissions", DbType.Int32);
92 data.Add("inventoryBasePermissions", DbType.Int32);
93 data.Add("inventoryEveryOnePermissions", DbType.Int32);
94
95 return data;
96 }
97
98 private Dictionary<string, DbType> createShapeDataDefs()
99 {
100 Dictionary<string, DbType> data = new Dictionary<string, DbType>();
101 data.Add("UUID", DbType.String); //folderID
102 // shape is an enum
103 data.Add("name", DbType.String);
104 // vectors
105 data.Add("agentID", DbType.String);
106 data.Add("parentID", DbType.String);
107 data.Add("type", DbType.Int32);
108 data.Add("version", DbType.Int32);
109 return data;
110 }
111
112 private SqliteCommand createInsertCommand(string table, Dictionary<string, DbType> defs)
113 {
114 /**
115 * This is subtle enough to deserve some commentary.
116 * Instead of doing *lots* and *lots of hardcoded strings
117 * for database definitions we'll use the fact that
118 * realistically all insert statements look like "insert
119 * into A(b, c) values(:b, :c) on the parameterized query
120 * front. If we just have a list of b, c, etc... we can
121 * generate these strings instead of typing them out.
122 */
123 string[] cols = new string[defs.Keys.Count];
124 defs.Keys.CopyTo(cols, 0);
125
126 string sql = "insert into " + table + "(";
127 sql += String.Join(", ", cols);
128 // important, the first ':' needs to be here, the rest get added in the join
129 sql += ") values (:";
130 sql += String.Join(", :", cols);
131 sql += ")";
132 SqliteCommand cmd = new SqliteCommand(sql);
133
134 // this provides the binding for all our parameters, so
135 // much less code than it used to be
136 foreach (KeyValuePair<string, DbType> kvp in defs)
137 {
138 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value));
139 }
140 return cmd;
141 }
142
143 private SqliteCommand createUpdateCommand(string table, string pk, Dictionary<string, DbType> defs)
144 {
145 string sql = "update " + table + " set ";
146 string subsql = "";
147 foreach (string key in defs.Keys)
148 {
149 if (subsql.Length > 0)
150 { // a map function would rock so much here
151 subsql += ", ";
152 }
153 subsql += key + "= :" + key;
154 }
155 sql += subsql;
156 sql += " where " + pk;
157 SqliteCommand cmd = new SqliteCommand(sql);
158
159 // this provides the binding for all our parameters, so
160 // much less code than it used to be
161 foreach (KeyValuePair<string, DbType> kvp in defs)
162 {
163 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value));
164 }
165 return cmd;
166 }
167
168 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
169 {
170 Dictionary<string, DbType> invDataDefs = createInventoryItemsDataDefs();
171
172 da.InsertCommand = createInsertCommand("inventoryitems", invDataDefs);
173 da.InsertCommand.Connection = conn;
174
175 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", invDataDefs);
176 da.UpdateCommand.Connection = conn;
177
178 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
179 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String));
180 delete.Connection = conn;
181 da.DeleteCommand = delete;
182 }
183
184 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
185 {
186 Dictionary<string, DbType> shapeDataDefs = createShapeDataDefs();
187
188 da.InsertCommand = createInsertCommand("inventoryfolders", shapeDataDefs);
189 da.InsertCommand.Connection = conn;
190
191 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", shapeDataDefs);
192 da.UpdateCommand.Connection = conn;
193
194 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
195 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String));
196 delete.Connection = conn;
197 da.DeleteCommand = delete;
198 }
199
200 private InventoryFolderBase buildFolder(DataRow row)
201 {
202 InventoryFolderBase folder = new InventoryFolderBase();
203 folder.folderID = new LLUUID((string)row["UUID"]);
204 folder.name = (string)row["name"];
205 folder.agentID = new LLUUID((string)row["agentID"]);
206 folder.parentID = new LLUUID((string)row["parentID"]);
207 folder.type = Convert.ToInt16(row["type"]);
208 folder.version = Convert.ToUInt16(row["version"]);
209 return folder;
210 }
211
212 private void fillFolderRow(DataRow row, InventoryFolderBase folder)
213 {
214 row["UUID"] = folder.folderID;
215 row["name"] = folder.name;
216 row["agentID"] = folder.agentID;
217 row["parentID"] = folder.parentID;
218 row["type"] = folder.type;
219 row["version"] = folder.version;
220 }
221
222 private void addFolder(InventoryFolderBase folder)
223 {
224 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
225
226 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.folderID);
227 if (inventoryRow == null)
228 {
229 inventoryRow = inventoryFolderTable.NewRow();
230 fillFolderRow(inventoryRow, folder);
231 inventoryFolderTable.Rows.Add(inventoryRow);
232 }
233 else
234 {
235 fillFolderRow(inventoryRow, folder);
236 }
237
238 this.invFoldersDa.Update(ds, "inventoryfolders");
239 }
240
241 public void Shutdown()
242 {
243 // TODO: DataSet commit
244 }
245
246 /// <summary>
247 /// Closes the interface
248 /// </summary>
249 public void Close()
250 {
251 }
252
253 /// <summary>
254 /// The plugin being loaded
255 /// </summary>
256 /// <returns>A string containing the plugin name</returns>
257 public string getName()
258 {
259 return "SQLite Inventory Data Interface";
260 }
261
262 /// <summary>
263 /// The plugins version
264 /// </summary>
265 /// <returns>A string containing the plugin version</returns>
266 public string getVersion()
267 {
268 return "0.1";
269 }
270
271 /// <summary>
272 /// Returns a list of inventory items contained within the specified folder
273 /// </summary>
274 /// <param name="folderID">The UUID of the target folder</param>
275 /// <returns>A List of InventoryItemBase items</returns>
276 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
277 {
278 return null;
279 }
280
281 /// <summary>
282 /// Returns a list of the root folders within a users inventory
283 /// </summary>
284 /// <param name="user">The user whos inventory is to be searched</param>
285 /// <returns>A list of folder objects</returns>
286 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
287 {
288 return null;
289 }
290
291 /// <summary>
292 /// Returns the users inventory root folder.
293 /// </summary>
294 /// <param name="user">The UUID of the user who is having inventory being returned</param>
295 /// <returns>Root inventory folder</returns>
296 public InventoryFolderBase getUserRootFolder(LLUUID user)
297 {
298 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
299 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
300 string selectExp = "agentID = '"+ user.ToString()+"' AND parentID = '"+ LLUUID.Zero.ToString()+"'";
301 DataRow[] rows = inventoryFolderTable.Select(selectExp);
302 foreach (DataRow row in rows)
303 {
304 folders.Add(this.buildFolder(row));
305 }
306
307 if (folders.Count == 1)
308 {
309 //we found the root
310 //System.Console.WriteLine("found root inventory folder");
311 return folders[0];
312 }
313 else if (folders.Count > 1)
314 {
315 //err shouldn't be more than one root
316 //System.Console.WriteLine("found more than one root inventory folder");
317 }
318 else if (folders.Count == 0)
319 {
320 // no root?
321 //System.Console.WriteLine("couldn't find root inventory folder");
322 }
323
324 return null;
325 }
326
327 /// <summary>
328 /// Returns a list of inventory folders contained in the folder 'parentID'
329 /// </summary>
330 /// <param name="parentID">The folder to get subfolders for</param>
331 /// <returns>A list of inventory folders</returns>
332 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
333 {
334 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
335 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
336 string selectExp = "parentID = '" + parentID.ToString() + "'";
337 DataRow[] rows = inventoryFolderTable.Select(selectExp);
338 foreach (DataRow row in rows)
339 {
340 folders.Add(this.buildFolder(row));
341 }
342 // System.Console.WriteLine("found " + folders.Count + " inventory folders");
343 return folders;
344 }
345
346 /// <summary>
347 /// Returns an inventory item by its UUID
348 /// </summary>
349 /// <param name="item">The UUID of the item to be returned</param>
350 /// <returns>A class containing item information</returns>
351 public InventoryItemBase getInventoryItem(LLUUID item)
352 {
353 return null;
354 }
355
356 /// <summary>
357 /// Returns a specified inventory folder by its UUID
358 /// </summary>
359 /// <param name="folder">The UUID of the folder to be returned</param>
360 /// <returns>A class containing folder information</returns>
361 public InventoryFolderBase getInventoryFolder(LLUUID folder)
362 {
363 return null;
364 }
365
366 /// <summary>
367 /// Creates a new inventory item based on item
368 /// </summary>
369 /// <param name="item">The item to be created</param>
370 public void addInventoryItem(InventoryItemBase item)
371 {
372 }
373
374 /// <summary>
375 /// Updates an inventory item with item (updates based on ID)
376 /// </summary>
377 /// <param name="item">The updated item</param>
378 public void updateInventoryItem(InventoryItemBase item)
379 {
380 }
381
382 /// <summary>
383 /// Adds a new folder specified by folder
384 /// </summary>
385 /// <param name="folder">The inventory folder</param>
386 public void addInventoryFolder(InventoryFolderBase folder)
387 {
388 this.addFolder(folder);
389 }
390
391 /// <summary>
392 /// Updates a folder based on its ID with folder
393 /// </summary>
394 /// <param name="folder">The inventory folder</param>
395 public void updateInventoryFolder(InventoryFolderBase folder)
396 {
397 this.addFolder(folder);
398 }
399 }
400}
401