aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.SQLite
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Framework/Data.SQLite')
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteUserData.cs705
1 files changed, 705 insertions, 0 deletions
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs
new file mode 100644
index 0000000..84e58fa
--- /dev/null
+++ b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs
@@ -0,0 +1,705 @@
1/*
2* Copyright (c) Contributors, http://www.openmetaverse.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 OpenSim 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.IO;
30using libsecondlife;
31using OpenSim.Framework.Utilities;
32using System.Data;
33using System.Data.SqlTypes;
34using Mono.Data.SqliteClient;
35using OpenSim.Framework.Console;
36
37namespace OpenSim.Framework.Data.SQLite
38{
39 /// <summary>
40 /// A User storage interface for the DB4o database system
41 /// </summary>
42 public class SQLiteUserData : IUserData
43 {
44 /// <summary>
45 /// The database manager
46 /// </summary>
47
48 /// <summary>
49 /// Artificial constructor called upon plugin load
50 /// </summary>
51 private const string userSelect = "select * from users";
52 private DataSet ds;
53 private SqliteDataAdapter da;
54
55 public void Initialise()
56 {
57 SqliteConnection conn = new SqliteConnection("URI=file:userprofiles.db,version=3");
58 TestTables(conn);
59
60 ds = new DataSet();
61 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
62
63 ds.Tables.Add(createUsersTable());
64 ds.Tables.Add(createUserAgentsTable());
65
66 setupUserCommands(da, conn);
67 da.Fill(ds.Tables["users"]);
68
69 return;
70 }
71
72 /// <summary>
73 /// Loads a specified user profile from a UUID
74 /// </summary>
75 /// <param name="uuid">The users UUID</param>
76 /// <returns>A user profile</returns>
77 public UserProfileData getUserByUUID(LLUUID uuid)
78 {
79 DataRow row = ds.Tables["users"].Rows.Find(uuid);
80 if(row != null) {
81 return buildUserProfile(row);
82 } else {
83 return null;
84 }
85 }
86
87 /// <summary>
88 /// Returns a user by searching for its name
89 /// </summary>
90 /// <param name="name">The users account name</param>
91 /// <returns>A matching users profile</returns>
92 public UserProfileData getUserByName(string name)
93 {
94 return getUserByName(name.Split(' ')[0], name.Split(' ')[1]);
95 }
96
97 /// <summary>
98 /// Returns a user by searching for its name
99 /// </summary>
100 /// <param name="fname">The first part of the users account name</param>
101 /// <param name="lname">The second part of the users account name</param>
102 /// <returns>A matching users profile</returns>
103 public UserProfileData getUserByName(string fname, string lname)
104 {
105 string select = "surname = '" + lname + "' and username = '" + fname + "'";
106 DataRow[] rows = ds.Tables["users"].Select(select);
107 if(rows.Length > 0) {
108 return buildUserProfile(rows[0]);
109 } else {
110 return null;
111 }
112 }
113
114 /// <summary>
115 /// Returns a user by UUID direct
116 /// </summary>
117 /// <param name="uuid">The users account ID</param>
118 /// <returns>A matching users profile</returns>
119 public UserAgentData getAgentByUUID(LLUUID uuid)
120 {
121 try
122 {
123 return getUserByUUID(uuid).currentAgent;
124 }
125 catch (Exception)
126 {
127 return null;
128 }
129 }
130
131 /// <summary>
132 /// Returns a session by account name
133 /// </summary>
134 /// <param name="name">The account name</param>
135 /// <returns>The users session agent</returns>
136 public UserAgentData getAgentByName(string name)
137 {
138 return getAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
139 }
140
141 /// <summary>
142 /// Returns a session by account name
143 /// </summary>
144 /// <param name="fname">The first part of the users account name</param>
145 /// <param name="lname">The second part of the users account name</param>
146 /// <returns>A user agent</returns>
147 public UserAgentData getAgentByName(string fname, string lname)
148 {
149 try
150 {
151 return getUserByName(fname,lname).currentAgent;
152 }
153 catch (Exception)
154 {
155 return null;
156 }
157 }
158
159 /// <summary>
160 /// Creates a new user profile
161 /// </summary>
162 /// <param name="user">The profile to add to the database</param>
163 public void addNewUserProfile(UserProfileData user)
164 {
165 DataTable users = ds.Tables["users"];
166 DataRow row = users.Rows.Find(user.UUID);
167 if (row == null)
168 {
169 row = users.NewRow();
170 fillUserRow(row, user);
171 users.Rows.Add(row);
172 }
173 else
174 {
175 fillUserRow(row, user);
176 }
177 }
178
179 /// <summary>
180 /// Creates a new user profile
181 /// </summary>
182 /// <param name="user">The profile to add to the database</param>
183 /// <returns>True on success, false on error</returns>
184 public bool updateUserProfile(UserProfileData user)
185 {
186 DataTable users = ds.Tables["users"];
187 DataRow row = users.Rows.Find(user.UUID);
188 if (row == null)
189 {
190 row = users.NewRow();
191 fillUserRow(row, user);
192 users.Rows.Add(row);
193 }
194 else
195 {
196 fillUserRow(row, user);
197 }
198 return true;
199 }
200
201
202
203 /// <summary>
204 /// Creates a new user agent
205 /// </summary>
206 /// <param name="agent">The agent to add to the database</param>
207 public void addNewUserAgent(UserAgentData agent)
208 {
209 // Do nothing. yet.
210 }
211
212 /// <summary>
213 /// Transfers money between two user accounts
214 /// </summary>
215 /// <param name="from">Starting account</param>
216 /// <param name="to">End account</param>
217 /// <param name="amount">The amount to move</param>
218 /// <returns>Success?</returns>
219 public bool moneyTransferRequest(LLUUID from, LLUUID to, uint amount)
220 {
221 return true;
222 }
223
224 /// <summary>
225 /// Transfers inventory between two accounts
226 /// </summary>
227 /// <remarks>Move to inventory server</remarks>
228 /// <param name="from">Senders account</param>
229 /// <param name="to">Recievers account</param>
230 /// <param name="item">Inventory item</param>
231 /// <returns>Success?</returns>
232 public bool inventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
233 {
234 return true;
235 }
236
237 /// <summary>
238 /// Returns the name of the storage provider
239 /// </summary>
240 /// <returns>Storage provider name</returns>
241 public string getName()
242 {
243 return "Sqlite Userdata";
244 }
245
246 /// <summary>
247 /// Returns the version of the storage provider
248 /// </summary>
249 /// <returns>Storage provider version</returns>
250 public string getVersion()
251 {
252 return "0.1";
253 }
254
255 /***********************************************************************
256 *
257 * DataTable creation
258 *
259 **********************************************************************/
260 /***********************************************************************
261 *
262 * Database Definition Functions
263 *
264 * This should be db agnostic as we define them in ADO.NET terms
265 *
266 **********************************************************************/
267
268 private void createCol(DataTable dt, string name, System.Type type)
269 {
270 DataColumn col = new DataColumn(name, type);
271 dt.Columns.Add(col);
272 }
273
274 private DataTable createUsersTable()
275 {
276 DataTable users = new DataTable("users");
277
278 createCol(users, "UUID", typeof(System.String));
279 createCol(users, "username", typeof(System.String));
280 createCol(users, "surname", typeof(System.String));
281 createCol(users, "passwordHash", typeof(System.String));
282 createCol(users, "passwordSalt", typeof(System.String));
283
284 createCol(users, "homeRegion", typeof(System.UInt32));
285 createCol(users, "homeLocationX", typeof(System.Double));
286 createCol(users, "homeLocationY", typeof(System.Double));
287 createCol(users, "homeLocationZ", typeof(System.Double));
288 createCol(users, "homeLookAtX", typeof(System.Double));
289 createCol(users, "homeLookAtY", typeof(System.Double));
290 createCol(users, "homeLookAtZ", typeof(System.Double));
291 createCol(users, "created", typeof(System.Int32));
292 createCol(users, "lastLogin", typeof(System.Int32));
293 createCol(users, "rootInventoryFolderID", typeof(System.String));
294 createCol(users, "userInventoryURI", typeof(System.String));
295 createCol(users, "userAssetURI", typeof(System.String));
296 createCol(users, "profileCanDoMask", typeof(System.UInt32));
297 createCol(users, "profileWantDoMask", typeof(System.UInt32));
298 createCol(users, "profileAboutText", typeof(System.String));
299 createCol(users, "profileFirstText", typeof(System.String));
300 createCol(users, "profileImage", typeof(System.String));
301 createCol(users, "profileFirstImage", typeof(System.String));
302 // Add in contraints
303 users.PrimaryKey = new DataColumn[] { users.Columns["UUID"] };
304 return users;
305 }
306
307 private DataTable createUserAgentsTable()
308 {
309 DataTable ua = new DataTable("useragents");
310 // this is the UUID of the user
311 createCol(ua, "UUID", typeof(System.String));
312 createCol(ua, "agentIP", typeof(System.String));
313 createCol(ua, "agentPort", typeof(System.UInt32));
314 createCol(ua, "agentOnline", typeof(System.Boolean));
315 createCol(ua, "sessionID", typeof(System.String));
316 createCol(ua, "secureSessionID", typeof(System.String));
317 createCol(ua, "regionID", typeof(System.String));
318 createCol(ua, "loginTime", typeof(System.Int32));
319 createCol(ua, "logoutTime", typeof(System.Int32));
320 createCol(ua, "currentRegion", typeof(System.String));
321 createCol(ua, "currentHandle", typeof(System.UInt32));
322 // vectors
323 createCol(ua, "currentPosX", typeof(System.Double));
324 createCol(ua, "currentPosY", typeof(System.Double));
325 createCol(ua, "currentPosZ", typeof(System.Double));
326 // constraints
327 ua.PrimaryKey = new DataColumn[] { ua.Columns["UUID"] };
328
329 return ua;
330 }
331
332 /***********************************************************************
333 *
334 * Convert between ADO.NET <=> OpenSim Objects
335 *
336 * These should be database independant
337 *
338 **********************************************************************/
339
340 private UserProfileData buildUserProfile(DataRow row)
341 {
342 // TODO: this doesn't work yet because something more
343 // interesting has to be done to actually get these values
344 // back out. Not enough time to figure it out yet.
345 UserProfileData user = new UserProfileData();
346 user.UUID = new LLUUID((String)row["UUID"]);
347 user.username = (string)row["username"];
348 user.surname = (string)row["surname"];
349 user.passwordHash = (string)row["passwordHash"];
350 user.passwordSalt = (string)row["passwordSalt"];
351
352 user.homeRegion = Convert.ToUInt32(row["homeRegion"]);
353 user.homeLocation = new LLVector3(
354 Convert.ToSingle(row["homeLocationX"]),
355 Convert.ToSingle(row["homeLocationY"]),
356 Convert.ToSingle(row["homeLocationZ"])
357 );
358 user.homeLookAt = new LLVector3(
359 Convert.ToSingle(row["homeLookAtX"]),
360 Convert.ToSingle(row["homeLookAtY"]),
361 Convert.ToSingle(row["homeLookAtZ"])
362 );
363 user.created = Convert.ToInt32(row["created"]);
364 user.lastLogin = Convert.ToInt32(row["lastLogin"]);
365 user.rootInventoryFolderID = new LLUUID((string)row["rootInventoryFolderID"]);
366 user.userInventoryURI = (string)row["userInventoryURI"];
367 user.userAssetURI = (string)row["userAssetURI"];
368 user.profileCanDoMask = Convert.ToUInt32(row["profileCanDoMask"]);
369 user.profileWantDoMask = Convert.ToUInt32(row["profileWantDoMask"]);
370 user.profileAboutText = (string)row["profileAboutText"];
371 user.profileFirstText = (string)row["profileFirstText"];
372 user.profileImage = new LLUUID((string)row["profileImage"]);
373 user.profileFirstImage = new LLUUID((string)row["profileFirstImage"]);
374 return user;
375 }
376
377 private void fillUserRow(DataRow row, UserProfileData user)
378 {
379 row["UUID"] = user.UUID;
380 row["username"] = user.username;
381 row["surname"] = user.surname;
382 row["passwordHash"] = user.passwordHash;
383 row["passwordSalt"] = user.passwordSalt;
384
385
386 row["homeRegion"] = user.homeRegion;
387 row["homeLocationX"] = user.homeLocation.X;
388 row["homeLocationY"] = user.homeLocation.Y;
389 row["homeLocationZ"] = user.homeLocation.Z;
390 row["homeLookAtX"] = user.homeLookAt.X;
391 row["homeLookAtY"] = user.homeLookAt.Y;
392 row["homeLookAtZ"] = user.homeLookAt.Z;
393
394 row["created"] = user.created;
395 row["lastLogin"] = user.lastLogin;
396 row["rootInventoryFolderID"] = user.rootInventoryFolderID;
397 row["userInventoryURI"] = user.userInventoryURI;
398 row["userAssetURI"] = user.userAssetURI;
399 row["profileCanDoMask"] = user.profileCanDoMask;
400 row["profileWantDoMask"] = user.profileWantDoMask;
401 row["profileAboutText"] = user.profileAboutText;
402 row["profileFirstText"] = user.profileFirstText;
403 row["profileImage"] = user.profileImage;
404 row["profileFirstImage"] = user.profileFirstImage;
405 }
406
407// private PrimitiveBaseShape buildShape(DataRow row)
408// {
409// PrimitiveBaseShape s = new PrimitiveBaseShape();
410// s.Scale = new LLVector3(
411// Convert.ToSingle(row["ScaleX"]),
412// Convert.ToSingle(row["ScaleY"]),
413// Convert.ToSingle(row["ScaleZ"])
414// );
415// // paths
416// s.PCode = Convert.ToByte(row["PCode"]);
417// s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
418// s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
419// s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
420// s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
421// s.PathShearX = Convert.ToByte(row["PathShearX"]);
422// s.PathShearY = Convert.ToByte(row["PathShearY"]);
423// s.PathSkew = Convert.ToSByte(row["PathSkew"]);
424// s.PathCurve = Convert.ToByte(row["PathCurve"]);
425// s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
426// s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
427// s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
428// s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
429// s.PathTwist = Convert.ToSByte(row["PathTwist"]);
430// s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
431// // profile
432// s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
433// s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
434// s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
435// s.ProfileHollow = Convert.ToByte(row["ProfileHollow"]);
436// // text TODO: this isn't right] = but I'm not sure the right
437// // way to specify this as a blob atm
438// s.TextureEntry = (byte[])row["Texture"];
439// s.ExtraParams = (byte[])row["ExtraParams"];
440// // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
441// // string texture = encoding.GetString((Byte[])row["Texture"]);
442// // if (!texture.StartsWith("<"))
443// // {
444// // //here so that we can still work with old format database files (ie from before I added xml serialization)
445// // LLObject.TextureEntry textureEntry = null;
446// // textureEntry = new LLObject.TextureEntry(new LLUUID(texture));
447// // s.TextureEntry = textureEntry.ToBytes();
448// // }
449// // else
450// // {
451// // TextureBlock textureEntry = TextureBlock.FromXmlString(texture);
452// // s.TextureEntry = textureEntry.TextureData;
453// // s.ExtraParams = textureEntry.ExtraParams;
454// // }
455
456// return s;
457// }
458
459// private void fillShapeRow(DataRow row, SceneObjectPart prim)
460// {
461// PrimitiveBaseShape s = prim.Shape;
462// row["UUID"] = prim.UUID;
463// // shape is an enum
464// row["Shape"] = 0;
465// // vectors
466// row["ScaleX"] = s.Scale.X;
467// row["ScaleY"] = s.Scale.Y;
468// row["ScaleZ"] = s.Scale.Z;
469// // paths
470// row["PCode"] = s.PCode;
471// row["PathBegin"] = s.PathBegin;
472// row["PathEnd"] = s.PathEnd;
473// row["PathScaleX"] = s.PathScaleX;
474// row["PathScaleY"] = s.PathScaleY;
475// row["PathShearX"] = s.PathShearX;
476// row["PathShearY"] = s.PathShearY;
477// row["PathSkew"] = s.PathSkew;
478// row["PathCurve"] = s.PathCurve;
479// row["PathRadiusOffset"] = s.PathRadiusOffset;
480// row["PathRevolutions"] = s.PathRevolutions;
481// row["PathTaperX"] = s.PathTaperX;
482// row["PathTaperY"] = s.PathTaperY;
483// row["PathTwist"] = s.PathTwist;
484// row["PathTwistBegin"] = s.PathTwistBegin;
485// // profile
486// row["ProfileBegin"] = s.ProfileBegin;
487// row["ProfileEnd"] = s.ProfileEnd;
488// row["ProfileCurve"] = s.ProfileCurve;
489// row["ProfileHollow"] = s.ProfileHollow;
490// // text TODO: this isn't right] = but I'm not sure the right
491// // way to specify this as a blob atm
492
493// // And I couldn't work out how to save binary data either
494// // seems that the texture colum is being treated as a string in the Datarow
495// // if you do a .getType() on it, it returns string, while the other columns return correct type
496// // MW[10-08-07]
497// // Added following xml hack but not really ideal , also ExtraParams isn't currently part of the database
498// // am a bit worried about adding it now as some people will have old format databases, so for now including that data in this xml data
499// // MW[17-08-07]
500// row["Texture"] = s.TextureEntry;
501// row["ExtraParams"] = s.ExtraParams;
502// // TextureBlock textureBlock = new TextureBlock(s.TextureEntry);
503// // textureBlock.ExtraParams = s.ExtraParams;
504// // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
505// // row["Texture"] = encoding.GetBytes(textureBlock.ToXMLString());
506// }
507
508 /***********************************************************************
509 *
510 * SQL Statement Creation Functions
511 *
512 * These functions create SQL statements for update, insert, and create.
513 * They can probably be factored later to have a db independant
514 * portion and a db specific portion
515 *
516 **********************************************************************/
517
518 private SqliteCommand createInsertCommand(string table, DataTable dt)
519 {
520 /**
521 * This is subtle enough to deserve some commentary.
522 * Instead of doing *lots* and *lots of hardcoded strings
523 * for database definitions we'll use the fact that
524 * realistically all insert statements look like "insert
525 * into A(b, c) values(:b, :c) on the parameterized query
526 * front. If we just have a list of b, c, etc... we can
527 * generate these strings instead of typing them out.
528 */
529 string[] cols = new string[dt.Columns.Count];
530 for (int i = 0; i < dt.Columns.Count; i++) {
531 DataColumn col = dt.Columns[i];
532 cols[i] = col.ColumnName;
533 }
534
535 string sql = "insert into " + table + "(";
536 sql += String.Join(", ", cols);
537 // important, the first ':' needs to be here, the rest get added in the join
538 sql += ") values (:";
539 sql += String.Join(", :", cols);
540 sql += ")";
541 SqliteCommand cmd = new SqliteCommand(sql);
542
543 // this provides the binding for all our parameters, so
544 // much less code than it used to be
545 foreach (DataColumn col in dt.Columns)
546 {
547 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
548 }
549 return cmd;
550 }
551
552 private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
553 {
554 string sql = "update " + table + " set ";
555 string subsql = "";
556 foreach (DataColumn col in dt.Columns)
557 {
558 if (subsql.Length > 0)
559 { // a map function would rock so much here
560 subsql += ", ";
561 }
562 subsql += col.ColumnName + "= :" + col.ColumnName;
563 }
564 sql += subsql;
565 sql += " where " + pk;
566 SqliteCommand cmd = new SqliteCommand(sql);
567
568 // this provides the binding for all our parameters, so
569 // much less code than it used to be
570
571 foreach (DataColumn col in dt.Columns)
572 {
573 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
574 }
575 return cmd;
576 }
577
578
579 private string defineTable(DataTable dt)
580 {
581 string sql = "create table " + dt.TableName + "(";
582 string subsql = "";
583 foreach (DataColumn col in dt.Columns)
584 {
585 if (subsql.Length > 0)
586 { // a map function would rock so much here
587 subsql += ",\n";
588 }
589 subsql += col.ColumnName + " " + sqliteType(col.DataType);
590 if(col == dt.PrimaryKey[0])
591 {
592 subsql += " primary key";
593 }
594 }
595 sql += subsql;
596 sql += ")";
597 return sql;
598 }
599
600 /***********************************************************************
601 *
602 * Database Binding functions
603 *
604 * These will be db specific due to typing, and minor differences
605 * in databases.
606 *
607 **********************************************************************/
608
609 ///<summary>
610 /// This is a convenience function that collapses 5 repetitive
611 /// lines for defining SqliteParameters to 2 parameters:
612 /// column name and database type.
613 ///
614 /// It assumes certain conventions like :param as the param
615 /// name to replace in parametrized queries, and that source
616 /// version is always current version, both of which are fine
617 /// for us.
618 ///</summary>
619 ///<returns>a built sqlite parameter</returns>
620 private SqliteParameter createSqliteParameter(string name, System.Type type)
621 {
622 SqliteParameter param = new SqliteParameter();
623 param.ParameterName = ":" + name;
624 param.DbType = dbtypeFromType(type);
625 param.SourceColumn = name;
626 param.SourceVersion = DataRowVersion.Current;
627 return param;
628 }
629
630 private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn)
631 {
632 da.InsertCommand = createInsertCommand("users", ds.Tables["users"]);
633 da.InsertCommand.Connection = conn;
634
635 da.UpdateCommand = createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]);
636 da.UpdateCommand.Connection = conn;
637
638 SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID");
639 delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String)));
640 delete.Connection = conn;
641 da.DeleteCommand = delete;
642 }
643
644 private void InitDB(SqliteConnection conn)
645 {
646 string createUsers = defineTable(createUsersTable());
647 SqliteCommand pcmd = new SqliteCommand(createUsers, conn);
648 conn.Open();
649 pcmd.ExecuteNonQuery();
650 conn.Close();
651 }
652
653 private bool TestTables(SqliteConnection conn)
654 {
655 SqliteCommand cmd = new SqliteCommand(userSelect, conn);
656 SqliteDataAdapter pDa = new SqliteDataAdapter(cmd);
657 DataSet tmpDS = new DataSet();
658 try {
659 pDa.Fill(tmpDS, "users");
660 } catch (Mono.Data.SqliteClient.SqliteSyntaxException) {
661 MainLog.Instance.Verbose("DATASTORE", "SQLite Database doesn't exist... creating");
662 InitDB(conn);
663 }
664 return true;
665 }
666
667 /***********************************************************************
668 *
669 * Type conversion functions
670 *
671 **********************************************************************/
672
673 private DbType dbtypeFromType(Type type)
674 {
675 if (type == typeof(System.String)) {
676 return DbType.String;
677 } else if (type == typeof(System.Int32)) {
678 return DbType.Int32;
679 } else if (type == typeof(System.Double)) {
680 return DbType.Double;
681 } else if (type == typeof(System.Byte[])) {
682 return DbType.Binary;
683 } else {
684 return DbType.String;
685 }
686 }
687
688 // this is something we'll need to implement for each db
689 // slightly differently.
690 private string sqliteType(Type type)
691 {
692 if (type == typeof(System.String)) {
693 return "varchar(255)";
694 } else if (type == typeof(System.Int32)) {
695 return "integer";
696 } else if (type == typeof(System.Double)) {
697 return "float";
698 } else if (type == typeof(System.Byte[])) {
699 return "blob";
700 } else {
701 return "string";
702 }
703 }
704 }
705}