aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite
diff options
context:
space:
mode:
authorSean Dague2008-04-02 15:24:31 +0000
committerSean Dague2008-04-02 15:24:31 +0000
commitc52c68f314c67c76c7181a6d0828f476290fbd66 (patch)
tree66ab347502892902a096fa985f31b25738eb1381 /OpenSim/Data/SQLite
parentreorganizing namespaces to put all the Data stuff into it's own namespace (diff)
downloadopensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.zip
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.gz
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.bz2
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.xz
whole lot more moving
Diffstat (limited to 'OpenSim/Data/SQLite')
-rw-r--r--OpenSim/Data/SQLite/Properties/AssemblyInfo.cs65
-rw-r--r--OpenSim/Data/SQLite/Resources/001_AssetStore.sql13
-rw-r--r--OpenSim/Data/SQLite/Resources/001_InventoryStore.sql26
-rw-r--r--OpenSim/Data/SQLite/Resources/001_RegionStore.sql122
-rw-r--r--OpenSim/Data/SQLite/Resources/001_UserStore.sql37
-rw-r--r--OpenSim/Data/SQLite/SQLiteAssetData.cs301
-rw-r--r--OpenSim/Data/SQLite/SQLiteGridData.cs234
-rw-r--r--OpenSim/Data/SQLite/SQLiteInventoryStore.cs664
-rw-r--r--OpenSim/Data/SQLite/SQLiteManager.cs282
-rw-r--r--OpenSim/Data/SQLite/SQLiteRegionData.cs1741
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserData.cs821
-rw-r--r--OpenSim/Data/SQLite/SQLiteUtils.cs269
12 files changed, 4575 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs
new file mode 100644
index 0000000..600405e
--- /dev/null
+++ b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs
@@ -0,0 +1,65 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.Reflection;
29using System.Runtime.InteropServices;
30
31// General Information about an assembly is controlled through the following
32// set of attributes. Change these attribute values to modify the information
33// associated with an assembly.
34
35[assembly : AssemblyTitle("OpenSim.Framework.Data.SQLite")]
36[assembly : AssemblyDescription("")]
37[assembly : AssemblyConfiguration("")]
38[assembly : AssemblyCompany("")]
39[assembly : AssemblyProduct("OpenSim.Framework.Data.SQLite")]
40[assembly : AssemblyCopyright("Copyright (c) OpenSimulator.org Developers 2007-2008")]
41[assembly : AssemblyTrademark("")]
42[assembly : AssemblyCulture("")]
43
44// Setting ComVisible to false makes the types in this assembly not visible
45// to COM components. If you need to access a type in this assembly from
46// COM, set the ComVisible attribute to true on that type.
47
48[assembly : ComVisible(false)]
49
50// The following GUID is for the ID of the typelib if this project is exposed to COM
51
52[assembly : Guid("6113d5ce-4547-49f4-9236-0dcc503457b1")]
53
54// Version information for an assembly consists of the following four values:
55//
56// Major Version
57// Minor Version
58// Build Number
59// Revision
60//
61// You can specify all the values or you can default the Revision and Build Numbers
62// by using the '*' as shown below:
63
64[assembly : AssemblyVersion("1.0.0.0")]
65[assembly : AssemblyFileVersion("1.0.0.0")]
diff --git a/OpenSim/Data/SQLite/Resources/001_AssetStore.sql b/OpenSim/Data/SQLite/Resources/001_AssetStore.sql
new file mode 100644
index 0000000..1dc05d8
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_AssetStore.sql
@@ -0,0 +1,13 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE assets(
4 UUID varchar(255) primary key,
5 Name varchar(255),
6 Description varchar(255),
7 Type integer,
8 InvType integer,
9 Local integer,
10 Temporary integer,
11 Data blob);
12
13COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql b/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql
new file mode 100644
index 0000000..e4951b0
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql
@@ -0,0 +1,26 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE inventoryitems(
4 UUID varchar(255) primary key,
5 assetID varchar(255),
6 assetType integer,
7 invType integer,
8 parentFolderID varchar(255),
9 avatarID varchar(255),
10 creatorsID varchar(255),
11 inventoryName varchar(255),
12 inventoryDescription varchar(255),
13 inventoryNextPermissions integer,
14 inventoryCurrentPermissions integer,
15 inventoryBasePermissions integer,
16 inventoryEveryOnePermissions integer);
17
18CREATE TABLE inventoryfolders(
19 UUID varchar(255) primary key,
20 name varchar(255),
21 agentID varchar(255),
22 parentID varchar(255),
23 type integer,
24 version integer);
25
26COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_RegionStore.sql b/OpenSim/Data/SQLite/Resources/001_RegionStore.sql
new file mode 100644
index 0000000..15f3d9f
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_RegionStore.sql
@@ -0,0 +1,122 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE prims(
4 UUID varchar(255) primary key,
5 RegionUUID varchar(255),
6 ParentID integer,
7 CreationDate integer,
8 Name varchar(255),
9 SceneGroupID varchar(255),
10 Text varchar(255),
11 Description varchar(255),
12 SitName varchar(255),
13 TouchName varchar(255),
14 CreatorID varchar(255),
15 OwnerID varchar(255),
16 GroupID varchar(255),
17 LastOwnerID varchar(255),
18 OwnerMask integer,
19 NextOwnerMask integer,
20 GroupMask integer,
21 EveryoneMask integer,
22 BaseMask integer,
23 PositionX float,
24 PositionY float,
25 PositionZ float,
26 GroupPositionX float,
27 GroupPositionY float,
28 GroupPositionZ float,
29 VelocityX float,
30 VelocityY float,
31 VelocityZ float,
32 AngularVelocityX float,
33 AngularVelocityY float,
34 AngularVelocityZ float,
35 AccelerationX float,
36 AccelerationY float,
37 AccelerationZ float,
38 RotationX float,
39 RotationY float,
40 RotationZ float,
41 RotationW float,
42 ObjectFlags integer,
43 SitTargetOffsetX float NOT NULL default 0,
44 SitTargetOffsetY float NOT NULL default 0,
45 SitTargetOffsetZ float NOT NULL default 0,
46 SitTargetOrientW float NOT NULL default 0,
47 SitTargetOrientX float NOT NULL default 0,
48 SitTargetOrientY float NOT NULL default 0,
49 SitTargetOrientZ float NOT NULL default 0);
50
51CREATE TABLE primshapes(UUID varchar(255) primary key,
52 Shape integer,
53 ScaleX float,
54 ScaleY float,
55 ScaleZ float,
56 PCode integer,
57 PathBegin integer,
58 PathEnd integer,
59 PathScaleX integer,
60 PathScaleY integer,
61 PathShearX integer,
62 PathShearY integer,
63 PathSkew integer,
64 PathCurve integer,
65 PathRadiusOffset integer,
66 PathRevolutions integer,
67 PathTaperX integer,
68 PathTaperY integer,
69 PathTwist integer,
70 PathTwistBegin integer,
71 ProfileBegin integer,
72 ProfileEnd integer,
73 ProfileCurve integer,
74 ProfileHollow integer,
75 State integer,
76 Texture blob,
77 ExtraParams blob);
78
79CREATE TABLE terrain(
80 RegionUUID varchar(255),
81 Revision integer,
82 Heightfield blob);
83
84CREATE TABLE land(
85 UUID varchar(255) primary key,
86 RegionUUID varchar(255),
87 LocalLandID string,
88 Bitmap blob,
89 Name varchar(255),
90 Desc varchar(255),
91 OwnerUUID varchar(36),
92 IsGroupOwned string,
93 Area integer,
94 AuctionID integer,
95 Category integer,
96 ClaimDate integer,
97 ClaimPrice integer,
98 GroupUUID varchar(255),
99 SalePrice integer,
100 LandStatus integer,
101 LandFlags string,
102 LandingType string,
103 MediaAutoScale string,
104 MediaTextureUUID varchar(255),
105 MediaURL varchar(255),
106 MusicURL varchar(255),
107 PassHours float,
108 PassPrice string,
109 SnapshotUUID varchar(255),
110 UserLocationX float,
111 UserLocationY float,
112 UserLocationZ float,
113 UserLookAtX float,
114 UserLookAtY float,
115 UserLookAtZ float);
116
117CREATE TABLE landaccesslist(
118 LandUUID varchar(255),
119 AccessUUID varchar(255),
120 Flags string);
121
122COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/001_UserStore.sql b/OpenSim/Data/SQLite/Resources/001_UserStore.sql
new file mode 100644
index 0000000..070e340
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/001_UserStore.sql
@@ -0,0 +1,37 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE users(
4 UUID varchar(255) primary key,
5 username varchar(255),
6 surname varchar(255),
7 passwordHash varchar(255),
8 passwordSalt varchar(255),
9 homeRegionX integer,
10 homeRegionY integer,
11 homeLocationX float,
12 homeLocationY float,
13 homeLocationZ float,
14 homeLookAtX float,
15 homeLookAtY float,
16 homeLookAtZ float,
17 created integer,
18 lastLogin integer,
19 rootInventoryFolderID varchar(255),
20 userInventoryURI varchar(255),
21 userAssetURI varchar(255),
22 profileCanDoMask integer,
23 profileWantDoMask integer,
24 profileAboutText varchar(255),
25 profileFirstText varchar(255),
26 profileImage varchar(255),
27 profileFirstImage varchar(255),
28 webLoginKey text default '00000000-0000-0000-0000-000000000000');
29
30CREATE TABLE userfriends(
31 ownerID varchar(255),
32 friendID varchar(255),
33 friendPerms integer,
34 ownerPerms integer,
35 datetimestamp integer);
36
37COMMIT;
diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs
new file mode 100644
index 0000000..afa73b1
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs
@@ -0,0 +1,301 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using System.Reflection;
31using libsecondlife;
32using Mono.Data.SqliteClient;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.SQLite
36{
37 /// <summary>
38 /// A User storage interface for the DB4o database system
39 /// </summary>
40 public class SQLiteAssetData : AssetDataBase
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 /// <summary>
48 /// Artificial constructor called upon plugin load
49 /// </summary>
50 private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
51 private const string DeleteAssetSQL = "delete from assets where UUID=:UUID";
52 private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, InvType, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :InvType, :Local, :Temporary, :Data)";
53 private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, InvType=:InvType, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID";
54 private const string assetSelect = "select * from assets";
55
56 private SqliteConnection m_conn;
57
58 public void Initialise(string dbfile, string dbname)
59 {
60 m_conn = new SqliteConnection("URI=file:" + dbfile + ",version=3");
61 m_conn.Open();
62 TestTables(m_conn);
63 return;
64 }
65
66 override public AssetBase FetchAsset(LLUUID uuid)
67 {
68
69 using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
70 {
71 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid)));
72 using (IDataReader reader = cmd.ExecuteReader())
73 {
74 if (reader.Read())
75 {
76 AssetBase asset = buildAsset(reader);
77 reader.Close();
78 return asset;
79 }
80 else
81 {
82 reader.Close();
83 return null;
84 }
85 }
86 }
87 }
88
89 override public void CreateAsset(AssetBase asset)
90 {
91 m_log.Info("[SQLITE]: Creating Asset " + Util.ToRawUuidString(asset.FullID));
92 if (ExistsAsset(asset.FullID))
93 {
94 m_log.Info("[SQLITE]: Asset exists already, ignoring.");
95 }
96 else
97 {
98 using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
99 {
100 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(asset.FullID)));
101 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
102 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
103 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
104 cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType));
105 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
106 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
107 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
108
109 cmd.ExecuteNonQuery();
110 }
111 }
112 }
113
114 override public void UpdateAsset(AssetBase asset)
115 {
116 LogAssetLoad(asset);
117
118 using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
119 {
120 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(asset.FullID)));
121 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
122 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
123 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
124 cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType));
125 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
126 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
127 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
128
129 cmd.ExecuteNonQuery();
130 }
131
132 }
133
134 private void LogAssetLoad(AssetBase asset)
135 {
136 string temporary = asset.Temporary ? "Temporary" : "Stored";
137 string local = asset.Local ? "Local" : "Remote";
138
139 int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
140
141 m_log.Info("[SQLITE]: " +
142 string.Format("Loaded {6} {5} Asset: [{0}][{3}/{4}] \"{1}\":{2} ({7} bytes)",
143 asset.FullID, asset.Name, asset.Description, asset.Type,
144 asset.InvType, temporary, local, assetLength));
145 }
146
147 override public bool ExistsAsset(LLUUID uuid)
148 {
149 using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
150 {
151 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid)));
152 using (IDataReader reader = cmd.ExecuteReader())
153 {
154 if(reader.Read())
155 {
156 reader.Close();
157 return true;
158 }
159 else
160 {
161 reader.Close();
162 return false;
163 }
164 }
165 }
166 }
167
168 public void DeleteAsset(LLUUID uuid)
169 {
170 using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
171 {
172 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid)));
173
174 cmd.ExecuteNonQuery();
175 }
176 }
177
178 override public void CommitAssets() // force a sync to the database
179 {
180 m_log.Info("[SQLITE]: Attempting commit");
181 // lock (ds)
182 // {
183 // da.Update(ds, "assets");
184 // ds.AcceptChanges();
185 // }
186 }
187
188 /***********************************************************************
189 *
190 * Database Definition Functions
191 *
192 * This should be db agnostic as we define them in ADO.NET terms
193 *
194 **********************************************************************/
195
196 private DataTable createAssetsTable()
197 {
198 DataTable assets = new DataTable("assets");
199
200 SQLiteUtil.createCol(assets, "UUID", typeof (String));
201 SQLiteUtil.createCol(assets, "Name", typeof (String));
202 SQLiteUtil.createCol(assets, "Description", typeof (String));
203 SQLiteUtil.createCol(assets, "Type", typeof (Int32));
204 SQLiteUtil.createCol(assets, "InvType", typeof (Int32));
205 SQLiteUtil.createCol(assets, "Local", typeof (Boolean));
206 SQLiteUtil.createCol(assets, "Temporary", typeof (Boolean));
207 SQLiteUtil.createCol(assets, "Data", typeof (Byte[]));
208 // Add in contraints
209 assets.PrimaryKey = new DataColumn[] {assets.Columns["UUID"]};
210 return assets;
211 }
212
213 /***********************************************************************
214 *
215 * Convert between ADO.NET <=> OpenSim Objects
216 *
217 * These should be database independant
218 *
219 **********************************************************************/
220
221 private AssetBase buildAsset(IDataReader row)
222 {
223 // TODO: this doesn't work yet because something more
224 // interesting has to be done to actually get these values
225 // back out. Not enough time to figure it out yet.
226 AssetBase asset = new AssetBase();
227
228 asset.FullID = new LLUUID((String) row["UUID"]);
229 asset.Name = (String) row["Name"];
230 asset.Description = (String) row["Description"];
231 asset.Type = Convert.ToSByte(row["Type"]);
232 asset.InvType = Convert.ToSByte(row["InvType"]);
233 asset.Local = Convert.ToBoolean(row["Local"]);
234 asset.Temporary = Convert.ToBoolean(row["Temporary"]);
235 asset.Data = (byte[]) row["Data"];
236 return asset;
237 }
238
239
240 /***********************************************************************
241 *
242 * Database Binding functions
243 *
244 * These will be db specific due to typing, and minor differences
245 * in databases.
246 *
247 **********************************************************************/
248
249 private void InitDB(SqliteConnection conn)
250 {
251 string createAssets = SQLiteUtil.defineTable(createAssetsTable());
252 SqliteCommand pcmd = new SqliteCommand(createAssets, conn);
253 pcmd.ExecuteNonQuery();
254 }
255
256 private bool TestTables(SqliteConnection conn)
257 {
258 SqliteCommand cmd = new SqliteCommand(assetSelect, conn);
259 SqliteDataAdapter pDa = new SqliteDataAdapter(cmd);
260 DataSet tmpDS = new DataSet();
261 try
262 {
263 pDa.Fill(tmpDS, "assets");
264 }
265 catch (SqliteSyntaxException)
266 {
267 m_log.Info("[SQLITE]: SQLite Database doesn't exist... creating");
268 InitDB(conn);
269 }
270 return true;
271 }
272
273 #region IPlugin interface
274
275 override public string Version
276 {
277 get
278 {
279 Module module = GetType().Module;
280 string dllName = module.Assembly.ManifestModule.Name;
281 Version dllVersion = module.Assembly.GetName().Version;
282
283 return
284 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
285 dllVersion.Revision);
286 }
287 }
288
289 override public void Initialise()
290 {
291 Initialise("AssetStorage.db", "");
292 }
293
294 override public string Name
295 {
296 get { return "SQLite Asset storage engine"; }
297 }
298
299 #endregion
300 }
301}
diff --git a/OpenSim/Data/SQLite/SQLiteGridData.cs b/OpenSim/Data/SQLite/SQLiteGridData.cs
new file mode 100644
index 0000000..94e8e50
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteGridData.cs
@@ -0,0 +1,234 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Security.Cryptography;
32using System.Text;
33using libsecondlife;
34
35namespace OpenSim.Framework.Data.SQLite
36{
37 /// <summary>
38 /// A Grid Interface to the SQLite database
39 /// </summary>
40 public class SQLiteGridData : GridDataBase
41 {
42 /// <summary>
43 /// A database manager
44 /// </summary>
45 private SQLiteManager database;
46
47 /// <summary>
48 /// Initialises the Grid Interface
49 /// </summary>
50 override public void Initialise()
51 {
52 database = new SQLiteManager("localhost", "db", "user", "password", "false");
53 }
54
55 /// <summary>
56 /// Shuts down the grid interface
57 /// </summary>
58 override public void Close()
59 {
60 database.Close();
61 }
62
63 /// <summary>
64 /// Returns the name of this grid interface
65 /// </summary>
66 /// <returns>A string containing the grid interface</returns>
67 override public string getName()
68 {
69 return "SQLite OpenGridData";
70 }
71
72 /// <summary>
73 /// Returns the version of this grid interface
74 /// </summary>
75 /// <returns>A string containing the version</returns>
76 override public string getVersion()
77 {
78 return "0.1";
79 }
80
81 /// <summary>
82 /// Returns a list of regions within the specified ranges
83 /// </summary>
84 /// <param name="a">minimum X coordinate</param>
85 /// <param name="b">minimum Y coordinate</param>
86 /// <param name="c">maximum X coordinate</param>
87 /// <param name="d">maximum Y coordinate</param>
88 /// <returns>An array of region profiles</returns>
89 override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d)
90 {
91 return null;
92 }
93
94 /// <summary>
95 /// Returns a sim profile from it's location
96 /// </summary>
97 /// <param name="handle">Region location handle</param>
98 /// <returns>Sim profile</returns>
99 override public RegionProfileData GetProfileByHandle(ulong handle)
100 {
101 Dictionary<string, string> param = new Dictionary<string, string>();
102 param["handle"] = handle.ToString();
103
104 IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param);
105 IDataReader reader = result.ExecuteReader();
106
107 RegionProfileData row = database.getRow(reader);
108 reader.Close();
109 result.Dispose();
110
111 return row;
112 }
113
114 /// <summary>
115 /// Returns a sim profile from it's Region name string
116 /// </summary>
117 /// <param name="regionName">The region name search query</param>
118 /// <returns>The sim profile</returns>
119 override public RegionProfileData GetProfileByString(string regionName)
120 {
121 if (regionName.Length > 2)
122 {
123 Dictionary<string, string> param = new Dictionary<string, string>();
124 // Add % because this is a like query.
125 param["?regionName"] = regionName + "%";
126 // Only returns one record or no record.
127 IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName LIMIT 1", param);
128 IDataReader reader = result.ExecuteReader();
129
130 RegionProfileData row = database.getRow(reader);
131 reader.Close();
132 result.Dispose();
133
134 return row;
135 }
136 else
137 {
138 //m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters");
139 return null;
140 }
141 }
142
143 /// <summary>
144 /// Returns a sim profile from it's UUID
145 /// </summary>
146 /// <param name="uuid">The region UUID</param>
147 /// <returns>The sim profile</returns>
148 override public RegionProfileData GetProfileByLLUUID(LLUUID uuid)
149 {
150 Dictionary<string, string> param = new Dictionary<string, string>();
151 param["uuid"] = uuid.ToString();
152
153 IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param);
154 IDataReader reader = result.ExecuteReader();
155
156 RegionProfileData row = database.getRow(reader);
157 reader.Close();
158 result.Dispose();
159
160 return row;
161 }
162
163 /// <summary>
164 /// // Returns a list of avatar and UUIDs that match the query
165 /// </summary>
166 public List<AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
167 {
168 //Do nothing yet
169 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
170 return returnlist;
171 }
172
173 /// <summary>
174 /// Adds a new specified region to the database
175 /// </summary>
176 /// <param name="profile">The profile to add</param>
177 /// <returns>A dataresponse enum indicating success</returns>
178 override public DataResponse AddProfile(RegionProfileData profile)
179 {
180 if (database.insertRow(profile))
181 {
182 return DataResponse.RESPONSE_OK;
183 }
184 else
185 {
186 return DataResponse.RESPONSE_ERROR;
187 }
188 }
189
190 /// <summary>
191 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
192 /// </summary>
193 /// <param name="uuid">The UUID of the challenger</param>
194 /// <param name="handle">The attempted regionHandle of the challenger</param>
195 /// <param name="authkey">The secret</param>
196 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
197 override public bool AuthenticateSim(LLUUID uuid, ulong handle, string authkey)
198 {
199 bool throwHissyFit = false; // Should be true by 1.0
200
201 if (throwHissyFit)
202 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
203
204 RegionProfileData data = GetProfileByLLUUID(uuid);
205
206 return (handle == data.regionHandle && authkey == data.regionSecret);
207 }
208
209 /// <summary>
210 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
211 /// </summary>
212 /// <remarks>This requires a security audit.</remarks>
213 /// <param name="uuid"></param>
214 /// <param name="handle"></param>
215 /// <param name="authhash"></param>
216 /// <param name="challenge"></param>
217 /// <returns></returns>
218 public bool AuthenticateSim(LLUUID uuid, ulong handle, string authhash, string challenge)
219 {
220 SHA512Managed HashProvider = new SHA512Managed();
221 ASCIIEncoding TextProvider = new ASCIIEncoding();
222
223 byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
224 byte[] hash = HashProvider.ComputeHash(stream);
225
226 return false;
227 }
228
229 override public ReservationData GetReservationAtPoint(uint x, uint y)
230 {
231 return null;
232 }
233 }
234}
diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
new file mode 100644
index 0000000..d31863f
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
@@ -0,0 +1,664 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using libsecondlife;
33using Mono.Data.SqliteClient;
34using OpenSim.Framework.Console;
35
36namespace OpenSim.Framework.Data.SQLite
37{
38 public class SQLiteInventoryStore : SQLiteUtil, IInventoryData
39 {
40 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
41
42 private const string invItemsSelect = "select * from inventoryitems";
43 private const string invFoldersSelect = "select * from inventoryfolders";
44
45 private DataSet ds;
46 private SqliteDataAdapter invItemsDa;
47 private SqliteDataAdapter invFoldersDa;
48
49 /// <summary>
50 /// Initialises the interface
51 /// </summary>
52 public void Initialise()
53 {
54 Initialise("inventoryStore.db", "inventoryDatabase");
55 }
56
57 public void Initialise(string dbfile, string dbname)
58 {
59 string connectionString = "URI=file:" + dbfile + ",version=3";
60
61 m_log.Info("[Inventory]: Sqlite - connecting: " + dbfile);
62 SqliteConnection conn = new SqliteConnection(connectionString);
63
64 conn.Open();
65
66 TestTables(conn);
67
68 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
69 invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
70 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
71
72 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
73 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
74
75 ds = new DataSet();
76
77 ds.Tables.Add(createInventoryFoldersTable());
78 invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
79 setupFoldersCommands(invFoldersDa, conn);
80 m_log.Info("[DATASTORE]: Populated Intentory Folders Definitions");
81
82 ds.Tables.Add(createInventoryItemsTable());
83 invItemsDa.Fill(ds.Tables["inventoryitems"]);
84 setupItemsCommands(invItemsDa, conn);
85 m_log.Info("[DATASTORE]: Populated Intentory Items Definitions");
86
87 ds.AcceptChanges();
88 }
89
90 public InventoryItemBase buildItem(DataRow row)
91 {
92 InventoryItemBase item = new InventoryItemBase();
93 item.inventoryID = new LLUUID((string) row["UUID"]);
94 item.assetID = new LLUUID((string) row["assetID"]);
95 item.assetType = Convert.ToInt32(row["assetType"]);
96 item.invType = Convert.ToInt32(row["invType"]);
97 item.parentFolderID = new LLUUID((string) row["parentFolderID"]);
98 item.avatarID = new LLUUID((string) row["avatarID"]);
99 item.creatorsID = new LLUUID((string) row["creatorsID"]);
100 item.inventoryName = (string) row["inventoryName"];
101 item.inventoryDescription = (string) row["inventoryDescription"];
102
103 item.inventoryNextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]);
104 item.inventoryCurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]);
105 item.inventoryBasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]);
106 item.inventoryEveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]);
107 return item;
108 }
109
110 private void fillItemRow(DataRow row, InventoryItemBase item)
111 {
112 row["UUID"] = Util.ToRawUuidString(item.inventoryID);
113 row["assetID"] = Util.ToRawUuidString(item.assetID);
114 row["assetType"] = item.assetType;
115 row["invType"] = item.invType;
116 row["parentFolderID"] = Util.ToRawUuidString(item.parentFolderID);
117 row["avatarID"] = Util.ToRawUuidString(item.avatarID);
118 row["creatorsID"] = Util.ToRawUuidString(item.creatorsID);
119 row["inventoryName"] = item.inventoryName;
120 row["inventoryDescription"] = item.inventoryDescription;
121
122 row["inventoryNextPermissions"] = item.inventoryNextPermissions;
123 row["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions;
124 row["inventoryBasePermissions"] = item.inventoryBasePermissions;
125 row["inventoryEveryOnePermissions"] = item.inventoryEveryOnePermissions;
126 }
127
128 private void addFolder(InventoryFolderBase folder)
129 {
130 lock (ds)
131 {
132 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
133
134 DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID));
135 if (inventoryRow == null)
136 {
137 inventoryRow = inventoryFolderTable.NewRow();
138 fillFolderRow(inventoryRow, folder);
139 inventoryFolderTable.Rows.Add(inventoryRow);
140 }
141 else
142 {
143 fillFolderRow(inventoryRow, folder);
144 }
145
146 invFoldersDa.Update(ds, "inventoryfolders");
147 }
148 }
149
150 private void moveFolder(InventoryFolderBase folder)
151 {
152 lock (ds)
153 {
154 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
155
156 DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID));
157 if (inventoryRow == null)
158 {
159 inventoryRow = inventoryFolderTable.NewRow();
160 fillFolderRow(inventoryRow, folder);
161 inventoryFolderTable.Rows.Add(inventoryRow);
162 }
163 else
164 {
165 moveFolderRow(inventoryRow, folder);
166 }
167
168 invFoldersDa.Update(ds, "inventoryfolders");
169 }
170 }
171
172 private void addItem(InventoryItemBase item)
173 {
174 lock (ds)
175 {
176 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
177
178 DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(item.inventoryID));
179 if (inventoryRow == null)
180 {
181 inventoryRow = inventoryItemTable.NewRow();
182 fillItemRow(inventoryRow, item);
183 inventoryItemTable.Rows.Add(inventoryRow);
184 }
185 else
186 {
187 fillItemRow(inventoryRow, item);
188 }
189 invItemsDa.Update(ds, "inventoryitems");
190 }
191 }
192
193 public void Shutdown()
194 {
195 // TODO: DataSet commit
196 }
197
198 /// <summary>
199 /// Closes the interface
200 /// </summary>
201 public void Close()
202 {
203 }
204
205 /// <summary>
206 /// The plugin being loaded
207 /// </summary>
208 /// <returns>A string containing the plugin name</returns>
209 public string getName()
210 {
211 return "SQLite Inventory Data Interface";
212 }
213
214 /// <summary>
215 /// The plugins version
216 /// </summary>
217 /// <returns>A string containing the plugin version</returns>
218 public string getVersion()
219 {
220 Module module = GetType().Module;
221 string dllName = module.Assembly.ManifestModule.Name;
222 Version dllVersion = module.Assembly.GetName().Version;
223
224
225 return
226 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
227 dllVersion.Revision);
228 }
229
230 /// <summary>
231 /// Returns a list of inventory items contained within the specified folder
232 /// </summary>
233 /// <param name="folderID">The UUID of the target folder</param>
234 /// <returns>A List of InventoryItemBase items</returns>
235 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
236 {
237 lock (ds)
238 {
239 List<InventoryItemBase> retval = new List<InventoryItemBase>();
240 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
241 string selectExp = "parentFolderID = '" + Util.ToRawUuidString(folderID) + "'";
242 DataRow[] rows = inventoryItemTable.Select(selectExp);
243 foreach (DataRow row in rows)
244 {
245 retval.Add(buildItem(row));
246 }
247
248 return retval;
249 }
250 }
251
252 /// <summary>
253 /// Returns a list of the root folders within a users inventory
254 /// </summary>
255 /// <param name="user">The user whos inventory is to be searched</param>
256 /// <returns>A list of folder objects</returns>
257 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
258 {
259 return new List<InventoryFolderBase>();
260 }
261
262 // see InventoryItemBase.getUserRootFolder
263 public InventoryFolderBase getUserRootFolder(LLUUID user)
264 {
265 lock (ds)
266 {
267 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
268 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
269 string selectExp = "agentID = '" + Util.ToRawUuidString(user) + "' AND parentID = '" +
270 Util.ToRawUuidString(LLUUID.Zero) + "'";
271 DataRow[] rows = inventoryFolderTable.Select(selectExp);
272 foreach (DataRow row in rows)
273 {
274 folders.Add(buildFolder(row));
275 }
276
277 // There should only ever be one root folder for a user. However, if there's more
278 // than one we'll simply use the first one rather than failing. It would be even
279 // nicer to print some message to this effect, but this feels like it's too low a
280 // to put such a message out, and it's too minor right now to spare the time to
281 // suitably refactor.
282 if (folders.Count > 0)
283 {
284 return folders[0];
285 }
286
287 return null;
288 }
289 }
290
291 /// <summary>
292 /// Append a list of all the child folders of a parent folder
293 /// </summary>
294 /// <param name="folders">list where folders will be appended</param>
295 /// <param name="parentID">ID of parent</param>
296 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
297 {
298 lock (ds)
299 {
300 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
301 string selectExp = "parentID = '" + Util.ToRawUuidString(parentID) + "'";
302 DataRow[] rows = inventoryFolderTable.Select(selectExp);
303 foreach (DataRow row in rows)
304 {
305 folders.Add(buildFolder(row));
306 }
307 }
308 }
309
310 /// <summary>
311 /// Returns a list of inventory folders contained in the folder 'parentID'
312 /// </summary>
313 /// <param name="parentID">The folder to get subfolders for</param>
314 /// <returns>A list of inventory folders</returns>
315 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
316 {
317 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
318 getInventoryFolders(ref folders, Util.ToRawUuidString(parentID));
319 return folders;
320 }
321
322 // See IInventoryData
323 public List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
324 {
325 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
326 getInventoryFolders(ref folders, Util.ToRawUuidString(parentID));
327
328 for (int i = 0; i < folders.Count; i++)
329 getInventoryFolders(ref folders, Util.ToRawUuidString(folders[i].folderID));
330
331 return folders;
332 }
333
334 /// <summary>
335 /// Returns an inventory item by its UUID
336 /// </summary>
337 /// <param name="item">The UUID of the item to be returned</param>
338 /// <returns>A class containing item information</returns>
339 public InventoryItemBase getInventoryItem(LLUUID item)
340 {
341 lock (ds)
342 {
343 DataRow row = ds.Tables["inventoryitems"].Rows.Find(Util.ToRawUuidString(item));
344 if (row != null)
345 {
346 return buildItem(row);
347 }
348 else
349 {
350 return null;
351 }
352 }
353 }
354
355 /// <summary>
356 /// Returns a specified inventory folder by its UUID
357 /// </summary>
358 /// <param name="folder">The UUID of the folder to be returned</param>
359 /// <returns>A class containing folder information</returns>
360 public InventoryFolderBase getInventoryFolder(LLUUID folder)
361 {
362 // TODO: Deep voodoo here. If you enable this code then
363 // multi region breaks. No idea why, but I figured it was
364 // better to leave multi region at this point. It does mean
365 // that you don't get to see system textures why creating
366 // clothes and the like. :(
367 lock (ds)
368 {
369 DataRow row = ds.Tables["inventoryfolders"].Rows.Find(Util.ToRawUuidString(folder));
370 if (row != null)
371 {
372 return buildFolder(row);
373 }
374 else
375 {
376 return null;
377 }
378 }
379 }
380
381 /// <summary>
382 /// Creates a new inventory item based on item
383 /// </summary>
384 /// <param name="item">The item to be created</param>
385 public void addInventoryItem(InventoryItemBase item)
386 {
387 addItem(item);
388 }
389
390 /// <summary>
391 /// Updates an inventory item with item (updates based on ID)
392 /// </summary>
393 /// <param name="item">The updated item</param>
394 public void updateInventoryItem(InventoryItemBase item)
395 {
396 addItem(item);
397 }
398
399 /// <summary>
400 ///
401 /// </summary>
402 /// <param name="item"></param>
403 public void deleteInventoryItem(LLUUID itemID)
404 {
405 lock (ds)
406 {
407 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
408
409 DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(itemID));
410 if (inventoryRow != null)
411 {
412 inventoryRow.Delete();
413 }
414
415 invItemsDa.Update(ds, "inventoryitems");
416 }
417 }
418
419 /// <summary>
420 /// Delete all items in the specified folder
421 /// </summary>
422 /// <param name="folderId">id of the folder, whose item content should be deleted</param>
423 //!TODO, this is horribly inefficient, but I don't want to ruin the overall structure of this implementation
424 private void deleteItemsInFolder(LLUUID folderId)
425 {
426 List<InventoryItemBase> items = getInventoryInFolder(Util.ToRawUuidString(folderId));
427
428 foreach (InventoryItemBase i in items)
429 deleteInventoryItem(Util.ToRawUuidString(i.inventoryID));
430 }
431
432 /// <summary>
433 /// Adds a new folder specified by folder
434 /// </summary>
435 /// <param name="folder">The inventory folder</param>
436 public void addInventoryFolder(InventoryFolderBase folder)
437 {
438 addFolder(folder);
439 }
440
441 /// <summary>
442 /// Updates a folder based on its ID with folder
443 /// </summary>
444 /// <param name="folder">The inventory folder</param>
445 public void updateInventoryFolder(InventoryFolderBase folder)
446 {
447 addFolder(folder);
448 }
449
450 /// <summary>
451 /// Moves a folder based on its ID with folder
452 /// </summary>
453 /// <param name="folder">The inventory folder</param>
454 public void moveInventoryFolder(InventoryFolderBase folder)
455 {
456 moveFolder(folder);
457 }
458
459 /// <summary>
460 /// Delete a folder
461 /// </summary>
462 /// <remarks>
463 /// This will clean-up any child folders and child items as well
464 /// </remarks>
465 /// <param name="item"></param>
466 public void deleteInventoryFolder(LLUUID folderID)
467 {
468 lock (ds)
469 {
470 List<InventoryFolderBase> subFolders = getFolderHierarchy(Util.ToRawUuidString(folderID));
471
472 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
473 DataRow inventoryRow;
474
475 //Delete all sub-folders
476 foreach (InventoryFolderBase f in subFolders)
477 {
478 inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(f.folderID));
479 if (inventoryRow != null)
480 {
481 deleteItemsInFolder(Util.ToRawUuidString(f.folderID));
482 inventoryRow.Delete();
483 }
484 }
485
486 //Delete the actual row
487 inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folderID));
488 if (inventoryRow != null)
489 {
490 deleteItemsInFolder(Util.ToRawUuidString(folderID));
491 inventoryRow.Delete();
492 }
493
494 invFoldersDa.Update(ds, "inventoryfolders");
495 }
496 }
497
498 /***********************************************************************
499 *
500 * Data Table definitions
501 *
502 **********************************************************************/
503
504 private static DataTable createInventoryItemsTable()
505 {
506 DataTable inv = new DataTable("inventoryitems");
507
508 createCol(inv, "UUID", typeof (String)); //inventoryID
509 createCol(inv, "assetID", typeof (String));
510 createCol(inv, "assetType", typeof (Int32));
511 createCol(inv, "invType", typeof (Int32));
512 createCol(inv, "parentFolderID", typeof (String));
513 createCol(inv, "avatarID", typeof (String));
514 createCol(inv, "creatorsID", typeof (String));
515
516 createCol(inv, "inventoryName", typeof (String));
517 createCol(inv, "inventoryDescription", typeof (String));
518 // permissions
519 createCol(inv, "inventoryNextPermissions", typeof (Int32));
520 createCol(inv, "inventoryCurrentPermissions", typeof (Int32));
521 createCol(inv, "inventoryBasePermissions", typeof (Int32));
522 createCol(inv, "inventoryEveryOnePermissions", typeof (Int32));
523
524 inv.PrimaryKey = new DataColumn[] {inv.Columns["UUID"]};
525 return inv;
526 }
527
528 private DataTable createInventoryFoldersTable()
529 {
530 DataTable fol = new DataTable("inventoryfolders");
531
532 createCol(fol, "UUID", typeof (String)); //folderID
533 createCol(fol, "name", typeof (String));
534 createCol(fol, "agentID", typeof (String));
535 createCol(fol, "parentID", typeof (String));
536 createCol(fol, "type", typeof (Int32));
537 createCol(fol, "version", typeof (Int32));
538
539 fol.PrimaryKey = new DataColumn[] {fol.Columns["UUID"]};
540 return fol;
541 }
542
543 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
544 {
545 lock (ds)
546 {
547 da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]);
548 da.InsertCommand.Connection = conn;
549
550 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]);
551 da.UpdateCommand.Connection = conn;
552
553 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
554 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
555 delete.Connection = conn;
556 da.DeleteCommand = delete;
557 }
558 }
559
560 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
561 {
562 lock (ds)
563 {
564 da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
565 da.InsertCommand.Connection = conn;
566
567 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
568 da.UpdateCommand.Connection = conn;
569
570 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
571 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
572 delete.Connection = conn;
573 da.DeleteCommand = delete;
574 }
575 }
576
577 private InventoryFolderBase buildFolder(DataRow row)
578 {
579 InventoryFolderBase folder = new InventoryFolderBase();
580 folder.folderID = new LLUUID((string) row["UUID"]);
581 folder.name = (string) row["name"];
582 folder.agentID = new LLUUID((string) row["agentID"]);
583 folder.parentID = new LLUUID((string) row["parentID"]);
584 folder.type = Convert.ToInt16(row["type"]);
585 folder.version = Convert.ToUInt16(row["version"]);
586 return folder;
587 }
588
589 private void fillFolderRow(DataRow row, InventoryFolderBase folder)
590 {
591 row["UUID"] = Util.ToRawUuidString(folder.folderID);
592 row["name"] = folder.name;
593 row["agentID"] = Util.ToRawUuidString(folder.agentID);
594 row["parentID"] = Util.ToRawUuidString(folder.parentID);
595 row["type"] = folder.type;
596 row["version"] = folder.version;
597 }
598
599 private void moveFolderRow(DataRow row, InventoryFolderBase folder)
600 {
601 row["UUID"] = Util.ToRawUuidString(folder.folderID);
602 row["parentID"] = Util.ToRawUuidString(folder.parentID);
603 }
604
605 /***********************************************************************
606 *
607 * Test and Initialization code
608 *
609 **********************************************************************/
610
611 private void InitDB(SqliteConnection conn)
612 {
613 string createInventoryItems = defineTable(createInventoryItemsTable());
614 string createInventoryFolders = defineTable(createInventoryFoldersTable());
615
616 SqliteCommand pcmd = new SqliteCommand(createInventoryItems, conn);
617 SqliteCommand scmd = new SqliteCommand(createInventoryFolders, conn);
618
619 pcmd.ExecuteNonQuery();
620 scmd.ExecuteNonQuery();
621 }
622
623 private bool TestTables(SqliteConnection conn)
624 {
625 SqliteCommand invItemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
626 SqliteDataAdapter pDa = new SqliteDataAdapter(invItemsSelectCmd);
627 SqliteCommand invFoldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
628 SqliteDataAdapter sDa = new SqliteDataAdapter(invFoldersSelectCmd);
629
630 DataSet tmpDS = new DataSet();
631 try
632 {
633 pDa.Fill(tmpDS, "inventoryitems");
634 sDa.Fill(tmpDS, "inventoryfolders");
635 }
636 catch (SqliteSyntaxException)
637 {
638 m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
639 InitDB(conn);
640 }
641
642 pDa.Fill(tmpDS, "inventoryitems");
643 sDa.Fill(tmpDS, "inventoryfolders");
644
645 foreach (DataColumn col in createInventoryItemsTable().Columns)
646 {
647 if (! tmpDS.Tables["inventoryitems"].Columns.Contains(col.ColumnName))
648 {
649 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
650 return false;
651 }
652 }
653 foreach (DataColumn col in createInventoryFoldersTable().Columns)
654 {
655 if (! tmpDS.Tables["inventoryfolders"].Columns.Contains(col.ColumnName))
656 {
657 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
658 return false;
659 }
660 }
661 return true;
662 }
663 }
664}
diff --git a/OpenSim/Data/SQLite/SQLiteManager.cs b/OpenSim/Data/SQLite/SQLiteManager.cs
new file mode 100644
index 0000000..b383b0d
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteManager.cs
@@ -0,0 +1,282 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SQLite;
32using libsecondlife;
33using Mono.Data.SqliteClient;
34using OpenSim.Framework.Console;
35
36namespace OpenSim.Framework.Data.SQLite
37{
38 internal class SQLiteManager : SQLiteUtil
39 {
40 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
41
42 private IDbConnection dbcon;
43
44 /// <summary>
45 /// Initialises and creates a new SQLite connection and maintains it.
46 /// </summary>
47 /// <param name="hostname">The SQLite server being connected to</param>
48 /// <param name="database">The name of the SQLite database being used</param>
49 /// <param name="username">The username logging into the database</param>
50 /// <param name="password">The password for the user logging in</param>
51 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param>
52 public SQLiteManager(string hostname, string database, string username, string password, string cpooling)
53 {
54 try
55 {
56 string connectionString = "URI=file:GridServerSqlite.db;";
57 dbcon = new SQLiteConnection(connectionString);
58
59 dbcon.Open();
60 }
61 catch (Exception e)
62 {
63 throw new Exception("Error initialising SQLite Database: " + e.ToString());
64 }
65 }
66
67 /// <summary>
68 /// Shuts down the database connection
69 /// </summary>
70 public void Close()
71 {
72 dbcon.Close();
73 dbcon = null;
74 }
75
76 /// <summary>
77 /// Runs a query with protection against SQL Injection by using parameterised input.
78 /// </summary>
79 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
80 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
81 /// <returns>A SQLite DB Command</returns>
82 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
83 {
84 SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand();
85 dbcommand.CommandText = sql;
86 foreach (KeyValuePair<string, string> param in parameters)
87 {
88 SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value);
89 dbcommand.Parameters.Add(paramx);
90 }
91
92 return (IDbCommand) dbcommand;
93 }
94
95// TODO: unused
96// private bool TestTables(SQLiteConnection conn)
97// {
98// SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM regions", conn);
99// SQLiteDataAdapter pDa = new SQLiteDataAdapter(cmd);
100// DataSet tmpDS = new DataSet();
101// try
102// {
103// pDa.Fill(tmpDS, "regions");
104// }
105// catch (SqliteSyntaxException)
106// {
107// m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
108// InitDB(conn);
109// }
110// return true;
111// }
112
113// TODO: unused
114// private DataTable createRegionsTable()
115// {
116// DataTable regions = new DataTable("regions");
117
118// createCol(regions, "regionHandle", typeof (ulong));
119// createCol(regions, "regionName", typeof (String));
120// createCol(regions, "uuid", typeof (String));
121
122// createCol(regions, "regionRecvKey", typeof (String));
123// createCol(regions, "regionSecret", typeof (String));
124// createCol(regions, "regionSendKey", typeof (String));
125
126// createCol(regions, "regionDataURI", typeof (String));
127// createCol(regions, "serverIP", typeof (String));
128// createCol(regions, "serverPort", typeof (String));
129// createCol(regions, "serverURI", typeof (String));
130
131
132// createCol(regions, "locX", typeof (uint));
133// createCol(regions, "locY", typeof (uint));
134// createCol(regions, "locZ", typeof (uint));
135
136// createCol(regions, "eastOverrideHandle", typeof (ulong));
137// createCol(regions, "westOverrideHandle", typeof (ulong));
138// createCol(regions, "southOverrideHandle", typeof (ulong));
139// createCol(regions, "northOverrideHandle", typeof (ulong));
140
141// createCol(regions, "regionAssetURI", typeof (String));
142// createCol(regions, "regionAssetRecvKey", typeof (String));
143// createCol(regions, "regionAssetSendKey", typeof (String));
144
145// createCol(regions, "regionUserURI", typeof (String));
146// createCol(regions, "regionUserRecvKey", typeof (String));
147// createCol(regions, "regionUserSendKey", typeof (String));
148
149// // Add in contraints
150// regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
151// return regions;
152// }
153
154// TODO: unused
155// private void InitDB(SQLiteConnection conn)
156// {
157// string createUsers = defineTable(createRegionsTable());
158// SQLiteCommand pcmd = new SQLiteCommand(createUsers, conn);
159// conn.Open();
160// pcmd.ExecuteNonQuery();
161// conn.Close();
162// }
163
164 /// <summary>
165 /// Reads a region row from a database reader
166 /// </summary>
167 /// <param name="reader">An active database reader</param>
168 /// <returns>A region profile</returns>
169 public RegionProfileData getRow(IDataReader reader)
170 {
171 RegionProfileData retval = new RegionProfileData();
172
173 if (reader.Read())
174 {
175 // Region Main
176 retval.regionHandle = (ulong) reader["regionHandle"];
177 retval.regionName = (string) reader["regionName"];
178 retval.UUID = new LLUUID((string) reader["uuid"]);
179
180 // Secrets
181 retval.regionRecvKey = (string) reader["regionRecvKey"];
182 retval.regionSecret = (string) reader["regionSecret"];
183 retval.regionSendKey = (string) reader["regionSendKey"];
184
185 // Region Server
186 retval.regionDataURI = (string) reader["regionDataURI"];
187 retval.regionOnline = false; // Needs to be pinged before this can be set.
188 retval.serverIP = (string) reader["serverIP"];
189 retval.serverPort = (uint) reader["serverPort"];
190 retval.serverURI = (string) reader["serverURI"];
191
192 // Location
193 retval.regionLocX = (uint) ((int) reader["locX"]);
194 retval.regionLocY = (uint) ((int) reader["locY"]);
195 retval.regionLocZ = (uint) ((int) reader["locZ"]);
196
197 // Neighbours - 0 = No Override
198 retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"];
199 retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"];
200 retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"];
201 retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"];
202
203 // Assets
204 retval.regionAssetURI = (string) reader["regionAssetURI"];
205 retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
206 retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
207
208 // Userserver
209 retval.regionUserURI = (string) reader["regionUserURI"];
210 retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
211 retval.regionUserSendKey = (string) reader["regionUserSendKey"];
212 }
213 else
214 {
215 throw new Exception("No rows to return");
216 }
217 return retval;
218 }
219
220 /// <summary>
221 /// Inserts a new region into the database
222 /// </summary>
223 /// <param name="profile">The region to insert</param>
224 /// <returns>Success?</returns>
225 public bool insertRow(RegionProfileData profile)
226 {
227 string sql =
228 "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
229 sql +=
230 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
231 sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES ";
232
233 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
234 sql +=
235 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
236 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
237
238 Dictionary<string, string> parameters = new Dictionary<string, string>();
239
240 parameters["regionHandle"] = profile.regionHandle.ToString();
241 parameters["regionName"] = profile.regionName;
242 parameters["uuid"] = profile.UUID.ToString();
243 parameters["regionRecvKey"] = profile.regionRecvKey;
244 parameters["regionSendKey"] = profile.regionSendKey;
245 parameters["regionDataURI"] = profile.regionDataURI;
246 parameters["serverIP"] = profile.serverIP;
247 parameters["serverPort"] = profile.serverPort.ToString();
248 parameters["serverURI"] = profile.serverURI;
249 parameters["locX"] = profile.regionLocX.ToString();
250 parameters["locY"] = profile.regionLocY.ToString();
251 parameters["locZ"] = profile.regionLocZ.ToString();
252 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
253 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
254 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
255 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
256 parameters["regionAssetURI"] = profile.regionAssetURI;
257 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
258 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
259 parameters["regionUserURI"] = profile.regionUserURI;
260 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
261 parameters["regionUserSendKey"] = profile.regionUserSendKey;
262
263 bool returnval = false;
264
265 try
266 {
267 IDbCommand result = Query(sql, parameters);
268
269 if (result.ExecuteNonQuery() == 1)
270 returnval = true;
271
272 result.Dispose();
273 }
274 catch (Exception)
275 {
276 return false;
277 }
278
279 return returnval;
280 }
281 }
282}
diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs
new file mode 100644
index 0000000..77161a4
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs
@@ -0,0 +1,1741 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.IO;
32using libsecondlife;
33using Mono.Data.SqliteClient;
34using OpenSim.Framework;
35using OpenSim.Framework.Console;
36using OpenSim.Region.Environment.Interfaces;
37using OpenSim.Region.Environment.Scenes;
38
39namespace OpenSim.Framework.Data.SQLite
40{
41 public class SQLiteRegionData : IRegionDataStore
42 {
43 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
44
45 private const string primSelect = "select * from prims";
46 private const string shapeSelect = "select * from primshapes";
47 private const string itemsSelect = "select * from primitems";
48 private const string terrainSelect = "select * from terrain limit 1";
49 private const string landSelect = "select * from land";
50 private const string landAccessListSelect = "select distinct * from landaccesslist";
51
52 private DataSet ds;
53 private SqliteDataAdapter primDa;
54 private SqliteDataAdapter shapeDa;
55 private SqliteDataAdapter itemsDa;
56 private SqliteDataAdapter terrainDa;
57 private SqliteDataAdapter landDa;
58 private SqliteDataAdapter landAccessListDa;
59
60 private SqliteConnection m_conn;
61
62 private String m_connectionString;
63
64 // Temporary attribute while this is experimental
65 private bool persistPrimInventories;
66
67 /***********************************************************************
68 *
69 * Public Interface Functions
70 *
71 **********************************************************************/
72
73 // see IRegionDataStore
74 public void Initialise(string connectionString, bool persistPrimInventories)
75 {
76 m_connectionString = connectionString;
77 this.persistPrimInventories = persistPrimInventories;
78
79 ds = new DataSet();
80
81 m_log.Info("[DATASTORE]: Sqlite - connecting: " + connectionString);
82 m_conn = new SqliteConnection(m_connectionString);
83 m_conn.Open();
84
85 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
86 primDa = new SqliteDataAdapter(primSelectCmd);
87 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
88
89 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
90 shapeDa = new SqliteDataAdapter(shapeSelectCmd);
91 // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
92
93 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
94 itemsDa = new SqliteDataAdapter(itemsSelectCmd);
95
96 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
97 terrainDa = new SqliteDataAdapter(terrainSelectCmd);
98
99 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
100 landDa = new SqliteDataAdapter(landSelectCmd);
101
102 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
103 landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
104
105 // We fill the data set, now we've got copies in memory for the information
106 // TODO: see if the linkage actually holds.
107 // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema");
108 TestTables(m_conn);
109
110 lock (ds)
111 {
112 ds.Tables.Add(createPrimTable());
113 setupPrimCommands(primDa, m_conn);
114 primDa.Fill(ds.Tables["prims"]);
115
116 ds.Tables.Add(createShapeTable());
117 setupShapeCommands(shapeDa, m_conn);
118
119 if (persistPrimInventories)
120 {
121 ds.Tables.Add(createItemsTable());
122 setupItemsCommands(itemsDa, m_conn);
123 itemsDa.Fill(ds.Tables["primitems"]);
124 }
125
126 ds.Tables.Add(createTerrainTable());
127 setupTerrainCommands(terrainDa, m_conn);
128
129 ds.Tables.Add(createLandTable());
130 setupLandCommands(landDa, m_conn);
131
132 ds.Tables.Add(createLandAccessListTable());
133 setupLandAccessCommands(landAccessListDa, m_conn);
134
135 // WORKAROUND: This is a work around for sqlite on
136 // windows, which gets really unhappy with blob columns
137 // that have no sample data in them. At some point we
138 // need to actually find a proper way to handle this.
139 try
140 {
141 shapeDa.Fill(ds.Tables["primshapes"]);
142 }
143 catch (Exception)
144 {
145 m_log.Info("[DATASTORE]: Caught fill error on primshapes table");
146 }
147
148 try
149 {
150 terrainDa.Fill(ds.Tables["terrain"]);
151 }
152 catch (Exception)
153 {
154 m_log.Info("[DATASTORE]: Caught fill error on terrain table");
155 }
156
157 try
158 {
159 landDa.Fill(ds.Tables["land"]);
160 }
161 catch (Exception)
162 {
163 m_log.Info("[DATASTORE]: Caught fill error on land table");
164 }
165
166 try
167 {
168 landAccessListDa.Fill(ds.Tables["landaccesslist"]);
169 }
170 catch (Exception)
171 {
172 m_log.Info("[DATASTORE]: Caught fill error on landaccesslist table");
173 }
174 return;
175 }
176 }
177
178 public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID)
179 {
180 lock (ds)
181 {
182 foreach (SceneObjectPart prim in obj.Children.Values)
183 {
184 if ((prim.ObjectFlags & (uint) LLObject.ObjectFlags.Physics) == 0)
185 {
186 m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
187 addPrim(prim, Util.ToRawUuidString(obj.UUID), Util.ToRawUuidString(regionUUID));
188 }
189 else if (prim.Stopped)
190 {
191 //m_log.Info("[DATASTORE]: " +
192 //"Adding stopped obj: " + obj.UUID + " to region: " + regionUUID);
193 //addPrim(prim, Util.ToRawUuidString(obj.UUID), Util.ToRawUuidString(regionUUID));
194 }
195 else
196 {
197 // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
198 }
199 }
200 }
201
202 Commit();
203 // m_log.Info("[Dump of prims]: " + ds.GetXml());
204 }
205
206 public void RemoveObject(LLUUID obj, LLUUID regionUUID)
207 {
208 m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID);
209
210 DataTable prims = ds.Tables["prims"];
211 DataTable shapes = ds.Tables["primshapes"];
212
213 string selectExp = "SceneGroupID = '" + Util.ToRawUuidString(obj) + "'";
214 lock (ds)
215 {
216 DataRow[] primRows = prims.Select(selectExp);
217 foreach (DataRow row in primRows)
218 {
219 // Remove shape rows
220 LLUUID uuid = new LLUUID((string) row["UUID"]);
221 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(uuid));
222 if (shapeRow != null)
223 {
224 shapeRow.Delete();
225 }
226
227 if (persistPrimInventories)
228 {
229 RemoveItems(uuid);
230 }
231
232 // Remove prim row
233 row.Delete();
234 }
235 }
236
237 Commit();
238 }
239
240 /// <summary>
241 /// Remove all persisted items of the given prim.
242 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
243 /// </summary>
244 private void RemoveItems(LLUUID uuid)
245 {
246 DataTable items = ds.Tables["primitems"];
247
248 String sql = String.Format("primID = '{0}'", uuid);
249 DataRow[] itemRows = items.Select(sql);
250
251 foreach (DataRow itemRow in itemRows)
252 {
253 itemRow.Delete();
254 }
255 }
256
257 /// <summary>
258 /// Load persisted objects from region storage.
259 /// </summary>
260 /// <param name="regionUUID"></param>
261 /// <returns>List of loaded groups</returns>
262 public List<SceneObjectGroup> LoadObjects(LLUUID regionUUID)
263 {
264 Dictionary<LLUUID, SceneObjectGroup> createdObjects = new Dictionary<LLUUID, SceneObjectGroup>();
265
266 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
267
268 DataTable prims = ds.Tables["prims"];
269 DataTable shapes = ds.Tables["primshapes"];
270
271 string byRegion = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'";
272 string orderByParent = "ParentID ASC";
273
274 lock (ds)
275 {
276 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
277 m_log.Info("[DATASTORE]: " +
278 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
279
280 foreach (DataRow primRow in primsForRegion)
281 {
282 try
283 {
284 SceneObjectPart prim = null;
285
286 string uuid = (string) primRow["UUID"];
287 string objID = (string) primRow["SceneGroupID"];
288 if (uuid == objID) //is new SceneObjectGroup ?
289 {
290 SceneObjectGroup group = new SceneObjectGroup();
291 prim = buildPrim(primRow);
292 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
293 if (shapeRow != null)
294 {
295 prim.Shape = buildShape(shapeRow);
296 }
297 else
298 {
299 m_log.Info(
300 "No shape found for prim in storage, so setting default box shape");
301 prim.Shape = PrimitiveBaseShape.Default;
302 }
303 group.AddPart(prim);
304 group.RootPart = prim;
305
306 createdObjects.Add(Util.ToRawUuidString(group.UUID), group);
307 retvals.Add(group);
308 }
309 else
310 {
311 prim = buildPrim(primRow);
312 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
313 if (shapeRow != null)
314 {
315 prim.Shape = buildShape(shapeRow);
316 }
317 else
318 {
319 m_log.Info(
320 "No shape found for prim in storage, so setting default box shape");
321 prim.Shape = PrimitiveBaseShape.Default;
322 }
323 createdObjects[new LLUUID(objID)].AddPart(prim);
324 }
325
326 if (persistPrimInventories)
327 {
328 LoadItems(prim);
329 }
330 }
331 catch (Exception e)
332 {
333 m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows");
334 m_log.Info("[DATASTORE]: " + e.ToString());
335 foreach (DataColumn col in prims.Columns)
336 {
337 m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]);
338 }
339 }
340 }
341 }
342 return retvals;
343 }
344
345 /// <summary>
346 /// Load in a prim's persisted inventory.
347 /// </summary>
348 /// <param name="prim"></param>
349 private void LoadItems(SceneObjectPart prim)
350 {
351 //m_log.DebugFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
352
353 DataTable dbItems = ds.Tables["primitems"];
354
355 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
356 DataRow[] dbItemRows = dbItems.Select(sql);
357
358 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
359
360 foreach (DataRow row in dbItemRows)
361 {
362 TaskInventoryItem item = buildItem(row);
363 inventory.Add(item);
364
365 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
366 }
367
368 prim.RestoreInventoryItems(inventory);
369
370 // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in
371 // every item). This data should really be stored in the prim table itself.
372 if (dbItemRows.Length > 0)
373 {
374 prim.FolderID = inventory[0].ParentID;
375 }
376 }
377
378 public void StoreTerrain(double[,] ter, LLUUID regionID)
379 {
380 lock (ds)
381 {
382 int revision = Util.UnixTimeSinceEpoch();
383
384 // the following is an work around for .NET. The perf
385 // issues associated with it aren't as bad as you think.
386 m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString());
387 String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" +
388 " values(:RegionUUID, :Revision, :Heightfield)";
389
390 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
391 {
392 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID)));
393 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
394 cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter)));
395 cmd.ExecuteNonQuery();
396 }
397
398 // This is added to get rid of the infinitely growing
399 // terrain databases which negatively impact on SQLite
400 // over time. Before reenabling this feature there
401 // needs to be a limitter put on the number of
402 // revisions in the database, as this old
403 // implementation is a DOS attack waiting to happen.
404
405 using (
406 SqliteCommand cmd =
407 new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision < :Revision",
408 m_conn))
409 {
410 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID)));
411 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
412 cmd.ExecuteNonQuery();
413 }
414 }
415 }
416
417 public double[,] LoadTerrain(LLUUID regionID)
418 {
419 lock (ds)
420 {
421 double[,] terret = new double[256,256];
422 terret.Initialize();
423
424 String sql = "select RegionUUID, Revision, Heightfield from terrain" +
425 " where RegionUUID=:RegionUUID order by Revision desc";
426
427 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
428 {
429 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID)));
430
431 using (IDataReader row = cmd.ExecuteReader())
432 {
433 int rev = 0;
434 if (row.Read())
435 {
436 // TODO: put this into a function
437 MemoryStream str = new MemoryStream((byte[]) row["Heightfield"]);
438 BinaryReader br = new BinaryReader(str);
439 for (int x = 0; x < 256; x++)
440 {
441 for (int y = 0; y < 256; y++)
442 {
443 terret[x, y] = br.ReadDouble();
444 }
445 }
446 rev = (int) row["Revision"];
447 }
448 else
449 {
450 m_log.Info("[DATASTORE]: No terrain found for region");
451 return null;
452 }
453
454 m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString());
455 }
456 }
457 return terret;
458 }
459 }
460
461 public void RemoveLandObject(LLUUID globalID)
462 {
463 lock (ds)
464 {
465 using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn))
466 {
467 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(globalID)));
468 cmd.ExecuteNonQuery();
469 }
470
471 using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn))
472 {
473 cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(globalID)));
474 cmd.ExecuteNonQuery();
475 }
476 }
477 }
478
479 public void StoreLandObject(ILandObject parcel)
480 {
481 lock (ds)
482 {
483 DataTable land = ds.Tables["land"];
484 DataTable landaccesslist = ds.Tables["landaccesslist"];
485
486 DataRow landRow = land.Rows.Find(Util.ToRawUuidString(parcel.landData.globalID));
487 if (landRow == null)
488 {
489 landRow = land.NewRow();
490 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
491 land.Rows.Add(landRow);
492 }
493 else
494 {
495 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
496 }
497
498 // I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around
499 using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn))
500 {
501 cmd.Parameters.Add(new SqliteParameter(":LandUUID", Util.ToRawUuidString(parcel.landData.globalID)));
502 cmd.ExecuteNonQuery();
503 }
504
505 foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList)
506 {
507 DataRow newAccessRow = landaccesslist.NewRow();
508 fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID);
509 landaccesslist.Rows.Add(newAccessRow);
510 }
511 }
512
513 Commit();
514 }
515
516 public List<LandData> LoadLandObjects(LLUUID regionUUID)
517 {
518 List<LandData> landDataForRegion = new List<LandData>();
519 lock (ds)
520 {
521 DataTable land = ds.Tables["land"];
522 DataTable landaccesslist = ds.Tables["landaccesslist"];
523 string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'";
524 DataRow[] rawDataForRegion = land.Select(searchExp);
525 foreach (DataRow rawDataLand in rawDataForRegion)
526 {
527 LandData newLand = buildLandData(rawDataLand);
528 string accessListSearchExp = "LandUUID = '" + Util.ToRawUuidString(newLand.globalID) + "'";
529 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
530 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
531 {
532 newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
533 }
534
535 landDataForRegion.Add(newLand);
536 }
537 }
538 return landDataForRegion;
539 }
540
541 public void Commit()
542 {
543 lock (ds)
544 {
545 primDa.Update(ds, "prims");
546 shapeDa.Update(ds, "primshapes");
547
548 if (persistPrimInventories)
549 {
550 itemsDa.Update(ds, "primitems");
551 }
552
553 terrainDa.Update(ds, "terrain");
554 landDa.Update(ds, "land");
555 landAccessListDa.Update(ds, "landaccesslist");
556 ds.AcceptChanges();
557 }
558 }
559
560 public void Shutdown()
561 {
562 Commit();
563 }
564
565 /***********************************************************************
566 *
567 * Database Definition Functions
568 *
569 * This should be db agnostic as we define them in ADO.NET terms
570 *
571 **********************************************************************/
572
573 private void createCol(DataTable dt, string name, Type type)
574 {
575 DataColumn col = new DataColumn(name, type);
576 dt.Columns.Add(col);
577 }
578
579 private DataTable createTerrainTable()
580 {
581 DataTable terrain = new DataTable("terrain");
582
583 createCol(terrain, "RegionUUID", typeof (String));
584 createCol(terrain, "Revision", typeof (Int32));
585 createCol(terrain, "Heightfield", typeof (Byte[]));
586
587 return terrain;
588 }
589
590 private DataTable createPrimTable()
591 {
592 DataTable prims = new DataTable("prims");
593
594 createCol(prims, "UUID", typeof (String));
595 createCol(prims, "RegionUUID", typeof (String));
596 createCol(prims, "ParentID", typeof (Int32));
597 createCol(prims, "CreationDate", typeof (Int32));
598 createCol(prims, "Name", typeof (String));
599 createCol(prims, "SceneGroupID", typeof (String));
600 // various text fields
601 createCol(prims, "Text", typeof (String));
602 createCol(prims, "Description", typeof (String));
603 createCol(prims, "SitName", typeof (String));
604 createCol(prims, "TouchName", typeof (String));
605 // permissions
606 createCol(prims, "ObjectFlags", typeof (Int32));
607 createCol(prims, "CreatorID", typeof (String));
608 createCol(prims, "OwnerID", typeof (String));
609 createCol(prims, "GroupID", typeof (String));
610 createCol(prims, "LastOwnerID", typeof (String));
611 createCol(prims, "OwnerMask", typeof (Int32));
612 createCol(prims, "NextOwnerMask", typeof (Int32));
613 createCol(prims, "GroupMask", typeof (Int32));
614 createCol(prims, "EveryoneMask", typeof (Int32));
615 createCol(prims, "BaseMask", typeof (Int32));
616 // vectors
617 createCol(prims, "PositionX", typeof (Double));
618 createCol(prims, "PositionY", typeof (Double));
619 createCol(prims, "PositionZ", typeof (Double));
620 createCol(prims, "GroupPositionX", typeof (Double));
621 createCol(prims, "GroupPositionY", typeof (Double));
622 createCol(prims, "GroupPositionZ", typeof (Double));
623 createCol(prims, "VelocityX", typeof (Double));
624 createCol(prims, "VelocityY", typeof (Double));
625 createCol(prims, "VelocityZ", typeof (Double));
626 createCol(prims, "AngularVelocityX", typeof (Double));
627 createCol(prims, "AngularVelocityY", typeof (Double));
628 createCol(prims, "AngularVelocityZ", typeof (Double));
629 createCol(prims, "AccelerationX", typeof (Double));
630 createCol(prims, "AccelerationY", typeof (Double));
631 createCol(prims, "AccelerationZ", typeof (Double));
632 // quaternions
633 createCol(prims, "RotationX", typeof (Double));
634 createCol(prims, "RotationY", typeof (Double));
635 createCol(prims, "RotationZ", typeof (Double));
636 createCol(prims, "RotationW", typeof (Double));
637
638 // sit target
639 createCol(prims, "SitTargetOffsetX", typeof (Double));
640 createCol(prims, "SitTargetOffsetY", typeof (Double));
641 createCol(prims, "SitTargetOffsetZ", typeof (Double));
642
643 createCol(prims, "SitTargetOrientW", typeof (Double));
644 createCol(prims, "SitTargetOrientX", typeof (Double));
645 createCol(prims, "SitTargetOrientY", typeof (Double));
646 createCol(prims, "SitTargetOrientZ", typeof (Double));
647
648 // Add in contraints
649 prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]};
650
651 return prims;
652 }
653
654 private DataTable createShapeTable()
655 {
656 DataTable shapes = new DataTable("primshapes");
657 createCol(shapes, "UUID", typeof (String));
658 // shape is an enum
659 createCol(shapes, "Shape", typeof (Int32));
660 // vectors
661 createCol(shapes, "ScaleX", typeof (Double));
662 createCol(shapes, "ScaleY", typeof (Double));
663 createCol(shapes, "ScaleZ", typeof (Double));
664 // paths
665 createCol(shapes, "PCode", typeof (Int32));
666 createCol(shapes, "PathBegin", typeof (Int32));
667 createCol(shapes, "PathEnd", typeof (Int32));
668 createCol(shapes, "PathScaleX", typeof (Int32));
669 createCol(shapes, "PathScaleY", typeof (Int32));
670 createCol(shapes, "PathShearX", typeof (Int32));
671 createCol(shapes, "PathShearY", typeof (Int32));
672 createCol(shapes, "PathSkew", typeof (Int32));
673 createCol(shapes, "PathCurve", typeof (Int32));
674 createCol(shapes, "PathRadiusOffset", typeof (Int32));
675 createCol(shapes, "PathRevolutions", typeof (Int32));
676 createCol(shapes, "PathTaperX", typeof (Int32));
677 createCol(shapes, "PathTaperY", typeof (Int32));
678 createCol(shapes, "PathTwist", typeof (Int32));
679 createCol(shapes, "PathTwistBegin", typeof (Int32));
680 // profile
681 createCol(shapes, "ProfileBegin", typeof (Int32));
682 createCol(shapes, "ProfileEnd", typeof (Int32));
683 createCol(shapes, "ProfileCurve", typeof (Int32));
684 createCol(shapes, "ProfileHollow", typeof (Int32));
685 createCol(shapes, "State", typeof(Int32));
686 // text TODO: this isn't right, but I'm not sure the right
687 // way to specify this as a blob atm
688 createCol(shapes, "Texture", typeof (Byte[]));
689 createCol(shapes, "ExtraParams", typeof (Byte[]));
690
691 shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]};
692
693 return shapes;
694 }
695
696 private DataTable createItemsTable()
697 {
698 DataTable items = new DataTable("primitems");
699
700 createCol(items, "itemID", typeof (String));
701 createCol(items, "primID", typeof (String));
702 createCol(items, "assetID", typeof (String));
703 createCol(items, "parentFolderID", typeof (String));
704
705 createCol(items, "invType", typeof (Int32));
706 createCol(items, "assetType", typeof (Int32));
707
708 createCol(items, "name", typeof (String));
709 createCol(items, "description", typeof (String));
710
711 createCol(items, "creationDate", typeof (Int64));
712 createCol(items, "creatorID", typeof (String));
713 createCol(items, "ownerID", typeof (String));
714 createCol(items, "lastOwnerID", typeof (String));
715 createCol(items, "groupID", typeof (String));
716
717 createCol(items, "nextPermissions", typeof (UInt32));
718 createCol(items, "currentPermissions", typeof (UInt32));
719 createCol(items, "basePermissions", typeof (UInt32));
720 createCol(items, "everyonePermissions", typeof (UInt32));
721 createCol(items, "groupPermissions", typeof (UInt32));
722
723 items.PrimaryKey = new DataColumn[] {items.Columns["itemID"]};
724
725 return items;
726 }
727
728 private DataTable createLandTable()
729 {
730 DataTable land = new DataTable("land");
731 createCol(land, "UUID", typeof (String));
732 createCol(land, "RegionUUID", typeof (String));
733 createCol(land, "LocalLandID", typeof (UInt32));
734
735 // Bitmap is a byte[512]
736 createCol(land, "Bitmap", typeof (Byte[]));
737
738 createCol(land, "Name", typeof (String));
739 createCol(land, "Desc", typeof (String));
740 createCol(land, "OwnerUUID", typeof (String));
741 createCol(land, "IsGroupOwned", typeof (Boolean));
742 createCol(land, "Area", typeof (Int32));
743 createCol(land, "AuctionID", typeof (Int32)); //Unemplemented
744 createCol(land, "Category", typeof (Int32)); //Enum libsecondlife.Parcel.ParcelCategory
745 createCol(land, "ClaimDate", typeof (Int32));
746 createCol(land, "ClaimPrice", typeof (Int32));
747 createCol(land, "GroupUUID", typeof (string));
748 createCol(land, "SalePrice", typeof (Int32));
749 createCol(land, "LandStatus", typeof (Int32)); //Enum. libsecondlife.Parcel.ParcelStatus
750 createCol(land, "LandFlags", typeof (UInt32));
751 createCol(land, "LandingType", typeof (Byte));
752 createCol(land, "MediaAutoScale", typeof (Byte));
753 createCol(land, "MediaTextureUUID", typeof (String));
754 createCol(land, "MediaURL", typeof (String));
755 createCol(land, "MusicURL", typeof (String));
756 createCol(land, "PassHours", typeof (Double));
757 createCol(land, "PassPrice", typeof (UInt32));
758 createCol(land, "SnapshotUUID", typeof (String));
759 createCol(land, "UserLocationX", typeof (Double));
760 createCol(land, "UserLocationY", typeof (Double));
761 createCol(land, "UserLocationZ", typeof (Double));
762 createCol(land, "UserLookAtX", typeof (Double));
763 createCol(land, "UserLookAtY", typeof (Double));
764 createCol(land, "UserLookAtZ", typeof (Double));
765
766 land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]};
767
768 return land;
769 }
770
771 private DataTable createLandAccessListTable()
772 {
773 DataTable landaccess = new DataTable("landaccesslist");
774 createCol(landaccess, "LandUUID", typeof (String));
775 createCol(landaccess, "AccessUUID", typeof (String));
776 createCol(landaccess, "Flags", typeof (UInt32));
777
778 return landaccess;
779 }
780
781 /***********************************************************************
782 *
783 * Convert between ADO.NET <=> OpenSim Objects
784 *
785 * These should be database independant
786 *
787 **********************************************************************/
788
789 private SceneObjectPart buildPrim(DataRow row)
790 {
791 // TODO: this doesn't work yet because something more
792 // interesting has to be done to actually get these values
793 // back out. Not enough time to figure it out yet.
794 SceneObjectPart prim = new SceneObjectPart();
795 prim.UUID = new LLUUID((String) row["UUID"]);
796 // explicit conversion of integers is required, which sort
797 // of sucks. No idea if there is a shortcut here or not.
798 prim.ParentID = Convert.ToUInt32(row["ParentID"]);
799 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
800 prim.Name = (String) row["Name"];
801 // various text fields
802 prim.Text = (String) row["Text"];
803 prim.Description = (String) row["Description"];
804 prim.SitName = (String) row["SitName"];
805 prim.TouchName = (String) row["TouchName"];
806 // permissions
807 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
808 prim.CreatorID = new LLUUID((String) row["CreatorID"]);
809 prim.OwnerID = new LLUUID((String) row["OwnerID"]);
810 prim.GroupID = new LLUUID((String) row["GroupID"]);
811 prim.LastOwnerID = new LLUUID((String) row["LastOwnerID"]);
812 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
813 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
814 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
815 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
816 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
817 // vectors
818 prim.OffsetPosition = new LLVector3(
819 Convert.ToSingle(row["PositionX"]),
820 Convert.ToSingle(row["PositionY"]),
821 Convert.ToSingle(row["PositionZ"])
822 );
823 prim.GroupPosition = new LLVector3(
824 Convert.ToSingle(row["GroupPositionX"]),
825 Convert.ToSingle(row["GroupPositionY"]),
826 Convert.ToSingle(row["GroupPositionZ"])
827 );
828 prim.Velocity = new LLVector3(
829 Convert.ToSingle(row["VelocityX"]),
830 Convert.ToSingle(row["VelocityY"]),
831 Convert.ToSingle(row["VelocityZ"])
832 );
833 prim.AngularVelocity = new LLVector3(
834 Convert.ToSingle(row["AngularVelocityX"]),
835 Convert.ToSingle(row["AngularVelocityY"]),
836 Convert.ToSingle(row["AngularVelocityZ"])
837 );
838 prim.Acceleration = new LLVector3(
839 Convert.ToSingle(row["AccelerationX"]),
840 Convert.ToSingle(row["AccelerationY"]),
841 Convert.ToSingle(row["AccelerationZ"])
842 );
843 // quaternions
844 prim.RotationOffset = new LLQuaternion(
845 Convert.ToSingle(row["RotationX"]),
846 Convert.ToSingle(row["RotationY"]),
847 Convert.ToSingle(row["RotationZ"]),
848 Convert.ToSingle(row["RotationW"])
849 );
850
851 try
852 {
853 prim.SetSitTargetLL(new LLVector3(
854 Convert.ToSingle(row["SitTargetOffsetX"]),
855 Convert.ToSingle(row["SitTargetOffsetY"]),
856 Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion(
857 Convert.ToSingle(
858 row["SitTargetOrientX"]),
859 Convert.ToSingle(
860 row["SitTargetOrientY"]),
861 Convert.ToSingle(
862 row["SitTargetOrientZ"]),
863 Convert.ToSingle(
864 row["SitTargetOrientW"])));
865 }
866 catch (InvalidCastException)
867 {
868 // Database table was created before we got here and now has null values :P
869 m_conn.Open();
870 SqliteCommand cmd =
871 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetX float NOT NULL default 0;", m_conn);
872 cmd.ExecuteNonQuery();
873 cmd =
874 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetY float NOT NULL default 0;", m_conn);
875 cmd.ExecuteNonQuery();
876 cmd =
877 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetZ float NOT NULL default 0;", m_conn);
878 cmd.ExecuteNonQuery();
879 cmd =
880 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientW float NOT NULL default 0;", m_conn);
881 cmd.ExecuteNonQuery();
882 cmd =
883 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientX float NOT NULL default 0;", m_conn);
884 cmd.ExecuteNonQuery();
885 cmd =
886 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientY float NOT NULL default 0;", m_conn);
887 cmd.ExecuteNonQuery();
888 cmd =
889 new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientZ float NOT NULL default 0;", m_conn);
890 cmd.ExecuteNonQuery();
891 }
892
893 return prim;
894 }
895
896 /// <summary>
897 /// Build a prim inventory item from the persisted data.
898 /// </summary>
899 /// <param name="row"></param>
900 /// <returns></returns>
901 private TaskInventoryItem buildItem(DataRow row)
902 {
903 TaskInventoryItem taskItem = new TaskInventoryItem();
904
905 taskItem.ItemID = new LLUUID((String)row["itemID"]);
906 taskItem.ParentPartID = new LLUUID((String)row["primID"]);
907 taskItem.AssetID = new LLUUID((String)row["assetID"]);
908 taskItem.ParentID = new LLUUID((String)row["parentFolderID"]);
909
910 taskItem.InvType = Convert.ToInt32(row["invType"]);
911 taskItem.Type = Convert.ToInt32(row["assetType"]);
912
913 taskItem.Name = (String)row["name"];
914 taskItem.Description = (String)row["description"];
915 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
916 taskItem.CreatorID = new LLUUID((String)row["creatorID"]);
917 taskItem.OwnerID = new LLUUID((String)row["ownerID"]);
918 taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]);
919 taskItem.GroupID = new LLUUID((String)row["groupID"]);
920
921 taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]);
922 taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]);
923 taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]);
924 taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]);
925 taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]);
926
927 return taskItem;
928 }
929
930 private LandData buildLandData(DataRow row)
931 {
932 LandData newData = new LandData();
933
934 newData.globalID = new LLUUID((String) row["UUID"]);
935 newData.localID = Convert.ToInt32(row["LocalLandID"]);
936
937 // Bitmap is a byte[512]
938 newData.landBitmapByteArray = (Byte[]) row["Bitmap"];
939
940 newData.landName = (String) row["Name"];
941 newData.landDesc = (String) row["Desc"];
942 newData.ownerID = (String) row["OwnerUUID"];
943 newData.isGroupOwned = (Boolean) row["IsGroupOwned"];
944 newData.area = Convert.ToInt32(row["Area"]);
945 newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
946 newData.category = (Parcel.ParcelCategory) Convert.ToInt32(row["Category"]);
947 //Enum libsecondlife.Parcel.ParcelCategory
948 newData.claimDate = Convert.ToInt32(row["ClaimDate"]);
949 newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]);
950 newData.groupID = new LLUUID((String) row["GroupUUID"]);
951 newData.salePrice = Convert.ToInt32(row["SalePrice"]);
952 newData.landStatus = (Parcel.ParcelStatus) Convert.ToInt32(row["LandStatus"]);
953 //Enum. libsecondlife.Parcel.ParcelStatus
954 newData.landFlags = Convert.ToUInt32(row["LandFlags"]);
955 newData.landingType = (Byte) row["LandingType"];
956 newData.mediaAutoScale = (Byte) row["MediaAutoScale"];
957 newData.mediaID = new LLUUID((String) row["MediaTextureUUID"]);
958 newData.mediaURL = (String) row["MediaURL"];
959 newData.musicURL = (String) row["MusicURL"];
960 newData.passHours = Convert.ToSingle(row["PassHours"]);
961 newData.passPrice = Convert.ToInt32(row["PassPrice"]);
962 newData.snapshotID = (String) row["SnapshotUUID"];
963
964 newData.userLocation =
965 new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
966 Convert.ToSingle(row["UserLocationZ"]));
967 newData.userLookAt =
968 new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
969 Convert.ToSingle(row["UserLookAtZ"]));
970 newData.parcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
971
972 return newData;
973 }
974
975 private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
976 {
977 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
978 entry.AgentID = new LLUUID((string) row["AccessUUID"]);
979 entry.Flags = (ParcelManager.AccessList) row["Flags"];
980 entry.Time = new DateTime();
981 return entry;
982 }
983
984 private Array serializeTerrain(double[,] val)
985 {
986 MemoryStream str = new MemoryStream(65536*sizeof (double));
987 BinaryWriter bw = new BinaryWriter(str);
988
989 // TODO: COMPATIBILITY - Add byte-order conversions
990 for (int x = 0; x < 256; x++)
991 for (int y = 0; y < 256; y++)
992 bw.Write(val[x, y]);
993
994 return str.ToArray();
995 }
996
997// private void fillTerrainRow(DataRow row, LLUUID regionUUID, int rev, double[,] val)
998// {
999// row["RegionUUID"] = regionUUID;
1000// row["Revision"] = rev;
1001
1002// MemoryStream str = new MemoryStream(65536*sizeof (double));
1003// BinaryWriter bw = new BinaryWriter(str);
1004
1005// // TODO: COMPATIBILITY - Add byte-order conversions
1006// for (int x = 0; x < 256; x++)
1007// for (int y = 0; y < 256; y++)
1008// bw.Write(val[x, y]);
1009
1010// row["Heightfield"] = str.ToArray();
1011// }
1012
1013 private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
1014 {
1015 row["UUID"] = Util.ToRawUuidString(prim.UUID);
1016 row["RegionUUID"] = Util.ToRawUuidString(regionUUID);
1017 row["ParentID"] = prim.ParentID;
1018 row["CreationDate"] = prim.CreationDate;
1019 row["Name"] = prim.Name;
1020 row["SceneGroupID"] = Util.ToRawUuidString(sceneGroupID);
1021 // the UUID of the root part for this SceneObjectGroup
1022 // various text fields
1023 row["Text"] = prim.Text;
1024 row["Description"] = prim.Description;
1025 row["SitName"] = prim.SitName;
1026 row["TouchName"] = prim.TouchName;
1027 // permissions
1028 row["ObjectFlags"] = prim.ObjectFlags;
1029 row["CreatorID"] = Util.ToRawUuidString(prim.CreatorID);
1030 row["OwnerID"] = Util.ToRawUuidString(prim.OwnerID);
1031 row["GroupID"] = Util.ToRawUuidString(prim.GroupID);
1032 row["LastOwnerID"] = Util.ToRawUuidString(prim.LastOwnerID);
1033 row["OwnerMask"] = prim.OwnerMask;
1034 row["NextOwnerMask"] = prim.NextOwnerMask;
1035 row["GroupMask"] = prim.GroupMask;
1036 row["EveryoneMask"] = prim.EveryoneMask;
1037 row["BaseMask"] = prim.BaseMask;
1038 // vectors
1039 row["PositionX"] = prim.OffsetPosition.X;
1040 row["PositionY"] = prim.OffsetPosition.Y;
1041 row["PositionZ"] = prim.OffsetPosition.Z;
1042 row["GroupPositionX"] = prim.GroupPosition.X;
1043 row["GroupPositionY"] = prim.GroupPosition.Y;
1044 row["GroupPositionZ"] = prim.GroupPosition.Z;
1045 row["VelocityX"] = prim.Velocity.X;
1046 row["VelocityY"] = prim.Velocity.Y;
1047 row["VelocityZ"] = prim.Velocity.Z;
1048 row["AngularVelocityX"] = prim.AngularVelocity.X;
1049 row["AngularVelocityY"] = prim.AngularVelocity.Y;
1050 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
1051 row["AccelerationX"] = prim.Acceleration.X;
1052 row["AccelerationY"] = prim.Acceleration.Y;
1053 row["AccelerationZ"] = prim.Acceleration.Z;
1054 // quaternions
1055 row["RotationX"] = prim.RotationOffset.X;
1056 row["RotationY"] = prim.RotationOffset.Y;
1057 row["RotationZ"] = prim.RotationOffset.Z;
1058 row["RotationW"] = prim.RotationOffset.W;
1059
1060 // Sit target
1061 LLVector3 sitTargetPos = prim.GetSitTargetPositionLL();
1062 row["SitTargetOffsetX"] = sitTargetPos.X;
1063 row["SitTargetOffsetY"] = sitTargetPos.Y;
1064 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1065
1066 LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL();
1067 row["SitTargetOrientW"] = sitTargetOrient.W;
1068 row["SitTargetOrientX"] = sitTargetOrient.X;
1069 row["SitTargetOrientY"] = sitTargetOrient.Y;
1070 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1071 }
1072
1073 private void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1074 {
1075 row["itemID"] = taskItem.ItemID;
1076 row["primID"] = taskItem.ParentPartID;
1077 row["assetID"] = taskItem.AssetID;
1078 row["parentFolderID"] = taskItem.ParentID;
1079
1080 row["invType"] = taskItem.InvType;
1081 row["assetType"] = taskItem.Type;
1082
1083 row["name"] = taskItem.Name;
1084 row["description"] = taskItem.Description;
1085 row["creationDate"] = taskItem.CreationDate;
1086 row["creatorID"] = taskItem.CreatorID;
1087 row["ownerID"] = taskItem.OwnerID;
1088 row["lastOwnerID"] = taskItem.LastOwnerID;
1089 row["groupID"] = taskItem.GroupID;
1090 row["nextPermissions"] = taskItem.NextOwnerMask;
1091 row["currentPermissions"] = taskItem.OwnerMask;
1092 row["basePermissions"] = taskItem.BaseMask;
1093 row["everyonePermissions"] = taskItem.EveryoneMask;
1094 row["groupPermissions"] = taskItem.GroupMask;
1095 }
1096
1097 private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID)
1098 {
1099 row["UUID"] = Util.ToRawUuidString(land.globalID);
1100 row["RegionUUID"] = Util.ToRawUuidString(regionUUID);
1101 row["LocalLandID"] = land.localID;
1102
1103 // Bitmap is a byte[512]
1104 row["Bitmap"] = land.landBitmapByteArray;
1105
1106 row["Name"] = land.landName;
1107 row["Desc"] = land.landDesc;
1108 row["OwnerUUID"] = Util.ToRawUuidString(land.ownerID);
1109 row["IsGroupOwned"] = land.isGroupOwned;
1110 row["Area"] = land.area;
1111 row["AuctionID"] = land.auctionID; //Unemplemented
1112 row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory
1113 row["ClaimDate"] = land.claimDate;
1114 row["ClaimPrice"] = land.claimPrice;
1115 row["GroupUUID"] = Util.ToRawUuidString(land.groupID);
1116 row["SalePrice"] = land.salePrice;
1117 row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus
1118 row["LandFlags"] = land.landFlags;
1119 row["LandingType"] = land.landingType;
1120 row["MediaAutoScale"] = land.mediaAutoScale;
1121 row["MediaTextureUUID"] = Util.ToRawUuidString(land.mediaID);
1122 row["MediaURL"] = land.mediaURL;
1123 row["MusicURL"] = land.musicURL;
1124 row["PassHours"] = land.passHours;
1125 row["PassPrice"] = land.passPrice;
1126 row["SnapshotUUID"] = Util.ToRawUuidString(land.snapshotID);
1127 row["UserLocationX"] = land.userLocation.X;
1128 row["UserLocationY"] = land.userLocation.Y;
1129 row["UserLocationZ"] = land.userLocation.Z;
1130 row["UserLookAtX"] = land.userLookAt.X;
1131 row["UserLookAtY"] = land.userLookAt.Y;
1132 row["UserLookAtZ"] = land.userLookAt.Z;
1133 }
1134
1135 private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID)
1136 {
1137 row["LandUUID"] = Util.ToRawUuidString(parcelID);
1138 row["AccessUUID"] = Util.ToRawUuidString(entry.AgentID);
1139 row["Flags"] = entry.Flags;
1140 }
1141
1142 private PrimitiveBaseShape buildShape(DataRow row)
1143 {
1144 PrimitiveBaseShape s = new PrimitiveBaseShape();
1145 s.Scale = new LLVector3(
1146 Convert.ToSingle(row["ScaleX"]),
1147 Convert.ToSingle(row["ScaleY"]),
1148 Convert.ToSingle(row["ScaleZ"])
1149 );
1150 // paths
1151 s.PCode = Convert.ToByte(row["PCode"]);
1152 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1153 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1154 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1155 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1156 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1157 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1158 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1159 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1160 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1161 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1162 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1163 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1164 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1165 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1166 // profile
1167 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1168 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1169 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1170 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1171 try
1172 {
1173 s.State = Convert.ToByte(row["State"]);
1174 }
1175 catch (InvalidCastException)
1176 {
1177 m_conn.Open();
1178 SqliteCommand cmd =
1179 new SqliteCommand("ALTER TABLE primshapes ADD COLUMN State Integer NOT NULL default 0;", m_conn);
1180 cmd.ExecuteNonQuery();
1181 }
1182 // text TODO: this isn't right] = but I'm not sure the right
1183 // way to specify this as a blob atm
1184
1185 byte[] textureEntry = (byte[]) row["Texture"];
1186 s.TextureEntry = textureEntry;
1187
1188 s.ExtraParams = (byte[]) row["ExtraParams"];
1189 // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
1190 // string texture = encoding.GetString((Byte[])row["Texture"]);
1191 // if (!texture.StartsWith("<"))
1192 // {
1193 // //here so that we can still work with old format database files (ie from before I added xml serialization)
1194 // LLObject.TextureEntry textureEntry = null;
1195 // textureEntry = new LLObject.TextureEntry(new LLUUID(texture));
1196 // s.TextureEntry = textureEntry.ToBytes();
1197 // }
1198 // else
1199 // {
1200 // TextureBlock textureEntry = TextureBlock.FromXmlString(texture);
1201 // s.TextureEntry = textureEntry.TextureData;
1202 // s.ExtraParams = textureEntry.ExtraParams;
1203 // }
1204
1205 return s;
1206 }
1207
1208 private void fillShapeRow(DataRow row, SceneObjectPart prim)
1209 {
1210 PrimitiveBaseShape s = prim.Shape;
1211 row["UUID"] = Util.ToRawUuidString(prim.UUID);
1212 // shape is an enum
1213 row["Shape"] = 0;
1214 // vectors
1215 row["ScaleX"] = s.Scale.X;
1216 row["ScaleY"] = s.Scale.Y;
1217 row["ScaleZ"] = s.Scale.Z;
1218 // paths
1219 row["PCode"] = s.PCode;
1220 row["PathBegin"] = s.PathBegin;
1221 row["PathEnd"] = s.PathEnd;
1222 row["PathScaleX"] = s.PathScaleX;
1223 row["PathScaleY"] = s.PathScaleY;
1224 row["PathShearX"] = s.PathShearX;
1225 row["PathShearY"] = s.PathShearY;
1226 row["PathSkew"] = s.PathSkew;
1227 row["PathCurve"] = s.PathCurve;
1228 row["PathRadiusOffset"] = s.PathRadiusOffset;
1229 row["PathRevolutions"] = s.PathRevolutions;
1230 row["PathTaperX"] = s.PathTaperX;
1231 row["PathTaperY"] = s.PathTaperY;
1232 row["PathTwist"] = s.PathTwist;
1233 row["PathTwistBegin"] = s.PathTwistBegin;
1234 // profile
1235 row["ProfileBegin"] = s.ProfileBegin;
1236 row["ProfileEnd"] = s.ProfileEnd;
1237 row["ProfileCurve"] = s.ProfileCurve;
1238 row["ProfileHollow"] = s.ProfileHollow;
1239 row["State"] = s.State;
1240
1241 row["Texture"] = s.TextureEntry;
1242 row["ExtraParams"] = s.ExtraParams;
1243 }
1244
1245 private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
1246 {
1247 DataTable prims = ds.Tables["prims"];
1248 DataTable shapes = ds.Tables["primshapes"];
1249
1250 DataRow primRow = prims.Rows.Find(Util.ToRawUuidString(prim.UUID));
1251 if (primRow == null)
1252 {
1253 primRow = prims.NewRow();
1254 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1255 prims.Rows.Add(primRow);
1256 }
1257 else
1258 {
1259 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1260 }
1261
1262 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
1263 if (shapeRow == null)
1264 {
1265 shapeRow = shapes.NewRow();
1266 fillShapeRow(shapeRow, prim);
1267 shapes.Rows.Add(shapeRow);
1268 }
1269 else
1270 {
1271 fillShapeRow(shapeRow, prim);
1272 }
1273 }
1274
1275 // see IRegionDatastore
1276 public void StorePrimInventory(LLUUID primID, ICollection<TaskInventoryItem> items)
1277 {
1278 if (!persistPrimInventories)
1279 return;
1280
1281 m_log.InfoFormat("[DATASTORE]: Entered StorePrimInventory with prim ID {0}", primID);
1282
1283 DataTable dbItems = ds.Tables["primitems"];
1284
1285 // For now, we're just going to crudely remove all the previous inventory items
1286 // no matter whether they have changed or not, and replace them with the current set.
1287 lock (ds)
1288 {
1289 RemoveItems(primID);
1290
1291 // repalce with current inventory details
1292 foreach (TaskInventoryItem newItem in items)
1293 {
1294// m_log.InfoFormat(
1295// "[DATASTORE]: ",
1296// "Adding item {0}, {1} to prim ID {2}",
1297// newItem.Name, newItem.ItemID, newItem.ParentPartID);
1298
1299 DataRow newItemRow = dbItems.NewRow();
1300 fillItemRow(newItemRow, newItem);
1301 dbItems.Rows.Add(newItemRow);
1302 }
1303 }
1304
1305 Commit();
1306 }
1307
1308 /***********************************************************************
1309 *
1310 * SQL Statement Creation Functions
1311 *
1312 * These functions create SQL statements for update, insert, and create.
1313 * They can probably be factored later to have a db independant
1314 * portion and a db specific portion
1315 *
1316 **********************************************************************/
1317
1318 private SqliteCommand createInsertCommand(string table, DataTable dt)
1319 {
1320 /**
1321 * This is subtle enough to deserve some commentary.
1322 * Instead of doing *lots* and *lots of hardcoded strings
1323 * for database definitions we'll use the fact that
1324 * realistically all insert statements look like "insert
1325 * into A(b, c) values(:b, :c) on the parameterized query
1326 * front. If we just have a list of b, c, etc... we can
1327 * generate these strings instead of typing them out.
1328 */
1329 string[] cols = new string[dt.Columns.Count];
1330 for (int i = 0; i < dt.Columns.Count; i++)
1331 {
1332 DataColumn col = dt.Columns[i];
1333 cols[i] = col.ColumnName;
1334 }
1335
1336 string sql = "insert into " + table + "(";
1337 sql += String.Join(", ", cols);
1338 // important, the first ':' needs to be here, the rest get added in the join
1339 sql += ") values (:";
1340 sql += String.Join(", :", cols);
1341 sql += ")";
1342 SqliteCommand cmd = new SqliteCommand(sql);
1343
1344 // this provides the binding for all our parameters, so
1345 // much less code than it used to be
1346 foreach (DataColumn col in dt.Columns)
1347 {
1348 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
1349 }
1350 return cmd;
1351 }
1352
1353 private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
1354 {
1355 string sql = "update " + table + " set ";
1356 string subsql = String.Empty;
1357 foreach (DataColumn col in dt.Columns)
1358 {
1359 if (subsql.Length > 0)
1360 {
1361 // a map function would rock so much here
1362 subsql += ", ";
1363 }
1364 subsql += col.ColumnName + "= :" + col.ColumnName;
1365 }
1366 sql += subsql;
1367 sql += " where " + pk;
1368 SqliteCommand cmd = new SqliteCommand(sql);
1369
1370 // this provides the binding for all our parameters, so
1371 // much less code than it used to be
1372
1373 foreach (DataColumn col in dt.Columns)
1374 {
1375 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
1376 }
1377 return cmd;
1378 }
1379
1380
1381 private string defineTable(DataTable dt)
1382 {
1383 string sql = "create table " + dt.TableName + "(";
1384 string subsql = String.Empty;
1385 foreach (DataColumn col in dt.Columns)
1386 {
1387 if (subsql.Length > 0)
1388 {
1389 // a map function would rock so much here
1390 subsql += ",\n";
1391 }
1392 subsql += col.ColumnName + " " + sqliteType(col.DataType);
1393 if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
1394 {
1395 subsql += " primary key";
1396 }
1397 }
1398 sql += subsql;
1399 sql += ")";
1400 return sql;
1401 }
1402
1403 /***********************************************************************
1404 *
1405 * Database Binding functions
1406 *
1407 * These will be db specific due to typing, and minor differences
1408 * in databases.
1409 *
1410 **********************************************************************/
1411
1412 ///<summary>
1413 /// This is a convenience function that collapses 5 repetitive
1414 /// lines for defining SqliteParameters to 2 parameters:
1415 /// column name and database type.
1416 ///
1417 /// It assumes certain conventions like :param as the param
1418 /// name to replace in parametrized queries, and that source
1419 /// version is always current version, both of which are fine
1420 /// for us.
1421 ///</summary>
1422 ///<returns>a built sqlite parameter</returns>
1423 private SqliteParameter createSqliteParameter(string name, Type type)
1424 {
1425 SqliteParameter param = new SqliteParameter();
1426 param.ParameterName = ":" + name;
1427 param.DbType = dbtypeFromType(type);
1428 param.SourceColumn = name;
1429 param.SourceVersion = DataRowVersion.Current;
1430 return param;
1431 }
1432
1433 private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn)
1434 {
1435 da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]);
1436 da.InsertCommand.Connection = conn;
1437
1438 da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]);
1439 da.UpdateCommand.Connection = conn;
1440
1441 SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID");
1442 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
1443 delete.Connection = conn;
1444 da.DeleteCommand = delete;
1445 }
1446
1447 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
1448 {
1449 da.InsertCommand = createInsertCommand("primitems", ds.Tables["primitems"]);
1450 da.InsertCommand.Connection = conn;
1451
1452 da.UpdateCommand = createUpdateCommand("primitems", "itemID = :itemID", ds.Tables["primitems"]);
1453 da.UpdateCommand.Connection = conn;
1454
1455 SqliteCommand delete = new SqliteCommand("delete from primitems where itemID = :itemID");
1456 delete.Parameters.Add(createSqliteParameter("itemID", typeof (String)));
1457 delete.Connection = conn;
1458 da.DeleteCommand = delete;
1459 }
1460
1461 private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
1462 {
1463 da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
1464 da.InsertCommand.Connection = conn;
1465 }
1466
1467 private void setupLandCommands(SqliteDataAdapter da, SqliteConnection conn)
1468 {
1469 da.InsertCommand = createInsertCommand("land", ds.Tables["land"]);
1470 da.InsertCommand.Connection = conn;
1471
1472 da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]);
1473 da.UpdateCommand.Connection = conn;
1474 }
1475
1476 private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn)
1477 {
1478 da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]);
1479 da.InsertCommand.Connection = conn;
1480 }
1481
1482 private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn)
1483 {
1484 da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]);
1485 da.InsertCommand.Connection = conn;
1486
1487 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]);
1488 da.UpdateCommand.Connection = conn;
1489
1490 SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID");
1491 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
1492 delete.Connection = conn;
1493 da.DeleteCommand = delete;
1494 }
1495
1496 /// <summary>
1497 /// Create the necessary database tables.
1498 /// </summary>
1499 /// <param name="conn"></param>
1500 private void InitDB(SqliteConnection conn)
1501 {
1502 string createPrims = defineTable(createPrimTable());
1503 string createShapes = defineTable(createShapeTable());
1504 string createItems = defineTable(createItemsTable());
1505 string createTerrain = defineTable(createTerrainTable());
1506 string createLand = defineTable(createLandTable());
1507 string createLandAccessList = defineTable(createLandAccessListTable());
1508
1509 SqliteCommand pcmd = new SqliteCommand(createPrims, conn);
1510 SqliteCommand scmd = new SqliteCommand(createShapes, conn);
1511 SqliteCommand icmd = new SqliteCommand(createItems, conn);
1512 SqliteCommand tcmd = new SqliteCommand(createTerrain, conn);
1513 SqliteCommand lcmd = new SqliteCommand(createLand, conn);
1514 SqliteCommand lalcmd = new SqliteCommand(createLandAccessList, conn);
1515
1516 try
1517 {
1518 pcmd.ExecuteNonQuery();
1519 }
1520 catch (SqliteSyntaxException)
1521 {
1522 m_log.Warn("[SQLITE]: Primitives Table Already Exists");
1523 }
1524
1525 try
1526 {
1527 scmd.ExecuteNonQuery();
1528 }
1529 catch (SqliteSyntaxException)
1530 {
1531 m_log.Warn("[SQLITE]: Shapes Table Already Exists");
1532 }
1533
1534 if (persistPrimInventories)
1535 {
1536 try
1537 {
1538 icmd.ExecuteNonQuery();
1539 }
1540 catch (SqliteSyntaxException)
1541 {
1542 m_log.Warn("[SQLITE]: Primitives Inventory Table Already Exists");
1543 }
1544 }
1545
1546 try
1547 {
1548 tcmd.ExecuteNonQuery();
1549 }
1550 catch (SqliteSyntaxException)
1551 {
1552 m_log.Warn("[SQLITE]: Terrain Table Already Exists");
1553 }
1554
1555 try
1556 {
1557 lcmd.ExecuteNonQuery();
1558 }
1559 catch (SqliteSyntaxException)
1560 {
1561 m_log.Warn("[SQLITE]: Land Table Already Exists");
1562 }
1563
1564 try
1565 {
1566 lalcmd.ExecuteNonQuery();
1567 }
1568 catch (SqliteSyntaxException)
1569 {
1570 m_log.Warn("[SQLITE]: LandAccessList Table Already Exists");
1571 }
1572 }
1573
1574 private bool TestTables(SqliteConnection conn)
1575 {
1576 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, conn);
1577 SqliteDataAdapter pDa = new SqliteDataAdapter(primSelectCmd);
1578
1579 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, conn);
1580 SqliteDataAdapter sDa = new SqliteDataAdapter(shapeSelectCmd);
1581
1582 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, conn);
1583 SqliteDataAdapter iDa = new SqliteDataAdapter(itemsSelectCmd);
1584
1585 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, conn);
1586 SqliteDataAdapter tDa = new SqliteDataAdapter(terrainSelectCmd);
1587
1588 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, conn);
1589 SqliteDataAdapter lDa = new SqliteDataAdapter(landSelectCmd);
1590
1591 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, conn);
1592 SqliteDataAdapter lalDa = new SqliteDataAdapter(landAccessListSelectCmd);
1593
1594 DataSet tmpDS = new DataSet();
1595 try
1596 {
1597 pDa.Fill(tmpDS, "prims");
1598 sDa.Fill(tmpDS, "primshapes");
1599
1600 if (persistPrimInventories)
1601 iDa.Fill(tmpDS, "primitems");
1602
1603 tDa.Fill(tmpDS, "terrain");
1604 lDa.Fill(tmpDS, "land");
1605 lalDa.Fill(tmpDS, "landaccesslist");
1606 }
1607 catch (SqliteSyntaxException)
1608 {
1609 m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
1610 InitDB(conn);
1611 }
1612
1613 pDa.Fill(tmpDS, "prims");
1614 sDa.Fill(tmpDS, "primshapes");
1615
1616 if (persistPrimInventories)
1617 iDa.Fill(tmpDS, "primitems");
1618
1619 tDa.Fill(tmpDS, "terrain");
1620 lDa.Fill(tmpDS, "land");
1621 lalDa.Fill(tmpDS, "landaccesslist");
1622
1623 foreach (DataColumn col in createPrimTable().Columns)
1624 {
1625 if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
1626 {
1627 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1628 return false;
1629 }
1630 }
1631
1632 foreach (DataColumn col in createShapeTable().Columns)
1633 {
1634 if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
1635 {
1636 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1637 return false;
1638 }
1639 }
1640
1641 // XXX primitems should probably go here eventually
1642
1643 foreach (DataColumn col in createTerrainTable().Columns)
1644 {
1645 if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
1646 {
1647 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1648 return false;
1649 }
1650 }
1651
1652 foreach (DataColumn col in createLandTable().Columns)
1653 {
1654 if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
1655 {
1656 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1657 return false;
1658 }
1659 }
1660
1661 foreach (DataColumn col in createLandAccessListTable().Columns)
1662 {
1663 if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
1664 {
1665 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1666 return false;
1667 }
1668 }
1669
1670 return true;
1671 }
1672
1673 /***********************************************************************
1674 *
1675 * Type conversion functions
1676 *
1677 **********************************************************************/
1678
1679 private DbType dbtypeFromType(Type type)
1680 {
1681 if (type == typeof (String))
1682 {
1683 return DbType.String;
1684 }
1685 else if (type == typeof (Int32))
1686 {
1687 return DbType.Int32;
1688 }
1689 else if (type == typeof (Double))
1690 {
1691 return DbType.Double;
1692 }
1693 else if (type == typeof (Byte))
1694 {
1695 return DbType.Byte;
1696 }
1697 else if (type == typeof (Double))
1698 {
1699 return DbType.Double;
1700 }
1701 else if (type == typeof (Byte[]))
1702 {
1703 return DbType.Binary;
1704 }
1705 else
1706 {
1707 return DbType.String;
1708 }
1709 }
1710
1711 // this is something we'll need to implement for each db
1712 // slightly differently.
1713 private string sqliteType(Type type)
1714 {
1715 if (type == typeof (String))
1716 {
1717 return "varchar(255)";
1718 }
1719 else if (type == typeof (Int32))
1720 {
1721 return "integer";
1722 }
1723 else if (type == typeof (Int64))
1724 {
1725 return "integer";
1726 }
1727 else if (type == typeof (Double))
1728 {
1729 return "float";
1730 }
1731 else if (type == typeof (Byte[]))
1732 {
1733 return "blob";
1734 }
1735 else
1736 {
1737 return "string";
1738 }
1739 }
1740 }
1741}
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
new file mode 100644
index 0000000..2efd4aa
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserData.cs
@@ -0,0 +1,821 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using libsecondlife;
32using Mono.Data.SqliteClient;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.SQLite
36{
37 /// <summary>
38 /// A User storage interface for the SQLite database system
39 /// </summary>
40 public class SQLiteUserData : UserDataBase
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 /// <summary>
48 /// Artificial constructor called upon plugin load
49 /// </summary>
50 private const string SelectUserByUUID = "select * from users where UUID=:UUID";
51 private const string SelectUserByName = "select * from users where username=:username and surname=:surname";
52 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";
53
54 private const string userSelect = "select * from users";
55 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";
56
57 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
58 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
59
60 private DataSet ds;
61 private SqliteDataAdapter da;
62 private SqliteDataAdapter daf;
63 SqliteConnection g_conn;
64
65 override public void Initialise()
66 {
67 SqliteConnection conn = new SqliteConnection("URI=file:userprofiles.db,version=3");
68 TestTables(conn);
69
70 // This sucks, but It doesn't seem to work with the dataset Syncing :P
71 g_conn = conn;
72 g_conn.Open();
73
74 ds = new DataSet();
75 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
76 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
77
78 lock (ds)
79 {
80 ds.Tables.Add(createUsersTable());
81 ds.Tables.Add(createUserAgentsTable());
82 ds.Tables.Add(createUserFriendsTable());
83
84 setupUserCommands(da, conn);
85 da.Fill(ds.Tables["users"]);
86
87 setupUserFriendsCommands(daf, conn);
88 try
89 {
90 daf.Fill(ds.Tables["userfriends"]);
91 }
92 catch (SqliteSyntaxException)
93 {
94 m_log.Info("[SQLITE]: userfriends table not found, creating.... ");
95 InitDB(conn);
96 daf.Fill(ds.Tables["userfriends"]);
97 }
98
99 }
100
101 return;
102 }
103
104 // see IUserData
105 override public UserProfileData GetUserByUUID(LLUUID uuid)
106 {
107 lock (ds)
108 {
109 DataRow row = ds.Tables["users"].Rows.Find(Util.ToRawUuidString(uuid));
110 if (row != null)
111 {
112 UserProfileData user = buildUserProfile(row);
113 row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(uuid));
114 if (row != null)
115 {
116 user.currentAgent = buildUserAgent(row);
117 }
118 return user;
119 }
120 else
121 {
122 return null;
123 }
124 }
125 }
126
127 // see IUserData
128 override public UserProfileData GetUserByName(string fname, string lname)
129 {
130 string select = "surname = '" + lname + "' and username = '" + fname + "'";
131 lock (ds)
132 {
133 DataRow[] rows = ds.Tables["users"].Select(select);
134 if (rows.Length > 0)
135 {
136 UserProfileData user = buildUserProfile(rows[0]);
137 DataRow row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(user.UUID));
138 if (row != null)
139 {
140 user.currentAgent = buildUserAgent(row);
141 }
142 return user;
143 }
144 else
145 {
146 return null;
147 }
148 }
149 }
150
151 #region User Friends List Data
152
153 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
154 {
155 string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
156
157 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
158 {
159 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
160 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
161 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
162 cmd.ExecuteNonQuery();
163 }
164 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
165 {
166 cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.UUID.ToString()));
167 cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.UUID.ToString()));
168 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
169 cmd.ExecuteNonQuery();
170 }
171 }
172
173 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
174 {
175 string DeletePerms = "delete from friendlist where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
176 using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
177 {
178 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
179 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
180 cmd.ExecuteNonQuery();
181 }
182 }
183
184 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
185 {
186 string UpdatePerms = "update friendlist set perms=:perms where ownerID=:ownerID and friendID=:friendID";
187 using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
188 {
189 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
190 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
191 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
192 cmd.ExecuteNonQuery();
193 }
194 }
195
196 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
197 {
198 List<FriendListItem> returnlist = new List<FriendListItem>();
199
200 using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
201 {
202 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
203
204 try
205 {
206 using (IDataReader reader = cmd.ExecuteReader())
207 {
208 while (reader.Read())
209 {
210 FriendListItem user = new FriendListItem();
211 user.FriendListOwner = friendlistowner;
212 user.Friend = new LLUUID((string)reader[0]);
213 user.FriendPerms = Convert.ToUInt32(reader[1]);
214 user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
215 returnlist.Add(user);
216 }
217 reader.Close();
218 }
219 }
220 catch (Exception ex)
221 {
222 m_log.Error("[USER]: Exception getting friends list for user: " + ex.ToString());
223 }
224 }
225
226 return returnlist;
227 }
228
229
230
231
232 #endregion
233
234 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid)
235 {
236 m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
237 }
238
239
240 override public List<Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
241 {
242 List<Framework.AvatarPickerAvatar> returnlist = new List<Framework.AvatarPickerAvatar>();
243 string[] querysplit;
244 querysplit = query.Split(' ');
245 if (querysplit.Length == 2)
246 {
247 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn))
248 {
249 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
250 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%"));
251
252 using (IDataReader reader = cmd.ExecuteReader())
253 {
254 while (reader.Read())
255 {
256 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
257 user.AvatarID = new LLUUID((string) reader["UUID"]);
258 user.firstName = (string) reader["username"];
259 user.lastName = (string) reader["surname"];
260 returnlist.Add(user);
261 }
262 reader.Close();
263 }
264 }
265 }
266 else if (querysplit.Length == 1)
267 {
268 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn))
269 {
270 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
271 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%"));
272
273 using (IDataReader reader = cmd.ExecuteReader())
274 {
275 while (reader.Read())
276 {
277 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
278 user.AvatarID = new LLUUID((string) reader["UUID"]);
279 user.firstName = (string) reader["username"];
280 user.lastName = (string) reader["surname"];
281 returnlist.Add(user);
282 }
283 reader.Close();
284 }
285 }
286 }
287 return returnlist;
288 }
289
290 /// <summary>
291 /// Returns a user by UUID direct
292 /// </summary>
293 /// <param name="uuid">The user's account ID</param>
294 /// <returns>A matching user profile</returns>
295 override public UserAgentData GetAgentByUUID(LLUUID uuid)
296 {
297 try
298 {
299 return GetUserByUUID(uuid).currentAgent;
300 }
301 catch (Exception)
302 {
303 return null;
304 }
305 }
306
307 /// <summary>
308 /// Returns a session by account name
309 /// </summary>
310 /// <param name="name">The account name</param>
311 /// <returns>The user's session agent</returns>
312 override public UserAgentData GetAgentByName(string name)
313 {
314 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
315 }
316
317 /// <summary>
318 /// Returns a session by account name
319 /// </summary>
320 /// <param name="fname">The first part of the user's account name</param>
321 /// <param name="lname">The second part of the user's account name</param>
322 /// <returns>A user agent</returns>
323 override public UserAgentData GetAgentByName(string fname, string lname)
324 {
325 try
326 {
327 return GetUserByName(fname, lname).currentAgent;
328 }
329 catch (Exception)
330 {
331 return null;
332 }
333 }
334
335
336 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
337 {
338 DataTable users = ds.Tables["users"];
339 lock (ds)
340 {
341 DataRow row = users.Rows.Find(Util.ToRawUuidString(AgentID));
342 if (row == null)
343 {
344 m_log.Warn("[WEBLOGIN]: Unable to store new web login key for non-existant user");
345 }
346 else
347 {
348 UserProfileData user = GetUserByUUID(AgentID);
349 user.webLoginKey = WebLoginKey;
350 fillUserRow(row, user);
351 da.Update(ds, "users");
352
353 }
354 }
355
356 }
357
358 /// <summary>
359 /// Creates a new user profile
360 /// </summary>
361 /// <param name="user">The profile to add to the database</param>
362 override public void AddNewUserProfile(UserProfileData user)
363 {
364 DataTable users = ds.Tables["users"];
365 lock (ds)
366 {
367 DataRow row = users.Rows.Find(Util.ToRawUuidString(user.UUID));
368 if (row == null)
369 {
370 row = users.NewRow();
371 fillUserRow(row, user);
372 users.Rows.Add(row);
373 }
374 else
375 {
376 fillUserRow(row, user);
377
378 }
379 // This is why we're getting the 'logins never log-off'.. because It isn't clearing the
380 // useragents table once the useragent is null
381 //
382 // A database guy should look at this and figure out the best way to clear the useragents table.
383 if (user.currentAgent != null)
384 {
385 DataTable ua = ds.Tables["useragents"];
386 row = ua.Rows.Find(Util.ToRawUuidString(user.UUID));
387 if (row == null)
388 {
389 row = ua.NewRow();
390 fillUserAgentRow(row, user.currentAgent);
391 ua.Rows.Add(row);
392 }
393 else
394 {
395 fillUserAgentRow(row, user.currentAgent);
396 }
397 }
398 else
399 {
400 // I just added this to help the standalone login situation.
401 //It still needs to be looked at by a Database guy
402 DataTable ua = ds.Tables["useragents"];
403 row = ua.Rows.Find(Util.ToRawUuidString(user.UUID));
404
405 if (row == null)
406 {
407 // do nothing
408 }
409 else
410 {
411 row.Delete();
412 ua.AcceptChanges();
413 }
414 }
415
416 m_log.Info("[SQLITE]: " +
417 "Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
418 // save changes off to disk
419 da.Update(ds, "users");
420 }
421 }
422
423 /// <summary>
424 /// Creates a new user profile
425 /// </summary>
426 /// <param name="user">The profile to add to the database</param>
427 /// <returns>True on success, false on error</returns>
428 override public bool UpdateUserProfile(UserProfileData user)
429 {
430 try
431 {
432 AddNewUserProfile(user);
433 return true;
434 }
435 catch (Exception)
436 {
437 return false;
438 }
439 }
440
441 /// <summary>
442 /// Creates a new user agent
443 /// </summary>
444 /// <param name="agent">The agent to add to the database</param>
445 override public void AddNewUserAgent(UserAgentData agent)
446 {
447 // Do nothing. yet.
448 }
449
450 /// <summary>
451 /// Transfers money between two user accounts
452 /// </summary>
453 /// <param name="from">Starting account</param>
454 /// <param name="to">End account</param>
455 /// <param name="amount">The amount to move</param>
456 /// <returns>Success?</returns>
457 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
458 {
459 return true;
460 }
461
462 /// <summary>
463 /// Transfers inventory between two accounts
464 /// </summary>
465 /// <remarks>Move to inventory server</remarks>
466 /// <param name="from">Senders account</param>
467 /// <param name="to">Receivers account</param>
468 /// <param name="item">Inventory item</param>
469 /// <returns>Success?</returns>
470 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
471 {
472 return true;
473 }
474
475 /// <summary>
476 /// Returns the name of the storage provider
477 /// </summary>
478 /// <returns>Storage provider name</returns>
479 override public string getName()
480 {
481 return "Sqlite Userdata";
482 }
483
484 /// <summary>
485 /// Returns the version of the storage provider
486 /// </summary>
487 /// <returns>Storage provider version</returns>
488 override public string GetVersion()
489 {
490 return "0.1";
491 }
492
493 /***********************************************************************
494 *
495 * DataTable creation
496 *
497 **********************************************************************/
498 /***********************************************************************
499 *
500 * Database Definition Functions
501 *
502 * This should be db agnostic as we define them in ADO.NET terms
503 *
504 **********************************************************************/
505
506 private static DataTable createUsersTable()
507 {
508 DataTable users = new DataTable("users");
509
510 SQLiteUtil.createCol(users, "UUID", typeof (String));
511 SQLiteUtil.createCol(users, "username", typeof (String));
512 SQLiteUtil.createCol(users, "surname", typeof (String));
513 SQLiteUtil.createCol(users, "passwordHash", typeof (String));
514 SQLiteUtil.createCol(users, "passwordSalt", typeof (String));
515
516 SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32));
517 SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32));
518 SQLiteUtil.createCol(users, "homeLocationX", typeof (Double));
519 SQLiteUtil.createCol(users, "homeLocationY", typeof (Double));
520 SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double));
521 SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double));
522 SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double));
523 SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double));
524 SQLiteUtil.createCol(users, "created", typeof (Int32));
525 SQLiteUtil.createCol(users, "lastLogin", typeof (Int32));
526 SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String));
527 SQLiteUtil.createCol(users, "userInventoryURI", typeof (String));
528 SQLiteUtil.createCol(users, "userAssetURI", typeof (String));
529 SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32));
530 SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32));
531 SQLiteUtil.createCol(users, "profileAboutText", typeof (String));
532 SQLiteUtil.createCol(users, "profileFirstText", typeof (String));
533 SQLiteUtil.createCol(users, "profileImage", typeof (String));
534 SQLiteUtil.createCol(users, "profileFirstImage", typeof (String));
535 SQLiteUtil.createCol(users, "webLoginKey", typeof(String));
536 // Add in contraints
537 users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]};
538 return users;
539 }
540
541 private static DataTable createUserAgentsTable()
542 {
543 DataTable ua = new DataTable("useragents");
544 // this is the UUID of the user
545 SQLiteUtil.createCol(ua, "UUID", typeof (String));
546 SQLiteUtil.createCol(ua, "agentIP", typeof (String));
547 SQLiteUtil.createCol(ua, "agentPort", typeof (Int32));
548 SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean));
549 SQLiteUtil.createCol(ua, "sessionID", typeof (String));
550 SQLiteUtil.createCol(ua, "secureSessionID", typeof (String));
551 SQLiteUtil.createCol(ua, "regionID", typeof (String));
552 SQLiteUtil.createCol(ua, "loginTime", typeof (Int32));
553 SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32));
554 SQLiteUtil.createCol(ua, "currentRegion", typeof (String));
555 SQLiteUtil.createCol(ua, "currentHandle", typeof (String));
556 // vectors
557 SQLiteUtil.createCol(ua, "currentPosX", typeof (Double));
558 SQLiteUtil.createCol(ua, "currentPosY", typeof (Double));
559 SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double));
560 // constraints
561 ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]};
562
563 return ua;
564 }
565
566 private static DataTable createUserFriendsTable()
567 {
568 DataTable ua = new DataTable("userfriends");
569 // table contains user <----> user relationship with perms
570 SQLiteUtil.createCol(ua, "ownerID", typeof(String));
571 SQLiteUtil.createCol(ua, "friendID", typeof(String));
572 SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32));
573 SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32));
574 SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32));
575
576 return ua;
577 }
578
579 /***********************************************************************
580 *
581 * Convert between ADO.NET <=> OpenSim Objects
582 *
583 * These should be database independant
584 *
585 **********************************************************************/
586
587 private static UserProfileData buildUserProfile(DataRow row)
588 {
589 // TODO: this doesn't work yet because something more
590 // interesting has to be done to actually get these values
591 // back out. Not enough time to figure it out yet.
592 UserProfileData user = new UserProfileData();
593 LLUUID.TryParse((String)row["UUID"], out user.UUID);
594 user.username = (String) row["username"];
595 user.surname = (String) row["surname"];
596 user.passwordHash = (String) row["passwordHash"];
597 user.passwordSalt = (String) row["passwordSalt"];
598
599 user.homeRegionX = Convert.ToUInt32(row["homeRegionX"]);
600 user.homeRegionY = Convert.ToUInt32(row["homeRegionY"]);
601 user.homeLocation = new LLVector3(
602 Convert.ToSingle(row["homeLocationX"]),
603 Convert.ToSingle(row["homeLocationY"]),
604 Convert.ToSingle(row["homeLocationZ"])
605 );
606 user.homeLookAt = new LLVector3(
607 Convert.ToSingle(row["homeLookAtX"]),
608 Convert.ToSingle(row["homeLookAtY"]),
609 Convert.ToSingle(row["homeLookAtZ"])
610 );
611 user.created = Convert.ToInt32(row["created"]);
612 user.lastLogin = Convert.ToInt32(row["lastLogin"]);
613 user.rootInventoryFolderID = new LLUUID((String) row["rootInventoryFolderID"]);
614 user.userInventoryURI = (String) row["userInventoryURI"];
615 user.userAssetURI = (String) row["userAssetURI"];
616 user.profileCanDoMask = Convert.ToUInt32(row["profileCanDoMask"]);
617 user.profileWantDoMask = Convert.ToUInt32(row["profileWantDoMask"]);
618 user.profileAboutText = (String) row["profileAboutText"];
619 user.profileFirstText = (String) row["profileFirstText"];
620 LLUUID.TryParse((String)row["profileImage"], out user.profileImage);
621 LLUUID.TryParse((String)row["profileFirstImage"], out user.profileFirstImage);
622 user.webLoginKey = new LLUUID((String) row["webLoginKey"]);
623
624 return user;
625 }
626
627 private void fillUserRow(DataRow row, UserProfileData user)
628 {
629 row["UUID"] = Util.ToRawUuidString(user.UUID);
630 row["username"] = user.username;
631 row["surname"] = user.surname;
632 row["passwordHash"] = user.passwordHash;
633 row["passwordSalt"] = user.passwordSalt;
634
635
636 row["homeRegionX"] = user.homeRegionX;
637 row["homeRegionY"] = user.homeRegionY;
638 row["homeLocationX"] = user.homeLocation.X;
639 row["homeLocationY"] = user.homeLocation.Y;
640 row["homeLocationZ"] = user.homeLocation.Z;
641 row["homeLookAtX"] = user.homeLookAt.X;
642 row["homeLookAtY"] = user.homeLookAt.Y;
643 row["homeLookAtZ"] = user.homeLookAt.Z;
644
645 row["created"] = user.created;
646 row["lastLogin"] = user.lastLogin;
647 row["rootInventoryFolderID"] = user.rootInventoryFolderID;
648 row["userInventoryURI"] = user.userInventoryURI;
649 row["userAssetURI"] = user.userAssetURI;
650 row["profileCanDoMask"] = user.profileCanDoMask;
651 row["profileWantDoMask"] = user.profileWantDoMask;
652 row["profileAboutText"] = user.profileAboutText;
653 row["profileFirstText"] = user.profileFirstText;
654 row["profileImage"] = user.profileImage;
655 row["profileFirstImage"] = user.profileFirstImage;
656 row["webLoginKey"] = user.webLoginKey;
657
658 // ADO.NET doesn't handle NULL very well
659 foreach (DataColumn col in ds.Tables["users"].Columns)
660 {
661 if (row[col] == null)
662 {
663 row[col] = String.Empty;
664 }
665 }
666 }
667
668 private static UserAgentData buildUserAgent(DataRow row)
669 {
670 UserAgentData ua = new UserAgentData();
671
672 ua.UUID = new LLUUID((String) row["UUID"]);
673 ua.agentIP = (String) row["agentIP"];
674 ua.agentPort = Convert.ToUInt32(row["agentPort"]);
675 ua.agentOnline = Convert.ToBoolean(row["agentOnline"]);
676 ua.sessionID = new LLUUID((String) row["sessionID"]);
677 ua.secureSessionID = new LLUUID((String) row["secureSessionID"]);
678 ua.regionID = new LLUUID((String) row["regionID"]);
679 ua.loginTime = Convert.ToInt32(row["loginTime"]);
680 ua.logoutTime = Convert.ToInt32(row["logoutTime"]);
681 ua.currentRegion = new LLUUID((String) row["currentRegion"]);
682 ua.currentHandle = Convert.ToUInt64(row["currentHandle"]);
683 ua.currentPos = new LLVector3(
684 Convert.ToSingle(row["currentPosX"]),
685 Convert.ToSingle(row["currentPosY"]),
686 Convert.ToSingle(row["currentPosZ"])
687 );
688 return ua;
689 }
690
691 private static void fillUserAgentRow(DataRow row, UserAgentData ua)
692 {
693 row["UUID"] = ua.UUID;
694 row["agentIP"] = ua.agentIP;
695 row["agentPort"] = ua.agentPort;
696 row["agentOnline"] = ua.agentOnline;
697 row["sessionID"] = ua.sessionID;
698 row["secureSessionID"] = ua.secureSessionID;
699 row["regionID"] = ua.regionID;
700 row["loginTime"] = ua.loginTime;
701 row["logoutTime"] = ua.logoutTime;
702 row["currentRegion"] = ua.currentRegion;
703 row["currentHandle"] = ua.currentHandle.ToString();
704 // vectors
705 row["currentPosX"] = ua.currentPos.X;
706 row["currentPosY"] = ua.currentPos.Y;
707 row["currentPosZ"] = ua.currentPos.Z;
708 }
709
710 /***********************************************************************
711 *
712 * Database Binding functions
713 *
714 * These will be db specific due to typing, and minor differences
715 * in databases.
716 *
717 **********************************************************************/
718
719 private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn)
720 {
721 da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]);
722 da.InsertCommand.Connection = conn;
723
724 da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]);
725 da.UpdateCommand.Connection = conn;
726
727 SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID");
728 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String)));
729 delete.Connection = conn;
730 da.DeleteCommand = delete;
731 }
732
733 private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn)
734 {
735 daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]);
736 daf.InsertCommand.Connection = conn;
737
738 daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]);
739 daf.UpdateCommand.Connection = conn;
740
741 SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID");
742 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String)));
743 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String)));
744 delete.Connection = conn;
745 daf.DeleteCommand = delete;
746
747 }
748
749 private void InitDB(SqliteConnection conn)
750 {
751 string createUsers = SQLiteUtil.defineTable(createUsersTable());
752 string createFriends = SQLiteUtil.defineTable(createUserFriendsTable());
753
754 SqliteCommand pcmd = new SqliteCommand(createUsers, conn);
755 SqliteCommand fcmd = new SqliteCommand(createFriends, conn);
756
757 conn.Open();
758
759 try
760 {
761
762 pcmd.ExecuteNonQuery();
763 }
764 catch (System.Exception)
765 {
766 m_log.Info("[USERS]: users table already exists");
767 }
768
769 try
770 {
771 fcmd.ExecuteNonQuery();
772 }
773 catch (System.Exception)
774 {
775 m_log.Info("[USERS]: userfriends table already exists");
776 }
777
778 conn.Close();
779 }
780
781 private bool TestTables(SqliteConnection conn)
782 {
783 SqliteCommand cmd = new SqliteCommand(userSelect, conn);
784 SqliteCommand fcmd = new SqliteCommand(userFriendsSelect, conn);
785 SqliteDataAdapter pDa = new SqliteDataAdapter(cmd);
786 SqliteDataAdapter fDa = new SqliteDataAdapter(cmd);
787
788 DataSet tmpDS = new DataSet();
789 DataSet tmpDS2 = new DataSet();
790
791 try
792 {
793 pDa.Fill(tmpDS, "users");
794 fDa.Fill(tmpDS2, "userfriends");
795 }
796 catch (SqliteSyntaxException)
797 {
798 m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
799 InitDB(conn);
800 }
801 conn.Open();
802 try
803 {
804 cmd = new SqliteCommand("select webLoginKey from users limit 1;", conn);
805 cmd.ExecuteNonQuery();
806 }
807 catch (SqliteSyntaxException)
808 {
809 cmd = new SqliteCommand("alter table users add column webLoginKey text default '00000000-0000-0000-0000-000000000000';", conn);
810 cmd.ExecuteNonQuery();
811 pDa.Fill(tmpDS, "users");
812 }
813 finally
814 {
815 conn.Close();
816 }
817
818 return true;
819 }
820 }
821}
diff --git a/OpenSim/Data/SQLite/SQLiteUtils.cs b/OpenSim/Data/SQLite/SQLiteUtils.cs
new file mode 100644
index 0000000..1334e53
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUtils.cs
@@ -0,0 +1,269 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using Mono.Data.SqliteClient;
31
32namespace OpenSim.Framework.Data.SQLite
33{
34 /// <summary>
35 /// A base class for methods needed by all SQLite database classes
36 /// </summary>
37 public class SQLiteUtil
38 {
39 /***********************************************************************
40 *
41 * Database Definition Helper Functions
42 *
43 * This should be db agnostic as we define them in ADO.NET terms
44 *
45 **********************************************************************/
46
47 public static void createCol(DataTable dt, string name, Type type)
48 {
49 DataColumn col = new DataColumn(name, type);
50 dt.Columns.Add(col);
51 }
52
53 /***********************************************************************
54 *
55 * SQL Statement Creation Functions
56 *
57 * These functions create SQL statements for update, insert, and create.
58 * They can probably be factored later to have a db independant
59 * portion and a db specific portion
60 *
61 **********************************************************************/
62
63 public static SqliteCommand createInsertCommand(string table, DataTable dt)
64 {
65 /**
66 * This is subtle enough to deserve some commentary.
67 * Instead of doing *lots* and *lots of hardcoded strings
68 * for database definitions we'll use the fact that
69 * realistically all insert statements look like "insert
70 * into A(b, c) values(:b, :c) on the parameterized query
71 * front. If we just have a list of b, c, etc... we can
72 * generate these strings instead of typing them out.
73 */
74 string[] cols = new string[dt.Columns.Count];
75 for (int i = 0; i < dt.Columns.Count; i++)
76 {
77 DataColumn col = dt.Columns[i];
78 cols[i] = col.ColumnName;
79 }
80
81 string sql = "insert into " + table + "(";
82 sql += String.Join(", ", cols);
83 // important, the first ':' needs to be here, the rest get added in the join
84 sql += ") values (:";
85 sql += String.Join(", :", cols);
86 sql += ")";
87 SqliteCommand cmd = new SqliteCommand(sql);
88
89 // this provides the binding for all our parameters, so
90 // much less code than it used to be
91 foreach (DataColumn col in dt.Columns)
92 {
93 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
94 }
95 return cmd;
96 }
97
98 public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
99 {
100 string sql = "update " + table + " set ";
101 string subsql = String.Empty;
102 foreach (DataColumn col in dt.Columns)
103 {
104 if (subsql.Length > 0)
105 {
106 // a map function would rock so much here
107 subsql += ", ";
108 }
109 subsql += col.ColumnName + "= :" + col.ColumnName;
110 }
111 sql += subsql;
112 sql += " where " + pk;
113 SqliteCommand cmd = new SqliteCommand(sql);
114
115 // this provides the binding for all our parameters, so
116 // much less code than it used to be
117
118 foreach (DataColumn col in dt.Columns)
119 {
120 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
121 }
122 return cmd;
123 }
124
125
126 public static string defineTable(DataTable dt)
127 {
128 string sql = "create table " + dt.TableName + "(";
129 string subsql = String.Empty;
130 foreach (DataColumn col in dt.Columns)
131 {
132 if (subsql.Length > 0)
133 {
134 // a map function would rock so much here
135 subsql += ",\n";
136 }
137 subsql += col.ColumnName + " " + sqliteType(col.DataType);
138 if (dt.PrimaryKey.Length > 0)
139 {
140 if (col == dt.PrimaryKey[0])
141 {
142 subsql += " primary key";
143 }
144 }
145 }
146 sql += subsql;
147 sql += ")";
148 return sql;
149 }
150
151 /***********************************************************************
152 *
153 * Database Binding functions
154 *
155 * These will be db specific due to typing, and minor differences
156 * in databases.
157 *
158 **********************************************************************/
159
160 ///<summary>
161 /// This is a convenience function that collapses 5 repetitive
162 /// lines for defining SqliteParameters to 2 parameters:
163 /// column name and database type.
164 ///
165 /// It assumes certain conventions like :param as the param
166 /// name to replace in parametrized queries, and that source
167 /// version is always current version, both of which are fine
168 /// for us.
169 ///</summary>
170 ///<returns>a built sqlite parameter</returns>
171 public static SqliteParameter createSqliteParameter(string name, Type type)
172 {
173 SqliteParameter param = new SqliteParameter();
174 param.ParameterName = ":" + name;
175 param.DbType = dbtypeFromType(type);
176 param.SourceColumn = name;
177 param.SourceVersion = DataRowVersion.Current;
178 return param;
179 }
180
181 /***********************************************************************
182 *
183 * Type conversion functions
184 *
185 **********************************************************************/
186
187 public static DbType dbtypeFromType(Type type)
188 {
189 if (type == typeof (String))
190 {
191 return DbType.String;
192 }
193 else if (type == typeof (Int32))
194 {
195 return DbType.Int32;
196 }
197 else if (type == typeof (UInt32))
198 {
199 return DbType.UInt32;
200 }
201 else if (type == typeof (Int64))
202 {
203 return DbType.Int64;
204 }
205 else if (type == typeof (UInt64))
206 {
207 return DbType.UInt64;
208 }
209 else if (type == typeof (Double))
210 {
211 return DbType.Double;
212 }
213 else if (type == typeof (Boolean))
214 {
215 return DbType.Boolean;
216 }
217 else if (type == typeof (Byte[]))
218 {
219 return DbType.Binary;
220 }
221 else
222 {
223 return DbType.String;
224 }
225 }
226
227 // this is something we'll need to implement for each db
228 // slightly differently.
229 public static string sqliteType(Type type)
230 {
231 if (type == typeof (String))
232 {
233 return "varchar(255)";
234 }
235 else if (type == typeof (Int32))
236 {
237 return "integer";
238 }
239 else if (type == typeof (UInt32))
240 {
241 return "integer";
242 }
243 else if (type == typeof (Int64))
244 {
245 return "varchar(255)";
246 }
247 else if (type == typeof (UInt64))
248 {
249 return "varchar(255)";
250 }
251 else if (type == typeof (Double))
252 {
253 return "float";
254 }
255 else if (type == typeof (Boolean))
256 {
257 return "integer";
258 }
259 else if (type == typeof (Byte[]))
260 {
261 return "blob";
262 }
263 else
264 {
265 return "string";
266 }
267 }
268 }
269}
generated by cgit v1.1 at 2024-11-10 05:30:33 +0000