aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.SQLite
diff options
context:
space:
mode:
authorSean Dague2007-08-24 15:42:53 +0000
committerSean Dague2007-08-24 15:42:53 +0000
commita70fac535f8dae7bc23213b26dd5bfdcc4887d13 (patch)
tree5f18dcce2152efad74e24fa8800a2c5975456895 /OpenSim/Framework/Data.SQLite
parentFixed a problem where some prims didn't show up when you crossed or teleporte... (diff)
downloadopensim-SC_OLD-a70fac535f8dae7bc23213b26dd5bfdcc4887d13.zip
opensim-SC_OLD-a70fac535f8dae7bc23213b26dd5bfdcc4887d13.tar.gz
opensim-SC_OLD-a70fac535f8dae7bc23213b26dd5bfdcc4887d13.tar.bz2
opensim-SC_OLD-a70fac535f8dae7bc23213b26dd5bfdcc4887d13.tar.xz
updated SQLite Inventory to newer model data definition,
and add automatic generating of the inventory table
Diffstat (limited to 'OpenSim/Framework/Data.SQLite')
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs1129
1 files changed, 644 insertions, 485 deletions
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
index fe494fb..b63619a 100644
--- a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
+++ b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs
@@ -1,485 +1,644 @@
1using System; 1using System;
2using System.Collections.Generic; 2using System.Collections.Generic;
3using System.Text; 3using System.Text;
4 4
5using OpenSim.Framework.Console; 5using OpenSim.Framework.Console;
6using OpenSim.Framework.Types; 6using OpenSim.Framework.Types;
7using OpenSim.Framework.Utilities; 7using OpenSim.Framework.Utilities;
8using libsecondlife; 8using libsecondlife;
9 9
10using System.Data; 10using System.Data;
11using System.Data.SqlTypes; 11using System.Data.SqlTypes;
12 12
13using Mono.Data.SqliteClient; 13using Mono.Data.SqliteClient;
14 14
15namespace OpenSim.Framework.Data.SQLite 15namespace OpenSim.Framework.Data.SQLite
16{ 16{
17 17
18 public class SQLiteInventoryStore : IInventoryData 18 public class SQLiteInventoryStore : IInventoryData
19 { 19 {
20 private const string invItemsSelect = "select * from inventoryitems"; 20 private const string invItemsSelect = "select * from inventoryitems";
21 private const string invFoldersSelect = "select * from inventoryfolders"; 21 private const string invFoldersSelect = "select * from inventoryfolders";
22 22
23 private DataSet ds; 23 private DataSet ds;
24 private SqliteDataAdapter invItemsDa; 24 private SqliteDataAdapter invItemsDa;
25 private SqliteDataAdapter invFoldersDa; 25 private SqliteDataAdapter invFoldersDa;
26 26
27 /// <summary> 27 /// <summary>
28 /// Initialises the interface 28 /// Initialises the interface
29 /// </summary> 29 /// </summary>
30 public void Initialise() 30 public void Initialise()
31 { 31 {
32 Initialise("inventoryStore.db", "inventoryDatabase"); 32 Initialise("inventoryStore.db", "inventoryDatabase");
33 } 33 }
34 34
35 public void Initialise(string dbfile, string dbname) 35 public void Initialise(string dbfile, string dbname)
36 { 36 {
37 string connectionString = "URI=file:" + dbfile + ",version=3"; 37 string connectionString = "URI=file:" + dbfile + ",version=3";
38 38
39 MainLog.Instance.Verbose("Inventory", "Sqlite - connecting: " + dbfile); 39 MainLog.Instance.Verbose("Inventory", "Sqlite - connecting: " + dbfile);
40 SqliteConnection conn = new SqliteConnection(connectionString); 40 SqliteConnection conn = new SqliteConnection(connectionString);
41 41
42 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn); 42 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
43 invItemsDa = new SqliteDataAdapter(itemsSelectCmd); 43 invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
44 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); 44 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
45 45
46 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); 46 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
47 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd); 47 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
48 48
49 ds = new DataSet(); 49 ds = new DataSet();
50 50
51 invItemsDa.Fill(ds, "inventoryitems"); 51 ds.Tables.Add(createInventoryFoldersTable());
52 invFoldersDa.Fill(ds, "inventoryfolders"); 52 invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
53 ds.AcceptChanges(); 53 setupFoldersCommands(invFoldersDa, conn);
54 54 MainLog.Instance.Verbose("DATASTORE", "Populated Intentory Folders Definitions");
55 DataTable itemsTable = ds.Tables["inventoryitems"]; 55
56 itemsTable.PrimaryKey = new DataColumn[] { itemsTable.Columns["UUID"] }; 56 ds.Tables.Add(createInventoryItemsTable());
57 setupItemsCommands(invItemsDa, conn); 57 invItemsDa.Fill(ds.Tables["inventoryitems"]);
58 58 setupItemsCommands(invItemsDa, conn);
59 // shapeDa.FillSchema(ds, SchemaType.Source, "ShapeSchema"); 59 MainLog.Instance.Verbose("DATASTORE", "Populated Intentory Items Definitions");
60 DataTable folderTable = ds.Tables["inventoryfolders"]; 60
61 folderTable.PrimaryKey = new DataColumn[] { folderTable.Columns["UUID"] }; 61 ds.AcceptChanges();
62 setupFoldersCommands(invFoldersDa, conn); 62 return;
63 return; 63 }
64 } 64
65 65 public InventoryItemBase BuildItem(DataRow row)
66 private SqliteParameter createSqliteParameter(string name, DbType type) 66 {
67 { 67 InventoryItemBase item = new InventoryItemBase();
68 SqliteParameter param = new SqliteParameter(); 68 item.inventoryID = new LLUUID((string)row["UUID"]);
69 param.ParameterName = ":" + name; 69 item.assetID = new LLUUID((string)row["assetID"]);
70 param.DbType = type; 70 item.assetType = Convert.ToInt32(row["assetType"]);
71 param.SourceColumn = name; 71 item.invType = Convert.ToInt32(row["invType"]);
72 param.SourceVersion = DataRowVersion.Current; 72 item.parentFolderID = new LLUUID((string)row["parentFolderID"]);
73 return param; 73 item.avatarID = new LLUUID((string)row["avatarID"]);
74 } 74 item.creatorsID = new LLUUID((string)row["creatorsID"]);
75 75 item.inventoryName =(string) row["inventoryName"];
76 private Dictionary<string, DbType> createInventoryItemsDataDefs() 76 item.inventoryDescription = (string) row["inventoryDescription"];
77 { 77
78 Dictionary<string, DbType> data = new Dictionary<string, DbType>(); 78 item.inventoryNextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]);
79 data.Add("UUID", DbType.String); //inventoryID 79 item.inventoryCurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]);
80 data.Add("assetID", DbType.String); 80 item.inventoryBasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]);
81 data.Add("assetType", DbType.Int32); 81 item.inventoryEveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]);
82 data.Add("invType", DbType.Int32); 82 return item;
83 data.Add("parentFolderID", DbType.String); 83 }
84 data.Add("avatarID", DbType.String); 84
85 data.Add("creatorsID", DbType.String); 85 private void fillItemRow(DataRow row, InventoryItemBase item)
86 86 {
87 data.Add("inventoryName", DbType.String); 87 row["UUID"] = item.inventoryID;
88 data.Add("inventoryDescription", DbType.String); 88 row["assetID"] = item.assetID;
89 // permissions 89 row["assetType"] = item.assetType;
90 data.Add("inventoryNextPermissions", DbType.Int32); 90 row["invType"] = item.invType;
91 data.Add("inventoryCurrentPermissions", DbType.Int32); 91 row["parentFolderID"] = item.parentFolderID;
92 data.Add("inventoryBasePermissions", DbType.Int32); 92 row["avatarID"] = item.avatarID;
93 data.Add("inventoryEveryOnePermissions", DbType.Int32); 93 row["creatorsID"] = item.creatorsID;
94 94 row["inventoryName"] = item.inventoryName;
95 return data; 95 row["inventoryDescription"] = item.inventoryDescription;
96 } 96
97 97 row["inventoryNextPermissions"] = item.inventoryNextPermissions;
98 private Dictionary<string, DbType> createShapeDataDefs() 98 row["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions;
99 { 99 row["inventoryBasePermissions"] = item.inventoryBasePermissions;
100 Dictionary<string, DbType> data = new Dictionary<string, DbType>(); 100 row["inventoryEveryOnePermissions"] = item.inventoryEveryOnePermissions;
101 data.Add("UUID", DbType.String); //folderID 101 }
102 // shape is an enum 102
103 data.Add("name", DbType.String); 103 private void addFolder(InventoryFolderBase folder)
104 // vectors 104 {
105 data.Add("agentID", DbType.String); 105 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
106 data.Add("parentID", DbType.String); 106
107 data.Add("type", DbType.Int32); 107 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.folderID);
108 data.Add("version", DbType.Int32); 108 if (inventoryRow == null)
109 return data; 109 {
110 } 110 inventoryRow = inventoryFolderTable.NewRow();
111 111 fillFolderRow(inventoryRow, folder);
112 private SqliteCommand createInsertCommand(string table, Dictionary<string, DbType> defs) 112 inventoryFolderTable.Rows.Add(inventoryRow);
113 { 113 }
114 /** 114 else
115 * This is subtle enough to deserve some commentary. 115 {
116 * Instead of doing *lots* and *lots of hardcoded strings 116 fillFolderRow(inventoryRow, folder);
117 * for database definitions we'll use the fact that 117 }
118 * realistically all insert statements look like "insert 118
119 * into A(b, c) values(:b, :c) on the parameterized query 119 this.invFoldersDa.Update(ds, "inventoryfolders");
120 * front. If we just have a list of b, c, etc... we can 120 }
121 * generate these strings instead of typing them out. 121
122 */ 122 private void addItem(InventoryItemBase item)
123 string[] cols = new string[defs.Keys.Count]; 123 {
124 defs.Keys.CopyTo(cols, 0); 124 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
125 125
126 string sql = "insert into " + table + "("; 126 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.inventoryID);
127 sql += String.Join(", ", cols); 127 if (inventoryRow == null)
128 // important, the first ':' needs to be here, the rest get added in the join 128 {
129 sql += ") values (:"; 129 inventoryRow = inventoryItemTable.NewRow();
130 sql += String.Join(", :", cols); 130 fillItemRow(inventoryRow, item);
131 sql += ")"; 131 inventoryItemTable.Rows.Add(inventoryRow);
132 SqliteCommand cmd = new SqliteCommand(sql); 132 }
133 133 else
134 // this provides the binding for all our parameters, so 134 {
135 // much less code than it used to be 135 fillItemRow(inventoryRow, item);
136 foreach (KeyValuePair<string, DbType> kvp in defs) 136 }
137 { 137 this.invItemsDa.Update(ds, "inventoryitems");
138 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); 138 }
139 } 139
140 return cmd; 140 public void Shutdown()
141 } 141 {
142 142 // TODO: DataSet commit
143 private SqliteCommand createUpdateCommand(string table, string pk, Dictionary<string, DbType> defs) 143 }
144 { 144
145 string sql = "update " + table + " set "; 145 /// <summary>
146 string subsql = ""; 146 /// Closes the interface
147 foreach (string key in defs.Keys) 147 /// </summary>
148 { 148 public void Close()
149 if (subsql.Length > 0) 149 {
150 { // a map function would rock so much here 150 }
151 subsql += ", "; 151
152 } 152 /// <summary>
153 subsql += key + "= :" + key; 153 /// The plugin being loaded
154 } 154 /// </summary>
155 sql += subsql; 155 /// <returns>A string containing the plugin name</returns>
156 sql += " where " + pk; 156 public string getName()
157 SqliteCommand cmd = new SqliteCommand(sql); 157 {
158 158 return "SQLite Inventory Data Interface";
159 // this provides the binding for all our parameters, so 159 }
160 // much less code than it used to be 160
161 foreach (KeyValuePair<string, DbType> kvp in defs) 161 /// <summary>
162 { 162 /// The plugins version
163 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); 163 /// </summary>
164 } 164 /// <returns>A string containing the plugin version</returns>
165 return cmd; 165 public string getVersion()
166 } 166 {
167 167 return "0.1";
168 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn) 168 }
169 { 169
170 Dictionary<string, DbType> invDataDefs = createInventoryItemsDataDefs(); 170 /// <summary>
171 171 /// Returns a list of inventory items contained within the specified folder
172 da.InsertCommand = createInsertCommand("inventoryitems", invDataDefs); 172 /// </summary>
173 da.InsertCommand.Connection = conn; 173 /// <param name="folderID">The UUID of the target folder</param>
174 174 /// <returns>A List of InventoryItemBase items</returns>
175 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", invDataDefs); 175 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
176 da.UpdateCommand.Connection = conn; 176 {
177 177 List<InventoryItemBase> retval = new List<InventoryItemBase>();
178 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID"); 178 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
179 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); 179 string selectExp = "parentFolderID = '" + folderID.ToString() + "'";
180 delete.Connection = conn; 180 DataRow[] rows = inventoryItemTable.Select(selectExp);
181 da.DeleteCommand = delete; 181 foreach (DataRow row in rows)
182 } 182 {
183 183 retval.Add(BuildItem(row));
184 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn) 184 }
185 { 185
186 Dictionary<string, DbType> shapeDataDefs = createShapeDataDefs(); 186 return retval;
187 187 }
188 da.InsertCommand = createInsertCommand("inventoryfolders", shapeDataDefs); 188
189 da.InsertCommand.Connection = conn; 189 /// <summary>
190 190 /// Returns a list of the root folders within a users inventory
191 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", shapeDataDefs); 191 /// </summary>
192 da.UpdateCommand.Connection = conn; 192 /// <param name="user">The user whos inventory is to be searched</param>
193 193 /// <returns>A list of folder objects</returns>
194 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID"); 194 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
195 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); 195 {
196 delete.Connection = conn; 196 return null;
197 da.DeleteCommand = delete; 197 }
198 } 198
199 199 /// <summary>
200 private InventoryFolderBase buildFolder(DataRow row) 200 /// Returns the users inventory root folder.
201 { 201 /// </summary>
202 InventoryFolderBase folder = new InventoryFolderBase(); 202 /// <param name="user">The UUID of the user who is having inventory being returned</param>
203 folder.folderID = new LLUUID((string)row["UUID"]); 203 /// <returns>Root inventory folder</returns>
204 folder.name = (string)row["name"]; 204 public InventoryFolderBase getUserRootFolder(LLUUID user)
205 folder.agentID = new LLUUID((string)row["agentID"]); 205 {
206 folder.parentID = new LLUUID((string)row["parentID"]); 206 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
207 folder.type = Convert.ToInt16(row["type"]); 207 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
208 folder.version = Convert.ToUInt16(row["version"]); 208 string selectExp = "agentID = '" + user.ToString() + "' AND parentID = '" + LLUUID.Zero.ToString() + "'";
209 return folder; 209 DataRow[] rows = inventoryFolderTable.Select(selectExp);
210 } 210 foreach (DataRow row in rows)
211 211 {
212 private void fillFolderRow(DataRow row, InventoryFolderBase folder) 212 folders.Add(this.buildFolder(row));
213 { 213 }
214 row["UUID"] = folder.folderID; 214
215 row["name"] = folder.name; 215 if (folders.Count == 1)
216 row["agentID"] = folder.agentID; 216 {
217 row["parentID"] = folder.parentID; 217 //we found the root
218 row["type"] = folder.type; 218 //System.Console.WriteLine("found root inventory folder");
219 row["version"] = folder.version; 219 return folders[0];
220 } 220 }
221 221 else if (folders.Count > 1)
222 public InventoryItemBase BuildItem(DataRow row) 222 {
223 { 223 //err shouldn't be more than one root
224 InventoryItemBase item = new InventoryItemBase(); 224 //System.Console.WriteLine("found more than one root inventory folder");
225 item.inventoryID = new LLUUID((string)row["UUID"]); 225 }
226 item.assetID = new LLUUID((string)row["assetID"]); 226 else if (folders.Count == 0)
227 item.assetType = Convert.ToInt32(row["assetType"]); 227 {
228 item.invType = Convert.ToInt32(row["invType"]); 228 // no root?
229 item.parentFolderID = new LLUUID((string)row["parentFolderID"]); 229 //System.Console.WriteLine("couldn't find root inventory folder");
230 item.avatarID = new LLUUID((string)row["avatarID"]); 230 }
231 item.creatorsID = new LLUUID((string)row["creatorsID"]); 231
232 item.inventoryName =(string) row["inventoryName"]; 232 return null;
233 item.inventoryDescription = (string) row["inventoryDescription"]; 233 }
234 234
235 item.inventoryNextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]); 235 /// <summary>
236 item.inventoryCurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]); 236 /// Returns a list of inventory folders contained in the folder 'parentID'
237 item.inventoryBasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]); 237 /// </summary>
238 item.inventoryEveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]); 238 /// <param name="parentID">The folder to get subfolders for</param>
239 return item; 239 /// <returns>A list of inventory folders</returns>
240 } 240 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
241 241 {
242 private void fillItemRow(DataRow row, InventoryItemBase item) 242 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
243 { 243 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
244 row["UUID"] = item.inventoryID; 244 string selectExp = "parentID = '" + parentID.ToString() + "'";
245 row["assetID"] = item.assetID; 245 DataRow[] rows = inventoryFolderTable.Select(selectExp);
246 row["assetType"] = item.assetType; 246 foreach (DataRow row in rows)
247 row["invType"] = item.invType; 247 {
248 row["parentFolderID"] = item.parentFolderID; 248 folders.Add(this.buildFolder(row));
249 row["avatarID"] = item.avatarID; 249 }
250 row["creatorsID"] = item.creatorsID; 250 // System.Console.WriteLine("found " + folders.Count + " inventory folders");
251 row["inventoryName"] = item.inventoryName; 251 return folders;
252 row["inventoryDescription"] = item.inventoryDescription; 252 }
253 253
254 row["inventoryNextPermissions"] = item.inventoryNextPermissions; 254 /// <summary>
255 row["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions; 255 /// Returns an inventory item by its UUID
256 row["inventoryBasePermissions"] = item.inventoryBasePermissions; 256 /// </summary>
257 row["inventoryEveryOnePermissions"] = item.inventoryEveryOnePermissions; 257 /// <param name="item">The UUID of the item to be returned</param>
258 } 258 /// <returns>A class containing item information</returns>
259 259 public InventoryItemBase getInventoryItem(LLUUID item)
260 private void addFolder(InventoryFolderBase folder) 260 {
261 { 261 return null;
262 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; 262 }
263 263
264 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.folderID); 264 /// <summary>
265 if (inventoryRow == null) 265 /// Returns a specified inventory folder by its UUID
266 { 266 /// </summary>
267 inventoryRow = inventoryFolderTable.NewRow(); 267 /// <param name="folder">The UUID of the folder to be returned</param>
268 fillFolderRow(inventoryRow, folder); 268 /// <returns>A class containing folder information</returns>
269 inventoryFolderTable.Rows.Add(inventoryRow); 269 public InventoryFolderBase getInventoryFolder(LLUUID folder)
270 } 270 {
271 else 271 return null;
272 { 272 }
273 fillFolderRow(inventoryRow, folder); 273
274 } 274 /// <summary>
275 275 /// Creates a new inventory item based on item
276 this.invFoldersDa.Update(ds, "inventoryfolders"); 276 /// </summary>
277 } 277 /// <param name="item">The item to be created</param>
278 278 public void addInventoryItem(InventoryItemBase item)
279 private void addItem(InventoryItemBase item) 279 {
280 { 280 this.addItem(item);
281 DataTable inventoryItemTable = ds.Tables["inventoryitems"]; 281 }
282 282
283 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.inventoryID); 283 /// <summary>
284 if (inventoryRow == null) 284 /// Updates an inventory item with item (updates based on ID)
285 { 285 /// </summary>
286 inventoryRow = inventoryItemTable.NewRow(); 286 /// <param name="item">The updated item</param>
287 fillItemRow(inventoryRow, item); 287 public void updateInventoryItem(InventoryItemBase item)
288 inventoryItemTable.Rows.Add(inventoryRow); 288 {
289 } 289 this.addItem(item);
290 else 290 }
291 { 291
292 fillItemRow(inventoryRow, item); 292 /// <summary>
293 } 293 ///
294 this.invItemsDa.Update(ds, "inventoryitems"); 294 /// </summary>
295 } 295 /// <param name="item"></param>
296 296 public void deleteInventoryItem(InventoryItemBase item)
297 public void Shutdown() 297 {
298 { 298 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
299 // TODO: DataSet commit 299
300 } 300 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.inventoryID);
301 301 if (inventoryRow != null)
302 /// <summary> 302 {
303 /// Closes the interface 303 inventoryRow.Delete();
304 /// </summary> 304 }
305 public void Close() 305
306 { 306 this.invItemsDa.Update(ds, "inventoryitems");
307 } 307 }
308 308
309 /// <summary> 309 /// <summary>
310 /// The plugin being loaded 310 /// Adds a new folder specified by folder
311 /// </summary> 311 /// </summary>
312 /// <returns>A string containing the plugin name</returns> 312 /// <param name="folder">The inventory folder</param>
313 public string getName() 313 public void addInventoryFolder(InventoryFolderBase folder)
314 { 314 {
315 return "SQLite Inventory Data Interface"; 315 this.addFolder(folder);
316 } 316 }
317 317
318 /// <summary> 318 /// <summary>
319 /// The plugins version 319 /// Updates a folder based on its ID with folder
320 /// </summary> 320 /// </summary>
321 /// <returns>A string containing the plugin version</returns> 321 /// <param name="folder">The inventory folder</param>
322 public string getVersion() 322 public void updateInventoryFolder(InventoryFolderBase folder)
323 { 323 {
324 return "0.1"; 324 this.addFolder(folder);
325 } 325 }
326 326
327 /// <summary> 327
328 /// Returns a list of inventory items contained within the specified folder 328 /***********************************************************************
329 /// </summary> 329 *
330 /// <param name="folderID">The UUID of the target folder</param> 330 * Data Table definitions
331 /// <returns>A List of InventoryItemBase items</returns> 331 *
332 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID) 332 **********************************************************************/
333 { 333
334 List<InventoryItemBase> retval = new List<InventoryItemBase>(); 334 private void createCol(DataTable dt, string name, System.Type type)
335 DataTable inventoryItemTable = ds.Tables["inventoryitems"]; 335 {
336 string selectExp = "parentFolderID = '" + folderID.ToString() + "'"; 336 DataColumn col = new DataColumn(name, type);
337 DataRow[] rows = inventoryItemTable.Select(selectExp); 337 dt.Columns.Add(col);
338 foreach (DataRow row in rows) 338 }
339 { 339
340 retval.Add(BuildItem(row)); 340 private DataTable createInventoryItemsTable()
341 } 341 {
342 342 DataTable inv = new DataTable("inventoryitems");
343 return retval; 343
344 } 344 createCol(inv, "UUID", typeof(System.String)); //inventoryID
345 345 createCol(inv, "assetID", typeof(System.String));
346 /// <summary> 346 createCol(inv, "assetType", typeof(System.Int32));
347 /// Returns a list of the root folders within a users inventory 347 createCol(inv, "invType", typeof(System.Int32));
348 /// </summary> 348 createCol(inv, "parentFolderID", typeof(System.String));
349 /// <param name="user">The user whos inventory is to be searched</param> 349 createCol(inv, "avatarID", typeof(System.String));
350 /// <returns>A list of folder objects</returns> 350 createCol(inv, "creatorsID", typeof(System.String));
351 public List<InventoryFolderBase> getUserRootFolders(LLUUID user) 351
352 { 352 createCol(inv, "inventoryName", typeof(System.String));
353 return null; 353 createCol(inv, "inventoryDescription", typeof(System.String));
354 } 354 // permissions
355 355 createCol(inv, "inventoryNextPermissions", typeof(System.Int32));
356 /// <summary> 356 createCol(inv, "inventoryCurrentPermissions", typeof(System.Int32));
357 /// Returns the users inventory root folder. 357 createCol(inv, "inventoryBasePermissions", typeof(System.Int32));
358 /// </summary> 358 createCol(inv, "inventoryEveryOnePermissions", typeof(System.Int32));
359 /// <param name="user">The UUID of the user who is having inventory being returned</param> 359
360 /// <returns>Root inventory folder</returns> 360 inv.PrimaryKey = new DataColumn[] { inv.Columns["UUID"] };
361 public InventoryFolderBase getUserRootFolder(LLUUID user) 361 return inv;
362 { 362 }
363 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 363
364 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; 364 private DataTable createInventoryFoldersTable()
365 string selectExp = "agentID = '" + user.ToString() + "' AND parentID = '" + LLUUID.Zero.ToString() + "'"; 365 {
366 DataRow[] rows = inventoryFolderTable.Select(selectExp); 366 DataTable fol = new DataTable("inventoryfolders");
367 foreach (DataRow row in rows) 367
368 { 368 createCol(fol, "UUID", typeof(System.String)); //folderID
369 folders.Add(this.buildFolder(row)); 369 createCol(fol, "name", typeof(System.String));
370 } 370 createCol(fol, "agentID", typeof(System.String));
371 371 createCol(fol, "parentID", typeof(System.String));
372 if (folders.Count == 1) 372 createCol(fol, "type", typeof(System.Int32));
373 { 373 createCol(fol, "version", typeof(System.Int32));
374 //we found the root 374
375 //System.Console.WriteLine("found root inventory folder"); 375 fol.PrimaryKey = new DataColumn[] { fol.Columns["UUID"] };
376 return folders[0]; 376 return fol;
377 } 377 }
378 else if (folders.Count > 1) 378
379 { 379 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
380 //err shouldn't be more than one root 380 {
381 //System.Console.WriteLine("found more than one root inventory folder"); 381 da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]);
382 } 382 da.InsertCommand.Connection = conn;
383 else if (folders.Count == 0) 383
384 { 384 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]);
385 // no root? 385 da.UpdateCommand.Connection = conn;
386 //System.Console.WriteLine("couldn't find root inventory folder"); 386
387 } 387 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
388 388 delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String)));
389 return null; 389 delete.Connection = conn;
390 } 390 da.DeleteCommand = delete;
391 391 }
392 /// <summary> 392
393 /// Returns a list of inventory folders contained in the folder 'parentID' 393 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
394 /// </summary> 394 {
395 /// <param name="parentID">The folder to get subfolders for</param> 395 da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
396 /// <returns>A list of inventory folders</returns> 396 da.InsertCommand.Connection = conn;
397 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID) 397
398 { 398 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
399 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 399 da.UpdateCommand.Connection = conn;
400 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; 400
401 string selectExp = "parentID = '" + parentID.ToString() + "'"; 401 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
402 DataRow[] rows = inventoryFolderTable.Select(selectExp); 402 delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String)));
403 foreach (DataRow row in rows) 403 delete.Connection = conn;
404 { 404 da.DeleteCommand = delete;
405 folders.Add(this.buildFolder(row)); 405 }
406 } 406
407 // System.Console.WriteLine("found " + folders.Count + " inventory folders"); 407 private InventoryFolderBase buildFolder(DataRow row)
408 return folders; 408 {
409 } 409 InventoryFolderBase folder = new InventoryFolderBase();
410 410 folder.folderID = new LLUUID((string)row["UUID"]);
411 /// <summary> 411 folder.name = (string)row["name"];
412 /// Returns an inventory item by its UUID 412 folder.agentID = new LLUUID((string)row["agentID"]);
413 /// </summary> 413 folder.parentID = new LLUUID((string)row["parentID"]);
414 /// <param name="item">The UUID of the item to be returned</param> 414 folder.type = Convert.ToInt16(row["type"]);
415 /// <returns>A class containing item information</returns> 415 folder.version = Convert.ToUInt16(row["version"]);
416 public InventoryItemBase getInventoryItem(LLUUID item) 416 return folder;
417 { 417 }
418 return null; 418
419 } 419 private void fillFolderRow(DataRow row, InventoryFolderBase folder)
420 420 {
421 /// <summary> 421 row["UUID"] = folder.folderID;
422 /// Returns a specified inventory folder by its UUID 422 row["name"] = folder.name;
423 /// </summary> 423 row["agentID"] = folder.agentID;
424 /// <param name="folder">The UUID of the folder to be returned</param> 424 row["parentID"] = folder.parentID;
425 /// <returns>A class containing folder information</returns> 425 row["type"] = folder.type;
426 public InventoryFolderBase getInventoryFolder(LLUUID folder) 426 row["version"] = folder.version;
427 { 427 }
428 return null; 428
429 } 429
430 430 /***********************************************************************
431 /// <summary> 431 *
432 /// Creates a new inventory item based on item 432 * SQL Statement Creation Functions
433 /// </summary> 433 *
434 /// <param name="item">The item to be created</param> 434 * These functions create SQL statements for update, insert, and create.
435 public void addInventoryItem(InventoryItemBase item) 435 * They can probably be factored later to have a db independant
436 { 436 * portion and a db specific portion
437 this.addItem(item); 437 *
438 } 438 **********************************************************************/
439 439
440 /// <summary> 440 private SqliteCommand createInsertCommand(string table, DataTable dt)
441 /// Updates an inventory item with item (updates based on ID) 441 {
442 /// </summary> 442 /**
443 /// <param name="item">The updated item</param> 443 * This is subtle enough to deserve some commentary.
444 public void updateInventoryItem(InventoryItemBase item) 444 * Instead of doing *lots* and *lots of hardcoded strings
445 { 445 * for database definitions we'll use the fact that
446 this.addItem(item); 446 * realistically all insert statements look like "insert
447 } 447 * into A(b, c) values(:b, :c) on the parameterized query
448 448 * front. If we just have a list of b, c, etc... we can
449 /// <summary> 449 * generate these strings instead of typing them out.
450 /// 450 */
451 /// </summary> 451 string[] cols = new string[dt.Columns.Count];
452 /// <param name="item"></param> 452 for (int i = 0; i < dt.Columns.Count; i++) {
453 public void deleteInventoryItem(InventoryItemBase item) 453 DataColumn col = dt.Columns[i];
454 { 454 cols[i] = col.ColumnName;
455 DataTable inventoryItemTable = ds.Tables["inventoryitems"]; 455 }
456 456
457 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.inventoryID); 457 string sql = "insert into " + table + "(";
458 if (inventoryRow != null) 458 sql += String.Join(", ", cols);
459 { 459 // important, the first ':' needs to be here, the rest get added in the join
460 inventoryRow.Delete(); 460 sql += ") values (:";
461 } 461 sql += String.Join(", :", cols);
462 462 sql += ")";
463 this.invItemsDa.Update(ds, "inventoryitems"); 463 SqliteCommand cmd = new SqliteCommand(sql);
464 } 464
465 465 // this provides the binding for all our parameters, so
466 /// <summary> 466 // much less code than it used to be
467 /// Adds a new folder specified by folder 467 foreach (DataColumn col in dt.Columns)
468 /// </summary> 468 {
469 /// <param name="folder">The inventory folder</param> 469 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
470 public void addInventoryFolder(InventoryFolderBase folder) 470 }
471 { 471 return cmd;
472 this.addFolder(folder); 472 }
473 } 473
474 474 private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
475 /// <summary> 475 {
476 /// Updates a folder based on its ID with folder 476 string sql = "update " + table + " set ";
477 /// </summary> 477 string subsql = "";
478 /// <param name="folder">The inventory folder</param> 478 foreach (DataColumn col in dt.Columns)
479 public void updateInventoryFolder(InventoryFolderBase folder) 479 {
480 { 480 if (subsql.Length > 0)
481 this.addFolder(folder); 481 { // a map function would rock so much here
482 } 482 subsql += ", ";
483 } 483 }
484} 484 subsql += col.ColumnName + "= :" + col.ColumnName;
485 485 }
486 sql += subsql;
487 sql += " where " + pk;
488 SqliteCommand cmd = new SqliteCommand(sql);
489
490 // this provides the binding for all our parameters, so
491 // much less code than it used to be
492
493 foreach (DataColumn col in dt.Columns)
494 {
495 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
496 }
497 return cmd;
498 }
499
500
501 private string defineTable(DataTable dt)
502 {
503 string sql = "create table " + dt.TableName + "(";
504 string subsql = "";
505 foreach (DataColumn col in dt.Columns)
506 {
507 if (subsql.Length > 0)
508 { // a map function would rock so much here
509 subsql += ",\n";
510 }
511 subsql += col.ColumnName + " " + sqliteType(col.DataType);
512 if(col == dt.PrimaryKey[0])
513 {
514 subsql += " primary key";
515 }
516 }
517 sql += subsql;
518 sql += ")";
519 return sql;
520 }
521
522 /***********************************************************************
523 *
524 * Database Binding functions
525 *
526 * These will be db specific due to typing, and minor differences
527 * in databases.
528 *
529 **********************************************************************/
530
531 ///<summary>
532 /// This is a convenience function that collapses 5 repetitive
533 /// lines for defining SqliteParameters to 2 parameters:
534 /// column name and database type.
535 ///
536 /// It assumes certain conventions like :param as the param
537 /// name to replace in parametrized queries, and that source
538 /// version is always current version, both of which are fine
539 /// for us.
540 ///</summary>
541 ///<returns>a built sqlite parameter</returns>
542 private SqliteParameter createSqliteParameter(string name, System.Type type)
543 {
544 SqliteParameter param = new SqliteParameter();
545 param.ParameterName = ":" + name;
546 param.DbType = dbtypeFromType(type);
547 param.SourceColumn = name;
548 param.SourceVersion = DataRowVersion.Current;
549 return param;
550 }
551
552 /***********************************************************************
553 *
554 * Test and Initialization code
555 *
556 **********************************************************************/
557 private void InitDB(SqliteConnection conn)
558 {
559 string createInventoryItems = defineTable(createInventoryItemsTable());
560 string createInventoryFolders = defineTable(createInventoryFoldersTable());
561
562 SqliteCommand pcmd = new SqliteCommand(createInventoryItems, conn);
563 SqliteCommand scmd = new SqliteCommand(createInventoryFolders, conn);
564 conn.Open();
565 pcmd.ExecuteNonQuery();
566 scmd.ExecuteNonQuery();
567 conn.Close();
568 }
569
570 private bool TestTables(SqliteConnection conn)
571 {
572 SqliteCommand invItemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
573 SqliteDataAdapter pDa = new SqliteDataAdapter(invItemsSelectCmd);
574 SqliteCommand invFoldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
575 SqliteDataAdapter sDa = new SqliteDataAdapter(invFoldersSelectCmd);
576
577 DataSet tmpDS = new DataSet();
578 try {
579 pDa.Fill(tmpDS, "inventoryitems");
580 sDa.Fill(tmpDS, "inventoryfolders");
581 } catch (Mono.Data.SqliteClient.SqliteSyntaxException) {
582 MainLog.Instance.Verbose("DATASTORE", "SQLite Database doesn't exist... creating");
583 InitDB(conn);
584 }
585
586 pDa.Fill(tmpDS, "inventoryitems");
587 sDa.Fill(tmpDS, "inventoryfolders");
588
589 foreach (DataColumn col in createInventoryItemsTable().Columns) {
590 if (! tmpDS.Tables["inventoryitems"].Columns.Contains(col.ColumnName) ) {
591 MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName);
592 return false;
593 }
594 }
595 foreach (DataColumn col in createInventoryFoldersTable().Columns) {
596 if (! tmpDS.Tables["inventoryfolders"].Columns.Contains(col.ColumnName) ) {
597 MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName);
598 return false;
599 }
600 }
601 return true;
602 }
603
604
605 /***********************************************************************
606 *
607 * Type conversion functions
608 *
609 **********************************************************************/
610
611 private DbType dbtypeFromType(Type type)
612 {
613 if (type == typeof(System.String)) {
614 return DbType.String;
615 } else if (type == typeof(System.Int32)) {
616 return DbType.Int32;
617 } else if (type == typeof(System.Double)) {
618 return DbType.Double;
619 } else if (type == typeof(System.Byte[])) {
620 return DbType.Binary;
621 } else {
622 return DbType.String;
623 }
624 }
625
626 // this is something we'll need to implement for each db
627 // slightly differently.
628 private string sqliteType(Type type)
629 {
630 if (type == typeof(System.String)) {
631 return "varchar(255)";
632 } else if (type == typeof(System.Int32)) {
633 return "integer";
634 } else if (type == typeof(System.Double)) {
635 return "float";
636 } else if (type == typeof(System.Byte[])) {
637 return "blob";
638 } else {
639 return "string";
640 }
641 }
642 }
643}
644
generated by cgit v1.1 at 2024-07-05 20:49:57 +0000