aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/SQLite')
-rw-r--r--OpenSim/Data/SQLite/Resources/001_AuthStore.sql18
-rw-r--r--OpenSim/Data/SQLite/Resources/001_Avatar.sql9
-rw-r--r--OpenSim/Data/SQLite/Resources/001_FriendsStore.sql10
-rw-r--r--OpenSim/Data/SQLite/Resources/001_GridUserStore.sql16
-rw-r--r--OpenSim/Data/SQLite/Resources/001_UserAccount.sql17
-rw-r--r--OpenSim/Data/SQLite/Resources/001_XInventoryStore.sql38
-rw-r--r--OpenSim/Data/SQLite/Resources/002_AuthStore.sql5
-rw-r--r--OpenSim/Data/SQLite/Resources/002_FriendsStore.sql5
-rw-r--r--OpenSim/Data/SQLite/Resources/002_UserAccount.sql5
-rw-r--r--OpenSim/Data/SQLite/Resources/002_XInventoryStore.sql8
-rw-r--r--OpenSim/Data/SQLite/Resources/005_AssetStore.sql5
-rw-r--r--OpenSim/Data/SQLite/Resources/019_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLite/SQLiteAssetData.cs53
-rw-r--r--OpenSim/Data/SQLite/SQLiteAuthenticationData.cs261
-rw-r--r--OpenSim/Data/SQLite/SQLiteAvatarData.cs74
-rw-r--r--OpenSim/Data/SQLite/SQLiteEstateData.cs131
-rw-r--r--OpenSim/Data/SQLite/SQLiteFramework.cs42
-rw-r--r--OpenSim/Data/SQLite/SQLiteFriendsData.cs70
-rw-r--r--OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs37
-rw-r--r--OpenSim/Data/SQLite/SQLiteGridData.cs286
-rw-r--r--OpenSim/Data/SQLite/SQLiteGridUserData.cs (renamed from OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs)55
-rw-r--r--OpenSim/Data/SQLite/SQLiteInventoryStore.cs76
-rw-r--r--OpenSim/Data/SQLite/SQLiteManager.cs225
-rw-r--r--OpenSim/Data/SQLite/SQLiteRegionData.cs270
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserAccountData.cs81
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserData.cs1262
-rw-r--r--OpenSim/Data/SQLite/SQLiteUtils.cs2
-rw-r--r--OpenSim/Data/SQLite/SQLiteXInventoryData.cs10
28 files changed, 1047 insertions, 2029 deletions
diff --git a/OpenSim/Data/SQLite/Resources/001_AuthStore.sql b/OpenSim/Data/SQLite/Resources/001_AuthStore.sql
new file mode 100644
index 0000000..468567d
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_AuthStore.sql
@@ -0,0 +1,18 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE auth (
4 UUID char(36) NOT NULL,
5 passwordHash char(32) NOT NULL default '',
6 passwordSalt char(32) NOT NULL default '',
7 webLoginKey varchar(255) NOT NULL default '',
8 accountType VARCHAR(32) NOT NULL DEFAULT 'UserAccount',
9 PRIMARY KEY (`UUID`)
10);
11
12CREATE TABLE tokens (
13 UUID char(36) NOT NULL,
14 token varchar(255) NOT NULL,
15 validity datetime NOT NULL
16);
17
18COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_Avatar.sql b/OpenSim/Data/SQLite/Resources/001_Avatar.sql
new file mode 100644
index 0000000..7ec906b
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_Avatar.sql
@@ -0,0 +1,9 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE Avatars (
4 PrincipalID CHAR(36) NOT NULL,
5 Name VARCHAR(32) NOT NULL,
6 Value VARCHAR(255) NOT NULL DEFAULT '',
7 PRIMARY KEY(PrincipalID, Name));
8
9COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_FriendsStore.sql b/OpenSim/Data/SQLite/Resources/001_FriendsStore.sql
new file mode 100644
index 0000000..f1b9ab9
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_FriendsStore.sql
@@ -0,0 +1,10 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE `Friends` (
4 `PrincipalID` CHAR(36) NOT NULL,
5 `Friend` VARCHAR(255) NOT NULL,
6 `Flags` VARCHAR(16) NOT NULL DEFAULT 0,
7 `Offered` VARCHAR(32) NOT NULL DEFAULT 0,
8 PRIMARY KEY(`PrincipalID`, `Friend`));
9
10COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_GridUserStore.sql b/OpenSim/Data/SQLite/Resources/001_GridUserStore.sql
new file mode 100644
index 0000000..1a24613
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_GridUserStore.sql
@@ -0,0 +1,16 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE GridUser (
4 UserID VARCHAR(255) primary key,
5 HomeRegionID CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
6 HomePosition CHAR(64) NOT NULL DEFAULT '<0,0,0>',
7 HomeLookAt CHAR(64) NOT NULL DEFAULT '<0,0,0>',
8 LastRegionID CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
9 LastPosition CHAR(64) NOT NULL DEFAULT '<0,0,0>',
10 LastLookAt CHAR(64) NOT NULL DEFAULT '<0,0,0>',
11 Online CHAR(5) NOT NULL DEFAULT 'false',
12 Login CHAR(16) NOT NULL DEFAULT '0',
13 Logout CHAR(16) NOT NULL DEFAULT '0'
14) ;
15
16COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_UserAccount.sql b/OpenSim/Data/SQLite/Resources/001_UserAccount.sql
new file mode 100644
index 0000000..c38d9a7
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_UserAccount.sql
@@ -0,0 +1,17 @@
1BEGIN TRANSACTION;
2
3-- useraccounts table
4CREATE TABLE UserAccounts (
5 PrincipalID CHAR(36) primary key,
6 ScopeID CHAR(36) NOT NULL,
7 FirstName VARCHAR(64) NOT NULL,
8 LastName VARCHAR(64) NOT NULL,
9 Email VARCHAR(64),
10 ServiceURLs TEXT,
11 Created INT(11),
12 UserLevel integer NOT NULL DEFAULT 0,
13 UserFlags integer NOT NULL DEFAULT 0,
14 UserTitle varchar(64) NOT NULL DEFAULT ''
15);
16
17COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/Resources/001_XInventoryStore.sql b/OpenSim/Data/SQLite/Resources/001_XInventoryStore.sql
new file mode 100644
index 0000000..7e21996
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_XInventoryStore.sql
@@ -0,0 +1,38 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE inventoryfolders(
4 folderName varchar(255),
5 type integer,
6 version integer,
7 folderID varchar(255) primary key,
8 agentID varchar(255) not null default '00000000-0000-0000-0000-000000000000',
9 parentFolderID varchar(255) not null default '00000000-0000-0000-0000-000000000000');
10
11CREATE TABLE inventoryitems(
12 assetID varchar(255),
13 assetType integer,
14 inventoryName varchar(255),
15 inventoryDescription varchar(255),
16 inventoryNextPermissions integer,
17 inventoryCurrentPermissions integer,
18 invType integer,
19 creatorID varchar(255),
20 inventoryBasePermissions integer,
21 inventoryEveryOnePermissions integer,
22 salePrice integer default 99,
23 saleType integer default 0,
24 creationDate integer default 2000,
25 groupID varchar(255) default '00000000-0000-0000-0000-000000000000',
26 groupOwned integer default 0,
27 flags integer default 0,
28 inventoryID varchar(255) primary key,
29 parentFolderID varchar(255) not null default '00000000-0000-0000-0000-000000000000',
30 avatarID varchar(255) not null default '00000000-0000-0000-0000-000000000000',
31 inventoryGroupPermissions integer not null default 0);
32
33create index inventoryfolders_agentid on inventoryfolders(agentID);
34create index inventoryfolders_parentid on inventoryfolders(parentFolderID);
35create index inventoryitems_parentfolderid on inventoryitems(parentFolderID);
36create index inventoryitems_avatarid on inventoryitems(avatarID);
37
38COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/002_AuthStore.sql b/OpenSim/Data/SQLite/Resources/002_AuthStore.sql
new file mode 100644
index 0000000..3237b68
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/002_AuthStore.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey) SELECT `UUID` AS UUID, `passwordHash` AS passwordHash, `passwordSalt` AS passwordSalt, `webLoginKey` AS webLoginKey FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/002_FriendsStore.sql b/OpenSim/Data/SQLite/Resources/002_FriendsStore.sql
new file mode 100644
index 0000000..6733502
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/002_FriendsStore.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO `Friends` SELECT `ownerID`, `friendID`, `friendPerms`, 0 FROM `userfriends`;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/002_UserAccount.sql b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..c7a6293
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, surname AS LastName, '' as Email, '' AS ServiceURLs, created as Created FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/002_XInventoryStore.sql b/OpenSim/Data/SQLite/Resources/002_XInventoryStore.sql
new file mode 100644
index 0000000..d38e2b7
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/002_XInventoryStore.sql
@@ -0,0 +1,8 @@
1ATTACH 'inventoryStore.db' AS old;
2
3BEGIN TRANSACTION;
4
5INSERT INTO inventoryfolders (folderName, type, version, folderID, agentID, parentFolderID) SELECT `name` AS folderName, `type` AS type, `version` AS version, `UUID` AS folderID, `agentID` AS agentID, `parentID` AS parentFolderID from old.inventoryfolders;
6INSERT INTO inventoryitems (assetID, assetType, inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType, creatorID, inventoryBasePermissions, inventoryEveryOnePermissions, salePrice, saleType, creationDate, groupID, groupOwned, flags, inventoryID, parentFolderID, avatarID, inventoryGroupPermissions) SELECT `assetID`, `assetType` AS assetType, `inventoryName` AS inventoryName, `inventoryDescription` AS inventoryDescription, `inventoryNextPermissions` AS inventoryNextPermissions, `inventoryCurrentPermissions` AS inventoryCurrentPermissions, `invType` AS invType, `creatorsID` AS creatorID, `inventoryBasePermissions` AS inventoryBasePermissions, `inventoryEveryOnePermissions` AS inventoryEveryOnePermissions, `salePrice` AS salePrice, `saleType` AS saleType, `creationDate` AS creationDate, `groupID` AS groupID, `groupOwned` AS groupOwned, `flags` AS flags, `UUID` AS inventoryID, `parentFolderID` AS parentFolderID, `avatarID` AS avatarID, `inventoryGroupPermissions` AS inventoryGroupPermissions FROM old.inventoryitems;
7
8COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/005_AssetStore.sql b/OpenSim/Data/SQLite/Resources/005_AssetStore.sql
new file mode 100644
index 0000000..f06121a
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/005_AssetStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE assets ADD COLUMN asset_flags INTEGER NOT NULL DEFAULT 0;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/019_RegionStore.sql b/OpenSim/Data/SQLite/Resources/019_RegionStore.sql
new file mode 100644
index 0000000..d62f848
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/019_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE regionsettings ADD COLUMN map_tile_ID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
4
5COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs
index c52f60b..7081f99 100644
--- a/OpenSim/Data/SQLite/SQLiteAssetData.cs
+++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs
@@ -30,7 +30,7 @@ using System.Data;
30using System.Reflection; 30using System.Reflection;
31using System.Collections.Generic; 31using System.Collections.Generic;
32using log4net; 32using log4net;
33using Mono.Data.SqliteClient; 33using Mono.Data.Sqlite;
34using OpenMetaverse; 34using OpenMetaverse;
35using OpenSim.Framework; 35using OpenSim.Framework;
36 36
@@ -44,10 +44,10 @@ namespace OpenSim.Data.SQLite
44// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 45
46 private const string SelectAssetSQL = "select * from assets where UUID=:UUID"; 46 private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
47 private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, UUID from assets limit :start, :count"; 47 private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, asset_flags, UUID from assets limit :start, :count";
48 private const string DeleteAssetSQL = "delete from assets where UUID=:UUID"; 48 private const string DeleteAssetSQL = "delete from assets where UUID=:UUID";
49 private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :Local, :Temporary, :Data)"; 49 private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, Local, Temporary, asset_flags, Data) values(:UUID, :Name, :Description, :Type, :Local, :Temporary, :Flags, :Data)";
50 private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID"; 50 private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, Local=:Local, Temporary=:Temporary, asset_flags=:Flags, Data=:Data where UUID=:UUID";
51 private const string assetSelect = "select * from assets"; 51 private const string assetSelect = "select * from assets";
52 52
53 private SqliteConnection m_conn; 53 private SqliteConnection m_conn;
@@ -136,6 +136,7 @@ namespace OpenSim.Data.SQLite
136 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); 136 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
137 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); 137 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
138 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); 138 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
139 cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
139 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data)); 140 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
140 141
141 cmd.ExecuteNonQuery(); 142 cmd.ExecuteNonQuery();
@@ -154,6 +155,7 @@ namespace OpenSim.Data.SQLite
154 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); 155 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
155 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); 156 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
156 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); 157 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
158 cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
157 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data)); 159 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
158 160
159 cmd.ExecuteNonQuery(); 161 cmd.ExecuteNonQuery();
@@ -208,20 +210,6 @@ namespace OpenSim.Data.SQLite
208 } 210 }
209 211
210 /// <summary> 212 /// <summary>
211 /// Delete an asset from database
212 /// </summary>
213 /// <param name="uuid"></param>
214 public void DeleteAsset(UUID uuid)
215 {
216 using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
217 {
218 cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
219
220 cmd.ExecuteNonQuery();
221 }
222 }
223
224 /// <summary>
225 /// 213 ///
226 /// </summary> 214 /// </summary>
227 /// <param name="row"></param> 215 /// <param name="row"></param>
@@ -234,13 +222,15 @@ namespace OpenSim.Data.SQLite
234 AssetBase asset = new AssetBase( 222 AssetBase asset = new AssetBase(
235 new UUID((String)row["UUID"]), 223 new UUID((String)row["UUID"]),
236 (String)row["Name"], 224 (String)row["Name"],
237 Convert.ToSByte(row["Type"]) 225 Convert.ToSByte(row["Type"]),
226 UUID.Zero.ToString()
238 ); 227 );
239 228
240 asset.Description = (String) row["Description"]; 229 asset.Description = (String) row["Description"];
241 asset.Local = Convert.ToBoolean(row["Local"]); 230 asset.Local = Convert.ToBoolean(row["Local"]);
242 asset.Temporary = Convert.ToBoolean(row["Temporary"]); 231 asset.Temporary = Convert.ToBoolean(row["Temporary"]);
243 asset.Data = (byte[]) row["Data"]; 232 asset.Flags = (AssetFlags)Convert.ToInt32(row["asset_flags"]);
233 asset.Data = (byte[])row["Data"];
244 return asset; 234 return asset;
245 } 235 }
246 236
@@ -253,6 +243,7 @@ namespace OpenSim.Data.SQLite
253 metadata.Description = (string) row["Description"]; 243 metadata.Description = (string) row["Description"];
254 metadata.Type = Convert.ToSByte(row["Type"]); 244 metadata.Type = Convert.ToSByte(row["Type"]);
255 metadata.Temporary = Convert.ToBoolean(row["Temporary"]); // Not sure if this is correct. 245 metadata.Temporary = Convert.ToBoolean(row["Temporary"]); // Not sure if this is correct.
246 metadata.Flags = (AssetFlags)Convert.ToInt32(row["asset_flags"]);
256 247
257 // Current SHA1s are not stored/computed. 248 // Current SHA1s are not stored/computed.
258 metadata.SHA1 = new byte[] {}; 249 metadata.SHA1 = new byte[] {};
@@ -337,6 +328,26 @@ namespace OpenSim.Data.SQLite
337 get { return "SQLite Asset storage engine"; } 328 get { return "SQLite Asset storage engine"; }
338 } 329 }
339 330
331 public override bool Delete(string id)
332 {
333 UUID assetID;
334
335 if (!UUID.TryParse(id, out assetID))
336 return false;
337
338 lock (this)
339 {
340 using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
341 {
342 cmd.Parameters.Add(new SqliteParameter(":UUID", assetID.ToString()));
343
344 cmd.ExecuteNonQuery();
345 }
346 }
347
348 return true;
349 }
350
340 #endregion 351 #endregion
341 } 352 }
342} \ No newline at end of file 353}
diff --git a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs
new file mode 100644
index 0000000..a1412ff
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs
@@ -0,0 +1,261 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using System.Reflection;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using Mono.Data.Sqlite;
37
38namespace OpenSim.Data.SQLite
39{
40 public class SQLiteAuthenticationData : SQLiteFramework, IAuthenticationData
41 {
42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 private string m_Realm;
45 private List<string> m_ColumnNames;
46 private int m_LastExpire;
47 private string m_connectionString;
48
49 protected static SqliteConnection m_Connection;
50 private static bool m_initialized = false;
51
52 public SQLiteAuthenticationData(string connectionString, string realm)
53 : base(connectionString)
54 {
55 m_Realm = realm;
56 m_connectionString = connectionString;
57
58 if (!m_initialized)
59 {
60 m_Connection = new SqliteConnection(connectionString);
61 m_Connection.Open();
62
63 Migration m = new Migration(m_Connection, GetType().Assembly, "AuthStore");
64 m.Update();
65
66 m_initialized = true;
67 }
68 }
69
70 public AuthenticationData Get(UUID principalID)
71 {
72 AuthenticationData ret = new AuthenticationData();
73 ret.Data = new Dictionary<string, object>();
74
75 SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
76 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
77
78 IDataReader result = ExecuteReader(cmd, m_Connection);
79
80 try
81 {
82 if (result.Read())
83 {
84 ret.PrincipalID = principalID;
85
86 if (m_ColumnNames == null)
87 {
88 m_ColumnNames = new List<string>();
89
90 DataTable schemaTable = result.GetSchemaTable();
91 foreach (DataRow row in schemaTable.Rows)
92 m_ColumnNames.Add(row["ColumnName"].ToString());
93 }
94
95 foreach (string s in m_ColumnNames)
96 {
97 if (s == "UUID")
98 continue;
99
100 ret.Data[s] = result[s].ToString();
101 }
102
103 return ret;
104 }
105 else
106 {
107 return null;
108 }
109 }
110 catch
111 {
112 }
113 finally
114 {
115 //CloseCommand(cmd);
116 }
117
118 return null;
119 }
120
121 public bool Store(AuthenticationData data)
122 {
123 if (data.Data.ContainsKey("UUID"))
124 data.Data.Remove("UUID");
125
126 string[] fields = new List<string>(data.Data.Keys).ToArray();
127 string[] values = new string[data.Data.Count];
128 int i = 0;
129 foreach (object o in data.Data.Values)
130 values[i++] = o.ToString();
131
132 SqliteCommand cmd = new SqliteCommand();
133
134 if (Get(data.PrincipalID) != null)
135 {
136
137
138 string update = "update `" + m_Realm + "` set ";
139 bool first = true;
140 foreach (string field in fields)
141 {
142 if (!first)
143 update += ", ";
144 update += "`" + field + "` = :" + field;
145 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
146
147 first = false;
148 }
149
150 update += " where UUID = :UUID";
151 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
152
153 cmd.CommandText = update;
154 try
155 {
156 if (ExecuteNonQuery(cmd, m_Connection) < 1)
157 {
158 //CloseCommand(cmd);
159 return false;
160 }
161 }
162 catch (Exception e)
163 {
164 m_log.Error("[SQLITE]: Exception storing authentication data", e);
165 //CloseCommand(cmd);
166 return false;
167 }
168 }
169
170 else
171 {
172 string insert = "insert into `" + m_Realm + "` (`UUID`, `" +
173 String.Join("`, `", fields) +
174 "`) values (:UUID, :" + String.Join(", :", fields) + ")";
175
176 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
177 foreach (string field in fields)
178 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
179
180 cmd.CommandText = insert;
181
182 try
183 {
184 if (ExecuteNonQuery(cmd, m_Connection) < 1)
185 {
186 //CloseCommand(cmd);
187 return false;
188 }
189 }
190 catch (Exception e)
191 {
192 Console.WriteLine(e.ToString());
193 //CloseCommand(cmd);
194 return false;
195 }
196 }
197
198 //CloseCommand(cmd);
199
200 return true;
201 }
202
203 public bool SetDataItem(UUID principalID, string item, string value)
204 {
205 SqliteCommand cmd = new SqliteCommand("update `" + m_Realm +
206 "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'");
207
208 if (ExecuteNonQuery(cmd, m_Connection) > 0)
209 return true;
210
211 return false;
212 }
213
214 public bool SetToken(UUID principalID, string token, int lifetime)
215 {
216 if (System.Environment.TickCount - m_LastExpire > 30000)
217 DoExpire();
218
219 SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
220 "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");
221
222 if (ExecuteNonQuery(cmd, m_Connection) > 0)
223 {
224 cmd.Dispose();
225 return true;
226 }
227
228 cmd.Dispose();
229 return false;
230 }
231
232 public bool CheckToken(UUID principalID, string token, int lifetime)
233 {
234 if (System.Environment.TickCount - m_LastExpire > 30000)
235 DoExpire();
236
237 SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now', 'localtime', '+" + lifetime.ToString() +
238 " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
239
240 if (ExecuteNonQuery(cmd, m_Connection) > 0)
241 {
242 cmd.Dispose();
243 return true;
244 }
245
246 cmd.Dispose();
247
248 return false;
249 }
250
251 private void DoExpire()
252 {
253 SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
254 ExecuteNonQuery(cmd, m_Connection);
255
256 cmd.Dispose();
257
258 m_LastExpire = System.Environment.TickCount;
259 }
260 }
261}
diff --git a/OpenSim/Data/SQLite/SQLiteAvatarData.cs b/OpenSim/Data/SQLite/SQLiteAvatarData.cs
new file mode 100644
index 0000000..c093884
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteAvatarData.cs
@@ -0,0 +1,74 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using Mono.Data.Sqlite;
37
38namespace OpenSim.Data.SQLite
39{
40 /// <summary>
41 /// A SQLite Interface for Avatar Data
42 /// </summary>
43 public class SQLiteAvatarData : SQLiteGenericTableHandler<AvatarBaseData>,
44 IAvatarData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public SQLiteAvatarData(string connectionString, string realm) :
49 base(connectionString, realm, "Avatar")
50 {
51 }
52
53 public bool Delete(UUID principalID, string name)
54 {
55 SqliteCommand cmd = new SqliteCommand();
56
57 cmd.CommandText = String.Format("delete from {0} where `PrincipalID` = :PrincipalID and `Name` = :Name", m_Realm);
58 cmd.Parameters.AddWithValue(":PrincipalID", principalID.ToString());
59 cmd.Parameters.AddWithValue(":Name", name);
60
61 try
62 {
63 if (ExecuteNonQuery(cmd, m_Connection) > 0)
64 return true;
65
66 return false;
67 }
68 finally
69 {
70 //CloseCommand(cmd);
71 }
72 }
73 }
74}
diff --git a/OpenSim/Data/SQLite/SQLiteEstateData.cs b/OpenSim/Data/SQLite/SQLiteEstateData.cs
index 1be99ee..9dd4a2e 100644
--- a/OpenSim/Data/SQLite/SQLiteEstateData.cs
+++ b/OpenSim/Data/SQLite/SQLiteEstateData.cs
@@ -30,7 +30,7 @@ using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Reflection; 31using System.Reflection;
32using log4net; 32using log4net;
33using Mono.Data.SqliteClient; 33using Mono.Data.Sqlite;
34using OpenMetaverse; 34using OpenMetaverse;
35using OpenSim.Framework; 35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces; 36using OpenSim.Region.Framework.Interfaces;
@@ -62,8 +62,8 @@ namespace OpenSim.Data.SQLite
62 Migration m = new Migration(m_connection, assem, "EstateStore"); 62 Migration m = new Migration(m_connection, assem, "EstateStore");
63 m.Update(); 63 m.Update();
64 64
65 m_connection.Close(); 65 //m_connection.Close();
66 m_connection.Open(); 66 // m_connection.Open();
67 67
68 Type t = typeof(EstateSettings); 68 Type t = typeof(EstateSettings);
69 m_Fields = t.GetFields(BindingFlags.NonPublic | 69 m_Fields = t.GetFields(BindingFlags.NonPublic |
@@ -80,17 +80,22 @@ namespace OpenSim.Data.SQLite
80 get { return new List<string>(m_FieldMap.Keys).ToArray(); } 80 get { return new List<string>(m_FieldMap.Keys).ToArray(); }
81 } 81 }
82 82
83 public EstateSettings LoadEstateSettings(UUID regionID) 83 public EstateSettings LoadEstateSettings(UUID regionID, bool create)
84 { 84 {
85 EstateSettings es = new EstateSettings();
86 es.OnSave += StoreEstateSettings;
87
88 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = :RegionID"; 85 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = :RegionID";
89 86
90 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); 87 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
91 88
92 cmd.CommandText = sql; 89 cmd.CommandText = sql;
93 cmd.Parameters.Add(":RegionID", regionID.ToString()); 90 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
91
92 return DoLoad(cmd, regionID, create);
93 }
94
95 private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
96 {
97 EstateSettings es = new EstateSettings();
98 es.OnSave += StoreEstateSettings;
94 99
95 IDataReader r = cmd.ExecuteReader(); 100 IDataReader r = cmd.ExecuteReader();
96 101
@@ -120,17 +125,15 @@ namespace OpenSim.Data.SQLite
120 } 125 }
121 r.Close(); 126 r.Close();
122 } 127 }
123 else 128 else if (create)
124 { 129 {
125 // Migration case
126 //
127 r.Close(); 130 r.Close();
128 131
129 List<string> names = new List<string>(FieldList); 132 List<string> names = new List<string>(FieldList);
130 133
131 names.Remove("EstateID"); 134 names.Remove("EstateID");
132 135
133 sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")"; 136 string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
134 137
135 cmd.CommandText = sql; 138 cmd.CommandText = sql;
136 cmd.Parameters.Clear(); 139 cmd.Parameters.Clear();
@@ -140,13 +143,13 @@ namespace OpenSim.Data.SQLite
140 if (m_FieldMap[name].GetValue(es) is bool) 143 if (m_FieldMap[name].GetValue(es) is bool)
141 { 144 {
142 if ((bool)m_FieldMap[name].GetValue(es)) 145 if ((bool)m_FieldMap[name].GetValue(es))
143 cmd.Parameters.Add(":"+name, "1"); 146 cmd.Parameters.AddWithValue(":"+name, "1");
144 else 147 else
145 cmd.Parameters.Add(":"+name, "0"); 148 cmd.Parameters.AddWithValue(":"+name, "0");
146 } 149 }
147 else 150 else
148 { 151 {
149 cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString()); 152 cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
150 } 153 }
151 } 154 }
152 155
@@ -164,8 +167,8 @@ namespace OpenSim.Data.SQLite
164 r.Close(); 167 r.Close();
165 168
166 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)"; 169 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
167 cmd.Parameters.Add(":RegionID", regionID.ToString()); 170 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
168 cmd.Parameters.Add(":EstateID", es.EstateID.ToString()); 171 cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
169 172
170 // This will throw on dupe key 173 // This will throw on dupe key
171 try 174 try
@@ -176,20 +179,6 @@ namespace OpenSim.Data.SQLite
176 { 179 {
177 } 180 }
178 181
179 // Munge and transfer the ban list
180 //
181 cmd.Parameters.Clear();
182 cmd.CommandText = "insert into estateban select "+es.EstateID.ToString()+", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = :UUID";
183 cmd.Parameters.Add(":UUID", regionID.ToString());
184
185 try
186 {
187 cmd.ExecuteNonQuery();
188 }
189 catch (Exception)
190 {
191 }
192
193 es.Save(); 182 es.Save();
194 } 183 }
195 184
@@ -222,13 +211,13 @@ namespace OpenSim.Data.SQLite
222 if (m_FieldMap[name].GetValue(es) is bool) 211 if (m_FieldMap[name].GetValue(es) is bool)
223 { 212 {
224 if ((bool)m_FieldMap[name].GetValue(es)) 213 if ((bool)m_FieldMap[name].GetValue(es))
225 cmd.Parameters.Add(":"+name, "1"); 214 cmd.Parameters.AddWithValue(":"+name, "1");
226 else 215 else
227 cmd.Parameters.Add(":"+name, "0"); 216 cmd.Parameters.AddWithValue(":"+name, "0");
228 } 217 }
229 else 218 else
230 { 219 {
231 cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString()); 220 cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
232 } 221 }
233 } 222 }
234 223
@@ -247,7 +236,7 @@ namespace OpenSim.Data.SQLite
247 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); 236 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
248 237
249 cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID"; 238 cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID";
250 cmd.Parameters.Add(":EstateID", es.EstateID); 239 cmd.Parameters.AddWithValue(":EstateID", es.EstateID);
251 240
252 IDataReader r = cmd.ExecuteReader(); 241 IDataReader r = cmd.ExecuteReader();
253 242
@@ -271,7 +260,7 @@ namespace OpenSim.Data.SQLite
271 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); 260 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
272 261
273 cmd.CommandText = "delete from estateban where EstateID = :EstateID"; 262 cmd.CommandText = "delete from estateban where EstateID = :EstateID";
274 cmd.Parameters.Add(":EstateID", es.EstateID.ToString()); 263 cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
275 264
276 cmd.ExecuteNonQuery(); 265 cmd.ExecuteNonQuery();
277 266
@@ -281,8 +270,8 @@ namespace OpenSim.Data.SQLite
281 270
282 foreach (EstateBan b in es.EstateBans) 271 foreach (EstateBan b in es.EstateBans)
283 { 272 {
284 cmd.Parameters.Add(":EstateID", es.EstateID.ToString()); 273 cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
285 cmd.Parameters.Add(":bannedUUID", b.BannedUserID.ToString()); 274 cmd.Parameters.AddWithValue(":bannedUUID", b.BannedUserID.ToString());
286 275
287 cmd.ExecuteNonQuery(); 276 cmd.ExecuteNonQuery();
288 cmd.Parameters.Clear(); 277 cmd.Parameters.Clear();
@@ -294,7 +283,7 @@ namespace OpenSim.Data.SQLite
294 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); 283 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
295 284
296 cmd.CommandText = "delete from "+table+" where EstateID = :EstateID"; 285 cmd.CommandText = "delete from "+table+" where EstateID = :EstateID";
297 cmd.Parameters.Add(":EstateID", EstateID.ToString()); 286 cmd.Parameters.AddWithValue(":EstateID", EstateID.ToString());
298 287
299 cmd.ExecuteNonQuery(); 288 cmd.ExecuteNonQuery();
300 289
@@ -304,8 +293,8 @@ namespace OpenSim.Data.SQLite
304 293
305 foreach (UUID uuid in data) 294 foreach (UUID uuid in data)
306 { 295 {
307 cmd.Parameters.Add(":EstateID", EstateID.ToString()); 296 cmd.Parameters.AddWithValue(":EstateID", EstateID.ToString());
308 cmd.Parameters.Add(":uuid", uuid.ToString()); 297 cmd.Parameters.AddWithValue(":uuid", uuid.ToString());
309 298
310 cmd.ExecuteNonQuery(); 299 cmd.ExecuteNonQuery();
311 cmd.Parameters.Clear(); 300 cmd.Parameters.Clear();
@@ -319,7 +308,7 @@ namespace OpenSim.Data.SQLite
319 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); 308 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
320 309
321 cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID"; 310 cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID";
322 cmd.Parameters.Add(":EstateID", EstateID); 311 cmd.Parameters.AddWithValue(":EstateID", EstateID);
323 312
324 IDataReader r = cmd.ExecuteReader(); 313 IDataReader r = cmd.ExecuteReader();
325 314
@@ -336,5 +325,63 @@ namespace OpenSim.Data.SQLite
336 325
337 return uuids.ToArray(); 326 return uuids.ToArray();
338 } 327 }
328
329 public EstateSettings LoadEstateSettings(int estateID)
330 {
331 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_settings where estate_settings.EstateID :EstateID";
332
333 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
334
335 cmd.CommandText = sql;
336 cmd.Parameters.AddWithValue(":EstateID", estateID.ToString());
337
338 return DoLoad(cmd, UUID.Zero, false);
339 }
340
341 public List<int> GetEstates(string search)
342 {
343 List<int> result = new List<int>();
344
345 string sql = "select EstateID from estate_settings where estate_settings.EstateName :EstateName";
346
347 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
348
349 cmd.CommandText = sql;
350 cmd.Parameters.AddWithValue(":EstateName", search);
351
352 IDataReader r = cmd.ExecuteReader();
353
354 while (r.Read())
355 {
356 result.Add(Convert.ToInt32(r["EstateID"]));
357 }
358 r.Close();
359
360 return result;
361 }
362
363 public bool LinkRegion(UUID regionID, int estateID)
364 {
365 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
366
367 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
368 cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
369 cmd.Parameters.AddWithValue(":EstateID", estateID.ToString());
370
371 if (cmd.ExecuteNonQuery() == 0)
372 return false;
373
374 return true;
375 }
376
377 public List<UUID> GetRegions(int estateID)
378 {
379 return new List<UUID>();
380 }
381
382 public bool DeleteEstate(int estateID)
383 {
384 return false;
385 }
339 } 386 }
340} 387}
diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs
index 12b2750..cf114d1 100644
--- a/OpenSim/Data/SQLite/SQLiteFramework.cs
+++ b/OpenSim/Data/SQLite/SQLiteFramework.cs
@@ -31,7 +31,7 @@ using System.Collections.Generic;
31using System.Data; 31using System.Data;
32using OpenMetaverse; 32using OpenMetaverse;
33using OpenSim.Framework; 33using OpenSim.Framework;
34using Mono.Data.SqliteClient; 34using Mono.Data.Sqlite;
35 35
36namespace OpenSim.Data.SQLite 36namespace OpenSim.Data.SQLite
37{ 37{
@@ -40,12 +40,10 @@ namespace OpenSim.Data.SQLite
40 /// </summary> 40 /// </summary>
41 public class SQLiteFramework 41 public class SQLiteFramework
42 { 42 {
43 protected SqliteConnection m_Connection; 43 protected Object m_lockObject = new Object();
44 44
45 protected SQLiteFramework(string connectionString) 45 protected SQLiteFramework(string connectionString)
46 { 46 {
47 m_Connection = new SqliteConnection(connectionString);
48 m_Connection.Open();
49 } 47 }
50 48
51 ////////////////////////////////////////////////////////////// 49 //////////////////////////////////////////////////////////////
@@ -53,27 +51,41 @@ namespace OpenSim.Data.SQLite
53 // All non queries are funneled through one connection 51 // All non queries are funneled through one connection
54 // to increase performance a little 52 // to increase performance a little
55 // 53 //
56 protected int ExecuteNonQuery(SqliteCommand cmd) 54 protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
57 { 55 {
58 lock (m_Connection) 56 lock (connection)
59 { 57 {
60 cmd.Connection = m_Connection; 58/*
59 SqliteConnection newConnection =
60 (SqliteConnection)((ICloneable)connection).Clone();
61 newConnection.Open();
62
63 cmd.Connection = newConnection;
64*/
65 cmd.Connection = connection;
66 //Console.WriteLine("XXX " + cmd.CommandText);
61 67
62 return cmd.ExecuteNonQuery(); 68 return cmd.ExecuteNonQuery();
63 } 69 }
64 } 70 }
65 71
66 protected IDataReader ExecuteReader(SqliteCommand cmd) 72 protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
67 { 73 {
68 SqliteConnection newConnection = 74 lock (connection)
69 (SqliteConnection)((ICloneable)m_Connection).Clone(); 75 {
70 newConnection.Open(); 76 //SqliteConnection newConnection =
77 // (SqliteConnection)((ICloneable)connection).Clone();
78 //newConnection.Open();
79
80 //cmd.Connection = newConnection;
81 cmd.Connection = connection;
82 //Console.WriteLine("XXX " + cmd.CommandText);
71 83
72 cmd.Connection = newConnection; 84 return cmd.ExecuteReader();
73 return cmd.ExecuteReader(); 85 }
74 } 86 }
75 87
76 protected void CloseReaderCommand(SqliteCommand cmd) 88 protected void CloseCommand(SqliteCommand cmd)
77 { 89 {
78 cmd.Connection.Close(); 90 cmd.Connection.Close();
79 cmd.Connection.Dispose(); 91 cmd.Connection.Dispose();
diff --git a/OpenSim/Data/SQLite/SQLiteFriendsData.cs b/OpenSim/Data/SQLite/SQLiteFriendsData.cs
new file mode 100644
index 0000000..b06853c
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteFriendsData.cs
@@ -0,0 +1,70 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.Sqlite;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteFriendsData : SQLiteGenericTableHandler<FriendsData>, IFriendsData
39 {
40 public SQLiteFriendsData(string connectionString, string realm)
41 : base(connectionString, realm, "FriendsStore")
42 {
43 }
44
45 public FriendsData[] GetFriends(UUID userID)
46 {
47 SqliteCommand cmd = new SqliteCommand();
48
49 cmd.CommandText = String.Format("select a.*,case when b.Flags is null then -1 else b.Flags end as TheirFlags from {0} as a left join {0} as b on a.PrincipalID = b.Friend and a.Friend = b.PrincipalID where a.PrincipalID = :PrincipalID", m_Realm);
50 cmd.Parameters.AddWithValue(":PrincipalID", userID.ToString());
51
52 return DoQuery(cmd);
53
54 }
55
56 public bool Delete(UUID principalID, string friend)
57 {
58 SqliteCommand cmd = new SqliteCommand();
59
60 cmd.CommandText = String.Format("delete from {0} where PrincipalID = :PrincipalID and Friend = :Friend", m_Realm);
61 cmd.Parameters.AddWithValue(":PrincipalID", principalID.ToString());
62 cmd.Parameters.AddWithValue(":Friend", friend);
63
64 ExecuteNonQuery(cmd, cmd.Connection);
65
66 return true;
67 }
68
69 }
70}
diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
index 8e91693..9b8e2fa 100644
--- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
+++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
@@ -30,7 +30,7 @@ using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Reflection; 31using System.Reflection;
32using log4net; 32using log4net;
33using Mono.Data.SqliteClient; 33using Mono.Data.Sqlite;
34using OpenMetaverse; 34using OpenMetaverse;
35using OpenSim.Framework; 35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces; 36using OpenSim.Region.Framework.Interfaces;
@@ -48,16 +48,35 @@ namespace OpenSim.Data.SQLite
48 protected string m_Realm; 48 protected string m_Realm;
49 protected FieldInfo m_DataField = null; 49 protected FieldInfo m_DataField = null;
50 50
51 protected static SqliteConnection m_Connection;
52 private static bool m_initialized;
53
51 public SQLiteGenericTableHandler(string connectionString, 54 public SQLiteGenericTableHandler(string connectionString,
52 string realm, string storeName) : base(connectionString) 55 string realm, string storeName) : base(connectionString)
53 { 56 {
54 m_Realm = realm; 57 m_Realm = realm;
55 if (storeName != String.Empty) 58
59 if (!m_initialized)
56 { 60 {
57 Assembly assem = GetType().Assembly; 61 m_Connection = new SqliteConnection(connectionString);
62 //Console.WriteLine(string.Format("OPENING CONNECTION FOR {0} USING {1}", storeName, connectionString));
63 m_Connection.Open();
64
65 if (storeName != String.Empty)
66 {
67 Assembly assem = GetType().Assembly;
68 //SqliteConnection newConnection =
69 // (SqliteConnection)((ICloneable)m_Connection).Clone();
70 //newConnection.Open();
71
72 //Migration m = new Migration(newConnection, assem, storeName);
73 Migration m = new Migration(m_Connection, assem, storeName);
74 m.Update();
75 //newConnection.Close();
76 //newConnection.Dispose();
77 }
58 78
59 Migration m = new Migration(m_Connection, assem, storeName); 79 m_initialized = true;
60 m.Update();
61 } 80 }
62 81
63 Type t = typeof(T); 82 Type t = typeof(T);
@@ -125,7 +144,7 @@ namespace OpenSim.Data.SQLite
125 144
126 protected T[] DoQuery(SqliteCommand cmd) 145 protected T[] DoQuery(SqliteCommand cmd)
127 { 146 {
128 IDataReader reader = ExecuteReader(cmd); 147 IDataReader reader = ExecuteReader(cmd, m_Connection);
129 if (reader == null) 148 if (reader == null)
130 return new T[0]; 149 return new T[0];
131 150
@@ -180,7 +199,7 @@ namespace OpenSim.Data.SQLite
180 result.Add(row); 199 result.Add(row);
181 } 200 }
182 201
183 CloseReaderCommand(cmd); 202 //CloseCommand(cmd);
184 203
185 return result.ToArray(); 204 return result.ToArray();
186 } 205 }
@@ -229,7 +248,7 @@ namespace OpenSim.Data.SQLite
229 248
230 cmd.CommandText = query; 249 cmd.CommandText = query;
231 250
232 if (ExecuteNonQuery(cmd) > 0) 251 if (ExecuteNonQuery(cmd, m_Connection) > 0)
233 return true; 252 return true;
234 253
235 return false; 254 return false;
@@ -242,7 +261,7 @@ namespace OpenSim.Data.SQLite
242 cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field); 261 cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field);
243 cmd.Parameters.Add(new SqliteParameter(field, val)); 262 cmd.Parameters.Add(new SqliteParameter(field, val));
244 263
245 if (ExecuteNonQuery(cmd) > 0) 264 if (ExecuteNonQuery(cmd, m_Connection) > 0)
246 return true; 265 return true;
247 266
248 return false; 267 return false;
diff --git a/OpenSim/Data/SQLite/SQLiteGridData.cs b/OpenSim/Data/SQLite/SQLiteGridData.cs
deleted file mode 100644
index 18abb88..0000000
--- a/OpenSim/Data/SQLite/SQLiteGridData.cs
+++ /dev/null
@@ -1,286 +0,0 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using OpenMetaverse;
34using OpenSim.Framework;
35
36namespace OpenSim.Data.SQLite
37{
38 /// <summary>
39 /// A Grid Interface to the SQLite database
40 /// </summary>
41 public class SQLiteGridData : GridDataBase
42 {
43 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
44
45 /// <summary>
46 /// SQLite database manager
47 /// </summary>
48 private SQLiteManager database;
49
50 override public void Initialise()
51 {
52 m_log.Info("[SQLite]: " + Name + " cannot be default-initialized!");
53 throw new PluginNotInitialisedException (Name);
54 }
55
56 /// <summary>
57 /// <list type="bullet">
58 /// <item>Initialises Inventory interface</item>
59 /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
60 /// <item>use default URI if connect string is empty.</item>
61 /// </list>
62 /// </summary>
63 /// <param name="dbconnect">connect string</param>
64 override public void Initialise(string connect)
65 {
66 database = new SQLiteManager(connect);
67 }
68
69 /// <summary>
70 /// Shuts down the grid interface
71 /// </summary>
72 override public void Dispose()
73 {
74 database.Close();
75 }
76
77 /// <summary>
78 /// Returns the name of this grid interface
79 /// </summary>
80 /// <returns>A string containing the grid interface</returns>
81 override public string Name
82 {
83 get { return "SQLite OpenGridData"; }
84 }
85
86 /// <summary>
87 /// Returns the version of this grid interface
88 /// </summary>
89 /// <returns>A string containing the version</returns>
90 override public string Version
91 {
92 get { return "0.1"; }
93 }
94
95 /// <summary>
96 /// Returns a list of regions within the specified ranges
97 /// </summary>
98 /// <param name="a">minimum X coordinate</param>
99 /// <param name="b">minimum Y coordinate</param>
100 /// <param name="c">maximum X coordinate</param>
101 /// <param name="d">maximum Y coordinate</param>
102 /// <returns>An array of region profiles</returns>
103 /// <remarks>NOT IMPLEMENTED ? always return null</remarks>
104 override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d)
105 {
106 return null;
107 }
108
109
110 /// <summary>
111 /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
112 /// </summary>
113 /// <param name="name">The name to match against</param>
114 /// <param name="maxNum">Maximum number of profiles to return</param>
115 /// <returns>A list of sim profiles</returns>
116 override public List<RegionProfileData> GetRegionsByName (string namePrefix, uint maxNum)
117 {
118 return null;
119 }
120
121 /// <summary>
122 /// Returns a sim profile from it's handle
123 /// </summary>
124 /// <param name="handle">Region location handle</param>
125 /// <returns>Sim profile</returns>
126 override public RegionProfileData GetProfileByHandle(ulong handle)
127 {
128 Dictionary<string, string> param = new Dictionary<string, string>();
129 param["handle"] = handle.ToString();
130
131 IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param);
132 IDataReader reader = result.ExecuteReader();
133
134 RegionProfileData row = database.getRow(reader);
135 reader.Close();
136 result.Dispose();
137
138 return row;
139 }
140
141 /// <summary>
142 /// Returns a sim profile from it's Region name string
143 /// </summary>
144 /// <param name="regionName">The region name search query</param>
145 /// <returns>The sim profile</returns>
146 override public RegionProfileData GetProfileByString(string regionName)
147 {
148 if (regionName.Length > 2)
149 {
150 Dictionary<string, string> param = new Dictionary<string, string>();
151 // Add % because this is a like query.
152 param["?regionName"] = regionName + "%";
153 // Only returns one record or no record.
154 IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName LIMIT 1", param);
155 IDataReader reader = result.ExecuteReader();
156
157 RegionProfileData row = database.getRow(reader);
158 reader.Close();
159 result.Dispose();
160
161 return row;
162 }
163 else
164 {
165 //m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters");
166 return null;
167 }
168 }
169
170 /// <summary>
171 /// Returns a sim profile from it's UUID
172 /// </summary>
173 /// <param name="uuid">The region UUID</param>
174 /// <returns>The sim profile</returns>
175 override public RegionProfileData GetProfileByUUID(UUID uuid)
176 {
177 Dictionary<string, string> param = new Dictionary<string, string>();
178 param["uuid"] = uuid.ToString();
179
180 IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param);
181 IDataReader reader = result.ExecuteReader();
182
183 RegionProfileData row = database.getRow(reader);
184 reader.Close();
185 result.Dispose();
186
187 return row;
188 }
189
190 /// <summary>
191 /// Returns a list of avatar and UUIDs that match the query
192 /// </summary>
193 /// <remarks>do nothing yet</remarks>
194 public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
195 {
196 //Do nothing yet
197 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
198 return returnlist;
199 }
200
201 /// <summary>
202 /// Adds a new specified region to the database
203 /// </summary>
204 /// <param name="profile">The profile to add</param>
205 /// <returns>A dataresponse enum indicating success</returns>
206 override public DataResponse StoreProfile(RegionProfileData profile)
207 {
208 if (database.insertRow(profile))
209 {
210 return DataResponse.RESPONSE_OK;
211 }
212 else
213 {
214 return DataResponse.RESPONSE_ERROR;
215 }
216 }
217
218 /// <summary>
219 /// Deletes a sim profile from the database
220 /// </summary>
221 /// <param name="uuid">the sim UUID</param>
222 /// <returns>Successful?</returns>
223 override public DataResponse DeleteProfile(string uuid)
224 {
225 Dictionary<string, string> param = new Dictionary<string, string>();
226 param["uuid"] = uuid;
227
228 IDbCommand result = database.Query("DELETE FROM regions WHERE uuid = @uuid", param);
229 if (result.ExecuteNonQuery() > 0)
230 {
231 return DataResponse.RESPONSE_OK;
232 }
233 return DataResponse.RESPONSE_ERROR;
234 }
235
236 /// <summary>
237 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
238 /// </summary>
239 /// <param name="uuid">The UUID of the challenger</param>
240 /// <param name="handle">The attempted regionHandle of the challenger</param>
241 /// <param name="authkey">The secret</param>
242 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
243 override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
244 {
245 bool throwHissyFit = false; // Should be true by 1.0
246
247 if (throwHissyFit)
248 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
249
250 RegionProfileData data = GetProfileByUUID(uuid);
251
252 return (handle == data.regionHandle && authkey == data.regionSecret);
253 }
254
255 /// <summary>
256 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
257 /// </summary>
258 /// <remarks>This requires a security audit.</remarks>
259 /// <param name="uuid"></param>
260 /// <param name="handle"></param>
261 /// <param name="authhash"></param>
262 /// <param name="challenge"></param>
263 /// <returns></returns>
264 public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
265 {
266 // SHA512Managed HashProvider = new SHA512Managed();
267 // Encoding TextProvider = new UTF8Encoding();
268
269 // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
270 // byte[] hash = HashProvider.ComputeHash(stream);
271
272 return false;
273 }
274
275 /// <summary>
276 /// NOT IMPLEMENTED
277 /// </summary>
278 /// <param name="x">x coordinate</param>
279 /// <param name="y">y coordinate</param>
280 /// <returns>always return null</returns>
281 override public ReservationData GetReservationAtPoint(uint x, uint y)
282 {
283 return null;
284 }
285 }
286}
diff --git a/OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs b/OpenSim/Data/SQLite/SQLiteGridUserData.cs
index c9953c5..1bb5ed8 100644
--- a/OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs
+++ b/OpenSim/Data/SQLite/SQLiteGridUserData.cs
@@ -25,40 +25,37 @@
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */ 26 */
27 27
28using System.IO; 28using System;
29using NUnit.Framework; 29using System.Collections.Generic;
30using OpenSim.Data.Tests; 30using System.Data;
31using OpenSim.Tests.Common; 31using System.Reflection;
32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
32 36
33namespace OpenSim.Data.SQLite.Tests 37namespace OpenSim.Data.SQLite
34{ 38{
35 [TestFixture, DatabaseTest] 39 /// <summary>
36 public class SQLiteUserTest : BasicUserTest 40 /// A SQL Interface for user grid data
41 /// </summary>
42 public class SQLiteGridUserData : SQLiteGenericTableHandler<GridUserData>, IGridUserData
37 { 43 {
38 public string file; 44// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
39 public string connect;
40
41 [TestFixtureSetUp]
42 public void Init()
43 {
44 // SQLite doesn't work on power or z linux
45 if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd"))
46 {
47 Assert.Ignore();
48 }
49 45
50 SuperInit(); 46 public SQLiteGridUserData(string connectionString, string realm)
51 file = Path.GetTempFileName() + ".db"; 47 : base(connectionString, realm, "GridUserStore") {}
52 connect = "URI=file:" + file + ",version=3";
53 db = new SQLiteUserData();
54 db.Initialise(connect);
55 }
56 48
57 [TestFixtureTearDown] 49 public new GridUserData Get(string userID)
58 public void Cleanup()
59 { 50 {
60 db.Dispose(); 51 GridUserData[] ret = Get("UserID", userID);
61 File.Delete(file); 52
53 if (ret.Length == 0)
54 return null;
55
56 return ret[0];
62 } 57 }
58
59
63 } 60 }
64} 61} \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
index 64591fd..ece2495 100644
--- a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
+++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
@@ -30,7 +30,7 @@ using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Reflection; 31using System.Reflection;
32using log4net; 32using log4net;
33using Mono.Data.SqliteClient; 33using Mono.Data.Sqlite;
34using OpenMetaverse; 34using OpenMetaverse;
35using OpenSim.Framework; 35using OpenSim.Framework;
36 36
@@ -46,10 +46,12 @@ namespace OpenSim.Data.SQLite
46 private const string invItemsSelect = "select * from inventoryitems"; 46 private const string invItemsSelect = "select * from inventoryitems";
47 private const string invFoldersSelect = "select * from inventoryfolders"; 47 private const string invFoldersSelect = "select * from inventoryfolders";
48 48
49 private SqliteConnection conn; 49 private static SqliteConnection conn;
50 private DataSet ds; 50 private static DataSet ds;
51 private SqliteDataAdapter invItemsDa; 51 private static SqliteDataAdapter invItemsDa;
52 private SqliteDataAdapter invFoldersDa; 52 private static SqliteDataAdapter invFoldersDa;
53
54 private static bool m_Initialized = false;
53 55
54 public void Initialise() 56 public void Initialise()
55 { 57 {
@@ -67,39 +69,46 @@ namespace OpenSim.Data.SQLite
67 /// <param name="dbconnect">connect string</param> 69 /// <param name="dbconnect">connect string</param>
68 public void Initialise(string dbconnect) 70 public void Initialise(string dbconnect)
69 { 71 {
70 if (dbconnect == string.Empty) 72 if (!m_Initialized)
71 { 73 {
72 dbconnect = "URI=file:inventoryStore.db,version=3"; 74 m_Initialized = true;
73 } 75
74 m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect); 76 if (dbconnect == string.Empty)
75 conn = new SqliteConnection(dbconnect); 77 {
78 dbconnect = "URI=file:inventoryStore.db,version=3";
79 }
80 m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
81 conn = new SqliteConnection(dbconnect);
76 82
77 conn.Open(); 83 conn.Open();
78 84
79 Assembly assem = GetType().Assembly; 85 Assembly assem = GetType().Assembly;
80 Migration m = new Migration(conn, assem, "InventoryStore"); 86 Migration m = new Migration(conn, assem, "InventoryStore");
81 m.Update(); 87 m.Update();
82 88
83 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn); 89 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
84 invItemsDa = new SqliteDataAdapter(itemsSelectCmd); 90 invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
85 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); 91 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
86 92
87 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); 93 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
88 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd); 94 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
89 95
90 ds = new DataSet(); 96 ds = new DataSet();
91 97
92 ds.Tables.Add(createInventoryFoldersTable()); 98 ds.Tables.Add(createInventoryFoldersTable());
93 invFoldersDa.Fill(ds.Tables["inventoryfolders"]); 99 invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
94 setupFoldersCommands(invFoldersDa, conn); 100 setupFoldersCommands(invFoldersDa, conn);
95 m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions"); 101 CreateDataSetMapping(invFoldersDa, "inventoryfolders");
102 m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
96 103
97 ds.Tables.Add(createInventoryItemsTable()); 104 ds.Tables.Add(createInventoryItemsTable());
98 invItemsDa.Fill(ds.Tables["inventoryitems"]); 105 invItemsDa.Fill(ds.Tables["inventoryitems"]);
99 setupItemsCommands(invItemsDa, conn); 106 setupItemsCommands(invItemsDa, conn);
100 m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions"); 107 CreateDataSetMapping(invItemsDa, "inventoryitems");
108 m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
101 109
102 ds.AcceptChanges(); 110 ds.AcceptChanges();
111 }
103 } 112 }
104 113
105 /// <summary> 114 /// <summary>
@@ -721,6 +730,15 @@ namespace OpenSim.Data.SQLite
721 * 730 *
722 **********************************************************************/ 731 **********************************************************************/
723 732
733 protected void CreateDataSetMapping(IDataAdapter da, string tableName)
734 {
735 ITableMapping dbMapping = da.TableMappings.Add(tableName, tableName);
736 foreach (DataColumn col in ds.Tables[tableName].Columns)
737 {
738 dbMapping.ColumnMappings.Add(col.ColumnName, col.ColumnName);
739 }
740 }
741
724 /// <summary> 742 /// <summary>
725 /// Create the "inventoryitems" table 743 /// Create the "inventoryitems" table
726 /// </summary> 744 /// </summary>
diff --git a/OpenSim/Data/SQLite/SQLiteManager.cs b/OpenSim/Data/SQLite/SQLiteManager.cs
deleted file mode 100644
index b6d4a1c..0000000
--- a/OpenSim/Data/SQLite/SQLiteManager.cs
+++ /dev/null
@@ -1,225 +0,0 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SQLite;
32using System.Reflection;
33using log4net;
34using OpenMetaverse;
35
36namespace OpenSim.Data.SQLite
37{
38 /// <summary>
39 /// SQLite Manager
40 /// </summary>
41 internal class SQLiteManager : SQLiteUtil
42 {
43 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
44
45 private IDbConnection dbcon;
46
47 /// <summary>
48 /// <list type="bullet">
49 /// <item>Initialises and creates a new SQLite connection and maintains it.</item>
50 /// <item>use default URI if connect string is empty.</item>
51 /// </list>
52 /// </summary>
53 /// <param name="connect">connect string</param>
54 public SQLiteManager(string connect)
55 {
56 try
57 {
58 string connectionString = String.Empty;
59 if (connect != String.Empty)
60 {
61 connectionString = connect;
62 }
63 else
64 {
65 m_log.Warn("[SQLITE] grid db not specified, using default");
66 connectionString = "URI=file:GridServerSqlite.db;";
67 }
68
69 dbcon = new SQLiteConnection(connectionString);
70
71 dbcon.Open();
72 }
73 catch (Exception e)
74 {
75 throw new Exception("Error initialising SQLite Database: " + e.ToString());
76 }
77 }
78
79 /// <summary>
80 /// Shuts down the database connection
81 /// </summary>
82 public void Close()
83 {
84 dbcon.Close();
85 dbcon = null;
86 }
87
88 /// <summary>
89 /// Runs a query with protection against SQL Injection by using parameterised input.
90 /// </summary>
91 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
92 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
93 /// <returns>A SQLite DB Command</returns>
94 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
95 {
96 SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand();
97 dbcommand.CommandText = sql;
98 foreach (KeyValuePair<string, string> param in parameters)
99 {
100 SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value);
101 dbcommand.Parameters.Add(paramx);
102 }
103
104 return (IDbCommand) dbcommand;
105 }
106
107 /// <summary>
108 /// Reads a region row from a database reader
109 /// </summary>
110 /// <param name="reader">An active database reader</param>
111 /// <returns>A region profile</returns>
112 public RegionProfileData getRow(IDataReader reader)
113 {
114 RegionProfileData retval = new RegionProfileData();
115
116 if (reader.Read())
117 {
118 // Region Main
119 retval.regionHandle = (ulong) reader["regionHandle"];
120 retval.regionName = (string) reader["regionName"];
121 retval.UUID = new UUID((string) reader["uuid"]);
122
123 // Secrets
124 retval.regionRecvKey = (string) reader["regionRecvKey"];
125 retval.regionSecret = (string) reader["regionSecret"];
126 retval.regionSendKey = (string) reader["regionSendKey"];
127
128 // Region Server
129 retval.regionDataURI = (string) reader["regionDataURI"];
130 retval.regionOnline = false; // Needs to be pinged before this can be set.
131 retval.serverIP = (string) reader["serverIP"];
132 retval.serverPort = (uint) reader["serverPort"];
133 retval.serverURI = (string) reader["serverURI"];
134
135 // Location
136 retval.regionLocX = (uint) ((int) reader["locX"]);
137 retval.regionLocY = (uint) ((int) reader["locY"]);
138 retval.regionLocZ = (uint) ((int) reader["locZ"]);
139
140 // Neighbours - 0 = No Override
141 retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"];
142 retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"];
143 retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"];
144 retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"];
145
146 // Assets
147 retval.regionAssetURI = (string) reader["regionAssetURI"];
148 retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
149 retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
150
151 // Userserver
152 retval.regionUserURI = (string) reader["regionUserURI"];
153 retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
154 retval.regionUserSendKey = (string) reader["regionUserSendKey"];
155 }
156 else
157 {
158 throw new Exception("No rows to return");
159 }
160 return retval;
161 }
162
163 /// <summary>
164 /// Inserts a new region into the database
165 /// </summary>
166 /// <param name="profile">The region to insert</param>
167 /// <returns>Success?</returns>
168 public bool insertRow(RegionProfileData profile)
169 {
170 string sql =
171 "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
172 sql +=
173 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
174 sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES ";
175
176 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
177 sql +=
178 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
179 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
180
181 Dictionary<string, string> parameters = new Dictionary<string, string>();
182
183 parameters["regionHandle"] = profile.regionHandle.ToString();
184 parameters["regionName"] = profile.regionName;
185 parameters["uuid"] = profile.UUID.ToString();
186 parameters["regionRecvKey"] = profile.regionRecvKey;
187 parameters["regionSendKey"] = profile.regionSendKey;
188 parameters["regionDataURI"] = profile.regionDataURI;
189 parameters["serverIP"] = profile.serverIP;
190 parameters["serverPort"] = profile.serverPort.ToString();
191 parameters["serverURI"] = profile.serverURI;
192 parameters["locX"] = profile.regionLocX.ToString();
193 parameters["locY"] = profile.regionLocY.ToString();
194 parameters["locZ"] = profile.regionLocZ.ToString();
195 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
196 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
197 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
198 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
199 parameters["regionAssetURI"] = profile.regionAssetURI;
200 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
201 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
202 parameters["regionUserURI"] = profile.regionUserURI;
203 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
204 parameters["regionUserSendKey"] = profile.regionUserSendKey;
205
206 bool returnval = false;
207
208 try
209 {
210 IDbCommand result = Query(sql, parameters);
211
212 if (result.ExecuteNonQuery() == 1)
213 returnval = true;
214
215 result.Dispose();
216 }
217 catch (Exception)
218 {
219 return false;
220 }
221
222 return returnval;
223 }
224 }
225}
diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs
index 81c0703..85703dc 100644
--- a/OpenSim/Data/SQLite/SQLiteRegionData.cs
+++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs
@@ -32,7 +32,7 @@ using System.Drawing;
32using System.IO; 32using System.IO;
33using System.Reflection; 33using System.Reflection;
34using log4net; 34using log4net;
35using Mono.Data.SqliteClient; 35using Mono.Data.Sqlite;
36using OpenMetaverse; 36using OpenMetaverse;
37using OpenSim.Framework; 37using OpenSim.Framework;
38using OpenSim.Region.Framework.Interfaces; 38using OpenSim.Region.Framework.Interfaces;
@@ -87,119 +87,142 @@ namespace OpenSim.Data.SQLite
87 /// <param name="connectionString">the connection string</param> 87 /// <param name="connectionString">the connection string</param>
88 public void Initialise(string connectionString) 88 public void Initialise(string connectionString)
89 { 89 {
90 m_connectionString = connectionString; 90 try
91 {
92 m_connectionString = connectionString;
91 93
92 ds = new DataSet(); 94 ds = new DataSet("Region");
93 95
94 m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString); 96 m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString);
95 m_conn = new SqliteConnection(m_connectionString); 97 m_conn = new SqliteConnection(m_connectionString);
96 m_conn.Open(); 98 m_conn.Open();
97 99
100 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
101 primDa = new SqliteDataAdapter(primSelectCmd);
98 102
103 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
104 shapeDa = new SqliteDataAdapter(shapeSelectCmd);
105 // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
99 106
100 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn); 107 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
101 primDa = new SqliteDataAdapter(primSelectCmd); 108 itemsDa = new SqliteDataAdapter(itemsSelectCmd);
102 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
103 109
104 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn); 110 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
105 shapeDa = new SqliteDataAdapter(shapeSelectCmd); 111 terrainDa = new SqliteDataAdapter(terrainSelectCmd);
106 // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
107 112
108 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn); 113 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
109 itemsDa = new SqliteDataAdapter(itemsSelectCmd); 114 landDa = new SqliteDataAdapter(landSelectCmd);
110 115
111 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn); 116 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
112 terrainDa = new SqliteDataAdapter(terrainSelectCmd); 117 landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
113 118
114 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn); 119 SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
115 landDa = new SqliteDataAdapter(landSelectCmd); 120 regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);
121 // This actually does the roll forward assembly stuff
122 Assembly assem = GetType().Assembly;
123 Migration m = new Migration(m_conn, assem, "RegionStore");
124 m.Update();
116 125
117 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn); 126 lock (ds)
118 landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd); 127 {
128 ds.Tables.Add(createPrimTable());
129 setupPrimCommands(primDa, m_conn);
119 130
120 SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn); 131 ds.Tables.Add(createShapeTable());
121 regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd); 132 setupShapeCommands(shapeDa, m_conn);
122 // This actually does the roll forward assembly stuff
123 Assembly assem = GetType().Assembly;
124 Migration m = new Migration(m_conn, assem, "RegionStore");
125 m.Update();
126 133
127 lock (ds) 134 ds.Tables.Add(createItemsTable());
128 { 135 setupItemsCommands(itemsDa, m_conn);
129 ds.Tables.Add(createPrimTable());
130 setupPrimCommands(primDa, m_conn);
131 primDa.Fill(ds.Tables["prims"]);
132 136
133 ds.Tables.Add(createShapeTable()); 137 ds.Tables.Add(createTerrainTable());
134 setupShapeCommands(shapeDa, m_conn); 138 setupTerrainCommands(terrainDa, m_conn);
135 139
136 ds.Tables.Add(createItemsTable()); 140 ds.Tables.Add(createLandTable());
137 setupItemsCommands(itemsDa, m_conn); 141 setupLandCommands(landDa, m_conn);
138 itemsDa.Fill(ds.Tables["primitems"]);
139 142
140 ds.Tables.Add(createTerrainTable()); 143 ds.Tables.Add(createLandAccessListTable());
141 setupTerrainCommands(terrainDa, m_conn); 144 setupLandAccessCommands(landAccessListDa, m_conn);
142 145
143 ds.Tables.Add(createLandTable()); 146 ds.Tables.Add(createRegionSettingsTable());
144 setupLandCommands(landDa, m_conn); 147 setupRegionSettingsCommands(regionSettingsDa, m_conn);
145 148
146 ds.Tables.Add(createLandAccessListTable()); 149 // WORKAROUND: This is a work around for sqlite on
147 setupLandAccessCommands(landAccessListDa, m_conn); 150 // windows, which gets really unhappy with blob columns
151 // that have no sample data in them. At some point we
152 // need to actually find a proper way to handle this.
153 try
154 {
155 primDa.Fill(ds.Tables["prims"]);
156 }
157 catch (Exception)
158 {
159 m_log.Info("[REGION DB]: Caught fill error on prims table");
160 }
148 161
149 ds.Tables.Add(createRegionSettingsTable()); 162 try
150 163 {
151 setupRegionSettingsCommands(regionSettingsDa, m_conn); 164 shapeDa.Fill(ds.Tables["primshapes"]);
165 }
166 catch (Exception)
167 {
168 m_log.Info("[REGION DB]: Caught fill error on primshapes table");
169 }
152 170
153 // WORKAROUND: This is a work around for sqlite on 171 try
154 // windows, which gets really unhappy with blob columns 172 {
155 // that have no sample data in them. At some point we 173 terrainDa.Fill(ds.Tables["terrain"]);
156 // need to actually find a proper way to handle this. 174 }
157 try 175 catch (Exception)
158 { 176 {
159 shapeDa.Fill(ds.Tables["primshapes"]); 177 m_log.Info("[REGION DB]: Caught fill error on terrain table");
160 } 178 }
161 catch (Exception)
162 {
163 m_log.Info("[REGION DB]: Caught fill error on primshapes table");
164 }
165 179
166 try 180 try
167 { 181 {
168 terrainDa.Fill(ds.Tables["terrain"]); 182 landDa.Fill(ds.Tables["land"]);
169 } 183 }
170 catch (Exception) 184 catch (Exception)
171 { 185 {
172 m_log.Info("[REGION DB]: Caught fill error on terrain table"); 186 m_log.Info("[REGION DB]: Caught fill error on land table");
173 } 187 }
174 188
175 try 189 try
176 { 190 {
177 landDa.Fill(ds.Tables["land"]); 191 landAccessListDa.Fill(ds.Tables["landaccesslist"]);
178 } 192 }
179 catch (Exception) 193 catch (Exception)
180 { 194 {
181 m_log.Info("[REGION DB]: Caught fill error on land table"); 195 m_log.Info("[REGION DB]: Caught fill error on landaccesslist table");
182 } 196 }
183 197
184 try 198 try
185 { 199 {
186 landAccessListDa.Fill(ds.Tables["landaccesslist"]); 200 regionSettingsDa.Fill(ds.Tables["regionsettings"]);
187 } 201 }
188 catch (Exception) 202 catch (Exception)
189 { 203 {
190 m_log.Info("[REGION DB]: Caught fill error on landaccesslist table"); 204 m_log.Info("[REGION DB]: Caught fill error on regionsettings table");
191 } 205 }
192 206
193 try 207 // We have to create a data set mapping for every table, otherwise the IDataAdaptor.Update() will not populate rows with values!
194 { 208 // Not sure exactly why this is - this kind of thing was not necessary before - justincc 20100409
195 regionSettingsDa.Fill(ds.Tables["regionsettings"]); 209 // Possibly because we manually set up our own DataTables before connecting to the database
196 } 210 CreateDataSetMapping(primDa, "prims");
197 catch (Exception) 211 CreateDataSetMapping(shapeDa, "primshapes");
198 { 212 CreateDataSetMapping(itemsDa, "primitems");
199 m_log.Info("[REGION DB]: Caught fill error on regionsettings table"); 213 CreateDataSetMapping(terrainDa, "terrain");
214 CreateDataSetMapping(landDa, "land");
215 CreateDataSetMapping(landAccessListDa, "landaccesslist");
216 CreateDataSetMapping(regionSettingsDa, "regionsettings");
200 } 217 }
201 return;
202 } 218 }
219 catch (Exception e)
220 {
221 m_log.Error(e);
222 Environment.Exit(23);
223 }
224
225 return;
203 } 226 }
204 227
205 public void Dispose() 228 public void Dispose()
@@ -272,13 +295,13 @@ namespace OpenSim.Data.SQLite
272 Commit(); 295 Commit();
273 } 296 }
274 } 297 }
275 public RegionMeta7WindlightData LoadRegionWindlightSettings(UUID regionUUID) 298 public RegionLightShareData LoadRegionWindlightSettings(UUID regionUUID)
276 { 299 {
277 //This connector doesn't support the windlight module yet 300 //This connector doesn't support the windlight module yet
278 //Return default LL windlight settings 301 //Return default LL windlight settings
279 return new RegionMeta7WindlightData(); 302 return new RegionLightShareData();
280 } 303 }
281 public void StoreRegionWindlightSettings(RegionMeta7WindlightData wl) 304 public void StoreRegionWindlightSettings(RegionLightShareData wl)
282 { 305 {
283 //This connector doesn't support the windlight module yet 306 //This connector doesn't support the windlight module yet
284 } 307 }
@@ -603,7 +626,7 @@ namespace OpenSim.Data.SQLite
603 } 626 }
604 } 627 }
605 } 628 }
606 rev = (int) row["Revision"]; 629 rev = Convert.ToInt32(row["Revision"]);
607 } 630 }
608 else 631 else
609 { 632 {
@@ -755,6 +778,7 @@ namespace OpenSim.Data.SQLite
755 /// </summary> 778 /// </summary>
756 public void Commit() 779 public void Commit()
757 { 780 {
781 //m_log.Debug("[SQLITE]: Starting commit");
758 lock (ds) 782 lock (ds)
759 { 783 {
760 primDa.Update(ds, "prims"); 784 primDa.Update(ds, "prims");
@@ -769,18 +793,11 @@ namespace OpenSim.Data.SQLite
769 { 793 {
770 regionSettingsDa.Update(ds, "regionsettings"); 794 regionSettingsDa.Update(ds, "regionsettings");
771 } 795 }
772 catch (SqliteExecutionException SqlEx) 796 catch (SqliteException SqlEx)
773 { 797 {
774 if (SqlEx.Message.Contains("logic error")) 798 throw new Exception(
775 { 799 "There was a SQL error or connection string configuration error when saving the region settings. This could be a bug, it could also happen if ConnectionString is defined in the [DatabaseService] section of StandaloneCommon.ini in the config_include folder. This could also happen if the config_include folder doesn't exist or if the OpenSim.ini [Architecture] section isn't set. If this is your first time running OpenSimulator, please restart the simulator and bug a developer to fix this!",
776 throw new Exception( 800 SqlEx);
777 "There was a SQL error or connection string configuration error when saving the region settings. This could be a bug, it could also happen if ConnectionString is defined in the [DatabaseService] section of StandaloneCommon.ini in the config_include folder. This could also happen if the config_include folder doesn't exist or if the OpenSim.ini [Architecture] section isn't set. If this is your first time running OpenSimulator, please restart the simulator and bug a developer to fix this!",
778 SqlEx);
779 }
780 else
781 {
782 throw SqlEx;
783 }
784 } 801 }
785 ds.AcceptChanges(); 802 ds.AcceptChanges();
786 } 803 }
@@ -802,6 +819,15 @@ namespace OpenSim.Data.SQLite
802 * 819 *
803 **********************************************************************/ 820 **********************************************************************/
804 821
822 protected void CreateDataSetMapping(IDataAdapter da, string tableName)
823 {
824 ITableMapping dbMapping = da.TableMappings.Add(tableName, tableName);
825 foreach (DataColumn col in ds.Tables[tableName].Columns)
826 {
827 dbMapping.ColumnMappings.Add(col.ColumnName, col.ColumnName);
828 }
829 }
830
805 /// <summary> 831 /// <summary>
806 /// 832 ///
807 /// </summary> 833 /// </summary>
@@ -1130,6 +1156,7 @@ namespace OpenSim.Data.SQLite
1130 createCol(regionsettings, "fixed_sun", typeof (Int32)); 1156 createCol(regionsettings, "fixed_sun", typeof (Int32));
1131 createCol(regionsettings, "sun_position", typeof (Double)); 1157 createCol(regionsettings, "sun_position", typeof (Double));
1132 createCol(regionsettings, "covenant", typeof(String)); 1158 createCol(regionsettings, "covenant", typeof(String));
1159 createCol(regionsettings, "map_tile_ID", typeof(String));
1133 regionsettings.PrimaryKey = new DataColumn[] { regionsettings.Columns["regionUUID"] }; 1160 regionsettings.PrimaryKey = new DataColumn[] { regionsettings.Columns["regionUUID"] };
1134 return regionsettings; 1161 return regionsettings;
1135 } 1162 }
@@ -1448,6 +1475,7 @@ namespace OpenSim.Data.SQLite
1448 newSettings.FixedSun = Convert.ToBoolean(row["fixed_sun"]); 1475 newSettings.FixedSun = Convert.ToBoolean(row["fixed_sun"]);
1449 newSettings.SunPosition = Convert.ToDouble(row["sun_position"]); 1476 newSettings.SunPosition = Convert.ToDouble(row["sun_position"]);
1450 newSettings.Covenant = new UUID((String) row["covenant"]); 1477 newSettings.Covenant = new UUID((String) row["covenant"]);
1478 newSettings.TerrainImageID = new UUID((String)row["map_tile_ID"]);
1451 1479
1452 return newSettings; 1480 return newSettings;
1453 } 1481 }
@@ -1766,6 +1794,7 @@ namespace OpenSim.Data.SQLite
1766 row["fixed_sun"] = settings.FixedSun; 1794 row["fixed_sun"] = settings.FixedSun;
1767 row["sun_position"] = settings.SunPosition; 1795 row["sun_position"] = settings.SunPosition;
1768 row["covenant"] = settings.Covenant.ToString(); 1796 row["covenant"] = settings.Covenant.ToString();
1797 row["map_tile_ID"] = settings.TerrainImageID.ToString();
1769 } 1798 }
1770 1799
1771 /// <summary> 1800 /// <summary>
@@ -1897,7 +1926,7 @@ namespace OpenSim.Data.SQLite
1897 /// <param name="items"></param> 1926 /// <param name="items"></param>
1898 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items) 1927 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items)
1899 { 1928 {
1900 m_log.InfoFormat("[REGION DB]: Entered StorePrimInventory with prim ID {0}", primID); 1929 //m_log.InfoFormat("[REGION DB]: Entered StorePrimInventory with prim ID {0}", primID);
1901 1930
1902 DataTable dbItems = ds.Tables["primitems"]; 1931 DataTable dbItems = ds.Tables["primitems"];
1903 1932
@@ -1964,6 +1993,7 @@ namespace OpenSim.Data.SQLite
1964 sql += ") values (:"; 1993 sql += ") values (:";
1965 sql += String.Join(", :", cols); 1994 sql += String.Join(", :", cols);
1966 sql += ")"; 1995 sql += ")";
1996 //m_log.DebugFormat("[SQLITE]: Created insert command {0}", sql);
1967 SqliteCommand cmd = new SqliteCommand(sql); 1997 SqliteCommand cmd = new SqliteCommand(sql);
1968 1998
1969 // this provides the binding for all our parameters, so 1999 // this provides the binding for all our parameters, so
@@ -2259,6 +2289,36 @@ namespace OpenSim.Data.SQLite
2259 return DbType.String; 2289 return DbType.String;
2260 } 2290 }
2261 } 2291 }
2292
2293 static void PrintDataSet(DataSet ds)
2294 {
2295 // Print out any name and extended properties.
2296 Console.WriteLine("DataSet is named: {0}", ds.DataSetName);
2297 foreach (System.Collections.DictionaryEntry de in ds.ExtendedProperties)
2298 {
2299 Console.WriteLine("Key = {0}, Value = {1}", de.Key, de.Value);
2300 }
2301 Console.WriteLine();
2302 foreach (DataTable dt in ds.Tables)
2303 {
2304 Console.WriteLine("=> {0} Table:", dt.TableName);
2305 // Print out the column names.
2306 for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
2307 {
2308 Console.Write(dt.Columns[curCol].ColumnName + "\t");
2309 }
2310 Console.WriteLine("\n----------------------------------");
2311 // Print the DataTable.
2312 for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
2313 {
2314 for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
2315 {
2316 Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
2317 }
2318 Console.WriteLine();
2319 }
2320 }
2321 }
2262 2322
2263 } 2323 }
2264} 2324}
diff --git a/OpenSim/Data/SQLite/SQLiteUserAccountData.cs b/OpenSim/Data/SQLite/SQLiteUserAccountData.cs
new file mode 100644
index 0000000..893f105
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserAccountData.cs
@@ -0,0 +1,81 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.Sqlite;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteUserAccountData : SQLiteGenericTableHandler<UserAccountData>, IUserAccountData
39 {
40 public SQLiteUserAccountData(string connectionString, string realm)
41 : base(connectionString, realm, "UserAccount")
42 {
43 }
44
45 public UserAccountData[] GetUsers(UUID scopeID, string query)
46 {
47 string[] words = query.Split(new char[] {' '});
48
49 for (int i = 0 ; i < words.Length ; i++)
50 {
51 if (words[i].Length < 3)
52 {
53 if (i != words.Length - 1)
54 Array.Copy(words, i + 1, words, i, words.Length - i - 1);
55 Array.Resize(ref words, words.Length - 1);
56 }
57 }
58
59 if (words.Length == 0)
60 return new UserAccountData[0];
61
62 if (words.Length > 2)
63 return new UserAccountData[0];
64
65 SqliteCommand cmd = new SqliteCommand();
66
67 if (words.Length == 1)
68 {
69 cmd.CommandText = String.Format("select * from {0} where ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{2}%')",
70 m_Realm, scopeID.ToString(), words[0]);
71 }
72 else
73 {
74 cmd.CommandText = String.Format("select * from {0} where (ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{3}%')",
75 m_Realm, scopeID.ToString(), words[0], words[1]);
76 }
77
78 return DoQuery(cmd);
79 }
80 }
81}
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
deleted file mode 100644
index caddcf8..0000000
--- a/OpenSim/Data/SQLite/SQLiteUserData.cs
+++ /dev/null
@@ -1,1262 +0,0 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using Mono.Data.SqliteClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLite
38{
39 /// <summary>
40 /// A User storage interface for the SQLite database system
41 /// </summary>
42 public class SQLiteUserData : UserDataBase
43 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 /// <summary>
47 /// The database manager
48 /// </summary>
49 /// <summary>
50 /// Artificial constructor called upon plugin load
51 /// </summary>
52 private const string SelectUserByUUID = "select * from users where UUID=:UUID";
53 private const string SelectUserByName = "select * from users where username=:username and surname=:surname";
54 private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID";
55
56 private const string userSelect = "select * from users";
57 private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b";
58 private const string userAgentSelect = "select * from useragents";
59 private const string AvatarAppearanceSelect = "select * from avatarappearance";
60
61 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
62 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
63
64 private DataSet ds;
65 private SqliteDataAdapter da;
66 private SqliteDataAdapter daf;
67 private SqliteDataAdapter dua;
68 private SqliteDataAdapter daa;
69 SqliteConnection g_conn;
70
71 public override void Initialise()
72 {
73 m_log.Info("[SQLiteUserData]: " + Name + " cannot be default-initialized!");
74 throw new PluginNotInitialisedException (Name);
75 }
76
77 /// <summary>
78 /// <list type="bullet">
79 /// <item>Initialises User Interface</item>
80 /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
81 /// <item>use default URI if connect string string is empty.</item>
82 /// </list>
83 /// </summary>
84 /// <param name="connect">connect string</param>
85 override public void Initialise(string connect)
86 {
87 // default to something sensible
88 if (connect == "")
89 connect = "URI=file:userprofiles.db,version=3";
90
91 SqliteConnection conn = new SqliteConnection(connect);
92
93 // This sucks, but It doesn't seem to work with the dataset Syncing :P
94 g_conn = conn;
95 g_conn.Open();
96
97 Assembly assem = GetType().Assembly;
98 Migration m = new Migration(g_conn, assem, "UserStore");
99 m.Update();
100
101
102 ds = new DataSet();
103 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
104 dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn));
105 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
106 daa = new SqliteDataAdapter(new SqliteCommand(AvatarAppearanceSelect, conn));
107 //if (daa == null) m_log.Info("[SQLiteUserData]: daa = null");
108
109 lock (ds)
110 {
111 ds.Tables.Add(createUsersTable());
112 ds.Tables.Add(createUserAgentsTable());
113 ds.Tables.Add(createUserFriendsTable());
114 ds.Tables.Add(createAvatarAppearanceTable());
115
116 setupUserCommands(da, conn);
117 da.Fill(ds.Tables["users"]);
118
119 setupAgentCommands(dua, conn);
120 dua.Fill(ds.Tables["useragents"]);
121
122 setupUserFriendsCommands(daf, conn);
123 daf.Fill(ds.Tables["userfriends"]);
124
125 setupAvatarAppearanceCommands(daa, conn);
126 daa.Fill(ds.Tables["avatarappearance"]);
127 }
128
129 return;
130 }
131
132 public override void Dispose ()
133 {
134 if (g_conn != null)
135 {
136 g_conn.Close();
137 g_conn = null;
138 }
139 if (ds != null)
140 {
141 ds.Dispose();
142 ds = null;
143 }
144 if (da != null)
145 {
146 da.Dispose();
147 da = null;
148 }
149 if (daf != null)
150 {
151 daf.Dispose();
152 daf = null;
153 }
154 if (dua != null)
155 {
156 dua.Dispose();
157 dua = null;
158 }
159 if (daa != null)
160 {
161 daa.Dispose();
162 daa = null;
163 }
164 }
165
166 /// <summary>
167 /// see IUserDataPlugin,
168 /// Get user data profile by UUID
169 /// </summary>
170 /// <param name="uuid">User UUID</param>
171 /// <returns>user profile data</returns>
172 override public UserProfileData GetUserByUUID(UUID uuid)
173 {
174 lock (ds)
175 {
176 DataRow row = ds.Tables["users"].Rows.Find(uuid.ToString());
177 if (row != null)
178 {
179 UserProfileData user = buildUserProfile(row);
180 return user;
181 }
182 else
183 {
184 return null;
185 }
186 }
187 }
188
189 /// <summary>
190 /// see IUserDataPlugin,
191 /// Get user data profile by name
192 /// </summary>
193 /// <param name="fname">first name</param>
194 /// <param name="lname">last name</param>
195 /// <returns>user profile data</returns>
196 override public UserProfileData GetUserByName(string fname, string lname)
197 {
198 string select = "surname = '" + lname + "' and username = '" + fname + "'";
199 lock (ds)
200 {
201 DataRow[] rows = ds.Tables["users"].Select(select);
202 if (rows.Length > 0)
203 {
204 UserProfileData user = buildUserProfile(rows[0]);
205 return user;
206 }
207 else
208 {
209 return null;
210 }
211 }
212 }
213
214 #region User Friends List Data
215
216 private bool ExistsFriend(UUID owner, UUID friend)
217 {
218 string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
219 using (SqliteCommand cmd = new SqliteCommand(FindFriends, g_conn))
220 {
221 cmd.Parameters.Add(new SqliteParameter(":ownerID", owner.ToString()));
222 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
223 try
224 {
225 using (IDataReader reader = cmd.ExecuteReader())
226 {
227 if (reader.Read())
228 {
229 reader.Close();
230 return true;
231 }
232 else
233 {
234 reader.Close();
235 return false;
236 }
237 }
238 }
239 catch (Exception ex)
240 {
241 m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
242 return false;
243 }
244 }
245 }
246 /// <summary>
247 /// Add a new friend in the friendlist
248 /// </summary>
249 /// <param name="friendlistowner">UUID of the friendlist owner</param>
250 /// <param name="friend">UUID of the friend to add</param>
251 /// <param name="perms">permission flag</param>
252 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
253 {
254 if (ExistsFriend(friendlistowner, friend))
255 return;
256
257 string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
258 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
259 {
260 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
261 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
262 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
263 cmd.ExecuteNonQuery();
264 }
265 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
266 {
267 cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.ToString()));
268 cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.ToString()));
269 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
270 cmd.ExecuteNonQuery();
271 }
272 }
273
274 /// <summary>
275 /// Remove a user from the friendlist
276 /// </summary>
277 /// <param name="friendlistowner">UUID of the friendlist owner</param>
278 /// <param name="friend">UUID of the friend to remove</param>
279 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
280 {
281 string DeletePerms = "delete from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
282 using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
283 {
284 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
285 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
286 cmd.ExecuteNonQuery();
287 }
288 }
289
290 /// <summary>
291 /// Update the friendlist permission
292 /// </summary>
293 /// <param name="friendlistowner">UUID of the friendlist owner</param>
294 /// <param name="friend">UUID of the friend to modify</param>
295 /// <param name="perms">updated permission flag</param>
296 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
297 {
298 string UpdatePerms = "update userfriends set friendPerms=:perms where ownerID=:ownerID and friendID=:friendID";
299 using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
300 {
301 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
302 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
303 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
304 cmd.ExecuteNonQuery();
305 }
306 }
307
308 /// <summary>
309 /// Get (fetch?) the friendlist for a user
310 /// </summary>
311 /// <param name="friendlistowner">UUID of the friendlist owner</param>
312 /// <returns>The friendlist list</returns>
313 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
314 {
315 List<FriendListItem> returnlist = new List<FriendListItem>();
316
317 using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
318 {
319 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
320
321 try
322 {
323 using (IDataReader reader = cmd.ExecuteReader())
324 {
325 while (reader.Read())
326 {
327 FriendListItem user = new FriendListItem();
328 user.FriendListOwner = friendlistowner;
329 user.Friend = new UUID((string)reader[0]);
330 user.FriendPerms = Convert.ToUInt32(reader[1]);
331 user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
332 returnlist.Add(user);
333 }
334 reader.Close();
335 }
336 }
337 catch (Exception ex)
338 {
339 m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
340 }
341 }
342
343 return returnlist;
344 }
345
346 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
347 {
348 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
349
350 DataTable agents = ds.Tables["useragents"];
351 foreach (UUID uuid in uuids)
352 {
353 lock (ds)
354 {
355 DataRow row = agents.Rows.Find(uuid.ToString());
356 if (row == null) infos[uuid] = null;
357 else
358 {
359 FriendRegionInfo fri = new FriendRegionInfo();
360 fri.isOnline = (bool)row["agentOnline"];
361 fri.regionHandle = Convert.ToUInt64(row["currentHandle"]);
362 infos[uuid] = fri;
363 }
364 }
365 }
366 return infos;
367 }
368
369 #endregion
370
371 /// <summary>
372 ///
373 /// </summary>
374 /// <param name="queryID"></param>
375 /// <param name="query"></param>
376 /// <returns></returns>
377 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
378 {
379 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
380 string[] querysplit;
381 querysplit = query.Split(' ');
382 if (querysplit.Length == 2)
383 {
384 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn))
385 {
386 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
387 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%"));
388
389 using (IDataReader reader = cmd.ExecuteReader())
390 {
391 while (reader.Read())
392 {
393 AvatarPickerAvatar user = new AvatarPickerAvatar();
394 user.AvatarID = new UUID((string) reader["UUID"]);
395 user.firstName = (string) reader["username"];
396 user.lastName = (string) reader["surname"];
397 returnlist.Add(user);
398 }
399 reader.Close();
400 }
401 }
402 }
403 else if (querysplit.Length == 1)
404 {
405 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn))
406 {
407 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
408 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%"));
409
410 using (IDataReader reader = cmd.ExecuteReader())
411 {
412 while (reader.Read())
413 {
414 AvatarPickerAvatar user = new AvatarPickerAvatar();
415 user.AvatarID = new UUID((string) reader["UUID"]);
416 user.firstName = (string) reader["username"];
417 user.lastName = (string) reader["surname"];
418 returnlist.Add(user);
419 }
420 reader.Close();
421 }
422 }
423 }
424 return returnlist;
425 }
426
427 /// <summary>
428 /// Returns a user by UUID direct
429 /// </summary>
430 /// <param name="uuid">The user's account ID</param>
431 /// <returns>A matching user profile</returns>
432 override public UserAgentData GetAgentByUUID(UUID uuid)
433 {
434 lock (ds)
435 {
436 DataRow row = ds.Tables["useragents"].Rows.Find(uuid.ToString());
437 if (row != null)
438 {
439 return buildUserAgent(row);
440 }
441 else
442 {
443 return null;
444 }
445 }
446 }
447
448 /// <summary>
449 /// Returns a session by account name
450 /// </summary>
451 /// <param name="name">The account name</param>
452 /// <returns>The user's session agent</returns>
453 override public UserAgentData GetAgentByName(string name)
454 {
455 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
456 }
457
458 /// <summary>
459 /// Returns a session by account name
460 /// </summary>
461 /// <param name="fname">The first part of the user's account name</param>
462 /// <param name="lname">The second part of the user's account name</param>
463 /// <returns>A user agent</returns>
464 override public UserAgentData GetAgentByName(string fname, string lname)
465 {
466 UserAgentData agent = null;
467
468 UserProfileData profile = GetUserByName(fname, lname);
469 if (profile != null)
470 {
471 agent = GetAgentByUUID(profile.ID);
472 }
473 return agent;
474 }
475
476 /// <summary>
477 /// DEPRECATED? Store the weblogin key
478 /// </summary>
479 /// <param name="AgentID">UUID of the user</param>
480 /// <param name="WebLoginKey">UUID of the weblogin</param>
481 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
482 {
483 DataTable users = ds.Tables["users"];
484 lock (ds)
485 {
486 DataRow row = users.Rows.Find(AgentID.ToString());
487 if (row == null)
488 {
489 m_log.Warn("[USER DB]: Unable to store new web login key for non-existant user");
490 }
491 else
492 {
493 UserProfileData user = GetUserByUUID(AgentID);
494 user.WebLoginKey = WebLoginKey;
495 fillUserRow(row, user);
496 da.Update(ds, "users");
497 }
498 }
499 }
500
501 private bool ExistsFirstLastName(String fname, String lname)
502 {
503 string FindUser = "select * from users where (username=:username and surname=:surname)";
504 using (SqliteCommand cmd = new SqliteCommand(FindUser, g_conn))
505 {
506 cmd.Parameters.Add(new SqliteParameter(":username", fname));
507 cmd.Parameters.Add(new SqliteParameter(":surname", lname));
508 try
509 {
510 using (IDataReader reader = cmd.ExecuteReader())
511 {
512 if (reader.Read())
513 {
514 reader.Close();
515 return true;
516 }
517 else
518 {
519 reader.Close();
520 return false;
521 }
522 }
523 }
524 catch (Exception ex)
525 {
526 m_log.Error("[USER DB]: Exception searching for user's first and last name: " + ex.ToString());
527 return false;
528 }
529 }
530 }
531
532 /// <summary>
533 /// Creates a new user profile
534 /// </summary>
535 /// <param name="user">The profile to add to the database</param>
536 override public void AddNewUserProfile(UserProfileData user)
537 {
538 DataTable users = ds.Tables["users"];
539 UUID zero = UUID.Zero;
540 if (ExistsFirstLastName(user.FirstName, user.SurName) || user.ID == zero)
541 return;
542
543 lock (ds)
544 {
545 DataRow row = users.Rows.Find(user.ID.ToString());
546 if (row == null)
547 {
548 row = users.NewRow();
549 fillUserRow(row, user);
550 users.Rows.Add(row);
551
552 m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
553
554 // save changes off to disk
555 da.Update(ds, "users");
556 }
557 else
558 {
559 m_log.WarnFormat("[USER DB]: Ignoring add since user with id {0} already exists", user.ID);
560 }
561 }
562 }
563
564 /// <summary>
565 /// Creates a new user profile
566 /// </summary>
567 /// <param name="user">The profile to add to the database</param>
568 /// <returns>True on success, false on error</returns>
569 override public bool UpdateUserProfile(UserProfileData user)
570 {
571 DataTable users = ds.Tables["users"];
572 lock (ds)
573 {
574 DataRow row = users.Rows.Find(user.ID.ToString());
575 if (row == null)
576 {
577 return false;
578 }
579 else
580 {
581 fillUserRow(row, user);
582 da.Update(ds, "users");
583 }
584 }
585
586 //AddNewUserProfile(user);
587 return true;
588 }
589
590 /// <summary>
591 /// Creates a new user agent
592 /// </summary>
593 /// <param name="agent">The agent to add to the database</param>
594 override public void AddNewUserAgent(UserAgentData agent)
595 {
596 UUID zero = UUID.Zero;
597 if (agent.SessionID == zero || agent.ProfileID == zero)
598 return;
599
600 DataTable agents = ds.Tables["useragents"];
601 lock (ds)
602 {
603 DataRow row = agents.Rows.Find(agent.ProfileID.ToString());
604 if (row == null)
605 {
606 row = agents.NewRow();
607 fillUserAgentRow(row, agent);
608 agents.Rows.Add(row);
609 }
610 else
611 {
612 fillUserAgentRow(row, agent);
613
614 }
615 m_log.Info("[USER DB]: Syncing useragent database: " + ds.Tables["useragents"].Rows.Count + " agents stored");
616 // save changes off to disk
617 dua.Update(ds, "useragents");
618 }
619 }
620
621 /// <summary>
622 /// Transfers money between two user accounts
623 /// </summary>
624 /// <param name="from">Starting account</param>
625 /// <param name="to">End account</param>
626 /// <param name="amount">The amount to move</param>
627 /// <returns>Success?</returns>
628 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
629 {
630 return false; // for consistency with the MySQL impl
631 }
632
633 /// <summary>
634 /// Transfers inventory between two accounts
635 /// </summary>
636 /// <remarks>Move to inventory server</remarks>
637 /// <param name="from">Senders account</param>
638 /// <param name="to">Receivers account</param>
639 /// <param name="item">Inventory item</param>
640 /// <returns>Success?</returns>
641 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
642 {
643 return false; //for consistency with the MySQL impl
644 }
645
646
647 /// <summary>
648 /// Appearance.
649 /// TODO: stubs for now to do in memory appearance.
650 /// </summary>
651 /// <param name="user">The user UUID</param>
652 /// <returns>Avatar Appearence</returns>
653 override public AvatarAppearance GetUserAppearance(UUID user)
654 {
655 m_log.Info("[APPEARANCE] GetUserAppearance " + user.ToString());
656
657 AvatarAppearance aa = new AvatarAppearance(user);
658 //try {
659 aa.Owner = user;
660
661 DataTable aap = ds.Tables["avatarappearance"];
662 lock (ds)
663 {
664 DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
665 if (row == null)
666 {
667 m_log.Info("[APPEARANCE] Could not find appearance for " + user.ToString());
668
669 //m_log.Debug("[USER DB]: Creating avatarappearance For: " + user.ToString());
670
671 //row = aap.NewRow();
672 //fillAvatarAppearanceRow(row, user, appearance);
673 //aap.Rows.Add(row);
674 // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
675 // save changes off to disk
676 //daa.Update(ds, "avatarappearance");
677 }
678 else
679 {
680 m_log.InfoFormat("[APPEARANCE] appearance found for {0}", user.ToString());
681
682 aa.BodyAsset = new UUID((String)row["BodyAsset"]);
683 aa.BodyItem = new UUID((String)row["BodyItem"]);
684 aa.SkinItem = new UUID((String)row["SkinItem"]);
685 aa.SkinAsset = new UUID((String)row["SkinAsset"]);
686 aa.HairItem = new UUID((String)row["HairItem"]);
687 aa.HairAsset = new UUID((String)row["HairAsset"]);
688 aa.EyesItem = new UUID((String)row["EyesItem"]);
689 aa.EyesAsset = new UUID((String)row["EyesAsset"]);
690 aa.ShirtItem = new UUID((String)row["ShirtItem"]);
691 aa.ShirtAsset = new UUID((String)row["ShirtAsset"]);
692 aa.PantsItem = new UUID((String)row["PantsItem"]);
693 aa.PantsAsset = new UUID((String)row["PantsAsset"]);
694 aa.ShoesItem = new UUID((String)row["ShoesItem"]);
695 aa.ShoesAsset = new UUID((String)row["ShoesAsset"]);
696 aa.SocksItem = new UUID((String)row["SocksItem"]);
697 aa.SocksAsset = new UUID((String)row["SocksAsset"]);
698 aa.JacketItem = new UUID((String)row["JacketItem"]);
699 aa.JacketAsset = new UUID((String)row["JacketAsset"]);
700 aa.GlovesItem = new UUID((String)row["GlovesItem"]);
701 aa.GlovesAsset = new UUID((String)row["GlovesAsset"]);
702 aa.UnderShirtItem = new UUID((String)row["UnderShirtItem"]);
703 aa.UnderShirtAsset = new UUID((String)row["UnderShirtAsset"]);
704 aa.UnderPantsItem = new UUID((String)row["UnderPantsItem"]);
705 aa.UnderPantsAsset = new UUID((String)row["UnderPantsAsset"]);
706 aa.SkirtItem = new UUID((String)row["SkirtItem"]);
707 aa.SkirtAsset = new UUID((String)row["SkirtAsset"]);
708
709 // Ewe Loon
710 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
711
712 String str = (String)row["Texture"];
713 byte[] texture = Convert.FromBase64String(str);
714 aa.Texture = new Primitive.TextureEntry(texture, 0, texture.Length);
715
716 str = (String)row["VisualParams"];
717 byte[] VisualParams = Convert.FromBase64String(str);
718 aa.VisualParams = VisualParams;
719
720 aa.Serial = Convert.ToInt32(row["Serial"]);
721 aa.AvatarHeight = Convert.ToSingle(row["AvatarHeight"]);
722 m_log.InfoFormat("[APPEARANCE] appearance set for {0}", user.ToString());
723 }
724 }
725
726 // m_log.Info("[APPEARANCE] Found appearance for " + user.ToString() + aa.ToString());
727 // } catch (KeyNotFoundException) {
728 // m_log.InfoFormat("[APPEARANCE] No appearance found for {0}", user.ToString());
729 // }
730 return aa;
731 }
732
733 /// <summary>
734 /// Update a user appearence
735 /// </summary>
736 /// <param name="user">the user UUID</param>
737 /// <param name="appearance">appearence</param>
738 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
739 {
740 appearance.Owner = user;
741 DataTable aap = ds.Tables["avatarappearance"];
742 lock (ds)
743 {
744 DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
745 if (row == null)
746 {
747 m_log.Debug("[USER DB]: Creating UserAppearance For: " + user.ToString());
748
749 row = aap.NewRow();
750 fillAvatarAppearanceRow(row, user, appearance);
751 aap.Rows.Add(row);
752 // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
753 // save changes off to disk
754 daa.Update(ds, "avatarappearance");
755 }
756 else
757 {
758 m_log.Debug("[USER DB]: Updating UserAppearance For: " + user.ToString());
759 fillAvatarAppearanceRow(row, user, appearance);
760 daa.Update(ds, "avatarappearance");
761 }
762 }
763 }
764
765 /// <summary>
766 /// Returns the name of the storage provider
767 /// </summary>
768 /// <returns>Storage provider name</returns>
769 override public string Name
770 {
771 get {return "Sqlite Userdata";}
772 }
773
774 /// <summary>
775 /// Returns the version of the storage provider
776 /// </summary>
777 /// <returns>Storage provider version</returns>
778 override public string Version
779 {
780 get {return "0.1";}
781 }
782
783 /***********************************************************************
784 *
785 * DataTable creation
786 *
787 **********************************************************************/
788 /***********************************************************************
789 *
790 * Database Definition Functions
791 *
792 * This should be db agnostic as we define them in ADO.NET terms
793 *
794 **********************************************************************/
795
796 /// <summary>
797 /// Create the "users" table
798 /// </summary>
799 /// <returns>DataTable</returns>
800 private static DataTable createUsersTable()
801 {
802 DataTable users = new DataTable("users");
803
804 SQLiteUtil.createCol(users, "UUID", typeof (String));
805 SQLiteUtil.createCol(users, "username", typeof (String));
806 SQLiteUtil.createCol(users, "surname", typeof (String));
807 SQLiteUtil.createCol(users, "email", typeof (String));
808 SQLiteUtil.createCol(users, "passwordHash", typeof (String));
809 SQLiteUtil.createCol(users, "passwordSalt", typeof (String));
810
811 SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32));
812 SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32));
813 SQLiteUtil.createCol(users, "homeRegionID", typeof (String));
814 SQLiteUtil.createCol(users, "homeLocationX", typeof (Double));
815 SQLiteUtil.createCol(users, "homeLocationY", typeof (Double));
816 SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double));
817 SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double));
818 SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double));
819 SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double));
820 SQLiteUtil.createCol(users, "created", typeof (Int32));
821 SQLiteUtil.createCol(users, "lastLogin", typeof (Int32));
822
823 //TODO: Please delete this column. It's now a brick
824 SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String));
825
826 SQLiteUtil.createCol(users, "userInventoryURI", typeof (String));
827 SQLiteUtil.createCol(users, "userAssetURI", typeof (String));
828 SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32));
829 SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32));
830 SQLiteUtil.createCol(users, "profileAboutText", typeof (String));
831 SQLiteUtil.createCol(users, "profileFirstText", typeof (String));
832 SQLiteUtil.createCol(users, "profileImage", typeof (String));
833 SQLiteUtil.createCol(users, "profileFirstImage", typeof (String));
834 SQLiteUtil.createCol(users, "webLoginKey", typeof(String));
835 SQLiteUtil.createCol(users, "userFlags", typeof (Int32));
836 SQLiteUtil.createCol(users, "godLevel", typeof (Int32));
837 SQLiteUtil.createCol(users, "customType", typeof (String));
838 SQLiteUtil.createCol(users, "partner", typeof (String));
839 // Add in contraints
840 users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]};
841 return users;
842 }
843
844 /// <summary>
845 /// Create the "useragents" table
846 /// </summary>
847 /// <returns>Data Table</returns>
848 private static DataTable createUserAgentsTable()
849 {
850 DataTable ua = new DataTable("useragents");
851 // this is the UUID of the user
852 SQLiteUtil.createCol(ua, "UUID", typeof (String));
853 SQLiteUtil.createCol(ua, "agentIP", typeof (String));
854 SQLiteUtil.createCol(ua, "agentPort", typeof (Int32));
855 SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean));
856 SQLiteUtil.createCol(ua, "sessionID", typeof (String));
857 SQLiteUtil.createCol(ua, "secureSessionID", typeof (String));
858 SQLiteUtil.createCol(ua, "regionID", typeof (String));
859 SQLiteUtil.createCol(ua, "loginTime", typeof (Int32));
860 SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32));
861 SQLiteUtil.createCol(ua, "currentRegion", typeof (String));
862 SQLiteUtil.createCol(ua, "currentHandle", typeof (String));
863 // vectors
864 SQLiteUtil.createCol(ua, "currentPosX", typeof (Double));
865 SQLiteUtil.createCol(ua, "currentPosY", typeof (Double));
866 SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double));
867 // constraints
868 ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]};
869
870 return ua;
871 }
872
873 /// <summary>
874 /// Create the "userfriends" table
875 /// </summary>
876 /// <returns>Data Table</returns>
877 private static DataTable createUserFriendsTable()
878 {
879 DataTable ua = new DataTable("userfriends");
880 // table contains user <----> user relationship with perms
881 SQLiteUtil.createCol(ua, "ownerID", typeof(String));
882 SQLiteUtil.createCol(ua, "friendID", typeof(String));
883 SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32));
884 SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32));
885 SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32));
886
887 return ua;
888 }
889
890 /// <summary>
891 /// Create the "avatarappearance" table
892 /// </summary>
893 /// <returns>Data Table</returns>
894 private static DataTable createAvatarAppearanceTable()
895 {
896 DataTable aa = new DataTable("avatarappearance");
897 // table contains user appearance items
898
899 SQLiteUtil.createCol(aa, "Owner", typeof(String));
900 SQLiteUtil.createCol(aa, "BodyItem", typeof(String));
901 SQLiteUtil.createCol(aa, "BodyAsset", typeof(String));
902 SQLiteUtil.createCol(aa, "SkinItem", typeof(String));
903 SQLiteUtil.createCol(aa, "SkinAsset", typeof(String));
904 SQLiteUtil.createCol(aa, "HairItem", typeof(String));
905 SQLiteUtil.createCol(aa, "HairAsset", typeof(String));
906 SQLiteUtil.createCol(aa, "EyesItem", typeof(String));
907 SQLiteUtil.createCol(aa, "EyesAsset", typeof(String));
908 SQLiteUtil.createCol(aa, "ShirtItem", typeof(String));
909 SQLiteUtil.createCol(aa, "ShirtAsset", typeof(String));
910 SQLiteUtil.createCol(aa, "PantsItem", typeof(String));
911 SQLiteUtil.createCol(aa, "PantsAsset", typeof(String));
912 SQLiteUtil.createCol(aa, "ShoesItem", typeof(String));
913 SQLiteUtil.createCol(aa, "ShoesAsset", typeof(String));
914 SQLiteUtil.createCol(aa, "SocksItem", typeof(String));
915 SQLiteUtil.createCol(aa, "SocksAsset", typeof(String));
916 SQLiteUtil.createCol(aa, "JacketItem", typeof(String));
917 SQLiteUtil.createCol(aa, "JacketAsset", typeof(String));
918 SQLiteUtil.createCol(aa, "GlovesItem", typeof(String));
919 SQLiteUtil.createCol(aa, "GlovesAsset", typeof(String));
920 SQLiteUtil.createCol(aa, "UnderShirtItem", typeof(String));
921 SQLiteUtil.createCol(aa, "UnderShirtAsset", typeof(String));
922 SQLiteUtil.createCol(aa, "UnderPantsItem", typeof(String));
923 SQLiteUtil.createCol(aa, "UnderPantsAsset", typeof(String));
924 SQLiteUtil.createCol(aa, "SkirtItem", typeof(String));
925 SQLiteUtil.createCol(aa, "SkirtAsset", typeof(String));
926
927 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
928 SQLiteUtil.createCol(aa, "Texture", typeof (String));
929 SQLiteUtil.createCol(aa, "VisualParams", typeof (String));
930
931 SQLiteUtil.createCol(aa, "Serial", typeof(Int32));
932 SQLiteUtil.createCol(aa, "AvatarHeight", typeof(Double));
933
934 aa.PrimaryKey = new DataColumn[] { aa.Columns["Owner"] };
935
936 return aa;
937 }
938
939 /***********************************************************************
940 *
941 * Convert between ADO.NET <=> OpenSim Objects
942 *
943 * These should be database independant
944 *
945 **********************************************************************/
946
947 /// <summary>
948 /// TODO: this doesn't work yet because something more
949 /// interesting has to be done to actually get these values
950 /// back out. Not enough time to figure it out yet.
951 /// </summary>
952 /// <param name="row"></param>
953 /// <returns></returns>
954 private static UserProfileData buildUserProfile(DataRow row)
955 {
956 UserProfileData user = new UserProfileData();
957 UUID tmp;
958 UUID.TryParse((String)row["UUID"], out tmp);
959 user.ID = tmp;
960 user.FirstName = (String) row["username"];
961 user.SurName = (String) row["surname"];
962 user.Email = (row.IsNull("email")) ? "" : (String) row["email"];
963
964 user.PasswordHash = (String) row["passwordHash"];
965 user.PasswordSalt = (String) row["passwordSalt"];
966
967 user.HomeRegionX = Convert.ToUInt32(row["homeRegionX"]);
968 user.HomeRegionY = Convert.ToUInt32(row["homeRegionY"]);
969 user.HomeLocation = new Vector3(
970 Convert.ToSingle(row["homeLocationX"]),
971 Convert.ToSingle(row["homeLocationY"]),
972 Convert.ToSingle(row["homeLocationZ"])
973 );
974 user.HomeLookAt = new Vector3(
975 Convert.ToSingle(row["homeLookAtX"]),
976 Convert.ToSingle(row["homeLookAtY"]),
977 Convert.ToSingle(row["homeLookAtZ"])
978 );
979
980 UUID regionID = UUID.Zero;
981 UUID.TryParse(row["homeRegionID"].ToString(), out regionID); // it's ok if it doesn't work; just use UUID.Zero
982 user.HomeRegionID = regionID;
983
984 user.Created = Convert.ToInt32(row["created"]);
985 user.LastLogin = Convert.ToInt32(row["lastLogin"]);
986 user.UserInventoryURI = (String) row["userInventoryURI"];
987 user.UserAssetURI = (String) row["userAssetURI"];
988 user.CanDoMask = Convert.ToUInt32(row["profileCanDoMask"]);
989 user.WantDoMask = Convert.ToUInt32(row["profileWantDoMask"]);
990 user.AboutText = (String) row["profileAboutText"];
991 user.FirstLifeAboutText = (String) row["profileFirstText"];
992 UUID.TryParse((String)row["profileImage"], out tmp);
993 user.Image = tmp;
994 UUID.TryParse((String)row["profileFirstImage"], out tmp);
995 user.FirstLifeImage = tmp;
996 user.WebLoginKey = new UUID((String) row["webLoginKey"]);
997 user.UserFlags = Convert.ToInt32(row["userFlags"]);
998 user.GodLevel = Convert.ToInt32(row["godLevel"]);
999 user.CustomType = row["customType"].ToString();
1000 user.Partner = new UUID((String) row["partner"]);
1001
1002 return user;
1003 }
1004
1005 /// <summary>
1006 /// Persist user profile data
1007 /// </summary>
1008 /// <param name="row"></param>
1009 /// <param name="user"></param>
1010 private void fillUserRow(DataRow row, UserProfileData user)
1011 {
1012 row["UUID"] = user.ID.ToString();
1013 row["username"] = user.FirstName;
1014 row["surname"] = user.SurName;
1015 row["email"] = user.Email;
1016 row["passwordHash"] = user.PasswordHash;
1017 row["passwordSalt"] = user.PasswordSalt;
1018
1019 row["homeRegionX"] = user.HomeRegionX;
1020 row["homeRegionY"] = user.HomeRegionY;
1021 row["homeRegionID"] = user.HomeRegionID.ToString();
1022 row["homeLocationX"] = user.HomeLocation.X;
1023 row["homeLocationY"] = user.HomeLocation.Y;
1024 row["homeLocationZ"] = user.HomeLocation.Z;
1025 row["homeLookAtX"] = user.HomeLookAt.X;
1026 row["homeLookAtY"] = user.HomeLookAt.Y;
1027 row["homeLookAtZ"] = user.HomeLookAt.Z;
1028
1029 row["created"] = user.Created;
1030 row["lastLogin"] = user.LastLogin;
1031 //TODO: Get rid of rootInventoryFolderID in a safe way.
1032 row["rootInventoryFolderID"] = UUID.Zero.ToString();
1033 row["userInventoryURI"] = user.UserInventoryURI;
1034 row["userAssetURI"] = user.UserAssetURI;
1035 row["profileCanDoMask"] = user.CanDoMask;
1036 row["profileWantDoMask"] = user.WantDoMask;
1037 row["profileAboutText"] = user.AboutText;
1038 row["profileFirstText"] = user.FirstLifeAboutText;
1039 row["profileImage"] = user.Image.ToString();
1040 row["profileFirstImage"] = user.FirstLifeImage.ToString();
1041 row["webLoginKey"] = user.WebLoginKey.ToString();
1042 row["userFlags"] = user.UserFlags;
1043 row["godLevel"] = user.GodLevel;
1044 row["customType"] = user.CustomType == null ? "" : user.CustomType;
1045 row["partner"] = user.Partner.ToString();
1046
1047 // ADO.NET doesn't handle NULL very well
1048 foreach (DataColumn col in ds.Tables["users"].Columns)
1049 {
1050 if (row[col] == null)
1051 {
1052 row[col] = String.Empty;
1053 }
1054 }
1055 }
1056
1057 /// <summary>
1058 ///
1059 /// </summary>
1060 /// <param name="row"></param>
1061 /// <param name="user"></param>
1062 private void fillAvatarAppearanceRow(DataRow row, UUID user, AvatarAppearance appearance)
1063 {
1064 row["Owner"] = Util.ToRawUuidString(user);
1065 row["BodyItem"] = appearance.BodyItem.ToString();
1066 row["BodyAsset"] = appearance.BodyAsset.ToString();
1067 row["SkinItem"] = appearance.SkinItem.ToString();
1068 row["SkinAsset"] = appearance.SkinAsset.ToString();
1069 row["HairItem"] = appearance.HairItem.ToString();
1070 row["HairAsset"] = appearance.HairAsset.ToString();
1071 row["EyesItem"] = appearance.EyesItem.ToString();
1072 row["EyesAsset"] = appearance.EyesAsset.ToString();
1073 row["ShirtItem"] = appearance.ShirtItem.ToString();
1074 row["ShirtAsset"] = appearance.ShirtAsset.ToString();
1075 row["PantsItem"] = appearance.PantsItem.ToString();
1076 row["PantsAsset"] = appearance.PantsAsset.ToString();
1077 row["ShoesItem"] = appearance.ShoesItem.ToString();
1078 row["ShoesAsset"] = appearance.ShoesAsset.ToString();
1079 row["SocksItem"] = appearance.SocksItem.ToString();
1080 row["SocksAsset"] = appearance.SocksAsset.ToString();
1081 row["JacketItem"] = appearance.JacketItem.ToString();
1082 row["JacketAsset"] = appearance.JacketAsset.ToString();
1083 row["GlovesItem"] = appearance.GlovesItem.ToString();
1084 row["GlovesAsset"] = appearance.GlovesAsset.ToString();
1085 row["UnderShirtItem"] = appearance.UnderShirtItem.ToString();
1086 row["UnderShirtAsset"] = appearance.UnderShirtAsset.ToString();
1087 row["UnderPantsItem"] = appearance.UnderPantsItem.ToString();
1088 row["UnderPantsAsset"] = appearance.UnderPantsAsset.ToString();
1089 row["SkirtItem"] = appearance.SkirtItem.ToString();
1090 row["SkirtAsset"] = appearance.SkirtAsset.ToString();
1091
1092 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
1093 row["Texture"] = Convert.ToBase64String(appearance.Texture.GetBytes());
1094 row["VisualParams"] = Convert.ToBase64String(appearance.VisualParams);
1095
1096 row["Serial"] = appearance.Serial;
1097 row["AvatarHeight"] = appearance.AvatarHeight;
1098
1099 // ADO.NET doesn't handle NULL very well
1100 foreach (DataColumn col in ds.Tables["avatarappearance"].Columns)
1101 {
1102 if (row[col] == null)
1103 {
1104 row[col] = String.Empty;
1105 }
1106 }
1107 }
1108
1109 /// <summary>
1110 ///
1111 /// </summary>
1112 /// <param name="row"></param>
1113 /// <returns></returns>
1114 private static UserAgentData buildUserAgent(DataRow row)
1115 {
1116 UserAgentData ua = new UserAgentData();
1117
1118 UUID tmp;
1119 UUID.TryParse((String)row["UUID"], out tmp);
1120 ua.ProfileID = tmp;
1121 ua.AgentIP = (String)row["agentIP"];
1122 ua.AgentPort = Convert.ToUInt32(row["agentPort"]);
1123 ua.AgentOnline = Convert.ToBoolean(row["agentOnline"]);
1124 ua.SessionID = new UUID((String) row["sessionID"]);
1125 ua.SecureSessionID = new UUID((String) row["secureSessionID"]);
1126 ua.InitialRegion = new UUID((String) row["regionID"]);
1127 ua.LoginTime = Convert.ToInt32(row["loginTime"]);
1128 ua.LogoutTime = Convert.ToInt32(row["logoutTime"]);
1129 ua.Region = new UUID((String) row["currentRegion"]);
1130 ua.Handle = Convert.ToUInt64(row["currentHandle"]);
1131 ua.Position = new Vector3(
1132 Convert.ToSingle(row["currentPosX"]),
1133 Convert.ToSingle(row["currentPosY"]),
1134 Convert.ToSingle(row["currentPosZ"])
1135 );
1136 ua.LookAt = new Vector3(
1137 Convert.ToSingle(row["currentLookAtX"]),
1138 Convert.ToSingle(row["currentLookAtY"]),
1139 Convert.ToSingle(row["currentLookAtZ"])
1140 );
1141 return ua;
1142 }
1143
1144 /// <summary>
1145 ///
1146 /// </summary>
1147 /// <param name="row"></param>
1148 /// <param name="ua"></param>
1149 private static void fillUserAgentRow(DataRow row, UserAgentData ua)
1150 {
1151 row["UUID"] = ua.ProfileID.ToString();
1152 row["agentIP"] = ua.AgentIP;
1153 row["agentPort"] = ua.AgentPort;
1154 row["agentOnline"] = ua.AgentOnline;
1155 row["sessionID"] = ua.SessionID.ToString();
1156 row["secureSessionID"] = ua.SecureSessionID.ToString();
1157 row["regionID"] = ua.InitialRegion.ToString();
1158 row["loginTime"] = ua.LoginTime;
1159 row["logoutTime"] = ua.LogoutTime;
1160 row["currentRegion"] = ua.Region.ToString();
1161 row["currentHandle"] = ua.Handle.ToString();
1162 // vectors
1163 row["currentPosX"] = ua.Position.X;
1164 row["currentPosY"] = ua.Position.Y;
1165 row["currentPosZ"] = ua.Position.Z;
1166 row["currentLookAtX"] = ua.LookAt.X;
1167 row["currentLookAtY"] = ua.LookAt.Y;
1168 row["currentLookAtZ"] = ua.LookAt.Z;
1169 }
1170
1171 /***********************************************************************
1172 *
1173 * Database Binding functions
1174 *
1175 * These will be db specific due to typing, and minor differences
1176 * in databases.
1177 *
1178 **********************************************************************/
1179
1180 /// <summary>
1181 ///
1182 /// </summary>
1183 /// <param name="da"></param>
1184 /// <param name="conn"></param>
1185 private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn)
1186 {
1187 da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]);
1188 da.InsertCommand.Connection = conn;
1189
1190 da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]);
1191 da.UpdateCommand.Connection = conn;
1192
1193 SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID");
1194 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String)));
1195 delete.Connection = conn;
1196 da.DeleteCommand = delete;
1197 }
1198
1199 private void setupAgentCommands(SqliteDataAdapter da, SqliteConnection conn)
1200 {
1201 da.InsertCommand = SQLiteUtil.createInsertCommand("useragents", ds.Tables["useragents"]);
1202 da.InsertCommand.Connection = conn;
1203
1204 da.UpdateCommand = SQLiteUtil.createUpdateCommand("useragents", "UUID=:UUID", ds.Tables["useragents"]);
1205 da.UpdateCommand.Connection = conn;
1206
1207 SqliteCommand delete = new SqliteCommand("delete from useragents where UUID = :ProfileID");
1208 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ProfileID", typeof(String)));
1209 delete.Connection = conn;
1210 da.DeleteCommand = delete;
1211 }
1212
1213 /// <summary>
1214 ///
1215 /// </summary>
1216 /// <param name="daf"></param>
1217 /// <param name="conn"></param>
1218 private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn)
1219 {
1220 daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]);
1221 daf.InsertCommand.Connection = conn;
1222
1223 daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]);
1224 daf.UpdateCommand.Connection = conn;
1225
1226 SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID");
1227 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String)));
1228 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String)));
1229 delete.Connection = conn;
1230 daf.DeleteCommand = delete;
1231
1232 }
1233
1234 /// <summary>
1235 ///
1236 /// </summary>
1237 /// <param name="daf"></param>
1238 /// <param name="conn"></param>
1239 private void setupAvatarAppearanceCommands(SqliteDataAdapter daa, SqliteConnection conn)
1240 {
1241 daa.InsertCommand = SQLiteUtil.createInsertCommand("avatarappearance", ds.Tables["avatarappearance"]);
1242 daa.InsertCommand.Connection = conn;
1243
1244 daa.UpdateCommand = SQLiteUtil.createUpdateCommand("avatarappearance", "Owner=:Owner", ds.Tables["avatarappearance"]);
1245 daa.UpdateCommand.Connection = conn;
1246
1247 SqliteCommand delete = new SqliteCommand("delete from avatarappearance where Owner=:Owner");
1248 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("Owner", typeof(String)));
1249 delete.Connection = conn;
1250 daa.DeleteCommand = delete;
1251 }
1252
1253
1254 override public void ResetAttachments(UUID userID)
1255 {
1256 }
1257
1258 override public void LogoutUsers(UUID regionID)
1259 {
1260 }
1261 }
1262}
diff --git a/OpenSim/Data/SQLite/SQLiteUtils.cs b/OpenSim/Data/SQLite/SQLiteUtils.cs
index 4a835ce..07c6b69 100644
--- a/OpenSim/Data/SQLite/SQLiteUtils.cs
+++ b/OpenSim/Data/SQLite/SQLiteUtils.cs
@@ -27,7 +27,7 @@
27 27
28using System; 28using System;
29using System.Data; 29using System.Data;
30using Mono.Data.SqliteClient; 30using Mono.Data.Sqlite;
31 31
32namespace OpenSim.Data.SQLite 32namespace OpenSim.Data.SQLite
33{ 33{
diff --git a/OpenSim/Data/SQLite/SQLiteXInventoryData.cs b/OpenSim/Data/SQLite/SQLiteXInventoryData.cs
index 5c93f88..6064538 100644
--- a/OpenSim/Data/SQLite/SQLiteXInventoryData.cs
+++ b/OpenSim/Data/SQLite/SQLiteXInventoryData.cs
@@ -29,7 +29,7 @@ using System;
29using System.Data; 29using System.Data;
30using System.Reflection; 30using System.Reflection;
31using System.Collections.Generic; 31using System.Collections.Generic;
32using Mono.Data.SqliteClient; 32using Mono.Data.Sqlite;
33using log4net; 33using log4net;
34using OpenMetaverse; 34using OpenMetaverse;
35using OpenSim.Framework; 35using OpenSim.Framework;
@@ -49,7 +49,7 @@ namespace OpenSim.Data.SQLite
49 public SQLiteXInventoryData(string conn, string realm) 49 public SQLiteXInventoryData(string conn, string realm)
50 { 50 {
51 m_Folders = new SQLiteGenericTableHandler<XInventoryFolder>( 51 m_Folders = new SQLiteGenericTableHandler<XInventoryFolder>(
52 conn, "inventoryfolders", "InventoryStore"); 52 conn, "inventoryfolders", "XInventoryStore");
53 m_Items = new SqliteItemHandler( 53 m_Items = new SqliteItemHandler(
54 conn, "inventoryitems", String.Empty); 54 conn, "inventoryitems", String.Empty);
55 } 55 }
@@ -115,7 +115,7 @@ namespace OpenSim.Data.SQLite
115 cmd.Parameters.Add(new SqliteParameter(":ParentFolderID", newParent)); 115 cmd.Parameters.Add(new SqliteParameter(":ParentFolderID", newParent));
116 cmd.Parameters.Add(new SqliteParameter(":InventoryID", id)); 116 cmd.Parameters.Add(new SqliteParameter(":InventoryID", id));
117 117
118 return ExecuteNonQuery(cmd) == 0 ? false : true; 118 return ExecuteNonQuery(cmd, m_Connection) == 0 ? false : true;
119 } 119 }
120 120
121 public XInventoryItem[] GetActiveGestures(UUID principalID) 121 public XInventoryItem[] GetActiveGestures(UUID principalID)
@@ -137,7 +137,7 @@ namespace OpenSim.Data.SQLite
137 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString())); 137 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
138 cmd.Parameters.Add(new SqliteParameter(":AssetID", assetID.ToString())); 138 cmd.Parameters.Add(new SqliteParameter(":AssetID", assetID.ToString()));
139 139
140 IDataReader reader = ExecuteReader(cmd); 140 IDataReader reader = ExecuteReader(cmd, m_Connection);
141 141
142 int perms = 0; 142 int perms = 0;
143 143
@@ -147,7 +147,7 @@ namespace OpenSim.Data.SQLite
147 } 147 }
148 148
149 reader.Close(); 149 reader.Close();
150 CloseReaderCommand(cmd); 150 //CloseCommand(cmd);
151 151
152 return perms; 152 return perms;
153 } 153 }
generated by cgit v1.1 at 2024-12-25 07:23:08 +0000