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_UserAccount.sql17
-rw-r--r--OpenSim/Data/SQLite/Resources/002_AuthStore.sql5
-rw-r--r--OpenSim/Data/SQLite/Resources/002_UserAccount.sql5
-rw-r--r--OpenSim/Data/SQLite/SQLiteAuthenticationData.cs214
-rw-r--r--OpenSim/Data/SQLite/SQLiteFramework.cs11
-rw-r--r--OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs15
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserAccountData.cs81
8 files changed, 359 insertions, 7 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_UserAccount.sql b/OpenSim/Data/SQLite/Resources/001_UserAccount.sql
new file mode 100644
index 0000000..f9bf24c
--- /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) NOT NULL,
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/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_UserAccount.sql b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..c0b3d7b
--- /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, lastname AS LastName, email as Email, CONCAT('AssetServerURI=', userAssetURI, ' InventoryServerURI=', userInventoryURI, ' GatewayURI= HomeURI=') AS ServiceURLs, created as Created FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs
new file mode 100644
index 0000000..271ed47
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs
@@ -0,0 +1,214 @@
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.SqliteClient;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteAuthenticationData : SQLiteFramework, IAuthenticationData
39 {
40 private string m_Realm;
41 private List<string> m_ColumnNames;
42 private int m_LastExpire;
43 private string m_connectionString;
44
45 private static bool m_initialized = false;
46
47 public SQLiteAuthenticationData(string connectionString, string realm)
48 : base(connectionString)
49 {
50 m_Realm = realm;
51 m_connectionString = connectionString;
52
53 if (!m_initialized)
54 {
55 using (SqliteConnection dbcon = new SqliteConnection(m_connectionString))
56 {
57 dbcon.Open();
58 Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore");
59 m.Update();
60 }
61 m_initialized = true;
62 }
63 }
64
65 public AuthenticationData Get(UUID principalID)
66 {
67 AuthenticationData ret = new AuthenticationData();
68 ret.Data = new Dictionary<string, object>();
69
70 using (SqliteConnection dbcon = new SqliteConnection(m_connectionString))
71 {
72 dbcon.Open();
73 SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = '" + principalID.ToString() + "'", dbcon);
74
75 IDataReader result = cmd.ExecuteReader();
76
77 if (result.Read())
78 {
79 ret.PrincipalID = principalID;
80
81 if (m_ColumnNames == null)
82 {
83 m_ColumnNames = new List<string>();
84
85 DataTable schemaTable = result.GetSchemaTable();
86 foreach (DataRow row in schemaTable.Rows)
87 m_ColumnNames.Add(row["ColumnName"].ToString());
88 }
89
90 foreach (string s in m_ColumnNames)
91 {
92 if (s == "UUID")
93 continue;
94
95 ret.Data[s] = result[s].ToString();
96 }
97
98 return ret;
99 }
100 else
101 {
102 return null;
103 }
104 }
105 }
106
107 public bool Store(AuthenticationData data)
108 {
109 if (data.Data.ContainsKey("UUID"))
110 data.Data.Remove("UUID");
111
112 string[] fields = new List<string>(data.Data.Keys).ToArray();
113 string[] values = new string[data.Data.Count];
114 int i = 0;
115 foreach (object o in data.Data.Values)
116 values[i++] = o.ToString();
117
118 SqliteCommand cmd = new SqliteCommand();
119
120 string update = "update `"+m_Realm+"` set ";
121 bool first = true;
122 foreach (string field in fields)
123 {
124 if (!first)
125 update += ", ";
126 update += "`" + field + "` = " + data.Data[field];
127
128 first = false;
129
130 }
131
132 update += " where UUID = '" + data.PrincipalID.ToString() + "'";
133
134 cmd.CommandText = update;
135
136 if (ExecuteNonQuery(cmd) < 1)
137 {
138 string insert = "insert into `" + m_Realm + "` (`UUID`, `" +
139 String.Join("`, `", fields) +
140 "`) values ('" + data.PrincipalID.ToString() + "', " + String.Join(", '", values) + "')";
141
142 cmd.CommandText = insert;
143
144 if (ExecuteNonQuery(cmd) < 1)
145 {
146 cmd.Dispose();
147 return false;
148 }
149 }
150
151 cmd.Dispose();
152
153 return true;
154 }
155
156 public bool SetDataItem(UUID principalID, string item, string value)
157 {
158 SqliteCommand cmd = new SqliteCommand("update `" + m_Realm +
159 "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'");
160
161 if (ExecuteNonQuery(cmd) > 0)
162 return true;
163
164 return false;
165 }
166
167 public bool SetToken(UUID principalID, string token, int lifetime)
168 {
169 if (System.Environment.TickCount - m_LastExpire > 30000)
170 DoExpire();
171
172 SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
173 "', '" + token + "', datetime('now, 'localtime', '+" + lifetime.ToString() + " minutes'))");
174
175 if (ExecuteNonQuery(cmd) > 0)
176 {
177 cmd.Dispose();
178 return true;
179 }
180
181 cmd.Dispose();
182 return false;
183 }
184
185 public bool CheckToken(UUID principalID, string token, int lifetime)
186 {
187 if (System.Environment.TickCount - m_LastExpire > 30000)
188 DoExpire();
189
190 SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now, 'localtime', '+" + lifetime.ToString() +
191 " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
192
193 if (ExecuteNonQuery(cmd) > 0)
194 {
195 cmd.Dispose();
196 return true;
197 }
198
199 cmd.Dispose();
200
201 return false;
202 }
203
204 private void DoExpire()
205 {
206 SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now, 'localtime')");
207 ExecuteNonQuery(cmd);
208
209 cmd.Dispose();
210
211 m_LastExpire = System.Environment.TickCount;
212 }
213 }
214}
diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs
index 12b2750..d745c92 100644
--- a/OpenSim/Data/SQLite/SQLiteFramework.cs
+++ b/OpenSim/Data/SQLite/SQLiteFramework.cs
@@ -40,12 +40,17 @@ 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 static SqliteConnection m_Connection;
44 private bool m_initialized;
44 45
45 protected SQLiteFramework(string connectionString) 46 protected SQLiteFramework(string connectionString)
46 { 47 {
47 m_Connection = new SqliteConnection(connectionString); 48 if (!m_initialized)
48 m_Connection.Open(); 49 {
50 m_Connection = new SqliteConnection(connectionString);
51 m_Connection.Open();
52 m_initialized = true;
53 }
49 } 54 }
50 55
51 ////////////////////////////////////////////////////////////// 56 //////////////////////////////////////////////////////////////
diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
index 8e91693..d29efa0 100644
--- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
+++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs
@@ -48,16 +48,23 @@ 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 private static bool m_initialized;
52
51 public SQLiteGenericTableHandler(string connectionString, 53 public SQLiteGenericTableHandler(string connectionString,
52 string realm, string storeName) : base(connectionString) 54 string realm, string storeName) : base(connectionString)
53 { 55 {
54 m_Realm = realm; 56 m_Realm = realm;
55 if (storeName != String.Empty) 57
58 if (!m_initialized)
56 { 59 {
57 Assembly assem = GetType().Assembly; 60 if (storeName != String.Empty)
61 {
62 Assembly assem = GetType().Assembly;
58 63
59 Migration m = new Migration(m_Connection, assem, storeName); 64 Migration m = new Migration(m_Connection, assem, storeName);
60 m.Update(); 65 m.Update();
66 }
67 m_initialized = true;
61 } 68 }
62 69
63 Type t = typeof(T); 70 Type t = typeof(T);
diff --git a/OpenSim/Data/SQLite/SQLiteUserAccountData.cs b/OpenSim/Data/SQLite/SQLiteUserAccountData.cs
new file mode 100644
index 0000000..50e8c23
--- /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.SqliteClient;
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}