aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework
diff options
context:
space:
mode:
authorSean Dague2007-08-27 22:03:21 +0000
committerSean Dague2007-08-27 22:03:21 +0000
commite92f2accadfa601aefc3c2106e7b589db797def0 (patch)
treeb43b0912964b6992b7fdceaa204d746687b54b04 /OpenSim/Framework
parentremove sqlite3-inventory, this db is autocreated now (diff)
downloadopensim-SC_OLD-e92f2accadfa601aefc3c2106e7b589db797def0.zip
opensim-SC_OLD-e92f2accadfa601aefc3c2106e7b589db797def0.tar.gz
opensim-SC_OLD-e92f2accadfa601aefc3c2106e7b589db797def0.tar.bz2
opensim-SC_OLD-e92f2accadfa601aefc3c2106e7b589db797def0.tar.xz
first pass implementation of sqlite storage for user data. This
isn't functional enough to use yet, but does compile. Should be ready for testing in another day or so.
Diffstat (limited to '')
-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}